– 数据库习题
– 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 – 1.1 联表查询
select st.*,sc.s_score as 01_score,sc01.s_score as 02_score from student st
-- 找出 01 课程 学生的成绩
left join score sc on st.s_id = sc.s_id and sc.c_id = '01' or sc.c_id = null
-- 找出02课程学生的成绩
join score sc01 on st.s_id = sc01.s_id and sc01.c_id = '02'
where sc.s_score < sc01.s_score
– 1.2 自连接查询
select
c.*,a.s_score 01score,b.s_score 02score
FROM
score a,score b,student c
where a.c_id ='01'
and b.c_id = '02'
and a.s_id = c.s_id
and b.s_id = c.s_id
and a.s_score >b.s_score
– 1.3 将长形数据变成宽型数据
SELECT
s.* ,t.01sc,
t.02sc
FROM
(
SELECT
a.s_id,-- 返回01课程的成绩
MAX( CASE WHEN a.c_id = '01' THEN a.s_score END ) AS 01sc,
MAX( CASE WHEN a.c_id = '02' THEN a.s_score END ) AS 02sc
FROM
score a
GROUP BY
a.s_id
) t,
student s
WHERE
t.01sc > t.02sc and t.s_id = s.s_id
– 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
– 2.1 自连接
SELECT
c.*,a.s_score sc01,b.s_score sc02
FROM
score a,score b, student c
where a.c_id = '01'
AND b.c_id = '02'
AND a.s_score < b.s_score
AND a.s_id = c.s_id
AND b.s_id = c.s_id
– 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
a.s_id,
( SELECT b.s_name FROM student b WHERE a.s_id = b.s_id ) AS s_name,
AVG( a.s_score ) AS avg_sc
FROM
score a
GROUP BY
a.s_id
HAVING
avg_sc >= 60
– 3.1自链接 – score avg student having
SELECT
b.s_id,
b.s_name,
avg( a.s_score ) avg_score
FROM
score a,
student b
WHERE
a.s_id = b.s_id
GROUP BY
b.s_id
HAVING
avg_score >= 60
– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 – (包括有成绩的和无成绩的) – 在学生表中，有八名学生，但是成绩表中只有7位，说明有一位同学缺考了，需要使用右连接查询 – ifnull 对为null 的值做处理 – 4.1 右连接
SELECT
b.s_id,
b.s_name,
IFNULL( avg( a.s_score ), 0 ) avg_score
FROM
score a
RIGHT JOIN student b ON a.s_id = b.s_id
GROUP BY
b.s_id
HAVING
avg_score < 60
--
SELECT
b.s_id,
b.s_name,
AVG( a.s_score ) AS age_sc
FROM
score a,
student b
WHERE
a.s_id = b.s_id
GROUP BY
b.s_id
HAVING
age_sc < 60
– 出5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 – student score count group sum – 在学生表中，有八名学生，但是成绩表中只有7位，说明有一位同学缺考了，需要使用右连接查询 – ifnull 对为null 的值做处理
SELECT
a.s_id,
a.s_name,
count( b.c_id ) AS count_c,
ifnull( sum( b.s_score ), 0 ) AS sum_sc
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id
GROUP BY
a.s_id
– 6、查询"李"姓老师的数量 – teacher count
SELECT
COUNT(*)
FROM
teacher a
where a.t_name like '李%'
– 7、查询学过"张三"老师授课的同学的信息 – teacher student course score
SELECT
a.*
FROM
student a
left join score b on a.s_id = b.s_id
JOIN course c on c.c_id = b.c_id
JOIN teacher d on d.t_id = c.t_id
WHERE d.t_name = '张三'
-- 内连接
SELECT
d.*
FROM
teacher a ,course b,score c, student d
where a.t_id = b.t_id
AND b.c_id = c.c_id
AND c.s_id = d.s_id
AND a.t_name = '张三'
– 8、查询没学过"张三"老师授课的同学的信息 – 找到学过张三老师课程的虚学生id，使用 not in关键字将其排除就行了
SELECT
student.*
FROM
student
WHERE
s_id NOT IN (
SELECT
c.s_id
FROM
teacher a,
course b,
score c
WHERE
a.t_id = b.t_id
AND b.c_id = c.c_id
AND a.t_name = '张三')
– 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 – student course score
1.
SELECT
a.*
FROM
student a
JOIN score b ON a.s_id = b.s_id and b.c_id = '01'
JOIN score c ON a.s_id = c.s_id and c.c_id = '02'
2.
SELECT
c.*
FROM
student c
JOIN score a ON c.s_id = a.s_id
JOIN score b ON c.s_id = b.s_id
WHERE
a.c_id = '01'
AND b.c_id = '02';
-- 自连接
SELECT
c.*
FROM
score a,
score b,
student c
WHERE
a.s_id = c.s_id
AND a.c_id = '01'
AND b.c_id = '02'
-- 同时学过01和02
AND a.s_id = b.s_id
– 宽型数据
SELECT
student.*
FROM
(
SELECT
s_id,
max( CASE WHEN a.c_id = '01' THEN a.s_score END ) 01_s,
max( CASE WHEN a.c_id = '02' THEN a.s_score END ) 02_s
FROM
score a
GROUP BY
a.s_id
) t,
student
WHERE
t.s_id = student.s_id
AND t.01_s IS NOT NULL
AND t.02_s IS NOT NULL
– 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT
student.*
FROM
(
SELECT
s_id,
max( CASE WHEN a.c_id = '01' THEN a.s_score END ) 01_s,
max( CASE WHEN a.c_id = '02' THEN a.s_score END ) 02_s
FROM
score a
GROUP BY
a.s_id
) t,
student
WHERE
t.s_id = student.s_id
AND t.01_s IS NOT NULL
AND t.02_s IS NULL
...