数据库MySQL语句分类&&DDL语句&&三大范式&&表约束（超详细，含存储引擎，持续更新分享！！！）

SQL语句分类

SQL语句，即结构化查询语言(Structured Query Language)，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，同时也是数据库脚本文件的扩展名（.sql）

名词	解释	命令
DDL(数据定义语言)	定义和管理数据对象，如数据库，数据表等	CREATE、DROP、ALTER
DML(数据操作语言)	用于操作数据库对象中所包含的数据	INSERT、UPDATE、DELETE
DQL(数据查询语言)	用于查询数据库数据	SELECT
DCL(数据控制语言)	用来管理数据库的语言，包括管理权限及数据更改	GRANT、COMMIT、ROLLBACK

DDL建库建表

数据库结构

DDL语句操作数据库

1. 查看数据库

   show databases;
   

2. 创建数据库

   CREATE DATABASE 数据库名;
   

3. 删除数据库(危险操作)

   DROP DATABASE 数据库名;
   

4. 使用数据库

   use 数据库名;
   

DDL字段类型

1. 字符串类型

   

   重点： varchar(M)类型和char(M)类型区别

   类型	特点	空间上	时间上	适用场景
   char(M)	固定长度	浪费存储空间	效率高	存储不大，速度要求高
   varchar(M)	可变长度	节省存储空间	效率低	非char的情况

   举例：

   当char(5)和varchar(5)都存储了5位，其没有任何区别；但当小于5位时，例如存了3位，char(5)存了3位空余2位，空余的2位需要补0，而varchar(5)则只用了3位；所以，当字段数据是定长时，用char，其余用varchar

2. 数值类型

   

   • decimal（10，2）：整数位：10-2=8位，小数位：2位

3. 日期和时间类型

   

4. NULL类型

   

DDL创建数据库表

语法：

• 第一步：create table 表名()

CREATE TABLE teacher2(
  tid int comment "老师编号",
  tname VARCHAR(10) comment "老师姓名",
  tsex CHAR(1) comment "老师性别",
  tmoney decimal(10,2) comment "老师薪资",
  tbirthday datetime comment "老师生日"
)

上面案例创建了teacher表，包括5个字段；comment为字段注释

注释：可以给字段增加注释，即给字段增加描述，方便后续修改和维护字段

查看表结构

语法：

-- 方式1
desc 表名;
-- 方式2
show create table 表名;

• 执行了show create table 表名; 语句后，全选复制控制台输出

-- ``反引号作用 取消关键性
CREATE TABLE `teacher2` (
  `tid` int(11) DEFAULT NULL COMMENT '老师编号',
  `tname` varchar(10) DEFAULT NULL COMMENT '老师姓名',
  `tsex` char(1) DEFAULT NULL COMMENT '老师性别',
  `tmoney` decimal(10,2) DEFAULT NULL COMMENT '老师薪资',
  `tbirthday` datetime DEFAULT NULL COMMENT '老师生日'
) 
-- 存储 引擎（规则）
ENGINE=InnoDB 

-- 编码
DEFAULT CHARSET=utf8mb4 

-- 排序编码
COLLATE=utf8mb4_0900_ai_ci

1. `` 反引号：作用取消关键性
2. 存储引擎
3. 编码：配置文件中所写的编码集
4. 排序编码

数据库存储引擎

语法：

show  engines;

数据库存储引擎-InnoDB

• MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎
• InnoDB是MysQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交Commit)和回滚(Rollback)
• 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎
• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎
• 数据文件结构:
  • 表名.frm存储表结构(MySQL8.0时，合并在表名.ibd中)
  • 表名.ibd存储数据和索引
• InnoDB是为处理巨大数据量的最大性能设计
  • 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如: .frm,.par , .trn ,.isl, .db.opt等都在MySQL8.0中不存在了
• 对比MylISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引
• MyISAM只缓存索引，不缓存真实数据;InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响

数据库存储引擎-MyISAM

• MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复
• 5.5之前默认的存储引擎
• 优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用
• 针对数据统计有额外的常数存储。故而count(*)的查询效率很高
• 数据文件结构:
  • 表名.frm存储表结构
  • 表名.MYD存储数据(MYData)
  • 表名.MYI存储索引 (MYIndex)
• 应用场景:只读应用或者以读为主的业务

数据库存储引擎-MyISAM 和InnoDB区别（重点）

修改和删除数据表

• 修改表名

  -- 修改表名
  -- alter table 表名 关键词 属性
  alter table teacher rename as student; 
  

  将teacher表的表名改为student

• 修改字段

  -- 修改字段
  -- 1.modify  覆盖式，类型和属性可以修改，不能修改字段名
  alter table student modify qq varchar(20);
  
  -- 2.change  覆盖式 字段名 类型 属性都可以修改
  alter table student change qq weixin varchar(50) comment '微信号';
  

• 删除字段

  -- 删除表的字段(危险操作)
  alter table student drop qq;
  

  将student表中的字段qq删除

• 添加字段

  -- 给表添加字段
  -- alter table 表名 add 字段 类型 属性
  alter table student add qq varchar(12) comment 'qq号'; 
  

  给student表添加字段qq

• 删除表

  -- drop table 表名;
  drop table student;
  

三范式

• 范式

  为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。

在关系型数据库中这种规则就叫做范式

• 约束作用

  数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常

• 三范式

1. 第一范式：确保每列保持原子性
2. 第二范式：确保表中的每列都和主键相关
3. 第三范式：确保每列都和主键列直接相关，而不是间接相关

第一范式（1NF）确保每列保持原子性

1. 每一列属性都是不可再分的属性值，确保每一列的原子性
2. 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据

真实生活中，地址是省/市/区/街道，所以地址可以再分为省、市、区、街道，上面的图违反了第一范式规则

第二范式（2NF）属性完全依赖于主键

1. 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的。即满足第二范式必须先满足第一范式
2. 第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键

第三范式（3NF）属性不依赖于其它非主属性 属性直接依赖于主键

1. 数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。

比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

这样一个表结构，就存在上述关系。 学号–> 所在院校 --> (院校地址，院校电话)

这样的表结构，我们应该拆开来，如下。

（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）

总结

1. 第一范式：拆字段
2. 第二范式：拆表
3. 三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。

如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库

表约束

• 约束：约束实际上就是表中数据的限制条件

• 约束作用：表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效

• 约束的种类：

  1. 非空约束(not null)
  2. 唯一性约束(unique)
  3. 主键约束(primary key) PK
  4. 外键约束(foreign key) FK
  5. 检查约束(目前MySQL不支持、Oracle支持)

非空约束

用not null约束的字段不能为null值，必须给定具体的数据

-- not null 非空约束
create table tb1(
  name varchar(10) not null,
  age int 
)

唯一约束

unique约束的字段，具有唯一性，不可重复，但可以为null

• 行级约束

-- 唯一约束
create table tb2(
  name varchar(10) unique, -- 行级约束
	age int
)

• 表级约束

1. 在建表的时候，单独的用unique(字段) 来设置字段的约束

   create table tb3(
   	name varchar(10),
   	age int,
   	sex char(1),
   	unique(NAME)  -- 表级约束
   )
   

   名字重复报错

2. 使用表级约束，给多个字段联合约束

   create table tb4(
   	name varchar(10),
   	age int,
   	sex char(1),
   	unique(name,age,sex)  -- 表级约束
   )
   

   联合约束后；name,age,sex三个重复才会报错

3. 表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束)

   create table tb5(
   	name varchar(10),
   	age int,
   	sex char(1),
   	constraint unique_name unique(name),
       constraint unique_age_sex unique(age,sex)
   )
   

主键约束(primary key)PK

• 主键(primary key)是表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录

• 表中的某个字段添加主键约束后，该字段为主键字段，主键字段中出现的每一个数据都称为主键值

• 主键约束与“not null unique”区别（重点）

  1. 作为Primary Key的域/域组不能为null，而Unique Key可以
  2. 在一个表中只能有一个Primary Key，而多个Unique Key可以同时存在。unique not null 可以将表的一列或多列定义为唯一性属性，而primary key设为多列时，仅能保证多列之和是唯一的，具体到某一列可能会重复
  3. 更大的区别在逻辑设计上。Primary Key一般在逻辑设计中用作记录标识，这也是设置Primary Key的本来用意，而Unique Key只是为了保证域/域组的唯一性

• 一张表应该有主键字段，如果没有，表示该表无效

• 主键值：是当前行数据的唯一标识、是当前行数据的身份证号

  即使表中两行记录相关数据相同，但由于主键值不同，所以也认为是两行不同的记录

• 按主键约束的字段数量分类

  单一主键：给一个字段添加主键约束

  create table tb6(
  	id char(18) primary key,
  	name varchar(10),
  	age int
  )
  

  复合主键：给多个字段联合添加一个主键约束(只能用表级定义)

  create table tb7(
  	id char(18),
  	name varchar(10),
  	age int,
      primary key(id,name,age)   -- 表级定义
  )
  

  

• 在MySQL数据库提供了一个自增的数字，专门用来自动生成主键值，主键值不用用户维护，自动生成，自增数从1开始，以1递增(auto_increment)

  id int primary key auto_increment
  

外键约束(foreign key)FK

• 外键：外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键

  A为基本表或父标，主表，B为信息表，子表，副表

• 只能是表级定义

  foreign key(表的字段名) references 父表表名(父表的字段名)

• 某个字段添加外键约束之后，该字段称为外键字段，外键字段中每个数据都是外键值

• 按外键约束的字段数量分类

  单一外键：给一个字段添加外键约束

  复合外键：给多个字段联合添加一个外键约束

  注意：

  1. 一张表可以有多个外键字段（与主键不同）
  2. 外键值可以为null
  3. 外键字段去引用一张表的某个字段的时候，被引用的字段必须具有unique约束
  4. 有了外键引用之后，表分为父表和子表

  ​ 班级表：父表

  ​ 学生表：子表

  ​ 创建先创建父表

  ​ 删除先删除子表数据

  ​ 插入先插入父表数据

约束的添加

• 添加非空约束

  alter table 表名 modify test_student char(10) not null; 删除非空约束

• 添加唯一约束

  alter table 表名 add unique(表字段名，字段，字段，字段);

• 添加主键约束

  alter table 表名 add primary key(表的字段名，字段，字段);

• 添加外键约束

  alter table 表名 add constraint N1 foreign key (表字段名) references 父表(父表字段名);

约束的删除

• 删除非空约束

  alter table 表名 modify 列名 类型;

• 删除unique约束

  alter table 表名 drop index 唯一约束名;

• 删除primary key约束

  alter table 表名 drop primary key;

• 删除foreign key约束

  alter table 表名 drop foreign key 外键名;