DDL【数据定义语言】
MySQL命名规则
- 数据库名不得超过30个字符，变量名限制为29个
- 必须只能包含A-Z,a-z,0-9,_共63个字符
- 不能在对象名的字符间留空格
- 必须不能和用户定义的其他对象重名
- 必须保证你的字段没有和保留字、数据库系统或常用方法冲突
- 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。
1.库的管理
1.1创建库
create database [if not exists] 库名 [character set 字符集];1.2修改库
#更改字符集
alter database 库名 character set 字符集;1.3删除库
drop database [if exists] 库名;举例：
#1.库的创建
#案例： 创建库Books
create database if not exists books;#2.更改库的字符集
alter database books character set gbk;#3.库的删除
drop databaseif exists books;2.表的管理
2.1创建表
#切库
use 库名;#创建表
create table 表名(
列名 列的类型[(长度) 约束],
...
列名 列的类型[(长度) 约束]
);#查看表结构
desc 表名;2.2修改表
#可以设置新加字段所在表的位置
alter table 表名 add column 字段名 字段类型 [约束 first|after 字段名]
#删除字段
alter table 表名 drop column 字段名;
#修改字段
alter table 表名 modify column 字段名 新字段类型 新约束;
alter table 表名 change column 旧字段名 新字段名 字段类型 [约束];
#删除表名
alter table 表名 rename to 新表名;2.3删除表
drop table [if exists] 表名;2.4表的复制
#只复制表结构
create table 表名 like 被复制表名;
#复制表结构+数据
create table 表名 查询语句;举例：
#创建表Book
create table book(id int primary key auto_increment comment '编号',bName varchar(20) comment '图书名',price double comment '价格',authorId int comment '作者编号',publishDate datetime comment '出版日期'
);#表的修改#修改列名
alter table book change column publishDate pubDate datetime;#修改列的类型或约束
alter table book modify column pubdate TIMESTAMP;#添加新列并设置位置
alter table 表名 add column 字段名 类型 [first|after 字段名];
alter table book add column annual double;#删除列
alter table book drop column annual;#修改表名
alter table book rename to tbl_book;#表的复制
#1.仅仅复制表的结构
create table copy like tbl_book;#2.复制表的结构 + 数据
create table copy2 select * from tbl_book;#仅仅复制某些字段
create table copy3 select id, bName from tbl_book where 0;3.数据类型
3.1分类
数值型
1.整数型
2.浮点型：定点数、浮点数
字符型
1.较短的文本：char、varchar
2.较长的文本：text、blob-较长的二进制数据
日期型
3.2整型
| 类型 | 大小（字节） |
|---|---|
| tinyint | 1 |
| smallint | 2 |
| mediumint | 3 |
| int/integer | 4 |
| bigint | 8 |
特点
- 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
- 如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
- 如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，并不是范围，如果不够会用0在左边填充，但必须搭配zerofill使用，并且默认变为无符号整型
小数
| 类型 | 小数类 |
|---|---|
| float(M,D) | 浮点型 |
| double(M,D) | 浮点型 |
| dec(M,D) 或 decimal(M,D) | 顶点型 |
特点：
- M：整数部位+小数部位，D：小数部位，如果超出范围，MySQL5.7不执行
- M和D都可以省略，如果是decimal，则M默认为10，D默认为0，如果是float和double，则会根据插入的数值的精度来决定精度
- 定点型的精确度较高，如果要求插入数值的精度较高如货币运算的等则考虑使用
3.3字符型
| 类型 | 大小（字节） | 说明 |
|---|---|---|
| char | 1 | 固定长度的字符，比较耗费空间，效率高，最大的字符数，可以省略，默认为1 |
| varchar | 可变长度的字符，比较节省空间，效率较char低，最大的字符串，不可以省略 | |
| binary和varbinary | 用于保存较短的二进制 | |
| enum | 用于保存枚举，只能n选1 | |
| set | 用于保存集合，与enum的不同是可以多选 | |
| text | 较长的文本保存 | |
| blob | 较大的二进制 |
3.4日期型
| 类型 | 说明 |
|---|---|
| date | 只保存日期 |
| time | 只保存时间 |
| year | 只保存年 |
| datetime | 保存日期+时间，大小：8字节，范围：1000-9999，不受时区影响 |
| timestamp | 保存日期+时间，大小：4字节，范围：19970-2038，受时区影响 |
4.约束
一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
4.1六大约束
- not null: 非空，用于保证该字段的值不能为空。比如姓名、学号等
- default：默认，用于保证该字段有默认值。比如性别。
- primary key：主键，用于保证该字段值非空唯一。比如学号、员工编号等
- unique：唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号
- check：检查约束。比如年龄、性别
- foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号等。
添加约束的时机
- 创建表时
- 修改表时
- 标识列
4.2创建表时
create table 表名 (
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
);约束的添加分类
- 列级约束：直接在字段名和类型后面追加约束类型即可，只支持：默认、非空、主键、唯一，外键没效果。
- 表级约束：在个字段的最下面，[constraint 约束名] 约束类型（字段名），支持：主键、唯一、外键
主键和唯一的对比
- 主键：保证唯一性，不允许为空，至多有1个主键，允许组合但不推荐
- 唯一：保证唯一性，允许为空，可以有多个唯一，允许组合但不推荐
外键（重要）
- 要求在从表设置外键关系
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
- 主表的关联列必须时一个key（一般是主键或唯一）
- 插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表
级联删除：alter table语句添加外键结尾追加 on delete cascade, 则可以直接删除主表，从表相应行同时被删除。
级联制空：alter table语句添加外键结尾追加 on delete set null, 则可直接删除主表，从表相应外键同时被置null
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 reference major (id) #外键#查看stuinfo中的所有索引，包括主键、外键、唯一会生成索引
show index from 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 fk_major foreign key (majorid) reference major (id) #外键
);4.3修改表时
添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];
修改表时删除约束
alter table 表名 drop [constraint 约束名];create table stuinfo (
id int,
stuname varchar(20),
gender char(1),
seat int,
age int,
majorid int
);desc stuinfo;#1.添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;#2.添加默认约束
alter table stuinfo modify column age int default 18;#3.添加主键
#列级约束
alter table stuinfo modify column id int primary key;
#表级约束
alter table stuinfo add primary key (id);#4.添加唯一
#列级约束
alter table stuinfo modify column seat int unique;
#表级约束
alter table stuinfo add unique (seat);#添加外键
alter table stuinfo add constraint fk_major foreign key (majorid) references major (id);#删除约束
#1.删除非空约束
alter table stuinfo modify column stuname varchar(20) NULL;#2.删除默认约束
alter table stuinfo modify column age int;#3.删除主键
alter table stuinfo drop primary key;#4.删除唯一
alter table stuinfo drop index seat;#5.删除外键,这里fk_major是外键名
alter table stuinfo drop foreign key fk_major;#show index 是用来查看表索引信息
show index from stuinfo;4.4标识列
又称为自增长列：可以不用手动的插入值，系统提供默认的序列值
注意点
- 标识列不是必须和主键搭配，但要求是一个key
- 一个表至多一个标识列
- 标识列的类型只能是数值型
- 标识列可以通过手动插入值，设置起始值
#设置系统起始序号
set auto_increment_offset = 100;
#设置步长
set auto_increment_increment = 3;create table tab_identity(
id int primary key auto_increment,
name varchar(20) unique,
seat int
);#查看mysql系统变量
show variables like '%auto_increment%';修改表设置标识列
#修改
alter table tab_identity modify column id int primary key auto_increment;
#删除
alter table tab_identity modify column id int primary key;
