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

上一篇關系數(shù)據(jù)庫常用SQL語句語法大全主要是關系型數(shù)據(jù)庫大體結構，本文細說一下關系型數(shù)據(jù)庫查詢的SQL語法。

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

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

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

-- 創(chuàng)建學生表
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)建成績表
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
);

-- 插入學生表基礎數(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","濟南");
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","沈陽");
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016009,"靜靜","二班","女",20,"18817716699","北京");

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

-- 插入成績表基礎數(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);

簡單查詢只需要SELECT、FROM、WHERE3個關鍵字即可實現(xiàn)。

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

語法

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

AS可以省略
當別名中含有非字母和下劃線時或者是關鍵字時，需要加上單/雙引號

示例

SELECT Id AS "學號",Name "姓名",Class ClassName FROM Students;

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

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

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

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

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

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

HAVING子句

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

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

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

WHERE語句在GROUP BY語句之前，SQL會在分組之前計算WHERE語句；
HAVING語句在GROUP BY語句之后，SQL會在分組之后計算HAVING語句。

語法

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

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

單個字符匹配

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語句查詢結果都相同只針對此表，原因是瞎貓碰上死耗子。（數(shù)據(jù)少，剛好查詢的結果是全部的內容）

范圍內查詢

-- 查詢所有手機號碼結尾兩位包含1和2的信息
SELECT * FROM Students WHERE Phone LIKE "188177166[12][12]";

不在范圍內查詢

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

關于SQL查詢就到這里告一段落，后面會更新相關內容。
如果你覺得有問題，歡迎和你一起探討。