SQL介绍—数据事务语言(TCL)、SQL进阶

目录

TCL (Transaction Control Language)

存储引擎

事务

视图

SQL进阶

变量

存储过程与函数

流程控制结构

TCL (Transaction Control Language)

---

存储引擎

概述：在mysql中的数据用各种不同的技术存储在文件(或内存)中。

查看mysql支持的存储引擎：show engines;

mysql中使用最多的存储引擎：innodb(支持事务)、myisam(不支持事务)、memory(不支持事务)。

事务

概述：一条或多条sql语句组成一个执行单位，一组sql语句要么都执行，要么都不执行
事务的ACID特点	1. 原子性 (Atomicity)：事务是不可分割的最小操作单位，要么同时成功，要么同时失败 2. 一致性 (Consistency)：保证数据的状态操作前和操作后保持一致 3. 隔离性 (Isolation)：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰 4. 持久性 (Durability)：当事务提交或回滚后，数据库会持久化的保存数据
分类	隐式事务	概述：没有明显的开启和结束事务的标志，本身就是事务自动提交 insert、update、delete语句本身就是一个事务
	显式事务	概述：具有明显的开启和结束事务的标志 1. 取消自动提交事务：set autocommit=0; (默认开启事务) 2. 开启事务：start transaction; (可以省略)     事务中的sql语句 (select、insert、update、delete)     设置回滚点：savepoint 回滚点名(自定义); 3. 提交或回滚事务：     提交- commit;     回滚- rollback;     回滚到指定的地方：rollback to 回滚点名;
隔离级别	事务并发问题是如何产生的？        - 当多个事务同时操作同一个数据库的相同数据时
	事务并发问题	脏读：一个事务读取到了另外一个事务未提交的数据，读到的是其他事务"更新"的数据 不可重复读：同一个事务中，多次读取到的数据不一致 幻读：第一个事务读取到了第二个更新事务没有更新的数据，读到的是其他事务"插入"的数据
	处理事务并发问题，设置事务隔离级别   - READ UNCOMMITTED：出现脏读、不可重复读、幻读   - READ COMMITTED：可以避免脏读 (oracle默认)   - REPEATABLE READ：可以避免脏读、不可重复读 (mysql默认)   - SERIALIZABLE(串行化)：可以避免脏读、不可重复读和幻读
	设置当前MySQL隔离级别：set session|global  transaction isolation level 隔离级别名; 设置全局MySQL隔离级别：set global  transaction isolation level 隔离级别名; 查看隔离级别：select @@tx_isolation;
	注意：隔离级别从小到大安全性越来越高，但是效率越来越低

视图

概述：一张虚拟的表，只保存了sql逻辑，不保存查询结果。mysql5.1版本出现的新特性，是通过表动态生成的数据
应用场景	1. 多个地方用到同样的查询结果 2. 该查询结果使用的sql语句较复杂
好处	1. sql语句提高重用性，效率高 2. 简化复杂的sql操作，不必知道细节 3. 表实现了分离，保护数据，提高了安全性
创建视图：create view  视图名 as 查询语句; 修改视图：     方式一：create or replace view 视图名 as 查询语句;     方式二：alter view 视图名 as 查询语句; 删除视图：drop view 视图名1,视图名2...; 查看视图：     desc 视图名;      show create view 视图名;
视图数据的操作	查看视图数据：select 查询字段 from 视图名; 插入视图数据：insert into 视图名(视图字段) values(值); 修改视图数据：update 视图名 set 视图字段名=值 where 条件; 删除视图数据：delete from 视图名 where 条件;
注意事项	视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的 包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all     - 常量视图     - Select中包含子查询     - join     - from一个不能更新的视图     - where子句的子查询引用了from子句中的表
视图与表的区别	视图创建语法关键字create view，不占用物理空间 (保存的是SQL逻辑)，使用增删改查，一般不能增删改
	表创建语法关键字create table，占用物理空间 (保存了数据)，使用增删改查

SQL进阶

---

变量

系统变量	概述：变量由系统提供，不是用户定义，属于服务器层面
	全局变量	作用域：必须拥有super权限才能为系统变量赋值，作用域为整个服务器，针对于所有的 会话(连接)有效，服务器每次启动将为所有的全局变量赋初始值，但不能跨重启
	会话变量	作用域：仅仅针对于当前会话(连接)有效
	查看所有的系统变量：show global(全局) |【session(会话)】variables; 查看满足条件的部分系统变量：show global |【session】variables like ' '; 查看指定的某个系统变量的值：select @@global |【session】.系统变量名; 为某个系统变量赋值：     方式一：set global |【session】系统变量名=值;     方式二：set @@global |【session】.系统变量名=值; 注意：全局变量加global，会话级别加session，默认为session
自定义变量	概述：变量是用户自定义的，不是系统的
	使用步骤：声明、赋值、使用(查看、比较、运算)
	用户变量	作用域：针对于当前会话(连接)有效，同于会话变量的作用域，应用在任何地方，也就是 begin end里面或外面，不限定类型
		赋值的操作符：=或 := 1. 声明并初始化 set @用户变量名=值; 或 set @用户变量名 :=值; 或 select @用户变量名 :=值;
		2. 赋值 (更新用户变量的值) 方式一： 通过set或select set @用户变量名=值; 或 set @用户变量名 :=值; 或 select @用户变量名 :=值; 方式二： 通过select into select 字段 into @变量名 from 表;
		3. 使用(查看用户变量的值) select @用户变量名;
	局部变量	作用域：仅仅在定义它的begin end中有效，应用在begin end中的第一句话，限定类型
		1. 声明：declare 变量名 类型 【default 值】;
		2.赋值 方式一： 通过set或select set @用户变量名=值; 或 set @用户变量名 :=值; 或 select @用户变量名 :=值; 方式二： 通过select into select 字段 into 局部变量名 from 表;
		3.使用(查看信息)：select 局部变量名;

存储过程与函数

概述：一组经过预先编译的sql语句的集合(批处理语句)。

好处：提高了sql语句的重用性；提高了效率；减少了传输次数。

存储过程	创建语法	create procedure 存储过程名(参数模式 参数名 参数类型) begin     存储过程体(一组合法的SQL语句) end
		例：     delimiter $     CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)     BEGIN         sql语句1;         sql语句2;     END $
	注意
	调用语法	call 存储过程名(实参列表); 调用in模式的参数：call sp1('值'); 调用out模式的参数： set @name; call sp1(@name); select @name; 调用inout模式的参数： set @name=值; call sp1(@name); select @name;
	查看存储过程	语法：show create procedure 存储过程名;
	删除存储过程	语法：drop procedure 存储过程名; 注意：不能同时删除多个存储过程
函数	创建语法	create function 函数名(参数列表) returns 返回类型 begin     函数体 end
	注意	1. 参数列表包含两部分：参数名 参数类型 2. 函数体：肯定会有return语句，如果没有会报错如果return语句 3. 没有放在函数体的最后也不报错，但不建议 4. 函数体中仅有一句话，则可以省略begin end 5. 使用delimiter语句设置结束标记
	调用语法	select 函数名(参数列表);
	查看函数	语法：show create function 函数名;
	删除函数	语法：drop function 函数名;
存储过程和函数的区别		存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新 函数：有且仅有1个返回，适合做处理数据后返回的一个结果

流程控制结构

顺序结构	概述：程序从上往下依次执行
分支结构	概述：程序从两条或多条路径中选择一条去执行
	if函数	功能：实现简单的双分支 语法：if(表达式1, 表达式2, 表达式3) 执行顺序：如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值 应用：在任何地方
	case结构	功能：实现多分支
		特点： 1. 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end中或 begin end的外面可以作为独立的语句去使用，只能放在begin end中 2. 如果when中的值满足条件或条件成立，则执行对应的then后面的语句，并且结束case 如果都不满足，则执行else中的语句或值 3. else可以省略，如果else省略了，并且所有when条件都不满足，返回null
	if结构	功能：实现多重分支 应用：在begin end中 语法： if 条件1 then 语句1; elseif 条件2 then 语句2; . . . 【else 语句n;】 end if;
循环结构	概述：程序在满足一定条件的基础上，重复执行一段代码 位置：在begin end中
	while	语法： 【标签：】while 循环条件 do     循环体; end while【标签】;
		特点：先判断，后执行
	loop	语法： 【标签：】loop     循环体; end loop【标签】;
		特点：模拟简单的死循环
	repeat	语法： 【标签：】repeat     循环体; until 结束循环的条件 end repeat【标签】;
		特点：先执行，后判断
	循环控制	iterate：类似于continue，继续，结束本次循环继续下一次 leave：类似于break，跳出，结束当前所在的循环

---