mysql基礎(chǔ)知識.ppt

1、mysql數(shù)據(jù)庫,學(xué)習(xí)目錄(1): 認(rèn)識數(shù)據(jù)庫和mysql mysql連接 入門語句 詳解列類型 增刪改查 查的5種子語句詳解,mysql數(shù)據(jù)庫,學(xué)習(xí)目錄(2): 連接查詢 子查詢 表引擎與字符集 索引與優(yōu)化 觸發(fā)器 事務(wù) 備份與恢復(fù),1:認(rèn)識數(shù)據(jù)庫,允許不寫,如果不寫,則默認(rèn)插入所有列 見下例:,INSERT INTO 表名 VALUES(值 1, 值 n);,VALUES 對應(yīng)的值的輸入順序與表中字段順序一致,5 增刪改查 之改 update 圖解,改哪張表? 你需要給改哪幾列的值? 分別改為什么值? 在哪些行生效?,update 表名 set 列1 = 新值1, 列2 = 新值2, wh

2、ere expr,5 增刪改查 之改 delete 語法,你要刪哪張表的數(shù)據(jù)? 你要刪掉哪些行?,delete from 表名 where expr,5 增刪改查 之改 select 語法,查哪張表的數(shù)據(jù)? 你要選擇哪些列來查詢? 要選擇哪些行?,select 列1,列2,.列n from 表名 where expr,6 select 5種子句介紹,Where 條件查詢 group by 分組 having 篩選 order by 排序 limit 限制結(jié)果條數(shù),where expression 用法:expression為真,則該行取出 運(yùn)用場合各種條件查詢場合,如按學(xué)號查學(xué)生,按價格查商品

3、,按發(fā)布時間查新聞等,6.1 select 5種子句 之where介紹,6.1 select 5種子句 之where常用運(yùn)算符,6.1 select 5種子句 之where 匹配,取出: 諾基亞系列手機(jī)? 取出諾基亞Nxx系列手機(jī)?,like 模糊匹配 % 通配任意字符 _ 通配單一字符,6.1 select 5種子句 之where 練習(xí),以ecshop庫為基礎(chǔ) 練習(xí) mysql復(fù)習(xí).html 文檔中的 第三部分: 查詢知識 1.1 -1.14題,6.2 select 5種子句 之group與統(tǒng)計函數(shù),max : 求最大 min : 求最小 sum : 求總和 avg : 求平均 count:

4、求總行數(shù),6.2 select 5種子句 之group介紹,group by 作用:把行 按 字段 分組 語法:group by col1,col2,.colN 運(yùn)用場合常見于統(tǒng)計場合,如按欄目計算帖子數(shù), 統(tǒng)計每個人的平均成績等.,6.2 select 5種子句 之group練習(xí),以ecshop庫為基礎(chǔ) 練習(xí) mysql復(fù)習(xí).html 文檔中的 第三部分: 查詢知識 2.1 -2.8題,6.3 select 5種子句 之having介紹,having與where類似,可篩選數(shù)據(jù) where后的表達(dá)式怎么寫,having就怎么寫,where針對表中的列發(fā)揮作用,查詢數(shù)據(jù) having針對查詢結(jié)

5、果中的列發(fā)揮作用,篩選數(shù)據(jù),having與where異同點,6.3 select 5種子句 之having練習(xí)(1),以ecshop庫為基礎(chǔ) 練習(xí) mysql復(fù)習(xí).html 文檔中的 第三部分: 查詢知識 3.1 -3.6題,設(shè)有成績表stu 如下： 姓名科目分?jǐn)?shù) 張三數(shù)學(xué)90 張三語文50 張三地理40 李四語文55 李四政治45 王五政治30 試查詢兩門及兩門以上不及格同學(xué)的平均分,6.3 select 5種子句 之having練習(xí)(2),Order by 排序功能 按一個或多個字段對查詢結(jié)果進(jìn)行排序 知識點在本項目案例的運(yùn)用 對欄目的商品按價格由高到低或由低到高排序 知識點的運(yùn)用場合描述

6、各種排序場合,如取熱點新聞,發(fā)帖狀元等,6.3 select 5種子句 之having練習(xí)(2),6.4 select 5種子句 之order排序,Order by 排序功能 按一個或多個字段對查詢結(jié)果進(jìn)行排序 用法:order by col1,col2,col3 知識點的運(yùn)用場合描述各種排序場合,如新聞按點擊量排序,商品按價格排序等 默認(rèn)排序:升續(xù)排列,Limit 限制條數(shù) limit offset, N,限制結(jié)果取N條 用法: limit 偏移量,取出條目 知識點的運(yùn)用場合描述分頁應(yīng)用中最為典型,如第1頁取1-20條,第2頁取21-40條.,6.5 select 5種子句 之limit 介

7、紹,6.5 select 5種子句 order,limit練習(xí),以ecshop庫為基礎(chǔ) 練習(xí) mysql復(fù)習(xí).html 文檔中的 第三部分: 查詢知識 4.1 -4.5題,7.0 連接查詢,學(xué)習(xí)內(nèi)容: 左連接 右連接 內(nèi)連接,應(yīng)用案例 非常廣泛 如,取文章及所在欄目名稱 取個人信息及所發(fā)布的文章等,作用: 從2張或多張表中, 取出有關(guān)聯(lián)的數(shù)據(jù).,7.1 連接查詢 之笛卡爾積,7.2 連接查詢 之什么是左連接,7.3 連接查詢 之連接查詢語法,左連接的語法. Select Ltable.* ,Rtable.* from Ltable left join Rltable on Ltable.col

8、Name = Rtable.colName 內(nèi)連接的語法. Select Ltable.* ,Rtable.* from Ltable inner join Rltable on Ltable.colName = Rtable.colName,7.4 連接查詢 之左右連接與內(nèi)連接的區(qū)別,1:左右連接可互換 A left join B 等價于B right join A,2:內(nèi)連接是左右連接的交集,3:mysql沒有外連接,7.5 連接查詢 之練習(xí),以ecshop庫為基礎(chǔ) 練習(xí) mysql復(fù)習(xí).html 文檔中的 第三部分: 查詢知識 5.1 -5.3題,8.0 子查詢,子查詢就是在原有的查詢語

9、句中， 嵌入新的查詢,來得到我們想要的結(jié)果集。 一般根據(jù)子查詢的嵌入位置分為， where型子查詢,from型子查詢,8.1 where型子查詢,where型子查詢即是:把內(nèi)層sql語句查詢的結(jié)果作為外層sql查詢的條件.,典型語法: select * from tableName where colName = (select colName from tbName where .) where colName in (select colName from tbName where .),典型案例: 1:查詢最新的一條商品 2:查詢出某大欄目下的所有商品,8.2 exists型子查詢,ex

10、ists即:外層sql查詢所查到的行代入內(nèi)層sql查詢,要使內(nèi)層查詢能夠成立 查詢可以與in型子查詢互換,但效率要高.,典型語法: select * from tablename where exists(select * from tableName where .),典型案例: 1:查詢出某大欄目下的所有商品,8.3 from型子查詢,from型子查詢即:把內(nèi)層sql語句查詢的結(jié)果作為臨時表供外層sql語句再次查詢.,典型語法: select * from (select * from tableName where .) where.,典型案例: 1:查詢出每個欄目下的最新商品 2:BB

11、S中查詢每個欄目下的最新帖子,8.4 子查詢練習(xí),以ecshop庫為基礎(chǔ) 練習(xí) mysql復(fù)習(xí).html 文檔中的 第三部分: 查詢知識 7.1 -7.4題,9.0 存儲引擎與字符集,學(xué)習(xí)目的: 出于速度和安全性的要求,選取合理的存儲引擎. 考慮多語言與移植的問題,選取合理的字符集.,9.1 存儲引擎是什么概念?,設(shè)有張馬虎,李小心兩人,都是地鐵口的自行車管理員. 每天都有很多人來存取自行車,張馬虎的管理方式是:來存自己存,不記錄存的是什么車,取時交5毛,也不檢查取的是否是自己的車. 李小心呢,則在存取自己車時,記錄存車人的特征與自行車的特征,當(dāng)人來取錢車,還要小心核一下,人與車的特征是否對應(yīng)

12、 思考: 張馬虎和李小心誰對業(yè)務(wù)的處理速度更高? 二者誰對自行車管理更安全?,數(shù)據(jù)庫對同樣的數(shù)據(jù),有著不同的存儲方式和管理方式 在mysql中,稱為存儲引擎,9.2 存儲引擎與其特點,9.3 存儲引擎的選擇,文章,新聞等安全性要求不高的,要求選myisam,訂單,資金,賬單,火車票等對安全性要求高的, 要求選用innodb,對于臨時中轉(zhuǎn)表,可以用memory型 ,速度最快,9.4 字符集 (charset),一句話說字符集 字符集就是一個字符二進(jìn)制字節(jié)的映射表,字符集是一套符號和編碼的規(guī)則,不論是在 oracle 數(shù)據(jù)庫還是在 mysql 數(shù)據(jù)庫,都 存在字符集的選擇問題,而且如果在數(shù)據(jù)庫創(chuàng)建

13、階段沒有正確選擇字符集,那么可能在后期 需要更換字符集,而字符集的更換是代價比較高的操作,也存在一定的風(fēng)險,所以,我們推 薦在應(yīng)用開始階段,就按照需求正確的選擇合適的字符集,避免后期不必要的調(diào)整。,9.5 字符集的選擇,1:節(jié)省空間 建議在能夠完全滿足應(yīng)用的前提下,盡量使用小的字符集。 因為更小的字符集意味著能夠節(jié)省空間、 減少網(wǎng)絡(luò)傳輸字節(jié)數(shù),同時由于存儲空間的較小間接的提高了系統(tǒng)的性能。 有很多字符集可以保存漢字,比如 utf8、gb2312、gbk、gb18030 等等, 但是常用的是gb2312 和 gbk。 2:兼容性 因為 gb2312 字庫比 gbk 字庫小,有些偏僻字(例如:洺)

14、不能保存,因此 在選擇字符集的時候一定要權(quán)衡這些偏僻字在應(yīng)用出現(xiàn)的幾率以及造成的影響, 3:在互聯(lián)網(wǎng)上,國際化的趨勢不可避免,且存儲空間已經(jīng)越來海量化, 因此推薦用utf8,如果開發(fā)內(nèi)網(wǎng)系統(tǒng),如內(nèi)部OA等,可以考慮GBK。,9.6 校對規(guī)則collate(了解即可),一句話說校對規(guī)則 校對規(guī)則就是對字符集中的字符的座次表,請你給 A a B b C c D d 按升序排列,9.7 字符集專題-徹底搞定亂碼,客戶端字符集 client,服務(wù)器存儲,查詢,比較時 用的字符集 (connection),查詢結(jié)果字符集 results,9.8 字符集專題-新舊網(wǎng)站并存案例,舊數(shù)據(jù)庫 GBK字符集,舊網(wǎng)

15、站系統(tǒng) GBK編碼,新網(wǎng)站 utf8編碼,思考: 數(shù)據(jù)庫不準(zhǔn)動, 新站又是utf8, 如何保持新網(wǎng)站與 舊數(shù)據(jù)的兼容?,10.0 索引與優(yōu)化 之索引是什么?,該字典前面的目錄全部丟失 請找出華這個字來,如何快速找到某個字 可以給字典加目錄 對數(shù)據(jù)庫來說,索引的作用即是給 數(shù)據(jù)加目錄,10.1 索引與優(yōu)化 之索引算法(了解),設(shè)有N條隨機(jī)記錄,不用索引, 平均查找N/2次,那么用了索引之后呢,btree(二叉樹)索引,hash(哈希)索引 1,10.2 索引與優(yōu)化 之索引的好處與壞處,好外: 加快了查詢速度(select ) 壞處: 降低了增,刪,改的速度(update/delete/inser

16、t) 增大了表的文件大小(索引文件甚至可能比數(shù)據(jù)文件還大),10.3 索引與優(yōu)化 之索引的使用原則,不過度索引 索引條件列(where后面最頻繁的條件比較適宜索引) 索引散列值,過于集中的值不要索引 例如:給性別男,女加索引,意義不大,10.4 索引與優(yōu)化 之索引類型,普通索引 (index) 主鍵索引 (primary key) 唯一索引 (unique) 全文索引 (fulltext),10.5 索引與優(yōu)化 之索引創(chuàng)建語法(1),建表時直接聲明索引: create table tableName ( 列1 列類型 列屬性, . 列N 列類型 列屬性, primary key(列名), in

17、dex (列名), unique(列名), fulltext(列名) )engine xxxxx charset xxxx,10.6 索引與優(yōu)化 之索引創(chuàng)建語法(2),通過修改表建立索引 alter table add index (列名); alter table add unique (列名); alter table add primary key(列名); alter table add fulltext (列名);,10.6 索引與優(yōu)化 之索引刪除語法,刪除主鍵: alter table drop primary key 刪除其他索引: alter table drop index

18、索引名 注:索引名一般是列名,如果不是, 可通過show index from tableName查看索引,10.7 索引與優(yōu)化 之索引練習(xí),1:創(chuàng)建右圖所示的表 并為各列建立索引,分別為 id ,primary key,主鍵索引 name,unique index 唯一索引 school index 普通索引 intro fulltext 全文索引 2:通過alter table drop index 刪掉所有索引 3:通過alter table add index 再分別給4個列增加索引,11.0 觸發(fā)器,學(xué)習(xí)目標(biāo): 觸發(fā)器的定義 觸發(fā)器的應(yīng)用場合 掌握觸發(fā)器的創(chuàng)建語法 會創(chuàng)建簡單觸發(fā)器,

19、進(jìn)行數(shù)據(jù)庫應(yīng)用軟件的開發(fā)時， 我們有時會碰到表中的某些數(shù)據(jù)改變， 希望同時引起其他相關(guān)數(shù)據(jù)改變的需求， 利用觸發(fā)器就能滿足這樣的需求。 它能在表中的某些特定數(shù)據(jù)變化時自動完成某些查詢。 運(yùn)用觸發(fā)器不僅可以簡化程序， 而且可以增加程序的靈活性。,觸發(fā)器是一類特殊的事務(wù) , 可以監(jiān)視某種數(shù)據(jù)操作(insert/update/delete), 并觸發(fā)相關(guān)操作(insert/update/delete),11.1 觸發(fā)器定義,11.2 觸發(fā)器應(yīng)用場合,1.當(dāng)向一張表中添加或刪除記錄時，需要在相關(guān)表中進(jìn)行同步操作。 比如，當(dāng)一個訂單產(chǎn)生時,訂單所購的商品的庫存量相應(yīng)減少。 2.當(dāng)表上某列數(shù)據(jù)的值與其他表

20、中的數(shù)據(jù)有聯(lián)系時。 比如，當(dāng)某客戶進(jìn)行欠款消費(fèi)， 可以在生成訂單時通過設(shè)計觸發(fā)器判斷該客戶的累計欠款是否超出了最大限度。 3.當(dāng)需要對某張表進(jìn)行跟蹤時。 比如，當(dāng)有新訂單產(chǎn)生時，需要及時通知相關(guān)人員進(jìn)行處理， 此時可以在訂單表上設(shè)計添加觸發(fā)器加以實現(xiàn),11.3 觸發(fā)器創(chuàng)建語法 之4要素,監(jiān)視地點 (table),監(jiān)視事件 insert/update/delete,觸發(fā)事件 insert/update/delete,觸發(fā)時間 after/before,11.4 觸發(fā)器創(chuàng)建語法,創(chuàng)建觸發(fā)器的語法 create trigger 觸發(fā)器名稱 after/befor (觸發(fā)時間) insert/upda

21、te/delete (監(jiān)視事件) on 表名 (監(jiān)視地址) for each row begin sql1; . sqlN; end,11.5 觸發(fā)器引用行變量(1),insert,delete,11.5 觸發(fā)器引用行變量(2),update,11.6 觸發(fā)器的刪除,drop trigger triggerName,11.5 觸發(fā)器案例實戰(zhàn),設(shè)計一張商品表 一張訂單表 創(chuàng)建3個觸發(fā)器,作用分別是: 當(dāng)下訂單購買商品時,相應(yīng)商品減少庫存 修改訂單中的商品數(shù)量時,相應(yīng)商品修改庫存 當(dāng)取消某訂單時,相應(yīng)商品增加庫存,12.0 事務(wù) 之事務(wù)的概念,思考: 我去銀行給朋友匯款, 我卡上有1000元, 朋友卡上500元, 我給朋友轉(zhuǎn)賬50元(無手續(xù)費(fèi)), 如果,我的錢剛扣,而朋友的錢又沒加時, 網(wǎng)線斷了,怎么辦?,12.1 事務(wù) 之事務(wù)的ACID特性,原子性(Atomicity)：原子意為最小的粒子，或者說不能再分的事物。 數(shù)據(jù)庫事務(wù)的不可再分的原則即為原子性。 組成事務(wù)的所有查詢必須： 要么全部執(zhí)行，要么全部取消（就像上面的銀行例子）。 一致性(Consistency)：指數(shù)據(jù)的規(guī)則,在事務(wù)前/后應(yīng)保持一致 隔離性(Isolation