--------------------------------------------------------------------注：如果你對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 ;
一、數學函數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('2009-10-04 22:23:00','%W %M %Y');->'Sunday October 2009'mysql> SELECT DATE_FORMAT('2007-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('2006-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_3306
Source Server Version :50720
Source Host : localhost:3306
Source Database : studentTarget Server Type : MYSQL
Target Server Version :50720
File Encoding :65001Date:2018-01-0212: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 85and100 THEN 1 ELSE 0 END)as'[100-85]',sum(CASE WHEN num BETWEEN 70and85 THEN 1 ELSE 0 END)as'[85-70]',sum(CASE WHEN num BETWEEN 60and70 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(idint primary key auto_increment,name varchar(32),hiredate date);insert into emp_info(name,hiredate) values('sgt','2009-09-02'),('wdc','2010-11-20'),('qxl','2010-11-12'),('ffz','2009-09-18'),('yl','2014-06-16'),('fqy','2014-06-05'),('cnj','2012-04-28'),('bvf','2009-09-18'),('hge','2012-04-22');-- 提取hire_date字段的值，按照格式化后結果年-月來分組。
select date_format(hiredate,'%Y-%m'),count(id)from emp_info
GROUP BY date_format(hiredate,'%Y-%m')--結果
-- date_format(hiredate,'%Y-%m') count(id)--2009-093--2010-112--2012-042--2014-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 ;
-- 覆蓋索引
select name from user where name ='sgt';
此語句叫做覆蓋索引
只在輔助索引的葉子節點中就找到了我們想要的數據記錄-- 非覆蓋索引
select age from user where name ='sgt';
此語句叫非覆蓋索引，雖然查詢的時候用的是name索引字段，但是要查詢的是age字段。
以下是了解內容，有興趣可以測試下
索引的測試
#1. 準備表
create table s1(idint,
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>1andid<3;
select count(id)from s1 where id>1andid<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'andid=3and email ='xxx';# 針對上面這種連續多個and的操作，mysql會從左到右先找區分度比較高的索引字段，先將整體范圍降下來再去比較其他條件
create index idx_name on s1(name);
select count(id)from s1 where name='jason'and gender ='male'andid=3and 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'andid=3and email ='xxx';# 快了 先通過id已經講數據快速鎖定成了一條了
select count(id)from s1 where name='jason'and gender ='male'andid>3and 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'andid>3and email ='xxx';# 快 通過email字段一劍封喉
聯合索引
select count(id)from s1 where name='jason'and gender ='male'andid>3and email ='xxx';# 如果上述四個字段區分度都很高，那給誰建都能加速查詢# 給email加然而不用email字段
select count(id)from s1 where name='jason'and gender ='male'andid>3;# 給name加然而不用name字段
select count(id)from s1 where gender ='male'andid>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'andid>3and email ='xxx';# 速度變快
