[mysql]数据库/表的DDL、DML基本语法参考

Back2zer0

已于 2022-10-03 17:42:41 修改

阅读量317

点赞数

文章标签： mysql 数据库 sql

于 2022-08-13 21:45:36 首次发布

本文链接：https://blog.csdn.net/m0_59298585/article/details/126324391

版权

DDL（数据库/表）

数据库

创建数据库

CREATE DATABASE 数据库名; 
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE IF NOT EXISTS 数据库名;
#如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。
#注意：DATABASE 不能改名。一些可视化工具可以改名，
#可它是建新库，把所有表复制到新库，再删旧库完成的。

常用数据类型：
在这里插入图片描述

使用数据库

SHOW DATABASES; #有一个S，代表多个数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数 
SHOW TABLES FROM 数据库名; 
SHOW CREATE DATABASE 数据库名; 
SHOW CREATE DATABASE 数据库名
USE 数据库名;
#注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，
#否则就要对所有对象加上“数据库名.”。

重命名数据库

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

删除数据库

 DROP DATABASE 数据库名; 
 DROP DATABASE IF EXISTS 数据库名;

表(table)

创建表

需要create table权限和存储空间。
方式1:
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
eg:

CREATE TABLE emp (
emp_id INT,
emp_name VARCHAR(20),
salary DOUBLE,
birthday DATE 

PRIMARY KEY (deptno)
);

方式2:

CREATE TABLE emp1 AS SELECT * FROM employees;

CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; 
# 创建的emp2是空表 

CREATE TABLE dept80 AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date 
FROM employees 
WHERE department_id = 80; 
DESCRIBE dept80;

重命名表

#1.
ALTER table dept 
RENAME （TO） detail_dept; 
#2.
RENAME TABLE emp TO myemp;

删除表

删除表：DROP
(慎重，破坏性极强，WARNING，警惕 ~~删库跑路~~ )

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

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

清空表

效果：

删除表中所有的数据
释放表的存储空间
表结构会保留

两种方式：DELETE / TRUNCATE
1.DELETE

DELETE FROM table_name

2.TRUNCATE

TRUNCATE TABLE table_name

DELETE可回滚，TRUNCATE不可以。

阿里开发规范：
【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无
事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同

ALTER：对表中的列的增删改

SHOW CREATE TABLE table_name # 查看
#增   ：添加一个列 row                ()内为可选选项
ALTER TABLE 表名 
ADD （COLUMN） 字段名 字段类型 （FIRST|AFTER 字段名）;
#删    ：对默认值的修改只影响今后对表的修改
ALTER TABLE 表名 DROP 【COLUMN】字段名
#改
ALTER TABLE 表名 
MODIFY (COLUMN) 字段名1 字段类型 (DEFAULT 默认值)(FIRST|AFTER 字段名 2);
#重命名列
ALTER TABLE 表名 CHANGE [column] 列名 新列名 新数据类型;

MYSQL 8新特性：DDL原子化

效果：要么成功要么回滚
示例：mysql> DROP TABLE book1,book2;
#(book1存在，book2不存在）
结果：DDL原子化会让本次操作直接失效，即使按顺序删除掉book1，在发现DROP book2操作后，也会回滚到最初始状态。
（mysql5.4 会直接删除book1，对book2报错，仅此而已）

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

DML（针对表）

两部分：增加(INSER)和更改（UPDATA）
最后是针对表中的列的操作。

增加表中数据(记录)

插入一条记录

#1. value和字段顺序必须一一对应，一次插入一条数据
INSERT INTO 表名 VALUES (value1,value2,....);
#2. 自己预设相应字段，没有包括的会视作默认值
INSERT INTO 表名(column1 [, column2, …, columnn]) 
VALUES (value1 [,value2, …, valuen]);
 #上述VALUES可写为VALUE

插入多条记录

#3.一次插入多条：VALUES后紧跟多条记录
VALUES (value1 [,value2, …, valuen]),
 (value1 [,value2, …, valuen]),
  ……
  (value1 [,value2, …, valuen]);
#4.子查询插入多条（SELECT值要对应字段）
INSERT INTO 目标表名 (tar_column1 [, tar_column2, …, tar_columnn]) 
SELECT (src_column1 [, src_column2, …, src_columnn])
FROM 源表名 [WHERE condition]

同时插入多条效率更高。

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

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

更新表中数据

UPDATE table_name  
SET column1=value1, column2=value2, … ,  #更新指定数据
[WHERE condition] #指定数据

可以一次更新多条数据。
如果需要回滚数据，则要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
使用 WHERE 子句指定需要更新的数据。
（如果省略 WHERE 子句，则表中的所有数据都将被更新。）

#格式：
DELETE FROM table_name 
[WHERE <condition>]; 
#筛选删除数据↓
DELETE FROM departments
WHERE department_name = 'Finance'; 

#不添加WHERE 则删除表中所有数据

MYSQL8 新特性:计算列

CREATE TABLE tb1
(
  a INT, 
  b INT,
  c INT GENERATED ALWAYS AS (a + b) VIRTUAL );
  #c 即为计算列，自动输入 generated 指定算术形式 的 数据

DCL（数据库/表）

DCL下，数据库和表只是观察视角的大小，与DCL本身无关。DCL针对的是当前操作的控制。

首先是两个关键字指令：

COMMIT 提交

提交数据。提交数据后，数据将被永久保存在数据库里，不能回滚。

ROLLBACK 回滚

回滚数据。将数据返回到最近的一次提交状态。

对于DDL和DML的回滚

DDL 操作一旦执行，则自动提交，不可回滚。（SET autocommit = FALSE 无效）
DML 操作在默认不可回滚。但执行之前，autocommit状态为FALSE，则可以回滚。

拓展：阿里巴巴《Java开发手册》之MySQL字段命名

【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。
数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name
【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
【强制】表必备三字段：
id, gmt_create, gmt_modified
说明：
1. 其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。
2. gmt_create, gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新
【推荐】表的命名最好是遵循 “业务名称_表的作用”。
正例：alipay_task 、 force_project、 trade_config
【推荐】库名与应用名称尽量一致。
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
正例：无符号值可以避免误存负数，且扩大了表示范围