常见的数据库对象
- 数据库(已介绍)
- 数据表(已介绍)
- 索引
- 视图
- 存储过程
一、索引
- 数据库中使用索引,目的在于提高查询效率,本质上就是针对设置了索引的属性列按照特定顺序排序,便于查询
- 以一本书为例,索引就像书的目录一样,要找到第8章:
- 如果没有目录,需要从头开始,一页一页翻到第8章
- 如果有了目录,就可以按照目录的标识,翻到对应的页码
- 创建索引的语法:
CREATE INDEX 索引名称 ON 表名(属性名称)
- 例子:
CREATE INDEX index_a ON test_a(name);
- 例子:
- 也可以在使用CREATE语句创建数据表时,直接创建索引
- 例子:
CREATE TABLE test( id INT, name CHAR(10), INDEX index_test(id) );
- 或者在使用ALTER语句更新数据表时,新增索引
- 例子:
ALTER TABLE test ADD INDEX index_test(id);
- 例子:
注意点
- 需要注意的是数据表在定义时,如果指定了主键,会自动在主键上创建索引
- 同一个数据表上可以定义多个索引,但实际上在查询时,也只能使用一个索引:
- 如果数据表中有主键,则按照主键上的索引查询
- 如果没有主键,会选择限制最为严格的索引
- 要查看某个数据表上创建的索引,可以使用SHOW命令
- 语法:
SHOW INDEX FROM 表名
- 例子:
SHOW INDEX FROM test;
- 语法:
- 要删除索引,可以使用DROP命令
- 语法:
DROP INDEX 索引名称 ON 表名
- 例子:
DROP INDEX index_test ON test;
- 语法:
索引的优缺点
- 索引的优势是提高查询效率
- 但同样也有缺点:
- 索引的使用会降低更新表的速度
- 如果要对数据表进行INSERT、UPDATE、DELETE等更新操作,数据库不仅仅要保存
- 更新之后的数据,也需要更新对应的索引,因此会导致更新效率降低
- 索引的使用会创建对应的索引文件,占用磁盘空间
- 一般这个问题不太严重,但如果在一个大表上创建了多种组合索引,索引文件的膨胀速度会很快,占用过多的磁盘空间
- 索引的使用会降低更新表的速度
二、视图
- 数据库中的视图是一个虚拟的表,在数据库中并不实际存在
- 视图是由查询数据库中的数据表产生的,因此视图的内容由查询定义,实际上也可以把视
图理解为保存查询结果的虚拟表 - 虽然视图是一个虚拟的表,但是它和像真实的数据表一样使用
- 使用视图的原因主要有:
- 安全性更强,通过视图的使用可以将一些数据隐藏,仅将视图中的数据提供给用户使用
- 提高查询性能,将经常访问的数据提前保存在视图中,后续的数据查询可以直接在视图上进行
- 使复杂的查询易于理解和使用,将问题进行分解,利用多个视图获取数据并最终联合起来就能得到需要的结果
创建视图
- 可以使用CREATE语句来创建视图
- 语法:
CREATE VIEW 视图名称 AS SELECT语句
- 例子:
CREATE VIEW view_test AS SELECT * FROM table1 WHERE id=1;
- 语法:
- 创建视图时,需要遵循的规则:
- 视图也是一个表(虚拟表),因此不能与已有的表或视图名称重复
- 如果要查看数据库上的视图,可以使用命令:
SHOW TABLE STATUS WHERE COMMENT='VIEW';
更新视图
- 可以使用ALTER或者CREATE OR REPLACE语句更新视图
- 语法:
ALTER VIEW 视图名称 AS SELECT语句
- 语法:
CREATE OR REPLACE VIEW 视图名称 AS SELECT语句
- 例子:
ALTER VIEW view_test AS SELECT * FROM table1 WHERE id=2;
CREATE OR REPLACE VIEW view_test AS SELECT * FROM table1 WHERE id=2;
- 语法:
- 可以使用DROP语句删除视图
- 语法:
DROP VIEW 视图名称
- 例子:
DROP VIEW view_test;
- 语法:
通过视图更新真实表
- 在实际应用中,视图的另外一个主要用途:通过视图来更新真实表
- 因为视图本身是虚拟表,实际上是对原表的引用,因此在视图中做的数据更新操作会直接反馈到原表中
- 例子:
UPDATE view_test SET name=‘zhangsan';
- 将视图
view_test
中的name
更新为’zhangsan’
后,原表中对应的数据也会相应的更新 - 同样因为这一特性,在原表中做的数据更新,也会反馈到视图中
- 需要注意的是,通过视图来更新真实表这一特性是有限制的:
- 如果视图中的属性来自于两个以上的数据表,则此视图不允许更新
- 如果视图中的属性来自于函数结果,则此视图不允许更新
- 如果视图定义中含有
GROUP BY
子句,则此视图不允许更新 - 如果视图定义中含有
DISTINCT
语句,则此视图不允许更新 - 一个不允许更新的视图上定义的视图也不允许更新
三、存储过程
- 存储过程是一组为了完成特定功能的SQL语句集,经过编译后存储在数据库中,用户可以
通过存储过程的名称来调用并执行它 - 存储过程的优点:
- 增强了SQL语言的功能和灵活性,可以用控制流语句编写,完成负责的判断和运算
- 实现较快的执行速度,因为存储过程是经过预编译后存储在数据库中的,调用并执行存储过程不需要再次编译
- 减少网络传输量,因为在调用执行存储过程时,只需要将存储过程的名称和参数进行传送
- 增强了数据的安全性,用户需要知道的仅仅是存储过程的名称和参数,避免了对数据的直接操作
- 需要注意的是MySQL在5.0以前并不支持存储过程
创建存储过程
-
使用CREATE语句创建存储过程
- 语法:
CREATE PROCEDURE 存储过程名称(参数列表) 程序体
存储过程的参数列表中每一个参数由3部分构成:输入输出类型、参数名称、参数类型
-
输入输出类型有3种:IN、OUT、INOUT
-
IN
表示输入参数 -
OUT
表示输出参数 -
INOUT
表示既可以是输入,也可以是输出
-
存储过程的程序体以
BEGIN
开始,END
结束,可以包含一组语句集例子:创建一个存储过程,能够根据输入的部门编号,统计出部门员工人数
CREATE PROCEDURE pro(IN dno INT,OUT count_num INT)
BEGIN
SELECT COUNT(empno) INTO count_num FROM insane_emp WHERE deptno=dno;
END;
- 存储过程创建完成后,使用CALL来调用存储过程
- 语法:
CALL 存储过程名称(参数列表);
- 语法:
- 存储过程调用时,需要传递具体数据给输入参数,使用变量接收输出参数
- 例子:统计编号为1的部门员工人数
CALL pro(1,@cn); SELECT @cn;
查看存储过程
- 查看数据库中所有存储过程基本信息
- 语法:
SHOW PROCEDURE STATUS;
- 语法:
- 查看某个具体存储过程
- 语法:
SHOW CREATE PROCEDURE 存储过程名称
- 语法:
- 存储过程的程序体也不能被修改,当存储过程不再被使用时,使用
DROP
语句删除- 语法:
DROP PROCEDURE 存储过程名称
- 语法:
四、事务处理
- 事务是一种机制,是一种操作序列,包含了一组数据库操作命令,这组命令要么全部执行,要么全部不执行,事务是一个不可分割的工作逻辑单元
- 事务机制主要用于维护数据库的完整性、一致性
- 典型事务应用场景:
- 在银行转账交易过程中,要从A账户向B账户转1000元
- 在对应的数据库中,要依次使用
UPDATE
语句分别更新A账户、B账户的对应信息 - 成功更新A账户信息后,在更新B账户信息时,可能由于某种原因导致B账户信息更新失败
- 此时,就造成了数据库中数据出现不一致问题
- 如果增加了事务机制,就可以让两次更新操作要么全部执行,要么全部不执行,避免了数据不一致问题的产生
- 需要注意的是MySQL只有InnoDB类型的数据库才支持事务处理
事务处理实例
- 将多条SQL语句放到一个事务中,需要使用下面的命令:
START TRANSACTION
COMMIT
SAVEPOINT
ROLLBACK
- 首先开启事务,使用命令:
START TRANSACTION
- 执行SQL语句,执行过程中建立保存节点,使用命令:
SAVEPOINT 节点名称
- 判断SQL语句是否执行成功
- 如果全部成功则提交事务,使用命令:
COMMIT
- 如果出现异常则不提交事务,执行回滚操作,使用命令:
ROLLBACK
或ROLLBACK TO 保 存节
- 如果全部成功则提交事务,使用命令: