一、简介
DDL（Data Definition Language）数据定义语言。
用来定义数据库对象：
数据库
、
表
、
列
等。关键词：
create
、
drop
、
alter
等
二、DDL操作数据库
1、创建数据库
创建语法：
create database [if not exists] 数据库名;
创建数据库并指定字符集（字符编码）：
create database 数据库名 character set 字符集;
创建一个数据库，如：
mysql> create database db1 character set utf8;
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
7 rows in set (0.00 sec)
2、查看某个数据库的定义信息（创建语句）：
语法：
show create database 数据库名;
mysql> show create database db1;
+----------+-------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database db1 \G
*************************** 1. row ***************************
Database: db1
Create Database: CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)
3、修改数据库
修改数据库字符集格式：
alter database 数据库名 default character set 字符集;
mysql> alter database db1 default character set gbk;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db1;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4、删除数据库
语法：
drop database 数据库名;
mysql> drop database db3;
Query OK, 0 rows affected (0.02 sec)
要删除的数据库不存在时：
mysql> drop database db3;
ERROR 1008 (HY000): Can't drop database 'db3'; database doesn't exist
mysql> drop database if exists db3;
Query OK, 0 rows affected, 1 warning (0.00 sec)
5、使用/切换数据库
查看正在使用的数据库：
select database();
切换/使用数据库：
use 数据库名;
mysql> use db1;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
三、DDL操作数据表
1、创建表
语法：
create table 表名 ([字段名 [属性]...,]...);
mysql> create table user (
-> id int,
-> name char
-> );
Query OK, 0 rows affected (0.04 sec)
创建一个结构相同的表：
create table 新表名 like 旧表名;
mysql> create table user1 like user;
Query OK, 0 rows affected (0.02 sec)
2、查看表
查看某个数据库中的所有表：
show tables;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| user |
| user1 |
+---------------+
2 rows in set (0.00 sec)
查看表结构：
desc 表名;
mysql> desc user;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
查看表的创建语句：
show create table 表名;
mysql> show create table user1 \G
*************************** 1. row ***************************
Table: user1
Create Table: CREATE TABLE `user1` (
`id` int DEFAULT NULL,
`name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
3、修改表
修改表名：
rename table 表名 to 新表名;
mysql> rename table user to user1;
Query OK, 0 rows affected (0.02 sec)
修改表的字符集：
alter table 表名 character set 字符集;
mysql> alter table user1 character set gbk;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table user1 \G
*************************** 1. row ***************************
Table: user1
Create Table: CREATE TABLE `user1` (
`id` int DEFAULT NULL,
`name` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
4、删除表
语法：
drop table [if exists] 表名;
mysql> drop table user1;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| user |
+---------------+
1 row in set (0.01 sec)
四、DDL操作数据列（字段）
1、添加表列
语法：
alter table 表名 add 列名 [属性]... [after 插入位置];
mysql> alter table user1 add age int;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc user1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2、修改表列
修改列类型：
alter table 表名 modify 列名 [新属性];
mysql> alter table user1 modify name varchar(8);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(8) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
修改列名：
alter table 表名 change 旧列名 新列名 [新属性];
mysql> alter table user1 change age phone varchar(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(8) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
3、删除列
语法：
alter table 表名 drop 列名;
mysql> alter table user1 drop phone;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
五、参考
8.0官方文档：
https://dev.mysql.com/doc/refman/8.0/en/
