mysql 基礎篇-數據查詢(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，也不同于空字符串，，空值一般表示數據未知，不適用或將在以后添加數據。

       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';