一，DDL(库和表的操作)

一，数据库的定义操作

CREATE DATABASE 数据库名; #使用系统默认的字符集

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

CREATE DATABASE IF NOT EXISTS 数据库名;

SHOW DATABASES; #S表示复数，代表多个数据库

SELECT DATABASE(); #调用DATABASE()系统函数

SHOW TABLES FROM 数据库名;

SHOW CREATE DATABASE 数据库名;

USE 数据库名;

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

DROP DATABASE 数据库名

DROP DATABASE IF EXISTS 数据库名

二，表的定义操作

常用的几种数据类型：

一，通用方式创建

语法格式：

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

注：IF NOT EXISTS关键字表示如果当前数据库中不存在要创建的数据表，则创建，如果不存在则不创建

必须指定：

• 表名
• 列名(字段名) 数据类型 长度

可选：

• 约束条件
• 默认值
• …………

例：创建一个test的表

CREATE TABLE test(
    id INT,
    emp_name VARCHAR(15),
    hire_data DATA
);

① 查看表结构

DESC test;

② 查看创建表的语句结构

SHOW CREATE TABLE test;

③ 查看表数据

SELECT * FROM test;

二，基于现有表创建

含义：提供其他表的查询结果来创建表，查询结果包括字段和数据

应用场景：

• 表的复制
• 对原有表进行拆分
• 对多表字段和数据的多重整合
• ……

注：默认情况这种方式表字段和字段数据会一并创建

格式：

CREATE TABLE 表名
AS (查询语句)

演示前的原有表结构：现在有employees表和departments表

例1(表复制)：复制employee表为em2表

CREATE TABLE em2
AS (SELECT * FROM employees)

例2(单表拆分)：通过employees表的employee_id，first_name，job_id字段创建一个名为WQL的表

CREATE TABLE wql
AS (SELECT employee_id,first_name,job_id FROM employees)

例3(多表整合)：通过employees表的employee_id，first_name，email字段和departments表的department_id，department_name创建一个名为fq的表

CREATE TABLE FQ
AS (
SELECT EM.employee_id,EM.first_name,EM.email,DP.department_id,DP.department_name
FROM employees EM
JOIN departments DP
ON EM.department_id = DP.department_id);

例4(其他设置)：创建时不要原表的数据，使用别名做为新表的字段名 --> 通过employees表的employee_id，first_name字段创建RNG表，不需要原表数据，新的字段名分别为emid和fname

CREATE TABLE rng
AS (SELECT employee_id emid,first_name fname
FROM employees
WHERE 1=2);

二，表的修改,删除等

ALTER TABLE 表名 ADD 字段名 数据类型 [FIRST/AFTER] [字段名]

ALTER TABLE 表名  MODIFY 字段名 修改类型 [DEFAULT]

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [DEFAULT]

ALTER TABLE 表名 DROP COLUMN 字段名

RENAME TABLE 原表名 TO 新表名

#1
ALTER TABLE WQL ADD RNG VARCHAR(15);
#2
ALTER TABLE WQL ADD `END` VARCHAR(15) FIRST;
#3
ALTER TABLE WQL ADD T1 VARCHAR(15) AFTER RNG;

ALTER TABLE WQL MODIFY RNG INT DEFAULT 11;

ALTER TABLE WQL CHANGE T1 G2 VARCHAR(15) DEFAULT "lala"

ALTER TABLE WQL DROP COLUMN G2;

RENAME TABLE WQL TO FQ

#方式一
DROP TABLE 表名
#方式二
DROP TABLE IF EXISTS 表名

DROP TABLE IF EXISTS FQ;

①格式1
TRUNCATE TABLE 表名;
②格式2
DELETE FROM 表名;

DML插入数据主要有两种：

1. 使用Values插入数据
2. 将查询结构插入表中

1，使用VALUES插入数据

三种场景：

1. VALUES全部列数据插入，字段的顺序与值的顺序对应 --> INSERT INTO 表名 VALUES (值1……值n)
2. 指定列插入数据 --> INSERT INTO 表名(列1……列n) VALUES (值1……值n)
3. 插入多行数据 --> INSERT INTO 表名 VALUES (值1……值n),(值1……值n)……

① VALUE全部列数据插入

INSERT INTO countries VALUES ("NW","挪威",1);

② 指定列插入数据

INSERT INTO countries(country_id,country_name) VALUES ("FI","芬兰");

③ 插入多行数据

INSERT INTO countries VALUES("NW","挪威",1),("FI","芬兰",1);

使用INSERT同时插入多条记录时，Mysql会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：

• Records：表名插入的记录条数
• Duplicates：表明插入时忽略的记录，原因可能是这些记录包含了重复的主键值
• Warnings：表明有问题的数据值，例如发生数据类型的转换

多行数据插入和单行数据插入的性能区别：一个同时插入多行记录的INSERT语句等于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率根高，因为MySQL执行单条INSERT语句插入多行数据比使用多个INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入

注：

• VALUES也可以写成VALUE，但是VALUES是标准的写法
• 字符和日期类型数据使用单引号

2，将查询结果插入表中

INSERT将SELECT语句查询结果插入到表中，此时不需要把每一个记录值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成

基本的语法格式：不需要写VALUES，后面直接加SELECT语句

INSERT INTO 表名 (列名1…………列名n) 
    SELECT * FROM 查询语句

例：

INSERT INTO countries
    SELECT * FROM countries1;

二，DML的更新删除

一，UPDATE更新

UPDATE更新语法：

UPDATA 表名 SET column1 = value1 …… column = value [WHERE condition]

① 单条更新

UPDATE countries SET country_name = "空想领地" WHERE country_id = "NW";

② 多条更新

UPDATE countries SET country_name = "梵帝冈",region_id = 3 WHERE  country_id='FI';

③ 模糊更新

UPDATE countries SET region_id = region_id*4 WHERE  country_id LIKE '%F%';

二，DELETE删除

DELETE删除语法：

DELETE FROM 表名 WHERE condition

① 单记录删除

DELETE FROM countries WHERE country_id='FI';

② 模糊删除

DELETE FROM countries WHERE country_id LIKE 'F%';

DML操作默认情况下，执行完以后都会自动提交数据，如果希望执行完以后自动提交数据，则需要使用SET autocommit = FALSE

三，补充

一，DCL的COMMIT和ROLLBACK使用

Commit和RollBack：

• Commit：提交数据，一旦执行COMMIT，则数据就被永久的保存在数据库中
• RollBack：回滚数据，一旦执行RollBack，则可以实现数据的回滚

注：RollBack回滚到最近一次Commit之后，Commit提交之后回就不能在进行回滚

TRUNCATE TABLE和DELETE TABLE的对比：

相同点：都可以实现对表中所有数据的清除

不同点：

• TRUNCATE TABLE：一旦执行此操作，表数据全部清除，同时数据不可以回滚
• DELETE FROM TABLE：一旦执行此操作，表数据可以全部清除，同时，数据可以实现回滚
• DELETE 可以加WHERE条件判断，而TRUNCATE不能

TRUNCATE和DELETE虽然功能都是清除数据，但它们各属于不同的分类，TRUNCATE属于DDL语句，DELETE属于DML语句

DDL和DML的说明：

1. DDL的操作一旦执行，就不可以回滚。指令SET autocommit = FALSE对DDL操作无效
2. DML的操作默认情况，一旦执行，也是不可回滚的。但可以通过SET autocommit = FALSE进行控制实现RollBack

SET autocommit = FALSE的作用是取消默认提交，开启之后需要手动提交

注：DDL和DML语句都是默认提交，只是DDL不能取消默认提交，而DML可以通过SET autocommit取消

阿里开发规范：TRUNCATE比DELETE速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发TRIGGER，有可能造成事务，故不建议在开发代码使用TRUNCATE

演示：

① DELETE代表的DML语句可以回滚

#1，设置不自动提交
SET autocommit = FALSE;
#2，清空表数据
DELETE FROM RNG;
#3，回滚
ROLLBACK;
#4，查询数据
SELECT * FROM RNG;

② TRUNCATE代表的DDL语句不能回滚

#1，设置不自动提交
SET autocommit = FALSE;
#2，清空表数据
TRUNCATE TABLE RNG;
#3，回滚
ROLLBACK;
#4，查询数据
SELECT * FROM RNG;

没有结果集，被清空

二，MySQL的命名规范

强制性：

1，表名，字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价极大，因为无法进行预发布，所以字段名称需要慎重考虑

• 正例：wql_admin，fq_config，level3_name
• 反例：AliyunAdmin，rdcConfig，level_3_name

2，禁止保留字命名，如：desc，range，match，delayed等

3，表必备三个字段：id，gmt_create，gmt_modified

• id：主键，类型为BIGINT UNSIGEND，单表自增
• gmt_create：类型为DATETIME，数据创建的时间
• gmt_modified：类型为DATETIME，数据更新的时间

推荐：

1，表的命名最后遵循"业务名称_表的作用"

• 如：login_project(生产环境的登录功能表），alipay_test(测试环境的支付功能表)

2，表名与应用名尽量一致

3，合适的字符存储长度，不仅节约数据库表空间，节约索引存储，更重要的是提升检索速度

• 正例：无符号值可以避免误存负数，且扩大了表示范围

拓展1：如何理解清空表，删除表需谨慎！

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

   同样的在使用ALTER TABLE进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的备份，因为在数据库alter后无法rollback回滚，如果删除一个重要字段将很严重

拓展2：Mysql8新特性-DDL的原子化

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

例：

#准备一个w数据库
create database w;
#切换到w库
use w;
#创建一个wql表
create table wql(
    id int,
    name varchar(10));

① 在MySQL5.7中

#执行删除，fq不存在
drop table wql,fq;

结果报错因为fq不存在，但wql表被删除

② 在MySQL8.0中

drop table wql,fq;

结果报错，但wql没有被删除，因为在8.0中DDL语句是一个原子子化的