摘要：連接查詢涉及兩個(gè)及以上的表查詢?yōu)檫B接查詢。查詢二班學(xué)生成績(jī)二班聚合函數(shù)查詢聚合函數(shù)是一個(gè)值的集合為輸入，返回單個(gè)值的函數(shù)。具體的數(shù)據(jù)庫(kù)還會(huì)預(yù)定義一些其他常用的函數(shù)，比如字符串相聚合函數(shù)時(shí)間聚合函數(shù)。

上一篇關(guān)系數(shù)據(jù)庫(kù)常用SQL語(yǔ)句語(yǔ)法大全主要是關(guān)系型數(shù)據(jù)庫(kù)大體結(jié)構(gòu)，本文細(xì)說(shuō)一下關(guān)系型數(shù)據(jù)庫(kù)查詢的SQL語(yǔ)法。

SELECT [ALL|DISTINCT] <目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]…
  FROM <表名或視圖名>[,<表名或視圖名>]…
  [WHERE <條件表達(dá)式>]
  [GROUP BY <列名> [HAVING <條件表達(dá)式>]]
  [ORDER BY <列名> [ASC|DESC]…]

SQL查詢語(yǔ)句的順序：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。SELECT、FROM是必須的，HAVING子句只能與GROUP BY搭配使用。

1.創(chuàng)建數(shù)據(jù)庫(kù)和數(shù)據(jù)表
2.插入基本數(shù)據(jù)
3.本文以SQL Server為例介紹

-- 創(chuàng)建學(xué)生表
CREATE TABLE Students(
    Id int NOT NULL PRIMARY KEY,
    Name varchar(20) NOT NULL,
    Class varchar(15) NOT NULL,
    Gender varchar(10) NULL,
    Age int NULL,
    Phone varchar(15) NULL,
    [Address] varchar(100) NULL
)

-- 創(chuàng)建課程表
CREATE TABLE Courses(
    Id int NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name varchar(50) NOT NULL
);

-- 創(chuàng)建成績(jī)表
CREATE TABLE Scores(
    Id int PRIMARY KEY IDENTITY(1,1),
    SId int NOT NULL,
    CId int NOT NULL,
    Grades decimal(5,2) NOT NULL,
    IsPassed bit NOT NULL
);

-- 插入學(xué)生表基礎(chǔ)數(shù)據(jù)
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016001,"小明","一班","男",20,"18817716611","北京");
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016002,"小龍","一班","男",19,"18817716622","天津");
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016003,"小王","二班","男",20,"18817716633","北京");
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016004,"婷婷","一班","女",17,"18817716644","濟(jì)南");
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016005,"張三","一班","男",19,"18817716655","北京");
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016006,"小趙","一班","男",20,"18817716666","北京");
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016007,"麗麗","二班","女",18,"18817716677","北京");
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016008,"花花","一班","女",19,"18817716688","沈陽(yáng)");
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016009,"靜靜","二班","女",20,"18817716699","北京");

-- 插入課程表基礎(chǔ)數(shù)據(jù)
INSERT INTO Courses(Name) VALUES("語(yǔ)文");
INSERT INTO Courses(Name) VALUES("數(shù)學(xué)");
INSERT INTO Courses(Name) VALUES("英語(yǔ)");

-- 插入成績(jī)表基礎(chǔ)數(shù)據(jù)
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016001,1,120,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016001,2,70,0);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016001,3,89,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016002,1,90,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016002,2,88,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016002,3,96,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016003,1,112,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016003,3,102,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016004,1,80,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016004,2,86,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016004,3,47,0);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016005,1,87,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016005,2,96,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016005,3,68,0);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016006,2,95,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016006,3,100,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016007,1,87,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016007,2,57,0);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016007,3,130,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016008,1,89,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016008,3,66,0);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016009,1,97,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016009,2,104,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016009,3,68,1);

簡(jiǎn)單查詢只需要SELECT、FROM、WHERE3個(gè)關(guān)鍵字即可實(shí)現(xiàn)。

SELECT * FROM Students;
SELECT * FROM Students WHERE Class="一班";
SELECT * FROM Students WHERE Class="一班" AND Age = 20;

語(yǔ)法

SELECT 字段名1 [AS] 別名 [,字段名1 AS 別名]… FROM <表名>

AS可以省略
當(dāng)別名中含有非字母和下劃線時(shí)或者是關(guān)鍵字時(shí)，需要加上單/雙引號(hào)

示例

SELECT Id AS "學(xué)號(hào)",Name "姓名",Class ClassName FROM Students;

子查詢也稱嵌套查詢，是指一個(gè)SELECT查詢語(yǔ)句可以嵌入另一個(gè)SELECT查詢語(yǔ)句之中。SQL中允許多級(jí)嵌套，子查詢?cè)趯?shí)際使用中非常多。
連接查詢：涉及兩個(gè)及以上的表查詢?yōu)檫B接查詢。

--查詢二班學(xué)生成績(jī)
SELECT * FROM Scores WHERE SId IN(SELECT Id FROM Students WHERE Class="二班")

聚合函數(shù)：是一個(gè)值的集合為輸入，返回單個(gè)值的函數(shù)。
SQL預(yù)定義了5個(gè)聚集函數(shù)：AVG（平均值）、MIN（最小值）、MAX（最大值）、SUM（求和）、COUNT（計(jì)數(shù)）。
具體的數(shù)據(jù)庫(kù)還會(huì)預(yù)定義一些其他常用的函數(shù)，比如字符串相聚合函數(shù)、時(shí)間聚合函數(shù)……。

SELECT AVG(Age),MAX(Age),MIN(Age),SUM(Age),COUNT(Id) FROM Students;

使用GROUP BY子句可進(jìn)行分組查詢
注意：分組查詢的時(shí)候要在GROUP BY子句后面跟上所有查詢字段的列表

--根據(jù)班級(jí)分組查詢各班平均年齡、最大 年齡、最小年齡、年齡總和、班級(jí)人數(shù)
SELECT Class,AVG(Age),MAX(Age),MIN(Age),SUM(Age),COUNT(Id) FROM Students GROUP BY Class;

HAVING子句

如果需要在分組前的數(shù)據(jù)進(jìn)行限制，可以使用HAVING子句
HAVING子句只能與GROUP BY搭配使用

--根據(jù)班級(jí)分組查詢各班平均年齡并且班級(jí)人數(shù)大于3人
SELECT Class,AVG(Age) FROM Students GROUP BY Class HAVING COUNT(Id)>3;

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

WHERE語(yǔ)句在GROUP BY語(yǔ)句之前，SQL會(huì)在分組之前計(jì)算WHERE語(yǔ)句；
HAVING語(yǔ)句在GROUP BY語(yǔ)句之后，SQL會(huì)在分組之后計(jì)算HAVING語(yǔ)句。

語(yǔ)法

SELECT 字段列表 FROM 表名 WHERE 字段 LIKE "<通配符>"

模糊查詢是通過(guò)關(guān)鍵字LIKE和通配符實(shí)現(xiàn)的
_：任何單個(gè)字符（一個(gè)"_"只匹配一個(gè)字符，多個(gè)字符就使用多個(gè)_）
%：包含零個(gè)或更多字符的任意字符串（匹配任意內(nèi)容）
[]：指定范圍的字符（只匹配[]內(nèi)的字符）
[ ^]：不在指定范圍的字符（只匹配除[]內(nèi)的字符）
SQL中通配符可以混合使用

單個(gè)字符匹配

SELECT * FROM Students WHERE Name LIKE "小_";
SELECT * FROM Students WHERE Phone LIKE "188177166__";

任意字符匹配

SELECT * FROM Students WHERE Phone LIKE "188177166__";
SELECT * FROM Students WHERE Phone LIKE "%";
SELECT * FROM Students;

這三條SQL語(yǔ)句查詢結(jié)果都相同只針對(duì)此表，原因是瞎貓碰上死耗子。（數(shù)據(jù)少，剛好查詢的結(jié)果是全部的內(nèi)容）

范圍內(nèi)查詢

-- 查詢所有手機(jī)號(hào)碼結(jié)尾兩位包含1和2的信息
SELECT * FROM Students WHERE Phone LIKE "188177166[12][12]";

不在范圍內(nèi)查詢

-- 查詢所有手機(jī)號(hào)碼結(jié)尾兩位不包含1、3、5、7的信息
SELECT * FROM Students WHERE Phone LIKE "188177166[^1357][^1357]";

關(guān)于SQL查詢就到這里告一段落，后面會(huì)更新相關(guān)內(nèi)容。
如果你覺(jué)得有問(wèn)題，歡迎和你一起探討。