mysql子查询、重命名as、 not in、any、all、year、union

最新推荐文章于 2023-12-21 21:23:42 发布

annaya

最新推荐文章于 2023-12-21 21:23:42 发布

阅读量647

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/annaya/article/details/106267933

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

这篇博客探讨了如何使用MySQL进行复杂查询，包括子查询来获取学生信息和成绩，使用AS进行重命名，NOT IN筛选特定条件，ANY与ALL比较操作，YEAR函数提取年份，以及UNION操作合并不同查询结果。还涵盖了查询特定班级平均分、高于特定学生成绩的记录，以及不同职称教师的情况。内容深入，适合数据库查询初学者和进阶者参考。

摘要由CSDN通过智能技术生成

查询所有的学生 s_name , c_no, sc_degree列

select s_name, c_no, sc_degree from student, score where student.s_no = score.s_no; 
+--------+-------+-----------+
| s_name | c_no  | sc_degree |
+--------+-------+-----------+
| 王丽   | 3-105 |        92 |
| 王丽   | 6-166 |        85 |
| 王芳   | 3-105 |        88 |
| 王芳   | 3-245 |        75 |
| 王芳   | 6-166 |        79 |
| 赵铁柱 | 3-105 |        76 |
| 赵铁柱 | 3-245 |        68 |
| 赵铁柱 | 6-166 |        81 |
+--------+-------+-----------+

查询所有学生的s_no, c_name, sc_degree列

select s_no, c_name, sc_degree from student, course, sc_degree where student.s_no = score.s_no and course.c_no = score.c_no;

查询所有的学生 s_name , c_name, sc_degree列

select s_name, c_name, sc_degree from student, course, score where student.s_no = score.s_no and course.c_no = score.c_no;
+--------+------------+-----------+
| s_name | c_name     | sc_degree |
+--------+------------+-----------+
| 王丽   | 计算机导论 |        92 |
| 王丽   | 数字电路   |        85 |
| 王芳   | 计算机导论 |        88 |
| 王芳   | 操作系统   |        75 |
| 王芳   | 数字电路   |        79 |
| 赵铁柱 | 计算机导论 |        76 |
| 赵铁柱 | 操作系统   |        68 |
| 赵铁柱 | 数字电路   |        81 |
+--------+------------+-----------+

查询班级是’95031’班学生每门课的平均分

# score中只有s_no,先过滤班级，首先从student中找出‘95031’班的学生，得到了s_no
select * from student where s_class = '95031'; 
# score中过滤找到的s_no
select * from score where s_no in (select s_no from student where s_class = '95031');
#每门课，平均分
select c_no, avg(sc_degree) from score 
where s_no in (select s_no from student where s_class = '95031') 
group by c_no;
+-------+----------------+
| c_no  | avg(sc_degree) |
+-------+----------------+
| 3-105 |        82.0000 |
| 3-245 |        71.5000 |
| 6-166 |        80.0000 |
+-------+----------------+
#进阶,加入课程名称:
SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) 
FROM student AS s, score AS SC, course AS c  
WHERE s.s_class = '95031' AND s.s_no = sc.s_no AND c.c_no = sc.c_no 
GROUP BY sc.c_no ;
+-------+------------+-------------------+
| c_no  | c_name     | AVG(sc.sc_degree) |
+-------+------------+-------------------+
| 3-105 | 计算机导论 |           82.0000 |
| 3-245 | 操作系统   |           71.5000 |
| 6-166 | 数字电路   |           80.0000 |
+-------+------------+-------------------+

查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩的所有同学的记录

# '109'号同学'3-105'成绩
select sc_degree from score where s_no = '109' and c_no = '3-105';
# 所有记录
select * from score as sc ,student as s ,course as c 
where sc_degree > (select sc_degree from score where s_no = '109' and c_no = '3-105') and sc.c_no = '3-105' and s.s_no = sc.s_no and c.c_no = sc.c_no;
+------+-------+-----------+------+--------+-------+---------------------+------
---+-------+------------+------+
| s_no | c_no  | sc_degree | s_no | s_name | s_sex | s_birthday          | s_cla
ss | c_no  | c_name     | t_no |
+------+-------+-----------+------+--------+-------+---------------------+------
---+-------+------------+------+
| 103  | 3-105 |        92 | 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033
   | 3-105 | 计算机导论 | 825  |
| 105  | 3-105 |        88 | 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031
   | 3-105 | 计算机导论 | 825  |
+------+-------+-----------+------+--------+-------+---------------------+------
---+-------+------------+------+

查询成绩高于学号为’109’,课程号为’3-105’的成绩的所有记录

select sc_degree from score where s_no = '109' and c_no = '3-105';
select * from score where sc_degree > (select sc_degree from score where s_no = '109' and c_no = '3-105');
# 进阶
SELECT s.s_no AS'学生学号', s.s_name AS'学生姓名', s_sex AS'性别', s_class AS'班级', c.c_no AS'课程编号', c.c_name AS'课程名称' ,sc.sc_degree AS'分数' 
FROM student AS s, score AS sc ,course AS c 
WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no AND sc.c_no = c.c_no;
+----------+----------+------+-------+----------+------------+------+
| 学生学号 | 学生姓名 | 性别 | 班级  | 课程编号 | 课程名称   | 分数 |
+----------+----------+------+-------+----------+------------+------+
| 103      | 王丽     | 女   | 95033 | 3-105    | 计算机导论 |   92 |
| 103      | 王丽     | 女   | 95033 | 6-166    | 数字电路   |   85 |
| 105      | 王芳     | 女   | 95031 | 3-105    | 计算机导论 |   88 |
| 105      | 王芳     | 女   | 95031 | 6-166    | 数字电路   |   79 |
| 109      | 赵铁柱   | 男   | 95031 | 6-166    | 数字电路   |   81 |
+----------+----------+------+-------+----------+------------+------+

查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday

SELECT s_no,s_name,s_birthday FROM student 
WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN('108','101'));
+------+--------+---------------------+
| s_no | s_name | s_birthday          |
+------+--------+---------------------+
| 101  | 曾华   | 1977-09-01 00:00:00 |
| 102  | 匡明   | 1975-10-02 00:00:00 |
| 105  | 王芳   | 1975-02-10 00:00:00 |
| 108  | 张全蛋 | 1975-02-10 00:00:00 |
+------+--------+---------------------+

查询张旭教师任课的学生的成绩

# 张旭的编号（teacher)）
select t_no from teacher where t_name = '张旭';
#  张旭任什么课（course）
select c_no from course where t_no = (select t_no from teacher where t_name = '张旭');
# 
select s_no, sc_degree from score as sc
where sc.c_no = (select c_no from course where t_no = (select t_no from teacher where t_name = '张旭'));
+------+-----------+
| s_no | sc_degree |
+------+-----------+
| 103  |        85 |
| 105  |        79 |
| 109  |        81 |
+------+-----------+

查询选修课程的同学人数多余 5 人的教师姓名

select t_name from teacher 
where t_no in 
(select t_no from course where c_no in
 (select c_no from score group by c_no having count(s_no) > 2));
+--------+
| t_name |
+--------+
| 王萍   |
| 张旭   |
+--------+

查询95033班和95031班全体学生的记录

SELECT * FROM student 
WHERE s_class IN('95031','95033') ORDER BY s_class;

查询’计算机系’与’电子工程系’ 不同职称的教师的name和rof
—UNION 求并集

SELECT * FROM teacher WHERE t_depart = '计算机系' AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '电子工程系')
UNION
SELECT * FROM teacher WHERE t_depart = '电子工程系' AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '计算机系');
+------+--------+-------+---------------------+--------+------------+
| t_no | t_name | t_sex | t_birthday          | t_rof  | t_depart   |
+------+--------+-------+---------------------+--------+------------+
| 804  | 李诚   | 男    | 1958-12-02 00:00:00 | 副教授 | 计算机系   |
| 856  | 张旭   | 男    | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |
+------+--------+-------+---------------------+--------+------------+

查询选修编号为"3-105"课程且成绩至少高于选修编号为’3-245’同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序
—至少 any
—全部 all

#至少？ any
select * from score 
where c_no = '3-105' and 
sc_degree > any(select sc_degree from score where c_no = '3-245') order by sc_degree desc;
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-105 |        92 |
| 105  | 3-105 |        88 |
| 109  | 3-105 |        76 |
+------+-------+-----------+