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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

推荐码发放 2018-09-04 07:02:56 浏览891

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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

执行存储过程 
CALL p47_emp(‘001’,SYSDATE())

原文地址https://blog.csdn.net/LetonLIU/article/details/82354206