MySQL-多表查询
MySQL-多表查询
文章目录
课前题目:
- 建立一个school数据库,将school.sql数据导入到shool数据库中。建立xkgl数据库,将xkgl.sql数据导入到xkgl数据库中。
create database school;
create database xkgl;
- 观察school数据库中course表每一列的列名,将xkgl数据库中course表前三行数据对应列内容插入到school的course表中,(请用insert语句+select语句完成将查找到的信息插入表的操作),数据插入后,打开school数据库的course表,应当看到如下三行内容)
INSERT INTO school.course
SELECT courseid,coursename,bookname
FROM course
LIMIT 0,3
- 输出平均成绩大于68分的同学的学号和他的平均成绩[school数据库]
SELECT studentid,AVG(grade)
FROM grade
GROUP BY school.grade
HAVING AVG(grade)>68
1. 多表连接查询
在关系型数据库管理系统中,通常一张表只会存储一个实体的相关信息,如果用户需要查询多张表中不同实体的数据,必须要将多个表的数据进行连接。
[shool数据库:]
[例]查询每条成绩对应的学生姓名,课程号,成绩
#1、自然连接
SELECT studentname,courseid,grade
FROM grade,student
WHERE grade.studentid = student.studentid
#2、内连接
SELECT studentname, courseid, grade
FROM student
INNER JOIN grade ON grade.studentid = student.studentid
如果用户需要查询多张表中不同实体的数据,可以使对表执行连接查询操作,但前提条件是,这些表中必须存在具有相同意义的字段。
连接查询:同时涉及两个以上的表的查询,用连接字段连接
1.1 等值连接(相等连接):从where中连接(用=号进行匹配)
SELECT {*|col_list}
FROM table1,table2
WHERE table1.section_id=table_2.section_id;
1.2 自然连接
数据库应用中最常用的是“自然连接”,它在目标列中去除相同的字段名。
进行自然连接运算要求两个表有共同属性(列),自然连接运算的结果表是在参与操作的两个表的共同属性上进行等值连接后,再去除重复的属性后所得的新表。
等值连接和自然连接的区别:
1)等值连接中不要求相等属性值的属性名相同,而自然连接要求相等属性值的属性名必须相同,即两关系只有在同名属性才能进行自然连接。
2)等值连接不将重复属性去掉,而自然连接去掉重复属性,也可以说,自然连接是去掉重复列的等值连接。
1.3 自/内/外连接:从from中连接(JION连接)
自连接作为一种特例,可以将一个表与它自身进行连接,称为自连接。若要在一个表中查找具有相同列值的行,则可以使用自连接。使用自连接时需为表指定两个别名,且对所有列的引用均要用别名限定。
SELECT a.学号, a.课程号, b.课程号, a.成绩
FROM CJB a JOIN CJB b
ON a.成绩=b.成绩 AND a.学号=b.学号 AND a.课程号!=b.课程号
指定了INNER关键字的连接是内连接,内连接按照ON所指定的连接条件合并两个表,返回满足条件的行。内连接是系统默认的,可以省略INNER关键字。使用内连接后仍可使用WHERE子句指定条件。
指定了left/right关键字的为外连接,外连接的结果表不但包含满足连接条件的行,还包括相应表中的所有行
SELECT {*|col_list}
FROM table_name1
INNER/LEFT/RIGHT JOIN table_name2 ON condition
[例]查询每条成绩对应的学生学号,姓名,课程号,成绩
SELECT grade.studentid,studentname,courseid,grade
FROM grade,student
WHERE grade.studentid = student.studentid
[例]查询每个同学的成绩,输出信息包括学号,学生名,课程号,成绩,如果该学生没有选过课,则输出该同学的学号和姓名,课程号和成绩信息为空
SELECT student.studentid,studentname,courseid,grade
FROM student
left JOIN grade
ON grade.studentid = student.studentid
#权利大的表是基本表,基本表中的数据都会保留
[学习通]查询每条成绩对应的学号,课程号,课程名,成绩
[分别用自然连接和外连接两种方式做]
- 自然连接
SELECT studentid,grade.courseid,coursename,grade
FROM grade,course
WHERE grade.courseid = course.courseid
- 外连接
SELECT studentid,grade.courseid,coursename,grade
FROM grade
LEFT JOIN course
ON grade.courseid = course.courseid
- 复合条件连接查询是通过在连接查询中添加过滤条件,以达到限制查询结果和筛选数据的目的
[例2]查询选修java程序设计课程且成绩在85分以上的所有学生的学号和姓名以及成绩。
#1、内连接
SELECT student.studentid,studentname,coursename,grade
FROM grade
INNER JOIN course ON grade.courseid = course.courseid
INNER JOIN student ON grade.studentid = student.studentid
WHERE coursename LIKE "J%" AND grade>85
#2、自然连接
SELECT student.studentid,studentname,coursename,grade
FROM grade,student,course
WHERE grade.studentid = student.studentid
AND grade.courseid = course.courseid
[xkgl数据库:]
[课堂练习]查询计算机系所有学生的姓名和所在班级名
#1、自然连接
SELECT studentname,classname
FROM department,student,class
WHERE departmentname = "计算机系"
AND class.DepartmentID = department.DepartmentID
AND student.ClassID = class.ClassID
#2、内连接
SELECT studentname,classname
FROM department
INNER JOIN class ON class.DepartmentID = department.DepartmentID
INNER JOIN student ON student.ClassID = class.ClassID
WHERE departmentname = "计算机系"
[学习通]:查询每个同学的平均成绩,输出姓名,平均成绩
1、自然连接
SELECT studentname,AVG( grade ) AS 平均成绩 ,student.studentid
FROM grade,student
WHERE grade.StudentID = student.StudentID
GROUP BY Studentid
#2、内连接
SELECT studentname,AVG( grade ) AS 平均成绩,student.studentid
FROM grade
INNER JOIN student ON grade.StudentID = student.StudentID
GROUP BY studentid
2. 子查询
如果一个查询语句中嵌套了一个或若干个其他的查询语句,那么在整个语句中,外层查询称为主查询,内层查询称为子查询或者嵌套查询。该类查询可以基于一个表或多个表。在此类查询中,系统会先执行子查询,将子查询的结果作为主查询的过滤条件,子查询分为以下几种:
2.1 带有IN谓词的子查询
2.2 带有ANY(SOME)或ALL谓词的子查询
2.3 带有条件判断符的子查询
2.4 带有EXISTS谓词的子查询
2.1 带有IN谓词的子查询
当子查询返回的是一个数据集合,主查询需要返回符合集合中条件的记录时,可以使用IN关键字,语法形式如下:
SELECT {*|col_list} FROM table_name1
WHERE col_name1 IN
(SELECT col_name2 FROM table_name2 [WHERE condition]);
[例]查询属于计算机系和信管系的班级名称,输出班级名称和所属系名称
SELECT
ClassName,
DepartmentName
FROM
class c,
department d
WHERE
c.DepartmentID = d.DepartmentID
AND c.DepartmentID IN ( SELECT DepartmentID FROM department d WHERE d.DepartmentName IN ( "计算机系", "信管系" ) );
[学习通]查询计算机系年龄最大的学生姓名及当前年龄(假设计算机系年龄最大的学生为20岁,将所有计算机系20岁的同学姓名都输出)
SELECT s.StudentName,'2020'-YEAR(birth1) as 年龄
FROM student s,department d,class c
WHERE s.ClassID = c.ClassID
AND c.DepartmentID = d.DepartmentID
AND d.DepartmentName = "计算机系"
AND YEAR(birth1) =(
SELECT DISTINCT YEAR(birth1) AS z
FROM student
ORDER BY z
LIMIT 1
)
另外,子查询还可以和NOT IN配合使用,表示:不在此范围内任一数据都可以
[课堂练习]查询不属于计算机系的班级名称
SELECT
ClassName,
DepartmentName
FROM
class c,
department d
WHERE
c.DepartmentID = d.DepartmentID
AND c.DepartmentID IN (
SELECT
DepartmentID
FROM
department d
WHERE
d.DepartmentName NOT IN ( "计算机系" ));
2.2 使用ANY、ALL关键字的子查询
l ANY(some)表示满足其中任一条件。
该类查询会创建一个表达式对子查询的返回值列表进行比较,只要满足子查询中的任一个比较条件,就返回一个结果。其语法形式如下:
SELECT {*|col_list} FROM table_name1
WHERE col_name1<any
(SELECT col_name2 FROM table_name2 [WHERE condition]);
ALL表示满足所有条件。
与ANY不同,使用关键字ALL的子查询,表示当一条记录符合子查询结果中所有的条件时,才会返回该记录。其语法形式如下:
SELECT {*|col_list} FROM table_name1
WHERE col_name1>All
(SELECT col_name2 FROM table_name2 [WHERE 条件]);
[例]找出其他班级中比“10电子商务1班“最大的学生年龄还大的同学姓名。
#方法一
SELECT StudentName,birth1
FROM student s,class c
WHERE s.classid=c.ClassID
AND c.ClassName != "10电子商务1班"
AND YEAR(birth1)<(
SELECT MIN(YEAR(birth1))
FROM student s,class c
WHERE ClassName = '10电子商务1班'
AND s.ClassID = c.ClassID
)
#方法二
SELECT StudentName,birth1
FROM student
WHERE YEAR(birth1) <ALL (
SELECT YEAR(birth1)
FROM student s,class c
WHERE ClassName = '10电子商务1班'
AND s.ClassID = c.ClassID
)
[课堂练习]找出其他班级中年龄比“10电子商务1班“最小的学生年龄大的同学姓名。
#方法一
SELECT StudentName,birth1
FROM student
WHERE YEAR(birth1)<(
SELECT max(YEAR(birth1))
FROM student s,class c
WHERE ClassName = '10电子商务1班'
AND s.ClassID = c.ClassID
);
#方法二
SELECT StudentName,birth1
FROM student
WHERE YEAR(birth1)<ANY(
SELECT YEAR(birth1)
FROM student s,class c
WHERE ClassName = '10电子商务1班'
AND s.ClassID = c.ClassID
);
2.3 使用条件判断符的子查询
在子查询中,还可以单独使用条件判断符。其语法形式如下:
SELECT {*|col_list} FROM table_name 1
WHERE col_name1 operators
(SELECT col_name2 FROM table_name2);
[例]找出’St0109010001’号学生超过他选修课程平均成绩的课程号。
SELECT courseid
FROM grade
WHERE studentid = "St0109010001"
AND grade >(
SELECT AVG(grade)
FROM grade
WHERE studentid = "St0109010001"
)
[课堂练习]找出其他班级中比“10电子商务1班“最大的学生年龄还大的同学姓名。
SELECT StudentName,birth1
FROM student s,class c
WHERE s.classid=c.ClassID
AND c.ClassName != "10电子商务1班"
AND YEAR(birth1)<(
SELECT MIN(YEAR(birth1))
FROM student s,class c
WHERE ClassName = '10电子商务1班'
AND s.ClassID = c.ClassID
)
[思考题]找出每个学生超过他选修课程平均成绩的课程号。
SELECT courseid
FROM grade x
WHERE grade>(
SELECT AVG(grade)
FROM grade y
WHERE y.studentid = x.studentid
)
不相关子查询:
子查询的查询条件不依赖于父查询
过程:由里向外逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。
相关子查询:
子查询的查询条件依赖于父查询
过程:首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表。然后再取外层表的下一个元组,重复这一过程,直至外层表全部检查完为止。
2.4 含有EXISTS关键字的子查询
2.4.1 EXISTS谓词
带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果集非空,则外层的WHERE子句返回真值
若内层查询结果集为空,则外层的WHERE子句返回假值
由EXISTS引出的子查询,其目标列表达式通常都用 * ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义。
2.4.2 NOT EXISTS谓词
若内层查询结果非空,则外层的WHERE子句返回假值
若内层查询结果为空,则外层的WHERE子句返回真值
Exists采用逐条遍历的方式,每次查询都会在内层进行匹配,若该记录返回条件为真,则输出当前记录,如果为假,则舍弃当前记录
[例]查询所有选修了Dp010001号课程的学生姓名。
SELECT studentname
FROM student s
WHERE EXISTS(
SELECT *
FROM grade g
WHERE g.studentid = s.studentid
AND courseid = 'Dp010001'
)
[课堂练习]查询没有选修Dp010001号课程的学生姓名。
SELECT studentname
FROM student s
WHERE NOT EXISTS(
SELECT *
FROM grade g
WHERE g.studentid = s.studentid
AND courseid = 'Dp010001'
)
[例]查询选修了全部课程的学生姓名。
SELECT studentname
FROM student s,grade g
WHERE s.studentid = g.studentid
GROUP BY s.studentid
HAVING COUNT(*) = (SELECT COUNT(*) FROM course)
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
用EXISTS/NOT EXISTS实现全称量词(难点)
SQL语言中没有全称量词 (For all)
可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:
(x)P ≡ ( x( P))
[课堂练习]查询至少选修了学生St0109010002选修的全部课程的学生号。**
SELECT studentname
FROM student s
WHERE NOT EXISTS(
SELECT *mysql
FROM grade a
WHERE a.studentid = 'St0109010002'
AND NOT EXISTS(
SELECT *
FROM grade b
WHERE b.studentid = s.studentid
AND a.courseid = b.courseid)
)
[课堂练习]查询所有同时选修了Dp010001号课程和Dp010004号课程的学生姓名。
SELECT StudentName
FROM student s
WHERE NOT EXISTS(
SELECT *
FROM course co
WHERE (CourseID = 'Dp010001' OR CourseID = 'Dp010004')
AND not EXISTS(
SELECT *
FROM Grade g
WHERE s.StudentID = g.StudentID
AND g.CourseID = co.CourseID
)
)
3. 派生表
子查询不仅可以出现在WHERE子句中,还可以出现在FROM子句中
将子查询做为一个表来处理,这个由子查询得出的新表就是我们说的"派生表"。
[例]利用派生表的方式查询所有选修了Dp010001号课程的学生姓名
SELECT StudentName
FROM student s,( SELECT CourseID, StudentID FROM Grade WHERE CourseID
= 'Dp010001' ) AS co
WHERE s.studentid = co.StudentID
【练习】用派生表方式找出每个学生超过他自己选修课程平均成绩的课程号
SELECT s.studentid,courseid
FROM grade g,( SELECT studentid, AVG( grade ) avg_grade
FROM grade GROUP BY studentid ) s
WHERE g.studentid = s.studentid
AND grade>avg_grade;
4. 合并查询结果
合并查询结果就是使用UNION关键字,将多条查询语句的结果合并在一起显示。UNION有两种使用方法
1.查询结果不重复(过滤掉重复的记录)
2.保留所有查询结果。
应用场景:适用于查询多个表的时候,但多个表没有直接的关系,查询的列基本是一致时
特点
1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致
3、union 去重,union all包含重复项
[例]查询所有选修了Dp010001号或Dp010004号课程的学生姓名。
SELECT
StudentName ,st.StudentID
FROM
student st,
grade g
WHERE
st.StudentID = g.StudentID
AND CourseID = 'Dp010001'
UNION
SELECT
StudentName , st.StudentID
FROM
student st,
grade g
WHERE
st.StudentID = g.StudentID
AND CourseID = 'Dp010004';
[例]查询选修了Dp010001号或Dp010004号课程的记录一共有多少条?
SELECT
StudentName ,st.StudentID,CourseID
FROM
student st,
grade g
WHERE
st.StudentID = g.StudentID
AND CourseID = 'Dp010001'
UNION ALL
SELECT
StudentName , st.StudentID,CourseID
FROM
student st,
grade g
WHERE
st.StudentID = g.StudentID
AND CourseID = 'Dp010004';
[练习]查找学生总数以及出生日期最早的同学姓名,将这两个查询结果合并到一个结果集中
SELECT COUNT(*) 学生总人数和出生最早学生
FROM student
UNION
SELECT StudentName
FROM student
WHERE birth1 = (SELECT MIN(birth1) FROM student)
5. 实验题:
1. 查询选修了课程的学生姓名
SELECT
DISTINCT student.studentid,studentname
FROM
student,grade
WHERE student.StudentID = grade.StudentID
2. 查询11网络工程和09数据库班每个学生的姓名和他所在的班级名,即使该班没有学生,也要输出班级名。
SELECT
classname,studentname
FROM
class LEFT JOIN student ON class.ClassID = student.classid
WHERE classname = "11网络工程" OR classname = "09数据库班"
3. 求出JAVA程序设计考试的前三名的姓名和成绩
SELECT
student.studentid,
grade,
studentname,
coursename
FROM
course,
student,
grade
WHERE
course.CourseID = grade.CourseID
AND student.StudentID = grade.StudentID
AND coursename = "JAVA程序设计"
GROUP BY
grade DESC,
student.studentid
LIMIT 3;
4. 求信管系以外的同学姓名,所在班级名和所在系名
SELECT
studentname,classname,departmentname
FROM
student,class,department
WHERE department.Departmentid = class.DepartmentID
AND student.ClassID = class.ClassID
and departmentname != "信管系"
5. 求姓名第二个字是丽的同学选修了哪些课程,请写出同学名和课程名。
SELECT
studentname,
coursename
FROM
student,
grade,
course
WHERE
student.studentid = grade.StudentID
AND grade.CourseID = course.CourseID
AND studentname LIKE "_丽%";
6. 查询11网络工程和10电子商务2班一共有多少人
SELECT count( StudentID ) 人数
FROM class c,student s
WHERE s.ClassID = c.ClassID
AND classname IN ('11网络工程','10电子商务2班')
7. 查询哪些课程没有被选修过
方法一:
SELECT CourseName
FROM course
WHERE CourseID not IN(
SELECT DISTINCT c.CourseID
FROM grade g
LEFT JOIN course c
ON c.CourseID = g.CourseID)
方法二:
SELECT CourseName
FROM course
WHERE coursename NOT IN (
SELECT DISTINCT CourseName
FROM course co,grade g
WHERE co.courseid = g.courseid
AND StudentID IN ( SELECT DISTINCT StudentID
FROM course co, grade g
WHERE co.CourseID = g.CourseID )
)
8. 找出选修了全部课程的同学的名字
SELECT StudentName
FROM student s
WHERE NOT EXISTS(
SELECT *
FROM course co
WHERE NOT EXISTS(
SELECT *
FROM grade g
WHERE g.StudentID = s.StudentID
AND co.CourseID = g.CourseID
)
)
9. 查询有哪些同学,没有选修学生st0210010005选修的课程(即st0210010005选修的课程他一门都没选)
SELECT StudentName
FROM Student s
WHERE NOT EXISTS(
SELECT *
FROM grade a
WHERE a.StudentID = 'st0210010005'
AND EXISTS(
SELECT *
FROM grade g
WHERE g.studentid = s.StudentID
AND a.courseid = g.CourseID
)
)
10. 找出每个学院每个班级各有多少人(需要通过student表找),需要输出的结果如下:
SELECT
DepartmentID,
c.ClassID,
COUNT(*)
FROM
student s,
class c
WHERE
s.ClassID = c.ClassID
GROUP BY
DepartmentID,
ClassID
11.12题选做
11. 查找每个科目前三名的学号和成绩(需要输出课程号,学号和成绩)
SELECT
CourseID 课程号,StudentID 学号,grade 成绩
FROM
grade
WHERE(
SELECT count(*)
FROM grade AS a
WHERE grade.CourseID = a.CourseID
AND Grade.Grade < a.Grade
)< 3
ORDER BY
CourseID ASC,
Grade.Grade DESC;
12. 求每个学生的课是哪位教师教授的(求学号,学生姓名,教师姓名,课程名称)
SELECT
student.studentname,
course.coursename,
teachername
FROM
student,
grade,
SCHEDULE,
teacher,
course
WHERE
student.studentid = grade.studentid
AND SCHEDULE.classid = student.classid
AND SCHEDULE.courseid = grade.courseid
AND course.courseid = grade.courseid
AND SCHEDULE.teacherid = teacher.teacherid;
本文都是自己上课的笔记和作业,如果有觉得我题目做的不对的或者麻烦的希望能指出来,让我学到更多的知识,如果想要具体的数据库文件也可以联系我。
本文地址:https://blog.csdn.net/HT412515/article/details/109445858
下一篇: js加减乘除精确运算方法实例代码