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) 编辑收藏举报

刷新页面返回顶部

luyi001