Mysql燕十八學(xué)習(xí)筆記

1、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ù)學(xué)上討論tinyint，建表時既能足夠存放，又不浪費(fèi)空間。即：(1). 占據(jù)空間(2). 存儲范圍Tinyint微小的列類型，1字節(jié)。類型字節(jié)最小值最大值（帶符號/無符號）（帶符號/無符號）Tinyint1-1281270255Smallin

2、t2-3276832767065535Mediumint3-223223-10224-1Int4-231231-10232-1Bigint8-263 263-10264-1通過占用字節(jié)和所存范圍分關(guān)系合理的選擇類型。例：Tinyint經(jīng)測試，Tinyint的默認(rèn)為有符號-128127之間，當(dāng)越界時自動取相應(yīng)的邊界值。若要存0255無符號時：列的可選屬性：Tinyint(M) unsigned zerofill.M：寬度（在0填充的時候才有意義）Unsigned：無符號類型（非負(fù)），影響存儲范圍Zerofill： 0填充（默認(rèn)無符號）列可以聲明默認(rèn)值，而且推薦聲明默認(rèn)值 not null def

3、ault 0；二 小數(shù)型/浮點(diǎn)型、定點(diǎn)型Float(M,D):M代表總位數(shù)，D代表小數(shù)位（6,2）為例：-9999.99 >9999.99Decimal(D,M) :三 字符型列Char：定長，char(M),M代表寬度，即：可容納的字符數(shù)0<=M<=255Varchar：變長,0<=M<=65535(約2W6W個字符受字符集影響)Text文本串，(約2W6W個字符受字符集影響)區(qū)別在哪呢？char定長：M個字符，如果存的小于M個字符，實(shí)占M個字符varchar：M個字符，存的小于M個字符，設(shè)為N，N<M，實(shí)占N個字符因此，char定長若存空格，讀取時會丟失

4、。而變長不會。Char利用率小于等于100%，而varchar永遠(yuǎn)小于100%，1-2個字節(jié)用于標(biāo)志實(shí)存字符長度。Char(M)如何占據(jù)M個字符寬度？答：如果實(shí)際存儲內(nèi)容不足M個，則后面加空格補(bǔ)齊，取出來的時候再把后面的空格去掉，（所以如果內(nèi)容后面有空格，將會被清除）選擇原則：1. 空間利用效率（M固定選Char）2. 速度速度上：定長速度快些，四 日期時間列類型Date 日期Time 時間Datatime 時間類型Year 年類型1. Year類型：1個字節(jié)表示1901-2155年【0000年表示錯誤時】如果輸入兩位，“00-69”表示20002069，如果輸入兩位“70-99”表示1970

5、1999如果記得麻煩，輸入四位就行了、2. Date類型：典型格式,1992-08-121000-01-01 à9999-12-313. Time 類型，典型格式 hh:mm:ss-838:59:59à+838:59:594. Datetime 典型格式：1989-05-06 12:23:341000-01-01 00:00:00à 9999-12-31 23:59:59注意：在開發(fā)中很少使用日期時間類型來表示一個需要精確到秒的列，原因：雖然日期時間類型能夠精確到秒，而且方便查看。但是計(jì)算不便。用時間戳來表示。時間戳: 1970-01-01 00:00:00 到當(dāng)

6、前的秒數(shù)面試1：當(dāng)表示性別時，可用1或0表示男女，如，可能會問到為什么不用enum枚舉，原因：enum不符合關(guān)系型數(shù)據(jù)庫設(shè)計(jì)理念，而且字節(jié)上不比tinyint少。實(shí)例：增刪改查之案例過程分析用戶注冊表單收集，提交數(shù)據(jù)，注冊頁面收集到表單的數(shù)據(jù)后，形成insert語句，user表插入該條數(shù)據(jù)，用戶注冊完成前臺用戶中心，用戶新昵稱，根據(jù)新昵稱和用戶id，形成update語句，完成昵稱修改管理后臺點(diǎn)擊會員列表，此時，網(wǎng)頁形成select語句，查詢出所有會員的數(shù)據(jù)，完成會員的查看管理員后臺選中某用戶并且刪除，捕捉此用戶的id，根據(jù)用戶id形成相應(yīng)的delete語句執(zhí)行delete語句，完成用戶的刪除。

7、階段總結(jié)（一）列類型的概念數(shù)值型 整型 tinyint smallint mediumint int bigint 整型的unsigned代表無符號，zerofill代表0填充，M代表寬度（在0填充） 浮點(diǎn)型/定點(diǎn)型 float(M,D) unsigned Mà精度，即總位數(shù)，D代表小數(shù)位 decimal比float更精確。 字符型Char（M）定長，可存儲的字符數(shù)，M<=255Varchar(M) ,變長，可存儲的字節(jié)數(shù)，M<=65535Char與varchar的不同點(diǎn)Char(M),實(shí)占M個字符，不夠M個右側(cè)補(bǔ)空格，取出時，在去除右側(cè)空格，導(dǎo)致右側(cè)真有空格時會丟失。V

8、archar（M） ，有1-2個字節(jié)來標(biāo)記真實(shí)的長度，日期時間型 Year 1901-2155，如果輸2位，00-69之間+2000，70-99之間+1900Date YYYY-MM-DD，范圍在1000-01-01à9999-12-31Time HH :ii:ss,范圍在-838:59:59à838:59:59Datetime YY-MM-DD HH:ii:ss 1000-01-01 00:00:00à9999-12-31 23:59:59開發(fā)中的一個問題精確到秒的時間表示方式，不是用datetime，而是用int來表示時間戳用時間戳方便計(jì)算，而且方便格式化成不

9、同的顯示樣式。建表語句Create table 表明( 列名稱 ，列類型列屬性默認(rèn)值，.)engine 引擎名 charset 字符集增：insert答：往哪張表增，增那幾列，各為什么值？Insert into 表名(列1，列2，.列N)Values（值1，值2.值N）*如果不聲明插入的列，則默認(rèn)插入所有列。改：update答：修改哪張表，修改那幾列，修改成什么值？在哪幾行上生效？Update表名Set列1 = 值1，列2 = 值2，. 列N =值NWhere 表達(dá)式；刪：delete刪除哪張表的數(shù)據(jù)，刪除哪些行？Delete from 表名Where 表達(dá)式查：select * from 表

10、名查詢的五種語句：where，group，having，order by，limit一 Where 條件查詢比較運(yùn)算符1. In <值1,值2,值3.值N>，等于1àN任意一個。例：select goods_id from goods where cat_id in(4,5);2. Between 值1 and 值2，表示在值1和值2之間例：select goods_id from goods where cat_id between 1 and 5;邏輯運(yùn)算符1. Not 邏輯非例：select good_id from goods where cat_id not i

11、n(4,5);2. Or 邏輯或3. And 邏輯與模糊查詢：案例：想查找諾基亞開頭的所有商品Likeà像，% à 通配任意字符 _ à單個字符Select goods_id from goods where goods_name like 諾基亞%；Select goods_id from goods where goods_name like 諾基亞_；二 Group分組查詢Group by作用：把 行按 字段分組語法： group by col1,col2colN運(yùn)用場合：常見于統(tǒng)計(jì)場合，如按欄目計(jì)算帖子數(shù)，統(tǒng)計(jì)每個人的平均成績等。Group與統(tǒng)計(jì)函數(shù)Max

12、：求最大，min： 求最小，sum：求總和，avg：求平均，count：求總行數(shù)練習(xí)：三 Having*例題：只用一個select，不用子查詢和左連接。四 Order byOrder by 排序功能接一個或多個字段對查詢結(jié)果進(jìn)行排序知識點(diǎn)在本項(xiàng)目案例的運(yùn)用對欄目的商品按價格由高到低或由低到高排序知識點(diǎn)的運(yùn)用場合描述各種排序場合，如取熱點(diǎn)新聞，發(fā)帖狀元等。多重排序：Limit在語句的最后起到了限制條目的作用。Limit offset,N offset：偏移量，N：條目；如果不寫，則從頭開始寫。即：limit 0,N;Truncate table清空表思考：取出每個欄目下最貴的商品解法一：首先建個

13、臨時表g2，將goods表導(dǎo)入g2，此時，g2中每個欄目的第一個goods_id就是該欄目下最貴的那個，再取出每個欄目下的第一個。解法二：思想類似解法一，將select后的結(jié)果看作是一張表。五 良好的理解模型1 Where 表達(dá)式：把表達(dá)式放在行中，看表達(dá)式是否為真，2 列：理解成變量，可以運(yùn)算3 取出結(jié)果：可以理解成一張臨時表六 子查詢1. Where型子查詢2. From型子查詢3. Exist型子查詢一 Where型子查詢是指把內(nèi)層的查詢結(jié)果作為外層查詢的比較條件。二 From型子查詢把內(nèi)層的查詢結(jié)果當(dāng)成表供外層繼續(xù)查詢使用了from + where子查詢。三 Exists子查詢把外層的

14、查詢結(jié)果拿到內(nèi)層，看內(nèi)層的查詢是否成立。階段總結(jié)二查selectWhere 表達(dá)式表達(dá)式在哪一行成立，哪一行就取出來=，!=/<>,>,<,>=,<=,in,between and,or,notGroup by分組，一般和統(tǒng)計(jì)函數(shù)配合使用Max，min，avg，sum，countHaving數(shù)據(jù)在表中，表在硬盤或內(nèi)存以文件形式存在Where就是針對表文件發(fā)揮作用的查詢的結(jié)果，也可以看成一張表，其文件一般臨時存在緩沖區(qū)針對查詢的結(jié)果發(fā)揮作用Order by作用：排序可以針對字段，升序【asc】，降序【desc】。有可能一個字段拍不出結(jié)果，可以選用其他字段繼續(xù)排

15、列Limit 限制條目Limit【offset】【N】Offset：偏移量N：取出條目，取出3-5條，limit2,3.子查詢Where型，內(nèi)層的查詢結(jié)果作為外層查詢的比較條件From型子查詢把內(nèi)層的查詢結(jié)果供外層再次查詢注意：內(nèi)層的查詢結(jié)果看成臨時表，加as臨時表名Exists型子查詢把外層的查詢結(jié)果帶入到內(nèi)層，看內(nèi)層是否成立。Union：聯(lián)合作用：把兩次或多次查詢結(jié)果合并要求：兩次查詢的列數(shù)一致。推薦：查詢的每一列，相對應(yīng)的列類型也一樣?？梢詠碜杂诙鄰埍怼６鄺lsql語句union后的列名以第一個sql語句的列名為準(zhǔn)。例：利用from型子查詢，并配合sum聚合函數(shù)來實(shí)現(xiàn)。如果不同的語句取出的

16、行，有完全相同，（每個列的值都相同）那么相同的行將會合并（去重復(fù)）如果不去重復(fù)，可以加all。如果子句中有order by，limit，需加（）。推薦排序放到所有子句之后，即對最終合并的結(jié)果進(jìn)行排序。在子句中，order by 配合limit使用才有意義。不配合limit使用，會被語法分析器優(yōu)化分析時，去除。連接查詢概念與左連接語法子查詢就是在原有的查詢語句中，嵌入新的查詢，來得到我們想要的結(jié)果集。一般根據(jù)子查詢的嵌入位置分為，where型子查詢，from型子查詢。學(xué)習(xí)內(nèi)容 左連接，右連接，內(nèi)連接作用：從兩張或多張表中取出相關(guān)聯(lián)的數(shù)據(jù)應(yīng)用案例：非常廣泛。如取文章及其所在欄目名稱去個人信息及其所

17、發(fā)布的文章等。數(shù)據(jù)庫中以表為單位存儲數(shù)據(jù)。左連接以左表為準(zhǔn)，去右表找匹配數(shù)據(jù)，找不到匹配，用null補(bǔ)齊右連接內(nèi)連接如何記憶：1. 左右連接可以相互轉(zhuǎn)化2. 推薦將右連接轉(zhuǎn)化為左連接，兼容性好一些3. 內(nèi)連接：查詢左右表都有的數(shù)據(jù)，即：不要左右連接中null的那一部分，是左右連接的交集。思考：能否查出左右連接的并集呢？目前不能，目前的mysql不支持外連接?？梢允褂胾nion來達(dá)到目的。三表連接面試題：回顧建表語句：Create table 表名（列名稱 列類型列屬性默認(rèn)值，à列聲明列名稱 列類型列屬性默認(rèn)值，列名稱 列類型列屬性默認(rèn)值，列名稱 列類型列屬性默認(rèn)值，）charset

18、= utf8/gbk表增加列，修改列，刪除列增加列：Alter table 表名 add 列聲明增加的 列默認(rèn)在表的最后一列可以用after來聲明新增的列在那一列后面。After table 表名 add 列聲明 after .如果新增放在最前面？After table 表名 add 列聲明 first修改列：After table 表名 change 舊列名 列聲明刪除列：Alter table 表名 drop 列名視圖：view查詢每個欄目下最貴的商品àSelect goods_id,goods_name,cat_id,shop_price from goods order by

19、 cat_id asc shop_price desc;查詢結(jié)果當(dāng)成一張表看，如果某個查詢結(jié)果出現(xiàn)的非常頻繁，也就是拿這個結(jié)果當(dāng)作子查詢出現(xiàn)的非常頻繁Create table g2 like goods;Insert into g2 select上面兩句是想保存一個查詢結(jié)果到表里面，供其他查詢用視圖的定義：視圖是有查詢結(jié)果形成的一張?zhí)摂M表，視圖的創(chuàng)建語法： Create view 視圖名 select 語句；為什么要視圖?答：1. 可以簡化查詢，某一個復(fù)雜的查詢，翻來覆去的查。簡化查詢的例子：2. 進(jìn)行權(quán)限控制把表的權(quán)限封閉，但是開放相應(yīng)的視圖權(quán)限，視圖里只開放部分?jǐn)?shù)據(jù)。3. 大數(shù)據(jù)分表時可以

20、用到比如，表的行數(shù)超過200萬時，就會變慢可以把一張表的數(shù)據(jù)拆成四張表來存放比如：News表Newsid，1,2,3,4News1，news2，news3，news4表把一張表的數(shù)據(jù)分散到四張表里，分散的方法有很多，最常用的可以用id取模來計(jì)算(把數(shù)據(jù)均勻的分到幾張表里)Id%4+1 =1,2,3,4比如$_GETid = 17, 17%4+1 = 2,$tableName = news.2Select * from news2 where id =17;還可以用視圖，把四張表形成一張視圖Create view news as select from news2 union.視圖的刪除：Dro

21、p view；視圖的修改：Alter view as select 視圖與表的關(guān)系：視圖的數(shù)據(jù)來源于表，若表的數(shù)據(jù)改變，則視圖的數(shù)據(jù)自動改變。是表的查詢結(jié)果。修改視圖的數(shù)據(jù)時，表的數(shù)據(jù)也會跟著修改。但是視圖也并不總是增刪改的，視圖的數(shù)據(jù)與表的數(shù)據(jù)有嚴(yán)格的一一對應(yīng)時，可以修改。對于insert還應(yīng)注意，視圖必須包含表中沒有默認(rèn)值的列視圖的算法algorithmAlgorithm = merge/temptable/underfinedMerge:當(dāng)引用視圖時，引用視圖的語句與定義視圖的語句合并Temptable：當(dāng)引用視圖時，根據(jù)視圖的創(chuàng)建語句建立一個臨時表Underfined：未定義，Merg

22、e意味著視圖只是一個規(guī)則，語句規(guī)則，當(dāng)查詢視圖時，把查詢視圖的語句與創(chuàng)建時的語句等合并，分析，形成一條語句。而temptable是根據(jù)創(chuàng)建語句瞬間創(chuàng)建一張臨時表，然后查詢視圖的語句從該臨時表查數(shù)據(jù)。字符集字符集與校對集Create table 表名（列聲明）charset utf8；mysql 的字符集設(shè)置非常靈活可以設(shè)置服務(wù)器默認(rèn)字符集數(shù)據(jù)庫默認(rèn)字符集表默認(rèn)字符集列字符集如果某一個級別沒有指定字符集，則繼承上一級，以表聲明utf8為例：存儲的數(shù)據(jù)在表中，最終是utf8；1. 告訴服務(wù)器，我發(fā)送的數(shù)據(jù)是什么編碼的。character_set_client2. 告訴轉(zhuǎn)換器，轉(zhuǎn)換成什么編碼的cha

23、racter_set_connection3. 查詢的結(jié)果用什么編碼character_set_results如果以上三者都為字符集N，則可以簡寫為Set names N;推論：什么時候會亂碼？1. Client聲明與事實(shí)不符2. Result與客戶端頁面不符的時候什么時間將會丟失數(shù)據(jù)？校對集：字符集的排序規(guī)則一個字符集可以有一個或者多個排序規(guī)則Utf8默認(rèn)utf8_general_ci不區(qū)分大小寫階段總結(jié)（三）Union的用法合并查詢的結(jié)果，取select結(jié)果的并集對于重復(fù)的行去掉，如果不去重復(fù)，可以用union allUnion的要求：各select查出的列數(shù)一致如果子句中用了order

24、by limit那么子句要用小括號（）抱起來如果子句只用order by 沒有l(wèi)imitOrderby被優(yōu)化掉，不起作用左連接、右連接、內(nèi)連接Select ta.列,tb.列Ta left/right/inner join tbOn ta.列 = tb.列 （關(guān)系不一定是等于）Where左連接與右連接：可以相互轉(zhuǎn)化A left b à b right a內(nèi)連接：inner join左右連接的交集，兩張表能相互匹配上的行表的管理à列的增刪改給表增加列：Alter table 表名add 列聲明after/first修改表的列：Alter table 表名 change 待修改

25、列名 列聲明 刪除別的列： Alter table 表名 drop 列名視圖 view視圖是一張?zhí)摂M的表，沒有真實(shí)的數(shù)據(jù)存在，只是一張與表的一種查詢產(chǎn)生的關(guān)系。語法：Create algorithm = merge/temptable/underfined view viewname as select.;Merge是將創(chuàng)建視圖時的語句和查詢視圖的語句合并成一條語句。Temptable：是創(chuàng)建一張臨時表，merge是一條語句，用的時候較多。字符集和校對集客戶端【轉(zhuǎn)換器】-服務(wù)器客戶端使用的字符集：set character_set_client = gbk/utf8轉(zhuǎn)換器轉(zhuǎn)換后的字符集:set

26、 character_set_connection = gbk/utf8返回給客戶端的字符集：set character_set_results = gbk/utf8總和：set names gbk/utf8;校對集：就是對排序的規(guī)則;一種字符集對應(yīng)一種或多種校對集；Create table（）charset utf8；觸發(fā)器：學(xué)習(xí)目標(biāo)：觸發(fā)器的定義：觸發(fā)器的應(yīng)用場合：掌握觸發(fā)器的創(chuàng)建語法：會創(chuàng)建簡單的觸發(fā)器：1. 觸發(fā)器：trigger，（槍擊，扳機(jī)，引線）見識某種情況，并觸發(fā)某種操作。能監(jiān)視增刪改，觸發(fā)操作：增刪改2. 當(dāng)向一張表中添加或刪除記錄時，需要在相關(guān)表中進(jìn)行同步操作。比如：當(dāng)一個

27、訂單產(chǎn)生時，訂單所購的商品的庫存量相應(yīng)減少。當(dāng)表上某列數(shù)據(jù)的值與其他表中的數(shù)據(jù)有聯(lián)系時。比如：當(dāng)某客戶進(jìn)行欠款消費(fèi)，可以在生成訂單時通過設(shè)計(jì)觸發(fā)器判斷該客戶的累計(jì)欠款是否超過了最大限度。當(dāng)需要對某張表進(jìn)行跟蹤時。比如：當(dāng)有新訂單產(chǎn)生時，需要及時通知相關(guān)人員進(jìn)行處理，此時可以在訂單表上設(shè)計(jì)添加觸發(fā)器加以實(shí)現(xiàn)。3. 監(jiān)視地點(diǎn)：table，監(jiān)視事件：insert/update/delete，觸發(fā)時間：after/before，觸發(fā)事件：insert/update/delete首先需要修改：Delimiter $,遇到$結(jié)束語句開始執(zhí)行。創(chuàng)建觸發(fā)器語法：Create trigger triggerNa

28、meAfter/before insert/update/delete on 表名For each rowBeginSql語句End刪除觸發(fā)器的語法： Drop trigger triggerName如何在觸發(fā)器中引用行的值，對于insert而言，新增的行用new來表示，行中的每一列的值，用new.列名來表示。 對于delete來說，原本有一行，后來被刪除想引用被刪除的這一行，用old來表示，old列名就可以引用被刪行中的值。對于update來說，原本有一行，修改后還是那一行，修改前的數(shù)據(jù)用old來表示，old列名引用被修改之前行中的值，修改后的數(shù)據(jù)用new來表示，new列名引用被修改之前前行

29、中的值，例：刪除一個訂單，庫存量相應(yīng)增加修改訂單的數(shù)量時，庫存相應(yīng)改變這里只是修改了訂單的數(shù)量，若也要修改gid，即訂單商品類別，則需要修改觸發(fā)器：觸發(fā)器里after和before的區(qū)別After是先完成數(shù)據(jù)的增刪改，再觸發(fā)，觸發(fā)中的語句晚于增刪改，不能對前面的增刪改產(chǎn)生影響B(tài)efore是先完成觸發(fā)，再增刪改，觸發(fā)的語句先于監(jiān)視增刪改，我們有機(jī)會判斷，修改即將發(fā)生的操作典型案例：對于所下訂單進(jìn)行判斷，如果訂單數(shù)量>5就是認(rèn)為惡意訂單，強(qiáng)制把所訂的商品數(shù)量改成5查看所有的觸發(fā)器Show triggers;存儲引擎表里的數(shù)據(jù)存儲在硬盤上，具體是如何存儲的？存儲的方式有很多，同樣的一張表的數(shù)據(jù)

30、對于用戶來說，無論什么樣的存儲引擎，用戶看到的數(shù)據(jù)是一樣的，對于服務(wù)器來說是不同的。數(shù)據(jù)庫對同樣的數(shù)據(jù)，有不同的存儲方式和管理方式，在mysql中，稱為存儲引擎。常用的表的引擎有myisam和innodbMyisam：批量插入速度快，不支持事務(wù)，鎖表innoDB：批量插入速度相對較慢，支持事務(wù)，鎖行。全文索引，目前mysql5.5都已經(jīng)支持。討論innoDB支持事務(wù)，myisam不支持事務(wù)。事務(wù)：事務(wù)的acid特性通俗的說，一組操作要么都成功執(zhí)行，要么都不執(zhí)行。à原子性（atomicity）在所有操作都沒有執(zhí)行完畢之前，其他會話不能夠看到中間改變的過程。à隔離性（isola

31、tion）事務(wù)發(fā)生之前和發(fā)生之后數(shù)據(jù)的總額依然匹配。à一致性（consistency）事務(wù)產(chǎn)生的影響不能夠撤銷à持久性（durability）如果出現(xiàn)了錯誤，事務(wù)也不允許撤銷，只能夠”補(bǔ)償事務(wù)”原子性體現(xiàn)的是不可分割性，不可見性。轉(zhuǎn)賬李三 à支出500，李三-500趙四 à收到500，趙四+500關(guān)于事務(wù)的引擎：選用innoDB/bdb查看mysql 服務(wù)器的模式：Show variables like zmodez;語法：開啟事務(wù)：SqlSqlCommit提交/rollback回滾注意：當(dāng)一個事務(wù)commit或者roolback之后，事務(wù)就已經(jīng)結(jié)束。只要事務(wù)沒有提交或回滾，事務(wù)處理都會看不見.事物的基本原理（了解）數(shù)據(jù)庫的備份和恢復(fù)Mysql索引設(shè)有N條隨機(jī)記錄，不用索引，平均查找N/2次，那么用了索引之后？Btree索引，Log2NHash索引，1索引與優(yōu)化之索引的