MySQL 进阶
# MySQL链接
# 一、MySQL高级知识
# 1.1 子查询
# 1、作为where条件使用
-- 查询福建省的所有城市 两步的写法
select * from t_province where ProvinceName = '福建省';
select * from t_city where ParentId = '350000';
2
3
-- 子查询写法
SELECT * FROM t_city WHERE ParentId = (select ProvinceId from t_province where ProvinceName = '福建省')
SELECT CityId,CityName FROM t_city WHERE ParentId = (SELECT ProvinceId FROM t_province WHERE ProvinceName = '福建省')
2
3
# 2、查询结果作为表来使用
SELECT * FROM ( SELECT * FROM t_province ORDER BY id DESC LIMIT 2) temp ORDER BY id;
# 3、查询结果作为列来使用
SELECT c.CityId,c.CityName,(SELECT ProvinceName FROM t_province p WHERE p.provinceid=c.ParentId) '省/市' FROM t_city c;
# 1.2 关键字 exists 和not exists
# 1、exists 和in的等价写法
# 2、not exists 和not in的等价写法
# 二、Json存储
# 三、事务
MySQL通过SET AUTOCOMMIT
、START TRANSACTION
、COMMIT
、ROLLBACK
等语句支
持本地事务,具体语法如下:
start TRANSACTION
或 begin
; 开启事务
commit
; 提交
rollback
;回滚;
begin
insert into type values(4,'XXX','XXX'),--订单表
update.... --订单详情表
delete.... --购物车
commit
rollback
2
3
4
5
6
# 3.1 事务的特性(ACID)
事务在单个会话期间,执行一系列有序的数据库操作。
A(原子性)
:事务的所有步骤必须成功完成,否则,任何步骤都不会被提交。整体,不可再分:
C(一致性)
:事务的所有步骤必须成功完成,否则,所有数据都会被恢复到事务开始前的状态。
I(隔离性)
:未完成事务的所做得步骤必须与系统隔离,直到认为事务完成为止。
D(持久性)
:所有的数据都以某种形式保存,确保系统出现故障时,可以恢复到原始的状态。在数据库中永久性存储.
# 3.2 事务处理案例
收付款案例
package com.singerw.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TransactionDao {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// java应用程序操作mysql数据库,用到事务
// 必须要注意,我们的事务边界的开始,提交,回滚用到的是同一个connection对象
Class.forName("com.mysql.cj.jdbc.Driver");
// 获取连接 Connection conn = DriverManager.getConnection(url,user,password)
String url = "jdbc:mysql://localhost:3306/newsdb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "795200";
Connection connection = DriverManager.getConnection(url, user, password);
try {
// 设置事务提交方式为手动提交 ,表示这里是事务的开始
connection.setAutoCommit(false);
// connection.setAutoCommit(true); //每次都是自动提交了
// 扣除1
String sql1 = "update users set balance=balance-1 where userid=1";
PreparedStatement preparedStatement = connection.prepareStatement(sql1);
int n1 = preparedStatement.executeUpdate();
//突然出事了
System.out.println(1/0);
// 加1
String sql2 = "update users set balance=balance+1 where userid=2";
preparedStatement = connection.prepareStatement(sql2);
int n2 = preparedStatement.executeUpdate();
if (n1 > 0 && n2 > 0) {
System.out.println("转账成功 ,事务提交.");
// 手动提交 ,事务结束了
connection.commit();
} else {
System.out.println("转账失败,事务回滚.");
// 事务回滚
connection.rollback();
}
} catch (Exception e) {
try {
System.out.println("转账失败,事务回滚.");
connection.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
//释放资源
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# 四、索引
索引是对数据库表中一或多个列的值进行排序的结构,是帮助数据库高效查询数据的数据结构。索引类似书的“目录”,可以快速找到我们需要找的内容的页码,然后直接翻到那一页。
索引是提高 MySQL 查询性能的一个重要途径,但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用,从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引,因为事后可能需要监控大量的 SQL 才能定位到问题所在,而且添加索引的时间肯定是远大于初始添加索引所需要的时间,可见索引的添加也是非常有技术含量的。
# 4.1 索引类型
- 标准索引
- **唯一索引(**唯一约束)
- 主键索引(主键约束)
- 全文索引
# 4.2 索引相关的语法
1、创建一个标准索引
- 基本语法
create INDEX idx_student_name on 表名(name);
建议的写法
- 修改表结构添加索引
alter table 表名 add index idx_student_name(name);
2、创建一个唯一索引
create UNIQUE INDEX idx_student_name on 表名(name)
;
insert into 表名(name,gender,age,phone) values('xx','男',null,null)
;
name
字段在插入重复数据时报错,无法插入。
3、查看索引
show index from 表名
;
4、删除索引
alter table 表名 drop index idx_student_name
;
# 4.3 查看执行计划
EXPLAIN SELECT * FROM t_area where ParentId='120100'
时是进行全表扫描。
添加索引,再来看执行计划EXPLAIN SELECT * FROM t_area where ParentId='120100'
时是走
id
:选择标识符select_type
:表示查询的类型。table
:输出结果集的表partitions
:匹配的分区type
:表示表的连接类型possible_keys
:表示查询时,可能使用的索引key
:表示实际使用的索引key_len
:索引字段的长度ref
:列与索引的比较rows:
扫描出的行数(估算的行数)filtered
:按表条件过滤的行百分比Extra
:执行情况的描述和说明
# 4.4 索引的优缺点
# ✔ 索引的优点:
- 索引大大减少了服务器需要扫描的数据量,从而加快检索速度。
- 支持行级锁的数据库,如 InnoDB 会在访问行的时候加锁。使用索引可以减少访问的行数,从而减少锁的竞争,提高并发。
- 索引可以帮助服务器避免排序和临时表。
- 索引可以将随机 I/O 变为顺序 I/O。
- 唯一索引可以确保每一行数据的唯一性,通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能。
# ❌ 索引的缺点:
- 创建和维护索引要耗费时间,这会随着数据量的增加而增加。
- 索引需要占用额外的物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立组合索引那么需要的空间就会更大。
- 写操作(
INSERT
/UPDATE
/DELETE
)时很可能需要更新索引,导致数据库的写操作性能降低。
# 4.5 索引效率对比试验:
创建t_index_users
表,字段:id
、user_name
、email
,并插入一百万条虚拟数据,进行索引效率对比试验。
INSERT INTO t_index_users ( user_name, email ) SELECT
CONCAT(
'USER_',
LPAD( rownum, 7, '0' )) username,
CONCAT( CONCAT( 'USER_', LPAD( rownum, 7, '0' )), '@qq.com' ) email
FROM
( SELECT ( @rownum := @rownum + 1 ) rownum FROM t_area, t_city,( SELECT @rownum := 0 ) t LIMIT 100000000 ) tmp;
2
3
4
5
6
7
100万数据测试的结果:有索引和没有的对比是相差600多倍。
# 测试方法:获得语句真正的执行时间
-- 打开记录
SET profiling = 1;
-- 执行查询
EXPLAIN SELECT * FROM t_index_users WHERE user_username = 'USER_0795058';
-- 显示查询的具体详情
SHOW PROFILES
2
3
4
5
6
-- 插入索引
CREATE INDEX idx_user_username ON t_index_users(user_username);
2
-- 打开记录
SET profiling = 1;
-- 执行查询
EXPLAIN SELECT * FROM t_index_users WHERE user_username = 'USER_0795058';
-- 显示查询的具体详情
SHOW PROFILES
2
3
4
5
6
# 4.6 何时使用索引
索引能够轻易将查询性能提升几个数量级。
✔ 什么情况适用索引:
- 表经常进行
SELECT
操作; - 表的数据量比较大;
- 列名经常出现在
WHERE
或连接(JOIN
)条件中
❌ 什么情况不适用索引:
- 频繁写操作(
INSERT
/UPDATE
/DELETE
)- 需要更新索引空间; - 非常小的表 - 对于非常小的表,大部分情况下简单的全表扫描更高效。
- 列名不经常出现在
WHERE
或连接(JOIN
)条件中 - 索引就会经常不命中,没有意义,还增加空间开销。 - 对于特大型表,建立和使用索引的代价将随之增长。可以考虑使用分区技术或 Nosql。
# 4.7 索引索引策略与基本原则
- 索引不是越多越好,不要为所有列都创建索引。
- 要尽量避免冗余和重复索引;
- 要考虑删除未使用的索引;
- 尽量的扩展索引,不要新建索引;
- 频繁作为
WHERE
过滤条件的列应该考虑添加索引
# 五、SQL函数过程
# 5.1 过程案例
创建SQL函数过程,完成转账操作:
CREATE PROCEDURE `p_trans` (
IN `fromid` INT,
IN `toid` INT,
IN `pbalance` DOUBLE,
OUT `flag` VARCHAR(50)
)
BEGIN
#定义变量,用于存储余额
DECLARE vbalance DOUBLE;
DECLARE result INT;
SET result = 0;
#1、查用户是否存在,查余额是否充足
SELECT balance
INTO vbalance
FROM t_account
WHERE id = fromid;
#2、如果余额充足,开启事务
IF vbalance - pbalance >= 0 THEN
START TRANSACTION;
#3、完成转出update操作
UPDATE t_account
SET balance = balance - pbalance
WHERE id = fromid;
IF ROW_COUNT() = 1 THEN
SET result = 1;
END IF;
#4、完成转入update操作
UPDATE t_account
SET balance = balance + pbalance
WHERE id = toid;
IF ROW_COUNT() = 1 THEN
SET result = 2;
END IF;
#5、判断受影响的行,如果=2 提交事务,给flag赋值
IF result = 2 THEN
SET flag = '转账成功,事务提交';
COMMIT;
ELSE
#6、如果!=2 回滚事务,给flag赋值
SET flag = '转账失败,事务回滚';
ROLLBACK;
END IF;
ELSE
#7、余额不足
SET flag = '转账失败,余额不足';
END IF;
END
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# 5.2 函数
# 六、触发器
# 6.1 触发器的概念
触发器是一种特殊的存储过程,由MySQL
自动执行(新增、修改、删除的时候,自动调用),用户没有办法自己执行。
触发器是与表(触发器建立在表上)有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。
- 注意:触发器只能创建在永久表上,不能对临时表创建触发器。
trigger_time
:触发时间,可以是BEFORE
或者AFTER
trigger_event
:触发事件,可以是INSERT
、UPATE
、DELETE
。
# 6.2 触发器的作用
- 审计对一个的表增、删、改的操作可以进行记录。
- 数据的合法性检查 比如银行账户的余额,可以限制大于0
- 对其它表的数据进行同步处理 对其它的表同步修改
# 6.3 两张特殊的表
old
和new
触发器执行的过程中才存在 ;修改理解为先删除,再增加
old
表保存的是 删除之前的数据会存在old
表中 ,修改之前的旧数据存在old
表中.
new
表保存的是 添加的新数据会存在new
表中 ,修改之前后新数据存在new
表中.
案例如下:
- 新建两张表:
dept
部门表和employee
员工表
# 1、在没有设置外键的情况下,使用触发器
dept
部门表:
deptid | deptname | deptremark |
---|---|---|
1 | 开发部 | 开发部 |
2 | 测试部 | 测试部 |
employee
员工表:
empid | empname | deptid |
---|---|---|
1 | 张欣 | 1 |
2 | 卓卓 | 1 |
3 | 方圆 | 2 |
在部门表添加一个触发器,这时部门表中的deptid
改变时,员工表中的deptid
也会跟着改变。
UPDATE employee SET employee.deptid = new.deptid WHERE deptid = old.deptid