一 視圖

視圖是一個(gè)虛擬表（非真實(shí)存在）米者，其本質(zhì)是【根據(jù)SQL語(yǔ)句獲取動(dòng)態(tài)的數(shù)據(jù)集韭畸，并為其命名】，用戶使用時(shí)只需使用【名稱】即可獲取結(jié)果集蔓搞，可以將該結(jié)果集當(dāng)做表來(lái)使用胰丁。

使用視圖我們可以把查詢過(guò)程中的臨時(shí)表摘出來(lái)，用視圖去實(shí)現(xiàn)喂分，這樣以后再想操作該臨時(shí)表的數(shù)據(jù)時(shí)就無(wú)需重寫(xiě)復(fù)雜的sql了隘马，直接去視圖中查找即可，但視圖有明顯地效率問(wèn)題妻顶，并且視圖是存放在數(shù)據(jù)庫(kù)中的酸员，如果我們程序中使用的sql過(guò)分依賴數(shù)據(jù)庫(kù)中的視圖，即強(qiáng)耦合讳嘱，那就意味著擴(kuò)展sql極為不便幔嗦，因此并不推薦使用

#兩張有關(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)

#查詢李平老師教授的課程名
mysql> select cname from course where teacher_id = (select tid from teacher where tname='李平老師');
+--------+
| cname  |
+--------+
| 物理   |
| 美術(shù)   |
+--------+
rows in set (0.00 sec)

#子查詢出臨時(shí)表，作為teacher_id等判斷依據(jù)
select tid from teacher where tname='李平老師'

臨時(shí)表應(yīng)用舉例

1 創(chuàng)建視圖**

#語(yǔ)法：CREATE VIEW 視圖名稱 AS  SQL語(yǔ)句
create view teacher_view as select tid from teacher where tname='李平老師';

#于是查詢李平老師教授的課程名的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ě)子查詢的sql钝鸽，但是這么效率并不高汇恤，還不如我們寫(xiě)子查詢的效率高

#2\. 而且有一個(gè)致命的問(wèn)題：視圖是存放到數(shù)據(jù)庫(kù)里的，如果我們程序中的sql過(guò)分依賴于數(shù)據(jù)庫(kù)中存放的視圖拔恰，
那么意味著因谎，一旦sql需要修改且涉及到視圖的部分，則必須去數(shù)據(jù)庫(kù)中進(jìn)行修改颜懊，而通常在公司中數(shù)據(jù)庫(kù)有專門(mén)的DBA負(fù)責(zé)财岔，
你要想完成修改风皿，必須付出大量的溝通成本DBA可能才會(huì)幫你完成修改，極其地不方便

2 使用視圖**

dss.png

修改視圖匠璧，原始表也跟著改

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: 0

mysql> 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ú)法修改視圖中的記錄的，如下圖

#### [圖片上傳失敗...(image-8a5728-1599384055539)]

3 修改視圖**

語(yǔ)法：ALTER VIEW 視圖名稱 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 視圖名稱

DROP VIEW teacher_view

使用觸發(fā)器可以定制用戶對(duì)表進(jìn)行【增夷恍、刪魔眨、改】操作時(shí)前后的行為，注意：沒(méi)有查詢
觸發(fā)器
五  創(chuàng)建觸發(fā)器
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 刪除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 刪除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
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 ROW
BEGIN
    IF 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');


#查詢錯(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ù)行拓挥。

2 使用觸發(fā)器
觸發(fā)器無(wú)法由用戶直接調(diào)用，而知由于對(duì)表的【增/刪/改】操作被動(dòng)引發(fā)的袋励。

3 刪除觸發(fā)器
drop trigger tri_after_insert_cmd;

二 事務(wù)

1 事物
事務(wù)用于將某些操作的多個(gè)SQL作為原子性操作侥啤，一旦有某一個(gè)出現(xiàn)錯(cuò)誤，即可回滾到原來(lái)的狀態(tài)茬故，從而保證數(shù)據(jù)庫(kù)數(shù)據(jù)完整性盖灸。

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;
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 |
+----+------+---------+
rows in set (0.00 sec)
#介紹
delimiter //
            create procedure p4(
                out status int
            )
            BEGIN
                1. 聲明如果出現(xiàn)異常則執(zhí)行{
                    set status = 1;
                    rollback;
                }

                開(kāi)始事務(wù)
                    -- 由秦兵賬戶減去100
                    -- 方少偉賬戶加90
                    -- 張根賬戶加10
                    commit;
                結(jié)束

                set status = 2;


            END //
            delimiter ;

#實(shí)現(xiàn)
delimiter //
create PROCEDURE p5(
    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; 
        DELETE from tb1; #執(zhí)行失敗
        insert into blog(name,sub_time) values('yyy',now());
    COMMIT; 

    -- SUCCESS 
    set 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()) #查詢select的查詢結(jié)果

cursor.execute('select @_p5_0;')
print(cursor.fetchall())

三 存儲(chǔ)過(guò)程介紹
存儲(chǔ)過(guò)程包含了一系列可執(zhí)行的sql語(yǔ)句磺芭，存儲(chǔ)過(guò)程存放于MySQL中赁炎，通過(guò)調(diào)用它的名字可以執(zhí)行其內(nèi)部的一堆sql

使用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：

#1. 用于替代程序?qū)懙腟QL語(yǔ)句，實(shí)現(xiàn)程序與sql解耦

#2. 基于網(wǎng)絡(luò)傳輸钾腺，傳別名的數(shù)據(jù)量小徙垫，而直接傳sql數(shù)據(jù)量大
使用存儲(chǔ)過(guò)程的缺點(diǎn)：

#1. 程序員擴(kuò)展功能不方便
補(bǔ)充：程序與數(shù)據(jù)庫(kù)結(jié)合使用的三種方式

#方式一：
    MySQL：存儲(chǔ)過(guò)程
    程序：調(diào)用存儲(chǔ)過(guò)程

#方式二：
    MySQL：
    程序：純SQL語(yǔ)句

#方式三：
    MySQL:
    程序：類(lèi)和對(duì)象，即ORM（本質(zhì)還是純SQL語(yǔ)句）
2 創(chuàng)建簡(jiǎn)單存儲(chǔ)過(guò)程（無(wú)參）
delimiter //
create procedure p1()
BEGIN
    select * 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())
3 創(chuàng)建存儲(chǔ)過(guò)程（有參）
對(duì)于存儲(chǔ)過(guò)程放棒，可以接收參數(shù)姻报，其參數(shù)有三類(lèi)：

#in          僅用于傳入?yún)?shù)用
#out        僅用于返回值用
#inout     既可以傳入又可以當(dāng)作返回值
delimiter //
create procedure p2(
    in n1 int,
    in n2 int
)
BEGIN

    select * from blog where id > n1;
END //
delimiter ;

#在mysql中調(diào)用
call p2(3,2)

#在python中基于pymysql調(diào)用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())

in:傳入?yún)?shù)
delimiter //
create procedure p3(
    in n1 int,
    out res int
)
BEGIN
    select * 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=0
print(cursor.fetchall()) #查詢select的查詢結(jié)果

cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一個(gè)參數(shù)吴旋，@p3_1代表第二個(gè)參數(shù)，即返回值
print(cursor.fetchall())

out：返回值
delimiter //
create procedure p4(
    inout n1 int
)
BEGIN
    select * 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,))
print(cursor.fetchall()) #查詢select的查詢結(jié)果

cursor.execute('select @_p4_0;') 
print(cursor.fetchall())

inout:既可以傳入又可以返回
4 執(zhí)行存儲(chǔ)過(guò)程
-- 無(wú)參數(shù)
call proc_name()

-- 有參數(shù)厢破，全in
call proc_name(1,2)

-- 有參數(shù)荣瑟，有in，out摩泪，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

執(zhí)行存儲(chǔ)過(guò)程

在MySQL中執(zhí)行存儲(chǔ)過(guò)程-- 無(wú)參數(shù)
call proc_name()

-- 有參數(shù)笆焰，全in
call proc_name(1,2)

-- 有參數(shù)，有in加勤，out仙辟，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

執(zhí)行存儲(chǔ)過(guò)程

在MySQL中執(zhí)行存儲(chǔ)過(guò)程
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = 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)

在python中基于pymysql執(zhí)行存儲(chǔ)過(guò)程
4 刪除存儲(chǔ)過(guò)程
drop procedure proc_name;

四 函數(shù)

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查詢中)
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'

5 日期和時(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'

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

7 控制流函數(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.1控制流函數(shù)小練習(xí)

7.1敲长、準(zhǔn)備表

/*
Navicat MySQL Data Transfer

Source Server : localhost_3306
Source Server Version : 50720
Source Host : localhost:3306
Source Database : student

Target Server Type : MYSQL
Target Server Version : 50720
File Encoding : 65001

Date: 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,課程名稱,[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;

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

更多函數(shù)：[中文猛擊這里](http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions) OR [官方猛擊這里](https://dev.mysql.com/doc/refman/5.7/en/functions.html)

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 int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END //
delimiter ;

delimiter //
create function f5(
i int
)
returns int
begin
declare res int default 0;
if i = 10 then
set res=100;
elseif i = 20 then
set res=200;
elseif i = 30 then
set res=300;
else
set res=400;
end if;
return res;
end //
delimiter ;

2  刪除函數(shù)**

drop function func_name;

3 執(zhí)行函數(shù)**

獲取返回值

select UPPER('egon') into @res;
SELECT @res;

在查詢中使用

select f1(11,nid) ,name from tb2;

五  流程控制
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN

    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END //
delimiter ;

if條件語(yǔ)句
2 循環(huán)語(yǔ)句
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END //
delimiter ;

3  while循環(huán)
delimiter //
CREATE PROCEDURE proc_repeat ()
BEGIN

    DECLARE i INT ;
    SET i = 0 ;
    repeat
        select i;
        set i = i + 1;
        until i >= 5
    end repeat;

END //
delimiter ;

 4  repeat循環(huán)
BEGIN

    declare i int default 0;
    loop_label: loop

        set i=i+1;
        if i<8 then
            iterate loop_label;
        end if;
        if i>=10 then
            leave loop_label;
        end if;
        select i;
    end loop loop_label;

END

loop