MySQL-視圖與存儲過程

一、MySQL基礎知識

增：
增加單條數據：insert into 表 (列名,列名...) values (值,值,值...)
增加多條數據：insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
導入其他表的數據：insert into 表 (列名,列名...) select 列名,列名... from 表

刪：
刪除具體數據：delete from 表 where 條件

改：
修改表中具體數據：update 表 set 列名＝ 值 where 條件

查：
查詢具體內容：select 列名01 as 別名 , 列名02 from 表 where 條件

常見條件：
1、邏輯運算符
and：與；or：或；not：非
2、比較運算符
等于=；大于>；大于等于>=；小于<；小于等于<=；不等于!=或<>

示例：
select * from test where id > 5 and num > 60;

3、范圍
in：在其中；not in：不在其中；between：在區間內

示例：
select * from test where nid between 5 and 10;

4、通配符
%：匹配任意零個字符或者任意多個字符；
_ ：匹配任意一個字符

示例：
select * from test where name like '李%';

5、空值
is null：空值
is not null：非空

6、限制條件
limit 3：取前三行
limit 3,5：從第三行開始取5行
limit 3 offset 5：從第五行開始取3行

7、排序
order by 列 asc：從小到大排序
order by 列 desc：從大到小排序
order by 列1 desc，列二 asc：根據列1從大到小排序，如果相同則按列二從小到大排序

8、分組
select count(列名),sum(列名),max(列名),min(列名) from 表 where 條件 group by 列名01,列名02 order by 列名
特別注意：group by 必須在where之后，order by之前

9、連表
無對應關系則不顯示：
select A.xx B.oo from A, B where A.x=B.o 沒有A.x=B.o對應的數據則不顯示任何結果

示例：select  tb1.num,tb2.name from tb1,tb2 where tb1.sid = tb2.sid

無對應關系則不顯示:
select A.xx B.oo from A inner join B where A.x=B.o A和B具有對等位置, 沒有A.x=B.o對應的數據則不顯示任何結果

A表所有顯示，如果B中無對應關系，則值為null
select A.num, A.name, B.name from A left join B?on A.nid = B.nid

select score.sid,score.course_id,score.num,student.sname 
from score 
left join student on score.student_id = student.sid 

B表所有顯示，如果B中無對應關系，則值為null
select A.num, A.name, B.name from A right join B on A.nid = B.nid

select score.sid,score.course_id,score.num,student.sname 
from student
right join score on score.student_id = student.sid

10、導入與導出數據庫

導出現有數據庫數據
    mysqldump -u 用戶名 -p 密碼 數據庫名 > 導出文件路徑    #結構+數據
    mysqldump -u 用戶名 -p 密碼 -d 數據庫名 > 導出文件路徑    #結構

導入現有數據
mysqldump -u root -p 密碼 數據庫名 < 文件路徑

二、視圖

視圖（view）是一種虛擬存在的表，是一個邏輯表，本身并不包含數據。作為一個select語句保存在數據字典中的。通過視圖，可以展現基表的部分數據；視圖數據來自定義視圖的查詢中使用的表，使用視圖動態生成。

1、創建視圖

格式：CREATE VIEW 視圖名稱 AS  SQL語句
示例：
    create view temp1 as 
    select score.sid,score.course_id,score.num,student.sname 
    from score 
    left join student on score.student_id = student.sid;

2、修改視圖

格式：ALTER VIEW 視圖名稱 AS SQL語句
示例：
    alter view temp2 as 
    select score.sid,score.course_id,score.num,student.sname 
    from score 
    left join student on score.student_id = student.sid 
    where course_id in (1,2);

3、使用視圖

格式：SELECT * FROM 視圖名稱
示例：
    select * from temp2;

4、刪除視圖

格式：DROP VIEW 視圖名稱
示例：
    drop view temp2;

三、存儲過程

存儲過程是一個SQL語句集合，當主動去調用存儲過程時，其中內部的SQL語句會按照邏輯執行。
1、創建無參數存儲過程

格式：CREATE PROCEDURE  過程名([[IN|OUT|INOUT] 參數名 數據類型[,[IN|OUT|INOUT] 參數名 數據類型…]]) [特性 ...] 過程體

示例：
        delimiter //
        create procedure p1()
        BEGIN
            select * from score;
        END//
        delimiter;

2、創建帶參數的參數過程
存儲過程可以接收的參數有三種，in：僅參數的值必須在調用存儲過程時指定，在存儲過程中修改該參數的值不能被返回，為默認值，out：該值可在存儲過程內部被改變，并可返回，inout：調用時指定，并且可被改變和返回。

示例：
        delimiter //
        create procedure p2(
            in i1 int,
            out r1 int,
            inout i2 int
        )
        BEGIN
            DECLARE temp1 int;
            DECLARE temp2 int default 5;
            set temp1 = 10;
            set i2 = i1 + 100;
            set r1 = i1 + temp1 + temp2;
        END//
        delimiter;

3、執行存儲過程
無參數：

call p1()

有參數：

set @t1 = 0;
set @t2 = 1;
call p2(1,@t1,@t2);
select @t1,@t2;

4、刪除存儲過程

drop procedure 存儲過程名;

5、pymysql執行存儲過程

#!/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)
# 執行存儲過程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 獲取執行完存儲的參數
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
print(result)

6、條件語句
IF-THEN-ELSE語句

delimiter \\
CREATE PROCEDURE p3 (
        in i1 int
)
BEGIN

    IF i1 = 1 THEN
        select * from t1;
    ELSEIF i = 2 THEN
        select * from t2;
    ELSE
        select * from t3;
    END IF;

END\\
delimiter ;

CASE-WHEN-THEN-ELSE語句

delimiter \\
CREATE PROCEDURE p3 (
        in i1 int
)
BEGIN

    CASE i1  
        WHEN    1   THEN
        select * from t1;
    WHEN    2   THEN
        select * from t2;
    ELSE
        select * from t3;
    END CASE;

END\\
delimiter ;

7、循環語句
WHILE-DO…END-WHILE

delimiter \\
CREATE PROCEDURE p4 ()
BEGIN
    DECLARE i int;
        set i = 1;
        WHILE i < 3 DO
            select i;
            set i = i + 1;
        END WHILE;
END\\
delimiter ;

REPEAT...END REPEAT
此語句的特點是執行操作后檢查結果

delimiter \\
CREATE PROCEDURE p5 ()
BEGIN
    DECLARE i int;
        set i = 1;
        REPEAT
            select i;
            set i = i + 1;
            until i > 3
        END REPEAT;
END\\
delimiter ;

LOOP...END LOOP

delimiter \\
CREATE PROCEDURE p5 ()
BEGIN
    DECLARE i int default 0;
        loop_lable: loop
            set i = i + 1;
            if i < 3 then
                iterate loop_lable;
            end if;
            if i > 5 then
                leave loop_lable;
            end if;
            select i;
        END loop loop_lable;
END\\
delimiter ;

8、存儲過程的基本函數
字符串類

CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //連接字串
INSTR (string ,substring ) //返回substring首次在string中出現的位置,不存在返回0
LCASE (string2 ) //轉換成小寫
LEFT (string2 ,length ) //從string2中的左邊起取length個字符
LENGTH (string ) //string長度
LOAD_FILE (file_name ) //從文件讀取內容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定開始位置
LPAD (string2 ,length ,pad ) //重復用pad加在string開頭,直到字串長度為length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重復count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替換search_str
RPAD (string2 ,length ,pad) //在str后用pad補充,直到長度為length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比較兩字串大小,
SUBSTRING (str , position [,length ]) //從str的position開始,取length個字符,
注：mysql中處理字符串時，默認第一個字符下標為1，即參數position必須大于等于1
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //轉換成大寫
RIGHT(string2,length) //取string2最后length個字符
SPACE(count) //生成count個空格

數學類

ABS (number2 ) //絕對值
BIN (decimal_number ) //十進制轉二進制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //進制轉換
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小數位數
HEX (DecimalNumber ) //轉十六進制
注：HEX()中可傳入字符串，則返回其ASC-11碼，如HEX('DEF')返回4142143
也可以傳入十進制整數，返回其十六進制編碼，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指數
RAND([seed]) //隨機數
ROUND (number [,decimals ]) //四舍五入,decimals為小數位數
SIGN (number2 ) // 正數返回1，負數返回-1

日期時間類

ADDTIME (date2 ,time_interval ) //將time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //轉換時區
CURRENT_DATE ( ) //當前日期
CURRENT_TIME ( ) //當前時間
CURRENT_TIMESTAMP ( ) //當前時間戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或時間
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式顯示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上減去一個時間
DATEDIFF (date1 ,date2 ) //兩個日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1為星期天
DAYOFYEAR (date ) //一年中的第幾天
EXTRACT (interval_name FROM date ) //從date中提取日期的指定部分
MAKEDATE (year ,day ) //給出年及年中的第幾天,生成日期串
MAKETIME (hour ,minute ,second ) //生成時間串
MONTHNAME (date ) //英文月份名
NOW ( ) //當前時間
SEC_TO_TIME (seconds ) //秒數轉成時間
STR_TO_DATE (string ,format ) //字串轉成時間,以format格式顯示
TIMEDIFF (datetime1 ,datetime2 ) //兩個時間差
TIME_TO_SEC (time ) //時間轉秒數]
WEEK (date_time [,start_of_week ]) //第幾周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第幾天
HOUR(datetime) //小時
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符號,正負或0
SQRT(number2) //開平方