MySQL必会50题（1）

---

一、新建数据库以及导入建表sql语句

1. 新建数据库属性如下：

2. 建表语句：

CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);

CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);

CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);

CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

3. 插入数据：

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');


insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

二、表结构

course表：

score表：

student表：

teacher表：

三、相关题目

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

思路：先分别查询课程编号为01和课程编号为02的信息，将这两张表做内连接，得到了一张由s_id、01课程的成绩、02课程的成绩组合而成的一张表，然后再从这张表中查询到01的课程成绩比02高的学号

SELECT a.s_id from
(
SELECT s_id,c_id,s_score from score where c_id='01'
) as a
INNER JOIN
(
SELECT s_id,c_id,s_score from score where c_id='02'
)as b ON a.s_id=b.s_id
WHERE a.s_score>b.s_score

执行结果：

如果要查询更加详细的数据，比如说要得到学生的姓名、每门课的分数，可以再内连接student表，在student表中得到学生的姓名信息

SELECT a.s_id "s_no",c.s_name,a.s_score "01",b.s_score "02" from
(
SELECT s_id,c_id,s_score from score where c_id='01'
) as a
INNER JOIN
(
SELECT s_id,c_id,s_score from score where c_id='02'
)as b ON a.s_id=b.s_id
INNER JOIN student as c on a.s_id=c.s_id
WHERE a.s_score>b.s_score

执行结果：

2.查询平均成绩大于60分的学生的学号和平均成绩

思路：考察group by的使用以及查询条件要用having

SELECT s_id,avg(s_score) 
	FROM score
GROUP BY s_id HAVING avg(s_score) > 60

执行结果：

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

思路：首先我们查询这些字段需要用到两个表，分别是student表和score表，这两个表要做外连接查询，将student表作为左表，score作为右表，通过s_id将两张表进行连接，为什么不用内连接？因为内连接求的是交集，分数表中缺少8号学生的成绩，但是student表中存在这个人，所以要求的是并集。接下来要做分组操作，根据s_id和s_name分组，为啥不就根据s_id来分组？因为如果使用了group by，那么查询条件中的字段必须在group by中出现，这样比较严谨。接着再用count和sum函数求出选课数和总成绩。为了显得比较专业，因为学号为8的同学没有成绩，所以可以使用case判断当这个字段为null时设置为0，else还是原来的值。

sql语句：

SELECT a.s_id,a.s_name,COUNT(c_id),
SUM(CASE WHEN b.s_score IS NULL THEN 0 ELSE b.s_score END)
from student as a
LEFT JOIN score as b on a.s_id=b.s_id
GROUP BY s_id,s_name

执行结果：

4.查询姓“猴”的老师的个数

思路：本题主要考察like和%以及count的操作
sql语句：

SELECT count(t_id)
FROM teacher
WHERE t_name LIKE '猴%'

执行结果：

5.查询没学过“张三”老师课的学生

思路：先从teacher表中根据老师名字查询出张三的教师id，再根据这个教师id从课程表中查询出他教的课的id，再从分数表中根据这个课的id查询出选了这门课的学生，再从学生表中查询出学号不在选了这门课的学号中的学生。

sql语句：

SELECT s_id,s_name
FROM student
WHERE s_id NOT IN
(
SELECT s_id
from score
WHERE c_id=(select c_id from course where 
t_id=(select t_id from teacher where t_name='张三')
)
)

执行结果：

6. 行变成列

思路：使用SUM和IF函数，再根据s_id作为条件进行分组，注意**IF(c_id=‘01’,s_score,0)**作为条件，即对所有c_id=‘01’的记录的s_score字段进行SUM()操作，如果s_score没有值则默认为0

sql语句：

SELECT s_id,SUM(IF(`c_id`='01',s_score,0)) as '01',SUM(IF(`c_id`='02',s_score,0)) as '02',SUM(IF(`c_id`='03',s_score,0)) as '03' FROM score GROUP BY s_id;

执行结果：