MySQL数据库练习【二】 @TOC一、MySQL基本语法-- 创建数据库时指定编码方式 create database students character set utf8; -- 查看数据库 show databases; -- 查看已经创建的数据库信息 show create database students; -- 修改数据库的编码 alter database students default character set utf8; -- 删除数据库 drop database students; -- 查看数据表 show create table student; -- 查看表结构 desc student; -- 修改创建好的表名 将原来的表名改为stu alter table student rename stu; -- 修改表中字段 将原来的字段名name改为sname字段名 alter table stu change name sname char(10); -- 添加字段 在stu表中添加一个age整形字段 alter table student add age int; -- 删除字段 刚刚创建的age整形字段 alter table student drop age; -- 删除表 drop table if exists student;二、建库建表-数据准备create database school; use school; --1.学生表 CREATE TABLE `student` ( `sid` varchar(10) NOT NULL COMMENT '学生编号', `sname` varchar(20) NOT NULL COMMENT '学生姓名', `sage` DATETIME NOT NULL COMMENT '出生年月', `ssex` varchar(10) NOT NULL COMMENT '学生性别' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表'; INSERT INTO student VALUES('01' , '赵雷' , '1990-01-01' , '男'),('02' , '钱电' , '1990-12-21' , '男'), ('03' , '孙风' , '1990-05-20' , '男'),('04' , '李云' , '1990-08-06' , '男'), ('05' , '周梅' , '1991-12-01' , '女'),('06' , '吴兰' , '1992-03-01' , '女'), ('07' , '郑竹' , '1989-07-01' , '女'),('08' , '王菊' , '1990-01-20' , '女'); select * from student; desc student; --2.课程表 CREATE TABLE `course` ( `cid` varchar(10) NOT NULL COMMENT '课程编号', `cname` varchar(10) NOT NULL COMMENT '课程名称', `tid` varchar(10) NOT NULL COMMENT '教师编号' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表'; INSERT INTO course VALUES('01' , '语文' , '02'),('02' , '数学' , '01'),('03' , '英语' , '03'); select * from course; desc course; --3.教师表 CREATE TABLE `teacher` ( `tid` varchar(10) NOT NULL COMMENT '教师编号', `tname` varchar(10) NOT NULL COMMENT '教师姓名' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师表'; INSERT INTO teacher VALUES('01' , '张三'),('02' , '李四'),('03' , '王五'); select * from teacher; desc teacher; --4.成绩表 CREATE TABLE `sc` ( `sid` varchar(10) NOT NULL COMMENT '学生编号', `cid` varchar(10) NOT NULL COMMENT '课程编号', `score` DECIMAL(18,1) NOT NULL COMMENT '分数' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表'; INSERT INTO sc VALUES('01' , '01' , 80),('01' , '02' , 90),('01' , '03' , 99), ('02' , '01' , 70),('02' , '02' , 60),('02' , '03' , 80),('03' , '01' , 80), ('03' , '02' , 80),('03' , '03' , 80),('04' , '01' , 50),('04' , '02' , 30), ('04' , '03' , 20),('05' , '01' , 76),('05' , '02' , 87),('06' , '01' , 31), ('06' , '03' , 34),('07' , '02' , 89),('07' , '03' , 98); select * from sc; desc sc;学生表student成绩表sc课程表course教师表teachersid：学生编号sid：学生编号cid：课程编号tid：教师编号sname：学生姓名cid：课程编号cname：课程名称tname：教师姓名sage：出生年月score：分数tid：教师编号 ssex：学生性别 sidsnamesagessex01赵雷1990-01-01 00:00:00男02钱电1990-12-21 00:00:00男03孙风1990-05-20 00:00:00男04李云1990-08-06 00:00:00男05周梅1991-12-01 00:00:00女06吴兰1992-03-01 00:00:00女07郑竹1989-07-01 00:00:00女08王菊1990-01-20 00:00:00女09李白2024-02-08 00:00:00男10玛利亚2024-02-08 00:00:00女sidcidscore010180.0010290.0010399.0020170.0020260.0020380.0030180.0030280.0030380.0040150.0040230.0040320.0050176.0050287.0060131.0060334.0070289.0070398.0cidcnametid01语文0202数学0103英语03tidtname01张三02李四03王五三、MySQL经典50题-题目4.1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 4.2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 4.3.0、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 4.3.1、总分超过200分的同学的学生编号和学生姓名和总成绩 4.4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 4.5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 4.6、查询"李"姓老师的数量 4.7、查询学过"张三"老师授课的同学的信息 4.8、查询没学过"张三"老师授课的同学的信息 4.9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 4.10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 4.11、查询没有学全所有课程的同学的信息 4.12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 4.13.0、查询和"01"号的同学学习的课程完全相同的其他同学的信息 4.13.1、MySQL中，聚合函数GROUP_CONCAT()用指定分隔符连接字符串 4.14、查询没学过"张三"老师讲授的任一门课程的学生姓名 4.15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 4.16、检索"01"课程分数小于60，按分数降序排列的学生信息 4.17.1、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 4.17.2、CASE WHEN函数 4.17.3、CASE WHEN函数使用 4.18、查询各科成绩最高分、最低分和平均分： 4.19、按各科成绩进行排序，并显示排名 4.20、查询学生的总成绩并进行排名 4.21、查询不同老师所教不同课程平均分从高到低显示 4.22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 4.23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 4.24、查询学生平均成绩及其名次 4.24.1、MySql中@符号的使用【经典】 4.25、查询各科成绩前三名的记录【经典】 4.26、查询每门课程被选修的学生数 4.27、查询出只有两门课程的全部学生的学号和姓名 4.28、查询男生、女生人数 4.29、查询名字中含有"风"字的学生信息 4.30、查询同名同性学生名单，并统计同名人数 4.31、查询1990年出生的学生名单 4.32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 4.33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 4.34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 4.35、查询所有学生的课程及分数情况； 4.36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数 4.37、查询不及格的课程 4.38、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名 4.39、求每门课程的学生人数 4.40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩 4.41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 4.42、查询每门功课成绩最好的前两名【经典】 4.43、统计每门课程的学生选修人数（超过5人的课程才统计）；要求输出课程号和选修人数，查询结果按人数降序排列。 4.44、检索至少选修两门课程的学生学号 4.45、查询选修了全部课程的学生信息 4.46、查询各学生的年龄 4.47、查询本周过生日的学生 4.48、查询下周过生日的学生 4.49、查询本月过生日的学生 4.50、查询下月过生日的学生四、MySQL经典50题-答案4.1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数-- 学生表student、成绩表sc、课程表course、教师表teacher -- 表结构：student、sc、course -- 连接条件：student.sid=sc.sid -- 先将课程为01和02的课程及对应分数筛选出来,再INNER JOIN,ON为01.sid = 02.sid,WHERE条件为01.score >02.score,结果'存'为新t3表 -- 再将student表和t3表INNER JOIN SELECT student.*,t3.score FROM (SELECT t1.sid,t1.score FROM (SELECT sid,score FROM sc WHERE cid = "01") as t1 INNER JOIN (SELECT sid,score FROM sc WHERE cid = "02") as t2 ON t1.sid=t2.sid WHERE t1.score > t2.score )as t3 INNER JOIN student ON t3.sid = student.sid; +-----+--------+---------------------+------+-------+ | sid | sname | sage | ssex | score | +-----+--------+---------------------+------+-------+ | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | +-----+--------+---------------------+------+-------+ 2 rows in set (0.00 sec)select stu.*, s1.score '01课程分数', s2.score '02课程分数' from student stu, (select sid,score from sc where cid = '01') s1, (select sid,score from sc where cid = '02') s2 where s1.score > s2.score -- 前者成绩高 and s1.sid = s2.sid and s1.sid = stu.sid;mysql> select -> stu.*, -> s1.score '01课程分数', -> s2.score '02课程分数' -> from -> student stu, -> (select sid,score from sc where cid = '01') s1, -> (select sid,score from sc where cid = '02') s2 -> where s1.score > s2.score -- 前者成绩高 -> and s1.sid = s2.sid -> and s1.sid = stu.sid; +-----+--------+---------------------+------+----------------+----------------+ | SID | Sname | Sage | Ssex | 01课程分数 | 02课程分数 | +-----+--------+---------------------+------+----------------+----------------+ | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | 30.0 | +-----+--------+---------------------+------+----------------+----------------+ 2 rows in set (0.01 sec)select stu.*, s1.score '01课程分数', s2.score '02课程分数' from student stu, sc s1, sc s2 where s1.score > s2.score -- 前者成绩高 and s1.sid = s2.sid and s1.cid = '01' and s2.cid = '02' and s1.sid = stu.sid;mysql> select -> stu.*, -> s1.score '01课程分数', -> s2.score '02课程分数' -> from -> student stu, -> sc s1, -> sc s2 -> where s1.score > s2.score -- 前者成绩高 -> and s1.sid = s2.sid -> and s1.cid = '01' -> and s2.cid = '02' -> and s1.sid = stu.sid; +-----+--------+---------------------+------+----------------+----------------+ | SID | Sname | Sage | Ssex | 01课程分数 | 02课程分数 | +-----+--------+---------------------+------+----------------+----------------+ | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | 30.0 | +-----+--------+---------------------+------+----------------+----------------+ 2 rows in set (0.00 sec)select stu.*, s1.score '01课程分数', s2.score '02课程分数' from student stu inner join sc s1 on s1.sid = stu.sid and s1.cid = '01' -- 两个表通过学号连接，指定01 left join sc s2 on stu.sid = s2.sid and s2.cid = '02' or s2.cid = NULL -- 指定02，或者c中的c_id直接不存在 -- 为NULL的条件可以不存在，因为左连接中会直接排除c表中不存在的数据，包含NULL where s1.score > s2.score; -- 判断条件mysql> select -> stu.*, -> s1.score '01课程分数', -> s2.score '02课程分数' -> from -> student stu -> inner join sc s1 on s1.sid = stu.sid and s1.cid = '01' -- 两个表通过学号连接，指定01 -> left join sc s2 on stu.sid = s2.sid and s2.cid = '02' or s2.cid = NULL -- 指定02，或者c中的c_id直接不存在 -> -- 为NULL的条件可以不存在，因为左连接中会直接排除c表中不存在的数据，包含NULL -> where s1.score > s2.score; -- 判断条件 +-----+--------+---------------------+------+----------------+----------------+ | SID | Sname | Sage | Ssex | 01课程分数 | 02课程分数 | +-----+--------+---------------------+------+----------------+----------------+ | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | 30.0 | +-----+--------+---------------------+------+----------------+----------------+ 2 rows in set (0.00 sec)4.2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数select stu.*, s1.score '01课程分数', s2.score '02课程分数' from student stu left join sc s1 on stu.sid = s1.sid and s1.cid = '01' or s1.cid = NULL -- 包含NULL的数据 inner join sc s2 on stu.sid = s2.sid and s2.cid = '02' where s1.score < s2.score;mysql> select -> stu.*, -> s1.score '01课程分数', -> s2.score '02课程分数' -> from -> student stu -> left join sc s1 on stu.sid = s1.sid and s1.cid = '01' or s1.cid = NULL -- 包含NULL的数据 -> inner join sc s2 on stu.sid = s2.sid and s2.cid = '02' -> where s1.score < s2.score; +-----+--------+---------------------+------+----------------+----------------+ | SID | Sname | Sage | Ssex | 01课程分数 | 02课程分数 | +-----+--------+---------------------+------+----------------+----------------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 80.0 | 90.0 | | 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 76.0 | 87.0 | +-----+--------+---------------------+------+----------------+----------------+ 2 rows in set (0.00 sec)4.3.0、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩select stu.sid '学生编号', stu.sname '学生姓名', round(avg(score),2) '平均成绩' -- round四舍五入函数 from student stu inner join sc on stu.sid = sc.sid group by stu.sid -- 根据sid进行分组,分组之后查询每个人的平均成绩 having avg(score) > 60; -- 先分组，后条件过滤mysql> select -> stu.sid '学生编号', -> stu.sname '学生姓名', -> round(avg(score),2) '平均成绩' -> from student stu -> inner join sc on stu.sid = sc.sid -> group by stu.sid -- 根据sid进行分组,分组之后查询每个人的平均成绩 -> having avg(score) > 60; -- 先分组，后条件过滤 +--------------+--------------+--------------+ | 学生编号 | 学生姓名 | 平均成绩 | +--------------+--------------+--------------+ | 01 | 赵雷 | 89.67 | | 02 | 钱电 | 70.00 | | 03 | 孙风 | 80.00 | | 05 | 周梅 | 81.50 | | 07 | 郑竹 | 93.50 | +--------------+--------------+--------------+ 5 rows in set (0.00 sec)4.3.1、总分超过200分的同学的学生编号和学生姓名和总成绩select stu.sid '学生编号', stu.sname '学生姓名', round(sum(score),2) '总成绩' from student stu inner join sc on stu.sid = sc.sid group by stu.sid having(sum(score)) > 200;mysql> select -> stu.sid '学生编号', -> stu.sname '学生姓名', -> round(sum(score),2) '总成绩' -> from student stu -> inner join sc on stu.sid = sc.sid -> group by stu.sid -> having(sum(score)) > 200; +--------------+--------------+-----------+ | 学生编号 | 学生姓名 | 总成绩 | +--------------+--------------+-----------+ | 01 | 赵雷 | 269.00 | | 02 | 钱电 | 210.00 | | 03 | 孙风 | 240.00 | +--------------+--------------+-----------+ 3 rows in set (0.00 sec)4.4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩select stu.sid '学生编号', stu.sname '学生姓名', round(avg(score)) '平均成绩' from student stu inner join sc on stu.sid = sc.sid group by stu.sid having avg(score) < 60;mysql> select -> stu.sid '学生编号', -> stu.sname '学生姓名', -> round(avg(score)) '平均成绩' -> from student stu -> inner join sc on stu.sid = sc.sid -> group by stu.sid -> having avg(score) < 60; +--------------+--------------+--------------+ | 学生编号 | 学生姓名 | 平均成绩 | +--------------+--------------+--------------+ | 04 | 李云 | 33 | | 06 | 吴兰 | 33 | +--------------+--------------+--------------+ 2 rows in set (0.00 sec)王菊成绩表中无数据select distinct sid from sc; -- 查询所有学号 select stu.sid '学生编号', stu.sname '学生姓名', 0 '平均成绩' from student stu where stu.sid not in (select distinct sid from sc); -- 学生的学号不在表中mysql> select -> stu.sid '学生编号', -> stu.sname '学生姓名', -> 0 '平均成绩' -> from student stu -> where stu.sid not in (select distinct sid from sc); -- 学生的学号不在表中 +--------------+--------------+--------------+ | 学生编号 | 学生姓名 | 平均成绩 | +--------------+--------------+--------------+ | 08 | 王菊 | 0 | +--------------+--------------+--------------+ 1 row in set (0.00 sec)select stu.sid '学生编号', stu.sname '学生姓名', round(avg(score)) '平均成绩' from student stu inner join sc on stu.sid = sc.sid group by stu.sid having avg(score) < 60 union -- 连接两张表 select stu.sid '学生编号', stu.sname '学生姓名', 0 '平均成绩' from student stu where stu.sid not in (select distinct sid from sc); -- 学生的学号不在表中mysql> select -> stu.sid '学生编号', -> stu.sname '学生姓名', -> round(avg(score)) '平均成绩' -> from student stu -> inner join sc on stu.sid = sc.sid -> group by stu.sid -> having avg(score) < 60 -> union -- 连接两张表 -> select -> stu.sid '学生编号', -> stu.sname '学生姓名', -> 0 '平均成绩' -> from student stu -> where stu.sid not in (select distinct sid from sc); -- 学生的学号不在表中 +--------------+--------------+--------------+ | 学生编号 | 学生姓名 | 平均成绩 | +--------------+--------------+--------------+ | 04 | 李云 | 33 | | 06 | 吴兰 | 33 | | 08 | 王菊 | 0 | +--------------+--------------+--------------+ 3 rows in set (0.00 sec)select stu.sid '学生编号', stu.sname '学生姓名', round(avg(ifnull(sc.score,0))) '平均成绩' from student stu inner join sc on stu.sid = sc.sid group by stu.sid having avg(sc.score) < 60;mysql> select -> stu.sid '学生编号', -> stu.sname '学生姓名', -> round(avg(ifnull(sc.score,0))) '平均成绩' -> from student stu -> inner join sc on stu.sid = sc.sid -> group by stu.sid -> having avg(sc.score) < 60; +--------------+--------------+--------------+ | 学生编号 | 学生姓名 | 平均成绩 | +--------------+--------------+--------------+ | 04 | 李云 | 33 | | 06 | 吴兰 | 33 | +--------------+--------------+--------------+ 2 rows in set (0.00 sec)4.5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩select stu.sid '学生编号', stu.sname '学生姓名', count(sc.sid) '选课总数', sum(sc.score) '总成绩' from student stu left join sc on stu.sid = sc.sid group by stu.sid;mysql> select -> stu.sid '学生编号', -> stu.sname '学生姓名', -> count(sc.sid) '选课总数', -> sum(sc.score) '总成绩' -> from student stu -> left join sc on stu.sid = sc.sid -> group by stu.sid; +--------------+--------------+--------------+-----------+ | 学生编号 | 学生姓名 | 选课总数 | 总成绩 | +--------------+--------------+--------------+-----------+ | 01 | 赵雷 | 3 | 269.0 | | 02 | 钱电 | 3 | 210.0 | | 03 | 孙风 | 3 | 240.0 | | 04 | 李云 | 3 | 100.0 | | 05 | 周梅 | 2 | 163.0 | | 06 | 吴兰 | 2 | 65.0 | | 07 | 郑竹 | 2 | 187.0 | | 08 | 王菊 | 0 | NULL | +--------------+--------------+--------------+-----------+ 8 rows in set (0.00 sec)4.6、查询"李"姓老师的数量select count(t.tname) from teacher t where t.tname like '李%'; -- 通配符mysql> select -> count(t.tname) -> from teacher t -> where t.tname like '李%'; -- 通配符 +----------------+ | count(t.tname) | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec)select t.tname, count(t.tname) from teacher t where left(t.tname,1) in ('李'); -- left函数mysql> select -> t.tname, -> count(t.tname) -> from teacher t -> where left(t.tname,1) in ('李'); -- left函数 +--------+----------------+ | tname | count(t.tname) | +--------+----------------+ | 李四 | 1 | +--------+----------------+ 1 row in set (0.00 sec)4.7、查询学过"张三"老师授课的同学的信息-- 查询课程cid select cid from teacher,course where teacher.tid = course.tid and teacher.tname = '张三'; select stu.* from student stu,sc where stu.sid = sc.sid and sc.cid in ( select cid from teacher,course where teacher.tid = course.tid and teacher.tname = '张三');mysql> select -> stu.* -> from student stu,sc -> where stu.sid = sc.sid -> and sc.cid in ( -> select cid -> from teacher,course -> where teacher.tid = course.tid and teacher.tname = '张三'); +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-05-20 00:00:00 | 男 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | | 05 | 周梅 | 1991-12-01 00:00:00 | 女 | | 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | +-----+--------+---------------------+------+ 6 rows in set (0.00 sec)select stu.* from teacher t left join course c on t.tid = c.tid -- 课程表和教师表 left join sc on c.cid = sc.cid -- 成绩表和课程表 left join student stu on sc.sid = stu.sid -- 学生表和成绩表 where t.tname = '张三';mysql> select stu.* -> from teacher t -> left join course c on t.tid = c.tid -- 课程表和教师表 -> left join sc on c.cid = sc.cid -- 成绩表和课程表 -> left join student stu on sc.sid = stu.sid -- 学生表和成绩表 -> where t.tname = '张三'; +------+--------+---------------------+------+ | sid | sname | sage | ssex | +------+--------+---------------------+------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-05-20 00:00:00 | 男 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | | 05 | 周梅 | 1991-12-01 00:00:00 | 女 | | 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | +------+--------+---------------------+------+ 6 rows in set (0.00 sec)4.8、查询没学过"张三"老师授课的同学的信息select distinct sc.sid from teacher t left join course c on t.tid = c.tid left join sc on c.cid = sc.cid where t.tname = '张三'; select stu.* from student stu where sid not in ( select distinct sc.sid from teacher t left join course c on t.tid = c.tid left join sc on c.cid = sc.cid where t.tname = '张三');mysql> select stu.* -> from student stu -> where sid not in ( -> select distinct sc.sid -> from teacher t -> left join course c on t.tid = c.tid -> left join sc on c.cid = sc.cid -> where t.tname = '张三'); +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | | 08 | 王菊 | 1990-01-20 00:00:00 | 女 | +-----+--------+---------------------+------+ 2 rows in set (0.00 sec)4.9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息select s1.sid from sc s1 inner join sc s2 on s1.sid = s2.sid where s1.cid = '01' and s2.cid = '02'; select stu.* from student stu where stu.sid in ( select s1.sid from sc s1 inner join sc s2 on s1.sid = s2.sid where s1.cid = '01' and s2.cid = '02');mysql> select stu.* -> from student stu -> where stu.sid in ( -> select s1.sid -> from sc s1 -> inner join sc s2 on s1.sid = s2.sid -> where s1.cid = '01' and s2.cid = '02'); +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-05-20 00:00:00 | 男 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | | 05 | 周梅 | 1991-12-01 00:00:00 | 女 | +-----+--------+---------------------+------+ 5 rows in set (0.00 sec)select stu.* from student stu,sc s1,sc s2 where s1.sid = s2.sid and stu.sid = s1.sid and s1.cid = '01' and s2.cid = '02';mysql> select stu.* -> from student stu,sc s1,sc s2 -> where s1.sid = s2.sid -> and stu.sid = s1.sid -> and s1.cid = '01' -> and s2.cid = '02'; +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-05-20 00:00:00 | 男 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | | 05 | 周梅 | 1991-12-01 00:00:00 | 女 | +-----+--------+---------------------+------+ 5 rows in set (0.00 sec)4.10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息-- 查询学过02课程的学生sid select sid from sc where sc.cid = '02'; select sid from sc where sc.cid = '01' -- 修过01课程的学号 and sc.sid not in (select sid from sc where sc.cid = '02'); -- 同时学号不能在修过02课程中出现 select stu.* from student stu where stu.sid in( select sid from sc where sc.cid = '01' and sc.sid not in (select sid from sc where sc.cid = '02') );mysql> select stu.* -> from student stu -> where stu.sid in( -> select sid -> from sc -> where sc.cid = '01' -> and sc.sid not in (select sid from sc where sc.cid = '02') -> ); +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | +-----+--------+---------------------+------+ 1 row in set (0.00 sec)4.11、查询没有学全所有课程的同学的信息select stu.* from student stu left join sc on stu.sid = sc.sid group by stu.sid -- 学号分组 having count(sc.cid) < (select count(*) from course); -- 筛选分组后学生的课程数<3mysql> select stu.* -> from student stu -> left join sc on stu.sid = sc.sid -> group by stu.sid -- 学号分组 -> having count(sc.cid) < (select count(*) from course); -- 筛选分组后学生的课程数<3 +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 05 | 周梅 | 1991-12-01 00:00:00 | 女 | | 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | | 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | | 08 | 王菊 | 1990-01-20 00:00:00 | 女 | +-----+--------+---------------------+------+ 4 rows in set (0.00 sec)select sc.sid from sc group by sc.sid having count(sc.cid) = (select count(*) from course);mysql> select sc.sid -> from sc -> group by sc.sid -> having count(sc.cid) = (select count(*) from course); +-----+ | sid | +-----+ | 01 | | 02 | | 03 | | 04 | +-----+ 4 rows in set (0.00 sec)select stu.* from student stu where stu.sid not in ( select sc.sid from sc group by sc.sid having count(sc.cid) = (select count(*) from course));mysql> select stu.* -> from student stu -> where stu.sid not in ( -> select sc.sid -> from sc -> group by sc.sid -> having count(sc.cid) = (select count(*) from course)); +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 05 | 周梅 | 1991-12-01 00:00:00 | 女 | | 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | | 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | | 08 | 王菊 | 1990-01-20 00:00:00 | 女 | +-----+--------+---------------------+------+ 4 rows in set (0.00 sec)4.12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息-- 查询01号同学的所学课程cid select sc.cid from sc where sc.sid = '01'; select stu.* from student stu inner join sc on stu.sid = sc.sid -- 学生表和成绩表的关联 and sc.cid in (select sc.cid from sc where sc.sid = '01') -- 对课程进行限制，只在01学生的课程内 group by stu.sid; -- 根据学号进行分组mysql> select stu.* -> from student stu -> inner join sc on stu.sid = sc.sid -- 学生表和成绩表的关联 -> and sc.cid in (select sc.cid from sc where sc.sid = '01') -- 对课程进行限制，只在01学生的课程内 -> group by stu.sid; -- 根据学号进行分组 +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-05-20 00:00:00 | 男 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | | 05 | 周梅 | 1991-12-01 00:00:00 | 女 | | 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | | 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | +-----+--------+---------------------+------+ 7 rows in set (0.00 sec)4.13.0、查询和"01"号的同学学习的课程完全相同的其他同学的信息-- 查询01号同学学习的课程cid select sc.cid from sc where sc.sid = '01'; -- 查询01号同学学习的课程cid总数 select count(sc.cid) from sc where sc.sid = '01'; select stu.* from student stu inner join sc on stu.sid = sc.sid and sc.cid in (select sc.cid from sc where sc.sid = '01') -- 和01同学课程相同 and sc.sid != '01' -- 排除自身 group by stu.sid -- 根据sid进行分组 having (select count(sc.cid) from sc where sc.sid = stu.sid) = -- 和01同学所学课程总数相同 (select count(sc.cid) from sc where sc.sid = '01');mysql> select stu.* -> from student stu -> inner join sc on stu.sid = sc.sid -> and sc.cid in (select sc.cid from sc where sc.sid = '01') -- 和01同学课程相同 -> and sc.sid != '01' -- 排除自身 -> group by stu.sid -- 根据sid进行分组 -> having (select count(sc.cid) from sc where sc.sid = stu.sid) = -- 和01同学所学课程总数相同 -> (select count(sc.cid) from sc where sc.sid = '01'); +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-05-20 00:00:00 | 男 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | +-----+--------+---------------------+------+ 3 rows in set (0.00 sec)4.13.1、MySQL中，聚合函数GROUP_CONCAT()用指定分隔符连接字符串-- 语法 group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])select sid,group_concat(cid order by cid) from sc group by sid;mysql> select sid,group_concat(cid order by cid) -> from sc -> group by sid; +-----+--------------------------------+ | sid | group_concat(cid order by cid) | +-----+--------------------------------+ | 01 | 01,02,03 | | 02 | 01,02,03 | | 03 | 01,02,03 | | 04 | 01,02,03 | | 05 | 01,02 | | 06 | 01,03 | | 07 | 02,03 | +-----+--------------------------------+ 7 rows in set (0.00 sec)select sid from sc group by sid having group_concat(cid order by cid) = ( -- 找出和01号学生分组合并结果相同的学号sid select group_concat(cid order by cid) -- 01号学生分组合并，同时进行排序 from sc group by sid having sid = '01') and sid != '01'; -- 排除01号同学mysql> select sid -> from sc -> group by sid -> having group_concat(cid order by cid) = ( -> select group_concat(cid order by cid) -> from sc -> group by sid -> having sid = '01') -> and sid != '01'; +-----+ | sid | +-----+ | 02 | | 03 | | 04 | +-----+ 3 rows in set (0.00 sec)4.14、查询没学过"张三"老师讲授的任一门课程的学生姓名-- 查询张三老师的课程cid select cid from course s inner join teacher t on s.tid = t.tid and t.tname = '张三'; -- 查询学过张三老师课程的学生sid select sid from sc where cid in ( select cid from course s inner join teacher t on s.tid = t.tid and t.tname = '张三'); select stu.* from student stu where stu.sid not in ( select sid from sc where cid in ( select cid from course s inner join teacher t on s.tid = t.tid and t.tname = '张三') );mysql> select stu.* -> from student stu -> where stu.sid not in ( -> select sid from sc where cid in ( -> select cid from course s inner join teacher t on s.tid = t.tid and t.tname = '张三') -> ); +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | | 08 | 王菊 | 1990-01-20 00:00:00 | 女 | +-----+--------+---------------------+------+ 2 rows in set (0.00 sec)4.15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩-- 查询成绩不合格同学的学号 select sid from sc where score < 60 -- 小于60分，不及格 group by sid having count(*) >= 2; -- 不及格两门以上 select stu.sname,stu.sid,round(avg(score),2) from student stu left join sc on stu.sid = sc.sid where stu.sid in ( select sid from sc where score < 60 group by sid having count(*) >= 2) group by stu.sid;mysql> select stu.sname,stu.sid,round(avg(score),2) -> from student stu -> left join sc on stu.sid = sc.sid -> where stu.sid in ( -> select sid -> from sc -> where score < 60 -> group by sid -> having count(*) >= 2) -> group by stu.sid; +--------+-----+---------------------+ | sname | sid | round(avg(score),2) | +--------+-----+---------------------+ | 李云 | 04 | 33.33 | | 吴兰 | 06 | 32.50 | +--------+-----+---------------------+ 2 rows in set (0.00 sec)select stu.sname,sc.sid,round(avg(score),2) from sc inner join student stu on sc.sid = stu.sid where sc.score < 60 group by sc.sid having count(sc.score) >= 2;mysql> select stu.sname,sc.sid,round(avg(score),2) -> from sc -> inner join student stu on sc.sid = stu.sid -> where sc.score < 60 -> group by sc.sid -> having count(sc.score) >= 2; +--------+-----+---------------------+ | sname | sid | round(avg(score),2) | +--------+-----+---------------------+ | 李云 | 04 | 33.33 | | 吴兰 | 06 | 32.50 | +--------+-----+---------------------+ 2 rows in set (0.00 sec)select stu.sid,stu.sname,round(avg(score),2) from student stu inner join sc on stu.sid = sc.sid and sc.score < 60 -- 内连接且分数小于60 group by stu.sid -- 根据sid分组 having count(sc.sid) >= 2; -- 两门课mysql> select stu.sid,stu.sname,round(avg(score),2) -> from student stu -> inner join sc on stu.sid = sc.sid and sc.score < 60 -- 内连接且分数小于60 -> group by stu.sid -- 根据sid分组 -> having count(sc.sid) >= 2; -- 两门课 +-----+--------+---------------------+ | sid | sname | round(avg(score),2) | +-----+--------+---------------------+ | 04 | 李云 | 33.33 | | 06 | 吴兰 | 32.50 | +-----+--------+---------------------+ 2 rows in set (0.00 sec)4.16、检索"01"课程分数小于60，按分数降序排列的学生信息select stu.*,sc.score from student stu left join sc on stu.sid = sc.sid where sc.cid = '01' and sc.score < 60 order by score desc;mysql> select stu.*,sc.score -> from student stu -> left join sc on stu.sid = sc.sid -> where sc.cid = '01' and sc.score < 60 -> order by score desc; +-----+--------+---------------------+------+-------+ | sid | sname | sage | ssex | score | +-----+--------+---------------------+------+-------+ | 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | | 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 31.0 | +-----+--------+---------------------+------+-------+ 2 rows in set (0.01 sec)4.17.1、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩-- 查询每位同学平均成绩，并降序 select sc.sid,avg(score) from sc group by sc.sid order by avg(score) desc; select s.sid, (select sname from student where student.sid = s.sid) '姓名', (select score from sc where sc.sid = s.sid and sc.cid = '01') '语文', (select score from sc where sc.sid = s.sid and sc.cid = '02') '数学', (select score from sc where sc.sid = s.sid and sc.cid = '03') '英语', round(avg(s.score),2) '平均成绩' from sc s group by s.sid order by avg(s.score) desc;mysql> select -> s.sid, -> (select sname from student where student.sid = s.sid) '姓名', -> (select score from sc where sc.sid = s.sid and sc.cid = '01') '语文', -> (select score from sc where sc.sid = s.sid and sc.cid = '02') '数学', -> (select score from sc where sc.sid = s.sid and sc.cid = '03') '英语', -> round(avg(s.score),2) '平均成绩' -> from sc s -> group by s.sid -> order by avg(s.score) desc; +-----+--------+--------+--------+--------+--------------+ | sid | 姓名 | 语文 | 数学 | 英语 | 平均成绩 | +-----+--------+--------+--------+--------+--------------+ | 07 | 郑竹 | NULL | 89.0 | 98.0 | 93.50 | | 01 | 赵雷 | 80.0 | 90.0 | 99.0 | 89.67 | | 05 | 周梅 | 76.0 | 87.0 | NULL | 81.50 | | 03 | 孙风 | 80.0 | 80.0 | 80.0 | 80.00 | | 02 | 钱电 | 70.0 | 60.0 | 80.0 | 70.00 | | 04 | 李云 | 50.0 | 30.0 | 20.0 | 33.33 | | 06 | 吴兰 | 31.0 | NULL | 34.0 | 32.50 | +-----+--------+--------+--------+--------+--------------+ 7 rows in set (0.00 sec)没有第八号学生4.17.2、CASE WHEN函数CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END -- THEN后边的值与ELSE后边的值类型应一致，否则会报错 -- 使用 IF 函数进行替换 IF(SCORE = 'A', '优', '不及格')-- CASE WHEN条件表达式函数 CASE WHEN condition THEN result [WHEN...THEN...] ELSE result ENDCASE WHEN SCORE > 90 THEN '优' WHEN SCORE > 70 THEN '良' WHEN SCORE > 60 THEN '中' ELSE '不及格' END4.17.3、CASE WHEN函数使用select stu.sname, sc.sid, sum(case when sc.cid = '01' then ifnull(sc.score,0) else 0 end) '语文', sum(case when sc.cid = '02' then ifnull(sc.score,0) else 0 end) '数学', sum(case when sc.cid = '03' then ifnull(sc.score,0) else 0 end) '英语', round(avg(sc.score),2) '平均成绩' from sc left join student stu on sc.sid = stu.sid group by sc.sid union select stu.sname,stu.sid,0 '语文',0 '数学',0 '英语',0 '平均成绩' from student stu where stu.sid not in (select distinct sid from sc) order by 6 desc;mysql> select -> stu.sname, -> sc.sid, -> sum(case when sc.cid = '01' then ifnull(sc.score,0) else 0 end) '语文', -> sum(case when sc.cid = '02' then ifnull(sc.score,0) else 0 end) '数学', -> sum(case when sc.cid = '03' then ifnull(sc.score,0) else 0 end) '英语', -> round(avg(sc.score),2) '平均成绩' -> from sc -> left join student stu on sc.sid = stu.sid -> group by sc.sid -> union -> select stu.sname,stu.sid,0 '语文',0 '数学',0 '英语',0 '平均成绩' -> from student stu -> where stu.sid not in (select distinct sid from sc) -> order by 6 desc; +--------+-----+--------+--------+--------+--------------+ | sname | sid | 语文 | 数学 | 英语 | 平均成绩 | +--------+-----+--------+--------+--------+--------------+ | 郑竹 | 07 | 0.0 | 89.0 | 98.0 | 93.50 | | 赵雷 | 01 | 80.0 | 90.0 | 99.0 | 89.67 | | 周梅 | 05 | 76.0 | 87.0 | 0.0 | 81.50 | | 孙风 | 03 | 80.0 | 80.0 | 80.0 | 80.00 | | 钱电 | 02 | 70.0 | 60.0 | 80.0 | 70.00 | | 李云 | 04 | 50.0 | 30.0 | 20.0 | 33.33 | | 吴兰 | 06 | 31.0 | 0.0 | 34.0 | 32.50 | | 王菊 | 08 | 0.0 | 0.0 | 0.0 | 0.00 | +--------+-----+--------+--------+--------+--------------+ 8 rows in set (0.00 sec)有第八号学生select stu.sname, stu.sid, s1.score '语文', s2.score '数学', s3.score '英语', round(avg(s.score),2) '平均成绩' from student stu left join sc s on stu.sid = s.sid left join sc s1 on s.sid = s1.sid and s1.cid = '01' left join sc s2 on s.sid = s2.sid and s2.cid = '02' left join sc s3 on s.sid = s3.sid and s3.cid = '03' group by stu.sid order by 6 desc;mysql> select -> stu.sname, -> stu.sid, -> s1.score '语文', -> s2.score '数学', -> s3.score '英语', -> round(avg(s.score),2) '平均成绩' -> from student stu -> left join sc s on stu.sid = s.sid -> left join sc s1 on s.sid = s1.sid and s1.cid = '01' -> left join sc s2 on s.sid = s2.sid and s2.cid = '02' -> left join sc s3 on s.sid = s3.sid and s3.cid = '03' -> group by stu.sid -> order by 6 desc; +--------+-----+--------+--------+--------+--------------+ | sname | sid | 语文 | 数学 | 英语 | 平均成绩 | +--------+-----+--------+--------+--------+--------------+ | 郑竹 | 07 | NULL | 89.0 | 98.0 | 93.50 | | 赵雷 | 01 | 80.0 | 90.0 | 99.0 | 89.67 | | 周梅 | 05 | 76.0 | 87.0 | NULL | 81.50 | | 孙风 | 03 | 80.0 | 80.0 | 80.0 | 80.00 | | 钱电 | 02 | 70.0 | 60.0 | 80.0 | 70.00 | | 李云 | 04 | 50.0 | 30.0 | 20.0 | 33.33 | | 吴兰 | 06 | 31.0 | NULL | 34.0 | 32.50 | | 王菊 | 08 | NULL | NULL | NULL | NULL | +--------+-----+--------+--------+--------+--------------+ 8 rows in set (0.00 sec)-- 非空判断 select stu.sname, stu.sid, if(s1.score is NULL,0,s1.score) '语文', if(s2.score is NULL,0,s2.score) '数学', if(s3.score is NULL,0,s3.score) '英语', if(round(avg(s.score),2) is NULL,0,round(avg(s.score),2)) '平均成绩' from student stu left join sc s on stu.sid = s.sid left join sc s1 on s.sid = s1.sid and s1.cid = '01' left join sc s2 on s.sid = s2.sid and s2.cid = '02' left join sc s3 on s.sid = s3.sid and s3.cid = '03' group by stu.sid order by 6 desc;mysql> select -> stu.sname, -> stu.sid, -> if(s1.score is NULL,0,s1.score) '语文', -> if(s2.score is NULL,0,s2.score) '数学', -> if(s3.score is NULL,0,s3.score) '英语', -> if(round(avg(s.score),2) is NULL,0,round(avg(s.score),2)) '平均成绩' -> from student stu -> left join sc s on stu.sid = s.sid -> left join sc s1 on s.sid = s1.sid and s1.cid = '01' -> left join sc s2 on s.sid = s2.sid and s2.cid = '02' -> left join sc s3 on s.sid = s3.sid and s3.cid = '03' -> group by stu.sid -> order by 6 desc; +--------+-----+--------+--------+--------+--------------+ | sname | sid | 语文 | 数学 | 英语 | 平均成绩 | +--------+-----+--------+--------+--------+--------------+ | 郑竹 | 07 | 0.0 | 89.0 | 98.0 | 93.50 | | 赵雷 | 01 | 80.0 | 90.0 | 99.0 | 89.67 | | 周梅 | 05 | 76.0 | 87.0 | 0.0 | 81.50 | | 孙风 | 03 | 80.0 | 80.0 | 80.0 | 80.00 | | 钱电 | 02 | 70.0 | 60.0 | 80.0 | 70.00 | | 李云 | 04 | 50.0 | 30.0 | 20.0 | 33.33 | | 吴兰 | 06 | 31.0 | 0.0 | 34.0 | 32.50 | | 王菊 | 08 | 0.0 | 0.0 | 0.0 | 0 | +--------+-----+--------+--------+--------+--------------+ 8 rows in set (0.00 sec)4.18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格：>=60，中等为：70-80，优良为：80-90，优秀为：>=90case when score then 1 else 0 end -- 总人数 case when score >= 60 then 1 else 0 end -- 及格 case when score > 70 and score <= 80 then 1 else 0 end -- 中等 case when score > 80 and score < 90 then 1 else 0 end -- 优良 case when score >= 90 then 1 else 0 end -- 优秀select sc.cid 课程ID, c.cname 课程name, max(sc.score) 最高分, min(sc.score) 最低分, round(avg(sc.score),2) 平均分, round(100*(sum(case when score >= 60 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 及格率, round(100*(sum(case when score > 70 and score <= 80 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 中等率, round(100*(sum(case when score > 80 and score < 90 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 优良率, round(100*(sum(case when score >= 90 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 优秀率 from sc left join course c on sc.cid = c.cid group by sc.cid,c.cname;mysql> select -> sc.cid 课程ID, -> c.cname 课程name, -> max(sc.score) 最高分, -> min(sc.score) 最低分, -> round(avg(sc.score),2) 平均分, -> round(100*(sum(case when score >= 60 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 及格率, -> round(100*(sum(case when score > 70 and score <= 80 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 中等率, -> round(100*(sum(case when score > 80 and score < 90 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 优良率, -> round(100*(sum(case when score >= 90 then 1 else 0 end) / sum(case when score then 1 else 0 end)),2) 优秀率 -> from sc -> left join course c on sc.cid = c.cid -> group by sc.cid,c.cname; +----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | 课程ID | 课程name | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 | +----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | 01 | 语文 | 80.0 | 31.0 | 64.50 | 66.67 | 50.00 | 0.00 | 0.00 | | 02 | 数学 | 90.0 | 30.0 | 72.67 | 83.33 | 16.67 | 33.33 | 16.67 | | 03 | 英语 | 99.0 | 20.0 | 68.50 | 66.67 | 33.33 | 0.00 | 33.33 | +----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ 3 rows in set (0.00 sec)count(1) 替换 sum(case when score then 1 else 0 end) 总人数select sc.cid 课程ID, c.cname 课程name, max(sc.score) 最高分, min(sc.score) 最低分, round(avg(sc.score),2) 平均分, round(100*(sum(case when score >= 60 then 1 else 0 end) / count(1)),2) 及格率, round(100*(sum(case when score > 70 and score <= 80 then 1 else 0 end) / count(1)),2) 中等率, round(100*(sum(case when score > 80 and score < 90 then 1 else 0 end) / count(1)),2) 优良率, round(100*(sum(case when score >= 90 then 1 else 0 end) / count(1)),2) 优秀率 from sc left join course c on sc.cid = c.cid group by sc.cid,c.cname;mysql> select -> sc.cid 课程ID, -> c.cname 课程name, -> max(sc.score) 最高分, -> min(sc.score) 最低分, -> round(avg(sc.score),2) 平均分, -> round(100*(sum(case when score >= 60 then 1 else 0 end) / count(1)),2) 及格率, -> round(100*(sum(case when score > 70 and score <= 80 then 1 else 0 end) / count(1)),2) 中等率, -> round(100*(sum(case when score > 80 and score < 90 then 1 else 0 end) / count(1)),2) 优良率, -> round(100*(sum(case when score >= 90 then 1 else 0 end) / count(1)),2) 优秀率 -> from sc -> left join course c on sc.cid = c.cid -> group by sc.cid,c.cname; +----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | 课程ID | 课程name | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 | +----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | 01 | 语文 | 80.0 | 31.0 | 64.50 | 66.67 | 50.00 | 0.00 | 0.00 | | 02 | 数学 | 90.0 | 30.0 | 72.67 | 83.33 | 16.67 | 33.33 | 16.67 | | 03 | 英语 | 99.0 | 20.0 | 68.50 | 66.67 | 33.33 | 0.00 | 33.33 | +----------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ 3 rows in set (0.00 sec)4.19、按各科成绩进行排序，并显示排名select s1.sid, s1.cid, s1.score, (select count(distinct score) from sc where cid = s1.cid and score >s1.score)+1 AS 排名 from sc s1 order by s1.cid,4;mysql> select -> s1.sid, -> s1.cid, -> s1.score, -> (select count(distinct score) from sc where cid = s1.cid and score >s1.score)+1 AS 排名 -> from sc s1 -> order by s1.cid,4; +-----+-----+-------+--------+ | sid | cid | score | 排名 | +-----+-----+-------+--------+ | 01 | 01 | 80.0 | 1 | | 03 | 01 | 80.0 | 1 | | 05 | 01 | 76.0 | 2 | | 02 | 01 | 70.0 | 3 | | 04 | 01 | 50.0 | 4 | | 06 | 01 | 31.0 | 5 | | 01 | 02 | 90.0 | 1 | | 07 | 02 | 89.0 | 2 | | 05 | 02 | 87.0 | 3 | | 03 | 02 | 80.0 | 4 | | 02 | 02 | 60.0 | 5 | | 04 | 02 | 30.0 | 6 | | 01 | 03 | 99.0 | 1 | | 07 | 03 | 98.0 | 2 | | 02 | 03 | 80.0 | 3 | | 03 | 03 | 80.0 | 3 | | 06 | 03 | 34.0 | 4 | | 04 | 03 | 20.0 | 5 | +-----+-----+-------+--------+ 18 rows in set (0.00 sec)select stu.sname '学生姓名', c.cname '课程名', s1.score '分数', (select count(distinct score) from sc where cid = s1.cid and score >s1.score)+1 AS 排名 from sc s1 join student stu on s1.sid = stu.sid join course c on s1.cid = c.cid order by s1.cid,4;mysql> select -> stu.sname '学生姓名', -> c.cname '课程名', -> s1.score '分数', -> (select count(distinct score) from sc where cid = s1.cid and score >s1.score)+1 AS 排名 -> from sc s1 -> join student stu on s1.sid = stu.sid -> join course c on s1.cid = c.cid -> order by s1.cid,4; +--------------+-----------+--------+--------+ | 学生姓名 | 课程名 | 分数 | 排名 | +--------------+-----------+--------+--------+ | 赵雷 | 语文 | 80.0 | 1 | | 孙风 | 语文 | 80.0 | 1 | | 周梅 | 语文 | 76.0 | 2 | | 钱电 | 语文 | 70.0 | 3 | | 李云 | 语文 | 50.0 | 4 | | 吴兰 | 语文 | 31.0 | 5 | | 赵雷 | 数学 | 90.0 | 1 | | 郑竹 | 数学 | 89.0 | 2 | | 周梅 | 数学 | 87.0 | 3 | | 孙风 | 数学 | 80.0 | 4 | | 钱电 | 数学 | 60.0 | 5 | | 李云 | 数学 | 30.0 | 6 | | 赵雷 | 英语 | 99.0 | 1 | | 郑竹 | 英语 | 98.0 | 2 | | 孙风 | 英语 | 80.0 | 3 | | 钱电 | 英语 | 80.0 | 3 | | 吴兰 | 英语 | 34.0 | 4 | | 李云 | 英语 | 20.0 | 5 | +--------------+-----------+--------+--------+ 18 rows in set (0.01 sec)4.20、查询学生的总成绩并进行排名select stu.sname '学生姓名', sum(score) '总成绩' from sc inner join student stu on sc.sid = stu.sid group by sc.sid order by 2 asc;mysql> select -> stu.sname '学生姓名', -> sum(score) '总成绩' -> from sc -> inner join student stu on sc.sid = stu.sid -> group by sc.sid -> order by 2 asc; +--------------+-----------+ | 学生姓名 | 总成绩 | +--------------+-----------+ | 吴兰 | 65.0 | | 李云 | 100.0 | | 周梅 | 163.0 | | 郑竹 | 187.0 | | 钱电 | 210.0 | | 孙风 | 240.0 | | 赵雷 | 269.0 | +--------------+-----------+ 7 rows in set (0.00 sec)select sid,sum(score) sscore from sc group by sid; select stu.sname '学生姓名', t1.sscore '总成绩', (select count(distinct t2.sscore) from (select sid,sum(score) sscore from sc group by sid) t2 where t2.sscore > t1.sscore )+1 '排名' from student stu,(select sid,sum(score) sscore from sc group by sid) t1 where stu.sid = t1.sid order by 3;mysql> select -> stu.sname '学生姓名', -> t1.sscore '总成绩', -> (select count(distinct t2.sscore) -> from (select sid,sum(score) sscore from sc group by sid) t2 -> where t2.sscore > t1.sscore -> )+1 '排名' -> from student stu,(select sid,sum(score) sscore from sc group by sid) t1 -> where stu.sid = t1.sid -> order by 3; +--------------+-----------+--------+ | 学生姓名 | 总成绩 | 排名 | +--------------+-----------+--------+ | 赵雷 | 269.0 | 1 | | 孙风 | 240.0 | 2 | | 钱电 | 210.0 | 3 | | 郑竹 | 187.0 | 4 | | 周梅 | 163.0 | 5 | | 李云 | 100.0 | 6 | | 吴兰 | 65.0 | 7 | +--------------+-----------+--------+ 7 rows in set (0.00 sec)4.21、查询不同老师所教不同课程平均分从高到低显示select c.cid,c.cname,t.tname from course c join teacher t on c.tid = t.tid;mysql> select c.cid,c.cname,t.tname -> from course c -> join teacher t on c.tid = t.tid; +-----+--------+--------+ | cid | cname | tname | +-----+--------+--------+ | 02 | 数学 | 张三 | | 01 | 语文 | 李四 | | 03 | 英语 | 王五 | +-----+--------+--------+ 3 rows in set (0.00 sec)select cid,avg(score) from sc group by cid;mysql> select cid,avg(score) from sc group by cid; +-----+------------+ | cid | avg(score) | +-----+------------+ | 01 | 64.50000 | | 02 | 72.66667 | | 03 | 68.50000 | +-----+------------+ 3 rows in set (0.00 sec)select c.cid '课程ID',c.cname '课程名',t.tname '教师名',score.avgscore '课程均分' from course c join teacher t on c.tid = t.tid join (select cid,avg(score) avgscore from sc group by cid) score on c.cid = score.cid order by 4 desc;mysql> select c.cid '课程ID',c.cname '课程名',t.tname '教师名',score.avgscore '课程均分' -> from course c -> join teacher t on c.tid = t.tid -> join (select cid,avg(score) avgscore from sc group by cid) score on c.cid = score.cid -> order by 4 desc; +----------+-----------+-----------+--------------+ | 课程ID | 课程名 | 教师名 | 课程均分 | +----------+-----------+-----------+--------------+ | 02 | 数学 | 张三 | 72.66667 | | 03 | 英语 | 王五 | 68.50000 | | 01 | 语文 | 李四 | 64.50000 | +----------+-----------+-----------+--------------+ 3 rows in set (0.00 sec)select c.cid '课程ID',c.cname '课程名',t.tname '教师名',round(avg(score),2) '课程均分' from course c left join teacher t on c.tid = t.tid left join sc on c.cid = sc.cid group by c.cid order by 4 desc;mysql> select c.cid '课程ID',c.cname '课程名',t.tname '教师名',round(avg(score),2) '课程均分' -> from course c -> left join teacher t on c.tid = t.tid -> left join sc on c.cid = sc.cid -> group by c.cid -> order by 4 desc; +----------+-----------+-----------+--------------+ | 课程ID | 课程名 | 教师名 | 课程均分 | +----------+-----------+-----------+--------------+ | 02 | 数学 | 张三 | 72.67 | | 03 | 英语 | 王五 | 68.50 | | 01 | 语文 | 李四 | 64.50 | +----------+-----------+-----------+--------------+ 3 rows in set (0.00 sec)4.22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩SELECT a.* , (SELECT COUNT(DISTINCT score) FROM SC WHERE CID = a.CID AND score > a.score) + 1 AS 排名 FROM SC AS a HAVING 排名 BETWEEN 2 AND 3 ORDER BY a.CID,排名;select s1.*, (select count(distinct score) from sc where cid = s1.cid and score > s1.score)+1 '排名' from sc s1;mysql> select -> s1.*, -> (select count(distinct score) from sc where cid = s1.cid and score > s1.score)+1 '排名' -> from sc s1; +-----+-----+-------+--------+ | sid | cid | score | 排名 | +-----+-----+-------+--------+ | 01 | 01 | 80.0 | 1 | | 01 | 02 | 90.0 | 1 | | 01 | 03 | 99.0 | 1 | | 02 | 01 | 70.0 | 3 | | 02 | 02 | 60.0 | 5 | | 02 | 03 | 80.0 | 3 | | 03 | 01 | 80.0 | 1 | | 03 | 02 | 80.0 | 4 | | 03 | 03 | 80.0 | 3 | | 04 | 01 | 50.0 | 4 | | 04 | 02 | 30.0 | 6 | | 04 | 03 | 20.0 | 5 | | 05 | 01 | 76.0 | 2 | | 05 | 02 | 87.0 | 3 | | 06 | 01 | 31.0 | 5 | | 06 | 03 | 34.0 | 4 | | 07 | 02 | 89.0 | 2 | | 07 | 03 | 98.0 | 2 | +-----+-----+-------+--------+ 18 rows in set (0.00 sec)select stu.sname '学生名字', c.cname '课程名', s1.score '分数', (select count(distinct score) from sc where cid = s1.cid and score > s1.score)+1 rank from sc s1 inner join student stu on s1.sid = stu.sid inner join course c on s1.cid = c.cid having rank between 2 and 3 order by 4 desc;mysql> select -> stu.sname '学生名字', -> c.cname '课程名', -> s1.score '分数', -> (select count(distinct score) from sc where cid = s1.cid and score > s1.score)+1 rank -> from sc s1 -> inner join student stu on s1.sid = stu.sid -> inner join course c on s1.cid = c.cid -> having rank between 2 and 3 -> order by 4 desc; +--------------+-----------+--------+------+ | 学生名字 | 课程名 | 分数 | rank | +--------------+-----------+--------+------+ | 钱电 | 英语 | 80.0 | 3 | | 钱电 | 语文 | 70.0 | 3 | | 孙风 | 英语 | 80.0 | 3 | | 周梅 | 数学 | 87.0 | 3 | | 郑竹 | 数学 | 89.0 | 2 | | 周梅 | 语文 | 76.0 | 2 | | 郑竹 | 英语 | 98.0 | 2 | +--------------+-----------+--------+------+ 7 rows in set (0.01 sec)4.23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比select sc.cid '课程编号', sum(case when score >= 0 and score < 60 then 1 else 0 end) '0-60人数', round(100 * (sum(case when score >= 0 and score < 60 then 1 else 0 end) / count(*) ),2) '0-60占比' from sc group by sc.cid; -- 分课程统计mysql> select -> sc.cid '课程编号', -> sum(case when score >= 0 and score < 60 then 1 else 0 end) '0-60人数', -> round(100 * (sum(case when score >= 0 and score < 60 then 1 else 0 end) / count(*) ),2) '0-60占比' -> from sc -> group by sc.cid; -- 分课程统计 +--------------+------------+------------+ | 课程编号 | 0-60人数 | 0-60占比 | +--------------+------------+------------+ | 01 | 2 | 33.33 | | 02 | 1 | 16.67 | | 03 | 2 | 33.33 | +--------------+------------+------------+ 3 rows in set (0.00 sec)select sc.cid '课程编号', c.cname '科目', count(*) '课程人数', sum(case when score >= 85 then 1 else 0 end) '85-100人数', round(100 * (sum(case when score > 85 then 1 else 0 end) / count(*) ),2) '85-100占比', sum(case when score < 85 and score >= 70 then 1 else 0 end) '70-85人数', round(100 * (sum(case when score < 85 and score >= 70 then 1 else 0 end) / count(*) ),2) '70-85占比', sum(case when score < 70 and score >= 60 then 1 else 0 end) '60-70人数', round(100 * (sum(case when score < 70 and score >= 60 then 1 else 0 end) / count(*) ),2) '60-70占比', sum(case when score >= 0 and score < 60 then 1 else 0 end) '0-60人数', round(100 * (sum(case when score >= 0 and score < 60 then 1 else 0 end) / count(*) ),2) '0-60占比' from sc left join course c on sc.cid = c.cid group by sc.cid -- 分课程统计 order by sc.cid;mysql> select -> sc.cid '课程编号', -> c.cname '科目', -> count(*) '课程人数', -> sum(case when score >= 85 then 1 else 0 end) '85-100人数', -> round(100 * (sum(case when score > 85 then 1 else 0 end) / count(*) ),2) '85-100占比', -> sum(case when score < 85 and score >= 70 then 1 else 0 end) '70-85人数', -> round(100 * (sum(case when score < 85 and score >= 70 then 1 else 0 end) / count(*) ),2) '70-85占比', -> sum(case when score < 70 and score >= 60 then 1 else 0 end) '60-70人数', -> round(100 * (sum(case when score < 70 and score >= 60 then 1 else 0 end) / count(*) ),2) '60-70占比', -> sum(case when score >= 0 and score < 60 then 1 else 0 end) '0-60人数', -> round(100 * (sum(case when score >= 0 and score < 60 then 1 else 0 end) / count(*) ),2) '0-60占比' -> from sc -> left join course c on sc.cid = c.cid -> group by sc.cid -- 分课程统计 -> order by sc.cid;+--------------+--------+--------------+--------------+--------------+-------------+-------------+-------------+-------------+------------+------------+ | 课程编号 | 科目 | 课程人数 | 85-100人数 | 85-100占比 | 70-85人数 | 70-85占比 | 60-70人数 | 60-70占比 | 0-60人数 | 0-60占比 | +--------------+--------+--------------+--------------+--------------+-------------+-------------+-------------+-------------+------------+------------+ | 01 | 语文 | 6 | 0 | 0.00 | 4 | 66.67 | 0 | 0.00 | 2 | 33.33 | | 02 | 数学 | 6 | 3 | 50.00 | 1 | 16.67 | 1 | 16.67 | 1 | 16.67 | | 03 | 英语 | 6 | 2 | 33.33 | 2 | 33.33 | 0 | 0.00 | 2 | 33.33 | +--------------+--------+--------------+--------------+--------------+-------------+-------------+-------------+-------------+------------+------------+ 3 rows in set (0.01 sec)4.24、查询学生平均成绩及其名次select sc.sid, avg(score) '平均成绩' from sc group by sc.sidmysql> select -> sc.sid, -> avg(score) '平均成绩' -> from sc -> group by sc.sid; +-----+--------------+ | sid | 平均成绩 | +-----+--------------+ | 01 | 89.66667 | | 02 | 70.00000 | | 03 | 80.00000 | | 04 | 33.33333 | | 05 | 81.50000 | | 06 | 32.50000 | | 07 | 93.50000 | +-----+--------------+ 7 rows in set (0.00 sec)-- 查询平均成绩并去重 select distinct avg(score) avg_score from sc group by sid;mysql> select distinct avg(score) avg_score from sc group by sid; +-----------+ | avg_score | +-----------+ | 89.66667 | | 70.00000 | | 80.00000 | | 33.33333 | | 81.50000 | | 32.50000 | | 93.50000 | +-----------+ 7 rows in set (0.00 sec)select stu.sid, t.avg_score '平均成绩', (select count(*) from (select distinct avg(score) avg_score from sc group by sid) t2 -- 查询平均成绩并去重 where t2.avg_score > t.avg_score )+1 '排名' from student stu,(select avg(score) avg_score,sid from sc group by sid) t where stu.sid = t.sid group by stu.sid order by 3;mysql> select -> stu.sid, -> t.avg_score '平均成绩', -> (select count(*) -> from (select distinct avg(score) avg_score from sc group by sid) t2 -- 查询平均成绩并去重 -> where t2.avg_score > t.avg_score -> )+1 '排名' -> from student stu,(select avg(score) avg_score,sid from sc group by sid) t -> where stu.sid = t.sid -> group by stu.sid -> order by 3; +-----+--------------+--------+ | sid | 平均成绩 | 排名 | +-----+--------------+--------+ | 07 | 93.50000 | 1 | | 01 | 89.66667 | 2 | | 05 | 81.50000 | 3 | | 03 | 80.00000 | 4 | | 02 | 70.00000 | 5 | | 04 | 33.33333 | 6 | | 06 | 32.50000 | 7 | +-----+--------------+--------+ 7 rows in set (0.00 sec)select stu.sid, t.avg_score '平均成绩', (select count(*) from (select distinct avg(score) avg_score from sc group by sid) t2 -- 查询平均成绩并去重 where t2.avg_score > t.avg_score )+1 '排名' from student stu,(select avg(score) avg_score,sid from sc group by sid) t where stu.sid = t.sid group by stu.sid;4.24.1、MySql中@符号的使用【经典】@符号的作用：@后接变量名，用以定义一个变量，该变量的有效期为语句级，即再一次执行中始终有效-- 由于通常情况下=被认为是等于比较运算符，因此赋值运算符一般使用:= SELECT @lt:=1, @lt:=@lt+1, @lt:=@lt+1;mysql> SELECT @lt:=1, @lt:=@lt+1, @lt:=@lt+1; +--------+------------+------------+ | @lt:=1 | @lt:=@lt+1 | @lt:=@lt+1 | +--------+------------+------------+ | 1 | 2 | 3 | +--------+------------+------------+ 1 row in set (0.00 sec)select s.sid, -- 学号 @i:=@i+1 as '不保留空缺排名', -- 直接i的自加 @k:=(case when @avgscore=s.avg_score then @k else @i end) as '保留空缺排名', @avgscore:=avg_score as '平均分' -- 表s中的值 from (select sid,round(avg(score), 2) as avg_score from sc group by sid order by 2 desc) s -- 表s：平均成绩的排序和学号 ,(select @avgscore:=0, @i:=0, @k:=0) b; -- 表b：通过变量设置初始值mysql> select -> s.sid, -- 学号 -> @i:=@i+1 as '不保留空缺排名', -- 直接i的自加 -> @k:=(case when @avgscore=s.avg_score then @k else @i end) as '保留空缺排名', -> @avgscore:=avg_score as '平均分' -- 表s中的值 -> -> from (select sid,round(avg(score), 2) as avg_score from sc -> group by sid -> order by 2 desc) s -- 表s：平均成绩的排序和学号 -> ,(select @avgscore:=0, @i:=0, @k:=0) b; -- 表b：通过变量设置初始值 +-----+-----------------------+--------------------+-----------+ | sid | 不保留空缺排名 | 保留空缺排名 | 平均分 | +-----+-----------------------+--------------------+-----------+ | 07 | 1 | 1 | 93.50 | | 01 | 2 | 2 | 89.67 | | 05 | 3 | 3 | 81.50 | | 03 | 4 | 4 | 80.00 | | 02 | 5 | 5 | 70.00 | | 04 | 6 | 6 | 33.33 | | 06 | 7 | 7 | 32.50 | +-----+-----------------------+--------------------+-----------+ 7 rows in set (0.00 sec)4.25、查询各科成绩前三名的记录【经典】先看 select * from sc s1 inner join sc s2 on s1.cid = s2.cid and s1.score <= s2.score; -- 将分数表sc自连接，通过课程id对应起来，每个分数表s2的成绩最大 然后是 select s1.*,count(s2.score) from sc s1 inner join sc s2 on s1.cid = s2.cid and s1.score <= s2.score group by s1.sid,s1.cid,s1.score; 然后是 select s1.*,count(distinct s2.score) from sc s1 inner join sc s2 on s1.cid = s2.cid and s1.score <= s2.score group by s1.sid,s1.cid,s1.score; 最后是 select s1.*,count(distinct s2.score) rank from sc s1 inner join sc s2 on s1.cid = s2.cid and s1.score <= s2.score group by s1.sid,s1.cid,s1.score having count(distinct s2.score) <=3 order by s1.cid,rank;mysql> select s1.*,count(distinct s2.score) rank -> from sc s1 -> inner join sc s2 on s1.cid = s2.cid and s1.score <= s2.score -> group by s1.sid,s1.cid,s1.score -> having count(distinct s2.score) <=3 -> order by s1.cid,rank; +-----+-----+-------+------+ | sid | cid | score | rank | +-----+-----+-------+------+ | 03 | 01 | 80.0 | 1 | | 01 | 01 | 80.0 | 1 | | 05 | 01 | 76.0 | 2 | | 02 | 01 | 70.0 | 3 | | 01 | 02 | 90.0 | 1 | | 07 | 02 | 89.0 | 2 | | 05 | 02 | 87.0 | 3 | | 01 | 03 | 99.0 | 1 | | 07 | 03 | 98.0 | 2 | | 02 | 03 | 80.0 | 3 | | 03 | 03 | 80.0 | 3 | +-----+-----+-------+------+ 11 rows in set (0.00 sec)4.26、查询每门课程被选修的学生数select c.cid, c.cname, count(sc.cid) from course c inner join sc on c.cid = sc.cid group by c.cid;mysql> select -> c.cid, -> c.cname, -> count(sc.cid) -> from course c -> inner join sc on c.cid = sc.cid -> group by c.cid; +-----+--------+---------------+ | cid | cname | count(sc.cid) | +-----+--------+---------------+ | 01 | 语文 | 6 | | 02 | 数学 | 6 | | 03 | 英语 | 6 | +-----+--------+---------------+ 3 rows in set (0.00 sec)4.27、查询出只有两门课程的全部学生的学号和姓名select stu.sid '学号', stu.sname '姓名' from student stu inner join sc on stu.sid = sc.sid group by stu.sid having count(sc.sid) = 2;mysql> select -> stu.sid '学号', -> stu.sname '姓名' -> from student stu -> inner join sc on stu.sid = sc.sid -> group by stu.sid -> having count(sc.sid) = 2; +--------+--------+ | 学号 | 姓名 | +--------+--------+ | 05 | 周梅 | | 06 | 吴兰 | | 07 | 郑竹 | +--------+--------+ 3 rows in set (0.00 sec)4.28、查询男生、女生人数select ssex '性别', count(ssex) '人数' from student group by ssex;mysql> select -> ssex '性别', -> count(ssex) '人数' -> from student -> group by ssex; +--------+--------+ | 性别 | 人数 | +--------+--------+ | 女 | 4 | | 男 | 4 | +--------+--------+ 2 rows in set (0.00 sec)select count(case when ssex = '男' then 1 else 0 end) '男', count(case when ssex = '女' then 1 else 0 end) '女' from student;mysql> select -> count(case when ssex = '男' then 1 else 0 end) '男', -> count(case when ssex = '女' then 1 else 0 end) '女' -> from student; +-----+-----+ | 男 | 女 | +-----+-----+ | 8 | 8 | +-----+-----+ 1 row in set (0.00 sec)select count(ssex='女') '女',count(ssex='男') '男' from student;mysql> select count(ssex='女') '女',count(ssex='男') '男' from student; +-----+-----+ | 女 | 男 | +-----+-----+ | 8 | 8 | +-----+-----+ 1 row in set (0.00 sec)4.29、查询名字中含有"风"字的学生信息select student.* from student where sname like '%风%';mysql> select -> student.* -> from student -> where sname like '%风%'; +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 03 | 孙风 | 1990-05-20 00:00:00 | 男 | +-----+--------+---------------------+------+ 1 row in set (0.00 sec)4.30、查询同名同性学生名单，并统计同名人数INSERT INTO student VALUES('09' , '赵雷' , '2013-05-21' , '男'),('10' , '王菊' , '1314-12-25' , '女');select s1.sname, s1.ssex, count(*) from student s1 inner join student s2 on s1.sid <> s2.sid and s1.sname = s2.sname and s1.ssex = s2.ssex group by s1.sname,s1.ssex;mysql> INSERT INTO student VALUES('09' , '赵雷' , '2013-05-21' , '男'),('10' , '王菊' , '1314-12-25' , '女'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select -> s1.sname, -> s1.ssex, -> count(*) -> from student s1 -> inner join student s2 on s1.sid <> s2.sid and s1.sname = s2.sname and s1.ssex = s2.ssex -> group by s1.sname,s1.ssex; +--------+------+----------+ | sname | ssex | count(*) | +--------+------+----------+ | 王菊 | 女 | 2 | | 赵雷 | 男 | 2 | +--------+------+----------+ 2 rows in set (0.00 sec) mysql> delete from student where sid in ('09','10'); Query OK, 2 rows affected (0.00 sec)delete from student where sid in ('09','10');4.31、查询1990年出生的学生名单select student.* from student where year(sage) = 1990;mysql> select -> student.* -> from student -> where year(sage) = 1990; +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-05-20 00:00:00 | 男 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | | 08 | 王菊 | 1990-01-20 00:00:00 | 女 | +-----+--------+---------------------+------+ 5 rows in set (0.00 sec)select student.* from student where sage like '1990%';mysql> select -> student.* -> from student -> where sage like '1990%'; +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-05-20 00:00:00 | 男 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | | 08 | 王菊 | 1990-01-20 00:00:00 | 女 | +-----+--------+---------------------+------+ 5 rows in set, 1 warning (0.00 sec)4.32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列select sc.cid '课程编号', avg(score) '平均成绩' from sc group by cid order by 2 desc,1 asc;mysql> select -> sc.cid '课程编号', -> avg(score) '平均成绩' -> from sc -> group by cid -> order by 2 desc,1 asc; +--------------+--------------+ | 课程编号 | 平均成绩 | +--------------+--------------+ | 02 | 72.66667 | | 03 | 68.50000 | | 01 | 64.50000 | +--------------+--------------+ 3 rows in set (0.00 sec)4.33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩select stu.sid '学号', stu.sname '姓名', avg(score) '平均成绩' from sc join student stu on sc.sid = stu.sid group by sc.sid having avg(score) >= 85;mysql> select -> stu.sid '学号', -> stu.sname '姓名', -> avg(score) '平均成绩' -> from sc -> join student stu on sc.sid = stu.sid -> group by sc.sid -> having avg(score) >= 85; +--------+--------+--------------+ | 学号 | 姓名 | 平均成绩 | +--------+--------+--------------+ | 01 | 赵雷 | 89.66667 | | 07 | 郑竹 | 93.50000 | +--------+--------+--------------+ 2 rows in set (0.00 sec)4.34、查询课程名称为"数学"，且分数低于60的学生姓名和分数select stu.sname '学生姓名', sc.score '分数' from sc inner join student stu on sc.sid = stu.sid inner join course c on sc.cid = c.cid and c.cname = '数学' where sc.score < 60;mysql> select -> stu.sname '学生姓名', -> sc.score '分数' -> from sc -> inner join student stu on sc.sid = stu.sid -> inner join course c on sc.cid = c.cid and c.cname = '数学' -> where sc.score < 60; +--------------+--------+ | 学生姓名 | 分数 | +--------------+--------+ | 李云 | 30.0 | +--------------+--------+ 1 row in set (0.00 sec)4.35、查询所有学生的课程及分数情况；select stu.sid, stu.sname, sum(case when sc.cid = '01' then sc.score else 0 end) '语文', sum(case when sc.cid = '02' then sc.score else 0 end) '数学', sum(case when sc.cid = '03' then sc.score else 0 end) '英语', sum(sc.score) from student stu left join sc on stu.sid = sc.sid group by stu.sid,stu.sname;mysql> select -> stu.sid, -> stu.sname, -> sum(case when sc.cid = '01' then sc.score else 0 end) '语文', -> sum(case when sc.cid = '02' then sc.score else 0 end) '数学', -> sum(case when sc.cid = '03' then sc.score else 0 end) '英语', -> sum(sc.score) -> from student stu -> left join sc on stu.sid = sc.sid -> group by stu.sid,stu.sname; +-----+--------+--------+--------+--------+---------------+ | sid | sname | 语文 | 数学 | 英语 | sum(sc.score) | +-----+--------+--------+--------+--------+---------------+ | 01 | 赵雷 | 80.0 | 90.0 | 99.0 | 269.0 | | 02 | 钱电 | 70.0 | 60.0 | 80.0 | 210.0 | | 03 | 孙风 | 80.0 | 80.0 | 80.0 | 240.0 | | 04 | 李云 | 50.0 | 30.0 | 20.0 | 100.0 | | 05 | 周梅 | 76.0 | 87.0 | 0.0 | 163.0 | | 06 | 吴兰 | 31.0 | 0.0 | 34.0 | 65.0 | | 07 | 郑竹 | 0.0 | 89.0 | 98.0 | 187.0 | | 08 | 王菊 | 0.0 | 0.0 | 0.0 | NULL | +-----+--------+--------+--------+--------+---------------+ 8 rows in set (0.00 sec)select stu.sid, stu.sname, sum(case c.cname when '语文' then sc.score else 0 end) '语文', sum(case c.cname when '数学' then sc.score else 0 end) '数学', sum(case c.cname when '英语' then sc.score else 0 end) '英语', sum(sc.score) from student stu left join sc on stu.sid = sc.sid left join course c on sc.cid = c.cid group by stu.sid;mysql> select -> stu.sid, -> stu.sname, -> sum(case c.cname when '语文' then sc.score else 0 end) '语文', -> sum(case c.cname when '数学' then sc.score else 0 end) '数学', -> sum(case c.cname when '英语' then sc.score else 0 end) '英语', -> sum(sc.score) -> from student stu -> left join sc on stu.sid = sc.sid -> left join course c on sc.cid = c.cid -> group by stu.sid; +-----+--------+--------+--------+--------+---------------+ | sid | sname | 语文 | 数学 | 英语 | sum(sc.score) | +-----+--------+--------+--------+--------+---------------+ | 01 | 赵雷 | 80.0 | 90.0 | 99.0 | 269.0 | | 02 | 钱电 | 70.0 | 60.0 | 80.0 | 210.0 | | 03 | 孙风 | 80.0 | 80.0 | 80.0 | 240.0 | | 04 | 李云 | 50.0 | 30.0 | 20.0 | 100.0 | | 05 | 周梅 | 76.0 | 87.0 | 0.0 | 163.0 | | 06 | 吴兰 | 31.0 | 0.0 | 34.0 | 65.0 | | 07 | 郑竹 | 0.0 | 89.0 | 98.0 | 187.0 | | 08 | 王菊 | 0.0 | 0.0 | 0.0 | NULL | +-----+--------+--------+--------+--------+---------------+ 8 rows in set (0.00 sec)4.36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数select stu.sname, c.cname, sc.score from sc join student stu on sc.sid = stu.sid join course c on sc.cid = c.cid having score > 70;mysql> select -> stu.sname, -> c.cname, -> sc.score -> from sc -> join student stu on sc.sid = stu.sid -> join course c on sc.cid = c.cid -> having score > 70; +--------+--------+-------+ | sname | cname | score | +--------+--------+-------+ | 赵雷 | 语文 | 80.0 | | 赵雷 | 数学 | 90.0 | | 赵雷 | 英语 | 99.0 | | 钱电 | 英语 | 80.0 | | 孙风 | 语文 | 80.0 | | 孙风 | 数学 | 80.0 | | 孙风 | 英语 | 80.0 | | 周梅 | 语文 | 76.0 | | 周梅 | 数学 | 87.0 | | 郑竹 | 数学 | 89.0 | | 郑竹 | 英语 | 98.0 | +--------+--------+-------+ 11 rows in set (0.00 sec)4.37、查询不及格的课程select sc.sid, c.cname, sc.score from sc join course c on sc.cid = c.cid where sc.score < 60;mysql> select -> sc.sid, -> c.cname, -> sc.score -> from sc -> join course c on sc.cid = c.cid -> where sc.score < 60; +-----+--------+-------+ | sid | cname | score | +-----+--------+-------+ | 04 | 语文 | 50.0 | | 04 | 数学 | 30.0 | | 04 | 英语 | 20.0 | | 06 | 语文 | 31.0 | | 06 | 英语 | 34.0 | +-----+--------+-------+ 5 rows in set (0.00 sec)4.38、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名select stu.sid, stu.sname, sc.score from sc join student stu on sc.sid = stu.sid where sc.score >= 80 and sc.cid = '01';mysql> select -> stu.sid, -> stu.sname, -> sc.score -> from sc -> join student stu on sc.sid = stu.sid -> where sc.score >= 80 and sc.cid = '01'; +-----+--------+-------+ | sid | sname | score | +-----+--------+-------+ | 01 | 赵雷 | 80.0 | | 03 | 孙风 | 80.0 | +-----+--------+-------+ 2 rows in set (0.00 sec)4.39、求每门课程的学生人数select c.cname '课程名', count(*) '学生人数' from course c join sc on c.cid = sc.cid group by c.cid;mysql> select -> c.cname '课程名', -> count(*) '学生人数' -> from course c -> join sc on c.cid = sc.cid -> group by c.cid; +-----------+--------------+ | 课程名 | 学生人数 | +-----------+--------------+ | 语文 | 6 | | 数学 | 6 | | 英语 | 6 | +-----------+--------------+ 3 rows in set (0.01 sec)4.40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩select stu.sid, stu.sname, sc.score, c.cname, t.tname from sc left join course c on c.cid = sc.cid left join teacher t on c.tid = t.tid left join student stu on sc.sid = stu.sid where t.tname = '张三';mysql> select -> stu.sid, -> stu.sname, -> sc.score, -> c.cname, -> t.tname -> from sc -> left join course c on c.cid = sc.cid -> left join teacher t on c.tid = t.tid -> left join student stu on sc.sid = stu.sid -> where t.tname = '张三'; +------+--------+-------+--------+--------+ | sid | sname | score | cname | tname | +------+--------+-------+--------+--------+ | 01 | 赵雷 | 90.0 | 数学 | 张三 | | 02 | 钱电 | 60.0 | 数学 | 张三 | | 03 | 孙风 | 80.0 | 数学 | 张三 | | 04 | 李云 | 30.0 | 数学 | 张三 | | 05 | 周梅 | 87.0 | 数学 | 张三 | | 07 | 郑竹 | 89.0 | 数学 | 张三 | +------+--------+-------+--------+--------+ 6 rows in set (0.00 sec)select stu.sid, stu.sname, max(sc.score), c.cname, t.tname from sc left join course c on c.cid = sc.cid left join teacher t on c.tid = t.tid left join student stu on sc.sid = stu.sid where t.tname = '张三';mysql> select -> stu.sid, -> stu.sname, -> max(sc.score), -> c.cname, -> t.tname -> from sc -> left join course c on c.cid = sc.cid -> left join teacher t on c.tid = t.tid -> left join student stu on sc.sid = stu.sid -> where t.tname = '张三'; +------+--------+---------------+--------+--------+ | sid | sname | max(sc.score) | cname | tname | +------+--------+---------------+--------+--------+ | 01 | 赵雷 | 90.0 | 数学 | 张三 | +------+--------+---------------+--------+--------+ 1 row in set (0.00 sec)4.41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩select distinct s1.sid, s1.cid, s1.score from sc s1 join sc s2 on s1.score = s2.score and s1.cid != s2.cid and s1.sid != s2.sid;mysql> select -> distinct s1.sid, -> s1.cid, -> s1.score -> from sc s1 -> join sc s2 on s1.score = s2.score and s1.cid != s2.cid and s1.sid != s2.sid; +-----+-----+-------+ | sid | cid | score | +-----+-----+-------+ | 02 | 03 | 80.0 | | 03 | 02 | 80.0 | | 03 | 03 | 80.0 | | 01 | 01 | 80.0 | | 03 | 01 | 80.0 | +-----+-----+-------+ 5 rows in set (0.00 sec)-- 查询成绩相同的分数 select score from sc group by score having count(1)>1; select sc.sid, sc.cid, sc.score from sc where sc.score in (select score from sc group by score having count(1)>1);mysql> select -> sc.sid, -> sc.cid, -> sc.score -> from sc -> where sc.score in (select score from sc group by score having count(1)>1); +-----+-----+-------+ | sid | cid | score | +-----+-----+-------+ | 01 | 01 | 80.0 | | 02 | 03 | 80.0 | | 03 | 01 | 80.0 | | 03 | 02 | 80.0 | | 03 | 03 | 80.0 | +-----+-----+-------+ 5 rows in set (0.00 sec)4.42、查询每门功课成绩最好的前两名【经典】select s1.*, count(distinct(s2.score)) rank from sc s1 join sc s2 on s1.cid = s2.cid and s1.score <= s2.score group by s1.sid,s1.cid,s1.score having rank <=2 order by s1.cid,rank;mysql> select -> s1.*, -> count(distinct(s2.score)) rank -> from sc s1 -> join sc s2 on s1.cid = s2.cid and s1.score <= s2.score -> group by s1.sid,s1.cid,s1.score -> having rank <=2 -> order by s1.cid,rank; +-----+-----+-------+------+ | sid | cid | score | rank | +-----+-----+-------+------+ | 01 | 01 | 80.0 | 1 | | 03 | 01 | 80.0 | 1 | | 05 | 01 | 76.0 | 2 | | 01 | 02 | 90.0 | 1 | | 07 | 02 | 89.0 | 2 | | 01 | 03 | 99.0 | 1 | | 07 | 03 | 98.0 | 2 | +-----+-----+-------+------+ 7 rows in set (0.00 sec)select stu.sname, c.cname, t.tname, s1.score, count(distinct(s2.score)) rank from sc s1 join sc s2 on s1.cid = s2.cid and s1.score <= s2.score join student stu on s1.sid = stu.sid join course c on s1.cid = c.cid join teacher t on c.tid = t.tid group by s1.sid,s1.cid,s1.score having rank <=2 order by s1.cid,rank;mysql> select -> stu.sname, -> c.cname, -> t.tname, -> s1.score, -> count(distinct(s2.score)) rank -> from sc s1 -> join sc s2 on s1.cid = s2.cid and s1.score <= s2.score -> join student stu on s1.sid = stu.sid -> join course c on s1.cid = c.cid -> join teacher t on c.tid = t.tid -> group by s1.sid,s1.cid,s1.score -> having rank <=2 -> order by s1.cid,rank; +--------+--------+--------+-------+------+ | sname | cname | tname | score | rank | +--------+--------+--------+-------+------+ | 孙风 | 语文 | 李四 | 80.0 | 1 | | 赵雷 | 语文 | 李四 | 80.0 | 1 | | 周梅 | 语文 | 李四 | 76.0 | 2 | | 赵雷 | 数学 | 张三 | 90.0 | 1 | | 郑竹 | 数学 | 张三 | 89.0 | 2 | | 赵雷 | 英语 | 王五 | 99.0 | 1 | | 郑竹 | 英语 | 王五 | 98.0 | 2 | +--------+--------+--------+-------+------+ 7 rows in set (0.00 sec)select s1.* from sc s1 where (select count(1) -- count(1)类似count(*):统计表s2中分数大的数量 from sc s2 where s2.cid=s1.cid -- 课程相同 and s2.score >= s1.score) <= 2 -- 前2名 order by s1.cid;此方法有缺陷，重复分数未统计mysql> select -> s1.* -> from sc s1 -> where (select count(1) -- count(1)类似count(*):统计表s2中分数大的数量 -> from sc s2 -> where s2.cid=s1.cid -- 课程相同 -> and s2.score >= s1.score) <= 2 -- 前2名 -> order by s1.cid; +-----+-----+-------+ | sid | cid | score | +-----+-----+-------+ | 01 | 01 | 80.0 | | 03 | 01 | 80.0 | | 01 | 02 | 90.0 | | 07 | 02 | 89.0 | | 01 | 03 | 99.0 | | 07 | 03 | 98.0 | +-----+-----+-------+ 6 rows in set (0.00 sec)select s1.* from sc s1 where (select count(distinct(s2.score)) -- 根据分数去重并统计表s2中分数大的数量 from sc s2 where s2.cid=s1.cid -- 课程相同 and s2.score >= s1.score) <= 2 -- 前2名 order by s1.cid;mysql> select -> s1.* -> from sc s1 -> where (select count(distinct(s2.score)) -- 根据分数去重并统计表s2中分数大的数量 -> from sc s2 -> where s2.cid=s1.cid -- 课程相同 -> and s2.score >= s1.score) <= 2 -- 前2名 -> order by s1.cid; +-----+-----+-------+ | sid | cid | score | +-----+-----+-------+ | 01 | 01 | 80.0 | | 03 | 01 | 80.0 | | 05 | 01 | 76.0 | | 01 | 02 | 90.0 | | 07 | 02 | 89.0 | | 01 | 03 | 99.0 | | 07 | 03 | 98.0 | +-----+-----+-------+ 7 rows in set (0.00 sec)4.43、统计每门课程的学生选修人数（超过5人的课程才统计）；要求输出课程号和选修人数，查询结果按人数降序排列。select sc.cid '课程号', c.cname '课程名', count(*) '选修人数' from sc join course c on sc.cid = c.cid group by sc.cid having(count(*)) > 5;mysql> select -> sc.cid '课程号', -> c.cname '课程名', -> count(*) '选修人数' -> from sc -> join course c on sc.cid = c.cid -> group by sc.cid -> having(count(*)) > 5; +-----------+-----------+--------------+ | 课程号 | 课程名 | 选修人数 | +-----------+-----------+--------------+ | 01 | 语文 | 6 | | 02 | 数学 | 6 | | 03 | 英语 | 6 | +-----------+-----------+--------------+ 3 rows in set (0.00 sec)4.44、检索至少选修两门课程的学生学号select sc.sid, stu.sname, count(*) from sc join student stu on sc.sid = stu.sid group by sc.sid having count(*) >= 2;mysql> select -> sc.sid, -> stu.sname, -> count(*) -> from sc -> join student stu on sc.sid = stu.sid -> group by sc.sid -> having count(*) >= 2; +-----+--------+----------+ | sid | sname | count(*) | +-----+--------+----------+ | 01 | 赵雷 | 3 | | 02 | 钱电 | 3 | | 03 | 孙风 | 3 | | 04 | 李云 | 3 | | 05 | 周梅 | 2 | | 06 | 吴兰 | 2 | | 07 | 郑竹 | 2 | +-----+--------+----------+ 7 rows in set (0.00 sec)4.45、查询选修了全部课程的学生信息select sc.sid, stu.sname, count(*) from sc join student stu on sc.sid = stu.sid group by sc.sid having count(*) = (select count(*) from course);mysql> select -> sc.sid, -> stu.sname, -> count(*) -> from sc -> join student stu on sc.sid = stu.sid -> group by sc.sid -> having count(*) = (select count(*) from course); +-----+--------+----------+ | sid | sname | count(*) | +-----+--------+----------+ | 01 | 赵雷 | 3 | | 02 | 钱电 | 3 | | 03 | 孙风 | 3 | | 04 | 李云 | 3 | +-----+--------+----------+ 4 rows in set (0.00 sec)4.46、查询各学生的年龄-- 通过date_format函数能够指定返回的数据 select year(now()); select date_format(now(), '%Y'); select date_format(now(), '%Y-%m-%d');mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2024 | +-------------+ 1 row in set (0.00 sec) mysql> select date_format(now(), '%Y'); +--------------------------+ | date_format(now(), '%Y') | +--------------------------+ | 2024 | +--------------------------+ 1 row in set (0.00 sec) mysql> select date_format(now(), '%Y-%m-%d'); +--------------------------------+ | date_format(now(), '%Y-%m-%d') | +--------------------------------+ | 2024-02-08 | +--------------------------------+ 1 row in set (0.00 sec)select stu.sname '姓名', TIMESTAMPDIFF(year,stu.sage,now()) '年龄' -- TIMESTAMPDIFF函数返回年差 from student stu;mysql> select -> stu.sname '姓名', -> TIMESTAMPDIFF(year,stu.sage,now()) '年龄' -> from student stu; +--------+--------+ | 姓名 | 年龄 | +--------+--------+ | 赵雷 | 34 | | 钱电 | 33 | | 孙风 | 33 | | 李云 | 33 | | 周梅 | 32 | | 吴兰 | 31 | | 郑竹 | 34 | | 王菊 | 34 | +--------+--------+ 8 rows in set (0.00 sec)select sname ,sage ,date_format(now(), '%Y') - date_format(sage, '%Y') - (case when date_format(now(), '%m%d') > date_format(sage, '%m%d') then 0 else 1 end) as age -- 当前日期大，说明已经过生了，年龄正常；反之说明今年还没有到年龄-1 from student;mysql> select -> sname -> ,sage -> ,date_format(now(), '%Y') - date_format(sage, '%Y') - (case when date_format(now(), '%m%d') > date_format(sage, '%m%d') then 0 else 1 end) as age -- 当前日 期大，说明已经过生了，年龄正常；反之说明今年还没有到年龄-1 -> from student; +--------+---------------------+------+ | sname | sage | age | +--------+---------------------+------+ | 赵雷 | 1990-01-01 00:00:00 | 34 | | 钱电 | 1990-12-21 00:00:00 | 33 | | 孙风 | 1990-05-20 00:00:00 | 33 | | 李云 | 1990-08-06 00:00:00 | 33 | | 周梅 | 1991-12-01 00:00:00 | 32 | | 吴兰 | 1992-03-01 00:00:00 | 31 | | 郑竹 | 1989-07-01 00:00:00 | 34 | | 王菊 | 1990-01-20 00:00:00 | 34 | +--------+---------------------+------+ 8 rows in set (0.00 sec)4.47、查询本周过生日的学生INSERT INTO student VALUES('09' , '李白' , date_format(now(),'%Y-%m-%d'), '男'), ('10' , '玛利亚' , date_format(now(),'%Y-%m-%d'), '女'); select * from student;mysql> INSERT INTO student VALUES('09' , '李白' , date_format(now(),'%Y-%m-%d'), '男'), -> ('10' , '玛利亚' , date_format(now(),'%Y-%m-%d'), '女'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> select * from student; +-----+-----------+---------------------+------+ | sid | sname | sage | ssex | +-----+-----------+---------------------+------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-05-20 00:00:00 | 男 | | 04 | 李云 | 1990-08-06 00:00:00 | 男 | | 05 | 周梅 | 1991-12-01 00:00:00 | 女 | | 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | | 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | | 08 | 王菊 | 1990-01-20 00:00:00 | 女 | | 09 | 李白 | 2024-02-08 00:00:00 | 男 | | 10 | 玛利亚 | 2024-02-08 00:00:00 | 女 | +-----+-----------+---------------------+------+ 10 rows in set (0.01 sec)select stu.* from student stu where week(date_format(now(),'%Y%m%d')) = week(sage);mysql> select -> stu.* -> from student stu -> where week(date_format(now(),'%Y%m%d')) = week(sage); +-----+-----------+---------------------+------+ | sid | sname | sage | ssex | +-----+-----------+---------------------+------+ | 09 | 李白 | 2024-02-08 00:00:00 | 男 | | 10 | 玛利亚 | 2024-02-08 00:00:00 | 女 | +-----+-----------+---------------------+------+ 2 rows in set (0.00 sec)select * from student where WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=0;mysql> select * from student where WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=0; +-----+-----------+---------------------+------+ | sid | sname | sage | ssex | +-----+-----------+---------------------+------+ | 09 | 李白 | 2024-02-08 00:00:00 | 男 | | 10 | 玛利亚 | 2024-02-08 00:00:00 | 女 | +-----+-----------+---------------------+------+ 2 rows in set (0.00 sec)4.48、查询下周过生日的学生select stu.* from student stu where week(date_format(now(),'%Y%m%d'))+1 = week(sage);select * from student where WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=-1;4.49、查询本月过生日的学生select stu.* from student stu where month(date_format(now(),'%Y%m%d')) = month(sage);mysql> select -> stu.* -> from student stu -> where month(date_format(now(),'%Y%m%d')) = month(sage); +-----+-----------+---------------------+------+ | sid | sname | sage | ssex | +-----+-----------+---------------------+------+ | 09 | 李白 | 2024-02-08 00:00:00 | 男 | | 10 | 玛利亚 | 2024-02-08 00:00:00 | 女 | +-----+-----------+---------------------+------+ 2 rows in set (0.00 sec)4.50、查询下月过生日的学生select stu.* from student stu where month(date_format(now(),'%Y%m%d'))+1 = month(sage);mysql> select -> stu.* -> from student stu -> where month(date_format(now(),'%Y%m%d'))+1 = month(sage); +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | +-----+--------+---------------------+------+ 1 row in set (0.00 sec)-- 假设现在是12月份，那么下个月就是明年的1月份,解决：当前月份与12的余数 select stu.* from student stu where mod(month(now()),12)+1 = month(sage);mysql> select -> stu.* -> from student stu -> where mod(month(now()),12)+1 = month(sage); +-----+--------+---------------------+------+ | sid | sname | sage | ssex | +-----+--------+---------------------+------+ | 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | +-----+--------+---------------------+------+ 1 row in set (0.00 sec)end

MySQL数据库练习【一】 @TOC一、建库建表-数据准备create database company; use company; -- 创建部门表 DROP TABLE IF EXISTS dept; CREATE TABLE `dept` ( `deptno` int(11) PRIMARY KEY NOT NULL COMMENT '部门编号', `dname` varchar(14) NOT NULL COMMENT '部门名称', `loc` varchar(13) NOT NULL COMMENT '部门所在地' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'; -- 向部门表插入数据 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'), (20,'RESEARCH','DALLAS'), (30,'SALES','CHICAGO'), (40,'OPERATIONS','BOSTON'); -- 创建员工表 DROP TABLE IF EXISTS emp; CREATE TABLE `emp` ( `empno` int(11) PRIMARY KEY NOT NULL COMMENT '员工编号', `ename` varchar(10) DEFAULT NULL COMMENT '员工姓名', `job` varchar(9) DEFAULT NULL COMMENT '工作岗位', `mgr` int(11) DEFAULT NULL COMMENT '直属领导', `hiredate` date DEFAULT NULL COMMENT '入职时间', `sal` double DEFAULT NULL COMMENT '工资', `comm` double DEFAULT NULL COMMENT '奖金', `deptno` int(11) DEFAULT NULL COMMENT '所属部门' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'; -- 向员工表插入数据 INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20), (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30), (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30), (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20), (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30), (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30), (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10), (7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20), (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10), (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30), (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20), (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30), (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20), (7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10); -- 创建工资等级表 DROP TABLE IF EXISTS salgrade; CREATE TABLE `salgrade` ( `grade` int(11) DEFAULT NULL COMMENT '等级', `losal` double DEFAULT NULL COMMENT '最低工资', `hisal` double DEFAULT NULL COMMENT '最高工资' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级表'; -- 向工资等级表插入数据 INSERT INTO salgrade VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);-- 为员工表表添加外键约束 -- ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY EMP(deptno) REFERENCES dept (deptno);员工表emp部门表dept工资等级表salgradeempno：员工编号deptno：部门编号grade：等级ename：员工姓名dname：部门名称losal：最低工资job：工作岗位loc：部门所在地hisal：最高工资mgr：直属领导 hiredate：入职时间 sal：工资 comm：奖金 deptno：所属部门 empnoenamejobmgrhiredatesalcommdeptno7369SMITHCLERK79021980-12-17800NULL207499ALLENSALESMAN76981981-02-201600300307521WARDSALESMAN76981981-02-221250500307566JONESMANAGER78391981-04-022975NULL207654MARTINSALESMAN76981981-09-2812501400307698BLAKEMANAGER78391981-05-012850NULL307782CLARKMANAGER78391981-06-092450NULL107788SCOTTANALYST75661987-07-033000NULL207839KINGPRESIDENTNULL1981-11-175000NULL107844TURNERSALESMAN76981981-09-0815000307876ADAMSCLERK77881987-07-131100NULL207900JAMESCLERK76981981-12-03950NULL307902FORDANALYST75661981-12-033000NULL207934MILLERCLERK77821981-01-231300NULL10deptnodnameloc10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONgradelosalhisal17001200212011400314012000420013000530019999二、习题2.1.查询部门编号为30的部门的员工详细信息 2.2.查询从事clerk工作的员工的编号、姓名以及其部门号 2.3.查询奖金多于基本工资的员工的信息、查询奖金小于基本工资的员工的信息 2.4.查询奖金多于基本工资60%的员工的信息 2.5.查询部门编号为10的部门经理 (job=manager) 2.6.查询部门编号为20的在部门中工作为CLERK的职员信息 2.7.查询部门编号为10的部门经理 (job=manager)或部门编号为20的部门工作为CLERK的职员信息 2.8.查询部门编号为10的部门经理或部门编号为20的部门工作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息 2.9.查询获得奖金的员工的信息 2.10.查询奖金少于100或者没有获得奖金的员工的信息 2.11.MySQL LEFT函数【语法】 2.12.查询姓名以A、B、S开头的员工的信息【LEFT函数】 2.13.查询找到姓名长度为6个字符的员工的信息【length函数】 2.14.查询姓名中不包含R字符的员工信息【not like】 2.15.查询员工的详细信息并按姓名排序【asc、desc】 2.16.查询员工的信息并按工作岗位降序、工资升序排列 2.17.计算员工的日薪(按30天计)【ROUND】 2.18.查询姓名中包含字符A的员工的信息 2.19.查询拥有员工的部门的部门名称和部门编号【自连接 inner join】 2.20.查询工资多于smith的员工信息 2.21.查询员工和及其所属经理的姓名(同一个部门，job=manager) 2.22.DATEDIFF函数与TIMESTAMPDIFF函数【语法】 2.23.查询员工雇佣日期早于其经理雇佣日期的员工及其经理姓名 2.24.查询员工姓名及其所在的部门名称 2.25.查询工作为clerk的员工姓名及其所在部门名称 2.26.查询各部门编号及其部门中的最低工资【group by】 2.27.查询销售部sales的所有员工的姓名 2.28.子查询【语法】 2.29.列出薪金高于公司平均薪金的所有员工，要求使用子查询实现【子查询】 2.29.查询工资比编号为7566雇员工资高的雇员姓名 2.30.显示和雇员scott同部门的雇员姓名、工资和部门编号 2.31.显示和雇员SCOTT从事相同工作，并且工资大于JAMES的雇员姓名、工作岗位和工资 2.32.显示工资最低的雇员姓名、工作和工资 2.33.显示工资最高的雇员姓名、工作和工资 2.34.显示工资高于平均工资的雇员姓名、工作、工资和工资等级 2.35.查询部门内的最低工资比20号部门的最低工资要高的部门的编号及部门内最低工资【having】 2.36.查询和SMITH从事相同工作的雇员姓名和工作 2.37.查询有下属的雇员姓名、工作、工资、部门号 2.38.查询没有下属的雇员姓名、工作、工资和部门号 2.39.查询各部门中工资最低的员工姓名、工作、工资和部门号 2.40.查询部门中工资最高的员工姓名、工作、工资和部门号 2.41.查询与销售部门（SALES）工作岗位相同的其它部门雇员姓名、工作、工资和部门名称 2.42.使用any运算符的多行子查询【语法】 2.43.查询工资低于某个文员（CLERK）雇员工资，但不从事文员工作的员工编号、姓名、工作岗位和工资 2.44.查询工资高于某个文员（CLERK）雇员工资，但不从事文员工作的员工编号、姓名、工作岗位和工资 2.45.查询工资高于部门编号是30的部门内某个雇员工资，但不在该部门工作的员工姓名、工作岗位、工资和部门编号 2.46.查询工资低于部门名称是SALES的部门内某个雇员工资，但不在该部门工作的员工姓名、工作岗位、工资和部门编号和部门名称 2.47.使用all运算符的多行子查询【语法】 2.48.查询高于所有部门平均工资的雇员姓名、工作、工资和部门编号 2.49.查询低于所有部门平均工资的雇员姓名、工作、工资和部门编号 2.50.查询工资高于部门编号为30的部门内所有员工工资的雇员姓名、工作、工资和部门编号 2.51.查询工资等级为4的雇员姓名、工作、工资、部门编号和工资等级，同时满足该雇员工资高于部门编号为30的部门内所有员工工资 2.52.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号 2.53.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门名称 2.54.查询工资与30部门中任意一个雇员的工资相等，同时奖金也与30部门中任意一个雇员奖金相等的雇员姓名、工资、奖金、部门编号，但该雇员不是来自30号部门 2.55.查询在纽约（NEW YORK）工作的雇员姓名、工作岗位、工资和奖金 2.56.显示工资高于部门平均工资的雇员姓名、工作、工资和部门号 2.57.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号 2.58.查询平均工资最低的工种名称及其平均工资 2.59.查询与部门编号为30的员工工资水平相同的员工信息 2.60.查询部门号、部门名、部门所在位置及其每个部门的员工总数 2.61.计算员工的年薪并且以年薪排序2.1. 查询部门编号为30的部门的员工详细信息mysql> select * from emp where deptno=30; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | +-------+--------+----------+------+------------+------+------+--------+2.2.查询从事clerk工作的员工的编号、姓名以及其部门号mysql> select empno,ename,deptno from emp where job='clerk'; +-------+--------+--------+ | empno | ename | deptno | +-------+--------+--------+ | 7369 | SMITH | 20 | | 7876 | ADAMS | 20 | | 7900 | JAMES | 30 | | 7934 | MILLER | 10 | +-------+--------+--------+ 4 rows in set (0.00 sec)2.3.查询奖金多于基本工资的员工的信息、查询奖金小于基本工资的员工的信息mysql> select * from emp where comm > sal; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | +-------+--------+----------+------+------------+------+------+--------+ 1 row in set (0.00 sec) mysql> select * from emp where comm is null or comm < sal; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+-----------+------+------------+------+------+--------+ 13 rows in set (0.00 sec)2.4.查询奖金多于基本工资60%的员工的信息mysql> select * from emp where comm > sal * 0.6; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | +-------+--------+----------+------+------------+------+------+--------+ 1 row in set (0.00 sec)2.5.查询部门编号为10的部门经理 (job=manager)mysql> select * from emp where deptno = 10 and job = 'manager'; +-------+-------+---------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+---------+------+------------+------+------+--------+ | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | +-------+-------+---------+------+------------+------+------+--------+ 1 row in set (0.00 sec)2.6.查询部门编号为20的在部门中工作为CLERK的职员信息mysql> select * from emp where deptno = 20 and job = 'clerk'; +-------+-------+-------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | +-------+-------+-------+------+------------+------+------+--------+ 2 rows in set (0.00 sec)2.7.查询部门编号为10的部门经理 (job=manager)或部门编号为20的部门工作为CLERK的职员信息mysql> select * from emp where (deptno = 10 and job = 'manager') or (deptno = 20 and job = 'clerk'); +-------+-------+---------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+---------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | +-------+-------+---------+------+------------+------+------+--------+ 3 rows in set (0.00 sec)2.8.查询部门编号为10的部门经理或部门编号为20的部门工作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息mysql> select * from emp where (deptno = 10 and job = 'manager') -> or (deptno = 20 and job = 'clerk') -> or (job not in ('manager','clerk') AND sal > 2000); +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | +-------+-------+-----------+------+------------+------+------+--------+ 6 rows in set (0.00 sec)2.9.查询获得奖金的员工的信息mysql> select * from emp where comm is not null and comm > 0; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | +-------+--------+----------+------+------------+------+------+--------+ 3 rows in set (0.00 sec)2.10.查询奖金少于100或者没有获得奖金的员工的信息mysql> select * from emp where comm is null or comm < 100; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+-----------+------+------------+------+------+--------+ 11 rows in set (0.00 sec)2.11.MySQL LEFT函数【语法】LEFT()函数是一个字符串函数，它返回具有指定长度的字符串的左边部分。LEFT()函数的语法LEFT(str,length);LEFT()函数接受两个参数：str是要提取子字符串的字符串。 length是一个正整数，指定将从左边返回的字符数。 LEFT()函数返回str字符串中最左边的长度字符。如果str或length参数为NULL，则返回NULL值。如果length为0或为负，则LEFT函数返回一个空字符串。如果length大于str字符串的长度，则LEFT函数返回整个str字符串。mysql> SELECT LEFT('MySQL LEFT', 5); +-----------------------+ | LEFT('MySQL LEFT', 5) | +-----------------------+ | MySQL | +-----------------------+ 1 row in set (0.00 sec)2.12.查询姓名以A、B、S开头的员工的信息【LEFT函数】LEFT(ename,1) 表示取名字的第一个字符 IN ('A','B','C') 取出姓名第一个字符与之相匹配的员工信息mysql> select * from emp where ename like 'A%' or ename like 'B%' or ename like 'C%'; +-------+-------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | +-------+-------+----------+------+------------+------+------+--------+ 4 rows in set (0.01 sec) mysql> select * from emp WHERE LEFT(ename,1) IN ('A','B','C'); +-------+-------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | +-------+-------+----------+------+------------+------+------+--------+ 4 rows in set (0.00 sec)2.13.查询找到姓名长度为6个字符的员工的信息【length函数】mysql> select * from emp where length(ename) = 6; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+----------+------+------------+------+------+--------+ 3 rows in set (0.00 sec)2.14.查询姓名中不包含R字符的员工信息【not like】mysql> select * from emp where ename not like '%R%'; +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | +-------+-------+-----------+------+------------+------+------+--------+ 8 rows in set (0.00 sec) mysql> select * from emp where ename like '%R%'; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+----------+------+------------+------+------+--------+ 6 rows in set (0.00 sec)2.15.查询员工的详细信息并按姓名排序【asc、desc】mysql> select * from emp order by ename asc; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | +-------+--------+-----------+------+------------+------+------+--------+ 14 rows in set (0.00 sec) mysql> select * from emp order by ename desc; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | +-------+--------+-----------+------+------------+------+------+--------+ 14 rows in set (0.00 sec)2.16.查询员工的信息并按工作岗位降序、工资升序排列mysql> select * from emp order by job desc,sal asc; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | +-------+--------+-----------+------+------------+------+------+--------+ 14 rows in set (0.00 sec)2.17.计算员工的日薪(按30天计)【ROUND】mysql round()函数的第二个参数表示需要保留的小数位数，如果不指定第二个参数，则默认保留零位小数。如果第二个参数为负数，则会将数字整数部分进行四舍五入。ROUND(sal/30,2)，2表示小数点后面两位后面的数进行四舍五入ROUND(sal/30,-1) ，-1表示小数点前面一位例如ROUND(316.15926,-1)，小数点的前面一位6前面的2保留，然后6进1，结果为320mysql> select ROUND(314.15926,2); +--------------------+ | ROUND(314.15926,2) | +--------------------+ | 314.16 | +--------------------+ 1 row in set (0.00 sec) mysql> select ROUND(314.15926,-1); +---------------------+ | ROUND(314.15926,-1) | +---------------------+ | 310 | +---------------------+ 1 row in set (0.00 sec) mysql> select ROUND(316.15926,-1); +---------------------+ | ROUND(316.15926,-1) | +---------------------+ | 320 | +---------------------+ 1 row in set (0.00 sec) mysql> select ename 员工姓名,sal 月薪,ROUND(sal/30,2) 日薪 from emp; +--------------+--------+--------+ | 员工姓名 | 月薪 | 日薪 | +--------------+--------+--------+ | SMITH | 800 | 26.67 | | ALLEN | 1600 | 53.33 | | WARD | 1250 | 41.67 | | JONES | 2975 | 99.17 | | MARTIN | 1250 | 41.67 | | BLAKE | 2850 | 95.00 | | CLARK | 2450 | 81.67 | | SCOTT | 3000 | 100.00 | | KING | 5000 | 166.67 | | TURNER | 1500 | 50.00 | | ADAMS | 1100 | 36.67 | | JAMES | 950 | 31.67 | | FORD | 3000 | 100.00 | | MILLER | 1300 | 43.33 | +--------------+--------+--------+ 14 rows in set (0.00 sec)2.18.查询姓名中包含字符A的员工的信息mysql> select * from emp where ename like '%A%'; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | +-------+--------+----------+------+------------+------+------+--------+ 7 rows in set (0.00 sec)2.19.查询拥有员工的部门的部门名称和部门编号【自连接 inner join】-- 查询拥有员工的部门的部门名称和部门编号【自连接 inner join】 -- INNER JOIN ... ON ... -- 表结构: emp,dept -- 连接条件: emp.deptno = dept.deptno select distinct dname 部门名称,dept.deptno 部门编号 from dept inner join emp on dept.deptno = emp.deptno;mysql> select distinct dname 部门名称,dept.deptno 部门编号 -> from dept -> inner join emp -> on dept.deptno = emp.deptno; +--------------+--------------+ | 部门名称 | 部门编号 | +--------------+--------------+ | RESEARCH | 20 | | SALES | 30 | | ACCOUNTING | 10 | +--------------+--------------+ 3 rows in set (0.00 sec)2.20.查询工资多于smith的员工信息mysql> select * from emp where ename = 'smith'; +-------+-------+-------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | +-------+-------+-------+------+------------+------+------+--------+ 1 row in set (0.00 sec) mysql> select * from emp where sal > (select sal from emp where ename = 'smith'); +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+-----------+------+------------+------+------+--------+ 13 rows in set (0.00 sec)2.21.查询员工和及其所属经理的姓名(同一个部门，job=manager)-- 查询员工和及其所属经理的姓名(同一个部门，job=manager) -- INNER JOIN ... ON ... -- 表结构: emp e,emp t -- 连接条件: e.deptno = t.deptno -- where: e.job!='manager' and t.job='manager' select e.*,t.ename 经理姓名 from emp e inner join emp t on e.deptno = t.deptno where e.job!='manager' and t.job='manager' order by e.empno asc;mysql> select e.*,t.ename 经理姓名 -> from emp e -> inner join emp t -> on e.deptno = t.deptno -> where e.job!='manager' and t.job='manager' -> order by e.empno asc; +-------+--------+-----------+------+------------+------+------+--------+--------------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | 经理姓名 | +-------+--------+-----------+------+------------+------+------+--------+--------------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | JONES | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | BLAKE | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | BLAKE | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | BLAKE | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | JONES | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | CLARK | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | BLAKE | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | JONES | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | BLAKE | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | JONES | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | CLARK | +-------+--------+-----------+------+------------+------+------+--------+--------------+ 11 rows in set (0.00 sec)2.22.DATEDIFF函数与TIMESTAMPDIFF函数【语法】MySQL DATEDIFF()函数是MySQL中常见的日期函数之一，它主要用于计算两个日期之间的差值，单位可以是天、周、月、季度和年。DATEDIFF(date1, date2)其中，date1和date2是两个日期，可以是日期型、时间型或时间戳型数据。-- 1 SELECT DATEDIFF('2022-04-30','2022-04-29'); -- 0 SELECT DATEDIFF('2022-04-30','2022-04-30'); -- -1 SELECT DATEDIFF('2022-04-29','2022-04-30'); -- 0 SELECT DATEDIFF('2022-04-30','2022-04-30 14:00:00'); -- 1 SELECT DATEDIFF('2022-04-30 13:00:00','2022-04-29 14:00:00'); -- 10 SELECT DATEDIFF('2017-06-25 09:34:21', '2017-06-15'); -- -2 SELECT DATEDIFF('2022-04-28', '2022-04-30'); -- 2 SELECT TIMESTAMPDIFF(DAY,'2022-04-28', '2022-04-30');DATEDIFF函数与TIMESTAMPDIFF函数的区别DATEDIFF函数仅用于返回两个日期的天数，TIMESTAMPDIFF函数用于返回计算两个日期指定单位的时间差(指定单位可以是年，季度，月，星期，天数，小时，分钟，秒等等)对日期差值的计算方式相反DATEDIFF函数的语法格式: DATEDIFF(start,end)DATEDIFF函数返回start - end的计算结果TIMESTAMPDIFF函数的语法格式: TIMESTAMPDIFF(DAY,start,end)TIMESTAMPDIFF函数返回end - start的计算结果2.23.查询员工雇佣日期早于其经理雇佣日期的员工及其经理姓名-- 查询雇佣日期早于其经理雇佣日期的员工及其经理姓名 -- INNER JOIN ... ON ... -- 表结构: emp e,emp t -- 连接条件: e.deptno = t.deptno -- where条件: e.job!='manager' and t.job='manager' and datediff(e.hiredate,t.hiredate)<0 select e.ename 员工姓名,e.hiredate 员工雇佣日期,t.ename 经理姓名,t.hiredate 经理雇佣日期 from emp e inner join emp t on e.deptno = t.deptno where e.job!='manager' and t.job='manager' and datediff(e.hiredate,t.hiredate)<0;mysql> select e.ename 员工姓名,e.hiredate 员工雇佣日期,t.ename 经理姓名,t.hiredate 经理雇佣日期 -> from emp e -> inner join emp t -> on e.deptno = t.deptno -> where e.job!='manager' and t.job='manager' and datediff(e.hiredate,t.hiredate)<0; +--------------+--------------------+--------------+--------------------+ | 员工姓名 | 员工雇佣日期 | 经理姓名 | 经理雇佣日期 | +--------------+--------------------+--------------+--------------------+ | SMITH | 1980-12-17 | JONES | 1981-04-02 | | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | | WARD | 1981-02-22 | BLAKE | 1981-05-01 | | MILLER | 1981-01-23 | CLARK | 1981-06-09 | +--------------+--------------------+--------------+--------------------+ 4 rows in set (0.00 sec)2.24.查询员工姓名及其所在的部门名称-- 查询员工姓名及其所在的部门名称 -- INNER JOIN ... ON ... -- 表结构: emp e,dept d -- 连接条件: e.deptno = d.deptno select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno; select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;mysql> select e.ename,d.dname -> from emp e -> inner join dept d -> on e.deptno = d.deptno; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.00 sec)2.25.查询工作为clerk的员工姓名及其所在部门名称-- 查询工作为clerk的员工姓名及其所在部门名称 -- INNER JOIN ... ON ... -- 表结构: emp e,dept d -- 连接条件: e.deptno = d.deptno -- where条件: e.job = 'clerk' select e.ename 员工姓名,d.dname 部门名称 from emp e inner join dept d on e.deptno = d.deptno where e.job = 'clerk';mysql> select e.ename 员工姓名,d.dname 部门名称 -> from emp e -> inner join dept d -> on e.deptno = d.deptno -> where e.job = 'clerk'; +--------------+--------------+ | 员工姓名 | 部门名称 | +--------------+--------------+ | SMITH | RESEARCH | | ADAMS | RESEARCH | | JAMES | SALES | | MILLER | ACCOUNTING | +--------------+--------------+ 4 rows in set (0.00 sec)2.26.查询各部门编号及其部门中的最低工资【group by】mysql> select deptno,min(sal) from emp group by deptno; +--------+----------+ | deptno | min(sal) | +--------+----------+ | 10 | 1300 | | 20 | 800 | | 30 | 950 | +--------+----------+ 3 rows in set (0.01 sec)2.27.查询销售部sales的所有员工的姓名-- 查询销售部sales的所有员工的姓名 -- INNER JOIN ... ON ... -- 表结构: emp e,dept d -- 连接条件: e.deptno = d.deptno -- where条件: d.dname = 'sales' select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno where d.dname = 'sales';mysql> select e.ename,d.dname -> from emp e -> inner join dept d -> on e.deptno = d.deptno -> where d.dname = 'sales'; +--------+-------+ | ename | dname | +--------+-------+ | ALLEN | SALES | | WARD | SALES | | MARTIN | SALES | | BLAKE | SALES | | TURNER | SALES | | JAMES | SALES | +--------+-------+ 6 rows in set (0.00 sec)2.28.子查询【语法】子查询是嵌套在一个select语句中的另一个select语句。当需要从一个表中检索信息，检索条件值又是来自该表本身的内部数据时，子查询非常有用。子查询可以嵌入以下SQL子句中：where子句、having子句和from子句。where子句使用单行子查询返回结果要采用单行比较运算符（=、>、>=、<、<=、<>）说明：子查询要用括号括起来；将子查询放在比较运算符的右边；不要在子查询中使用order by子句，select语句中只能有一个order by子句，并且它只能是主select语句的最后一个子句。2.29.列出薪金高于公司平均薪金的所有员工，要求使用子查询实现【子查询】mysql> select * from emp where sal > (select avg(sal) from emp); +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | +-------+-------+-----------+------+------------+------+------+--------+ 6 rows in set (0.00 sec)2.29.查询工资比编号为7566雇员工资高的雇员姓名select ename from emp where sal > (select sal from emp where empno = 7566) order by ename;mysql> select ename -> from emp -> where sal > (select sal from emp where empno = 7566) -> order by ename; +-------+ | ename | +-------+ | FORD | | KING | | SCOTT | +-------+ 3 rows in set (0.00 sec)2.30.显示和雇员scott同部门的雇员姓名、工资和部门编号select ename,sal,deptno from emp where deptno = (select deptno from emp where ename = 'scott');mysql> select ename,sal,deptno -> from emp -> where deptno = (select deptno from emp where ename = 'scott'); +-------+------+--------+ | ename | sal | deptno | +-------+------+--------+ | SMITH | 800 | 20 | | JONES | 2975 | 20 | | SCOTT | 3000 | 20 | | ADAMS | 1100 | 20 | | FORD | 3000 | 20 | +-------+------+--------+ 5 rows in set (0.00 sec)2.31.显示和雇员SCOTT从事相同工作，并且工资大于JAMES的雇员姓名、工作岗位和工资select ename,job,sal from emp where job = (select job from emp where ename = 'scott') and sal > (select sal from emp where ename = 'james');mysql> select ename,job,sal -> from emp -> where job = (select job from emp where ename = 'scott') -> and sal > (select sal from emp where ename = 'james'); +-------+---------+------+ | ename | job | sal | +-------+---------+------+ | SCOTT | ANALYST | 3000 | | FORD | ANALYST | 3000 | +-------+---------+------+ 2 rows in set (0.00 sec)2.32.显示工资最低的雇员姓名、工作和工资select ename,job,sal from emp where sal = (select min(sal) from emp);mysql> select ename,job,sal -> from emp -> where sal = (select min(sal) from emp); +-------+-------+------+ | ename | job | sal | +-------+-------+------+ | SMITH | CLERK | 800 | +-------+-------+------+ 1 row in set (0.00 sec)2.33.显示工资最高的雇员姓名、工作和工资select ename,job,sal from emp where sal = (select max(sal) from emp);mysql> select ename,job,sal -> from emp -> where sal = (select max(sal) from emp); +-------+-----------+------+ | ename | job | sal | +-------+-----------+------+ | KING | PRESIDENT | 5000 | +-------+-----------+------+ 1 row in set (0.01 sec)2.34.显示工资高于平均工资的雇员姓名、工作、工资和工资等级select e.ename,e.job,e.sal,s.grade from emp e,salgrade s where e.sal > (select avg(sal) from emp) and e.sal between s.losal and s.hisal;mysql> select e.ename,e.job,e.sal,s.grade -> from emp e,salgrade s -> where e.sal > (select avg(sal) from emp) -> and e.sal between s.losal and s.hisal; +-------+-----------+------+-------+ | ename | job | sal | grade | +-------+-----------+------+-------+ | JONES | MANAGER | 2975 | 4 | | BLAKE | MANAGER | 2850 | 4 | | CLARK | MANAGER | 2450 | 4 | | SCOTT | ANALYST | 3000 | 4 | | FORD | ANALYST | 3000 | 4 | | KING | PRESIDENT | 5000 | 5 | +-------+-----------+------+-------+ 6 rows in set (0.00 sec)2.35.查询部门内的最低工资比20号部门的最低工资要高的部门的编号及部门内最低工资【having】-- 按部门显示部门编号、部门最低工资 select deptno 部门编号,min(sal) 最低工资 from emp group by deptno; select deptno 部门编号,min(sal) 最低工资 from emp group by deptno having min(sal)>(select min(sal) from emp where deptno = 20);mysql> select deptno 部门编号,min(sal) 最低工资 -> from emp -> group by deptno; +--------------+--------------+ | 部门编号 | 最低工资 | +--------------+--------------+ | 10 | 1300 | | 20 | 800 | | 30 | 950 | +--------------+--------------+ 3 rows in set (0.00 sec) mysql> select deptno 部门编号,min(sal) 最低工资 -> from emp -> group by deptno -> having min(sal)>(select min(sal) from emp where deptno = 20); +--------------+--------------+ | 部门编号 | 最低工资 | +--------------+--------------+ | 10 | 1300 | | 30 | 950 | +--------------+--------------+ 2 rows in set (0.00 sec)2.36.查询和SMITH从事相同工作的雇员姓名和工作select ename,job from emp where job = (select job from emp where ename = 'smith');mysql> select ename,job -> from emp -> where job = (select job from emp where ename = 'smith'); +--------+-------+ | ename | job | +--------+-------+ | SMITH | CLERK | | ADAMS | CLERK | | JAMES | CLERK | | MILLER | CLERK | +--------+-------+ 4 rows in set (0.00 sec)2.37.查询有下属的雇员姓名、工作、工资、部门号select ename,job,sal,deptno,mgr from emp where empno in (select mgr from emp where mgr is not NULL);mysql> select ename,job,sal,deptno,mgr -> from emp -> where empno in (select mgr from emp where mgr is not NULL); +-------+-----------+------+--------+------+ | ename | job | sal | deptno | mgr | +-------+-----------+------+--------+------+ | FORD | ANALYST | 3000 | 20 | 7566 | | BLAKE | MANAGER | 2850 | 30 | 7839 | | KING | PRESIDENT | 5000 | 10 | NULL | | JONES | MANAGER | 2975 | 20 | 7839 | | SCOTT | ANALYST | 3000 | 20 | 7566 | | CLARK | MANAGER | 2450 | 10 | 7839 | +-------+-----------+------+--------+------+ 6 rows in set (0.00 sec)2.38.查询没有下属的雇员姓名、工作、工资和部门号select ename,job,sal,deptno,mgr from emp where empno not in (select mgr from emp where mgr is not null);mysql> select ename,job,sal,deptno,mgr -> from emp -> where empno not in (select mgr from emp where mgr is not null); +--------+----------+------+--------+------+ | ename | job | sal | deptno | mgr | +--------+----------+------+--------+------+ | SMITH | CLERK | 800 | 20 | 7902 | | ALLEN | SALESMAN | 1600 | 30 | 7698 | | WARD | SALESMAN | 1250 | 30 | 7698 | | MARTIN | SALESMAN | 1250 | 30 | 7698 | | TURNER | SALESMAN | 1500 | 30 | 7698 | | ADAMS | CLERK | 1100 | 20 | 7788 | | JAMES | CLERK | 950 | 30 | 7698 | | MILLER | CLERK | 1300 | 10 | 7782 | +--------+----------+------+--------+------+2.39.查询各部门中工资最低的员工姓名、工作、工资和部门号select ename,job,sal,deptno from emp where sal in (select min(sal) from emp group by deptno);mysql> select ename,job,sal,deptno -> from emp -> where sal in (select min(sal) from emp group by deptno); +--------+-------+------+--------+ | ename | job | sal | deptno | +--------+-------+------+--------+ | SMITH | CLERK | 800 | 20 | | JAMES | CLERK | 950 | 30 | | MILLER | CLERK | 1300 | 10 | +--------+-------+------+--------+ 3 rows in set (0.01 sec)2.40.查询部门中工资最高的员工姓名、工作、工资和部门号select ename,job,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);mysql> select ename,job,sal,deptno -> from emp -> where sal in (select max(sal) from emp group by deptno); +-------+-----------+------+--------+ | ename | job | sal | deptno | +-------+-----------+------+--------+ | BLAKE | MANAGER | 2850 | 30 | | SCOTT | ANALYST | 3000 | 20 | | KING | PRESIDENT | 5000 | 10 | | FORD | ANALYST | 3000 | 20 | +-------+-----------+------+--------+ 4 rows in set (0.00 sec)2.41.查询与销售部门（SALES）工作岗位相同的其它部门雇员姓名、工作、工资和部门名称select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno and d.dname<>'sales' and job in (select distinct e.job from emp e,dept d where e.deptno = d.deptno and d.dname='sales');mysql> select e.ename,e.job,e.sal,d.dname -> from emp e,dept d -> where e.deptno = d.deptno -> and d.dname<>'sales' -> and job in (select distinct e.job from emp e,dept d where e.deptno = d.deptno and d.dname='sales'); +--------+---------+------+------------+ | ename | job | sal | dname | +--------+---------+------+------------+ | SMITH | CLERK | 800 | RESEARCH | | JONES | MANAGER | 2975 | RESEARCH | | CLARK | MANAGER | 2450 | ACCOUNTING | | ADAMS | CLERK | 1100 | RESEARCH | | MILLER | CLERK | 1300 | ACCOUNTING | +--------+---------+------+------------+ 5 rows in set (0.00 sec)2.42.使用any运算符的多行子查询【语法】Any运算符将和内部查询返回的结果逐个比较，与单行操作符配合使用。<any：表示比子查询返回结果中的最大值小；=any：表示可以是子查询返回结果中的任意一个值；\>any：表示比子查询返回结果中的最小值大。2.43.查询工资低于某个文员（CLERK）雇员工资，但不从事文员工作的员工编号、姓名、工作岗位和工资select empno,ename,job,sal from emp where sal <any (select sal from emp where job = 'clerk') and job<>'clerk';mysql> select empno,ename,job,sal -> from emp -> where sal <any (select sal from emp where job = 'clerk') -> and job<>'clerk'; +-------+--------+----------+------+ | empno | ename | job | sal | +-------+--------+----------+------+ | 7521 | WARD | SALESMAN | 1250 | | 7654 | MARTIN | SALESMAN | 1250 | +-------+--------+----------+------+ 2 rows in set (0.00 sec)2.44.查询工资高于某个文员（CLERK）雇员工资，但不从事文员工作的员工编号、姓名、工作岗位和工资select empno,ename,job,sal from emp where sal >any (select sal from emp where job = 'clerk') and job<>'clerk';mysql> select empno,ename,job,sal -> from emp -> where sal >any (select sal from emp where job = 'clerk') -> and job<>'clerk'; +-------+--------+-----------+------+ | empno | ename | job | sal | +-------+--------+-----------+------+ | 7499 | ALLEN | SALESMAN | 1600 | | 7521 | WARD | SALESMAN | 1250 | | 7566 | JONES | MANAGER | 2975 | | 7654 | MARTIN | SALESMAN | 1250 | | 7698 | BLAKE | MANAGER | 2850 | | 7782 | CLARK | MANAGER | 2450 | | 7788 | SCOTT | ANALYST | 3000 | | 7839 | KING | PRESIDENT | 5000 | | 7844 | TURNER | SALESMAN | 1500 | | 7902 | FORD | ANALYST | 3000 | +-------+--------+-----------+------+ 10 rows in set (0.00 sec)2.45.查询工资高于部门编号是30的部门内某个雇员工资，但不在该部门工作的员工姓名、工作岗位、工资和部门编号select empno,ename,job,sal from emp where sal >any (select sal from emp where deptno = 30) and deptno<>30;mysql> select empno,ename,job,sal -> from emp -> where sal >any (select sal from emp where deptno = 30) -> and deptno<>30; +-------+--------+-----------+------+ | empno | ename | job | sal | +-------+--------+-----------+------+ | 7566 | JONES | MANAGER | 2975 | | 7782 | CLARK | MANAGER | 2450 | | 7788 | SCOTT | ANALYST | 3000 | | 7839 | KING | PRESIDENT | 5000 | | 7876 | ADAMS | CLERK | 1100 | | 7902 | FORD | ANALYST | 3000 | | 7934 | MILLER | CLERK | 1300 | +-------+--------+-----------+------+ 7 rows in set (0.00 sec)2.46.查询工资低于部门名称是SALES的部门内某个雇员工资，但不在该部门工作的员工姓名、工作岗位、工资和部门编号和部门名称select e.empno,e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno and d.dname<>'sales' and e.sal <any (select distinct e.sal from emp e,dept d where e.deptno = d.deptno and dname = 'sales');mysql> select e.empno,e.ename,e.job,e.sal,d.dname -> from emp e,dept d -> where e.deptno = d.deptno -> and d.dname<>'sales' -> and e.sal <any (select distinct e.sal from emp e,dept d where e.deptno = d.deptno and dname = 'sales'); +-------+--------+---------+------+------------+ | empno | ename | job | sal | dname | +-------+--------+---------+------+------------+ | 7369 | SMITH | CLERK | 800 | RESEARCH | | 7782 | CLARK | MANAGER | 2450 | ACCOUNTING | | 7876 | ADAMS | CLERK | 1100 | RESEARCH | | 7934 | MILLER | CLERK | 1300 | ACCOUNTING | +-------+--------+---------+------+------------+ 4 rows in set (0.00 sec)2.47.使用all运算符的多行子查询【语法】All运算符将和内部查询返回的每个结果比较。\>all：比最大的大；<all：比最小的小。2.48.查询高于所有部门平均工资的雇员姓名、工作、工资和部门编号select ename,job,sal,deptno from emp where sal >all (select avg(sal) from emp group by deptno);mysql> select ename,job,sal,deptno -> from emp -> where sal >all (select avg(sal) from emp group by deptno); +-------+-----------+------+--------+ | ename | job | sal | deptno | +-------+-----------+------+--------+ | JONES | MANAGER | 2975 | 20 | | SCOTT | ANALYST | 3000 | 20 | | KING | PRESIDENT | 5000 | 10 | | FORD | ANALYST | 3000 | 20 | +-------+-----------+------+--------+ 4 rows in set (0.00 sec)2.49.查询低于所有部门平均工资的雇员姓名、工作、工资和部门编号select ename,job,sal,deptno from emp where sal <all (select avg(sal) from emp group by deptno);mysql> select ename,job,sal,deptno -> from emp -> where sal <all (select avg(sal) from emp group by deptno); +--------+----------+------+--------+ | ename | job | sal | deptno | +--------+----------+------+--------+ | SMITH | CLERK | 800 | 20 | | WARD | SALESMAN | 1250 | 30 | | MARTIN | SALESMAN | 1250 | 30 | | TURNER | SALESMAN | 1500 | 30 | | ADAMS | CLERK | 1100 | 20 | | JAMES | CLERK | 950 | 30 | | MILLER | CLERK | 1300 | 10 | +--------+----------+------+--------+ 7 rows in set (0.00 sec)2.50.查询工资高于部门编号为30的部门内所有员工工资的雇员姓名、工作、工资和部门编号select ename,job,sal,deptno from emp where sal >all (select distinct sal from emp where deptno = 30);mysql> select ename,job,sal,deptno -> from emp -> where sal >all (select distinct sal from emp where deptno = 30); +-------+-----------+------+--------+ | ename | job | sal | deptno | +-------+-----------+------+--------+ | JONES | MANAGER | 2975 | 20 | | SCOTT | ANALYST | 3000 | 20 | | KING | PRESIDENT | 5000 | 10 | | FORD | ANALYST | 3000 | 20 | +-------+-----------+------+--------+ 4 rows in set (0.00 sec)2.51.查询工资等级为4的雇员姓名、工作、工资、部门编号和工资等级，同时满足该雇员工资高于部门编号为30的部门内所有员工工资select e.ename,e.job,e.sal,e.deptno,s.grade from emp e,salgrade s where s.grade = 4 and e.sal between s.losal and s.hisal and e.sal >all (select distinct sal from emp where deptno = 30);mysql> select e.ename,e.job,e.sal,e.deptno,s.grade -> from emp e,salgrade s -> where s.grade = 4 -> and e.sal between s.losal and s.hisal -> and e.sal >all (select distinct sal from emp where deptno = 30); +-------+---------+------+--------+-------+ | ename | job | sal | deptno | grade | +-------+---------+------+--------+-------+ | JONES | MANAGER | 2975 | 20 | 4 | | SCOTT | ANALYST | 3000 | 20 | 4 | | FORD | ANALYST | 3000 | 20 | 4 | +-------+---------+------+--------+-------+ 3 rows in set (0.00 sec)2.52.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号select max(s.grade),e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno; select e.ename,e.job,e.sal,s.grade,e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal and (s.grade,e.deptno) in ( select max(s.grade),e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno) order by e.deptno;mysql> select max(s.grade),e.deptno -> from emp e,salgrade s -> where e.sal between s.losal and s.hisal -> group by e.deptno; +--------------+--------+ | max(s.grade) | deptno | +--------------+--------+ | 5 | 10 | | 4 | 20 | | 4 | 30 | +--------------+--------+ 3 rows in set (0.00 sec) mysql> select e.ename,e.job,e.sal,s.grade,e.deptno -> from emp e,salgrade s -> where e.sal between s.losal and s.hisal -> and (s.grade,e.deptno) in ( -> select max(s.grade),e.deptno -> from emp e,salgrade s -> where e.sal between s.losal and s.hisal -> group by e.deptno) -> order by e.deptno; +-------+-----------+------+-------+--------+ | ename | job | sal | grade | deptno | +-------+-----------+------+-------+--------+ | KING | PRESIDENT | 5000 | 5 | 10 | | FORD | ANALYST | 3000 | 4 | 20 | | SCOTT | ANALYST | 3000 | 4 | 20 | | JONES | MANAGER | 2975 | 4 | 20 | | BLAKE | MANAGER | 2850 | 4 | 30 | +-------+-----------+------+-------+--------+ 5 rows in set (0.00 sec)2.53.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门名称select max(s.grade),e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno; select e.ename,e.job,e.sal,s.grade,d.dname from emp e,dept d,salgrade s where e.sal between s.losal and s.hisal and e.deptno = d.deptno and (s.grade,e.deptno) in ( select max(s.grade),e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno) order by e.deptno;mysql> select e.ename,e.job,e.sal,s.grade,d.dname -> from emp e,dept d,salgrade s -> where e.sal between s.losal and s.hisal -> and e.deptno = d.deptno -> and (s.grade,e.deptno) in ( -> select max(s.grade),e.deptno -> from emp e,salgrade s -> where e.sal between s.losal and s.hisal -> group by e.deptno) -> order by e.deptno; +-------+-----------+------+-------+------------+ | ename | job | sal | grade | dname | +-------+-----------+------+-------+------------+ | KING | PRESIDENT | 5000 | 5 | ACCOUNTING | | JONES | MANAGER | 2975 | 4 | RESEARCH | | SCOTT | ANALYST | 3000 | 4 | RESEARCH | | FORD | ANALYST | 3000 | 4 | RESEARCH | | BLAKE | MANAGER | 2850 | 4 | SALES | +-------+-----------+------+-------+------------+ 5 rows in set (0.00 sec)2.54.查询工资与30部门中任意一个雇员的工资相等，同时奖金也与30部门中任意一个雇员奖金相等的雇员姓名、工资、奖金、部门编号，但该雇员不是来自30号部门select ename,sal,comm,deptno from emp where sal in (select sal from emp where deptno = 30) and comm in (select comm from emp where deptno = 30 and comm is not null) and comm is not null and comm > 0;mysql> select ename,sal,comm,deptno -> from emp -> where sal in (select sal from emp where deptno = 30) -> and comm in (select comm from emp where deptno = 30 and comm is not null) -> and comm is not null -> and comm > 0; +--------+------+------+--------+ | ename | sal | comm | deptno | +--------+------+------+--------+ | ALLEN | 1600 | 300 | 30 | | WARD | 1250 | 500 | 30 | | MARTIN | 1250 | 1400 | 30 | +--------+------+------+--------+ 3 rows in set (0.00 sec)2.55.查询在纽约（NEW YORK）工作的雇员姓名、工作岗位、工资和奖金select ename,job,sal,comm from emp where deptno = (select deptno from dept where loc = 'new york');mysql> select ename,job,sal,comm -> from emp -> where deptno = (select deptno from dept where loc = 'new york'); +--------+-----------+------+------+ | ename | job | sal | comm | +--------+-----------+------+------+ | CLARK | MANAGER | 2450 | NULL | | KING | PRESIDENT | 5000 | NULL | | MILLER | CLERK | 1300 | NULL | +--------+-----------+------+------+ 3 rows in set (0.00 sec)2.56.显示工资高于部门平均工资的雇员姓名、工作、工资和部门号-- 在from子句中使用子查询时，必须给子查询指定别名 select ename,job,sal,emp.deptno from emp,(select deptno,avg(sal) avgsal from emp group by deptno) t where emp.deptno = t.deptno and sal > t.avgsal;mysql> select ename,job,sal,emp.deptno -> from emp,(select deptno,avg(sal) avgsal from emp group by deptno) t -> where emp.deptno = t.deptno -> and sal > t.avgsal; +-------+-----------+------+--------+ | ename | job | sal | deptno | +-------+-----------+------+--------+ | ALLEN | SALESMAN | 1600 | 30 | | JONES | MANAGER | 2975 | 20 | | BLAKE | MANAGER | 2850 | 30 | | SCOTT | ANALYST | 3000 | 20 | | KING | PRESIDENT | 5000 | 10 | | FORD | ANALYST | 3000 | 20 | +-------+-----------+------+--------+ 6 rows in set (0.00 sec)2.57.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号select max(s.grade) maxgrade,e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno; select e.ename,e.job,e.sal,s.grade,e.deptno from emp e,salgrade s,( select max(s.grade) maxgrade,e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno) p where e.sal between s.losal and s.hisal and e.deptno = p.deptno and s.grade = p.maxgrade order by e.deptno;mysql> select e.ename,e.job,e.sal,s.grade,e.deptno -> from emp e,salgrade s,( -> select max(s.grade) maxgrade,e.deptno -> from emp e,salgrade s -> where e.sal between s.losal and s.hisal -> group by e.deptno) p -> where e.sal between s.losal and s.hisal -> and e.deptno = p.deptno -> and s.grade = p.maxgrade -> order by e.deptno; +-------+-----------+------+-------+--------+ | ename | job | sal | grade | deptno | +-------+-----------+------+-------+--------+ | KING | PRESIDENT | 5000 | 5 | 10 | | SCOTT | ANALYST | 3000 | 4 | 20 | | JONES | MANAGER | 2975 | 4 | 20 | | FORD | ANALYST | 3000 | 4 | 20 | | BLAKE | MANAGER | 2850 | 4 | 30 | +-------+-----------+------+-------+--------+ 5 rows in set (0.00 sec)2.58.查询平均工资最低的工种名称及其平均工资-- 1.按工种查询平均工资 select job,avg(sal) from emp group by job; -- 2.按工种查询最低平均工资 select min(avgsal) from (select avg(sal) avgsal from emp e group by e.job) as t; -- 3.使用having子句把（2）作为（1）子查询 select job,avg(sal) from emp group by job having avg(sal) = (select min(avgsal) from (select avg(sal) avgsal from emp e group by e.job) as t);mysql> select job,avg(sal) from emp group by job; +-----------+--------------------+ | job | avg(sal) | +-----------+--------------------+ | ANALYST | 3000 | | CLERK | 1037.5 | | MANAGER | 2758.3333333333335 | | PRESIDENT | 5000 | | SALESMAN | 1400 | +-----------+--------------------+ 5 rows in set (0.00 sec) mysql> select min(avgsal) from (select avg(sal) avgsal from emp e group by e.job) as t; +-------------+ | min(avgsal) | +-------------+ | 1037.5 | +-------------+ 1 row in set (0.00 sec) mysql> select job,avg(sal) -> from emp -> group by job -> having avg(sal) = (select min(avgsal) from (select avg(sal) avgsal from emp e group by e.job) as t); +-------+----------+ | job | avg(sal) | +-------+----------+ | CLERK | 1037.5 | +-------+----------+ 1 row in set (0.00 sec)2.59.查询与部门编号为30的员工工资水平相同的员工信息select avg(sal) from emp where deptno = 30; select grade from salgrade where (select avg(sal) from emp where deptno = 30) between losal and hisal; select * from emp inner join salgrade s on sal between s.losal and s.hisal where s.grade = (select grade from salgrade where (select avg(sal) from emp where deptno = 30) between losal and hisal);mysql> select avg(sal) from emp where deptno = 30; +--------------------+ | avg(sal) | +--------------------+ | 1566.6666666666667 | +--------------------+ 1 row in set (0.00 sec) mysql> select grade from salgrade where (select avg(sal) from emp where deptno = 30) between losal and hisal; +-------+ | grade | +-------+ | 3 | +-------+ 1 row in set (0.00 sec) mysql> select * -> from emp -> inner join salgrade s -> on sal between s.losal and s.hisal -> where s.grade = -> (select grade from salgrade where (select avg(sal) from emp where deptno = 30) between losal and hisal); +-------+--------+----------+------+------------+------+------+--------+-------+-------+-------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | grade | losal | hisal | +-------+--------+----------+------+------------+------+------+--------+-------+-------+-------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 3 | 1401 | 2000 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 3 | 1401 | 2000 | +-------+--------+----------+------+------------+------+------+--------+-------+-------+-------+ 2 rows in set (0.00 sec)2.60.查询部门号、部门名、部门所在位置及其每个部门的员工总数select d.deptno,d.dname,d.loc,count(e.deptno) from emp e inner join dept d on e.deptno = d.deptno group by d.deptno;mysql> select d.deptno,d.dname,d.loc,count(e.deptno) -> from emp e -> inner join dept d -> on e.deptno = d.deptno -> group by d.deptno; +--------+------------+----------+-----------------+ | deptno | dname | loc | count(e.deptno) | +--------+------------+----------+-----------------+ | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | +--------+------------+----------+-----------------+ 3 rows in set (0.00 sec)2.61.计算员工的年薪并且以年薪排序select (sal*12+(if(comm is null,0,comm))) from emp; select e.*,(select (sal*12+(if(comm is null,0,comm))) y_sal from emp where e.empno = emp.empno) y_sal from emp e order by y_sal asc;mysql> select e.*,(select (sal*12+(if(comm is null,0,comm))) y_sal from emp where e.empno = emp.empno) y_sal -> from emp e -> order by y_sal asc; +-------+--------+-----------+------+------------+------+------+--------+-------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | y_sal | +-------+--------+-----------+------+------------+------+------+--------+-------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 9600 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 11400 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | 13200 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 15500 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | 15600 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 16400 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 18000 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 19500 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 29400 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 34200 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 35700 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | 36000 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 36000 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 60000 | +-------+--------+-----------+------+------------+------+------+--------+-------+ 14 rows in set (0.00 sec)endl