数据库二

MySQL常用函数

MySQL常用函数

SELECT ABS(-8); -- 绝对值

SELECT CEILING(9.4) ;-- 向上取整

SELECT FLOOR(9.4);-- 向下取整

SELECT RAND(); -- 返回一个0~1之间的随机数

SELECT SIGN(-10); -- 判断一个数的符号，0 返回0 负数返回-1 正数返回1

-- 字符串函数

SELECT CHAR_LENGTH('哈哈'); -- 字符串长度

SELECT CONCAT('我','爱','你'); -- 拼接字符串

SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); -- 插入，替换

SELECT LOWER('ZYY'); -- 小写字母

SELECT UPPER('zyy'); -- 大写字母

SELECT INSTR('zyy','y'); -- 返回第一次出现的子串的索引

SELECT REPLACE('坚持就能成功','坚持','努力'); -- 替换出现的指定字符串

SELECT SUBSTR('坚持就能成功', 5, 2); -- 返回指定的子字符串（源字符串，截取的位置，截取的长度）

SELECT REVERSE('清晨我上马'); -- 反转-- 时间和日期函数（记住！）

SELECT CURRENT_DATE(); -- 获取当前日期

SELECT CURDATE(); -- 获取当前日期

SELECT NOW(); -- 获取当前的时间

SELECT LOCALTIME(); -- 获取本地时间

SELECT SYSDATE(); -- 获取系统时间

SELECT YEAR(NOW()); -- 年

SELECT MONTH(NOW()); -- 月

SELECT DAY(NOW()); -- 日

SELECT HOUR(NOW()); -- 时

SELECT MINUTE(NOW()); -- 分

SELECT SECOND(NOW()); -- 秒

-- 系统

SELECT SYSTEM_USER();

SELECT USER();

SELECT VERSION();

---

自连接

自己的表和自己的表连接，核心：==一张表拆为两张一样的表即可==

-- 创建表

-- unsigned 无符号

-- auto_increment=9 自增的起始值

DROP TABLE IF EXISTS `category` ;

CREATE TABLE `category` (  

`category_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',  `pid` INT(10) NOT NULL COMMENT '父id',

 `category_name` VARCHAR(50) NOT NULL COMMENT '主题名字',  PRIMARY KEY (`category_id`)

)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- 插入值

INSERT INTO `category`(`category_id`,`pid`,`category_name`)

VALUES('2','1','信息技术'),

('3','1','软件开发'),

('4','3','数据库'),

('5','1','美术设计'),

('6','3','web开发'),

('7','5','ps技术'),

('8','2','办公信息');

SELECT * FROM `category`;

父类

pid	category_id	category_name
1	2	信息技术
1	3	软件开发
1	5	美术设计

子类

pid	category_id	category_name
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息，把一张表看为两个一模一样的表

SELECT a.`category_name` AS '父栏目',  b.`category_name` AS '子栏目 '

FROM `category` AS a, `category` AS b

WHERE a.`category_id`=b.`pid`;

---

排序

-- 排序： 升序 ASC  降序 DESC

-- ORDER BY 通过那个字段排序，怎么排

-- 查询的结果根据成绩降序 排序

SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`

FROM student stu

INNER JOIN `result` res

ON stu.`student_no` = res.`student_no`

INNER JOIN `subject` sub

ON res.`subject_no`=sub.`subject_no`

WHERE sub.`subject_name`='数据结构-1'

ORDER BY `student_result` DESC;

事务ACID原则、脏读、不可重复读、幻读

1.什么是事务

==要么都成功，要么都失败==

将一组sql放到一个批次中取执行

事务原则：ACID原则 原子性 、一致性、隔离性、持久性 （脏读，幻读。。。）

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据完整性要保持一致

下图操作前和操作后的总和都是1000

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）

事务一旦移交不可逆，被持久化到数据库中隔离所导致的一些问题

脏读：

1、在事务A执行过程中，事务A对数据资源进行了修改，事务B读取了事务A修改后的数据。

2、由于某些原因，事务A并没有完成提交，发生了RollBack操作，则事务B读取的数据就是脏数据。

这种读取到另一个事务未提交的数据的现象就是脏读(Dirty Read)。

不可重复读：

事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致。

这种**==在同一个事务中==，前后两次读取的数据不一致的现象就是不可重复读(Nonrepeatable Read)。**

虚读(幻读)

事务B前后两次读取同一个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后一次读取到前一次查询没有看到的行。

幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新。

第一类更新丢失

事务A和事务B都对数据进行更新，但是事务A由于某种原因事务回滚了，把已经提交的事务B的更新数据给覆盖了。这种现象就是第一类更新丢失。

第二类更新丢失

其实跟第一类更新丢失有点类似，也是两个事务同时对数据进行更新，但是事务A的更新把已提交的事务B的更新数据给覆盖了。这种现象就是第二类更新丢失。

事务隔离级别

为了解决以上的问题，主流的关系型数据库都会提供四种事务的隔离级别。事务隔离级别从低到高分别是：读未提交，读已提交，可重复读，串行化。事务隔离级别越高，越能保证数据的一致性和完整性，但是执行效率也越低，所以在设置数据库的事务隔离级别时需要做一下权衡，mysql默认是可重复读

读未提交

读未提交(Read Uncommitted)，是最低的隔离级别，**所有的事务都可以看到其他未提交的事务的执行结果。**只能防止第一类更新丢失，不能解决脏读，可重复读，幻读，所以很少应用于实际项目。

读已提交

读已提交(Read Committed)，在该隔离级别下，**一个事务的更新操作只有在该事务提交之后，另外一个事务才可能读取到同一笔数据更新后的结果。**可以防止脏读和第一类更新丢失，但是不能解决可重复和幻读的问题。

可重复读（重要）

可重复读(Repeatable Read)，mysql默认的隔离级别。在该隔离级别下，一个事务多次读同一个数据，在这个事务还没有结束时，其他事务不能访问该数据（包括了读写），这样就可以在同一个事务内两次读到的数据是一样的。可以防止脏读、不可重复读、第一类更新丢失，第二类更新丢失的问题，不过还是会出现幻读。

串行化

串行化(Serializable)，这是最高的隔离级别。它要求事务序列化执行，事务只能一个接着一个的执行，不能并发执行。在这个级别，可以解决上面提到的所有并发问题，但是可能导致大量的超时现象和锁竞争，通常不会用这个隔离级别。

总结

扩展：回滚机制

在mysql中，恢复机制是通过回滚日志（undo log）实现的，所有的事务进行的修改都会先记录到这个回滚日志中，然后在堆数据库中的对应进行写入。

mysql的事务是由redo和undo的，redo操作的所有信息都是记录到重做日志（redo_log）中，也就是说当一个事务做commit操作时，需要先把这个事务的操作写到redo_log中，然后在把这些操作flush到磁盘上，当出现故障时，只需要读取redo_log，然后在重新flush到磁盘就行了。

而对于undo就比较麻烦，mysql在处理事务时，会在数据共享表空间里申请一个段就做segment段，用保存undo信息，当在处理rollback，不是完完全全的物理undo，而是逻辑undo，也就是说会之前的操作进行反操作（对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。），但是这些共享表空间是不进行回收的。这些表空间的回收需要由mysql的master thread进程进行回收。