一、事务
1.ACID
原子性：要么都执行，要么都不执行
一致性：钱转到另外的地方，总数不变；一个状态到另一个状态
隔离性：一个事务不被其他事务干扰，要看设置的隔离级别
持久性：一旦事务提交，就是永久的改变，不可以撤回
show variables like ‘autocommit’ 查看事务
2.事务创建
隐式事务：insert update delete 没有明显开启和结束标记
显示事务：
set autocommit=0; #关闭自动提交事务，只是针对当前有效
start transaction;
编写sql #使用显示的可以将多个语句放同一个事务里
commit/rollback
总结：相当于事务只是保存在内存，结束事务后才决定撤回还是保存到磁盘
3.事务并发运行
同时运行多个事务，多个事务访问同一个数据库里相同数据，如果没有隔离机制，可能导致并发问题 类似线程
脏读–读到没有提交数据【更新】
不可重复读–同一个事务多次查询结果不一样
幻读–读到没有提交数据【插入、删除】
read uncommitted读未提交—会出现幻读、不可重复读、脏读
read committed读已提交—幻读、不可重复读
repeatable read可重复读–幻读【默认】
serializable序列化–都可以避免，它是像同步锁一样，需要commit后才可以执行，性能低
查看当前隔离级别：select @@tx_isolation;
使用表如果出现乱码：**set names gbk;**来改变编码
设置当前mysql隔离级别：set transaction isolation level read committed;
设置全局隔离级别：set global transaction isolation level read committed;
4.实际操作事务隔离级别
#在cmd界面
set session transaction isolation level read uncommitted//设置隔离级别
set autocommit=0; #开启事务
update .... #sql
但是我们暂时不提交
#另一个cmd界面
set session transaction isolation level read uncommitted
select * from xx #我们可以看到更新的，即发生了脏读
5.回滚点savepoint rollback to 到保存点
set autocommit=0; #开启事务
start transaction;
update .... #sql
savepoint a; #保存点******
insert ... #sql
rollback to a; #回滚到a处，即回滚点到回滚处之间insert语句被回滚了******
6.delete和truncate在事务中区别
使用delete的sql语句在事务里还可以rollback回滚，即撤销
使用truuncate不可以rollback回滚，哪怕没有报错，即撤销不了
二、视图
视图就可以看作一个表，我们都可以不写子查询了，只是视图是虚拟的
视图本身是一个虚拟表，数据来自表，执行时动态生成
好处：重用sql、简化sql不必知道细节、提高安全性不提供原始表数据
1.视图创建
create view 视图名
as
查询sql语句
2.视图修改
#方式一
create or replace 视图名 #如果不存在就创建
as
查询sql语句
#方式二
alter view 视图名
as
查询sql语句
3.视图删除
drop view 视图名1,视图名2,视图名3
4.视图查看
#两种都可以
desc 视图名
show create view 视图名
5.视图更新–更新视图里数据
<1>可以对视图进行普通的增删改，而且还会影响到原始表，一般情况下我们不会 对视图进行增删改
<2>如果修改以下6种情况，我们不能够视图更新
#1.包含：【分组函数、distinct、group by、having、union、union all】
#2.【常量视图，查询出来的是常量】
create view 视图名1
as
select 'join' name;
update 视图名1 set name="" #失败×
#3.【select中包含子查询】
create view 视图名1
as
select id,(select test from xxx) 测试 from xxxxx
update 视图名1 set name="" #失败×
#4.【join】
create view 视图名1
as
select id,name
from 表1
join 表2
on 连接条件
update 视图名1 set name="" #失败×
#5.【from另一个视图里查询了作为视图会更新失败】
create view 视图名2
as
select * from 视图1
update 视图名2 set name="" #失败×
#6.【where子句的子查询引用了from子句中的表】
create view 视图名1
as
select id,name
from xx
where id in(
select sex
from xx//查询的表和子查询外的表是同一个表
where ...
)
update 视图名2 set name="" #失败×
<6>视图和表对比
视图：create view 只保存了sql逻辑 增删改查，一般不能增删改
表： create tabel 保存了数据 增删改查
三、约束
1.常见约束：
not null
unique
default
check—检查，mysql不支持
primary key----等价于unique+not null
foreign key-----从表里写，引用了另外表字段
2.主键唯一区别：
<1>一个表至多一个主键，多个唯一
<2>主键不允许为空，唯一可以为空
3.外键
<1>外键用于从表引用主表某些字段，最好被引用字段类型一致
<2>主表被引用的要是key
<3>插入：先插入主表； 删除：先删除从表
演示
#插入：我们有主表A 从表B
先insert into A value(...);
再insert into B value(...);
#删除两种方式----【要么删除从表，再删除主表;要么设置级联】
#方法一：级联删除：添加外键时候添加一个on delete cascade----删除有关联的
#设置级联后就可以删除
1.alter table 从表 add constraint 外键名 foreign key(从表字段) references 主表(主表字段) on delete cascade
2.delete from 主表 where ...
#方法二：级联置空：添加外键时候添加一个on delete set null----有关联的都变null
1.alter table 从表 add constraint 外键名 foreign key(从表字段) references 主表(主表字段) on delete set null
2.delete from 主表 where ...
4.创建表约束
create table 表名(
字段 字段类型 not null,
字段 字段类型 primary key,
字段 字段类型 unique,
字段 字段类型 default,#默认
constraint 自定义约束名 foreign key(字段) references 主表(主表字段)
)
列级约束—不支持外键，不可以起别名
表级约束—不支持非空和默认，支持别名，但主键别名无效
5.修改/添加/删除约束
#1.非空
添加：alter table 表名 modify column 字段名 字段类型 not null;
删除：alter table 表名 modify column 字段名 字段类型;
#2.默认
添加：alter table 表名 modify column 字段名 字段类型 default 值;
删除：alter table 表名 modify column 字段名 字段类型;
#3.主键---****
添加：alter table 表名 add constraint 约束名 primary key(字段名);
删除：alter table 表名 drop primary key;
#4.唯一---****
添加：alter table 表名 add constraint 约束名 unique(字段名);
删除：alter table 表名 drop index 索引名;
#5.外键---****
添加：alter table 表名 add foregin key(字段名) references 主表(主表字段);
删除：alter table 表名 drop foregin key 约束名;
四、自增长
auto_increment_increment
一个表最多一个自增长，自增长只支持数值类型，自增长必须为key
#创建
create table 表(
字段名 字段类型 约束 auto_increment
)
#修改设置自增长
alter tabel 表 modify column 字段名 字段类型|约束 auto_increment
#删除自增长
alter tabel 表 modify column 字段名 字段类型|约束
五、变量
系统变量–全局变量、会话变量
global全局变量作用域：每次启动所有的都有效，所有会话连接有效；
如果重启会失效，如果需要重启仍然有效要修改配置文件
session会话变量作用域：当前会话有效，切换了就无效了
#1.查看所有系统变量
show global variables//查看全局变量
show session variables//查看会话变量，session可以省略
#2.查看满足条件的系统变量
show global variables like '%c%'
show session variables like '%c%'
#3.查看指定的某个系统变量值
select @@global.系统变量名
select @@session.系统变量名 #session可以不写
#给某个系统变量赋值,session可以不写
#方法一
set global 系统变量名=值
set session 系统变量名=值
#方法二
set @@global.系统变量名=值
set @@session.系统变量名=值
自定义变量–用户变量、局部变量
用户变量作用域：当前会话里仍和地方，begin end里或begin end外
局部变量作用域：当前会话，begin end中的第一句话
#用户变量
#1.声明三种方式
set @用户变量名=值
set @用户变量名:=值
select @用户变量名:=值
#2.赋值
#方式一:通过set或select----即可以当声明也可以赋值
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
#方式二：通过select into----查出来的字段赋值给变量名---常用
select 字段 into 变量名
form xx
#使用
select @用户变量名;
#局部变---局部变量基本上和用户变量用法差不多，只是声明不一样
#声明
declare 变量名 类型;
declare 变量名 类型 default 值; #顺便初始化
#赋值-----基本上和用户变量差不多
#方式一：通过set或select
set @局部变量名=值;
set @局部变量名:=值;
select @局部变量名:=值;
#方式二：通过select into
select 字段 into 变量名
form xx
#使用
select @局部变量名;
用户变量和局部变量对比
用户变量：作用域–当前会话，任何地方 语法：必须加@，不限定类型
局部变量：作用域–begin end，第一句 语法：不加@，限定类型
案例对比：
#用户变量
set @m=1;
set @n=2;
set @sum=@m+@n;
select @sum;
#局部变量--注意写在begin end里，下面省略了
declare m int default 1;
declare n int default 2;
declare sum int;
set sum=m+n;
select sum;
六、存储过程
储存过程和函数类似于Java里方法，可以提高代码重用性，更加简单化
储存过程：一组预先编译好的sql集合，减少了连接次数，提高了效率
1.创建储存过程
#1.创建
#参数列表：参数模式 参数名 参数类型 例如：int sruname varch(20)
#参数模式：in传入 out返回值 inout即可传入又可返回
#如果存储过程体只有一句,begin end可以省略
#存储过程体必须加;号
#可以使用【delimiter 结束标记】来设置结束标记
delimiter 结束标记
create procedure 储存过程名(参数模式 参数名 参数类型)
begin
储存过程体，即一组sql，【注意sql必须加;号】
end 结束标记
#2.调用
call 储存过程名(实参列表) 结束标记
演示in：
delimiter $
create procedure mytest(in username varchar(20))
begin
declare result varchar(20) default '' #声明局部变量
select count(*) into result #结果赋值给result
from xx
where ...;
select result; #使用
end $
call mytest('我是测试') $
演示out:
delimiter $
create procedure mytest(in username varchar(20),out boy varchar(20),out girl varchar(20))
begin
select a,b into boy,girl #结果赋值直接赋值给out参数,可以是多个
from xx
where ...;
end $
call mytest('我是测试',@boy,@girl) $ #out需要传入
select @boy,@girl #查看结果
演示inout:
#其实有点像c语言里的传地址，一旦改变，都改变
delimiter $
create procedure mytest(inout a int,inout b int)
begin
set a=a*2
set b=b*2
end $
set @m=10$ #我们可以先赋值
set @n=20$
call mytest(@m,@n) $
select @m,@n #可以当in传入，也可以当out传出
2.储存过程删除
drop procedure p1; #只能一个一个删除
3.储存过程查看
没办法改变储存过程，如果需要改只能删除重建
show create procedure p1;
七、函数
储存过程：可以有0个返回，也可以有多个
适合批量插入、更新
函数：仅有1个返回，时候处理数据返回一个结果
1.函数创建
#函数体一定有一个return，没有不报错，最好写
create function 函数名(参数名 参数类型) returns 返回类型
begin
函数体...
return
end
案例
#定义时候有返回类型
create function mytest(empName varchar(20)) returns double
begin
set @sal=0; #定义变量
select salary into @sal #赋值
from xx
where ..;
return sal; #返回
end $
select mytest('test')$
2.函数查看
show create function mytest
3.函数删除
drop function mytest
八、流程控制
顺序结构
分支结构
循环结构
分支
1.if函数---------------适用于简单的
if(表达式1,表达式2,表达式3)
2.分支结构case--------适用于等值判断
#用法一：类似switch----用于等值判断
case 变量|表达式|字段
when 要判断的值 then 返回值1或语句1；
when 要判断的值 then 返回值2或语句2；
..
else 返回值n或语句n;
end case;
#用法二：类似if--------用于区间判断
#注意case后没有值，case后没有可以理解为if的多个条件
case
when 判断条件1 then 返回值1或语句1；
when 判断条件2 then 返回值2或语句2；
..
else 返回值n或语句n;
end case;
3.if结构----------------适用于多重分支
#注意分号
#if...then 语句1;
#elseif ...then 语句2;
#else 语句n;
#end if;
create function mytest(score int) returns char
begin
if score>=90 and score<=100 then return 'A';
elseif score>=80 then return 'B';
elseif score>=60 then return 'C'; #满足一个后面就不执行了
else return 'D'; #如果都不满足则else
end if;
end $
select mytest('test')$
循环
循环：while、loop、repeat
iterate----类似continue
leave----类似break
1.while
自定义标签:while 循环条件 do
循环体;
end while 自定义标签；
2.loop—可以模拟死循环–通常搭配leave使用
自定义标签:loop
循环体;
end loop 自定义标签；
3.repeat—类似do while 先执行后判断
自定义标签:repeat
循环体;
until 结束循环条件;
end repeat 自定义标签；
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
