MySql高级复习
视图
1. 视图是简化查询过程，提高数据库安全性的虚拟表。
2. 视图中保存的仅仅是一条select语句，保存的是视图的定义，并没有保存真正的数据。视图中的源数据都来自于数据库表，数据库表称为基本表或者基表，视图称为虚拟表。
作用：
1. 防止未经许可的用户访问敏感数据，确保数据的安全性
2. 封装sql语句，简化查询过程
3. 视图可对用户屏蔽真实表结构
语法：
格式：
CREATEA VIEW 视图名字 AS SELECT 语句;
注意：
查看当前用户是否有创建视图的权限
SELECT Select_priv,Create_view_priv FROM MySQL.user WHERE user='用户名';
参数说明：
(1)Select_priv：属性表示用户是否具有SELECT权限，Y表示拥有SELECT权限，N表示没有。
(2)Create_view_priv：属性表示用户是否具有CREATE VIEW权限，Y表示拥有CREATE 权限，N表示没有；
(3)MySQL.user：表示MySQL数据库下面的user表。
(4)用户名：参数表示要查询是否拥有权限的用户，该参数需要用单引号引起来。
注意2：
1、修改视图也会影响到真实表、真实表修改也会影响到视图。
2、视图主要为了简化查询、所以不要进行增删改
案例
建表语句
CREATE TABLE user1(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50)NOT NULL,
PASSWORD VARCHAR(50)NOT NULL,
NAME VARCHAR(100)DEFAULT NULL,
home VARCHAR(100) DEFAULT NULL,
age INT(11) DEFAULT NULL,
createTime DATETIME DEFAULT NULL
);
INSERT INTO user1(id,username,PASSWORD,NAME,home,age,createTime) VALUES
(1,'admiin','123456','张三','上海市',20,'2020-12-31 11:53:36'),
(2,'abc','123456','冯小刚','北京市',50,'2020-12-31 10:21:00'),
(3,'liMing','123456','黎明','香港',50,'2021-01-03 15:27:46'),
(4,'huGe','123456','胡歌','上海',35,'2021-01-03 16:21:36'),
(5,'haTest','111111','哈哈测试','上海',35,'2021-01-03 16:22:47'),
(7,'hb','111111','黄渤','山东',30,'2021-01-04 09:39:11');
练习
#把user1表中年龄大于20的数据存入名称为user1_view视图中
CREATE VIEW user1_view AS SELECT * FROM user1 WHERE age > 20;
#查询视图
SELECT * FROM user1_view;
#查看视图结构
DESC user1_view;
#查看创建视图的文本信息
SHOW CREATE VIEW user1_view;
#修改视图 语法： ALTER VIEW 视图名称 AS SQL语句 ;
ALTER VIEW user1_view AS SELECT * FROM user1 WHERE age > 20 AND NAME = '黎明';
#删除视图
DROP VIEW user1_view;
存储过程
（一）概念
1. 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。类似于java中的方法。
2. 存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
3. 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
（二）优点
1. 存储过程在创建的时候直接编译，而sql语句每次使用都要编译，提高执行效率
2. 一个存储过程可以被重复使用。
3. 一条sql语句，可能需要访问几张表，对数据库连接好几次，存储过程只会连接一次
4. 存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。
（三）缺点
1. 可移植性太差了
2. 对于简单的sql语句，毫无意义
3. 对于只有一类用户的系统安全性毫无意义
4. 团队开发，标准不定好的话，后期维护很麻烦
5. 对于开发和调试都很不方便
6. 复杂的业务逻辑，用存储过程还是很吃力的
（四）创建存储过程语法
语法：
CREATE PROCEDURE sp_name (参数类型 参数名 数据类型 ，...)
BEGIN
SQL语句集合；
END
说明：
sp_name ：存储过程名字
BEGIN ... END: 存储过程开始和结束符号
参数类型:参数类型可以是以下三种[ IN | OUT | INOUT ]:
IN 输入参数：表示调用者向过程传入值
OUT 输出参数：表示过程向调用者传出值
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值
调用存储过程
语法：call 存储过程名; 例如:call users_pro();
案例
1. 创建无参数存储过程
#DELIMITER$$ 定义分解符$$
DELIMITER$$
CREATE PROCEDURE s1()
BEGIN
SELECT * FROM user1;
END$$
CALL s1();
2. IN(输入)参数存储过程
DELIMITER$$
CREATE PROCEDURE s2(IN us INT)
BEGIN
SELECT * FROM user1 WHERE id = uid;
END$$
CALL s2(2);
3. out（输出）参数存储过程说明
DELIMITER$$
CREATE PROCEDURE s3(OUT user2 INT)
BEGIN
SELECT id INTO user2 FROM user1 WHERE username='abc';
END$$
CALL s3(@user2)
SELECT @user2;
4. inout参数存储过程说明
DELIMITER--
CREATE PROCEDURE s4(INOUT temp INT)
BEGIN
SELECT age INTO temp FROM user1 WHERE id = temp;
END--
-- 定义变量
SET @temp = 1;
#调用存储函数 @t:会自动接收函数的返回值
CALL s4(@temp);
#查询函数的返回值
SELECT @temp;
5.不用inout来写一个既有入参又有返回值的函数
DELIMITER$$
CREATE PROCEDURE s5(IN uid INT,OUT uname VARCHAR(20))
BEGIN
SELECT username INTO uname FROM user1 WHERE id = uid;
END$$
CALL s5(1,@uname);
SELECT @uname;
6. 删除存储过程
语法： DROP PROCEDURE 存储过程名;
7.带有循环的存储过程
案例:计算1+2+3+4+5...+100的和
参数说明：
1. declare：声明变量
2. set: 给某个变量赋值
3. while .. do ... end while
-- 案例:计算1+2+3+4+5...+100的和
# declare:声明变量 while 判断条件 do 循环体 end while;
DELIMITER$$
CREATE PROCEDURE getSum(IN n INT,OUT sum1 INT)
BEGIN
#声明变量i初始值为1
DECLARE i INT DEFAULT 1;
#声明累加和变量temp 初始值 0;
DECLARE temp INT DEFAULT 0;
WHILE i<=n DO
#循环体
SET temp =temp +i;
SET i= i+1;
END WHILE;#循环结束
#将temp的值给sum1
SET sum1 =temp;
END$$
CALL getSum(100,@sum1)
SELECT @sum1
触发器
（一）概念
1. 触发器的这种特性可以协助应用在数据库端确保数据的完整性。也可以把触发器理解成一个特殊的存储过程，不需要显示调用，是自动被调用的存储过程。
2. 类似于servlet中的监听器
3. 监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（insert，delete， update）时就会激活它执行。
（二）语法
DELIMITER $$
CREATE TRIGGER 触发器名 触发时机（BEFORE|AFTER） 触发事件（INSERT|UPDATE|DELETE）
ON 表名 FOR EACH ROW
BEGIN
执行语句列表;
END $$
DELIMITER
说明：
- BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后。
- FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。
- 触发事件参数详解：
1. INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、REPLACE 语句触发；
2. UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；
3. DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。
（三）六种触发器的介绍
BEFORE INSERT：在添加之前激活触发器
AFTER INSERT：在添加之后激活触发器
BEFORE UPDATE：在修改之前激活触发器
AFTER UPDATE：在修改之后激活触发器
AFTER DELETE：在删除之后激活触发器
BEFORE DELETE：在删除之前激活触发器
（四） 查看触发器
语法： SHOW TRIGGERS；
show triggers;
（五）删除触发器
语法 ： DROP TRIGGER 触发器名
drop trigger trig2;
（六）NEW与OLD介绍
MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：
① 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
② 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为 的新数据；
③ 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；
使用方法：
NEW.columnName （columnName为相应数据表某一列名）
案例
准备表字段
CREATE TABLE `times` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`createTime` datetime DEFAULT NULL,
`state` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
)
创建触发器
案例一:创建一个名为trig1的触发器,一旦在user1表中有插入数据动作,那么就往times表中添加一条记录
#创建一个名为trig1的触发器,一旦在user1表中有插入数据动作,那么就往times表中添加一条记录
DELIMITER$$
CREATE TRIGGER trig1 AFTER INSERT ON user1 FOR EACH ROW
BEGIN
INSERT INTO times VALUES(NULL,NOW(),"你往user1中插了数据");
END$$
注: 删除、修改类型的触发器同理，只需要修改触发器后的关键字即可
案例二:创建一个名为trig2的触发器,一旦在user1表中有删除操作,那么就往times表中添加两条记录
#创建一个名为trig2的触发器,一旦在user1表中有删除操作,那么就往times表中添加两条记录
DELIMITER$$
CREATE TRIGGER trig2 AFTER DELETE ON user1 FOR EACH ROW
BEGIN
INSERT INTO times VALUES(NULL,NOW(),'删除了一条数据');
INSERT INTO times VALUES(NULL,NOW(),'删除了两条数据');
END$$
DELETE FROM user1 WHERE id =8;
案例三：在user1表上绑定一个触发器,当user1表中发生了更新后、往times表中添加一条信息
(要求:state中信息:修改了黄渤这条数据)
#在user1表上绑定一个触发器,当user1表中发生了更新后、往times表中添加一条信息
DELIMITER$$
CREATE TRIGGER trigg AFTER UPDATE ON user1 FOR EACH ROW
BEGIN
INSERT INTO times VALUES(NULL,NOW(),CONCAT(old.name,'这条数据修改了'));
END$$
UPDATE user1 SET username = 'huangbo' WHERE id = 7;
存储引擎
1.MyISAM存储引擎
特点：
1. 在做插入、查询时速度快，性能高
2. 支持全文索引(普通索引对检索条件只能进行精确匹配，而大字段中的文本内容很多，通常也不会在这种字段上执行精确的文本匹配查询，而更多的是基于关键字的全文检索查询，例如你查一篇文章信息，你会只输入一些关键字，而不是把整篇文章输入查询（如果有整篇文章也就不用查询了）。而全文索引正是适合这种查询需求。)，表级锁
3. 不支持事务，外键
文件存储格式：
1. tb01.frm：存储tb01表结构信息（表中有哪些列，数据类型等）
2. tb01.MYI：MY表示MYISAM存储引擎，I是index索引，这里存储tb01的索引信息。
3. tb01.MYD：MY表示MYISAM存储引擎，D是data数据，这里存储tb01的数据信息（即表中的记录）
- InnoDB存储引擎
特点：
1. 支持外键、支持事务
2. 支持行级锁，因此在高并发量的情况下效率高
3. 支持全文索引 支持(5.6版本之后)
文件存储格式：
1. xx.frm:同MYISAM存储引擎，也是用来存储表结构信息。
2. ibdata1:共享表空间，且来存储所有InnoDB数据表的数据信息，包含索引信息
- 指定存储引擎建表
在建表时指定
create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM;
create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;
#也可以使用alter table语句，修改一个已经存在的表的存储引擎。
alter table ai engine = innodb;
在配置文件中指定
#my.ini文件
[mysqld]
default-storage-engine=INNODB
索引高效查询
（一）概念
1. 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度
（二）索引的分类
1. 普通索引 ：仅加速查询
2. 唯一索引 ：加速查询 + 列值唯一（可以有null）
3. 组合索引 ：多列值组成一个索引，
4. 主键索引：加速查询 + 列值唯一 + 表中只有一个（不可以有null）
-
普通索引的创建
创建一张表使用存储过程添加1万条数据
CREATE TABLE offcn(
id INT,
username VARCHAR(50),
email VARCHAR(50)
);
存储过程
DELIMITER$$
CREATE PROCEDURE p1(IN n INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<= n DO
INSERT INTO offcn VALUES(i,CONCAT('offcn',i),CONCAT(i,'@qq.com'));
SET i = i +1;
END WHILE;
END$$
CALL p1(5000000);
总耗时 : 1 hr 6 min
在已经存在的表上创建索引：
语法：
1.CREATE INDEX 索引名称 ON 表名（字段名）
2.ALTER TABLE 表名 ADD INDEX 索引名称(字段名称);
CREATE INDEX index_username ON offcn(username);
查看索引:
show index from 表名;
SHOW INDEX FROM offcn;
删除索引
drop index 索引名 on 表名;
浙公网安备 33010602011771号