MySQL(二)

============================================================

where条件查询

功能: 对表中的数据进行帅选和过滤

语法:

1.判断的符号

= (!= <>不等于) > >= < <=

2.拼接不同的条件的关键字

and or not

3.查询对应的区间值

between 小值 and 大值 [小值,大值]  查询两者之间的范围值

4.查询具体在哪个范围中

in(1,21,333,444) 指定范围

5.模糊查询 like % 通配符  _ 通配符

like "%b"  匹配以b结尾的任意长度的字符串

like "b%"  匹配以b开头的任意长度的字符串

like "%b%" 匹配字符串中含有b的任意长度的内容

like "__b" 匹配总长度为3个字符,任意内容的字符串,并且以b结尾

like "b_"  匹配总长度为2个字符,任意内容的字符串,并且以b开头

--------------------------------------------------------------------------------------

建立两个表，附上字段，内容自己填：

department(id int, name varchar(255));

employee(id int, name varchar(25), sex varchar(8), age int, dep_id int);

---------------------------------------------------------------------------------------

# 1. 查询部门是sale的所有员工姓名:

select emp_name from employee where post="sale";

# 2. 部门是teacher , 收入大于10000的所有数据

select * from employee where post = "teacher" and salary > 10000;

# 3. 收入在1万到2万之间的所有员工姓名和收入

select emp_name,salary from employee where salary between 10000 and 20000;

# 4. 收入不在1万到2万之间的所有员工姓名和收入

select emp_name,salary from employee where salary not between 10000 and 20000;

# 5. 查看岗位描述为NULL的员工信息

select emp_name from employee where post_comment = null;

select emp_name from employee where post_comment = '';

select emp_name from employee where post_comment is null;

# 6. 查看岗位描述不为NULL的员工信息

select emp_name from employee where post_comment is not null;

# 7. 查询收入是3000 ,4000 ,5000,8300 所有员工的姓名和收入

select emp_name,salary from employee where salary in(3000,4000,5000,8300);

select emp_name,salary from employee where salary = 3000 or salary=4000 or salary=5000 or salary=8300;

# 8. 查询收入不是3000 ,4000 ,5000,8300 所有员工的姓名和收入

select emp_name,salary from employee where salary not in(3000,4000,5000,8300);

# 9. 以on结尾的员工名搜一下

select emp_name from employee where emp_name like "%on";

select emp_name from employee where emp_name like "ji%";

select emp_name from employee where emp_name like "_le_";

# 10. 统计员工一年的年薪

select concat(" 姓名: ",emp_name,"  收入:  ",salary) from employee;

# 计算年薪,可以在mysql中使用四则运算符 + - * /

select concat(" 姓名: ",emp_name,"  收入:  ",salary * 12) from employee;

select concat_ws("  :  ",emp_name,salary*12 ) from employee;

# 11. 查询部门的种类

# distinct  返回唯一不同的值

select distinct(post)  from employee;

# 二.group by 子句 分组分类

"""group by 字段,对数据进行分类, by后面接什么字段,select后面就搜什么字段"""

select sex from  employee group by sex;

# group_concat 按照分组把对应字段拼在一起;

select group_concat(emp_name),post from  employee group by post;

# 聚合函数

# count 统计总数 *所有

select count(*) from employee;

# max  统计最大值

select max(salary) from employee;

# min  统计最小值

select min(salary) from employee;

# avg  统计平均值

select avg(salary) from employee;

# sum  统计总和

select sum(salary) from employee;

# 1. 查询部门名以及各部门的平均薪资

select avg(salary),post from employee group by post;

# 2. 查询部门名以及各部门的最高薪资

select max(salary),post from employee group by post;

# 3. 查询部门名以及各部门的最低薪资

select min(salary),post from employee group by post;

# 4. 查询公司内男员工和女员工的个数

select count(*),sex from employee group by sex;

# 5. 查询部门名以及部门包含的所有员工名字

select group_concat(emp_name),post from employee group by post;

# 6 可以group by 两个字段,就可以同时搜索两个字段

select emp_name,post from employee group by post ,emp_name;

# 三.having 在数据分类分组之后,对数据进行二次过滤,一般配合group by来使用的;

# 找出各部门平均薪资,并且大于10000

select post , avg(salary) from  employee group by post having avg(salary) > 10000

# 1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

select post , group_concat(emp_name), count(*) from employee group by post having count(*) < 2;

# 2.查询各岗位平均薪资小于10000的岗位名、平均工资

select post , avg(salary) from employee group by post having avg(salary) < 10000

# 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

select post, avg(salary) from employee group by post having avg(salary) between 10000 and 20000

select post, avg(salary) from employee group by post having avg(salary) > 10000 and  avg(salary) < 20000;

# 四.order by 排序 , 按照某字段排序

order by age asc (升序) order by age desc (降序)

# 按照年龄从小到大排序

select * from employee order by age;

# 按照年龄从大到小排序

select * from employee order by age desc;

# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序

select * from employee order by age asc ,  hire_date desc;

# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列

select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc

# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列

select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc

# 五.limit 限制查询条数 (应用在分页)

""" limit m,n m代表从第几条数据开始查, n 代表查几条  m=0 代表的是第一条数据"""

select * from employee limit 0,10  # 0代表的是第一条数据

select * from employee limit 10,10  # 10代表的是第十一条数据

select * from employee limit 20,10  # 20代表的是第二十一条数据

# limit + num  num => 搜索的条数据

select * from employee limit 1

# 搜索这个表里面最后一条数据

select * from employee order by id desc limit 1

# 搜索这个表里面最后五条数据

select * from employee order by id desc limit 5

# 六.mysql 当中可以使用正则表达式 (不推荐,效率低)

select * from employee where  emp_name regexp ".*on$"; # mysql中无法识别?

select * from employee where  emp_name regexp "^程.*";

select * from employee where  emp_name regexp "^程.*金";

# `### part2  多表查询

# 1.内联接 :  inner join  :  两表或者多表之间,把满足条件的所有数据查询出来 (多表之间共同拥有的数据会被查询出来)

# 两表联查

select 字段 from 表1 inner join 表2 on 必要的关联条件

# 多表联查

select 字段 from 表1 inner join 表2 on 必要的关联条件1 inner join 表3 on 必要的关联条件2

select * from employee inner join department on employee.dep_id = department.id;

# as 起别名

select * from employee as e inner join department as d on e.dep_id = d.id;

# 也可以省略as (不推荐)

select * from employee e inner join department d on e.dep_id = d.id;

# where 写法默写是内联接( 等同于inner join )

select * from employee,department where employee.dep_id = department.id;

select * from employee as e ,department as d where e.dep_id = d.id;

# 2.外联接 :  left join左联接  / right join 右联接

# (1)left  join左联接 : 以左表为主,右表为辅,完整查询左表所有数据,右表没有的数据补null

select * from employee left join department on employee.dep_id = department.id;

# (2)right join右联接 : 以右表为主,左表为辅,完整查询右表所有数据,左表没有的数据补null

select * from employee right join department on employee.dep_id = department.id;

# 3.全联接 :  union

select * from employee left join department on employee.dep_id = department.id

union

select * from employee right join department on employee.dep_id = department.id;

# ### part3 子查询

------------------------------

子查询: 嵌套查询

(1) sql语句当中又嵌套了另外一条sql,用括号()进行包裹,表达一个整体

(2) 一般用在from子句,where子句... 身后,表达一个条件或者一个表

(3) 速度快慢: 单表查询 > 联表查询 > 子查询;

------------------------------

# 一.找出平均年龄大于25岁以上的部门

# (1) where

select

d.id,d.name

from

employee as e ,department as d

where

e.dep_id = d.id

group by

d.id,d.name

having

avg(e.age) > 25

# (2) inner join

select

d.id,d.name

from

employee as e inner join department as d on e.dep_id = d.id

group by

d.id,d.name

having

avg(e.age) > 25

# (3) 子查询

# 1.先找出平均年龄大于25岁的部门id

select dep_id from employee group by employee.dep_id having avg(age)>25; # 201 202

# 2.通过部门的id找部门的名字

select name from department where id in (201,202);

# 3.综合拼接:

select id , name from department where id in (select dep_id from employee group by employee.dep_id having avg(age)>25);

# 二.查看技术部门员工姓名

# (1) 普通的where 查询

select

e.id,e.name

from

employee as e,department as d

where

e.dep_id = d.id

and

d.name = "技术"

# (2) inner join

select

e.id,e.name

from

employee as e inner join department as d on e.dep_id = d.id

where

d.name = "技术"

# (3)子查询

# (1) 找技术部门对应的id

select id from department where name = "技术";

# (2) 通过id找员工姓名

select name from employee where dep_id = 200;

# (3) 综合拼接

select id,name from employee where dep_id = (select id from department where name = "技术");

# 三.查看哪个部门没员工

# 联表写法

select

d.id,d.name

from

department as d left join employee as e on d.id = e.dep_id

where

e.dep_id is null

# 1.找员工在哪些部门 (200  201  202 204)

select dep_id from employee  group by dep_id

# 2.把不在该部门的员工找出来

select  id  from department where id not in (200,201,202,204);

# 3.综合拼接

select  id,name  from department where id not in (select dep_id from employee  group by dep_id);

# 四.查询大于平均年龄的员工名与年龄

# 假设已经知道了平均年龄;

select name,age from employee where age > 30;

# 计算平均年龄

select avg(age) from employee;

# 综合拼接

select name,age from employee where age > (select avg(age) from employee);

# 五.把大于其本部门平均年龄的员工名和姓名查出来

# 1.先计算本部门的平均年龄是多少

select dep_id , avg(age) from employee  group by dep_id;

# 2.把查询的各部门平均年龄和employee进行联表,变成一张大表,最后做单表查询

select * from

employee as t1 inner join (1号查询出来的数据) as t2 on t1.dep_id = t2.dep_id

# 3.综合拼装

select * from

employee as t1 inner join (select dep_id , avg(age) as avg_age from employee  group by dep_id) as t2 on t1.dep_id = t2.dep_id

# 4.最后做一次单表查询,让age > 平均值

select * from

employee as t1 inner join (select dep_id , avg(age) as avg_age from employee  group by dep_id) as t2 on t1.dep_id = t2.dep_id

where

age >avg_age

# 六.查询每个部门最新入职的那位员工  # 利用上一套数据表进行查询;

employee表字段（id, emp_name, sex, age, hire_data, post_comment, salary, office, depart_id, max_date)

# 1.找各部门的最新入职的时间

select post,max(hire_date) as max_date from employee group by post

# 2.把子查询搜索出来的结果作为一张表和employee这个表做联表,把max_date拼接在employee这个表中,变成一张大表,最后做一次单表查询

select * from

employee as t1 inner join (1号数据) as t2 on t1.post = t2.post

where

t1.hire_date = t2.max_date

# 3.综合拼装

select emp_name , max_date from

employee as t1 inner join (select post,max(hire_date) as max_date from employee group by post) as t2 on t1.post = t2.post

where

t1.hire_date = t2.max_date

# 七.带EXISTS关键字的子查询

------------------------------------------------

exists 关键字 , 表达存在 , 应用在子查询中

如果内层sql , 能够查到数据, 返回True ,  外层sql执行相应的sql语句

如果内层sql , 不能查到数据, 返回False , 外层sql不执行sql语句

------------------------------------------------

select * from employee where exists (select * from employee where id = 1);

select * from employee where exists (select * from employee where id = 100000);

------------------------------------------------

总结:

子查询可以单独作为临时数据,作为一张表或者一个字段,通过()进行包裹,表达一个整体;

一般用在from,where,select.子句的后面

可以通过查询出来的数据和另外的表做联表变成更大一张表,

最后做单表查询,达到目的;

------------------------------------------------

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 221,695评论 6赞 515
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 94,569评论 3赞 399
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 168,130评论 0赞 360
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 59,648评论 1赞 297
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 68,655评论 6赞 397
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 52,268评论 1赞 309
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,835评论 3赞 421
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,740评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 46,286评论 1赞 318
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 38,375评论 3赞 340
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,505评论 1赞 352
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 36,185评论 5赞 350
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,873评论 3赞 333
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,357评论 0赞 24
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,466评论 1赞 272
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,921评论 3赞 376
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 45,515评论 2赞 359

推荐阅读更多精彩内容

  • MYSQL:多表查询
    一 介绍本节主题 多表连接查询复合条件连接查询子查询准备表 建表 create table department(...
    wujimozun阅读 217评论 0赞 0
  • MySql多表查询
    @[TOC] 1.准备表 2. 多表连接查询 交叉连接：不适用任何匹配条件。生成笛卡尔积mysql> select...
    态度哥阅读 140评论 0赞 0
  • mysql
    119.常⻅见SQL（必备）1.数据库库操作①创建数据库CREATE DATABASE 数据库名 charset ...
    wit92阅读 105评论 0赞 0
  • MySQL二
    约束 约束的概念 约束是作用于表中列上的规则,用于限制加入表的数据 约束的存在保证了数据库中正确性,有效性和完整性...
    苦风阅读 164评论 0赞 1
  • MySQL语句练习
    -- ------------------------------------------------SQL练习语...
    Owen270阅读 580评论 0赞 0