mysql基础进阶

视图

一种虚拟存在的数据表，在数据库当中并不存在

作用：将复杂查询语句的结果封装到一个虚拟表中，后续有重复需求，直接查虚拟表

create view 视图名 [(列名)] as 查询语句；创建

select * from 视图名；查询

update 视图名 set 列名=值 where 条件；修改

alter view 视图名 （列名）as 查询语句；修改结构

drop view 视图名；删除

存储过程和函数

事先经过编译并存储在数据库中的一段sql语句的集合，减少数据和应用服务器之间的传输，存储函数必须有返回值。

语法：

delimiter$    --修改结束分隔符

create procedure 存储过程名称（参数列表）

begin  

    sql语句

end$

delimiter；

call 存储过程名称（实际参数）； --调用

select * from mysql.proce where db='库名'；--查看数据库中所有的存储过程

drop procedure 存储过程名称；--删除

1.变量的使用

declare 变量名 数据类型 [默认值]；--定义变量

set 变量名 = 值；--赋值1

select 列名 into 变量名 from 表名 [where 条件]；--赋值2

2.if语句

if 判断条件1 then 执行语句1；[elseif...]

[else 执行语句n]；

end if；

3.参数传递

create procedure 存储过程名称（[in][out][inout]参数名 数据类型）

begin      --in：输入，实际参数   out：输出，可做返回值      inout：输入输出都行

      sql语句

end$

4.while循环语句

初始化语句；

while 条件判断 do

循环体语句；

条件控制语句；

end while；

5.存储函数语法

create function 函数名称（参数列表）

returns 返回值类型

begin  

   sql语句列表；

   return 结果；

end$

select function（实际参数）；--调用

drop function 函数名称；--删除

触发器

• 一条语句执行触发另一语句执行，是与表有关的数据库对象，在插入，修改，删除之前或是之后触发并执行触发器当中定义的sql语句。
• 协助应用系统在数据库端确保数据的完整性，日志记录，数据校验等操作。
• 使用别名new和old来引用触发器中发生变化的内容记录

语法：

delimiter$

create trigger 名称

before|after insert|update|delete

on 表名

for each row

begin

触发器要执行的功能

end$

delimiter；

show triggers；--查看

drop trigger 名；--删除

事务

一条或多条sql语句组成一个执行单元

1.基本使用

start transaction；--开启事务

rollback；--回滚事务

commit；--提交事务

2.提交方式

   1 --自动    0 --手动

select @@autocommit；--查看提交方式

set @@autocommit = 数字；--修改事务提交方式

3.四大特征

• 原子性：成功则应用到数据库，失败不对数据库产生任何影响
• 一致性：执行前后的都处于一致性状态
• 隔离性：多个并发事务间相互隔离，互不干扰
• 持久性：事务提交，对数据库的改变是永久性的

4.隔离级别

脏读：事务1读取到事务2未提交的数据，两次查询结果不一致

幻读：查询不存在，插入却存在（无中生有）

不可重复读：事务1读取事务2修改并提交的数据

隔离级别	引发问题
读未提交read uncommited	脏读，幻读，不可重复读
读已提交read commited	幻读，不可重复读
可重复读repeatable read	幻读
串行化serializable	无

select @@tx_isolation；--查询隔离级别

set global transaction isolation level 级别字符串；--修改后需重新连接

存储引擎

客户端连接，支持多种语言c java

第一层：网络连接层，连接池用来管理，缓冲用户的连接

第二层：核心服务层

• 管理服务和工具，备份，复制，集群
• SQL接口，接受sql命令，返回查询结果
• 查询解析，验证和解析sql命令
• 查询优化，查询前使用默认优化机制优化sql语句
• 缓存，缓存中有结果直接返回，没有再查

第三层：存储引擎，插拔式

第四层：系统文件层，配置文件，数据文件，日志文件等

Mysql常用有3种

1. MyISAM：访问快，不支持事务和外键，查询为主
2. InnoDB：支持事务，外键，并发控制，占用磁盘空间大
3. MEMORY：内存存储，速度快，不安全，适合小量快速访问

show engines；

show table status from 库名；

create table t（列名 数据类型...）engine=引擎名；

alter table t engine = 引擎名；

索引

协助mysql高效获取数据的一种数据结构，这种数据结构以某种方式指向数据。

普通，唯一，主键，联合，外键，全文

BTree索引，Hash索引

create [unique | fulltext] index 索引名 [using 索引类型] on 表名（列名）；

show index from 表名；--查看

alter table 表名 add index...；--添加

drop index 索引名 on 表名；--删除

BTree索引基于B+tree数据结构

磁盘存储：系统从磁盘读取数据到内存是以磁盘块为基本单位的，Inodb有页的概念，是磁盘管理的最小单位，默认大小是16kb，若干地址连接磁盘块，以此来达到页的大小是16kb，能够减少IO次数。

BTree：id 指针 数据 根节点不仅保存指针还保存数据，查询增加磁盘IO

B+Tree：真实数据保存在叶节点上，叶节点相连接，能进行范围查询

索引建立原则

• 对查询频次高，数据量大的表建立索引
• 使用唯一索引，区分度高，效率高
• 索引字段选择，最佳候选列应当从where条件中提取
• 索引不是越多越好
• 最左匹配适用于组合索引

锁

共享资源被并发访问时保证数据的一致性和安全性，

InoDB默认表锁

select 语句 lock in share mode；--创建共享锁

select 语句 for update；--创建排他锁

MyCat

主从复制

读写分离：主写副读

水平拆分：根据数据逻辑关系将数据按某种条件拆分到多台服务器

垂直拆分：根据数据业务维度拆分