数据库定义语句

Data Definition Language[DDL]

本节内容中，我们将学习并实践如何对数据库表和表中的内容做修改，删除，重命名等操作。

SQL 概述

SQL 背景知识

1946 年，世界上第一台电脑诞生，如今，借由这台电脑发展起来的互联网已经自成江湖。在这几十年里，无数的技术、产业在这片江湖里沉浮，有的方兴未艾，有的已经几幕兴衰。但在这片浩荡的波动里，有一门技术从未消失，甚至“老当益壮”，那就是 SQL。
1974 年，IBM 研究员发布了一篇揭开数据库技术的论文《SEQUEL：一门结构化的英语查询语言》，直到今天这门结构化的查询语言并没有太大的变化，相比于其他语言，SQL 的半衰期可以说是非常长 了。
不论是前端工程师，还是后端算法工程师，都一定会和数据打交道，都需要了解如何又快又准确地提取自己想要的数据。更别提数据分析师了，他们的工作就是和数据打交道，整理不同的报告，以便指导业务决策。
SQL（Structured Query Language，结构化查询语言）是使用关系模型的数据库应用语言，与数据直接打交道，由IBM 上世纪70年代开发出来。后由美国国家标准局（ANSI）开始着手制定 SQL 标准，先后有SQL-86，SQL-89，SQL-92，SQL-99等标准。
不同的数据库生产厂商都支持 SQL 语句，但都有特有内容。

SQLisputonghua

SQL 语言排行榜

自从 SQL 加入了 TIOBE 编程语言排行榜，就一直保持在 Top 10。

SQL 分类

SQL 语言在功能上主要分为如下 3 大类：

DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
- 主要的语句关键字包括CREATE、DROP、ALTER等。
DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。
- 主要的语句关键字包括INSERT、DELETE、UPDATE、SELECT等。
- SELECT 是 SQL 语言的基础，最为重要。
DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
- 主要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。

提示

因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。

还有单独将 COMMIT、ROLLBACK 取出来称为 TCL （Transaction Control Language，事务控制语言）。

SQL 规则与规范

基本规则

SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
每条命令以 ; 或 \g 或 \G 结束
关键字不能被缩写也不能分行
关于标点符号
- 必须保证所有的()、单引号、双引号是成对结束的
- 必须使用英文状态下的半角输入方式
- 字符串型和日期时间类型的数据可以使用单引号（''）表示
- 列的别名，尽量使用双引号（""），而且不建议省略 as

SQL 大小写规范（建议遵守）

MySQL 在 Windows 环境下是大小写不敏感的
MySQL 在 Linux 环境下是大小写敏感的
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
推荐采用统一的书写规范：
- 数据库名、表名、表别名、字段名、字段别名等都小写
- SQL 关键字、函数名、绑定变量等都大写

注释

可以使用如下格式的注释结构

单行注释：# 注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

命名规则（暂时了解）

数据库、表名不得超过 30 个字符，变量名限制为 29 个
必须只能包含 A–Z, a–z, 0–9, _ 共 63 个字符
数据库名、表名、字段名等对象名中间不要包含空格
同一个 MySQL 软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在 SQL 语句中使用 `（着重号、反引号）引起来
保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了
对于 web 程序员来讲，重点是数据的 crud（增删改查），必须熟练编写 DQL、DML ，能够编写 DDL 完成数据库、表的操作，其它语言如 TPL、DCL、CCL 了解即可
SQL 是一门特殊的语言,专门用来操作关系数据库
不区分大小写

数据库

关系型数据库核心元素

数据行(记录)
数据列(字段)
数据表(数据行的集合)
数据库(数据表的集合)

txt

excel(office/wps)          sql(mysql/sql server.....)
workbook 一个文件           databases 一个数据库
sheet 一个表                table 一张表
列                          字段
行                          记录

创建数据库

方式1：创建数据库
sql
```
CREATE DATABASE 数据库名;
```
1

方式2：创建数据库并指定字符集

sql

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）
sql
```
CREATE DATABASE IF NOT EXISTS 数据库名;
```
1

如果 MySQL 中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

案例：

sql

-- 显示数据库
show databases;

-- 创建数据库 create 创建
create database python;

-- 删除数据库
drop database python;

-- 设置数据库的编码
create database python character set 'utf8mb4';

-- CREATE DATABASE IF NOT EXISTS 库名
-- 如果存在就不创建
-- 设置默认字符集为 utf8
CREATE DATABASE IF NOT EXISTS python CHARACTER SET 'utf8mb4';

创建一个数据库，给它一个名字，比如 python，以后的几次操作也是对 python 这个数据库进行操作。语句格式为 CREATE DATABASE <数据库名字>;，（注意不要漏掉分号 ;），前面的 CREATE DATABASE 也可以使用小写，具体命令为：

sql

-- create database 数据库名 charset=utf8;
create database python charset = utf8;

创建成功后输入命令 show databases; （注意不要漏掉;）检查一下：

在大多数系统中，SQL 语句都是不区分大小写的，因此以下语句都是合法的：

sql

CREATE DATABASE python;
create database python;
CREATE database python;
create DAtabaSE python;

但是出于严谨，而且便于区分保留字（保留字(reserved word)：指在高级语言中已经定义过的字，使用者不能再将这些字作为变量名或过程名使用。）和变量名，我们把保留字大写，把变量和数据小写。

使用数据库

查看当前所有的数据库

sql

SHOW DATABASES; -- 有一个 S ，代表多个数据库

查看当前正在使用的数据库

sql

SELECT DATABASE(); -- 使用的一个 mysql 中的全局函数

查看指定库下所有的表
sql
```
SHOW TABLES FROM 数据库名;
```
1
查看数据库的创建信息
sql
```
SHOW CREATE DATABASE 数据库名;
```
1
使用/切换数据库
sql
```
USE 数据库名;
```
1

提示

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

案例

接下来的操作，就在刚才创建的 python 中进行，由于一个系统中可能会有多个数据库，要确定当前是对哪一个数据库操作，使用语句 use <数据库名字>：

sql

-- 查看拥有的数据库
show databases;

-- 选择数据库
use python;

如图显示，则连接成功：

shell

mysql> use python;
Database changed
mysql>

输入命令 show tables; 可以查看当前数据库里有几张表，现在python 里还是空的：

shell

mysql> show tables;
Empty set (0.00 sec)

修改数据库

sql

-- 更改数据库的字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; -- 比如：gbk、utf8等

删除数据库

方式1：删除指定的数据库
sql
```
DROP DATABASE 数据库名;
```
1
方式2：删除指定的数据库（推荐）
sql
```
DROP DATABASE IF EXISTS 数据库名;
```
1

数据表

数据表（table）简称表，它是数据库最重要的组成部分之一。数据库只是一个框架，表才是实质内容。

而一个数据库中一般会有多张表，这些各自独立的表通过建立关系被联接起来，才成为可以交叉查阅、一目了然的数据库。

新建数据表

1、创建方式 1

在数据库中新建一张表的语句格式为：

sql

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);

提示

加上了 IF NOT EXISTS 关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

案例

如下便是一张表：

ID	name	math	chinese	english	total
01	张三	60	60	60	180
02	李四	60	60	60	180
03	王五	60	60	60	180

我们新建一张表 student，包含姓名与学生的成绩，所以语句为：

sql

create table student
(
    id      int primary key auto_increment,
    name    char(50),
    math    int,
    chinese int,
    english int,
    total   int
);

这时候再 show tables; 一下，可以看到刚才添加的两张表：

mysql> show tables;
+------------------+
| python		   |
+------------------+
| students         |
+------------------+
1 rows in set (0.00 sec)

2、创建方式 2

使用 AS subquery 选项，将创建表和插入数据结合起来
指定的列和子查询中的列要一一对应
通过列名和默认值定义列

sql

CREATE TABLE emp1 AS
SELECT *
FROM student;

CREATE TABLE emp2 AS
SELECT *
FROM student
WHERE 1 = 2; -- 创建的 emp2 是空表

3、查看数据库结构在 MySQL 中创建好数据表之后，可以查看数据表的结构。MySQL 支持使用DESCRIBE/DESC 语句查看数据表结构，也支持使用SHOW CREATE TABLE语句查看数据表结构。

语法格式如下：

sql

SHOW CREATE TABLE 表名;

使用 SHOW CREATE TABLE 语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

修改表

修改表指的是修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句可以实现：

向已有的表中添加列
修改现有表中的列
删除现有表中的列
重命名现有表中的列

追加一个列

语法格式如下：

sql

ALTER TABLE 表名
    ADD 【COLUMN】 字段名 字段类型 【FIRST| AFTER 字段名】;

举例：

sql

ALTER TABLE student
    ADD height float;

修改一个列

可以修改列的数据类型，长度、默认值和位置
修改字段数据类型、长度、默认值、位置的语法格式如下：

sql

ALTER TABLE 表名
    MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST| AFTER 字段名2】;

举例：

sql

ALTER TABLE student
    MODIFY height int;

对默认值的修改只影响今后对表的修改
此外，还可以通过此种方式修改列的约束。这里暂先不讲。

重命名一个列

使用 CHANGE old_column new_column dataType 子句重命名列。语法格式如下：

sql

ALTER TABLE 表名
    CHANGE 【column】 列名 新列名 新数据类型;

举例：

sql

ALTER TABLE student
    CHANGE name username varchar (15);

删除一个列

删除表中某个字段的语法格式如下：

sql

ALTER TABLE 表名 DROP COLUMN 字段名;

举例：

sql

ALTER TABLE student DROP COLUMN height;

重命名表

方式一：使用RENAME

sql

RENAME TABLE student TO student1;

方式二：

sql

ALTER table student RENAME [TO] student1; -- [TO] 可以省略

删除表

在 MySQL 中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
语法格式：

sql

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

举例：

sql

DROP TABLE student;

注意

DROP TABLE 语句不能回滚，删除之后数据都没了

清空表

TRUNCATE TABLE 语句：
- 删除表中所有的数据
- 释放表的存储空间
举例：

sql

TRUNCATE TABLE student;

TRUNCATE 语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

提示

阿里开发规范：

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

数据类型

在刚才新建表的过程中，我们提到了数据类型，MySQL 的数据类型和其他编程语言大同小异，下表是一些 MySQL 常用数据类型：

数据类型	大小(字节)	用途	格式
INT	4	整数
FLOAT	4	单精度浮点数
DOUBLE	8	双精度浮点数
ENUM	--	单选,比如性别	ENUM('a','b','c')
SET	--	多选	SET('1','2','3')
DATE	3	日期	YYYY-MM-DD
TIME	3	时间点或持续时间	HH:MM:SS
YEAR	1	年份值	YYYY
CHAR	0~255	定长字符串
VARCHAR	0~255	变长字符串
text	0~65535	长文本数据

整数除了 INT 外，还有 TINYINT、SMALLINT、MEDIUMINT、BIGINT。

CHAR 和 VARCHAR 的区别： CHAR 的长度是固定的，而 VARCHAR 的长度是可以变化的。

比如，存储字符串 "abc"，对于 CHAR(10)，表示存储的字符将占 10 个字节(包括 7 个空字符)
而同样的 VARCHAR(12) 则只占用 4 个字节的长度，增加一个额外字节来存储字符串本身的长度，12 只是最大值，当你存储的字符小于 12 时，按实际长度存储。

ENUM和 SET 的区别: ENUM 类型的数据的值，必须是定义时枚举的值的其中之一，即单选，而 SET 类型的值则可以多选。

想要了解更多关于 MySQL 数据类型的信息，可以参考下面两篇博客。

案例-数据表修改

运行以下指令，在服务器中产生数据。

sql

show databases;
drop database if exists python;
create database python character set 'utf8mb4';
use python;

create table employee
(
    id     int primary key auto_increment,
    name   char(20),
    age    int,
    salary float
);

insert into employee
values (0, 'Tom', 26, 2500),
       (0, 'Jack', 24, 2500),
       (0, 'Rose', 26, 2800),
       (0, 'Jim', 35, 3000),
       (0, 'Mary', 21, 3000),
       (0, 'Alex', 21, 3000);

对表结构的修改，有时候一些小的错误会造成不可挽回的后果，所以请细心操作。另外需要注意，非必要情况不要修改表结构。

1、现在 employee 表中有 id、name、age、salaryt 这 3 个列，尝试加入 height (身高)一个列并指定 DEFAULT 约束：

注意

在开始之前需要重新初始化一下数据

mysql> select * from employee;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | Tom  |   26 |   2500 |
|  2 | Jack |   24 |   2500 |
|  3 | Rose |   26 |   2800 |
|  4 | Jim  |   35 |   3000 |
|  5 | Mary |   21 |   3000 |
|  6 | Alex |   21 |   3000 |
+----+------+------+--------+
6 rows in set (0.00 sec)

mysql> ALTER TABLE employee ADD height INT(4) DEFAULT 170;
Query OK, 6 rows affected (0.09 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from employee;
+----+------+------+--------+--------+
| id | name | age  | salary | height |
+----+------+------+--------+--------+
|  1 | Tom  |   26 |   2500 |    170 |
|  2 | Jack |   24 |   2500 |    170 |
|  3 | Rose |   26 |   2800 |    170 |
|  4 | Jim  |   35 |   3000 |    170 |
|  5 | Mary |   21 |   3000 |    170 |
|  6 | Alex |   21 |   3000 |    170 |
+----+------+------+--------+--------+
6 rows in set (0.00 sec)

可以发现：新增加的列，被默认放置在这张表的最右边。如果要把增加的列插入在指定位置，则需要在语句的最后使用 AFTER 关键词(“AFTER 列1” 表示新增的列被放置在 “列1” 的后面)。

提示

提醒：语句中的 INT(4) 不是表示整数的字节数，而是表示该值的显示宽度，如果设置填充字符为 0，则 170 显示为 0170

比如我们新增一列 weight(体重) 放置在 age(年龄) 的后面：

mysql> ALTER TABLE employee ADD weight INT(4) DEFAULT 120 AFTER age;
Query OK, 6 rows affected (0.07 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age  | weight | salary | height |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |    120 |   2500 |    170 |
|  2 | Jack |   24 |    120 |   2500 |    170 |
|  3 | Rose |   26 |    120 |   2800 |    170 |
|  4 | Jim  |   35 |    120 |   3000 |    170 |
|  5 | Mary |   21 |    120 |   3000 |    170 |
|  6 | Alex |   21 |    120 |   3000 |    170 |
+----+------+------+--------+--------+--------+
6 rows in set (0.00 sec)

上面的效果是把新增的列加在某位置的后面，如果想放在第一列的位置，则使用 FIRST关键词，如语句：

mysql> ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;
Query OK, 6 rows affected (0.15 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from employee;
+------+----+------+------+--------+--------+--------+
| test | id | name | age  | weight | salary | height |
+------+----+------+------+--------+--------+--------+
|   11 |  1 | Tom  |   26 |    120 |   2500 |    170 |
|   11 |  2 | Jack |   24 |    120 |   2500 |    170 |
|   11 |  3 | Rose |   26 |    120 |   2800 |    170 |
|   11 |  4 | Jim  |   35 |    120 |   3000 |    170 |
|   11 |  5 | Mary |   21 |    120 |   3000 |    170 |
|   11 |  6 | Alex |   21 |    120 |   3000 |    170 |
+------+----+------+------+--------+--------+--------+
6 rows in set (0.00 sec)

2、删除表中的一列和刚才使用的新增一列的语句格式十分相似，只是把关键词 ADD 改为 DROP ，语句后面不需要有数据类型、约束或位置信息。具体语句格式：

把刚才新增的 test 删除：

txt

mysql> ALTER TABLE employee DROP test;
Query OK, 6 rows affected (0.14 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age  | weight | salary | height |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |    120 |   2500 |    170 |
|  2 | Jack |   24 |    120 |   2500 |    170 |
|  3 | Rose |   26 |    120 |   2800 |    170 |
|  4 | Jim  |   35 |    120 |   3000 |    170 |
|  5 | Mary |   21 |    120 |   3000 |    170 |
|  6 | Alex |   21 |    120 |   3000 |    170 |
+----+------+------+--------+--------+--------+
6 rows in set (0.00 sec)

注意：这条重命名语句后面的 “数据类型” 不能省略，否则重命名失败。

3、句将 “height” 一列重命名为汉语拼音 “shengao” ，效果如下：

txt

mysql> ALTER TABLE employee CHANGE height shengao INT (4) DEFAULT 170;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from employee;
+----+------+------+--------+--------+---------+
| id | name | age  | weight | salary | shengao |
+----+------+------+--------+--------+---------+
|  1 | Tom  |   26 |    120 |   2500 |     170 |
|  2 | Jack |   24 |    120 |   2500 |     170 |
|  3 | Rose |   26 |    120 |   2800 |     170 |
|  4 | Jim  |   35 |    120 |   3000 |     170 |
|  5 | Mary |   21 |    120 |   3000 |     170 |
|  6 | Alex |   21 |    120 |   3000 |     170 |
+----+------+------+--------+--------+---------+
6 rows in set (0.00 sec)

4、把 Tom 的 age 改为 21，salary 改为 3000：

txt

mysql> select * from employee where name = 'Tom';
+----+------+------+--------+--------+---------+
| id | name | age  | weight | salary | shengao |
+----+------+------+--------+--------+---------+
|  1 | Tom  |   26 |    120 |   2500 |     170 |
+----+------+------+--------+--------+---------+
1 row in set (0.00 sec)

mysql> UPDATE employee SET age=21, salary=3000 WHERE name = 'Tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee where name = 'Tom';
+----+------+------+--------+--------+---------+
| id | name | age  | weight | salary | shengao |
+----+------+------+--------+--------+---------+
|  1 | Tom  |   21 |    120 |   3000 |     170 |
+----+------+------+--------+--------+---------+
1 row in set (0.00 sec)

注意

一定要有 WHERE 条件，否则会出现你不想看到的后果

5、尝试把 Tom 的数据删除：

运行结果

txt

mysql> DELETE FROM employee WHERE name = 'Tom';
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee where name = 'Tom';
Empty set (0.00 sec)

数据库定义语句 ​

SQL 概述 ​

SQL 背景知识 ​

SQL 语言排行榜 ​

SQL 分类 ​

SQL 规则与规范 ​

基本规则 ​

SQL 大小写规范 （建议遵守） ​

注 释 ​

命名规则（暂时了解） ​

数据库 ​

创建数据库 ​

使用数据库 ​

修改数据库 ​

删除数据库 ​

数据表 ​

新建数据表 ​

修改表 ​

追加一个列 ​

修改一个列 ​

重命名一个列 ​

删除一个列 ​

重命名表 ​

删除表 ​

清空表 ​

数据类型 ​

案例-数据表修改 ​