数据的基本查询

理解查询

服务器执行命令，在原始数据表中查找符合条件的数据，产生一个虚拟表。
虚拟表是数据组合后的重新展示，而不是原始的物理数据。

查询基本语法构成

select <列名>
from <表名>
[where <查询条件表达式>]
[order by <排序的列名> ASC或DESC]

查询全部行和列

select * from Students

查询部分行

select StudentName,Gender,出生日期
from Students where Gender='男' and Age>20

使用“AS”或使用“=”重新命名字段

select StudentName as 姓名,Gender as 性别,出生日期=birthday
from Students where Gender='男'

加号的使用

select 姓名=StudentName,地址和电话=StudentAddress+'【'+PhoneNumber+'】' 
from Students where Gender='男'
select 总成绩=CSharp+SQLServerDB from ScoreList

注意：
1.+连接的数据类型必须兼容
2.如果使用+连接字符型数据，结果为字符串数据的连接
3.如果使用+连接数值型数据，结果为数值的和

查询空列

select * from ScoreList where SQLServerDB is null

使用常量列

select StudentName as 姓名,Gender as 性别,出生日期=birthday,所在学校='北京大学'
from Students where Gender='男'

限制固定行数

select top 5 StudentName,Gender,Birthday from Students

返回百分之多少行

select top 20 percent StudentName,Gender,Birthday from Students

按多列排序

select top 3 StudentId,CSharp as C#,DB=SQLServerDB
from ScoreList  
where StudentId not in(select top 6 StudentId from ScoreList order by SQLServerDB DESC,CSharp DESC )
order by SQLServerDB DESC,CSharp DESC

模糊查询-like

select StudentName,StudentAddress from Students where StudentAddress like '天津%'
select StudentName,StudentAddress from Students where StudentName like '%小%'

模糊查询-between

select * from ScoreList where CSharp between 80 and 90
select StudentName,StudentAddress,Birthday from Students where Birthday between '1987-01-01' and '1988-01-01'

模糊查询-in

select StudentName,StudentAddress,age from Students where Age in(21,22,23)
select StudentName,StudentAddress,age from Students where StudentName in('王小虎','贺小张')

查询函数的使用

聚合函数（求和、统计、求最大值、最小值、平均值）

select SUM(CSharp) as C#总成绩 from ScoreList
select 总人数=COUNT(*) from Students
select MAX(Csharp) as C#最高分 ,MIN(CSharp) as C#最低分,AVG(CSharp) as C#平均分 from ScoreList

多表之间的数据查询

内连接（inner join...on...）查询

select Students.StudentId,C#成绩=CSharp,StudentName,ClassName
from ScoreList
inner join Students on Students.StudentId=ScoreList.StudentId
inner join StudentClass on Students.ClassId=StudentClass.ClassId
where CSharp >80

左外连接（left outer join...on...）

select Students.StudentId,StudentName,Gender ,C#成绩=CSharp from Students
left outer join ScoreList on Students.StudentId=ScoreList.StudentId
where Gender='男'

右外连接（right outer join...on...）

分组查询、统计及统计筛选（group by...having...）

select 班级=StudentClass.ClassName,人数=COUNT(*),C#最高分=Max(CSharp),DB最高分=MAX(SQLServerDB),
AVG(CSharp) as C#平均分,AVG(SQLServerDB) as DB平均分
from Students
inner Join StudentClass on Students.ClassId =StudentClass.ClassId
inner join ScoreList on ScoreList.StudentId=Students.StudentId
group by ClassName
having AVG(CSharp)>=70 and AVG(SQLServerDB)>=70

查询重复

--查询所有重复的记录
select * from ScoreList
where StudentId in(select StudentId from ScoreList group by StudentId  having COUNT(*)>1)
order by StudentId
--其它方法
select * from ScoreList
where (select COUNT(*) from ScoreList s where s.StudentId=ScoreList.StudentId)>1
order by StudentId

分组查询对比

where字句：从数据源中去掉不符合其搜索条件的数据。
group by字句：搜集数据行到各个组中，统计函数为各个组计算统计值。
having字句：在分组结果中，去掉不符合其组搜索条件的各组数据行。

最后编辑于：2019.07.21 22:54:15