1.mysql常用聚合函数
分类：sum求和，avg平均数，max最大值，min最小值，count计算个数
简单使用：
sum求和： select sum(age) from test;
avg平均数：select ROUND(avg(age),2) from test;
min最小值：select min(age) from test;
max最大值：select max(age) from test;
count计算个数：select count(age) from test;
参数支持哪些类型：可以和distinct去重搭配
sum求和，avg平均数：数值类型
max最大值，min最小值：数值类型，日期类型，字符型
count计算个数：不为null的类型
count(1)和count(*)效率问题：
Innodb：count(1)和count(*)效率差不多，比count(字段)高
Myisam： count(*)效率最高
2.事务的ACID属性
原子性：事务不可再分割，事务的操作要么都执行，要么都不执行。
一致性：事务执行会使数据从一个一致状态切换到另一个一致状态。
隔离性：一个事务的执行不受其他事务的干扰。
持久性：事务一旦提交，无法改变。
3.事务的创建
·隐式事务：事务没有明显的开启和结束标识。比如：insert update delete
·显示事务：事务有明显的开启和结束标识。前提：必须先设置自动提交事务功能为禁止
Set autocommit = 0
-----------------------------------------------------------------------------
步骤1： Set autocommit = 0; start transaction;//可选
步骤2：编写事务的sql语句(select,insert,update,delete)
语句1,语句2......
步骤3：结束事务
set autocommit = 0;
start transaction;
Update account set balance = 500 where name=’郭襄’;
Update account set balance = 1500 where name=’张君宝’;
Commit;-------------------------------------------------------------------------------
4.数据库的隔离级别
对于 同时运行多个事务，当这些事务访问数据库相同的数据时，如果没有采取必要的隔离机制，，将会导致各种并发问题。
脏读：两个事务a，b，a读取了b更新但未提交的字段，若b回滚，a读取的内容是临时无效的。
不可重复读：两个事务a，b，a读取了表中一个字段，然后b更新了该字段之后，a再去读同一个字段，值就不同了。
幻读：两个事务a，b，a读取了表中一个字段，然后b在该表中插入了一行数据，之后a再次去读一个表时，就会多出几行。
数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使他们无相互影响，解决高并发问题。
----------------------------------------------------------------------------------------------------------------------------------------------
数据库的隔离级别：
读未提交数据（read uncommited）：允许事务读取未被其他事务提交的变更，脏读，不可重复读，幻读问题都会发生。
读已提交数据（read commited）：直允许事务读取已经被其他事务提交的变更，可避免脏读，但不可重复读，幻读问题会发生。
可重复读（repeatable read）：确保事务可以多次从一个字段中读取相同的值，在事务持续时间，禁止其他事务对这个字段更新，可避免脏读，不可重复读，但幻读仍然存在
串行化（serializable）：确保事务可以从一个表中读取相同的行，在事务持续时间，禁止其他事务进行更新，插入，删除操作，可避免所有并发问题，但效率低下。
---------------------------------------------------------------------------------------------------------------------
Oracle支持的隔离级别：读已提交数据（read commited），串行化（serializable）；默认的隔离级别：串行化（serializable）
mysql支持四种隔离级别，但默认的隔离级别为：可重复读（repeatable read）
---------------------------------------------------------------------------------------------------------------------------
Delete和truncate在事务使用时的区别
set autocommit = 0;
start transaction;
Delete from account；
Rollback; //数据已经删除，支持回滚；truncate不支持回滚
----------------------------------------------------------------------------------
5.视图：虚拟表，使通过表动态生成的数据。
创建视图语法：create view 视图名 as 查询的语句
用视图语法：select * from 视图名 where
CREATE VIEW userandrole AS
SELECT a.username uname, r.`name` rname, p.`name` as pname FROM USER a
LEFT JOIN user_role ur ON a.id = ur.uid
LEFT JOIN role r ON ur.rid = r.id
LEFT JOIN role_permission rp ON r.id = rp.rid
LEFT JOIN permission p ON p.id = rp.pid;
注意：必须指定查询的字段，当字段有相同时起别名。
SELECT * from userandrole WHERE uname ='zhuweilin'------------------------------------------------------------------------------------------------
视图的优点：重用mysql，简化复杂sql优化，保护数据提高安全性，但是只能用于不变的数据集
修改视图：
·create or replace view 视图名 as 查询语句
·alter 视图名 as 查询语句
CREATE OR REPLACE VIEW userandrole AS
SELECT a.username uname, r.`name` rname, p.`name` as pname FROM USER a
LEFT JOIN user_role ur ON a.id = ur.uid
LEFT JOIN role r ON ur.rid = r.id
LEFT JOIN role_permission rp ON r.id = rp.rid
LEFT JOIN permission p ON p.id = rp.pid;--------------------------------------------------------------------------------------------------
删除视图：
drop view 视图名,视图名...........
查看视图：
DESC 视图名
show create view 视图名
视图更新：更新视图的数据，也适用增删改，但是会影响其他表，视图内数据不允许增删改，包含以下关键字的sql语句不能进行操作：分组函数，distinct ,group by ,having，Union 或者 Union all
视图只占用少部分物理空间只是保存了sql逻辑，少于表占的物理空间
--------------------------------------------------------------------------------------------------------
6.存储过程和函数，类java中的方法
指一组预先编译好的sql语句的集合，理解成批处理的语句
·提高代码的重用性
·简化操作
·减少了编译次数并且减少了数据库服务器的连接次数，提高效率
创建语法：
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体
END
注意：
1.参数列表包括三部分：参数模式，参数名，参数类型。 比如：in stuname varchar(20)
参数模式：in让参数作为输入，也就是该参数需要调用方法传值
out 让参数作为输出，也就是该参数可以作为返回值
Inout 既让参数作为输入也让参数作为输出，既可以传值，又可以返回值
2.如果存储过程只有一句话，begin end 可以省略，存储过程体中的每条sql语句的结尾必须加分号。存储过程的结尾可以用重新设置。
语法：DELIMTER 结束标记 比如：DELIMTER $
调用语法：
Call 存储过程名（实参列表）
#创建无参数的存储过程
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO a_table VALUES(null,'hzz','hzz'),(null,'cmm','cmm');
END $
CALL myp1();#创建in模式参数的存储过程，支持传入多个参数
CREATE PROCEDURE myp12(IN teacher_name VARCHAR(20))
BEGIN
SELECT * FROM test_teacher tt WHERE tt.teacher_name = teacher_name ;
END
CALL myp12('柳岩')
支持传入多个参数
CREATE PROCEDURE myp3(IN uname VARCHAR(20) , IN pwd VARCHAR(20))
BEGIN
SELECT COUNT(*) INTO result //赋值
FROM uesr u WHERE u.uname = uname and u.pwd = pwd ;
END
CALL myp3('admin',admin)
创建带out模式的存储过程
CREATE PROCEDURE myp4(IN student_name VARCHAR(20),OUT student_sex VARCHAR(20))
BEGIN
SELECT ts.student_sex INTO student_sex
FROM test_student ts WHERE ts.student_name = student_name ;
END#定义输出变量
set @sex;
CALL myp4('张三',@sex)
SELECT @sex
------------------------------------------------------------------------------
游标的相关知识点：
# Cursor 游标 游标的标志
# 1条sql,取出的N条资源，取出的接口/句柄，就是游标
# 沿着游标，可以一次取出一行
# declare 声明；declare 游标名 cursor for select_statement;
# open 打开; open 游标名
# fetch 取值; fetch 游标名 into var1,var2[,.......]
# close 关闭;close 游标名;
-------------------------------------------------------------------------------------------------
游标为取一次游一次，若没有了则报错
CREATE PROCEDURE test()
BEGIN
DECLARE row_t_id INT; #声明游标之前声明变量
DECLARE row_t_teacher VARCHAR(20);
DECLARE getteacher CURSOR FOR #声明游标
SELECT tt.id,tt.teacher_name FROM test_teacher tt; #游标所需要的sql
OPEN getteacher #打开游标
FETCH getteacher INTO row_t_id,row_t_teacher; #取值
SELECT row_t_id,row_t_teacher; #查值
CLOSE getteacher; #关闭游标
END
CALL test();
如何循环游标所有行
CREATE PROCEDURE test2()
BEGIN
DECLARE cnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE row_t_id INT; #声明游标之前声明变量
DECLARE row_t_teacher VARCHAR(20);
DECLARE getteacher CURSOR FOR #声明游标
SELECT tt.id,tt.teacher_name FROM test_teacher tt;
SELECT COUNT(*) FROM test_teacher; #游标所需要的sql
OPEN getteacher ; #打开游标
REPEAT
SET i :=i+1;
FETCH getteacher INTO row_t_id,row_t_teacher; #取值
SELECT row_t_id,row_t_teacher ; #查值
UNTIL i >= cnt END REPEAT;
CLOSE getteacher; #关闭游标
END
CALL test2();#在mysql curor中可以DECLARE CONTINUE HANDLER来操作一个越界标识
#DECLARE CONTINUE HANDLER FOR NOT FOUND statement；
CREATE PROCEDURE test3()
BEGIN
DECLARE row_t_id INT; #声明游标之前声明变量
DECLARE row_t_teacher VARCHAR(20);
DECLARE you INT DEFAULT 1;
DECLARE getteacher CURSOR FOR #声明游标
SELECT tt.id,tt.teacher_name FROM test_teacher tt; #游标所需要的sql
DECLARE CONTINUE HANDLER FOR NOT FOUND SET you :=0;
OPEN getteacher ; #打开游标
REPEAT
FETCH getteacher INTO row_t_id,row_t_teacher; #取值
SELECT row_t_id,row_t_teacher ; #查值
UNTIL you=0 END REPEAT;
CLOSE getteacher; #关闭游标
END
CALL test3();为何多出来一行呢？如何修复？
解决后的方案
#DECLARE exit HANDLER FOR NOT FOUND statement
#CONTINUE 和exit区别:exit触发后后面的语句不再执行
#除了CONTINUE 和exit还有一个undo
#CONTINUE 是触发后，后面语句继续执行
#exit是触发后，后面语句不执行
#undo是触发后，前面语句撤销（目前mysql不支持）
CREATE PROCEDURE test4()
BEGIN
DECLARE row_t_id INT; #声明游标之前声明变量
DECLARE row_t_teacher VARCHAR(20);
DECLARE you INT DEFAULT 1;
DECLARE getteacher CURSOR FOR #声明游标
SELECT tt.id,tt.teacher_name FROM test_teacher tt; #游标所需要的sql
DECLARE EXIT HANDLER FOR NOT FOUND SET you :=0;
OPEN getteacher ; #打开游标
REPEAT #遍历
FETCH getteacher INTO row_t_id,row_t_teacher; #取值
SELECT row_t_id,row_t_teacher ; #查值
INSERT INTO test_class VALUES(null,row_t_teacher); #插入值
UNTIL you=0 END REPEAT;
CLOSE getteacher; #关闭游标
END
CALL test4();------------------------------------------------------------------------------------
# 不用exit也能解决问题
------------------------------------------------------------------------------------
# 不用exit也能解决问题
CREATE PROCEDURE test5()
BEGIN
DECLARE row_t_id INT; #声明游标之前声明变量
DECLARE row_t_teacher VARCHAR(20);
DECLARE you INT DEFAULT 1;
DECLARE getteacher CURSOR FOR #声明游标
SELECT tt.id,tt.teacher_name FROM test_teacher tt; #游标所需要的sql
DECLARE CONTINUE HANDLER FOR NOT FOUND SET you :=0;
OPEN getteacher ; #打开游标
FETCH getteacher INTO row_t_id,row_t_teacher;
REPEAT
SELECT row_t_id,row_t_teacher ;
FETCH getteacher INTO row_t_id,row_t_teacher; #查值
UNTIL you=0 END REPEAT;
CLOSE getteacher; #关闭游标
END
CALL test5();查询后的数据插入另一张表
#在mysql curor中可以DECLARE CONTINUE HANDLER来操作一个越界标识
#DECLARE CONTINUE HANDLER FOR NOT FOUND statement
CREATE PROCEDURE test4()
BEGIN
DECLARE row_t_id INT; #声明游标之前声明变量
DECLARE row_t_teacher VARCHAR(20);
DECLARE you INT DEFAULT 1;
DECLARE getteacher CURSOR FOR #声明游标
SELECT tt.id,tt.teacher_name FROM test_teacher tt; #游标所需要的sql
DECLARE EXIT HANDLER FOR NOT FOUND SET you :=0;
OPEN getteacher ; #打开游标
REPEAT
FETCH getteacher INTO row_t_id,row_t_teacher; #取值
SELECT row_t_id,row_t_teacher ; #查值
INSERT INTO test_class VALUES(null,row_t_teacher); #插入值
UNTIL you=0 END REPEAT;
CLOSE getteacher; #关闭游标
END
CALL test4();
函数的相关理解
例：创建一个函数，函数一定要有返回值
CREATE FUNCTION select_student(student_neme VARCHAR(20)) #定义函数select_student()
RETURNS INT #定义返回值
BEGIN
DECLARE res INT; #定义变量
IF student_neme IS NULL THEN
SET res = -1;
ELSE
SET res = 1;
END IF;
RETURN res;
END#调用函数
Select select_student(null) as res;
----------------------------------------------------------------------------------------------------------------
7.mysql锁机制：其目的使解决因资源共享而造成的并发问题
分类
1.操作类型
·读锁（共享锁）：对同一数据，多个操作可以同时进行，互不干扰
·写锁（互斥锁）：如果当前写操作没有完毕，则无法进行其他的读操作，写操作
2.操作范围：
·表锁：一次性对一个表整体加锁。如myISAM存储引擎使用表锁，开销小，加锁快，无死锁，但锁的范围大，容易发生冲突，并发度低。
·行锁：一次性对一个条数据加锁。如innoDB存储引擎使用行锁，开销大，加锁满，经常死锁，但锁的范围小，不易发生冲突，并发度高（很小概率发生高并发问题—>脏读，幻读，不可重复读，以及丢失数据问题）。
·页锁
加读锁
总结1：加读锁，如果一个会话 可以对表能进行读操作，不能进行写操作；且该会话对其他表不能进行读写操作。
会话0：Lock table tablelock read;
Select * from tablelock ; //读（查）可以
Delete from tablelock where id =1; //写（增删改）不可以
会话1： Select * from tablelock ; //读（查）可以
Delete from tablelock where id =1; //写（增删改）等待，不可以操作，需等待释放表锁。
会话3：
Select * from emp; //读（查）可以
Delete from emp where id =1; //写（增删改）可以
总结2：会话0给A表加锁，其他会话的操作：可以对A表之外的表进行任何操作
对A表的操作只能读操作，写操作需要等待A表释放锁。
释放锁：unlock tables;
加写锁
会话0：lock table tablelock write;
结论：当前会话，可对该表进行任何操作（增删改查）;但不能操作其他表。
其他会话：
结论：会话0中加锁的表，可以进行增删改查，但需等待会话0释放锁。
分析表锁定：
查看被锁表：show open tables; 1代表加了锁。
行锁（INNODB）
为了研究行锁，需关闭自动commit。Set autocommit = 0;
对行锁的一个小结：
·如果会话X对某条数据A进行DML操作（研究时：关闭commit的情况下），则在其他会话必须等待会话X结束事务（commit/roleback）后才能对数据a进行操作
·表锁是通过unlock tables也可以用事务解锁; 行锁：通过事务解锁；
·行锁，写操作不同的数据，一次锁一行数据，因此，如果操作不同数据，不影响
行锁注意事项
·如果没有索引，行锁会转移为表锁
·行锁的一种特殊情况：间隙锁：值在范围内，但不存在
