MySQL学习Day06——多表连接和子查询

一、MySQL的多表操作

在实际的开发中，一个项目往往需要多张表才能够完成。表和表之间存在一定的关系。

二、多表关系:MySQL多表之间的关系可以概括为一对一、一对多/多对一关系和多对多关系

1.一对一关系:在任一表中添加唯一外键，指向另一方主键，确保一对一的关系，一对一关系一般比较少见，遇到一对一关系的表最好是合并表。

2.一对多/多对一关系:在多的一方建立外键，指向一的一方的主键

3.多对多关系:多对多关系的实现需要借助第三张中间表，中间表至少包含两个字段，将多对多的关系拆分成一对多的关系。中间表至少要有两个外键，这两个外键分别指向原来的两张表的主键

三、外键约束:

MySQL外键约束(FOREIGN KEY)是表的一个特殊字段，经常与主键约束一起使用，对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表，外键所在的表就是从表。外键用来建立主表和从表的关联关系，为两个表的数据建立连接，约束两个表中的数据一致性和完整性。

外键约束特点:

1.主表必须已经存在于数据库中，或是当前正在创建的表

2.必须为主表定义主键。

3.主键不能包含空值，但允许在外键中出现空值，也就是说只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

4.在主表的表名后面指定列名或列名的组合，这个列或列的组合必须是主表的主键或者候选键

5.外键中列的数目必须和主表的主键中列的数目相同

6.外键中列的数据类型必须和主表主键中对应列的数据类型相同

创建外键约束:

方式1:在创建表时设置外键约束，即在create table语句中，通过foreign key关键字指定外键，具体的语法格式如下：

[constraint <外键名>] foreign key (字段名[,字段名2,...,字段名n]) reference <主表名> (主键列1[,主键列2,...,主键列n])

方式2:外键约束也可以在修改表时添加，但是添加外键约束的前提是从表中外键列中的数据必须与主表主列中的数据一致或是没有数据

alter table <从表名> add constraint <外键名> foreign key (字段名[,字段名2,...,字段名n]) reference <主表名> (主键列1[,主键列2,...,主键列n])

在外键约束下的数据操作:

1.给从表添加数据时外键列的值必须依赖表的主键列

2.主表的数据被从表依赖时不能删除，从表的数据可以随意删除

删除外键约束:

当一个表不需要外键约束时，就需要从表中将其删除，外键一旦删除，就会解除主表和从表之间的关联关系

alter table <表名> drop foreign key <外键约束名>;

四、多表联合查询:多表联合查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据时需要显示的数据来自多张表。

1.交叉连接查询(产生笛卡尔积):交叉连接查询返回被连接的两张表所有数据行的笛卡尔积，笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配。假如A表有m行数据，B表有n行数据，则返回m*n行数据。笛卡尔积会产生很多的冗余数据，后期的其他查询条件可以在该集合的基础上进行条件筛选。交叉连接时两个表数据一一对应，返回结果的行数等于两个表行数的乘积。其格式如下:

select * from A,[cross join] B;

2.内连接查询(使用的关键字inner join,inner可以省略):带有条件的交叉连接，内连接查询多张表的交集。内连接根据联结条件来组合两个表中的字段，以创建一个新的结果表。假如我们想将表 1 和表 2 进行内连接，SQL 查询会逐个比较表 1 和表 2 中的每一条记录，来寻找满足联结条件的所有记录对。当联结条件得以满足时，所有满足条件的记录对的字段将会结合在一起构成结果表。简单的说，内连接就是取两个表的交集，返回的结果就是连接的两张表中都满足条件的部分。

笛卡尔积的错误会在下面条件中产生:省略多个表的连接条件、连接条件无效、所有表中的所有行互相连接。为了避免笛卡尔积，可以在WHERE中加入有效的连接条件。如果要查询的字段在两个表中都出现，则需要写作TableName.ColumnName,即指明该字段所在的表。从SQL优化的角度，建议多表查询时在每个字段前都指明其所在的表。可以在SELECT和WHERE中使用在FROM中给表起的别名，但是如果一旦给表起了别名，一旦在SELECT或WHERE中使用表名的话则必须使用表的别名，而不能再使用表的原名。内连接时多个连接条件之间使用and连接，如果有n张表连接则至少需要n-1个连接条件。

在MySQL中，内连接inner join和join等价

隐式内连接(SQL92):SELECT * FROM A,B WHERE 条件;
显式内连接(SQL99):SELECT * FROM A INNER JOIN(JOIN) B ON 条件;

3.外连接查询(使用关键字LEFT/RIGHT JOIN):合并具有同一列的两个以上的表的行，结果集合中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行。

(1)左外连接(LEFT JOIN):两个表在连接的过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，结果行数等于左表的行数，右表中不符合要求的列值为NULL。外连接查询中的左外连接就是指新关系中执行匹配条件时，以关键字 LEFT JOIN 左边的表为参考表。左外连接的结果包括 LEFT JOIN 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行，这就意味着，左连接会返回左表中的所有记录，加上右表中匹配到的记录。如果左表的某行在右表中没有匹配行，那么在相关联的结果行中，右表的所有选择列表均为空值。

(2)右外连接:两个表在连接的过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行,结果行数等于右表的行数，左表中不符合要求的列值为NULL.外连接查询中的右外连接是指新关系中执行匹配条件时，以关键字 RIGHT JOIN 右边的表为参考表，如果右表的某行在左表中没有匹配行，左表就返回空值。

(3)满外连接:结合了 LEFT JOIN 和 RIGHT JOIN 的结果。MySQL目前不支持FULL JOIN方式,想要实现全连接可以使用 UNION ALL 来将左连接和右连接结果组合在一起实现全连接。

外连接的格式:
SELECT column_name1,column_name2 ... column_namen
FROM table1 LEFT | RIGHT | FULL  (OUTER) JOIN table2
ON CONDITION;
左外连接:
SELECT * FROM A LEFT JOIN B ON 条件;
以左表(A)为主，会把左表所有的数据都会输出，右表没有数据便会输出null
右外连接:
SELECT * FROM A RIGHT JOIN B ON 条件;
以右表(B)为主，会把右表所有的数据都会输出，左表没有数据便会输出null
满外连接:
SELECT * FROM A FULL JOIN B ON 条件;
左外连接的结果和右外连接的结果的并集

(4)UNION/UNION ALL关键字:合并查询结果，可以给出多条SELECT语句，并将它们的结果组合成单个结果集，合并时，两个表对应的列数和数据类型必须相同，并且互相对应，各个SELECT语句之间使用UNION或者UNION ALL关键字分隔。UNION操作符返回两个查询结果集的并集，并且去除重复结果，UNION ALL返回两个查询结果集的并集，但不会去除重复结果，执行UNION ALL语句所需要的资源比UNION语句少，如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。其语法格式如下:

SELECT column... FROM table1
UNION|UNION ALL
SELECT column... FROM table2

4.SQL JOIN图解:

五、子查询:select的嵌套

1.子查询：子查询就是指在一个完整的查询语句中嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。该特性与MySQL4.1引入，子查询大大增强了SELECT的查询能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（标量或者集合）进行比较。子查询在主查询之前一次执行完成，且子查询的结果被主查询使用。子查询本质上就是一个完整的 SELECT 语句，它可以使一个 SELECT、INSERT INTO 语句、DELETE 语句或 UPDATE 语句嵌套在另一子查询中。SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。子查询的输出可以包括一个单独的值（单行子查询）、几行值（多行子查询）、或者多列数据（多列子查询）。​​​​​​​子查询要包含在括号内，将子查询放在比较条件的右侧，单行操作符对应单行子查询，多行操作符对应多行子查询。单行子查询和多行子查询是按照查询的结果是返回一条记录还是多条记录进行分类的。其基本语法格式如下:

1.SELECT语句中的子查询:
SELECT select_list
FROM table
WHERE expr operator
        (
             SELECT select_list
             FROM table
              
        );
2.INSERT语句中的子查询:对于INSERT语句中的子查询来说，首先是使用子查询的SELECT语句找到需要插入的数据，之后将返回的数据插入到另一个表中。INSERT 语句中的子查询其实是将一个表中查询到的数据“复制”到另一个表中。在子查询中所选择的数据可以用任何字符、日期或数字函数修改。
INSERT INTO table_name
	SELECT colnum_name(s)
	FROM table_name
	[ WHERE VALUE OPERATOR ]
3.UPDATE语句中的子查询:对于UPDATE语句，首先通过SELECT语句查询需要更新的信息，再使用UPDATE语句对信息进行更新。当通过 UPDATE 语句使用子查询时，能够实现表中单个列或多个列的数据更新。在UPDATE语句的子查询中，子查询SELECT语句所用的表和UPDATE语句所要更改的表不能是同一张表.
UPDATE table_name`
SET column_name = new_value
WHERE column_name OPERATOR 
   (SELECT column_name
   FROM table_name
   [WHERE] )
4.DELETE语句中的子查询
对于DELETE语句，首先通过SELECT语句查询需要删除的数据，再使用DELETE语句对数据进行删除。当通过DELETE语句使用子查询时，可以完成复杂的数据删除控制。在DELETE语句的子查询中，子查询SELECT语句所用的表和DELETE语句所要更改的表不能是同一张表。
DELETE FROM `table_name`
WHERE `column_name` OPERATOR 
   (SELECT `column_name`
   FROM `table_name`  
   [WHERE] )

2.相关子查询和不相关子查询:子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询，同样，如果子查询要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询,相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

3.子查询的特点:

(1)单列单行:返回的是一个具体列的内容，可以理解为一个单值数据

(2)单行多列:返回一行数据中多个列的内容

(3)多行单列:返回多行记录之中同一列的内容，相当于给出了一个操作范围

(4)多行多列:查询返回的结果是一张临时表

4.子查询关键字:

 (1)ALL:

select ... from ... where c>all(子查询语句)
等价于
select ... from ... where c>results1 and c>results2 and c>results3.....

特点:

ALL和子查询返回的所有值进行比较为true则返回true。

ALL可以与=、>、>=、<、<=、<>结合使用，分别表示等于、大于等于、小于、小于等于和不等于其中所有的数据。

ALL表示指定列中的值必须要大于子查询集合中的每一个值，即必须要大于子查询集合的最大值。

(2)ANY/SOME:

select ... from ... where c>any(子查询语句)
等价于
select ... from ... where c>results1 or c>results2 or c>results3 ...

特点:

ANY和子查询返回的任何值进行比较为true则返回true。

ANY可以与=、>、>=、<、<=、<>结合使用，分别表示等于、大于等于、小于、小于等于和不等于其中任何一个数据。

ALL表示指定列中的值必须要大于子查询集合中的任何一个值。

SOME的作用和ANY一样，可以理解为ANY的别名。

(3)IN:

格式:
select ... from ... where c in (查询语句)
等价于
select ... from ... where c = results1 or c=results2 or c=results3

IN关键字用于判断某个记录的值是否在指定的集合中,在IN关键字前面加上not可以将条件反过来。

(4)EXISTS:该子查询如果有数据结果，则该exists结果为true，外层查询执行，该子查询如果没有数据结果(没有任何数据返回)，则该exists结果为false，外层查询不执行。exists后面的子查询不返回任何实际数据，只返回真或假，当返回真时，where条件成立。exists关键字比IN关键字的运算效率高，在实际开发中推荐使用exists关键字。

格式:select ... from ... where exists(查询语句)

在查询SELECT结构中，除了GROUP BY和LIMIT中不能声明子查询外其余部分都可以声明子查询

5.相关更新:

6.相关删除:

7.表自关联:将一张表当成多张表来使用， MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己相关联，一张表当成多张表来使用。自关联时一定要给表起别名

select 字段列表 from 表1 a, 表1 b where 条件;
或者
select 字段列表 from 表1 a [left] join  表1 b on 条件

建议:

(1).如果子查询相对简单，则建议从外往里写，一旦子查询结构较复杂，建议从里往外写。

(2).如果是相关子查询，建议从外往里写。