MySQL数据库DDL命令

MySQL数据库DDL命令

前言

DDL：Data Definition Language，数据定义语言，用来维护存储数据的结构(数据库，表)，代表指令：create，drop，alter等，本文记录常用DDL命令。

1. 数据库操作

-- 创建数据库
CREATE DATABASE database_name;
-- 若不存在则创建数据，同时指定编码
CREATE DATABASE IF NOT EXISTS database_name DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
-- 查看数据库
SHOW DATABASES;
-- 选择数据库
USE database_name;
-- 删除数据库：
DROP DATABASE database_name;

2. 表操作

-- 创建表
CREATE TABLE table_name  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `account` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '账号',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `age` int(3) UNSIGNED NOT NULL COMMENT '年龄',
  `status` tinyint(1) NOT NULL COMMENT '状态',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_account`(`account`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- 复制表，create table as模式，查询可以跟where条件
CREATE TABLE new_table_name AS SELECT * FROM table_name;
-- 复制表，create table as模式，WHERE 1=2查不出来数据，表示复制表数据
CREATE TABLE new_table_name AS SELECT * FROM table_name WHERE 1=2;
-- 复制表，create table as模式，若表不存在则建表
CREATE TABLE IF NOT EXISTS new_table_name SELECT * FROM table_name WHERE 1=2;
-- 复制表，create table like模式
CREATE TABLE new_table_name LIKE table_name;
-- 复制表，create table like模式，若表不存在则建表
 CREATE TABLE IF NOT EXISTS new_table_name LIKE table_name; 
-- 查询表结构
DESC table_name;
-- 查询表创建信息
SHOW CREATE TABLE table_name;
-- 修改表
-- 修改表备注
ALTER TABLE table_name COMMENT '备注描述';
-- 修改表名
RENAME TABLE table_name TO new_table_name;
-- 删除表
DROP TABLE table_name; 
-- 删除表，若果存在则删除表
DROP TABLE IF EXISTS table_name;

注：1、create table as 和 create table like区别

• create table as 只是复制原数据，其实就是把查询的结果建一个表

• create table like 产生与源表相同的表结构，包括索引和主键，数据需要用insert into 语句复制进去

2、drop table，delete table；，truncate table三种删除表的区别

• 1.速度上，drop>truncate>delete
• 2.truncate和delete不删除表的结构，drop删除删除表的结构和被依赖的约束，触发器，索引 ; 存储过程/函数将被保留，但是变为invalid状态
• 3.delete 会写日志,有备份可以回滚，truncate 不会写日志，不能回滚，因此慎用
• 4.delete删除表中数据不释放空间，truncate和drop删除数据、释放空间
• 5.truncate不触发任何delete触发器，deletre会触发 相应的触发器

3. 字段操作

-- 向表添加int字段
ALTER TABLE table_name ADD COLUMN age int(11)  DEFAULT NULL COMMENT '年龄';
-- 添加varchar类型，并指定编码
ALTER TABLE table_name ADD COLUMN nick_name varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '年龄';
-- 向表添加age字段，并添加到email字段后
ALTER TABLE table_name ADD COLUMN age int(11) DEFAULT NULL COMMENT '年龄'  AFTER email;
-- 修改一个字段
ALTER TABLE table_name MODIFY U1 VARCHAR(20) NULL COMMENT '字段描述'; 　
--  修改一个字段的名称
ALTER TABLE table_name CHANGE U1 U2 VARCHAR(32) NOT NULL  COMMENT '字段描述'; 　
-- 删除一个字段
ALTER TABLE table_name DROP U2; 
-- 新增一个自增主键并放在第一列
ALTER TABLE table_name ADD id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;
-- 取消自增
ALTER TABLE table_name MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT;

注：注意修改字段名称只能是用change，modify不能修改字段名称。

4. 索引操作

4.1 索引类型

• UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值

• INDEX(普通索引)：允许出现相同的索引内容

• PROMARY KEY(主键索引)：不允许出现相同的值

• fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维

• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

4.2 使用alter table创建索引

-- 向表添加int字段
ALTER TABLE table_name ADD COLUMN u1 int(11)  DEFAULT NULL COMMENT '备注';
-- 添加varchar类型，并指定编码
ALTER TABLE table_name ADD COLUMN u1 VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注';
-- 向表添加u1字段，并添加到u2字段后
ALTER TABLE table_name ADD COLUMN u1 int(11) DEFAULT NULL COMMENT '备注'  AFTER u2;
-- 修改一个字段
ALTER TABLE table_name MODIFY u1 VARCHAR(20) NULL COMMENT '字段描述'; 　
--  修改一个字段的名称
ALTER TABLE table_name CHANGE u1 u2 VARCHAR(20) NOT NULL  COMMENT '字段描述'; 　
-- 删除一个字段
ALTER TABLE table_name DROP u1; 
-- 添加主键索引
ALTER TABLE table_name add PRIMARY KEY(id);

4.3 使用CREATE INDEX语句对表增加索引

-- 添加UNIQUE(唯一索引) 
CREATE UNIQUE INDEX index_name ON table_name(column_name);
-- 添加INDEX(普通索引) 
CREATE INDEX index_name on table_name(column_name); 
-- 添加FULLTEXT(全文索引) 
CREATE FULLTEXT INDEX index_name ON table_name(column_name);
-- 添加多列索引 
CREATE INDEX index_name ON table_name(column1,column2);
-- 删除索引
DROP INDEX index_name ON table_name;
alter table products change id id int(11) not null COMMIT '主键';
alter table di_site_point drop primary key;

参考文档