MySQl心得4

MySQL中純數的記錄在程序中可以不加單引號；如果字符型的記錄中只有數字則也可以不加單引號；as 語句后面的記錄加不加單引號都一樣。 1.語法式： select [all | distinct | distinctrow ] [high_priority] [straight_join] [sql_small_result] [sql_big_resu

MySQL中純數È的記錄在程序中可以不加單引號；如果字符型的記錄中只有數字則也可以不加單引號；as 語句后面的記錄加不加單引號都一樣。

1.語法格式：

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, ...

[into outfile 'name' export_options | INTO dumpfile 'name']

[from table_reference [ ,table_reference] …] /*FROM子句*/

[wherewhere_definition] /*WHERE子句*/

[group by{col_name| expr | position} [asc|desc], ... [with rollup]] /*GROUP BY子句*/

[having where_definition] /* HAVING 子句*/

[order by{col_name | expr | position} [ASC| DESC] , ...] /*ORDER BY子句*/

[limt {[offset,] row_count | row_count OFFSET offset}] /*LIMIT子句*/

說明：

從這個基本語法可以看出，最簡單的SELECT語句是SELECTselect_expr，利用這個最簡單的SELECT語句，可以進行MySQL所支持的任何運算，例如，SELECT 1+1，它將返回2。

SELECT關鍵詞的后面可以使用很多的選項：

ALL | distinct | DISTINCTROW：這幾個選項指定是否重復行應被返回。如果這些選項沒有被給定，則默認È為ALL（所有的匹配行被返回）。DISTINCT和 DISTINCTROW是同義詞，用于消除結果集合中的重復行。

● HIGH_PRIORITY，STRAIGHT_JOIN和以SQL_為開頭的選項都是MySQL相對于標準SQL的擴展，這些選項在多數情況下可以選擇不使用。

● high_priority：給予SELECT更高的優(yōu)先權，使查詢立刻執(zhí)行，加快查詢速度。

● straight_join：用于促使MySQL優(yōu)化器把表聯(lián)合在一起，加快查詢速度。

● sql_small_result：可以與GROUP BY或DISTINCT同時使用，來告知MySQL優(yōu)化器結果集合是較小的。在此情況下，MySQL使用快速臨時表來儲存生成的表，不使用分類。

● SQL_buffer_RESULT：促使結果被放入一個臨時表中。這可以幫助MySQL提前解開表鎖定，在需要花費較長時間的情況下，也可以幫助把結果集合發(fā)送到客戶端中。

● SQL_BIG_RESULT：可以與GROUPBY或DISTINCT同時使用，來告知MySQL優(yōu)化器結果集合有很多行。在這種情況下，MySQL會優(yōu)先進行分類，不優(yōu)先使用臨時表。

● SQL_cache：告知MySQL把查詢結果存儲在查詢緩存中。對于使用UNION的查詢或子查詢，本選項會影響查詢中的所有SELECT。

● SQL_NO_CACHE：告知MySQL不要把查詢結果存儲在查詢緩存中。

● SQL_CALC_FOUND_ROWS：告知MySQL計算有多少行應位于結果集合中，不考慮任何LIMIT子句。

● SELECT…INTO OUTFILE 'name'：這個語句可以將表中的行導出到一個文件中，這個文件被創(chuàng)建在服務器主機中，name為文件名。

所有被使用的子句必須按語法說明中顯示的順序嚴格地排序。例如，一個HAVING子句必須位于GROUP BY子句之后，并位于ORDER BY子句之前。

2. 消除結果集中的重復行

對表只選擇其某些列時，可能會出現重復行。例如，若對XSCJ數據庫的XS表只選擇專業(yè)名和總學分，則出現多行重復的情況。可以使用distinct或distinctrow關鍵字消除結果集中的重復行，其格式是：select distinct|distinctrow 字段名1 [ , 字段名2…]

其含義是對結果集中的重復行只選擇一個，保證行的唯一性。

例：對XSCJ數據庫的XS表只選擇專業(yè)名和總學分，消除結果集中的重復行。 select distinct 專業(yè)名，總學分 from xs;

2. 聚合函數（aggregationfunction）（分組：分類匯總）

SELECT子句的表達式中還可以包含所謂的聚合函數。聚合函數常常用于對一組È進行計算，然后返回單個È。除COUNT函數外，聚合函數都會忽略空È。聚合函數通常與GROUP BY子句一起使用，但是不能與where語句一起使用。如果SELECT語句中有一個GROUP BY子句，則這個聚合函數對所有列起作用，如果沒有，則SELECT語句只產生一行作為結果。

（1）COUNT函數

聚合函數中最經常使用的是COUNT()函數，用于統(tǒng)計組中滿足條件的行數或總行數，返回SELECT語句檢索到的行中非NULLÈ的數目，若找不到匹配的行，則返回0。

語法格式為：COUNT ( { [ ALL | DISTINCT ] expression } | * )

其中，expression是一個表達式，其數據類型是除BLOB或TEXT之外的任何類型。ALL表示對所有È進行運算，DISTINCT表示去除重復È，默認為ALL。使用COUNT(*)時將返回檢索行的總數目，不論其是否包含 NULLÈ。

count(*):統(tǒng)計記錄的條數（包括null）；

count(字段名）：統(tǒng)計字段中有È的記錄個數（不包括null）；

count(distinct 字段名）：去掉重復后再統(tǒng)計字段中有È的記錄個數（也不包括null）

例1：求學生的總人數。

SELECT COUNT(*) AS '學生總數' FROMXS;

例2. 統(tǒng)計備注不為空的學生數目。

SELECT COUNT（備注）AS '備注不為空的學生數目' FROM XS;

注意：這里COUNT（備注）計算時備注為NULL的行被忽略

例3： 統(tǒng)計總學分在50分以上的人數。

SELECT COUNT（總學分） AS '總學分50分以上的人數'

FROM XS WHERE 總學分>50;

（2）MAX和MIN

MAX和MIN分別用于求表達式中所有È項的最大È與最小È，語法格式為：MAX / MIN ( [ ALL | DISTINCT ] expression )其中，expression是常量、列、函數或表達式，其數據類型可以是數字、字符和時間日期類型。

例：求選修101課程的學生的最高分和最低分。

SELECT MAX（成績）, MIN（成績）

FROM XS_KC WHERE 課程號 ='101';

注意：當給定列上只有空È或檢索出的中間結果為空時，MAX和MIN函數的È也為空。

（3）SUM函數和AVG函數

SUM和AVG分別用于求表達式中所有È項的總和與平均È，語法格式為：SUM / AVG ( [ ALL | DISTINCT ] expression )其中，expression是常量、列、函數或表達式，其數據類型只能是數È型數據。

例：求學號081101的學生所學課程的總成績。

SELECT SUM(成績) AS '課程總成績'

FROM XS_KC WHERE 學號 ='081101';

（4）variance和stddev（STD）函數

variance和stddev函數分別用于計算特定的表達式中的所有È的方差和標準差。語法格式：

VARIANCE / STDDEV ( [ ALL | DISTINCT ]expression )

說明：方差的計算按照以下幾個步驟進行。

① 計算相關列的平均È；

② 求列中的每一個È和平均È的差；

③ 計算差È的平方的總和；

④ 用總和除以（列中的）È得結果。

STDDEV函數用于計算標準差。標準差等于方差的平均根。所以，stddev(…)和sqrt（VARIANCE(…)）這兩個表達式是相等的。

STDDEV可以縮寫為STD，這對結果沒有影響。

（5）group_concat函數

MySQL支持一個特殊的聚合函數group_concat函數。該函數返回來自一個組指定列的所有非NULLÈ，這些È一個接著一個放置，中間用逗號隔開，并表示為一個長長的字符串。這個字符串的長度是有的，標準È是1024。

語法格式為：GROUP_CONCAT({[ALL|DISTINCT] expression }| * )

（6）BIT_AND、BIT_OR和BIT_XOR

與二進制運算符|（或）、&（與）和^（異或）相對應的聚合函數也存在，分別是BIT_OR 、BIT_AND、BIT_XOR。例如，函數BIT_OR在一列中的所有È上執(zhí)行一個二進制OR。

語法格式為： BIT_AND |BIT_OR | BIT_XOR( { [ ALL | DISTINCT ] expression } | * )

例： 有一個表BITS，其中有一列bin_value上有3個integerÈ：1、3、7，獲取在該列上執(zhí)行BIT_OR的結果，使用如下語句：

SELECT BIN(BIT_OR(bin_value)) FROM BITS;

說明：MySQL在后臺執(zhí)行如下表達式：（001|011）|111，結果為111。 其中BIN函數用于將結果轉換為二進制位。

3. 范圍比較

用于范圍比較的關鍵字有兩個：between和in。

當要查詢的條件是某個È的范圍時，可以使用between關鍵字。BETWEEN關鍵字指出查詢范圍，格式為：

expression [ NOT ] between expression1 andexpression2

當不使用NOT時，若表達式expression的È在表達式expression1與expression2之間（包括這兩個È，相反，使用not時，不包括這兩個È），則返回TRUE，否則返回FALSE；使用NOT時，返回È剛好相反。

注意：expression1的È不能大于expression2的È。

使用IN關鍵字可以指定一個È表，È表中列出所有可能的È，當與È表中的任一個匹配時，即返回TRUE，否則返回FALSE。使用IN關鍵字指定È表的格式為：expression IN ( expression [,…n])

例:前面那個求1900年出生的學生的第三種做法；

SELECT 學號, 姓名, 專業(yè)名, 出生日期 FROM XS

WHERE 出生日期 NOT BETWEEN '1990-01-01'and '1990-12-31';

例：查詢XS表中專業(yè)名為“計算機”、“通信工程”或“無線電”的學生的情況。

SELECT * FROM XS

WHERE 專業(yè)名 IN ('計算機', '通信工程', '無線電');

該語句與下列語句等價：

SELECT * FROM XS

WHERE 專業(yè)名 ='計算機' OR 專業(yè)名 = '通信工程' OR 專業(yè)名 = '無線電';

說明：IN關鍵字最主要的作用是表達子查詢。

4. 空È比較

當需要判定一個表達式的È是否為空È時，使用IS NULL關鍵字，格式為： expression IS [ NOT ] NULL

當不使用NOT時，若表達式expression的È為空È，返回TRUE，否則返回FALSE；當使用NOT時，結果剛好相反。

例： 查詢XSCJ數據庫中總學分尚不定的學生情況。

SELECT * FROM XS

WHERE 總學分 IS NULL;

本例即查找總學分為空的學生，結果為空。

5. group by子句主要用于根據字段對行分組。

例如，根據學生所學的專業(yè)對XS表中的所有行分組，結果是每個專業(yè)的學生成為一組。group by子句的語法格式如下：group by {col_name | expr| position} [ASC | DESC], ... [WITH ROLLUP]

說明：

group by子句后通常包含列名或表達式。MySQL對GROUP BY子句進行了擴展，可以在列的后面指定ASC（升序）或DESC（降序）。GROUP BY可以根據一個或多個列進行分組，也可以根據表達式進行分組，經常和聚合函數一起使用。

例1:將XSCJ數據庫中各專業(yè)名輸出。

SELECT 專業(yè)名 FROM XS GROUP BY 專業(yè)名;

例2: 求XSCJ數據庫中各專業(yè)的學生數。

SELECT 專業(yè)名,COUNT(*) AS '學生數'

FROM XS GROUP BY 專業(yè)名;

使用帶rollup操作符的GROUP BY子句： 指定在結果集內不僅包含由 GROUP BY 提供的正常行，還包含匯總行。

例： 在XSCJ數據庫上產生一個結果集，包括每個專業(yè)的男生人數、女生人數、總人數，以及學生總人數。

SELECT 專業(yè)名, 性別, COUNT(*) AS '人數'

FROM XS GROUP BY 專業(yè)名,性別 with rollup;

使用了ROLLUP操作符后，將對GROUP BY子句中所指定的各列產生匯總行，產生的規(guī)則是：按列的排列的逆序依次進行匯總。如本例根據專業(yè)名和性別將XS表分為4組，使用ROLLUP后，先對性別字段產生了匯總行（針對專業(yè)名相同的行），然后對專業(yè)名與性別均不同的È產生了匯總行。所產生的匯總行中對應具有不同列È的字段È將置為NULL。

6. having子句（使用分組后在進行挑選）

使用having子句的目的與where子句類似，having與where的區(qū)別：WHERE子句是用來在FROM子句之后選擇行，where是對原始記錄進行挑選；而HAVING子句用來在GROUP BY子句后選擇行，對分組后的記錄進行挑選。

語法格式： having 選擇條件；

條件的定義和WHERE子句中的條件類似，不過HAVING子句中的條件可以包含聚合函數，而WHERE子句中則不可以。

SQL標準要求HAVING必須引用GROUP BY子句中的列或用于聚合函數中的列。不過，MySQL支持對此工作性質的擴展，并允許HAVING引用SELECT清單中的列和外部子查詢中的列。

例： 查找XSCJ數據庫中平均成績在85分以上的學生的學號和平均成績。

SELECT 學號, AVG(成績) AS '平均成績'

FROM XS_KC GROUP BY 學號 HAVING AVG(成績) >=85;

7. order by字句

在一條SELECT語句中，如果不使用order by子句，結果中行的順序是不可預料的。使用ORDER BY子句后可以保證結果中的行按一定順序排列。 語法格式：ORDER BY {col_name | expr | position} [ASC | DESC] , ...

說明：ORDER BY子句后可以是一個列、一個表達式或一個正整數。正整數表示按結果表中該位置上的列排序。關鍵字asc表示升序排列，desc表示降序排列，系統(tǒng)默認È為ASC。

例： 將通信工程專業(yè)的學生按出生日期先后排序。

SELECT 學號,姓名,專業(yè)名,出生日期 FROM XS

WHERE 專業(yè)名 = '通信工程' ORDER BY 出生日期;

注意：當對空È排序時，ORDER BY子句將空È作為最小È對待，按升序排列的話將空È放在最上方，降序放在最下方。

如果是按多個字段排序，先按第一個字段排，當第一個字段的È相同時，再按第二個排；如果第一個字段都不相同，則第二個字段用不著；order by語句在limit語句之前。

8. limit字句

LIMIT子句是SELECT語句的最后一個子句，主要用于被SELECT語句返回的行數。 語法格式：LIMIT {[offset,]row_count | row_count OFFSET offset}

說明：如果記錄特別多可以用limit語句分頁！

語法格式中的offset和row_count都必須是非負的整數常數，offset指定返回的第一行的偏移量，row_count是返回的行數。例如，“LIMIT 5”表示返回SELECT語句的結果集中最前面5行，而“LIMIT3，5”則表示從第4行開始返回5行。

È得注意的是初始行的偏移量為0而不是1。

例： 查找XS表中學號最靠前的5位學生的信息。

SELECT 學號, 姓名, 專業(yè)名, 性別, 出生日期, 總學分

FROM XS ORDER BY 學號 LIMIT 5;

為了與PostgreSQL兼容，MySQL也支持LIMIT row_count OFFSET offset語法。所以將上面例子中的LIMIT子句換成“l(fā)imit 5 offset 3”，結果一樣。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com