9.数据库-查-多表操作

1.外键

实际开发中，健壮的数据库一定要有很好的参照完整性，为了保证数据的完整性，这里将两表之间数据建立关系，所以要在表中添加外键约束：

1.1什么是外键？

外键是指引用另一个表的一列或多列，被引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的连接。

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

1.2 为表创建外键

为表添加外键的格式如下：

alter table 表名 add constraint FK_ID foreign key(外键字段名) PEFERENCES 外表表名(主键字段名);

创建班级和学生表：

CREATE TABLE class(

cid int(4) NOT NULL PRIMARY KEY,

gname VARCHAR(10)

);

CREATE TABLE student(

sid int(4) NOT NULL PRIMARY KEY,

sname VARCHAR(10)

);

然后我们为他添加外键约束：

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

你可以用show create table 表名; 语句查看主从关系！

可以看出 sid是student表的外键，并且呢 sid外键依赖于class表中的cid主键，这样两个表就通过外键关联起来了。

需要注意的地方：

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

（2）定义外键名时，不能加引号，如：

constraint ‘FK_ID’ 或 constraint “FK_ID” 都是错误的。

多学一招：添加外键约束的参数说明：

如果主表中的数据被删除或修改，从表中对应的数据该怎么办？

很明显也应该被删除~ 否则会导致很多垃圾数据。

MYSQL可以在建立外键的时候添加 ON DELETE 或 ON UPDATE

子句来告诉数据库，怎么避免产生垃圾数据。

语法格式如下：

Alter table 表名 add constraint FK_ID foreign key(外键字段名) REFERENCES 外表表名(主键字段名);

[ ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT } ]

添加外键约束的参数说明

参数名称	功能描述
CASCADE	删除包括与已删除键值有参照关系的所有记录
SET NULL	修改包含与已删除健值有参照关系的所有记录，使用NULL值替换(前提是不能用于 NOT NULL 的字段！)
NO ACTION	不进行任何工作
RESTRICT	拒绝主表删除或修改外键关联列。

1.3删除外键约束

按照业务逻辑需求，需要解除两个表之间的关联关系时，就需要删除外键约束，删除外键约束的语法如下：

alter table 表名 drop foreign key 外键名;

例如：删除student表的外键：

ALTER TABLE student DROP FOREIGN KEY FK_ID;

2.关联关系

1.根据实体的内容设计数据库，实体间会有各种关联关系。所以根据实体设计的数据表之前也存在着各种关联关系，MySql中有三种：

（1）多对一

多对一是数据表最常见的一种关系。比如员工和部门之间的关系，一个部门可以有多个员工，而一个员工不能属于多个部门，也就是说部门表中的 “一行” 在员工表可有许多匹配行，但员工表中的一行在部门表中只能有一个匹配项。

所以我们讲外键时，在多对一的表关系中，应该把外键建在多的一方，否则会照成数据冗余。

（2）多对多

比如学生与课程之间的关系，一个学生可以选择多门课程，当然一门课程可以供多个学生选择，也就是说学生表的一行在课程表中能有许多匹配项，课程表表中的一行在学生表中也有许多匹配项。

（3）一对一

一对一在生活中最常见，比如人和身份证之间就是一对一的关系，一个人对应一张身份证，一张身份证匹配一个人。

首先我们要弄懂主从关系，从表需要主表的存在才有意义，身份证需要有人的存在才有意义，因此人是主表，身份证是从表。

要在身份证建立外键。身份证的外键必须是非空唯一的，例如身份证号码是外键，那么就该如此，所以呢一般都是用从表的主键座位外键进行约束。

在实际开发中，一对一并不常见，因为用这种关系存的话一般会放在一个表中，就不用约束了。

一对一常见于几个方面:

分割具有很多列的表
由于安全原因隔离表的一部分
保证临时的数据，且可以毫无费力的通过删除来删除这些数据。

2.添加数据

实际开发，最常见的就是多对一关系，我们上面有一个class 和

Student 表了我们添加约束：

ALTER TABLE student add CONSTRAINT FK_ID FOREIGN KEY(sid) REFERENCES class(cid);

此时表student 和 class 之间是多对一的关系。外键列只能插入参照列存在的值，所以如果要吧数据添加到student【添加学生】的话，要先添加class【班级】的表，你可以试一下先添加学生，会报错的.

删除数据

删除数据你得先删除从表的数据才能删除主表的数据，因为他们是关联的。例如：软件1班不开了，你得先删除软件1班的学生，才能把软件1班删除。

实际开发中不需要这样干，你可以查询到一班的学生，使他们的班级为null，软件1班没参照了，自然可以把软件1班删除。

3.连接查询

在实际开发中，一般都是把每个实体的所有信息存放在一个表中，当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询，连接查询包括交叉查询、内连接查询、外连接查询、下面针对连接查询讲解：

1. 交叉查询

交叉查询返回的结果是被连接的两个表的笛卡尔积，也就是表1 符合条件的行数 * 表2符合条件的行数。例如department 有4个部门，employee有4个员工，那么交叉连接的结果就有 4*4=16条数据。

交叉连接的语法格式如下：

SELECT * FROM 表1 CROSS JOIN 表2;

其中： CROSS JOIN 用于连接两个要查询的表，通过该语句可以查询量表中全部数据组合。

先创建department【部门】表和 employee【雇员】表：

CREATE TABLE department(

did int(4) NOT NULL PRIMARY KEY,

dname varchar(36)

);

CREATE TABLE employee(

id int(4) NOT NULL PRIMARY KEY,

name VARCHAR(36),

age int(2),

did int(4) NOT null

);

INSERT INTO department(did,dname)VALUES(1,'网络部');

INSERT INTO department(did,dname)VALUES(2,'媒体部');

INSERT INTO department(did,dname)VALUES(3,'研发部');

INSERT INTO department(did,dname)VALUES(5,'人事部');

INSERT INTO employee (id,name,age,did)VALUES(1,'王强',20,1);

INSERT INTO employee (id,name,age,did)VALUES(2,'李红',22,1);

INSERT INTO employee (id,name,age,did)VALUES(3,'赵四',20,2);

INSERT INTO employee (id,name,age,did)VALUES(4,'恒娟',20,4);

创建了两个和插入了数据↑

现在我们用交叉连接试一下：

SELECT * FROM department CROSS JOIN employee;

我们可以看到，结果是部门和雇员表的结合，实际开发不会这样，但是会结合数据条件进行目的信息查询。

2. 内连接

内连接（Inner Join）又叫简单连接或自然连接，是一种常见的连接查询，内连接用运算符对两个表的数据进行比较，并列出与连接条件匹配的数据行，组成新的记录，换句话说：

-内连接只有满足条件的记录才能出现查询结果中，语法格式如下：

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

其中：

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

例1 在department表和 employee表之间用内连接查询：

SELECT employee.name,department.dname FROM department JOIN employee

ON department.did = employee.did;

从查询结果可以看出，只有 department.did 和 department.did 一样的时候，才会被显示。

例2：还可以用WHERE条件语句来写：【其实就是 ON 换成 WHERE】

SELECT employee.name,department.dname FROM department,employee WHERE department.did = employee.did;

虽然用 ON或用WHERE 查询出来的结果是一样的，但是用WHERE 可以不用加 JOIN 直接在FROM 后用逗号分隔表，然后直接WHERE 添加条件即可。

如果在一个连接查询中，涉及的两个表是同一个表，这种查询称为自连接查询，自连接查询是一个特殊的内查询。

例如：查询李红所在的部门中还有其他什么人：【别名方法】

SELECT p1.* FROM employee p1 JOIN employee p2 ON p1.did = p2.did WHERE p2.name='李红';

如果你看不懂，那么尝试把 p1 去掉：

SELECT * FROM employee p1 JOIN employee p2 ON p1.did = p2.did WHERE p2.name='李红';

这题一定要懂啊！主要还是他自连接会有两个表. 【自连接一定要别名来查询】

3. 外连接

内连接只会查询包含符合查询条件和连接条件的数据，但是很多时候都包含很多没必要的数据，所以：外连接查询、分为左连接查询和右连接查询：

外连接查询格式语法如下：

SELECT 所查字段 FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2

ON 表1.关系字段=表2.关系字段 WHERE 条件;

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

左、右查询的结果是不一致的：

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

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

LEFT JOIN 左连接

左连接如果左表某条记录不存在，那么在右表中显示为空(NULL)

例：在department表和 employee表之间使用左连接查询：

SELECT department.did,department.dname,employee.name FROM department

LEFT JOIN employee ON department.did = employee.did;

结果：

did dname name

1 网络部王强

1 网络部李红

2 媒体部赵四

3 研发部 NULL

5 人事部 NULL

在结果可以看出，研发部和人事部在 employee【雇员】表中根本没人在其中。

RIGHT JOIN 右连接

和左连接正好相反：右连接如果右表某条记录不存在，那么在右表中显示为空(NULL)

例：在department表和 employee表之间使用右连接查询：

SELECT department.did,department.dname,employee.name FROM department

RIGHT JOIN employee ON department.did=employee.did;

结果：

did dname name

1 网络部王强

1 网络部李红

2 媒体部赵四

NULL NULL 恒娟

可以看出是和左连接相反的，但是查询的数据是：

employee【雇员】表中只有恒娟是没在 department【部门】表中的。

4.复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件来限制查询结果，使得查询更加精确。

例：

在部门表和雇员表中用内连接查询，并且查询结果按照年龄从小排到大：

SELECT employee.name,employee.age,department.dname FROM department JOIN employee

ON employee.did=department.did ORDER BY age ;

直接在最后加上所以他就会从按照年龄小排到大了。

4.子查询

1.带IN关键字的子查询

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

例：查询存在年龄20岁的员工的部门【即 20岁员工所在的部门】：

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

你可以分开看先看括号的子查询返回括号返回值出来在配合外层查询。

还可以用 NOT IN 呢， NOT IN 这里就不多说了。

2.带EXISTS 关键字的子查询

EXISTS 后面可以是任意一个子查询，这个子查询作用相当于测试，他不产生任何数据，他只返回 TURE / FALSE ，当返回 TRUE时，外层的查询才会执行。

例：如果雇员表中存在年龄大于21 的员工，若存在，则打印整个表，否则不打印：

SELECT * FROM employee WHERE EXISTS(SELECT did FROM employee WHERE age > 21);

其中：因为子查询有数据返回所以会返回 TRUE 然后整个表达式为真所以打印了employee 的表。

3. 带ANY关键字的子查询

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

例：使用带ANY关键字的子查询查询满足条件的部门：

SELECT * FROM department WHERE did > ANY (SELECT did FROM employee);

其实就是说 department 的 did 要是大于 employee中的did【其中一个】，就显示。

4. 带ALL关键字的子查询

All关键字和ANY关键字有点相似，只不过带ALL关键字的子查询返回的结果需同时满足所有内层查询条件、

例：使用带ALL关键字的子查询，查询满足条件的部门：

SELECT * FROM department WHERE did > ALL(SELECT did FROM employee);

它的意思就是说department表中的 did ，如果大于 employee中的did【全部】就显示。

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

前面讲的 ANY 和 ALL 都用了 > 比较运算符，子查询还可以用其他的比较运算符，例如： < 、 >= 、 = 、 != 等…

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

SELECT * FROM department WHERE did = (SELECT did FROM employee WHERE employee.name = '赵四');

所以自己去多尝试即可：

完。

posted @ 2021-06-07 19:49 咸瑜阅读(323) 评论(0) 编辑收藏举报

刷新页面返回顶部

咸瑜

无限风光在险峰

9.数据库-查-多表操作

9.数据库-查-多表操作

2.关联关系

公告