数据库——进阶，约束介绍，数据库设计概念，表关系以及多表查询方法，超详细教程

约束

概念和分类

约束的概念

• 约束是作用于表中列上的规则，用于限制加入表的数据
• 约束的存在保证了数据库中数据的正确性、有效性和完整性

约束的分类

约束名称	描述	关键字
非空约束	保证列中所有数据不能有null值	not null
唯一约束	保证列中所有数据各不相同	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
检查约束	保证列中的值满足某唯一条件	check
默认约束	保存数据时，未指定值则采用默认值	default
外键约束	外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性	foreign key

注意：MySQL不支持检查约束

自动增长 ：auto_increment 当列是数字类型并且唯一约束时。

非空约束

概念：

• 非空约束用于保证列中所有数据不能有NULL值

语法：

• 添加约束

  -- 创建表时添加非空约束
  CREATE TABLE 表名(
     列名 数据类型 NOT NULL,
     …
  ); 
  
  

  -- 建完表后添加非空约束
  ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
  

• 删除约束

  ALTER TABLE 表名 MODIFY 字段名 数据类型;
  

唯一约束

概念：

• 唯一约束用于保证列中所有数据各不相同

语法：

• 添加约束

  -- 创建表时添加唯一约束
  CREATE TABLE 表名(
     列名 数据类型 UNIQUE [AUTO_INCREMENT],
     -- AUTO_INCREMENT: 当不指定值时自动增长
     …
  ); 
  CREATE TABLE 表名(
     列名 数据类型,
     …
     [CONSTRAINT] [约束名称] UNIQUE(列名)
  ); 
  

  -- 建完表后添加唯一约束
  ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
  

• 删除约束

  ALTER TABLE 表名 DROP INDEX 字段名;
  

主键约束

概念：

• 主键是一行数据的唯一标识，要求非空且唯一

• 一张表只能有一个主键

语法：

• 添加约束

  -- 创建表时添加主键约束
  CREATE TABLE 表名(
     列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
     …
  ); 
  CREATE TABLE 表名(
     列名 数据类型,
     [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
  ); 
  
  

  -- 建完表后添加主键约束
  ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
  

• 删除约束

  ALTER TABLE 表名 DROP PRIMARY KEY;
  

默认约束

概念：

• 保存数据时，未指定值则采用默认值

语法：

• 添加约束

  -- 创建表时添加默认约束
  CREATE TABLE 表名(
     列名 数据类型 DEFAULT 默认值,
     …
  ); 
  

  -- 建完表后添加默认约束
  ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
  

• 删除约束

  ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
  

外键约束

概念：

• 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

语法：

• 添加约束

  --创建表时添加
  create table 表名(
  	列名 数据类型,
  	...
  	constraint 外键名 foreign key(外键列名) references 主表(主表列名)
  )
  

  --建完表后添加约束
  alter table 表名 add constraint 外键名称 foreign key(外键字段名称) references 主表名称(主要列名称);
  

• 删除约束

  alter table 表名 drop foreign key 外键名称;
  

数据库设计

简介

软件的研发步骤

数据库设计概念

• 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型
• 建立数据库中的表结构以及表与表之间的关联关系的过程
• 有哪些表？表里有哪些字段？表与表之间有什么关系？

数据库设计的步骤

1. 需求分析（数据是什么？数据具有哪些属性？数据与属性的特点是什么）
2. 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
3. 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
4. 维护设计（对新的需求进行建表，表的优化）

表关系

• 一对一

  • 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能。
  • 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一（UNIQUE）

• 一对多（多对一）

  实现方式：在多的一方建立外键，指向一的一方的主键

• 多对多

  实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

多表查询

笛卡尔积

取A，B集合所有组合情况

多表查询

• 连接查询
  • 内连接：相当于查询A B交集数据
  • 外连接：
    • 左外连接：相当于查询A表所有数据和交集部分数据
    • 右外连接：相当于查询B表所有数据和交集部分
• 子查询

内连接查询

语法：

-- 隐式内连接
select 字段列表 from 表1,表2... where 条件;
-- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 条件

内连接相当于查询A B交集数据

外连接查询

语法：

-- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件

--右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件

• 左外连接：相当于查询A表所有数据和交集部分数据
• 右外连接：相当于查询B表所有数据和交集部分

子查询

1. 子查询概念

   • 查询中嵌套查询，称嵌套查询为子查询

2. 子查询根据查询结果不同，作用不同

   • 单行单列：作为条件值，使用= != > < 等进行条件判断

     select 字段列表 from 表 where 字段名 = (子查询);
     

   • 多行单列：作为条件值，使用in关键字进行条件判断

     select 字段列表 from 表 where 字段名 in (子查询);
     

   • 多行多列：作为虚拟表

     select 字段列表 from (子查询) where 条件;
     

事务

简介：

• 数据库的事务是一种机制，一个操作序列，包含了一组数据库操作命令
• 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
• 事务是一个不可分割的工作单元

-- 开启事务
start transaction;
-- 或
begin;
-- 提交事务
commit;
-- 回滚事务
rollback;

事务的四大特征：

• 原子性（Atomicity）：事务是不可分割的最小操作单位，要么同时成功，要么同时失败
• 一致性（Consistency）：事务完成时，必须使所有的数据保存一致状态
• 隔离性（Isolation）：多个事务之间，操作的可见性
• 持久性（Durability）：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的

mysql的事务默认自动提交

-- 查看事务的默认提交方式
select @@autocommit;
-- 1 自动提交 0 手动提交
-- 修改事务提交方式
set @@autocommit = 0;