Mysql之数据定义语言

DDL语言（数据定义语言）

---

如果再努力再认真一些 应该会好点的吧

一、概念

1. 简介

• DDL：数据定义语言
• 库和表的管理
• 库的管理：
  • 创建、删除、修改
• 表的管理：
  • 创建、删除、修改
• 创建：create
• 删除：drop
• 修改：alter

二、库的管理

1. 语法

• 1）创建库：
  • create database 库名
  • 添加判断：create database if not exists 库名
• 2）库的修改
  • rename database 库名 to 新库名：该语句舍弃了
• 3）修改库的字符集
  • alter database 库名 character set gbk
• 4）库的删除
  • drop database 库名
  • 添加判断：drop database if existe 库名

三、表的管理

1. 表的创建

① 语法

create table 表名(
	列名 列的类型【（长度） 约束】，
    列名 列的类型【（长度） 约束】，
    ...
)

2. 表的修改

① 总体语法

• alter table 表名 add|drop|modify|change column 列名 列类型 约束

② 修改列名

• alter table author change column publishDate pubDate datetime

③ 修改表名

• alter table author rename to book_author

④ 修改列的类型或约束

• alter table book modify column pubDate timestamp

⑤ 添加列

• alter table author add column annual double

⑥ 删除列

• alter table author drop column annual

3. 表的删除

• drop table if exists book

4. 表的复制

• 1）仅复制结构：create table copy1 like author
• 2）复制结构+数据：create table copy2 select * from author
• 3）只复制部分数据：create table copy3 select id,au_name from author where nation='China'
• 4）只复制部分结构：create table copy4 select id,au_name from author where 1=2

四、数据类型

1. 分类

• 1）数值型：
  • 整形
  • 小数：定点数和浮点数
• 2）字符型：
  • 较短的文本：char、varchar
  • 较长的文本：text、blob（较长的二进制数据）
• 3）日期型

2. 整型

① 分类

• Tinyint：1字节
• Smallint：2字节
• Mediumint：3字节
• Int、integer：4字节
• Bigint：8字节

② 特点

• 如果不设置无符号还是有符号，默认是有符号。如果设置无符号，需添加关键字unsigned
• （★）如果插入数值超出范围，报出异常，并且插入临界值
• 如果不设置长度，会有默认长度
  • 长度代表：显示的最大宽度，如果不够会用0填充，但必须搭配zerofill使用

3. 小数

① 分类

• 1）浮点型：
  • float（m，d）：4字节
  • double（m，d）：8字节
• 2）定点型
  • dec（m，d） / decimal（m，d）
  • （★）最大取值范围与double相同，取值范围与m和d有关
  • 精度比double高

② 特点

• （★）M和D代表什么：
  • M：整数位数+小数位数
  • D：小数位数
  • 如果m和d超出范围，则插入临界值
  • m和d可以省略，默认decimal（10，0）、float和double会根据插入数值的精度决定
• 定点型精度高，如果要求插入数值精度要求高，用定点型

4. 字符型

① 分类

• 1）较短的字符
  • char（m）：m 代表最多字符数
  • varchar（m）：m不可省略
  • binary：保存较短的二进制
  • varbinary：保存较短的二进制
• 2）较长的字符
  • text
  • TinyBlob：255
  • blob（较大的二进制）:65k
  • MediumBlob：16M
  • LongBlob：4G
• 3）enum（保存枚举）
  • 语法：sex enum('男','女')
• 4）set（保存集合）

② 特点

（★）char和varchar区别：

• char代表固定长度字符，varchar代表可变长度字符
• char比较耗费空间，varchar比较节省空间
• char效率高点
• char（m），m可以省略，默认为1。varchar（m），m不可省略

5. 日期型

① 分类

• date：4字节，只保存日期
• datetime：8字节，保存日期+时间
• timestamp：4字节，保存日期+时间
• time：3字节，只保存时间
• year：1字节，只保存年

② 特点

• 	字节	范围	时区等的影响
  datetime	8	1000-9999	不受
  timestamp	4	1970-2038	受

• datetime：只能反映处插入时的当时地区

• timestamp：和实际时区有关，更能反映实际的日期。使用较多

6. 原则

• 所选择的类型越简单越好，能保存的数值的类型越小越好

五、约束

1. 概念

• 约束：一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性

2. 分类

• 1）not null：该字段不能为空
• 2）default：默认，该字段有默认值
• 3）primary key：主键（必须非空），该字段具有唯一性
• 4）unique：唯一（可以为空），该字段具有唯一性
• 5）check：检查约束「mysql不支持」
• 6）foreign key：外键，限制连个表的关系。该字段的值必须来自于主表的关联列的值
  • references 主表(字段)

① 约束的添加分类还分为：

• 1）列级约束

  • 六大约束语法都支持，但外键约束没有效果

  • #1.添加列级约束
    create table stuinfo(
    	id int primary key, #主键
    	stuName varchar(20) not null, #非空
    	gender char(1) check(gender='男' or gender='女'), #检查
    	seat int unique, #唯一
    	age int default 18, #默认约束
    	majorId int references major(id) #外键
    );
    

• 2）表级约束

  • 除了非空、默认都支持

  • #2.添加表级约束
    drop table stuinfo;
    create table stuinfo(
    	id int,
    	stuname varchar(20),
    	gender char(1),
    	seat int,
    	age int,
    	majorid int,
    	
    	constraint pk primary key(id),#主键
    	constraint uq unique(seat),#唯一键
    	constraint ck check(gender='男' or gender='女'),
    	constraint fk_stuinfo_majorid foreign key(majorid) references major(id)#外键
    );
    

② 注意

• 1）mysql不支持check、foreign key约束
• 2）何时用列级约束何时用表级约束？
  • 当需要给约束起名时，用表级约束
  • 当需要使用外键约束时，用表级约束
  • 其余情况，用列级约束

③ 比较

• 1）主键和唯一键：

/*
			保证唯一性		是否允许为空	一个表中可以有多少个	是否允许组合
      主键	Y					N			至多一个		   Y
      唯一	Y					Y			多个				Y
      
      唯一约束允许为空，但是只能有一个值为null
*/

• 2）外键：
  • 要求在从表设置外键关系
  • 从表的外键列的类型和主表的关联列的类型必须一致或兼容，名称无要求
  • 主表的关联列必须是一个key（主键、唯一键）

六、标识列

1. 概念

• 标识列：又称为自增长列。可以不用手动的插入值，系统提供默认的序列值

2. 使用

① 创建表时设置标识列

create table tab_identity(
	id int primary key auto_increment,
	name varchar(20)
);

② 修改表时设置标识列

alter table tableName modify column id int primary key autu_increment

③ 修改表时删除标识列

alter table tableName modify column id int primary key

3. 注意

• 标识列不一定和主键搭配，但必须是一个key
• 一个表中只能有一个标识列
• 标识列必须为数值型
• 标识列可以通过：set autu_increment_increment = 3来设置步长