mysql五表查询_MySQL(五） 多表查询

5 多表操作

5.1. 外键

引用另一个表中的一列或者多列，被引用的列应该具有主键约束或者非空约束

搭建环境，在数据库test中创建主表class：

CREATE TABLEclass(

cidINT(4) NOT NULL PRIMARY KEY,

cnameVARCHAR(10)

);

5.1.1 添加外键约束

(1) 在创建表的同时为表添加外键(在从表中添加SQL语句)

格式：CONSTRAINT 自定义外键名 FOREIGN KEY (从表引用字段名)

REFERENCES 主表名 (主表被引用字段名)

CREATE TABLEstudent(

sidINT(4),

snameVARCHAR(10),

cidINT(4),CONSTRAINT FK_ID FOREIGN KEY (cid) REFERENCESclass (cid)

);

(2) 为已存在的表添加外键

搭建环境，在数据库test下创建数据表pupil(先删除数据表student)

CREATE TABLEpupil(

sidINT(4),

snameVARCHAR(10),

cidINT(4)

);

格式：ALTER TABLE 从表名 ADD CONSTRAINT 自定义外键名

FOREIGN KEY (从表引用字段名)

REFERENCES 主表名 (主表被引用字段名)

ALTER TABLE pupil ADD CONSTRAINTFK_IDFOREIGN KEY(cid)REFERENCES class (cid);

5.1.2 删除外键约束

格式：ALTER TABLE 从表名

DROP FOREIGN KEY 外键名

ALTER TABLEpupilDROP FOREIGN KEY FK_ID;

5.2. 操作关联表

5.2.1 关联关系

一对多(班级与学生)：将外键建在多的一方。

多对多(课程与学生)：定义一张中间表(连接表)，该表会存在两个外键，分别参照课程表和学生表

一对一(人与身份证)：从主从关系入手，从表需要主表的存在才有意义

5.2.2 添加数据

给从表添加数据时，它引用的字段必须为主表中被引用字段的值

5.2.3 删除数据

在具有关联关系的表中删除数据时，一定要先删除从表中的数据，然后再删除主表中的数据，否则会报错

5.3. 连接查询

下面的两张表通过相同意义的字段模拟外键约束，并没有真正的外键约束，但其特点和外键是一样的，可以通过这些字段对不同的表进行连接查询

搭建查询环境，在数据库test下创建数据表department和employee

CREATE TABLEdepartment(

didINT(4) NOT NULL PRIMARY KEY,

dnameVARCHAR(20)

);

CREATE TABLEemployee(

idINT(4) NOT NULL PRIMARY KEY,

nameVARCHAR(10),

ageINT(2),

didINT(4) NOT NULL);

在两个表中插入相关数据

INSERT INTOdepartmentVALUES (1,'network'),(2,'media'),(3,'development'),(5,'personnel');

INSERT INTOemployeeVALUES (1,'Zhou',20,1),(2,'Will',22,1),(3,'Marry',20,2),(4,'Jin',20,4);

5.3.1 交叉连接(笛卡尔积)

格式：SELECT 字段名 FROM 主表CROSS JOIN 从表;

SELECT * FROM department CROSS JOIN employee;

相当于：

SELECT * FROM department,employee;

5.3.2 内连接(自然连接)

格式：SELECT 字段名 FROM 主表

[INNER] JOIN 从表

ON 主表.关系字段=从表.关系字段

SELECTdepartment.dname,employee.nameFROM department INNER JOINemployeeON department.did=employee.did;

相当于：

SELECTdepartment.dname,employee.nameFROMdepartment,employeeWHERE department.did=employee.did;

5.3.3 外连接

(1) 左连接：以左表为准，去右表找数据，找不到，用null补齐

格式：SELECT 字段名 FROM 左表

LEFT JOIN 右表

ON 左表.关系字段=右表.关系字段

SELECTdepartment.dname,employee.nameFROM department LEFT JOINemployeeON department.did=employee.did;

(2) 右连接：以右表为准，去左表找数据，找不到，用null补齐

格式：SELECT 字段名 FROM 左表

RIGHT JOIN 右表

ON 左表.关系字段=右表.关系字段

SELECTdepartment.dname,employee.nameFROM department RIGHT JOINemployeeON department.did=employee.did;

注意：在左连接中互换左右表的位置，同样可以得到右连接的效果

(3) 全连接(FULL JOIN)：MySQL不支持，可以通过左连接UNION右连接得到

5.3.4 子查询

(1) 带IN关键字的子查询

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

例：查询存在20岁的员工的部门

SELECT dname FROMdepartmentWHERE did IN(SELECT did FROM employee WHERE age=20);

(2) 带EXISTS关键字的子查询

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

例：查询是否存在年龄大于21岁的员工，如果存在，则查询department表中所有的记录

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

(3) 带ANY关键字的查询

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

SELECT * FROMdepartmentWHERE did>ANY (SELECT did FROM employee);

(4) 带ALL关键字的子查询

ALL关键字表示满足所有的条件，子查询返回的结果需同时满足所有的内层查询条件

SELECT * FROMdepartmentWHERE did>ALL (SELECT did FROM employee);

(5) 带比较运算符的子查询

SELECT * FROMdepartmentWHERE did=(SELECT did FROM employee WHERE name='Marry');