1、全國計算機等級考試二級 MySQL 總結第一章數據庫技術的的基本概念與方法1.1 基本概念數據：描述事物的符號記錄數據庫中存儲的基本對象數據的描述形式還不能完全表達其內容， 需要經過解釋數據庫：長期儲存在計算機內的、有組織的、可 共享的數據集合數據庫中的數據按一定的數據模型組 織、描述和存儲，具有較小的冗余度、 較高的數據獨立性和易擴展性數據庫管理系統:數據定義功能、數據操縱功能、 數據庫的運行管理、數據庫的 建立和維護功能、提供方便有 效存儲數據庫信息的接口和 工具數據庫系統：在計算機系統中引入數據庫后的系 統一般由數據庫、數據庫管理系統、 應用開發工具、應用系統、數 據庫管理員和用戶構成1

2、.2 數據庫系統的特點數據結構化數據冗余度小數據共享性好數據獨立性高：物理獨立性、邏輯獨立性 數據由DBMS統一管理和控制1.3 數據庫系統的結構從數據庫用戶試圖的視角來看：三級模式結構 從數據庫管理系統的角度來看：集中式結構、分 布式結構、客戶/服務器結構、并行結構從數據庫系統應用的角度來看：客戶/服務器結 構、瀏覽器/服務器結構1.3.1 數據庫系統的三級模式結構數據庫系統由模式、外模式河內模式三級構成 模式：邏輯模式或概念模式對數據庫中全體數據的邏輯結構和特征 的描述外模式：子模式或用戶模式對數據庫用戶能夠看見和使用的局部 數據的邏輯結構和特征的描述內模式：存儲模式對數據庫中數據物理結構

3、和存儲方法的描述一個數據庫只有一個內模式1.3.2 三級模式結構的兩級映像與兩級數據獨立性外模式/模式映像：同一個模式可以有任意多個 外模式對每一個外模式，數據庫系統都有一個外模式/模式映像邏輯獨立性：模式發生改變, 改變各個外模式/模式映像 作相應改變，外模式保持不 變，應用程序不必修改模式/內模式映像：唯一物理獨立性：存儲結構改變, 改變模式/內模式映像，保持模式不變1.3.3 數據庫系統的運行與維護結構C/S結構：客戶端/前臺/表示層：完成與數據庫 使用者的交互任務服務器/后臺/數據層：主要負責數據管單機方式、網絡方式B/S結構：基于 Web應用的客戶/服務器結構、 三層客戶/服務器結構

4、表示層：瀏覽器處理層/中間層：處理數據庫使用者的具體應用邏輯數據層1.4 數據模型數據模型：對現實世界中數據特征的抽象， 描述 數據的共性數據模型滿足三方面的要求：比較真實的模擬現 實世界、容易為人們所理解、便于在計算機上實 現分為三層：物理層：物理數據模型，數據物理存 儲結構和存儲方法邏輯層：邏輯數據模型/數據模型， 層次模型、網狀模型、關系 模型、面型對象模型概念層：概念數據模型/概念模型， 獨立于任何DBMS,但容易 向DBMS所支持的邏輯數據模型轉換1.4.1 概念模型實體、屬性、碼、域、實體型、實體集、聯系： 一對一、一對多、多對多E-R圖1.4.2 關系模型用二維表結構來表示實體及

5、實體間聯系的模型關系、元組、屬性、域、分量、關系模式：關系 名（屬性1,屬性2, 屬性n）、表、行、列、主鍵、外鍵、數據類型關系規范化：1NF:表中每個列只包含具有原子 性的值，關系的每個分量必 須是不可分的數據項2NF:沒有部分函數依賴，表中不 存在非主鍵的列依賴于組合 主鍵某個部分的現象3NF:沒有傳遞函數依賴，表中不 存在任何非主鍵列與其他非 主鍵列相互關聯的現象BCNF:表中不存在主鍵列對主鍵的部分函數依賴和傳遞函數 依賴低一級的關系模式，通過模式分解 消除數據依賴中不合適的部 分，使模式中的各關系模式達到某種程度的分離， 可以轉換為若干個高一級的 關系模式的集合1.5 數據庫設計需求

6、分析、概念結構設計、邏輯結構設計、物理 結構設計、數據庫實施、數據庫運行與維護1.5.1 概念結構設計1.5.2 邏輯結構設計把E-R圖轉換為關系模型一個實體型轉換為一個關系模式一個一對一的聯系可以轉換為一個獨立的關系 模式，也可以與任意一端對應的關系模式合并 一個一對多的聯系可轉換為一個獨立的關系模 式，也可以與N端對應的關系模式合并 一個多對多的聯系轉換為一個關系模式三個或三個以上實體間的聯系一個多元聯系可以轉換為一個關系模式具有相同碼的關系模式可以合并1.5.3 物理設計對物理結構進行評價的重點是時間和空間的效率第二章MySQL概述2.1 MySQL系統特性2.2 MySQL服務器的安裝

7、和配置MySQL安裝包括典型安裝、定制安裝和完全安裝三種安裝類型在MySQL安裝過程中，若選用“啟用 TCP/IP 網絡”，則MySQL會默認選用的端口號是3306 MySQL安裝成功后，在系統中會默認建立一個 root用戶2.3 服務器的啟動與關閉2.4 MySQL客戶端管理工具MySQL命令行客戶端MySQL客戶端程序與實用工具MySQL圖形化管理工具2.5 MySQL語言結構MySQL語言的組成：數據定義語言：createalter、drop數據操縱語言：selectinsert、update delete數據控制語言：grant、revokeMySQL擴展增加的語言 要素:常 量、變

8、量( 用 戶 變量 、心 刖系 統 變 量、心 刖 )、運 算 符、表 達 式函數第三章數據庫和表3.1 數據庫的創建與使用3.1.1 創建數據庫Create database/schema (if not exists) *(default)chaa c t e rs e t*( de f al t ) c ol l a t e*);3.1.2 選擇數據庫Use *；再用create database句創建了數據庫后)該數庫 不 會 自 動 成 為 當、心 刖數 據 庫3.1.3 修改數據庫Alter database/schema (*) (default) characterset*(

9、d e f a u l t ) c o l l a t e*; ;修改數據庫默認字符集或校對規則，必須刪除并重 新 創 建 使 用 數 據 庫 的 默 認 參 數 的 存 儲 過Update data directory name3.1.4 刪除數據庫Drop database/schema (if exists) *;3.1.5 查看數據庫Show databases/schemas (like* /where *);3.2 創建和操縱表3.2.1 創建表創建定義、表選項、分區選項Create (temporary) table (if not exists)c us t o m e rs(

10、Cust_id int not null auto_increment,Cust_name char(50) not null,Cust_sex char(1) not null default 0,Cust_address char(50) null,Cust_contact char(50) null,Primary key(cust_id)engine = InnoDB;每個表只能有一個 auto_increment歹U)并且它必須 被 索 引其 值 可 以 覆 蓋3.2.2 更新表增加或刪減列、創建或取消索引、更改原有列的數 據重 新 命 名 列 或 表、更 改 表 的 評 注和表的引

11、擎類型Alter (ignore) table * +Add column: alter table mysql_test.customersAdd column cust_city char(10)no tn u l ld e f a u l tW u h aa f tercu s tse x;；First/after類似：add primary key（首先刪除表 中鍵)、a d dforeg nkea d dn dexChange columnalter tablem y s q ite s tu s t o m e r sChange cust_sex sex charn ul ld

12、e f a uAlter column:修改或刪除表中指定列的默認值Alter table mysql_test.customersAlter column cust_city set defaultModify column:只會修改制定列的數據類型,而 不 會 干涉 它 的 列 名Altertablemy sq ites t.cus tm ersModify column cust_namec ha r( 20) fr s t;；First/afterDrop column:類彳以： drop primary key、dropo t eg nk e y、 d r o Pn d eRenam

13、e to: alter table mysql_test.customersRenametoy s q it e s t.b a c k u Pc utomers; ；3.2.3 重命名表Rename table * to *可以同時重命名多個表3.2.4 復制表Create (temporary) table (is not exists) * like*As selec反制表的內容，但索引和完整性約束不會 被 復 制3.2.5 刪除表Drop (temporary) table (if exists) *( r e s t rc t/ c a s c a d e ); ;3.2.6 查看表

14、顯示表的名稱：show (full) tables (from/in *)lk e*/ w h e r e) ; ;顯示表的結構：show (full) columns from/in *r o m/n*) (lk e*/ wh e r e) ; ;Describe/desc *(*)；第四章 表數據的基本操作4.1插入表數據Insert 語 句 ： insert (low_priority/delayed/high_priority) (ignore) (into) * values/value * (on duplicatekey update* 一* . - =,);Insert ()(

15、)(into) * set *=*/default (on duplicate key update );Insert ( ) ( ) (into) * select(on duplicate key update );Replace語句：在插入數據前將表中與待插入的新紀錄相沖突的舊記錄刪除4.2 刪除表數據Delete語句：從單個表中刪除數據： delete (low_priority) (quick) (ignore) from * (where ) (order by ) (limit ); 從多個表中刪除數據： Delete ( ) *,*, from * (where);刪除from

16、子句之前表中 對應的行 Delete ( ) from *, using * (where);刪除from子句之中的表 中對應的行 Delete tb1,tb2 from tb1,tb2,tb3 where tb1.id=tb2.id and tb2.id=tb3.id;Delete from tb1,tb2 using tb1,tb2,tb3 where tb1.id=tb2.id and tb2.id=tb3.id;Truncate語句：從表中刪除所有的行Truncate (table) *;對于參與了索引和視圖的表，不 能用truncate語句刪除數據4.3 修改表數據修改單個表：upd

17、ate (low_priority) (ignore) * set(where ) (order )(limit )；修改多個表：update tb1,tb2 set ='李 明,='張亮'where tb1.id=tb2.id;第五章數據庫的查詢5.1 select 句5.2 列的選擇與指定選擇指定的列定義并使用列 的別名：select cust name, cust_address as '地 址'from mysql_test.customers;自定義的列別名中有空格，必須用單引號將列別名括起來 列別名不允許出現在

18、where子句中替換查詢結果集中的數據：select cust_nameCaseWhencust_sex=M ' then'男'Else'女' End as性別Frommysql_test.customers;計算歹 U 值:select cust_name, cust_id+100 frommysql_test.customers；聚合函數5.3 from子句與鏈接表交叉連接：select * from tb1 cross join tb2;Select * from tb1,tb2;內連接：select - from * (inner) join *

19、 on ;Straight_join類似，其后不能使用using,只能用on設置連接條件相等連接 不等連接自連接：需要在一個表中查找有相同列值的行 要為表指定兩個不同的別名，且對所有 查詢的引用均必須使用表別名限定 自然連接：連接的列在兩張表中名稱相同Natural join 外連接：一定會提供數據行，無論該行能否在另 外一個表中找出相匹配的行 左外連接：left (outer) join,右外連接： right (outer) join5.4 where 子句比較運算 字符串匹配：通配符：%、可在搜索模式后用關鍵字 escape 和一個指定的轉義字符，臨時改變 通配符的作用和意義Select

20、*frommysql_test.customers where cust_addresslike %#_% ' escape #' 文本匹配：rlike/regexp (binary)基本字符匹配：select * from mysql_test.customers where cust_address regexp市'；選擇匹配：分隔符'|'范圍匹配：3-8,a-p特殊字符匹配：轉義字符字符類匹配：將常用的數字、字符等 定義成一個預定義的字符集，直接 使用:upper:重復匹配使用定位符匹配：人文本的開始、$ 文本的結尾判定范圍： between-an

21、d In:最主要的作用是表達子查詢Select * from customerswhere cust_id in (901,902,903);判定空值：is null子查詢：表子查詢、行子查詢、列子查詢、標量 子查詢結合關鍵字in使用：子查詢只返回一 列數據結合比較運算符使用：all、some/any結合關鍵字exists使用：主要用于判定 子查詢的結果集是否為空子查詢通常可以改為表的鏈接，只是兩 者的執行性能會有所差異5.5 group by子句與分組數據Group by * (asc/desc) (with rollup);With rollup包括分組匯總行，以及所有分組的 整體匯總行除

22、聚合函數外，select語句中的每個列都必須在 group by子句中給出Select cust_address,cust_sex,count(*) as 數'From mysql_test.customersGroup by cust_address,cust_sexWith rollup;5.6 having 子句Having子句用來過濾分組與 where子句類似)Having子句中的條件可以 包含聚合函數)where子句中不可 以Select cust_name,cust_addressFrom mysql.customers Group by cust_address,cust

23、_nameHaving count(*)<=3;5.7 order by 子句Order by * (asc/desc);5.8 limit 子句 使用limit子句限制被selec畸句返回的行數第5位客戶開始的3位客戶Select cust_id,cust_name from mysql_test.customersOrder by cust_idLimit 4,3;Select cust_id,cust_nsme from mysql_test.customersOrder by cust_id Limit 3 offset 4;5.9 union語句與聯合查詢Union語句中的每個

24、select查詢必須包含相同的歹I、表達式或聚合函數第一個select語句中被使用的列名會被作為 union語句結果集的列名稱只能使用一條 order by子句或limit子句，且必 須置于最后一條selec儲句之后類有多個 where子句的單條select句，union all 可以完成where子句完成不了的 工作第六章索引數據庫性能調優技術的基礎6.1 索引概述6.2 索引的存儲與分類索引在存儲引擎中實現，每種存儲引擎所支持的 索引類型不一定完全相同索引存儲方式不同：B-樹索引：索引的使用必須 遵循“最左邊前綴”原則哈希索引：memory存儲引 擎，heap存儲引擎支持索引用途：普通索引

25、：關鍵字index/key唯一性索引：unique主鍵：primary,主鍵是一種唯一性索引全文索引：fulltext空間索引單列索引/組合索引索引策略對應的索引類型：聚集索引：一個表只 能有一個聚集索引覆蓋索引：6.3 索引的創建Create index 語 句 ： create (online/offline) (unique/fulltext/spatial) index * (using btree/hash) on *(*(length) (asc/desc),.);不能創建主鍵Create table語句:(constraint *) primary key*(*,)Index/k

26、ey *(*,)(constraint *) unique (index/key) *(*,)Fulltext/spatial (idex/key)*(* .)(constraint *) foreign key*(*, )Alter table語句：add+上述語句6.4 索引的查看Show (index/indexes/keys)from/in * (from/in*) (where );6.5 索引的刪除Drop index語句：drop (online/offline) index * on *;Alter index 語句：alter table * drop primarykey;Drop index *;Drop foreignkey *;6.6 對索引的進一步說明 第七章視圖7.1 視圖概述7.2 創建視圖Create(orreplace)(algorithm=undefi