MySQL學習筆記二

標籤：

Ø function 函數

函數的作用比較大，一般多用在select查詢語句和where條件陳述式之後。按照函數返回的結果，

可以分為：多行函數和單行函數；所謂的單行函數就是將每條資料進行獨立的計算，然後每條資料得到一條結果。

如：字串函數；而多行函數，就是多條記錄同時計算，得到最終只有一條結果記錄。如：sum、avg等

多行函數也稱為聚集合函式、分組函數，主要用於完成一些統計功能。MySQL的單行函數有如下特徵：

    單行函數的參數可以是變數、常量或資料列。單行函數可以接受多個參數，但返回一個值。

    單行函數就是它會對每一行單獨起作用，每一行（可能包含多個參數）返回一個結果。

    單行函數可以改變參數的資料類型。單行函數支援嵌套使用：內層函數的傳回值是外層函數的參數。

單行函數可以分為：

    類型轉換函式；

    位函數；

    流程式控制制語句；

    加密解密函數；

    資訊函數

單行函數

1、    char_length字元長度

select char_length(tel) from user;

2、    sin函數

select sin(age) from user;

select sin(1.57);

3、    添加日期函數

select date_add(‘2010-06-21‘, interval 2 month);

interval是一個關鍵字，2 month是2個月的意思，2是數值，month是單位

select addDate(‘2011-05-28‘, 2);

在前面的日期上加上後面的天數

4、    擷取當前系統時間、日期

select curdate();

select curtime();

5、    加密函數

select md5(‘zhangsan‘);

6、    Null 處理函數

select ifnull(birthday, ‘is null birthday‘) from user;

如果birthday為null，就返回後面的字串

select nullif(age, 245) from user;

如果age等於245就返回null，不等就返回age

select isnull(birthday) from user;

判斷birthday是否為null

select if(isnull(birthday), ‘birthday is null‘, ‘birthday not is null‘) from user;

如果birthday為null或是0就返回birthday is null，否則就返回birthday not is null；類似於三目運算子

7、    case 流程函數

case函數是一個流程式控制制函數，可以接受多個參數，但最終只會返回一個結果。

select name,

age,

(case sex

    when 1 then ‘男‘

    when 0 then ‘女‘

    else ‘火星人‘

    end

) sex

from user;

組函數

組函數就是多行函數，組函數是完成一行或多行結果集的運算，最後返回一個結果，而不是每條記錄返回一個結果。

1、    avg平均值運算

select avg(age) from user;

select avg(distinct age) from user;

2、    count 記錄條數統計

select count(*), count(age), count(distinct age) from user;

3、    max 最大值

select max(age), max(distinct age) from user;

4、    min 最小值

select min(age), min(distinct age) from user;

5、    sum 求和、聚和

select sum(age), sum(distinct age) from user;

select sum(ifnull(age, 0)) from user;

6、    group by 分組

select count(*), sex from user group by sex;

select count(*) from user group by age;

select * from user group by sex, age;

7、    having進行條件過濾

不能在where子句中過濾組，where子句僅用於過濾行。過濾group by需要having

不能在where子句中用組函數，having中才能用組函數

select count(*) from user group by sex having sex <> 2;

Ø 多表查詢和子查詢

資料庫的查詢功能最為豐富，很多時候需要用到查詢完成一些事物，而且不是單純的對一個表進行操作。而是對多個表進行聯集查詢，

MySQL中多表串連查詢有兩種規範，較早的SQL92規範支援，如下幾種表串連查詢：

    等值串連

    非等值串連

    外串連

    廣義笛卡爾積

SQL99規則提供了可讀性更好的多表串連文法，並提供了更多類型的串連查詢，SQL99支援如下幾種多表串連查詢：

    交叉串連

    自然串連

    使用using子句的串連

    使用on子句串連

    全部串連或者左右外串連

SQL92的串連查詢

SQL92的串連查詢文法比較簡單，多將多個table放置在from關鍵字之後，多個table用“，”隔開；

串連的條件放在where條件之後，與查詢條件直接用and邏輯運算子進行串連。如果條件中使用的是相等，

則稱為等值串連，相反則稱為非等值，如果沒有任何條件則稱為廣義笛卡爾積。

廣義笛卡爾積：select s.*, c.* from student s, classes c;

等值：select s.*, c.* from student s, classes c where s.cid = c.id;

非等值：select s.*, c.* from student s, classes c where s.cid <> c.id;

select s.*, c.name classes from classes c, student s where c.id = s.classes_id and s.name is not null;

SQL99串連查詢

1、交叉串連cross join，類似於SQL92的笛卡爾積查詢，無需條件。如：

select s.*, c.name from student s cross join classes c;

2、自然串連 natural join查詢，無需條件，預設條件是將2個table中的相同欄位作為串連條件，如果沒有相同欄位，查詢的結果就是空。

select s.*, c.name from student s natural join classes c;

3、using子句串連查詢：using的子句可以是一列或多列，顯示的指定兩個表中同名列作為串連條件。

如果用natural join的串連查詢，會把所有的相同欄位作為串連查詢。而using可以指定相同列及個數。

select s.*, c.name from student s join classes c using(id);

4、    join … on串連查詢，查詢條件在on中完成，每個on語句只能指定一個條件。

select s.*, c.name from student s join classes c on s.classes_id = c.id;

5、    左右外串連：3種外串連，left [outer] join、right [outer] join，串連條件都是通過用on子句來指定，條件可以等值、非等值。

select s.*, c.name from student s left join classes c on s.classes_id = c.id;

select s.*, c.name from student s right join classes c on s.classes_id = c.id;

    子查詢

    子查詢就是指在查詢語句中嵌套另一個查詢，子查詢可以支援多層嵌套。子查詢可以出現在2個位置：

    from關鍵字之後，被當做一個表來進行查詢，這種用法被稱為行內視圖，因為該子查詢的實質就是一個臨時視圖

    出現在where條件之後作為過濾條件的值

子查詢注意點：

    子查詢用括弧括起來，特別情況下需要起一個臨時名稱

    子查詢當做暫存資料表時（在from之後的子查詢），可以為該子查詢起別名，尤其是要作為首碼來限定資料列名時

    子查詢用作過濾條件時，將子查詢放在比較子的右邊，提供可讀性

    子查詢作為過濾條件時，單行子查詢使用單行運算子，多行子查詢用多行運算子

將from後面的子查詢當做一個table來用：

select * from (select id, name from classes) s where s.id in (1, 2);

當做條件來用：

select * from student s where s.classes_id in (select id from classes);

select * from student s where s.classes_id = any (select id from classes);

select * from student s where s.classes_id > any (select id from classes);

Ø 操作符和函數

1、    boolean只判斷

select 1 is true, 0 is false, null is unknown;

select 1 is not unknown, 0 is not unknown, null is not unknown;

2、    coalesce函數，返回第一個非null的值

select coalesce(null, 1);

select coalesce(1, 1);

select coalesce(null, 1);

select coalesce(null, null);

3、    當有2個或多個參數時，返回最大的那個參數值

select greatest(2, 3);

select greatest(2, 3, 1, 9, 55, 23);

select greatest(‘D‘, ‘A‘, ‘B‘);

4、    Least函數，返回最小值，如果有null就返回null值

select least(2, 0);

select least(2, 0, null);

select least(2, 10, 22.2, 35.1, 1.1);

5、    控制流程函數

select case 1 when 1 then ‘is 1‘ when 2 then ‘is 2‘ else ‘none‘ end;

select case when 1 > 2 then ‘yes‘ else ‘no‘ end;

6、    ascii字串函數

select ascii(‘A‘);

select ascii(‘1‘);

7、    二進位函數

select bin(22);

8、    返回二進位字串長度

select bit_length(11);

9、    char將值轉換成字元，小數取整四捨五入

select char(65);

select char(65.4);

select char(65.5);

select char(65.6);

select char(65, 66, 67.4, 68.5, 69.6, ‘55.5‘, ‘97.3‘);

10、    using改變字元集

select charset(char(0*65)), charset(char(0*65 using utf8));

11、    得到字元長度char_length,character_length

select char_length(‘abc‘);

select character_length(‘eft‘);

12、    compress壓縮字串、uncompress解壓縮

select compress(‘abcedf‘);

select uncompress(compress(‘abcedf‘));

13、    concat_ws分隔字串

select concat_ws(‘#‘, ‘first‘, ‘second‘, ‘last‘);

select concat_ws(‘#‘, ‘first‘, ‘second‘, null, ‘last‘);

Ø 交易處理

動作

    開始事務：start transaction

    提交事務：commit

    復原事務：rollback

    設定自動認可：set autocommit 1 | 0

    atuoCommit系統預設是1立即提交模式；如果要手動控制事務，需要設定set autoCommit 0;

    這樣我們就可以用commit、rollback來控制事務了。

在一段語句塊中禁用autocommit 而不是set autocommit

start transaction;

select @result := avg(age) from temp;

update temp set age = @result where id = 2;

select * from temp where id = 2;//值被改變

rollback;//復原

select * from temp where id = 2;//變回來了

在此期間只有遇到commit、rollback，start Transaction的禁用autocommit才會結束。然後就恢複到原來的autocommit模式；

不能復原的語句

    有些語句不能被復原。通常，這些語句包括資料定義語言 (Data Definition Language)（DDL）語句，比如建立或取消資料庫的語句，

    和建立、取消或更改表或儲存的子程式的語句。

    您在設計事務時，不應包含這類語句。如果您在事務的前部中發布了一個不能被復原的語句，

    則後部的其它語句會發生錯誤，在這些情況下，通過發布ROLLBACK語句不能 復原事務的全部效果。

一些操作也會隱式的提交事務

如alter、create、drop、rename table、lock table、set autocommit、start transaction、truncate table 等等,

在事務中出現這些語句也會提交事務的

    事務不能嵌套事務

    事務的儲存點

Savepoint pointName/Rollback to savepoint pointName

一個事務可以設定多個儲存點，rollback可以復原到指定的儲存點，恢複儲存點後面的操作。

如果有後面的儲存點和前面的同名，則刪除前面的儲存點。

Release savepoint會刪除一個儲存點，如果在一段事務中執行commit或rollback，則事務結束，所以儲存點刪除。

 Set Transaction設計資料庫隔離等級

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

本語句用於設定事務隔離等級，用於下一個事務，或者用於當前會話。

在預設情況下，SET TRANSACTION會為下一個事務（還未開始）設定隔離等級。

如果您使用GLOBAL關鍵詞，則語句會設定全域性的預設事務等級，

用於從該點以後建立的所有新串連。原有的串連不受影響。使用SESSION關鍵測可以設定預設事務等級，

用於對當前串連執行的所有將來事務。

預設的等級是REPEATABLE READ全域隔離等級。

Ø 注釋

select 1+1;     # 單行注釋

select 1+1;     -- 單行注釋

select 1 /* 多行注釋 */ + 1;

Ø 基礎資料型別 (Elementary Data Type)操作

字串

    select ‘hello‘, ‘"hello"‘, ‘""hello""‘, ‘hel‘‘lo‘, ‘\‘hello‘;

    select "hello", "‘hello‘", "‘‘hello‘‘", "hel""lo", "\"hello";

\n換行

    select ‘This\nIs\nFour\nLines‘;

\轉義

    select ‘hello \ world!‘;

    select ‘hello \world!‘;

    select ‘hello \\ world!‘;

    select ‘hello \‘ world!‘;

Ø 設定資料庫mode模式

SET sql_mode=‘ANSI_QUOTES‘;

create table t(a int);

create table "tt"(a int);

create table "t""t"(a int);

craate talbe tab("a""b" int);

Ø 使用者變數

set @num1 = 0, @num2 = 2, @result = 0;

select @result := (@num1 := 5) + @num2 := 3, @num1, @num2, @result;

Ø 預存程序

建立預存程序：

delimiter //

create procedure get(out result int)

begin

 select max(age) into result from temp;

end//

調用預存程序：

call get(@temp);

查詢結果：

select @temp;

刪除預存程序：

drop procedure get;

查看預存程序建立語句：

show create procedure get;

select…into 可以完成單行記錄的賦值：

create procedure getRecord(sid int)

begin

    declare v_name varchar(20) default ‘jason‘;

    declare v_age int;

    declare v_sex bit;

    select name, age, sex into v_name, v_age, v_sex from temp where id = sid;

    select v_name, v_age, v_sex;

end;

call getRecord(1);

Ø 函數

函數類似於預存程序，只是調用方式不同

例如：select max(age) from temp;

建立函數：

create function addAge(age int) returns int

     return age + 5;

使用函數：

select addAge(age) from temp;

刪除函數：

drop function if exists addAge;

drop function addAge;

顯示建立文法：

show create function addAge;

Ø 遊標

聲明遊標：declare cur_Name cursor for select name from temp;

開啟遊標：open cur_Name;

Fetch遊標：fetch cur_Name into @temp;

關閉遊標：close cur_Name;

樣本：

CREATE PROCEDURE cur_show()

BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE v_id, v_age INT;

  DECLARE v_name varchar(20);

  DECLARE cur_temp CURSOR FOR SELECT id, name, age FROM temp;

  DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done = 1;

  OPEN cur_temp;

  REPEAT

    FETCH cur_temp INTO v_id, v_name, v_age;

    IF NOT done THEN

       IF isnull(v_name) THEN

          update temp set name = concat(‘test-json‘, v_id) where id = v_id;

       ELSEIF isnull(v_age) THEN

          update temp set age = 22 where id = v_id;

       END IF;

    END IF;

  UNTIL done END REPEAT;

  CLOSE cur_temp;

END

Ø 觸發器

觸發器分為insert、update、delete三種觸發事件類型

還有after、before觸發時間

建立觸發器：

create trigger trg_temp_ins

before insert

on temp for each row

begin

insert into temp_log values(NEW.id, NEW.name);

end//

刪除觸發器：

drop trigger trg_temp_ins

 

MySQL學習筆記二