MySQL 数据定义语言（DDL）

路人甲Java 2022-04-05

展开全文

SQL 包含以下 4 部分：
    1    数据定义语言（DDL）：DROP、CREATE、ALTER 等语句。
    2    数据操作语言（DML）：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。
    3    数据查询语言（DQL）：SELECT 语句。
    4    数据控制语言（DCL）: GRANT、REVOKE、COMMIT、ROLLBACK 等语句。

MySQL之DDL(Data Definition Language): 数据定义语言：CREATE,ALTER,DROP等

DDL：操作数据库，表（CRUD）

1、操作数据库（CRUD）

(1)C(Create):创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名
[[DEFAULT] CHARACTER SET 字符集名]
[[DEFAULT] COLLATE 校对规则名];

[ ]中的内容是可选的。
语法说明如下：
数据库名：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
[DEFAULT] COLLATE：指定字符集的默认校对规则。

eg.

mysql> CREATE DATABASE IF NOT EXISTS test
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected, 1 warning (0.01 sec)

(2)R(Retrieve):查询

SHOW DATABASES [LIKE '数据库名'];

语法说明如下：
LIKE 从句是可选项，用于匹配指定的数据库名称。LIKE 从句可以部分匹配，也可以完全匹配。
数据库名由单引号' '包围。

eg.查询全部

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |         |
| mqcms              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| sys                |
| test               |
| test_aa            |
| ultrax             |
+--------------------+
9 rows in set (0.00 sec)

eg.查询某一个(我有test和test_aa两个库)

mysql> show databases like 'test';
+-----------------+
| Database (test) |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)

mysql> show databases like '%test%';
+-------------------+
| Database (%test%) |
+-------------------+
| dedetest          |
| test              |
| test_aa           |
+-------------------+
3 rows in set (0.00 sec)

这里的like跟模糊查询一样%aa%;%aa;aa%;

eg.查询某个数据库的创建语言

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

(3)U(Update):修改

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

语法说明如下：
ALTER DATABASE 用于更改数据库的全局特性。
使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
数据库名称可以忽略，此时语句对应于默认数据库。
CHARACTER SET 子句用于更改默认的数据库字符集。

eg.修改数据库test的字符集

mysql> ALTER DATABASE test
    -> DEFAULT CHARACTER SET gb2312
    -> DEFAULT COLLATE gb2312_chinese_ci;
Query OK, 1 row affected (0.02 sec)


mysql> show create database test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gb2312 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

(4)D(Delete):删除

DROP DATABASE [ IF EXISTS ] <数据库名>

语法说明如下：
<数据库名>：指定要删除的数据库名。
IF EXISTS：用于防止当数据库不存在时发生错误。
DROP DATABASE：删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心，以免错误删除。如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

注意：MySQL 安装后，系统会自动创建名为 information_schema 和 mysql 的两个系统数据库，系统数据库存放一些和数据库相关的信息，如果删除了这两个数据库，MySQL 将不能正常工作。

使用 DROP DATABASE 命令时要非常谨慎，在执行该命令后，MySQL 不会给出任何提示确认信息。DROP DATABASE 删除数据库后，数据库中存储的所有数据表和数据也将一同被删除，而且不能恢复。因此最好在删除数据库之前先将数据库进行备份。

eg.删除数据库test_aa;

mysql> DROP DATABASE IF EXISTS test_aa;
Query OK, 0 rows affected (0.02 sec)

mysql> show databases like 'test%';
+------------------+
| Database (test%) |
+------------------+
| test             |
+------------------+
1 row in set (0.00 sec)

2、操作数据表（CRUD）

（1）C(Create)创建表

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

其中，[表定义选项]的格式为：
<列名1> <类型1> [,…] <列名n> <类型n>


CREATE TABLE 语句的主要语法及使用说明如下：
CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。
<表名>：指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。例如，'mydb'.'mytbl' 是合法的，但 'mydb.mytbl' 不合法。
<表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。
默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。

eg.创建一张测试表test_tbl;

字段名称	字段类型	字段备注
id	int(11)	ID
name	varchar(20)	名称
age	int(11)	年龄
sex	tinyint(4)	性别0 男 1女 2 未知

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| board          |
| data           |
+----------------+
2 rows in set (0.01 sec)


mysql> CREATE TABLE test_tbl
    -> (
    -> id INT(11),
    -> name VARCHAR(20),
    -> age INT(11),
    -> sex TINYINT(4)
    -> );
Query OK, 0 rows affected (0.03 sec)

复制表结构：

 CREATE TABLE <数据表名> like <被复制的表名>;

eg.

mysql> CREATE TABLE test_tbl_cp like test_tbl;
Query OK, 0 rows affected (0.01 sec)

(2)R(Retrieve)查看表

查看所有表：

SHOW TABLES;

eg.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| board          |
| data           |
| test_tbl       |
| test_tbl_cp    |
+----------------+
4 rows in set (0.00 sec)

查看表结构：

DESCRIBE <表名>;

或简写成：
DESC <表名>;

DESCRIBE/DESC 语句可以查看表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等

eg.

mysql> DESC test_tbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

其中，各个字段的含义如下：
Null：表示该列是否可以存储 NULL 值。
Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，如果有，值是多少。
Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE语句:

SHOW CREATE TABLE <表名>\G；

提示：使用 SHOW CREATE TABLE 语句不仅可以查看创建表时的详细语句，而且可以查看存储引擎和字符编码。如果不加“\G”参数，显示的结果可能非常混乱，加上“\G”参数之后，可使显示的结果更加直观，易于查看。

eg.

mysql> show create table test_tbl\G;
*************************** 1. row ***************************
       Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

(3)U(Update)修改

ALTER TABLE <表名> [修改选项]


修改选项的语法格式如下：
{ 
ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> 
}

添加字段:

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]；

新字段名为需要添加的字段的名称；FIRST 为可选参数，其作用是将新添加的字段设置为表的第一个字段；AFTER 为可选参数，其作用是将新添加的字段添加到指定的已存在的字段名的后面。

eg.在表test_tbl的name后面添加新字段name_cp：

mysql> ALTER TABLE test_tbl ADD name_cp VARCHAR(20) AFTER name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_tbl;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| name_cp | varchar(20) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sex     | tinyint(4)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改字段名称：

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

其中，旧字段名指修改前的字段名；新字段名指修改后的字段名；新数据类型指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

eg.修改name的名称改为names

mysql> ALTER TABLE test_tbl
    -> CHANGE name names VARCHAR(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_tbl;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| names   | varchar(20) | YES  |     | NULL    |       |
| name_cp | varchar(20) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sex     | tinyint(4)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改/删除字段默认值：

ALTER TABLE <表名> ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }

eg.将字段sex的默认值改为2

mysql> ALTER TABLE test_tbl ALTER sex SET DEFAULT 2;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_tbl;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| names   | varchar(20) | YES  |     | NULL    |       |
| name_cp | varchar(50) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sex     | tinyint(4)  | YES  |     | 2       |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改字段类型：

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

其中，表名指要修改数据类型的字段所在表的名称，字段名指需要修改的字段，数据类型指修改后字段的新数据类型。

eg.修改name_cp的数据类型varchar(50)

mysql> ALTER TABLE test_tbl 
    -> MODIFY name_cp varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_tbl;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| name_cp | varchar(50) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sex     | tinyint(4)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除字段：

ALTER TABLE <表名> DROP <字段名>；

其中，字段名指需要从表中删除的字段的名称。

eg.删除sex字段

mysql> ALTER TABLE test_tbl
    -> DROP sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_tbl;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| names   | varchar(20) | YES  |     | NULL    |       |
| name_cp | varchar(50) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改表名：

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

其中，TO 为可选参数，使用与否均不影响结果。

eg.修改表名test_tbl改为test_tb;

mysql> ALTER TABLE test_tbl
    -> RENAME TO test_tb;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| board          |
| data           |
| test_tb        |
| test_tbl_cp    |
+----------------+
4 rows in set (0.00 sec)

(4)D(Delete)删除表

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]


对语法格式的说明如下：
表名1, 表名2, 表名3 ...表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

两点注意：
用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。
表被删除时，用户在该表上的权限不会自动删除。

eg.删除表test_tbl_cp

mysql> DROP TABLE test_tbl_cp;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| board          |
| data           |
| test_tb        |
+----------------+
3 rows in set (0.00 sec)