多表操作
一、外键
定义: 外键是指引用另一个表中的一列或多列,被引用的列应该是具有主键约束或唯一性约束.外键用于建立
和加强两个表数据之间的连接.
添加外键约束:
alter table 表名 add constraint PK_ID foreign key(外键字段名) references 外表表名(主键字段名)
在为表添加外键约束时,需要注意以下地方:
(1) 建立外键的表必须是InnoDB型,不能是临时表.因为在mySQL中只有InnoDB类型的表才支持外键.
(2) 定义外键名时,不能加引号,如constraint 'FK_ID'是错误的.
其他
alter table 表名 add constraint FK_ID foreign key (外键名) references 外表表名(主键字段名);
[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT }]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT }]
RESTRICT 是默认设置.
删除外键约束:
alter table 表名 drop foreign key 外键名;
二、操作关联表
2. 1 MySQL中的关联关系
多对一: 如员工和部门. 在多对一的表关系中,应该将外键建立在多的一方,否则会造成数据的冗余.
多对多: 如学生和课程. 需要定义一张中间表,该表会存在两个外键,分别参照课程表和学生表.
在多对多的表关系中,连接两个表的外键都是可以重复的,但是两个外键之间的关系是不能重复的,
所以这两个外键又是连接表的联合主键.
一对一: 如人和身份证. 人为主表, 身份证为外表, 通常会直接用从表中的主键作为外键.
2.2 连接查询
在关系型数据库管理系统中,建立表时各个数据之间的关系不必确定,通常将每个实体的所有信息存放
在一个表中,当查询数据时,通过连接操作查询多个表中的实体信息,当两个或多个表中存在相同意义的字段时,
便可以通过这些字段对不同的表进行连接查询。
连接查询包括:交叉连接查询、内连接查询、外连接查询。
交叉连接
交叉连接返回的结果是被连接的两个表中所有行的笛卡尔积。也就是返回第一个表中符合查询条件的
数据行数乘以第二个表中符合查询条件的数据行数。
语法: SELECT * FROM 表1 CROSS JOIN 表 2;
需要注意的是,在实际开发中一般不会使用交叉连接。
内连接
内连接又称简单连接或自然连接,是一种常见的连接查询。内连接使用比较运算符对两个表中的数据
进行比较,并列出与连接条件匹配的数据行,组合成新的记录,也就是说在内连接查询中,只有满足条件的记录
才能出现在查询结果中。
语法:
SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段
如果在一个连接查询中,涉及到的两张表是同一个表,这种查询称为自连接查询。自连接是一种特殊的
内连接,它是指相互连接的表在物理上为同一个表,但逻辑上分为两个表。
外连接
内连接返回的结果只包含符合查询条件和连接条件的数据,然而有时候我们还需要包含没有关联的数据,
即返回查询结果中不仅包含符合条件的数据,而且还包括左表(左连接或左外连接)、右表(右连接或右外连接)
或两个表(全外连接)中的所有数据,此时就需要使用到外连接。
外连接包括:左连接和右连接。
语法
SELECT 所查字段 FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON
表1.关系字段 = 表2.关系字段 WHERE 条件
(1)LEFT JOIN(左连接):返回包括左表中的所有记录和右表中符合连接条件的记录。
(2)RIGHT JOIN (右连接):返回右表中的所有记录和左表中符合连接条件的记录。
复合条件连接查询
复合条件连接查询就是在连接查询的过程中,通过添加过滤条件来限制查询结果,使查询结果更加精确。
2.3 子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询。它可以嵌套在一个SELECT、SELECT ...INTO
语句、INSERT...INTO等语句中。在执行查询语句时,首先会执行子查询中的语句,然后将返回的结果作为外层查询
的过滤条件,在子查询中通常可以使用IN、EXISTS、ANY、ALL操作符。
带IN关键字的子查询
使用IN关键字进行子查询时,内层查询语句仅返回一个数据列,这个数据列中的值将供外层查询语句进行比
较操作。
带EXISTS关键字的子查询
EXISTS关键字后面的参数可以是任意一个子查询,这个子查询的作用相当于测试,它不产生任何数据,只
返回TRUE或FALSE,当返回值为TRUE时,外层查询才会执行。
由于,EXISTS关键字比IN关键字的运行效率高,在实际开发中,特别是大数据量时,推荐使用EXISTS关键字。
带ANY关键字的子查询
ANY关键字表示满足其中任意一个条件,它允许创建一个表达式对子查询的返回列表进行比较,只要满足
子查询中的任意一个比较条件,就返回一个结果作为外层查询条件。
带ALL关键字的查询
带ALL关键字的子查询返回的结果需同时满足所有内层查询条件。
带比较运算符的子查询
子查询中使用常见的比较运算符。