Mysql练习

刚离职,去面试,才察觉自己在sql这方面还是有很大的欠缺,普通的增删改查完全应付不了笔试,故此稍加练习较为复杂的sql语句.

在网上查找了些许mysql的练习题,原链接已经忘却了,抱歉!

废话不说:

学生表:

CREATE TABLE `student` (   `S` varchar(10) DEFAULT NULL,   `Sname` varchar(10) DEFAULT NULL,   `Sage` datetime DEFAULT NULL,   `Ssex` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

教师表:

CREATE TABLE `teacher` (   `T` varchar(10) DEFAULT NULL,   `Tname` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

课程表: T对应教师表的T

CREATE TABLE `course` (   `C` varchar(10) DEFAULT NULL,   `Cname` varchar(10) DEFAULT NULL,   `T` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

分数表:  关联学生表和课程表

CREATE TABLE `sc` (   `S` varchar(10) DEFAULT NULL,   `C` varchar(10) DEFAULT NULL,   `score` decimal(18,1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分

SELECT a.*,b.score AS '01课程分数', c.score AS '02课程分数' FROM student AS a 
LEFT JOIN sc AS b ON a.s = b.s AND b.c = '01' 
LEFT JOIN sc AS c ON a.s = c.s AND c.c = '02' 
WHERE b.score > c.score

  --2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

     SELECT a.* ,b.score AS '01分数',c.score AS '02分数' FROM Student a 
 LEFT JOIN sc b ON a.s = b.s AND b.c ='01'
 LEFT JOIN sc c ON a.s = c.s AND c.c = '02'
 WHERE b.score < c.score

 --3、查询 平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

 SELECT student.s,student.`Sname`,AVG(sc.score) AS avgScore FROM sc 
 LEFT JOIN student ON sc.`S` = student.`S` GROUP BY sc.s HAVING AVG(sc.`score`) >= 60

 --4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT a.s,a.sname,AVG(b.score) FROM student a LEFT JOIN sc b ON a.s = b.s GROUP BY a.s HAVING AVG(b.score) <= 60 ;

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

SELECT a.s,a.sname,SUM(b.score) AS '总成绩' ,COUNT(c.c) AS '选课总数' FROM student a LEFT JOIN sc b ON a.s = b.s LEFT JOIN Course c ON b.c = c.c GROUP BY a.s

SELECT a.s ,a.sname ,COUNT(b.c) ,SUM(b.score) FROM student a LEFT JOIN sc b ON a.s=b.s GROUP BY 1,2

--6、查询学过"张三"老师授课的同学的信息 

SELECT a.* FROM student a LEFT JOIN sc b ON a.s = b.s LEFT JOIN Course c ON b.c = c.c LEFT JOIN Teacher d ON c.t = d.t WHERE d.Tname = '张三'

--7、查询没学过"张三"老师授课的同学的信息 

SELECT a.* FROM student a LEFT JOIN sc b ON a.s = b.s WHERE NOT EXISTS 
(SELECT * FROM Teacher c LEFT JOIN Course d ON c.T = d.t LEFT JOIN sc f ON d.c = f.c WHERE c.tname = '张三' AND a.s = f.s) GROUP BY a.s

--8、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

第一种:
SELECT aa.s,aa.name FROM (SELECT a.s AS s,a.sname AS NAME FROM student a INNER JOIN sc b ON a.s = b.s AND b.c ='01') aa,
(SELECT a.s,a.sname AS NAME FROM student a INNER JOIN sc b ON a.s = b.s AND b.c ='02') bb WHERE aa.name = bb.name
第二种:
SELECT a.s,a.sname FROM Student a INNER JOIN sc b ON a.s = b.s AND b.c = '01' INNER JOIN sc c ON a.s = c.s AND c.c = '02'