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进程进行回收。