mysql学习笔记二

3.6.连接查询

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

连接查询分类标准：

按年代分类：sql92标准(仅支持内连接), sql99标准(支持内连接，外连接中的左外连接和右外连接，交叉连接)

sql99语法

select 查询列表
from 表一(别名) 连接类型
join 表二(别名)
on 连接条件
(where 筛选条件)
(group by 分组)
(having 筛选条件)
(order by 排序列表)

按功能分类：

​ 内连接：等值连接，非等值连接，自连接

​ 外连接：左外连接，右外连接，全外连接

​ 交叉连接

表一

a	b	c
a1	b1	c1
a2	b2	c2
a3	b3	c3

表二

a	d	e
a2	d2	e2
a3	d3	e3
a4	d4	e4

3.6.1.内连接

在sql99中内连接为 inner

内连接中的示例语法都是sql92语法

笛卡尔连接

a	b	c	a	d	e
a1	b1	c1	a2	d2	e2
a1	b1	c1	a3	d3	e3
a1	b1	c1	a4	d4	e4
a2	b2	c2	a2	d2	e2
a2	b2	c2	a3	d3	e3
a2	b2	c2	a4	d4	e4
a3	b3	c3	a2	d2	e2
a3	b3	c3	a3	d3	e3
a3	b3	c3	a4	d4	e4

3.6.1.1.等值连接

select 查询列表 from 表1， 表2.... where 连接条件 and 筛选条件

这里的连接条件是a表的字段和b表的字段相等的条件

先经过笛卡尔连接，再把符合连接条件的条目筛选出来

如果根据两个表中的a位筛选条件的话，结果如下：

1.a	b	c	2.a	d	e
a2	b2	c2	a2	d2	e2
a3	b3	c3	a3	d3	e3

3.6.1.2.非等值连接

select 查询列表 from 表1， 表2.... where 连接条件 and 筛选条件

这里的连接条件是a表的字段和b表的除了相等外的别的条件，比如说>, < 等

3.6.1.3.内连接

就是上面表一和表二换成两张相同的表，然后进行笛卡尔连接

再根据连接条件筛选出来合适的条目

3.6.2.外连接

一般用于查询一个表中有，而另一个表中没有的数据

3.6.2.1.左外连接

以一个表为基准，然后基准表中的每一条数据去匹配另一张表中的每一条数据。

当基准表的一条数据与另一张表的一条数据符合连接条件时，保存下来，之后接着向下匹配另一张表的数据。

如果没有找到像匹配的数据，则该条数据连接后的另一张表的字段值都为空。

连接类型：left (outer)

表一 left jion 表二 on 1.a = 2.a

1.a	b	c	2.a	b	c
a1	b1	c1	null	null	null
a2	b2	c2	a2	d2	e2
a3	b3	c3	a3	d3	e3

3.6.2.2.右外连接

原理跟左外连接相似

连接类型：right (outer)

表一 rightjion 表二 on 1.a = 2.a

1.a	b	c	2.a	b	c
a2	b2	c2	a2	d2	e2
a3	b3	c3	a3	d3	e3
null	null	null	a4	d4	e4

3.6.2.3.全外连接

mysql语言不支持 full (outer)

两个表相互用映射，然后可以把两个表没有匹配到的数据都输出出来,相当于左连接加右连接

具体效果如下表：

1.a	b	c	2.a	b	c
a1	b1	c1	null	null	null
a2	b2	c2	a2	d2	e2
a3	b3	c3	a3	d3	e3
null	null	null	a4	d4	e4

3.6.3.交叉连接

cross join 相当于使用sql99标准实现笛卡尔连接

---

3.7.子查询

出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句成为外查询或主查询

出现位置：select后，from后，where或having后，exists后（相关子查询）

按结果集的行数不同分为：标量子查询（结果只有一列一行），列子查询（结果集只有一列多行），

行子查询（结果集有一行多列） 表子查询（结果集一般为多行多列）

3.7.1.where或having后

1. 标量子查询（单行子查询）
2. 列子查询（多行子查询）
3. 行子查询（多列多行）

特点：

1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 标量子查询一般搭配着单行操作符使用：> < >= <= = <>
4. 列子查询一般搭配着多行操作符使用 in, not in, any/some, all
5. 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

标量子查询部分示例

where (或 having) (判断条件 and) 与子查询比较的筛选内容   单行操作符 (标量子查询) 

列自查询部分示例

where (或 having) (判断条件 and) 与子查询比较的筛选内容   多行操作符 (标量子查询) 

行子查询部分示例

where (或 having) (判断条件 and) 
(与子查询比较的筛选内容1, 与子查询比较的筛选内容2, ...) 操作符 
    (select 与子查询比较的筛选内容1, 与子查询比较的筛选内容2, ...
        from ......
        where .....
    )

3.7.2.select后

select 后面的子查询只返回一个字段一条数据，结果具有唯一性,即标量子查询

示例

select 查询列表, (标量子查询)......

这里的标量子查询将的结果将添加在主查询的每行数据中

其中标量子查询可以用于解决：聚合函数无法和分组后没有出现的字段一起使用的问题

3.7.3.from后

from后面可以放各种类型的结果集，但是要符合题意的要求，在这里就是把结果集当成一张新表进行查询

示例

select 查询列表 from (子查询) ......

3.7.4.exists后

exists又叫相关子查询，它不关心后面子查询的类型，只关心子查询是否存在内容，如果存在返回1，不存在返回0

示例

exists(完整子查询语句)

相关子查询先查询外查询再执行子查询

---

3.8.分页查询

当显示的数据，一页显示不全，需要分页提交sql请求

语法

select 查询列表
from 表名 (连接查询....)
(where 筛选条件)
(group by 分组字段)
(having 筛选条件)
(order by 排序的字段)
limit (offset,) size

offset：要显示条目的起始索引 ，这里的索引是从0开始

size：表示要显示的条目个数

limit是放在查询语句的最后，实际应用传参时一般是 limit (page - 1) * size, size

---

3.9.联合查询

union 联合，合并：将多条查询语句的结果合并成一个结果

通常用于要查询的内容来自多个表，并且表之间没有直接的连接关系，但查询的信息一致

查询语句1
union
查询语句2
union
查询语句3
....
查询语句n;

等价于

select 查询列表 from 表名 where 筛选条件1 or 筛选条件2 .... 筛选条件n

各个查询语句的结果中，列数必须相同，列名与第一条查询语句设置的列名相同

联合查询会去掉重复的数据，如果不想去重可以用 union all 来保留重复项