? function 函數(shù)
函數(shù)的作用比較大，一般多用在select查詢語句和where條件語句之后。按照函數(shù)返回的結(jié)果，
可以分為：多行函數(shù)和單行函數(shù)；所謂的單行函數(shù)就是將每條數(shù)據(jù)進(jìn)行獨(dú)立的計(jì)算，然后每條數(shù)據(jù)得到一條結(jié)果。
如：字符串函數(shù)；而多行函數(shù)，就是多條記錄同時(shí)計(jì)算，得到最終只有一條結(jié)果記錄。如：
sum
、avg等
多行函數(shù)也稱為聚集函數(shù)、分組函數(shù)，主要用于完成一些統(tǒng)計(jì)功能。MySQL的單行函數(shù)有如下特征：
單行函數(shù)的參數(shù)可以是變量、常量或數(shù)據(jù)列。單行函數(shù)可以接受多個(gè)參數(shù)，但返回一個(gè)值。
單行函數(shù)就是它會(huì)對(duì)每一行單獨(dú)起作用，每一行（可能包含多個(gè)參數(shù)）返回一個(gè)結(jié)果。
單行函數(shù)可以改變參數(shù)的數(shù)據(jù)類型。單行函數(shù)支持嵌套使用：內(nèi)層函數(shù)的返回值是外層函數(shù)的參數(shù)。
?
單行函數(shù)可以分為：
類型轉(zhuǎn)換函數(shù)；
位函數(shù)；
流程控制語句；
加密解密函數(shù)；
信息函數(shù)
單行函數(shù)
?
1、 char_length字符長度
select
char_length
(tel)
from
user
;
?
2、 sin函數(shù)
select
sin(age)
from
user
;
select
sin(1.57);
?
3、 添加日期函數(shù)
select
date_add(
'2010-06-21'
,
interval
2
month
);
interval是一個(gè)關(guān)鍵字，2 month是2個(gè)月的意思，2是數(shù)值，month是單位
select
addDate(
'2011-05-28'
, 2);
在前面的日期上加上后面的天數(shù)
?
4、 獲取當(dāng)前系統(tǒng)時(shí)間、日期
select
curdate();
select
curtime();
?
5、 加密函數(shù)
select
md5(
'zhangsan'
);
?
6、
Null
處理函數(shù)
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
；類似于三目運(yùn)算符
?
7、
case
流程函數(shù)
case函數(shù)是一個(gè)流程控制函數(shù)，可以接受多個(gè)參數(shù)，但最終只會(huì)返回一個(gè)結(jié)果。
select
name,
age,
(
case
sex
when
1
then
'男'
when
0
then
'女'
else
'火星人'
end
) sex
from
user
;
?
組函數(shù)
組函數(shù)就是多行函數(shù)，組函數(shù)是完成一行或多行結(jié)果集的運(yùn)算，最后返回一個(gè)結(jié)果，而不是每條記錄返回一個(gè)結(jié)果。
1、 avg平均值運(yùn)算
select
avg
(age)
from
user
;
select
avg
(
distinct
age)
from
user
;
?
2、
count
記錄條數(shù)統(tǒng)計(jì)
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進(jìn)行條件過濾
不能在where子句中過濾組，where子句僅用于過濾行。過濾group by需要having
不能在where子句中用組函數(shù)，having中才能用組函數(shù)
select
count
(*)
from
user
group
by
sex
having
sex <> 2;
?
? 多表查詢和子查詢
數(shù)據(jù)庫的查詢功能最為豐富，很多時(shí)候需要用到查詢完成一些事物，而且不是單純的對(duì)一個(gè)表進(jìn)行操作。而是對(duì)多個(gè)表進(jìn)行聯(lián)合查詢，
MySQL中多表連接查詢有兩種規(guī)范，較早的SQL92規(guī)范支持，如下幾種表連接查詢：
等值連接
非等值連接
外連接
廣義笛卡爾積
SQL99規(guī)則提供了可讀性更好的多表連接語法，并提供了更多類型的連接查詢，SQL99支持如下幾種多表連接查詢：
交叉連接
自然連接
使用using子句的連接
使用on子句連接
全部連接或者左右外連接
?
SQL92的連接查詢
SQL92的連接查詢語法比較簡(jiǎn)單，多將多個(gè)table放置在from關(guān)鍵字之后，多個(gè)table用“，”隔開；
連接的條件放在where條件之后，與查詢條件直接用and邏輯運(yùn)算符進(jìn)行連接。如果條件中使用的是相等，
則稱為等值連接，相反則稱為非等值，如果沒有任何條件則稱為廣義笛卡爾積。
廣義笛卡爾積：
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查詢，無需條件，默認(rèn)條件是將2個(gè)table中的相同字段作為連接條件，如果沒有相同字段，查詢的結(jié)果就是空。
select
s.*, c.name
from
student s
natural
join
classes c;
?
3、using子句連接查詢：using的子句可以是一列或多列，顯示的指定兩個(gè)表中同名列作為連接條件。
如果用natural join的連接查詢，會(huì)把所有的相同字段作為連接查詢。而using可以指定相同列及個(gè)數(shù)。
select
s.*, c.name
from
student s
join
classes c
using
(id);
?
4、
join
… on連接查詢，查詢條件在on中完成，每個(gè)on語句只能指定一個(gè)條件。
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;
?
子查詢
子查詢就是指在查詢語句中嵌套另一個(gè)查詢，子查詢可以支持多層嵌套。子查詢可以出現(xiàn)在2個(gè)位置：
from關(guān)鍵字之后，被當(dāng)做一個(gè)表來進(jìn)行查詢，這種用法被稱為行內(nèi)視圖，因?yàn)樵撟硬樵兊膶?shí)質(zhì)就是一個(gè)臨時(shí)視圖
出現(xiàn)在where條件之后作為過濾條件的值
?
子查詢注意點(diǎn)：
子查詢用括號(hào)括起來，特別情況下需要起一個(gè)臨時(shí)名稱
子查詢當(dāng)做臨時(shí)表時(shí)（在from之后的子查詢），可以為該子查詢起別名，尤其是要作為前綴來限定數(shù)據(jù)列名時(shí)
子查詢用作過濾條件時(shí)，將子查詢放在比較運(yùn)算符的右邊，提供可讀性
子查詢作為過濾條件時(shí)，單行子查詢使用單行運(yùn)算符，多行子查詢用多行運(yùn)算符
?
將from后面的子查詢當(dāng)做一個(gè)table來用：
select
*
from
(
select
id, name
from
classes) s
where
s.id
in
(1, 2);
當(dāng)做條件來用：
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);
? 操作符和函數(shù)
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函數(shù)，返回第一個(gè)非null的值
select
coalesce
(
null
, 1);
select
coalesce
(1, 1);
select
coalesce
(
null
, 1);
select
coalesce
(
null
,
null
);
?
3、 當(dāng)有2個(gè)或多個(gè)參數(shù)時(shí)，返回最大的那個(gè)參數(shù)值
select
greatest(2, 3);
select
greatest(2, 3, 1, 9, 55, 23);
select
greatest(
'D'
,
'A'
,
'B'
);
?
4、 Least函數(shù)，返回最小值，如果有null就返回null值
select
least(2, 0);
select
least(2, 0,
null
);
select
least(2, 10, 22.2, 35.1, 1.1);
?
5、 控制流函數(shù)
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字符串函數(shù)
select
ascii(
'A'
);
select
ascii(
'1'
);
?
7、 二進(jìn)制函數(shù)
select
bin(22);
?
8、 返回二進(jìn)制字符串長度
select
bit_length
(11);
?
9、 char將值轉(zhuǎn)換成字符，小數(shù)取整四舍五入
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'
);
? 事務(wù)處理
動(dòng)作
開始事務(wù)：
start
transaction
提交事務(wù)：
commit
回滾事務(wù)：
rollback
設(shè)置自動(dòng)提交：
set
autocommit 1 | 0
atuoCommit系統(tǒng)默認(rèn)是1立即提交模式；如果要手動(dòng)控制事務(wù)，需要設(shè)置set autoCommit 0;
這樣我們就可以用commit、rollback來控制事務(wù)了。
?
在一段語句塊中禁用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才會(huì)結(jié)束。然后就恢復(fù)到原來的autocommit模式；
?
不能回滾的語句
有些語句不能被回滾。通常，這些語句包括數(shù)據(jù)定義語言（DDL）語句，比如創(chuàng)建或取消數(shù)據(jù)庫的語句，
和創(chuàng)建、取消或更改表或存儲(chǔ)的子程序的語句。
您在設(shè)計(jì)事務(wù)時(shí)，不應(yīng)包含這類語句。如果您在事務(wù)的前部中發(fā)布了一個(gè)不能被回滾的語句，
則后部的其它語句會(huì)發(fā)生錯(cuò)誤，在這些情況下，通過發(fā)布ROLLBACK語句不能 回滾事務(wù)的全部效果。
?
一些操作也會(huì)隱式的提交事務(wù)
如alter、
create
、
drop
、rename
table
、lock
table
、
set
autocommit、
start
transaction
、
truncate
table
等等,
在事務(wù)中出現(xiàn)這些語句也會(huì)提交事務(wù)的
事務(wù)不能嵌套事務(wù)
事務(wù)的保存點(diǎn)
Savepoint
pointName/
Rollback
to
savepoint
pointName
一個(gè)事務(wù)可以設(shè)置多個(gè)保存點(diǎn)，rollback可以回滾到指定的保存點(diǎn)，恢復(fù)保存點(diǎn)后面的操作。
如果有后面的保存點(diǎn)和前面的同名，則刪除前面的保存點(diǎn)。
Release savepoint會(huì)刪除一個(gè)保存點(diǎn)，如果在一段事務(wù)中執(zhí)行commit或rollback，則事務(wù)結(jié)束，所以保存點(diǎn)刪除。
?
Set
Transaction設(shè)計(jì)數(shù)據(jù)庫隔離級(jí)別
SET
[
GLOBAL
|
SESSION
]
TRANSACTION
ISOLATION
LEVEL
{
READ
UNCOMMITTED |
READ
COMMITTED | REPEATABLE
READ
| SERIALIZABLE }
本語句用于設(shè)置事務(wù)隔離等級(jí)，用于下一個(gè)事務(wù)，或者用于當(dāng)前會(huì)話。
在默認(rèn)情況下，
SET
TRANSACTION會(huì)為下一個(gè)事務(wù)（還未開始）設(shè)置隔離等級(jí)。
如果您使用GLOBAL關(guān)鍵詞，則語句會(huì)設(shè)置全局性的默認(rèn)事務(wù)等級(jí)，
用于從該點(diǎn)以后創(chuàng)建的所有新連接。原有的連接不受影響。使用SESSION關(guān)鍵測(cè)可以設(shè)置默認(rèn)事務(wù)等級(jí)，
用于對(duì)當(dāng)前連接執(zhí)行的所有將來事務(wù)。
默認(rèn)的等級(jí)是REPEATABLE READ全局隔離等級(jí)。
?
? 注釋
select
1+1; # 單行注釋
select
1+1;
-- 單行注釋
select
1
/* 多行注釋 */ + 1;
? 基本數(shù)據(jù)類型操作
字符串
select
'hello'
,
'"hello"'
,
'""hello""'
,
'hel'
'lo'
,
'\'
hello
';
select "hello", "'hello
'", "'
'hello'
'", "hel""lo", "\"hello";
?
\n換行
select 'This\nIs\nFour\nLines
';
?
\轉(zhuǎn)義
select 'hello \ world!
';
select 'hello \world!
';
select 'hello \\ world!
';
select 'hello \
' world!'
;
? 設(shè)置數(shù)據(jù)庫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
;
? 存儲(chǔ)過程
創(chuàng)建存儲(chǔ)過程：
delimiter
//
create
procedure
get
(
out
result
int
)
begin
select
max
(age)
into
result
from
temp;
end
//
調(diào)用存儲(chǔ)過程：
call
get
(@temp);
查詢結(jié)果：
select
@temp;
?
刪除存儲(chǔ)過程：
drop
procedure
get
;
?
查看存儲(chǔ)過程創(chuàng)建語句：
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);
? 函數(shù)
函數(shù)類似于存儲(chǔ)過程，只是調(diào)用方式不同
例如：
select
max
(age)
from
temp;
?
創(chuàng)建函數(shù)：
create
function
addAge(age
int
)
returns
int
return
age + 5;
?
使用函數(shù)：
select
addAge(age)
from
temp;
?
刪除函數(shù)：
drop
function
if
exists
addAge;
drop
function
addAge;
?
顯示創(chuàng)建語法：
show
create
function
addAge;
? 游標(biāo)
聲明游標(biāo)：
declare
cur_Name
cursor
for
select
name
from
temp;
打開游標(biāo)：
open
cur_Name;
Fetch游標(biāo)：
fetch
cur_Name
into
@temp;
關(guān)閉游標(biāo)：
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
? 觸發(fā)器
觸發(fā)器分為insert、
update
、delete三種觸發(fā)器事件類型
還有after、before觸發(fā)時(shí)間
創(chuàng)建觸發(fā)器：
create
trigger
trg_temp_ins
before
insert
on
temp
for
each
row
begin
insert
into
temp_log
values
(
NEW
.id,
NEW
.name);
end
//
?
刪除觸發(fā)器：
drop
trigger
trg_temp_ins
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
