mysql中的DDL

– 创建库
CREATE DATABASE test;
– 删除库
DROP DATABASE test;

USE test;
– 创建表
CREATE TABLE player(
pid int ,
pname VARCHAR(10)
);
– 删除表
DROP TABLE player;

– 修改列类型（前提是类型兼容，比如char类型转INT则CHAR中不能有ABC等）
ALTER TABLE player MODIFY pname VARCHAR(50);

– 修改列名及类型
ALTER TABLE player CHANGE pname panme2 VARCHAR(10);

– 删除列
ALTER TABLE player DROP panme2;

– 修改表名
ALTER TABLE player RENAME TO player2;

– 约束

– 直接添加主键约束
CREATE TABLE player(
pid int PRIMARY KEY,
pname VARCHAR(10)
);

– 修改表，添加主键，也可用CONSTRAINT 主键名，但主键就一个所以可以省略
ALTER TABLE player ADD PRIMARY KEY (pid);

– 删除主键
ALTER TABLE player DROP PRIMARY KEY;

– 自增长
DROP TABLE player;
– 添加自增长
CREATE TABLE player(
pid int PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10)
);
– 删除自增长并不会删除主键
ALTER TABLE player CHANGE pid pid INT;
– 添加自增
ALTER TABLE player CHANGE pid pid INT AUTO_INCREMENT;

– 非空约束
DROP TABLE player;
– 添加非空约束
CREATE TABLE player(
pid int PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10) NOT NULL
);
– 删除非空约束
ALTER TABLE player CHANGE pname pname VARCHAR(10);
– 添加非空约束
ALTER TABLE player CHANGE pname pname VARCHAR(10) NOT NULL;

– 默认值

– 添加默认值
DROP TABLE player;
– 添加默认值
CREATE TABLE player(
pid int PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10) DEFAULT “呵呵”
);

– 主键和唯一键都会对应一个索引
– 类似于的书的目录,提高查询速度（多了也不好需要维护等）
DROP TABLE player;
– 添加唯一键
CREATE TABLE player(
pid int PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10) UNIQUE
);
– 删除唯一键，默认键名为列名
ALTER TABLE player DROP INDEX pname;
– 添加唯一约束
ALTER TABLE player ADD CONSTRAINT uq_pname UNIQUE (pname);

– 检查约束
DROP TABLE player;
– 添加检查约束,不报错,mysql也并不支持
CREATE TABLE player(
pid int PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10),
page INT CHECK( page < 20 AND page>10)
);

– 外键约束(先将主表建好在加子表及其的外键)
DROP TABLE player;
– 添加外键约束
CREATE TABLE player(
pid int PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10),
page INT,
pid2 INT,
CONSTRAINT fk_player_pid2 FOREIGN KEY(pid2) REFERENCES player2(pid2)
);
– 删除外键
ALTER TABLE player DROP FOREIGN KEY fk_player_pid2;
– 添加外键
ALTER TABLE player ADD CONSTRAINT fk_player_pid2 FOREIGN KEY(pid2) REFERENCES player2(pid2);