目录
外连接是对内连接查询结果的补全(查询结果完全显示) 外连接会显示内连接中没有关联匹配的数据(没有匹配的用Null代替) 左外连接 left join(左连接):
在现实生活中的查询往往需要查找多个表中的数据,要求所查询的数据表存在联系,这就涉及到了多表查询了。
一交叉连接:
交叉连接的基本格式:select <[数据库1.]表1.属性1> [,[数据库2.]表2.属性2,...,[数据库n.]表n.属性n] from<[数据库1,]表1> [,数据库2.]表2,...,[数据库n.]表n];
-- 这里涉及到一个笛卡尔积的概念,也就是多个表中所有数据都相互匹配一次
交叉连接的基本格式
-- 交叉连接 -- 交叉连接的基本格式 -- 查询所有班级所在班级的班级名称 select student.s_id,student.s_name,class.c_name from student,class;
笛卡尔积:
select student.*,class.* from student,class; -- 笛卡尔积:连接查询的多个表格中所有的数据都相互匹配一次,形成一个包含所有可能情况的临时数据表 -- 笛卡尔积会显示一些无效的数据,非常影响查询性能,会降低查询效率,所以要尽量避免产生笛卡尔积 -- 交叉连接会产生笛卡尔积
带条件的交叉连接:
-- 查询所有学生所在班级的班级名称 select student.s_id,student.s_name,class.c_name from student,class where student.s_cid = class.c_id; -- 等待连接:外键值等于主键值
二、内连接:
内连接先判断连接条件,根据表中匹配的字段,列出与连接条件匹配的数据,也就是说只有相互匹配的结果才能查询出来。
内连接的关键字:inner join
内连接的基本格式:
slect[数据库1.]表1,属性1,
[数据库2.]表2.属性2,....,[数据库n.]表n.属性n
from[数据库1.]表2.属性2,...,[数据库2.]表2 on 条件表达式1
inner join....inner join [数据库n.] 表n on 条件表达式n;
- 内连接inner join
内连接的基本格式:
-- 查询所有学生所在班级的班级名称
select student.s_id,student.s_name,class.c_name from student inner join class
on student.s_cid = class.c_id;-- 等值连接:外键值=主键值
-- 相同条件下,交叉连接和内连接的查询效果一致
不带条件的内连接:
-- 查询所有学生所在班级的班级名称
select student.s_id,student.s_name,class.c_name from student inner join class;
内连接和交叉连接的区别:
交叉连接是先把所有情况都连接起来,再从中查询出满足条件的数据,也就是先产生笛卡尔积,然后判断条件筛选数据
内连接是先筛选出满足条件的数据,在将筛选出的数据进行连接,避免产生笛卡尔积。
所以内连接比交叉连接的查询性能要好。
内连接产生笛卡尔积,内连接的查询效率比交叉连接要高很多
三、外连接:
外连接是对内连接进行筛选,又分为左外连接(左连接)、右外连接(右连接)和全外连接(全连接)。
1、左外连接(简称左连接)
以左表为基准,显示左表查询字段的所有记录,右表只显示与左表匹配的记录,没有匹配的用Null代替
左连接关键字:left join
左连接基本格式:
select [数据库1.]表1.属性1,
[数据库2.]表2.属性2,...,[数据库n.]表n.属性n
from [数据库1.]表1 left join [数据库2.]表2 on 条件表达式1
left join ... left join [数据库n.]表n on 条件表达式n;
2、外连接:
外连接是对内连接查询结果的补全(查询结果完全显示)
外连接会显示内连接中没有关联匹配的数据(没有匹配的用Null代替)
左外连接 left join(左连接):
-- 查询所有学生所在班级的班级名称
select student.s_id,student.s_name,class.c_name from student left join class
on student.s_cid = class.c_id;
右外连接(简称右连接):
与左连接相反,是以右边为基准,显示右表查询字段的所有记录,左表只显示和右表匹配的记录,没有匹配的用Null代替
右连接关键字:right join
右连接的基本格式:
select [数据库1.]表1.属性1,
[数据库2.]表2.属性2,...,[数据库n.]表n.属性n
from [数据库1.]表1 right join [数据库2.]表2 on 条件表达式1
right join...right join [数据库n.]表n on 条件表达式n;
#举一反三:左右连接可以相互转换吗
答:可以的,只要把两个表的顺序倒过来就可以了,没有特别限制
-- 右外连接right join(右连接)
-- 查询所有学生所在班级的班级名称
select student.s_id,student.s_name,class.c_name from student right join class
on student.s_cid = class.c_id;
select student.s_id,student.s_name,class.c_name from class right join student
on student.s_cid = class.c_id;
-- 右外连接可以相互转换
三、全外连接(简称全连接)
全连接左右表不做限制,显示全部数据,没有匹配的数据用Null代替。
全连接关键字:full join
全连接的基本格式:
select[数据库1.]表1.属性1,
[数据库2.]表2.属性2,...,[数据库n.]表n.属性n
from [数据库1.]表1 full join[数据库2.]表2 on 条件表达式1
full join ... full join [数据库n.]表n on 条件表达式n;
#注意:mysql不支持全外连接,但可以使用union连接两个查询语句来实现全连接
-- 全外连接full join (全连接)
-- 查询所有学生所在班级的班级名称
select student.s_id,student.s_name,class.c_name from student full join class
on student.s_cid = class.c_id;
-- mysql不支持全外连接
四、多表连接查询:
DQL不仅可以连接查询两个表,也可以连接查询多个表中的数据(一般最好不要超过4个表)。
基本格式:
select 表1.属性1,表2.属性2.[,...,表n.属性n]
from 表1 连接(inner/left/right/ join)表2 on 连接条件1
连接(inner/left/right join) 表3 on 连接条件2
...... 连接(inner/left/right/ join) 表n on 连接条件n;
(连接两个表之后写上连接条件,在连接另一个表)
# 进行多表连接查询时应注意连接表之间的外键关系和连接条件,只有表中属性相互有关系的表才能进行连接
多表连接查询:
-- 查询所有学生所在班级的班级名称,以及对应班级的任课老师select student.s_id,student.s_name,class.c_name,teacher.t_name from student left join class on student.s_cid = class.c_id left join teacher on class.c_tid = teacher.t_id;
-- 只有存在外键联系的数据表连接查询才有意义
-- 多表连接查询最好不要涉及到4个以上表的连接(不包括4个表),这样查询效率非常低,是数据库设计的问题
-- 表的连接查询很吃查询性能
五、别名的使用:
mysql支持给属性(字段)取别名,也可以给表取别名。
取别名关键字:as
一般格式:select <属性名> as <属性别名>
from <表名> as <表别名>;
如:
# 用关键字as给查询的属性取别名 select S.s_id as 学号,S.s_name as 姓名 from students as S; -- 取别名时as可以忽略 select S.s_id 学号,S.s_name 姓名 from students S; -- 给表取别名能够缩短连接查询的解析时间,使查询效率更高
select S.s_id 学号,S.s_name 学生姓名,C.c_name 班级名称,T.t_name 任课老师 from student S left join class C on S.s_cid = C.c_id left join teacher T on C.c_tid = T.t_id;
别名的使用:
-- 查询所有学生所在班级的班级名称,以及对应班级的任课老师
select S.s_id as 学号,S.s-name as 学生姓名,C.c_name as 班级名称,T.t_name as 任课老师
from student as S left join class as C on S.s_cid = C.c_id
left join teacher as T on C.c_tid = T.t_id;
-- 可以使用关键字as给属性名和表名取别名
-- 给数据表取别名可以减少表名的解析时间,从而提高查询效率
-- 关键字as可以省略
-- 查询所有学生所在班级的班级名称,以及对应班级的任课老师
select S.s_id 学号,S.s_name 学生姓名,C.c_name 班级名称,T.t_name 任课老师 from student S left join class C on S.s_cid = C.c_id left join teacher T on C.c_tid = T.t_id;