mysql 基礎(chǔ)篇-數(shù)據(jù)查詢(1)

一、查詢數(shù)據(jù)的概念：查詢數(shù)據(jù)、篩選數(shù)據(jù)、確定數(shù)據(jù)的以什么樣的格式輸出。

二、數(shù)據(jù)查詢的基本語(yǔ)句

   1、基本語(yǔ)句格式 ：select 字段列表  FROM 表或者視圖  where 查詢條件

   2.1 單表查詢的內(nèi)容：查詢所有字段、查詢指定字段、查詢指定記錄、查詢空值、多條件的查詢

對(duì)查詢結(jié)果進(jìn)行排序。

 2.1.1  查詢所有字段，通過* 通配符來(lái)實(shí)現(xiàn)。

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

      數(shù)據(jù)返回的時(shí)候按照定義表時(shí)候的順序顯示的。

      一般不推薦使用*查詢所有的數(shù)據(jù)，不然會(huì)降低查詢和所使用得應(yīng)用程序的效率

2.1.2 查詢指定單個(gè)字段：

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

2.1.3 查詢多個(gè)字段：

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

      不同字段名稱之間要用逗號(hào)分隔開。

2.2通過where來(lái)寫條件 進(jìn)行數(shù)據(jù)的過濾篩選

 SELECT 字段名1...n FROM 表名 WHERE 查詢條件

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

2.2.3 等于 （用來(lái)比較字符串和數(shù)值）

       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關(guān)鍵字的查詢   用來(lái)查詢滿足指定范圍內(nèi)的條件的記錄，只要滿足條件范圍內(nèi)的一個(gè)值即為匹配。

     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 % 匹配任意長(zhǎng)度的字符，甚至包括零字符，%可以放在不同的位置。

     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  _ 下劃線通配符，一次只能匹配任意一個(gè)字符。

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

2.4.1 查詢空值 ： 空值不同于0，也不同于空字符串，，空值一般表示數(shù)據(jù)未知，不適用或?qū)⒃谝院筇砑訑?shù)據(jù)。

       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 操作符限定只有滿足所有查詢條件的記錄才會(huì)被返回，可以使用AND 鏈接多個(gè)甚至多個(gè)查詢條件，多個(gè)條件之間用AND分開。

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

     可以有多個(gè)過濾條件，  增加條件的同時(shí)增加一個(gè)關(guān)鍵字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 表示只需要滿足其中一個(gè)條件的記錄即可返回?cái)?shù)據(jù)，OR可以連接多個(gè)甚至多個(gè)查詢條件，多個(gè)條件表達(dá)式之間使用OR分開。

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

    IN 和 OR 的操作符使用后結(jié)果是一樣的，但是使用IN操作符使得檢索語(yǔ)句更加簡(jiǎn)明，并且IN執(zhí)行的速度要快于OR ,使用IN操作符，可以執(zhí)行更加復(fù)雜的嵌套查詢。

    AND 的優(yōu)先級(jí)高于 OR。

2.7.1 查詢結(jié)果不重復(fù)   distinct 消除重復(fù)的值

SELECT DISTINCT  s_id FROM fruits;

2.8.1 對(duì)查詢結(jié)果進(jìn)行排序

    SELECT f_name FROM fruits ORDER BY f_name;   默認(rèn)應(yīng)該都是升序。

 2.8.2  多列排序  對(duì)多列數(shù)據(jù) 進(jìn)行排序，需將需要排序的列之間用逗號(hào)隔開。

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

在對(duì)多列進(jìn)行排序的時(shí)候，首先排序的第一列必須有相同的列值，才會(huì)對(duì)第二列進(jìn)行排序。如果第一列數(shù)據(jù)中的所有值都是唯一的，將不再對(duì)第二列進(jìn)行排序。

2.8.3 指定排序的方向  通過關(guān)鍵字DESC來(lái)實(shí)現(xiàn)

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

 ASC 是升序， 在排序的時(shí)候 ASC是作為默認(rèn)的排序方式，所以加不加都可以。

要對(duì)多列都進(jìn)行降序排序，必須要在每一列的列名后面都要加DESC 關(guān)鍵字

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

2.9.1 分組查詢  分組查詢是對(duì)數(shù)據(jù)按照某個(gè)或者多個(gè)字段進(jìn)行分組。 使用GROUP BY 關(guān)鍵字對(duì)數(shù)據(jù)進(jìn)行分組。

2.9.2 基本語(yǔ)法： GROUP BY 字段 HAVING 條件表達(dá)式

  字段值為進(jìn)行分組時(shí)所依據(jù)的列名稱；‘HAVING（條件表達(dá)式）’ 指定滿足表達(dá)式限定條件的結(jié)果將被顯示。

2.9.3 GROUP BY 關(guān)鍵字通常和集合函數(shù)一期使用 如：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 一起限定顯示記錄所需滿足的條件，只有滿足條件的分組才能會(huì)被顯示。

       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 的區(qū)別 ：HAVING在數(shù)據(jù)分組之后進(jìn)行過濾來(lái)選擇分組，而WHERE 在分組之前用來(lái)選擇記錄，在WHERE 排除的記錄不在包括在分組內(nèi)。

2.9.6 GROUP BY 中使用 WITH ROLLUP

       使用WITH ROLLUP 后，在所有查詢出的分組之后增加一條記錄，該記錄計(jì)算查詢出的所有記錄的綜合，即統(tǒng)計(jì)數(shù)量。

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

2.9.7 多字段分組   實(shí)驗(yàn)無(wú)法成功。

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 限制查詢結(jié)果的數(shù)量

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

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

4.1.1 使用聚合函數(shù)查詢

  功能：計(jì)算數(shù)據(jù)表中記錄行數(shù)的總數(shù)、計(jì)算某個(gè)字段列下數(shù)據(jù)的總和，以及計(jì)算表中某個(gè)字段下的最大值、最小值或者平均值。

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

4.1.2 COUNT()函數(shù)

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

       SELECT COUNT(*)   AS cust_num FROM customers;

      COUNT(字段名) 計(jì)算機(jī)指定列下總的行數(shù)，計(jì)算時(shí)忽略空值的行

      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 關(guān)鍵字先按照哦訂單號(hào)進(jìn)行分組，然后計(jì)算每個(gè)分組中的總記錄數(shù)。

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()函數(shù)使用時(shí)，其參數(shù)為要計(jì)算的列名稱，如果要得到多個(gè)列的多個(gè)平均值，則需要在每一列上使用AVG()函數(shù)

4.1.5 MAX() 函數(shù)

      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  可以對(duì)字母進(jìn)行大小判斷，并返回最大的字符或者字符串值。

4.1.6   MIN()函數(shù)

     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 關(guān)鍵字 根據(jù) s_id 字段對(duì)記錄進(jìn)行分組，然后計(jì)算出每個(gè)分組中的最小值。

5.1.1  連接是數(shù)據(jù)庫(kù)模型的主要特點(diǎn)。主要包含內(nèi)連接，外連接等。

5.1.2 內(nèi)連接查詢

        使用標(biāo)膠運(yùn)算符進(jìn)行表間某列數(shù)據(jù)的比較操作，并列出這些表中與連接條件相匹配的數(shù)據(jù)行，組合成新記錄。

       SELECT suppliers.s_id, s_name,f_name, f_price 

       FROM fruits ,suppliers

       WHERE  fruits.s_id =suppliers.s_id;

      SELECT后面指定的列分別屬于兩個(gè)不同的表。 WHERE 子句作為過濾條件，指明只有兩個(gè)表中的s_id字段值相等的時(shí)候才符合連接查詢的條件。

       SELECT suppliers.s_id,s_name, f_name, f_price

       FROM fruits INNER JOIN suppliers

       ON fruits.s_id = suppliers.s_id;

      INNER JOIN 是標(biāo)準(zhǔn)規(guī)范

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

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