视图
视图: 视图是一个虚拟表(非真实存在),动态获取数据,仅仅能作查询操做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)
- 连表时注意条件类型需一致
- 索引散列值(重复少)不适合建索引,例:性别不适合