今天就跟大家聊聊有关如何分析MySQL子句及子查询，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
1. mysql_where子句_聚合函数
# ### part 单表查询""" select ... from ... where ... group by ... having ... order by ... limit ... """# 一.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开头"""# 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;2. mysql_其他子句语法
# 三.having 在数据分类分组之后,对数据进行二次过滤,一般配合group by来使用的;# 找出各部门平均薪资,并且大于10000select 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 20000select 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左联接 : 以左表为主,右表为辅,完整查询左表所有数据,右表没有的数据补nullselect * from employee left join department on employee.dep_id = department.id;# (2)right join右联接 : 以右表为主,左表为辅,完整查询右表所有数据,左表没有的数据补nullselect * from employee right join department on employee.dep_id = department.id;# 3.全联接 : unionselect * from employee left join department on employee.dep_id = department.idunionselect * from employee right join department on employee.dep_id = department.id;3. mysql_子查询
# ### part3 子查询 """子查询: 嵌套查询(1) sql语句当中又嵌套了另外一条sql,用括号()进行包裹,表达一个整体(2) 一般用在from子句,where子句... 身后,表达一个条件或者一个表(3) 速度快慢: 单表查询 > 联表查询 > 子查询;"""# 一.找出平均年龄大于25岁以上的部门# (1) whereselect d.id,d.namefrom employee as e ,department as dwheree.dep_id = d.idgroup by d.id,d.namehavingavg(e.age) > 25# (2) inner join select d.id,d.namefrom employee as e inner join department as d on e.dep_id = d.idgroup by d.id,d.namehavingavg(e.age) > 25# (3) 子查询# 1.先找出平均年龄大于25岁的部门idselect 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.namefromemployee as e,department as dwheree.dep_id = d.idandd.name = "技术"# (2) inner join select e.id,e.namefromemployee as e inner join department as d on e.dep_id = d.id whered.name = "技术"# (3)子查询# (1) 找技术部门对应的idselect 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 = "技术");# 三.查看哪个部门没员工# 联表写法selectd.id,d.namefromdepartment as d left join employee as e on d.id = e.dep_idwheree.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);department;+------+--------------+| id | name |+------+--------------+| 200 | 技术 || 201 | 人力资源 || 202 | 销售 || 203 | 运营 |+------+--------------+employee;+----+------------+--------+------+--------+| id | name | sex | age | dep_id |avg(age) +----+------------+--------+------+--------+| 1 | egon | male | 18 | 200 | 18| 2 | alex | female | 48 | 201 | 43| 3 | wupeiqi | male | 38 | 201 | 43| 4 | yuanhao | female | 28 | 202 | 28| 5 | liwenzhou | male | 18 | 200 | 18| 6 | jingliyang | female | 18 | 204 | 18+----+------------+--------+------+--------+# 四.查询大于平均年龄的员工名与年龄# 假设已经知道了平均年龄;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;+--------+----------+| dep_id | avg(age) |+--------+----------+| 200 | 18.0000 || 201 | 43.0000 || 202 | 28.0000 || 204 | 18.0000 |+--------+----------+# 2.把查询的各部门平均年龄和employee进行联表,变成一张大表,最后做单表查询select *fromemployee as t1 inner join (1号查询出来的数据) as t2 on t1.dep_id = t2.dep_id# 3.综合拼装select *fromemployee 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 *fromemployee 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_idwhere age >avg_age# 六.查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询;employee+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | max_date+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 1 | egon | male | 18 | 2017-03-01 | 办事处外交大使 | | 7300.33 | 401 | 1 | 2017-03-01| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | 2015-03-02| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | 2015-03-02 | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | 2015-03-02| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | 2015-03-02| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | 2015-03-02| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | 2015-03-02| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | 2015-03-02| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | 2017-01-27| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | 2017-01-27| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | 2017-01-27| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | 2017-01-27| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | 2017-01-27| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | 2016-03-11| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | 2016-03-11| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | 2016-03-11| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | 2016-03-11| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | 2016-03-11+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+# 1.找各部门的最新入职的时间select post,max(hire_date) as max_date from employee group by post+-----------------------------------------+------------+| post | max_date |+-----------------------------------------+------------+| operation | 2016-03-11 || sale | 2017-01-27 || teacher | 2015-03-02 || 办事处外交大使 | 2017-03-01 |+-----------------------------------------+------------+# 2.把子查询搜索出来的结果作为一张表和employee这个表做联表,把max_date拼接在employee这个表中,变成一张大表,最后做一次单表查询select *fromemployee as t1 inner join (1号数据) as t2 on t1.post = t2.postwheret1.hire_date = t2.max_date# 3.综合拼装select emp_name , max_datefromemployee as t1 inner join (select post,max(hire_date) as max_date from employee group by post) as t2 on t1.post = t2.postwheret1.hire_date = t2.max_date4. exists关键字
# 七.带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.子句的后面可以通过查询出来的数据和另外的表做联表变成更大一张表,最后做单表查询,达到目的;"""5. 练习所需表数据
# 单表练习#创建表create table employee(id int not null unique auto_increment,emp_name varchar(20) not null,sex enum('male','female') not null default 'male', #大部分是男的age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int, #一个部门一个屋子depart_id int);#三个部门：教学，销售，运营insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','办事处外交大使',7300.33,401,1), #以下是教学部('alex','male',78,'20150302','teacher',1000000.31,401,1),('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1),('成龙','male',48,'20101111','teacher',10000,401,1),('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门('丫丫','female',38,'20101101','sale',2000.35,402,2),('丁丁','female',18,'20110312','sale',1000.37,402,2),('星星','female',18,'20160513','sale',3000.29,402,2),('格格','female',28,'20170127','sale',4000.33,402,2),('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门('程咬金','male',18,'19970312','operation',20000,403,3),('程咬银','female',18,'20130311','operation',19000,403,3),('程咬铜','male',18,'20150411','operation',18000,403,3),('程咬铁','female',18,'20140512','operation',17000,403,3);# 练习:where# 1. 查询部门是sale的所有员工姓名:# 2. 部门是teacher , 收入大于10000的所有数据# 3. 收入在1万到2万之间的所有员工姓名和收入# 4. 收入不在1万到2万之间的所有员工姓名和收入# 5. 查看岗位描述为NULL的员工信息# 5. 查询收入是3000 ,4000 ,5000,8300 所有员工的姓名和收入# 6. 以on结尾的员工名搜一下# 7. 统计员工一年的年薪# 8. 查询部门的种类#练习:group# 1. 查询部门名以及各部门的平均薪资# 2. 查询部门名以及各部门的最高薪资# 3. 查询部门名以及各部门的最低薪资# 4. 查询公司内男员工和女员工的个数# 5. 查询部门名以及部门包含的所有员工名字#练习:having# 1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数# 2.查询各岗位平均薪资小于10000的岗位名、平均工资# 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资#练习:order by# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列# 多表练习:#建表create table department(id int,name varchar(20) );create table employee(id int primary key auto_increment,name varchar(20),sex enum('male','female') not null default 'male',age int,dep_id int);#插入数据insert into department values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');insert into employee(name,sex,age,dep_id) values('egon','male',18,200),('alex','female',48,201),('wupeiqi','male',38,201),('yuanhao','female',28,202),('liwenzhou','male',18,200),('jingliyang','female',18,204);# 查询:# 一.找出平均年龄大于25岁以上的部门# 二.查看技术部门员工姓名# 三.查看哪个部门没员工# 四.查询大于平均年龄的员工名与年龄# 五.把大于其本部门平均年龄的员工名和姓名查出来# 六.查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询;# 七.带EXISTS关键字的子查询6. 小练习
（1）表结构:
（2）黏贴如下sql,直接建表
# 1、创建表# 创建班级表create table class(cid int primary key auto_increment,caption varchar(32) not null);# 创建学生表create table student(sid int primary key auto_increment,gender char(1) not null,class_id int not null,sname varchar(32) not null,foreign key(class_id) references class(cid) on delete cascade on update cascade);# 创建老师表create table teacher(tid int primary key auto_increment,tname varchar(32) not null);# 创建课程表create table course(cid int primary key auto_increment,cname varchar(32) not null,teacher_id int not null,foreign key(teacher_id) references teacher(tid) on delete cascade on update cascade);# 创建成绩表create table score(sid int primary key auto_increment,student_id int not null,course_id int not null,num int not null,foreign key(student_id) references student(sid) on delete cascade on update cascade,foreign key(course_id) references course(cid) on delete cascade on update cascade);# 2、插入记录# 班级表插入记录insert into class values('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年一班');# 学生表插入记录insert into student values('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'),('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');# 老师表插入记录insert into teacher values('1', '张磊'), ('2', '李平'), ('3', '刘海燕'), ('4', '朱云海'), ('5', '李春秋');# 课程表插入记录insert into course values('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');# 成绩表插入记录insert into score values('1', '1', '1', '10'), ('2', '1', '2', '9'), ('3', '1', '3', '76'),('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'),('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');（3）练习题目
1、查询所有的课程的名称以及对应的任课老师姓名2、查询学生表中男女生各有多少人3、查询物理成绩等于100的学生的姓名4、查询平均成绩大于八十分的同学的姓名和平均成绩5、查询所有学生的学号，姓名，选课数，总成绩6、 查询姓李老师的个数7、 查询没有报李平老师课的学生姓名8、 查询物理课程的分数比生物课程的分数高的学生的学号9、 查询没有同时选修物理课程和体育课程的学生姓名10、查询挂科超过两门(包括两门)的学生姓名和班级11、查询选修了所有课程的学生姓名12、查询李平老师教的课程的所有成绩记录13、查询全部学生都选修了的课程号和课程名14、查询每门课程被选修的次数15、查询只选修了一门课程的学生学号和姓名16、查询所有学生考出的成绩并按从高到低排序（成绩去重）17、查询平均成绩大于85的学生姓名和平均成绩18、查询生物成绩不及格的学生姓名和对应生物分数19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名20、查询每门课程成绩最好的课程id、学生姓名和分数21、查询不同课程但成绩相同的课程号、学生号、成绩 22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称 23、查询所有选修了学号为2的同学选修过的一门或者多门课程的同学学号和姓名 24、任课最多的老师中学生单科成绩最高的课程id、学生姓名和分数看完上述内容，你们对如何分析MySQL子句及子查询有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注编程网行业资讯频道，感谢大家的支持。
