【一】分组查询group by
1.要先关闭严格模式，才能进行分组查询
set global sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
2.select * from emp group by post;
意思：以post分组查询所有emp表中的信息
3.指定别名
select post as "部门" ,max(salary) as "最高薪资" from emp group by post;
【二】聚合函数
(1)max--->最大值
select name as '姓名',max(salary) as '最高薪资' from emp group by post;
+----------+----------+
| 姓名 | 最高薪资 |
+----------+----------+
| dream | 730.33 |
| mengmeng | 15000.99 |
| 娜娜 | 420.33 |
| 大古 | 630.33 |
+----------+----------+
(2)min--->最小值
(3)avg--->平均值
(4)sum---->取总和
select post as '部门',sum(salary) as '每个部门总薪资' from emp group by post;
+----------------+----------------+
| 部门 | 每个部门总薪资 |
+----------------+----------------+
| 陌夜痴梦久生情 | 730.33 |
| teacher | 65003.61 |
| sale | 2173.45 |
| operation | 2132.33 |
+----------------+----------------+
(5)count--->计数
注意：不能对null计数
mysql> select post as '部门',count(name) as '人数' from emp group by post;
+----------------+------+
| 部门 | 人数 |
+----------------+------+
| 陌夜痴梦久生情 | 1 |
| teacher | 5 |
| sale | 6 |
| operation | 5 |
+----------------+------+
(6)group_concat--->可以列出分组之后的每组具体的值
1.select post as '部门',group_concat(name) as '人员' from e
mp group by post;
+----------------+-----------------------------------------+
| 部门 | 人员 |
+----------------+-----------------------------------------+
| operation | 大古,张三,李四,王五,赵六 |
| sale | 娜娜,芳芳,小明,亚洲,华华,田七 |
| teacher | mengmeng,xiaomeng,xiaona,xiaoqi,suimeng |
| 陌夜痴梦久生情 | dream |
+----------------+-----------------------------------------+
2.还可以对数据进行拼接后列出
select post,group_concat(name,'_123') from emp group by p
ost;
+----------------+-------------------------------------------------------------+
| post | group_concat(name,'_123')
|
+----------------+-------------------------------------------------------------+
| operation | 大古_123,张三_123,李四_123,王五_123,赵六_123 |
| sale | 娜娜_123,芳芳_123,小明_123,亚洲_123,华华_123,田七_123 |
| teacher | mengmeng_123,xiaomeng_123,xiaona_123,xiaoqi_123,suimeng_123 |
| 陌夜痴梦久生情 | dream_123
|
+----------------+-------------------------------------------------------------+
3.可以查人可以拼接，也可以拼上其他字段
mysql> select post,group_concat(name,':',age) from emp group by
post;
+----------------+--------------------------------------------------------+
| post | group_concat(name,':',age)
|
+----------------+--------------------------------------------------------+
| operation | 大古:66,张三:51,李四:47,王五:39,赵六:36 |
| sale | 娜娜:69,芳芳:45,小明:34,亚洲:42,华华:55,田七:44 |
| teacher | mengmeng:25,xiaomeng:35,xiaona:29,xiaoqi:27,suimeng:33 |
| 陌夜痴梦久生情 | dream:78
|
+----------------+--------------------------------------------------------+
【三】group by(分组) 注意事项
(1)关键字 where 和 group by 同时出现
where 先对整体数据进行过滤，group by 再对数据进行分组
(2)where 筛选条件不能使用聚合函数
(3)聚合函数只能在分组之后使用
什么时候会要where 和 group by 同时出现---》要对数据进行过滤后分组
【四】having分组之后筛选
● having与where的功能是一模一样的 都是对数据进行筛选
○ where用在分组之前的筛选
○ havng用在分组之后的筛选
例题1：统计各部门年龄在 30 岁以上的员工的工资，并且保留平均薪资大于1w的部门：
select post as '部门',avg(salary) as '平均薪资薪资' from emp where age>30 group by post having avg(salary)>10000;
+---------+--------------+
| 部门 | 平均薪资薪资 |
+---------+--------------+
| teacher | 14500.805000 |
+---------+--------------+
【五】distinct(去重)
● 必须是完全一样的数据才可以去重
● 一定要注意主键的问题
● 在主键存在的情况下是一定不可能去重的
用法：写在字段前
select distinct id,age from emp;
【六】order by(排序)
● order by : 默认是升序
● asc 默认可以省略不写 ---> 修改降序
● desc : 降序
用法：以什么字段来排序
select * from emp order by salary;
select * from emp order by salary desc;
【六】limit(限制展示条数)
limit x,y : 第一个参数是起始位置，第二个是条数
用法：
select * from emp limit 0,6;
【七】正则记住贪婪匹配
用法：
属性名 REGEXP '匹配方式'
| 选项 | 说明 | 例子 | 匹配值示例 |
|---|
| ^ | 匹配文本的开始字符 | ‘^b’ 匹配以字母 b 开头的字符串 | book、big、banana、bike |
| $ | 匹配文本的结束字符 | ‘st$’ 匹配以 st 结尾的字符串 | test、resist、persist |
| . | 匹配任何单个字符 | ‘b.t’ 匹配任何 b 和 t 之间有一个字符 | bit、bat、but、bite |
| * | 匹配前面的字符 0 次或多次 | ‘f*n’ 匹配字符 n 前面有任意个字符 f | fn、fan、faan、abcn |
| + | 匹配前面的字符 1 次或多次 | ‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a | ba、bay、bare、battle |
| ? | 匹配前面的字符 0 次或1次 | ‘sa?’ 匹配0个或1个a字符 | sa、s |
| 字符串 | 匹配包含指定字符的文本 | ‘fa’ 匹配包含‘fa’的文本 | fan、afa、faad |
| [字符集合] | 匹配字符集合中的任何一个字符 | ‘[xz]’ 匹配 x 或者 z | dizzy、zebra、x-ray、extra |
| [^] | 匹配不在括号中的任何字符 | ‘abc’ 匹配任何不包含 a、b 或 c 的字符串 | desk、fox、f8ke |
| 字符串{n,} | 匹配前面的字符串至少 n 次 | ‘b{2}’ 匹配 2 个或更多的 b | bbb、bbbb、bbbbbbb |
| 字符串{n,m} | 匹配前面的字符串至少 n 次， 至多 m 次 | ‘b{2,4}’ 匹配最少 2 个，最多 4 个 b | bbb、bbbb |
【八】子查询
将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件
eg:你要查某员工的所在部门名
先从员工表中查出部门id，以这个部门id去部门表查部门名
例题：获取员工dream所在的部门名称
select name from dep where id=(select dep_id from emp where name='dream');
+--------+
| name |
+--------+
| 技术部 |
+--------+
【九】联表查询
先将多张表拼接到一起 形成一张大表 然后基于该表查询获取数据
eg:你要查某员工的所在部门名
可以把员工表，以部门id进行拼接，然后查询员工姓名查出部门名
(1)直接拼会是笛卡尔积就是dep条数*emp条数
select * from dep,emp;
(2)以dep_id进行拼接
select * from dep,emp where emp.dep_id = dep.id;
+------+--------------+----+----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+----------+--------+------+--------+
| 200 | 技术部 | 1 | dream | male | 18 | 200 |
| 201 | 人力资源 | 2 | chimeng | female | 18 | 201 |
| 202 | 销售部 | 3 | menmgneg | male | 38 | 202 |
| 203 | 运营部 | 4 | hope | male | 18 | 203 |
| 204 | 售后部 | 5 | own | male | 28 | 204 |
+------+--------------+----+----------+--------+------+--------+
(3)inner join：内连接
只拼接两张表中共有的数据部分
如果员工表中有没有部门号的就会被去除不显示
select * from emp inner join dep on emp.dep_id = dep.id;
+----+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
+----+----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
(4)left join：左连接
左表所有的数据都展示出来，没有对应的项就用null表示
select * from emp left join dep on emp.dep_id = dep.id;
+----+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
| 6 | thdream | male | 18 | 205 | NULL | NULL |
+----+----------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
(5)right join：右连接
右表所有的数据都展示出来，没有对应的项就用null表示
由于 thdream没有部门，但是右表是部门表所以他不显示
select * from emp right join dep on emp.dep_id = dep.id;
+------+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
+------+----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
(6)union：全连接
左右两表的数据都展示出来
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
;
+------+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
| 6 | thdream | male | 18 | 205 | NULL | NULL |
+------+----------+--------+------+--------+------+--------------+
6 rows in set (0.02 sec)
例题1：查询平均年龄在25岁以上的部门名称
mysql> select dep.name from emp left join dep on emp.dep_id=dep.id group by dep.name having avg(age)>25;
+--------+
| name |
+--------+
| 销售部 |
| 售后部 |
+--------+
【十】exist
● 只返回布尔值
● 返回true时，外层查询语句执行
● 返回false时，外层查询语句不执行
用法：
select * from emp where exists (select id from dep where id > 3);
# 结果为 true
【作业】
1、查询所有的课程的名称以及对应的任课老师姓名
select teacher.tname,course.cname from course join teacher on course.teacher_id=teacher.tid;
2、查询学生表中男女生各有多少人
SELECT gender as '性别',count(gender) as '人数' from student GROUP BY gender;
3、查询物理成绩等于100的学生的姓名
select sname from student where sid in(
select student_id from score where num=100 and course_id=(
select cid from course where cname='物理'));
4、查询平均成绩大于八十分的同学的姓名和平均成绩
select sname,AVG(num) from student join score on score.student_id=student.sid GROUP BY sname HAVING AVG(num)>80;
5、查询所有学生的学号，姓名，选课数，总成绩
select student.sid,sname,count(course_id),sum(num) from student join score on score.student_id=student.sid GROUP BY sname;
6、 查询姓李老师的个数
SELECT count(tname) from teacher WHERE tname like'李%';
7、 查询没有报李平老师课的学生姓名
select sname from student where sid not in(
select student_id from score where course_id in (
select cid from course where teacher_id=(select tid from teacher WHERE tname='李平老师')));
8、 查询物理课程比生物课程高的学生的学号
思路：分别把物理的学号和分数单独变成一个表，生物也是，然后拼接进行比较
select t1.student_id from(
select student_id,num from score WHERE course_id=(select cid from course where cname='物理'))as t1
join
(select student_id,num from score WHERE course_id=(select cid from course where cname='生物'))as t2 on t1.student_id=t2.student_id where t1.num>t2.num
;
9、 查询没有同时选修物理课程和体育课程的学生姓名
select sname from student where sid not in(
select student_id from score where course_id =
(select cid from course where cname='物理' )and course_id =(
select cid from course where cname='体育') );
10、查询挂科超过两门(包括两门)的学生姓名和班级
、查询选修了所有课程的学生姓名
select sname,caption from student join class on cid=class_id where sid in(select sid from score GROUP BY student_id having count(num<60));
12、查询李平老师教的课程的所有成绩记录
SELECT student_id,course_id,num from score where course_id in(SELECT cid from course join teacher on tid=teacher_id where tname='李平老师');
13、查询全部学生都选修了的课程号和课程名
SELECT DISTINCT cname ,cid from course JOIN score on course_id=cid
14、查询每门课程被选修的次数
SELECT cname ,count(cname) from course JOIN score on course_id=cid GROUP BY cid
15、查询之选修了一门课程的学生姓名和学号
SELECT sname ,student.sid from student JOIN score on student_id=student.sid GROUP BY sid HAVING count(course_id)=1;
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT num from score ORDER BY num ;
17、查询平均成绩大于85的学生姓名和平均成绩
select sname,AVG(num) from student join score on score.student_id=student.sid GROUP BY sname HAVING AVG(num)>85;
18、查询生物成绩不及格的学生姓名和对应生物分数
select sname,num from student join score on score.student_id=student.sid where (num<60 and course_id in (select cid from course where cname='生物' ));
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT student_id,sname from score join student on student.sid=score.sid GROUP BY course_id having course_id in(SELECT cid from course join teacher on tid=teacher_id where tname='李平老师') ORDER BY avg(num) desc LIMIT 0,1;
20、查询每门课程成绩最好的前两名学生姓名
21、查询不同课程但成绩相同的学号，课程号，成绩
22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
select sname as '学生姓名' ,course_id as '所选课程' from student join score on score.student_id=student.sid where student.sid not in(
select student_id from score where course_id in (
select cid from course where teacher_id =(select tid from teacher WHERE tname='叶平老师')));
23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
select DISTINCT sname as '学生姓名' ,student_id as '学号' from student join score on score.student_id=student.sid where score.course_id in(SELECT course_id from score where student_id=1) and student_id !=1;
24、任课最多的老师中学生单科成绩最高的学生姓名
select sname from student join score on score.student_id=student.sid where course_id in (select course_id from course where teacher_id=(select teacher_id from course GROUP BY teacher_id ORDER BY COUNT(teacher_id)DESC limit 1)) ORDER BY num DESC LIMIT 1;
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
