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也可以用事务解锁; 行锁:通过事务解锁;
·行锁,写操作不同的数据,一次锁一行数据,因此,如果操作不同数据,不影响
行锁注意事项
·如果没有索引,行锁会转移为表锁
·行锁的一种特殊情况:间隙锁:值在范围内,但不存在