MySQL数据库DDL常用语句

测试版本：MySQL-8.0.33

SQL 语言分类：

DDL：数据定义语言，CREATE、ALTER、DROP、RENAME、TRUNCATE等。

DML：数据操作语言，SELECT、INSERT、UPDATE、DELETE等。

DCL：数据控制语言，COMMIT、ROLLBACK、SAVEPOINT、GRANT、REVOKE等。

DDL：不支持回滚，8版本新特性：DDL操作具有原子性。

数据库操作

-- 查看数据库列表
SHOW DATABASES;

-- 查看当前数据库
SHOW DATABASE();
SELECT DATABASE() FROM DUAL;

-- 创建数据库
CREATE DATABASE db_test;
CREATE DATABASE IF NOT EXISTS db_test;
CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

-- 切换数据库
USE db_test;

-- 查看数据库创建信息
SHOW CREATE DATABASE db_test;

-- 修改数据库字符集
ALTER DATABASE db_test CHARACTER SET = gbk;

-- 删除数据库
DROP DATABASE db_test;

数据表操作

-- 查看表
SHOW TABLES;
SHOW TABLES FROM db_test;

-- 创建表
CREATE TABLE t_user_info (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(36) NOT NULL,
    gender VARCHAR(2) NOT NULL DEFAULT '3' COMMENT '性别',
    id_card VARCHAR(18) NOT NULL UNIQUE COMMENT '证件号码',
    phone VARCHAR(64) COMMENT '电话',
    birthdate DATETIME COMMENT '出生日期',
    other VARCHAR(512) COMMENT '备注',
    -- PRIMARY KEY(id)
    -- 直接在字段声明上增加 UNIQUE 则约束名称为字段名（对应唯一索引名称也是字段名）
    CONSTRAINT uk_user_info_phone UNIQUE(phone)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT '用户表';

-- 查看表定义
DESC t_user_info;

-- 重命名表
RENAME TABLE t_user_info TO user_info;
ALTER TABLE t_user_info RENAME TO user_info;

-- 删除表
DROP TABLE t_user_info;

-- 清空表
TRUNCATE TABLE user_info;

每个表只可以设置一个主键（可以是多个字段的联合主键），并且主键约束和索引的名字永久都为 “PRIMARY ”，使用 CONSTRAINT 声明不起作用。

每个表只可以设置一个自增长 AUTO_INCREMENT 列，并且只能用在单字段主键上。

字段操作

-- 增加字段
ALTER TABLE user_info ADD email VARCHAR(125) comment '邮箱';
ALTER TABLE user_info ADD email VARCHAR(125) COMMENT '邮箱' FIRST;
ALTER TABLE user_info ADD email VARCHAR(125) COMMENT '邮箱' AFTER phone;
-- 修改字段
ALTER TABLE user_info MODIFY birthdate DATE comment '出生日期';
-- 删除字段（会同时删除该字段相关约束和索引）
ALTER TABLE user_info DROP COLUMN other;
-- 重命名字段
ALTER TABLE user_info CHANGE birthdate birth_date DATE comment '出生日';

约束与索引操作

约束只能通过删除索引方式同时删除约束。

-- 查看相关表约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'user_info';
-- 查看表索引
SHOW INDEX FROM user_info;

-- 增加约束(在增加唯一约束和外键约束时会自动创建相应索引)
ALTER TABLE user_info ADD CONSTRAINT uk_user_info_email UNIQUE KEY(email);
ALTER TABLE user_info ADD CONSTRAINT fk_name FOREIGN KEY(id) REFERENCES other_table(pk);

-- 删除主键索引
--（有自增长 AUTO_INCREMENT 关键字时，不可直接删除）
ALTER TABLE user_info DROP PRIMARY KEY;
-- 删除外键约束
ALTER TABLE user_info DROP FOREIGN KEY fk_name;
-- 删除索引
ALTER TABLE user_info DROP INDEX uk_user_info_phone;
DROP INDEX uk_user_info_phone ON user_info;