mysql数据库006

1、 ACID

atomicity原子性
consistency一致性
isolation隔离性
durability持久性
隐式事务，
显式事务：先关闭自动提交
set autocommit = 0;
start transaction;

结束事务：
commit; // 提交
rollback;//回滚

操作事务的时候是在内存，结束事务才写到磁盘。
delete是在内存中删除，通过使用rollback可实现回滚，
而truncate删除直接删除了磁盘的，回滚不回去了。也就是说内存中的操作可以回滚，落盘之后的数据就不能回滚了。

1.1事务并发问题

多个事务，同时，操作，同一个数据库的相同数据时候。

脏读，读取了其他事务还没有提交的数据，“更新”时候
不可重复读，前后两次读取不一致。
幻读，读取还没有提交的事务，“插入”时候

解决：
设置隔离级别

1.2事务隔离级别

查看：select @@tx_isolation;

设置：
set autocommit=0;
set session transaction isolation level repeatable read;

幻读，针对插入时候出现
最高隔离级别可以避免幻读：serializable（串行化，不过性能低）
会给事务加锁，只有完成了前一个事务，才解锁。

保存点：为了回滚做标记。
savepoit a;
rollback a;

2、视图（虚拟表）

动态临时
多个地方用到同样的查询结果
该查询结果使用的sql语句比较复杂
保护数据，提高安全性

2.1 创建视图

create
as

2.2 修改视图

create or replace view 名字
as

方式2、
alter view 名字
as

2.3 删除视图

drop view 视图名1，视图名2，，，

2.4 查看视图

desc myv3;
show create view myv3;

视图可以插入、修改、删除，也会更改原始表，。

2.5、具备以下特点的视图，不允许更新

1、包含如下关键字的sql语句：
group by,分组
distinct
having
union
union all

2、常量视图。
3、select 中包含子查询。
4、join
5、 from一个不能更新的视图
6、where子句的子查询引用了一个from子句中的表

2.6】、视图和表的比较

创建关键字：create view create table
占用内存：表保存数据，视图保存sql逻辑
使用：视图尽量只用查
表：增删改查

主键和唯一
区别：只有一个主键，很多唯一。主键不能为空，唯一可以。
相同点：都具有唯一性。都支持组合键，但不推荐。

外键：
1、用于限制两个表的关系。从表字段引用了主表的某个字段值
2、外键和主表的被引用列要求类型一致，意义一样，名称无要求。
3、主表的被引用列要求是一个key,(一般是主键)

3、变量

系统变量：全局变量、会话变量
自定义变量：用户变量、局部变量

查看系统变量

show session variables;  // session可以省略
show global variables;

//查看满足某些条件的变量
show global varibales like '%char%';

// 查看指定的系统变量的值
select @@系统变量名;  // 默认回话变量
select @@global.系统变量名;

// 赋值：
set global 系统变量名 = 值;
set @@global.系统变量名 = 值;

修改全局变量，对所有的会话都有效，但是不能夸重启，
修改配置文件可以跨重启

会话：当前连接

查看当前会话变量：

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

修改当前会话变量的值：

mysql> set @@session.transaction_isolation = 'read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+

3.2.用户变量

声明，赋值，使用
1.用户变量 ：作用域，只对当前会话（当前连接）有效

1、赋值并初始化，=，或者：=
=搞不清是赋值，还是比较所以：=

set @用户自定义变量 = 值
set @用户自定义变量 := 值
select @用户自定义变量 := 值

2、赋值
和刚才的一样

set @用户自定义变量 = 值
set @用户自定义变量 := 值
select @用户自定义变量 := 值

赋值方式二：
select into

select 字段 into 变量名
from 表;

是弱类型的
所以：

set @name = "join"
set @name = 100
这样是对的

局部变量
作用于实在begin和end之间的，且在第一句话中写出来
声明的时候指定类型

declare  变量名 类型;
declare 变量名 类型 default 值；

2、赋值
方式1：通过set或者select
set 局部变量名 = 值，
set 局部变量名:= 值
select @局部变量名:=值
from 表；

3、使用
select 局部变量名；

declare m int default 1;
declare n int default 2;
declare sum int;
set sum = m + n;
select sum;

4.存储过程函数

含义：一组预先编译好的sql语句的集合，理解成批处理语句
相当于java中的方法，
1 提高代码的重用性
2 简单操作
3 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

使用：
一、创建：
类似java中的方法

create procedure 存储过程名（参数列表）
begin
	存储过程体（一组合法的sql语句）
end

notes
1、参数列表有3部分：参数模式，参数名，参数类型
举例
in stuname varchar(20)
参数模式：
in 传入类型参数
out 传出类型参数
inout 传入传出参数

2、如果存储过程体只有一句话，begin,end可以省略
3.存储过程的结尾可以使用delimiter 重新设置

二、调用
call 存储过程名(参数列表);
eg

# 1、编写存储过程函数
mysql> delimiter $  # 这句话之后，所有的结束都是这个$符号作为结尾了
mysql> create procedure myp1()
    -> begin
    -> insert into stuinfo(id,name) values(3,'xiaoming');
    -> insert into stuinfo(id,name) values(4,'dongfang');
    -> insert into stuinfo(id,name) values(5,'linghucong');
    -> insert into stuinfo(id,name) values(6,'huawuque');
    -> end $
Query OK, 0 rows affected (0.27 sec)

# 2、调用
mysql> call myp1() $

# 3、查询，
mysql> select * from stuinfo $
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | join        |
|    2 | yangyanmeng |
|    3 | xiaoming    |
|    4 | dongfang    |
|    5 | linghucong  |
|    6 | huawuque    |
+------+-------------+
6 rows in set (0.00 sec)

如果报字符集错误：就：
set names gbk;

eg1、根据传入的女神名，给出对应的男神名
eg2、判断能否登陆成功
查找表中的用户名和密码，如果存在就判定为成功

4.2、存储过程的删除

drop procedure 存储过程名
一次只能删除一个存储过程

4 .3、查看存储过程的信息

show create procedure myp2;

5、函数

和存储过程类似，
都是一组预先编译好的sql语句的集合，理解成批处理语句
1.提高代码重用性
2.简化操作
3.减少编译次数并减少了和数据库副武器的连接次数，提高了效率

区别
存储过程可以有0个和多个返回。适用于增删改查，批量插入，批量更新
函数只能有一个返回。处理数据后，返回一个结果。适合用来查询一个值、

5.1、创建语法

create function 函数名（参数列表）returns 返回类型
begin
	函数体
end

注意：
参数列表只有两部分，参数名 、类型
函数体：有return语句
只有一个语句的时候可以省略begin,end
使用delimiter语句作为设置结束标记

5.2.调用语法

select 函数名（参数列表）
案例：
返回公司的员工个数

create function myfun() return int
begin 
	declare c int default 0;
	select count(*) into c
	from employees;
	return c;
end $

//调用
select myfun()$

5.3.查看删除函数

show create function mfun3;
drop function myf3;

6、流程控制结构

顺序结构
分支结构
循环结构

分支结构：
if函数

if(表达式1，表达式2，表达式3）
如果表达式1成立，执行表达式2，否则执行3

2.case结构
case变量、表达式，用于等值判断
when 要判断的值，then返回的值1
when要判断的值，then返回的值2
多重if，实现区间判断