MySQL DDL 数据定义语句SQL大全

一、库操作

连接数据库

mysql -h127.0.0.1 -uroot -p

1、创建数据库

create database test_db01;

2、查看数据库（只能查看当前账号有权限的库）

show databases;

3、选择数据库

use test_db01;

4、删除数据库

drop database test_db01;

二、表操作

1、创建表

1.1、基础创建

CREATE  [TEMPORARY]  TABLE  [IF NOT EXISTS] [database_name.] <table_name>
(<column_name>  <data_type>  [[not] null],…
)

• 注：TEMPORARY：指明创建临时表
  　　IF NOT EXISTS：如果要创建的表已经存在，则不显示错误消息也不会执行创建逻辑
  　　database_name：数据库名
  　　table_name：表名
  　　column_name：列名
  　　data_type：数据类型

例

CREATE TABLE IF NOT EXISTS `test_db01`.`test_table01`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `nick_name` varchar(255) NULL COMMENT '昵称',
  PRIMARY KEY (`id`)
);

1.2、根据旧表创建新表

CREATE TABLE test_table02 LIKE test_table01;

1.3、根据旧表创建新表，并且复制旧表数据到新表

• 方法一

# 第一步根据老表创建新表，这样创建会携带老表的索引信息，但是不会携带老表的自增序列
CREATE TABLE test_table02 LIKE test_table01;

# 将老表数据迁移到新表
INSERT INTO test_table02 SELECT * FROM test_table01 WHERE 1=1;
# 或者
INSERT INTO test_table02 (id, nick_name) SELECT id, nick_name FROM test_table01 WHERE 1=1;

• 方法二（做数据备份时推荐）

CREATE TABLE test_table02 SELECT * FROM test_table01 WHERE 1=1;

不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra(auto_increment)等属性。需要自己用"alter"添加，而且容易搞错，这种方式比较适合做数据备份。

2、查看创建表的语句

SHOW CREATE TABLE test_table01;

3、更改表名

ALTER TABLE test_table01 RENAME test_table02;
ALTER TABLE test_table02 RENAME test_table01;

4 、删除表

DROP TABLE test_table01;

三、表字段操作

1、查看表字段定义

DESC test_table01;

2、增加表字段

2.1、普通增加表字段

在表末尾添加一个年龄字段

• TINYINT (4) ：字段为TINYINT类型长度4，NOT NULL：不能为空，DEFAULT 0 ：默认值是0，COMMENT ‘年龄’ ：描述是年龄

ALTER TABLE test_table01 ADD COLUMN age TINYINT (4) NOT NULL DEFAULT 0 COMMENT '年龄';

2.2、指定添加表字段在某个字段后面

• TINYINT (4) ：字段为TINYINT类型长度4，NOT NULL：不能为空，DEFAULT 0 ：默认值是0，COMMENT ‘性别’ ：描述是性别，AFTER nick_name ：字段添加在nike_name字段后

ALTER TABLE test_table01 ADD COLUMN sex TINYINT (4) NOT NULL DEFAULT 0 COMMENT '性别' AFTER nick_name;

3、修改表字段

• 修改字段描述有两种方式分别是使用change和modify，前者可以修改列名称后者不能，change需要些两次列名称

• PS：要注意一个问题修改字段时必须携带上一个字段所有属性如：字段类型、是否能为空、默认值、描述等，如果没有携带则会被修改成默认值

3.1、修改列名（使用change）

修改列名时需要将之前列的信息都带上，比如我这里需要带字段类型、是否为空、默认值、描述。

ALTER TABLE test_table01 CHANGE age age_01 TINYINT (4) NOT NULL DEFAULT 0 COMMENT '年龄01';

3.2、修改表字段类型、是否能为空、默认值、描述（使用modify）

• INT (4) ：将字段类型修改成int 长度为4，NULL ：可以为空，DEFAULT 1 ：默认值为1，COMMENT ‘性别01’ ：描述信息为性别01

ALTER TABLE test_table01 MODIFY sex INT (4) NULL DEFAULT 1 COMMENT '性别01';

3.3、修改表主键字段设置为自动增长并且设置起始值

# 设置主键为自动增长
ALTER TABLE `test_table01` MODIFY COLUMN `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID';
# 设置增长起始值为100
ALTER TABLE `test_table01` AUTO_INCREMENT = 100;

4、删除表字段

ALTER TABLE test_table01 DROP COLUMN age;

四、索引操作

4.1、查询表有那些索引

SHOW INDEX FROM `table_name`;

4.2、新增索引

• 添加PRIMARY KEY（主键索引）

  ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);
  

• 添加UNIQUE（唯一索引），使用BTREE结构

  ALTER TABLE `table_name` ADD UNIQUE INDEX `index_name`(`column`) USING BTREE;
  

• 添加NORMAL（普通索引），使用BTREE结构

  ALTER TABLE `table_name` ADD INDEX `index_name`(`column`) USING BTREE;
  

• 添加FULLTEXT（全文索引），使用BTREE结构

  ALTER TABLE `table_name` ADD FULLTEXT INDEX `index_name`(`column`) USING BTREE;
  

• 添加多列复合索引，使用BTREE结构

  ALTER TABLE `table_name` ADD INDEX `index_name`(`column1`, `column2`, `column3`) USING BTREE;
  

4.3、删除索引

• 删除主键索引 （主键索引不能是自动递增）

  ALTER TABLE `table_name` DROP PRIMARY KEY;
  

• 删除其它索引

  ALTER TABLE `table_name` DROP INDEX `index_name`;
  

4.4、修改索引

在mysql中，索引无法直接修改，可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作.