mysql多表查询-笔记七

一、外键

1、什么是外键

外键是指引用另一个表中的一列或多列，别引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的连接。未来是初学者更好地理解外键的定义，接下来，通过两张表来讲解什么是外键。

首先需要创建两个表，一个班级表（grade）和一个学生表（student），具体语句如下：

create database chapter05;	//创建数据库
use chapter05;	//进入数据库

create table grade(	//创建数据表grade
id int not null primary key,
name varchar(255)
)
create table student(	//创建数据表studnet
sid int not null primary key,
sname varchar(255),
gid int not null
)

学生表（student）中gid是学生表在的班级id，是引入了班级表（grade）中主键id，那么gid就可以作为表student的外键。被引用的表，即表grade是主键；引用外键的表，即表student是从表，两表是主从关系。表student用gid可以连接表grade中的信息，从而建立了两个表数据之间的连接。

引入外键后，外键列只能插入参照列存在的值，参照列被参照的值不能被删除，这就保证了数据的参照完整性。

2、为表添加外键约束

语法：
alter table 表名 add constraint 外建名 foreign key(外键字段名) references 外表表名(主键字段名);

列如：为表student添加外键约束，具体语句如下：

alter table student add constraint FK_ID foreign key(gid) references grade(id);

在为表添加外键约束时，有些需要注意的地方，如下所示。

（1）建立外键的表必须是InnoDB型，不能是临时表。因为在MySQLzhong中只有InnoDB类型的表才支持外键。

（2）定义外键名时，不能加引号，如constraint "FK_ID"或constraint 'FK_ID’都是错误的。

3、删除外键约束

语法：
alter table 表名 drop foreign key 外建名;
列如：
alter table student drop foreign key FK_ID; 

二、操作关联表

1、关联关系

1.1多对一

1.2多对多

1.3一对一

1、添加数据

alter table student add constraint FK_ID foreign key(gid) REFERENCES grade(id);

此时表student和表grade之间是多对一的关系。因为外键只能插入参照列存在的值，所以如果腰围两个表添加数据，就需要为主表grade添加数据，具体语句如下：

insert into grade(id,name) values(1,"软件一班");
insert into grade(id,name) values(2,"软件二班");

然后再能为student表添加数据。

2、删除数据

在某些情况下还需要删除关联表中的数据，列如学校的软件一班取消，就需要在数据库中将该班以及该班的学生一起删除。由于grade表和student表之间具有关联关系。参照列被参照的值是不能被删除的，因此，在删除软件一班时，一定要先删除该班级的所有学生，然后在删除班级。

delete from student where sanme = "王红"；

三、连接查询

1、交叉连接

语法：
SELECT * FROM 表1 CROSS JOIN 表2;

在上述语法格式中，CROSS JOIN用于连接两个要查询的表，通过语句可以查询两个表中所有的数据组合。

2、内连接

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

在上述语法格式中，INNER JOIN 用于连接两表，ON来指定连接条件，其中INER可以省略。

3、外连接

语法：
SELECT 所查字段 FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件;

外连接的语法格式和内连接格式类似，只不过使用的是LEFT JOIN、RIGHT JOIN关系字，其中关键字左边的表被称为左表，关键字右边的表被称为右表。

使用左连接和右连接查询时，查询结果是不一致的，具体如下。

（1）LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中符合连接条件的记录。

（2）RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中符合连接条件的记录。

3.1、LEFT JOIN（左连接）

列如：在department表和employee表之间使用左连接查询，SQL语句如下：

select department.did,department.dname,employee.name FROM department LEFT JOIN employee on department.did=employee.did;

3.2、RIGHT JOIN（右连接）

列如：在department表和employee表之间使用右连接查询，SQL查询如下：

select department.did,department.dname,employee.name FROM department RIGHT JOIN employee ON department.did=employee.did;

4、符合条件连接查询

符合条件连接查询就是在连接查询的过程中，通过添加过滤条件来限制查询结果，是查询结果更加准确。

列如：在department表和employee表之间使用内连接查询，并将查询结果按照年龄从大道小进行排序，SQL语句如下：

select employee.name,employee.age,department.dname FROM department JOIN employee ON department.did=employee.did order by age;

四、子查询

1、带IN关键字的子查询

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

列如：查询存在年级为20岁的员工的部门，SQL语句如下：

select * from departmet WHERE did IN(SELECT did FROM employee WHERE age=20);

SELECT语句中还可以使用NOT IN关键字，其作用正好与IN相反。

列如：查询不存在年龄为20岁的员工的部门，SQL语句如下：

SELECT * FROM department WHERE did NOT IN(SELECT did FROM employee WHERE age=20);

2、带EXISTS关键字的子查询

EXISTS关键字后面参数可以是任意一个子查询，这个子查询的作用相当于测试，他不产生人和数据，只返回TRUE或FALSE，当返回值为TRUE时，外层查询才会执行。

列如：查询employee表中是否存在年龄大于21岁的员工，如果存在，则查询department表中的所有记录，SQL语句如下：

SELECT * FROM department WHERE EXISTS(select did from employee where age>21);

3、带ANY关键字的子查询

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

列如：使用带ANY关键字的子查询，查询满足条件的部门，SQL语句如下：

SELECT * FROM department WHERE did>any(select did from employee);

4、带ALL关键字的子查询

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

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

SELECT * FROM department WHERE did>all(select did from employee);

5、带比较运算符的子查询

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

列如：使用带比较运算符的子查询，查询赵四是哪个部门的员工，SQL语句如下：

SELECT * FROM department WHERE did=(select did from employee where name="赵四");