表的管理

表的定义

表结构：

• 表名

• 字段

  • 字段名称（列名）

  • 字段类型

  • 字段属性

  • 字段约束

• 表属性

  • 存储引擎

  • 字符集

  • 校对规则（排序、比对规则）

字段属性与约束：

约束	说明
PRIMARY KEY	主键约束，表中只能有一个，非空且唯一
UNIQUE KEY	唯一键约束，不允许重复值
NOT NULL	非空约束，不允许空值
DEFAULT	一般配合 NOT NULL 一起使用
UNSIGNED	无符号，一般配合数字列，非负数
COMMENT	注释
AUTO_INCREMENT	自增长的列

建表规范

1. 表名用小写字母，不能数字开头

2. 不能是保留字符，使用和业务有关的表名

3. 选择合适的数据类型及长度

4. 每个列设置 NOT NULL + DEFAULT , 对于数字用0填充，对于字符用有效字符串填充

5. 每个列设置注释

6. 表必须设置存储引擎和字符集

7. 主键列尽量是无关列数字列，最好是自增长

8. enum类型不要保存数字，只能是字符串类型

创建表

CREATE TABLE `zabbix`.`user`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `age` tinyint(3) UNSIGNED NOT NULL DEFAULT 99 COMMENT '年龄',
  `gender` enum('f','m','u') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'u' COMMENT '性别',
  `intime` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '入学时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

创建一个结构一样的表

CREATE table user3 LIKE  student;

创建一张一模一样的表（表的复制，非只有结构，还包含数据）

CREATE TABLE user4 AS SELECT * FROM user;

# 将查询结果当做表创建出来（表的复制）

查询建表信息

查看数据表

SHOW TABLES;

查看建表语句

SHOW CREATE TABLE user;

查看表结构

DESC user;

删除表

DROP TABLE user;

修改表

修改表名

ALTER TABLE old_table RENAME new_table;

RENAME TABLE old_table TO new_table;

RENAME TABLE old_table1 TO new_table1,
             old_table2 TO new_table2,
             old_table3 TO new_table3;

修改表属性

ALTER TABLE user CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

在user表中添加qq列

ALTER TABLE user ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';

在user表中是name列后面添加wechat列

ALTER TABLE user ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER name;

在user表中添加num列至第一列

ALTER TABLE user ADD num INT NOT NULL UNIQUE COMMENT '身份证' FIRST;

在表user中删除num列

ALTER TABLE user DROP num;

# 列只能一个一个删，不能同时删除多个列

修改user表中name列属性

ALTER TABLE user MODIFY name VARCHAR(100) NOT NULL COMMENT '姓名';

# 需将要修改name列的所有属性写完整

修改user表中gender列名及属性

ALTER TABLE user CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';

在线修改表会导致锁表，影响业务正常运行

可使用复制表的方式，或者使用 pt-osc 工具