【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代表主键⾃动增长。
设计⼯具
我们可以使⽤⼀些可视化⼯具来创建和操作数据库和数据表。
假如想创建⼀个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 …
exists的用法可视化管理和设计⼯具：Navicat
参考资料
1、MySQL 8从⼊门到精通：视频教学版／王英英著．—北京：清华⼤学出版社，2019
2、MySQL必知必会 - [英]Ben Forta
3、SQL必知必会，从⼊门到数据实战 - 陈旸这篇笔记主要就是记录下极客时间的SQL必知必会
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论