1. MySQL數(shù)據(jù)庫執(zhí)行查詢操作時(shí)的查詢流程：

請求-->查詢緩存
請求-->查詢緩存-->解析器-->預(yù)處理器-->優(yōu)化器-->查詢執(zhí)行引擎-->存儲(chǔ)引擎-->緩存-->響應(yīng)

mysql查詢流程.png

客戶端通過mysql協(xié)議連接至mysql server朦前，mysql server接收到請求后绍些，在建立線程連接之后，通過連接線程接收客戶發(fā)出的查詢請求語句嘀掸，并且接收后嘗試在緩存中是否能命中材蹬，如果能命中則直接返回結(jié)果实幕，所以后面的流程都省去了；所以對(duì)mysql而言堤器，內(nèi)部的重要組件叫query cache查詢緩存茬缩；
如果緩存未命中，查詢語句交有解析器進(jìn)行解析吼旧，并生成一個(gè)解析樹，然后解析器被預(yù)處理器預(yù)處理未舟，生成了可以訪問數(shù)據(jù)的路徑圈暗，由優(yōu)化器評(píng)估眾多路徑中，哪條時(shí)最優(yōu)裕膀，并嘗試對(duì)最優(yōu)路徑進(jìn)行改寫员串，以加速其執(zhí)行性能；優(yōu)化完成以后昼扛，將優(yōu)化后的執(zhí)行請求提交給查詢執(zhí)行計(jì)劃寸齐，排隊(duì)欲诺，每一個(gè)執(zhí)行計(jì)劃，有查詢執(zhí)行引擎負(fù)責(zé)渺鹦；這包括通過API調(diào)用存儲(chǔ)引擎從磁盤上取到相應(yīng)的數(shù)據(jù)扰法，在執(zhí)行查詢引擎上做過濾等等；之后再把結(jié)果返回給客戶端毅厚，返回客戶端之前塞颁，如果程序結(jié)果是確定的而且符合緩存條件的測保存在緩存中。

2. SELECT語句的執(zhí)行流程：

FROM --> WHERE --> Group By --> Having --> Order BY --> SELECT --> Limit
一個(gè)select語句啟動(dòng)時(shí)吸耿，會(huì)首先判斷from子句祠锣，決定從哪張表中完成查詢操作，這個(gè)關(guān)鍵詞被分析完成后咽安，經(jīng)過第一步分析from以確定對(duì)應(yīng)表伴网；確定完表以后，要從表上通過where子句來指明過濾條件妆棒，挑選出需要獲取數(shù)據(jù)的行澡腾，所有第二步是分析where子句；分析完成where子句后募逞，要把查詢到的所有結(jié)果做分組蛋铆，所有接下來非是group by子句，做分組放接；分組完成后做having刺啦，也就是所謂對(duì)分組的結(jié)果指明過濾條件再一次進(jìn)行過濾；此處group by和having可以省略纠脾，之后對(duì)后續(xù)進(jìn)行排序操作order by玛瘸；排序后挑選出期望用到的列也叫字段，最終是要limit限制整體的輸出結(jié)果中需要用到多少行苟蹈，最終得到結(jié)果糊渊。

3. 單表查詢

• 語法：

  SELECT
  [ALL | DISTINCT | DISTINCTROW ]
  [HIGH_PRIORITY]
  [STRAIGHT_JOIN]
  [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
  [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
  select_expr [, select_expr ...]
  [FROM table_references
  [WHERE where_condition]
  [GROUP BY {col_name | expr | position}
  [ASC | DESC], ... [WITH ROLLUP]]
  [HAVING where_condition]
  [ORDER BY {col_name | expr | position}
  [ASC | DESC], ...]
  [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  [PROCEDURE procedure_name(argument_list)]
  [INTO OUTFILE 'file_name'
    [CHARACTER SET charset_name]
    export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name]]
  [FOR UPDATE | LOCK IN SHARE MODE]]
  
  常用選項(xiàng)：
    DISTINCT：數(shù)據(jù)去重；
    SQL_CACHE：顯示指定存儲(chǔ)查詢結(jié)果于緩存之中慧脱；
    SQL_NO_CACHE：顯示查詢結(jié)果不予緩存渺绒；
  
   常用功能：
     query_cache_type的值為ON時(shí)，查詢緩存功能打開菱鸥；
     SELECT的結(jié)果符合緩存條件即會(huì)緩存宗兼，否則，不予緩存氮采；
                    如果顯示指定SQL_NO_CACHE殷绍，則不予緩存；
      query_cache_type的值為DEMAND時(shí)鹊漠，表示查詢緩存功能按需進(jìn)行主到；
                    顯示指定SQL_CACHE的SELECT語句才會(huì)緩存茶行；其它均不予緩存；
  
  字段顯示可用使用別名：
            col1 AS alias1登钥，col2 AS alias2畔师，...
  

• WHERE 子句：指明過濾條件以實(shí)現(xiàn)選擇的功能；

    算術(shù)表達(dá)式：+怔鳖，-茉唉，*，/结执，%
    比較操作符：=度陆，!=，<>（不等于）献幔，<=>（跟空值比較不報(bào)錯(cuò)）懂傀，>，>=蜡感，<蹬蚁，<=
  
    BETWEEN min_num AND max_num：在兩值的范圍之間；
    IN(element1, element2, ...)：多值等于郑兴；
    IS NULL：取值為空犀斋；
    IS NOT NULL
    LIKE：模糊匹配
              %：任意長度的任意字符；
              _（下劃線）：任意單個(gè)字符情连；
    ELIKE：表示后面匹配條件可使用正則表達(dá)式叽粹；
    REGEXP：匹配字符串可用正則表達(dá)式書寫模式；最好不用却舀；
  
    邏輯操作符：
                NOT：非虫几，單目操作符；
                AND：與挽拔，雙目操作符辆脸；
                OR：或，雙目操作符螃诅；
                XOR：異或啡氢，雙目操作符；不同為真术裸，相同為假空执；
  
            注意：MySQL自己有很多操作函數(shù)；
  

• GROUP: 根據(jù)指定的條件把查詢結(jié)果進(jìn)行分組穗椅，以用于做聚合運(yùn)算；

  • avg() ：求平均值
  • max()：最大值
  • min()：最小值
  • count()：求數(shù)量
  • sum()：求和

• HAVING：對(duì)分組聚合運(yùn)算后的結(jié)果指定過濾條件奶栖；

• ORDER BY：根據(jù)指定的字段對(duì)查詢結(jié)果進(jìn)行排序匹表；

  • 升序：ASC,默認(rèn)方式
  • 降序：DESC; 在指定的字段后面寫明即可门坷；

• LIMIT [[offset,]row_count]：對(duì)查詢的結(jié)果進(jìn)行輸出行數(shù)數(shù)量限制；

  • offset ：標(biāo)識(shí)偏移量
  • row_count：行數(shù)

4. 多表查詢

• 連接操作：
  • 交叉連接：笛卡爾乘積（多項(xiàng)式相乘）袍镀；
  • 內(nèi)連接：

    • 等值連接：讓表之間的字段以等值建立連接關(guān)系

    • 不等值連接：

    • 自然連接

    • 自連接

      SELECT s.Name,t.Name FROM students AS s,students AS t WHERE s.TeacherID=t.StuID
      

  • 外鏈接：

    • 左外連接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col左表中的每一項(xiàng)都有保證出現(xiàn)默蚌；而右表中沒有對(duì)應(yīng)時(shí)留空；

      SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID=c.ClassID;
      

    • 右外連接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

      SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID=c.ClassID;
      

5. 子查詢：在查詢語句中嵌套著查詢語句苇羡；即基于某語句的查詢結(jié)果再次進(jìn)行的查詢绸吸；

• 用在WHERE子句中的子查詢：
  （1）用于比較表達(dá)式中的子查詢；子查詢僅能返回單個(gè)值设江；
  例如： SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
  （2）用在IN中的子查詢：子查詢應(yīng)該單鍵查詢并返回一個(gè)或多個(gè)值從構(gòu)成列表锦茁；
  SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
  （3）用EXISTS；用在where子句中叉存；（沒介紹）
• 用于FROM子句中的子查詢：
  • 使用格式：SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE Clause;不建議使用码俩；
  • 大多數(shù)from子句都可拆為多表查詢；
  • 聯(lián)合查詢：UNION
  • 把兩個(gè)select語句查詢結(jié)果合并成一個(gè)歼捏；

6. 練習(xí)：

(1) 在students表中稿存，查詢年齡大于25歲，且為男性的同學(xué)的名字和年齡瞳秽；

SELECT Name,Age,Gender FROM students WHERE Age>25 AND Gender='M';

(2) 以ClassID為分組依據(jù)瓣履，顯示每組的平均年齡；

SELECT avg(Age),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID;

(3) 顯示第2題中平均年齡大于30的分組及平均年齡练俐；

SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID HAVING aage >30;

(4) 顯示以L開頭的名字的同學(xué)的信息袖迎；

SELECT * FROM students WHERE Name LIKE 'L%';

(5) 顯示TeacherID非空的同學(xué)的相關(guān)信息；

SELECT * FROM students WHERE TeacherID IS NOT NULL;

(6) 以年齡排序后痰洒，顯示年齡最大的前10位同學(xué)的信息瓢棒；

SELECT * FROM students ORDER BY Age DESC LIMIT 10;

(7) 查詢年齡大于等于20歲，小于等于25歲的同學(xué)的信息丘喻；用三種方法脯宿；

SELECT * FROM students WHERE Age>=20 HAVING Age<25; 
SELECT * FROM students WHERE Age>=20 AND Age<25;
SELECT * FROM (SELECT * FROM students WHERE Age>=20) AS s WHERE s.Age<25;

7. 練習(xí)：

1、以ClassID分組泉粉，顯示每班的同學(xué)的人數(shù)连霉；

SELECT count(ClassID),ClassID FROM students GROUP BY ClassID;

2、以Gender分組嗡靡，顯示其年齡之和跺撼；

SELECT Gender,sum(Age) FROM students GROUP BY Gender;

3、以ClassID分組讨彼，顯示其平均年齡大于25的班級(jí)歉井；

SELECT ClassID,avg(Age) AS a FROM students GROUP BY ClassID HAVING a>25;

4、以Gender分組哈误，顯示各組中年齡大于25的學(xué)員的年齡之和哩至；

SELECT sum(Age),Gender FROM (SELECT Age,Gender FROM students WHERE Age>25) AS a GROUP BY Gender;

SELECT sum(Age),Gender FROM students WHERE Age IN (SELECT Age FROM students WHERE Age>25);

練習(xí)：導(dǎo)入hellodb.sql躏嚎，完成以下題目：
1、顯示前5位同學(xué)的姓名菩貌、課程及成績卢佣；

SELECT s.Name,c.Course,e.Score FROM students AS s,courses AS c,scores AS e WHERE s.StuID=e.StuID AND c.CourseID=e.CourseID AND s.StuID<=5;

2、顯示其成績高于80的同學(xué)的名稱及課程箭阶；

    SELECT c.Course,d.Name,d.Score FROM courses AS c RIGHT JOIN (SELECT s.Name,a.CourseID,a.Score FROM students AS s LEFT JOIN (SELECT StuID,CourseID,Score FROM scores WHERE Score>80) AS a ON s.StuID=a.StuID WHERE CourseID IS NOT NULL) AS d ON d.CourseID=c.CourseID; 
    SELECT s.Name,c.Course,b.Score FROM students as s,courses as c,scores as b WHERE s.StuID=b.StuID AND c.CourseID=b.CourseID AND b.Score>80;

3虚茶、求前8位同學(xué)每位同學(xué)自己兩門課的平均成績，并按降序排列仇参；

SELECT StuID,CourseID,avg(Score) FROM scores WHERE StuID<=8 GROUP BY StuID ORDER BY avg(Score)DESC;
SELECT Name,avg(Score) FROM students as s,scores as a WHERE s.StuID=a.StuID AND s.StuID<=8 GROUP BY Name ORDER BY avg(Score) DESC; 

4嘹叫、顯示每門課程課程名稱及學(xué)習(xí)了這門課的同學(xué)的個(gè)數(shù)；

SELECT c.Course,c.CourseID,count(s.Name) FROM students as s,courses as c,coc WHERE s.ClassID=coc.ClassID AND c.CourseID=coc.CourseID GROUP BY c.CourseID;

思考：

1冈敛、如何顯示其年齡大于平均年齡的同學(xué)的名字待笑？

SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);        

2、如何顯示其學(xué)習(xí)的課程為第1抓谴、2暮蹂，4或第7門課的同學(xué)的名字？

SELECT s.Name,c.CourseID FROM students AS s,coc AS c WHERE s.ClassID=c.ClassID AND c.CourseID IN (1,2,4,7);

3癌压、如何顯示其成員數(shù)最少為3個(gè)的班級(jí)的同學(xué)中年齡大于同班同學(xué)平均年齡的同學(xué)仰泻？

SELECT s.Name,cvg.ClassID,s.Age,cvg.ag FROM students AS s,(SELECT ClassID,count(Name),avg(Age) AS ag FROM students GROUP BY ClassID HAVING count(Name)>=3) AS cvg WHERE s.ClassID=cvg.ClassID AND s.Age>cvg.ag AND s.ClassID ;

4、統(tǒng)計(jì)各班級(jí)中年齡大于全校同學(xué)平均年齡的同學(xué)滩届。

SELECT Name,Age FROM students WHERE age>(SELECT avg(Age) FROM students);