MySQL练习50题记录之【06-10】

练习题和SQL语句

-- 6、查询"李"姓老师的数量

#基础的模糊查询
SELECT
COUNT(t.t_id)
FROM
teacher t
WHERE
t_name
LIKE
'李%';

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

# 隐式内连接
SELECT
s.*
FROM
teacher t,course c,score sc,student s
WHERE
t.t_name='张三'
AND
t.t_id = c.t_id
AND
c.c_id = sc.c_id
AND
sc.s_id = s.s_id;

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

#1. 可以直接把第七题学号作为第八题的查询条件
SELECT
s.*
FROM
student s
WHERE
s.s_id
NOT in
(SELECT
sc.s_id
FROM
teacher t,course c,score sc
WHERE
t.t_name='张三'
AND
t.t_id = c.t_id
AND
c.c_id = sc.c_id);

#2. 提高查询效率的一个写法 not in替换为 not exists
SELECT * FROM student s WHERE NOT EXISTS(
SELECT 1 FROM
(SELECT
sc.s_id
FROM
teacher t,course c,score sc
WHERE
t.t_name='张三'
AND
t.t_id = c.t_id
AND
c.c_id = sc.c_id) t
WHERE
t.s_id = s.s_id); 

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

# 自连接
SELECT
s.*
FROM
score sc1,score sc2,student s
WHERE
sc1.c_id = '01'
AND
sc2.c_id = '02'
AND
sc1.s_id = sc2.s_id
AND
sc1.s_id = s.s_id;

 -- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

# 行转列
SELECT
s.*
FROM
(SELECT
sc.s_id,
MAX(CASE WHEN sc.c_id ='01' THEN sc.s_score END) sc01,
MAX(CASE WHEN sc.c_id ='02' THEN sc.s_score END) sc02
FROM
score sc
GROUP BY
sc.s_id) t,student s
WHERE
t.s_id = s.s_id
AND
sc01 IS NOT NULL
AND
sc02 IS NULL;