MySQL的多表操作

在实际开发中，很多功能都会涉及两张以上的表的操作，及多表操作。

1. • 外键约数

外键是指在一张表中定义的、能确定另一张表记录的字段。该字段中引用了另一张表中的一列或多列，被引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的链接。

下面在数据库中创建一个班级表(class)和一个学生表(student)，具体语句如下：

CREATE TABLE class(
id int(5) NOT NULL PRIMARY KEY,
name varchar(40)
);
CREATE TABLE student(
id int(5) NOT NULL PRIMARY KEY,
name varchar(40),
cid int(5) NOT NULL
);

上述两个SQL建表语句中，如果学生表(student)中的cid字段的值是学生所在的班级id，并且它引入了班级表(class)中的主键id，那么cid就可以作为表student的外键。在这两个表中，被引用的class表是主表，引用外键的student表是从表，它们是主从关系。

要想真正连接两张表的数据，就需要为表添加外键约束。为表添加外键约束的语法格式如下：

ALTER TABLE 表名 ADD CONSTRAINT FK_ID FOREIGN KEY(外键字段名) REFERENCES 外表表名(主键字段名);

为student表添加外键约束的语法如下：

ALTER TABLE student ADD CONSTRAINT FK_ID FOREIGN KEY(cid) REFERENCES class(id);

上述SQL语句执行成功后，两张表就通过外键连接起来了。

成功添加外键后，student和class表之间就已经形成了多对一的关系。因为外键列只能插入参照列存在的值，所以如果要为两个表添加数据，就要先为主表class添加数据，再为从表student添加数据，具体如下：

INSERT INTO class(id,name) VALUES(1,'一班'),(2,'二班');
INSERT INTO student(id,name,cid) VALUES(1,'张三',1),(2,'李四',1),(3,'王五',2),(4,'赵六',2);

执行结果如下：

如果为从表外键列添加参照列不存在的值，会发生错误：

2.内连接

内连接(INNER JOIN)又称简单连接或自然连接，是一种常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较，并列出于连接条件比配的数据行，组合成新的记录，也就是说在内连接查询中，只有满足条件的记录才能出现在查询结果中。

内连接查询的语法格式如下：

SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段;

“INNER JOIN”用于连接两个表，“ON”用于指定连接条件，其中“INNER”可以省略。

下面创建表lesson和teacher作为示例：

CREATE TABLE class(
lid int(5) NOT NULL PRIMARY KEY,
lname varchar(40)
);
CREATE TABLE student(
id int(5) NOT NULL PRIMARY KEY,
name varchar(40),
age int(3),
lid int(5) NOT NULL
);

INSERT INTO lesson(lid,lname) VALUES(1,'语文'),(2,'数学'),(3,'英语'),(6,'生物');
INSERT INTO teacher(id,name,age,lid) VALUES(1,'李菲',31,1),(2,'高珊珊',44,1),(3,'孟莉苹',50,2),(4,'赵一平',25,5);

执行成功后，使用内连接查询lesson表和teacher表数据的SQL语句如下：

SELECT lesson.lname, teacher.name FROM lesson JOIN teacher ON lesson.lid=teacher.lid;

执行成功后，结果如下：

只有lesson.lid与teacher.lid相等的信息才会被显示。

注意：在MySQL中，还可以使用WHERE条件语句实现同样的功能，具体如下：

SELECT lesson.lname,teacher.name FROM lesson,teacher WHERE lesson.lid=teacher.lid;

执行结果如下：

从结果看，使用WHERE子句的查询结果与使用INNER JOIN的查询结果是一致的。

注意：这两个语句的查询结果虽然相同，但是INNER JOIN是内连接语句，WHERE是条件判断语句，在WHERE语句后可以直接添加其他条件，而INNER JOIN 语句则不可以。

3.外连接

内连接查询返回的结果只包含复合查询条件和连接条件的数据，然而有时还需要包含没有关联的数据，即返回的查询结果中不仅包含符合条件的数据，还要包括左表(左连接和左外连接)、右表(右连接和右外连接)中的所有数据，此时就需要使用外连接查询。

其语法格式如下：

SELECT 所查字段 FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段;

其中关键字左端的表称为左表，关键字右边的表称为右表。

• LEFT JOIN(左连接)：返回包括左表中的所有记录和右表中符合连接条件的记录

• RIGHT JOIN(右连接)：返回包括右表中的所有记录和左表中符号连接条件的记录

3.1LEFT JOIN(左连接)

左连接的结果包括LEFT JOIN子句中指定的左表的所有记录和所有满足连接条件的记录。如果左表中的某条记录在右表中不存在，则在右表显示为空。

在lesson表和teacher表之间使用左连接查询，SQL语句如下：

SELECT lesson.lid,lesson.lname,teacher.name FROM lesson LEFT JOIN teacher ON lesson.lid=teacher.lid;

执行结果如下：

3.2RIGHT JOIN(右连接)

右连接会返回右表中的所有记录和所有满足连接条件的左表记录。如果右表中的某条记录在左表中没有匹配，则左表返回控制。

在lesson表和teacher表之间使用右连接查询，SQL语句如下：

SELECT lesson.lid,lesson.lname,teacher.name FROM lesson RIGHT JOIN teacher ON lesson.lid=teacher.lid;

执行结果如下：

4.子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询，它可以嵌套在一个SELECT、SELECT...INTO语句或INSERT...INTO等语句中。在执行查询语句时，首先会执行子查询中的语句，然后将返回的结果作为外层查询的过滤条件进行查询。

4.1带IN关键字的子查询

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

例如，要查询年龄为50岁的教师的课程，其SQL语句如下：

SELECT * FROM lesson WHERE lid IN(SELECT lid FROM teacher WHERE age=50);

执行结果如下：

在查询过程中，会首先执行内层子查询，得到年龄为50岁的教师的课程lid，然后根据lid与外层查询的比较条件进行查询，从而最终得到符合条件的数据。

注意：SELECT语句中还可以使用NOT IN关键字进行查询，其作用与IN相反。

4.2带ANY关键字的子查询

ANY关键字表示满足其中任意一个条件，它允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。

使用带ANY关键字查询满足条件课程的SQL语句如下：

SELECT * FROM lesson WHERE lid>ANY(SELECT lid from teacher);

执行结果如下：

在执行过程中，首先将子句的teacher表中的所有lid查询出来，然后将lesson表中lid的值与之进行比较，只要大于teacher.lid中的任意一个值，就是符合条件的查询结果。

4.3带ALL关键字的子查询

ALL关键字与ANY关键字的作用有些类似，只不过带ALL关键字的子查询返回的结果需要同时满足所有内存查询条件。

使用带ALL关键字的子查询，查询满足条件课程的SQL语句如下：

SELECT * FROM lesson WHERE lid>ALL(SELECT lid FROM teacher);

执行结果如下：

执行过程中，首先子查询会将teacher表中所有的lid查询出来，然后将lesson表中lid的值与之进行比较，只有大于teacher.lid的所有值，才是符合条件的执行结果。

4.4带比较运算符的子查询

前面已经只用过>比较运算符，子查询可以使用<、>=、=、!=等比较运算符。

使用带比较运算符的子查询，查询孟莉苹是哪个课程的老师的SQL语句如下：

SELECT * FROM lesson WHERE lid=(SELECT lid FROM teacher WHERE name='孟莉苹');

执行结果如下：