SQL之玩转DDL

最新推荐文章于 2024-04-03 09:40:48 发布

划水yi术家

最新推荐文章于 2024-04-03 09:40:48 发布

阅读量380

点赞数

本文链接：https://blog.csdn.net/qq_51938362/article/details/127435600

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一、基础概述

1. 标识符命名规则

数据库名、表名不得超过30个字符，变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
数据库名、表名、字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用 ``（着重号）引起来
保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

2. MySQL中的数据类型

在这里插入图片描述
其中，常用的几类类型介绍如下：

在这里插入图片描述

二、创建和管理数据库

1. 创建数据库

方式1：创建数据库

CREATE DATABASE 数据库名;

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

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

方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）

CREATE DATABASE IF NOT EXISTS 数据库名;
# 如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

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

2. 使用数据库

查看当前所有的数据库

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

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

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

查看指定库下所有的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

使用/切换数据库

USE 数据库名;

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

3. 修改数据库

更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集; # 比如：gbk、utf8等

4. 删除数据库

方式1：删除指定的数据库

DROP DATABASE 数据库名;

方式2：删除指定的数据库（推荐）

DROP DATABASE IF EXISTS 数据库名;

5. 临时数据库的创建与使用

三、创建和管理表

1. 创建表

必备条件：
- CREATE TABLE权限
- 存储空间
方式1："白手起家"的方式

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);
# 加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；
# 如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

举例

mysql> # 方式1："白手起家"的方式 需要用户具备创建表的权限。
mysql> CREATE TABLE IF NOT EXISTS myemp1(   
    ->  	id INT,
    ->  	emp_name VARCHAR(15), #使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。
    ->  	hire_date DATE
    -> );
Query OK, 0 rows affected (0.58 sec)

mysql> # 查看表结构
mysql> DESC myemp1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| emp_name  | varchar(15) | YES  |     | NULL    |       |
| hire_date | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> # 查看创建表的语句结构
mysql> # 如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集。
mysql> SHOW CREATE TABLE myemp1;
+--------+--------------------------------------+
| Table  | Create Table
                             |
+--------+--------------------------------------+
| myemp1 | CREATE TABLE `myemp1` (
  `id` int(11) DEFAULT NULL,
  `emp_name` varchar(15) DEFAULT NULL,
  `hire_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------+
1 row in set (0.00 sec)

方式2：基于现有的表，同时导入数据

CREATE TABLE 表名 
AS
subquery

# 使用 AS subquery 选项，将创建表和插入数据结合起来

举例

# 方式2：基于现有的表，同时导入数据
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;

# 说明1：查询语句中字段的别名，可以作为新创建的表的字段的名称。
# 说明2：此时的查询语句可以结构比较丰富，使用前面章节讲过的各种SELECT
CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id,e.last_name lname,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

# 需求1：创建一个表employees_copy，实现对employees表的复制，包括表数据
CREATE TABLE employees_copy
AS
SELECT *
FROM employees;

# 需求2：创建一个表employees_blank，实现对employees表的复制，不包括表数据
CREATE TABLE employees_blank
AS
SELECT *
FROM employees
# 创建一个不可能条件
WHERE 1 = 2;

2. 修改表

修改表指的是修改数据库中已经存在的数据表的结构。
使用 ALTER TABLE 语句可以实现：
- 向已有的表中添加列
- 修改现有表中的列
- 删除现有表中的列
- 重命名现有表中的列

（1）添加字段

语法格式

ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];

举例

mysql> #3. 修改表  --> ALTER TABLE
mysql> DESC myemp1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| emp_name  | varchar(15) | YES  |     | NULL    |       |
| hire_date | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> # 添加一个字段
mysql> # 默认添加到表中的最后一个字段的位置
mysql> ALTER TABLE myemp1
    -> ADD salary DOUBLE(10,2);
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC myemp1;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | YES  |     | NULL    |       |
| emp_name  | varchar(15)  | YES  |     | NULL    |       |
| hire_date | date         | YES  |     | NULL    |       |
| salary    | double(10,2) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> # 添加到表中的第一个字段
mysql> ALTER TABLE myemp1
    -> ADD phone_number VARCHAR(20) FIRST;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC myemp1;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| phone_number | varchar(20)  | YES  |     | NULL    |       |
| id           | int(11)      | YES  |     | NULL    |       |
| emp_name     | varchar(15)  | YES  |     | NULL    |       |
| hire_date    | date         | YES  |     | NULL    |       |
| salary       | double(10,2) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> # 添加到表中emp_name后
mysql> ALTER TABLE myemp1
    -> ADD email VARCHAR(45) AFTER emp_name;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC myemp1;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| phone_number | varchar(20)  | YES  |     | NULL    |       |
| id           | int(11)      | YES  |     | NULL    |       |
| emp_name     | varchar(15)  | YES  |     | NULL    |       |
| email        | varchar(45)  | YES  |     | NULL    |       |
| hire_date    | date         | YES  |     | NULL    |       |
| salary       | double(10,2) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

（2）修改字段

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

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

举例

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ;

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';

（3）重命名字段

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

ALTER TABLE 表名 CHANGE [column] 列名 新列名 新数据类型;

举例

ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);

（4）删除字段

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

ALTER TABLE 表名 DROP [COLUMN] 字段名

举例

ALTER TABLE myemp1
DROP COLUMN my_email;

3. 重命名表

方式一：使用RENAME

RENAME TABLE emp
TO myemp;

举例

RENAME TABLE myemp1
TO myemp11;

方式二：

ALTER table dept
RENAME [TO] detail_dept;

举例

ALTER TABLE myemp2
RENAME TO myemp12;

4. 删除表

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

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

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

举例

# 不光将表结构删除掉，同时表中的数据也删除掉，释放表空间
DROP TABLE IF EXISTS myemp2;

DROP TABLE 语句不能回滚
DROP TABLE 语句不能回滚
DROP TABLE 语句不能回滚

5. 清空表

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

举例

TRUNCATE TABLE employees_copy;
或者
DELETE FROM employees_copy;

TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
TRUNCATE语句比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

关于 TRUNCATE 和 DELETE 的对比可见 DCL中 COMMIT 与 ROLLBACK 的使用

6. 临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。
当你使用 SHOW TABLES命令显示数据表列表时，你将无法看到临时表。

（1）创建

这与普通表的格式是大致差不多的，只需要在CREATE TABLE中添加一个TEMPORARY 即可。同样，他也有两种方式，“白手起家”以及“基于现有的表，同时导入数据”

语法格式

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

CREATE TEMPORARY  TABLE 临时表名
AS
subquery

（2）删除

 DROP TABLE 临时表名;

四、删表需谨慎

1. 谨慎谨慎

表删除操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信息提示，因此执行删除操时应当慎重。在删除表前，最好对表中的数据进行备份，这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。

同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的备份，因为数据库的改变是无法撤销的，如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

2. MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即 DDL操作要么成功要么回滚。DDL操作回滚日志写入到data dictionary数据字典mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表，结果如下：

CREATE DATABASE mytest;
USE mytest;
CREATE TABLE book1(
	book_id INT ,
	book_name VARCHAR(255)
);
SHOW TABLES;

在MySQL 5.7版本中，测试步骤如下：删除数据表book1和数据表book2，结果如下：

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

mysql># 再次查询数据库中的数据表名称，结果如下：
mysql> SHOW TABLES;
Empty set (0.00 sec)

# 从结果可以看出，虽然删除操作时报错了，但是仍然删除了数据表book1。

在MySQL 8.0版本中，测试步骤如下：删除数据表book1和数据表book2，结果如下：

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

mysql># 再次查询数据库中的数据表名称，结果如下：
mysql> SHOW TABLES;
+------------------+
| Tables_in_mytest |
+------------------+
| book1 |
+------------------+
1 row in set (0.00 sec)

# 从结果可以看出，数据表book1并没有被删除。

划水yi术家

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL之玩转DDL

目录一、基础概述1. 标识符命名规则2. MySQL中的数据类型二、创建和管理数据库1. 创建数据库2. 使用数据库3. 修改数据库4. 删除数据库5. 临时数据库的创建与使用三、创建和管理表1. 创建表2. 修改表（1）添加字段（2）修改字段（3）重命名字段（4）删除字段3. 重命名表4. 删除表5. 清空表6. 临时表（1）创建（2）删除四、删表需谨慎1. 谨慎谨慎2. MySQL8新特性—DDL的原子化一、基础概述1. 标识符命名规则数据库名、表名不得超过30个字符，变量名限制为29个必须
复制链接

扫一扫