04 DDL

删表

drop table if exists player;

创表
create table TAB (
字段 类型(长度) 说明
)

CREATE TABLE `player`  (
  `player_id` int NOT NULL,
  `team_id` int NOT NULL,
  `player_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `height` float(3, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`player_id`) USING BTREE,
  UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

unique index 就是比normal index

修改表结构
alter table TAB add/rename/modify/drop column COL (rename to)…

alter table player add column age2 int(11);
alter table player rename column age to player_age;
alter table player modify column player_age float(3,1);
alter table player drop column age2;

常见约束
键的约束：
主键约束
外键约束
字段的约束：
唯一性约束 唯一性索引 = 普通索引 + 唯一性约束
not null
default
check

设计原则
1.减少表的个数
2.减少字段个数
3.减少联合主键的个数

4.合理使用主键