mysql內(nèi)置功能：
1.視圖
2.觸發(fā)器
3.存儲(chǔ)過(guò)程
4.事務(wù)
5.函數(shù)

一、視圖
介紹：
視圖是一個(gè)虛擬表（非真實(shí)存在），其本質(zhì)是【根據(jù)SQL語(yǔ)句獲取動(dòng)態(tài)的數(shù)據(jù)集，并為其命名】，
用戶(hù)使用時(shí)只需使用【名稱(chēng)】即可獲取結(jié)果集，可以將該結(jié)果集當(dāng)做表來(lái)使用。
使用視圖我們可以把查詢(xún)過(guò)程中的臨時(shí)表摘出來(lái)，用視圖去實(shí)現(xiàn)，
這樣以后再想操作該臨時(shí)表的數(shù)據(jù)時(shí)就無(wú)需重寫(xiě)復(fù)雜的sql了，直接去視圖中查找即可，
但視圖有明顯地效率問(wèn)題，并且視圖是存放在數(shù)據(jù)庫(kù)中的，
如果我們程序中使用的sql過(guò)分依賴(lài)數(shù)據(jù)庫(kù)中的視圖，即強(qiáng)耦合，那就意味著擴(kuò)展sql極為不便，
因此并不推薦使用；

把sql語(yǔ)句查詢(xún)出來(lái)得虛擬表保存下來(lái)；
視圖：硬盤(pán)只存結(jié)構(gòu)，不存數(shù)據(jù)，因?yàn)閿?shù)據(jù)本身來(lái)源于其他表
減少代碼冗余，但是不建議使用，數(shù)據(jù)庫(kù)擴(kuò)展，就影響視圖，就得修改視圖。

視圖得目的：取代復(fù)雜得sql語(yǔ)句，針對(duì)單表做視圖，沒(méi)有意義；
視圖是用來(lái)查得，多張表，視圖數(shù)據(jù)不應(yīng)該被改，
視圖是方便來(lái)查得，不是用來(lái)改得！
舉例： #兩張有關(guān)系的表 mysql> select * from course;+-----+--------+------------+| cid | cname | teacher_id |+-----+--------+------------+| 1 | 生物 | 1 || 2 | 物理 | 2 || 3 | 體育 | 3 || 4 | 美術(shù) | 2 |+-----+--------+------------+rows in set (0.00 sec)mysql> select * from teacher;+-----+-----------------+| tid | tname |+-----+-----------------+| 1 | 張磊老師 || 2 | 李平老師 || 3 | 劉海燕老師 || 4 | 朱云海老師 || 5 | 李杰老師 |+-----+-----------------+rows in set (0.00 sec)#查詢(xún)李平老師教授的課程名mysql> select cname from course where teacher_id = (select tid from teacher where tname='李平老師');+--------+| cname |+--------+| 物理 || 美術(shù) |+--------+rows in set (0.00 sec)#子查詢(xún)出臨時(shí)表，作為teacher_id等判斷依據(jù)select tid from teacher where tname='李平老師'
1、創(chuàng)建視圖 #語(yǔ)法：CREATE VIEW 視圖名稱(chēng) AS SQL語(yǔ)句create view teacher_view as select tid from teacher where tname='李平老師';#于是查詢(xún)李平老師教授的課程名的sql可以改寫(xiě)為mysql> select cname from course where teacher_id = (select tid from teacher_view);+--------+| cname |+--------+| 物理 || 美術(shù) |+--------+rows in set (0.00 sec)#！！！注意注意注意：#1. 使用視圖以后就無(wú)需每次都重寫(xiě)子查詢(xún)的sql，但是這么效率并不高，還不如我們寫(xiě)子查詢(xún)的效率高#2. 而且有一個(gè)致命的問(wèn)題：視圖是存放到數(shù)據(jù)庫(kù)里的，如果我們程序中的sql過(guò)分依賴(lài)于數(shù)據(jù)庫(kù)中存放的視圖， 那么意味著，一旦sql需要修改且涉及到視圖的部分，則必須去數(shù)據(jù)庫(kù)中進(jìn)行修改，而通常在公司中數(shù)據(jù)庫(kù)有專(zhuān)門(mén)的DBA負(fù)責(zé)，你要想完成修改，必須付出大量的溝通成本DBA可能才會(huì)幫你完成修改，極其地不方便 創(chuàng)建視圖

?

2、使用視圖 #修改視圖，原始表也跟著改mysql> select * from course;+-----+--------+------------+| cid | cname | teacher_id |+-----+--------+------------+| 1 | 生物 | 1 || 2 | 物理 | 2 || 3 | 體育 | 3 || 4 | 美術(shù) | 2 |+-----+--------+------------+rows in set (0.00 sec)mysql> create view course_view as select * from course; #創(chuàng)建表course的視圖Query OK, 0 rows affected (0.52 sec)mysql> select * from course_view;+-----+--------+------------+| cid | cname | teacher_id |+-----+--------+------------+| 1 | 生物 | 1 || 2 | 物理 | 2 || 3 | 體育 | 3 || 4 | 美術(shù) | 2 |+-----+--------+------------+rows in set (0.00 sec)mysql> update course_view set cname='xxx'; #更新視圖中的數(shù)據(jù)Query OK, 4 rows affected (0.04 sec)Rows matched: 4 Changed: 4 Warnings: 0mysql> insert into course_view values(5,'yyy',2); #往視圖中插入數(shù)據(jù)Query OK, 1 row affected (0.03 sec)mysql> select * from course; #發(fā)現(xiàn)原始表的記錄也跟著修改了+-----+-------+------------+| cid | cname | teacher_id |+-----+-------+------------+| 1 | xxx | 1 || 2 | xxx | 2 || 3 | xxx | 3 || 4 | xxx | 2 || 5 | yyy | 2 |+-----+-------+------------+rows in set (0.00 sec) 使用視圖 注：我們不應(yīng)該修改視圖中的記錄，而且在涉及多個(gè)表的情況下是根本無(wú)法修改視圖中的記錄的，如下圖

?

3、修改視圖 #語(yǔ)法：ALTER VIEW 視圖名稱(chēng) AS SQL語(yǔ)句mysql> alter view teacher_view as select * from course where cid>3;Query OK, 0 rows affected (0.04 sec)mysql> select * from teacher_view;+-----+-------+------------+| cid | cname | teacher_id |+-----+-------+------------+| 4 | xxx | 2 || 5 | yyy | 2 |+-----+-------+------------+rows in set (0.00 sec) 修改視圖 4、刪除視圖
語(yǔ)法：DROP VIEW 視圖名稱(chēng)
DROP VIEW teacher_view

二、觸發(fā)器
使用觸發(fā)器可以定制用戶(hù)對(duì)表進(jìn)行【增、刪、改】操作時(shí)前后的行為，注意：沒(méi)有查詢(xún)。
這是mysql內(nèi)部實(shí)現(xiàn)得觸發(fā)器得功能；
用哪一個(gè)觸發(fā)器好：mysql自帶得，還是應(yīng)用程序?qū)崿F(xiàn)類(lèi)似觸發(fā)器得功能好？
真實(shí)環(huán)境：
應(yīng)用程序觸發(fā)好，擴(kuò)展方便！！

1.創(chuàng)建觸發(fā)器 # 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROWBEGIN...END# 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROWBEGIN...END# 刪除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROWBEGIN...END# 刪除后 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROWBEGIN...END# 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROWBEGIN...END# 更新后 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROWBEGIN...END#準(zhǔn)備表 CREATE TABLE cmd (id INT PRIMARY KEY auto_increment,USER CHAR (32),priv CHAR (10),cmd CHAR (64),sub_time datetime, #提交時(shí)間success enum ('yes', 'no') #0代表執(zhí)行失敗 );CREATE TABLE errlog (id INT PRIMARY KEY auto_increment,err_cmd CHAR (64),err_time datetime);#創(chuàng)建觸發(fā)器delimiter //CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROWBEGINIF NEW.success = 'no' THEN #等值判斷只有一個(gè)等號(hào)INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必須加分號(hào)END IF ; #必須加分號(hào)END//delimiter ;#往表cmd中插入記錄，觸發(fā)觸發(fā)器，根據(jù)IF的條件決定是否插入錯(cuò)誤日志 INSERT INTO cmd (USER,priv,cmd,sub_time,success)VALUES('egon','0755','ls -l /etc',NOW(),'yes'),('egon','0755','cat /etc/passwd',NOW(),'no'),('egon','0755','useradd xxx',NOW(),'no'),('egon','0755','ps aux',NOW(),'yes');#查詢(xún)錯(cuò)誤日志，發(fā)現(xiàn)有兩條mysql> select * from errlog;+----+-----------------+---------------------+| id | err_cmd | err_time |+----+-----------------+---------------------+| 1 | cat /etc/passwd | 2017-09-14 22:18:48 || 2 | useradd xxx | 2017-09-14 22:18:48 |+----+-----------------+---------------------+rows in set (0.00 sec)插入后觸發(fā)觸發(fā)器注：特別的：NEW表示即將插入的數(shù)據(jù)行，OLD表示即將刪除的數(shù)據(jù)行。 創(chuàng)建觸發(fā)器 2.使用觸發(fā)器
觸發(fā)器無(wú)法由用戶(hù)直接調(diào)用，而知由于對(duì)表的【增/刪/改】操作被動(dòng)引發(fā)的。

　　　　　查看觸發(fā)器：show triggers\G;

3.刪除觸發(fā)器
drop trigger tri_after_insert_cmd; 三、存儲(chǔ)過(guò)程
1.存儲(chǔ)過(guò)程介紹：
存儲(chǔ)過(guò)程包含了一系列可執(zhí)行的sql語(yǔ)句，存儲(chǔ)過(guò)程存放于MySQL中，通過(guò)調(diào)用它的名字可以執(zhí)行其內(nèi)部的一堆sql。
優(yōu)點(diǎn)：
1. 用于替代程序?qū)懙腟QL語(yǔ)句，實(shí)現(xiàn)程序與sql解耦
2. 基于網(wǎng)絡(luò)傳輸，傳別名的數(shù)據(jù)量小，而直接傳sql數(shù)據(jù)量大
缺點(diǎn)：
1. 程序員擴(kuò)展功能不方便
補(bǔ)充：
程序與數(shù)據(jù)庫(kù)結(jié)合使用的三種方式
#方式一：
MySQL：存儲(chǔ)過(guò)程
程序：調(diào)用存儲(chǔ)過(guò)程
評(píng)論：
應(yīng)用程序與數(shù)據(jù)庫(kù)解開(kāi)耦合，效率高，發(fā)存儲(chǔ)過(guò)程名就可以了，
但是實(shí)際情況：擴(kuò)展性低，人為因素造成得！

#方式二：
MySQL：
程序：純SQL語(yǔ)句
評(píng)論：
通常用這種方式，運(yùn)行效率低一些，但擴(kuò)展性方便。

#方式三：
MySQL:
程序：類(lèi)和對(duì)象，即ORM（本質(zhì)還是純SQL語(yǔ)句），Django框架里面有ORM框架，本質(zhì)：應(yīng)用程序控制sql
評(píng)論：
運(yùn)行效率低，相比方式二，因?yàn)橐D(zhuǎn)換，發(fā)送。
開(kāi)發(fā)效率高，面向?qū)ο?#xff0c;可維護(hù)性高。 2.創(chuàng)建簡(jiǎn)單存儲(chǔ)過(guò)程（無(wú)參） create table blog(id int primary key auto_increment,name varchar(16),sub_time datetime);insert into blog(name,sub_time) values('alice',now()); delimiter //create procedure p1()beginselect * from blog;insert into blog(name,sub_time) values('xxx',now());end //delimiter ;#在mysql中調(diào)用 call p1(); #在python中基于pymysql調(diào)用cursor.callproc('p1') print(cursor.fetchall())conn.commit() # 一定要有，否則在增刪改時(shí)，不能更新到數(shù)據(jù)庫(kù)； 創(chuàng)建簡(jiǎn)單存儲(chǔ)過(guò)程（無(wú)參） 3.創(chuàng)建存儲(chǔ)過(guò)程（有參）
對(duì)于存儲(chǔ)過(guò)程，可以接收參數(shù)，其參數(shù)有三類(lèi)：
in 僅用于傳入?yún)?shù)用
out 僅用于返回值用
inout 既可以傳入又可以當(dāng)作返回值 1.in:傳入?yún)?shù) delimiter //create procedure p2(in n1 int,in n2 int)beginselect * from blog where id between n1 and n2;end //delimiter ;#在mysql中調(diào)用call p2(2,15);#在python中基于pymysql調(diào)用 cursor.callproc('p2',(2,15))print(cursor.fetchall())conn.commit() # 一定要有，否則在增刪改時(shí)，不能更新到數(shù)據(jù)庫(kù)； 2.out:返回值 delimiter //create procedure p3(in n1 int,out res int)beginselect * from blog where id > n1; set res = 1;end //delimiter ;#在mysql中調(diào)用set @res=0; #0代表假（執(zhí)行失敗），1代表真（執(zhí)行成功）call p3(3,@res);select @res; #在python中基于pymysql調(diào)用cursor.callproc('p3',(3,0)) #0相當(dāng)于set @res=0print(cursor.fetchall()) #查詢(xún)select的查詢(xún)結(jié)果 cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一個(gè)參數(shù)，@p3_1代表第二個(gè)參數(shù)，即返回值print(cursor.fetchall()) #((3, 1),) 3.inout:既可以傳入又可以返回 delimiter //create procedure p4(inout n1 int)beginselect * from blog where id > n1;set n1 = 1;end // delimiter ;#在mysql中調(diào)用set @x=3;call p4(@x);select @x;#在python中基于pymysql調(diào)用cursor.callproc('p4',(3,)) # 逗號(hào)（，）一定要寫(xiě)，否則會(huì)報(bào)錯(cuò)，是元祖print(cursor.fetchall()) #查詢(xún)select的查詢(xún)結(jié)果 cursor.execute('select @_p4_0;') print(cursor.fetchall()) 創(chuàng)建存儲(chǔ)過(guò)程（有參） 4.執(zhí)行存儲(chǔ)過(guò)程 1.在MySQL中執(zhí)行存儲(chǔ)過(guò)程-- 無(wú)參數(shù)call proc_name()-- 有參數(shù)，全incall proc_name(1,2)-- 有參數(shù)，有in，out，inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2)2.在python中基于pymysql執(zhí)行存儲(chǔ)過(guò)程#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 執(zhí)行存儲(chǔ)過(guò)程cursor.callproc('p1', args=(1, 22, 3, 4))# 獲取執(zhí)行完存儲(chǔ)的參數(shù)cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")result = cursor.fetchall()conn.commit()cursor.close()conn.close()print(result) 執(zhí)行存儲(chǔ)過(guò)程 5.刪除存儲(chǔ)過(guò)程
drop procedure proc_name;

四、事務(wù)
事務(wù)用于將某些操作的多個(gè)SQL作為原子性操作，一旦有某一個(gè)出現(xiàn)錯(cuò)誤，即可回滾到原來(lái)的狀態(tài)，從而保證數(shù)據(jù)庫(kù)數(shù)據(jù)完整性。
一堆sql語(yǔ)句：要么同時(shí)執(zhí)行成功，要么同時(shí)失敗 原子性
場(chǎng)景: 轉(zhuǎn)賬； create table user(id int primary key auto_increment,name char(32),balance int);insert into user(name,balance) values('wsb',1000),('egon',1000),('ysb',1000);# 原子操作 start transaction; # 開(kāi)啟事務(wù)監(jiān)測(cè),sql語(yǔ)句得執(zhí)行，執(zhí)行一條sql失敗就回滾，有異常處理，監(jiān)測(cè)到異常就回滾；update user set balance = 900 where name = 'wsb'; #買(mǎi)支付100元 update user set balance = 1010 where name = 'egon'; #中介拿走10元update user set balance = 1090 where name = 'ysb'; #賣(mài)家拿到90元 commit;# 出現(xiàn)異常，回滾到初始狀態(tài) start transaction; update user set balance = 900 where name = 'wsb'; #買(mǎi)支付100元update user set balance = 1010 where name = 'egon'; #中介拿走10元uppdate user set balance = 1090 where name = 'ysb'; #賣(mài)家拿到90元,出現(xiàn)異常沒(méi)有拿到 rollback;commit;mysql> select * from user;+----+------+---------+| id | name | balance |+----+------+---------+| 1 | wsb | 1000 || 2 | egon | 1000 || 3 | ysb | 1000 |+----+------+---------+

?

介紹： delimiter //create procedure p4(out status int)BEGIN1. 聲明如果出現(xiàn)異常則執(zhí)行{set status = 1;rollback;}開(kāi)始事務(wù)-- 由秦兵賬戶(hù)減去100-- 方少偉賬戶(hù)加90-- 張根賬戶(hù)加10commit;結(jié)束set status = 2;END //delimiter ;

?

實(shí)現(xiàn)： delimiter //create procedure p6(out p_return_code int)begindeclare exit handler for sqlexceptionbegin-- errorset p_return_code = 1;rollback;end;declare exit handler for sqlwarningbegin-- warningset p_return_code = 2;rollback;end;start transaction;delete from tb1; # 執(zhí)行失敗 返回 1 insert into blog(name,sub_time) values('yyy',now());commit;-- successset p_return_code = 0; #0代表執(zhí)行成功 end //delimiter ;# 在mysql中調(diào)用存儲(chǔ)過(guò)程set @res = 123;call p5(@res)select @res;# 在python中基于pymysql調(diào)用存儲(chǔ)過(guò)程cursor.callproc('p5',(123,))print(cursor.fetchall()) # 查詢(xún)select得查詢(xún)結(jié)果 cursor.execute('select @_p5_0;')print(cursor.fetchall()) 五、函數(shù)
官方：
https://dev.mysql.com/doc/refman/5.7/en/functions.html
注： 函數(shù)，流程控制，事務(wù)，觸發(fā)器，視圖，都可以封裝到存儲(chǔ)過(guò)程中；
函數(shù)用在sql語(yǔ)句中，函數(shù)和存儲(chǔ)過(guò)程要分開(kāi)；
存儲(chǔ)過(guò)程不叫函數(shù)，存儲(chǔ)過(guò)程是把一堆sql功能封裝到一起；
MySQL中提供了許多內(nèi)置函數(shù)，例如：
1、數(shù)學(xué)函數(shù)
ROUND(x,y)
返回參數(shù)x的四舍五入的有y位小數(shù)的值

RAND()
返回０到１內(nèi)的隨機(jī)值,可以通過(guò)提供一個(gè)參數(shù)(種子)使RAND()隨機(jī)數(shù)生成器生成一個(gè)指定的值。

2、聚合函數(shù)(常用于GROUP BY從句的SELECT查詢(xún)中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的個(gè)數(shù)
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由屬于一組的列值連接組合而成的結(jié)果

3、字符串函數(shù)

CHAR_LENGTH(str)
返回值為字符串str 的長(zhǎng)度，長(zhǎng)度的單位為字符。一個(gè)多字節(jié)字符算作一個(gè)單字符。
CONCAT(str1,str2,...)
字符串拼接
如有任何一個(gè)參數(shù)為NULL ，則返回值為 NULL。
CONCAT_WS(separator,str1,str2,...)
字符串拼接（自定義連接符）
CONCAT_WS()不會(huì)忽略任何空字符串。 (然而會(huì)忽略所有的 NULL）。

CONV(N,from_base,to_base)
進(jìn)制轉(zhuǎn)換
例如：
SELECT CONV('a',16,2); 表示將 a 由16進(jìn)制轉(zhuǎn)換為2進(jìn)制字符串表示

FORMAT(X,D)
將數(shù)字X 的格式寫(xiě)為'#,###,###.##',以四舍五入的方式保留小數(shù)點(diǎn)后 D 位， 并將結(jié)果以字符串的形式返回。若 D 為 0, 則返回結(jié)果不帶有小數(shù)點(diǎn)，或不含小數(shù)部分。
例如：
SELECT FORMAT(12332.1,4); 結(jié)果為： '12,332.1000'
INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos：要替換位置其實(shí)位置
len：替換的長(zhǎng)度
newstr：新字符串
特別的：
如果pos超過(guò)原字符串長(zhǎng)度，則返回原字符串
如果len超過(guò)原字符串長(zhǎng)度，則由新字符串完全替換
INSTR(str,substr)
返回字符串 str 中子字符串的第一個(gè)出現(xiàn)位置。

LEFT(str,len)
返回字符串str 從開(kāi)始的len位置的子序列字符。

LOWER(str)
變小寫(xiě)

UPPER(str)
變大寫(xiě)

REVERSE(str)
返回字符串 str ，順序和字符順序相反。

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不帶有l(wèi)en 參數(shù)的格式從字符串str返回一個(gè)子字符串，起始于位置 pos。帶有l(wèi)en參數(shù)的格式從字符串str返回一個(gè)長(zhǎng)度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式為標(biāo)準(zhǔn) SQL 語(yǔ)法。也可能對(duì)pos使用一個(gè)負(fù)值。假若這樣，則子字符串的位置起始于字符串結(jié)尾的pos 字符，而不是字符串的開(kāi)頭位置。在以下格式的函數(shù)中可以對(duì)pos 使用一個(gè)負(fù)值。

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'

4、日期和時(shí)間函數(shù)
CURDATE()或CURRENT_DATE() 返回當(dāng)前的日期
CURTIME()或CURRENT_TIME() 返回當(dāng)前的時(shí)間
DAYOFWEEK(date) 返回date所代表的一星期中的第幾天(1~7)
DAYOFMONTH(date) 返回date是一個(gè)月的第幾天(1~31)
DAYOFYEAR(date) 返回date是一年的第幾天(1~366)
DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根據(jù)指定的fmt格式，格式化UNIX時(shí)間戳ts
HOUR(time) 返回time的小時(shí)值(0~23)
MINUTE(time) 返回time的分鐘值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回當(dāng)前的日期和時(shí)間
QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date) 返回日期date為一年中第幾周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)

重點(diǎn):
DATE_FORMAT(date,format) 根據(jù)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'

5、加密函數(shù)
MD5()
計(jì)算字符串str的MD5校驗(yàn)和
PASSWORD(str)
返回字符串str的加密版本，這個(gè)加密過(guò)程是不可逆轉(zhuǎn)的，和UNIX密碼加密過(guò)程使用不同的算法。

6、控制流函數(shù)
CASE WHEN[test1] THEN [result1]...ELSE [default] END
如果testN是真，則返回resultN，否則返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END
如果test和valN相等，則返回resultN，否則返回default

IF(test,t,f)
如果test是真，返回t；否則返回f

IFNULL(arg1,arg2)
如果arg1不是空，返回arg1，否則返回arg2

NULLIF(arg1,arg2)
如果arg1=arg2返回NULL；否則返回arg1

7、控制流函數(shù)小練習(xí) #7.1、準(zhǔn)備表/*Navicat MySQL Data TransferSource Server : localhost_3306Source Server Version : 50720Source Host : localhost:3306Source Database : studentTarget Server Type : MYSQLTarget Server Version : 50720File Encoding : 65001Date: 2018-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', '關(guān)羽', '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、統(tǒng)計(jì)各科各分?jǐn)?shù)段人數(shù).顯示格式:課程ID,課程名稱(chēng),[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;小練習(xí) 小練習(xí) 使用： #1 基本使用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'#2 準(zhǔn)備表和記錄 CREATE TABLE blog (id INT PRIMARY KEY auto_increment,NAME CHAR (32),sub_time datetime);INSERT INTO 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');#3. 提取sub_time字段的值，按照格式后的結(jié)果即"年月"來(lái)分組SELECT DATE_FORMAT(sub_time,'%Y-%m'),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m');#結(jié)果+-------------------------------+----------+| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |+-------------------------------+----------+| 2015-03 | 2 || 2016-07 | 4 || 2017-03 | 3 |+-------------------------------+----------+rows in set (0.00 sec)需要掌握函數(shù)：date_format

?

1.自定義函數(shù) #！！！注意！！！#函數(shù)中不要寫(xiě)sql語(yǔ)句（否則會(huì)報(bào)錯(cuò)），函數(shù)僅僅只是一個(gè)功能，是一個(gè)在sql中被應(yīng)用的功能#若要想在begin...end...中寫(xiě)sql，請(qǐng)用存儲(chǔ)過(guò)程delimiter //create function f1(i1 int,i2 int)returns intBEGINdeclare num int;set num = i1 + i2;return(num);END //delimiter ;------------------delimiter //create function f5(i int)returns intbegindeclare res int default 0;if i = 10 thenset res=100;elseif i = 20 thenset res=200;elseif i = 30 thenset res=300;elseset res=400;end if;return res;end //delimiter ; 2.刪除函數(shù)
drop function func_name;
3.執(zhí)行函數(shù)
# 獲取返回值
select UPPER('egon') into @res;
SELECT @res;

# 在查詢(xún)中使用
select f1(11,nid) ,name from tb2;

六、流程控制
1.if條件語(yǔ)句 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 ; if條件語(yǔ)句

?

2.循環(huán)語(yǔ)句 # 1.while循環(huán)delimiter //CREATE PROCEDURE proc_while ()BEGINDECLARE num INT ;SET num = 0 ;WHILE num < 10 DOSELECTnum ;SET num = num + 1 ;END WHILE ;END //delimiter ; # 2.repeat循環(huán)delimiter //CREATE PROCEDURE proc_repeat ()BEGINDECLARE i INT ;SET i = 0 ;repeatselect i;set i = i + 1;until i >= 5end repeat;END //delimiter ;# 3.loop循環(huán) BEGINdeclare i int default 0;loop_label: loopset i=i+1;if i<8 theniterate loop_label;end if;if i>=10 thenleave loop_label;end if;select i;end loop loop_label;END 循環(huán)語(yǔ)句

?

轉(zhuǎn)載于:https://www.cnblogs.com/mumupa0824/p/9446929.html

總結(jié)

以上是生活随笔為你收集整理的数据库 - mysql内置功能的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。