MySQL联合查询
联合查询
前言
使用联合查询的原因
在数据库设计的范式下，有时候数据不可以放在一个表中，我们要分成多个表，但是我们在查询的时候可能是要获取这多个表中的信息，因此我们就需要联合这多个表进行查询
createtable class( id intprimarykeyauto_increment, name varchar(20));createtable student( id intprimarykeyauto_increment, name varchar(20), sex char(1), class_id int,foreignkey(id)references class(id));insertinto class(name)values('java113'),('java78'),('C++110');insertinto student(name, sex,class_id)values('张三','男',1),('李四','女',1),('王五','男',2);
如果我们用多表查询，则会出现下面这种情况
select*from student,class;
这两个表的联合查询，其实是笛卡尔积，因此其查询的列数就是两个表的列数之和，行数就是其两个表列数之积
如果表内容多，其数据就会变成非常庞大，因此上面这样我们联合查询是不对的，并且其班级id要一样才是正确的结果 ，剩余都是错误的结果
为了让其按照我们的要求查询，因此我们要加上where条件，当其class表中的id和student表中的class_id相等时候才可以
--此时只需要加上where条件即可select*from student,class where student.class_id = class.id;此时查询结果就合理了
由于使用select * 查询比较耗时，因此我们此时也可以指定列，但是我们的形式是 表名.列名，因为两个表中可能有相同的列名，因此要加上列名
如果两个表中列名不相同的列，其可以不添加表名
--查询其对应列的信息select student.id,student.name,sex,student.class_id,class.id,class.name from student,class where student.class_id = class.id;--查询张三的信息select student.id,student.name,student.sex,student.class_id,class.id,class.name from student,class where student.class_id = class.id and student.name ='张三';
如果我们不添加其对应的表，并且还是相同的列名，此时就会出现此时的id对应的列是摸棱两可的，就是我们不知道这个列是来自那个表，因此就会出错
联合查询步骤
1.确定查询表 -->进行笛卡尔积
2.确定连接条件
3.加入查询条件
4.精简查询列，也可以给表取别名
内连接
上面我们只是简单的介绍了可以联合查询，下面我们就来详细介绍一些联合查询的方式
1select 字段 from 表1 别名1, 表2 别名2where 连接条件 and 其他条件;2select 字段 from 表1 别名1[inner]join 表2 别名2on 连接条件 where 其他条件;--此时的别名不是必须要取的，并且此时取别名的as是可以省略的此时我们创建四个表来举例
--创建课程表createtable course( id intprimarykeyauto_increment, name varchar(20));--班级表createtable class( id intprimarykeyauto_increment, name varchar(20));--学生表createtable student( id intprimarykeyauto_increment, name varchar(20), class_id int,foreignkey(class_id)references class(id));--分数表createtable score( student_id int, course_id int, score int,foreignkey(student_id)references student(id),foreignkey(course_id)references course(id));# 课程表insertinto course (name)values('Java'),('C++'),('MySQL'),('操作系统'),('计 算机网络'),('数据结构');# 班级表insertinto class(name)values('Java001班'),('C++001班'),('前端001班');# 学⽣表insertinto student (name, class_id)values('唐三藏',1),('孙悟空',1),('猪悟能',1),('沙悟净',1),('宋江',2),('武松',2),('李逹',2),('不想毕业',2);# 成绩表insertinto score (score, student_id, course_id)values(70.5,1,1),(98.5,1,3),(33,1,5),(98,1,6),(60,2,1),(59.5,2,5),(33,3,1),(68,3,3),(99,3,5),(67,4,1),(23,4,3),(56,4,5),(72,4,6),(81,5,1),(37,5,5),(56,6,2),(43,6,4),(79,6,6),(80,7,2),(92,7,6);查询唐三藏的所有学科成绩
-- 我们用上面两种方式都可以select s.name,sc.score from student as s,score as sc where s.id = sc.student_id and s.name ='唐三藏';select s.name,sc.score from student as s join score sc on s.id = sc.student_id and s.name ='唐三藏';
例如查询所有学生的总成绩
select s.name,sum(sc.score)from student as s,score as sc where s.id = sc.student_id groupby(s.id);-- 要进行分组，因为其求和肯定要是一个学生的成绩
查询所有同学的详细信息包括考试成绩
select s.id, s.name as 姓名, c.name as 课程, sc.score as 分数 from course c, student s, score sc where s.id = sc.student_id and c.id = sc.course_id orderby s.id;
外连接
外连接分为左外连接、右外连接和全外连接，但是MySQL是不支持全外连接
左外连接：返回左表中的所有记录和右表匹配的记录，如果右表没有匹配到，结果集会显示空
右外连接：和左外连接相反，返回右表中的所有记录和左表匹配的记录，如果左表没有匹配到，结果集会显示空
全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹配的记录，则结果集中对应字段会显⽰为NULL
-- 左外连接，左表表1完全显⽰ select 字段名 from 表名1leftjoin 表名2on 连接条件;-- 右外连接，右表表2完全显⽰ select 字段 from 表名1rightjoin 表名2on 连接条件;左外连接
以上面的为例，查询没有参加考试的学生
这时候就可以让student左外连接score，这样就会把学生全部显示出来
到时候如果成绩为空，说明其没有参加考试
select s.id,s.name,sc.*from student s leftjoin score sc on s.id = sc.student_id;
# 因为未考试的成绩为空，因此其可以筛选掉考试的同学select s.id,s.name,s.class_id from student s leftjoin score sc on s.id = sc.student_id where sc.score isnull;
右外连接
查询没有学生的班级
select*from student s rightjoin class c on s.class_id = c.id;
# 此时没有学生班级其学生的id肯定为空select*from student s rightjoin class c on s.class_id = c.id where s.id isnull;
自连接
自连接就是自己对自己求笛卡尔积，在查询的时候可以使用where条件对结果进行过滤，或者可以使用自己对自己表的数据进行比较，但是自连接的时候要对表起别名
select*from score,score;此时会报错，因为使用两个名字相同的表或者是没有取别名
select*from score as s1,score as s2;查询MySQL成绩大于java成绩大的学生
分开写 # 先获取起对应课程idselect id,name from course where name ='MySQL'or name ='java';select*from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id =3and s2.course_id =1#学生id相同and s1.score > s2.score;# MysSQL 成绩>java-- 放在一起写SELECT s1.*, s2.*FROM score s1, score s2, course c1, course c2 WHERE s1.student_id = s2.student_id AND s1.course_id = c1.id AND s2.course_id = c2.id AND c1.NAME ='MySQL'AND c2.NAME ='java'AND s1.score > s2.score;
此时如果想看到其所有信息，就要将其student 、class和score都要进行笛卡尔积
# 显示其所有信息SELECT stu.name as 姓名, c.name as 班级, s1.score as MySQL, s2.score as java FROM score s1, score s2, course c1, course c2, student stu, class c WHERE s1.student_id = s2.student_id AND s1.course_id = c1.id AND s2.course_id = c2.id and stu.id = s1.student_id and stu.class_id = c.id AND c1.NAME ='MySQL'AND c2.NAME ='java'AND s1.score > s2.score 
子查询
有时候我们进行查询时候的where条件可能还需要进行查询，就是一个查询的结果作为另一个查询的条件
select*from table1 where col_name1 {=|IN} (select col_name1 from table2 where col_name2 {=|IN} [(select...)]...)单行子查询
where条件里的查询只返回一行数据
例如查询姓名为 '不想毕业’的同学
因此要先查找其班级id，后面再根据其班级id进行查找
select*from student where class_id =(select class_id from student where name ='不想毕业');
当然这个我们还是可以分开写的
select class_id from student where name ='不想毕业';select*from student where class_id =2;多列子查询
返回多行数据，因此我们要是用[not ] in 关键字
查询课程MySQL和java的成绩
select*from score where score.course_id in(select id from course where name ='MySQL'or name ='java');
当然也可以查询除了MySQL和java的成绩
select*from score where score.course_id in(select id from course where name ='MySQL'or name ='java');
多列子查询
多列子查询就是返回的是多个列的数据
例如：查询重复录入的分数
insertinto score(score, student_id, course_id)values(70.5,1,1),(98.5,1,3),(60,2,1);# 查询重复成绩select*from score where(score,student_id,course_id)in(select score,student_id,course_id from score groupby score,student_id,course_id havingcount(*)>1);
from 语句中使用子查询
我们在进行查询的时候，MySQL是自动创建一个临时表来存放，因此我们可以那这个临时表进行子查询或者表连接操作
例如：查询所有比‘java001班’平均分高的成绩信息
# 先求平均分selectavg(sc.score)from score sc,class c,student s where c.id = s.class_id and sc.student_id = s.id and c.name ='java001班';
SELECT*FROM score s,(SELECTavg( sc.score ) score FROM score sc, class c, student s WHERE c.id = s.class_id AND sc.student_id = s.id AND c.NAME ='java001班') tmp WHERE s.score > tmp.score;tmp是临时表
合并查询
开发过程中，为了合并多个select 返回的结果，可以使用操作集 union 、union all
# 先创建一个和原本student相同的表createtable student1 like student;# 插入一些数据insertinto student1 (name, class_id)values('唐三藏',1),('刘备',3),('张飞',3),('关羽',3);# 此时select*from student1;
union
用于两个结果的并集，并且可以去掉重复行
⽰例：查询student表中id<3的同学和student1表中的所有同学
# 查询student表中student中id < 3的同学和student1表中的所有同学select*from student where id <3unionselect*from student1;此时会根据要求将两个select结果合并，并且去掉了重复部分
Union all
也是用于合并结果集，此操作符是不可以去掉结果的重复行
select*from student where id <3unionallselect*from student1;此时就可能会有重复的行
插入查询结果
可以将一个查询结果当作数据插入一个表中
INSERTINTO table_name [(column[,column...])]SELECT...例如：将student表中C++001班的学⽣复制到student1表中
insertinto student1 (name,class_id)select s.name ,s.class_id from student s ,class c where s.class_id = c.id and c.name ='C++001班';
到这里就结束了，欲知后事如何，请听下回分解
