mysql进阶知识_MySQL进阶知识

视图

概念：给某个查询语句设置别名，创建这个别名的过程就叫创建视图，这个别名就是视图；注意：视图其实就是给一个select创建别名，在调用的时候才会生效，所以如果源数据内容更改，则视图内容同样会跟着改变

创建格式

create view 视图名称asSQL;

修改视图

alter view 视图名称as新SQL;

删除视图

drop view 视图名称;

例如：

create view v1as select * from student where sid > 10;

触发器

概念：SQL触发器，是一种特殊类型的存储过程，不由用户直接调用。它在指定的表中的数据发生变化时自动生效。唤醒调用触发器以响应 INSERT、UPDATE 或 DELETE 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误(例如，磁盘空间不足)，则整个事务即自动回滚。常常用于强制业务规则和数据完整性。

创建格式

delimiter//# 更改断句符create trigger 触发器名称 BEFORE INSERT on 表名 forEACH ROW

#trigger后面跟触发器名字，表名是告知触发器要在那个表的内容有变动之前做操作，EACH ROW是数据变动一行就触发一次触发器BEGIN

INSERT into teacher(tname) values(NEW.sname);END //# 还原断句符

delimiter ;

创建数据之前触动触发器create table t1(id int auto_increment key,name varchar(10) not null)engine=innodb;create table t2(id int auto_increment key,py varchar(10) not null)engine=innodb;

delimiter \\create trigger tt1 BEFORE INSERT on t1 forEACH ROWBEGIN

insert into t2(py) values(NEW.name);END\\

delimiter ;insert into t1(name) values('梁晓');

创建数据之后触动触发器create table t1(id int auto_increment key,name varchar(10) not null)engine=innodb;create table t2(id int auto_increment key,py varchar(10) not null)engine=innodb;

delimiter \\create trigger tt2 AFTER INSERT on t1 forEACH ROWBEGIN

insert into t2(py) values(NEW.name);END\\

delimiter ;insert into t1(name) values('王文杰');

删除数据之前触动触发器create table t1(id int auto_increment key,name varchar(10) not null)engine=innodb;create table t2(id int auto_increment key,py varchar(10) not null)engine=innodb;

delimiter \\create trigger tt2 BEFORE DELETE on t1 forEACH ROWBEGIN

insert into t2(py) values(NEW.name);END\\

delimiter ;insert into t1(name) values('王文杰');

删除数据之后触动触发器create table t1(id int auto_increment key,name varchar(10) not null)engine=innodb;create table t2(id int auto_increment key,py varchar(10) not null)engine=innodb;

delimiter \\create trigger tt2 AFTER DELETE on t1 forEACH ROWBEGIN

insert into t2(py) values(OLD.name);END\\

delimiter ;insert into t1(name) values('王文杰');

更新数据之前触动触发器create table t1(id int auto_increment key,name varchar(10) not null)engine=innodb;create table t2(id int auto_increment key,py varchar(10) not null)engine=innodb;

delimiter \\create trigger tt2 BEFORE UPDATE on t1 forEACH ROWBEGIN

insert into t2(py) values(NEW.name);END\\

delimiter ;insert into t1(name) values('王文杰');

更新数据之后触动触发器create table t1(id int auto_increment key,name varchar(10) not null)engine=innodb;create table t2(id int auto_increment key,py varchar(10) not null)engine=innodb;

delimiter \\create trigger tt2 AFTER UPDATE on t1 forEACH ROWBEGIN

insert into t2(py) values(OLD.name);END\\

delimiter ;insert into t1(name) values('王文杰');

注意：这里的NEW代表的是新数据，OLD代表的是旧数据

函数

内置函数

mysql中有内置函数，也可以自定义函数，函数用来完成用户的一些特定功能；下面附关于内置函数的教程地址。

https://dev.mysql.com/doc/refman/5.7/en/functions.html(官方地址)

https://www.runoob.com/mysql/mysql-functions.html     (中文教程)

函数的使用格式

select Function();

# Function是函数名，函数名后面可以不跟载体，直接加入参数或者不加

有一个比较要注意的内置date_format，下面附关于这个内置函数的一些相关参数：

%a Abbreviated weekday name (Sun..Sat)%b Abbreviated month name (Jan..Dec)%c Month, numeric (0..12)%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)%d Day of the month, numeric (00..31)%e Day of the month, numeric (0..31)%f Microseconds (000000..999999)%H Hour (00..23)%h Hour (01..12)%I Hour (01..12)%i Minutes, numeric (00..59)%j Day of year (001..366)%k Hour (0..23)%l Hour (1..12)%M Month name (January..December)%m Month, numeric (00..12)%p AM or PM%r Time, 12-hour (hh:mm:ss followed by AM or PM)%S Seconds (00..59)%s Seconds (00..59)%T Time, 24-hour (hh:mm:ss)%U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0

%u Week (00..53), where Monday is the first day of the week; WEEK() mode 1

%V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X%v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x%W Weekday name (Sunday..Saturday)%w Day of the week (0=Sunday..6=Saturday)%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v%Y Year, numeric, four digits%y Year, numeric (two digits)%% A literal %character%x x, for any “x” not listed above

自定义函数

创建自定义函数

delimiter \\

# 更改换行符create functionf1(

#createfunction是固定格式，f1是函数名

i1int,

# i1是参数，int是参数类型

i2int)

# i2是参数，int是参数类型returns int# 设定返回值的数据类型，这个操作叫强定义BEGIN

declare num int;

# 声明一个变量名，并且声明这个变量名的类型set num = i1 +i2;

# 设定函数要操作的具体内容，这里操作具体内容不能用mysql本身有的东西，比如selectreturn(num);

# 返回一个数据END\\

# 结束

delimiter ;

# 还原换行符

删除自定义函数drop function f1;

执行自定义函数select f1(11,22);

# 在查询中执行函数

存储过程

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

存储过程的创建

delimiter//

create procedurep1()

#create procedure是固定格式 p1()是存储过程BEGIN# 设定开始标识select * fromt1;

# 存储过程需要执行的SQL语句END//# 设定结束标识

delimiter ;

call p1()

# 执行p1这个存储过程

存储过程的相关参数in仅用于传入参数用

out 仅用于返回值用

inout 既可以传入又可以当作返回值

所有参数的存储过程实例

delimiter \\create procedurep1(in i1 int,in i2 int,

inout i3int,

out r1int)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;

#@变量名是用来创建mysql会话级的变量

set @t2 = 0;

CALL p1 (1, 2 ,@t1, @t2);SELECT @t1,@t2;

# 这里@变量名也是用来调用会话级的变量

事物

事物用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。事物需要借助存储过程来进行完成

事物的执行性能并不高

示例

delimiter \\create PROCEDUREp1(

OUT p_return_codetinyint# 定义一个存储过程，并且让这个存储过程返回一个值，以便程序调用

)BEGIN

DECLARE exit handler forsqlexception

# 如果监测到事物执行异常，则执行下面的两个代码BEGIN

--ERROR

set p_return_code = 1;

# 设置p_return_code的返回值是1rollback;

# 事物回滚END;DECLARE exit handler forsqlwarning

# 如果事物执行成功一部分，则执行下面的内容BEGIN

--WARNING

set p_return_code = 2;

# 设置p_return_code的返回值是2rollback;

# 回滚END;

STARTTRANSACTION;

# 开始执行事物DELETE fromtb1;

# 事物内容第一行insert into tb2(name)values('seven');

# 事物内容第二行COMMIT;

# 提交--SUCCESS

set p_return_code = 0;

# 如果上面的事物执行成功，则设置p_return_code的返回值0END\\

delimiter ;

动态执行SQL(防SQL注入)

BEGIN#Routine body goes here.../*SQL语句变量*/

DECLARE vstrSql VARCHAR(3000) DEFAULT '';/*映射表字段*/

DECLARE vSourceTableName VARCHAR(50);DECLARE vSourceIdFieldName VARCHAR(50);DECLARE vSourceNameFieldName VARCHAR(50);DECLARE vTargetTableName VARCHAR(50);DECLARE vTargetIdFieldName VARCHAR(50);DECLARE vTargetNameFieldName VARCHAR(50);/*更新数据*/

DECLARE vnewValue VARCHAR(100);DECLARE vSourceIdFieldValue VARCHAR(100);DECLARE Done INT DEFAULT 0;/*声明游标*/

DECLARE curRow CURSOR FOR

SELECTSourceTableName,SourceIdFieldName,SourceNameFieldName,TargetTableName,TargetIdFieldName,TargetNameFieldNameFROMdb_redundancy.TableFieldMap;/*设置终止标记*/

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;/*打开游标*/

OPENcurRow;/*循环取出数据*/

FETCH NEXT FROM curRow INTOvSourceTableName,vSourceIdFieldName,vSourceNameFieldName,vTargetTableName,vTargetIdFieldName,vTargetNameFieldName;WHILE Done<>1DO

#获取需要更新的数据SELECT SourceNameFieldNewValue,SourceIdFieldValue INTO vnewValue,vSourceIdFieldValue FROMdb_redundancy.TableFieldValueModifyLogWHERE IsProcess=0 AND SourceTableName=vSourceTableName AND SourceIdFieldName=vSourceIdFieldName AND SourceNameFieldName=vSourceNameFieldNameORDER BY CreateDate DESC limit 1;IF ISNULL(vnewValue) <> NULL || LENGTH(trim(vnewValue))>1 THEN#拼接语句SET vstrSql=CONCAT('UPDATE',vTargetTableName,'SET', vTargetNameFieldName ,'= "',vnewValue,'" WHERE', vTargetIdFieldName ,'=', vSourceIdFieldValue,';');

#注意很重要，将连成成的字符串赋值给一个变量(可以之前没有定义，但要以@开头)SET @vSql=vstrSql;

#预处理需要执行的动态SQL，其中stmt是一个变量PREPARE stmt FROM @vSql;

#执行语句EXECUTEstmt ;

#释放语句DEALLOCATE PREPAREstmt;

#更新历史表中状态标记UPDATE db_redundancy.TableFieldValueModifyLog SET IsProcess=1 WHERE IsProcess=0 AND SourceTableName=vSourceTableName AND SourceIdFieldName=vSourceIdFieldName AND SourceIdFieldValue=vSourceIdFieldValue;END IF;

#重置数据SET vnewValue='';SET vstrSql='';FETCH NEXT FROM curRow INTOvSourceTableName,vSourceIdFieldName,vSourceNameFieldName,vTargetTableName,vTargetIdFieldName,vTargetNameFieldName;END WHILE;/*关闭游标*/

CLOSEcurRow;END

注意：防SQL注入可以在程序级别操作，也可以在SQL语句中做限制

索引

索引是一种数据加速查找的结构文件，需要使用者通过相关的SQL语句进行创建

索引概念

加速查找及部分约束

功能描述

主键索引：加速查找，且内容不能为空、不能重复

普通索引：加速查找

唯一索引：加速查找，且不能重复

组合索引：多列组合成一个索引，功能可以参考上面的索引内容，组合的方式有如下几种

组合主键索引

组合唯一索引

组合普通索引

功能分类

hash索引与btree索引，默认使用btree索引

索引种类

查询快、插入/更新慢、加速查找需要命中索引内容才可以

索引特点

组合索引：特点是最左前缀匹配，组合索引效率大于索引合并

覆盖索引：在索引文件中直接获取数据，这种方式其实也没有多少加速效果

索引合并：把多个单列索引合并使用，特点是方便一个表中有多列需要加速，不足是联合索引速度不如组合索引

名词介绍

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。即使建立索引，索引也不会生效，索引的命中规则如下：- like '%xx'(尽量不要用like匹配)select * from tb1 where name like '%cn';-使用函数(不要用函数)select * from tb1 where reverse(name) = 'wupeiqi';-orselect * from tb1 where nid = 1 or email = 'seven@live.com';

特别的：当or条件中有未建立索引的列才失效，以下会走索引select * from tb1 where nid = 1 or name = 'seven';select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'

-类型不一致

如果列是字符串类型，传入条件是必须用引号引起来，不然...select * from tb1 where name = 999;- !=

select * from tb1 where name != 'alex'特别的：如果是主键，则还是会走索引select * from tb1 where nid != 123

- >

select * from tb1 where name > 'alex'特别的：如果是主键或索引是整数类型，则还是会走索引select * from tb1 where nid > 123

select * from tb1 where num > 123

-order byselect email fromtb1 order by name desc;

当根据索引排序时候，选择的映射如果不是索引，则不走索引

特别的：如果对主键排序，则还是走索引：select * fromtb1 order by nid desc;-组合索引最左前缀

如果组合索引为：(name,email)

name and email--使用索引

name--使用索引

email--不使用索引

注意事项：- 避免使用select *

- count(1)或count(列) 代替 count(*)- 创建表时尽量时 char代替 varchar-表的字段顺序固定长度的字段优先-组合索引代替多个单列索引(经常使用多个条件查询时)-尽量使用短索引- 使用连接(JOIN)来代替子查询(Sub-Queries)-连表时注意条件类型需一致- 索引散列值(重复少)不适合建索引，例：性别不适合

索引命中

# 作用：加速查询

# 单独创建：create index index_name on table_name(column_name)

# 删除格式：drop index_name on table_name;

# 查看索引：show indexfromtable_name;

# 连表创建：

create table in1(

nidint not nullauto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

index ix_name (name)

)

注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

普通索引

# 作用：加速查询 和 唯一约束(可含null)

# 单独格式：create unique index 索引名 on 表名(列名)

# 删除格式：drop unique index 索引名 on 表名

# 连表创建：

create table in1(

nidint not nullauto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

unique ix_name (name)

)

唯一索引

# 作用：加速查询 和 唯一约束(不可含null)

# 定义格式：alter table 表名 add primary key(列名);

# 删除格式1：alter table 表名 drop primary key;

# 删除格式2：alter table 表名 modify 列名int, drop primary key;

# 创建表+创建主键

create table in1(

nidint not nullauto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

index ix_name (name)

)

或者

create table in1(

nidint not nullauto_increment,

name varchar(32) not null,

email varchar(64) not null,

extra text,

primary key(ni1),

index ix_name (name)

)

主键索引

# 作用：将n个列组合成一个索引

# 应用场景：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。

# 创建表

create table in3(

nidint not nullauto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text

)

# 创建组合索引

create index ix_name_email on in3(name,email);

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

# 如上创建组合索引之后，查询：

name and email--使用索引

name--使用索引

email-- 不使用索引

组合索引