文章目录

• 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响应以下任意语句delete、insert、update而自动执行的一条mysql语句，或位于begin和end语句之间的一组语句，其他mysql语句不支持触发器

1.create trigger创建触发器，仅支持表，不支持视图。每个表每个事件，每次仅允许一个触发器，因此每个表最多支持6个触发器(delete、insert、update前后before、after)

• 触发器名唯一(每个表中唯一)
• 触发器关联的表
• 触发器应该响应的活动(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操作要么完全执行，要么完全不执行。transaction、rollback、commit、savepoint

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 set、collate字符集、编码、校对,_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 all、revoke 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优化