MySQL(四)多表操作 | 您所在的位置:网站首页 › 多表操作的前提条件是 › MySQL(四)多表操作 |
一、外键 FOREIGN KEY
外键是值引用另一个表的一列或者多列,被引用的列应该具有主键约束或者唯一性约束。 被引用的表是主表;引用外键的表是从表。两者是主从关系。从表可以通过外键连接主表中的信息,从而建立两个表之间的联系。 1、为表添加外键约束 ADD ALTER TABLE 表名 ADD CONSTRAINT 外键别名 FOREIGN KEY(外键字段名) REFERENCES 外表表名(主键字段名); 建立外键的表必须是InnoDB型;定义外键名时,外键名不能使用引号。若主表中的数据被删除或者修改,从表中的数据怎么处理?MySQL 可以在建立外键时添加 ON DELETE 或则 ON UPDATE 子句来告诉数据库,怎样避免垃圾数据的产生。 ALTER TALBE 表名 ADD CONSTRIANT 外键别名 FOREIGN KEY(外键字段名) REFERENCES 外键表名(主键字段名) [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}];添加外键约束的参数说明 参数名称 功能描述 CASCADE 删除包含与已删除键值有参照关系的所有记录 SET NULL 修改包含与已删除键值有参照关系的所有记录,使用 NULL值 替换。(不能用于设定为NOT NULL 的字段) NO ACTION 不进行任何操作 RESTRICT 拒绝主表删除或者修改外键关联列。(在不定义ON DELETE 和 ON UPDATE 子句时,只是默认值,也是最安全的设置) 2、为表删除外键约束 DROP ALTER TABLE 表名 DROP FOREIGN KEY 外键别名; 二、操作关联表 1、关联关系(1)多对一; (2)多对多; 通常,为了实现这种关系需要定义一张中间表(称为连接表)。 (3)一对一。 2、添加数据 首先,需要建立表之间的关联关系,即“为表添加外键约束”;然后,再主表中添加数据(使用普通的INSERT语句即可);最后,向从表中添加数据。 INSERT INTO 表名(字段名1, 字段名2, ...) VALUES (值1, 值2, ...); 3、删除数据(1)如果主表与从表在建立外键连接关系中,没有具体指明主表删除或者更新数据时对应的参数:CASCADE、SET NULL、NO ACTION、RESTRICT,那么默认是RESTRICT——从表拒绝主表删除或修改外键关联的列。如果指定了修改的参数,则根据指定的参数进行更改。 (2)现在我们以默认情况说明:假设我们要删除主表中的某部分数据,则需要先删除从表总与之关联的数据。删除从表中关联的数据后,约束即解除。然后再删除主表中的数据。 (3)删除语句: DELETE FROM 表名 WHERE 条件表达式; 三、连接查询查询数据时,通过连接操作查询多个表中的实体信息,若两个或多个表中存在相同意义的字段,便可以通过这些字段对不同的表进行连接查询。 连接查询包括:交叉连接查询、内连接查询、外连接查询。 1、交叉连接 CROSS JOIN返回的结果为两个表所有数据的笛卡尔积,即两个表中所有数据的组合。 SELECT * FROM 表1 CROSS JOIN 表2; 2、内连接 [INNER] JOIN又称为简单连接或者自然连接。对两个表中的数据进行比较,并列出与连接条件匹配的数据行,组合成新的记录。 SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段;例:SELECT employee.name,department.dname FROM department JOIN employee ON department.did = employee.did; 其中,employee.name,department.dname 为要结果要显示的字段; department.did = employee.did 为约束条件。 3、外连接 LEFT|RIGHT [OUTER] JOIN外连接分为左连接与右连接。 SELECT 所查字段 FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件; LEFT JOIN (左连接),返回的结果包括左表中的所有记录和右表中符合条件的记录;RIGHT JOIN (右连接),返回的结果包括右表中的所有记录和左表中符合条件的记录。(1)左连接:如果左表中的某条记录在右表中不存在,则在右表中显示为空; (2)右连接:如果右表中的某条记录在左表中不存在,则在左表中显示为空。 4、复合条件连接查询再连接查询的基础上增加过滤条件来限制查询结果,使得查询结果更加精确。 例: SELECT employee.name, employee.age, department.dname FROM department JOIN employee ON department.did=employee.did ORDER BY age; 四、子查询子查询是指一个查询语句嵌套在另一个查询语句内部的查询,其将查询的结果作为外层查询的过滤条件。子查询常用的操作符包括:IN、EXISTS、ANY、ALL等。 1、带 IN 关键字的子查询例: SELECT * FROM department WHERE did IN(SELECT did FROM employee WHERE age=20); 2、带 EXISTS 关键字的子查询EXISTS 关键字后面的参数可以是任意一个子查询,这个子查询的作用相当于测试,它不产生任何数据,只返回FALSE 或者 TRUE。 只有返回 TRUE 时,外层查询才会执行。当 子查询返回 TRUE 时,相当于直接执行了外层查询。 例: SELECT * FROM department WHERE EXISTS(SELECT did FROM employee WHERE age>21); 3、带 ANY 关键字的查询ANY关键字表示满足其中任意一个查询条件,允许创建一个表达式对子查询结果的返回值进行比较,只要满足内层子查询中的任意一个比较条件,就返回一个结果作为外层查询条件。 例: SELECT * FROM department WHERE did>ANY(SELECT did FROM employee); 4、带 ALL 关键字的子查询外层查询,需要同时满足所有内层查询条件。 例: SELECT * FROM department WHERE did>ALL(SELECT did FROM employee); 5、带比较运算符的子查询例: SELECT * FROM department WHERE did=(SELECT did FROM employee WHERE name='赵四');
|
CopyRight 2018-2019 实验室设备网 版权所有 |