【数据库】MySQL数据库之约束与多表查询

约束

1.概述

1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
2. 目的：保证数据库中数据的正确性、有效性，完整性和一致性
3. 分类：

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

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');