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或者AFTERtrigger_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