数据库

数据库事务

1. 定义

​ 1）数据库事务是构成单一逻辑工作单元的操作集合，包含一个或多个数据库操作，这些操作构成一个逻辑上的整体。

​ 2）一个典型的数据库事务如下所示

1
2
3
4

BEGIN TRANSACTION  //事务开始
SQL1
SQL2
COMMIT/ROLLBACK   //事务提交或回滚

2. 事务的ACID特性

​ 1）**原子性(Atomicity)**：事务中的所有操作作为一个整体像原子一样不可分割，要么全部成功，要么全部失败。

​ 2）**一致性(Consistency)**：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。

​ 3）**隔离性(Isolation)**：并发执行的事务不会相互影响，其对数据库的影响和它们串行执行时一样。比如多个用户同时往一个账户转账，最后账户的结果应该和他们按先后次序转账的结果一样。

​ 4）**持久性(Durability)**：事务一旦提交,其对数据库的更新就是持久的。任何事务或系统故障都不会导致数据丢失。

一致性状态：

1. 系统的状态满足数据的完整性约束(主码,参照完整性,check约束等)

2. 系统的状态反应数据库本应描述的现实世界的真实状态,比如转账前后两个账户的金额总和应该保持不变。

数据库的脏读、幻读、不可重复读

1. 脏读：

​ 1）指一个事务A正在访问数据，并且对该数据进行了修改，但是这种修改还没有提交到数据库中（也可能因为某些原因Rollback了）。这时候另外一个事务B也访问这个数据，然后使用了这个被A修改的数据，那么这个数据就是脏的，并不是数据库中真实的数据。这就被称作脏读。

​ 2）解决办法：把数据库事务隔离级别调整到READ_COMMITTED

​ 3）即让用户在更新时锁定数据库，阻止其他用户读取，直到更新全部完成才让你读取。

2. 幻读：

​ 1）指一个事务A对一个表中的数据进行了修改，而且该修改涉及到表中所有的数据行；同时另一个事务B也在修改表中的数据，该修改是向表中插入一行新数据。那么经过这一番操作之后，操作事务A的用户就会发现表中还有没修改的数据行，就像发生了幻觉一样。这就被称作幻读。

​ 2）解决办法：把数据库事务隔离级别调整到SERIALIZABLE_READ

3. 不可重复读：

​ 1）指在一个事务A内，多次读同一个数据，但是事务A没有结束时，另外一个事务B也访问该同一数据。那么在事务A的两次读数据之间，由于事务B的修改导致事务A两次读到的数据可能是不一样的。这就发生了在一个事务内两次读到的数据不一样，这就被称作不可重复读。

​ 2）解决办法：把数据库事务隔离级别调整到REPEATABLE_READ

注：

级别高低：脏读 < 不可重复读 < 幻读

所以设置了最高级别的SERIALIZABLE_READ就不需要设置其他的了，即解决了幻读问题那么脏度和不可重复读自然就都解决了。

索引

1. 为什么需要索引？

​ 1）索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据，解决效率低问题

2. 优点

​ 1）可以通过建立唯一索引或者主键索引，保证数据库表中每一行数据的唯一性

​ 2）建立索引可以大大提高检索的数据，以及减少表的检索行数

​ 3）在表连接的连接条件，可以加速表与表直接的相连

​ 4）在分组和排序字句进行数据检索，可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)

​ 5）建立索引，在查询中使用索引 可以提高性能

3. 索引采取什么样的结构

​ 1）hash(哈希)

​ 2）二叉树

​ 3）红黑树

​ 4）B+树

SQL基础

1. 多表联合查询

1

SELECT * FROM py_user u, py_paths p where u.userUUID = p.userUUID;

2. 内连接查询（与多表联合查询效果一样）

1

SELECT * FROM py_user u INNER JOIN py_paths p ON u.userUUID = p.userUUID;

3. 左外连接查询 (左边表中的数据优先全部显示)

1

SELECT * FROM py_user u LEFT JOIN py_paths p ON u.userUUID = p.userUUID;

4. 右外连接查询 (右边表中的数据优先全部显示)

1

SELECT * FROM py_user u RIGHT JOIN py_paths p ON u.userUUID = p.userUUID;

5. 全连接查询(显示左右表中全部数据,使用 UNION 可以间接实现 full JOIN 功能)

1
2
3

SELECT * FROM py_user u LEFT JOIN py_paths p ON u.userUUID = p.userUUID;
UNION
SELECT * FROM py_user u RIGHT JOIN py_paths p ON u.userUUID = p.userUUID;

6. 子查询(嵌套查询)： 查多次，多个select

​ 1）第一次的查询结果可以作为第二次的查询的条件或者表名使用.

​ 2）子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字. 还可以包含比较运算符：= 、 !=、> 、< 等

1

select * from (select * from person) as 表名;

7. ORDER BY 排序，ASC默认升序，降序后面接”DESC”即可

1

SELECT * FROM table_name ORDER BY column_name1, column_name2 DESC;

8. GROUP BY 分组，count(*) 包括所有列，返回表中的记录数

1
2
3

SELECT u.userName, COUNT(*) FROM py_user u, py_paths p WHERE u.userUUID = p.userUUID GROUP BY regEmail;

SELECT u.userName, COUNT(*) FROM py_user u INNER JOIN py_paths p ON u.userUUID = p.userUUID GROUP BY regEmail;

9. AVG() 求平均工资

1

SELECT AVG(salary) FROM person;

10. MAX() 求最大工资

1

SELECT MAX(salary) FROM person;

11. MIN() 求最小工资

1

SELECT MIN(salary) FROM person;

12. HAVING 和 WHERE 的区别

​ 1）作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组。

​ 2）WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。

​ 3）WHERE 子句不能包含聚集函数，HAVING 子句总是包含聚集函数。

​ 4）HAVING 一般跟在 GROUP BY 之后，执行记录组选择的一部分来工作的。WHERE 则是执行所有数据来工作的

​ 5）HAVING 可以用聚合函数，如 HAVING SUM(salary) > 1000

1

SELECT city FROM weather WHERE temp_lo = (SELECT MAX(temp_lo) FROM weather);