3. SQL语句-DDL/DML/DCL
首先,任何对数据库/表的增删改的操作都是需要用户有相应的权限的,这里都是用的root用户,权限最高,在高级篇我们再详细讲关于权限的问题。
3.1 (创建/管理)(表/库)
3.1.1 基础知识
标识符规则:
3.1.2 创建/管理数据库
(1)创建数据库
# 显示数据库创建细节
SHOW CREATE DATABASE testdb;
(2)使用数据库
# 查看当前连接中的数据库有哪些
SHOW DATABASES;
# 切换/使用哪个数据库
USE atguigudb;
# 查看当前使用的数据库
SELECT DATABASE()
FROM DUAL;
# 查看当前数据库中保存的数据表
SHOW TABLES;
# 查看指定数据库下保存的表
SHOW TABLES
FROM atguigudb;
(3)修改数据库
慎用!刚创建数据库,还没数据,改的成本很低,但如果是使用过的数据库,有很多表很多配套的代码,修改数据库成本很高。
# 修改数据库字符集
ALTER DATABASE testdb CHARACTER SET 'gbk';
(4)删除数据库
# 方式1
DROP DATABASE testdb;
# 方式2 (推荐)
DROP DATABASE IF EXISTS testdb;
3.1.3 创建/管理数据表
(1)创建数据表
创建表之前,首先要知道表中有哪些字段,字段是什么类型的数据,关于数据类型影响到很多问题(数据精度,存储大小,查询性能等),详细的可以参考下面的3.3节。
下面只是简单介绍几种常见的数据类型:
USE `testdb`;
# 方式1 创建新表,仅有结构,无数据
CREATE TABLE IF NOT EXISTS myemp1(
id INT,
emp_name VARCHAR(15),
hire_data DATE
);
# 方式2 基于现有表创建新表,结构+数据
# 由于SELECT语句很强大,所以该方式极其灵活。
# 查询语句中字段的别名,会作为新表的字段名称
CREATE TABLE myemp2
AS
SELECT id,hire_data
FROM myemp1;
(2)修改数据表
(3)重命名数据表
(4)删除数据表
删除掉了数据+结构,请和下面(5)的清空表区分开,删除表不可恢复,误删如果没有日志文件什么的恢复不了那就得跑路了兄弟!
(5)清空数据表
(6)补充: DCL中的DELETE和ROLLBACK
清空数据库 TRUNCATE操作是个commit操作,不能回滚,DELETE操作可以回滚。
DDL语言在执行后一定会自动commit,所以无法回滚。
(7)阿里巴巴开发手册–MySQL字段命名
(8)MySQL8新特性,DDL的原子化
DDL操作成功就成功,不成功会回滚。
3.2 数据增/删/改
数据的增删改操作是有可能因为约束执行失败的,约束后面会讲。
3.2.1 插入数据
(1)VALUES方式插入
测试数据插入的表如下:
(2)查询结果插入
3.2.2 更新数据
3.2.3 删除数据
delete
优点:
允许根据条件删除数据,具有灵活性。
可以记录删除操作,支持事务。不会重置AUTO_INCREMENT计数器,保留表结构。
缺点:
删除大量数据时较慢,因为它逐行删除。
会生成事务日志,可能会增加数据库的负担。
truncate
truncate table table_name;
优点:
非常快速,特别适用于大型表的数据清空。
不生成事务日志,减少数据库负担。
保留表结构,但会重置AUTO_INCREMENT计数器。
缺点:
无法指定条件删除,总是删除整个表中的数据。
不记录删除操作,无法回滚(@transactional注解不生效)。
需要适当权限。
3.2.4 MySQL新特性: 计算列
3.3 mysql数据类型精讲
首先用字段字符集,没指明就用表的,表也没指明就用库的,库也没指明就用my.ini配置文件中的,mysql8默认是utf8mb3,mysql5默认是latin,可以查询一下:
3.3.1 整数型
(1)介绍
(2)可选属性 M,UNSIGNED,ZEROFILL
(3)适用场景
3.3.2 浮点型
(1)介绍
(2)精度说明
3.3.3 定点数
(1)介绍
(2)使用经验
3.3.4 位类型 BIT
3.3.5 日期时间
(1)YEAR
(2)DATE
(3)TIME
(4)DATETIME
(5)TIMESTAMP
(6)开发中的经验
3.3.6 文本字符串
(1)CHAR与VARCHAR类型
char与varchar比较:
(2)TEXT类型
3.3.7 ENUM类型
3.3.8 SET类型
3.3.9 二进制字符串类型
(1)BINARY与VARBINARY
(2)BLOB
3.3.10 JSON类型
3.3.11 空间类型
了解,知道有这个东西,如果真要使用的话请查阅别的详细的教程。
3.3.12 小结-选择建议
3.4 约束
3.4.1 约束(constraint)概述
为什么要约束? 保证数据完整性。
什么是约束? 对表中字段的限制。
约束分类:
约束添加/删除:
查看约束:
3.4.2 非空约束 NOT NULL
3.4.3 唯一性约束 UNIQUE
用来限制某个字段值不能重复。
# create table 时添加约束
CREATE TABLE test1(
id INT UNIQUE, #列级约束,不给约束起名的话,约束名就是列名
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),
# 表级约束
CONSTRAINT uni_test1_email UNIQUE(email)
);
# ALTER TABLE 时添加约束
# 这个例子不太恰当,工资是可以重复的,这里就是个演示
ALTER TABLE test1
ADD CONSTRAINT uni_test1_sal UNIQUE(salary);
# 复合的唯一性约束,只能在表级约束上添加
# 复合唯一约束,一般在一些多对多的情况下添加
# 比如选课表,学号和课程编号可以设为复合唯一。
CREATE TABLE `user`(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),
# 这个约束的意思是只要不是name和password都相同,就满足唯一性。
CONSTRAINT uni_user_name_password UNIQUE(`name`,`password`)
);
# 删除唯一性约束
-- 添加唯一性约束的列上会自动创建唯一索引
-- 删除唯一约束只能通过删除唯一索引的方式删除
-- 删除时需要指定唯一索引名,唯一索引名和唯一约束名一样
-- 未创建约束名的情况下,单列约束默认是列名,多列约束默认是()里面排第一的列名
ALTER TABLE `test1`
DROP INDEX uni_test1_sal;
# 唯一性约束的列可以添加多个NULL值,不会报错
3.4.4 主键约束 PRIMARY KEY
主键约束=唯一约束+非空约束
# 一个表最多一个主键约束
# 可以在列级创建,也可以在表级创建;可以单列约束,也可以复合约束
# 如果是复合约束,这些列都不能为空
# 建立主键约束,会自动建立主键索引,删除主键约束,自动删除主键索引
# 不能修改主键字段的值,因为主键是唯一标识,改了有可能会破坏数据完整性
CREATE TABLE `test2`(
id INT PRIMARY KEY,#列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
# 或
CREATE TABLE `test2`(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
#表级约束
CONSTRAINT pk_test2_id PRIMARY KEY(id)#主键的约束名总是PRIMARY,自己命名无效
);
# 复合约束
CREATE TABLE `test3`(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
CONSTRAINT PRIMARY KEY(id,salary)
);
# ALTER TABLE时添加主键
CREATE TABLE `test4`(
id INT,
salary DECIMAL
);
# 添加主键约束
ALTER TABLE test4
ADD PRIMARY KEY(id);
# 删除主键约束(实际开发中不太可能做这一步)
ALTER TABLE test4
DROP PRIMARY KEY;
3.4.5 自增列 AUTO INCREAMENT
# 一个表只能一个自增列
# 自增只能加在主键/唯一键,而且数据类型只能是整数
# 添加数据时0/NULL给自增列会在当前最大值上累加,手动指定具体值则会添加具体值
CREATE TABLE `test5`(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
# 添加的时候不用指定自增列的数据,会自动累加,从1开始
INSERT INTO test5(last_name)
VALUES('tom');
# alter table 时添加/删除auto_increment
# 别这样做,一开始数据库设计的时候弄好,别开发中途改来改去的,开发中改表代价很大,很有可能相应代码就得跟着变
#添加自增:
ALTER TABLE test5
MODIFY id INT AUTO_INCREMENT;
#删除自增:
ALTER TABLE test5
MODIFY id INT;
# 自增的列在删除后会有记忆,添加新数据会从下一个顺序添加,而不会接着删掉的顺序
# 比如id是自增的,有1,2,3了,删除3,添加新的,会从4开始,而不是接着3
# mysql5版本重启后没记忆,上一行描述的情况下会从3开始添加
# 而mysql8版本这个自增主键的计数器会持久化到日志,重启mysql依然会从4开始
3.4.6 外键 FOREIGN KEY
# 外键约束,目的是保证引用完整性
# 上句话的意思举个例子:员工表(从表)中的部门id(外键),在部门表(主表)中要能找到
# 外键列必须引用的是主表的主键/唯一键的列
# 创建外键约束时默认约束名是个
# 删表时必须先删从表,再删主表,删数据也是
# 创建外键约束时,自动在外键列上创建普通索引,索引名也是外键约束名。
# 删除外键约束后必须手动删除对应索引
# 先创主表
CREATE TABLE dept1(
id INT UNIQUE,
`name` VARCHAR(15)
);
# 再创从表
CREATE TABLE emp1(
id INT ,
`name` VARCHAR(15),
dept1_id INT ,
#外键约束
CONSTRAINT fk_emp1_dept1_id FOREIGN KEY(dept1_id) REFERENCES dept1(id)
);
INSERT INTO dept1
VALUES(1,'it');
INSERT INTO emp1
VALUES(1,'tom',1);
#由于外键约束,添加失败
INSERT INTO emp1
VALUES(2,'ming',3);
# 在创建外键约束时还能添加约束等级
-- cascade方式:update/delete主表时,同步update/delete从表相应字段。
-- set null方式:update/delete主表时,从表相应字段变为null
-- no action方式:子表中有相应字段,不允许主表update/delete
-- restrict方式:同no action方式,这个是不指定约束等级时的默认方式
-- set default方式:update/delete主表时,从表相应字段变为一个默认值
# 推荐:对于外键约束最好采用:ON UPDATE CASCADE ON DELETE RESTRICT
CREATE TABLE dept2(
id INT UNIQUE,
`name` VARCHAR(15)
);
CREATE TABLE emp2(
id INT ,
`name` VARCHAR(15),
dept2_id INT ,
#外键约束,带约束等级
CONSTRAINT fk_emp2_dept2_id FOREIGN KEY(dept2_id) REFERENCES dept2(id)
ON UPDATE CASCADE ON DELETE RESTRICT
);
# 删除外键
# 1.删除外键约束
ALTER TABLE `emp2` DROP FOREIGN KEY fk_emp2_dept2_id;
# 2.删除索引
ALTER TABLE `emp2` DROP INDEX fk_emp2_dept2_id;
3.4.7 CHECK约束
# check约束
CREATE TABLE test6(
id INT,
salary DECIMAL(10,2) CHECK(salary>2000)
);
#或者标准写法
CREATE TABLE `test6` (
`id` INT DEFAULT NULL,
`salary` DECIMAL(10,2) DEFAULT NULL,
CONSTRAINT `test6_chk_1` CHECK ((`salary` > 2000))
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3;
INSERT INTO test6
VALUES(1,1999); #Check constraint 'test6_chk_1' is violated.
3.4.8 DEFAULT约束
CREATE TABLE test7(
id INT,
salary DECIMAL(10,2) DEFAULT 2000
);
INSERT INTO test7 (id)
VALUES(1); # 默认salary填2000
4. 其他数据库对象
4.1 视图
4.1.1 常见的数据库对象
4.1.2 视图概述
为啥使用视图?某个表里有某些不希望别人看到的字段,新建表又太冗余,就用视图来当一个虚拟表,仍可进行crud,但屏蔽了某些敏感字段。
# 创建视图
# create view xxx as (查询语句);
CREATE VIEW view_emp
AS
SELECT employee_id,hire_date,job_id,manager_id,department_id
FROM `employees`;
# 查询视图
SELECT *
FROM `view_emp`;
# 修改视图
# 方式1
CREATE OR REPLACE VIEW view_emp
AS
SELECT employee_id id,hire_date,job_id,manager_id,department_id
FROM `employees`;
# 方式2
ALTER VIEW view_emp
AS
SELECT employee_id id,hire_date `date`,job_id,manager_id,department_id
FROM `employees`;
# 删除视图
DROP VIEW IF EXISTS view_emp;
4.1.3 优缺点
优点:
- 视图封装常用查询,简化操作
- 视图不占资源,减少冗余
- 视图创建可屏蔽敏感字段,保证数据安全
- 可以使用视图,减少表结构改变,适应需求变更,工作中常用
缺点:
- 表改,视图要跟着进行维护。
4.2 存储过程与函数
存储过程和函数类似,就是在sql服务器预先封装了一段sql代码,使用时调用,存储过程没返回值,函数有返回值。
优点:提高sql代码重用性,减少网络传输量,减少sql暴露在网络中的风险。
存储过程分类:
- 无参 :无参数,无返回值)
- IN :有参无返回值
- OUT:无参有返回值
- IN+OUT:有参有返回值
- INOUT:有参有返回值
4.3 变量,流程控制,游标
4.3.1 变量
# 变量:系统变量+用户自定义变量
# 系统变量:全局+会话
# 用户变量:会话用户变量+局部变量
# 1.查看系统变量
# 查看全局变量
SHOW GLOBAL VARIABLES;
# 查看会话变量
SHOW SESSION VARIABLES;
# 模糊查询
SHOW GLOBAL VARIABLES LIKE 'admin_%';
# 查看指定变量值
SELECT @@global.变量名;
SELECT @@session.变量名;
# 2.修改变量
-- 1.改my.ini,然后重启服务,成本有点高
-- 2.set命令在服务运行时更改
SET @@global.变量名=变量值;
SET GLOBAL 变量名=变量值;
# 会话变量同上。
# 用户变量:用户会话+局部
# 用户变量用一个@开头
# 局部变量只在BEGIN-END内有效,在存储过程和函数中用
# 会话用户变量:
# 1.定义变量
SET @变量名=变量值;
SELECT AVG(salary) INTO @avg_sal FROM `employees`;
# 2.使用变量
SELECT @变量名;
# 局部变量这里不做说明,感兴趣的可以自行了解,
# 但由于阿里规范不允许使用存储过程和函数,所以就用不上局部变量
4.3.2 定义条件与处理程序
定义条件是事先定义程序执行过程中遇到的问题,处理程序则在定义条件下采取的处理方式,并能保证在报错后仍能继续执行。
# 定义错误条件
DECLARE 错误名称 CONDITION FOR 数值类型错误码;
DECLARE 错误名称 CONDITION FOR SQLSTATE 字符串类型错误码;
# 定义错误处理
DECLARE 处理方式 HANDLER FOR 错误条件 处理语句;
4.3.3 流程控制
流程控制这个东西是在定义的存储过程与函数里面的,可以只是了解,不掌握。
# 分支:
-- IF:使用在begin-end中
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2 ].....
[ELSE 操作N ]
END IF
-- CASE
-- 情况1:离散条件
CASE 表达式
WHEN 值1 THEN 结果1或语句1,是语句后面要加分号
WHEN 值2 THEN 结果2或语句2,是语句后面要加分号
....
ELSE 结果n或语句n,是语句后面要加分号
END [CASE](放在begin-end中要加case,放select中不用)
-- 情况2:连续条件
CASE
WHEN 条件1 THEN 结果1或语句1,是语句后面要加分号
WHEN 条件2 THEN 结果2或语句2,是语句后面要加分号
...
ELSE 结果n或语句n,是语句后面要加分号
END [CASE](放在begin-end中要加case,放select中不用)
# 循环:
-- LOOP
[loop_label:]LOOP # loop_label:loop的标注名称,可省略
循环语句
END LOOP [loop_label];
-- WHILE
[while_label]WHILE 循环条件 DO
循环体
END WHILE [while_label];
-- REPEAT
[repeat_label] REPEAT
循环体
UNTIL 循环结束条件
END REPEAT [repeat_label];
# 跳转:
-- ITERATE:类似java中的contiune
在循环内使用,表示回到循环开头,开始下一次循环,
-- LEAVE: 类似java中的break
可以在循环中使用 ( IF ... THEN LEAVE 标记名 )来跳出循环
4.3.4 游标
在之前的sql操作中,都是对查询结果的集合进行操作,而不能一条一条记录的操作,这时候就要用到游标。
游标让这种面向集合的语言有了面向过程的能力。
在存储过程和函数中用的多,这里不多解释。
4.4 触发器
触发器可以让某一个表的操作会自动触发其他表的操作,实现相关表的数据同步的改变。
触发器也是个写在sql服务器里的代码片段,像这种写在sql服务器里的操作都不建议使用,请在业务逻辑里实现。
具体不解释,感兴趣自己了解。
5. mysql8新特性
5.1 窗口函数
针对需要用到分组统计的结果对每条记录进行计算的场景。
窗口函数类似分组,但分组会把数据聚合成一条记录,窗口函数则是将结果置于每一条数据记录中。
# 1 序号函数:分组+排序
# 例子:按照部门分组,按照salary排序
# 1.1 ROW_NUMBER()函数:分组+排序,相同salary的row_num不并列,1234
SELECT
ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num,
employee_id,salary,department_id
FROM `employees`
WHERE department_id IS NOT NULL;
# 1.2 RANK()函数:分组+排序,相同salary的row_num并列,1134
SELECT
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num,
employee_id,salary,department_id
FROM `employees`
WHERE department_id IS NOT NULL;
# 1.3 DENSE_RANK()函数:相同salary的row_num并列,1123
SELECT
DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num,
employee_id,salary,department_id
FROM `employees`
WHERE department_id IS NOT NULL;
# 2 分布函数:累计比率?
# 2.1 PERCENT_RANK()函数
# 求50号部门的员工个人工资在本部门总工资的累计比率
SELECT
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS r,
PERCENT_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS pr,
employee_id,salary,department_id
FROM `employees`
WHERE department_id=50;
# 2.2 CUME_DIST()函数:
SELECT
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS r,
CUME_DIST() OVER(PARTITION BY department_id ORDER BY salary DESC) AS pr,
employee_id,salary,department_id
FROM `employees`
WHERE department_id=50;
# 3 前后函数:将前/后一条记录的某字段数据放在当前记录行,方便计算差值
# 3.1 LAG(expr,n): 前
SELECT
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS r,
LAG(salary,1) OVER(PARTITION BY department_id ORDER BY salary DESC) AS pre_salary,
employee_id,salary,department_id
FROM `employees`
WHERE department_id=50;
# 3.2 LAG(expr,n): 后
SELECT
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS r,
LEAD(salary,1) OVER(PARTITION BY department_id ORDER BY salary DESC) AS pro_salary,
employee_id,salary,department_id
FROM `employees`
WHERE department_id=50;
# 4 首尾函数:要和首尾数据比较的情况可用
# 4.1 FIRST_VALUE():首
SELECT
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS r,
FIRST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY salary DESC) AS highest_salary,
employee_id,salary,department_id
FROM `employees`
WHERE department_id=50;
# 4.1 LAST_VALUE():尾,博主不清楚测试的时候为啥无效?
SELECT
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS r,
LAST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY salary DESC) AS lowest_salary,
employee_id,salary,department_id
FROM `employees`
WHERE department_id=50;
# 5 其他函数
# 5.1 NTH_VALUE():第几个数据
SELECT
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS r,
NTH_VALUE(salary,3) OVER(PARTITION BY department_id ORDER BY salary DESC) AS 3th_salary,
employee_id,salary,department_id
FROM `employees`
WHERE department_id=50;
# 5.2 NTILE():数据均分几部分
SELECT
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS r,
NTILE(3) OVER(PARTITION BY department_id ORDER BY salary DESC) AS group_number,
employee_id,salary,department_id
FROM `employees`
WHERE department_id=50;
5.2 公用表表达式
# 公用表表达式(CTE):类似子查询,可以被其他公用表表达式访问。
# 1. 普通型
WITH CTE名称
AS (子查询)
SELECT/DELETE/UPDATE 语句;
# 案例:查找有员工的部门的详细信息
WITH cte_emp
AS(
SELECT DISTINCT department_id
FROM `employees`
)
SELECT *
FROM `departments` d
JOIN cte_emp e
ON d.department_id=e.department_id;
# 2. 递归型:递归型博主没搞懂,感兴趣的读者自己找找相关资料吧。
WITH RECURSIVE CTE名称
AS (子查询)
SELECT/DELETE/UPDATE 语句;
比较复杂的查询语句,博主喜欢在业务层拆成一系列简单查询的组合,但多次查询会加重数据库服务器的负担,所以能一次数据库查询做成的问题,不要分成多次查询,只是在高并发情况下的优化方向,