mysql（四）

秒速五厘米 2022-01-26 06:23 267阅读 0赞

# 多表操作 #

**建数据库原则:**

*  通常情况下,一个项目/应用建一个数据库
 *  多表之间的建表原则
    
     *  一对多 : 商品和分类
        
         *  建表原则: 在多的一方添加一个外键,指向一的一方的主键
     *  多对多: 老师和学生, 学生和课程
        
        建表原则: 建立一张中间表,将多对多的关系,拆分成一对多的关系,中间表至少要有两个外键,分别指向原来的那两张表
     *  一对一: 班级和班长, 公民和身份证, 国家和国旗
        
         *  建表原则:
            
             *  将一对一的情况,当作是一对多情况处理,在任意一张表添加一个外键,并且这个外键要唯一,指向另外一张表
             *  直接将两张表合并成一张表
             *  将两张表的主键建立起连接,让两张表里面主键相等
         *  实际用途: 用的不是很多. (拆表操作 )
            
             *  相亲网站:
                
                 *  个人信息 : 姓名,性别,年龄,身高,体重,三围,兴趣爱好,(年收入, 特长,学历, 职业, 择偶目标,要求)
                 *  拆表操作 : 将个人的常用信息和不常用信息,减少表的臃肿,
                 *

我们首先创建一个数据库db（多表），再在db里创建两个数据表：grade（班级表）和student（学生表）

**创建数据库**：CREATE DATABASE db;

**选择要操作的数据表**：USE db;

**创建数据表**：

CREATE TABLE grade 
    
    　　　　　　(
    
    　　　　　　　　 id INT(4) NOT NULL PRIMARY KEY,
    
    　　　　　　 　　name varchar(36)
    
    　　　　　　 );
    
    　　　　　　
    
    　　　　　　CREATE TABLE student
    　　　　　　(
    　　　　　　　　sid INT(4) NOT NULL PRIMARY KEY,
    　　　　　　　　sname VARCHAR(36),
    　　　　　　　　gid INT(4) NOT NULL
    　　　　　　);

**外键**

定义：外键是指引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一性约束，外键用于建立和加强两个表数据之间的连接。在已经建立的grade表和student表中，student表中的gid就是grade表中的id，那么gid就可以作为student表的外键。其中，被引用的表grade就是主表，引用外键的表，即student表就是从表，两个表示主从关系。

**主键约束:** 默认就是不能为空, 唯一

*  外键都是指向另外一张表的主键
 *  主键一张表只能有一个

**唯一约束**: 列面的内容, 必须是唯一, 不能出现重复情况, 为空

*  唯一约束不可以作为其它表的外键
 *  可以有多个唯一约束

**为表添加外键约束**  
语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY（外键字段名） REFERENCES 外表表名（主键字段名）；

举例：为student表添加外键约束

命令：ALTER TABLE student ADD CONSTRAINT FK\_ID FOREIGN KEY (gid) REFERENCES grade(id);

注意：定义外键名时不能加引号。

使用SHOW CREATE ABLE student命令查看student表：

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwODE3ODI3_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwODE3ODI3_size_16_color_FFFFFF_t_70 1]  
**删除外键约束**

语法：ALTER TABLE 表名 DROP FOREIGN KEY 外键名；  
举例：删除student表中的外键约束  
命令：ALTER TABLE student DROP FOREIGN KEY FK\_ID;

## **操作关联表** ##

**添加数据**  
在表grade和表student中添加约束来建立两个表的关联关系：  
ALTER TABLE student ADD CONSTRAINT FK\_ID FOREIGN KEY(gid) REFERENCES grade (id);  
先为主表grade添加数据：  
INSERT INTO grade(id,name) VALUES (1,‘软件一班’),(2,‘软件二班’);  
此处若出现“1366错误”，可先执行如下命令：  
ALTER TABLE grade CONVERT TO CHARACTER SET utf8 COLLATE utf8\_general\_ci ;  
查询插入数据后的grade表：  
![在这里插入图片描述][20190528093908973.png]  
同理再在student表中插入数据，而且因为grade中添加的主键id只有1和2，所以student表中的gid字段值只能为1或者2，如下：  
INSERT INTO student (sid,sname,gid) VALUES (1,‘王红’,1),(2,‘李强’,1),(3,‘赵四’,2),(4,‘郝娟’,2);  
SELECT \* FROM student  
查询插入数据后的student表：  
![在这里插入图片描述][20190528094857659.png]  
上述命令执行完之后，两个表之间就具有了关联性，假如要查询软件一班有哪些学生，就要先查询软件一班的 id ，再根据这个 id 在student表中查询有哪些学生。  
步骤一：查出grade表中软件一班的 id ：  
命令：SELECT id FROM grade WHERE name=‘软件一班’;  
结果：  
![在这里插入图片描述][20190528101515505.png]  
步骤二：根据 id=1 ，在student表中查出对应的学生。  
命令：SELECT sname FROM student WHERE gid=1;  
结果：  
![在这里插入图片描述][20190528102208552.png]  
**删除数据**

因为grade表和student表具有关联关系，，而参照列的被参照值是不能被删除的，所以若想删除grade表中的软件一班，必须先删除student表中软件一班对应的所有学生。  
步骤1：删除软件一班所有学生  
命令：DELETE FROM student WHERE sname=‘张三’ OR sname='李四’OR sname=‘王五’;  
或者　DELETE FROM student WHERE gid=1;  
查询：SELECT \* FROM student WHERE gid=1;  
结果：  
![在这里插入图片描述][20190528103237191.png]  
说明软件一班的学生已经全部删除。  
步骤二：在grade表中将软件一班删除  
命令：DELETE FROM grade WHERE id=1;  
查询：SELECT \* FROM grade;  
结果：  
![在这里插入图片描述][20190528103615477.png]  
**连接查询**

在进行下面的操作之前，先在test数据库中创建两个表：department表和employee表。

USE test;
    
    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 
    
    );

在进行下面的操作之前，先在test数据库中创建两个表：department表和employee表。

USE test;

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,‘网络部’),(2,‘媒体部’),(3,‘研发部’),(5,‘人事部’);

INSERT INTO employee(id,name,age,did)  
VALUES (1,‘王红’,20,1),(2,‘李强’,22,1),(3,‘赵四’,20,2),(4,‘郝娟’,20,4);  
![在这里插入图片描述][2019052811022333.png]  
![在这里插入图片描述][20190528110241536.png]  
**交叉连接**

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔集，也即返回第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数，例如，department表中有四个部门，employee表中有四个员工，那么交叉连接的结果就有4 \* 4 = 16 条数据。  
交叉连接的语法：SELECT \* FROM 表1 CROSS JOIN 表2；  
举例：使用交叉连接查询department表和employee表中的所有数据  
命令：SELECT \* FROM department CROSS JOIN employee;  
结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwODE3ODI3_size_16_color_FFFFFF_t_70 2]  
**内连接**

内连接（Inner Join）又称简单连接或自然连接，是一种常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较，并列出与连接条件匹配的数据行，组成新的记录。  
语法：SELECT 查询字段 FROM 表1 \[ INNER \] JOIN 表2 ON 表1.关系字段=表2.关系字段  
其中 INNER JOIN 用于连接两个表，ON 来指定连接条件  
举例：在department表和employee表之间使用内连接查询。  
命令：SELECT [employee.name][],department.dname FROM department JOIN employee ON department.did=employee.did;  
从结果可以看出，只有department.did 与employee.did 相等的员工才会被显示。  
此处还可以使用WHERE 语句来实现同样的功能：  
SELECT [employee.name][],department.dname FROM department,employee WHERE department.did=employee.did;  
结果：  
![在这里插入图片描述][20190528110930832.png]  
**自连接**

如果在一个连接查询中涉及的两个表其实是同一个表，这种查询称为自连接查询，例如要查询王红所在的部门有多少个人，就可以用自连接查询。  
命令：SELECT p1.\* FROM employee AS p1 JOIN employee AS p2 ON p1.did=p2.did WHERE p2.name=‘王红’;  
结果：  
![在这里插入图片描述][20190528111325598.png]

**外连接**

外连接分为左连接和右连接，当返回的查询结果不仅需要包含符合条件的数据，还需要包含其中一个表或者两个表的所有数据的时候，需要用到外连接查询。  
语法：SELECT 所查字段 FROM 表1 LEFT | RIGHT \[ OUTER \] JOIN 表2  
　　　ON 表1.关系字段=表2.关系字段  
　　　WHERE 条件  
（1）LEFT JOIN ——左连接：返回包括左表中的所有记录和右表中符合条件的记录。  
举例：SELECT department.did,department.dname,[employee.name][] FROM department LEFT JOIN employee ON department.did=employee.did;  
结果：![在这里插入图片描述][20190528112233192.png]

在此结果中，department中的记录全部显示，而employee中只显示了符合条件的数据（一共四条记录，只显示了符合条件的其中3条）,因为“人事部”没有人，所以相应的字段显示为NULL。

（2）RIGHT JOIN ——右连接：与左连接相反，返回包括右表中的所有记录和左表中符合条件的记录。  
举例：SELECT department.did,department.dname,[employee.name][] FROM department RIGHT JOIN employee ON department.did=employee.did;  
结果：  
![在这里插入图片描述][20190528112338579.png]

在此结果中，employee中的记录全部显示，而department中只显示了符合条件的数据,因为“郝娟”没有对应部门，所以相应的字段显示为NULL。

**复合条件连接查询**

复合条件连接查询指在连接查询时，通过添加限制条件来过滤结果。  
举例：在department表和employee表中使用内连接查询，并将结果按照年龄降序排列  
命令：SELECT [employee.name][],employee.age,department.dname FROM department JOIN employee ON department.did=employee.did ORDER BY age DESC;  
结果：

![在这里插入图片描述][20190528112429491.png]

**子查询**

子查询是指一个查询语句嵌套在另一个查询语句内部的查询。在执行时，首先执行子查询中的语句，然后将返回的结果作为外层查询的过滤条件。

**带 IN 关键字的子查询**  
举例1：查询年龄为20岁的员工的部门  
命令：SELECT \* FROM department WHERE did IN (SELECT did FROM employee WHERE age=20);  
结果：  
![在这里插入图片描述][20190528124747190.png]

举例2：查询不存在年龄为20岁的员工的部门  
命令：SELECT \* FROM department WHERE did NOT IN (SELECT did FROM employee WHERE age=20);  
结果：  
![在这里插入图片描述][20190528125701384.png]

**带 EXISTS 关键字的子查询**

EXISTS 关键字后面的参数可以是任意一个子查询，这个子查询不产生任何数据，只返回 TRUE 或 FALSE，当返回 TRUE 时，外层查询才会执行。  
举例：查询employee表中是否存在年龄大于21岁的员工，若存在则查询department表中所有记录。  
命令：SELECT \* FROM department WHERE EXISTS (SELECT did FROM employee WHERE age >21 );  
结果：  
![在这里插入图片描述][20190528125756845.png]

**带 ANY 关键字的子查询**

ANY 关键字表示只要满足内层子查询中的任意一个条件，就返回一个结果作为外层查询条件。  
举例：使用带ANY 关键字的查询，查询满足条件的部门。  
命令：SELECT \* FROM department WHERE did >ANY (SELECT did FROM employee);  
结果：  
![在这里插入图片描述][20190528125828907.png]

在此命令中，子查询会先将employee表中所有did查询出来，分别是1,1,2,4，然后将 department 中的 did 的值与之比较，只要大于employee.did中的任意一个值，就是符合查询条件的结果。由于employee.did的最小值为1，所以department中只要大于1的did都满足条件，即2,3,5。

**带 ALL 关键字的子查询**  
ALL关键字类似于ANY ，只是ALL关键字的子查询返回的结果需要同时满足所有内查询条件。  
举例：使用带 ALL 关键字的子查询，查询满足条件的部门。  
命令：SELECT \* FROM department WHERE did > ALL (SELECT did FROM employee);  
结果：  
![在这里插入图片描述][2019052812585649.png]

在此命令中，子查询会先将employee表中所有did查询出来，分别是1,1,2,4，然后将 department 中的 did 的值与之比较，只有大于employee.did中的所有值，才是符合查询条件的结果。由于employee.did的最大值为4，所以department中只有大于4的did才满足条件，即5。

**带 比较运算符的子查询**  
举例：使用带比较运算符的子查询，查询赵四属于哪个部门  
命令：SELECT did,dname FROM department WHERE did = (SELECT did FROM employee WHERE name=‘赵四’);  
结果：  
![在这里插入图片描述][201905281302275.png]  
参考https://www.cnblogs.com/heyangblog/p/7630118.html

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwODE3ODI3_size_16_color_FFFFFF_t_70]: /images/20220126/dfd3e33106174f3fa1145663255ff05c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwODE3ODI3_size_16_color_FFFFFF_t_70 1]: /images/20220126/d84ddae177994b239a2a15fda9ded0b1.png
[20190528093908973.png]: /images/20220126/746b96e45ea64f6cba3c4d2d6ba82243.png
[20190528094857659.png]: /images/20220126/9aa42dde82a04e14b71ef7e273780eda.png
[20190528101515505.png]: /images/20220126/796026272b9749d5b2402d0f7fdef6a2.png
[20190528102208552.png]: /images/20220126/952bcb97b34948acb6d9b3e8b2f7cdeb.png
[20190528103237191.png]: /images/20220126/6abe401d747a4155bf43245543d1e737.png
[20190528103615477.png]: /images/20220126/a059ce8c04f94aff83dc2735c75c8426.png
[2019052811022333.png]: /images/20220126/9f80680b75a0472ba2917dbd165ba847.png
[20190528110241536.png]: /images/20220126/8dd8a3f8dea34056b397ca688c8c4513.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwODE3ODI3_size_16_color_FFFFFF_t_70 2]: /images/20220126/fbcc0cc266ae4b61bc92a1d5a990c7ab.png
[employee.name]: http://employee.name
[20190528110930832.png]: /images/20220126/80b0ce7302eb455fbe61686ff65661dd.png
[20190528111325598.png]: /images/20220126/1392f17e28ee40158e9289a344e73aa0.png
[20190528112233192.png]: /images/20220126/e5727fb7e0a347f5a074f07a5666db68.png
[20190528112338579.png]: /images/20220126/d96d02bee67a417ebfe5647e6b1c7b77.png
[20190528112429491.png]: /images/20220126/ee425e895f514ddabd8ca70a88a2cb0e.png
[20190528124747190.png]: /images/20220126/a55a4a5b091640f693b18cdd232c49a6.png
[20190528125701384.png]: /images/20220126/b2fd077cf99b432c91f5f766b902f82d.png
[20190528125756845.png]: /images/20220126/86ef443cdd1a46b19e3d0c6b4e71afe9.png
[20190528125828907.png]: /images/20220126/bf693f74754e48d480fe14de076dad5c.png
[2019052812585649.png]: /images/20220126/2028e270d6c74e08bf7a8c65ee679eff.png
[201905281302275.png]: /images/20220126/b22c8add542f4bd2851f559c492cbd1f.png