SQL總結（一）基本查詢

Coding-lover發表於2015-11-30
SQL

SQL查詢的事情很簡單，但是常常因為很簡單的事情而出錯。遇到一些比較複雜的查詢我們更是忘記了SQL查詢的基本語法。

本文希望通過簡單的總結，把常用的查詢方法予以總結，希望能夠明確在心。

場景：學生資訊系統，包括學生資訊、教師資訊、專業資訊和選課資訊。


--學生資訊表
IF OBJECT_ID (N'Students', N'U') IS NOT NULL
    DROP TABLE Students;
GO
CREATE TABLE Students(
    ID int primary key not null,
    Name nvarchar(50),
    Age int,
    City nvarchar(50),
    MajorID int
)


--專業資訊表
IF OBJECT_ID (N'Majors', N'U') IS NOT NULL
    DROP TABLE Majors;
GO
CREATE TABLE Majors(
    ID int primary key not null,
    Name nvarchar(50)
)

--課程表
IF OBJECT_ID (N'Courses', N'U') IS NOT NULL
    DROP TABLE Courses;
GO
CREATE TABLE Courses(
    ID int primary key not null,
    Name nvarchar(50) not null
)

IF OBJECT_ID (N'SC', N'U') IS NOT NULL
    DROP TABLE SC;
GO
--選課表
CREATE TABLE SC(
    StudentID int not null,
    CourseID int not null,
    Score int    
)

1、基本查詢

從表中查詢某些列的值，這是最基本的查詢語句。

SELECT 列名1,列名2 FROM 表名

2、Where（條件）

作用：按照一定的條件查詢資料

語法：

SELECT 列名1,列名2 FROM 表名 WHERE 列名1 運算子  值

運算子：

運算子 描述
= 等於
<> 不等於
> 大於
< 小於
>= 大於等於
<= 小於等於
BETWEEN 在某個範圍內
LIKE 搜尋某種模式


比較操作符都比較簡單，不再贅述。關於BETWEEN和LIKE，專門拿出來重點說下

3、BETWEEN

在兩個值之間，比如我從學生中查詢年齡在18-20之間的學生資訊

SELECT ID,Name,Age FROM Students WHERE Age BETWEEN 18 AND 20

4、LIKE

作用：模糊查詢。LIKE關鍵字與萬用字元一起使用

主要的萬用字元：

萬用字元 描述
% 替代一個或多個字元
_ 僅替代一個字元
[charlist] 字元列中的任何單一字元
[^charlist]
或者
[!charlist]		 不在字元列中的任何單一字元


例項：

1）查詢姓氏為張的學生資訊

SELECT ID,Name FROM Students WHERE Name LIKE '張%'

2）查詢名字最後一個為“生”的同學

SELECT ID,Name FROM Students WHERE Name LIKE '%生'

3）查詢名字中含有“生”的學生資訊

SELECT ID,Name FROM Students WHERE Name LIKE '%生%'

4）查詢姓名為兩個字，且姓張學生資訊

SELECT ID,Name FROM Students WHERE Name LIKE '張_'

5)查詢姓氏為張、李的學生資訊

這個可以使用or關鍵字，但是使用萬用字元更簡單高效

SELECT ID,Name FROM Students WHERE Name LIKE '[張李]%'

6)查詢姓氏非張、李的學生資訊

這個也可以使用NOT LIKE 來實現，用下面方法更好。

SELECT ID,Name FROM Students WHERE Name LIKE '[^張李]%'

或者：

SELECT ID,Name FROM Students WHERE Name LIKE '[!張李]%'

5、AND

AND 在 WHERE 子語句中把兩個或多個條件結合起來。表示和的意思，多個條件都成立。

1）查詢年齡大於18且姓張的學生資訊

SELECT ID,Name FROM Students WHERE Age>18 AND Name LIKE '張%'

6、OR

OR可在 WHERE 子語句中把兩個或多個條件結合起來。或關係，表示多個條件，只有一個符合即可。

1)查詢姓氏為張、李的學生資訊

SELECT ID,Name FROM Students WHERE Name LIKE '張%' OR Name LIKE '李%'

7、IN

IN 操作符允許我們在 WHERE 子句中規定多個值。表示：在哪些值當中。

1）查詢年齡是18、19、20的學生資訊

SELECT ID,Name FROM Students WHERE Age IN (18,19,20)

8、NOT 否定

NOT對於條件的否定，取非。

1）查詢非張姓氏的學習資訊

SELECT ID,Name FROM Students WHERE Name NOT LIKE '張%'

9、ORDER BY（排序）

功能：對需要查詢後的結果集進行排序

標識 含義 說明
ASC 升序 預設
DESC 倒序


例項：

1）查詢學生資訊表的學號、姓名、年齡，並按Age升序排列

SELECT ID,Name,Age FROM Students ORDER BY Age

或指明ASC

SELECT ID,Name,Age FROM Students ORDER BY Age ASC

2)查詢學生資訊，並按Age倒序排列

SELECT ID,Name,Age FROM Students ORDER BY Age DESC

除了制定某個列排序外，還能指定多列排序，每個排序欄位可以制定排序規則

說明：優先第一列排序，如果第一列相同，則按照第二列排序規則執行，以此類推。

3）查詢學生的資訊，按照總成績倒序、學號升序排列

SELECT ID,Name,Score FROM Students ORDER BY Score DESC,ID ASC

這個查詢含義：首先按Score倒序排列，如果有多條記錄Score相同，再按ID升序排列。

查詢結果，例子：

ID Name Score
2 廣坤 98
3 老七 98
1 趙四 79

10、AS(Alias)

可以為列名稱和表名稱指定別名（Alias）

作用：我們可以將查詢的列，或者表指定需要的名字，如表名太長，用其簡稱，在連表查詢中經常用到。

1) 將結果列改為需要的名稱

SELECT ID AS StudentID,Name AS StudentName FROM Students

2）用表名的別名，標識列的來源

SELECT S.ID,S.Name,M.Name AS MajorName 
FROM Students AS S 
LEFT JOIN Majors AS M
ON S.MajorID = M.ID

3)在合計函式中，給合計結果命名

SELECT COUNT(ID) AS StudentCount FROM Students

11、Distinct

含義：不同的

作用：查詢時忽略重複值。

語法：

SELECT DISTINCT 列名稱 FROM 表名稱

例項：

1）查詢學生所在城市名，排除重複

SELECT DISTINCT City FROM Student

2)查詢成績分佈分佈情況

SELECT DISTINCT(Score),Count(ID) FROM Student GROUP BY Score

學生成績可能重複，以此得到分數、得到這一成績的學生數。後續會詳細介紹GROUP BY 用法。

12、MAX/MIN

MAX 函式返回一列中的最大值。NULL 值不包括在計算中。

MIN 函式返回一列中的最小值。NULL 值不包括在計算中。

MIN 和 MAX 也可用於文字列，以獲得按字母順序排列的最高或最低值。

1）查詢學生中最高的分數

SELECT MAX(Score) FROM Students

2)查詢學生中最小年齡

SELECT MIN(Age) FROM Students

13、SUM

查詢某列的合計值。

1）查詢ID為1001的學生的各科總成績

SC即為學生的成績表，欄位：StudentID，CourseID，Score.

SELECT SUM(Score) AS TotalScore FROM SC WHERE StudentID='1001'

14、AVG

AVG 函式返回數值列的平均值

1)查詢學生的平均年齡

SELECT AVG(Age) AS AgeAverage FROM Students

2）求課程ID為C001的平均成績

SELECT AVG(Score) FROM SC WHERE CourseID='C001'

15、COUNT

COUNT() 函式返回匹配指定條件的行數。

1)查詢學生總數

SELECT COUNT(ID) FROM Students

2)查詢學生年齡分佈的總數

SELECT COUNT(DISTINCT Age) FROM Students

3)查詢男生總數

SELECT COUNT(ID) FROM Students WHERE Sex='男'

4）查詢男女生各有多少人

SELECT Sex,COUNT(ID) FROM Students GROUP BY Sex

16、GROUP BY

GROUP BY 語句用於結合合計函式，根據一個或多個列對結果集進行分組。

1）查詢男女生分佈，上面已經給了答案。

SELECT Sex,COUNT(ID) FROM Students GROUP BY Sex

2) 查詢學生的城市分佈情況

SELECT City,COUNT(ID) FROM Students GROUP BY City

3)學生的平均成績，查詢結果包括：學生ID，平均成績

SELECT StudentID,AVG(Score) FROM SC GROUP BY StudentID

4）刪除學生資訊中重複記錄

根據列進行分組，如果全部列相同才定義為重複，則就需要GROUP BY所有欄位。否則可按指定欄位進行處理。

DELETE FROM Students WHERE ID NOT IN (SELECT MAX(ID) FROM Students GROUP BY ID,Name,Age,Sex,City,MajorID)

17、HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 關鍵字無法與合計函式一起使用。

語法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

1）查詢平均成績大等於於60的學生ID及平均成績

SELECT StudentID,AVG(Score) FROM SC GROUP BY StudentID HAVING AVG(Score)>=60

2）還是用HAVING的SQL語句中，可以有普通的WHERE條件

查詢平均成績大於等於60，且學生ID等於1的學生的ID及平均成績。

SELECT StudentID,AVG(Score) FROM SC 
WHERE StudentID='1' 
GROUP BY StudentID 
HAVING AVG(Score)>=60

3）查詢總成績在600分以上（包括600）的學生ID

SELECT StudentID FROM SC GROUP BY StudentID HAVING SUM(Score)>=600

18、TOP

TOP 子句用於規定要返回的記錄的數目。對於大資料很有用的，在分頁時也會常常用到。

1）查詢年齡最大的三名學生資訊

SELECT TOP 3 ID,Name FROM Students ORDER BY Age DESC

2）還是上一道題，如果有相同年齡的如何處理呢？

SELECT ID,Name,Age FROM Students WHERE Age IN (SELECT TOP 3 Age FROM Students)

19、Case語句

計算條件列表，並返回多個可能的結果表示式之一。
CASE 表示式有兩種格式：

  • CASE 簡單表示式，它通過將表示式與一組簡單的表示式進行比較來確定結果。
  • CASE 搜尋表示式，它通過計算一組布林表示式來確定結果。

簡單表示式語法：

CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

搜尋式語法：

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

1）查詢學習資訊，如果Sex為0則顯示為男，如果為1顯示為女，其他顯示為其他。

SELECT ID, Name, CASE Sex WHEN '0' THEN '男' WHEN '1' THEN '女' ELSE '其他' END AS Sex
FROM Students

2）查詢學生資訊，根據年齡統計是否成年，大於等於18為成年，小於18為未成年

SELECT ID, Name, CASE WHEN Age>=18 THEN '成年' ELSE '未成年'END AS 是否成年
FROM Students

3）統計成年未成年學生的個數

要求結果

成年 未成年
23 6


SQL語句

SELECT SUM(CASE WHEN Age>=18 THEN  1 ELSE 0 END) AS '成年',SUM(CASE WHEN Age<18 THEN  1 ELSE 0 END) AS '未成年'
FROM Students

4）行列轉換。統計男女生中未成年、成年的人數

結果如下：

性別 未成年 成年
男 3 13
女 2 18


SQL語句:

SELECT CASE WHEN Sex=0 THEN '男' ELSE '女' END AS '性別',
SUM(CASE WHEN Age<18 THEN 1 ELSE 0 END) AS '未成年', 
SUM(CASE WHEN Age>=18 THEN 1 ELSE 0 END) AS '成年'
FROM Students
GROUP BY Sex

轉載自：SQL總結（一）基本查詢

相關文章