MySQL学习 - DDL 操作 - 小小程日记

一、数据库操作

创建

CREATE DATABASE 库名;
CREATE DATABASE 库名 CHARACTER SET 字符集;
CREATE DATABASE IF NOT EXISTS 库名;

查询

SHOW DATABASES;

查看所有数据库
USE 库名;

切换要操作数据库
SHOW DATABASES;

查询当前数据库所有的表
SELECT DATABASE();

查询当前操作的数据库的库名
SHOW TABLES FROM 库名;

查询指定数据库有哪些数据表
SHOW CREATE DATABASE 库名;

查询创建数据库时的命令包括默认

修改

不支持修改数据库名操作

ALTER DATABASE 库名 CHARACTER SET 字符集;

修改数据库的字符集

删除

DROP DATABASE 库名;
DROP DATABASE IF EXISTS 库名;

二、数据表操作

创建

初始创建

CREATE TABLE 表名(
  字段名 字段类型,
    ... ...
);

不存在再初始创建

CREATE TABLE IS NOT EXIST 表名(
  字段名 字段类型,
    ... ...
);

基于现有表创建

根据查询语句的结果创建新的表，新表会创建字段和数据。

CREATE TABLE 表名 AS 查询语句;

从 departments 创建新表

SQL > DESC departments;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| department_id   | int         | NO   | PRI | 0       |       |
| department_name | varchar(30) | NO   |     | NULL    |       |
| manager_id      | int         | YES  | MUL | NULL    |       |
| location_id     | int         | YES  | MUL | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

SQL -> CREATE TABLE IF NOT EXISTS departments2 AS
  -> SELECT department_id id,department_name name,manager_id
  -> FROM departments;
Query OK, 27 rows affected (0.0380 sec)

Records: 27  Duplicates: 0  Warnings: 0

SQL > DESC departments2;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | NO   |     | 0       |       |
| name       | varchar(30) | NO   |     | NULL    |       |
| manager_id | int         | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

 SQL > SELECT * FROM departments2;
+-----+----------------------+------------+
| id  | name                 | manager_id |
+-----+----------------------+------------+
|  10 | Administration       |        200 |
|  20 | Marketing            |        201 |
|  30 | Purchasing           |        114 |
|  40 | Human Resources      |        203 |
|  50 | Shipping             |        121 |
-- ... ...

查询

DESC 表名;

查询表结构
SHOW CREATE TABLE 表名;

查询创建数据表的命令包括默认状态
SHOW TABLES;

查询当前数据库有哪些表
SHOW TABLES FROM 库名;

查询指定数据库有哪些表

重命名

重命名表（方式一）
```
RENAME TABLE 旧表名
TO 新表名;
```

重命名表（方式二）

ALTER TABLE 旧表名
RENAME TO 新表名;

删除

DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;

清空

删除数据，保留表结构

TRUNCATE TABLE 表名;

字段操作

添加字段

默认插入最后

ALTER TABLE 表名
ADD 字段名 类型;

插入最前面

ALTER TABLE 表名
ADD 字段名 类型 FIRST;

插入指定字段后面

ALTER TABLE 表名
ADD 字段名 类型 AFTER 被插入的字段名;

修改字段

ALTER TABLE 表名
MODIFY 字段名 类型 默认值...;

 SQL -> ALTER TABLE `departments2`
   -> MODIFY name varchar(20) default 'zhangsan';

重命名字段

ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新字段类型;

删除字段

ALTER TABLE 表名
DROP COLUMN 字段名;

三、`DDL` 和 `DML` 的 `ROLLBACK` 操作

DDL 数据定义语言，操作对象是数据库、数据表，DML 数据操作语言，目标对象是表里面的记录。

实现回滚的前提是执行了 SET autocommit = FALSE，关闭了自动提交。

回滚操作属于 DCL 数据控制语言，包括有 ROLLBACK、COMMIT

`COMMIT` 和 `ROLLBACK`

COMMIT 提交数据

一旦执行了 COMMIT，数据就无法回滚
ROLLBACK 回滚数据

讲数据回滚到最近的一次 COMMIT 之后

`DDL` 和 `DML` 回滚区别

DDL 无法回滚
DML 回滚前需要执行 SET autocommit = FALSE

测试

`DML` 回滚

原始数据

+---------------+----------------------+------------+-------------+
| department_id | department_name      | manager_id | location_id |
+---------------+----------------------+------------+-------------+
|            30 | Purchasing           |        114 |        1700 |
|            40 | Human Resources      |        203 |        2400 |
|            50 | Shipping             |        121 |        1500 |
|            60 | IT                   |        103 |        1400 |
|            70 | Public Relations     |        204 |        2700 |
|            80 | Sales                |        145 |        2500 |
|            90 | Executive            |        100 |        1700 |
|           100 | Finance              |        108 |        1700 |
|           110 | Accounting           |        205 |        1700 |
|           120 | Treasury             |       NULL |        1700 |
|           130 | Corporate Tax        |       NULL |        1700 |
|           140 | Control And Credit   |       NULL |        1700 |
|           150 | Shareholder Services |       NULL |        1700 |
|           160 | Benefits             |       NULL |        1700 |
|           170 | Manufacturing        |       NULL |        1700 |
|           180 | Construction         |       NULL |        1700 |
|           190 | Contracting          |       NULL |        1700 |
|           200 | Operations           |       NULL |        1700 |
|           210 | IT Support           |       NULL |        1700 |
|           220 | NOC                  |       NULL |        1700 |
|           230 | IT Helpdesk          |       NULL |        1700 |
|           240 | Government Sales     |       NULL |        1700 |
|           250 | Retail Sales         |       NULL |        1700 |
|           260 | Recruiting           |       NULL |        1700 |
|           270 | Payroll              |       NULL |        1700 |
+---------------+----------------------+------------+-------------+

关闭自动提交，执行删除

SQL >SET autocommit = FALSE;
Query OK, 0 rows affected (0.0004 sec)
SQL > DELETE FROM departments2 WHERE department_id<100;
Query OK, 7 rows affected (0.0008 sec)
SQL > SELECT * FROM departments2 ;
+---------------+----------------------+------------+-------------+
| department_id | department_name      | manager_id | location_id |
+---------------+----------------------+------------+-------------+
|           100 | Finance              |        108 |        1700 |
|           110 | Accounting           |        205 |        1700 |
|           120 | Treasury             |       NULL |        1700 |
|           130 | Corporate Tax        |       NULL |        1700 |
|           140 | Control And Credit   |       NULL |        1700 |
|           150 | Shareholder Services |       NULL |        1700 |
|           160 | Benefits             |       NULL |        1700 |
|           170 | Manufacturing        |       NULL |        1700 |
|           180 | Construction         |       NULL |        1700 |
|           190 | Contracting          |       NULL |        1700 |
|           200 | Operations           |       NULL |        1700 |
|           210 | IT Support           |       NULL |        1700 |
|           220 | NOC                  |       NULL |        1700 |
|           230 | IT Helpdesk          |       NULL |        1700 |
|           240 | Government Sales     |       NULL |        1700 |
|           250 | Retail Sales         |       NULL |        1700 |
|           260 | Recruiting           |       NULL |        1700 |
|           270 | Payroll              |       NULL |        1700 |
+---------------+----------------------+------------+-------------+

执行回滚，查询

SQL > ROLLBACK;
Query OK, 0 rows affected (0.0014 sec)
SQL > SELECT * FROM departments2;
+---------------+----------------------+------------+-------------+
| department_id | department_name      | manager_id | location_id |
+---------------+----------------------+------------+-------------+
|            30 | Purchasing           |        114 |        1700 |
|            40 | Human Resources      |        203 |        2400 |
|            50 | Shipping             |        121 |        1500 |
|            60 | IT                   |        103 |        1400 |
|            70 | Public Relations     |        204 |        2700 |
|            80 | Sales                |        145 |        2500 |
|            90 | Executive            |        100 |        1700 |
|           100 | Finance              |        108 |        1700 |
|           110 | Accounting           |        205 |        1700 |
|           120 | Treasury             |       NULL |        1700 |
|           130 | Corporate Tax        |       NULL |        1700 |
|           140 | Control And Credit   |       NULL |        1700 |
|           150 | Shareholder Services |       NULL |        1700 |
|           160 | Benefits             |       NULL |        1700 |
|           170 | Manufacturing        |       NULL |        1700 |
|           180 | Construction         |       NULL |        1700 |
|           190 | Contracting          |       NULL |        1700 |
|           200 | Operations           |       NULL |        1700 |
|           210 | IT Support           |       NULL |        1700 |
|           220 | NOC                  |       NULL |        1700 |
|           230 | IT Helpdesk          |       NULL |        1700 |
|           240 | Government Sales     |       NULL |        1700 |
|           250 | Retail Sales         |       NULL |        1700 |
|           260 | Recruiting           |       NULL |        1700 |
|           270 | Payroll              |       NULL |        1700 |
+---------------+----------------------+------------+-------------+
25 rows in set (0.0006 sec)

再执行一次删除，并执行 COMMIT

SQL > DELETE FROM departments2 WHERE department_id<100;
Query OK, 7 rows affected (0.0008 sec)
SQL > SELECT * FROM departments2 ;
+---------------+----------------------+------------+-------------+
| department_id | department_name      | manager_id | location_id |
+---------------+----------------------+------------+-------------+
|           100 | Finance              |        108 |        1700 |
|           110 | Accounting           |        205 |        1700 |
|           120 | Treasury             |       NULL |        1700 |
|           130 | Corporate Tax        |       NULL |        1700 |
|           140 | Control And Credit   |       NULL |        1700 |
|           150 | Shareholder Services |       NULL |        1700 |
|           160 | Benefits             |       NULL |        1700 |
|           170 | Manufacturing        |       NULL |        1700 |
|           180 | Construction         |       NULL |        1700 |
|           190 | Contracting          |       NULL |        1700 |
|           200 | Operations           |       NULL |        1700 |
|           210 | IT Support           |       NULL |        1700 |
|           220 | NOC                  |       NULL |        1700 |
|           230 | IT Helpdesk          |       NULL |        1700 |
|           240 | Government Sales     |       NULL |        1700 |
|           250 | Retail Sales         |       NULL |        1700 |
|           260 | Recruiting           |       NULL |        1700 |
|           270 | Payroll              |       NULL |        1700 |
+---------------+----------------------+------------+-------------+
SQL > COMMIT;
Query OK, 0 rows affected (0.0025 sec)

执行回滚，查询

SQL > ROLLBACK;
Query OK, 0 rows affected (0.0014 sec)
SQL > SELECT * FROM departments2;
+---------------+----------------------+------------+-------------+
| department_id | department_name      | manager_id | location_id |
+---------------+----------------------+------------+-------------+
|           100 | Finance              |        108 |        1700 |
|           110 | Accounting           |        205 |        1700 |
|           120 | Treasury             |       NULL |        1700 |
|           130 | Corporate Tax        |       NULL |        1700 |
|           140 | Control And Credit   |       NULL |        1700 |
|           150 | Shareholder Services |       NULL |        1700 |
|           160 | Benefits             |       NULL |        1700 |
|           170 | Manufacturing        |       NULL |        1700 |
|           180 | Construction         |       NULL |        1700 |
|           190 | Contracting          |       NULL |        1700 |
|           200 | Operations           |       NULL |        1700 |
|           210 | IT Support           |       NULL |        1700 |
|           220 | NOC                  |       NULL |        1700 |
|           230 | IT Helpdesk          |       NULL |        1700 |
|           240 | Government Sales     |       NULL |        1700 |
|           250 | Retail Sales         |       NULL |        1700 |
|           260 | Recruiting           |       NULL |        1700 |
|           270 | Payroll              |       NULL |        1700 |
+---------------+----------------------+------------+-------------+
18 rows in set (0.0009 sec)

COMMIT 之后数据无法回滚

`DDL` 回滚

接着上面继续操作，清空数据表

SQL >SET autocommit = FALSE;
Query OK, 0 rows affected (0.0004 sec)
SQL > TRUNCATE TABLE departments2;
Query OK, 0 rows affected (0.0510 sec)
SQL > SELECT * FROM departments2;
Empty set (0.0022 sec)

回滚查询

SQL > ROLLBACK;
Query OK, 0 rows affected (0.0014 sec)
SQL > SELECT * FROM departments2;
SQL > SELECT * FROM departments2;
Empty set (0.0022 sec)

数据回滚失败

文章目录

MySQL学习 - DDL 操作

一、数据库操作

创建

查询

修改

删除

二、数据表操作

创建

查询

重命名

删除

清空

字段操作

三、`DDL` 和 `DML` 的 `ROLLBACK` 操作

`COMMIT` 和 `ROLLBACK`

`DDL` 和 `DML` 回滚区别

测试

`DML` 回滚

`DDL` 回滚

添加新评论

最新文章

最近回复

分类

标签

归档

其它

MySQL学习 - DDL 操作

一、数据库操作

创建

查询

修改

删除

二、数据表操作

创建

查询

重命名

删除

清空

字段操作

三、DDL 和 DML 的 ROLLBACK 操作

COMMIT 和 ROLLBACK

DDL 和 DML 回滚区别

测试

DML 回滚

DDL 回滚

添加新评论

最新文章

最近回复

分类

标签

归档

其它

三、`DDL` 和 `DML` 的 `ROLLBACK` 操作

`COMMIT` 和 `ROLLBACK`

`DDL` 和 `DML` 回滚区别

`DML` 回滚

`DDL` 回滚