mysql练手
2024-08-24 04:17:49
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的学过的所有人
然后个数 ＝ 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 = "苍空"
)
最新文章
- Javascript学习笔记
- 关于opencv中人脸识别主函数的部分注释详解。
- 项目开发之UML之初识
- alert的换行问题
- UVa10820 Send a Table[欧拉函数]
- IOS-MVC的使用
- Event List
- url rewrite优化url的可读性
- 帮哥们做的一个整理文档的小工具（C++ string的标准函数还是很给力的，代码在最下）
- 【转】perl 变量 $/ 的用法解析 上下文为行模式时，$/ 定义以什么来区分行
- datagridview 右键选中行 并弹出菜单
- LaTeX笔记
- [Math]Sqrt(x)
- JqueryUI-3
- 选择 GCD 还是 NSTimer
- 关于css选择器的一些细节
- BZOJ-3105: 新Nim游戏 (nim博弈&线性基)
- Git 记不住命令
- Android项目实战（三十三）：AS下获取获取依赖三方的jar文件、aar 转 jar
- mysql YEARWEEK(date[,mode]) 函数 查询上周数据 以及本周数据