文章目录
- DDL(数据库/表)
- 数据库
- 创建数据库
- 使用数据库
- 重命名数据库
- 删除数据库
- 表(table)
- 创建表
- 重命名表
- 删除表
- 清空表
- ALTER:对表中的列的增删改
- MYSQL 8新特性:DDL原子化
- DML(针对表)
- 增加表中数据(记录)
- 更新表中数据
- MYSQL8 新特性:计算列
- DCL(数据库/表)
- COMMIT 提交
- ROLLBACK 回滚
- 对于DDL和DML的回滚
- 拓展:阿里巴巴《Java开发手册》之MySQL字段命名
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
说明:
- 其中 id 必为主键,类型为BIGINT UNSIGNED、单表时自增、步长为 1。
- gmt_create, gmt_modified 的类型均为 DATETIME 类型,前者现在时表示主动式创建,后者过去分词表示被动式更新
- 【 推荐 】表的命名最好是遵循 “业务名称_表的作用”。
正例:alipay_task 、 force_project、 trade_config
- 【 推荐 】库名与应用名称尽量一致。
- 【参考】合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。
正例:无符号值可以避免误存负数,且扩大了表示范围