欢迎您的光临,本博所发布之文章皆为作者亲测通过,如有错误,欢迎通过各种方式指正。由于本站位于香港虚拟主机,故速度比较慢。

文摘  MySQL数据库高级用法(一):视图、事务、索引、存储过程、触发器

MySQL 本站 173 0评论

一、视图


对于复杂的查询,在多次使用后,维护是一件非常麻烦的事情 

解决:定义视图 

视图本质就是对查询的一个封装,虚拟的表,一旦封装的内容改变了,视图的内容也随着用 


· 视图是一个虚拟表,从一个或者多个表中导出,视图的行为与表非常相似

· 在视图中可以使用select语句查询数据,以及使用insert、update和delect语句修改记录。

· 视图操作、使用方便,而且可以保证数据库系统的安全(可以限制表中的机密数据)。

· 视图一经定义遍储存在数据库中,与其相应的数据并没有像表那样在数据库中再储存一份,通过视图看到的数据只是存放在基本表中的数据。


对视图的操作与对表的操作一样可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时,相应的基本表的数据也要发生变化;

同时,对基本表中的数据进行修改的时候,这种变化也自动地反映到视图中。


通俗的讲,视图就是一条select语句执行后返回的结果集。

视图是对若干张基本表的引用,是一张虚表,查询语句执行的结果,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变);方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性。


视图的作用


· 提高了重用性,就像一个函数

· 对数据库重构,却不影响程序的运行

· 提高了安全性能,可以对不同的用户

· 让数据更加清晰


1.创建视图:

单个表上创建视图、多个表上创建视图


创建的语法形式:

creat view 视图名称 (视图的字段名称表) as select语句;

建议视图名称以 v_开头


# 示例一:

cteate view v_new_table (id, name, glass) 

    as select table1.id, table1.name, table2.glass 

    from table1, table2 where table1.g_id=table2.id;


# 示例二:

create view v_new_table 

    as select table1.*, table2.name 

    from table1 left join table2 

    on table1.g_id=table2.id;


查看数据库中所有表(show tables),会把视图表显示出来。


下面这个查询就被定义为一个视图,在我们下次使用时直接调用视图就可以了,有点像面向对象里面的调用方法。 

视图的用途就是查询 

select * from stuscore; 

111.jpg

就如图所示,直接调用视图,不用再写sql语句,这样避免多次使用相同sql语句,简化了代码。


2.查看视图:

select * from 视图表名称;


3.修改视图:

create or replace view 语句修改视图

· 该语句与创建视图语句一样:

· 当视图存在的时候,修改语句对视图进行修改;

· 当视图不存在的时候,创建视图。


alter 语句修改视图:

alter view 视图名 as select语法;


4.删除视图:

drop view [if exists] 视图名;


实例:


创建视图

create view v_goods as

select g.*,c.name as cate_name,b.name as brand_name from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brands as b on g.brand_id=b.id;

查看表

show tables;

查看所有

select * from v_goods;


二、事务


为什么要有事务

事务广泛的运用于订单系统、银行系统等多种场景,事务只对增删改有效


所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。 

例如:银行转账工作:从一个账号扣钱并使一个账号赠款,这两个操作要么都执行,要么都不执行。所以,应该把他们(扣款、赠款)看成一个事务。


事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性


事务四大特效(简称ACID)


原子性(Atomicity):

一个事务必须被视为一个不可分割的最小工作单元,整个事务中所有的操作要么全部执行,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作,这就是事务的原子性。


一致性(Consistency):

数据库总是从一个一致性的状态转换到另一个一致性的状态。(相当于前面的例子,要么所有的操作要么全部执行,要么全部失败回滚)


隔离性(Isolation):

通常来说,一个事务所做的修改在最终提交以前,对其他的事务视而不见。


持久性(Durability):

一旦事务提交,则其所做的修改会永久保存到数据库。(此时即使系统崩溃,修改的数据也不会丢失。可靠性和高速度不可兼得,innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。)


事务命令


重点:数据表的引擎必须是innodb类型才可以使用事务,这是mysql表的默认引擎。

查看表的创建语句,就可以看到表的引擎engine。


1.开启事务:

开启事务后执行修改命令,变更会维护到本地缓存中,而不会维护到物理表中。

begin; 或者 start transaction;


2.提交事务:

将缓存的书库提交变更维护到物理表中。

commit;


3.回滚事务:

放弃缓存中的变更数据

rollback;


· BEGIN或START TRANSACTION;显式地开启一个事务; 

· COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的; 

· ROLLBACK;有可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的务,并撤销正在进行的所有未提交的修改; 

· SAVEPOINT identifier;SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT; 

· RELEASE SAVEPOINT identifier;删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常; 

· ROLLBACK TO identifier;把事务回滚到标记点;


三、索引


对于一个应用来说,对数据库的读写比例基本上是10:1,即读多写少

而且对于写来说极少出现性能问题,大多数性能问题都是慢查询

提到加速查询,就必须用到索引


什么是索引?

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。 

更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度


索引的目的?

索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的,如果我想找到m开头的单词呢?或者ze开头的单词呢?是不是觉得如果没有索引,这个事情根本无法完成?


索引的使用


1.查看索引 

show index from 表名; 


2.创建索引 

如果指定字段是字符串,需要指定长度,建议长度与定义字段时的长度一致 

字段类型如果不是字符串,可以不填写长度部分 

create index 索引名称 on 表名(字段名称(长度))


若字段名是字符串需要带上长度,且尽量与定义字段时的长度相同。

alter table 表名 add index 索引名(字段名(长度))


注意:当数据表创建主键和外键的时候,会自动创建索引

因此用主键和外键搜索数据,相当快。


3.删除索引

drop index 索引名称 on 表名;

alter table 表名 drop index 索引名;


索引demo


创建测试表testindex

create table test_index(title varchar(10));


使用python程序通过pymsql模块 向表中加十万条数据

from pymysql import connect

def main():

# 创建Connection连接

conn = connect(host='localhost',port=3306,database='jing_dong'

# 获得Cursor对象

cursor = conn.cursor()

# 插入10万次数据

for i in range(100000):

cursor.execute("insert into test_index values('ha-%d')" % i)

# 提交数据

conn.commit()

if __name__ == "__main__":

main()


查询

开启运行时间监测:

set profiling=1;

查找第1万条数据ha-99999

select * from test_index where title='ha-99999';

查看执行的时间:

show profiles;


为表title_index的title列创建索引:

create index title_index on test_index(title(10));

执行查询语句:

select * from test_index where title='ha-99999';

再次查看执行的时间

show profiles;


注意:

· 要注意的是,建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格,就没有

· 必要为一个很少使用的where字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立另外的索引。 

· 建立索引会占用磁盘空间

· 在索引创建完毕后,对表的查询性能会大幅度提升,但是写性能会降低


应该对哪些字段做索引:

· 应该对数据量小的字段做索引(数据量小,比对查询的快)

· 应该对区分度高的字段做索引

· 索引字段不要参与运算


四、存储过程


为了完成特定功能的sql集合,编译后存储在服务器端数据库中,利用存储过程可以加速sql 语句的执行。


存储过程的分类以及优点


存储过程可以分为两类:系统存储过程自定义存储过程。


系统存储过程:系统存储过程是在sql 成功安装后存在系统数据库master 中的,这些存储过程是以sp- 为前缀命名的,主要功能是在系统表中获取信息,系统管理员可以通过简单的调用系统存储过程完成复杂的管理工作,调用系统存储过程时在其他非master数据库中也可以调用 。


自定义存储过程有用户自己定义完成特定功能,返回值只能表明返回值执行是否成功而且只能利用execute 完成存储过程。


优点:

· 提高应用程序的通用性和可移植性

· 可以更有效的管理用户操作数据库的权限

· 可以提高sql的速度

· 减轻服务器的负担。


创建并执行存储过程


语法结构:

Create procedure  过程名

@ming 参数类型  output

~~

~~~

As

Begin  命令块 end


创建完成后利用:execute 过程名   [参数值~~][output]


例子:

求0-5之间的和

alter proc proc3

as

declare  @sum int,@a int

select  @a=0,@sum=0

while @a<6

begin

set @sum=@sum+@a

set @a=@a+1

End

print '和是'+cast(@sum as varchar)

Go

Exectute proc3  


判断系统商品库存 以及购买者账户金额 决定交易是否进行

create proc shuiguo2

@idss varchar(10),

@buyer varchar(10),

@shuliang int

as

--判断库存

if (select numbers from Fruit where Ids=@idss)>@shuliang

begin

if(select price from Fruit where Ids=@idss )*@shuliang>(select account from Login where UserName=@buyer )

begin

--减库存

update fruit set numbers=numbers-@shuliang where Ids=@idss

--减余额

update Login set Account=Account-(select price from Fruit)*@shuliang where UserName=@buyer

end

else

begin

print'余额不足,请充值!'

end

end

else

begin

print'抱歉,库存不足!'

end

go


不带参数的存储过程 

带输入参数的存储过程

带有输入参数的数据查询功能的存储过程

带有输入输出参数的存储过程

带有登陆判断功能的存储过程

带有判断条件的插入功能的存储过程(exists)

带有判断条件的删除功能的存储过程

带有判断条件的更新功能的存储过程

加密存储过程 

Create proc 过程名

With encryption

As  命令块   Go


查看存储过程的属性以及功能代码信息

execute sp_help proc3 --查询存储过程的属性信息

execute sp_depends proc1--查询存储过程所使用的数据对象的信息,如果没有引用或者加密会提示    没有引用.

execute sp_helptext proc3--查询存储过程的功能代码,如果加密后不能被查询到

select name from car where type ='p'--能共查找本表创建存储过程的名字和时间


修改存储过程的功能代码

Alter proc 过程名

@参数   参数类型  ~~~~

As 

Begin   命令行   end


重命名存储过程

Execute sp_rename  原名, 新名


删除存储过程

Drop proc 过程名1,过程名2~~ 同时可以删除多个过程


带有判断条件的删除存储过程(与if exists语句结合)

存储过程的自动执行和监控  


五、触发器


触发器与存储过程的区别

1.存储过程是独立于表存在的,触发器需要依附某个表的某个操作。

2.存储过程需要使用名称去调用才能执行,触发器则在表的操作过程中自动被触发调用。


触发器的分类和作用


定义:

触发器是一种特殊类型的存储过程,通过事件的触发来被执行,例如update delete drop alter drop等。   在sql 中有两种方法可以保持数据的完整性和有效性:约束和触发器约束直接设置在表内只能执行一些比较简单的功能,而触发器可以执行比较复杂的过程。


作用:

· 可以调用存储过程

· 强化数据条件约束

· 跟踪数据库内数据的变化并判断数据变化是否符合数据库的要求

· 级联和并行运行。


分类:

DML触发器 和 DDL触发器


DML触发器:data manipulation language又可以分为 事后触发器(after触发器)替代触发器 (instead of) 

· After 触发器 先执行变得增删改操作后在触发触发器 。

· Instead of 触发器 不执行表的增删改操作,它的这些操作只起到触发触发器的功能。


DDl触发器用来执行数据库的管理任务。

注释:

触发器中两个临时表:inserted,deleted

这两个表是临时表,触发器执行完成后,会自动消失,再次触发会再次创建。

这两个表的结构与on后面的那表的结构是一样的(列名、列数、类型)。而且里面只有一条记录。

插入操作--把新增的数据放到inserted表中。

删除操作--把删除的数据放到deleted表中。

修改操作--把旧数据放到deleted表中,把新数据放到inserted表中。


对两个临时表的使用。

从两个临时表中把数据取出来放到变量中,以备后面的使用。


创建执行事后触发器


After 触发器注意事项

· 触发器名在同一数据库中是唯一的;

· 可以利用with encryption 对触发器进行加密;  

· 只能定义在数据表中不能定义在视图上;

· after触发器的动作只能是update delete insert 三种 其中的一种或者多种。


创建语法:

Create trigger 触发器名  on  表名 for  insert[update  delete]

As

Begin

命令  

End


实例:运行触发器当数据表进行增删改时自动发送邮件

Create  trigger 触发器名 on表名 for insert[update ,delete]

As begin 

Execute xp_sendmail ‘邮箱地址’

end


创建与执行替代触发器


注意点:instead of触发器主要作用是使不可修改的视图能够支持修改。当然也可以对表进行操作。

构建语法:

Create trigger 触发器名 on 表名或者视图名 instead of  insert[delete update]

As

Begin

命令块

End

Go


查看触发器的属性以及功能代码信息

修改触发器的功能代码

重命名和删除触发器


Sp_rename 原名,新名  --重命名触发器

Drop trigger 触发器名【123】--可以同时删除多个触发器


禁用和启用触发器

Alter table 表名  disable trigger 触发器名   禁用触发器

Alter table 表名    enable trigger 触发器名   启用触发器

Alter table 表名    enable trigger 触发器名1,触发器名2  启用多个触发器触发器

Alter table 表名    enable trigger all  启用 这个表中的所有触发器


创建与执行ddl 触发器


DDL 触发器一般用于数据库的管理工作,其触发事件为 create alter drop 开头的语句


创建DDl 触发器语法结构:

Create trigger 触发器名 on 服务器或者是数据库

For或者after create[drop alter ]

As  begin

命令块   

End


注意点: DDl 触发器没有替代触发器只有事后触发器,不同是作用于数据库还是服务器


修改和删除ddl 触发器


触发器的使用


1)触发器的类型(触发事件)

1、insert触发器

2、update触发器

3、delete触发器


2)创建触发器

#语法:

Create trigger 触发器名 触发时间[before|after] 触发事件 on 表名 for each row

Begin

    //代码

end


3)new表和old表

1、这两个表是个临时表

2、当触发器触发的时候在内存中自己创建,触发器执行完毕后自动销毁

3、他们的表结构和触发器触发的表的结构一样

4、只读,不能修改


stuinfo curd


打开文件,内存中需要加载,会随即分配一个空间用来保存文件的所有数据,->old 

在新的一轮操作后,内存会生成新的空间,这个空间里面保存了新的数据变化->new


4)insert触发器

#在stuinfo中插入一个值,就会自动在stumarks中插入一条数据

#after insert 表示的是在insert动作执行完毕以后触发

#on stuinfo for each row  针对的stuinfo表,并且可以读取到每一行的变化

#触发器中定义的局部变量不能与表中的字段名一致,否则会发生字段识别问题(识别不出到底是字段,还是变量)

create trigger trig1

after insert on stuinfo for each row

begin

     declare sidno int default 0;

     declare nch int default 0;

     declare nmath int default 0;

     declare nseat int default 0;

     set sidno=new.sid;

     set nseat=new.seat;

     insert into stumarks set sid=sidno,ch=nch,math=nmath,seat=nseat;

end //


insert into stuinfo values(null,'随便','male',20,'合肥',12)//


5)update触发器

create trigger trig2

after update on stuinfo for each row

begin

    declare sidno int default 0;

    declare seatno int default 0;

    set seatno=new.seat;

    set sidno =new.sid;

    update stumarks set seat=seatno where sid =sidno;

end //


select ((select max(seat) from stuinfo)+1)//

update stuinfo set seat=12 where sid=12//


6)delete触发器

create trigger trig3

after delete on stuinfo for each row

begin

     declare sidno int default 0;

     set sidno =old.sid; #删除了新表里面就没有了,只能从老表里面拿

     delete from stumarks where sid=sidno;

end //


delete from stuinfo where sid =13//


#触发器能做钩子函数


7)查看 和 删除 触发器

show triggers\G

drop trigger if exists trig1//


参考网址:

http://www.cnblogs.com/woniu-net/p/4679765.html

https://blog.csdn.net/beichen0518/article/details/80820335

https://blog.csdn.net/t15263857960/article/details/82081864

https://blog.csdn.net/weixin_42932562/article/details/82963765


转载请注明: ITTXX.CN--分享互联网 » MySQL数据库高级用法(一):视图、事务、索引、存储过程、触发器

最后更新:2019-04-13 14:53:41

赞 (2) or 分享 ()
游客 发表我的评论   换个身份
取消评论

表情
(0)个小伙伴在吐槽