MySQL进阶

1.视图

概述:视图就相当于一个临时表,但是只定义了对应关系,用这个视图的时候,就会自行执行视图里定义好的sql语句,视图是只能去查原表的数据,不能去修改原表里面的数据的;

视图的增删改查:

1.创建视图:

语法:CREATE VIEW 视图名称 AS SQL语句

示例:

CREATE VIEW v1 AS 
SELECT nid, name FROM user
WHERE nid > 3;

2.删除视图:

语法:DROP 视图名称

3.修改视图

语法:ALTER VIEW 视图名称 AS sQL语句

示例:

ALTER VIEW v1 AS 
SELECT sex, birthday FROM user
WHERE nid > 1

4.使用视图:

示例:

select nid, name from v1;

2.触发器

概述:对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时,可以使用触发器,触发器用于定制用户对表的行进行【增/删/改】前后的行为。

触发器的创建

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

示例1:

delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW. NAME == 'alex' THEN
    INSERT INTO tb2 (NAME)
VALUES
    ('aa')
END
END//
delimiter ;

示例2:

delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW. NAME == 'alex' THEN
    INSERT INTO tb2 (NAME)
VALUES
    ('aa')
END
END//
delimiter ;

注意:delimiter // 把默认以“;”作为结束符改为了以//作为结束符,NEW表示即将插入的数据行,OLD表示即将被删除的数据行

触发器的删除

语法:DROP TRIGGER 触发器的名称;

使用触发器

触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的

3.存储过程

概述:存储过程是一个SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行

创建存储过程

1.无参数的存储过程

示例:

-- 创建存储过程

delimiter //
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;



-- 执行存储过程

call p1()

2.有参数的存储过程

对于存储过程,可以接收三类参数:

  • in:仅用于传入参数用
  • out:仅用于返回值用
  • inout:既可以传入又可以当作返回值

创建有参数的存储过程示例:

-- 创建存储过程
delimiter \\
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    
    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;
    
    set i3 = i3 + 100;

end\\
delimiter ;

-- 执行存储过程
set @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

事务型存储过程示例:

delimiter \\
    create PROCEDURE p1(
        OUT p_return_code tinyint
    )
    BEGIN 
      DECLARE exit handler for sqlexception 
      BEGIN 
        -- ERROR 
        set p_return_code = 1; 
        rollback; 
      END; 
     
      DECLARE exit handler for sqlwarning 
      BEGIN 
        -- WARNING 
        set p_return_code = 2; 
        rollback; 
      END; 
     
      START TRANSACTION; 
        DELETE from tb1;
        insert into tb2(name)values('seven');
      COMMIT; 
     
      -- SUCCESS 
      set p_return_code = 0; 
     
      END\\
delimiter ;

删除存储过程

drop procedure 存储过程名称

4.函数

MySQL中的内置函数

  • INSTR(str, len):返回字符串str从开始的len位置的子序列字符
  • LEFT(str, len):返回字符串str从开始的len位置的子序列字符
  • LOWER(str):变小写
  • UPPER(str):变大写
  • ...

自定义函数

1.定义一个函数:

delimiter \\
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END \\
delimiter ;

-- 执行函数:
f1(1, 3);

2.删除函数:

drop function 函数名称

5.事务

概述:事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性

6.索引

概述:索引是数据库中专门用于帮助用户快速查询数据的一种数据结构,类似于字典中的目录,查找字典内容时可以根据目录查找找到数据的存放位置,然后直接获取即可

MySQL中常见索引有:

1.普通索引:仅有一个功能,那就是加快查询

  • create index 索引名 ob 表名(列名)

2.唯一索引:加速查询和唯一约束(可为null)

  • create unique index 索引名 on 表名(列名)

3.主键索引:加速查询和唯一约束(不可为null)

  • alter table 表名 add primary key(列名);

4.联合索引:将n个列组合成一个索引

  • create index 索引名 on 表名(列名1,列名2。。。);

联合索引需要注意的点:

  • 联合索引的最左匹配原则,即最左优先,以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
  • 多字段的联合索引在查询单个字段时是否可以用到索引:如下面的联合索引的语法所示,如果你查询单个字段时,查询的是列名1和列名2或者查询列名1的话,就能命中索引,如果只查询列名2就中不了索引

索引相关命令

查看表结构:

desc 表名

查看生成表的sql

show create table 表名

查看执行时间:

set profilling = 1;
SQL语句...
show profiles

查看执行计划:

explain select * from tb2;

执行计划里面type属性的值代表的意思

type
查询时的访问方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表扫描,对于数据表从头到尾找一遍
select * from tb1;
特别的:如果有limit限制,则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。

INDEX           全索引扫描,对索引从头到尾找一遍
                select nid from tb1;

RANGE          对索引列进行范围查找
                select *  from tb1 where name < 'alex';
                PS:
                    between and
                    in
                    >   >=  <   <=  操作
                    注意:!= 和 > 符号


INDEX_MERGE     合并索引,使用多个单列索引搜索
                select *  from tb1 where name = 'alex' or nid in (11,22,33);

REF             根据索引查找一个或多个值
                select *  from tb1 where name = 'seven';

EQ_REF          连接时使用primary key 或 unique类型
                select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;



CONST           常量
                表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
                select nid from tb1 where nid = 2 ;

SYSTEM          系统
                表仅有一行(=系统表)。这是const联接类型的一个特例。
                select * from (select nid from tb1 where nid = 1) as A;

7.动态执行sql语句

示例:

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
    declare p1 int;
    set p1 = 11;
    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?';
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\\
delimiter ;

8.如何优化SQL语句

1.列类型尽量定义成数值类型,且长度尽可能短,如主键和外键,类型字段等等

2.建立单列索引

3.根据需要建立多列联合索引

  • 当单个列过滤之后还有很多数据,那么索引的效率将会比较低,即列的区分度较低,那么如果在多个列上建立索引,那么多个列的区分度就大多了,将会有显著的效率提高。

4.根据业务场景建立覆盖索引

  • 只查询业务需要的字段,如果这些字段被索引覆盖,将极大的提高查询效率

5.多表连接的字段上需要建立索引这样可以极大的提高表连接的效率

6.where条件字段上需要建立索引

7.排序字段上需要建立索引

8.分组字段上需要建立索引

9.where条件上不要使用运算函数,以免索引失效

posted @ 2020-09-21 17:48  luyi001  阅读(141)  评论(0编辑  收藏  举报