【MySQL】DDL数据定义语言的基本用法create、drop和alter（增删改）
DDL 的基础语法
简单复习一波 SQL必知必会
DDL 的英文全称是 Data Definition Language（数据定义语言），
它定义了数据库的结构和数据表的结构。
在 DDL 中，我们常用的功能是增删改，分别对应的命令是 CREATE、DROP 和 ALTER。
对数据库进行定义
建数据库的基本SQL语法格式为：
CREATE DATABASE database_name;//创建一个名为 database_name 的数据库
“database_name”为要创建的数据库的名称，该名称不能与已经存在的数据库重名。
mysql> CREATE DATABASE database_name;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE DATABASE database_name;
ERROR 1007 (HY000): Can't create database 'database_name'; database exists
删除数据库是将已经存在的数据库从磁盘空间上清除，清除之后，数据库中的所有数据也将一同被删除。
删除数据库的基本SQL语法格式为：
DROP DATABASE database_name;//删除一个名为 database_name 的数据库
“database_name”为要删除的数据库的名称。若指定的数据库不存在，则删除出错。
mysql> DROP DATABASE database_name;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP DATABASE database_name;
ERROR 1008 (HY000): Can't drop database 'database_name'; database doesn't exist
mysql> CREATE DATABASE database_name;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE DATABASE database_name;
ERROR 1007 (HY000): Can't create database 'database_name'; database exists
mysql> SHOW CREATE DATABASE database_name;
+---------------+------------------------------------------------------------------------+
| Database | Create Database |
+---------------+------------------------------------------------------------------------+
| database_name | CREATE DATABASE `database_name` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+---------------+------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> DROP DATABASE database_name;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE DATABASE database_name;
ERROR 1049 (42000): Unknown database 'database_name'
CREATE DATABASE mysqlcrashcourse;// 创建一个名为 mysqlcrashcourse 的数据库
DROP DATABASE mysqlcrashcourse;// 删除一个名为 mysqlcrashcourse 的数据库
对数据表进行定义
创建表结构的语法是这样的：
CREATE TABLE table_name;创建一个名为 table_name的表
一般要写至少写一行 A table must have at least 1 column，后面介绍如何创建表结构
创建一个名为 table_name的表
mysql> CREATE DATABASE database_name;
Query OK, 1 row affected (0.00 sec)
mysql> USE database_name;
Database changed
mysql> CREATE TABLE table_name;
ERROR 1113 (42000): A table must have at least 1 column
mysql> CREATE TABLE table_name( name VARCHAR(50) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------------+
| Tables_in_database_name |
+-------------------------+
| table_name |
+-------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE table_name( name VARCHAR(50) NOT NULL);
ERROR 1050 (42S01): Table 'table_name' already exists
mysql>
删除表的基本SQL语法格式为：
DROP TABLE table_name;
DROP TABLE [IF EXISTS] table_name;
DROP TABLE table_name;删除一个名为 table_name的表
DROP TABLE IF EXISTS table_name; 执行了这条语句如果存在table_name表就删除，不存在不会报错也是执行。
mysql> show tables;
+-------------------------+
| Tables_in_database_name |
+-------------------------+
| table_name |
+-------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE table_name( name VARCHAR(50) NOT NULL);
ERROR 1050 (42S01): Table 'table_name' already exists
mysql> DROP TABLE table_name;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> DROP TABLE table_name;
ERROR 1051 (42S02): Unknown table 'database_name.table_name'
mysql> CREATE TABLE table_name;
ERROR 1113 (42000): A table must have at least 1 column
mysql> CREATE TABLE table_name( name varchar(50) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> drop table if exists table_name;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table if exists table_name;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop table table_name;
ERROR 1051 (42S02): Unknown table 'database_name.table_name'
mysql> drop table if exists table_name;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
创建表结构（数据表）
创建数据表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束的过程。
比如我们想创建一个用户表，表名为 user，里面有两个字段，一个是 user_id，它是 int 类型，另一个 user_name 字段是varchar(255)类型。这两个字段都不为空，且 user_id 是递增的。
那么创建的时候就可以写为：
CREATE TABLE user(
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(255) NOT NULL
);
使用CREATE TABLE创建表时，必须指定以下信息：
（1）要创建的表的名称，不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
（2）数据表中每一列（字段）的名称和数据类型，如果创建多列，就要用逗号隔开。
CREATE TABLE user(
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(255) NOT NULL,
primary key(user_id)
);
mysql> CREATE TABLE user(
-> user_id int(11) NOT NULL AUTO_INCREMENT,
-> user_name varchar(255) NOT NULL
-> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> CREATE TABLE user(
-> user_id int(11) NOT NULL AUTO_INCREMENT,
-> user_name varchar(255) NOT NULL,
-> primary key(user_id)
-> );
Query OK, 0 rows affected (0.01 sec)
需要注意的是，语句最后以分号（;）作为结束符，最后一个字段的定义结束后没有逗号。
数据类型中 int(11) 代表整数类型，显示长度为 11 位，括号中的参数 11 代表的是最大有效显示长度，与类型包含的数值范围大小无关。varchar(255)代表的是最大长度为 255 的可变字符串类型。NOT NULL表明整个字段不能是空值，是一种数据约束。AUTO_INCREMENT代表主键自动增长。
设计工具
我们可以使用一些可视化工具来创建和操作数据库和数据表。
在这里使用 Navicat，https://www.navicat.com.cn/
它是一个数据库管理和设计工具，跨平台，支持很多种数据库管理软件，比如 MySQL、Oracle、MariaDB 等。
假如想创建一个nba的数据库，在nba数据库中创建player表，我们设计以下的字段：
字段 含义 类型
player_id 球员ID int整型类型，最大显示长度11
team_id 球队ID int整数类型，最大显示长度为11
player_name 球员姓名 varchar字符串类型，最大长度255
height 身高 float浮点类型，一共存储3个有效数字，其中小数点长度为2
其中 player_id 是数据表 player 的主键，且自动增长，也就是 player_id 会从 1 开始，然后每次加 1。player_id、team_id、player_name 这三个字段均不为空，height 字段可以为空。
如何实现呢？
按照上面的设计需求，可以使用 Navicat 软件进行设计，如下所示：
然后，还可以对 player_name 字段进行索引，索引可以使查询速度快了点，索引类型为Unique。使用 Navicat 设置如下：
btree
这样一张 player 表就通过可视化工具设计好了。我们可以把这张表导出来，可以看看这张表对应的 SQL 语句是怎样的。方法是在 Navicat 左侧用右键选中 player 这张表。
这样就可以看到导出的 SQL 文件了，代码如下：
/*
Navicat Premium Data Transfer
Source Server : 本地测试
Source Server Type : MySQL
Source Server Version : 50728
Source Host : localhost:3306
Source Schema : nba
Target Server Type : MySQL
Target Server Version : 50728
File Encoding : 65001
Date: 19/03/2020 22:01:28
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for player
-- ----------------------------
DROP TABLE IF EXISTS `player`;
CREATE TABLE `player` (
`player_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '球员id',
`player_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '球员名字',
`team_id` int(11) NOT NULL COMMENT '球队id',
`height` float(3, 2) NULL DEFAULT NULL COMMENT '身高',
PRIMARY KEY (`player_id`) USING BTREE,
UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
我们可以看到整个 SQL 文件中的 DDL 处理，首先先删除 player 表（如果数据库中存在该表的话），然后再创建 player 表，里面的数据表和字段都使用了反引号``，
这是为了避免它们的名称与 MySQL 保留字段相同，对数据表和字段名称都加上了反引号。
其中 player_name 字段的字符集是 utf8mb4，排序规则是utf8_general_ci，代表对大小写不敏感，如果设置为utf8_bin，代表对大小写敏感，还有许多其他排序规则这里不进行介绍。
因为 player_id 设置为了主键，因此在 DDL 中使用PRIMARY KEY进行规定。
同时索引方法采用 BTREE，因为我们对 player_name 字段进行索引，在设置字段索引时，我们可以设置为UNIQUE INDEX（唯一索引），也可以设置为其他索引方式，比如NORMAL INDEX（普通索引），这里我们采用UNIQUE INDEX。唯一索引和普通索引的区别在于它对字段进行了唯一性的约束。在索引方式上，你可以选择BTREE或者HASH，这里采用了BTREE方法进行索引。
整个数据表的存储规则采用 InnoDB，它是 MySQL5.5 版本之后默认的存储引擎。同时，我们将字符集设置为 utf8mb4，排序规则为utf8_general_ci，行格式为Dynamic，就可以定义数据表的最后约定了：
ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic
能看出可视化工具还是非常方便的，它能直接帮我们将数据库的结构定义转化成 SQL 语言，方便数据库和数据表结构的导出和导入。不过在使用可视化工具前，我们首先需要了解 DDL 的基础语法，至少能清晰地看出来不同字段的定义规则、索引方法，以及主键和外键的定义。
修改表结构
在创建表结构之后，我们还可以对表结构进行修改，那如何使用 DDL 命令来完成表结构的修改。
\1. 添加字段，比如在数据表中添加一个 age 字段，类型为int(11)
ALTER TABLE player ADD (age int(11));
\2. 修改字段名，将 age 字段改成player_age
ALTER TABLE player RENAME COLUMN age to player_age
MySQL：rename column 没有用。。。
下面的可以
ALTER TABLE player change age player_age int(11);
ALTER TABLE player change COLUMN age player_age int(11);
\3. 修改字段的数据类型，将player_age的数据类型设置为float(3,1)
ALTER TABLE player MODIFY (player_age float(3,1));
modify
MySQL：ALTER TABLE player MODIFY player_age float(3,1);
\4. 删除字段, 删除刚才添加的player_age字段
ALTER TABLE player DROP COLUMN player_age;
drop
mysql> use nba;
Database changed
mysql> show tables;
+---------------+
| Tables_in_nba |
+---------------+
| player |
+---------------+
1 row in set (0.00 sec)
mysql> show player;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'player' at line 1
mysql> desc player;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| player_id | int(11) | NO | PRI | NULL | auto_increment |
| player_name | varchar(255) | NO | UNI | NULL | |
| team_id | int(11) | NO | | NULL | |
| height | float(3,2) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table player add(age int(11));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc player;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| player_id | int(11) | NO | PRI | NULL | auto_increment |
| player_name | varchar(255) | NO | UNI | NULL | |
| team_id | int(11) | NO | | NULL | |
| height | float(3,2) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> ALTER ATBLE player RENAME COLUMN age to player_name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ATBLE player RENAME COLUMN age to player_name' at line 1
mysql> ALTER TABLE player RENAME COLUMN age to player_age
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMN age to player_age' at line 1
mysql> use player;
ERROR 1049 (42000): Unknown database 'player'
mysql> ALTER TABLE player change COLUMN age to player_age;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to player_age' at line 1
mysql> ALTER TABLE player change COLUMN age player_age;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> ALTER TABLE player change COLUMN age player_age int(11);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE player MODIFY (player_age float(3,1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(player_age float(3,1))' at line 1
mysql> ALTER TABLE player MODIFY player_age float(3,1);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE player DROP COLUMN player_age;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
小结
稍微系统的复习了下DDL的基础语法，了解下对数据库和数据表进行定义。
DDL
基础语法
DDL Data Definition Language 数据定义语言 定义了数据库的结构和数据表的结构
对数据库进行定义:
create database,drop database
对数据表进行定义
创建: create table [table_name] ( 字段名 数据类型,…)
修改: alter table …
可视化管理和设计工具：Navicat
参考资料
1、MySQL 8从入门到精通：视频教学版／王英英著．—北京：清华大学出版社，2019
2、MySQL必知必会 - [英]Ben Forta
3、SQL必知必会，从入门到数据实战 - 陈旸 这篇笔记主要就是记录下极客时间的SQL必知必会
