Mysql燕十八學習筆記

Mysql三大列類型

1.數(shù)值型

整型

Tinyint>Smallint^Mediumint>Int>Bigint

小數(shù)型

Float(D,M),Decimal(D,M)

2.字符串型

Char(M)

Varchar(M)

Text文本類型

3.日期時間類型

Date日期

Time時間

Datetime時間類型

Year年類型

一.整型列的字節(jié)及存儲范圍

從數(shù)學上討論tinyint,建表時既能足夠存放，又不

浪費空間。

即：

(1).占據(jù)空間

(2).存儲范圍

Tinyint微小的列類型，1字節(jié)。

類型字節(jié)最小值最大值

（帶符號/無符號）（帶符號/無符號）

Tinyint1-128127

0255

Smallint2-3276832767

065535

Mediumint3-2-232123T

02124T

Int4-2^312131T

02132T

Bigint8-2飛32163T

02"64-1

通過占用字節(jié)和所存范圍分關系合理的選擇類型。

例：Tinyint

經(jīng)測試，Tinyint的默認為有符號-128?127之間，當越界時自動

取相應的邊界值。若要存0~255無符號時：

列的可選屬性：

Tinyint（M）unsignedzerofill.

M：寬度（在。填充的時候才有意義）

Unsigned：無符號類型（非負），影響存儲范圍

Zerofill：0填充（默認無符號）

列可以聲明默認值，而且推薦聲明默認值notnulldefault0；

二.小數(shù)型/浮點型、定點型

Float(M,D):M代表總位數(shù)，D代表小數(shù)位

(6,2)為例：-9999.99->9999.99

Decimal(D,M):

三.字符型列

Char：定長，char(M),M代表寬度，即：可容納的字符數(shù)(K=M<=255

Varchar：變長,0<=M<=65535(約2W—6W個字符受字符集影響)

Text文本串，(約2W—6W個字符受字符集影響)

區(qū)別在哪呢？

char定長：M個字符，如果存的小于M個字符，實占M個字符

varchar：M個字符，存的小于M個字符，設為N,N<M,實占N

個字符

因此，char定長若存空格，讀取時會丟失。而變長不會。

Char利用率小于等于100%,而varchar永遠小于100%,1-2個

字節(jié)用于標志實存字符長度。

Char(M)如何占據(jù)M個字符寬度？

答：如果實際存儲內容不足M個，則后面加空格補齊，取出來的

時候再把后面的空格去掉，(所以如果內容后面有空格，將會被

清除)

選擇原則：

1.空間利用效率(M固定選Char)

2.速度

速度上：定長速度快些,

四.日期時間列類型

Date日期

Time時間

Datatime時間類型

Year年類型

1.Year類型：1個字節(jié)表示1901-2155年【0000年

表示錯誤時】

如果輸入兩位，“00—69”表示2000—2069,

如果輸入兩位“70—99”表示1970—1999

如果記得麻煩，輸入四位就行了、

2.Date類型:典型格式，1992-08T2

'1000-01-01''9999-12-3T

3.Time類型，典型格式hh:mm:ss

'-838:59:59''+838:59:59'

4.Datetime典型格式：'1989-05-0612:23:34'

41000-01-0100:00:00,(9999-12-3123:59:59'

注意：

在開發(fā)中很少使用日期時間類型來表示一個需要精確到秒的

歹U，原因：雖然日期時間類型能夠精確到秒，而且方便查看。

但是計算不便。用時間戳來表示。

時間戳：1970-01-0100:00:00到當前的秒數(shù)

面試1:

當表示性別時，可用1或。表示男女，如，

可能會問到為什么不用enum枚舉，原因：enum不符合關系型數(shù)

據(jù)庫設計理念，而且字節(jié)上不比tinyint少。

實例：

增刪改查之案例過程分析

用戶注冊表單收集，提交數(shù)據(jù)，注冊頁面收集到表單的數(shù)據(jù)后，

形成insert語句，user表插入該條數(shù)據(jù)，用戶注冊完成

前臺用戶中心，用戶新昵稱，根據(jù)新昵稱和用戶id,形成update

語句，完成昵稱修改

管理后臺點擊會員列表，此時，網(wǎng)頁形成select語句，查詢出

所有會員的數(shù)據(jù)，完成會員的查看

管理員后臺選中某用戶并且刪除，捕捉此用戶的id,根據(jù)用戶

id形成相應的delete語句

執(zhí)行delete語句，完成用戶的刪除。

階段總結(-)

列類型的概念

數(shù)值型

整型tinyintsmallintmediumintintbigint

整型的unsigned代表無符號,zerofill代表0填充,

M代表寬度(在0填充)

浮點型/定點型float(M,D)unsignedM精度，即總位

數(shù)，D代表小數(shù)位decimal比float更精確。

字符型

Char(M)定長，可存儲的字符數(shù)，M<=255

Varchar(M),變長,可存儲的字節(jié)數(shù),M<=65535

Char及varchar的不同點

Char(M),實占M個字符，不夠M個右側補空格，取出時，在去除

右側空格，導致右側真有空格時會丟失。

Varchar(M),有卜2個字節(jié)來標記真實的長度，

日期時間型

Year1901-2155,如果輸2位，'00-69'之間+2000「70-99'

之間+1900

DateYYYY-MM-DD,范圍在1000-01-019999-12-31

TimeHH:ii:ss,范圍在-838:59:59838:59:59

DatetimeYY-MM-DDHH:ii:ss1000-01-01

00:00:009999-12-3123:59:59

開發(fā)中的一個問題一精確到秒的時間表示方式，不是用

datetime,而是用int來表示時間戳

用時間戳方便計算，而且方便格式化成不同的顯示樣式。

建表語句

Createtable表明(

列名稱，列類型［列屬性］［默認值］,

)engine引擎名charset字符集

增：insert

答：往哪張表增，增那幾列，各為什么值？

Insertinto表名

（列1,列2,..列N）

Values

（值1,值2..值N）

**如果不聲明插入的列，則默認插入所有列。

改：update

答：修改哪張表，修改那幾列，修改成什么值？在哪幾行

上生效？

Update表名

Set

列1=值1,

列2=值2,

歹UN=值N

Where表達式；

刪：delete

刪除哪張表的數(shù)據(jù)，刪除哪些行？

Deletefrom表名

Where表達式

查：select*from表名

查詢的五種語句：where,group,having,orderby,limit

一.Where條件查詢

比較運算符

1.In〈值1,值2,值3…..值N〉，等于1N任意一個。

例：selectgoods_idfromgoodswherecat_idin(4,5);

2.Between值1and值2,表示在值1和值2之間

例：selectgoods_idfromgoodswherecatidbetween

1and5;

邏輯運算符

1.Not邏輯非

例：selectgood_idfromgoodswherecat_idnot

in(4,5);

2.Or邏輯或

3.And邏輯及

模糊查詢：

案例：想查找‘諾基亞'開頭的所有商品

Like像，%通配任意字符_單個字符

Selectgoods_idfromgoodswheregoods_namelike'諾基

亞％'；

Selectgoodsidfromgoodswheregoods_namelike'諾基

亞_'；

二.Group分組查詢

Groupby

作用：把行按字段分組

語法：groupbycoll,col2…colN

運用場合：常見于統(tǒng)計場合，如按欄目計算帖子數(shù)，統(tǒng)計

每個人的平均成績等。

Group及統(tǒng)計函數(shù)

Max：求最大，

min：求最小，

sum：求總和，

avg：求平均，

count：求總行數(shù)

練習：

三.Having

*******例題：

只用一個select,不用子查詢和左連接。

四.Orderby

Orderby排序功能

接一個或多個字段對查詢結果進行排序

知識點在本項目案例的運用

對欄目的商品按價格由高到低或由低到高排序

知識點的運用場合描述

各種排序場合，如取熱點新聞，發(fā)帖狀元等。

多重排序：

Limit在語句的最后起到了限制條目的作用。

Limitoffset,[N]

offset：偏移量，N：條目；如果不寫，則從頭開始寫。即：limit

0,N;

Truncatetable清空表

思考：取出每個欄目下最貴的商品

解法一：

首先建個臨時表g2,將goods表導入g2,

此時，g2中每個欄目的第一個goods_id就是該欄目下最貴的那

個，再取出每個欄目下的第一個。

解法二：

思想類似解法一，將select后的結果看作是一張表。

五.良好的理解模型

1.Where表達式：把表達式放在行中，看表達式是否為

真，

2.歹U：理解成變量，可以運算

3.取出結果：可以理解成一張臨時表

六.子查詢

1.Where型子查詢

2.From型子查詢

3.Exist型子查詢

一.Where型子查詢

是指把內層的查詢結果作為外層查詢的比較條件。

二.From型子查詢

把內層的查詢結果當成表供外層繼續(xù)查詢

使用了from+where子查詢。

三.Exists子查詢

把外層的查詢結果拿到內層，看內層的查詢是否

成立。

階段總結二

查select

Where表達式

表達式在哪一行成立，哪一行就取出來

=,\-/<>,>,<,>=,<=,in,betweenand,or,not

Groupby

分組，一般和統(tǒng)計函數(shù)配合使用

Max,min,avg,sum,count

Having

數(shù)據(jù)在表中，表在硬盤或內存以文件形式存在

Where就是針對表文件發(fā)揮作用的

查詢的結果，也可以看成一張表，其文件一般臨時存在緩沖區(qū)

針對查詢的結果發(fā)揮作用

Orderby

作用：排序

可以針對字段，升序[asc],降序【desc】。

有可能一個字段拍不出結果，可以選用其他字段繼續(xù)排列

Limit

限制條目

Limit[offset][N]

Offset：偏移量

N：取出條目，取出3-5條，limit2,3.

子查詢

Where型，內層的查詢結果作為外層查詢的比較條件

From型子查詢

把內層的查詢結果供外層再次查詢

注意：內層的查詢結果看成臨時表，加as臨時表名

Exists型子查詢

把外層的查詢結果帶入到內層，看內層是否成立。

Union：聯(lián)合

作用：把兩次或多次查詢結果合并

要求：兩次查詢的列數(shù)一致。

推薦：查詢的每一列，相對應的列類型也一樣。

可以來自于多張表。多條sql語句union后的列名以第一個sql

語句的列名為準。

例：

利用from型子查詢，并配合sum聚合函數(shù)來實現(xiàn)。

如果不同的語句取出的行，有完全相同，（每個列的值都相同）

那么相同的行將會合并（去重復）

如果不去重復，可以加all。

如果子句中有orderby,limit,需加（）。

推薦排序放到所有子句之后，即對最終合并的結果進行排序。

在子句中，orderby配合limit使用才有意義。不配合limit

使用，會被語法分析器優(yōu)化分析時，去除。

連接查詢概念及左連接語法

子查詢就是在原有的查詢語句中，嵌入新的查詢，來得到我們想

要的結果集。一般根據(jù)子查詢的嵌入位置分為，where型子查詢,

from型子查詢。

學習內容

左連接，右連接，內連接

作用：

從兩張或多張表中取出相關聯(lián)的數(shù)據(jù)

應用案例：

非常廣泛。如取文章及其所在欄目名稱

去個人信息及其所發(fā)布的文章等。

數(shù)據(jù)庫中以表為單位存儲數(shù)據(jù)。

左連接

以左表為準，去右表找匹配數(shù)據(jù)，找不到匹配，用null補齊

右連接

內連接

如何記憶：

1.左右連接可以相互轉化

2.推薦將右連接轉化為左連接，兼容性好一些

3.內連接：查詢左右表都有的數(shù)據(jù)，即：不要左右連接中null

的那一部分，是左右連接的交集。

思考：能否查出左右連接的并集呢？

目前不能，目前的mysql不支持外連接?？梢允褂胾nion來

達到目的。

三表連接

面試題：

回顧建表語句：

Createtable表名（

列名稱列類型［列屬性］［默認值］,列聲明

列名稱列類型［列屬性］［默認值］,

列名稱列類型［列屬性］［默認值］,

列名稱列類型［列屬性］［默認值］，

）charset=utf8/gbk…

表增加列，修改列，刪除列

增加列：

Altertable表名add列聲明

增加的列默認在表的最后一列

可以用after來聲明新增的列在那一列后面。

Aftertable表名add列聲明after..

如果新增放在最前面？

Aftertable表名add列聲明first

修改列：

Aftertable表名change舊列名列聲明

刪除列：

Altertable表名drop列名

視圖：view

查詢每個欄目下最貴的商品

Selectgoods_id,goods_name,cat_id,shop_pricefrom

goodsorderbycat_idascshop_pricedesc;

查詢結果當成一張表看，

如果某個查詢結果出現(xiàn)的非常頻繁，也就是拿這個結果當作

子查詢出現(xiàn)的非常頻繁

Createtableg2likegoods;

Insertintog2select...

上面兩句是想保存一個查詢結果到表里面，供其他查詢用

視圖的定義：

視圖是有查詢結果形成的一張?zhí)摂M表，

視圖的創(chuàng)建語法：

Createview視圖名select語句；

為什么要視圖?

答：

1.可以簡化查詢，某一個復雜的查詢，翻來覆去的查。

簡化查詢的例子：

2.進行權限控制

把表的權限封閉，但是開放相應的視圖權限，視圖里只開放

部分數(shù)據(jù)。

3.大數(shù)據(jù)分表時可以用到

比如，表的行數(shù)超過200萬時，就會變慢

可以把一張表的數(shù)據(jù)拆成四張表來存放

比如：News表

Newsid,1,2,3,4

Newsl,news2,news3,news4表

把一張表的數(shù)據(jù)分散到四張表里，分散的方法有很多，最常

用的可以用id取模來計算（把數(shù)據(jù)均勻的分到幾張表里）

Id%4+1=[1,2,3,4]

比如$_GET['id']=17,17%4+1=2,$tabieName=

'news'.'2'

Select*fromnews2whereid=17;

還可以用視圖，把四張表形成一張視圖

Createviewnewsasselectfromnews2union….

視圖的刪除：

Dropview；

視圖的修改：

Alterviewasselect…

視圖及表的關系：

視圖的數(shù)據(jù)來源于表，若表的數(shù)據(jù)改變，則視圖的數(shù)據(jù)自動

改變。是表的查詢結果。

修改視圖的數(shù)據(jù)時，表的數(shù)據(jù)也會跟著修改。

但是視圖也并不總是增刪改的，視圖的數(shù)據(jù)及表的數(shù)據(jù)有嚴

格的一一對應時，可以修改。對于insert還應注意，視圖必

須包含表中沒有默認值的列

視圖的算法algorithm

Algorithm=merge/temptable/underfined

Merge:當引用視圖時，引用視圖的語句及定義視圖的語句合

并

Temptable：當引用視圖時，根據(jù)視圖的創(chuàng)建語句建立一個臨

時表

Underfined：未定義，

Merge意味著視圖只是一個規(guī)則，語句規(guī)則，當查詢視圖時，

把查詢視圖的語句及創(chuàng)建時的語句等合并，分析，形成一條

語句。

而temptable是根據(jù)創(chuàng)建語句瞬間創(chuàng)建一張臨時表，然后查

詢視圖的語句從該臨時表查數(shù)據(jù)。

字符集

字符集及校對集

Createtable表名（

列聲明

）charsetutf8；

mysql的字符集設置非常靈活

可以設置服務器默認字符集

數(shù)據(jù)庫默認字符集

表默認字符集

列字符集

如果某一個級別沒有指定字符集，則繼承上一級，

以表聲明utf8為例：

存儲的數(shù)據(jù)在表中，最終是utf8；

1.告訴服務器，我發(fā)送的數(shù)據(jù)是什么編碼的。

character_set_client

2.告訴轉換器，轉換成什么編碼的

charactersetconnection

3.查詢的結果用什么編碼character_set_results

如果以上三者都為字符集N,則可以簡寫為

SetnamesN;

推論：什么時候會亂碼？

1.Client聲明及事實不符

2.Result及客戶端頁面不符的時候

什么時間將會丟失數(shù)據(jù)？

校對集：字符集的排序規(guī)則

一個字符集可以有一個或者多個排序規(guī)則

Utf8默認utf8_general_ci不區(qū)分大小寫

階段總結（三）

Union的用法

合并查詢的結果，取select結果的并集

對于重復的行去掉，如果不去重復，可以用unionall

Union的要求:

各select查出的列數(shù)一致

如果子句中用了orderbylimit

那么子句要用小括號（）抱起來

如果子句只用orderby沒有l(wèi)imit

Orderby被優(yōu)化掉，不起作用

左連接、右連接、內連接

Selectta.列,tb.列

Taleft/right/innerjointb

011｛比列=坨.列（關系不一定是等于）

Where…

左連接及右連接:

可以相互轉化

Aleftbbrighta

內連接：innerjoin

左右連接的交集，兩張表能相互匹配上的行

表的管理列的增刪改

給表增加列：

Altertable表名add列聲明[after/first]

修改表的列：

Altertable表名change待修改列名列聲明

刪除別的列：

Altertable表名drop列名

視圖view

視圖是一張?zhí)摂M的表，沒有真實的數(shù)據(jù)存在，只是一張及表的一

種查詢產生的關系。

語法：

Create[algorithm=merge/temptable/underfined]view

viewnameasselect??,..;

Merge是將創(chuàng)建視圖時的語句和查詢視圖的語句合并成一條語

句。

Temptable：是創(chuàng)建一張臨時表，merge是一條語句，用的時候

較多。

字符集和校對集

客戶端一》【轉換器】--》服務器

客戶端使用的字符集：setcharacter_set_c1ient=gbk/utf8

轉換器轉換后的字符集:setcharacter_set_connection=

gbk/utf8

返回給客戶端的字符集：setcharacter_set_results=

gbk/utf8

總和：setnamesgbk/utf8;

校對集：就是對排序的規(guī)則；

一種字符集對應一種或多種校對集；

Createtable()charsetutf8；

觸發(fā)器：

學習目標：

觸發(fā)器的定義：

觸發(fā)器的應用場合：

掌握觸發(fā)器的創(chuàng)建語法：

會創(chuàng)建簡單的觸發(fā)器：

1.觸發(fā)器：trigger,(槍擊，扳機，引線)見識某種情況，并

觸發(fā)某種操作。能監(jiān)視增刪改，觸發(fā)操作：增刪改

2.當向一張表中添加或刪除記錄時，需要在相關表中進行同步

操作。比如：當一個訂單產生時，訂單所購的商品的庫存量

相應減少。

當表上某列數(shù)據(jù)的值及其他表中的數(shù)據(jù)有聯(lián)系時。

比如：當某客戶進行欠款消費，可以在生成訂單時通過設計

觸發(fā)器判斷該客戶的累計欠款是否超過了最大限度。

當需要對某張表進行跟蹤時。

比如：當有新訂單產生時，需要及時通知相關人員進行處理,

此時可以在訂單表上設計添加觸發(fā)器加以實現(xiàn)。

3.監(jiān)視地點：table,監(jiān)視事件：insert/update/delete,觸發(fā)

時間：after/before,觸發(fā)事件：insert/update/delete

首先需要修改：

Delimiter$，遇到$結束語句開始執(zhí)行。

創(chuàng)建觸發(fā)器語法：

CreatetriggertriggerName

After/beforeinsert/update/deleteon表名

Foreachrow

Begin

Sql語句

End

刪除觸發(fā)器的語法：

DroptriggertriggerName

如何在觸發(fā)器中引用行的值，

對于insert而言，新增的行用new來表示，行中的每一列的

值，用new.列名來表示。

對于delete來說，原本有一行，后來被刪除想引用被刪除的

這一行，用。Id來表示，old列名就可以引用被刪行中的值。

對于update來說，原本有一行，修改后還是那一行，修改前的

數(shù)據(jù)用old來表示，old列名引用被修改之前行中的值，修改后

的數(shù)據(jù)用new來表示，new列名引用被修改之前前行中的值，

例：

刪除一個訂單，庫存量相應增加

修改訂單的數(shù)量時，庫存相應改變

這里只是修改了訂單的數(shù)量，若也要修改gid,即訂單商品類

另U，則需要修改觸發(fā)器：

觸發(fā)器里after和before的區(qū)別

After是先完成數(shù)據(jù)的增刪改，再觸發(fā)，觸發(fā)中的語句晚于增

刪改，不能對前面的增刪改產生影響

Before是先完成觸發(fā)，再增刪改，觸發(fā)的語句先于監(jiān)視增刪

改，我們有機會判斷，修改即將發(fā)生的操作

典型案例：

對于所下訂單進行判斷，如果訂單數(shù)量＞5就是認為惡意訂單,

強制把所訂的商品數(shù)量改成5

查看所有的觸發(fā)器

Showtriggers;

存儲引擎

表里的數(shù)據(jù)存儲在硬盤上，具體是如何存儲的？

存儲的方式有很多，同樣的一張表的數(shù)據(jù)對于用戶來說，無

論什么樣的存儲引擎，用戶看到的數(shù)據(jù)是一樣的，對于服務

器來說是不同的。

數(shù)據(jù)庫對同樣的數(shù)據(jù)，有不同的存儲方式和管理方式，在

mysql中，稱為存儲引擎。

常用的表的引擎有myisam和innodb

Myisam：批量插入速度快，不支持事務，鎖表

innoDB：批量插入速度相對較慢，支持事務，鎖行。

全文索引，目前mysql5.5都已經(jīng)支持。

討論innoDB支持事務，myisam不支持事務。

事務：

事務的acid特性

通俗的說，一組操作要么都成功執(zhí)行，要么都不執(zhí)行。原

子性(atomicity)

在所有操作都沒有執(zhí)行完畢之前，其他會話不能夠看到中間

改變的過程。隔離性(isolation)

事務發(fā)生之前和發(fā)生之后數(shù)據(jù)的總額依然匹配。一致性

(consistency)

事務產生的影響不能夠撤銷持久性(durability)

如果出現(xiàn)了錯誤，事務也不允許撤銷，只能夠“補償事務”

原子性體現(xiàn)的是不可分割性，不可見性。

轉賬

李三支出500,李三-500

趙四收到500,趙四+500

關于事務的引擎：選用innoDB/bdb

查看mysql服務器的模式：

Showvariableslike'zmodez’;

語法：

開啟事務：

Sql…

Sql…

Commit