1. 视图
1.1 视图的基本操作
视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上
创建语法:
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
#创建视图 CREATE OR REPLACE VIEW emp_v_1 AS SELECT * FROM emp WHERE sal <= 2000;
查询:
#查看创建视图语句： SHOW CREATE VIEW 视图名称; #查看视图数据： SELECT * FROM 视图名称 ...... ;
-- 查询视图 SHOW CREATE VIEW emp_v_1; SELECT * FROM emp_v_1; SELECT * FROM emp_v_1 WHERE sal < 1000;
修改:
--方式一： CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] --方式二： ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
-- 修改视图 CREATE OR REPLACE VIEW emp_v_1 AS SELECT empno,sal,ename FROM emp WHERE sal <= 2000; ALTER VIEW emp_v_1 AS SELECT * FROM emp WHERE sal <= 2000;
删除:
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
drop view if exists emp_v_1;
上述我们演示了，视图应该如何创建、查询、修改、删除，那么我们能不能通过视图来插入、更新数据呢？ 接下来，做一个测试
CREATE OR REPLACE VIEW emp_v_2 AS SELECT empno,ename,sal FROM emp_back WHERE sal<=2000; SELECT * FROM emp_v_2; INSERT INTO emp_v_2 VALUES(8080,'TOM',1888); INSERT INTO emp_v_2 VALUES(8081,'MARY',2500);
执行上述的SQL，我们会发现，empno为8080和8081的数据都是可以成功插入的。 但是我们执行查询，查询出来的数据，却没有id为8081的记录。
因为我们在创建视图的时候，指定的条件为 sal<=2000, id为8081的数据，是不符合条件的，所以没有查询出来，但是这条数据确实是已经成功的插入到了基表中。
如果我们定义视图时，如果指定了条件，然后我们在插入、修改、删除数据时，是否可以做到必须满足条件才能操作，否则不能够操作呢？ 答案是可以的，这就需要借助于视图的检查选项了。
1.2 检查选项
当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL，默认值为 CASCADED 。
-
CASCADED 级联
比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。
CREATE OR REPLACE VIEW emp_v_1 AS SELECT empno,ename,sal FROM emp_back WHERE sal<=2000; CREATE OR REPLACE VIEW emp_v_2 AS SELECT empno,ename,sal FROM emp_v_1 WHERE sal>=1000 WITH CASCADED CHECK OPTION; INSERT INTO emp_v_2 VALUES(8080,'TOM',1888); INSERT INTO emp_v_2 VALUES(8081,'MARY',2500);
执行上述的SQL，我们会发现，empno为8080的数据都是可以成功插入的。 而8081是无法插入的,这是因为, 2500 满足了emp_v_2的检查条件,但是emp_v_2视图是基于emp_v_1视图创建的,而是使用的是CASCADED,尽管emp_v_1没有设置with check option,但是emp_v_2使用with cascaded check option,所以在执行插入,不仅要检查插件的数据满足emp_v_2的条件,而且还有满足emp_v_1的条件,而8081满足emp_v_2条件,但是不满足emp_v_1,所以也插入失败,
-
LOCAL 本地。
比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，知会检查v2，不会检查v2的关联视图v1。
CREATE OR REPLACE VIEW emp_v_1 AS SELECT empno,ename,sal FROM emp_back WHERE sal<=2000; CREATE OR REPLACE VIEW emp_v_2 AS SELECT empno,ename,sal FROM emp_v_1 WHERE sal>=1000 WITH LOCAL CHECK OPTION; INSERT INTO emp_v_2 VALUES(8080,'TOM',1888); INSERT INTO emp_v_2 VALUES(8081,'MARY',2500);
执行上述的SQL，我们会发现，empno为8080和8081的数据都是可以成功插入的。
1.3 视图的更新
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
B. DISTINCT
C. GROUP BY
D. HAVING
E. UNION 或者 UNION ALL
create or replace view emp_v_1 as select job,count(1) from emp_back ;
INSERT INTO emp_v_1(job,sal) VALUES('市场',2000);
1.4 视图的作用
-
简单
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
-
安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
-
数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。
案例
1). 为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽
手机号和邮箱两个字段
create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user; select * from tb_user_view;
2). 查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图
create view tb_stu_course_view as select s.name student_name , s.no student_no , c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id; select * from tb_stu_course_view;
2. 存储过程
2.1 存储过程简介
存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
优点:
-
封装，复用 -----------------------> 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
-
可以接收参数，也可以返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回值。
-
减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。
2.2 基础语法
创建
CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ]) BEGIN -- SQL语句 END ;调用:
CALL 名称 ([ 参数 ]);查看:
-- 查询指定数据库的存储过程及状态信息 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询某个存储过程的定义 SHOW CREATE PROCEDURE 存储过程名称 ;删除:
DROP PROCEDURE [ IF EXISTS ] 存储过程名称;注意:
在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。sqlyog 需要运行 执行所有查询选项
演示示例:
存储过程基本语法 :
-- 创建 DELIMITER $$ CREATE PROCEDURE p1() BEGIN SELECT COUNT(*) FROM emp; END$$ DELIMITER ;--调用 call p1();-- 查看 select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'test'; show create procedure p1;-- 删除 drop procedure if exists p1;
2.3 变量
在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。
2.3.1 系统变量
系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。
1.查看系统变量
#查看所有系统变量 SHOW [ SESSION | GLOBAL ] VARIABLES ; #可以通过LIKE模糊匹配方 式查找变量 SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; #查看指定变量的值 SELECT @@[SESSION | GLOBAL] 系统变量名;2.设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ; SET @@[SESSION | GLOBAL] 系统变量名 = 值 ;注意:
如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 my.ini 中配置。
A. 全局变量(GLOBAL): 全局变量针对于所有的会话。
B. 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。
-- 查看系统变量 show session variables ; show session variables like 'auto%'; show global variables like 'auto%'; select @@global.autocommit; select @@session.autocommit; -- 设置系统变量 set session autocommit = 1; insert into course(id, name) VALUES (6, 'ES'); set global autocommit = 0; select @@global.autocommit;
2.3.2 用户定义变量
用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名" 使用就可以。其作用域为当前连接。
-
赋值
--方式一 SET @var_name = expr [, @var_name = expr] ... ; SET @var_name := expr [, @var_name := expr] ... ;
赋值时，可以使用 = ，也可以使用 := 。
--方式二 SELECT @var_name := expr [, @var_name := expr] ... ; SELECT 字段名 INTO @var_name FROM 表名;
-
使用
SELECT @var_name ;
注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
-
使用
-- 赋值 set @myname = 'zhangsan'; set @myage := 18; set @mygender := '男',@myhobby := 'java'; select @mycolor := 'red'; select count(*) into @mycount from tb_user; -- 使用 select @myname,@myage,@mygender,@myhobby; select @mycolor , @mycount; select @abc;
2.3.3 局部变量
局部变量 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。
-
声明
DECLARE 变量名 变量类型 [DEFAULT ... ] ;
变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。
-
赋值
SET 变量名 = 值 ; SET 变量名 := 值 ; SELECT 字段名 INTO 变量名 FROM 表名 ... ;
-
演示示例:
-- 声明局部变量 - declare -- 赋值 create procedure p2() begin declare stu_count int default 0; select count(*) into stu_count from student; select stu_count; end; call p2();
3.索引
3.1.1 介绍
索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足 特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
一提到数据结构，大家都会有所担心，担心自己不能理解，跟不上节奏。不过在这里大家完全不用担 心，我们后面在讲解时，会详细介绍。
3.1.2 演示
表结构及其数据如下：
假如我们要执行的SQL语句为 ： select * from user where age = 45;
1). 无索引情况
在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。
2). 有索引情况
如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对age这个字段建 立一个二叉树的索引结构。
此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。
备注： 这里我们只是假设索引的结构是二叉树，介绍一下索引的大概原理，只是一个示意图，并不是索引的真实结构，索引的真实结构，后面会详细介绍。
3.1.3 特点
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的。 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。 | 索引大大提高了查询效率，同时却也降低更新表的速度， 如对表进行INSERT、UPDATE、DELETE时，效率降低。 |
4.索引分类
在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建, 只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
聚集索引&二级索引
而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引(Clustered Index) | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
| 二级索引(Secondary Index) | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索 引。
聚集索引和二级索引的具体结构如下：
聚集索引的叶子节点下挂的是这一行的数据 。
二级索引的叶子节点下挂的是该字段值对应的主键值。
接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的
具体过程如下:
①. 由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
③. 最终拿到这一行的数据，直接返回即可。
回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。
扫一扫
354
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
