数据库（四）数据库（四）子查询的分类：相关子查询在外查询中，每查询一条记录，需要重新做一次子查询，这种称为相关子查

数据库（四）

子查询的分类：

非相关子查询

在主查询中，子查询只需要执行一次，子查询结果不再变化。子查询结果供主查询使用，这种查询方式称为非相关子查询。

-- 显示欧州人口总数和总GDP 

SELECT SUM(population),SUM(gdp) FROM bbc WHERE region='Europe';

区分：

是相关子查询，还是非相关子查询。主要看子查询是否能单独运行，子查询是否依赖外查询的查询结果。

如果子查询需要依赖外查询的查询结果，不能单独运行，就是相关子查询；

如果子查询不需要依赖外查询的查询结果，可以单独运行，就是非相关子查询。

数据完整性

数据完整性 = 数据准确性 + 数据可靠性

为了防止垃圾数据的产生，从而影响数据库的执行效率。

分类：

数据完整性分为：实体完整性、域完整性、引用完整性、自定义完整性

实体完整性

保证每行所代表的实体能相互区别，不能存在两条一模一样的记录。

实现方法：

1、主键约束 ：主键（Primary Key）是表中的一到多个列，主键不能为空，也不能重复。一个表中只能有一个主键。

适合做主键的列，需要满足四个条件：

1、值必须唯一

2、值不能为空

3、不能带业务含义

4、值不能变化

注：身份证不适合做主键。身份证中国人才有，外国人没有（值不能为空）；身份证具有业务含义；身份证可能会发生变动（一代身份证、二代身份证）。

2、唯一约束：指给定列的所有值必须唯一，该列在表中每一行的值必须唯一。它和主键约束的区别在于该列可以为空，并且可以在一张表中给出多个列设置唯一约束。

CREATE TABLE 表名（

……

phone VARCHAR(20) **UNIQUE**

）

**unique：设置唯一性**

域完整性

保证指定列是数据是有效性（mysql 对域完整性支持不好）。

实现方式：

1、非空约束（not null） ：指定列的值，不能为null值。

2、默认值（default） ：添加记录时，如果该列的值不做插入，那么以默认值插入。

3、检查约束（check） ：检查插入列数据的有效性。（mysql不支持）

  CREATE TABLE t_em( 
  pk_emld INT PRIMARY KEY AUTO_INCREMENT, 
  e_name VARCHAR(20) NOT NULL, -- 非空约束 
  e_status ENUM('在职','离职') DEFAULT '在职', -- 默认值 
  e_money INT CHECK(e_money>1000 AND e_money<10000) -- 检查约束 );

引用完整性

从表外键中出现的数据，必须在主表的主键列中出现。

实现方式：

外键约束：

外键和外键约束：

外键是描述表与表之间联系的一个列，是表中的列；

外键约束是指在外键列上加上一个约束，强制外键列引用的数据是正确的。如果违反该约束，则不允许该条数据的修改或插入。

注意：没有外键约束，不代表没有外键。

-- 添加外键约束 

ALTER TABLE t_car ADD CONSTRAINT fk_1 FOREIGN KEY(fk_manId) REFERENCES t_man(pk_manId);

删除主表记录：

删除主表记录时，如果该主表记录有从表记录引用。由于有外键约束存在，则无法删除主表记录。

删除主表记录有三种方式：

1、级联删除：先将主表记录中关联的从表记录全部删除，然后再删除主表记录。

DELETE FROM t_car WHERE fk_manId=1; DELETE FROM t_man WHERE Pk_manId=1;

2、外键置空：先将主表记录中关联的从表记录外键设置为null，然后再删除主表记录。

UPDATE t_car SET fk_manId=NULL WHERE fk_manId=2; DELETE FROM t_man WHERE pk_manId=2;

3、改变状态：将主表记录添加标识列（有效/无效）；删除主表记录时，将状态修改为无效。

联表查询：

如果数据来自多个表，那么可以采用联表查询的方式来实现。

表联接就是指将多个表联合在一起实现查询效果。

笛卡尔乘积：

是将两张表的所有数据相连，最后联接的结果数为两张表数量的乘积。

-- 查询所有汽车，以及汽车的主人名字 

SELECT c.*,m.m_name FROM t_man m **JOIN** t_car c **ON** **m.pk_manId=c.fk_manId**;

SELECT c.*,m.m_name FROM t_man m,t_car c WHERE m.pk_manId=c.fk_manId;

表联接分类：

內联接、外联接、自联接

內联接

只能查询两个表之间有关联记录的数据。

-- 显示所有的公民，以及公民拥有的汽车数量 

SELECT m.m_name,COUNT(c.pk_carId) FROM t_man m **JOIN** t_car c **ON** m.pk_manId=c.fk_manId **GROUP BY m.pk_manId**; 

SELECT m.m_name,COUNT(c.pk_carId) FROM t_man m **,** t_car c **WHERE** m.pk_manId=c.fk_manId GROUP BY m.pk_manId;

如果有汽车没有主人，则不在查询结果中。內联接只能查询有主人的汽车。

外联接

可以查询出一个表中，所有符合条件的记录，无论该记录是否有关联数据。

-- 显示所有的公民，以及公民拥有的汽车数量

SELECT m.m_name,COUNT(c.pk_carId) FROM t_man m **LEFT** JOIN t_car c ON m.pk_manId=c.fk_manId GROUP BY m.pk_manId;

自联接

将自己的表与自己联接。

-- 列出所有的员工和员工的领导人 

SELECT w2.`w_name`,w1.`w_name` FROM t_worker w1 RIGHT JOIN t_worker w2 ON w1.`pk_workerId`=w2.`fk_leader`;

视图：

视图可以看做是SQL语句的封装，可以看做是临时表，视图查询的结果会随着真实表数据的变化而变化。视图只提供查询功能，不提供数据修改的功能。

-- 列出所有政党和每个政党议员人数 

**CREATE VIEW** v_partyNum **AS** SELECT p.`partyName`,COUNT(m.`mspName`) num FROM party p LEFT JOIN msp m ON p.`partyCode`=m.`party` GROUP BY p.`partyCode`;

SELECT * FROM v_partyNum;

数据库三范式

第一范式

一范式：行不重复（每条记录不能完全一样），列不可再分（一条记录的列只能有一个值）。

第二范式

二范式：非主依主。非主键列必须依赖于主键列。二范式要求一个表只描述一个实体。

第三范式

三范式：非主独立。非主键列之间不能有依赖关系。