SQL 练习题
编程    sql    2015-01-26 18:53:56    1480    0    0
  sql

SQL 的想法和一般的编程语言想法不太一样。写简单的 sql 容易,写复杂点的 sql 脑子不清楚。而且 sql 还很难维护。还没有找到正确的学习 sql 的方法。入门的的书是 《sql 必知必会》,知识点应该都覆盖了,感觉内容有点老。关于 sql 优化的部分还没有考虑。

这是网上的一组练习题,整理和学习一下。

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cno` varchar(10) NOT NULL DEFAULT '',
  `cname` varchar(20) NOT NULL DEFAULT '',
  `tno` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`cno`,`cname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('c001', 'J2SE', 't002');
INSERT INTO `course` VALUES ('c002', 'Java Web', 't002');
INSERT INTO `course` VALUES ('c003', 'SSH', 't001');
INSERT INTO `course` VALUES ('c004', 'Oracle', 't001');
INSERT INTO `course` VALUES ('c005', 'SQL SERVER 2005', 't003');
INSERT INTO `course` VALUES ('c006', 'C#', 't003');
INSERT INTO `course` VALUES ('c007', 'JavaScript', 't002');
INSERT INTO `course` VALUES ('c008', 'DIV+CSS', 't001');
INSERT INTO `course` VALUES ('c009', 'PHP', 't003');
INSERT INTO `course` VALUES ('c010', 'EJB3.0', 't002');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sno` varchar(10) NOT NULL DEFAULT '',
  `cno` varchar(10) NOT NULL DEFAULT '',
  `score` float(4,2) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('s001', 'c001', '78.90');
INSERT INTO `sc` VALUES ('s002', 'c001', '80.90');
INSERT INTO `sc` VALUES ('s003', 'c001', '81.90');
INSERT INTO `sc` VALUES ('s004', 'c001', '60.90');
INSERT INTO `sc` VALUES ('s001', 'c002', '82.90');
INSERT INTO `sc` VALUES ('s002', 'c002', '72.90');
INSERT INTO `sc` VALUES ('s003', 'c002', '81.90');
INSERT INTO `sc` VALUES ('s001', 'c003', '59.00');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno` varchar(10) NOT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `sage` int(2) DEFAULT NULL,
  `ssex` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('s001', '张三', '23', '男');
INSERT INTO `student` VALUES ('s002', '李四', '23', '男');
INSERT INTO `student` VALUES ('s003', '吴鹏', '25', '男');
INSERT INTO `student` VALUES ('s004', '琴沁', '20', '女');
INSERT INTO `student` VALUES ('s005', '王丽', '20', '女');
INSERT INTO `student` VALUES ('s006', '李波', '21', '男');
INSERT INTO `student` VALUES ('s007', '刘玉', '21', '男');
INSERT INTO `student` VALUES ('s008', '萧蓉', '21', '女');
INSERT INTO `student` VALUES ('s009', '陈萧晓', '23', '女');
INSERT INTO `student` VALUES ('s010', '陈美', '22', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tno` varchar(10) NOT NULL,
  `tname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`tno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('t001', '刘阳');
INSERT INTO `teacher` VALUES ('t002', '谌燕');
INSERT INTO `teacher` VALUES ('t003', '胡明星');
  • 查询姓“刘”的老师的个数
SELECT count(*) from teacher WHERE teacher.tname like '刘%'

一个简单的匹配查询

  • 查询没学过“谌燕”老师课的同学的学号、姓名
SELECT * from student st WHERE st.sno not in 
(   SELECT DISTINCT sno from sc 
        join course on course.cno = sc.cno 
        join teacher on course.tno = teacher.tno
        WHERE tname = '谌燕'
)

先把表sc 、 course 和 teacher 表并起来,从而筛选出所有选了 谌燕 课的学生的 id ,然后再使用 not in ,反向筛选。
还用到了 DISTINCT ,处理重复的学生 id。

  • 查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名
SELECT * from student
JOIN sc a on a.sno = student.sno
JOIN sc b on b.sno = student.sno
WHERE a.cno = 'c001' and b.cno = 'c002'

这个类似第一题,只要使用 join ,那就只包含存在的,null 的自动去掉了。

  • 查询学过“谌燕”老师所教的所有课的同学的学号、姓名
    SELECT * from (
    SELECT *,COUNT(*) stcnt FROM sc 
    WHERE sc.cno in 
    (SELECT cno from course 
        LEFT JOIN teacher on course.tno = teacher.tno
        WHERE teacher.tname = '谌燕')
    GROUP BY sc.sno) c
    WHERE stcnt = (SELECT count(cno) from course 
        LEFT JOIN teacher on course.tno = teacher.tno
        WHERE teacher.tname = '谌燕')
    

这个题要求找出上了“谌燕”老师所教的所有课的同学,网上的答案应该是错了,我也只能写出这么丑的一个大 sql。由于不清楚 sql 的查询机制,自己觉得这样效率应该不高。

主要想法是,找出这个老师的所有课,统计每个学上上了这些课的多少,是否和这个老师的所有课的数量相等。

  • 查询所有课程成绩小于60 分的同学的学号、姓名
    这个题目有奇异,到底是 “所有有课程成绩小于60 分的同学” 还是 “所有的课程成绩都小于60 分的同学”?

按第一种理解比较简单:

SELECT * from student st
LEFT JOIN sc on sc.sno = st.sno
where sc.score <60

按第二种理解:

SELECT * from
(SELECT sc.sno,count(*) acnt FROM sc
GROUP BY sc.sno) a
WHERE a.acnt = (select count(*) from sc WHERE sc.sno = a.sno and sc.score <60)

我依然按照之前的想法,通过对比所有课程数和小于60分的课程数,来确定某个同学的课程是不是所有课程都小于 60 分。

  • 查询没有学全所有课的同学的学号、姓名
select stu.sno,stu.sname,count(sc.cno) from student stu
left join sc on stu.sno=sc.sno
group by stu.sno,stu.sname
having count(sc.cno)<(select count(distinct cno)from course)

一样的想法

  • 查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名
SELECT DISTINCT(sc.sno) from sc 
WHERE 
sc.sno <> 's001' and sc.cno in 
(SELECT cno from sc WHERE sc.sno = 's001')

先查出 s001 同学的课程,然后检查其他同学的课有没有在范围内的,过滤后输出。

  • 查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名
    SELECT * from (
    SELECT sc.sno,count(*) st_cnt from sc 
    WHERE 
    sc.sno <> 's001' and sc.cno in 
    (SELECT cno from sc WHERE sc.sno = 's001')
    GROUP BY sc.sno) t 
    WHERE st_cnt = (SELECT count(cno) from sc WHERE sc.sno = 's001')
    

又是一个这样的题。

  • 查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT sc.cno,max(sc.score),min(sc.score) from sc 
GROUP BY sc.cno 
  • 查询各科成绩的及格率
SELECT sc.cno,sum(case when sc.score>=60 then 1 else 0 end)/count(*) from sc 
GROUP BY sc.cno

使用 sum 配合 case when… then… else…end 语句数出及格人数。

  • 查询不同老师所教不同课程平均分从高到低显示
SELECT *,convert(COALESCE(AVG(sc.score),0),decimal(10,2)) from course 
LEFT JOIN sc on sc.cno = course.cno
LEFT JOIN teacher on teacher.tno = course.tno
GROUP BY course.cno 

保留两位小数的方法:

  • select convert(4545.1366,decimal); //四舍五入

  • select truncate(4545.1366,2); //截取

  • 统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT sc.sno,
SUM(case when sc.score >90 then 1 ELSE 0 end) as '>90' ,
SUM(case when sc.score >= 80 and sc.score<90 then 1 ELSE 0 end) as '80-90' ,
SUM(case when sc.score >= 70 and sc.score<80 then 1 ELSE 0 end) as '70-80' ,
SUM(case when sc.score >= 60 and sc.score<70 then 1 ELSE 0 end) as '60-70' ,
SUM(case when sc.score < 60 then 1 ELSE 0 end) as '<60' 
from sc
GROUP BY sc.sno
  • 查询各科成绩前三名的记录

    不会写
    
  • 查询每门课程被选修的学生数

    SELECT cno,count(sno) from sc GROUP BY cno
    
  • 查询出只选修了一门课程的全部学生的学号和姓名

    SELECT sno,count(cno) c_cnt 
    from sc GROUP BY sno
    HAVING c_cnt =1
    

    直接通过 having 过滤就 OK 了,刚开始想错了。

  • 查询男生、女生人数

    SELECT ssex,count(*) from student 
    GROUP BY ssex
    
  • 查询姓“张”的学生名单

    SELECT * from student st
    where st.sname LIKE '张%' 
    
  • 查询同名同性学生名单,并统计同名人数

    select sname,count(*)
    from student 
    group by sname having count(*)>1;
    
  • 1981 年出生的学生名单(注:Student 表中Sage 列的类型是number)

    select sno,sname,sage,ssex from student t where year(now()) - sage  =1996
    
  • 查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列

    SELECT sc.cno,avg(sc.score) avg_score from sc
    GROUP BY sc.cno
    ORDER by avg_score,cno DESC
    
  • 查询平均成绩大于85 的所有学生的学号、姓名和平均成绩

    SELECT sc.sno,avg(sc.score) avg_score from sc
    GROUP BY sc.sno
    HAVING avg_score > 85
    
  • 查询课程名称为“数据库”,且分数低于60 的学生姓名和分数

    SELECT * from sc 
    join course on sc.cno = course.cno
    where course.cname = 'J2SE' and sc.score < 60
    
  • 查询所有学生的选课情况

    select st.sno,st.sname,c.cname from student st,sc,course c
    where sc.sno=st.sno and sc.cno=c.cno;
    ORDER BY st.sno
    
- 查询任何一门课程成绩在70 分以上的姓名、课程名称和分数

select st.sno,st.sname,co.cname,sc.score from sc
join student st on sc.sno = st.sno
join course co on co.cno = sc.cno
WHERE sc.score >70

- 查询不及格的课程,并按课程号从大到小排列

select st.sno,st.sname,co.cname,sc.score from sc
join student st on sc.sno = st.sno
join course co on co.cno = sc.cno
WHERE sc.score <60 ORDER BY sc.cno desc

- 查询“c001”课程比“c002”课程成绩高的所有学生的学号;

SELECT * from
(SELECT * from sc a WHERE a.cno = 'c001') a,
(SELECT * from sc b where b.cno = 'c002') b
WHERE a.sno = b.sno and a.score > b.score

这里用到了两个子查询作为表,限定了  a.sno = b.sno 之后,就相当于把这张表的两个课程的成绩拼成了一行。之后的筛选就很方便了。
要从子查询的表中取数据就要给子查询取个别名。

SELECT * from sc a
JOIN sc b on a.sno = b.sno
WHERE a.cno = 'c001' and b.cno = 'c002'

同样也可以使用 join 的方法。

对于有限个的这种条件筛选,总是可以使用这种把当前表和自己合并之后再筛选的方法。


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

SELECT *,avg(sc.score) avg_score,COUNT(sc.score) from sc
GROUP BY sc.sno
HAVING avg_score > 60

这里就是基本的 group 的用法。


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

SELECT *,count(sc.cno),SUM(sc.score) from student
LEFT JOIN sc on sc.sno = student.sno
GROUP BY student.sno

为了保证打印出所有同学的信息,(注意,包含没有选课,sc 表中不存在信息的同学),所以需要使用 leftjoin
然后就是基本的分组查询了。

如果觉得 sum 返回 null 影响后台程序执行, 可以使用 COALESCE(x,0)来提供一个默认值。

oracle 中可以还可以使用 `NVL` ,myslq 中还可以使用 `ifnull`
文档导航