笔记3

事务:
1.原子性：这件事情不可分割，要么都成功要么都失败
父母 -500 姐姐-1000
你 +500

2.隔离性：一个事务的执行不能被其他事务干扰
3.一致性：多次执行状态如一
4.持久性：持久化下来

****mysql 数据库采用事务自动提交的方式

开启事务:start transaction -手动处理事务

Commit提交
或
rollback回滚

******数据库只要没有接受到 commit 那么这个事务不会提交
******事务处理只能够对dml语句有效
----保存点 savepoint - dba

视图:view 和表在查询上一样的，可以当成表查询
解决问题：
1、当表的数据比较大的时候，可以把常用数据生成虚拟表，以后查这个虚拟表性能提高
2、有一些敏感信息或者保密信息，可以把敏感信息踢掉，然后生成虚拟表，以后查这个虚拟表
3、分割表，把一个表分割成多个视图，针对不同应用

****性能权限保护敏感信息

视图定义:
视图是由查询结果形成的一张虚拟表

视图的创建语法:
Create view 视图名 as select 语句

视图的删除语法:
Drop view 视图名

视图的修改：覆盖原始图或者你再建个新的
Alter view 视图名 as select xxxxxxx

***了解即可： create view student_view as select * from student where id<5; select name from student_view where id=3;
视图的algorithm
1.Merge :select *from emp_view ==> select+select select name from （select * from student where id<5） where id=3;

2.TEMPTABLE:Create view 视图名 as select 语句 ==>select *from emp_view
会创建一个临时表（视图）select+视图

3.UNDEFINED ：不指定哪种视图，数据库会根据自身情况，采用上述某一种。 mysql数据库默认采用这个方式

视图与表：
1、视图可以改变表的数据（视图中数据与原始表数据一一对应）
2、视图中信息与表的信息不是一一对应的就不能该分组函数 group by 。。。
3、原始表数据改变影响视图

***视图就是一个查询的sql语句，不会在硬盘上留有记录
*****通常建的视图都是只读视图（查询）

索引: 书的目录 ****主要目的提高查询性能
索引：是针对数据所建立的列
作用：可以加速查询速度
负面影响：降低了增删改的速度
***建立索引的列的信息不能频繁更新

索引类型
普通索引:index 仅仅是加快查询速度
唯一索引:unique 列上的值不能重复
主键索引:primary key 不能重复
主键必唯一，但是唯一索引不一定是主键
一张表上，只能有一个主键，但是可以有一个或多个唯一索引

全文索引:fulltext 停止词

查看一张表上所有索引
Show index from 表名； show index from 表名 G

建立索引

Alter table 表名 add index/unique/fulltext [索引名](列名)；
Alter table 表名 add primary key(列名);//不要加索引名,因为主键只有一个

create index 索引名 on 表（列）

删除索引

删除非主键索引：Alter table 表名 drop index 索引名;

删除主键：alter table 表名 drop primary key;

了解：

查看匹配度
Select id,email,match (intro) against (‘health’) from member;

关于全文索引的用法
Match (全文索引名) against (‘keyword’);
例：
Select * from member where match (intro) against (‘health’);

关于全文索引的停止词
全文索引不针对非常频繁的词做索引
如 this, is,you,my 等等

全文索引：在mysql的默认情况下，对于中文意义不大
因为英文有空格，标点符号来拆分单词，进而对单词进行索引
而对中文，没有空格来隔开单词，mysql无法识别每个中文词

索引的创建原则：经常查询值不变值分散
1：不要过度索引
2：在where条件最频繁的列上加
3：尽量索引散列值，过于集中的值加索引意义不大
4. 列的数据一般不更新

**注意：
设有新闻表15列，10列上有索引，共500W行数据，如何快速导入？
1：把空表的索引全部删除
2：导入数据
3：数据导入完毕后，集中建索引。

存储过程：类似函数的一段代码，具有结构性能够使用 if if-else while 等
*******mysql存储过程

函数：和存储过程一样，只是函数有返回值 function f（）{ return }

查看现有的存储过程
Show procedure status where db=''; Show procedure status G 横着显示

删除存储过程
Drop procedure 存储过程的名字

调用存储过程
Call 存储过程名字（）;

*****创建存储过程：

分割符：
DELIMITER // ---告诉数据库结束符号改为 // $

参数：

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回

1、IN参数例子
创建:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)
-> BEGIN
-> SELECT p_in;
-> SET p_in=2;
-> SELECT p_in;
-> END;
-> //
mysql > DELIMITER ;
mysql > SET @p_in=1;
mysql > CALL demo_in_parameter(@p_in);
mysql> SELECT @p_in;

******p_in虽然在存储过程中被修改，但并不影响@p_id的值(相等于copy一份进去操作)
2、OUT参数例子
创建:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)
-> BEGIN
-> SELECT p_out;
-> SET p_out=2;
-> SELECT p_out;
-> END;
-> //
mysql > DELIMITER ;
执行结果:
mysql > SET @p_out=1;
mysql > CALL demo_out_parameter(@p_out);
mysql> SELECT @p_out;
3、 INOUT参数例子
创建:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> END;
-> //
mysql > DELIMITER ;
执行结果:
mysql > SET @p_inout=1;
mysql > CALL demo_inout_parameter(@p_inout) ;
mysql> SELECT @p_inout;

变量：
1、变量定义
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)
例如:
DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT '1999-12-31';
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
*****一般定义在存储过程、触发器、函数内部（临时变量）

2、 SET 变量名 = 表达式值 [,variable_name = expression ...] SET @p_inout=1;
3、用户变量
在MySQL客户端使用用户变量
mysql > SELECT 'Hello World' into @x;
mysql > SELECT @x;
mysql > SET @p=1;

(1). 变量作用域
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3()
-> begin
-> declare x1 varchar(5) default 'outer';
-> begin
-> declare x1 varchar(5) default 'inner';
-> select x1;
-> end;
-> select x1;
-> end;
-> //
mysql > DELIMITER ;

(2). 条件语句
if-then -else语句

mysql > DELIMITER //
mysql > CREATE PROCEDURE proc2(IN parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> if var=0 then
-> insert into t values(17);
-> end if;
-> if parameter=0 then
-> update t set s1=s1+1;
-> else
-> update t set s1=s1+2;
-> end if;
-> end;
-> //
mysql > DELIMITER ;

case语句：
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc4 (in parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> case var
-> when 0 then
-> insert into t values(17);
-> when 1 then
-> insert into t values(18);
-> else
-> insert into t values(19);
-> end case;
-> end;
-> //
mysql > DELIMITER ;

循环语句
Ⅰ. while ···· end while：
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc5()
-> begin
-> declare var int;
-> set var=0;
-> while var<6 do
-> insert into t values(var);
-> set var=var+1;
-> end while;
-> end;
-> //
mysql > DELIMITER ;

repeat···· end repeat：
它在执行操作后检查结果，而while则是执行前进行检查。
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc6 ()
-> begin
-> declare v int;
-> set v=0;
-> repeat
-> insert into t values(v);
-> set v=v+1;
-> until v>=5
-> end repeat;
-> end;
-> //
mysql > DELIMITER ;

loop ·····end loop:
loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc7 ()
-> begin
-> declare v int;
-> set v=0;
-> LOOP_LABLE:loop
-> insert into t values(v);
-> set v=v+1;
-> if v >=5 then
-> leave LOOP_LABLE;
-> end if;
-> end loop;
-> end;
-> //
mysql > DELIMITER ;

****连上数据库直接可以执行效率高，同一个存储过程可以共享一个事务

触发器:trigger,枪击，扳击，引线的意思
一触即发
作用：监视某种情况并触发某种操作

观察一下场景
一个电子商城
商品表，g
主键商品名库存
1 电脑 28
2 自行车 12

订单表，o
订单主键商品外键购买数量
1 2 3
2 1 5

完成下单与减少库存的逻辑
Insert into o(gid,num) values(2,3); //插入语句
Update g set goods_num=goods_num-3 where id=2; //更新过程

这两个逻辑可以看成一个整体或者，insert --- 引来 update

用触发器可以解决上述问题
我们可以监视某表的变化，当发生某种变化时，触发某个操作

触发器:

能监视: 增.删.改
触发操作: 增.删.改

四个要素：

监视地点监视事件
table insert/update/delete

触发时间触发事件
after/before insert/update/delete

创建触发器：
delimiter // $
Create trigger triggerName
After/before insert/update/delete on 表名
For each row #这句话是固定的
Begin
Sql语句 #一句或多句，insert/update/delete范围内
End;

创建触发器的语法
create trigger 触发器名称
after/befor (触发时间)
insert/update/delete (监视事件)
on 表名 (监视地址)
for each row
begin
sql1;
..
sqlN;
end

删除触发器的语法: 查看触发器:
Drop trigger 触发器名; show triggers;

如何在触发器引用行的值
对于insert而言，新增的行用new来表示，
行中的每一列的值，用new.列名来表示

对于delete来说，原本有一行，后来被删除，
想引用被删除的这一行,用old,来表示old.列名,就可以引用被删除行中的值

对于update来说，
被修改的行，
修改前的数据,用old来表示,old.列名引用被修改之前行中的值
修改后的数据,用new来表示,new.列名引用被修改之后行中的值

例子:
#建立商品表
Create table g(
Id int,
Name varchar(10),
Num int
)charset utf8;
#建立订单表
Create table o(
Oid int,
Gid int,
Much int
)charset utf8;

Insert into g
Values
(1,'猪',22),
(2,'羊',19),
(3,'狗',12),
(4,'猫',8);
#0，添加订单，库存减少
Create trigger tg2
After insert on o
For each row
Begin
Update g set num = num-new.much where id = new.gid;
End$

1、添加订单：
Insert into o values(1,4,6)$
2、查看订单
3、查看商品表

#1，删除一个订单，库存相应增加
#监听地点 o表
#监听事件 delete
#触发事件 update
#触发时间 after
Create trigger tg3
After delete on o
For each row
Begin
Update g set num = num+old.much where id=old.gid;
End$
1、删除订单
Delete from o where oid = 2$
2、查看订单表
3、查看商品表

#2，修改订单的数量时，库存相应改变
#监听地点 o表
#监听事件 update
#触发事件 update
#触发时间 after

Create trigger tg4
After update on o
For each row
Begin
Update g set num = num+old.much-new.much where id=old.gid;
End$

1、修改订单
Update o set much = 5 where oid =1$
2、查看订单
3、查看商品表

触发器里before和after的区别
After是先完成数据的增,删,改再出发
触发的语句晚于监视的增,删,改,无法影响前面的增删改动作

Before是先完成触发,再增,删,改
触发的语句先于监视的增,删,改,发生,我们有机会判断,修改即将发生的操作.

应用场合：
1.当向一张表中添加或删除记录时，需要在相关表中进行同步操作。
比如，当一个订单产生时,订单所购的商品的库存量相应减少。

2.当表上某列数据的值与其他表中的数据有联系时。
比如，当某客户进行欠款消费，
可以在生成订单时通过设计触发器判断该客户的累计欠款是否超出了最大限度。

3.当需要对某张表进行跟踪时。
比如，当有新订单产生时，需要及时通知相关人员进行处理，
此时可以在订单表上设计添加触发器加以实现

典型案例:
对于所下订单进行判断,如果订单的数量>5,就认为是恶意订单，
强制把订单的商品数量改成5

#监听地点 o表
#监听事件 insert
#触发事件 update
#触发时间 before

#目的，触发时间先于监视事件发生，并判断监视事件的数据

Create trigger tg5
before insert on o
For each row
Begin
declare gnum int default 0;
select Num into gnum from g where id=4;

if new.much < gnum then

If new.much > 5 then
Set new.much = 5;

end if;
Update g set num = num-new.much where id = new.gid;
End if;
End$

Insert into o values (2,4,2)$
Select * from o$
Select * from g$

分页查询：提高查询性能（降低了数据库的IO吞吐量）
limit
select * from student limit 3,5; 从第3条开始，查询 5条记录
top：SqlServer-- top10
rowid：伪列

级联更新：
CREATE TABLE `user` (
`id` int(4) NOT NULL,
snum int(4) unique not null,
`sex` enum('f','m') DEFAULT NULL,
PRIMARY KEY (`id`)
)

CREATE TABLE `userinfo` (
`sn` int(4) NOT NULL AUTO_INCREMENT,
`userid` int(4) NOT NULL,
`info` varchar(20) DEFAULT NULL,
PRIMARY KEY (`sn`),
KEY `userid` (`userid`),
CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`)
)

INSERT INTO `user` (`id`,snum ,`sex`)
VALUES ('1',1, 'f'), ('2',2, 'm'), ('3',3, 'f');

INSERT INTO `userinfo` (`sn`,`userid`,`info`)
VALUES ('1', '1', '2005054dsf'),
('2', '1', 'fdsfewfdsfds'),
('3', '1', 'gdsgergergrtre'),
('4', '2', 'et34t5435435werwe'),
('5', '2', '435rtgtrhfghfg'),
('6', '2', 'ret345tr4345'),
('7', '3', 'fgbdfvbcbfdgr'),
('8', '3', '45r2343234were'),
('9', '3', 'wfyhtyjtyjyjy');

------------------------------------------------------------------------
游标：用来遍历
函数：一般都采用了存储过程替代了