新闻公告

MySQL中多表操作有哪些？

发布时间：2020-08-06 12:28:08

多表查询

联合查询

联合查询是多表查询的一种方式，在保证多个SELETE语句的查询字段数相同的情况下，合并多个查询的结果

SELECT···
UNION [ALL|DISTINCT] SELECT···
[UNION [ALL|DISTINCT]SELECT···];123

在上述语法中，UNION是实现联合查询的关键字，ALL和DISTINCT是联合查询的选项，其中ALL表示保留所有的查询结果；DISTINCT是默认值，可以省略，表示去除完全重复的记录。

除此之外，若要对联合查询的记录进行排序等操作，需要使用圆括号“()”包裹每- -个SELECT语句，在SELECT语句内或在联合查询的最后添加ORDER BY语句。并且若要排序生效，必须在ORDER BY后添加LIMIT限定联合查询排序的数量，通常推荐使用大于表记录数的任意值。

连接查询

交叉连接

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积。

SELECT 查询字段 FROM 表1 CROSS JOIN 表21

CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据集合。

内连接

内连接是一种常见的连接查询，他根据匹配的条件返回第一个表与第二个表所有匹配成功的记录。

SELECT 查询字段 FROM 表1
[INNER] JOIN 表2 ON 匹配条件；12

ON用于指定内连接的查询条件，在不设置ON时，与交叉连接等价。此时可以使用WHERE完成条件的限定，效果与ON一样。但由于WHERE是限定已全部查询出来的记录，那么在数据量很大的情况下，此操作会浪费很多性能，所以此处推荐使用ON实现内连接的条件匹配。

在标准的SQL中，交叉连接(CROSS JOIN)与内连接(INNER JOIN)表示的含义不同，前者一-般只连接表的笛卡尔积，而后者则是获取符合ON筛选条件的连接数据。
但是在MySQL中，CROSS JOIN与INNER JOIN(或JOIN)语法的功能相同，都可以使用ON设置连接的筛选条件，可以互换使用，但是此处不推荐读者将交叉连接与内连接混用

左外连接

左外连接是外连接查询中的一种，也可以将其称为左连接。它用于返回关键字(LEFT JOIN)左表中所有的记录，以及右表中符合连接条件的记录。当左表的某行记录在右表中没有匹配的记录时，右表相关的记录将会设为NULL。

SELECT 查询字段
FROM 表1 LEFT [OUTER] JOIN 表2 ON 匹配条件;12

关键字LEFT [OUTER] JOIN左边的表（表1）被称为左表，也可称为主表；关键字左边的表(表2)被称为右表，也可称为从表。其中，OUTER在查询时可以省略。

右外连接

右外连接也是外连接查询中的一种，可以将其称为右连接。它用于返回连接关键字(RIGHT JOIN)右表(主表)中所有的记录，以及左表(从表)中符合连接条件的记录。
当右表的某行记录在左表中没有匹配的记录时，左表中相关的记录将设为空值。

SELECT 查询字段 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 匹配条件;1

外连接是最常用的一种查询数据的方式，分为左外连接(LEFTJOIN)和右外连接(RIGHT JOIN )。
外连接与内连接的区别是，内连接只能获取符合连接条件的记录，而外连接不仅可以获取符合连接条件的记录，还可以保留主表与从表不能匹配的记录。
右连接查询正好与左连接相反。
因此，在应用外连接时仅调整关键字(LEFT或RIGHT JOIN) 和主从表的位置，即可实现左连接和右连接的互换使用。

using关键字

若连接查询数据表连接的字段同名，则连接时的匹配条件使用USING代替ON。

SELECT 查询字段 FROM 表1 [CROSS|INNER|LEFT|RIGHT] JOIN 表2USING (同名的连接字段列表);12

子查询

什么是子查询

子查询可以理解为，在一个SQL语句A (SELECT、INSERT、UPDATE等)中嵌入一个查询语句B，作为执行的条性或查询的数据源(代替FROM后的数据表) ,那么B就是子查询语句，它是一-条完整的SELECT语句，能够独立的执行。

在含有子查询的语句中，子查询必须书写在圆括号()内。
➢SQL语句首先会执行子查询中的语句。
➢然后再将返回的结果作为外层SQL语句的过滤条件。
➢当遇到同一个SQL语句中含有多层子查询时，它们执行的顺序是从最里层的子查询开始执行。

子查询分类

子查询的划分方式有多种，最常见的是以功能和位置进行划分。

按子查询的功能可以分为标量子查询、列子查询、行子查询和表子查询。
按子查询出现的位置可以分为WHERE子查询和FROM子查询。
➢标量子查询、列子查询和行子查询都属于WHERE子查询。
➢表子查询属于FROM子查询。.

标量子查询

标量子查询:子查询返回的结果是-一个数据，即一-行一-列。

WHERE条件判断{ # | <>}(SELECT 字段名 FROM 数据源 [WHERE] [GROUP BY] [HAVING][ORDER BY] [LIMIT]);123

子查询利用比较运算符=或<>，判断子查询语句返回的数据是否与指定的条件相等或不等，然后根据比较结果完成相关需求的操作。
数据源表示一-个符合二维表结构的数据，如数据表。

列子查询

列子查询:子查询返回的结果是一个字段符合条件的所有数据，即一列多行。

WHERE 条件判断 {IN|NOT IN}(SELECT 字段名 FROM 数据源 [WHERE] [GROUP BY] [HAVING][ORDER BY] [LIMIT]);123

子查询利用比较运算函数IN()或NOT IN()。
判断指定的条件是否在子查询语句返回的结果集中。
然后根据比较结果完成相关需求的操作。

行子查询

当子查询的结果是一条包含多个字段的记录(一行多列)时，称为行子查询。

WHERE (指定字段名1,指定字段名2...)=(SELCT 字段名1,字段名2,... FROM 数据源[WHERE] [GROUP BY][HAVING] [ORDER BY] [LIMIT]);123

行子查询返回的一条记录与指定的条件比较，比较的运算符通常使用=。
子查询的结果必须全部与指定的字段相等才满足WHERE指定的条件。

行在相等比较(=或<=>)时，各条件之间是与的逻辑关系。
在不等比较(<>或!)时，各条件之间是或的逻辑关系。
在进行其他方式比较时，各条件之间的逻辑关系包含两种情况。

因此，读者在选取行子查询的比较运算符时，要根据实际需求慎重选择。

表子查询

表子查询:子查询的返回结果用于FROM数据源，它是一个符合二维表结构的数据，可以是一行一列、一列多行、一行多列或多行多列。

SELECT 字段列表 FROM (SELECT语句) [AS] 别名[WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT];12

FROM后的数据源都是表名。
当数据源是子查询时必须为其设置别名，同时也是为了将查询结果作为一个表使用时，可以进行条件判断、分组、排序以及限量等操作。

外键约束

添加外键约束

外键指的是-一个表中引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一性约束，从而保证数据的一-致性和完整性。
➢被引用的表称为主表。
➢引用外键的表称为丛表。

创建数据表(CREATE TABLE)，在相应的位置添加外键约束。
修改数据结构(ALTER TABLE) ，在相应的位置添加外键约束。

[CONSTRAINT symbol] FOREIGN KEY [index_ name] (index_ _col_ _name, ..REFERENCES tbl_ name (index_ col_ name, ..[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}][ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]1234

FOREIGN KEY.REFERENCES关键字:向数据表中添加外键约束。
语法中第一行的参数“index_ col_ name, …”表示从表中外键名称列表。.
tbl_ name表示主表，主表后的参数列表“index_ col_ name,…"表示主键约束或唯一-性约束字段。
关键字CONSTRAINT用于定义外键约束的名称symbol,如果省略，MYSQL将会自动生成-一个名字。
index_ name也是可选参数，表示外键索引名称，如果省略，MySQL也会在建立外键时自动创建一个外键索引，加快查询速度。
ON DELETE与ON UPDATE用于设置主表中的数据被删除或修改时，从表对应数据的处理办法。

关联表操作

实体之间具有一对一、一对多和多对多的联系。
➢具有关联的表中的数据，可以通过连接查询的方式获取，并且在没有添加外键约束时，关联表中的数据插入、更新和删除操作互不影响。
➢对于添加了外键约束的关联表而言，数据的插入、更新和删除操作就会受到一定的约束。

一个具有外键约束的丛表在插入数据时，外键字段的值会受主表数据的约束，保证从表插入的数据必须符合约束规范的要求。例如，从表外键字段不能插入主表中不存在的数据。

删除外键约束

ALTER TABLE表名DROP FOREIGN KEY外键名;12

若要在删除外键约束后，同时删除系统为外键创建的普通索引，则需要通过手动删除索引的方式分完成。

关于我们