一、多表关系
在进行数据库表结构的设计时,会根据业务的需求和业务模块之间的关系,分析设计表结构,由于业务之间相互关联,所以各个表结构之间也存在各种联系
1.表与表之间的联系:
1.一对多(多对一)
2.多对多
3.一对一
2.多表查询概述:
概述:指从多张表中查询数据
笛卡尔积:笛卡尔积乘积是指在数学中,两个集合 A集合和B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
1.连接查询:将多个数据表一起查询
内连接:相当于查询A、B交集部分数据
外连接:
左连接:查询左表所有数据,以及两张表交集部分的数据
SELECT 字段列表 FROM 表1 LEFT[OUTER]JOIN 表2 ON 条件...
右连接:查询右表所有数据,以及两张表交集部分的数据
SELECT 字段列表 FROM 表1 RIGHT[OUTER]JOIN 表2 ON 条件...
自连接:当前表与自身的连接查询,自连接必须使用表表名
SELECT 字段列表 FROM 表A 表名A JOIN 表A 表名B ON 条件....
新建两张表:
表1:student 截图如下:
表2:course 截图如下:
(此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键。)
一、外连接
外连接可分为:左连接、右连接、完全外连接。
1、左连接 left join 或 left outer join
SQL语句:select * from student left join course on student.ID=course.ID
执行结果:
左外连接包含left join左表所有行,如果左表中某行在右表没有匹配,则结果中对应行右表的部分全部为空(NULL).
2、右连接 right join 或 right outer join
SQL语句:select * from student right join course on student.ID=course.ID
执行结果:
右外连接包含right join右表所有行,如果左表中某行在右表没有匹配,则结果中对应左表的部分全部为空(NULL)。
3、完全外连接 full join 或 full outer join
SQL语句:select * from student full join course on student.ID=course.ID
执行结果:
完全外连接包含full join左右两表中所有的行,如果右表中某行在左表中没有匹配,则结果中对应行右表的部分全部为空(NULL),如果左表中某行在右表中没有匹配,则结果中对应行左表的部分全部为空(NULL)。
二、内连接 join 或 inner join
SQL语句:select * from student inner join course on student.ID=course.ID
inner join 是比较运算符,只返回符合条件的行。
此时相当于:select * from student,course where student.ID=course.ID
三、交叉连接 cross join
1.概念:没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
SQL语句:select * from student cross join course
如果我们在此时给这条SQL加上WHERE子句的时候比如SQL:select * from student cross join course where student.ID=course.ID
此时将返回符合条件的结果集,结果和inner join所示执行结果一样。
四、两表关系为一对多,多对一或多对多时的连接语句
当然上面两表为一对一关系,那么如果表A和表B为一对多、多对一或多对多的时候,我们又该如何写连接SQL语句呢?
其实两表一对多的SQL语句和一对一的SQL语句的写法都差不多,只是查询的结果不一样,当然两表也要略有改动。
比如表1的列可以改为:
Sno Name Cno
表2的列可以改为:
Cno CName
这样两表就可以写一对多和多对一的SQL语句了,写法和上面的一对一SQL语句一样。
下面介绍一下当两表为多对多的时候我们该如何建表以及些SQL语句。
新建三表:
表A: student 截图如下:
表B: course 截图如下:
表C: student_course 截图如下:
一个学生可以选择多门课程,一门课程可以被多个学生选择,因此学生表student和课程表course之间是多对多的关系。
当两表为多对多关系的时候,我们需要建立一个中间表student_course,中间表至少要有两表的主键,当然还可以有别的内容。
SQL语句:select s.Name,C.Cname from student_course as sc left join student as s on s.Sno=sc.Sno left join course as c on c.Cno=sc.Cno
2.union all 、union:
UNION
UNION 是一个用于合并多个 SELECT 查询结果的操作符。它的作用是将多个查询的结果集合并为一个结果集,并去除重复的行。
主要特点和作用如下:
合并结果集:
UNION 操作符可以将多个查询的结果集合并为一个结果集。每个查询的结果集必须具有相同的列数和相似的数据类型。
去除重复行:
与 UNION ALL 操作符不同,UNION 会去除合并结果集中的重复行,只保留一份。它通过对结果集进行去重操作来实现。
排序结果:
UNION 默认会对合并后的结果集进行排序,以确保返回的结果是按照默认顺序排列的。如果需要自定义排序顺序,可以使用外部的 ORDER BY 子句来指定排序条件。
总结起来,
UNION 是用于合并多个查询结果集的操作符,它合并结果集并去除重复行,适用于需要合并结果集并去重的场景。
UNION ALL
UNION ALL 是一个用于合并多个 SELECT 查询结果的操作符。它的作用是将多个查询的结果集合并为一个结果集,包含所有查询的结果,不去除重复行。
主要特点和作用如下:
合并结果集:
UNION ALL 操作符可以将多个查询的结果集合并为一个结果集。每个查询的结果集必须具有相同的列数和相似的数据类型。
保留重复行:
与 UNION 操作符不同,UNION ALL 不会去除重复的行。它简单地将所有查询的结果按照顺序连接起来,保留所有行,包括重复的行。
总结起来,
UNION ALL 是用于合并多个查询结果集的操作符,它保留所有行,不去除重复的行,适用于需要合并结果集并且不需要去重的场景。性能更高一些。
3.子查询定义
子查询指一个查询语句嵌套在另一个查询语句内部的查询
SELECT * FROM t1 WHERE column1 = (SELECT colimn1 FROM t2);
1.根据子查询结果不同,分为:
1.标量子查询(子查询结果为单个值)
2.列子查询(子查询结果为一列)
3.行子查询(子查询结果为一行)
4.表子查询(子查询结果为多行多列)
2.根据子查询位置,分为:WHERE之后、FROM之后、SELECT之后。
2.1标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询
常用的操作符:= <> > >= <
2.2列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询
常用的操作符:IN NOTIN SOME ALL
例如:有两张表:商品表1和商品表2
-- 查询电冰箱和电视机的所有销售信息
select * from 商品表2 where 商品代号 in(select 商品代号 from 商品表1 where 分类名 = '电冰箱' or 分类名 = '电视机');
-- 查询出比 电冰箱单价都要高的其他商品的信息
select 商品代号 from 商品表1 where 分类名 = '电冰箱';
select 单价 from 商品表1 where 商品代号 = (select 商品代号 from 商品表1 where 分类名 = '电冰箱');
select * from 商品表1 where 单价 > all (select 单价 from 商品表1 where 商品代号 = (select 商品代号 from 商品表1 where 分类名 = '电冰箱'));
select * from 商品表1 where 单价 > any (select 单价 from 商品表1 where 商品代号 = (select 商品代号 from 商品表1 where 分类名 = '电冰箱'));
2.3行子查询:
子查询返回的结果是一行(可以是多行),这种子查询称为行子查询
常用的操作符:= <> IN NOT IN
-- 查询出与 刘芹 的性别及专业相同的学生信息
select 性别,专业 from 学生 where 姓名 = '刘芹';
select * from 学生 where 性别 = '女'and 专业 = '电子';
select * from 学生 where (性别,专业) = (select 性别,专业 from 学生 where 姓名 = '刘芹');
2.4表子查询:
子查询返回的结果是多行多列,这种子查询称为表子查询
常用的操作符: IN