MySQL的DDL操作表

一、DDL操作表

1.1 创建表

1.1.1 语法

create table 表名(
	列名  类型 [约束],
	列名  类型 [约束]
	...
);
1
2
3
4
5

1.1.2 类型

1.1.2.1 数值类型

• 整型系列：xxxInt

int(M)，必须和unsigned zerofill一起使用才有意义。

• 浮点型系列：float，double

double(M,D)：表示最长为M位，其中小数点后D位。

• 定点型系列：decimal（底层使用字符串进行存储）

decimal(M,D)：表示最长为M位，其中小数点后D位

• 位类型：bit
  字节范围是：1~8，值范围是：bit(1)~bit(64)，默认bit(1)。

用来存储二进制数。对于位字段，直接使用select命令将不会看到结果。可以使用bit()或hex()函数进行读取。插入bit类型字段时，使用bit()函数转为二进制值再插入，因为二进制码是"01"。

1.1.2.2 日期时间类型

日期时间类型：year，date，datetime，timestamp

• 日期时间的表示范围如下表：

timestamp和datetime区别：

• timestamp范围比较小
• timestamp和时区有关
• timestamp受MySQL版本和服务器的SQLMode影响很大
• 表中的第一个非空的timestamp字段如果插入和更新为NULL则会自动设置为系统时间。

1.1.2.3 字符串类型

MySQL中提供了多种对字符数据的存储类型，不同的版本可能有所差异。常见的有：
char，varchar，xxtext，binary，varbinary，xxblob，enum，set等等

• 字符串类型char，varchar(M)
  char如果没有指定宽度，默认为1个字符。
  varchar(M)，必须指定宽度。

• binary和varbinary类似于char和varchar，不同的是它们包含二进制字符串，不支持模糊查询之类的。

• 一般在保存少量字符串的时候，我们会选择char和varchar；而在保存较大文本时，通常会选择使用text或blob系列。blob和text值会引起一些性能问题，特别是执行了大量的删除操作时，会在数据表中留下很大的“空洞”，为了提高性能，建议定期用optimize table功能对这类表进行碎片整理。可以使用Synthetic索引来提高大文本字段的查询性能，如果需要对大文本字段进行模糊查询，MySQL提供了前缀索引。

• enum枚举类型，它的取值范围需要在创建表时通过枚举方式显式指定，对于1~255个成员的枚举需要1个字节存储；对于255~65535个成员需要2个字节存储。

• set集合类型，可以包含0~64个成员。一次可以从集合中选择多个成员。

1.1.3 约束

• 就是规则，规矩，限制；
• 作用是保证用户插入的数据保存到数据库中是符合规范的。

约束种类：

• not null：非空。
• unique：唯一约束，后面的数据不能和前面重复。
• primary key：主键约束（非空+唯一）；一般用在表的id列上面。一张表基本上都有id列，id列作为唯一标识。

注意：

1. 先设置了primary key，再能设置auto_increment
2. 只有当设置了auto_increment才可以插入null，否则插入null会报错。

1.1.4 练习

-- 1.创建表
-- 创建一张学生表（含有id字段，姓名字段不能重复，性别字段不能为空。id为主键自动增长）
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长
	NAME VARCHAR(30) UNIQUE, -- 唯一约束
	gender CHAR(1) NOT NULL
);
1
2
3
4
5
6
7

1.2 查看表

1.2.1 查看所有的表

-- 2.查看某个数据库中的所有表
SHOW TABLES;
1
2

1.2.2 查看表的定义结构

• 语法
  desc 表名;

-- 3.查看某张表的结构
DESC student;
1
2

1.3 修改表

-- 4.修改表结构的SQL语句
-- 4.1 添加一个字段
-- 给学生表增加一个grade字段，类型为varchar(20)，不能为空
ALTER TABLE student  ADD grade VARCHAR(20)  NOT NULL;

-- 4.2 修改某个字段的类型和约束
-- 给学生表的gender字段改成int类型
ALTER TABLE student MODIFY gender INT NOT NULL DEFAULT 1;

-- 4.3 修改某个字段的名字
-- 给学生的grade字段修改成class字段
ALTER TABLE student CHANGE grade class VARCHAR(20)  NOT NULL;

-- 4.4 删除某个字段
-- 把class字段删除
ALTER TABLE student DROP class;

-- 4.5 修改表的名字
-- 把学生表修改成老师表
RENAME TABLE student TO teacher;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

1.4 删除表

-- 5. 删除表
DROP TABLE teacher;
1
2

学海无涯苦作舟