MySQL学习总结（交叉连接查询、内连接查询、外连接查询、子查询、外键约束、操作关联表、删除外键约束）

目录

一、交叉查询

二、内连接查询

示例1：

示例2：

三、外连接查询

1、左连接查询

2、右连接查询

3、复合条件连接查询

 四、子查询

1、IN关键字结合子查询

示例1：

示例2：

2、EXISTS 关键字结合子查询

3、ANY关键字结合子查询

 4、ALL关键结合子查询

5、比较运算符结合子查询 

五、外键约束

1、添加外键约束

六、操作关联表

1、关联表添加数据

（1）往主表dept中插入数据

（2）往从表emp中插入数据

2、关联表删除数据

（1）删除从表emp中属于人力资源部的员工信息

 （2）删除主表dept中的数据

 七、删除外键约束

---

         学习MySQL交叉连接查询、内连接查询、外连接查询、子查询、外键约束、操作关联表

一、交叉查询

          交叉查询（ CROSS JOIN ）查询返回的结果是被连接的两张数据表中所有数据行的笛卡儿积。

语法格式：

SELECT <字段名> FROM <数据表名1> CROSS JOIN <数据表名2> ;

或者

SELECT <字段名> FROM <数据表名1> , <数据表名2> ;

        在上述语法格式中，两种语法格式的返回结果相同，其中<字段名>指的是需要查询的字段名称；<数据表名1>和<数据表名2>指的是需要交叉连接的数据表的名称；CROSS JOIN用于连接两个要查询的数据表，通过CROSS JOIN语句可以查询两个表中所有的数据组合。

示例：对数据库ems中员工表emp和部门表dept进行交叉连接查询

 

          （中间省略了一些）交叉连接查询的结果就是两个连接表中所有数据的组合，查询出的记录数为44。由于交叉连接查询的结果中存在很多不合理的数据，因此在实际应用中应避免交叉连接查询，而是使用具体条件对数据进行有目的的查询。

二、内连接查询

       内连接（INNER JOIN）查询又称简单连接查询或自然连接查询，是常见的连接查询。内连接查询根据连接条件可以对交叉连接查询的部分结果进行筛选，仅筛选出两张表中相互匹配的记录。

语法格式：

SELECT 查询字段 FROM 数据表1 [ INNER ] JOIN 数据表2 ON 匹配条件 ;

       在上述语法格式中，INNER JOIN用于连接两张数据表，其中INNER可以省略；ON用于指定查询的匹配条件，即同时匹配两张数据表的条件。由于内连接查询是对两张数据表进行操作，因此需要在匹配条件中指定所操作的字段来源于哪一张数据表，如果为数据表设置了别名，也可以通过别名指定数据表。

示例1：

示例：查询已经分配了部门(部门号不为NULL)的员工的信息，员工信息只需要显示员工姓名和对应部门的名称

          在上述查询语句中，通过匹配员工表emp和部门表dept中的字段deptno，使用内连接查询分配了部门的员工信息；由执行结果可知，查询出了员工姓名和对应部门的名称。

         如果在一个连接查询中，涉及的两张数据表是同一张数据表，则这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的数据表在物理上为同一张数据表，但逻辑上分为两张数据表。

示例2：

示例：查询员工王五所在部门的所有员工信息。查询时可以使用自连接查询实现

          在上述查询语句中，名称为e1和名称为e2的数据表在物理上是同一张数据表emp，e1和e2通过字段depno进行关联，并且通过WHERE指定筛选的条件；执行结果中，返回了王五所在部门的所有员工信息。由执行结果可知，王五所在部门有4个员工，分别是张三、李四、赵六和王五。

三、外连接查询

        内连接的查询结果是符合连接条件的记录，然而有时在查询时，除了要查询符复合条件的数据外，还需要查询出其中一张数据表中数据表符号条件之外的其他数据，此时就需要使用外连接查询。

语法格式：

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

        外连接查询分为左连接（LEFT JOIN）查询和右连接（RIGHT JOIN）查询，一般上述语法格式中的数据表1被称为左表，数据表2被称为右表。使用左连接查询和右连接查询的区别如下。

• LEFT JOIN：返回左表中的所有记录和右表中符合连接条件的记录。
• RIGHT JOIN：返回右表中的所有记录和左表中符合连接条件的记录。

1、左连接查询

       左连接查询的结果包括 LEFT JOIN 子句中左表的所有记录以及右表中满足连接条件的记录。如果左表的某条记录在右表中不存在，则右表中对应字段的值显示为NULL。

示例：查询所有部门名称及部门对应员工的姓名。因为需要查询出所有部门的名称，所以查询时可以使用左连接查询，将部门表作为查询中的左表

         在上述查询语句中，使用左连接将部门表和员工表通过deptno字段进行连接；由执行结果可知，上述查询语句返回了12条记录，其中返回了左表 dept中dname字段所有的数据，运营部没有员工，对应的员工姓名字段显示为NULL。

2、右连接查询

        右连接查询的结果包括RIGHT JOIN 子句中右表的所有记录以及左表中满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表中对应字段的值显示为NULL。

示例：查询所有员工姓名及对应部门的名称，没有分配部门的员工也需要查询出来。

        因为需要查询出所有员工的名称，所以查询时可以使用右连接查询，将员工表作为查询中的右表

 

3、复合条件连接查询

       复合条件连接查询是指在连接查询的过程中通过添加过滤条件限制执行结果，使执行结果更精确。
示例：查询所有员工信息，员工信息包含员工所在部门的名称，并且按员工的工资降序排序

在查询时，可以根据deptno字段使用左连接将部门表和员工表进行关联查询，并且使用ORDER  BY 根据sal字段的值对查询结果进行排序。

 四、子查询

1、IN关键字结合子查询

        IN关键字结合子查询使用时，需要内层子查询语句返回的结果是一个数据列，这个数据列中的值供外层语句进行比较操作。

示例1：

示例：查询工资大于2900的员工所属部门

查询时可以先通过子查询返回工资大于2900的员工所在部门的编号，接着使用IN关键字根据部门编号查询部门信息

        从执行结果可知，只有总裁办和研究院存在工资大于2900的员工。
        外层SELECT语句使用NOT IN关键字结合子查询使用时，其作用正好和使用IN相反。

示例2：

示例：查询工资小于2900的员工所在的部门信息

        从上述执行结果可以得出，使用NOT IN的查询结果是数据表中使用IN查询到的结果之外的其他数据，只有运营部和销售部不存在工资大于2900的员工。

2、EXISTS 关键字结合子查询

       EXISTS关键字用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回TRUE，否则返回FALSE。使用EXISTS关键字结合子查询进行查询时，会先执行外层查询语句，再根据EXISTS关键字后面子查询的查询结果，判断是否保留外层语句查询出的记录。EXISTS的判断结果为TRUE时，保留对应的记录，否则去除记录。

示例：查询工资大于2900的员工所在的部门信息

        首先查询出部门的所有信息，然后通过子查询筛选出工资大于2900的员工信息，接着使用EXISTS关键字将符合子查询结果的记录返回

        从上述执行结果可以得出，只有总裁办和研究院存在工资大于2900的员工。

       使用EXISTS关键字结合子查询和使用IN关键字结合子查询的结果一致，但在表数据量不同时，这两种方式的性能也不同。当外表数据量比较大而内表数据量比较小时，适合使用IN关键字结合子查询进行查询；当外表数据量比较小而内表数据量比较大时，适合使用EXISTS关键字结合子查询进行查询。

3、ANY关键字结合子查询

         ANY关键字表示 “ 任意一个 ” 的意思，必须和比较操作符一起使用，例如ANY和 > 结合起来使用表示大于任意一个。ANY关键字结合子查询使用时，表示子查询的查询结果集中的任一查询结果，例如“ 值1 > ANY(子查询) ”比较值1是否大于子查询返回的结果集中的任意一个结果。

示例：查询部门编号为10的员工信息，要求查询到的员工信息中工资都高于部门编号为20的部门中的最低工资

查询时可以先使用子查询语句查询出部门编号为20的部门中所有员工工资，接着查询部门编号为10的部门中所有员工信息，最后使用ANY连接两者的工资进行比较。

 4、ALL关键结合子查询

       ALL关键字表示“ 所有 ”的意思，该关键字结合子查询使用时，表示子查询结果集中的所有结果，例如“ 值1>ALL(子查询) ”比较值1是否大于子查询返回的结果集中的所有结果。

示例：查询部门编号为10的员工信息，要求查询到的员工信息中工资都高于部门编号为20的部门中的最高工资

         查询时可以使用子查询将部门编号为20的所有员工工资查询出来，然后将部门编号为10的所有员工工资与子查询的结果进行比较，只要大于子查询中的所有值，就是符合查询条件的记录

5、比较运算符结合子查询 

       前面讲解的ANY关键字和ALL关键字的子查询中使用了比较运算符＞。除了>运算符，子查询中还可以使用其他的比较运算符，如< 、= 、!= 等。

示例：查询与王五职位相同的员工信息

        查询时可以先使用子查询获取王五的职位，接着根据子查询的结果筛选出职位和王五相同的员工信息 

         从执行结果可知，王五的职位是分析员，和王五职位相同的员工只要赵六。

五、外键约束

1、添加外键约束

      外键是数据表中的一个特殊字段，它引用另一张数据表中的一列或多列，被引用的列应该具有主键约束或唯一约束。

        例如在员工表emp的deptno字段上添加外键约束，引用部门表dept的主键字段deptno，如此就通过外键加强了员工表和部门表数据之间的关联，如图所示。

        对于两个具有关联关系的数据表来说，相关联字段中主键所在的数据表就是主表，外键所在的数据表就是从表。从上图可以得出dept表是主表，emp表是从表，emp表通过deptno连接 dept 表中的信息，从而建立了两张数据表之间的关联。

为从表添加外键约束的语法格式：

ALTER TABLE 从表名 ADD CONSTRAINT [ 外键名称 ] FOREIGN KEY ( 外键字段名)
REFERENCES 主表名 ( 主键字段名 ) ;

       在上述语法格式中，ADD CONSTRAINT 表示添加约束；外键名称是可选参数，用来指定添加的外键约束的名称；FOREIGN KEY表示外键约束；使用REFERENCES指定创建的外键引用哪个表的主键。

示例：上述语法格式，为员工表emp添加外键约束

         添加外键成功之后，可以使用 SHOW CREATE TABLE 语句查看员工表emp的创建语句

六、操作关联表

1、关联表添加数据

（1）往主表dept中插入数据

         因为从表emp的外键列只能插入所引用的列（部门表的deptno字段）中存在的值，所以如果要为两个数据表添加数据，就需要先为主表dept添加数据，如下。 

（2）往从表emp中插入数据

       在主表中添加的数据中，主键deptno的值包含10、20、30、40和50，由于员工表emp的外键引用部门表的主键deptno，因此在往员工表emp中添加数据时，其deptno字段的值只能是10、20、30、40和50，不能使用其他的值，如下。 

 

         数据插入成功后，如果要查询人力资源部有哪些员工，可以使用连接查询完成，也
可以使用子查询完成。

示例：使用内连接查询完成查询需求

        从上述执行结果可以得出，人力资源部只有1名员工。需要注意的是，外键约束是为了保证数据的完整性和统一性，主表和从表中进行数据的新增、编辑、删除时需要遵循外键约束的要求，但是对数据的查询没有约束性。

2、关联表删除数据

       由于员工表emp和部门表dept之间使用外键进行了关联，因此主表dept中已经被引用的值不能直接删除。如果要删除人力资源部，需要先将人力资源部中的员工删除，或者转移到其他部门，又或者不分配部门(部门编号设置为NULL)。在此选择先删除人力资源部中的员工，再删除部门表中的人力资源部。

（1）删除从表emp中属于人力资源部的员工信息

         上述语句的执行结果可以得出，删除语句执行成功。

验证是否删除成功

 （2）删除主表dept中的数据

 验证是否删除成功

 七、删除外键约束

删除外键约束语法格式：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名 ;

示例：将员工表emp中的外键约束删除

        可以使用 SHOW CREATE TABLE 语句查看员工表emp的创建语句