那些年我們一起做過的[分組查詢]_MySQL

分組查詢

在SQL Server中使用的分組查詢是ORDER BY子句，使用ORDER BY子句要同聚合函數(shù)配合使用才能完成分組查詢，在SELECT查詢的字段中如果字段沒有使用聚合函數(shù)就必須出現(xiàn)在ORDER BY子句中（即SELECT后邊的字段名要么出現(xiàn)在聚合函數(shù)中，要么在ORDER BY子句中使用）

使用group by進(jìn)行分組查詢

在使用group by關(guān)鍵字時(shí)，在select列表中可以指定的項(xiàng)目是有限制的，select語句中僅許以下幾項(xiàng)：

*被分組的列

*為每個(gè)分組返回一個(gè)值得表達(dá)式，例如用一個(gè)列名作為參數(shù)的聚合函數(shù)

注意:group by 有一個(gè)原則,就是 select 后面的所有列中,沒有使用聚合函數(shù)的列,必須出現(xiàn)在 group by 后面

HAVING子句與WHERE子句的區(qū)別

HAVING子句和WHERE子句的相似之處在于，它也定義搜索條件。但與WHERE子句不同，HAVING子句與組有關(guān)，而不是與單個(gè)的行有關(guān)。

1、如果指定了GROUP BY子句，那么HAVING子句定義的搜索條件將作用于這個(gè)GROUP BY子句創(chuàng)建的那些組。

2、如果指定WHERE子句，而沒有指定GROUP BY子句，那么HAVING子句定義的搜索條件將作用于WHERE子句的輸出，并把這個(gè)輸出看作是一個(gè)組。

3、如果既沒有指定GROUP BY子句也沒有指定WHERE子句，那么HAVING子句定義的搜索條件將作用于FROM子句的輸出，并把這個(gè)輸出看作是一個(gè)組。

4、在SELECT語句中，WHERE和HAVING子句的執(zhí)行順序不同。上面SELECT語句的執(zhí)行步驟可知，WHERE子句只能接收來自FROM子句的輸入，而HAVING子句則可以接收來自GROUP BY子句、WHERE子句和FROM子句的輸入。

S-TQL語句執(zhí)行順序

select 5:投影 ，映射為對(duì)應(yīng)的列

from 1:定位到表

where 2:進(jìn)行分組前的第一次篩選

group by 3:分組

having 4:對(duì)分組后的數(shù)據(jù)進(jìn)行第二次篩選

order by 6:對(duì)映射出的結(jié)果進(jìn)行排序

注意:where后面不能加聚合函數(shù)

三個(gè)順序：

01where：對(duì)表中記錄進(jìn)行篩選（分組前）

02group by 分組依據(jù)

03having 對(duì)分組后的數(shù)據(jù)進(jìn)行篩選

見到having,之前必須有g(shù)roup by,因?yàn)閔aving是對(duì)分組后的數(shù)據(jù)進(jìn)行篩選

三者使用順序不可顛倒

eg:

myschool數(shù)據(jù)庫(kù)中有四張表,分別為student(學(xué)生表)result(成績(jī)表)subject(科目表)grade(年級(jí)表)

01查詢每個(gè)年級(jí)的總學(xué)時(shí)數(shù),并按照升序排列

題目是每個(gè)年級(jí)的總學(xué)時(shí)數(shù),每個(gè)年級(jí)肯定做為group by的分組依據(jù),總學(xué)時(shí)則利用sum()函數(shù)

至于order by后也可以跟SUM(classhour).寫總學(xué)時(shí)數(shù)的目的是為了更詳細(xì)的理解語句的執(zhí)行順序

select gradeid as 年級(jí)編號(hào),SUM(classhour) as 總學(xué)時(shí)數(shù)

from Subject

group by GradeId

order by 總學(xué)時(shí)數(shù)

02查詢每個(gè)參加考試的學(xué)員的平均分

每個(gè)參加考試的學(xué)員平均分,學(xué)員對(duì)應(yīng)學(xué)員編號(hào)肯定為group by的分組依據(jù),平均分利用avg()函數(shù)計(jì)算出來

select studentno as 學(xué)員編號(hào),AVG(studentresult) as 平均分

from Result

group by StudentNo

--03查詢每門課程的平均分,并按照降序排列

每門課的平均分,按降序排列,group by的分租依據(jù)是subjectid(課程編號(hào))平均分則利用avg()函數(shù)計(jì)算出來

利用order by 平均分 desc 來進(jìn)行降序排列

select subjectid as 課程編號(hào) , SUM(studentresult) as 平均分

from Result

group by SubjectId

order by 平均分 desc

04查詢每個(gè)學(xué)生參加的所有考試的總分,并按照降序排列

每個(gè)學(xué)生所有考試總分,按降序排列,group by的分組依據(jù)是studentno(學(xué)生編號(hào)),總分利用sum()函數(shù)進(jìn)行計(jì)算

最后利用order by總分desc來進(jìn)行降序排序

select studentno as 學(xué)生編號(hào) ,SUM(StudentResult) as 總分

from Result

group by StudentNo

order by 總分 desc

05查詢每學(xué)期學(xué)時(shí)數(shù)超過50的課程數(shù)

每學(xué)期學(xué)時(shí)超過50的課程數(shù),group by的分組依據(jù)是gradeid(年級(jí)編號(hào)),現(xiàn)有一個(gè)限定條件學(xué)時(shí)不超過50,這里用where來限定他classhour>50

select * from Subject

select gradeid as 年級(jí)編號(hào),COUNT(classhour) as 課程數(shù)

from Subject

where classhour>50

group by GradeId

06查詢每學(xué)期學(xué)生的平均年齡

每學(xué)期學(xué)生的平均年齡,group by的分組依據(jù)是studentno(學(xué)生編號(hào)),平均年齡用datediff()進(jìn)行處理得出年齡,用avg()在進(jìn)行處理,得出平均年齡.

select studentno as 學(xué)生編號(hào),AVG(DATEDIFF(yy,birthday,GETDATE())) as 平均年齡

from Student

group by StudentNo

07查詢北京地區(qū)的每學(xué)期學(xué)生人數(shù)

北京地區(qū)的每學(xué)期學(xué)生人數(shù).group by的分組依據(jù)是gradeid ,學(xué)生人數(shù)用count(studentno)得出學(xué)生人數(shù),最后有個(gè)限定條件,只查詢北京地區(qū)的,用where進(jìn)行限定,address like '%北京%'%代表任意長(zhǎng)度的字符.

select gradeid as 年級(jí)編號(hào),COUNT(studentno) as 學(xué)生人數(shù)

from Student

where Address like '%北京%'

group by GradeId

08查詢參加考試的學(xué)生中平均分及格的學(xué)生記錄,并按照成績(jī)降序排列

參加考試的學(xué)生中平均分及格的學(xué)生記錄,按降序排列,group by 分組依據(jù)是studentno,平均分用av(studentresult)進(jìn)行計(jì)算出平均分,having進(jìn)行判定avg(studentresult)>60是否及格,在用order by 平均分及格 desc進(jìn)行降序排列.

select Studentno as 學(xué)生編號(hào),AVG(Studentresult) as 平均分及格

from Result

group by StudentNo

having AVG(Studentresult)>=60

order by 平均分及格 desc

09查詢考試日期為2009年9月9號(hào)的課程的及格平均分

考試日期為2009年9月9號(hào)的課程的及格平均分,group by 的分組依據(jù)是課程編號(hào),限定條件是考試日期為2009年9月9號(hào)

where ExamDate>='2009-9-9' and ExamDate<'2009-9-10'.最后進(jìn)行過濾及格平均分having avg(studentresult)>=60.

select subjectid,AVG(studentresult) as 及格平均分

from Result

where ExamDate>='2009-9-9' and ExamDate<'2009-9-10'

group by SubjectId

having AVG(StudentResult)>=60

10查詢至少一次考試不及格的學(xué)生學(xué)號(hào),不及格次數(shù)

考試不及格的學(xué)生學(xué)號(hào),不及格次數(shù),先限定條件不及格的學(xué)生成績(jī),在按照學(xué)生學(xué)號(hào)進(jìn)行分組,group by studentno

select studentno as 學(xué)生編號(hào),COUNT(1) as 次數(shù)

from Result

where StudentResult<60

group by StudentNo

學(xué)生數(shù)據(jù)庫(kù)中數(shù)據(jù)的增加、修改和刪除

目標(biāo)：

1：使用T-SQL向表中插入數(shù)據(jù)

2：使用T-SQL更新表中數(shù)據(jù)

3：使用T-SQL刪除表中數(shù)據(jù)

首先我們來簡(jiǎn)單的介紹一下SQL及它的作用是什么

在進(jìn)行數(shù)據(jù)庫(kù)管理時(shí)，如果每次創(chuàng)建數(shù)據(jù)庫(kù)、表或者從數(shù)據(jù)庫(kù)中讀取數(shù)據(jù)，都需要手動(dòng)在SQL Server Management Studio中進(jìn)行的話，不但管理不方便，而且存儲(chǔ)在數(shù)據(jù)庫(kù)中的數(shù)據(jù)也根本無法提供給程序使用。所以，數(shù)據(jù)庫(kù)也需要一套指令集，能夠識(shí)別指令、執(zhí)行相應(yīng)的操作為程序提供數(shù)據(jù)，目前表中的指令集及時(shí)SQL語言。

SQL語言是真的數(shù)據(jù)庫(kù)而言的一門語言，它可以創(chuàng)建數(shù)據(jù)庫(kù)、數(shù)據(jù)表，可以針對(duì)數(shù)據(jù)庫(kù)的數(shù)據(jù)進(jìn)行增、刪、改、查等操作，可以常見視圖、存儲(chǔ)過程，可以賦予用戶權(quán)限等。

SQL中的運(yùn)算符

運(yùn)算符是一種符號(hào)，是用來進(jìn)行列間或者變量之間的比較和數(shù)學(xué)運(yùn)算的。在SQL中，常用的運(yùn)算符有算數(shù)運(yùn)算符、賦值運(yùn)算符、比較運(yùn)算符和邏輯運(yùn)算符。

1：算數(shù)運(yùn)算符

算數(shù)運(yùn)算符包括：+（加），—（減），*（乘），/（除），%（模）五個(gè)。算數(shù)運(yùn)算符用來在兩個(gè)數(shù)或表達(dá)式上執(zhí)行數(shù)學(xué)運(yùn)算，這兩個(gè)表達(dá)式可以是任意兩個(gè)數(shù)字?jǐn)?shù)據(jù)類型的表達(dá)式

運(yùn)算符 說明

+ 加運(yùn)算，求兩個(gè)數(shù)或表達(dá)式相加的和

— 減運(yùn)算，求兩個(gè)數(shù)或表達(dá)式相減的差

* 乘運(yùn)算，求兩個(gè)數(shù)或表達(dá)式相乘的積

/ 除運(yùn)算，求兩個(gè)數(shù)或表達(dá)式相除的商，例如，5/5的值為1,5.7/3的值為1.900000

% 取模運(yùn)算，求兩個(gè)數(shù)或表達(dá)式相除的余數(shù)，例如，5%3的值為2

2：賦值運(yùn)算符

SQL有一個(gè)賦值運(yùn)算符，即“=”（等號(hào)），用于將一個(gè)數(shù)或變量或表達(dá)式賦值給另一個(gè)變量

運(yùn)算符 說明

= 吧一個(gè)數(shù)或變量或表達(dá)式賦值給另一個(gè)變量，例如：Name=‘張三’

3:比較運(yùn)算符

比較運(yùn)算符用來判斷兩個(gè)表達(dá)式的大小關(guān)系，除text、ntext或Image數(shù)據(jù)類型的表達(dá)式外，比較運(yùn)算符幾乎可以用于其他所有的表達(dá)式。

運(yùn)算符 說明

= 等于，例如：age=23

> 大于，例如：price>100

< 小于

<> 不等于

>= 大于等于

<= 小于等于

!= 不等于（非SQL-92標(biāo)準(zhǔn)）

4：邏輯運(yùn)算符

邏輯運(yùn)算符用來對(duì)某個(gè)條件進(jìn)行判斷，以獲得判斷條件的真假，返回帶有TRUE或FALSE值的布爾數(shù)據(jù)類型

運(yùn)算符 說明

AMD 當(dāng)且僅當(dāng)兩個(gè)布爾表達(dá)式都為TRUE時(shí)，返回TRUE

OR 當(dāng)且僅當(dāng)兩個(gè)布爾表達(dá)式都為FALSE時(shí)，返回FALSE

NOT 對(duì)布爾表達(dá)式的值取反，有限級(jí)別最好

——————————————————————————————————————————————————————————————————————

使用T-SQL插入數(shù)據(jù)

1.使用INSERT插入數(shù)據(jù)（使用INSERT語句一行一行的插入數(shù)據(jù)是最常用的方法）

語法如下：

INSERT [INTO] 表名 [(列名列表)] VALUES(值類表);

其中

1.[INTO]是可選的，也可以省略。

2.表名是必須的。

3.表的列名是可選的，如果省略，將依次插入所有列。

4.多個(gè)列名和多個(gè)值列表用逗號(hào)分隔。

5.分號(hào)（;）是T-SQL語句終止符，分好不是必須的。

一次插入多行數(shù)據(jù)

1.通過INSERT SELECT語句向表中添加數(shù)據(jù)

例如，創(chuàng)建一張新表AddressList來存儲(chǔ)本班的通訊信息，則可以從學(xué)生表中提取相關(guān)的數(shù)據(jù)插入建好的AddressList表中，語句如下：

INSERT INTO Addresslist(姓名，地址，電子郵件)

SELECT SName,SAdd熱身賽，SEmail

FROM Students

注意：

查詢的多的數(shù)據(jù)個(gè)數(shù)，順序，數(shù)據(jù)類型等，必須與插入的項(xiàng)保持一致。

AddressList表必須預(yù)先創(chuàng)建好，并且具有姓名，地址和電子郵件三個(gè)列。

2.通過SELECT INTO語句將現(xiàn)有表中的數(shù)據(jù)添加到新表中

與上面的INSERT INTO 類似，SELECT INTO語句也是從一個(gè)表中選擇一些數(shù)據(jù)插入新表中，所不同的是，這個(gè)新表是執(zhí)行查詢語句的時(shí)候創(chuàng)建的，不能夠預(yù)先存在。

例：

SELECT Students.Name,Students.SAddress,Students.SEMmail

INTO AddressList

FROM Students

將創(chuàng)建新表的AddressList，把Students表中的SName，SAddress，SEmail作為AddressList表的新列，并且把查詢到的數(shù)據(jù)全部插入新表中。

3.通過UNION關(guān)鍵字合并數(shù)據(jù)進(jìn)行插入

UNION語句用于將兩個(gè)不同的數(shù)據(jù)或查詢結(jié)果組合成一個(gè)新的結(jié)果集。當(dāng)然，不同的數(shù)據(jù)或查詢結(jié)果，也要求數(shù)據(jù)個(gè)數(shù)，順序，數(shù)據(jù)類型都一致，因此，當(dāng)向表中多次插入數(shù)據(jù)的時(shí)候，可以使用SELECT...UNION來簡(jiǎn)化操作

例：

INSERT Student(SName,SGrade,SSex)

SELECT '張三',7,1 UNION

SELECT '李四',4,0 UNION

SELECT '王五',2,0 UNION

SELECT '朱六',3,0 UNION

SELECT '王二麻子',7,1 UNION

這樣的效果其實(shí)與上面INSERT...SELECT的效果是一樣的，只不過多行數(shù)據(jù)是后寫的，然后用UNION合并組成多行數(shù)據(jù)記錄，最后把這些多行數(shù)據(jù)記錄一起插入

——————————————————————————————————————————————————————————————————————

使用T-SQL更新數(shù)據(jù)

語法：

UPDATE 表名 SET 列名 = 更新值 [WHERE 更新條件]

其中：

1.SET后面可以緊隨多個(gè)”列名=更新值“，修改東哥數(shù)據(jù)列的值，不限一個(gè)，使用逗號(hào)分隔。

2.WHERE子句是可選的，用來限制更新數(shù)據(jù)的條件。若不限制，則整個(gè)表的數(shù)據(jù)行將被更新。

使用T-SQL刪除數(shù)據(jù)

語法：

DELETE [FROM] 表名 [WHERE <刪除條件>]

例：

在學(xué)生信息表中刪除姓名為”張三“的數(shù)據(jù)

DELETE FROM Students

WHERE SName = '張三'

使用TTRUNCATE TABLE刪除數(shù)據(jù)

TTRUNCATE TABLE用來刪除表中說有行，功能上它類似于沒有WHERE子句的DELETE語句。

例如，要?jiǎng)h除學(xué)生信息表中的所有記錄行，可以使用以下語句

TRNCATE TABLE Students

但TRUNCATE TABLE 比DELETE 執(zhí)行速度更快，使用的系統(tǒng)資源和事務(wù)日志資源更少，并且刪除數(shù)據(jù)后表的標(biāo)識(shí)列會(huì)重新開始編號(hào)。

實(shí)際工作中，不建議使用TRUNCATE TABLE 語句，因?yàn)槭褂盟鼊h除的數(shù)據(jù)不能恢復(fù)還原。

友情提示：刪除前問自己三遍是不是真的要?jiǎng)h除，問下令刪除的人三遍是否要?jiǎng)h除，確定沒有遺漏后執(zhí)行操作。

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