事务
事务:一条或多条sql语句组成一个执行单位,一组sql语句要么都执行要么都不执行
特点(ACID)
- (Atomicity) 原子性:一个事务是不可再分割的整体,要么都执行要么都不执行
- (Consistency) 一致性:一个事务可以使数据从一个一致状态切换到另外一个一致的状态
- (Isolation) 隔离性:一个事务不受其他事务的干扰,多个事务互相隔离的
- (Durability) 持久性:一个事务一旦提交了,则永久的持久化到本地
并发事务(重点)
1、事务的并发问题是如何发生的?
多个事务 同时 操作 同一个数据库的相同数据时
2、并发问题都有哪些?
1 | 脏读:一个事务读取了其他事务还没有提交的数据,读到的是其他事务“更新”的数据 |
3、如何解决并发问题
通过设置隔离级别来解决并发问题
4、隔离级别
脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 | |
---|---|---|---|---|
read uncommitted:读未提交 | 是 | 是 | 是 | |
read committed:读已提交 | 否 | 是 | 是 | Oracle和SQL Server |
repeatable read:可重复读 | 否 | 否 | 是 | MySQL默认 |
serializable:串行化 | 否 | 否 | 否 |
事务的使用步骤
了解:
隐式(自动)事务:没有明显的开启和结束,本身就是一条事务可以自动提交,比如insert、update、delete
显式事务:具有明显的开启和结束
1 | 使用显式事务: |
隔离级别的演示
事物的隔离级别(读未提交):出现脏读
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17#查看隔离级别
select @@tx_isolation;
#设置隔离级别
set session|global transaction isolation level 隔离级别;
eg:
-- 设置隔离级别成为:读未提交。出现脏读(其他相对应改变)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTE;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
#结束事务
ROLLBACK;
#commit;
SELECT * FROM account;session|global 当前会话/全局 有效!建议使用session
问题:出现脏读,一个事物读取到了其他事物未提交的数据。
事物的隔离级别(读已提交):不会出现脏读,但是出现不可重复读
略
1
2-- 设置隔离级别成为:读可提交,不会出现脏读,但是会出现不可重复读。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;可以解决脏读问题:不会读取到其他事物未提交的数据。但是会出现不可重复读。
问题:出现不可重复读,一次事物中,多次读取到的数据不一样,读取到了别人已提交的数据。事物的隔离级别(可重复读):可重复读演示:不会出现不可重复读,会出现幻读
略
1
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
可以解决的问题:不会出现不可重复读,一次事物多次读取到数据是一样的。
问题:出现了幻读
事物的隔离级别(可串行化):serializable可串行化。所有问题都不会出现
1
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SQL四种语言
DDL(Data Definition Language)数据库定义语言
DDL不需要commit
1
2
3CREATE
ALTER
DROPDML(Data Manipulation Language 数据操纵语言
需要commit
1
2
3
4SELECT
INSERT
UPDATE
DELETEDCL(Data Control Language)数据库控制语言 授权,角色控制等
1
2GRANT 授权
REVOKE 取消授权TCL(Transaction Control Language)事务控制语言
1
2
3SAVEPOINT 设置保存点
ROLLBACK 回滚
SET TRANSACTION
内连接
内连接:内连接可整合多张表,获取相关关系或者公共部分的记录
内连接分为:隐式内连接,显式内连接
隐式内连接
1
select 列名 from 左表,右表 where 主表.主键=从表.外键
显式内连接
1
2
3-使用INNER JOIN ... ON语句, 可以省略INNER
select 列名 from 左表 inner join 右表 on 主表.主键=从表.外键 --显式内连接与隐式内连接查询结果是一样的**
select * from dept d inner join emp e on d.id = e.dept_id;
外连接
左外连接
左外连接:在内连接的基础上,保证左表中所有的记录都出现。相应记录使用NULL和它匹配。
左外连接:使用LEFT OUTER JOIN … ON,OUTER可以省略
1 | -- 需求:在部门表中增加一个销售部,需要查询所有的部门和员工,将部门表设置成左表,员工表设置成右表 |
右外连接
在内连接的基础上,保证右表中所有的数据都显示。左表中如果没有匹配的数据,使用NULL匹配。
1 | -- 需求:在员工表中增加一个员工:'沙僧','男',6666,'2013-02-24',null |
查询独有的数据
查询左边独有的数据
1 | Select <select_list> from tableA A Left Join tableB B on A.Key = B.Key where B.key IS NULL |
查询右表独有的数据
1 | Select <select_list> from tableA A Right Join tableB B on A.Key = B.Key where A.key IS NULL |
查询左右表各独有的数据
1 | Select <select_list> from tableA A Full Outter Join tableB B on A.Key = B.Key where A.key = null or B.key=null |
查询左右表全部的数据
1 | Select <select_list> from tableA A Full Outter Join tableB B on A.Key = B.Key |
表的级联
在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作 ON UPDATE CASCADE
– 级联更新,主键发生更新时,外键也会更新 ON DELETE CASCADE – 级联删除,主键发生删除时,从表关联的全部数据都会被直接删除。
1 | REATE TABLE employee ( |
存储引擎
1 | 查看存储引擎 |
1 | 查看系统当前支持的存储引擎,需要使用如下命令: |
创建新表时,如果不指定存储引擎,那么系统就会使用默认的存储引擎,MySQL 5.5之前的默认存储引擎是MyISAM , 5.5之后改为了InnoDB,如果需要修改存储引擎可以在核心配置文件中配置如下操作
default-storage-engine=INNODB
在创建表的时候,通过增加ENGINE关键字设置新表的存储引擎
1 | REATE TABLE `test1` ( |
也可以把一个已经存在的表的存储引擎,修改成其他表的存储引擎,操作如下
1 | alter table 表名 ENGINE = innoDB; |
常见存储引擎的区别
存储过程,函数
MySQL从5.0版本开始支持存储过程和函数。
存储过程和函数是事先经过编译和存储在数据库中的一段SQL语句的集合,然后直接通知调用执行即可, 所以调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值,而存储过程没有,存储过程的参数可以使用IN,OUT,INOUT类型,而函数的参数只能是IN类型的.
创建,删除,修改存储过程或者函数都需要权限,例如创建存储过程或者函数需要CREATE ROUNTINE权限,修改或者删除存储过程或者函数需要ALTER ROUTINE权限,执行存储过程或者函数需要EXECUTE权限。
一个简单的存储过程
创建存储过程/函数
1 | -- 创建存储过程 |
创建格式:create procedure 存储过程名
包含一个以上代码块,代码块使用begin和end 之间
在命令行中创建需要定义分隔符 delimiter $$
存储过程调用使用call命令
存储过程的特点:
能完成较复杂的判断和运算,而且处理逻辑都封装在数据库端,调用者不需要自己处理业务逻辑,一旦逻辑发生变化,只需要修改存储过程即可,而对调用者程序完全没有影响。
可编程性强,灵活
SQL编程的代码可重复使用
执行速度相对快一些
减少网络之间数据传输,节省开销
删除存储过程/函数
1 | -- 删除存储过程 |
1 | -- 查看存储过程或者函数的状态 |
存储过程的变量
语法
SET
直接赋值使用 SET,可以赋常量或者赋表达式
1
SET var_name = expr [, var_name = expr] ...
也可以通过select … into 方式进行赋值操作 :
1 | CREATE PROCEDURE pro_test5() |
DECLARE
通过 DECLARE 可以定义一个局部变量,该变量的作用范围只能在 BEGIN…END 块中。
1
DECLARE var_name[,...] type [DEFAULT value]
存储过程的变量
需求1: 编写存储过程,使用变量取id=2的用户名.
1 | DELIMITER $$ |
变量的声明使用declare,一句declare只声明一个变量,变量必须先声明后使用
变量具有数据类型和长度,与mysql的SQL数据类型保持一致,因此甚至还能指定默认值、字符集和排序规则等
变量可以通过set来赋值,也可以通过select into的方式赋值
变量需要返回,可以使用select语句,如:select 变量名
需求2:统计表users,student的行数量和student表中英语最高分,数学最高分的注册时间
1 | DELIMITER $$ |
变量是有作用域的,作用范围在begin与end块之间,end结束变量的作用范围即结束。
需要多个块之间传递值,可以使用全局变量,即放在所有代码块之前。
传参变量是全局的,可以在多个块之间起作用
存储过程的传入参数IN
需求:编写存储过程,传入id,返回该用户的name
1 | CREATE PROCEDURE getName(my_uid INT) |
传入参数:类型为IN,表示该参数的值必须在调用存储过程时指定,如果不显式指定为IN,那么默认就是IN类型。
IN类型参数一般只用于传入,在调用存储过程中一般不作修改和返回
如果调用存储过程中需要修改和返回值,可以使用OUT类型参数
存储过程的传出参数OUT
需求:调用存储过程时,传入uid返回该用户的uname
1 | CREATE PROCEDURE getName22(IN my_uid INT,OUT my_uname VARCHAR(32)) |
1.传出参数:在调用存储过程中,可以改变其值,并可返回
2.OUT是传出参数,不能用于传入参数值
调用存储过程时,OUT参数也需要指定,但必须是变量,不能是常量
如果既需要传入,同时又需要传出,则可以使用INOUT类型参数
存储过程的可变参数INOUT
需求:调用存储过程时,参数my_uid和my_uname,既是传入,也是传出参数
1 | CREATE PROCEDURE getName33(INOUT my_uid INT,INOUT my_uname VARCHAR(32)) |
可变变量INOUT:调用时可传入值,在调用过程中,可修改其值,同时也可返回值。
INOUT参数集合了IN和OUT类型的参数功能
INOUT调用时传入的是变量,而不是常量
存储过程条件语句
需求:编写存储过程,如果用户uid是偶数则就给出uname,其它情况只返回uid
1 | CREATE PROCEDURE getName44(IN my_uid INT ) |
条件语句最基本的结构:if() then …else …end if;
If判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式
需求:根据用户传入的uid参数判断: (1)如果状态status为1,则给用户score加10分 (2)如果状态status为2,则给用户score加20分 (3)其它情况加30分
1 | CREATE PROCEDURE addscore1(IN my_uid INT ) |
存储过程循环语句
while循环
需求:使用循环语句,向表uesrs中插入10条uid连续的记录。
1 | CREATE PROCEDURE insertdata() |
while语句最基本的结构:while() do…end while;
while判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式
repeat循环语句
需求:使用repeat循环向表users插入10条uid连续的记录
1 | CREATE PROCEDURE insertdata2() BEGIN |
repeat语句最基本的结构:repeat…until …end REPEAT;
until判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式,只有当until语句为真时,循环结束
光标的使用
在存储过程和函数中,可以使用光标(有时也称为游标)对结果集进行循环的处理,光标的使用包括了:
需求:编写存储过程,使用光标,把id为偶数的记录逐一更新用户名。
1 | -- 编写存储过程,使用光标,把id为偶数的记录逐一更新用户名。 |