MySQL必知必會

本文連結

<a href="http://alex-my.xyz/books/database/mysql%e5%bf%85%e7%9f%a5%e5%bf%85%e4%bc%9a">http://alex-my.xyz/books/database/mysql必知必會</a>

<a href="http://blog.csdn.net/alex_my/article/details/72357498">http://blog.csdn.net/alex_my/article/details/72357498</a>

唯一辨別表中每行的這個列(這組列)稱為主鍵。

應該總是定義主鍵，雖然并不總是需要主鍵。

任意兩行都不具有相同的主鍵值。

每一行都必須具有一個主鍵值，不可為null。

show databases;

show tables;

show columns from table;

show status;

show create database database;

show create table table;

除非确實需要絕大部分列或全部列，否則最好不使用通配符* 來擷取所有的列。檢索不需要的列通常會降低搜尋和應用程式的性能。

二者都可以達到去重的效果。

distinct把列中的全部内容存儲到記憶體中，可以了解為一個hash，最後的到hash中的key就可以得到結果。比較耗記憶體。

group by先将列排序，然後去重。排序比較耗時間。

從0開始算

以上兩條指令都表示從3開始的5行。

預設為升序 <code>asc</code>。

以上語句中, account_id預設為升序排列，也可以寫上asc。

以上兩句都是模糊比對使用者名以t開頭。

%: 比對任意0個或者多個字元。

_: 一個_比對1個任意字元，且必須有一個。

mysql僅支援多數正規表達式實作的一個很小的子集。

<code>.</code>表示比對任意一個字元。

like和regexp差別:

like 要求整個列比對(使用通配符除外), regexp隻要列中某個片段比對即可。

假設有使用者名為s123。則以下例子中, like沒有得到結果。

使用|功能上類似于select中的or語句。多個or語句可以使用正規表達式替代，更簡潔。

相當于

也可以添加^，來比對除指定以外的内容

為了比對特殊字元，必須用<code>\\</code>為前導。

<code>\\</code>也用來引用具有特殊含義的字元

特殊字元

含義

\\f

換頁

\\n

換行

\\r

回車

\\t

制表

\\v

縱向制表

多數正規表達式使用<code>\</code>轉義特殊字元，以便能使用這些字元本身。但mysql要求用<code>\\</code>。

mysql解釋一個，正規表達式解釋另外一個。

為了友善工作，可以使用預定義的字元集

類

說明

[:alnum:]

任意字母和數字 ([a-za-z0-9])

[:alpha:]

任意字元 ([a-za-z])

[:blank:]

空格和名額 (\\t)

[:cntrl:]

ascii控制字元 (ascii 0~31, 127)

[:digit:]

任意數字 ([0-9])

[:graph:]

與[:print:]相同，但不包括空格

[:lower:]

任意小寫字母 ([a-z])

[:print:]

任意可列印的數字

[:punct:]

同時不在[:alnum:][:cntrl:]中的任意字元

[:space:]

包括空格在内的任意空白字元 ([\\t\\n\\r\\t\\v])

[:upper:]

任意大寫字母 ([a-z])

[:xdigit:]

任意十六進制數字 ([a-fa-f0-9])

示例:

字元

*

0個或多個比對

+

1個或多個比對 ({1, })

?

0個或1個比對 ({0, 1})

{n}

指定數目的比對

{n, }

不少于指定數目的比對

{n, m}

比對數目範圍, m 不超過255

元字元

^

文本的開始

$

文本的結尾

[[:&lt;:]]

詞的開始

[[:&gt;:]]

詞的結尾

* 示例1

假設要找到以字母開頭的使用者名

以上語句将會在文本任意位置進行查找比對，并不符合以字母開頭這依規定. 這裡可以使用<code>^</code>

存儲在表中的資料不一定是應用程式所需要的。我們可以直接從資料庫中檢索出轉換，計算或格式化過的資料。而不是檢索出原始資料然後在應用程式中重新格式化。

表中含有role_id, name字段，應用程式需要這樣的格式 role_name(role_id)

rtrim()函數去掉了值右邊的所有空格。其餘有ltrim(), trim()

拼接處的結果沒有名字，應用程式沒法引用。可以使用别名解決這個問題。

這樣，應用程式就可以使用info這個列，就像它本來就存在于表中一樣。

假設使用者充值了money(元),每元可以換成10個代币,這裡通過計算直接得出獲得的總代币。

函數

示例

結果

char_length(s)

傳回字元串s字元數

select char_length(‘abc 你好’);

6

length(s)

傳回字元串s的長度

select length(‘abc 你好’);

10

concat(s1,s2,…)

合并為一個字元串

select concat(‘hello’, ’ abc’);

hello abc

concat_ws(x, s1, s2,…)

同concat,但會加上x

select concat_ws(‘+’, ‘1’, ‘2’, ‘3’);

1+2+3

insert(s1, x, length, s2)

将字元串s2替換s1的x位置開始長度為length的字元串

select insert(‘abcdefg’, 2, 3, ‘123’);

a123efg

upper(s)

将字元串s的所有字母變成大寫字母

select upper(‘abcd’);

abcd

lower(s)

将字元串s的所有字母變成小寫字母

select lower(‘abcd’);

left(s, n)

傳回字元串s的前n個字元

select left(‘abcdef’, 3);

abc

right(s, n)

傳回字元串s的後n個字元

select right(‘abcdef’, 3);

def

lpad(s1, length, s2)

字元串s2來填充s1的開始處，使字元串長度達到length

select lpad(‘abc’, 8, ‘123’);

12312abc

rpad(s1, length, s2)

字元串s2來填充s1的結尾處，使字元串的長度達到length

select rpad(‘abc’, 8, ‘123’);

abc12312

ltrim(s)

去掉字元串s開始處的空格

select ltrim(’ abc ‘);

‘abc ‘

rtrim(s)

去掉字元串s結尾處的空格

select rtrim(’ abc ‘);

’ abc’

trim(s)

去掉字元串s開始和結尾處的空格

select trim(’ abc ‘);

‘abc’

trim(s1 from s)

去掉字元串s中開始處和結尾處的字元串s1

select trim(‘-’ from ‘—hello–’);

hello

repeat(s, n)

将字元串s重複n次

select repeat(‘abc’, 3);

abcabcabc

space(n)

傳回n個空格

select space(3);

‘   ’

replace(s, s1, s2)

将字元串s2替代字元串s中的字元串s1

select replace(‘abcdef’, ‘abc’, ‘12’);

12def

strcmp(s1, s2)

比較字元串s1和s2

select strcmp(‘abc’, ‘abc’);

select strcmp(‘abc’, ‘abcd’);

-1

select strcmp(‘abc’, ‘ab’);

1

substring(s, n, length)

擷取從字元串s中的第n個位置開始長度為length的字元串

select substring(‘abcdefg’, 2, 3);

bcd

mid(s, n, length)

同substring

select mid(‘abcdefg’, 3, 2);

cd

locate(s1, s)

從字元串s中擷取s1的開始位置

select locate(‘de’, ‘abcdefg’);

4

position(s1, s)

select position(‘de’ in ‘abcdefg’);

instr(s, s1)

select instr(‘abcdefg’, ‘de’);

reverse(s)

将字元串s的順序反過來

select reverse(‘a,b,c,d,e,f’);

f,e,d,c,b,a

elt(n, s1, s2, …)

傳回第n個字元串

select elt(3, ‘abc’, ‘def’, ‘ghi’, ‘jkl’);

ghi

export_set(…)

見示例

select export_set(6, ‘y’, ‘n’, ‘_’, 3);

n_y_y

field(s, s1, s2, …)

傳回第一個與字元串s比對的字元串位置

select field(‘b’, ‘a’, ‘b’, ‘c’);

2

find_in_set(str, str_list)

select find_in_set(‘4’, ‘6,5,4,3,2,1’);

3

abs(x)

傳回x的絕對值

ceil(x)

傳回大于或等于x的最小整數

ceiling(x)

floor(x)

傳回小于或等于x的最大整數

rand()

傳回0-&gt;1的随機數

rand(x)

傳回0-&gt;1的随機數，x值相同時傳回的随機數相同

sign(x)

傳回x的符号，x是負數、0、正數分别傳回-1、0和1

pi()

傳回圓周率(3.141593）

truncate(x, y)

傳回數值x保留到小數點後y位的值(不會四舍五入)

round(x)

傳回離x最近的整數

round(x, y)

保留x小數點後y位的值(四舍五入)

pow(x, y)

傳回x的y次方

power(x, y)

sqrt(x)

傳回x的平方根

exp(x)

傳回e的x次方

mod(x, y)

傳回x除以y以後的餘數

log(x)

傳回自然對數(以e為底的對數)

log10(x)

傳回以10為底的對數

radians(x)

将角度轉換為弧度

degrees(x)

将弧度轉換為角度

sin(x)

求正弦值(參數是弧度)

asin(x)

求反正弦值(參數是弧度)

cos(x)

求餘弦值(參數是弧度)

acos(x)

求反餘弦值(參數是弧度)

tan(x)

求正切值(參數是弧度)

atan(), atan2()

求反正切值(參數是弧度)

cot()

求餘切值(參數是弧度)

curdate(), current_date()

傳回目前日期

select current_date();

2017-05-11

curtime(), current_time

傳回目前時間

select current_time();

19:01:11

now()

傳回目前日期和時間

select now();

2017-05-11 19:01:30

current_timestamp()

同上

localtime()

sysdate()

localtimestamp()

unix_timestamp()

以unix時間戳的形式傳回目前時間

select unix_timestamp();

1494500521

unix_timestamp(d)

将時間d以unix時間戳的形式傳回

select unix_timestamp(‘2017-05-11 19:02:01’);

from_unixtime(d)

将unix時間戳的時間轉換為普通格式的時間

select from_unixtime(1494500521);

2017-05-11 19:02:01

utc_date()

傳回utc日期

select utc_date();

utc_time()

傳回utc時間

select utc_time();

11:06:13

month(d)

傳回日期d中的月份值，1-&gt;12

select month(‘2017-05-11’);

5

monthname(d)

傳回日期當中的月份名稱

select monthname(‘2017-05-11’);

may

dayname(d)

傳回日期d是星期幾

select dayname(‘2017-05-11 19:07:12’);

thursday

dayofweek(d)

日期d今天是星期幾，1星期日，2星期一

select dayofweek(‘2017-05-11’);

weekday(d)

日期d今天是星期幾，0表示星期一，1表示星期二

select weekday(‘2017-05-11’);

week(d)，weekofyear(d)

計算日期d是本年的第幾個星期，範圍是0-&gt;53

select week(‘2017-05-11’);

19

dayofyear(d)

計算日期d是本年的第幾天

select dayofyear(‘2017-05-11’);

131

dayofmonth(d)

計算日期d是本月的第幾天

select dayofmonth(‘2017-05-11’);

11

quarter(d)

傳回日期d是第幾季節，傳回1-&gt;4

select quarter(‘2017-05-11’);

hour(t)

傳回t中的小時值

select hour(‘2017-05-11 19:11:23’);

minute(t)

傳回t中的分鐘值

select minute(‘2017-05-11 19:11:23’);

second(t)

傳回t中的秒鐘值

select second(‘2017-05-11 19:11:23’);

23

extract(type from d)

從日期d中擷取指定的值，type指定傳回的值(見下文)

select extract(week from ‘2017-05-11 19:11:23’);

time_to_sec(t)

将時間t轉換為秒

select time_to_sec(‘19:11:23’);

69083

sec_to_time(s)

将以秒為機關的時間s轉換為時分秒的格式

select sec_to_time(69083);

19:11:23

to_days(d)

計算日期d距離0000年1月1日的天數

select to_days(‘2017-05-11 19:11:23’);

736825

from_days(n)

計算從0000年1月1日開始n天後的日期

select from_days(736825);

datediff(d1,d2)

計算日期d1-&gt;d2之間相隔的天數

select datediff(‘2017-05-11’, ‘2017-05-12’);

adddate(d,n)

計算其實日期d加上n天的日期

select adddate(‘2017-05-11 19:11:23’, 3);

2017-05-14 19:11:23

adddate(d，interval expr type)

計算起始日期d加上一個時間段後的日期

select adddate(‘2017-05-11 19:11:23’, interval 3 hour);

2017-05-11 22:11:23

date_add(d,interval expr type)

select date_add(‘2017-05-11 19:11:23’, interval 10 hour);

2017-05-12 05:11:23

subdate(d,n)

日期d減去n天後的日期

select subdate(‘2017-05-12 05:11:23’, 13);

2017-04-29 05:11:23

subdate(d,interval expr type)

日期d減去一個時間段後的日期

select subdate(‘2017-04-29 05:11:23’, interval 10 minute);

2017-04-29 05:01:23

addtime(t,n)

時間t加上n秒的時間

select addtime(‘2017-04-29 05:01:23’, 30);

2017-04-29 05:01:53

subtime(t,n)

時間t減去n秒的時間

select subtime(‘2017-04-29 05:01:53’, 30);

date_format(d,f)

按表達式f的要求顯示日期d

select date_format(‘2017-04-29 05:01:23’, ‘%y-%m-%d’);

2017-04-29

time_format(t,f)

按表達式f的要求顯示時間t

select time_format(‘2017-04-29 05:01:23’, ‘%r’);

05:01:23 am

* type的值可以為:

<code>microsecond second minute hour day week month quarter year second_microsecond minute_microsecond minute_second hour_microsecond hour_second hour_minute day_microsecond day_second day_minute day_hour year_month</code>

if (expr, v1, v2);

ifnull(v1, v2);

如果v1不為null, 傳回v1，否則傳回v2

version()

傳回資料庫的版本号

select version();

5.7.11

connection_id()

傳回伺服器的連接配接數

select connection_id();

13

database()

傳回目前資料庫名

select database();

database-learn

user()

傳回目前使用者

select user();

root@localhost

charset(s)

傳回字元串s的字元集

select charset(“123”);

utf8

collation(s)

傳回字元串s的字元排列方式

select collation(“a123”);

utf8_general_ci

last_insert_id()

傳回最近生成的auto_increment值

select last_insert_id();

假設要擷取使用者的充值次數，最低充值額度，最高充值額度，平均充值額度，可以用以下指令:

以上得出的是總的資訊，如果要擷取每個使用者的這些資訊，就可以使用分組了。

以上按照每個使用者來計算結果。

需要注意的是，group by 必須出現在 where 之後，order by 之前

可以使用with rollup得到彙總的值

以上在在結果的最後，會附上總的結果。

假設隻需要得到充值2次(包含)以上使用者的資料，則需要使用having來過濾。

注意having跟group by後面。

也可以同時使用where和having。

以上通過where新增了條件，2017年以來充值的。

當sql_mode為only_full_group_by需要注意

檢視sql_mode值

結果:

在sql_mode=only_full_group_by的模式下，以下句子報錯

錯誤: <code>select list is not in group by clause and contains nonaggregated column ...</code>

<code>表中的列</code>，出現在select中時，也得出現在group by中。

同樣，order by也需要注意這個問題。

假設要得出充值使用者的使用者資訊

在select語句中，子查詢總是從内向外處理。

需要保證where語句中需要和子select語句中有相同數目的列。二者名稱可以不相同。

假設要得出使用者的充值次數(user_pay)以及使用者資訊(user)

同9.2 假設要得出使用者的充值次數(user_pay)以及使用者資訊(user), 以下兩種方法都可以獲得結果。

使用where子句

使用inner join

ansi sql規範首選inner join。

外聯結使用outer join來表示。

必須在outer 前加上left或right關鍵字。outer可以省略不寫。

<code>left: 表示選中outer左側表的所有行。 right: 表示選中outer右側表的所有行。</code>

以上資訊擷取使用者的充值資訊，如果有使用者沒有充值，則total_recharge=null。

如果使用right join，如果user_pay中有使用者資料在user表中找不到，則role_id=null。

假設需要擷取充值額度為30的使用者, 以及管道為1001的使用者，使用組合查詢:

組合使用union将獨立的select相連。

每個select查詢都必須包含相同的列，表達式或函數。但次序不必相同。

union從查詢結果中自動去除了重複的行。比如管道1001也有人充值30的。

如果不想被去除重複的行，可以使用union all。

可以在最後一條的select後添加order by語句對結果進行排序。

myisam和innodb(5.6)都支援全文本搜尋。

<code>todo</code>

視圖可以簡化操作，保護資料。

使用create view建立視圖。

使用drop view删除視圖。

這邊使用 create or replace view

建立一個視圖，該視圖從使用者表(user), 使用者充值表(user_pay)擷取使用者基本資訊，總充值額度。

使用show tables可以發現多了一個表，user_pay_info。

建立好視圖後，再想獲得使用者充值資訊，可以通過以下語句:

十分便捷。

雖然表面看是從user_pay_info中擷取資料，但實際上仍然是從user, user_pay中擷取資料。

視圖中存在以下操作，則不可更新:

分組 (group by, having)

聯結

子查詢

聚集函數 (min, count, sum)

distinct

但凡mysql不能确定能夠正确更新到實際表(user, user_pay),則不允許進行視圖更新。

一般，應該将視圖用于檢索，而不用于更新。

相當于調用預編譯好的sql集合。

假設要知道每個使用者的充值總額

以上就建立好了。

需要注意的是，如果在指令行工具中直接用以上語句建立，會報錯。

因為指令行工具也用<code>;</code>做為分隔符，sql語句中也是用<code>;</code>做為分隔符，存在沖突。

是用 delimiter 可以自定義指令行工具的分隔符

以上delimiter告訴指令行工具，使用<code>//</code>做為分隔符。最後一句恢複回<code>;</code>做為分隔符。

以上語句會執行剛才建立的存儲過程。

可以直接使用drop删除

但是，如果不存在user_pay_total()，就會報錯。

是以，建議用以下指令:

參數可以用in, out, inout修飾。

以下語句可以顯示建立存儲過程的鳄魚局

mysql觸發器隻響應以下語句: insert, update, delete

保持每個資料庫觸發器名稱唯一。

隻有表才支援觸發器，視圖，臨時表不支援。

建立tb1, tb2同tb1

建立觸發器，指令行下别忘了 <code>delimiter</code>

以上觸發器在tb1執行insert操作時觸發，會給tb2插入相同的資料。

insert

insert觸發器可在insert執行之前或之後觸發。

在觸發器代碼内，可以使用一個名為new的虛拟表，通路被插入的行。

對于auto_increment列，new在insert之前為0，在insert執行之後為自動生成的值。

update

同insert

delete

delete觸發器可在delete執行之前或之後觸發。

在觸發器代碼内，可以使用一個名為old的虛拟表，通路被插入的行。

從tb1删除的資料會被複制到tb2中。

事務處理可以用來維護資料庫的完整性，保證多個sql指令要麼完全執行，要麼完全不執行。

以上語句中，當删除tb1後，再次查詢，沒有内容。當復原後，資料又出現了。

可以使用commit将事務送出上去執行。

不能回退select, create, drop操作。

當執行commit或rollback後，事務會自動關閉。

複雜的事務進行中，可能存在需要部分回退或者部分送出的情況。

可以使用保留點來處理。

當事務關閉後，保留點會自動釋放。

innodb預設 autocommit=on，即每一條sql語句都是當成一個事務，執行後就送出。

當寫下start transaction時，autocommit的設定就無效了。需要等待commit或rollback來結束事務。

autocommit針對的是每個與mysql的連結，改變其值不會影響其它連結。