1、根据图创建下列表格
没有外键的表先创建,创建顺序为teacher,class,course,student
CREATE TABLE class (
cid INT NOT NULL auto_increment PRIMARY KEY,
caption CHAR (32) NOT NULL
) ENGINE = INNODB DEFAULT charset = utf8;
INSERT INTO score (caption)
VALUES
('三年二班'),
('一年三班'),
('三年一班') ;
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号
CREATE VIEW vw1 AS SELECT
score.student_id AS "学号",
student.sname AS "姓名",
course.cname AS "科目",
number AS "生物分数"
FROM
score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN student ON score.student_id = student.sid
WHERE
course.cname = '生物';
CREATE VIEW vw2 AS SELECT
score.student_id AS "学号",
student.sname AS "姓名",
course.cname AS "科目",
number AS "物理分数"
FROM
score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN student ON score.student_id = student.sid
WHERE
course.cname = '物理';
SELECT
*
FROM
vw1
INNER JOIN vw2 ON vw1.学号 = vw2.学号
WHERE
vw1.生物分数 > vw2.物理分数;
3、查询平均成绩大于60分的同学的学号和平均成绩
SELECT
score.student_id AS "学号",
student.sname AS "姓名",
sum(score.number) AS "总分数",
avg(score.number) AS gva
FROM
score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN student ON score.student_id = student.sid
GROUP BY
student_id
HAVING
gva > 60;
4、查询所有同学的学号、姓名、选课数、总成绩
SELECT
score.student_id AS "学号",
student.sname AS "姓名",
sum(score.number) AS "总成绩",
count(score.course_id) AS '课程数'
FROM
score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN student ON score.student_id = student.sid
GROUP BY
student_id
5、查询姓“李”的老师的个数
SELECT
count(tname) AS "个数"
FROM
teacher
WHERE
tname LIKE "波%" ;
6、查询没学过“叶平”老师课的同学的学号、姓名
思路:没学过某个老师,我可以找到学过这个老师的学生,并在学生表判断,排除这些学过的就是没学过了
(学生学的课程id in (先找叶平老师教的课程id))
最后只要排除 not in这群学生就可了
SELECT
*
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
LEFT JOIN teacher ON teacher_id = tid
WHERE
tname = "波多"
)
)
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
思路:先查出所有学了 001 或 002 的学生 course_id in (001,002)
然后group by 学生id,having进行科目数统计,等于2的就是符合条件的
SELECT
student_id,
sname
FROM
student
LEFT JOIN score ON student_id = student.sid
WHERE
course_id IN (1, 2)
GROUP BY
student_id
HAVING
count(student_id) = 2;
8、查询学过“叶平”老师所教的所有课的同学的学号、姓名
SELECT
*
FROM
student
WHERE
student.sid IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
LEFT JOIN teacher ON teacher.tid = course.teacher_id
WHERE
teacher.tname = '饭岛'
)
);
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
SELECT
id1,
NAME
FROM
(
SELECT
student_id AS id1,
number AS number1,
student.sname AS NAME
FROM
score
LEFT JOIN student ON student.sid = score.student_id
WHERE
score.course_id = 1
) AS A
LEFT JOIN (
SELECT
student_id AS id2,
number AS number2
FROM
score
LEFT JOIN student ON student.sid = score.student_id
WHERE
score.course_id = 2
) AS B ON A.id1 = B.id2
WHERE
number1 > number2;
10、查询有课程成绩小于60分的同学的学号、姓名
SELECT DISTINCT
student.sid,
sname
FROM
student
LEFT JOIN score ON student.sid = score.student_id
WHERE
student.sid IN (
SELECT
student_id
FROM
score
WHERE
number < 60
);
11、查询没有学全所有课的同学的学号、姓名
SELECT
sid,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
count(student_id) = 3
);
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
思路:先找到001同学的course---一个元组
course_id in 元组 ---student id元组
sid in student id元组
SELECT DISTINCT
sid,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course_id
FROM
score
WHERE
student_id = 1
)
)
AND sid != 1;
13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名
先找到和001的学过的所有人
SELECT
student.sid,
sname,
count(course_id)
FROM
score
LEFT JOIN student ON student.sid = score.student_id
WHERE
course_id IN (
SELECT
course_id
FROM
score
WHERE
student_id = 1
)
AND student_id != 1
GROUP BY
student_id
HAVING
count(student_id) = (
SELECT
count(course_id)
FROM
score
WHERE
student_id = 1
);
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
思路:总课程数=002课程总数 --》student_id集
每位同学符合002同学学过课程的数目=002课程总数 》》student_id
去两 --》交集
SELECT
student.sid,
sname,
count(course_id)
FROM
score
LEFT JOIN student ON student.sid = score.student_id
WHERE
student.sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
count(course_id) = (
SELECT
count(course_id)
FROM
score
WHERE
student_id = 2
)
)
AND course_id IN (
SELECT
course_id
FROM
score
WHERE
student_id = 2
)
AND student_id != 2
GROUP BY
student_id
HAVING
count(student_id) = (
SELECT
count(course_id)
FROM
score
WHERE
student_id = 2
);
15、删除学习“叶平”老师课的SC表记录
思路:delete from where 条件
条件:老师--》》课程id_list-->>course_id in id_list
DELETE
FROM
score
WHERE
course_id in (
SELECT
cid
FROM
course
LEFT JOIN teacher ON teacher.tid = course.teacher_id
WHERE
tname = "苍空"
)