MySQL一张训练表,SQL【复习】经典MySQL练习50题之1-7

问题

试问：SELECT * FROM employees; 和 SELECT * FROM employees WHERE commission_pct LIKE “%%” AND last_name LIKE “%%”;结果是否一样？并说明原因

答：

结果不一样 ，因为表中可能存在NULL值，如果表中没有NULL值，则结果是一样的，若and改为or，则结果一样。

#--------------------------------

SELECT *

FROM employees

WHERE commission_pct LIKE '%%’ or last_name like “%%”;

经典MySQL练习50题

表名和字段

学生表

Student(s_id、s_name、s_birth、s_sex)：学生编号、学生姓名、出生年月、学生性别。

课程表

Course(c_id、c_name、t_id)：课程编号、课程名称、教师编号。

教师表

Teacher(t_id、t_name)：教师编号、教师姓名。

成绩表

Score(s_id、c_id、s_score)：学生编号、课程编号、分数。

第一题

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数(重点)

-- 解答一

SELECT s.*

FROM `student` AS s,`score` AS sc1,`score` AS sc2

WHERE s.`s_id`=sc1.`s_id` AND s.`s_id`=sc2.`s_id`

AND sc1.`c_id`='01' AND sc2.`c_id`='02'

AND sc1.`s_score`>sc2.`s_score`;

-- 解答二

SELECT s.`s_id`,s.`s_name`,a.`c_id`

FROM (SELECT * FROM `score` AS sc1 WHERE sc1.`c_id`='01') AS a

INNER JOIN

(SELECT * FROM `score` AS sc2 WHERE sc2.`c_id`='02') AS b ON a.`s_id`=b.`s_id`

INNER JOIN `student` AS s

ON s.`s_id`=a.`s_id`

WHERE a.`s_score`>b.`s_score`;

第二题

2、查询平均成绩大于60分的学生的学号和平均成绩(重点)

SELECT `s_id` AS "学号",AVG(`s_score`) AS "平均成绩"

FROM `score` AS sc

GROUP BY sc.`s_id`

HAVING AVG(sc.`s_score`)>60;

第三题

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

SELECT s.`s_id` AS 'ID',s.`s_name` AS '姓名',COUNT(sc.`s_score`) AS '选课数',SUM(sc.`s_score`) AS '总分'

FROM `student` AS s

LEFT JOIN `score` AS sc ON s.`s_id`=sc.`s_id`

GROUP BY sc.`s_id`;

第四题

4、查询"李"姓老师的数量

SELECT COUNT(*)

FROM `teacher` AS t

WHERE t.`t_name`LIKE '李%';

第五题

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

-- 解法一

SELECT s.*

FROM `student` AS s,`score` AS sc,`course` AS c,`teacher` AS t

WHERE s.`s_id`=sc.`s_id` AND sc.`c_id`=c.`c_id` AND c.`t_id`=t.`t_id`

AND t.`t_name`='张三';

-- 解法二

SELECT s.*

FROM Student AS s , Score AS sc

WHERE s.s_id = sc.s_id

AND sc.c_id IN (SELECT DISTINCT(c.c_id)

FROM Course AS c,Teacher AS t

WHERE c.t_id = t.t_id

AND t.t_name = "张三")

第六题

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

-- 解法一

SELECT *

FROM `student` AS s

WHERE s.`s_id` NOT IN(

SELECT sc.`s_id`

FROM `student` AS s

LEFT JOIN `score` AS sc

ON s.`s_id`=sc.`s_id`

WHERE sc.`c_id` IN(

SELECT c.`c_id`

FROM `course` AS c

INNER JOIN `teacher` AS t

WHERE c.`t_id`=t.`t_id` AND t.`t_name`='张三'

));

第七题

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

-- 解法一

SELECT s.*

FROM

(SELECT * FROM `score` AS sc1 WHERE sc1.`c_id`='01') AS a

INNER JOIN

(SELECT * FROM `score` AS sc1 WHERE sc1.`c_id`='02') AS b

ON a.`s_id`=b.`s_id`

INNER JOIN `student` AS s ON s.`s_id`=a.`s_id`;

-- 解法二

SELECT s.*

FROM Student AS s, Score AS sc1 ,Score AS sc2

WHERE s.s_id = sc1.s_id

AND s.s_id = sc2.s_id

AND sc1.c_id = "01"

AND sc2.c_id = "02"