mysql进阶3_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。。。);

联合索引需要注意的点：

联合索引的最左匹配原则，即最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、

多字段的联合索引在查询单个字段时是否可以用到索引：如下面的联合索引的语法所示，如果你查询单个字段时，查询的是列名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条件上不要使用运算函数，以免索引失效