此前仅介绍了最常用的数据的查询操作，属于数据的DML(数据操作语言)，此外还有增删改操作，同属于DML。此外需要先了解数据库、表的结构定义，也即数据库、表的增删改，属于DDL（数据定义语言）。DDL直接决定数据库、表、索引、视图等结构，不能回滚（或备份回滚），而DML是针对数据的增删改查，可以回滚（关闭自动commit）。

数据库的DDL

数据库的创建

数据库、表、字段都有命名规则，数据库、表名长度不能大于30个，变量不能超过29个，且只能包含63种字符（大小写字母52种，数字0到9，下划线_），且命名不能重复，使用保留字需要着重号；

创建一个数据库

当同名数据库存在不会重复创建、不会报错，以字符集utf8（低版本MySQL可能默认非utf8，建议显式指定） 

1
CREATE DATABASE IF NOT EXISTS mytest1 CHARACTER SET 'utf8';

查看数据库创建信息

1
SHOW CREATE DATABASE mytest1;

输出 

1
2
3
CREATE DATABASE `mytest1`   #名称
/*!40100 DEFAULT CHARACTER SET utf8mb3 */   #使用字符：utf8
/*!80016 DEFAULT ENCRYPTION='N' */        #加密：不加密

查看数据库

1
2
3
SHOW DATABASES;  -- 查看系统所有数据库

SELECT DATABASE() FROM DUAL; -- 查看当前正在使用数据库

使用/切换至某个数据库

1
Use mytest1;

查看当前数据库的表

1
2
3
SHOW TABLES;  --查看当前数据库的表

SHOW TABLES FROM 数据库名; --查看某数据库的表

数据库的修改(少用、慎用)

使用保留字ALTER DATABASE，数据库名不能修改，在可视化工具中改名实际上是建立新数据库、拷贝使用的表，开销很大。

更改字符集

1
ALTER DATABASE mytest1 CHARACTER SET 'utf8';

验证： 

1
SHOW CREATE DATABASE mytest1;

数据库的删除

使用保留字DROP；

存在才删除、且不会报错： 

1
DROP DATABASE IF EXISTS mytest1;

数据表的DDL

记录了表的增删改操作；

数据表的创建

USE+数据库切换到具体数据库，执行：

创建一张新表： 

1
2
3
4
5
CREATE TABLE IF NOT EXISTS table_employees(  --表名
  employees_id INT,    -- 变量名+数据类型
  employees_name VARCHAR(15),
  hire_data DATE
);

基于现有的表，拷贝字段创建新表： 确保权限足够访问源数据库，执行： 

1
2
3
4
CREATE TABLE table_test 
AS
SELECT employee_id,last_name,salary 
FROM atguigudb.employees;  --任何查询语句均适用

特殊的需求：只复制字段，不负值字段数据，使用恒否定的条件： 

1
2
3
4
CREATE TABLE table_test 
AS
SELECT * FROM employees
WHERE 1=2;

查看表信息

表的结构信息： 

1
DESC table_employees;
表的信息

表的创建信息： 

1
SHOW CREATE TABLE table_employees;

输出： 

1
2
3
4
5
CREATE TABLE `table_employees` (
  `employees_id` int DEFAULT NULL,
  `employees_name` varchar(15) DEFAULT NULL,
  `hire_data` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3  #存储引擎、字符集

数据表的修改

保留字ALTER TABLE；

添加一个字段ADD

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 默认添加于最后字段
ALTER TABLE table_employees 
ADD salary DOUBLE(10,2);  --数据类型DOUBLE，一共10位，小数点2位

-- 在第一个字段添加
ALTER TABLE table_employees 
ADD phone_num VARCHAR(11) FIRST;

-- 在xxx字段后添加email字段：
ALTER TABLE table_employees 
ADD email VARCHAR(20) AFter xxx;

-- 验证：
DESC table_employees;

修改一个字段MODIFY

修改数据类型（少用）、字符长度、默认值 

1
2
ALTER TABLE table_employees
MODIFY email VARCHAR(18) DEFAULT 'xxx@gmail.com';

移动字段： 

1
2
ALTER TABLE table_employees
MODIFY email VARCHAR(18) AFTER phone_num;

重命名一个字段CHANGE

email字段重命名成gmail： 

1
2
ALTER TABLE table_employees
CHANGE email gmail VARCHAR(20);

删除一个字段DROP COLUMN

1
2
ALTER TABLE table_employees
DROP COLUMN gmail;

数据表的重命名RENAME

写法一： 

1
2
ALTER TABLE table_employees
RENAME TO te;

写法二： 

1
2
RENAME TABLE table_employees
TO te;

数据表的删除DROP TABLE

1
DROP TABLE IF EXISTS te1;

数据表的清空TRUNCATE

1
2
3
4
TRUNCATE TABLE te;

-- 验证：
SELECT * FROM te;

MySQL8新特性：DDL原子性

DDL操作要么全部成功执行，要么报错回滚到初始状态，是MySQL8的原子性体现；

例如数据库仅存在table1时执行DROP TABLE table1,table2;，MySQL5.7会因为table2失败，仅删除表table1；而MySQL8则因为删除table2失败，仍然保留table1，体现出操作的原子性。

数据的DML

查询SELECT已经在前面三篇文章介绍完毕，以下是剩下的增删改操作，同属于DML；

新建测试数据库： 

1
2
3
4
5
6
7
8
9
10
CREATE DATABASE IF NOT EXISTS test_dml;
USE test_dml;

CREATE TABLE IF NOT EXISTS emp1(
  id INT,
  `name` VARCHAR(15),
  hire_date DATE,
  salary  DOUBLE(10,2)
);
SELECT * FROM emp1;

数据的添加Insert

1. 逐条添加数据

如果省略emp1的括号，插入Values必须按照表的字段顺序；如果满足约束条件（值可以为NULL），尽管省略某些字段，也能够成功添加。 

1
2
3
INSERT INTO emp1(id,`name`,hire_date,salary)
VALUES(1,'Eden','2024-11-08',3000),
(2,'Mike','2024-11-09',4000);   --多条添加，数据可以重复

2. 查询结果插入数据 注意将大长度字段放入小长度字段，如果其实际使用小于小长度字段则没问题，否则应该先扩容再拷贝查询结果。 

1
2
3
4
INSERT INTO emp1(id,`name`,hire_date,salary)
SELECT employee_id,last_name,hire_date,salary
FROM atguigudb.employees
WHERE department_id = 60;

数据的修改UPDATE...SET

通常配合WHERE表示修改哪一个记录的数据，否则全部进行修改： 

1
2
3
4
5
6
7
UPDATE emp1   -- 表名
SET `name` = 'Eason',salary = salary*1.2
WHERE `name` = 'MIKE'; --条件

UPDATE emp1   -- 表名
SET hire_date=CURDATE(),salary = salary*1.2
WHERE BINARY `name` like '%e%'; -- 条件:BINARY表示区分大小写匹配，仅匹配小写

数据的删除DELETE FROM

如果缺少过滤条件，全部数据将被清空 

1
2
DELETE FROM emp1  -- 表名
where `name`= 'Eden';

事务控制语言TCL

两个命令COMMIT和ROLLBACK，和git差不多一致，COMMIT代表将更改应用到数据库，ROLLBACK是在没有COMMIT当前修改的情况下，回滚到上次的COMMIT；

DLL蕴含着COMMIT操作，因此如果没有做备份，任何结构修改的命令都会不可逆地应用到数据库，例如使用TRUNCATE TABLE清空表数据；

而DML是否COMMIT取决于配置，SET autocommit = FALSE能够关闭自动commit，在这个前提下，DELETE FROM清空了所有数据，也能使用ROLLBACK回滚回来。 

1
2
3
SET autocommit = FALSE;
DELETE from emp1;
ROLLBACK;

DML支持回滚机制，也额外加大了系统的开销。