第九章 数据库其他对象

数据库其他对象

视图

视图是一张虚拟表
表示一张表的部分数据或多张表的综合数据
其结构和数据是建立在对表的查询基础上
视图中不存放数据
数据存放在视图所引用的原始表中
一个原始表，根据不同用户的不同需求，可以创建不同的视图

视图的用途

筛选表中的行
防止未经许可的用户访问敏感数据
降低数据库的复杂程度
为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。
简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。

使用管理器创建视图

使用sql语句创建视图

语法

CREATE VIEW `数据库名`.`视图名称` AS select ...... ;

CREATE VIEW `stu`.`v_stu_sub_res` AS 
SELECT * FROM `subject` s, result r where s.SubjectId=r.subjectNo;

查看视图

-- 语法
SELECT * FROM view_name

SELECT*FROM v_stu;

删除视图

-- 语法
DROP VIEW view_name

DROP VIEW v_sub_res;

注意事项

视图中可以使用多个表
一个视图可以嵌套另一个视图
在视图定义中命名的表必须已存在
select语句不能引用系统或用户变量
视图的命名必须遵循标志符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。

更新视图

ALTER VIEW v_stu as select  studentNo,studentname from `student`;

索引

概念

MySQL 中的数据也是按页存放
索引：是MySQL编排数据的内部方法。它为MySQL提供一种方法来编排查询数据
索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页
索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能

索引结构:B+树

索引类型

普通索引 index:加速查找
唯一索引:

unique:唯一 加速查找+约束(唯一)

主键索引

primary key:主键 加速查找+约束(唯一,非空)

组合索引

primary key(id,name)联合主键索引

unique(id,name)联合唯一索引

index(id,name)联合的普通索引

全文索引

fulltext:用于搜索很长的文章的时候,效果最佳

空间索引:

spatial:基本不用,不需要了解

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name 
[index_type] ON tbl_name (index_col_name,...) 
[index_type] 
index_col_name: col_name [(length)] [ASC | DESC] 
index_type: USING {BTREE | HASH | RTREE}

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引
2.index_col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
3.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
4.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
5.asc或desc指定升序或降序的索引值存储

# 创建一个普通索引
CREATE INDEX  index_student_address on student(address);
#创建唯一索引
CREATE UNIQUE INDEX index_student_phone on student(phone);
#修改表的形式创建索引
ALTER TABLE student  
  ADD  UNIQUE INDEX index_Student_studentName (studentName);
#删除索引
#drop index 索引名 on 表名 ;
  DROP INDEX index_student_studentName ON student ;

索引的优缺点

优点
加快访问速度
加强行的唯一性
缺点
带索引的表在数据库中需要更多的存储空间
操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新

创建索引的指导原则

按照下列标准选择建立索引的列
频繁搜索的列
经常用作查询选择的列
经常排序、分组的列
经常用作连接的列（主键/外键）
请不要使用下面的列创建索引
仅包含几个不同值的列
表中仅包含几行

使用索引时注意事项

查询时减少使用*返回全部列，不要返回不需要的列
索引应该尽量小，在字节数小的列上建立索引
WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前(最左匹配)
避免在ORDER BY子句中使用表达式
根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理

最左匹配原则:

条件按照索引的优先级,从左到右排列,无索引的数据放在最后

# 根据id,主键索引查询的速度是最快的
# 主键hash索引  优先使用
SELECT id,name,phone FROM us where id=221084;
# 没有索引,查询速度明显增多
SELECT id,name,phone FROM us where name='zs123';
#最左匹配:必须按照从左到右的顺序
# 优先把有索引的列放到左边
SELECT id,name,phone FROM us where name like'zs123' and phone like'asd123456';-- name没有索引,所以先查询name,索引会失效
SELECT id,name,phone FROM us where  phone like 'asd123456' and name like 'zs123';

索引不会包含有NULL值的列
使用短索引
索引列排序
like语句操作
不要在列上进行运算
不使用NOT IN和<>操作

使用变量

局部变量

mysql局部变量，只能用在begin/end语句块中，
比如存储过程中的begin/end语句块。
其作用域仅限于该语句块。
– declare语句专门用于定义局部变量，可以使用default来说明默认值
declare name varchar(20) default 0;
– 局部变量的赋值方式一
set names=‘’;
– 局部变量的赋值方式二
select StudentName into names from student where StudentNo=1;

#创建存储过程
CREATE DEFINER = CURRENT_USER PROCEDURE `aa`()
#局部变量
BEGIN
#声明变量  DECLARE只能用在存储过程
DECLARE a int;
#赋值方式1
set a=12;
#赋值方式2,讲查询结果赋值给变量
SELECT id INTO a FROM us where name='zs1234';
END;

用户变量

用户变量,类似于java的成员变量,java的成员变量使用对象访问,uer.getName();

mysql用户变量，mysql中用户变量不用提前申明，在用的时候直接用“@变量名”使用就可以了。

其作用域为当前连接。
– 第一种用法，使用set时可以用“=”或“:=”两种赋值符号赋值
set @id=6;
set @id:=20;
– 第二种用法，使用select时必须用“:=”赋值符号赋值
select @id:=22;
select @id:=StudentNo from student where StudentName=‘张三’;

#创建存储过程
CREATE DEFINER = CURRENT_USER PROCEDURE `a2`()
#用户变量
BEGIN
#赋值方式1
set @var1 = 2;
set @var2 := 12;
#赋值方式2,讲查询结果赋值给变量
SELECT id INTO @var3 FROM us where name='zs1234';
END;
call a2();
SELECT @var3;

会话变量

mysql会话变量，服务器为每个连接的客户端维护一系列会话变量。
其作用域仅限于当前连接，即每个连接中的会话变量是独立的。

#查看所有会话(session)变量
SHOW SESSION VARIABLES;
#查询回复变量
SHOW VARIABLES like 'host%';
SELECT @@session.auto_increment_increment;
SELECT @@local.auto_increment_increment;
SELECT @@auto_increment_increment;
#设置会话变量
set auto_increment_increment=1;
set SESSION auto_increment_increment=1;
set @@session.auto_increment_increment =1;
set @@local.auto_increment_increment =1;
set @@auto_increment_increment=1;

全局变量

mysql全局变量，全局变量影响服务器整体操作，当服务启动时，它将所有全局变量初始化为默认值。要想更改全局变量，必须具有super权限。

其作用域为server的整个生命周期。

#显示所有全局变量
SHOW GLOBAL VARIABLES;
#查看的两种方式
show VARIABLES like 'sql_war%';
SELECT @@global.sql_warnings;
#设置
set sql_warnings=1;
set GLOBAL sql_warnings=1;
set @@global.sql_warnings=1;

存储过程

简介

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

存储过程的优点

执行速度更快
允许模块化程序设计
提高系统安全性
减少网络流通量

存储过程的缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。

创建存储过程

CREATE DEFINER = CURRENT_USER PROCEDURE `aaa`()
BEGIN
	#Routine body goes here...

END;

CREATE DEFINER=`root`@`localhost` PROCEDURE `aaa`()
BEGIN
	#Routine body goes here...
	#查询所有学生的手机号
	SELECT*FROM phonelist;

END;
#调用存储过程
call aaa();

存储过程的参数分三种：
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

形式如下:
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形…])

带参数的存储过程

#java初级考试未通过考试的学员名单
#由于每次考试的难易程度不一样，每次 成绩的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。
CREATE DEFINER=`root`@`localhost` PROCEDURE `abc`(IN `score` double)
BEGIN
	SELECT s.studentname,r.studentResult FROM student s,result r where s.studentNo=r.studentNo
	and r.studentResult>=score;

END;
CALL	abc(50);

带输出参数的存储过程

#返回未通过考试的学员人数。
CREATE DEFINER=`root`@`localhost` PROCEDURE `abd`(OUT `num` int)
BEGIN
#使用into给变量num赋值
	SELECT COUNT(*) INTO num FROM student s,result r where s.studentNo=r.studentNo
	and r.studentResult>=60;

END;
#调用时必须带OUT关键字 ，返回结果将存放在变量@num   
CALL abd(@num);
#可以通过变量进行查询
SELECT @num;

带输入输出参数的存储过程

#根据分数线返回未通过考试的学员人数 直接使用输入参数：及格线
CREATE DEFINER=`root`@`localhost` PROCEDURE `abe`(INOUT `num` int)
BEGIN
#使用into给变量num赋值 
	SELECT COUNT(*) INTO num FROM student s,result r where s.studentNo=r.studentNo
	and r.studentResult>=num;

END;
#调用时必须先设置变量值 ，返回结果将存放在变量@num中
set @num=60;
CALL abe(@num);
SELECT @num;

触发器

触发器

是个特殊的存储过程
它的执行不是由程序调用，也不是手工启动，而是由事件来触发。 insert，delete， update）
触发器经常用于加强数据的完整性约束和业务规则等。

为什么要使用触发器：

可以使用它来检查或预防坏的数据进入数据库。
可以改变或取消INSERT、UPDATE、以及DELETE语句。
可以在一个会话中监视数据改变的动作。

语法：

DELIMITER $$
CREATE
    TRIGGER `myschool`.`student_phone` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `myschool`.`<Table Name>`
    FOR EACH ROW BEGIN
    #事件
    END$$
DELIMITER ;

触发器名称：触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象
触发程序的动作时间：BEFORE AFTER. 可以设置为事件发生前或后.
事件：指明了激活触发程序的语句的类型。可以是下述值之一：
INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。

DELIMITER $$
CREATE
    TRIGGER menus_price BEFORE INSERT
    ON menus
    FOR EACH ROW BEGIN
    UPDATE	orders set num=num-1 where id=1;
    END$$
DELIMITER ;

#判断插入的数据成绩是否正确
DELIMITER $$
CREATE
    TRIGGER result_score BEFORE INSERT
		#BEFORE 在。。之前 insert插入操作
    ON result
    FOR EACH ROW BEGIN
    if  #判断条件   
		#new.字段 代表的是新添加的数据
		new.studentresult>100 or new.studentresult<0
		then #符合条件之后的操作
		#错误提示
		set @msg='成绩错误';
		SIGNAL SQLSTATE 'HY000' set MESSAGE_TEXT=msg;
		end if;#必须结束if语句
    END$$  #mysql遇到;会结束运行DELIMITER ;就无法执行，使用$,$$代替
DELIMITER ;

修改

#修改    验证修改的成绩是否正确
DELIMITER $$
CREATE
    TRIGGER result_score_up BEFORE UPDATE
		#BEFORE 在。。之前 insert插入操作
    ON result
    FOR EACH ROW BEGIN
    if  #判断条件   
		#new.字段 代表的是新修改的数据
		new.studentresult>100 or new.studentresult<0
		then #符合条件之后的操作
		#错误提示
		set @msg='成绩错误';
		SIGNAL SQLSTATE 'HY000' set MESSAGE_TEXT=@msg;
		end if;#必须结束if语句
    END$$  #mysql遇到;会结束运行DELIMITER ;就无法执行，使用$,$$代替
DELIMITER ;
#修改    考试两次机会，取最大值
DELIMITER $$
CREATE
    TRIGGER result_score_up2 BEFORE UPDATE
		#BEFORE 在。。之前 insert插入操作
    ON result
    FOR EACH ROW BEGIN
    if  #old代表修改之前的数(原来的数据)  new修改后的数据（新数据）
		old.studentresult>new.studentresult
		then 
		set new.studentresult=old.studentresult;
		end if;
    END$$  #mysql遇到;会结束运行DELIMITER ;就无法执行，使用$,$$代替
DELIMITER ;

删除

#删除  考试的时间在2022年后的不允许删除
DELIMITER $$
CREATE
    TRIGGER result_score_de BEFORE DELETE
		#BEFORE 在。。之前 insert插入操作
    ON result
    FOR EACH ROW BEGIN
    if  #old代表修改之前的数(原来的数据)
		old.resultid<10
		then 
		set @msg='前十条数据不允许删除';
		SIGNAL SQLSTATE 'HY000' set MESSAGE_TEXT=@msg;
		end if;
    END$$  #mysql遇到;会结束运行DELIMITER ;就无法执行，使用$,$$代替
DELIMITER ;

说明：
表：触发器是属于某一个表的:当在这个表上执行插入、更新或删除操作的时候就导致触发器的激活。我们不能给同一张表的同一个事件安排两个触发器，而且必须引用永久性表，不能将触发程序与TEMPORARY表或视图关联起来。
触发间隔：FOR EACH ROW通知触发器每隔一行执行一次动作，而不是对整个表执行一次。
关于旧的和新创建的列的标识
在触发器的SQL语句中，你可以关联表中的任意列。但你不能仅使用列的名称去标识，那会使系统混淆，因为那里可能会有列的新名（这可能正是你要修改的，你的动作可能正是要修改列名），还有列的旧名存在。因此你必须用这样的语法来标识： “NEW . column_name"或者"OLD . column_name”.这样在技术上处理（NEW | OLD . column_name）新和旧的列名属于创建了过渡变量（“transition variables”）。
对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及OLD同时使用。