程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MYSQL入門全套(第二部),mysql入門

MYSQL入門全套(第二部),mysql入門

編輯:MySQL綜合教程

MYSQL入門全套(第二部),mysql入門


上一篇文章講的是mysql的基本操作,這一篇會有一點難以理解,本節主要內容mysql視圖,存儲過程,函數,事務,觸發器,以及動態執行sql

視圖view

視圖是一個虛擬表,其內容由查詢定義。同真實的表一樣,視圖包含一系列帶有名稱的列和行數據。但是,視圖並不在數據庫中以存儲的數據值集形式存在。行和列數據來自由定義視圖的查詢所引用的表,並且在引用視圖時動態生成。對其中所引用的基礎表來說,視圖的作用類似於篩選。定義視圖的篩選可以來自當前或其它數據庫的一個或多個表,或者其它視圖。通過視圖進行查詢沒有任何限制,通過它們進行數據修改時的限制也很少。視圖是存儲在數據庫中的查詢的SQL 語句,它主要出於兩種原因:安全原因, 視圖可以隱藏一些數據。

1、創建視圖

--格式:CREATE VIEW 視圖名稱 AS  SQL語句
CREATE VIEW v1 AS SELET nid, name FROM tab1 WHERE nid > 4

2、刪除視圖

--格式:DROP VIEW 視圖名稱
DROP VIEW v1

3、修改視圖

-- 格式:ALTER VIEW 視圖名稱 AS SQL語句
ALTER VIEW v1 AS
SELET A.nid,B. NAME FROM tab1
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE tab1.id > 2

也就只是改了把create改成alter,中間的語句更換了。

4、使用視圖

使用視圖時,將其當作表進行操作即可,由於視圖是虛擬表,所以無法使用其對真實表進行創建、更新和刪除操作,僅能做查詢用。

select * from v1

存儲過程procedure

1、我們為什麼要用存儲過程呢 ?

我們都知道應用程序分為兩種,一種是基於web,一種是基於桌面,他們都和數據庫進行交互來完成數據的存取工作。假設現在有一種應用程序包含了這兩 種,現在要修改其中的一個查詢sql語句,那麼我們可能要同時修改他們中對應的查詢sql語句,當我們的應用程序很龐大很復雜的時候問題就出現這,不易維 護!另外把sql查詢語句放在我們的web程序或桌面中很容易遭到sql注入的破壞。而存儲例程正好可以幫我們解決這些問題。

2、創建存儲過程

創建存儲過程這塊主要有兩種,一種是帶參數的,一種是不帶參數的,先講不帶參數的再說不帶參數的

不帶參數案例:

-- 創建存儲過程
delimiter //        --自定義語句結尾符號,因為這裡要執行好多句sql語句,所以就得自定義,以防止出錯
create procedure p1()
BEGIN
    select * from tab1;
END//
delimiter ;         --自定義局域結尾符號結束

-- 執行存儲過程
call p1()

帶參數案例這塊主要有三個類

  • in          僅用於傳入參數用
  • out        僅用於返回值用
  • inout     既可以傳入又可以當作返回值
-- 創建存儲過程
delimiter \\
create procedure p1(
    in i1 int,                        -- 傳入參數i1
    in i2 int,                        -- 傳入參數i2
    inout i3 int,                     -- 即傳入又能得到返回值
    out r1 int                        -- 得到返回值
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;  
    set temp1 = 1;
    set r1 = i1 + i2 + temp1 + temp2; 
    set i3 = i3 + 100;
end\\
delimiter ;

-- 執行存儲過程
DECLARE @t1 INT default 3;           --  設置變量默認值為3
DECLARE @t2 INT;                     --  設置變量
CALL p1 (1, 2 ,@t1, @t2);            --  執行存儲過程,並傳入參數,t2自動取消
SELECT @t1,@t2;                      --  查看存儲過程輸出結果

2、刪除存儲過程

drop procedure p1;

3、python用pymysql模塊調用存儲過程,因為我們學習這些就是為了語言調用

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='day39b_')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 執行存儲過程
row = cursor.callproc('p1',(1,2,3))
# 存儲過程的查詢結果
selc = cursor.fetchall()
print(selc)
# 獲取存儲過程返回
effect_row = cursor.execute('select @_p1_0,@_p1_1,@_p1_2')
# 曲存儲過程的返回值
ret = cursor.fetchone()
print(ret)
# 提交,不然無法保存新建或者修改的數據
conn.commit()
# 關閉游標
cursor.close()
# 關閉連接
conn.close()

函數function

 在mysql中有很多內置函數,比如我們經常用的求平均值,求和,個數,各式各樣,先給大家來一部門內置函數,然後再說說自定義函數吧,函數也可以傳參數,也可以接收返回值,但是函數沒辦法得到執行語句得到的結果,存儲過程可以。

CHAR_LENGTH(str) 返回值為字符串str 的長度,長度的單位為字符。一個多字節字符算作一個單字符。 對於一個包含五個二字節字符集, LENGTH()返回值為 10, 而CHAR_LENGTH()的返回值為5。 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) 變大寫 LTRIM(str) 返回字符串 str ,其引導空格字符被刪除。 RTRIM(str) 返回字符串 str ,結尾空格字符被刪去。 SUBSTRING(str,pos,len) 獲取字符串子序列 LOCATE(substr,str,pos) 獲取子序列索引位置 REPEAT(str,count) 返回一個由重復的字符串str 組成的字符串,字符串str的數目等於count 。 若 count <= 0,則返回一個空字符串。 若str 或 count 為 NULL,則返回 NULL 。 REPLACE(str,from_str,to_str) 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 REVERSE(str) 返回字符串 str ,順序和字符順序相反。 RIGHT(str,len) 從字符串str 開始,返回從後邊開始len個字符組成的子序列 SPACE(N) 返回一個由N空格組成的字符串。 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' TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 返回字符串 str , 其中所有remstr 前綴和/或後綴都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的,則假設為BOTH 。 remstr 為可選項,在未指定情況下,可刪除空格。 mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx' 內置函數

更多參考請參考中文文檔http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions

1、自定義創建函數

delimiter \\
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END \\
delimiter ;

2、刪除函數

drop function f1;

3、執行函數

# 獲取返回值
declare @i VARCHAR(32);
select UPPER('alex') into @i;
SELECT @i;

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

事務

 事務用於將某些操作的多個SQL作為原子性操作,一旦有某一個出現錯誤,即可回滾到原來的狀態,從而保證數據庫數據完整性。例如:當兩張銀行卡之間進行轉賬,甲方錢轉出去了,突然光纜壞了,乙方還沒收到錢,錢跑哪裡去了,就為了防止這種情況,事務就出來了,事務可以防止這種事情發生。

應用事務實例:

delimiter \\
create PROCEDURE p1(
    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;                   -- sql語句都放在這個裡面
    insert into tb2(name)values('seven');
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END\\
delimiter ;

執行存儲過程:

DECLARE @i TINYINT;
call p1(@i);
select @i;

觸發器TRIGGER

 觸發器,簡單來說就是當你在執行這條語句之前或者之後觸發一次增刪改查,觸發器用於定制用戶對表的行進行【增/刪/改】前後的行為。

1、基本語法

# 插入前
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

事例一插入前:

-- 在往tab1插入數據之前往tab2中插入一條name = 張巖林,當然是在判斷往tab1中插入的名字是不是等於aylin
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW. NAME == 'aylin' THEN
    INSERT INTO tb2 (NAME)
VALUES
    ('張巖林')
END
END//
delimiter ;

事例二插入後:

delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    IF NEW. num = 666 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('張巖林'),
            ('很帥') ;
    ELSEIF NEW. num = 555 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('aylin'),
            ('非常帥') ;
    END IF;
END//
delimiter ;

同樣的刪,改,查都是同樣的道理

特別的:NEW表示即將插入的數據行,OLD表示即將刪除的數據行。

2、刪除觸發器

DROP TRIGGER tri_after_insert_tb1;

3、使用觸發器

觸發器無法由用戶直接調用,而知由於對表的【增/刪/改】操作被動引發的。

insert into tb1(name) values(‘張巖林’)

下章給大家更新數據庫索引,這方面東西比較多,所以樓主決定把他從新寫一篇博客,看完的記得點贊喲!!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved