mysql 视图 过程 函数_Mysql学习---视图/触发器/存储过程/函数/执行计划/sql优化 180101...

视图

视图： 视图是一个虚拟表(非真实存在),动态获取数据，仅仅能作查询操做html

本质：【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】便可获取结果集，并能够将其看成表来使用。因为视图是虚拟表，因此没法使用其对真实表进行建立、更新和删除操做，PyMysql是支持视图的。sql

仅能作查询用。数据库

建立视图：

create VIEW stu as select * from student; # 这里只是创建了一个对应关系，视图是虚表，动态获取数据

select * from stu; # 这里只是简化了操做，实际上仍是执行了select * from student

查看视图：

show TABLES # 会显示table和view视图信息

删除视图：

drop VIEW stu;

修改视图：

ALTER VIEW stu as select * from student where gender = '男';

PyMysql是支持视图的

触发器

对某个表进行【增/删/改】操做的先后若是但愿触发某个特定的行为时，能够使用触发器函数

触发器用于定制用户对表的行进行【增/删/改】先后的行为性能

触发器没法由用户直接调用，而知因为对表的【增/删/改】操做被动引起的。学习

特别的：优化

NEW表示即将插入的数据行，OLD表示即将删除的数据行spa

多行操做的时候，每一行都会进行一个轮询操做code

触发器的范围： INSERT、DELETE、UPDATEhtm

触发器的时机： BEFORE、AFTER

建立触发器: 特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON student FOR EACH ROW

BEGIN

IF NEW.gender == 'M' THEN # NEW == student

INSERT into Male_stu(sex) VALUES('M'); # 输入性别为M，则插入Male_stu

ELSE

INSERT into Feamle_stu(sex) VALUES('W');

ELSEIF

END

INSERT into student(gender, class_id, sname) values('W', 1, '哈哈哈')

注意： 更新操做须要2个值，一个NEW传入的值，一个OLD的值

删除触发器

drop TRIGGER tri_before_insert_tb1

存储过程

存储过程是一个SQL语句集合[可增删改查在一个函数里]，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行；内部可又有for等语句。

注意：执行存储过程，确定会先执行里面的sql语句的，且只能返回一条结果集，全部有多表的联合查询操做是，最好合并为一条结果集返回。

存储过程

a. 可写复杂逻辑

b. 参数：in   out   inout

c. 结果集：select ...

# 建立无参数的存储过程，相似函数的建立

CREATE PROCEDURE p1()

BEGIN

select * from student;

END

# 存储过程调用

call p1() # 使用CALL 存储名便可， 执行存储过程，显示结果

删除存储过程：

drop procedure p1;

# 建立有参数的存储过程，用来执行自定义变量和获取sql集[结果集只能有一个，但能够拼接结果集]

# 对于存储过程，能够接收参数，其参数有三类：

in         仅用于传入参数用

out         仅用于返回值用，在内部直接赋值后外部调用能够直接获取到内容[Mysql自动帮咱们创建连接关系]

inout        既能够传入又能够看成返回值

create procedure p3(in i1 int,in i2 int,inout i3 int, out r1 int)

BEGIN

DECLARE temp1 int; # DECLARE声明变量，且存储过程里面必须使用

DECLARE temp2 int default 0; # 声明默认变量值

set temp1 = 1;

set r1 = i1 + i2 + temp1 + temp2;

set i3 = i3 + 100; # 功能一：自定义的函数操做

select * from student; # 功能二：查询并返回结果集，且一次只能返回一个，但能够拼接结果集

END; # 注意封号是用来执行结果的，没有封号则不会在执行存储

-- 执行存储过程： 使用CALL 存储名便可

set @t1 =4; # 必须带@符号

set @t2 = 0;

CALL p3 (1, 2 ,@t1, @t2); # 执行存储，而且自动返回了select * 的结果结合

SELECT @t1,@t2; # 单独执行此行，仅仅返回了自定义的函数结果

事务：Innodb支持事务

事务用于将某些操做的多个SQL做为原子性操做，一旦有某一个出现错误，便可回滚到原来的状态，从而保证数据库数据完整性。

delimiter \ \

create

PROCEDURE

p1(

OUT

p_return_code

tinyint

)

BEGIN

DECLARE

exit

handler

for sqlexception

BEGIN

-- ERROR

set

p_return_code = 1;

rollback;

END;

DECLARE

exit

handler

for sqlwarning

BEGIN

-- WARNING

set

p_return_code = 2;

rollback;

END;

START

TRANSACTION;

DELETE

from tb1;

insert

into

tb2(name)

values('seven');

COMMIT;

-- SUCCESS

set

p_return_code = 0;

END\ \

delimiter;

支持事务的存储过程

函数

函数： 内置函数 +  自定义函数

内置函数：

SELECT 1;

select CHAR_LENGTH('hello world'); #11,返回字符长度

SELECT CONCAT('hello ','world ','2017'); # hello world 2017，字符拼接

SELECT CONCAT_WS('_','hello ','world ','2017'); #hello _world _2017,添加了分隔符的字符拼接

SELECT CONV('8', 10, 2); #1000， 进制转换 10进制转化2进制

SELECT FORMAT(123456.2,2);#123,456.20 小数点后保留2位

SELECT LOWER('HELLO'); # 大写变小写

SELECT UPPER('hello'); # 小写变大写

SELECT INSERT('hello',0,2,'YY'); # 不更改，因此说明字符的替换是从第一个位置开始的

SELECT INSERT('hello',1,2,'YY'); # YYllo，从第一个位置开始替换

# 特别的：

# 若是pos超过原字符串长度，则返回原字符串

# 若是len超过原字符串长度，则由新字符串彻底替换

SELECT INSTR('hello','e'); # 2， 返回e出现的索引位置

SELECT LEFT('hello', 3); #hel, 获取前3个字符

SELECT RIGHT('hello',3); #llo, 从右边取出3个值

SELECT SUBSTRING('hello',1,3); #hel, 默认从第一个位置开始取

SELECT TRIM(' ' ' hello ')# hello, 移除左右的空格

SELECT LTRIM(' hello world '); #helloworld,其引导空格字符被删除。

SELECT RTRIM(' hello world'); #hello world,结尾空格字符被删去

SELECT LOCATE('ll','hello'); # 3, 返回字符串所在的位置

SELECT REPEAT('h',5) #hhhhh, 重复前面的字符n次

SELECT REPLACE('hello','ll','yy')#heyyo, 替换字符

SELECT REVERSE('hello') #olleh, 字符反转

SELECT SPACE(2)# 返回2个空格

自定义函数：函数仅仅支持传递参数，返回一个结果，不容许写sql,不支持返回结果集

建立函数：

create function f1(i1 int,i2 int) # 传递2个参数

returns int # 返回结果，相似Java publist int f1(int i1, int i2)

BEGIN # 函数内容,函数内容不容许写sql, 不容许获取结果集

declare num int;

set num = i1 + i2;

# declare a int; # 函数里利用 select into 也能够实现赋值的操做

# select nid into a from student where name = 'hhh'; # 将nid值赋给a

return(num); # 返回结果

END;

执行函数：

SELECT f1(2,3) # 5

删除函数：

drop function f1;

函数和存储过程的区别：

执行计划

相对比较准确表达出当前SQL运行情况，根据参考信息能够进行SQL优化通常显示All/Index的时候，效率不高，由于All 是全数据表扫描，index是全索引表扫描，并且rows里面的数据都是相对的，不是很准确。

- limit 的好处，找到第一个后就不在继续查找，效率相比较高

select * from tb1 where email='123'              -->[不推荐]

select * from tb1 where email='123' limit 1;   -->[推荐]

EXPLAIN select sid from student;

EXPLAIN select sid from student;

EXPLAIN select sid from student limit 1; # 也是从表扫描，可是找到第一条后，后面就不执行了

EXPLAIN select sid from student where sid < 12;[因此建立表的时候，能够考虑将列设置为索引]

对SQL进行优化

- 对须要进行范围查找的列进行索引设置，由于在查找 , !=进行操做的时候，又是全局查找了

- 对于全表查找，最好加上limit, 由于有了limit查找到了数据后，就不在继续向下查找了

- 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system / const

- 避免使用select *

- count(1)或count(列) 代替 count(*)

- 建立表时尽可能时 char 代替 varchar[char：定长用于固定长度的表单提交数据存储效率高,  varchar：不定长，效率偏低]

- 表的字段顺序固定长度的字段优先[varchar, text是不定长]

- 组合索引代替多个单列索引(常常使用多个条件查询时，组合索引比单独索引的合并快)

- 尽可能使用短索引[指定列的某几个字符为索引]

- 使用链接(JOIN)来代替子查询(Sub-Queries)

- 连表时注意条件类型需一致

- 索引散列值(重复少)不适合建索引，例：性别不适合