MySQL视图索引与存储过程精析

1.MySQL分页查询

1.1 limit函数：

SELECT * FROM emp LIMIT 3 –只查询三条数据，其他忽略

1.2 select * from emp order by empno limit2,4 ; 注： limit 分页最好和排序并用，并且要放在排序后面。limit的一个属性值是下标值，第二个属性值是长度。

2.视图

2.1视图理解：经常用到的数据为了便于查询，将数据结果放到视图当中，便于以后的查询，视图中的数据结果跟原表同步。

2.2视图操作：

create view v_emp as select *from emp where deptno=10;

select * from v_emp where sal=5000;

update v_emp sat sal=1234 where sal=5000;

insert into v_emp(empno,ename,deptno)values(8001,’stu1’,10);

delete from v_emo where empno =8001;

create or replace view v_dept as select * from dept where deptno=20;//创建或替换视图v_dept，查询表中deptno 为30的数据；

2.3查看所有视图，不包含表:

show tables like ‘v_%’

2.4查看视图详情

desc v_emp

3.索引

3.1索引理解：

将常查询的字段维护到一张（虚拟表），查询该字段的时候到这个虚拟表中查询到对应的表的位置

优点：查询效率高。（因为查询的时候先从已经去除多余的字段的小虚拟表中查询，在找到对应的数据信息，所以效率高。）

缺点：赠删改效率偏低。（因为正删改的时候要维护表的同时还要维护一下索引）；

3.2普通索引：

普通索引是索引的对应字段可重复

可重复

查询效率低：（因为有索引中重复值，所以查询到结果的时候要继续检索，一直将全表检索完毕）

3.3唯一索引：

唯一索引是索引对应的字段不可重复

不可重复

查询效率快：（因为没有重复值，所以查询到结果后直接返回，不用全表检索）

3.4索引操作

为emp表的empno字段创建一个索引i_emp:

create index i_emp on emp(empno)

修改emp表，为其添加一个索引i_emp_deptno(deptno):

alter table emp add index i_emp_deptno(deptno);

删除emp表的i_emp索引：

drop index i_emp on emp；

为员工表创建唯一索引，名为i_emp_empno，字段：empno:

create unique index i_emp_empno on emp(empno);

修改表方式为员工表的ename字段创建唯一索引:

alter table emp add unique index i_emp_ename(ename)

4.存储过程

4.1存储过程介绍：

简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，主要应用场景是etl（数据清洗）

4.2需求分析：

•需求分析

•将emp表中的数据插入到t48_emp表中

•注意：t48_emp表比emp表多两个字段，eventkey,etldt

•因为emp表中没有这两个字段，所有查询emp表中的所有字段也差不多这两个字段的值，所以要做etl数据清洗（加工）

•Eventkey字段的数据格式要求：001-入职时间(年月日)-empno

•Etldt字段的数据格式要求：当前系统时间-年月日时分秒

•将emp表中的comm字段如果是null则用0来替换

4.3ETL循序渐进

4.3.1查询emp表中的所有字段，将comm字段的null替换成0，并额外查询两个属性：eventkey，etldt

SELECT empno,ename,job,mgr,hiredate,sal,IFNULL(comm,0),deptno from emp

SELECT CONCAT(‘001-‘,DATE_FORMAT(HIREDATE,’%Y%m%d’),’-‘,empno) eventkey,DATE_FORMAT(SYSDATE(),’%Y%m%d%H%i%s’) etldt,

empno,ename,job,mgr,hiredate,sal,IFNULL(comm,0),deptno from emp

4.3.2查询emp表中的所有字段，将comm字段的null替换成0，并额外查询两个属性：eventkey，etldt，将查询到的结果插入到t48_emp表中

INSERT INTO t48_emp(eventkey,etldt, empno,ename,job,mgr,hiredate,sal,comm,deptno)

SELECT

CONCAT(‘001-‘,DATE_FORMAT(HIREDATE,’%Y%m%d’),’-‘,empno) eventkey,DATE_FORMAT(SYSDATE(),’%Y%m%d%H%i%s’) etldt,

empno,ename,job,mgr,hiredate,sal,IFNULL(comm,0),deptno

from emp；

5、存储过程的使用场景

用以上的sql语句即可完成数据迁移清洗（etl），在真实工作场景中经常要用到，后期学到大数据的时候也会用到etl数据清洗。以上的sql语句不便于存储和调用，每次都要输入过长的sql语句，为了便于etl的操作，可以使用存储过程。

查看存储过程的格式规范：

CREATE DEFINER = CURRENT_USER PROCEDURE NewProc()

BEGIN

#Routine body goes here…

END;;

执行存储过程p48_emp()

Call语句执行存储过程

格式：call 存储过程名称(实参)

call p48_emp()

6.存储过程优化：

因为存储过程执行中每个执行语句没必然联系，没有必要一起成功失败，所以每个执行提直接要加commit来提交事物

CREATE  PROCEDURE `p47_emp`(mybefore VARCHAR(10),etldt VARCHAR(30))BEGIN                                                                        -- 过程开始    INSERT INTO t48_emp(    eventkey                ,            -- 事件编码    etldt                   ,            -- etl时间    empno                   ,            -- 员工号    ename                   ,            -- 员工姓名    job                     ,            -- 职位    mgr                     ,            -- 领导    hiredate                ,            -- 领导    sal                     ,            -- 领导    comm                    ,            -- 领导    deptno                               -- 领导)  SELECT     CONCAT(mybefore,DATE_FORMAT(HIREDATE,'-%Y%m%d-'),empno)     ,     -- '001'    etldt                                                       ,     empno                                                       ,    ename                                                       ,    job                                                         ,    mgr                                                         ,    hiredate                                                    ,    sal                                                         ,    IFNULL(comm,0)                                              ,    deptno

执行存储过程

CALL p47_emp(‘001’,SYSDATE())

原文地址