MySQL查询数据（多表查询）

准备工作，新建名为students的数据，三张表分别是student，courses，stu_cou，并创建外键约束，级联删除更新，插入数据。

/*创建数据库*/
create database if not EXISTS students character set utf8 collate utf8_general_ci;
/*创建表*/
use students;
create table if not EXISTS student
(
    stuID  int(5)  not null primary key,
    stuName  varchar(50)  not null,
    stuSex CHAR(10),
    stuAge smallint
);

CREATE TABLE if not EXISTS courses(
         couID int  not null primary key auto_increment COMMENT '学号',
         couName varchar(50) not null DEFAULT('大学英语'),
         couHours  smallint UNSIGNED COMMENT '学时',
         couCredit  float DEFAULT(2) COMMENT '学分'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE  utf8mb4_0900_ai_ci;

CREATE TABLE if not EXISTS stu_cou(
         ID int not null primary key auto_increment,
         stuID int(5)  not null  COMMENT '学号',
         couID int  not null  COMMENT '课程编号',
         time timestamp not null DEFAULT(now())
);

 /*添加外键约束*/
 alter table stu_cou add CONSTRAINT fk_stuid foreign key(stuID) REFERENCES student(stuID) ON UPDATE CASCADE ON DELETE CASCADE ;
 alter table stu_cou add CONSTRAINT fk_couid foreign key(couID) REFERENCES courses(couID) ON UPDATE CASCADE ON DELETE CASCADE ;

  /*插入数据*/
 insert into student(stuID,stuName,stuSex,stuAge) values(1001,'张三','男',19),(1002,'李四','男',18),(1003,'王五','男',18),(1004,'黄丽丽','女',18),(1005,'李晓辉','女',19),(1006,'张敏','女',18); insert into student VALUES(1007,'五条人','男',20),(1008,'胡五伍','女',19);
 insert into courses(couID,couName,couHours,couCredit) values(50,'大学英语',64,2),(60,'计算机基础',78,2.5),(70,'Java程序设计',108,6),(80,'数据库应用',48,2.5);
 insert into stu_cou(stuID,couID) values(1001,50),(1001,60),(1001,70),(1001,80),(1002,50),(1002,60),(1002,70),(1002,80),(1003,50),(1003,60),(1003,70),(1003,80),(1004,50),(1004,60),(1004,70),(1004,80),(1005,50),(1005,60),(1005,70),(1005,80),(1006,50),(1006,60),(1006,70),(1006,80);

alter table stu_cou add COLUMN grade FLOAT null;
UPDATE stu_cou set grade=(SELECT FLOOR(50 +RAND() * 50));

alter table student add COLUMN stuColleage varchar(100) null;
update student set stuColleage='大数据学院' where stuID BETWEEN 1001 and  1003;
update student set stuColleage='物流学院' where stuID BETWEEN 1004 and  1006;
update student set stuColleage='康养学院' where stuID BETWEEN 1007 and  1008;
View Code

 1.交叉连接（全连接）（CROSS JOIN）

语法：select 字段列表 from 表1 cross join 表2 [where 条件]；或者  select 字段列表 from 表1,表2 [where 条件]；

注意：多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。

例1：查询student表和courses表。

select * from student CROSS join courses;
select * from student,courses;

得到的结果：

 此处的结果是student表的8行记录*courses表的4行记录，总共有32行记录（笛卡尔积），也叫作全连接，形成的结果大多数没有意义。

笛卡尔积的概念：http://c.biancheng.net/view/7456.html

2.自连接（self join）（转自：https://www.cnblogs.com/Huzihu/p/12244796.html）

针对相同的表进行的连接被称为“自连接”（self join），那么为什么要把相同的一张表连接起来呢？一开始还是挺难理解的。把它想象成连接两张不同的表，这样容易理解一些。事实上，自连接还是有很多用处的。

（1）在同一张表里面进行比较

例2：查找大于张三年龄的同学信息的学号，姓名和年龄

select s1.stuID,s1.stuName,s1.stuID,s1.stuAge from student as s1,student as s2
where s2.stuName='张三' and s1.stuAge>s2.stuAge;

 

 例3：查找学时数大于“数据库应用”课程的课程ID，课程名称，学时数

select c1.couID,c1.couName,c1.couHours from courses as c1,courses as c2 where c2.couName='数据库应用' and c1.couHours>c2.couHours;

（2）找出列的组合

修改courses表的结构，新增开课学院列，并添加数据

alter table courses add COLUMN couColleage varchar(50) null;

update courses set couColleage='通识教育学院' where couName='大学英语';
update courses set couColleage='通识教育学院' where couName='计算机基础';
update courses set couColleage='大数据学院' where couName='Java程序设计';
update courses set couColleage='大数据学院' where couName='数据库应用';

insert into courses values(90,'大学体育',56,1.5,'通识教育学院'),(100,'Android程序设计',92,5,'大数据学院'),(101,'大学物理',48,2,'通识教育学院');
View Code

例4：查找学分相等的所有课程信息

select DISTINCT c1.couName,c1.couCredit from courses c1 join courses c2
on c1.couCredit=c2.couCredit;

（3）查找部分内容重复的记录

例5：查找课时数相等但课程名称不同的课程的课程名称，课时数。

select DISTINCT c1.couName,c1.couHours from courses c1 join courses c2
on c1.couHours=c2.couHours and c1.couName!=c2.couName;

其他操作，如删除课时数相等的课程信息。

自连接实际上是内连接的一种特殊情况。

3.内连接

内连接：[inner] join,从左表中取出每一条记录，去右表中与所有的记录进行匹配，匹配必须在某个条件在左表中和右表中相同最终才会保留结果，否则不保留。

语法：SELECT 字段列表 FROM 表1[INNER] JOIN 表2 ON 表1.列1= 表2.列2 where  条件

例6：查询学生姓名和分数

select stuName as '姓名',grade as '分数' from student INNER JOIN stu_cou on 
student.stuID=stu_cou.stuID;

  此处涉及到学生表student和学生选课表stu_cou，还可以省略inner关键字，如下所示：

select stuName as '姓名',grade as '分数' from student JOIN stu_cou on
student.stuID=stu_cou.stuID;

三张表的内连接语法：SELECT 字段列表 FROM 表1[INNER] JOIN 表2 ON 表1.列1= 表2.列2  [INNER] JOIN 表3 ON 表3.列1= 表2.列3  where  条件

例7：查询学生姓名,课程名称和分数

SELECT stuName as '姓名',couName as '课程名称',grade as '分数' from student s INNER JOIN stu_cou sc on s.stuID=sc.stuID INNER JOIN courses c on c.couID=sc.couID;

   例8：查询分数大于等于75分的学生姓名和分数

select stuName as '姓名',grade as '分数' from student INNER JOIN stu_cou on 
student.stuID=stu_cou.stuID where stu_cou.grade>=75;

此处加上了where条件进一步筛选记录。 

查询大学英语成绩大于等于90分的学生姓名,课程名称和分数

SELECT stuName as '姓名',couName as '课程名称',grade as '分数' from student s INNER JOIN stu_cou sc on s.stuID=sc.stuID INNER JOIN courses c on c.couID=sc.couID where sc.grade>=90 and c.couName='大学英语';

 

注意：内连接还可以使用where代替on关键字（where没有on的效率高）如：

select stuName as '姓名',grade as '分数' from student INNER JOIN stu_cou where 
student.stuID=stu_cou.stuID;

为表指定别名有两种方式：

第一种是通过关键字AS指定

SELECT * from student as s,courses as c;

第二种是在表名后直接加表的别名实现（注意有空格）

SELECT * from student s,courses c;

 语法：select  字段列表  from 表1 [as 别名1],表2 [as 别名2]....where 表1.字段 = 表2.字段 AND 其它查询条件

4.外连接

外连接：outer join,以某张表为主，取出里面的所有记录，然后每条记录去另外一张表进行连接，不管能不能匹配上条件，最终都会保留，能匹配，正确保留，不能匹配，其他表的字段都置空。

基本方法：左表 left/right join 右表 on 左表.字段 = 右表.字段;

 向student表中增加2条记录，但不在选课表中输入该2名新同学的成绩。

insert into stu_cou(stuID,couID,grade) values(1007,50,86),(1007,60,71),(1008,70,56),(1008,80,63);
 insert into student VALUES(1009,'曾小小','男',17,'物流学院'),(1010,'项XXX','女',21,'大数据学院');

左外连接语法：SELECT 字段列表 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.列1= 表2.列2 where  条件

例9：以student为左表，使用左外连接查询student表和stu_cou表中的姓名和成绩。（换个说法：查询出所有选课学生的姓名和成绩，以及没有选课的学生信息）

/*左外连接*/
select stuName as '姓名',grade as '成绩' from 
student s left outer JOIN stu_cou sc on s.stuID=sc.stuID

结果如下：

 

 显示出左表student的两条记录在右表stu_cou表中是没有相同的记录的，但是左表也显示出来了，如果使用内连接，则这两条记录是不会显示出来的，这就是内连接和外连接的区别。

如果想要显示课程名称列，则需要连接三张表，如下所示：

 select stuName as '姓名',couName as '课程名称',grade as '成绩' from student s left
 outer join stu_cou sc on s.stuID=sc.stuID left
 outer join courses c on c.couID=sc.couID;

下面这张图清楚的展示内外连接的区别，转自：https://blog.csdn.net/qq_63776150/article/details/123878527

右外连接语法：SELECT 字段列表 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.列1= 表2.列2 where  条件

例10：以student为左表，使用右外连接查询student表和stu_cou表中的姓名和成绩。 （换个说法：查询出所有选课学生的姓名和成绩）

/*右外连接*/
select stuName as '姓名',grade as '成绩' from 
student s right outer JOIN stu_cou sc on s.stuID=sc.stuID

 例11：以stu_cou表为左表，使用右外连接查询student表和stu_cou表中的姓名和成绩。（与例9相同）  

select stuName as '姓名',grade as '成绩' from 
stu_cou sc right outer JOIN student s on s.stuID=sc.stuID

注意例11与例9得到的结果是一样的。

5.三张表以上的多表查询，也可以省略关键字join和on,直接使用主从表之间的外键字段进行连接

语法：SELECT 字段列表 FROM 表1,表2,表3.... where 主表1.主键= 从表3.外键 and 主表2.主键= 从表3.外键  and  其他条件

/*查询张三的大学英语成绩，显示姓名，课程名称和成绩*/
select s.stuName as '姓名',c.couName as '课程名称',sc.grade as '成绩' from student s,stu_cou sc,courses c where s.stuID=sc.stuID and c.couID=sc.couID and c.couName='大学英语' and s.stuName='张三';

6.子查询（转自：https://blog.csdn.net/qq_44111805/article/details/124680208）

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

子查询的基本使用：

  • 子查询要包含在括号内。
  • 将子查询放在比较条件的右侧。
  • 单行操作符对应单行子查询，多行操作符对应多行子查询。

子査询可以返回各种​不同类型的信息：​

  • 标量子査询返回一个值。
  • 列子査询返回一个由一个值或多个值构成的​列​。
  • 行子査询返回一个由一个值或多个值构成的​行​。
  • 表子査询返回一个由一个行或多个行构成的表，而行则由一个或多个列构成。

子査询结果的测试方法各不相同：​

  • 可以用​诸​如"="或​"<"​之类的关系​比较运算符​来评估标量子査询的结果。
  • 可以用​运算符IN​和​NOT IN​来测试​某给定值是否包含​在子査询的结果集里。
  • 可以用​运算符ALL​、​ANY​和​SOME​把某给定值与子査询的结果集​进行比较​。
  • 可以用运算符​EXISTS​和​NOT EXISTS​来测试子査询的​结果是否为空​。

​      标量子査询是最严格的，​因为它只会产生一个值。正因如此，标量子査询的适用范围也最大。从理论上讲，标量子査询可以出现在任何允许使用标量操作数的地方，如出现在某个表达式里、作为函数参数或者放置在输出列的列表里。列、行和表这3类子査询会返回更多的信息，因此不能用在要求必须使用单个值的环境里。
​     子査询要不要相关都可以​。也就是说，子査询既可以引用和依赖外层査询里的值，也可以不这样做。
除了SELECT语句，子査询还可以用在其他语句里。不过，如果把子查询用在一条会改变表内容的语句里(如DELETE、INSERT、REPLACE、UPDATE、LOADDATA)，那么MySQL会强行限制这个子査询，​不允许它查询正被修改的那个表​。
      有些子査询​可以改写为连接操作​。你会发现掌握子査询的改写技术很有用，通过这门技术可以看出MySQL优化器在使用连接的情况下，是否比使用等价的子査询表现得更好。（转自：https://blog.51cto.com/u_15346415/5171229）

我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询 、 多行子查询。

6-1.(1)单行子查询，有以下单行操作符

 例12.查询与张三同在一个学院的其他同学信息。 

select * from student where stuColleage=(select stuColleage from student where stuName='张三');

例13.查询与李晓辉性别相同且与张三同在一个学院的其他同学的信息。 

select * from student where stuColleage=(select stuColleage from student where stuName='张三') AND
stuSex=(select stuSex from student where stuName='李晓辉');

例14：查询成绩大于张三英语成绩的其他同学的姓名和成绩。

第一步，先查询张三的大学英语成绩。

select sc.grade as '成绩' from student s,stu_cou sc,courses c where s.stuID=sc.stuID and c.couID=sc.couID and c.couName='大学英语' and s.stuName='张三';

第二步，以第一步的结果作为条件。

select stuName as '姓名',grade as '成绩' from student s inner join stu_cou sc on s.stuID=sc.stuID
where sc.grade>(select sc.grade as '成绩' from student s,stu_cou sc,courses c where s.stuID=sc.stuID and c.couID=sc.couID and c.couName='大学英语' and s.stuName='张三') and s.stuName<>'张三';

查询结果：

 例15：查询年龄最小的所有同学的信息。

select * from student where stuAge=(select min(stuAge) from student);

(2)having中的子查询

 例16：查询大于物流学院最小年龄的其他学院年龄最小的学生的性别和年龄。 

select stuSex,min(stuAge) as '最小年龄' from student GROUP BY stuSex HAVING min(stuAge)>(select min(stuAge) from student where stuColleage='物流学院');

6-2.多行子查询，多行操作符如下

 (1)运算符ALL和ANY常与某个关系​比较运算符结合在一起使用​，以便测试列子査询的结果。它们会测试比较值与子查询返回的​全部或部分值是否匹配​。​SOME是ANY的同义词。​

例如，当比较值小于或等于子查询返回的每个值时​，<=​ALL的结果为真；当比较值小于或等于子查询返回的任意值时​，<=​ANY的结果为真。

例17：查询大于张三和李四大学英语成绩的其他同学的英语成绩，显示其他同学的姓名和成绩。

第一步：先查询张三和李四的大学英语成绩

select s.stuName,c.couName,sc.grade from student s INNER JOIN stu_cou sc on s.stuID=sc.stuID INNER JOIN courses c on c.couID=sc.couID where c.couName='大学英语' AND s.stuName in('张三','李四');
select s.stuName,c.couName,sc.grade from student s ,stu_cou sc ,courses c where s.stuID=sc.stuID and c.couID=sc.couID and c.couName='大学英语' AND s.stuName in('张三','李四');

第二步：将第一步的查询select后面只返回grade列作为子查询，这里使用>any，只要大于其中一个值即可。

select s.stuName,c.couName,sc.grade from student s INNER JOIN stu_cou sc on s.stuID=sc.stuID INNER JOIN courses c on c.couID=sc.couID where c.couName='大学英语' and sc.grade>any(select sc.grade from student s ,stu_cou sc ,courses c where s.stuID=sc.stuID and c.couID=sc.couID and c.couName='大学英语' AND s.stuName in('张三','李四'));

  

 将>any改为>all时

select s.stuName,c.couName,sc.grade from student s INNER JOIN stu_cou sc on s.stuID=sc.stuID INNER JOIN courses c on c.couID=sc.couID where c.couName='大学英语' and sc.grade>all(select sc.grade from student s ,stu_cou sc ,courses c where s.stuID=sc.stuID and c.couID=sc.couID and c.couName='大学英语' AND s.stuName in('张三','李四'));

结果如下：

  因李四的英语成绩最高，所以没有筛选结果显示。>any只要大于子查询结果中其中某个值即可，>all是要大于子查询结果中所有的值。

(2) IN和NOT INT子查询，当子査询要​返回多个行​来与外层查询进行比较运算时，可以使用运算符​IN​和​NOT IN​。它们会测试某个给定的比较值​是否存在于某一组值里​。

例18：查询没有选择课程号为70的学生的学号和姓名。

select stuID,stuName from student where stuID not in(select stuID from stu_cou where couID=70);

(3)EXISTS、NOT EXISTS子查询（转自：https://blog.51cto.com/u_15346415/5171229）

EXISTS后面的参数是一个​任意的子查询​。其只会测试某个子査询​是否返回了行​。如果有返回，则​EXISTS​的结果为真，而​NOT EXISTS​的结果为假。
下面两条语句展示了这两个运算符的具体用法。如果​student​表为空，那么第一条语句将返回0​，​第二条语句将返回1：

SELECT EXISTS (SELECT * FROM student);
SELECT NOT EXISTS (SELECT * FROM student);

      系统对子查询进行运算以判断​是否返回结果​给外面的查询。如果内查询至少返回一行，那么EXISTS的结果为true，此时外层查询语句开始进行查询。如果子查询没有结果，则EXISTS的结果为false，此时外层查询不进行查询。
      在使用EXISTS和NOT EXISTS时，子査询通常将用作​输出列的列表​。因为这两个运算符是根据子査询是否返回了行来判断真假的，并​不关心行所包含的具体内容​，所以没必要显式地列出列名。事实上，可以在子査询的列选取列表里编写任何东西，但如果想要确保在子査询成功时返回一个真值，则可以把它写成SELECT 1，而不要写成SELECT *。
       EXISTS和NOT EXISTS实际上在​相关子查询里比较常见​。请参阅下面介绍。

(4)相关子查询（作为计算字段使用子查询）（转自：https://blog.51cto.com/u_15346415/5171229）

        子査询要不要相关都可以
       不相关的子査询​不会引用外层査询里的值​，因此它自己可以作为一条的​单独査询命令​去执行。例如，下面这条语句里的子査询就是不相关的，它只引用了t1，而没有引用t2：

SELECT j FROM t2 WHERE j IN(SELECT i FROM t1);

      相关子査询则引用了外层査询里的值，​所以它也就​依赖于外层査询​。因为有了这种联系，所以相关子査询不能脱离外层查询作为一条独立的査询语句去执行。例如，对于下面这条语句里的子査询，其作用是把t2中j列的每一个值与t1中i列的值进行匹配：

SELECT j FROM t2 WHERE (SELECT i FROM t1 WHERE i=j)

    相关子査询通常​用在EXISTS和NOT EXISTS子査询里​，这类子査询主要用于在某个表里査找在另一个表里有匹配行或没有匹配行的行。​相关子査询的工作原理是：​把值从外层査询传递到子査询，并检査它们是否满足子查询里指定的那些条件。因此，如果列名会引起歧义(在多个表里有同名列)，那么必须使用​表名来限定这些列名​。
例19：查询student表中没有选课的学生的学号和姓名。

select stuID,stuName from student where 
not EXISTS(select * from stu_cou where stu_cou.stuID=student.stuID);

     上面的NOT EXISTS子査询可以标识出​两个表之间的匹配情况​——即在​两个表里都存在的那些值​。整条语句的作用是将在student表里没选课的学生査询出来。

例20：查询stu_cou表（选课表）中所有选课的学生的学号、姓名以及所选课程的数量。

select stuID,stuName,(select count(stuID) from stu_cou where stu_cou.stuID=student.stuID) as '选课数量' from student order by stuName;

 （5）FROM子句里的子查询

 

注意，子查询应遵循以下规则（转自https://zhuanlan.zhihu.com/p/393923248）：

(1)由比较运算符引入的内层子查询只包含一个表达式或列名，在外层语句中的WHERE子句内命名的列必须与内层子查询命名的列兼容。

(2)由不可更改的比较运算符引入的子查询(比较运算符后面不跟关键字ANY或ALL)不包括GROUP BY 或 HAVING子句，除非预先确定了成组或单个的值。

(3)用EXISTS引入的SELECT列表一般都由*组成，不必指定列名。

(4)子查询不能在内部处理其结果。

 

参考文章：

https://zhuanlan.zhihu.com/p/393923248   MySQL多表查询史上最强讲解

https://www.cnblogs.com/nf01/articles/15344166.html

https://www.shuzhiduo.com/A/MAzALpZOd9/

https://blog.51cto.com/u_15346415/5171229

posted @ 2022-11-01 21:30  YorkShare  阅读(2508)  评论(1)  编辑  收藏  举报