常见数据库对象

一、视图

1、视图的概念

（1）视图是指计算机数据库中的视图，是一个虚拟表， 其内容由查询结果来定义 。

（2）视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询 所引用的表，并且在引用视图时动态生成。

（3）如果基表中的数据发生变化，则从视图中查询出的数据也随之改变。

2、视图的优点

（1）定制特定用户数据

（2）简化数据查询操作

（3）细粒化的安全机制

视图是虚拟的，数据是随着基表的更新而更新的，用户不可以随意地更改和删除视图数据。

（4）合并分离的数据（union）

3、创建视图和使用

（1）语法

1 create view view_name as

2 select column_name ( s ) from table_name ( s ) where condition

3

4 view_name ：视图名。

5 column_names ( s ) ：视图中的字段列表，可以来源于多个表。

6 table_name ( s ) ：表名，可以来源于多个表。

7 condition ：条件表达式，如果是多个表，则该表达式还包含表的连接条件。

（2）使用方式

创建语句

navicate操作界面创建

（3）例子

1 ‐‐ 用 from 子查询语句方式查询每个员工的业绩数和业绩金额

2 CREATE VIEW v_workers_perform AS

3 SELECT w . NAME , SUM ( xd . num ) as orders , SUM ( xd . allMoney ) as money

4 FROM t_worker w ,

5 ( SELECT o . CASHIER_ID as USER_ID , COUNT ( * ) as num , SUM ( MONEY ) as allMoney

6 FROM t_order o WHERE o . STATUS IN ( '2' , '3' , '4' ) GROUP BY o . CASHIER_ID

7 UNION

8 SELECT o . COOK_ID as USER_ID , COUNT ( * ) as num , SUM ( MONEY ) as allMoney

9 FROM t_order o WHERE o . STATUS IN ( '2' , '3' , '4' ) GROUP BY o . COOK_ID

10 ) xd

11 WHERE w . WORKER_ID = xd . USER_ID GROUP BY w . WORKER_ID ORDER BY money DESC ,

12 orders DESC ;

4、解决数据库的复杂问题

1 ‐‐ 1 、创建视图：统计每个订单的真实金额

2 CREATE VIEW v_order_money AS

3 SELECT ORDER_ID , SUM ( f . MONEY * od . COUNT ) FROM t_orderdetail od 4 INNER JOIN t_food f on od . food_id = f . food_id

5 GROUP BY ORDER_ID ;

6

7 ‐‐ 2 、更新订单的金额为真实金额数据

8 UPDATE t_order o SET MONEY =

9 ( SELECT MONEY FROM v_order_money

10 where ORDER_ID = o . order_id );

二、索引

1.索引的基本知识

（1）概念

索引是一个单独的、物理的数据库结构，它依赖于表来建立。

索引是对数据库表中一个或多个列（如 employee 表的 name 列）的值进行排 序的结构，如果想按特定职员的姓来查找某个职员，与在表中搜索所有的行相比，索 引有助于更快地获取信息。

索引的作用是快速定位。 建立索引后，每当发起查询请求时，数据库则直接在索引 的基础上执行检索 （搜索、查询）算法，然后快速地找到 相应记录

（2）原理：

索引的本质就是排序。 当然，我们一般不会把原始数据集排序，而是把每条记录的键值和这条记录在数 据集中的位置，按键值次序做成一个规模较小的数据集，这也就是索引表了。

（3）优缺点：

优点

• 大大加快了数据的检索速度
• 创建唯一性索引，保证数据库表中每一行数据的唯一性
• 加速表和表之间的连接
• 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

缺点

• 索引需要占物理空间。
• 当对表中的数据进行增加、删除和修改时，数据库系统需要对索引进行动态维 护，降低了数据的维护速度。

（4） 应该创建索引 的场景

在 经常需要搜索的列 上，可以加快搜索的速度。 在作为主键的列上。 经常用在连接的列 上，这些列主要是一些外键，在这些列上建立索引可以加快连 接的速度。

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范 围是连续的。

在 经常需要排序的列 上创建索引，这样查询可以利用索引的排序，节省排序查询 的时间。

在 使用 where 子句的列 上创建索引，加快条件的判断速度。

（5） 不该创建 索引的场景

对于那些在查询中 很少使用或参考的列 不应该创建索引。由于增加了索引，反而 降低了系统的维护速度且增大了空间需求。

对于那些 只有很少数据值的列 而言，同样不应该增加索引，对于那些定义为 text、image 和 bit 数据类型的列不应该增加索引。因为这些列的数据量要么相当 大，要么取值很少。

当要求 修改性能远大于检索性能 时，不应该创建索引。因为修改性能和检索性能 是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。但是当减少 索引时，会提高修改性能，降低检索性能。

2.索引分类

（1）常规索引

一张数据表中可以有多个常规索引。

（2）主键索引

可以提高查询效率，并提供唯一性约束。

一张表中 只能有一个主键 ， 被标志为自动增长的字段一定是主键，但主键不一定

是自动增长 。

（3）唯一索引

可以提高查询效率，并提供唯一性约束。

一张表中可以 有多个唯一索引 。

（4）全文索引

目前搜索引擎使用的一种关键技术。

它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要

性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

（5）外键索引

外键字段如果没有指定索引名称，会自动生成。

外键会自动和对应的其他表的主键关联，它的主要作用是保证记录的一致性和完

整性。

3.创建索引 （1）自动创建

MySQL 在创建唯一索引约束或主键索引约束时，系统会自动在这些约束列上创 建 唯一索引和主键索引 ；

系统通常也会在外键列上自动创建 外键索引 。

（2）用户创建索引

navicat的表结构设计界面创建索引；

create index 索引名

1 create index index_name on table_name ( column_list )

2 create unique index index_name on table_name ( column_list )

3 ‐‐ 不能使用 create index 语句创建主键索引。

alter 表 add 索引

1 ‐‐ 创建普通索引

2 alter table table_name add index index_name ( column_list )

3 ‐‐ 创建唯一索引

4 alter table table_name add unique index_name ( column_list )

5 ‐‐ 创建主键索引

6 alter table table_name add primary key ( column_list ) ‐‐ 没有索引名

7

8 column_list 指出对哪些列进行索引，多列时各列之间用逗号分隔 , 多个列时表示复合索

引。

9 索引名 index_name 可选，缺省时， MySQL 将根据第一个索引列赋一个名称

（3）删除索引

1 drop index index_name on table_name

2 或

3 alter table table_name drop index index_name

4 ‐‐ 删除主键索引

5 ALTER TABLE table_name DROP PRIMARY KEY ;

（4）例子

1 ‐‐ 给职工姓名加唯一索引

2 create unique index idx_username on t_worker ( name );

3 ‐‐ 删除索引

4 drop index idx_name on t_worker ;

5

6 ‐‐ 给员工编号添加主键索引

7 ALTER table t_worker add PRIMARY key ( worker_id );

三、触发器

1.触发器简介

数据库触发器定义了一系列操作，这一系列操作称为触发程序，当触发事件发生

时，触发程序会自动运行。

触发器主要用于监视某个表的 insert、update 以及 delete 等数据维护操作，

这些维护操作可以分别激活该表的insert、update 或 delete 类型的触发程序运行，

从而实现数据的自动维护。

2.定义触发器

（1）语法结构：

1 create triger 触发器名 触发时间 触发事件 on 表名 for each row

2 begin

3 触发程序

4 end ;

5

6 触发器是数据库对象，因此创建触发器时，需要指定该触发器隶属于哪个数据库

7 触发器是基于基表的，不能基于临时表（ temporary 类型的表）和视图

8 MySQL 的触发事件有 3 种： insert 、 update 及 delete

9 触发器的触发时间有两种： before 和 after

10 for each row 表示行级触发器

11 触发程序中可以使用 old 关键字和 new 关键字；

12

13 在 before 触发程序中，可使用 “ set new . col_name = value ” 更改 new 记录的值，

14 但在 after 触发程序中，由于记录的值已经发生了变更，

15 因此不能使用 “ set new . col_name = value ” 更改 new 记录的值。

对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而

UPDATE语句可以在和NEW以及OLD同时使用。

3.使用触发器实现检查约束

（1）例子

1 ‐‐ 删除订单时候同时删除订单里的明细信息

2 delimiter $$

3 create TRIGGER tri_delOrd AFTER DELETE ON t_order for each row

4 BEGIN

5 DELETE FROM t_orderdetail WHERE ORDER_ID = old . ORDER_ID ;

6 END

7 $$

8 delimiter ;

四、数据库事务

1.事务概述

事务是一个由用户所定义的完整的工作单元，一个事务内的所有语句作为一个整

体来执行，或者全部执行，或者全部不执行。

当遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中

数据的一致性和可恢复性。

2.事务特性

四大特性，简称 ACID：

原子性（atomic、atomicity）：事务必须是一个原子工作单元。对于其数据修

改，或者全都执行，或者全都不执行。

一致性（consistent、consistency）：事务在完成时，必须使所有的数据都保

持一致的状态，即事务执行的结果必须是使数据库从一个一致性状态转换为另一个一

致性状态。

隔离性（isolation)：由并发事务所作的修改必须与任何其他并发事务所作的修

改隔离。

持久性（duration、durability）：事务完成后，其对于系统的影响是永久性

的，即事务一旦完成，即使系统出现致命的故障，也将一直保持修改之后的状态。

3.关闭 MySQL 自动提交

（1）显式地关闭自动提交;( 不建议使用 )

1 show variables like 'autocommit' ; ‐‐ 查出来这个变量的值为 on 或 1 时表示开启了自

动提交

2 set autocommit = 0 ; ‐‐ 显式地关闭 MySQL 自动提交

（2）隐式地关闭自动提交;

1 start transaction ：标识一个事务的开始，即启动事务。

2 commit ：提交事务。标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。

3 rollback ：回滚事务。标识一个事务的结束，表明事务执行过程中遇到错误，

4 事务内所修改的数据被回滚到事务执行前的状态。

5.MySQL 事务应用例子

1 ‐‐ 删除订单时候同时删除订单明细

2 delimiter $$

3 CREATE PROCEDURE pro_del_orders ( in _orderId int )

4 BEGIN

5 declare state VARCHAR ( 10 );

6 declare continue handler for SQLEXCEPTION set state = 'error' ;

7 START TRANSACTION ; 8 DELETE FROM t_order WHERE ORDER_ID = _orderId ;

9 IF ( state = 'error' ) THEN

10 ROLLBACK ;

11 ELSE

12 DELETE FROM t_orderdetail WHERE ORDER_ID = _orderId ;

13 IF ( state = 'error' ) THEN

14 ROLLBACK ;

15 ELSE

16 COMMIT ;

17 END IF ;

18 END IF ;

19 END

20 $$