mysql|Mysql高级学习笔记

Mysql高级: 索引:

索引是帮助Mysql高效获取数据的数据结构(有序) BTREE索引: B+树(多路平衡搜索树) 结构:BTree又叫多路平衡搜索树,一颗m叉的BTree特性如下: 1.树中每个节点最多包含m个孩子 2.除根节点于叶子节点,每个节点至少有[ceil(m/2)]个孩子 3.若根节点不是叶子节点,则至少有两个孩子 4.所有的叶子节点都在同一层 5.每个非叶子节点由n个key与n+1个指针组成,[ceil(m/2)-1] <= n <= m-1 (当n大于m-1时,中间节点分裂到夫节点,两边节点分裂) B+TREE结构: 1.n叉B+Tree最多包含n个key,而BTree最多含有n-1个key 2.B+Tree的叶子节点保存所有的key信息,依key大小顺序排列 3.所有的非叶子节点都可以看作是key的索引部分 (B+Tree只有叶子节点保存key信息,查询任何key都要从root走到叶子,查询稳定性高) Mysql中的B+Tree: Mysql的索引数据结构对经典的B+Tree进行了优化,在原B+Tree的基础上,增加一个指向相邻叶子节点 的链表指针,形成了带有顺序指针的B+Tree,提高区间访问的性能 索引分类: 单值索引:即一个索引只包含单个列,一个表可以有多个单列索引 唯一索引:索引列的值必须唯一,但允许有空值 复合索引:即一个索引包含多个列 索引语法: 创建索引:create [索引类型] index 索引名称 on 表名(字段) 查看索引:show index from 表名 (\G 切换格式) 删除索引:drop index 索引名 on 表名 ALTER命令: alter table 表名 add [索引类型] 索引名(字段) 索引设计原则: 1.查询频次较高,且数据量比较大的表建立索引 2.索引字段的选择,最佳候选列应当从where子句的条件中提取 3.唯一索引,区分度越高,使用索引的效率越高 4.索引不是多多益善 5.使用短索引 6.利用最左前缀,N个列组合而成的组合索引

视图:View
视图是一种虚拟存在的表,视图并不在数据库中实际存在,行和列数据来自定义视图的查询中 使用的表,并且实在使用视图时动态生成的 视图相对于普通的表的优势:简单、安全、数据独立 创建视图: create view 视图名 as select语句 修改视图: alter view 视图名 as select语句 查看视图: show tables; show create view 视图名 删除视图: drop view 视图名

存储过程和存储函数:
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合, 存储过程和函数的区别在于函数必须有返回值,而存储过程没有 函数:是一个有返回值的过程 过程:是一个没有返回值的函数 创建存储过程: cteate procedure 存储过程名() begin SQL语句 end delimiter 分隔符声明 调用存储过程: call 存储过程名() 查看存储过程: select name from mysql.proc where db = '数据库名' 查询数据库中所有的存储过程 show procedure status 存储过程状态信息 show create procedure 存储过程名 查看存储的定义 删除存储过程: drop procedure 存储过程名 语法: 变量:定义 DECLARE declare 变量名 int(类型) default ... 赋值 SET、INTO set 变量名 = ... select ... into ... 赋值 if条件判断: if search_condition then ... else if ... then ... end if 传递参数: create procedure 存储过程名([in/out/inout] 参数名 参数类型) ... IN:输入参数(默认)OUT:输出参数 @变量名:代表用户会话变量 @@变量名:系统变量 case结构: 1.case 值 when 值 then ... else ... end case 2.case when 条件 then ... else ... end case while循环: while 条件 do ... end while repeat循环: repeat ... until 条件 end repeat loop循环: [label:]loop ... leave [label] end loop [label] leave语句:用来从标注的流程构造中退出 游标/光标:用来存储查询结果集的数据类型 声明:declare 游标名 cursor for ... open 游标名 fetch 游标名 into ... close 游标名 declare exit ... for not found set ... 定义结束 存储函数: create function 函数名() returns type begin ... end 调用:select 函数名()

触发器:
是与表有关的数据库对象,指在 insert/update/delete 之前或之后,触发并执行触发器 中定义的SQL语句集合,可以协助应用数据库端确保数据的完整性,日志记录,数据校验等操作 使用别名OLD和NEW来引用触发器中发生变化的记录内容 创建触发器: create trigger 触发器名 before/after insert/update/delete on 表名 [for each row] -- 行级触发器 begin ... end 查看触发器:show triggers 删除触发器: drop trigger 触发器名

    推荐阅读