mysql之DDL(Data Definition Language)

数据库操作

创建数据库

create databse [if not exists] 库名;

删除数据库

drop database [if exists] 库名;

更改数据库字符集

alter database books character set 字符集;

常见的字符集如下：

• utf8: 国际通用编码。
• gbk和gb2312: 简体中文的编码。

表操作

创建表

use 数据库名称; #切换到指定数据库。
create table 表名(
	列名 列类型 [约束],
	列名 列类型 [约束],
	列名 列类型 [约束],
	...
	列名 列类型 [约束]
);

数据类型

整型

如何设置有符号整型还是无符号整型？

create table 表名(
	col1 int, # 创建一个有符号的整型
	col2 unsigned int # 创建一个无符号的整型
);

浮点型

• 浮点型

mysql数据类型	字节	含义
float(m,d)	4字节	单精度浮点型，8位精度，m是十进制数字的总个数，d是小数点后面的数字个数
double(m,d)	8字节	双精度浮点型，16位精度

 float(7,4)的可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。参数m存储数字的总长度，d却不同，影响到精度，小数点后的位数。

• 定点型

 数值范围依赖于M和D的值。
decimal(m,d)　　m必须大于等于d　　
 浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。参数m是定点类型数字的最大个数（精度），范围为0-65。小数点右侧数字的个数，范围为0~30，但不得超过m。 对定点数的计算能精确到65位数字。

字符型

• 较短文本

字符串类型	最多字符数	含义
char(M)	M	固定长度字符串
varchar(M)	M	变长字符串

• 长文本

类型	最多字符数
text	64K
mediumtext	16M
longtext	4G

• 二进制数据（音频，图片等）

类型	最多字符数
blob	64K
mediumblob	16M
longblob	4G

• 枚举

枚举在插入时做了限制。

create table 表名(
	col enum('a','b','c'), # 插入时只能从'a','b','c'中选一个插入
);

• 集合

create table 表名(
	col set('a','b','c'), # 插入时只能从'a','b','c'中选多个插入
);

时间类型

约束

含义

表中限制条件，确保数据的可靠性，完整性。

约束类型

• 非空约束(not null)
• 唯一性约束(unique)
• 主键约束(primary key) PK
• 外键约束(foreign key) FK
• 检查约束(目前MySQL不支持、Oracle支持)
• 默认(default)

创建约束

• 列级约束

create table stuinfo(
	id int primary key, #主键
	name varchar(20) not null, #非空
	gender char(1) check(gender='男' or gender='女'), #检查
	seat int unique, #唯一
	age int default 18, #默认
	majorid int references major(id) #外键
);

• 表级约束

create table stuinfo(
	id int, 
	name varchar(20) not null, #非空
	gender char(1),
	seat int,
	age int default 18, #默认
	majorid int,
	constraint pk primary key(id), #主键
	constraint uq unique(seat), #唯一
	constraint ck check(gender='男' or gender='女'), #主检查
	constraint fk foreign key(majorid) references major(id) #外键
);

• 主键与唯一的区别

1. 同一张表只能有一个主键，但能有多个唯一约束；
2. 主键字段值不能为NULL，唯一约束字段值可以为NULL；
3. 主键字段可以做为其他表的外键，唯一约束字段不可以做为其他表的外键；
4. SQLServer默认为主键字段创建聚集索引，为唯一约束字段创建非聚集索引；

• 外键

1. 要求在从表设置外键关系。
2. 从表外键类型需要与主表一致。
3. 主表关联列必须是key。
4. 插入数据时要先插入主表再插入从表，删除数据时要先删从表再删主表。
5. 从表指向主表。

修改约束

alter table 表名 modify column 列名 列类型 新约束;
alter table 表名 add 约束(列名);

例子

alter table stuinfo modify column id int primary key;
alter table stuinfo add primary key(id);

删除约束

删除非空约束

#删除非空约束
alter table stuinfo modify column name varchar(20) null;
#删除默认约束
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 fk;

修改表

• 修改列名

alter table book change column 旧列名 新列名 新列名类型

• 修改类型约束

alter table book modify column 列名 类型 [约束]

• 添加列

alter table book add column 列名 类型 [约束]

• 删除列

alter table book drop column 列名

• 修改表名

alter table book rename to 新列名

删除表

drop table 表名

复制表

• 仅仅复制表结构

create table copy like 表名

• 复制结构+数据

create table 表名 select * from 原表