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
2.使用having子句进行分组筛选。次序为：where ,group by , having，其中where、groupby可以单独使用，而having一般是和分组查询一起使用的。例：select studentID,courseID,avg(分数列) from 分数表 where 分数列 >= 60 group by studentID,courseID having count(分数列) > 1。按学员编号，内部测试编号分组查询分数在60分以上的平均数。
＊having 与where 的异同点
having与where类似，可以筛选数据，where后的表达式怎么写，having后就怎么写
where针对表中的列发挥作用，查询数据
having对查询结果中的列发挥作用，筛选数据
#查询本店商品价格比市场价低多少钱，输出低200元以上的商品
select goods_id,good_name,market_price - shop_price as s from goods having s>200 ;
//这里不能用where因为s是查询结果，而where只能对表中的字段名筛选
如果用where的话则是：
select goods_id,goods_name from goods where market_price - shop_price > 200;
#同时使用where与having
select cat_id,goods_name,market_price - shop_price as s from goods where cat_id = 3 having s > 200;
#查询积压货款超过2万元的栏目，以及该栏目积压的货款
select cat_id,sum(shop_price * goods_number) as t from goods group by cat_id having s > 20000
#查询两门及两门以上科目不及格的学生的平均分
思路：
#先计算所有学生的平均分
select name,avg(score) as pj from stu group by name;
#查出所有学生的挂科情况
select name,score<60 from stu;
#这里score<60是判断语句，所以结果为真或假，mysql中真为1假为0
#查出两门及两门以上不及格的学生
select name,sum(score<60) as gk from stu group by name having gk > 1;
#综合结果
select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk >1;
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
二、mysql子查询
1、where型子查询
（把内层查询结果当作外层查询的比较条件）
#不用order by 来查询最新的商品
select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);
#取出每个栏目下最新的产品(goods_id唯一)
select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);
2、from型子查询
(把内层的查询结果供外层再次查询)
#用子查询查出挂科两门及以上的同学的平均成绩
思路：
#先查出哪些同学挂科两门以上
select name,count(*) as gk from stu where score < 60 having gk >=2;
#以上查询结果，我们只要名字就可以了，所以再取一次名字
select name from (select name,count(*) as gk from stu having gk >=2) as t;
#找出这些同学了，那么再计算他们的平均分
select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;
3、exists型子查询
（把外层查询结果拿到内层，看内层的查询是否成立）
#查询哪些栏目下有商品，栏目表category,商品表goods
select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);
三、union的用法
（把两次或多次的查询结果合并起来，要求查询的列数一致，推荐查询的对应的列类型一致，可以查询多张表，多次查询语句时如果列名不一
样，则取第一次的列名！如果不同的语句中取出的行的每个列的值都一样，那么结果将自动会去重复，如果不想去重复则要加all来声明，即union all）
## 现有表a如下
id num
a 5
b 10
c 15
d 10
表b如下
id num
b 5
c 10
d 20
e 99
求两个表中id相同的和
select id,sum(num) from (select * from ta union select * from tb) as tmp group by id;
//以上查询结果在本例中的确能正确输出结果，但是，如果把tb中的b的值改为10以查询结果的b的值就是10了，因为ta中的b也是10，所以union后会被过滤掉一个重复的结果，这时就要用union all
select id,sum(num) from (select * from ta union all select * from tb) as tmp group by id;
#取第4、5栏目的商品，按栏目升序排列，每个栏目的商品价格降序排列，用union完成
select goods_id,goods_name,cat_id,shop_price from goods where cat_id=4 union select goods_id,goods_name,cat_id,shop_price from goods where cat_id=5 order by cat_id,shop_price desc;
【如果子句中有order by 需要用( ) 包起来，但是推荐在最后使用order by，即对最终合并后的结果来排序】
#取第3、4个栏目，每个栏目价格最高的前3个商品，结果按价格降序排列
(select goods_id,goods_name,cat_id,shop_price from goods where cat_id=3 order by shop_price desc limit 3) union (select goods_id,goods_name,cat_id,shop_price from goods where cat_id=4 order by shop_price desc limit 3) order by shop_price desc;
四、左连接，右连接，内连接
现有表a有10条数据，表b有8条数据，那么表a与表b的笛尔卡积是多少？
select * from ta,tb //输出结果为8*10=80条
1、左连接
以左表为准，去右表找数据，如果没有匹配的数据，则以null补空位，所以输出结果数>=左表原数据数
语法：select n1,n2,n3 from ta left join tb on ta.n1= ta.n2 [这里on后面的表达式，不一定为=，也可以>，<等算术、逻辑运算符]【连接完成后，可以当成一张新表来看待，运用where等查询】
#取出价格最高的五个商品，并显示商品的分类名称
select goods_id,goods_name,goods.cat_id,cat_name,shop_price from goods left join category on goods.cat_id = category.cat_id order by shop_price desc limit 5;
2、右连接
a left join b 等价于 b right join a
推荐使用左连接代替右连接
语法：select n1,n2,n3 from ta right join tb on ta.n1= ta.n2
3、内连接
查询结果是左右连接的交集，【即左右连接的结果去除null项后的并集（去除了重复项）】
mysql目前还不支持 外连接（即左右连接结果的并集,不去除null项）
语法：select n1,n2,n3 from ta inner join tb on ta.n1= ta.n2
#########
例：现有表a
name hot
a 12
b 10
c 15
表b:
name hot
d 12
e 10
f 10
g 8
表a左连接表b，查询hot相同的字段
select a.*,b.* from a left join b on a.hot = b.hot
查询结果：
name hot name hot
a 12 d 12
b 10 e 10
b 10 f 10
c 15 null null
从上面可以看出，查询结果表a的列都存在，表b的数据只显示符合条件的项目
再如表b左连接表a，查询hot相同的数据
select a.*,b.* from b left join a on a.hot = b.hot
查询结果为：
name hot name hot
d 12 a 12
e 10 b 10
f 10 b 10
g 8 null null
再如表a右连接表b，查询hot相同的数据
select a.*,b.* from a right join b on a.hot = b.hot
查询结果和上面的b left join a一样
###练习，查询商品的名称，所属分类，所属品牌
select goods_id,goods_name,goods.cat_id,goods.brand_id,category.cat_name,brand.brand_name from goods left join category on goods.cat_id = category.cat_id left join brand on goods.brand_id = brand.brand_id limit 5;
理解：每一次连接之后的结果都可以看作是一张新表
###练习，现创建如下表
?
create table m(
id int,
zid int,
kid int,
res varchar(10),
mtime date
) charset utf8;
insert into m values
(1,1,2,'2:0','2006-05-21'),
(2,3,2,'2:1','2006-06-21'),
(3,1,3,'2:2','2006-06-11'),
(4,2,1,'2:4','2006-07-01');
create table t
(tid int,tname varchar(10)) charset utf8;
insert into t values
(1,'申花'),
(2,'红牛'),
(3,'火箭');
要求按下面样式打印2006-0601至2006-07-01期间的比赛结果
样式：
火箭 2:0 红牛 2006-06-11
查询语句为：
select zid,t1.tname as t1name,res,kid,t2.tname as t2name,mtime from m left join t as t1 on m.zid = t1.tid
left join t as t2 on m.kid = t2.tid where mtime between '2006-06-01' and '2006-07-01';
总结：可以对同一张表连接多次，以分别取多次数据