小小程知识库
xxcheng
2023-08-05
目录

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 表名(
	字段名 字段类型,
    ... ...
);
    1
    2
    3
    4

  • 不存在再初始创建

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

  • 基于现有表创建

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

    CREATE TABLE 表名 AS 查询语句;
    1

    从 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 |
-- ... ...
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36

# 查询

  • DESC 表名;

    查询表结构

  • SHOW CREATE TABLE 表名;

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

  • SHOW TABLES;

    查询当前数据库有哪些表

  • SHOW TABLES FROM 库名;

    查询指定数据库有哪些表

# 重命名

  • 重命名表（方式一）

    RENAME TABLE 旧表名
TO 新表名;
    1
    2

  • 重命名表（方式二）

    ALTER TABLE 旧表名
RENAME TO 新表名;
    1
    2

# 删除

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

# 清空

删除数据，保留表结构

  • TRUNCATE TABLE 表名;

# 字段操作

  • 添加字段

    默认插入最后

    ALTER TABLE 表名
ADD 字段名 类型;
    1
    2

    插入最前面

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

    插入指定字段后面

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

  • 修改字段

    ALTER TABLE 表名
MODIFY 字段名 类型 默认值...;
    1
    2
     SQL -> ALTER TABLE `departments2`
	 -> MODIFY name varchar(20) default 'zhangsan';
    1
    2

  • 重命名字段

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

  • 删除字段

    ALTER TABLE 表名
DROP COLUMN 字段名;
    1
    2

# 三、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 |
+---------------+----------------------+------------+-------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

关闭自动提交，执行删除

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 |
+---------------+----------------------+------------+-------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

执行回滚，查询

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

再执行一次删除，并执行 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

执行回滚，查询

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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)
1
2
3
4
5
6

回滚查询

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

数据回滚失败

上次更新: 2023/08/05, 10:48:31
子查询

←

最近更新
01
Go：GMP模型深入理解
01-10
02
rpc学习：进阶到gRPC
01-04
03
配置
12-12
更多文章>
Theme by Vdoing | Copyright © 2019-2024 xxcheng | 浙ICP备19024050号