【数据库】MySQL数据库之约束与多表查询
约束
1.概述
- 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
- 目的：保证数据库中数据的正确性、有效性，完整性和一致性
- 分类：
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束
2.外键约束
- 概念
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
- 语法
添加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;- 删除/更新行为
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;多表查询
1.多表关系
在项目开发中, 各个表结构之间存在三种关系
- 一对多 (多对一)
- 多对多
- 一对一
一对多: 部门和员工
多对多: 学生与课程之间的关系
一对一: 用户与用户详情
2.多表查询概述
笛卡尔积: 集合A 和 集合B 的所有组合情况 (在多表查询时, 需要消除无效的笛卡尔积)
SELECT * FROM emp, dept WHERE emp.dept_id = dept.id;多表查询分类
- 连接查询
内连接: 相当于查询A \ B 交集部分数据
外连接:
左外连接: 查询左表所有数据, 以及两张表交集部分数据
右外连接: 查询右表所有数据, 以及两张表交集部分数据
自连接: 当前表与自身的连接查询, 自连接必须使用表别名
- 子查询
3.连接查询
3.1.内连接
内连接查询的时两张表交集的部分
内连接查询语法:
- 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;SELECT ca.camera_id, co.community_name FROM community co, camera ca WHEREca.community_id = co.community_id;- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;SELECT ca.camera_name, co.community_name FROM community co INNER JOIN camera ca ONca.community_id = co.community_id;3.2.外连接
- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;3.3.自连接
自连接查询可以是内连接查询, 也可以是外连接查询
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;4.联合查询
对于 uniobn 查询, 就是把多次查询的结果合并起来, 形成一个新的查询结果集
多张表的字段类型要保持一致
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;删掉 ALL 则达到去重的效果
5.子查询
SQL 语句中嵌套 SELECT语句, 称为嵌套查询, 又称子查询
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 中的任何一个
根据子查询结果不同, 分为:
- 标量子查询 (子查询结果为单个值)
- 列子查询 (子查询结果为一列)
- 行子查询 (子查询结果为一行)
- 表子查询 (子查询结果为多行多列)
5.1.标量子查询
标量子查询返回的结果是单个值 (数字 / 字符串 / 日期等), 最简单的形式, 这种子查询称为标量子查询
SELECT * FROM camera WHERE community_id = (SELECT community_id FROM community WHEREcommunity_name = '金达园');5.2.列子查询
列子查询返回的结果是一列 (可以是多行)
SELECT * FROM camera WHERE community_id in (SELECT community_id FROM community WHEREcommunity_name = '金达园');5.3.行子查询
行子查询返回的结果是一行 (可以是多列)
SELECT * FROM camera WHERE (community_id, creater) = (SELECT community_id, creater FROM camera WHERE camera_name = '摄像头1')5.4.表子查询
表子查询返回的结果是多行多列
SELECT * FROM camera WHERE (community_id, creater) in (SELECT community_id, creater FROM camera WHERE camera_name = '摄像头1');