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列作为唯一标识。
注意：
- 先设置了primary key，再能设置auto_increment
- 只有当设置了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;
