MySQL-多表查询

文章目录

• • MySQL-多表查询
  • • 课前题目：
    • 1. 多表连接查询
    • • 1.1 等值连接（相等连接）：从where中连接（用=号进行匹配）
      • 1.2 自然连接
      • 等值连接和自然连接的区别：
      • 1.3 自/内/外连接：从from中连接（JION连接)
    • 2. 子查询
    • • 2.1 带有IN谓词的子查询
      • 2.2 带有ANY（SOME）或ALL谓词的子查询
      • 2.3 带有条件判断符的子查询
      • 2.4 带有EXISTS谓词的子查询
    • 3. 派生表
    • 4. 合并查询结果
    • 5. 实验题：

课前题目：

1. 建立一个school数据库，将school.sql数据导入到shool数据库中。建立xkgl数据库，将xkgl.sql数据导入到xkgl数据库中。

create database school;
create database xkgl;

1. 观察school数据库中course表每一列的列名，将xkgl数据库中course表前三行数据对应列内容插入到school的course表中，（请用insert语句+select语句完成将查找到的信息插入表的操作），数据插入后，打开school数据库的course表，应当看到如下三行内容）

INSERT INTO school.course 

SELECT courseid,coursename,bookname 

FROM course

LIMIT 0,3

1. 输出平均成绩大于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 

#权利大的表是基本表，基本表中的数据都会保留

[学习通]查询每条成绩对应的学号，课程号，课程名，成绩

[分别用自然连接和外连接两种方式做]

1. 自然连接

SELECt studentid,grade.courseid,coursename,grade

FROM grade,course

WHERe grade.courseid = course.courseid

1. 外连接

SELECt studentid,grade.courseid,coursename,grade

FROM grade

LEFT JOIN course 

ON grade.courseid = course.courseid

1. 复合条件连接查询是通过在连接查询中添加过滤条件，以达到限制查询结果和筛选数据的目的

[例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;

本文都是自己上课的笔记和作业，如果有觉得我题目做的不对的或者麻烦的希望能指出来，让我学到更多的知识，如果想要具体的数据库文件也可以联系我。