【MySQL】MySQL-07-DDL语言

1. DDL语言（库和表的管理）

(1) 简介

库的管理

• 创建
• 修改
• 删除

表的管理

• 创建
• 修改
• 删除

关键字

• 创建create
• 修改alter
• 删除drop

(2) 库的管理

语法

create database 库名;
create database if not exists 库名;# 如果不存在就建库


# 修改库名
rename database oldname to newname;
# 修改字符集
alter database oldname character set gbk;

# 删除数据库
drop database oldname;
drop database if exists oldname;# 存在才删除

(3) 表的管理

语法

# 创建表
create  table 表名(
	列名 列类型 [(长度) 约束],
    列名 列类型 [(长度) 约束],
    列名 列类型 [(长度) 约束],
    列名 列类型 [(长度) 约束],
    ...
);

# 修改列名
alert table 表名 change column 原来的列名 新列名 新列类型;
# 修改类型或者约束
alert table 表名 modify column 列名 新列类型或者约束;
# 添加新列
alter table 表名 add column 列名 列类型与约束;
# 删除列
alter table 表名 drop column 列名;
# 修改表名
alter table 表名 rename to 新表名;

# 表的删除
drop table 表名;
drop table if exists 表名;

# 1.表的复制
# 仅仅复制新表的结构
create table 新表名 like 旧表名;
# 2.带数据复制
create table 新表名 
select *  			# * 可以换成很多的列名
from 旧表名;
# 3.仅仅复制表的部分结构
create table 新表名 
select *  			# * 可以换成很多的列名
from 旧表名
where 1 = 2 ;

2. 数据类型

(0) 常见类型

数值型

• 整形(以下均分带符号和无符号)

  • tinyint 1
  • smallint 2
  • mediumint 3
  • int integer 4
  • bigint 8

  默认为有符号
  超出范围用范围边界值（高版本8.0报错）
  
  # 如何设置无符号
  create table test(
  	t1 int(7) unsigned
  );
  解释：int标记了数据范围，括号中的7是显示数据的长度，不够补0，需要显示出补0的话需要后面尾随一个zerofill零填充，这时，数据将自动变成无符号。
  

• 小数

  • 定点数
    • dec(m,d) m+2字节 最大取值与double相同
    • decimal(m,d) 同上
  • 浮点数
    • float(m,d) 4
    • double(m,d) 8

  m:小数点前+小数点后所有的位数
  d:小数点后的位数
  
  省略MD：
  	定点数：默认m=10 d=0
  	浮点数：随着数值的插入而变化
  	
  	
  区别：定点数、浮点数
  	定点数精度高
  

字符型

• 短文本
  • char(m) m<256 m为最多的字符数，一个汉字也算一个字符
  • varchar(m) m<65536
• 长文本 text blob(二进制)

char 不可变长度
varchar 可变长度

日期型

• date 4 1000-01-01 9999-12-31
• datetime 8 1000-01-01 00:00:00 9999-12-31 23: 59:59
• timestamp 4 19700101080001 2038年的某个时刻
• time 3 -838:59:59 838:59:59
• year 1 1901 2155

其他类型

• bit(m) m:1-8 位类型
• binary和varbinary 二进制字符串
• enum(value1,value2,value3) 随着插入的数据变化字节大小
  • 如果列表成员为1~255，则需要1个字节存储
  • 如果列表成员为255~65535，则需要2个字节存储
• enum(value1,value2,value3)
  • SET类型一次可以选取多个成员，而Enum只能选一个
  • 1~8成员 1字节
  • 9~16成员 2字节

3. 约束

(0) 格式

create table 表名(
	字段名 字段类型 列级约束,
    字段名 字段类型 列级约束,
    表级约束
);

添加约束的时机：
	创建表的时候
	修改表的时候

约束的添加分类：
	列级约束
		六大约束语法上都支持，但是外键约束没有效果
	表级约束
		除了非空和默认约束，其他的都支持

外键写到表约束，其他写到列约束
    
    
主键 外键 唯一会生成索引
通过show index from 表名 可以查看索引

多个约束用空格隔开即可

(1) 分类

not null 非空约束

字段不能为空

default 默认约束

字段可设置默认值

primary key 主键约束

非空+唯一

unique 唯一约束

唯一

foreign key 外键

限制两个表的关系，保证该字段的值来自其主表的关联列的值

check 检查约束

MySQL不支持

(2) 列级约束的添加

create table stuinfo(
	id int primary key,
    name varchar(20) not null,
    gender char(1) check(gender='nan' or gender='nv'),
    seat int unique,
    age int default 18,
    # mid int references major(id) 不支持
	
);
create table major(
	id int primary key,
    name varchar(20)
);

(3) 表级约束的添加

create table stuinfo(
	id int,
    name varchar(20),
    gender char(1),
    seat int,
    age int,
    mid int,
    # constraint 约束名  可以不写，默认为字段名，主键为primary
    constraint pk primary key(id), # 约束名 pk(mysql中改不了，实际上还是primary) 主键为id
    constraint uq unique(seat),
    constraint f_stuinfo_major foreign key(mid) references major(id)
);
create table major(
	id int primary key,
    name varchar(20)
);

(4) 其他注意点

主键与唯一的对比

• 主键必须非空，唯一可以为空
• 主键与唯一都仅有一条
• 主键与非空允许组合 primary key(a,b) a和b构成一个组合主键（形成两个索引）

外键特点

• 从表设置外键
• 列类型一直，但是名称随便
• 主表中的关联列一定是一个key（主键或者唯一）
• 插入数据的时候先插主表

(5)修改表时管理约束

添加约束

alter table stuinfo modify column stuname varchar(20) not null;

alter table stuinfo modify column age     int default 18;

alter table stuinfo modify column id      int primary key;
alter table stuinfo add primary key(id); # 表约束写法


alter table stuinfo modify column seat      int unique;
alter table stuinfo add unique(seat);	 # 表约束写法

外键
alter table stuinfo add constraint f_stuinfo_major foreign key(mid) references major(id);

删除约束

alter table stuinfo modify column stuname varchar(20);

alter table stuinfo modify column age     int;

alter table stuinfo drop primary key;

alter table stuinfo drop index seat;

alter table stuinfo drop foreign key f_stuinfo_major;# 外键的约束名

(6) 标识列（自增长）

• 在primary key后添加自增长关键字auto_increment
• 插入时指定位置填入null。
• 修改起始值：第一个添加的行自增长指定好value
• 修改偏移量：set auto_increment_increment=3
• 自增长必须是key（唯一或者主键）
• 一个表只能有一个
• 类型只能是数值型
• 修改表的时候也可以设置标识列