[数据库] 子查询与连接 (二)
1. 子查询
子查询常见应用场景：
子查询中不可以使用order by
子查询必须在一个小括号内
举例：
-- 子查询 SELECT * FROM employees WHERE emp_no in (SELECT emp_no FROM employees WHERE emp_no > 10015) ORDER BY emp_no DESC; SELECT emp.emp_no,emp.first_name,gender FROM (SELECT * from employees WHERE emp_no > 10015) AS emp WHERE emp_no < 10019 ORDER BY emp_no DESC;
查询结果1/2：
查询结果2/2：
2. 连接 join
select * from xxx AS A;
AS 可以省略，xxx可以是子查询语句，再结合join就可以绕过拦截逗号的情况
# 使用join的SQL注入语句，不允许使用逗号联合查询时使用join自己爆相同列数的位置 mysql> select * from ((select 1)A join (select 2)B join (select 3)C join (select 4)D join (select 5)E join (select 6)F join (select 7)H); +---+---+---+---+---+---+---+ | 1 | 2 | 3 | 4 | 5 | 6 | 7 | +---+---+---+---+---+---+---+ | 1 | 2 | 3 | 4 | 5 | 6 | 7 | +---+---+---+---+---+---+---+ 1 row in set (0.00 sec) mysql>
# 使用join的SQL注入语句，在爆出的位置上注入 mysql> select * from ((select group_concat(current_user(),session_user()))A join (select system_user())B join (select database())C join (select @@version_compile_os)D join (select version())E join (select user())F join (select @@datadir)H); +---------------------------------------------+----------------+------------+----------------------+-------------+----------------+-----------------+ | group_concat(current_user(),session_user()) | system_user() | database() | @@version_compile_os | version() | user() | @@datadir | +---------------------------------------------+----------------+------------+----------------------+-------------+----------------+-----------------+ | root@localhostroot@localhost | root@localhost | gogs | Linux | 5.5.45-37.4 | root@localhost | /var/lib/mysql/ | +---------------------------------------------+----------------+------------+----------------------+-------------+----------------+-----------------+ 1 row in set (0.00 sec) mysql>
笛卡尔乘积： M*N cross join
两个集合全部交叉连接，基本不用。
举例：
-- 连接join -- 交叉连接cross join -- 笛卡尔乘积，全局交叉 -- 在MySQL中，cross join从语法上说与inner join等同 -- 工资40行 SELECT * FROM salaries; -- 员工20行 SELECT * from employees; -- 800行 SELECT * FROM employees CROSS JOIN salaries
查询结果1/3：
查询结果2/3：
查询结果3/3：
3.连接join
3.1 内连接： INNER JOIN
内连接相当于两张表做笛卡尔乘积
3.2 等值连接
只查询两张表等值条件都符合的列的记录
3.3 自然连接
隐藏了两张表的重复列emp_no，值显示一个
举例：
-- 内连接 INNER JOIN，可以省略为join SELECT * FROM employees INNER JOIN salaries; -- 等值连接 SELECT * FROM employees INNER JOIN salaries ON employees.emp_no=salaries.emp_no; -- 自然连接 SELECT * FROM employees NATURAL JOIN salaries; -- 自定义类的筛选 SELECT employees.*,salaries.salary FROM employees INNER JOIN salaries ON employees.emp_no=salaries.emp_no;
查询结果1/4：
查询结果2/4：
查询结果3/4：
查询结果4/4：
4. 外连接：OUTER JOIN
外连接分为左右外连接、也叫左右连接
左右连接时，两个表的记录和行数不能完全匹配时会用NULL补充
举例：
-- 左连接 SELECT * FROM employees LEFT JOIN salaries ON employees.emp_no=salaries.emp_no; -- 右连接 SELECT * FROM employees RIGHT JOIN salaries ON employees.emp_no=salaries.emp_no; -- 右连接两张表互换顺序等于上面的左连接 SELECT * FROM salaries RIGHT JOIN employees on employees.emp_no=salaries.emp_no;
查询结果1/3：
查询结果2/3：
查询结果3/3：
5. 自连接：表自己和自己连接
-- 自连接 SELECT B.emp_no,B.first_name FROM employees AS A,employees AS B WHERE A.emp_no=B.emp_no;
查询结果1/1：
事务Transaction:
ACID 属性
若干条语句组成的一组动作，就是事务
| 原子性(Atomicity) | 打包好的一组动作不可分割，要么全部执行，要么全部不执行 |
| 持久性(Durability) | 永久性，一旦提交，断电恢复也会正常保存，数据更改完整留存下来，永久的存储在磁盘上 |
| 一致性(Conistency) | 两个状态之间的变化，原子性操作，不能出现中间状态，一个状态到另一个状态，必须保证的。 |
| 隔离性(Isolation) |
不能看到中间正在变化的状态，对数据的访问是否隔离 |
一个数据库必须保证原子性、持久性、一致性，但隔离性可以调整。
隔离性不好，带来的问题：
1.更新丢失 Lost Update:
就是做的修改没了，两个事务同时更新一个数据，都读取了初值，A要减10，B要加100，A减去10后更新为90，B加100后更新为200，A的更新就丢失了。
事务并发时，结果不确定
2.脏读
事务A和B，事务A做完了提交，事务B读取到了B的一个未提交的数据，这个中间值就称为脏数据，这个动作就称为脏读。
读到的是没有真正提交的，而是一个临时值。
隔离做的不好，读到了临时数据。
3.不可重复读 Unrepeatable read
任何一个时机，不能保证同一条查询语句得到相同的结果
场景：事务A在读，事务B在修改
4.幻读 Phantom read
特殊的不可重复读，增加了不可重复数据读，
读到了多个不同的数据。
读到数据有增加，
隔离级别(从低到高)：
| READ UNCOMMITED | 读取到未提交的数据 |
| READ COMMITTED | 读取已经提交的数据，Oracle的默认级别 |
| REPEATABLE READ | 可以重复读。MySQL的默认级别 |
| SERIALIZABLE | 可串行化。事务间完全隔离，事务不能并发，只能串行执行 |
隔离级别越高，串行化越高，数据库执行效率越低；
隔离级别越低，并行化越高，性能越高；
隔离级别越高，当前事务处理的中间结果对其它事务不可见程度越高。
设置隔离级别：
SESSION 和 GLOBAL表示隔离级别作用在全局还是当前会话
-- 查看会话隔离级别
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
-- 设置会话级或全局隔离级别
-- SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
-- 举例：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
查询结果1/2：
查询结果2/2：
各隔离级别可以解决的问题：
事务语法：
开始：BEGIN
回滚：ROLLBACK
提交：COMMIT 将记录永久保存下来
SET AUTOCOMMIT，出错自动回滚，一般会关闭它，性能问题，批量更改一次提交
数据仓库和数据库的区别：
一个大的数据库，
分库，分表
纵向、横向
OLAP：A(分析) 事务如何分析，往往囤积历史数据做分析，不做增删改修改。
OLTP：T(事务)在线事务系统，一致性完整性，需要频繁增删改查，
游标：指针
指向操作到了哪一行
存储过程：
函数，一组SQL语句，支持流程控制，传参
触发器：在表上添加
当条件满足时，自动调用一段SQL语句或存储过程
存储过程和触发器一般不常用，触发器会带来性能问题，Oracle中的触发器生成序列
隔离级别：
1.可重复读
2.读已提交的：
会话1和会话2都检查一下当前会话和全局隔离级别，然后关闭自动提交： mysql> select @@global.tx_isolation; mysql> select @@tx_isolation; mysql> set autocommit=0; 会话1设置SESSION隔离级别为可已读已提交的： mysql> set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec) 会话2，插入一条数据并提交： mysql> INSERT INTO employees VALUE(11111,'2018-01-05','zhang','san','M','2018-01-01'); mysql> commit; 回到会话1，就可以读取到已经提交的数据： mysql> select * from employees; +--------+------------+------------+-------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-------------+--------+------------+ | 10020 | 1952-12-24 | Mayuko | Warwick | M | 1991-01-26 | | 11111 | 2018-01-05 | zhang | san | M | 2018-01-01 | +--------+------------+------------+-------------+--------+------------+
3.上锁（排它锁）：
#会话1 mysql> INSERT INTO employees VALUE(44444,'2018-01-01','wang','wu','F','2018-01-02'); mysql> select * from employees for update; #上锁 #会话2操作此表时就会阻塞，直到返回超时异常 mysql> update employees set last_name='erdan' where emp_no=44444; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction #如果会话2阻塞的同时，会话1提交了他的操作，会话2才能继续操作该表 mysql> commit; #会话2才能更新成功 mysql> update employees set last_name='erdan' where emp_no=44444; Query OK, 1 row affected (5.43 sec) Rows matched: 1 Changed: 1 Warnings: 0
Inodb支持行级锁
