[

第五章 1.MySQL-习题答案

1. DDL语句以及插入数据练习

  1. 创建一张学生（Student）表，属性如下：

    1. 学生编号 SID 整数 主键 自增从1000开始
    2. 学生姓名 SNAME 字符串 长度为20 不为空
    3. 学生年龄 BIRTHDAY 日期 
    4. 学生性别 SEX 字符串 长度为1
    1. create table if not exists student(
    2.  sid int primary key auto_increment,
    3.  sname varchar(20) not null,
    4.  birthday date,
    5.  sex char(1)
    6. ) auto_increment=1001;

  2. 向学生表插入数据

    +------+-------+------------+------+
| sid  | sname | birthday   | sex  |
+------+-------+------------+------+
| 1001 | 张三  | 1990-10-10 | 男   |
| 1002 | 李四  | 1981-10-10 | 男   |
| 1003 | 王五  | 1981-11-10 | 女   |
| 1004 | 赵六  | 1988-10-10 | 男   |
| 1005 | 孙七  | 1989-01-10 | 女   |
| 1006 | 周八  | 1990-10-10 | 男   |
| 1007 | 张三  | 1990-06-10 | 女   |
+------+-------+------------+------+

    insert into student values(null,'张三','1990-10-10','男');
insert into student values(null,'李四','1981-10-10','男');
insert into student values(null,'王五','1981-11-10','女');
insert into student values(null,'赵六','1988-10-10','男');
insert into student values(null,'孙七','1989-01-10','女');
insert into student values(null,'周八','1990-10-10','男');
insert into student values(null,'张三','1990-06-10','女');

  3. 创建教师表（Teacher）

    教师编号 TID 整数 主键 自增
教师姓名 TNAME 字符串 长度为20 不为空

    create table if not exists teacher(
 tid int primary key auto_increment,
 tname varchar(20) not null
);

  4. 向教师表插入数据

    +-----+--------+
| tid | tname  |
+-----+--------+
|   1 | 叶平   |
|   2 | 王老师 |
|   3 | 张老师 |
|   4 | 李老师 |
|   5 | 孙老师 |
+-----+--------+

    insert into teacher(tname) values('叶平');
insert into teacher(tname) values('王老师');
insert into teacher(tname) values('张老师');
insert into teacher(tname) values('李老师');
insert into teacher(tname) values('孙老师');

  5. 创建一张课程表（Course）

    课程编号 CID 整数 主键 自增
课程名称 CNAME 字符串 长度为20 不为空
教师编号 TID 必须与教师表中的TID相符，不能为空

    create table if not exists course(
 cid int primary key auto_increment,
 cname varchar(20) not null,
 tid int not null,
 foreign key(tid) references teacher(tid)    
);

  6. 插入课程数据，注意课程与教师编号的对应关系

    +-----+----------+-----+
| cid | cname    | tid |
+-----+----------+-----+
|   1 | 企业管理 |   1 |
|   2 | 马克思   |   2 |
|   3 | UML      |   3 |
|   4 | 数据库   |   4 |
|   5 | 英语     |   5 |
|   6 | 语文     |   1 |
|   7 | 数学     |   2 |
+-----+----------+-----+

    insert into course values(null,'企业管理',1);
insert into course values(null,'马克思',2);
insert into course values(null,'UML',3);
insert into course values(null,'数据库',4);
insert into course values(null,'英语',5);
insert into course values(null,'语文',1);
insert into course values(null,'数学',2);

  7. 创建成绩表（SC）

    学生编号 SID  必须与学生表中的学生编号相符，不为空
课程编号 CID  必须与课程表中的课程编号相符，不为空
成绩 SCORE 整数 不为空
课程编号与学生编号应该联合唯一

    create table if not exists sc(
 sid int not null,
 cid int not null,    
 score tinyint not null,
 foreign key(cid) references course(cid),
 foreign key(sid) references student(sid),
 primary key(cid,sid)
);

  8. 插入成绩数据

    +------+-----+-------+
| sid  | cid | score |
+------+-----+-------+
| 1001 |   1 |    50 |
| 1002 |   1 |    90 |
| 1003 |   1 |    59 |
| 1004 |   1 |    80 |
| 1005 |   1 |    50 |
| 1006 |   1 |    60 |
| 1007 |   1 |   100 |
| 1001 |   2 |    70 |
| 1003 |   2 |    70 |
| 1004 |   2 |    70 |
| 1005 |   2 |    50 |
| 1001 |   3 |    80 |
| 1005 |   3 |   100 |
| 1001 |   4 |    90 |
| 1002 |   4 |    55 |
| 1005 |   4 |    30 |
| 1007 |   4 |    58 |
| 1001 |   5 |    80 |
| 1001 |   6 |    80 |
| 1002 |   6 |    90 |
| 1007 |   6 |    90 |
| 1001 |   7 |   100 |
| 1002 |   7 |    80 |
| 1007 |   7 |    80 |
+------+-----+-------+

    insert into sc values(1001,1,50);
insert into sc values(1001,2,70);
insert into sc values(1001,3,80);
insert into sc values(1001,4,90);
insert into sc values(1001,5,80);
insert into sc values(1001,6,80);
insert into sc values(1001,7,100);
insert into sc values(1002,1,90);
insert into sc values(1002,4,55);
insert into sc values(1002,6,90);
insert into sc values(1002,7,80);
insert into sc values(1003,1,59);
insert into sc values(1003,2,70);
insert into sc values(1004,1,80);
insert into sc values(1004,2,70);
insert into sc values(1005,1,50);
insert into sc values(1005,2,50);
insert into sc values(1005,3,100);
insert into sc values(1005,4,30);
insert into sc values(1006,1,60);
insert into sc values(1007,1,100);
insert into sc values(1007,4,58);
insert into sc values(1007,6,90);
insert into sc values(1007,7,80);

    2. 单表查询练习

  9. 查询姓“李”的老师的个数

    select count(*) from teacher where tname like '李%';

  10. 查询男女生人数个数

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

  11. 查询同名同姓学生名单，并统计同名人数

    select sname,count(*) from student group by sname having count(*)>1;

  12. 1981年出生的学生名单

    select * from student where extract(year from birthday) = 1981;

  13. 查询平均成绩大于60分的同学的学号和平均成绩

    select sid,avg(score) from sc group by sid having avg(score)>60;

  14. 求选了课程的学生人数

    select count(distinct sid) from sc;

  15. 查询至少选修两门课程的学生学号

    select sid,count(*) from sc group by sid having count(*)>=2;

  16. 查询各科成绩最高和最低的分。以如下形式显示：课程ID，最高分，最低分

    select cid 课程ID,max(score) 最高分,min(score) 最低分 from sc group by cid;

  17. 统计每门课程的学生选修人数。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

    select cid,count(sid) from sc group by cid order by count(sid) desc,cid asc;

    以下练习针对部门员工表，请导入scott.sql的数据

  18. 打印入职时间超过38年的员工信息

    SELECT e.* FROM emp e 
 WHERE EXTRACT(YEAR FROM NOW())-EXTRACT(YEAR FROM hiredate)>=38;

  19. 把hiredate列看做是员工的生日,求本月过生日的员工

    SELECT e.* FROM emp e WHERE EXTRACT(MONTH FROM hiredate) = EXTRACT(MONTH FROM NOW());

  20. 把hiredate列看做是员工的生日,求下月过生日的员工

    SELECT e.* FROM emp e WHERE EXTRACT(MONTH FROM hiredate) = EXTRACT(MONTH FROM NOW())+1;

  21. 求1980年下半年入职的员工

    SELECT e.* FROM emp e WHERE EXTRACT(YEAR FROM hiredate) = 1980 AND  EXTRACT(MONTH FROM hiredate)>=7;

  22. 请用两种的方式查询所有名字长度为4的员工的员工编号,姓名

    select * from emp where ename like '____'; /*模糊查询性能低*/
select * from emp where length(ename) = 4; /*仅适用于英文字符的长度判断*/
select * from emp where char_length(ename) = 4;

  23. 显示各种职位的最低工资

    SELECT MIN(sal),job FROM emp GROUP BY job;

  24. 求1980年各个月入职的的员工个数

    SELECT EXTRACT(MONTH FROM hiredate), COUNT(*) FROM emp 
 WHERE EXTRACT(YEAR FROM hiredate)=1980 
 GROUP BY EXTRACT(MONTH FROM hiredate);

  25. 查询每个部门的最高工资

    SELECT MAX(sal), deptno FROM emp GROUP BY deptno;

  26. 查询每个部门,每种职位的最高工资

    SELECT MAX(sal), deptno, job FROM emp GROUP BY deptno, job;

  27. 查询各部门的总工资和平均工资

    SELECT AVG(sal), SUM(sal), deptno FROM emp e GROUP BY deptno

  28. 查询10号部门,20号部门的平均工资（尝试用多种写法）

如果条件既可以放在where，也可以放在having处，优先选择where
例如个数条件，最大值条件…只能用having

select deptno, avg(sal) from emp where deptno=10 or deptno=20 group by deptno; /*推荐写法*/
select deptno, avg(sal) from emp where deptno in(10,20) group by deptno; /*推荐写法*/
select deptno, avg(sal) from emp group by deptno having deptno in(10,20); /*不推荐*/
select deptno, avg(sal) from emp group by deptno having deptno =10 or deptno=20; /*不推荐*/

  1. 查询平均工资高于2000元的部门编号和平均工资

    SELECT AVG(sal), deptno FROM emp e GROUP BY deptno HAVING AVG(sal)>2000;

  2. 统计公司里经理的人数

    SELECT COUNT(*) FROM emp e WHERE job = 'manager';

  3. 查询工资最高的3名员工信息

    SELECT * FROM emp ORDER BY sal DESC LIMIT 3;

  4. 查询工资由高到低第6到第10的员工信息

    select * from emp order by sal desc limit 5,5;

    3. 表连接查询练习

  5. 查询李四学习的课程，考试分数，课程的授课老师

    SELECT sc.`score`, c.`cname`, t.tname
 FROM student s,sc,course c,teacher t 
 WHERE s.`sid`=sc.`sid` AND c.`cid`=sc.`cid` AND c.`tid`=t.`tid` AND s.`sname`='李四';

  6. 查询王五有哪些课程没选，显示这些课程名称

    -- 先查询王五选了哪些课
SELECT sc.`cid` FROM sc, student s WHERE sc.`sid`=s.`sid` AND s.`sname`='王五';
-- 再用 not in 排除
SELECT * FROM course WHERE cid NOT IN (
 SELECT sc.`cid` FROM sc, student s WHERE sc.`sid`=s.`sid` AND s.`sname`='王五');

  7. 查询所有同学的学号、姓名、选课数、总成绩

    -- 先查询出学号，选课数和总成绩
SELECT SUM(score),COUNT(*),sid FROM sc GROUP BY sid;
-- 再与学生表连接获得姓名
SELECT s.`sname`, t.* FROM student s, (SELECT SUM(score),COUNT(*),sid FROM sc GROUP BY sid)t 
 WHERE s.`sid` = t.sid;

  8. 查询所有课程成绩都小于等于60分的同学的学号、姓名；（解法见下面第1题）

  9. 查询没有学全所有课的同学的学号、姓名； （解法见下面第2题）
  10. 查询每门课程选修人数，格式为课程名称，人数；（解法见下面第3题）

  11. 查询出只选修了一门课程的全部学生的学号和姓名 ；

    select s.* from student s, (select sid from sc group by sid having count(*)=1) t where s.sid = t.sid;

  12. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

    SELECT c.*, t.a FROM course c, (SELECT AVG(score) a,cid FROM sc GROUP BY cid)t
 WHERE c.`cid`=t.cid ORDER BY t.a, c.cid DESC

  13. 查询学生平均成绩大于80的所有学生的学号、姓名和平均成绩

    -- 先找出平均成绩和学生编号
SELECT sid,AVG(score)a FROM sc GROUP BY sid HAVING a>80;
-- 再执行表连接
SELECT s.sname,t.* FROM student s, (SELECT sid,AVG(score)a FROM sc GROUP BY sid HAVING a>80) t
 WHERE s.`sid`=t.sid

  14. 查询课程相同且成绩相同的的学生的学号、课程号、学生成绩

    SELECT * FROM sc WHERE (sc.`cid`,sc.`score`) IN (SELECT cid, score FROM sc GROUP BY cid, score HAVING COUNT(*)>1);

  15. 查询全部学生都选修的课程的课程号和课程名 （解法见下面第4题）

  16. 查询两门以上不及格课程的同学的学号及其平均成绩（解法见下面第5题）

    4. 子查询练习

  17. 查询所有课程成绩都小于等于60分的同学的学号、姓名

    (select sid from sc group by sid having max(score)<=60) a /*1006, 1007*/
用表连接
select * from student b inner join (select sid from sc group by sid having max(score)<=60) a on b.sid=a.sid;
用in
select * from student where sid in (select sid from sc group by sid having max(score)<=60);
用取反的做法
select sid from sc where score > 60;
select * from student where sid not in(select sid from sc where score > 60);

  18. 查询没有学全所有课的同学的学号、姓名

    select count(*) from course; /*求得总课程数*/
(select sid from sc group by sid having count(*)<(select count(*) from course)) a /*求得没有学全总课程数的sid*/
select * from (select sid from sc group by sid having count(*)<(select count(*) from course))a inner join student b on a.sid=b.sid;

  19. 查询每门课程选修人数，格式为课程名称，人数

    先统计个数，再连接课程表
(select cid, count(*) from sc group by cid) a
select totalcount,cname from 
 (select cid, count(*) totalcount from sc group by cid) 
a inner join course b on a.cid=b.cid;
先连接课程表，再统计个数
select a.cid,cname,count(*) from sc a inner join course b on a.cid=b.cid group by a.cid, cname;

  20. 查询全部学生都选修的课程的课程号和课程名

    select * from (select sid from sc group by sid having count(*)=(select count(*) from course))a inner join student b on a.sid=b.sid;

  21. 查询两门以上不及格课程的同学的学号及其平均成绩

    select sid from sc where score<60 group by sid having count(*)>=2;
select sid,avg(score) from sc where sid in 
 (select sid from sc where score<60 group by sid having count(*)>=2) 
group by sid;

  22. 查询2号课程成绩比1号课程成绩低的学生的学号、姓名

    (select sid,cid,score from sc where cid=1)a
(select sid,cid,score from sc where cid=2)b
(select a.sid from (select sid,cid,score from sc where cid=1)a inner join (select sid,cid,score from sc where cid=2)b on a.sid=b.sid and a.score>b.score)c
select * from (select a.sid from (select sid,cid,score from sc where cid=1)a inner join (select sid,cid,score from sc where cid=2)b on a.sid=b.sid and a.score>b.score)c inner join student d where c.sid=d.sid;

  23. 查询学过1号课程并且也学过编号2号课程的同学的学号、姓名

    select * from (select a.sid from (select sid,cid,score from sc where cid=1)a inner join (select sid,cid,score from sc where cid=2)b on a.sid=b.sid)c inner join student d where c.sid=d.sid;

  24. 查询没学过“叶平”老师课的同学的学号、姓名

    找到的是叶平教过的学生的sid
select sid from 
teacher a inner join course b on a.tid=b.tid inner join sc c on b.cid=c.cid where a.tname='叶平';
再利用not in 排除这些sid
select * from student where sid not in (select sid from 
teacher a inner join course b on a.tid=b.tid inner join sc c on b.cid=c.cid where a.tname='叶平');

    ]()