视图视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】, 用户使用时只需使用【名称】即可获取结果集,并可以将其当作表来使用。 1、创建视图
View Code
2、删除视图
View Code
3、修改视图
View Code
4、使用视图 使用视图时,将其当作表进行操作即可,由于视图是虚拟表,所以无法使用其对真实表进行创建、更新和删除操作,仅能做查询用。 select * from v1 存储过程存储过程是一个SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行。 1、创建存储过程 -- 创建存储过程 drop procedure if exists proc_p1; delimiter $$ -- 指明不把 ; 当结束 用$$ create procedure proc_p1() begin select * from man; end $$ delimiter ; -- 执行存储过程 call proc_p1() 对于存储过程,可以接收参数,其参数有三类:
类型一:
View Code
类型二:
View Code
注: declare 用来定义局部变量
@ 用来定义会话变量
2、删除存储过程 drop procedure proc_name; 3、执行存储过程 -- 无参数 call proc_name() -- 有参数,全in call proc_name(1,2) -- 有参数,有in,out,inout set @t1=0; set @t2=3; call proc_name(1,2,@t1,@t2) 补充: 该语句在数据库中进行查询,并将得到的结果赋值给变量。 declare d2 int default 0 ; select man_id into d2 from man_to_women where nid = 4 ; select name from man where nid = d2; pymysql 执行存储过程
View Code
获取返回值固定的格式:
触发器对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时,可以使用触发器, 触发器用于定制用户对表的行进行【增/删/改】前后的行为。 1、创建基本语法
View Code
例子1: 插入前触发,并且获取 待插入数据
View Code
例子2: 删除前触发,并且获取 待删除数据
View Code
特别的:NEW表示即将插入的数据行,OLD表示即将删除的数据行。更新时,old ,new都可以同时使用! 2、删除触发器 DROP TRIGGER tri_after_insert_tb1; 3、使用触发器 触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的。 insert into tb1(num) values(666) 函数MySQL中提供了许多内置函数,例如:
View Code
1、自定义函数 函数 , 不能返回结果集!
delimiter $$ drop function if exists f1 $$ create function f1( i1 int, i2 int) returns int BEGIN declare num int; set num = i1 + i2; return(num); END $$ delimiter ; 2、删除函数 drop function func_name; 3、执行函数 # 获取返回值 declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; # 在查询中使用 select f1(11,22) ,name from tb2; select f1(11,nid) ,name from tb2; 事务事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。 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; #开始事务 UPDATE userinfo set asset = asset - 5 where name ='alex'; UPDATE userinfo set asset = asset + 5 where name ='reic'; COMMIT; -- SUCCESS set p_return_code = 0; END $$ delimiter ; pymysql 默认开启了事务 cursor.execute("UPDATE userinfo set asset = asset - 5 where name ='alex'") cursor.execute("UPDATE uerinfo set asset = asset + 5 where name ='eric'") 第2条 sql语句中执行报错 , 数据库中的 asset 并没有发生改变 其他1、条件语句
View Code
2、循环语句 while 循环
View Code
repeat 循环
View Code
loop 循环:
View Code
3、动态执行SQL语句 方式1: delimiter $$ DROP PROCEDURE IF EXISTS proc_sql $$ CREATE PROCEDURE proc_sql () BEGIN declare p1 int; set p1 = 2; set @p1 = p1; PREPARE prod FROM 'select * from man where nid > ?'; EXECUTE prod USING @p1; DEALLOCATE prepare prod; END $$ delimiter ; call proc_sql(); 方式2: delimiter $$ DROP PROCEDURE IF EXISTS proc_sql $$ CREATE PROCEDURE proc_sql ( in strSQL varchar(128), in nid int ) BEGIN set @p1 = nid; set @strsql = strSQL; PREPARE prod FROM @strsql; -- 带一个@用户变量(用户退出才消失), 两个@@全局变量 EXECUTE prod USING @p1; -- 执行sql 替换占位符 DEALLOCATE prepare prod; -- 删除编译sql语句 END $$ delimiter ; call proc_sql('select * from man where nid > ? ' , 2); pymyql执行方法: cursor.callproc('proc_sql',('select * from man where nid > ? ' , 2)) 索引索引,是数据库中专门用于帮助用户快速查询数据的一种数据结构。 类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即可。 索引类型: B-tree索引: 注:名叫btree索引,大的方面看,都用的是平衡树,但具体的实现上,各引擎稍有不同,比如,严格的说,NDB引擎,使用的是T-tree,但是在MyISAM,Innodb中,默认的使用的是B-tree索引 但我们抽象一下---B-tree系统,可以理解成为“排好序的快速查找结构” hash索引 : 在memory表中,默认的是hash索引,hash的理论查询时间复杂度为O(1) 那为啥hash的查找如此的高效,为什么都不用hash索引? 答:1.hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,以主键id为例,那么随着id的增长,id对应的行,在磁盘上随机的放置。 2.无法对范围查询进行优化。 3.无法利用前缀索引 比如 在btree中, field列的值“hellopworld”,并加索引查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引) 因 为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机 4.排序无法进行优化。 5.必须得回行。就是说,通过索引拿到的数据位置,必须回到表中取出数据。 MySQL中常见索引有:
索引合并:使用多个单列索引组合搜索 覆盖索引:select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖 1、普通索引 普通索引仅有一个功能:加速查询 创建索引: create index index_name on table_name(column_name) 删除索引: drop index index_name on table_name; 查看索引: show index from table_name;
创建表 + 索引
注意:对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。 create index ix_extra on in1(extra(32)); 2、唯一索引 唯一索引有两个功能:加速查询 和 唯一约束(可含null) 创建索引: create unique index 索引名 on 表名(列名) 删除索引: drop index 索引名 on 表名
创建表 + 索引
3、主键索引 主键有两个功能:加速查询 和 唯一约束(不可含null) 创建索引: alter table 表名 add primary key(列名); 删除索引: alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key; 4、组合索引 组合索引是将n个列组合成一个索引 其应用场景为:频繁的同时使用n列来进行查询,如:where name = 'alex' and email = 666。
创建索引: create index ix_name_email on tb1(name,email); create unique index ix_name_email on tb1(name,email);
创建表
当搜索语句使用场合 多数为: select * from tb1 where A.. and B.. select * from tb1 where A.. 使用 组合索引 效率更高
5、覆盖索引 select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖 select * from tb1 where nid = 1; select nid from tb1 where nid = 1; 6、索引合并 使用多个单列索引组合搜索 SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3; 7、前缀索引
相关命令: - 查看表结构 desc 表名 - 查看生成表的SQL show create table 表名 - 查看索引 show index from 表名 - 查看执行时间 set profiling = 1; SQL... show profiles; 正确使用索引 数据库表中添加索引后确实会让查询速度起飞,但前提必须是正确的使用索引来查询,如果以错误的方式使用,则即使建立索引也会不奏效。 即使建立索引,索引也不会生效: - like '%xx' select * from tb1 where name like '%cn'; -- 不使用索引 select * from tb1 where name like 'cn%'; -- 使用索引 - 使用函数 select * from tb1 where reverse(name) = 'alex'; -- 不使用索引 select * from tb1 where name = reverse('alex'); -- 使用索引 - or select * 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 by select email from tb1 order by name desc; 当根据 索引name 排序时候,选择的 映射email 如果不是索引,则不走索引 特别的:如果对主键排序,则还是走索引: select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为:(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引 其他注意事项 - 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引(经常使用多个条件查询时) - 尽量使用短索引 create index 名字 on 表(列(8)) 前8列建索引 text 建立索引需要指定长度 - 使用连接(JOIN)来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值(重复少)不适合建索引,例:性别不适合 limit分页 无论是否有索引,limit分页是一个值得关注的问题 1000 990 980 970 960 | | | | | 990 980 970 960 950 1 2 3 4 5 现在在第1页,要跳转去第5页: 最优方法: ( 每页10条数据 ) 1.第1页去第5页,相差4页, 差了4-1 *10 = 30 条数据! select nid from tb1 where nid < 990 order by nid desc limit 30 2.取得第5页开始的nid为960的数据: select nid from (...) as A order by nid desc limit 1 3.然后往下取10条即可: select * from tb1 where nid < 动态nid order by nid desc limit 10 综合: 动态nid => select nid from (select nid from tb1 where nid < 990 order by nid desc limit 30 ) as A order by nid desc limit 1 select * from tb1 where nid < 动态nid order by nid desc limit 10 执行计划 explain + 查询SQL - 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化 mysql> explain select * from tb2;
详细
更多参见: http://www.cnblogs.com/xiaoboluo768/p/5400990.html http://dev./doc/refman/5.7/en/explain-output.html#jointype_system 慢日志查询 a、配置MySQL自动记录慢日志 slow_query_log = OFF 是否开启慢日志记录 long_query_time = 2 时间限制,超过此时间,则记录 slow_query_log_file = /usr/slow.log 日志文件 log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录 注:查看当前配置信息: show variables like '%query%' 修改当前配置: set global 变量名 = 值 b、查看MySQL慢日志
|
|