本章目录
视图是指计算机数据库中的视图,是一个虚拟表,其内容由查询结果来定义。
同真实的表一样,视图包含一系列带有名称的行和列数据
视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成
如果基表中的数据发生变化,则从视图中查询出的数据也随之改变
定制用户数据(聚焦特定的数据)
简化数据操作
细粒化的安全机制
合并分离的数据
语法:
create view view_name as
select column_name(s) from table_name(s) where condition
view_name:视图名。
column_names(s):视图中的字段列表,可以来源于多个表。
table_name(s):表名,可以来源于多个表。
condition:条件表达式,如果是多个表,则该表达式还包含表的连接条件。
示例:
利用 Navicat for MySQL 工具创建视图,该视图用于查看战争题材类电影的相关信息,要求输出电影名、导演名、票价和片长,按照片长升序显示
示例:
创建一个视图,该视图用于获得所有影片的排片情况,要求输出电影名、片长、放映日期、放映时间和放映厅名,按放映日期降序、放映时间升序显示
create view v_schedule_info as
select movieName 电影名 ,filmLength 片长 ,screenDate 放映日期 ,screenTime 放映时间 ,
sh.screeningHall 放映厅 from movie m left join `schedule` s on m.id=s.movieId
left join screening_hall sh on s.hallId=sh.id order by screenDate desc,screenTime
由于视图中的列不仅可以是来源于基表中的数据列,还可以是基于基表数据列的表达式计算或聚合函数值。所以,我们可以利用这一特性解决数据库复杂应用
示例:
创建一个视图,该视图用于生成每个客户的订票总金额,然后利用该视图来更新每个客户的订票总金额
(1)在售票表中,根据客户编号分组汇总出每个客户的订票总金额
create view v_calCusFee as select customerID 客户编号 ,sum(purchasePrice) 订票总金额 from ticket_sell group by customerId
(2)查询视图 v_calCusFee 显示所有客户的订票总金额
select * from v_calCusFee
(3)利用视图 v_calCusFee 中订票总金额的值更新客户表 totalFee 列值
update customer set totalFee=
(select 订票总金额 from v_calCusFee where 客户编号 =customer.id)
示例:
将获取的每个客户的购票详细数据封装到一个视图,以后每次需要获取客户的购票详情只需要查询该视图即可
create view v_ticket_purchase as
select username 客户名 ,movieName 电影名 ,screenDate 放映日期 ,screenTime 放映时间 ,
purchasePrice 实际购票价(元)from customer c, `schedule` s, movie m, screening_hall sh, ticket_sell ts
where c.id=ts.customerId and s.id=ts.scheduleID and s.movieID=m.id and s.hallID=sh.id order by username,screenDate,screenTime
建立索引后,每当发起查询请求时,数据库则直接在索引 的基础上执行检索(搜索、查询)算法,然后快速地找到 相应记录
在数据库系统中建立索引主要有以下作用
索引的优点
索引的缺点
由于在创建、使用索引时,会损失一定的系统性能,所以在工程实践中需综合考虑性能与效率,决定是否创建索引,以及在哪些列上创建索引。一般情况下,可依据下述标准创建索引
一般而言,不应该创建索引的列具有下列特点
常规索引(Regular Index,也称普通索引)
主键索引(Primary Key,也称主键)
唯一索引(Unique Key)
全文索引(Full Text,也称全文检索)
外键索引(Foreign Key,简称外键)
只有 InnoDB 存储引擎的表才支持外键。5.6 及其以上版本的 MySQL 的默认存储引擎是 InnoDB。
自动创建索引
用户创建索引
示例:
影院在线售票平台浏览影片信息时需要按影片价格排序显示,为了提高检索效率,往往在电影表票价列上创建索引(此处“索引”为“常规索引”)
在索引编辑窗口空白处,单击右键弹出“添加索引”,用于新增索引
在“名”处填写索引名“idx_ticketPrice”,在“栏位”处下拉选择索引列“ticketPrice”,“索引类型”默认选择为“Normal”,“索引方式”默认选择为“BTREE”
1、使用“alter 表 add 索引”命令创建索引
语法:
alter table table_name add index index_name (column_list)
alter table table_name add unique index_name (column_list)
alter table table_name add primary key(column_list)
上述 3 条语句分别创建了普通索引、唯一索引和主键索引。
table_name 为需要增加索引的表名。
column_list 指出对哪些列进行索引,多列时各列之间用逗号分隔。 索引名
index_name 可选,缺省时,MySQL 将根据第一个索引列赋一个名称。
示例:
使用 alter 命令在电影表(movie)的票价(ticketPrice)列上创建索引的 SQL 语句
alter table movie add index idx_ticketPrice(ticketPrice)
省略索引名的 SQL 语句
alter table movie add index (ticketPrice)
使用 alter 命令在电影表的电影名(movieName)列上创建唯一索引的 SQL 语句
alter table movie add unique uni_movieName(movieName)
2、使用“create 索引”命令创建索引
语法:
create index index_name on table_name (column_list)
create unique index index_name on table_name (column_list)
table_name、index_name 和 column_list 具有与“alter table add 索引”命令中的相关词汇具有相同的含义。
索引名为必须项。
不能使用 create index 语句创建主键索引。
示例:
使用 create 命令,分别在电影表的 ticketPrice 列和 movieName 列上创建唯一索引
create index idx_ticketPrice on movie(ticketPrice)
create unique index uni_movieName on movie(movieName)
使用 drop 命令和 alter 命令删除索引的命令
drop index index_name on table_name
或 alter table table_name drop index index_name
使用 drop 命令和 alter 命令删除 movie 表的 idx_ticketPrice 索引的命令
drop index idx_ticketPrice on movie
或 alter table movie drop index idx_ticketPrice
复合索引是指一个索引中包含多个列,复合索引也称多列索引或组合索引。索引列既可以为单个列,也可以为多个列
查询票价低于 80 元且张涵予为主演之一的战争题材的电影信息
select * from movie m, movie_type mt where m.typeID=mt.id and typeName=' 战争 '
and ticketPrice<80 and actors like '% 张涵予 %'
如果只在多列上创建一个索引,即在 ticketPrice 列和 actors 列上创建一个复合索引,那么就只需要执行一次检索即可完成任务,以下命令将在ticketPrice 列和 actors 列上创建组合索引 idx_ticketPrice_actors
create index idx_ticketPrice_actors on movie(ticketPrice,actors)
如果在多个列上分别创建了索引,MySQL 会试图选择一个限制最严格的索引。但是,即使是限制最严格的单列索引,它的限制能力也肯定远低于在多个列上所创建的复合索引,所以在多个列上创建复合索引的检索效率要高于分别在这些列上创建单个索引的检索效率。
数据库触发器定义了一系列操作,这一系列操作称为触发程序,当触发事件发生时,触发程序会自动运行
触发器主要用于监视某个表的 insert、update 以及 delete 等数据维护操作,这些维护操作可以分别激活该表的insert、update 或 delete 类型的触发程序运行,从而实现数据的自动维护
触发器可以实现的功能包括:使用触发器实现检查约束、维护冗余数据以及维护外键列数据等
触发器优点
MySQL 创建触发器的语法
语法:
create triger 触发器名 触发时间 触发事件 on 表名 for each row
begin
触发程序
end;
触发器是数据库对象,因此创建触发器时,需要指定该触发器隶属于哪个数据库
触发器是基于基表的,不能基于临时表(temporary 类型的表)和视图
MySQL 的触发事件有 3 种:insert、update 及 delete
触发器的触发时间有两种:before 和 after for each row 表示行级触发器
触发程序中可以使用 old 关键字和 new 关键字
在 before 触发程序中,可使用“set new.col_name=value”更改 new 记录的值,但在 after 触发程序中,由于记录的值已经发生了变更,因此不能使用“set new.col_name=value”更改 new 记录的值。
在 MySQL 中,可以使用触发器实现检查约束逻辑
示例:
使用触发器实现检查约束,用于确保新上线电影的片长不能超过 180 分钟
delimiter $$
drop trigger if exists movie_insert_before_trigger; -- 如果触发器存在,则删除
create trigger movie_insert_before_trigger before insert on movie for each row
begin
-- 如果片长大于 180 分钟
if(new.filmLength>180) then
insert into mytable values(0); -- mytable 表不存在
end if;
end;
$$
delimiter ;
示例:
当某部电影的“优惠票价激活”状态发生变更,或“票价”“优惠票价”的值发生了变更,使用触发器实现级联更新售票表中相应记录的“实际票价”的值
delimiter $$
drop trigger if exists movie_update_after_trigger;
create trigger movie_update_after_trigger after update on movie for each row
begin
declare _scheduleID int; -- 排片编号
declare state varchar(20);
-- 定义当前发生数据更新的电影所对应的拍片编号的游标
if(new.isActive<>old.isActive||new.ticketPrice<>old.ticketPrice
||new.preferentialPrice<>old.preferentialPrice) then
if(new.isActive=0) then -- 票价没有优惠,实际票价等于票价
update ticket_sell set purchasePrice=new.ticketPrice where scheduleID=_scheduleID;
else -- 票价有优惠,实际票价等于优惠价
update ticket_sell set purchasePrice=new.preferentialPrice
where scheduleID=_scheduleID;
end if;
end if;
end while discount;
close scheduleID_cursor; -- 关闭游标
end
$$
delimiter ;
在触发程序中修改记录时,使用 update 语句时要特别小心,因为该语句会激活该表的 update 触发器,它可能导致陷入死循环。
在日常生活和工作中,经常会遇到以下问题,如果甲乙客户去银行转账,中间系统出现故障,导致甲客户账户少了500元,而乙客户账户却没有多500元,这就导致了数据的不一致性,通过数据库事务,可以解决该问题
事务是一个由用户所定义的完整的工作单元,一个事务内的所有语句作为一个整体来执行,或者全部执行,或者全部不执行
当遇到错误时,可以回滚事务,取消事务内所做的所有改变,从而保证数据库中数据的一致性和可恢复性
数据库事务有四大特性,简称 ACID
默认情况下,MySQL 开启了自动提交(autocommit)
这就意味着任意一条 DML 中的 insert 语句、update语句和 delete 语句,一旦发送到 MySQL 服务器上,MySQL 服务实例会立即解析、执行,并将新增、更新结果提交到数据库文件中,成为数据库中永久的数据
关闭自动提交的方式有两种:一种是显式地关闭自动提交,另一种是隐式地关闭自动提交
显式地关闭自动提交
隐式地关闭自动提交
使用 MySQL 命令“start transaction;”可以隐式地关闭自动提交,隐式地关闭自动提交不会修改系统会话变量@@autocommit 的值。
start transaction:标识一个事务的开始,即启动事务
commit:提交事务。标识一个事务的结束,事务内所修改的数据被永久保存到数据库中
rollback:回滚事务。标识一个事务的结束,表明事务执行过程中遇到错误,事务内所修改的数据被回滚到事务执行前的状态
在实际开发过程中,不建议使用 命令“set autocommit=0;”显式地关闭自动提交,而建议选用“start transaction;”命令。它不仅可以开启新的事务,还可以隐式地关闭 自动提交,而且不会影响系统变量 @@autocommit 的值
示例:
客户“雷亚波”一共购买了两款商品,所以除在订单表(Orders)上生成一条记录外,还应在订单明细表(OrdersDetail)上生成两条记录
换言之,需要在这两张表上分别进行 insert 操作,即订单维护应包含这两个新增操作,它们是一个业务整体,仅允许全部成功,如果其中有一个失败,则撤销这两个新增操作
在存储过程中使用数据库事务技术可以很好地实现上述需求
delimiter $$
create procedure proc_insertOrdersAndOrdersDetail(
_customerName varchar(20),
_title1 varchar(50),
_quantity1 int,
_title2 varchar(50),
_quantity2 int
)
modifies sql data
begin
declare state varchar(20);
declare _customerID int; -- 客户编号
declare _productID1 int; -- 第 1 件商品编号
declare _productID2 int; -- 第 2 件商品编号
declare _ordersID int; -- 订单编号
declare _ordersDate date default current_date(); -- 订单生成日期,默认为当前日期
-- 定义错误处理
declare continue handler for sqlexception set state='error';
select customerID into _customerID from customer where customerName=_customerName;
start transaction; -- 启动事务
-- 添加订单表记录
insert into orders(customerID,ordersDate) values(_customerID,_ordersDate);
if(state='error') then
select ' 添加订单失败 ';
rollback; -- 回滚事务
else
select max(ordersID) into _ordersID from orders; -- 获取新增订单的订单编号
select productID into _productID1 from product where title=_title1;
-- 添加第 1 件商品到订单明细表
insert into ordersDetail(ordersID,productID,quantity)
values(_ordersID,_productID1,_quantity1);
if(state='error') then
select concat(' 添加 ',title1,' 订单明细失败 ');
rollback;
else
select productID into _productID2 from product where title=_title2;
-- 添加第 2 件商品到订单明细表
insert into ordersDetail(ordersID,productID,quantity)
values(_ordersID,_productID2,_quantity2);
if(state='error') then
select concat(' 添加 ',title2,' 订单明细失败 ');
rollback;
else
select ' 成功添加订单和订单明细数据 ' as 显示结果 ;
commit; -- 提交事务
end if;
end if;
end if;
end
$$
delimiter ;
示例:
执行以下批处理将生成一个订单,该订单的订购项为两份“菠萝爆肉片”和一件“资生堂菲婷”,下单时间为当前时间,另外还生成两条对应的订单明细记录
set @customerName=' 雷亚波 ';
set @title1=' 菠萝爆肉片 ';
set @quantity1=2;
set @title2=' 资生堂菲婷 ';
set @quantity2=1;
call proc_insertOrdersAndOrdersDetail(@customerName,@title1,@quantity1,@title2,@quantity2);
手机扫一扫
移动阅读更方便
你可能感兴趣的文章