MySQL子查询，函数，事务

一、子查询

-- 子查询
--  select子查询（外语句先执行，后执行子语句）
-- 	查询课程表并显示课程老师的名称
	SELECT *,(SELECT tname FROM teacher t WHERE t.tid=c.teacher_id) name FROM course c;
	
-- 	where 子查询
-- 查询学了体育课程的学生
SELECT st.* FROM student st WHERE st.sid in(
	SELECT sc.student_id FROM score sc
	LEFT JOIN course co on co.cid=sc.course_id
	WHERE co.cname = '体育'
)
 
-- from子查询（先执行子查询，再执行）
-- 		列出三年二班学了体育的学生
		SELECT a.* FROM(SELECT st.sname,cl.caption FROM score sc
				LEFT JOIN course co on co.cid = sc.course_id
				LEFT JOIN student st on st.sid = sc.student_id
				LEFT JOIN class cl on cl.cid = st.class_id
				WHERE co.cname = '体育') a WHERE a.caption='三年二班';
				
-- exists 子查询（可以用来代替in） （先执行外语句，再执行子查询，根据子查询是否返回结果行来决定外查询结果行是否保留）
-- 			查询学了课程id为1的学生的姓名
			SELECT * FROM student st WHERE st.sid in(
			SELECT sc.student_id FROM score sc WHERE sc.course_id = '1'
			)
			SELECT * FROM student st WHERE EXISTS(
				SELECT * FROM score sc WHERE sc.course_id='1' and sc.student_id = st.sid
			)
			
 

快捷用法进行排编号
SELECT st.*,@num:=@num+1 FROM student st,(SELECT @num:=0) a;

二、函数

-- 函数
-- 	数字函数
-- (1.round四舍五入，2.rand随机数范围0-1)
		SELECT ROUND(4.49); -- 四舍五入
		SELECT RAND(); -- 随机数范围是0-1
		
-- 	字符串函数
-- （1.concat拼接，2.substring截取 from 从几开始 for 几个内容，3.reverse翻转）
	SELECT CONCAT('1','2'); -- 拼接
	SELECT SUBSTRING('我今天学习了数据库' FROM 1 FOR 4); -- FROM 索引（从 1开始） for 几个
	SELECT REVERSE('hello'); -- 翻转
	
-- 	日期时间 
-- （1.now（）当前时间，2.date_format(时间，'%Y%m%d %H%i%s')）
		SELECT now(); -- 当前时间
		SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s');
		
-- 		条件判断
-- 			IFNULL(要找的数,如果为null替换成)
			SELECT st.sname,sc.course_id,IFNULL(num,0) FROM student st
			LEFT JOIN score sc on sc.student_id = st.sid
			WHERE st.class_id = 3;
-- 			IF(条件,为真 执行1,为假 执行2)
			SELECT IF(YEAR(NOW())=2022,'是2022','不是2022年');
			
-- 			系统信息 version看版本
			SELECT VERSION();
			
-- 			加密函数 md5
			SELECT PASSWORD('aaa');
			SELECT MD5('123');
			
-- 			其他函数 format（数，保留几位小数）
			SELECT FORMAT('6.6666',2); -- 保留几位小数

三、事务

事务(Transaction)，一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。在计算机术语中，事务通常就是指数据库事务。

数据库事务的特性（重点，面试必考）

事务具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。 （总和不变） 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。例如：张三向李四转500，王五向赵六转1000，两者互不影响 持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。

事务的实现方式：

①多线程

②日志

事务的操作：

INNODB是支持事务的引擎，而myisam引擎是不支持事务的

开启事务： begin

提交事务：commit

回滚事务：rollback

insert into t1(name,money) VALUES ('张三','1000'),('李四','1000');
​
SELECT * FROM t1;
​
-- 张三给李四转500块钱
​
-- 模拟张三
-- 开启事务
begin;
UPDATE t1 set money = money-500 WHERE id = '1';
UPDATE t1 set money = money+500 WHERE id = '2';
​
-- 回滚事务
ROLLBACK;
​
-- 提交事务
COMMIT;

一、事务的隔离级别

1.并发事务带来的问题：

更新丢失：事务A和事务B同时操作，事务B覆盖了事务A做的操作，导致事务A的更新丢失了

脏读：事务A读取到事务B还未提交的事务

不可重读：事务A在同一事务的不同时间段内，读取同一数据，得到的值不一样（读取到其他事务修改的值）

幻读：事务A在同一事务的不同时间段内，第一次读取的结果行与第二次读取的结果行数不一样（读取到其他事务新增的内容）

2.事务的隔离级别：

读未提交：能读取到其它事务未提交的数据

读已提交：只能读取到其他事务已提交的数据

可重复读（默认隔离）：事务A在同一事务的不同时间段内，读取同一数据，得到的值一样（即使其它事务修改了该值）

串行化：事务依次执行，不存在并发问题

二、数据库锁

1.共享锁/排他锁：数据访问，

读锁/写锁

2.乐观锁/悲观锁

乐观锁：对事务保持乐观态度，认为不会有其它事务来修改数据，仅在更新前查询是否修改

悲观锁：对事物保持悲观态度，认为一定有其它事务来修改数据，所以操作时会加锁，不让其它事务操作。

3.表锁/行锁

行锁：一次锁1~n行数据

表锁：一次锁整张表