MySQL的DDL操作表

一、DDL操作表

1.1 创建表

1.1.1 语法

create table 表名(
	列名  类型 [约束],
	列名  类型 [约束]
	...
);

1.1.2 类型

1.1.2.1 数值类型

• 整型系列：xxxInt

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

整数类型	字节	取值范围 (有符号、无符号)
TINYINT	1	-128~127、0~255
SMALLINT	2	-32768~32767、0~65535
MEDIUMINT	3	-8388608~8388607、0~1677215
INT、INTEGER	4	-2147483648~2147483647、0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807、0~18446744073709551615

• 浮点型系列：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

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

类型	说明	标准格式	范围
date	日期	YYYY-MM-DD	1000-01-01到9999-12-31
time	时间	HH:MM:SS	-838:59:59和838:59:59
datetime	日期时间	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00到9999-12-31 23:59:59
year	年份类型	YYYY 和 YY（不推荐）	1901到2155
timestamp	时间戳	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01到2038-01-19 03:14:07

timestamp和datetime区别：

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

1.1.2.3 字符串类型

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

字符串类型	描述
char(M)	M为0~255，允许0~255个字符，char默认char(1)
varchar(M)	M为0~65535，允许0~65535个字符，varchar必须指定（M），实际长度=值得长度+1个字节
binary(M)	M为0~255，允许0~255个定长字节字符串
varbinary(M)	M为0~65535，允许0~65535个变长字节字符串，实际长度=值得长度+1个字节
tinyblob	允许0~255字节，实际长度=值得长度+1个字节
blob	允许0~65535个字节，实际长度=值的长度+2个字节
mediumblob	允许0~167772150个字节，实际长度=值的长度+3个字节
longblob	允许0~4294967295个字节，实际长度=值的长度+4个字节
tinytext	允许0~255个字节，实际长度=指的长度+2个字节
text	允许0~65535个字节，实际长度=值的长度+2个字节
mediumtext	允许0~167772150个字节，实际长度=值的长度+3个字节
longtext	允许0~4294967295个字节，实际长度=值的长度+4个字节

• 字符串类型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 约束

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

约束	约束关键字	说明
主键	primary key	非空且唯一，并且一张表只能有一个主键
唯一	unique	唯一，当前列不能出现相同的数据
非空	not null	非空，当前列不能为null
默认	default	如果当前列没有数据，则指定默认数据

约束种类：

• 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 查看表

1.2.1 查看所有的表

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

1.2.2 查看表的定义结构

• 语法
  desc 表名;

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

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.4 删除表

-- 5. 删除表
DROP TABLE teacher;

学海无涯苦作舟