多表数据记录查询--重点

第七章：

内连接查询 

在关系型数据库管理系统中，通常一张表只会存储一个实体的相关信息，如果用户需要查询多张表中不同实体的数据，可以使用关键字JOIN对表执行连接查询操作，但前提条件是，这些表中必须存在具有相同意义的字段。连接查询主要包括内连接查询和外连接查询，另外还可以在连接查询中添加过滤条件，筛选符合条件的数据，这就是复合条件连接查询。 

创建实验表 

1、 staff表结构 

字  段	数据类型	约  束	注  释
staff_id	INT(10)	无符号、主键、自增、非空	员工ID
section_id	INT(10)	无符号、非空	部门ID
positions_id	INT(10)	非空	职位ID
name	VARCHAR(10)	非空	姓名
sex	ENUM('男','女')	非空	性别
phone_number	CHAR(11)	非空	手机号
money	DECIMAL(10,2)	无符号、非空，默认值0	薪资
entry_date	DATETIME	非空	入职时间

2、 section表结构 

字  段	数据类型	约  束	注  释
section_id	INT(10)	无符号、主键、自增、非空	部门ID
section_title	VARCHAR(20)	非空	部门名称

本例首先创建数据库staff，然后参照上述表结构创建数据表staff和section，并在其中插入数据。 

登录MySQL后执行以下语句，创建数据库staff。 

CREATE DATABASE staff; 

选择数据库staff，并执行以下语句创建数据表staff。 

USE staff; 

CREATE TABLE staff 

  ( 

  staff_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 

  section_id INT(10) UNSIGNED NOT NULL, 

  positions_id INT(10) NOT NULL, 

  name VARCHAR(10) NOT NULL, 

  sex ENUM('男','女') NOT NULL, 

  phone_number CHAR(11) NOT NULL, 

  money decimal(10,2) UNSIGNED NOT NULL DEFAULT '0.00', 

  entry_date DATETIME NOT NULL, 

  PRIMARY KEY(staff_id) 

); 

向staff表中插入数据。 

INSERT INTO staff(staff_id,section_id,positions_id,name,sex,phone_number,money,entry_date) 

VALUES(1,'1','1','刘长生','男','13753697300',20000,'2018-04-02 14:35:52'), 

(2,'1','2','赵霞','女','13753697301',10000,'2018-04-03 14:40:52'), 

(3,'2','3','季庆奇','女','13753697302',15000,'2018-04-03 14:43:52'), 

(4,'3','3','李星宇','男','13753697303',15000,'2018-04-03 14:45:52'), 

(5,'4','3','张向阳','男','13753697304',15000,'2018-04-03 14:47:24'), 

(6,'4','8','张旭','男','13753697306',10000,'2018-04-03 14:50:52'); 

创建数据表section。 

CREATE TABLE section( 

  section_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 

  section_title VARCHAR(20) NOT NULL, 

  PRIMARY KEY (section_id) 

); 

向数据表section中插入数据。 

INSERT INTO section(section_id,section_title) 

VALUES(1,'总经办'), 

(2,'财务部'), 

(3,'销售部'), 

(4,'研发部'), 

(5,'运营部'), 

(6,'人力资源部'), 

(7,'售后服务部'); 

内连接查询 

内连接查询（INNER JOIN）是使用比较运算符对多个表间的某些列数据进行比较，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。表之间的连接条件由表中具有相同意义的字段组成。 

1、普通内连接查询 

SELECT {*|col_list} FROM table_name1 

INNER JOIN table_name2 ON condition; 

在staff表和section表之间使用内连接查询，从staff表中查询staff_id（员工ID）、name（姓名）、sex（性别）和phone_number（手机号），从section表中查询section_title（部门名称）。 

根据表结构可知，两个表都有相同数据类型的字段section_id，可以通过该字段建立联系。为此，首先选择数据库staff，然后执行内连接查询语句，结果如下： 

SELECT staff_id,name,sex,section_title,phone_number FROM staff INNER JOIN section ON staff.section_id=section.section_id; 

另外，使用WHERE子句也可以给出连接条件，以下语句将返回与前面完全相同的结果。 

SELECT staff_id,name,sex,section_title,phone_number FROM staff,section 

WHERE staff.section_id=section.section_id; 

使用WHERE子句定义连接条件简单明了，但在某些时候会影响查询性能，而使用INNER JOIN语法能够确保不会忘记连接条件。 

2、 自连接查询 

SELECT table_alias1.*,table_alias2.* FROM table_name AS table_alias1  

INNER JOIN table_name AS table_alias2 ON condition; 

使用内连接查询语句，从staff表中查询薪资低于15000的员工的staff_id（员工ID）、name（姓名）和money（薪资）。 

SELECT s1.staff_id,s1.name,s1.money FROM staff AS s1 INNER JOIN staff AS s2 ON s1.staff_id=s2.staff_id AND s2.money<15000; 

此处查询的两个表是同一个表staff，为防止产生二义性，使用AS关键字为表起了别名，staff表第1次以s1为别名出现，第2次以s2为别名出现。INNER JOIN连接两个表，并按照第2个表的money值对数据进行过滤。 

外连接查询 

外连接查询（OUTER JOIN）是以一张表为基表，根据连接条件，与另外一张表的每一行进行匹配，如果没有匹配上，则在相关联的结果行中，另一张表的所有选择列均返回空值。 

外连接查询通常分为两种：左连接查询（LEFT JOIN）和右连接查询（RIGHT JOIN）。其基本语法形式如下： 

SELECT {*|col_list} FROM table_name1  

{LEFT|RIGHT} [OUTER] JOIN table_name2 ON condition; 

选择galaxy数据库，由于在上述实验中，已经将orders表删除，因此重新创建orders表 

USE galaxy; 

CREATE TABLE orders( 

o_id INT(11) PRIMARY KEY, 

add_time DATETIME, 

goods_id INT(11), 

CONSTRAINT goo_ord FOREIGN KEY(goods_id) REFERENCES goods(id) 

); 

关闭orders表的外键约束。 

SET FOREIGN_KEY_CHECKS=0; 

对于先创建表关系，之后才插入数据的表，在其中插入数据时需要先关闭外键约束，否则会插入不成功。由于前面在创建orders表时为其设置了外键约束，此处先将其关闭，待插入数据后再开启。开启表外键约束的语句为SET FOREIGN_KEY_CHECKS=1;。 

向orders表中插入数据，结果如下所示。 

insert into orders(o_id,add_time,goods_id) 

values(1,'2018-04-02 14:35:52',6), 

(2,'2018-04-03 14:40:52',1), 

(3,'2018-04-03 14:43:52',5), 

(4,'2018-04-03 14:45:52',1), 

(5,'2018-04-03 14:47:24',15), 

(6,'2018-04-03 14:50:52',4), 

(7,'2018-04-03 14:53:35',20) 

; 

1、 左外连接查询 

在外连接查询语句中，LEFT JOIN关键字之前的表称为左表，左连接查询会以左表为基表，与另外一张表的每一行进行匹配，如果符合连接条件，则返回两张表相对应的行；如果不符合，则只返回左表中的行，并且其对应的行为一个空值。 

执行左连接查询语句，查询tb_goods表中哪些商品有订单，哪些商品没有订单。 

由于tb_goods表中的id字段对应orders表中的goods_id字段，此处将这两个字段进行比较，如果tb_goods表中的id字段在orders表中有对应的goods_id值，则返回其对应的o_id值；否则就表示该商品没有对应订单，返回空值。执行结果如下： 

SELECT tb_goods.id,tb_goods.name,orders.o_id FROM tb_goods LEFT JOIN orders ON tb_goods.id=orders.goods_id; 

2、 右外连接查询 

在外连接查询语句中，RIGHT JOIN关键字之后的表称为右表，右连接查询会以右表为基表，与另外一张表的每一行进行匹配，如果符合连接条件，则返回两张表相对应的行；如果不符合，则只返回右表中的行，并且其对应的行为一个空值。 

执行右连接查询语句，查询订单中所对应的商品哪些仍然存在，哪些已经删除。 

与左外连接类似，依然将商品编号进行比较，所不同的是，此处是将orders表中的goods_id与tb_goods表中的id字段逐行进行比较，如果在tb_goods表中有对应的记录，则返回相应的name值，如果没有则返回空值。执行结果如下： 

SELECT orders.o_id, tb_goods.name FROM tb_goods RIGHT JOIN orders ON orders.goods_id=tb_goods.id; 

复合连接查询 

复合条件连接查询是通过在连接查询中添加过滤条件，以达到限制查询结果和筛选数据的目的，下面通过实例进行介绍。 

在staff表和section表中，执行内连接查询语句，查询section表中部门名称为“总经办”的员工ID、姓名、性别和电话号码等信息。 

USE staff; 

在staff表和section表中查询部门名称为“总经办”的员工ID、姓名、性别和电话号码，执行结果如下： 

SELECT staff_id,name,sex,section_title,phone_number FROM staff INNER JOIN section ON staff.section_id=section.section_id WHERE section.section_title='总经办'; 

执行左连接查询语句，查询tb_goods表中哪些商品有订单，哪些商品没有订单，并使用商品ID号对查询结果进行排序。 

USE galaxy; 

SELECT tb_goods.id,tb_goods.name,orders.o_id FROM tb_goods LEFT JOIN orders ON tb_goods.id=orders.goods_id ORDER BY tb_goods.id;