SQL练习题50道

// 首先是数据库脚本

CREATE DATABASE babel;
USE `babel`;





CREATE TABLE `course` (
  `cid` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `tid` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;



insert  into `course`(`cid`,`cname`,`tid`) values (1,'语文','02'),(2,'数学','01'),(3,'英语','03');

/*Table structure for table `sc` */

DROP TABLE IF EXISTS `sc`;

CREATE TABLE `sc` (
  `sid` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `cid` varchar(10) NOT NULL DEFAULT '',
  `score` decimal(18,1) DEFAULT NULL,
  PRIMARY KEY (`sid`,`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;



insert  into `sc`(`sid`,`cid`,`score`) values (1,'01','80.0'),(1,'02','90.0'),(1,'03','99.0'),(2,'01','70.0'),(2,'02','60.0'),(2,'03','80.0'),(3,'01','80.0'),(3,'02','80.0'),(3,'03','80.0'),(4,'01','50.0'),(4,'02','30.0'),(4,'03','20.0'),(5,'01','76.0'),(5,'02','87.0'),(6,'01','31.0'),(6,'03','34.0'),(7,'01','98.0'),(7,'02','89.0'),(7,'03','98.0');


CREATE TABLE `student` (
  `SNO` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `Sage` datetime DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



insert  into `student`(`SNO`,`Sname`,`Sage`,`Ssex`) values ('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','女'),('09','张三','2017-12-20 00:00:00','女'),('10','李四','2017-12-25 00:00:00','女'),('11','李四','2017-12-30 00:00:00','女'),('12','赵六','2017-01-01 00:00:00','女'),('13','孙七','2018-01-01 00:00:00','女');



CREATE TABLE `t_user` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '',
  `password` varchar(255) DEFAULT '',
  `phone` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;



CREATE TABLE `teacher` (
  `tid` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `tname` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;

insert  into `teacher`(`tid`,`tname`) values (1,'张三'),(2,'李四'),(3,'王五');

1、查询”01″课程比”02″课程成绩高的学生的信息及课程分数

// 这道题根据学生是否存在课程2分成两种情况讨论,不存在的用左连接，不存在的多表关联查询。
// 换个清晰点的描述方式：求一张表的某个列处于不同值时候的情况进行比较分析. 需要重点了解下这种一个表换算成两个变量来比较的方式
// 1. 多表关联查询
SELECT a.*, b.score 课程01的分数, c.score 课程02的分数 FROM student a, sc b, sc c WHERE
a.SNO = b.sid AND 
a.SNO = c.sid AND 
b.cid = '01'  AND 
c.cid = '02'  AND 
b.score > c.score;
// 2. 左连接
SELECT a.*, b.score 课程01的分数, c.score 课程02的分数 FROM student a 
LEFT JOIN sc b ON b.`sid` = a.`SNO` AND b.`cid` = '01'
LEFT JOIN sc c ON c.sid = a.`SNO` AND c.`cid` = '02'
WHERE b.`score` > IFNULL(c.score, 0);

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

// 先用where语句来获得每个学生他所拥有的所有课程的成绩。 然后再通过学生id来做聚合条件查询.
SELECT a.`SNO`,a.`Sname`,AVG(b.score) 平均分数 
FROM student a, sc b 
WHERE a.`SNO` = b.`sid` 
GROUP BY b.sid 
HAVING AVG(b.score) >= 60;

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

SELECT a.`SNO`, a.`Sname`, COUNT(b.`cid`) 选课总数, SUM(b.`score`) 成绩总数 
FROM student a, sc b 
WHERE a.SNO = b.sid 
GROUP BY  b.sid;

4、查询学过”张三”老师授课的同学的信息

/*细致的理解一下， 首先select 多张表的记录的时候， 实际是对这多张表记录做笛卡尔积运算，比如a表10行

数据，b表12行数据，那么我select from a,b 这样会有120条数据。通过a.SNO = d.sid,就过滤了一些数据 

过滤的就是a.SNO != d.sid的数据.这样层层递推， 但是其实后面的3个条件都是唯一性的相等也就是一对一进

行映射， 并且包含了个唯一性的条件name = '张三'.(如果没有这个name = '张三'), 那两个唯一性的条件将不

会对记录的行数产生任何影响 这样去理解会清晰一些
*/
SELECT a.* FROM student a, course b, teacher c,sc d 
WHERE a.`SNO` = d.`sid` 
AND d.`cid` = b.`cid` 
AND b.`tid` = c.`tid` 
AND c.`tname` = '张三';

5、查询学过编号为”01″并且也学过编号为”02″的课程的同学的信息

SELECT student. * 
FROM student, sc sc1, sc sc2 
WHERE student.`SNO` = sc1.`sid` 
AND student.`SNO` = sc2.`sid` 
AND sc1.`cid` = '01' 
AND sc2.`cid` = '02';

6、查询没有学全所有课程的同学的信息

SELECT student.* 
FROM student, sc 
WHERE student.`SNO` = sc.`sid` 
GROUP BY sc.`sid` 
HAVING COUNT(sc.`cid`) < (SELECT COUNT(course.`cid`) FROM course);

7、查询至少有一门课与学号为”01″的同学所学相同的同学的信息

SELECT DISTINCT student.* 
FROM student, sc 
WHERE sc.`cid` IN (SELECT cid FROM sc WHERE sc.`sid` = '01') 
AND sc.`sid` = student.`SNO` 
AND student.`SNO` <> '01'

8、查询和”01″号的同学学习的课程完全相同的其他同学的信息

/* 这道题的难点是完全相同: 首先获得sid = 01 的所有课程的cid. 然后查询哪些记录有这些cid,获取他们的

sid（并且不包含01这个自身的sid）, 对这些记录 根据他们的sid进行一个聚合 如果个数和sid = 01所拥有的

课程编号的个数还能相同就可以绝对说明他们学习的课程编号情况是一样的。(如果初始的时候数量一致但学习的课程编号不一样的话

也会在sc.cid in 这个条件语句当中被过滤导致后面的group by的count数量小于'01'所拥有的课程数量).

*/
SELECT student.* 
FROM student 
WHERE student.`SNO` 
IN (
SELECT DISTINCT sc.`sid` FROM sc WHERE sc.`sid` != '01' AND sc.`cid` 
IN (SELECT DISTINCT cid FROM sc WHERE sid = '01')
GROUP BY sid 
HAVING COUNT(1) = (
SELECT COUNT(1) FROM sc WHERE sc.`sid`= '01')
);

9、查询没学过”张三”老师讲授的任一门课程的学生姓名

// 解法1
SELECT student.`Sname` FROM student 
WHERE Student.`SNO` NOT IN (
SELECT sc.`sid` FROM sc  WHERE  
sc.cid IN( SELECT course.cid FROM course, teacher 
WHERE course.`tid` = teacher.`tid`  
AND teacher.tname = '张三')
);

/* 解法2 两个 = 号的过滤条件实质上就是在以SC表为基础， 扩展了其他几个表的列属性而已， 综合起来然后做个name ='张三'的过滤)
*/
SELECT student.`Sname` FROM student 
WHERE Student.`SNO` NOT IN (
SELECT sc.`sid` FROM sc,course, teacher  
WHERE  sc.cid = course.`cid` 
AND course.`tid` = teacher.`tid`
AND teacher.`tname` = '张三');

10、 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT student.`SNO`,student.`Sname`, AVG(sc.score) FROM student, sc 
WHERE student.`SNO` = sc.`sid`
AND sc.sid IN (
SELECT sc.sid FROM sc 
WHERE sc.score < 60 
GROUP BY sc.`sid` 
HAVING COUNT(1) >= 2)
GROUP BY sc.`sid` 

11、检索”01″课程分数小于60，按分数降序排列的学生信息

SELECT  student.*,sc.`score`, sc.`cid` FROM student, sc 
WHERE sc.`sid` = student.`SNO` 
AND sc.score < 60 
AND sc.`cid` = '01' 
ORDER BY sc.score DESC

插播个case when的学习科普

CASE WHEN 及 SELECT CASE WHEN的用法

Case具有两种格式。简单Case函数和Case搜索函数。

简单Case函数 (就好像switch一样）

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END

 

--Case搜索函数  （就好像if else 一样)

CASE WHEN sex = '1' THEN '男' 

WHEN sex = '2' THEN '女' 

ELSE '其他' END  

还有个东西叫做用户变量 

SELECT (@rowno:=@rowno+1),sc.* FROM sc,(SELECT (@rowno:=0)) b WHERE score = 80;这个是记录你的记录在当前查询排第几个用的。