【MySQL】DDL数据定义语言的基本用法create、drop和alter（增删改）

DDL 的基础语法

文章目录

• 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必知必会