delimiter $$
createprocedure p2(in m int,# in表示这个参数必须只能是传入不能被返回出去in n int,out res int# out表示这个参数可以被返回出去)beginselect tname from teacher where tid > m and tid < n;end $$
delimiter;
注意:
针对res需要先提前定义
set@res=10;# 定义 res=10select@res;# 查看call p2(1,5,@res)# 调用select@res# 查看
-注意与存储过程的区别,mysql内置的函数只能在sql语句中使用!
CREATETABLE blog (
id INTPRIMARYKEYauto_increment,
NAME CHAR(32),
sub_time datetime);INSERTINTO blog (NAME, sub_time)VALUES('第1篇','2015-03-01 11:31:21'),('第2篇','2015-03-11 16:31:21'),('第3篇','2016-07-01 10:21:31'),('第4篇','2016-07-22 09:23:21'),('第5篇','2016-07-23 10:11:11'),('第6篇','2016-07-25 11:21:31'),('第7篇','2017-03-01 15:33:21'),('第8篇','2017-03-01 17:32:21'),('第9篇','2017-03-01 18:31:21');select date_format(sub_time,'%Y-%m'),count(id)from blog groupby date_format(sub_time,'%Y-%m');
char_length
-思路:
id | NAME | sub_time | sub_month
+----+-------------------------------------+---------------------+------------------+|1| 第1篇 |2015-03-0111:31:21|2015-03|2| 第2篇 |2015-03-1116:31:21|2015-03|3| 第3篇 |2016-07-0110:21:31|2016-07|4| 第4篇 |2016-07-2209:23:21|2016-07|5| 第5篇 |2016-07-2310:11:11|2016-07|6| 第6篇 |2016-07-2511:21:31|2016-07|7| 第7篇 |2017-03-0115:33:21|2017-03|8| 第8篇 |2017-03-0117:32:21|2017-03|9| 第9篇 |2017-03-0118:31:21|2017-03selectcount(*), sub_month from blog groupby sub_month;
把年月日 时分秒的时间格式转为 年-月的形式,然后在对年-月分组
用到MySQL的内置函数
date_format()
能使用代码操作的尽量使用代码操作,不要过多的使用MySQL的函数
七、流程控制
# if条件语句delimiter//CREATEPROCEDURE proc_if ()BEGINdeclare i intdefault0;if i =1THENSELECT1;ELSEIF i =2THENSELECT2;ELSESELECT7;ENDIF;END//delimiter;-----------------------------------------------------------------------# while循环delimiter//CREATEPROCEDURE proc_while ()BEGINDECLARE num INT;SET num =0;WHILE num <10DOSELECT
num ;SET num = num +1;ENDWHILE;END//delimiter;
1. 准备表
createtable s1(
id int,
name varchar(20),
gender char(6),
email varchar(50));select id,name from t1;2. 创建存储过程,实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$createprocedure auto_insert1()BEGINdeclare i intdefault1;while(i<100000)doinsertinto s1 values(i,'jason','male',concat('jason',i,'@oldboy'));set i=i+1;endwhile;END$$ #$$结束delimiter;#重新声明分号为结束符号3. 查看存储过程
showcreateprocedure auto_insert1\G
4. 调用存储过程
call auto_insert1();
# 表没有任何索引的情况下select*from s1 where id=1000;# 避免打印带来的时间损耗selectcount(id)from s1 where id =1000000;selectcount(id)from s1 where id =1;# 给id做一个主键altertable s1 addprimarykey(id);# 速度很慢selectcount(id)from s1 where id =1;# 速度相较于未建索引之前两者差着数量级selectcount(id)from s1 where name ='jason'# 速度仍然很慢100000001000select*from s1 limit0,1000;select*from s1 limit1000,1000;select*from s1 limit2000,1000;select*from s1 limit9999000,1000;# 把100000万条数据都查一遍,然后把前99000条丢掉,要最后1000"""
范围问题
"""# 并不是加了索引,以后查询的时候按照这个字段速度就一定快 selectcount(id)from s1 where id >1;# 速度相较于id = 1慢了很多selectcount(id)from s1 where id >1and id <3;selectcount(id)from s1 where id >1and id <10000;selectcount(id)from s1 where id !=3;altertable s1 dropprimarykey;# 删除主键 单独再来研究name字段selectcount(id)from s1 where name ='jason';# 又慢了createindex idx_name on s1(name);# 给s1表的name字段创建索引selectcount(id)from s1 where name ='jason'# 仍然很慢!!!"""
再来看b+树的原理,数据需要区分度比较高,而我们这张表全是jason,根本无法区分
那这个树其实就建成了“一根棍子”
"""selectcount(id)from s1 where name ='xxx';# 这个会很快,我就是一根棍,第一个不匹配直接不需要再往下走了selectcount(id)from s1 where name like'xxx';selectcount(id)from s1 where name like'xxx%';selectcount(id)from s1 where name like'%xxx';# 慢 最左匹配特性# 区分度低的字段不能建索引dropindex idx_name on s1;# 给id字段建普通的索引createindex idx_id on s1(id);selectcount(id)from s1 where id =3;# 快了selectcount(id)from s1 where id*12=3;# 慢了 索引的字段一定不要参与计算dropindex idx_id on s1;selectcount(id)from s1 where name='jason'and gender ='male'and id =3and email ='xxx';# 针对上面这种连续多个and的操作,mysql会从左到右先找区分度比较高的索引字段,先将整体范围降下来再去比较其他条件createindex idx_name on s1(name);selectcount(id)from s1 where name='jason'and gender ='male'and id =3and email ='xxx';# 并没有加速dropindex idx_name on s1;# 给name,gender这种区分度不高的字段加上索引并不难加快查询速度createindex idx_id on s1(id);selectcount(id)from s1 where name='jason'and gender ='male'and id =3and email ='xxx';# 快了 先通过id已经讲数据快速锁定成了一条了selectcount(id)from s1 where name='jason'and gender ='male'and id >3and email ='xxx';# 慢了 基于id查出来的数据仍然很多,然后还要去比较其他字段dropindex idx_id on s1
createindex idx_email on s1(email);selectcount(id)from s1 where name='jason'and gender ='male'and id >3and email ='xxx';# 快 通过email字段一剑封喉
联合索引
selectcount(id)from s1 where name='jason'and gender ='male'and id >3and email ='xxx';# 如果上述四个字段区分度都很高,那给谁建都能加速查询# 给email加然而不用email字段selectcount(id)from s1 where name='jason'and gender ='male'and id >3;# 给name加然而不用name字段selectcount(id)from s1 where gender ='male'and id >3;# 给gender加然而不用gender字段selectcount(id)from s1 where id >3;where id=1;where name='';where name='jason'and age=18;where name='jason'and age=18and gender ='male';"""最左匹配原则"""KEY`index_1`(`name`,`gender`,`email`)# 联合索引where name ='';where name=''and gender ='';where name =and gender =and email=;KEY`index_1`(`name`)# KEY`index_1`(`name`,`gender`)KEY`index_1`(`name`,`gender`,`email`)# 带来的问题是所有的字段都建了索引然而都没有用到,还需要花费四次建立的时间createindex idx_all on s1(email,name,gender,id);# 最左匹配原则,区分度高的往左放selectcount(id)from s1 where name='jason'and gender ='male'and id >3and email ='xxx';# 速度变快