一.连接查询:
有SQL92语法和SQL99语法之分
1. SQL92的基本形式:
主要讲内连接的三种:
等值连接
#等值连接
SELECT 查询列表
FROM 表1 别名,表2 别名
WHERE 表1.关键字段=表2.关键字段
【AND 筛选条件】
【GROUP BY 分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
/*
特点:
①一般为表起别名
②多表的顺序可以调换
③N表连接至少需要N-1个连接条件
④等值连接的结果是多表的交集部分
*/
非等值连接
#非等值连接
SELECT 查询列表
FROM 表1 别名,表2 别名
WHERE 非等值的连接条件(如:between...and..,<>,>,<,等)
【AND 筛选条件】
【GROUP BY 分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
自连接
#自连接
SELECT 查询列表
FROM 表1 别名a,表1 别名b
WHERE 别名a.关键字段=别名b.关键字段
【AND 筛选条件】
【GROUP BY 分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
#实质就是将一张表查询了两次,看成两张表
2.SQL99的基本形式:
2.1 内连接:
只是将sql92语法里面的两张表之间的逗号,用inner jion 连接起来,两张表的连接条件用在on后面写下来,where 后面还是可以写筛选条件。
2.1.1等值连接
#等值连接
inner join
作为连接表与表之间的关键字
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件
【where 筛选条件】
【GROUP BY 分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
2.1.2非等值连接
#非等值连接
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 非等值的连接条件(如:between...and..,<>,>,<,等)
【WHERE 筛选条件】
【GROUP BY 分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
2.1.3自连接
#自连接
SELECT 查询列表
FROM 表1 别名a
INNER JOIN 表1 别名b
ON 连接条件(比如:别名a.关键字段=别名b.关键字段)
【WHERE筛选条件】
【GROUP BY 分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
2.2 外连接
应用场景:用于查询一个表中有,另一个表中没有的记录
特点:
1. 外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示匹配null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2. 左外连接,left jion 左边的是主表
右外连接,right jion 右边的是主表
3. 左外和右外 交换两个表的顺序,可以实现同样的效果
2.2.1左外连接
#左外连接
SELECT 查询列表
FROM 表1 别名
LEFT JOIN 表2 别名
ON 连接条件
【where 筛选条件】
【GROUP BY 分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
2.2.2右外连接
#右外连接
SELECT 查询列表
FROM 表1 别名
RIGHT JOIN 表2 别名
ON 连接条件
【where 筛选条件】
【GROUP BY 分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
2.2.3全外连接
#全外连接
SELECT 查询列表
FROM 表1 别名
FULL JOIN 表2 别名
ON 连接条件
【where 筛选条件】
【GROUP BY 分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
2.3交叉连接
特点:
在SQL92语法中,笛卡尔乘积的产生是两张表用逗号隔开,没有加where 连接条件产生的
在SQL99语法中,笛卡尔乘积的产生就是交叉连接的查询
#交叉连接
SELECT 查询列表
FROM 表1 别名
CROSS JOIN 表2 别名
【where 筛选条件】
【GROUP BY 分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
3. 区别就在于:
原来sql92语法里面的两张表之间的 逗号 ,用 关键字 (inner,left,right,full,cross) jion 连接起来,两张 表的连接条件用在on后面 写下来,where 后面写筛选条件
4.案例
两张表beauty和boys
1.等值连接案例(内连接中的inner jion 中的inner 可以省略,以下案例并未省略)
要求1:查询女生的男朋友信息
等值连接查询:两种写法参考对比,更加容易理解记忆
# SQL92语法
select name,boyname from boys b,beauty a where a.boyfrind_id=b.id
# SQL99语法
select name,boyname from beauty INNER JOIN boys on beauty.boyfrind_id=boys.id
两条语句的执行结果完全一样:
要求2:查询女生的男朋友信息,其中男朋友名称必须带有‘张’字
思路:带有筛选条件的等值连接查询
#SQL92
select name,boyname
from boys b,beauty a
where a.boyfrind_id=b.id and boyname like '%张%';
#SQL99
select name,boyname
from beauty a INNER JOIN boys b
on a.boyfrind_id=b.id
where boyname like '%张%';
结果完全一样:
要求3:查询每个男生中拥有过女朋友的个数大于2 的男生名称和女朋友个数
思考:分组函数,分组查询group by ,筛选条件 都要用到
#SQL92
select boyname,count(*)
from boys b,beauty a
where a.boyfrind_id=b.id
group by boyname
having count(boyname)>2
#SQL99
select boyname,count(*)
from beauty INNER JOIN boys
on beauty.boyfrind_id=boys.id
group by boyname
having count(boyname)> 2
结果一样:
要求4:inner jion 的三表连接 (注意书写格式,N张表需要N-1个连接条件)
截图课件案例(和另外两张都有关联的表,放在最前面)
2.非等值连接的案例 (内连接中的inner jion 中的inner 可以省略)
案例1:
案例2:
3.自连接的案例 (内连接中的inner jion 中的inner 可以省略)
4. 左(右) 外连接【left (right)outer …join…中outer可以省略】
外连接
应用场景:用于查询一个表中有,另一个表中没有的记录
特点:
1. 外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示匹配null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2. 左外连接,left jion 左边的是主表
右外连接,right jion 右边的是主表
3. 左外和右外 交换两个表的顺序,可以实现同样的效果
案例1:
要求:查询男朋友 不在男神表的女神表:
#查询男朋友 不在男神表的女神表:
#左外连接实现:
select g.id,g.name,b.boyName
from beauty g LEFT join boys b
on g.boyfrind_id=b.id
where b.boyName IS NULL
#右外连接实现:
select g.id,g.name,b.boyName
from boys b right join beauty g
on g.boyfrind_id=b.id
where b.boyName IS NULL
效果一样:
案例2:
课件案例截图
5.全外连接 (mysql不支持,oracle支持)
全外连接 = 内连接的结果 + 表1中有但表2中没有的 + 表2中有但表1中没有的
注意:mysql不支持全外连接,oracle支持,两张表没有顺序关系
语法结构一样。举例:
#两张表的数据全部查询,不分主表,从表
select g.*,b.*
FROM beauty g FULL JOIN boys b
ON g.boyfrind_id=b.id
6.交叉连接(就是笛卡尔乘积)
在SQL92语法中,笛卡尔乘积的产生是两张表用逗号隔开,没有加where 连接条件产生的
在SQL99语法中,笛卡尔乘积的产生就是交叉连接的查询
#SQL92 笛卡尔乘积
SELECT g.*,b.*
FROM beauty g,boys b
#SQL99 交叉连接(笛卡尔乘积)
SELECT g.*,b.*
FROM beauty g CROSS JOIN boys b
结果:
二、关于join的集合的图形记忆