MySql教程

3.mysql基础2

本文主要是介绍3.mysql基础2,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

文章目录

  • 1.视图
  • 2.存储过程
  • 3.游标
  • 4.触发器
  • 5.管理事务处理
  • 6.字符集
  • 7.安全
  • 8.性能+维护

1.视图

视图是虚拟的表,不包含表中应有的任何列或数据,包含的是一个sql查询,由于视图不包含数据,每次使用必须处理查询执行时所需的任何一个检索,若使用多个联结和过滤创建了复杂的视图可能会导致性能问题

视图的作用

  • 重用sql语句,简化复杂的sql操作
  • 使用表的组成部分而不是整个表,保护数据,可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
  • 更改数据格式和表示,视图可返回与底层表的表示和格式不同的数据

1.规则

  • 视图必须唯一命名、对于可以创建的视图数目没有限制,创建视图需要足够的访问权限
  • 视图可以嵌套,order by可以用在视图中,但若从该视图检索数据select中页含有order by,那么该视图中的order by将被覆盖
  • 视图不能索引,也不能有关联的触发器或默认值
  • 视图可以和表一起使用

2.使用

  • create view创建视图
  • show create view {viewname};查看创建视图的语句
  • drop view {viewname};删除视图
  • create op replace view更新视图,更新视图实际上是更新基表,若视图定义中有以下操作,则不能进行视图的更新:分组、联结、子查询、并、聚集函数、distinct、导出列
--创建视图
create view productcustomers as
select cust_name,cust_contact,prod_id
from customers,orders,orderitems
where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num;
--使用
select cust_name,cust_contact
from productcustomers 
where prod_id = 'TNT2';

2.存储过程

保存的一条或多条sql语句的集合,可将其视为批文件,存储过程简化复杂的操作,保证安全性完整性,通过存储过程限制对基础数据的访问,减少了数据讹误的机会。使用存储过程比使用单独sql语句要快。

1.call调用存储过程,call接受存储过程的名字以及需要传递的参数。

--执行名为productpricing的存储过程,计算并返回产品的最低、最高、平均价格
call productpricing(
@pricelow,
@pricehigh,
@priceaverage)

2.create procedure创建存储过程,DELIMITER 指定结束符,mysql在遇到;时会执行语句

DELIMITER $$
create procedure productpricing()
begin
	select avg(prod_price) as priceaverage
	from products;
end$$
DELIMITER ;

3.drop procedure if exists删除存储过程

drop procedure productpricing if exists;

4.in传递给存储过程、out从存储过程传出、inout对存储过程传入和传出。

--创建
create procedure productpricing(
	out pl decimal(8,2),
	out ph decimal(8,2),
	out pa decimal(8,2),
)
begin
	select min(prod_price) as priceaverage
	into pl
	from products;
	select max(prod_price) as priceaverage
	into ph
	from products;
	select avg(prod_price) as priceaverage
	into pa
	from products;
end;
--调用,mysql变量必须以@开始
call productpricing(
@pricelow,
@pricehigh,
@priceaverage)
-- 获取检索数据
select @pricelow,@pricehigh,@priceaverage;

5.建立智能存储过程,使用declare建立了两个局部变量

--创建
create procedure ordertotal(
	in onumber int,
	in taxable boolean,
	out ototal decimal(8,2),
)
begin
	declare total decimal(8,2);
	declare taxrate int default 6;
	
	select sum(item_price*quantity) 
	from orderitems
	where order_num = onumber
	into total;
	
	if taxable
		select total+(total/100*taxrate) into total;
	end if;

	select total into ototal;
end;
--调用
call ordertotal(20005,0,@total);
select @total;
call ordertotal(20005,1,@total);
select @total;

3.游标

游标是一个存储在mysql服务器上的数据库查询,被select语句检索出来的结果集,在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。mysql游标只能用于存储过程(和函数)

1.使用游标

  • 使用游标前必须声明游标,这个过程没有检索数据,只是定义要使用的select语句
  • 声明游标后必须打开游标,这个过程用前面定义的select语句把数据实际检索出来
  • 对于填有数据的游标,根据需要检索各行
  • 结束使用后需要关闭游标

2.declare创建游标

create procedure processorders()
begin
	declare ordernumbers cursor
	for
	select order_num from orders;
end;

3.open cursor打开游标、close cursor关闭游标,close释放游标使用的内存和资源

open ordernumbers;
close ordernumbers;

4.fetch使用游标;使用fetch检索当前order_num到声明的名为o的变量中,在repeat内,直到done为真。done 通过定义 continue handler,当sqlstate '02000’出现时,set done=1

create procedure processorders()
begin
	declare done boolean default 0;
	declare o int;
	declare t decimal(8,2);

	declare ordernumbers cursor
	for
	select order_num from orders;
	declare continue handler for sqlstate '02000' set done=1;
	create table if not exists ordertotals (order_num int, total decimal(8,2));
	
	open ordernumbers;
	repeat
		fetch ordernumbers into o;
		call ordertotal(o,1,t)
		insert into ordertotals(order_num,total) values(o,t);
	until done end repeat;
	close ordernumbers;
end;

此存储过程不返回数据,但是它能够创建和填充另一个表

select * from ordertotals;

4.触发器

在某个表发生更改时自动处理,触发器是mysql响应以下任意语句deleteinsertupdate而自动执行的一条mysql语句,或位于beginend语句之间的一组语句,其他mysql语句不支持触发器

1.create trigger创建触发器,仅支持表,不支持视图。每个表每个事件,每次仅允许一个触发器,因此每个表最多支持6个触发器(deleteinsertupdate前后beforeafter)

  • 触发器名唯一(每个表中唯一)
  • 触发器关联的表
  • 触发器应该响应的活动(delete、insert、update)
  • 触发器何时执行

创建名为newproduct的触发器,在insert语句成功之后执行,指定for each row,代码对每个插入行执行,文本Product added将对每个插入行显示一次

create trigger newproduct after insert on products for each row select 'Product added';

2.drop trigger删除触发器,触发器无法更新或覆盖

drop trigger newproduct;

3.使用触发器

  • insert触发器代码内,可引用一个名为new的虚拟表,访问被插入的行
  • before insert触发器中,new中的值可以被更新
  • 对于auto_increment列,new在insert执行之前包含0,在insert执行之后包含新的自动生成值

在插入新订单到orders表时,触发器从new.order_num取得新订单号并返回,必须是after

create trigger neworder after insert on orders
for each row select new.order_num;

测试
在这里插入图片描述

  • delete触发器内,可以引用一个名为old的虚拟表,访问被删除的行
  • old中的值全是只读的,不能更新

在任意订单被删除前执行此触发器,它使用一条insert语句将old中要被删除的值保存到一个名为archive_olders的存档表中,需要线创建archive_orders表

create trigger deleteorder before delete on orders for each row
begin
	insert into archive_orders(order_num,order_date,cust_id)
	values(old.order_num,old.order_date,old_cust_id)
end;
  • 在update触发器代码中,可以引用一个名为old的虚拟表访问update前的值,引用一个new的虚拟表访问新更新的值
  • before update触发器,new中的值可能也被更新,old中值全是只读,不可更新

每次更新一个行时,new.vend_state中的值(用来更新表行的值)都用Upper(new.vend_state)替换

create trigger updatevendor before update on vendors
for each row set new.vend_state = Upper(new.vend_state);

5.管理事务处理

事务处理保证数据库完整性,成批的mysql操作要么完全执行,要么完全不执行。transactionrollbackcommitsavepoint

1.start transaction开始事务
2.rollback回退事务,rollback只能在一个事务处理内使用(在start transaction之后)

select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

3.commit提交事务

start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;

4.savepoint使用保留点,复杂的事务可能需要部分提交或回退,支持部分事务处理

savepoint deletel;
rollback deletel;

5.set autocommit = 0;更改默认提交行为

6.字符集

1.character setcollate字符集、编码、校对,_ci区分大小写、_cs不区分大小写

--显示所有可用的字符集以及每个字符集的描述和默认校对
show character set;
--显示所有可用校对
show collation;
create table mytable(
	column1 int,
	column2 varchar(10),
	column3 varchar(10) character set latin1 collate lation_general_ci
) default character set hebrew collate hebrew_general_ci;

7.安全

1.查看用户

查看数据库所有用户账号列表
use mysql;
select user from user;

2.创建用户账号identified by password指定登录密码、rename重命名

创建新用户账号
create user ben identified by 'password';
用户重命名
rename user ben to bforta;

3.drop删除用户账号

drop use bforta;

4.访问权限,创建用户账号后,必须分配访问权限,否则无法看到数据执行操作。show grants for查看访问权限、

show grants for ben;

USAGE表示没有权限
在这里插入图片描述
5.grant设置访问权限、revoke撤销权限

授予ben用户在crashcourse数据库所有表上使用select权限
grant select on crashcourse.* to ben;
revoke select on crashcourse.* from ben;

grant和revoke可以在几个层次上控制访问权限

  • 整个服务器:grant allrevoke all
  • 整个数据库:on database.*
  • 特定表:on database.table
  • 特定的列
  • 特定的存储过程

grant和revoke支持的权限all、alter、create、select、delete、drop等如下图
在这里插入图片描述
在这里插入图片描述
6.set password更改密码,不指定用户名时,更新当前登录用户的密码

set password for ben = password('new password');
set password = password('new password');

8.性能+维护

1.show variables;show status;查看当前设置
2.show processlist 显示所有活动进程以及其线程id执行时间;kill终结某个特定的进程(需要管理员方式登录)
3.explain解释sql
4.sql优化

这篇关于3.mysql基础2的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!