不想靠爸爸，他老了。 ----夏不夏说
1、存储过程：
所谓存储过程，就是为了实现某一特定的功能，将多条sql语句集合在一起， 一次性执行，当下一次需要再次执行的时候只需要调用存储过程即可。
查看存储过程：
show procedure status where db = '数据库名'；
删除存储过程：
drop procedure 存储过程名字，一次只能删一个
例：
delimiter $$(符号任意， 表示用这种符号作为结束符, 注意空格)
create procedure func1()
begin
select * from student3;
.........
end
$$
更改过后建议用dilimiter ; 再改回来
call func1();
2、局部变量：
定义在sql语句块中的变量， begin/end 定义的变量
declare var1[...] 类型 default 默认值
select 变量名
例：
mysql> create procedure p2(a int, b int)
-> begin
-> declare c int default 0
-> select c;
-> set c = a+ b;
-> select c as C;
-> end;
-> &&
3、用户变量：相当与全局变量。 只在一个数据库中有效
在客户端
连接到数据库实例整个过程中用户变量都是有效的
mysql 中用户变量不需要事先声明， 在用的时候直接用“@变量名”使用就可以
1、
set @num = 1;
set @num:=1;
2、
select @num:=1;
select @num:=字段名 from 表名 where ......
注：第二种只能用：=
4、创建含有参数的存储过程in、out、inout（默认为in）
1）创建含有参数的存储过程----out
该值可在存储过程内部被改变， 并可被返回（不能接收值的传入， 只能接收值的传出）
只能传出去，不能传进来
例：
mysql> delimiter &&
mysql> create procedure pro1(out a int)
-> begin
-> select a;
-> set a = 10;
-> select a;
-> end
-> &&
2）创建含有参数的存储过程----in
表示该参数的值必须在调用存储过程时指定，在存储过程中
只能传进来，不能传出去
例：
mysql> delimiter &&
mysql> create procedure pro3(in a int)
-> begin
-> select a;
-> set a = 10;
-> select a;
-> end;
-> &&
注：不写参数in/out/intout 则默认为in
5、触发器：
修改一张表，另一张表也会改变
是一个特殊的存储过程，不同的是，存储过程是要用call来调用，而触发器不需要手动启动，只要当一个预定义的事件发生的时候，就会被mysql自动调用。
查看所有的触发器：
show triggers;
删除触发器：
drop trigger trigger_name;
定义格式：
create trigger
trigger_name trigger_time trigger_event
on table_name for each row trigger_body
注：trigger_name; 触发器名字, 不能与已存在的触发器重名
trigger_time: before after 表示在事件发生之前或之后触发
trigger_event: insert updata delete 触发该触发器的具体事件
table_name: 该触发器是作用在哪张表上
trigger_body: 是当触发器被触发时执行的语句， 如果指向多个语句，用begin.. end 结构
格式：
delimiter &&
create trigger trigger_name trigger_time trigger_event on table_name for each row
begin
trigger body
end
&&
例：
delimiter &&;
mysql>
create trigger
trigger_student_count_insert1
-> after insert
-> on student_info1 for each row
-> begin
-> update student_count1 set student_count = student_count+1 where student_class = 0;
-> update student_count1 set student_count = student_count+1 where student_class = NEW.stu_class;
-> end
-> &&
NEW：在触发器为insert事件时有效， 表示当前正插入的数据
mysql> delimiter &&
mysql> create trigger trigger_student_count_delete1
-> after delete
-> on student_info1 for each row
-> begin
-> update student_count1 set student_count = student_count -1 where student_class = 0;
-> update student_count1 set student_count = student_count -1 where student_class = OLD.stu_class;
-> end
-> &&
OLD：在触发器为delete事件时有效，表示当前正删除的数据
6、游标：
游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制，游标充当指针的作用。尽管游标能够遍历结果集中所有的行，但是一次只能指向一行
mysql 游标只能用于存储过程
使用步骤：
1。必须先声明游标 注：游标定义后不能再定义变量
declare 游标名 cursor for select_statement
2.必须打开游标才能使用
open 游标名
3。通过游标根据需要取出数据
fetch 游标名 into var1, var2...
4.使用完之后必须关闭游标
close 游标名
例：
mysql> delimiter &&
mysql> create procedure p22()
-> begin
-> declare row_num;
-> declare row_name;
-> declare row_score;
-> declare cnt int default 0;
-> declare i int default 0;
-
> declare leveler cursor for select num , name, score from cour;
-> select count(*) int cnt from cour;
-> open leveler;
-> repeat
-> set i:= i+1;
-> fetch leveler into row_num, row_name, row_score;
-> select row_num, row_name, row_score;
-> until i >= cnt end repeat;
-> close leveler;
-> end
-> &&
7、cntinue handler 越界标志
在mysql游标中可以定义continue handler 来操作一个越界标志
例：
delimiter &&
create procedure p55()
begin
declare row_num int;
declare row_course varchar(20);
declare row_score int;
declare go_on int default 1;
declare leveler cursor for select num, course, score from cour;
declare continue handler for NOT FOUND set go_on:=0;
open leveler;
repeat
fetch leveler into row_num, row_course, row_score;
if(go_on = 1)then
select row_num , row_course, row_score;
end if;
until go_on = 0 end repeat;
close leveler;
end
&&
8、mysql 函数;
查看函数：
show function status;
删除函数 ：
drop function function_name;
包括：数学函数，字符串函数，日期和时间函数
数学函数：
abs(-1);
pi();
sqrt(16);
rand(); //0-1随机数
round(10.51)求10.51最近的整数
round(10.1243, 2)保留后两位
sign(x); 返回x的符号，x是负数返回-1，0返回0， 正数返回1
pow(x, y)/power(x, y); x的y次方
exp(x); e的x次方
log(x); 以e为底x的对数
log10(x); 以10为底x的对数
字符函数：
char_length(s)； 计算s的字符数
concat（s1， s2，。。。）将多个字符串拼接为一个字符串
concat_ws(x，s1， s2。。。
) 合并多个字符串，并以x间隔
upper(s)/ucase(s); 将字符串s的字符全部变成大写字母
lower（s）/lacase(s); 将字符串s的字符全部变成小写字母
repeat（s, n
）;将字符串s重复n次
strcmp（s1，s2
）; 比较s1 和s2 的大小（相等返回0， 小于返回-1， 大于返回1） 第一个不相同的字符
substring(s, n, len)/mid(s, n, len); 从字符串s第三个位置获取长度为len的子串
reverse(s); 将字符串s倒置
日期时间函数:
curdate()/current_date(); 获取当前日期
curtime()/current_time(); 获取当前时间
now(); 获取当前日期和时间
current_timestamp();
localtime();
sysdate()
条件判断函数：
if(expr, v1, v2) 如果表达式expr成立返回v1，否则返回v2
select code ,name , if(code)
自定义函数：
语法：
create function function_name(参数列表)
returns 返回值类型
func_body
注：1。函数名合法，不能是关键字
2。一个函数应该属于一个数据库，可使用db_name.function_name的形式执行指定数据库的函数，否则，默认执行当前数据库的函数
3。参数部分，由参数名和参数类型组成
4。返回值类型，一定要有returns返回值语句
5。关于函数体：
函数体由合法的sql语句组成
函数体可以是简单的select, insert 语句
如果函数体是复合结构的多条语句，则使用begin。。。。end结构
注释语法：
1。# 单行注释
2。/* */ 多行注释
例：
mysql> delimiter &&
mysql> create function adduser(username varchar(10), userage int)
-> returns int
-> begin
-> insert into stu(name, age) values(username, userage);
-> return(select max(age) from stu);
-> end
-> &&
9、视图：
安全、便捷
1。什么是视图？
视图是由查询结果形成的一张虚拟的表。与包含数据的表不一样，试图只包含使用动态检索数据的查询结果
2。什么时候用到视图？
当需要频繁的查询某个结果。
3。创建视图的语法
格式：
create view view_name as select .....
create view test_view as select * from cour;
select * from test_view;
4。查看视图
show table status where comment = "view";
5。删除视图
drop view test_view
6。使用视图的好处
1）简化查询语句
2）进行权限控制；把表的权限封闭，但是开放相应的视图的权限，视图里只开放部分数据列
3）简化多表查询语句
10、索引：
查看索引：
show index from tablename;
删除索引：
drop index 索引名 on 表名
alter table 表名 drop index 索引名；
索引是一种特殊的文件，它们包含着这对数据表中所有记录的引用。相当于书的目录，
优点：可以提高检索数据的速度，这是创建索引的最主要原因。
缺点：创建和维护索引需要耗费时间，耗费的时间随着数据量的增加而增加。索引需要占用物理空间，索引摇控制索引的数量。
分类：1 普通索引
2 唯一性索引
3 全文索引
4 单列索引
5 多列索引
6 空间索引
索引的设计原则：
1选择唯一性索引
2为经常需要排序，分组和联合操作的字段建立索引
3为常做查询条件的字段建立索引
4限制索引的数目
5删除不再使用或使用少的索引
创建索引：
1 创建表的时候创建索引
格式：
create table table_name(
....
[unique| fulltext|spatial]
index | key[别名]（属性名1）【（长度）】【asc| desc】;
例：普通索引
create table index1(
id int,
name varchar(20),
sex varchar(10),
index(id));
例：创建唯一性索引
在创建索引时，可以规定索引能否包含重复的值，如果不包含，则创建为primary key 或unique索引
create table index2(
id int unique,
name varchar(10);
unique index index2_id(id asc));
例：创建全文索引
只能创建char, varchar 或test 类型的字段上，而且，只有myISAM存储以及器支持全文索引
查看引擎
show engines;
create table index(
id int,
info varchar(20),
fulltext index index_info(info))engine=myisam;
例；创建单列索引
create table index4(
id int,
subject varchar(30),
index index4_st(subject(10));//索引使用列的前十个字符
例：创建多列索引：
create table index5(
id int,
name varchar(10),
sex char(5),
index index5_ns(name, sex));
例：创建空间索引：
create table index6(
id int,
space geometry not null,
spatial index index6_sp (spqce))engine = myisam;
创建空间索引时，表的引擎必须是myisam类型，而且索引字段必须是非空约束，很少用到
2。在已经存在的表上创建索引
格式：
create [unique | fulltext | spatial] index 索引名 on 表名
（索引列名【长度】【asc| desc】）;
例：
mysql> delimiter &&
mysql> create procedure pro2(in num int)
-> begin
-> declare i int default 0;
-> while i < num do
-> insert into test(id,age, score) values(round(rand()*num), round(rand()*num), round(rand()*num));
-> set i:= i+1;
-> end while;
-> end
-> &&
11、事务：
广泛运用于订单系统，银行系统等多种场景
例：在人员管理系统中，删除一个人员，需要删除人员的基本资料，也要删除他的相关信息，邮箱，档案等等，这样，这些数据库操作语句就构成了一个事务。
如果在删除过程中，出错了，只
执行了一条语句，那么后果很严重，
运用事务处理，如果删除出错了，可以取消之前成功的操作（rollback）。这样就回到了最初的状态。
对于事务而言，需要满足ACID特征；
1。A（atomicity）：
原子性，指整个数据库事务是不可分割的工作单位，只有使事务中所有数据库操作都执行成功，整个事务才算成功，事务中任何一个sql语 句执行失败，那么已经执行成功的sql语句也必须撤销。数据库退回到最初状态。
2。C（consistency）：
一致性：指事务将数据库转变成另一种一致的状态。在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏。
3。I（isolation）：
隔离性：每个对写事务的对象与其它事务的操作对象能够相互分离，互相不影响，并发控制
4。D（durability）：
持久性：表示事务一旦提交，其结果就是永久，不能恢复到之前的状态。
事务分类：
1。扁平事务
2。带有保存点的扁平事务
3。链事务
4。嵌套事务
5。分布式事务
扁平事务：
所有的操作处于同一层次。
begin work
operation1
operation2
...
operation n
commit work
begin work
operation 1
operation 2
...
operation n
rollback work
带有保存点的扁平事务：
这种事务除了支持扁平事务的操作外，允许在事务执行过程中回滚到同一事务的最早的一个状态。
因为某些事务中的执行语句是有效的，放弃整个事务，开销太大，所以利用保存点记住当前阶段事务的状态，以便日后发生错误时回到该状态。
链事务：
指回滚时，只能恢复到最近的一个保存点，
保存点扁平事务可以滚回到任意正确的保存点
嵌套事务：
begin work
substraction1
:
begin work
sql1..
. ..
commit work
substraction2:
begin work
sql
......
commit work
commit work
分布式事务：
通常是指在一个分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络上的不同节点。
比如：通过建设银行向招商银行转账，建行和招行用的是不同的数据库，同时二者的数据库也不在一个网络节点上，当用户跨行转账
开启事务：
start transaction
或者
begin 【work】
提交事务（关闭事务）
commit 【work】 //提交并关闭
放弃事务（关闭事务）：也叫回滚事务，指放弃当前事务，回到上一次事务的状态：
rollback //撤销并关闭
保存点：
savepoint p1: 在事务中创建一个保存点，一个事务可以有多个保存点
rollback to savepoint p1; 把事务回滚到p1, 则发生在p1之前的事务被提交，p1之后的被忽略
release savepoint p1: 删除一个事务的保存点。当没有指定的保存点
不同隔离级别的事务
1。未提交就能看到
2。提交之后才能看到
3。只能看到之前提交的内容 // 隔离级别最高
事务的隔离级别：
1。read uncommitted（未提交读）：在RU的隔离级别下，事务A对数据做的修改即使没有提交，对事务B也是可见的。这种问题叫脏读。这是隔离程度较低的一种隔离级别，在实际中会有很多问题，一般不常用。
2。read committed（提交读）：在RC隔离级别下，不会出现脏读问题。事务 A对数据做的修改，提交后B可见。例，事务B开启时读到数据1，事务A开启，把这个数据改成了2，提交事务A，B再次度这个数据，读到的就是2。
3。repeatable read（可重复读）：在RR隔离级别下。事务A对数据做的修改，提交之后，对于先于事务A开启的事务是不可见的。例：事务B开启时读到数据1，开启事务A，把数据改成2，提交，B再次读数据，仍然读到的是1。
MySQL默认的隔离级别是RR
4。serializble（可串行化）：最高隔离级别，要求所有的事务串行执行。在这种隔离级别下，读取每行数据都加锁，导致大量的锁征用问题，性能最差。
可以用set transaction 语句改变单个绘画或者所有新进连接的隔离级别
格式：
set 【session| global】transaction isolation level read uncommited| read committed | repeatable read | serializble
1。不带session， global的set命令只对下一个事务有效
2。set session：为当前会话设置隔离级别
3。set global： 为以后新建的所有MySQL连接设置隔离级别
查看隔离状态：
select @@tx_isolation;
设置隔离状态：set session transaction isolation level read uncommitted;
QQ：1786610699 倔强的木木 2017年9月15日
242
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
