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    
)

从表中查询某些列的值，这是最基本的查询语句。

SELECT 列名1,列名2 FROM 表名

作用：按照一定的条件查询数据
语法：

SELECt 列名1,列名2 FROM 表名 WHERe 列名1 运算符  值

运算符：

比较操作符都比较简单，不再赘述。关于BETWEEN和LIKE，专门拿出来重点说下。

在两个值之间，比如我从学生中查询年龄在18-20之间的学生信息

SELECt ID,Name,Age FROM Students WHERe Age BETWEEN 18 AND 20

作用：模糊查询。LIKE关键字与通配符一起使用
主要的通配符：

实例：

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 '[!张李]%'

AND 在 WHERe 子语句中把两个或多个条件结合起来。表示和的意思，多个条件都成立。

1）查询年龄大于18且姓张的学生信息

SELECt ID,Name FROM Students WHERe Age>18 AND Name LIKE '张%'

OR可在WHERe 子语句中把两个或多个条件结合起来。或关系，表示多个条件，只有一个符合即可。

1)查询姓氏为张、李的学生信息

SELECt ID,Name FROM Students WHERe Name LIKE '张%' OR Name LIKE '李%'

IN 操作符允许我们在 WHERe 子句中规定多个值。表示：在哪些值当中。

1）查询年龄是18、19、20的学生信息

SELECt ID,Name FROM Students WHERe Age IN (18,19,20)

NOT对于条件的否定，取非。

1）查询非张姓氏的学习信息

SELECt ID,Name FROM Students WHERe Name NOT LIKE '张%'

功能：对需要查询后的结果集进行排序

实例：

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升序排列。

查询结果，例子：

可以为列名称和表名称指定别名（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

含义：不同的
作用：查询时忽略重复值。
语法：

SELECt DISTINCT 列名称 FROM 表名称

实例：

1）查询学生所在城市名，排除重复

SELECt DISTINCT City FROM Student

2 ) 查询成绩分布分布情况

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

学生成绩可能重复，以此得到分数、得到这一成绩的学生数。后续会详细介绍GROUP BY 用法。

• MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
• MIN 函数返回一列中的最小值。NULL 值不包括在计算中。
• MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。

1）查询学生中最高的分数

SELECt MAX(Score) FROM Students

2 ) 查询学生中最小年龄

SELECt MIN(Age) FROM Students

查询某列的合计值。

1）查询ID为1001的学生的各科总成绩

SC即为学生的成绩表，字段：StudentID，CourseID，Score.

SELECt SUM(Score) AS TotalScore FROM SC WHERe StudentID='1001' 

AVG 函数返回数值列的平均值

1 ) 查询学生的平均年龄

SELECt AVG(Age) AS AgeAverage FROM Students

2）求课程ID为C001的平均成绩

SELECt AVG(Score) FROM SC WHERe CourseID='C001'

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

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)

在 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

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)

计算条件列表，并返回多个可能的结果表达式之一。
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）统计成年未成年学生的个数

要求结果

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）行列转换。统计男女生中未成年、成年的人数

结果如下：

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

作者：停留的风
文章源自：http://www.cnblogs.com/yank/p/3672478.html

1.15个初学者必看的基础SQL查询语句

2.比较全面的MySQL优化参考

3.SQL的几种连接：内连接、左联接、右连接、全连接、交叉连接

4.更好的 SQL 模式的 10 条规则

5.常用的MySQL语句写法