MySQL进阶操作
视图VIEW
视图是虚拟的表,与数据库里的数据表不一样,视图只有每次调用时都会执行查询。视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的。
1、创建视图
CREATE VIEW 视图名 AS sql语句;
2、删除视图
DROP VIEW 视图名;
3、更新视图
ALTER VIEW 视图名 AS sql语句;
4、使用视图
SELECT * FROM 视图名;
5、示例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 创建视图v1 CREATE VIEW v1 AS SELECT * FROM users WHERE u_id > 10; -- 修改视图v1的sql语句 ALTER VIEW v1 AS SELECT * FROM users WHERE u_id > 5; -- 使用视图v1,进行分组排序限制等操作 SELECT age FROM v1 GROUP BY age ORDER BY age DESC LIMIT 2 OFFSET 1; -- 删除视图v1 DROP VIEW v1;
函数FUNCTION
在mysql中有很多内置函数,也可以自定义函数,自定义函数可以传入参数,也可以返回值,但是函数没办法得到执行语句得到的结果,存储过程可以。
1、创建函数
CREATE FUNCTION f1( i1 INT, i2 INT ) RETURNS INT BEGIN DECLARE num INT; SET num = i1 + i2; RETURN (num); END;
2、调用函数
SELECT f1(10,nid), username FROM tab1; SELECT f1(10,20) INTO @1; SELECT @1;
3、删除函数
DROP FUNCTION 函数名;
存储过程PROCEDURE
存储过程简单来说,就是为以后使用而保存的一条或多条MySQL语句的集合。
简单:通过把处理封装在容易使用的单元中,简化复杂的操作。如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
安全:不要求反复建立一系列处理步骤,这保证了数据的完整性。
高性能:使用存储过程比使用单独的SQL语句要快。
1、创建存储过程
DELIMITER // CREATE PROCEDURE 存储过程名 (参数) BEGIN sql语句1; sql语句2; ... END// DELIMITER ; -- 使用存储过程 CALL 存储过程名;
2、删除存储过程
DROP PROCEDURE 存储过程名; DROP PROCEDURE IF EXISTS 存储过程名; -- 如果存在则删除
3、带参数的存储过程。
参数允许的数据类型与表中使用的数据类型相同;查询集不是允许的类型,因此不能通过一个参数返回多个行和列。
in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值
CREATE PROCEDURE p1 ( IN v1 INT, OUT v2 INT, INOUT v3 INT ) BEGIN DECLARE i INT; DECLARE j INT DEFAULT 5; SET i = 2; SET v1 = i + v3; SET v2 = v1 + j; SELECT age INTO v3 from users WHERE u_id = 10; -- 将查询结果赋给v3 END; -- 执行存储过程 SET @p1_v1 = 1; SET @p1_v2 = 2; SET @p1_v3 = 3; CALL p1 (@p1_v1,@p1_v2,@p1_v3); SELECT @p1_v1,@p1_v2,@p1_v3; -- p1_v1=1, p1_v2=10, p1_v3=28
4、条件判断
当in参数为1时,out返回'first',当in参数为2时,out返回'second',当in参数为其他时,out返回'ahhh'。
CREATE PROCEDURE p4( IN v1 INT, OUT v2 VARCHAR(20) ) BEGIN IF v1 = 1 THEN SET v2 = 'first'; ELSEIF v1 = 2 THEN SET v2 = 'second'; ELSE SET v2 = 'ahhh'; END IF; END; SET @p4_v1 = 2; CALL p4 (@p4_v1,@p4_v2); SELECT @p4_v1,@p4_v2;
5、循环语句
in给一个值v1,然后求1-v1的和
CREATE PROCEDURE p5( in v1 INT, out v2 INT ) BEGIN DECLARE i INT DEFAULT 1; DECLARE sums INT DEFAULT 0; WHILE i <= v1 DO SET sums = sums + i; SET i = i +1; END WHILE; SET v2 = sums; END; SET @v1 = 10; CALL p5 (@v1, @v2); SELECT @v1, @v2; -- 10 55
触发器TRIGGER
触发器作用:当操作某张表时,同时自动执行一些语句。只有DELETE, INSERT, UPDATE支持触发器。
1、创建触发器
CREATE TRIGGER用来创建触发器,AFTER INSERT定义了此触发器将在INSERT语句成功执行后执行,同时插入多行时FOR EACH ROW设置对每个插入行执行。
CREATE TRIGGER 触发器名 AFTER INSERT ON 表名 FOR EACH ROW BEGIN sql语句... END;
2、删除触发器
DROP TRIGGER 触发器名;
3、使用触发器
在INSERT触发器代码内,可引用一个名为NEW的虚拟表,通过它访问被插入的行;在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值)。
-- 插入后,更新u_cls列为插入数据的c_id CREATE TRIGGER t1 AFTER INSERT ON class FOR EACH ROW BEGIN UPDATE users SET u_cls = NEW.c_id WHERE users.u_id = 7; END; -- 测试触发器 INSERT INTO class(c_name,c_num) VALUES('java',20021); -- 插入前,修改插入数据中的c_num为2009,更新u_cls列为插入数据的c_id CREATE TRIGGER t1 BEFORE INSERT ON class FOR EACH ROW BEGIN SET NEW.c_num = 20099; -- 修改NEW中的值 UPDATE users SET u_cls= NEW.c_id WHERE users.u_id = 7; END; -- 测试触发器 INSERT INTO class(c_name,c_num) VALUES('js',20021);
在DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,通过它访问被删除的行;OLD中的值全都是只读的,不能更新。
-- 对class表进行DELETE前,更新u_cls为删除数据中的c_id。 CREATE TRIGGER t1 BEFORE DELETE ON class FOR EACH ROW BEGIN UPDATE users SET u_cls = OlD.c_id WHERE users.u_id = 10; END; -- 测试触发器 DELETE FROM class WHERE c_id = 11;
在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值,引用一个名为NEW的虚拟表访问新更新的值;在BEFORE UPDATE触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值);OLD中的值全都是只读的,不能更新。
-- 对class表进行UPDATE前,将更新数据中的c_name转为小写,更新u_cls为更新前的c_num CREATE TRIGGER t1 BEFORE UPDATE ON class FOR EACH ROW BEGIN SET NEW.c_name = LOWER(NEW.c_name); UPDATE users SET u_cls = OLD.c_num WHERE u_id = 11; END; -- 测试触发器 UPDATE class SET c_num = 20071 WHERE c_id = 13;
事务
事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。InnoDB引擎支持事务管理。
CREATE PROCEDURE p1 (OUT p_return_code TINYINT) BEGIN -- 报错则执行 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET p_return_code = 1; ROLLBACK; END; -- 警告则执行 DECLARE EXIT HANDLER FOR SQLWARNING BEGIN SET p_return_code = 2; ROLLBACK; END; -- 监控的sql语句 START TRANSACTION; DELETE FROM tb1; INSERT INTO tb2 (NAME) VALUES ('seven'); COMMIT; -- 成功则执行 SET p_return_code = 0; END;
MySQL 索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
普通索引,基本的索引,它没有任何限制,只是为了加快查找速度。
唯一索引,增加了唯一约束,就是索引列的值必须唯一,但允许有空值NULL。
主键索引,表中定义一个主键将自动创建主键索引,主键索引要求主键列的每个值是唯一的,并且数据不能为NULL。一个表只能创建一个主键索引。
组合索引,将多列组合建立一个索引,最左匹配。
创建表时指定索引
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE fruits( f_id INT NOT NULL auto_increment, f_name CHAR (30) NOT NULL, f_price FLOAT NOT NULL, PRIMARY KEY (f_id), --创建主键 INDEX index_name (f_name) --创建普通索引 UNIQUE unique_name (f_name) --创建唯一索引,f_name列不能重复 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
创建索引
--创建普通索引 CREATE INDEX 索引名 ON 表名 (列名); --创建唯一索引 CREATE UNIQUE INDEX 索引名 ON 表名 (列名); --创建主键索引 ALTER TABLE 表名 ADD PRIMARY KEY (列名); --创建组合索引 CREATE INDEX 索引名 ON 表名(列名1,列名2...);
删除索引
-- 删除普通索引 DROP INDEX 索引名 ON 表名; -- 删除唯一索引 DROP INDEX 索引名 ON 表名; -- 删除主键索引 ALTER TABLE 表名 MODIFY 字段名 INT; -- 先取消AUTO_INCREMENT ALTER TABLE 表名 DROP PRIMARY KEY; -- 删除主键 -- 删除组合索引 DROP INDEX 索引名 ON 表名;
查看索引
SHOW INDEX FROM 表名;
如何正确使用索引
1、不会命中索引的情况
1、like '%xx' 通配符%_开头的匹配不会走索引 SELECT age FROM tab1 WHERE username LIKE '%om'; 通配符不在开头的匹配会走索引 SELECT age FROM tab1 WHERE username LIKE 'To%'; 2、使用函数 不要在列名一侧使用函数。 SELECT age FROM tab1 WHERE LOWER(username) = 'Tom'; 3、or 尽量使用in代替or SELECT * FROM tab1 WHERE nid = 2 OR username = 'Tom'; 4、类型不一致 如果列是字符串类型,传入条件是必须用引号引起来。 SELECT * FROM tab1 WHERE username = 8848; 5、 != SELECT * FROM tab1 WHERE username != 'Tom'; 如果条件判断是主键,还是会走索引 SELECT * FROM tab1 WHERE nid != '2'; 6、 > 情况不固定啊,搞不懂 字符类型 SELECT * FROM tab1 WHERE username > 'alex'; --不走索引 SELECT * FROM tab1 WHERE username > 'tom'; --走索引 int类型 SELECT * FROM tab1 WHERE age > 2; --不走索引 如果是主键,还是会走索引 SELECT * FROM tab1 WHERE nid > 2; --走索引 7、order by 如果排序age为索引并且只select age列,才会走索引 SELECT age FROM tab1 ORDER BY age; 如果对主键排序,则还是走索引: SELECT * FROM tab1 ORDER BY nid DESC; 8、 组合索引最左前缀 如果组合索引为:(name,age) name and age -- 使用索引 name -- 使用索引 age -- 不使用索引
2、注意事项
- 避免使用select*,获取所有列应该selcet所有列名。
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char代替 varchar,char是固定长度。
- 表的字段顺序固定长度的字段优先。
- 组合索引代替多个单列索引(经常使用多个条件查询时),组合索引比多个单列索引查询快,节省资源。
- 尽量使用短索引,BLOB和TEXT类型,必须指定 length。
- 使用连接(JOIN)来代替子查询(Sub-Queries)。
- 连表时注意条件类型需一致。
- 索引散列值(重复少)不适合建索引,例:性别不适合。
3、执行计划
EXPLAIN+ 查询SQL, 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化
EXPLAIN SELECT * FROM tab1 WHERE username = 'tom';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tab1 | NULL | ref | ix_tb | ix_tb | 60 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
id,查询顺序标识,如果使用union连接气值可能为null
select_type,查询类型
table,正在访问的表名
type,查询时的访问方式
possible_keys,可能使用的索引
key,真实使用的
key_len,MySQL中使用索引字节长度
rows,mysql估计为了找到所需的行而要读取的行数 -- 只是预估值
extra,该列包含MySQL解决查询的详细信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
性能:从上到下越来越快 ALL,全表扫描,对于数据表从头到尾找一遍 select * from tb1; 如果有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;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
“Using index” 此值表示mysql将使用覆盖索引,以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 表示mysql服务器将在存储引擎检索行后再进行过滤,许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示:查询可受益于不同的索引。 “Using temporary” 表示mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 表示mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成,explain不会告诉你mysql将使用哪一种文件排序,也不会告诉你排序会在内存里还是磁盘上完成。 “Range checked for each record(index map: N)” 表示没有好用的索引,新的索引将在联接的每一行上重新估算,N是显示在possible_keys列中索引的位图,并且是冗余的。
4、limit分页
利用limit实现分页时,limit对是数据全盘扫描,在取到相应数据后停止扫描;如果数据量比较大,会有几十页、几百页,那么大页数的SQL查询性能比较慢。
-- 如果每页10条数据 SELECT * FROM tab1 LIMIT 10 OFFSET 0; --第一页 SELECT * FROM tab1 LIMIT 10 OFFSET 10; --第二页 SELECT * FROM tab1 LIMIT 10 OFFSET 20; --第三页 ... SELECT * FROM tab1 LIMIT 10 OFFSET 10(n-1); --第n页,性能会下降
可以利用上一页的最后一个值的主键,对其进行>匹配再筛选出10条;对主键进行>匹配会走索引,效率就会更高。
-- 假设主键没有删除,都是连续的 SELECT * FROM tab1 WHERE nid > 0 LIMIT 10; --第一页 SELECT * FROM tab1 WHERE nid > 10 LIMIT 10; --第二页 SELECT * FROM tab1 WHERE nid > 20 LIMIT 10; --第三页 ... SELECT * FROM tab1 WHERE nid > last_nid LIMIT 10; --第n页