目录
2.索引
2.1索引概述
MYSQL官方对索引的定义为:索引(index)是帮助mysql高效获取数据的数据结构(有序).在数据之外,数据库系统还维护者满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种结构就是索引.
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也不一定是物理相邻的),为了加快col2的查找,可以维护一个右边所示的二叉树查找,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找快速获取到相应数据.
一般来说索引本身也很大,不可能全部储存在内存中,因此索引往往以索引文件的形式存储在磁盘上,索引时数据库中用来提高性能饿最常用的工具.
2.2 索引优势劣势
优势
(1)类似于书籍的目录索引,提高数据检索的效率,降低数据库的io成本
(2)通过索引列对数据局进行排序,降低数据排序的成本,降低cpy的消耗
劣势
(1)实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的
(2)虽然索引大大提高了查询效率,同事却也降低了更新表的速度,如对表坚信insert update delete.因为更新表时,mysql不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所代开的键值的变化后的索引信息.
2.3 索引结构
索引时在mysql的存储引擎层中实现的,而不是在服务器层实现的.所以每种存储引擎的索引都不一定完全一样,也不是所有的存储引擎都支持所有的索引类型.mysql目前提供了以下4中索引:
- BTREE索引:最常见的索引类型,大部分索引都支持B树索引
- HASH索引:只有Memory引擎支持,使用场景简单
- R-tree索引(空间索引):空间索引是myisam引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少不做特别介绍
- Full-text(全文索引):全文索引也是myisam的一个特殊索引类型,主要用于全文索引,innodb从mysql5.6版本开始支持全文索引
我们平常所说的索引,如果没有特别指明,都是B+树(多路索引树,并不一定是二叉的)结构组织的索引.其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树索引,统称为索引.
2.3.1 BTREE结构
BTree又叫多路平衡腰索树,一颗m叉的BTree特性如下:
- 树中每个节点最多包含个孩子。
- 除根节点与叶子节点外,每个节点至少有ci(m/2)】个孩子。。若根节点不是叶子节点,则至少有两个孩子。
- 所有的叶子节点都在同一层。
- 每个非叶子节点由n个key与n+1个指针组成,其中[ceil(m/2)1]c=nc=m-1
以5叉BTree为例,key的数显:公式推导[cei(m/2)1]门<=n<=m-1。所以2<=n<=4。当n>4时,中间节点分裂到父节点,两边节点分裂.
2.3.2 B+TREE结构
B+Tree为BTree的变种,B+Tree与BTree的区别为:
1).n叉B+Tee量多含有n个key,而BTreei显多含有n-1个ky.
2).B+Tree的叶子节点保存所有的key信息,依key大小顶序排列。
3).所有的非叶子节点都可以看作是ky的索分
2.4索引分类
- 神值索引:即一个索引只包含单个列,一个表可以有多个单列索引
- 唯一索引:索引列的值必须唯一,但允许有空值
- 复合索引:即一个索引包含多个列
2.5.1创建索引语法:
CREATE[UNIQUE |FULLTEXT SPATIAL]INDEXindex_name [USING index_type] ON tbl_name(index_col_name....)
index_col_name column_name[(length)][ASC DESC]
示例:为city表中的city_name孛段创建索引: mysql>create index idx city name on city(city name); Query OK,0 rows affected (0.05 sec) Records:0 Duplicates:0 Warnings:0
2.5.4 ALTER命令 删除drop 查看 show
alter table tb_name add primary key(column_list);
2.6索引设计原则
索引的设计可以总佰一些已有的原则,创建索引的时候清尽量考虑符合这些原则,便于提升索引的使用率,更高效的使用索引。
- ·对查询频次较高,且数居显比较大的表理立索引。·索引字设的选择,最佳候达列应当从where-子句的条件中提取,如果where子句中的组合比较多,那么应当桃选最常用、过效果最好的列的组合。
- ·使用唯一索引,区分度越高,使用索引的效率越高。
- ·索引可以有的提升查问数据的效率,但索引数显不是多多益苦,索引越多,维护索引的代价自然也就水张船高。对于插入、更新、删除等DM礼操作比较频察的表来说,索引过多,会引入相当高的维护代价,降低DL操作的效率,增加相应操作的时间消耗。另外索引过多的话,MySQL也会犯选挥困准病,虽然最悠仍然会找到一个可用的索引,但无疑提高了选择的代价。
- ·使用短索引,索引创理之后也是使用硬盘来存储的,因此提升索引访问的/0效率,也可以提升总体的访问效率,假如构成索的字段总长度比按短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升M小ySQL访问索引的1/O效率。
- ·利用最左前缀,N个列组合而成的组合索引,那么相当于是创速了N个索引,如果查问时whr子句中使用了组成该索引的前几个字段,那么这条查询SQL可以利用组合索引来提升查询效率。
3.视图
3.1视图概述视图(川W)
是一种虚拟存在的表,视,图并不在数据库中实际存在,行和列数据来自定义视,☒的查询中使用的表,井且是在使用视图时动态生成的。通俗的讲,视图就是一条SELECT语句执行后返回咖的结果集。所以我们在创生视图的时候,主要的工作就落在创注这条5QL查问语句上。视图相对于普通的表的优,势主要包括以下几项。
·简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。
·安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限刮到某个行某个列,但是通过视图就可以简单的实现:
·数据独立:一旦视图的结构确定了,可以屏敬表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。
3.2创建或者修改视图
创建视图的语法为:
OR REPLACE][ALGORTTM={UNDEFINED I MERGE I TEMPTABLE)]
VIEW view_name [(column_list)]
AS select_statement 6[WITH [CASCADED LOCAL]CHECK OPTION]
修改视图的语法为:
ALTER [ALGORITHM [UNDEFINED MERGE TEMPTABLE}]
VIEw view_name [(column_list)]
As select_statement
[WITH [CASCADED LOCAL]CHECK OPTION]
选项: 2 TTH[CASCADED LOCAL]CHECK OPTION决定了是香心许更新数据使记不再满足视图的条件. LOCAL:只要满足本视图的条件就可以更新。 CASCADED:必须两足所有针对该视图的所有视图的亲件才可以更新。
-- 创建视图
CREATE view view_park_building as SELECT p.*,b.building_code from building_info b left join park_info p on b.park_id=p.ID
-- 查询视图
SELECT *from view_park_building where `Name` like '苹果小镇'
-- 更新视图
update view_test set `Name` ='苹果小镇1' where ID='1590944269576658946'
-- 查看视图
show TABLES
show CREATE view view_park_building
-- 删除视图
drop view if EXISTS view_test
4.存储过程和函数
4.1存储过程和函数概述
存储过程和函数是事先经过编译并存储在数据库中的一段sql语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,见啥好数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的
存储过程和函数的区别在于函数必须是有返回值的 而存储过程没有
函数:是一个有返回值的过程
过程:是一个没有返回值的函数
4.2创建存储过程
CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) beqin--S0L语句 end ;
知识小贴士
DELIMITER该关键字用来声明SQL语句的分隔符,告诉MysQL解释器,该设命令是否已经结束了,mysql是否可以执行了。默认情沉下,delimiter是分号:。在命令行客户端中,如果有一行命冷以分写结束,那么回车后,ySg将会执行该命令。
-- 创建存储过程
CREATE PROCEDURE proce_test()
BEGIN
SELECT * FROM park_info;
END;
-- 调用存储过程
CALL proce_test
-- 查询存储过程
-- 1.查询db_name 数据库中的所有的存储过程
SELECT name from mysql.proc where db='property_manage'
-- 2.查询存储过程的状态信息
show PROCEDURE STATUS
-- 3.查询某个存储过程的定义
show CREATE PROCEDURE proce_test
-- 删除存储过程
drop PROCEDURE proce_test
4.6语法
存储过程是可以编程的,急味若可以使用变显,表达式,控制结构,来完成比较复杂的功能。
4.6.1变量
·DECLARE
通过DECLARE可以定义一个局部变显,该变品的作用范国只能在BEGIN...END块中。
DECLARE var_name[,...]type [DEFAULT value]
-- 存储过程的语法--变量 DECLARE
delimiter $
create procedure pro_test2()
begin
declare num int default 5;
select num+10;
end$
delimiter ;
·SET
直接试值使用ST,可以赋显或者赋表达式,,具体语法如下:
SET var_name expr [var_name expr]...
-- 存储过程的语法--变量 SET
delimiter $
create procedure pro_test3() begin declare num int default 0;
set num=num+10;
select num;
end $
delimiter ;
也可以通过select ..into..方式进行赋值操作:
DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
declare countnum int;
select count(*) into countnum from park_info;
select countnum;
END $
DELIMITER ;
4.6.2 if条件判断
语法结构:
if search_condition then statement_list
[elseif search_condition then statement_list]...
[else statement_list]
end if
比如现在有一个需求
180以及以上====>身材高挑
170-180===>标准身材
170以下====>一般身材
CREATE PROCEDURE pro_test9()
BEGIN
DECLARE height INT DEFAULT 181;
DECLARE description VARCHAR(50) DEFAULT '';
IF height >= 180 THEN
SET description = '身材高挑';
ELSEIF height >= 170 AND height < 180 THEN
SET description = '标准身材';
ELSE
SET description = '一般身材';
END IF;
SELECT CONCAT('最终的身材是', description) AS result;
END;
4.6.3 传递参数
语法格式:
create procedure procedure_name([in/out/inout]参数名 参数类型)
...
IN:该参数可以作为输入,也就是需要调用方传入值,默认
OUT:该参数作为输出,也就是该参数可以作为返回值
INOUT:既可以作为输入参数,也可以作为输出参数
IN.输入
需求:根据定义的身高变量,判断当前身高的所属的身材类型
CREATE PROCEDURE pro_test10(in height int )
BEGIN
DECLARE description VARCHAR(50) DEFAULT '';
IF height >= 180 THEN
SET description = '身材高挑';
ELSEIF height >= 170 AND height < 180 THEN
SET description = '标准身材';
ELSE
SET description = '一般身材';
END IF;
SELECT CONCAT('最终的身材是', description) AS result;
END;
OUT.输出
需求:根据传入的身高变量 获取当前身高所属的身材类型
CREATE PROCEDURE pro_test11(in height int,out description VARCHAR(50) )
BEGIN
IF height >= 180 THEN
SET description = '身材高挑';
ELSEIF height >= 170 AND height < 180 THEN
SET description = '标准身材';
ELSE
SET description = '一般身材';
END IF;
END;
-- 调用 @description代表的是用户变量 当断开连接 当前的会话变量全部清空
CALL pro_test11(188,@description)
SELECT @description
4.6.4 case
-- case 结构
CREATE PROCEDURE pro_test13(in month int,out result VARCHAR(50) )
BEGIN
CASE
WHEN month>=1 and month<=2 THEN
set result='第一季度';
WHEN month>=3 and month<=6 THEN
set result='第二季度';
WHEN month>=7 and month<=9 THEN
set result='第三季度';
ELSE
set result='第四季度';
END CASE;
END;
call pro_test13(1,@result)
SELECT @result
4.6.5 while循环
语法结构:
while searchcondition do
statement_list
end while;
需求:计算从1加到n的值
CREATE PROCEDURE pro_test16(in n int)
BEGIN
DECLARE total int DEFAULT 0;
DECLARE num int DEFAULT 0;
WHILE num<=n DO
set total=total+num;
set num=num+1;
END WHILE;
SELECT total;
END;
CALL pro_test16(2)
4.6.6 repeat结构
有条件的循环控制语句,当满足条件的时候退出佰环。whle是满足条件才执行,repeat是满足条件就退出循环,语法结构:
REPEAT
statementlist
UNTIL search_condition
END REPEAT:
需求:计算从1加到n的值
-- repeat循环
CREATE PROCEDURE pro_test19(in num int)
BEGIN
DECLARE total int DEFAULT 0;
REPEAT
set total=total+num;
set num=num-1;
UNTIL num=0
END REPEAT;
SELECT total;
END;
CALL pro_test19(3)
-- 注意unitl后面的';'不要否则会报错
4,6.7loop语句
LOOP实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用LEAVE语句实现,具体语法如下:
[begin_label:LOOP
statement_list
END LOOP [end_label]
如果不在statement_list中增加退出盾环的语句,那么LOOP语句可以用来实现简单的死循环。
4.6.8 leave语句
用来从正的流程构造中退出,通常和BEGN,,ND或者循环一起使用。下面是一个使用LOOP和LEAVE的简单例子,退出循环:
-- loop==leave
CREATE PROCEDURE pro_test20(in num int)
BEGIN
DECLARE total int DEFAULT 0;
c: LOOP
set total=total+num;
set num=num-1;
IF num<0 THEN
LEAVE c;
END IF;
END LOOP c;
SELECT total;
END;
CALL pro_test20(4)
4.6.9游标/光标
游标是用来存储查问结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环的处进。光标的使用包括光标的声明OPEN、 FETCH和CLOSE,其语法分别如下.
声明光标:
DECLARE cursor_name CURSOR FOR select_statement
OPEN光标
OPEN cursor_name
FETCH光标:
FETCH cursor_name INTO var_name [var_name]...
CLOSE光标:
CLOSE cursorname
-- 通过循环来获取数据
-- 边界变量
DELIMITER //
CREATE PROCEDURE pro_test31()
BEGIN
DECLARE e_id INT(11);
DECLARE e_name VARCHAR(50);
DECLARE e_age INT(11);
DECLARE e_salary INT(11);
DECLARE has_data INT DEFAULT 1;
DECLARE emp_result CURSOR FOR SELECT * FROM emp;
DECLARE EXIT HANDLER FOR NOT FOUND SET has_data = 0;
OPEN emp_result;
FETCH_LOOP: REPEAT
FETCH emp_result INTO e_id, e_name, e_age, e_salary;
IF has_data = 1 THEN
SELECT CONCAT('id=', e_id, ' name=', e_name, ' age=', e_age, ' salary=', e_salary) AS result;
END IF;
UNTIL has_data = 0 END REPEAT FETCH_LOOP;
CLOSE emp_result;
END //
DELIMITER ;
4.7存储函数
语法结构
CREATE FUNCTION function_name([param type ..])
RETURNS type
BEGIN
END;
案例:定义一个存储过程,请求满足条件的总记录数
CREATE FUNCTION fun1(parkId int)
returns int
BEGIN
DECLARE cnum int;
SELECT COUNT(*) into cnum from park_info ;
return cnum;
END;
SELECT fun1(1)
5.触发器
5.1介绍
触发器是与表有关的数居库对象,指在insert/update/delete之前或,之后,触发井执行触发器中定义的SQL语句朱合,触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。
使用别名OLD和NEW来引用触发带中发生变化的记录内容,这与其他的数据库是相以的。现在触发器还只支持行级触发,不支持语句级触发,
5.2 创建触发器
语法结构
create triggertrigger_name
before/after insert/update/delete
on tbl_name
[for each row]
trigger_stmt;