mysql數據庫課件

1、-. z.數據庫簡介數據庫的概念數據庫Database是按照數據構造來組織、存儲和管理數據的倉庫，每個數據庫都有一個或多個不同的API用于創(chuàng)立，訪問，管理，搜索和復制所保存的數據。我們也可以將數據存儲在文件中，但是在文件中讀寫數據速度相對較慢。所以，現在我們使用關系型數據庫管理系統(tǒng)RDBMS來存儲和管理的大數據量。所謂的關系型數據庫，是建立在關系模型根底上的數據庫，借助于集合代數等數學概念和方法來處理數據庫中的數據。RDBMS即關系數據庫管理系統(tǒng)(Relational Database Management System)的特點：1.數據以表格的形式出現2.每行為各種記錄名稱3.每列為記錄名稱

2、所對應的數據域4.許多的行和列組成一*表單5.假設干的表單組成databaseRDBMS 術語在我們開場學習My SQL 數據庫前，讓我們先了解下RDBMS的一些術語：數據庫:數據庫是一些關聯(lián)表的集合。.數據表:表是數據的矩陣。在一個數據庫中的表看起來像一個簡單的電子表格。列:一列(數據元素) 包含了一樣的數據, 例如郵政編碼的數據。行：一行=元組，或記錄是一組相關的數據，例如一條用戶訂閱的數據。冗余：存儲兩倍數據，冗余可以使系統(tǒng)速度更快。主鍵：主鍵是唯一的。一個數據表中只能包含一個主鍵。你可以使用主鍵來查詢數據。外鍵：外鍵用于關聯(lián)兩個表。復合鍵：復合鍵組合鍵將多個列作為一個索引鍵，一般用于復

3、合索引。索引：使用索引可快速訪問數據庫表中的特定信息。索引是對數據庫表中一列或多列的值進展排序的一種構造。類似于書籍的目錄。參照完整性:參照的完整性要求關系中不允許引用不存在的實體。與實體完整性是關系模型必須滿足的完整性約束條件，目的是保證數據的一致性。My SQL數據庫My SQL是一個關系型數據庫管理系統(tǒng)，由瑞典My SQL AB公司開發(fā)，目前屬于Oracle公司。My SQL是一種關聯(lián)數據庫管理系統(tǒng)，關聯(lián)數據庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度并提高了靈活性。My SQL是開源的，所以你不需要支付額外的費用。My SQL支持大型的數據庫。可以處理擁

4、有上千萬條記錄的大型數據庫。My SQL使用標準的SQL數據語言形式。My SQL可以允許于多個系統(tǒng)上，并且支持多種語言。這些編程語言包括C、C+、Python、Java、Perl、PHP、Eiffel、Ruby和TCL等。My SQL對PHP有很好的支持，PHP是目前最流行的Web開發(fā)語言。My SQL支持大型數據庫，支持5000萬條記錄的數據倉庫，32位系統(tǒng)表文件最大可支持4GB，64位系統(tǒng)支持最大的表文件為8TB。My SQL是可以定制的，采用了GPL協(xié)議，你可以修改源碼來開發(fā)自己的My SQL系統(tǒng)。-. z.My SQL數據庫的安裝與配置所需My SQL安裝包mysql-5.0.41_

5、Setup. E*E安裝方法步驟圖解1運行安裝程序下一步選擇“Custom自定義安裝，下一步這里可以將不需要的安裝刪除，可以更改安裝目錄，下一步單擊“Install按鈕，開場安裝安裝中，耐心等一會兒6.這里是詢問你是否要注冊一個My SQL.的賬號，或是使用已有的賬號登陸My SQL.，一般不需要了，點選“Skip Sign-Up，按“Ne*t略過此步驟。7.安裝完成出現如下界面現在軟件安裝完成了，出現上面的界面，這里有一個很好的功能， My SQL配置向導，不用向以前一樣，自己手動亂七八糟的配置my.INI了，將 “Configure the My SQL Server now前面的勾打上，

6、點“Finish完畢軟件的安裝并啟動 My SQL配置向導。進入配置過程選擇配置方式選擇配置方式，“Detailed Configuration手動準確配置、“Standard Configuration標準配置，我們選擇“Detailed Configuration，方便熟悉配置過程。10.選擇效勞器類型，“Developer Machine開發(fā)測試類，My SQL占用很少資源、“Server Machine效勞器類型，My SQL占用較多資源、“Dedicated My SQL Server Machine專門的數據庫效勞器，My SQL占用所有可用資源，大家根據自己的類型選擇了，一般選“

7、Server Machine，不會太少，也不會占滿。選擇My SQL數據庫的大致用途，“MULTI functional Database通用多功能型，好、“Tran Sac TIONAL Database Only效勞器類型，專注于事務處理，一般、“Non - Tran Sac TIONAL Database Only非事務處理型，較簡單，主要做一些監(jiān)控、記數用，對MYL SAM數據類型的支持僅限于Non - Tran Sac TIONAL，隨自己的用途而選擇了，我這里選擇“Tran Sac TIONAL Database Only，按“Ne*t繼續(xù)。對INNODB Table space進

8、展配置，就是為INNODB 數據庫文件選擇一個存儲空間，如果修改了，要記住位置，重裝的時候要選擇一樣的地方，否則可能會造成數據庫損壞，當然，對數據庫做個備份就沒問題了，這里不詳述。我這里沒有修改，使用用默認位置，直接按“Ne*t繼續(xù)選擇您的的一般My SQL訪問量，同時連接的數目，“Decision Support(DSS)/OLAP20個左右、“Online Transaction Processing(OLTP)500個左右、“Manual Setting手動設置，自己輸一個數，我這里選“Online Transaction Processing(OLTP)，自己的效勞器，應該夠用了，按“

9、Ne*t繼續(xù)是否啟用TCP/IP連接，設定端口，如果不啟用，就只能在自己的機器上訪問My SQL數據庫了，我這里啟用，把前面的勾打上，Port Number：3306，在這個頁面上，您還可以選擇“啟用標準模式Enable Strict Mode，這樣My SQL就不會允許細小的語法錯誤。如果您還是個新手，我建議您取消標準模式以減少麻煩。但熟悉My SQL以后，盡量使用標準模式，因為它可以降低有害數據進入數據庫的可能性。按“Ne*t繼續(xù)這個比擬重要，就是對My SQL默認數據庫語言編碼進展設置，第一個是西文編碼，第二個是多字節(jié)的通用utf8編碼，都不是我們通用的編碼，這里選擇第三個，然后在Cha

10、racter Set那里選擇或填入“GBK，當然也可以用“gb2312，區(qū)別就是GBK的字庫容量大，包括了gb2312的所有漢字，并且加上了繁體字、和其它亂七八糟的字使用My SQL的時候，在執(zhí)行數據操作命令之前運行一次“SET NAMES GBK;運行一次就行了，GBK可以替換為其它值，視這里的設置而定，就可以正常的使用漢字或其它文字了，否則不能正常顯示漢字。按 “Ne*t繼續(xù)。選擇是否將My SQL安裝為windows效勞，還可以指定Service Name效勞標識名稱，是否將My SQL的bin目錄參加到Windows PATH參加后，就可以直接使用bin下的文件，而不用指出目錄名，比方

11、連接，“My SQL.E*E -UUSER Name -PPASS word;就可以了，不用指出My SQL.E*E的完整地址，很方便，我這里全部打上了勾，Service Name不變。按“Ne*t17 這一步詢問是否要修改默認root用戶超級管理的密碼默認為空，“New root password如果要修改，就在此填入新密碼如果是重裝，并且之前已經設置了密碼，在這里更改密碼可能會出錯，請留空，并將“Modify Security Settings前面的勾去掉，安裝配置完成后另行修改密碼，“Confirm再輸一遍內再填一次，防止輸錯。“Enable root access from remot

12、e machines是否允許root用戶在其它的機器上登陸，如果要平安，就不要勾上，如果要方便，就勾上它。最后“Create An Anonymous Account新建一個匿名用戶，匿名用戶可以連接數據庫，不能操作數據，包括查詢，一般就不用勾了，設置完畢，按“Ne*t繼續(xù)。18.確認設置無誤，如果有誤，按“Back返回檢查。按“E*ecute使設置生效。設置完畢，按“Finish完畢My SQL的安裝與配置這里有一個比擬常見的錯誤，就是不能“Start service，一般出現在以前有安裝My SQL的效勞器上，解決的方法，先保證以前安裝的My SQL效勞器徹底卸載掉了；不行的話，檢查是否按

13、上面一步所說，之前的密碼是否有修改，照上面的操作；如果依然不行，將My SQL安裝目錄下的data文件夾備份，然后刪除，在安裝完成后，將安裝生成的 data文件夾刪除，備份的data文件夾移回來，再重啟My SQL效勞就可以了，這種情況下，可能需要將數據庫檢查一下，然后修復一次，防止數據出錯。安裝路徑不要帶有中文！也不能有空格括號之類的，否則就會遇到下列圖顯示的錯誤到此我們的My SQL安裝配置完成，我們需要測試My SQL是否真的安裝配置成功。在我們的CMD 命令行下輸入 net start My SQL看是否能啟動效勞；使用工具NAVI cat Lite for My SQL看是否能夠到M

14、y SQL數據庫-. z.數據庫和表的操作SQL語句分類DQLData Query Language，數據查詢語言 查詢數據庫中的數據 SELECT DMLData Manipulation Language，數據操作語言 插入、刪除和修改數據庫中的數據； INSERT、 UPDATE 、DELETE等； DCL Data Control Language，數據控制語言 用來控制存取許可、存取權限等； GRANT、REVOKE 等； DDL Data Definition Language，數據定義語言 用來建立數據庫、數據庫對象和定義其列 CREATE 、DROP 等事務控制語言Tran S

15、ac TIONAL Control Language, TCL MIT、ROLLBACKSQL語句書寫規(guī)則書寫SQL語句應遵循以下規(guī)則： SQL語句不區(qū)分大小寫字符值和日期值區(qū)分大小寫 SQL語句可以寫成一行或多行關鍵字不能簡寫或分割于多行子句通常置于單獨行這樣更具可讀性并便于編輯 Tab和縮進的使用可以提高程序的可讀性關鍵字最好大寫，其他詞諸如表名、列名使用小寫 SQL 中的注釋： SQL標準：多行注釋： /* */。單行注釋： - My SQL：“#數據庫對象的命名規(guī)則必須以字母開頭可包括數字和三個特殊字符# _ $不要使用My SQL的保存字保存字與關鍵字My SQL 數據庫系統(tǒng)的用戶只

16、能使用，不能用它來定義表的名稱與字段名同一Schema下的對象不能同名列類型 My SQL支持多種列類型：數值類型、日期/時間類型、字符串(字符)類型My SQL中的數值數據類型：數值數據類型-整數數值數據類型-浮點數日期/時間類型字符串類型數據庫的操作查看數據庫： show databases;創(chuàng)立數據庫： create database if not e*ists 數據庫名;刪除數據庫： drop database 數據庫名;使用數據庫 use 數據庫名表的操作顯示當前數據庫中已有的數據表的信息：show tables;查看數據表中各列的信息：DESCRIBE|DESC 表名 列名;查看表

17、的詳細構造：show create table 表名G;備注：如果不加G參數，顯示的結果可能非?；靵y；加上該參數可以使結果更加直觀，易于查看。創(chuàng)立表：CREATE TABLE 表名列名列類型，列名列類型；刪除表：DROP TABLE 表名修改表構造：增加列ALTER TABLE 表名 ADD 列名列類型;修改列類型ALTER TABLE 表名 MODIFY 列名列類型;列改名ALTER TABLE 表名 CHANGE 舊列名新列名列類型；刪除列ALTER TABLE 表名 DROP列名;更改表名ALTER TABLE 表名 RENAME 新表名；RENAME TABLE 表名 TO 新表名；支

18、持一次重命名多個表。RENAME TABLE old_table1 TO tmp_table,new_table TO old_table,tmp_table TO new_table;表的約束：約束約束用于確保數據庫數據滿足特定的商業(yè)規(guī)則。約束是表級的強制規(guī)定，約束可分為列級、表級約束兩種：列級約束是字段定義的一局部，只能應用在一個列上表級約束是獨立于列的定義定義約束的關鍵字constraint。大局部數據庫都支持以下五種約束：NOT NULL非空如果在列上定義了not null，則當插入數據時，必須為列提供數據。 not null約束只能定義在列級；UNIQUE唯一：當定義了唯一約束后，該

19、列值不能是重復的，但是可以為null。被定義的唯一性約束，會自動建立一個唯一性的索引；UNIQUE約束既可以定義在列級，也可以定義在表級；注意：如果字段定義為UNIQUE約束時，允許包含多個NULL值。PRIMARY KEY主鍵用于唯一的標識表行的數據，當定義主鍵約束后，該列不但不能重復而且不能為null。需要說明的是：一*表最多只能有一個主鍵，但是可以有多個unique約束。My SQL中會自動產生主鍵索引；主鍵約束既可以定義在列級，也可以定義在表級；FOREIGN KEY外鍵用于定義主表和從表之間的關系。外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束，當定義外鍵約束后，

20、要求外鍵列數據必須在主表的主鍵列存在或是為null。一個表中可以定義多個外鍵約束；My SQL中會自動產生外鍵鍵索引；外鍵約束既可以定義在列級，也可以定義在表級；CHECK檢查檢查性約束，檢查輸入的每一個數據，只有符合條件的數據才允許插入到表中。check表達式的結果必須是一個布爾值check約束既可以定義在列級也可以定義在表級。所有的存儲引擎均對CHECK子句進展分析，但是忽略CHECK子句。添加約束可增加或刪除約束，但不能直接修改；可使約束啟用和禁用只要是可以使用表級約束語法來定義的約束，都可以通過add constraint來增加該約束。語法：ALTER TABLE tableADD C

21、ONSTRAINT constraint_name type (column);非空約束必須使用MODIFY子句增加ALTER TABLE table MODIFY column type not null;添加約束例如給學生表添加約束：1. 將sid設為主鍵alter table stu add constraint pk_sid primary key(sid);2. 為sname添加唯一約束alter table stu add constraint uq_sname unique(sname);3. 性別默認為男alter table stu modify se* char(2) de

22、fault 男;注意：默認值不是約束，要加默認值可以在建表時添加或修改表構造。4. 性別為非空alter table stu modify se* char(2) not null; 5. 給classId添加外鍵alter table stu add constraint fk_classId foreign key (classId) references classes(classId);刪除定義的約束刪除定義的約束刪除約束例如：刪除主鍵約束 alter table stu drop primary key;注意：如果主鍵列有自增長列，必須先刪除自增長，再刪除主鍵：alter table

23、 stu change tno tno int(11);刪除not null約束alter table stu modify se* char(2) ;刪除唯一約束 alter table stu drop inde* uq_tname;刪除外鍵約束alter table stu drop foreign key fk_stu_sc;表級約束和列級約束列級定義：是在定義列的同時定義約束如在classes表定義主鍵約束：create table classes(classId number(2) constraint pk_cid primary key, -給約束取名字ame varchar2

24、(12);表級定義：是指在定義了所有列后，再定義約束。注意：not null約束只能在列級上定義。以建立stu表時定義主鍵約束和外鍵約束為例：creat table stu(sid number(4), sname varchar2(20), classid number(2),constraint pk_sid primary key(sid),constraint uq_sname unique(sname);自動增長和默認值作業(yè)：創(chuàng)立數據庫：school在其中創(chuàng)立表：stu, course, sc添加相應的約束信息。-. z.數據更新操作1課程學習目標：數據的增加數據的修改數據的刪除插入

25、數據INSERT INSERT語句用于完成各種向數據表中插入數據的功能，可以對列賦值一次插入一條記錄，也可以根據select查詢子句獲得的結果記錄集批量插入指定數據表。語法格式：1.所有字段都插入：INSERT INTO 表名 VALUES(value1，value2，value3.);2.插入局部字段：INSERT INTO 表名 (column1,column2,column3,.)VALUES (value1，value2，value3,.); 注意：1 插入空值可以用null，默認值用default。 2 可以使用last_insert_id()函數獲取上一條insert命令生成的au

26、to_increment值。這是一個面向連接的函數，只對本次會話有效。、插入數據時的考前須知：考前須知1：每次插入一行數據，不可能只插入半行或者幾列數據，因此，插入的數據是否有效將按照整行的完整性的要求來檢驗；考前須知2：每個數據值的數據類型、精度和小數位數必須與相應的列匹配；考前須知3：如果在設計表的時候就指定了*列不允許為空，則必須插入數據；考前須知4：插入的數據項，要求符合檢查約束的要求考前須知5：具有缺省值的列，可以使用DEFAULT缺省關鍵字來代替插入的數值；允許為空值的列，可以使用NULL關鍵字來插入空值。修改數據UPDATE UPDATE語句用于修改表中一列或多列的值，使用whe

27、re子句限制修改的行。語法格式： UPDATE 表名 SET column1=value1,column2=value2,. WHERE 條件;刪除數據DELETEDELETE語句向用戶提供了刪除數據的功能，同UPDATE語句一樣限定表中哪些行將被刪除。語法格式： DELETE FROM 表名 WHERE 條件; TRUNCATE TABLE 表名 ;二者關系：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句一樣，二者均刪除表中的全部行。但 TRUNCATE TABLE速度快，但不可以回滾；DELETE 速度慢，但可以回滾。-. z.第五章數據庫查詢查詢根本

28、語法單表查詢：SELECT FROM WHERE ORDER BY ASC或DESC查詢全部的行和列select * from stu;查詢局部的列：select sid, sname, se*, age from stu;查詢滿足條件的行的局部列：select sid, sname ,se* from stu where age=20;SQL操作符算術運算符：+ - * / % 如：select sal*12 from emp;關系運算符： = = = !=或 between, in, like, is null, 注意：字符類型不區(qū)分大小寫，binary類型區(qū)分大小寫；如果字符串的后面有空

29、白符，則會自動忽略。邏輯運算符：and or not, &, |, !根本查詢怎樣消除重復行：select distinct deptno , job from emp;使用別名：格式：列名別名或列名 as 別名如：select empno, sal*12 “年工資 from emp;或 select empno, sal*12 as “年工資 from emp;注意：1.如果別名中使用特殊字符,或者是強制大小寫敏感,或有空格時,都可以通過為別名添加加雙引號實現。2. where子句中不能使用別名。null值：空值是指不可用、未分配的值空值不等于零或空格任意類型都可以支持空值包括空值的任何算術

30、表達式都等于空字符串和null進展連接運算，得到也是null.如何查詢null值：使用is nullselect * from emp where m is null;select * from emp where m is not null;通配符使用通配符進展模糊查詢：如：查詢名字以S開頭的員工： select * from emp where ename like S%;根本查詢使用邏輯操作指定多個查詢條件select empno,ename,job,sal from emp where ename =SMITH or ename=FORD;在where條件中使用in：如：查詢SMITH

31、, FORT, KING的員工號、工作，薪水select empno,ename,job,sal from emp where ename in(SMITH,FORD,KING);在where條件中使用between and ：select empno,ename,job,sal from emp where sal between 1000 and 2000;注意：數據庫執(zhí)行sql語句時是從右到左的順序，所以盡可能把最優(yōu)化條件寫右邊。根本查詢排序使用order by子句進展排序：排序的類型為兩種升序asc 、降序desc。數字、日期、字符串可以進展排序。NULL認為是排序中的最大值。排序方式

32、：按單個列排序select * from emp order by sal desc;按多列排序select * from emp order by sal desc, empno ;按別名排序select ename,sal*12 “年薪 from emp order by “年薪 asc;一次插入多條語句select into語句語句形式為：create table table2 select vale1, value2 from table1;要求：目標表table2不存在，因為在插入時會自動創(chuàng)立表table2，并將table1中指定字段數據復制到table2中。insert into

33、select from 語句形式為：insert into table2(column1,column2) select col1,col2 from table1;要求：目標表table2已創(chuàng)立好，且從table1表查詢出來的列類型應與table2中列的類型一致。 nsert into select value union select value語句形式為：insert into table2(column1,column2) select value1_1,value1_2 union select value2_1,value2_1 union;作用：將多行值一次性插入到表中。要求：目

34、標表table2已創(chuàng)立好。常用的聚合函數：分組查詢： GROUP BYSELECT 列名條件, MA*(結果列名) AS 要求查詢的結果顯示列名FROM 表名GROUP BY 列名條件分組查詢多列分組：SELECT 列名1條件,列名2條件 , AVG(結果列名) AS 要求查詢的結果顯示列名FROM 表名GROUP BY 列名1條件,列名2條件分組查詢HAVING：HAVING的使用 HAVING通常是在GROUP BY的后面SELECT 列名1條件,列名2條件, AVG(結果列名) AS 平均成績FROM 表名GROUP BY 列名1條件,列名2條件HAVING avg(結果列名)10條件分

35、組查詢：WHERE子句從數據源中去掉不符合其搜索條件的數據GROUP BY子句搜集數據行到各個組中，統(tǒng)計函數為各個組計算統(tǒng)計值HAVING子句去掉不符合其組搜索條件的各組數據行ORDER BY排序使用順序先 WHERE 接著1 GROUP BY 接著2 HAVING 最后 ORDER BY with rollup如果group by子句里只有一個數據列，加上with rollup關鍵字的效果是將在查詢結果的最后一行將自動添加一條總數統(tǒng)計記錄。如果按多列分組，將會進展階段性總和相當于“小計，最后再為全體記錄統(tǒng)計一個最終的總和相當于“總計。例： select sno, count(*),avg(s

36、core) from sc group by sno with rollup;統(tǒng)計函數的使用：SELECT 字段名,group_concat(DISTINCT 要連接的字段 Order BY ASC/DESC 排序字段 Separator 分隔符)多表聯(lián)接查詢內聯(lián)：INNER JOIN外聯(lián)：左外聯(lián)接 (LEFT JOIN)右外聯(lián)接 (RIGHT JOIN)完整外聯(lián)接(FULL JOIN) -My SQL 5.0暫不支持穿插聯(lián)接(CROSS JOIN)內聯(lián)查詢：NNER JOIN叫做等值連接2個表聯(lián)接SQL語法：SELECT * FROM 表名1 INNER JOIN 表名2 ON 表名1.與表

37、名2一樣的字段= 表名2.與表名1一樣的字段多表聯(lián)接：SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段號=表2.字段號) INNER JOIN 表3 ON 表1.字段號=表3.字段號SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段號=表2.字段號) INNER JOIN 表3 ON 表1.字段號=表3.字段號) INNER JOIN 表4 ON Member.字段號=表4.字段號SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段號=表2.字段號) INNER JOIN 表3 ON 表1.字段號=表3.

38、字段號) INNER JOIN 表4 ON Member.字段號=表4.字段號) INNER JOIN 表5 ON Member.字段號=表5.字段號左外聯(lián)接：顯示左表T1中的所有行，并把右表T2中符合條件加到左表T1中；右表T2中不符合條件，就不用參加結果表中，并且NULL表示。SQL語法：select * from T1 left outer join T2 on T1.userid=T2.userid右聯(lián)(right outer join)。顯示右表T2中的所有行，并把左表T1中符合條件加到右表T2中;左表T1中不符合條件，就不用參加結果表中，并且NULL表示SQL語句：select *

39、 from T1 right outer join T2 on T1.userid=T2.userid第四：全聯(lián)(full outer join)。顯示左表T1、右表T2兩邊中的所有行，即把左聯(lián)結果表+右聯(lián)結果表組合在一起，然后過濾掉重復的。SQL語句：select * from T1 full outer join T2 on T1.userid=T2.userid更新關聯(lián)數據表里的數據記錄修改關聯(lián)數據表里的數據記錄使用updata命令在一條SQL語句中對多個表中的數據記錄做出修改語法：UPDATE product p INNER JOIN productPrice pp ON d

40、uctId = ductId SET pp.price = pp.price * 0.8, p.dateUpdate = CURDATE() WHERE p.dateCreated ANY (SELECT s2 FROM table2)在子查詢中，返回的是 table2 的所有 s2 列結果5,12,20，然后將 table1 中的 s1 的值與之進展比擬，只要大于 s2 的任何值即表示為 TRUE，符合查詢條件。IN 是 = ANY 的別名，二者一樣，但 NOT IN 的別名卻不是 ANY 而是 SOME。特殊情況如果 table2 為空表，則 ANY 后的結果為 FALSE；如

41、果子查詢返回如 (NULL,NULL,NULL) 列為空的結果，則 ANY 后的結果為 UNKNOWN 。ALL操作符：ALL 關鍵字必須接在一個比擬操作符的后面，表示與子查詢返回的所有值比擬為 TRUE ，則返回 TRUE例子：SELECT s1 FROM table1 WHERE s1 ALL (SELECT s2 FROM table2)該查詢不會返回任何結果，因為 s1 中沒有比 s2 所有值都大的值。當然在該例子查詢中，返回了 s2 的所有值，您可以在該子查詢中添加任何條件以限制返回的查詢結果而無需全部返回。NOT IN 是 ALL 的別名，二者一樣。特殊情況如果 table2 為空

42、表，則 ALL 后的結果為 TRUE；如果子查詢返回如 (0,NULL,1) 這種盡管 s1 比返回結果都大，但有空行的結果，則 ALL 后的結果為 UNKNOWN 。行子查詢：例子：FROM 子查詢：FROM 子查詢是指 FROM 的子句作為子查詢語句，主查詢再到子查詢結果中獲取需要的數據語法：SELECT . FROM (subquery) AS name .子查詢會生成一個臨時表，由于 FROM 子句中的每個表必須有一個名稱，因此 AS name 是必須的。FROM 子查詢也稱為衍生數據表子查詢。標量子查詢標量子查詢是指子查詢返回的是單一值的標量，如一個數字或一個字符串，也是子查詢中最簡

43、單的返回形式一個標量子查詢的例子如下：SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)在該例子中，子查詢語句：SELECT uid FROM user WHERE status = 1 ORDER BY uid DESC LIMIT 1返回的是單一的數字如 2，實際的查詢語句為：SELECT * FROM article WHERE uid = 2使用子查詢進展比擬可以使用 = = = 這些操作符對子查詢的標量結果進展比擬，通常子查詢的位置在比擬式的

44、右側：SELECT * FROM t1 WHERE column1 = (SELECT MA*(column2) FROM t2)提示對于采用這些操作符之一進展的比擬，子查詢必須返回一個標量。唯一的例外是可以和行子查詢同時使用。子查詢與表連接在很多情況下，子查詢的效果與JOIN 表連接很類似，但一些特殊情況下，是必須用子查詢而不能用表連接的，如：SELECT * FROM t1 WHERE column1 = (SELECT MA*(column2) FROM t2)SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM artic

45、le WHERE article.uid = t.uid)E*ISTS 和 NOT E*ISTS 子查詢E*ISTS 和 NOT E*ISTS 子查詢語法如下：SELECT . FROM table WHERE E*ISTS (subquery)該語法可以理解為：將主查詢的數據，放到子查詢中做條件驗證，根據驗證結果TRUE 或 FALSE來決定主查詢的數據結果是否得以保存。例子：從語句執(zhí)行結果可以看出，article 表中第 4 條記錄沒有被保存，原因就是該條記錄的數據在子查詢中返回的結果是 FALSE 。當上面的 SQL 使用 NOT E*ISTS 時，查詢的結果就是 article 表中

46、uid 不存在于 user 表中的數據記錄。提示E*ISTS (subquery) 只返回 TRUE 或 FALSE，因此子查詢中的 SELECT * 也可以是 SELECT 1 或其他，官方說法是實際執(zhí)行時會忽略 SELECT 清單，因此沒有區(qū)別。E*ISTS 子查詢的實際執(zhí)行過程可能經過了優(yōu)化而不是我們理解上的逐條比照，如果擔憂效率問題，可進展實際檢驗以確定是否有效率問題。E*ISTS 子查詢往往也可以用條件表達式、其他子查詢或者JOIN來替代，何種最優(yōu)需要具體問題具體分析。關聯(lián)子查詢：關聯(lián)子查詢是指一個包含對表的引用的子查詢，該表也顯示在外部查詢中。通俗一點來講，就是子查詢引用到了主查詢

47、的數據數據。以一個實際的例子來理解關聯(lián)子查詢：將該例 SQL 與如下語句比擬更能看出關聯(lián)子查詢與普通子查詢的區(qū)別：SELECT * FROM article WHERE uid IN(SELECT uid FROM user)在本實例中，雖然兩個 SQL 執(zhí)行后的返回結果都一樣，但它們的實現過程是完全不一樣的。后者普通子查詢實際被執(zhí)行為：SELECT * FROM article WHERE uid IN(1,2,3)但在關聯(lián)子查詢中，是無法單獨執(zhí)行子查詢語句的。其實際流程大致為：先做外部主查詢；將主查詢的值傳入子查詢并執(zhí)行；子查詢再將查詢結果返回主查詢，主查詢根據返回結果完成最終的查詢。這個

48、執(zhí)行流程類似于E*ISTS 子查詢，實際上*些情況下 MySQL 就是將關聯(lián)子查詢重寫為 E*ISTS 子查詢來執(zhí)行的。關聯(lián)子查詢效率很明顯，一般情況下關聯(lián)子查詢的效率是比擬低下的，實際上本例中的關聯(lián)子查詢例子也僅是為了演示關聯(lián)子查詢的原理及用法。如果可以的話，關聯(lián)子查詢盡量使用 JOIN 或其他查詢來代替。如本例中，使用INNER JOIN來替換的 SQL 為：SELECT article.* FROM article INNER JOIN user ON article.uid = user.uid注意：此處只是為了演示用 INNER JOIN 替換關聯(lián)子查詢的樣例，并非表名這種處理是最優(yōu)

49、處理。-. z.索引、視圖、事務索引的概念：索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成局部)，它們包含著對數據表里所有記錄的引用指針。更通俗的說，數據庫索引好比是一本書前面的目錄，能加快數據庫的查詢速度。上述SQL語句，在沒有索引的情況下，數據庫會遍歷全部200條數據后選擇符合條件的；而有了相應的索引之后，數據庫會直接在索引中查找符合條件的選項。如果我們把SQL語句換成“SELECT * FROM article WHERE id=2000000，則你是希望數據庫按照順序讀取完200萬行數據以后給你結果還是直接在索引中定位呢？上面的兩個圖片鮮明的用時比照已經給出了答案注

50、：一般數據庫默認都會為主鍵生成索引。索引分為聚簇索引和非聚簇索引兩種，聚簇索引是按照數據存放的物理位置為順序的，而非聚簇索引就不一樣了；聚簇索引能提高多行檢索的速度，而非聚簇索引對于單行的檢索很快。索引的類型：1. 普通索引這是最根本的索引，它沒有任何限制，MyIASM中默認的BTREE類型的索引，也是我們大多數情況下用到的索引。2. 唯一索引與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值注意和主鍵不同。如果是組合索引，則列值的組合必須唯一，創(chuàng)立方法和普通索引類似。4. 單列索引、多列索引多個單列索引與單個多列索引的查詢效果不同，因為執(zhí)行查詢時，MySQL只能使用一個索引，會從多

51、個索引中選擇一個限制最為嚴格的索引。5. 組合索引最左前綴平時用的SQL查詢語句一般都有比擬多的限制條件，所以為了進一步榨取MySQL的效率，就要考慮建立組合索引。例如上表中針對title和time建立一個組合索引：ALTER TABLE article ADD INDE* inde*_titme_time (title(50),time(10)。建立這樣的組合索引，其實是相當于分別建立了下面兩組組合索引：title,timetitle為什么沒有time這樣的組合索引呢？這是因為MySQL組合索引“最左前綴的結果。簡單的理解就是只從最左面的開場組合。并不是只要包含這兩列的查詢都會用到該組合索引

52、，如下面的幾個SQL所示：1使用到上面的索引2SELECT*FROMarticle WHREE title=測試ANDtime=1234567890;3SELECT*FROMarticle WHREE utitle=測試;4不使用上面的索引5SELECT*FROMarticle WHREEtime=1234567890;索引的優(yōu)化：上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進展 INSERT、UPDATE和DELETE。因為更新表時，My SQL不僅要保存數據，還要保存一下索引文件。建立索引會占用

53、磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創(chuàng)立了多種組合索引，索引文件的會膨脹很快。索引只是提高效率的一個因素，如果你的My SQL有大數據量的表，就需要花時間研究建立最優(yōu)秀的索引，或優(yōu)化查詢語句。下面是一些總結以及收藏的My SQL索引的考前須知和優(yōu)化方法。2. 索引不會包含有NULL值的列只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，則這一列對于此復合索引就是無效的。所以我們在數據庫設計時不要讓字段的默認值為NULL。3. 使用短索引對串列進展索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前1

54、0個或20個字符內，多數值是惟一的，則就不要對整個列進展索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。4. 索引列排序MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，則order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)立復合索引。5. like語句操作一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa% 不會使用索引而like “aaa%可以使用索引。6. 不要在列上進展運算例如：select * from use

55、rs where YEAR(adddate)2007，將在每個行上進展運算，這將導致索引失效而進展全表掃描，因此我們可以改成：select * from users where adddate2007-01-01。關于這一點可以圍觀：一個單引號引發(fā)的MYSQL性能損失。最后總結一下，MySQL只對一下操作符才使用索引：,=,between,in,以及*些時候的 like(不以通配符%或_開頭的情形)。而理論上每*表里面最多可創(chuàng)立16個索引，不過除非是數據量真的很多，否則過多的使用索引也不是則好玩的，比方我剛剛針對te*t類型的字段創(chuàng)立索引的時候，系統(tǒng)差點就卡死了。視圖：視圖就是一個存在于數據庫

56、中的虛擬表。視圖本身沒有數據，只是通過執(zhí)行相應的select語句完成獲得相應的數據。視圖和表的區(qū)別表需要占用磁盤空間，視圖不需要；視圖不能添加索引視圖的作用：使用視圖可以簡化復雜查詢視圖有利于提高平安性，比方不同用戶查看不同的視圖邏輯數據獨立性，可以幫助用戶屏蔽真實表構造變化帶來的影響。創(chuàng)立視圖：CREATE OR REPLACE ALGORITHM = UNDEFINED | MERGE | TEMPTABLE VIEW view_name (column_list) AS select_statementWITH CASCADED | LOCAL CHECK OPTION例句：mysql

57、create view v_emp as select empno,ename,job from emp;Query OK, 0 rows affected (0.00 sec)刪除視圖：Drop view view_name;查詢視圖跟普通表一樣查詢即可 select * from 視圖名稱通過以下方式可以查看視圖的根本信息：1. describe 視圖名;查看視圖的字段定義、字段數據類型、是否為空，是否為主/外鍵、默認值和額外信息。2. show table status like 視圖名G查看視圖的存儲引擎、創(chuàng)立時間等，ment的值為View表示視圖。3. show create vie

58、w 視圖名G;查看視圖的詳細定義，包括名稱、創(chuàng)立視圖的語句等信息。4. select * from information_schema.views;在MySQL中，information_schema數據庫下的views表中存儲了所有視圖的定義，通過該查詢，可以查看數據庫中所有視圖的詳細信息。修改視圖例子：mysql alter view v_emp as select empno,ename,job,deptno from emp;Query OK, 0 rows affected (0.01 sec)修改視圖構造，即修改所使用的字段的名稱可以隱含基表的字段名稱：mysql alter v

59、iew v_emp(v1,v2,v3,v4) as select empno,ename,job,deptno from emp;Query OK, 0 rows affected (0.00 sec)視圖縮減業(yè)務邏輯視圖用來隱藏復雜的業(yè)務邏輯，從join連接查詢產生一個view。先使用視圖完成一定的邏輯，再在視圖的根底上完成另外的邏輯。通常，視圖完成的邏輯都是相比照擬根底的邏輯。視圖的執(zhí)行算法：存在兩種執(zhí)行算法：1、Merge：合并的執(zhí)行方式，每當執(zhí)行的時候，先將我們視圖的sql語句與外部查詢視圖的sql語句，混合在一起，最終執(zhí)行；2、Temptable：臨時表模式，每當查詢的時候，將視圖所

60、使用的select語句生成一個結果的臨時表，再在當前的臨時表內進展查詢。指的是一個視圖是在什么時候執(zhí)行，依據哪些方式執(zhí)行；對于MERGE，會將引用視圖的語句的文本與視圖定義合并起來，使得視圖定義的*一局部取代語句的對應局部。對于TEMPTABLE，視圖的結果將被置于臨時表中，然后使用它執(zhí)行語句。對于UNDEFINED，MySQL將選擇所要使用的算法。如果可能，它傾向于MERGE而不是TEMPTABLE，這是因為MERGE通常更有效，而且如果使用了臨時表，視圖是不可更新當用戶創(chuàng)立視圖時，mysql默認使用一種undefine的處理算法，就是會自動在合并和臨時表內進展選擇。注意：1、盡量使用視圖完