mysql 基礎篇-資料查詢(1)

標籤：資料查詢（1）

一、查詢資料的概念：查詢資料、篩選資料、確定資料的以什麼樣的格式輸出。

二、資料查詢的基本語句

   1、基本語句格式 ：select 欄位列表  FROM 表或者視圖  where 查詢條件

   2.1 單表查詢的內容：查詢所有欄位、查詢指定欄位、查詢指定記錄、查詢空值、多條件的查詢

對查詢結果進行排序。

 2.1.1  查詢所有欄位，通過* 萬用字元來實現。

        SELECT * FROM fruits；（*代表所有的表中的欄位，fruits是表名）

      資料返回的時候按照定義表時候的順序顯示的。

      一般不推薦使用*查詢所有的資料，不然會降低查詢和所使用得應用程式的效率

2.1.2 查詢指定單個欄位：

      SELECT f_name FROM fruits (f_name 是fruits表中的一個列)

2.1.3 查詢多個欄位：

      SELECT f_name, f_price FROM fruits;(f_name,f_price 都是fruits的一個列)。    

      不同欄位名稱之間要用逗號分隔開。

2.2通過where來寫條件 進行資料的過濾篩選

 SELECT 欄位名1...n FROM 表名 WHERE 查詢條件

2.2.2  條件判斷符  大於（>）, 小於(<) ,大於等於 (>=) ,小於等於(<=)  ,不等於(!= <>) ,之間(BETWEEN).

2.2.3 等於 （用來比較字串和數值）

       SELECT f_name, f_price FROM fruits WHERE f_price =10.2;

       SELECT f_name, f_price FROM fruits WHERE f_name=‘apple‘;

2.2.4 小於  

        SELECT f_name,f_price FROM fruits WHERE f_price <10.00;

2.2.5 帶 IN關鍵字的查詢   用來查詢滿足指定範圍內的條件的記錄，只要滿足條件範圍內的一個值即為匹配。

     SELECT s_id, f_name, f_price FROM fruits  WHERE s_id IN(101,102) ORDER BY f_name;

 NOT IN:

    SELECT s_id ,f_name, f_price FROM fruits WHERE  s_id IN(101,102)  ORDER BY f_name;

2.2.6  between ... and ...

    SELECT f_name ,f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;

not between .... and....

    SELECT f_name ,f_price FROM fruits WHERE f_price NOT BETWEEN  2.00 AND 10.20;

2.3.1 LIKE 的字元匹配查詢， 配合LIKE 使用的萬用字元是 %   和 _。

2.3.2 % 匹配任意長度的字元，甚至包括零字元，%可以放在不同的位置。

     SELECT f_id, f_name FROM fruits WHERE f_name LIKE ‘b%‘; (尋找以b 字開頭的水果)

     SELECT f_id , f_name FROM fruits WHERE f_name  LIKE ‘%g%‘(尋找名字中含g的水果)

2.3.3  _ 底線萬用字元，一次只能匹配任意一個字元。

      SELECT f_id, f_name FROM fruits WHERE f_name LIKE ‘____y‘;

2.4.1 查詢空值 ： 空值不同於0，也不同於Null 字元串，，空值一般表示資料未知，不適用或將在以後添加資料。

       SELECT c_id, c_name, c_email FROM customers WHERE c_email IS NULL;

   NOT IS NULL: 

       SELECT c_id,c_name,c_email FROM customers WHERE c_email NOT IS NULL;

2.5.1  AND 操作符限定只有滿足所有查詢條件的記錄才會被返回，可以使用AND 連結多個甚至多個查詢條件，多個條件之間用AND分開。

     SELECT f_id, f_price ,f_name FROM fruits WHERE s_id =‘101‘ AND f_price >=5;

     可以有多個過濾條件，  增加條件的同時增加一個關鍵字AND

     SELECT  f_id ,f_price ,f_name FROM fruits 

     WHERE s_id IN(101,102)   AND f_price >=5 AND f_name= ‘apple‘;

2.6.1 OR 表示只需要滿足其中一個條件的記錄即可返回資料，OR可以串連多個甚至多個查詢條件，多個條件運算式之間使用OR分開。

    SELECT s_id ,f_name,f_price FROM fruits WHERE s_id =101 OR s_id =102;

    IN 和 OR 的操作符使用後結果是一樣的，但是使用IN操作符使得檢索語句更加簡明，並且IN執行的速度要快於OR ,使用IN操作符，可以執行更加複雜的巢狀查詢。

    AND 的優先順序高於 OR。

2.7.1 查詢結果不重複   distinct 消除重複的值

SELECT DISTINCT  s_id FROM fruits;

2.8.1 對查詢結果進行排序

    SELECT f_name FROM fruits ORDER BY f_name;   預設應該都是升序。

 2.8.2  多列排序  對多列資料 進行排序，需將需要排序的列之間用逗號隔開。

   SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price;

在對多列進行排序的時候，首先排序的第一列必須有相同的列值，才會對第二列進行排序。如果第一列資料中的所有值都是唯一的，將不再對第二列進行排序。

2.8.3 指定排序的方向  通過關鍵字DESC來實現

SELECT f_name ,f_price FROM fruits ORDER BY f_price DESC;  降序

 ASC 是升序， 在排序的時候 ASC是作為預設的排序方式，所以加不加都可以。

要對多列都進行降序排序，必須要在每一列的列名後面都要加DESC 關鍵字

SELECT f_price, f_name FROM fruits ORDER BY f_price DESC ,f_name DESC;

2.9.1 分組查詢  分組查詢是對資料按照某個或者多個欄位進行分組。 使用GROUP BY 關鍵字對資料進行分組。

2.9.2 基本文法： GROUP BY 欄位 HAVING 條件運算式

  欄位值為進行分組時所依據的列名稱；‘HAVING（條件運算式）’ 指定滿足運算式限定條件的結果將被顯示。

2.9.3 GROUP BY 關鍵字通常和集合函數一期使用 如：MAX()、 MIN()、COUNT()、SUM()、AVG()

        SELECT s_id, COUNT(*)  AS total FROM fruits GROUP BY s_id;

        SELECT s_id,GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;

  2.9.4  GROUP BY 可以和HAVING 一起限定顯示記錄所需滿足的條件，只有滿足條件的分組才能會被顯示。

       SELECT s_id, GROUP_CONCAT(f_name) AS Names

       FROM fruits

       GROUP BY s_id HAVING COUNT(f_name)>1 ;

2.9.5 HAVING 和WHERE 的區別 ：HAVING在資料分組之後進行過濾來選擇分組，而WHERE 在分組之前用來選擇記錄，在WHERE 排除的記錄不在包括在分組內。

2.9.6 GROUP BY 中使用 WITH ROLLUP

       使用WITH ROLLUP 後，在所有查詢出的分組之後增加一條記錄，該記錄計算查詢出的所有記錄的綜合，即統計數量。

      SELECT s_id ,COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP;

2.9.7 多欄位分組   實驗無法成功。

2.9.8 GROUP BY 和 ORDER BY 一起使用

      SELECT o_num, SUM(quantity* item_price)  AS orderTotal  

      FROM orderitems

     GROUP BY o_num

     HAVING SUM(quantity*item_price) >=100;

3.1.1 LIMIT 限制查詢結果的數量

 SELECT * FROM fruits LIMIT 4;  顯示查詢結果的前4行

 SELECT * FROM fruits LIMIT4,3； 顯示從第五行開始後的三條記錄。

4.1.1 使用彙總函式查詢

  功能：計算資料表中記錄行數的總數、計算某個欄位列下資料的總和，以及計算表中某個欄位下的最大值、最小值或者平均值。

 AVG （某列的平均數） COUNT （某列的行數） MAX （某列的最大值）MIN（某列的最小值） SUM(某列值得和)

4.1.2 COUNT()函數

      COUNT(*) 計算表中總的行數，不管某列有數值或者為空白值。

       SELECT COUNT(*)   AS cust_num FROM customers;

       

      COUNT(欄位名) 電腦指定列下總的行數，計算時忽略空值的行

      SELECT COUNT(o_email)   AS email_num FROM customers;

4.1.2   COUNT  GROUP BY

     SELECT o_num, COUNT(f_id)

     FROM orderitems

    GROUP BY o_num;

   GROUP BY 關鍵字先按照哦訂單號進行分組，然後計算每個分組中的總記錄數。

4.1.3 SUM  

      SELECT SUM(quantity)  AS items_total

       FROM orderitems

       WHERE o_num =30005;

   SELECT o_num ,SUM(quantity)   AS items_total

   FROM orderitems

   GROUP BY o_num;

count 行的總和， sum 是列的值得總和。

4.1.4 AVG

       SELECT AVG(f_price)   AS avg_price

       FROM fruits

       WHERE s_id=103;

  

       SELECT s_id, AVG(f_price )   AS avg_price

        FROM  fruits

       GROUP BY s_id;

  AVG()函數使用時，其參數為要計算的列名稱，如果要得到多個列的多個平均值，則需要在每一列上使用AVG()函數

4.1.5 MAX() 函數

      SELECT MAX(f_price)  AS max_price FROM fruits;

      

       SELECT s_id, MAX(f_price)   AS max_price FROM fruits GROUP BY s_id;

      SELECT MAX(f_name)   FROM fruits;

     MAX  可以對字母進行大小判斷，並返回最大的字元或者字串值。

4.1.6   MIN()函數

     SELECT MIN(f_price)  AS min_price FROM fruits;

     

     SELECT s_id ,MIN(f_price)   AS min_price

     FROM fruits

     GROUP BY s_id;

GROUP BY 關鍵字 根據 s_id 欄位對記錄進行分組，然後計算出每個分組中的最小值。

5.1.1  串連是資料庫模型的主要特點。主要包含內串連，外串連等。

5.1.2 內串連查詢

        使用標膠運算子進行表間某列資料的比較操作，並列出這些表中與串連條件相匹配的資料行，組合成新記錄。

       SELECT suppliers.s_id, s_name,f_name, f_price 

       FROM fruits ,suppliers

       WHERE  fruits.s_id =suppliers.s_id;

      SELECT後面指定的列分別屬於兩個不同的表。 WHERE 子句作為過濾條件，指明只有兩個表中的s_id欄位值相等的時候才符合串連查詢的條件。

       SELECT suppliers.s_id,s_name, f_name, f_price

       FROM fruits INNER JOIN suppliers

       ON fruits.s_id = suppliers.s_id;

      INNER JOIN 是標準規範

5.1.3 在一個串連查詢中，涉及的兩個表都是同一個表，這種查詢稱為自串連查詢。自串連查詢是一種特殊的內 串連，它是指相互串連的表在物理上為同一張表，但在邏輯上分為兩張表。

     SEELCT f1.f_id ,f1.f_name

      FROM fruits AS f1 , fruits AS f2

      WHERE f1.s_id = f2.s_id AND f2.f_id = ‘a1‘;

mysql 基礎篇-資料查詢(1)