继上文
--------------------------------------------------------------------注：如果你对python感兴趣，我这有个学习Python基地，里面有很多学习资料，感兴趣的+Q群：895817687--------------------------------------------------------------------- 存储过程与事务使用的举例delimiter //create procedure p5( -- 创建存储过程p5out p_return_code tinyint) begin-- 这里表示如果捕获到异常，则执行下面set p_return_code = 1并且回滚操作declare exit handler for sqlexceptionbegin-- ERRORset p_return_code = 1;rollback;end;-- 这里表示如果捕获sql警告，则执行下面set p_return_code = 2并且回滚操作declare exit handler for sqlwarningbegin-- WARNINGSset p_return_code = 2;rollback;end;start transaction; # 开始启用事务update user set balance=900 where id =1;update user set balance=1010 where id = 2;update user set balance=1090 where id =3;commit; # 如果没有出现异常或者警告就会继续执行提交语句-- successset p_return_code = 0; -- 代表执行成功end //delimiter ;
五、函数
注意！ 函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能若要想在begin…end…中写sql，请用存储过程MySQL中提供了许多内置函数，例如：
一、数学函数ROUND(x,y)返回参数x的四舍五入的有y位小数的值RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。二、聚合函数(常用于GROUP BY从句的SELECT查询中)AVG(col)返回指定列的平均值COUNT(col)返回指定列中非NULL值的个数MIN(col)返回指定列的最小值MAX(col)返回指定列的最大值SUM(col)返回指定列的所有值之和GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果 三、字符串函数CHAR_LENGTH(str)返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。CONCAT(str1,str2,...)字符串拼接如有任何一个参数为NULL ，则返回值为 NULL。CONCAT_WS(separator,str1,str2,...)字符串拼接（自定义连接符）CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。CONV(N,from_base,to_base)进制转换例如：SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示FORMAT(X,D)将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。例如：SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'INSERT(str,pos,len,newstr)在str的指定位置插入字符串pos：要替换位置其实位置len：替换的长度newstr：新字符串特别的：如果pos超过原字符串长度，则返回原字符串如果len超过原字符串长度，则由新字符串完全替换INSTR(str,substr)返回字符串 str 中子字符串的第一个出现位置。LEFT(str,len)返回字符串str 从开始的len位置的子序列字符。LOWER(str)变小写UPPER(str)变大写REVERSE(str)返回字符串 str ，顺序和字符顺序相反。SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。mysql> SELECT SUBSTRING('Quadratically',5);-> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4);-> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6);-> 'ratica'mysql> SELECT SUBSTRING('Sakila', -3);-> 'ila'mysql> SELECT SUBSTRING('Sakila', -5, 3);-> 'aki'mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);-> 'ki'四、日期和时间函数CURDATE()或CURRENT_DATE() 返回当前的日期CURTIME()或CURRENT_TIME() 返回当前的时间DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)DAYOFMONTH(date) 返回date是一个月的第几天(1~31)DAYOFYEAR(date) 返回date是一年的第几天(1~366)DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳tsHOUR(time) 返回time的小时值(0~23)MINUTE(time) 返回time的分钟值(0~59)MONTH(date) 返回date的月份值(1~12)MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);NOW() 返回当前的日期和时间QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);WEEK(date) 返回日期date为一年中第几周(0~53)YEAR(date) 返回日期date的年份(1000~9999)重点:DATE_FORMAT(date,format) 根据format字符串格式化date值mysql> SELECT DATE_FORMAT('-10-04 22:23:00', '%W %M %Y');-> 'Sunday October 'mysql> SELECT DATE_FORMAT('-10-04 22:23:00', '%H:%i:%s');-> '22:23:00'mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',-> '%D %y %a %d %m %b %j');-> '4th 00 Thu 04 10 Oct 277'mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',-> '%H %k %I %r %T %S %w');-> '22 22 10 10:23:00 PM 22:23:00 00 6'mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');-> '1998 52'mysql> SELECT DATE_FORMAT('-06-00', '%d');-> '00'五、加密函数MD5() 计算字符串str的MD5校验和PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。六、控制流函数 CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回defaultCASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，则返回resultN，否则返回defaultIF(test,t,f) 如果test是真，返回t；否则返回fIFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1 七、控制流函数小练习#7.1、准备表/*Navicat MySQL Data TransferSource Server : localhost_3306Source Server Version : 50720Source Host : localhost:3306Source Database : studentTarget Server Type : MYSQLTarget Server Version : 50720File Encoding : 65001Date: -01-02 12:05:30*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for course-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (`c_id` int(11) NOT NULL,`c_name` varchar(255) DEFAULT NULL,`t_id` int(11) DEFAULT NULL,PRIMARY KEY (`c_id`),KEY `t_id` (`t_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of course-- ----------------------------INSERT INTO `course` VALUES ('1', 'python', '1');INSERT INTO `course` VALUES ('2', 'java', '2');INSERT INTO `course` VALUES ('3', 'linux', '3');INSERT INTO `course` VALUES ('4', 'web', '2');-- ------------------------------ Table structure for score-- ----------------------------DROP TABLE IF EXISTS `score`;CREATE TABLE `score` (`id` int(11) NOT NULL AUTO_INCREMENT,`s_id` int(10) DEFAULT NULL,`c_id` int(11) DEFAULT NULL,`num` double DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;-- ------------------------------ Records of score-- ----------------------------INSERT INTO `score` VALUES ('1', '1', '1', '79');INSERT INTO `score` VALUES ('2', '1', '2', '78');INSERT INTO `score` VALUES ('3', '1', '3', '35');INSERT INTO `score` VALUES ('4', '2', '2', '32');INSERT INTO `score` VALUES ('5', '3', '1', '66');INSERT INTO `score` VALUES ('6', '4', '2', '77');INSERT INTO `score` VALUES ('7', '4', '1', '68');INSERT INTO `score` VALUES ('8', '5', '1', '66');INSERT INTO `score` VALUES ('9', '2', '1', '69');INSERT INTO `score` VALUES ('10', '4', '4', '75');INSERT INTO `score` VALUES ('11', '5', '4', '66.7');-- ------------------------------ Table structure for student-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`s_id` varchar(20) NOT NULL,`s_name` varchar(255) DEFAULT NULL,`s_age` int(10) DEFAULT NULL,`s_sex` char(1) DEFAULT NULL,PRIMARY KEY (`s_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');INSERT INTO `student` VALUES ('3', '刘备', '35', '男');INSERT INTO `student` VALUES ('4', '关羽', '34', '男');INSERT INTO `student` VALUES ('5', '张飞', '33', '女');-- ------------------------------ Table structure for teacher-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (`t_id` int(10) NOT NULL,`t_name` varchar(50) DEFAULT NULL,PRIMARY KEY (`t_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of teacher-- ----------------------------INSERT INTO `teacher` VALUES ('1', '大王');INSERT INTO `teacher` VALUES ('2', 'alex');INSERT INTO `teacher` VALUES ('3', 'egon');INSERT INTO `teacher` VALUES ('4', 'peiqi');#7.2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]select score.c_id,course.c_name, sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'from score,course where score.c_id=course.c_id GROUP BY score.c_id;复制代码需要掌握函数：date_format复制代码--date_format的一些应用：-- 准备表和记录create table emp_info(id int primary key auto_increment,name varchar(32),hiredate date);insert into emp_info(name,hiredate) values('sgt','-09-02'),('wdc','-11-20'),('qxl','-11-12'),('ffz','-09-18'),('yl','-06-16'),('fqy','-06-05'),('cnj','-04-28'),('bvf','-09-18'),('hge','-04-22');-- 提取hire_date字段的值，按照格式化后结果年-月来分组。select date_format(hiredate,'%Y-%m'),count(id) from emp_infoGROUP BY date_format(hiredate,'%Y-%m')--结果-- date_format(hiredate,'%Y-%m') count(id)-- -093-- -112-- -042-- -062-- 按时间分组，统计同一入职时间段的员工人数
六、流程控制
流程控制实际上就是在sql语句中使用if else、while循环的操作，不同的是语法格式有所不同# if条件语句delimiter //CREATE PROCEDURE proc_if ()BEGINdeclare i int default 0;if i = 1 THENSELECT 1;ELSEIF i = 2 THENSELECT 2;ELSESELECT 7;END IF;END //delimiter ;
# while循环delimiter //CREATE PROCEDURE proc_while ()BEGINDECLARE num INT ;SET num = 0 ;WHILE num < 10 DOSELECTnum ;SET num = num + 1 ;END WHILE ;END //delimiter ;
七、索引和慢查询优化
首先我们要知道索引的存在感，数据库中的数据最终形式肯定是存在硬盘中，那么我们队数据库的操作不可厚非的都会进行IO操作
既然有IO操作，肯定会存在操作耗时问题，简单的单个或者多个数据在操作时候我们感觉不出来快慢或者耗时太少，但是现实工作中或者实际的项目的数据库中的数据可能非常的大，这样在对数据库数据操作的IO过程中就会无法忽视IO操作的耗时问题。
由于数据的IO操作导致MySQL的存储引擎有一套用于快速找到记录的一种数据结构，这个结构称之为–索引，在MySQL中也叫‘键’
1:primary key
2:unique key
3:index key
4:注意，我们前面学的外键foreign key 不是用来加速查询的，不算索引，上面的三种key前两种出了有加速查询的作用，还有额外的约束条件（primary key：非空且唯一，unique key ：唯一），而index 没有任何约束功能，仅仅具有加速查询的作用。
所以我们可以总结一下：索引就是一种数据结构，类似于新华字典的最前面那几页的查询方法，按拼音还是按部首可以根据实际情况去选择，目的只有一个：加速我们找到一个汉字的具体位置，索引就是这样，也就是说我们在数据库操作数据应该都是先查找数据，怎么用最快的速度找到数据是个重要的问题，对该数据修改仅仅只算小问题了。
本质：通过不断缩小我们想要获取数据的查找范围，来筛选出最终我们想要的结果，把漫无目的的搜寻变成顺理成章的查找，有方法有目的性的去锁定我们需要的数据。
索引有优点肯定也有缺点：
1:在表中有大量数据的时候，创建索引速度会很慢，同时索引也会占用数据空间
2:在索引创建完成后，对表的查询性能会大幅度提升，但是写的能力会降低
b+树
上图，就是b+树的结构图，只有最下面的叶子节点是存放真实数据的，根和树枝节点存的仅仅是虚拟的数据
查询次数主要由树的层数决定，也就是说层数越少查找次数越少
同时一块磁盘块的大小也是有限制的，也就是说叶子节点处的磁盘快存放的数据应该是那种特别小的单位。
要想降低树的层级高度，我们应该在一张表中建立一个主键id字段，通过该唯一的主键字段来锁定到我们想要的数据。
聚集索引（primary key）
聚集索引其实指的就是表的主键，innodb存储引擎规定一张表中必须要有主键，在建表时候会在硬盘产生2个文件，一个是表结构frm文件，一个是ibd文件，这里面就存着记录数据和索引数据。而myisam存储引擎在建表的时候会在硬盘中产生3个文件，一个表结构frm文件，一个MYD文件存记录，一个MYI索引文件专门来存索引。
辅助索引（unique，index）
查询数据的时候不可能都是用id作为查询筛选条件，也会用到其他字段名作为查询条件，name这个时候无法利用到聚集索引来加速查询，就需要给其他字段建立索引，这些索引就叫做辅助索引。
特点：叶子节点存放的是辅助索引字段对应的那条记录的主键的值，比如辅助索引是name这个字段，那么叶子节点存放的是name这个字段对应的id主键值。
-- 覆盖索引select name from user where name = 'sgt';此语句叫做覆盖索引只在辅助索引的叶子节点中就找到了我们想要的数据记录-- 非覆盖索引select age from user where name = 'sgt';此语句叫非覆盖索引，虽然查询的时候用的是name索引字段，但是要查询的是age字段。
以下是了解内容，有兴趣可以测试下索引的测试
#1. 准备表create table s1(id int,name varchar(20),gender char(6),email varchar(50));#2. 创建存储过程，实现批量插入记录delimiter $$ #声明存储过程的结束符号为$$create procedure auto_insert1()BEGINdeclare i int default 1;while(i<3000000)doinsert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));set i=i+1;end while;END$$ #$$结束delimiter ; #重新声明 分号为结束符号#3. 查看存储过程show create procedure auto_insert1\G #4. 调用存储过程call auto_insert1();
# 表没有任何索引的情况下select * from s1 where id=30000;# 避免打印带来的时间损耗select count(id) from s1 where id = 30000;select count(id) from s1 where id = 1;# 给id做一个主键alter table s1 add primary key(id); # 速度很慢select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级select count(id) from s1 where name = 'jason' # 速度仍然很慢"""范围问题"""# 并不是加了索引，以后查询的时候按照这个字段速度就一定快 select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多select count(id) from s1 where id >1 and id < 3;select count(id) from s1 where id > 1 and id < 10000;select count(id) from s1 where id != 3;alter table s1 drop primary key; # 删除主键 单独再来研究name字段select count(id) from s1 where name = 'jason'; # 又慢了create index idx_name on s1(name); # 给s1表的name字段创建索引select count(id) from s1 where name = 'jason' # 仍然很慢！！！"""再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分那这个树其实就建成了“一根棍子”"""select count(id) from s1 where name = 'xxx'; # 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了select count(id) from s1 where name like 'xxx';select count(id) from s1 where name like 'xxx%';select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性# 区分度低的字段不能建索引drop index idx_name on s1;# 给id字段建普通的索引create index idx_id on s1(id);select count(id) from s1 where id = 3; # 快了select count(id) from s1 where id*12 = 3; # 慢了 索引的字段一定不要参与计算drop index idx_id on s1;select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件create index idx_name on s1(name);select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并没有加速drop index idx_name on s1;# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度create index idx_id on s1(id);select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了 先通过id已经讲数据快速锁定成了一条了select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了 基于id查出来的数据仍然很多，然后还要去比较其他字段drop index idx_id on s1create index idx_email on s1(email);select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快 通过email字段一剑封喉
联合索引
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 如果上述四个字段区分度都很高，那给谁建都能加速查询# 给email加然而不用email字段select count(id) from s1 where name='jason' and gender = 'male' and id > 3; # 给name加然而不用name字段select count(id) from s1 where gender = 'male' and id > 3; # 给gender加然而不用gender字段select count(id) from s1 where id > 3; # 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快
慢查询日志
设定一个时间检测所有超出改时间的sql语句，然后针对性的进行优化！
