SELECT TOP (1000) [id] 主键 //查询前1000行数据
,[name] 姓名
,[course] 课程
,[score] 分数
FROM [Lloyd].[dbo].[ScoreInfo]
2、条件查询
SELECT TOP (1000) [id] 主键
,[name]
,[course]
,[score]
FROM [Lloyd].[dbo].[ScoreInfo] where id>=4
3、查询范围
SELECT TOP (1000) [id] 主键
,[name]
,[course]
,[score]
FROM [Lloyd].[dbo].[ScoreInfo] where id>5 and id<10
SELECT TOP (1000) [id] 主键
,[name]
,[course]
,[score]
FROM [Lloyd].[dbo].[ScoreInfo] where id between 4 and 10
4、null判断
SELECT TOP (1000) [id] 主键
,[name]
,[course]
,[score]
FROM [Lloyd].[dbo].[ScoreInfo] where course is null
5、查询前多少行/按比例查询结果
select top (3) * from ScoreInfo //查询前三行
select top (50) percent * from ScoreInfo //查询前50%行
6、case when判断
#案例1
SELECT *,
case when score<60 then '不及格'
when score>=60 and score<70 then '及格'
when score>=70 and score<80 then '中等'
when score>=80 and score<90 then '良好'
when score>=90 then '优秀'
else '零分'
end '是否及格'
FROM ScoreInfo order by score asc //升序排序
#案例2
SELECT *,
case course
when 'vue' then 'vue.js'
when '上位机' then '上位机学习'
when '数学' then '高等数学'
when '英语' then '大学英语'
else '三好学生'
end '课程'
FROM ScoreInfo
--鼠、牛、虎、兔、龙、蛇、马、羊、猴、鸡、狗、猪
--4 5 6 7 8 9 10 11 0 1 2 3
--查询所有人的信息，添加一列，显示生肖（生日列字段名是PeopleBirth）
select *,
csse
when year(PeopleBirth) % 12 = 4 then '鼠'
when year(PeopleBirth) % 12 = 5 then '牛'
when year(PeopleBirth) % 12 = 6 then '虎'
when year(PeopleBirth) % 12 = 7 then '兔'
when year(PeopleBirth) % 12 = 8 then '龙'
when year(PeopleBirth) % 12 = 9 then '蛇'
when year(PeopleBirth) % 12 = 10 then '马'
when year(PeopleBirth) % 12 = 11 then '羊'
when year(PeopleBirth) % 12 = 0 then '猴'
when year(PeopleBirth) % 12 = 1 then '鸡'
when year(PeopleBirth) % 12 = 2 then '狗'
when year(PeopleBirth) % 12 = 3 then '猪'
else ''
end 生肖
from People
select *,
csse year(PeopleBirth) % 12
when 4 then '鼠'
when 5 then '牛'
when 6 then '虎'
when 7 then '兔'
when 8 then '龙'
when 9 then '蛇'
when 10 then '马'
when 11 then '羊'
when 0 then '猴'
when 1 then '鸡'
when 2 then '狗'
when 3 then '猪'
else ''
end 生肖
from People
7、in查询
SELECT * FROM ScoreInfo where id in (4,5,6,7,8)
8、模糊查询
模糊查询使用like关键字和通配符结合来实现，通配符具体含义如下：
%:代表匹配0个字符、1个字符或多个字符
_:代表匹配有且只有1个字符
[]:代表匹配范围内
[^]:代表匹配不在范围内
模糊匹配--和通配符%一起使用才有效果 %表示可以匹配任何字符
SELECT * FROM ScoreInfo where name like '张%'
--查询出姓张的人的信息，名字是2个字
select * from ScoreInfo where name like '张_'
select * from ScoreInfo where SUBSTRING(name,1,1) = '张'
and len(name) = 2
--SUBSTRING(name,1,1)表示在“name”里面从第一个字符开始取，取一个
--查询出电话号码开头为138的，第四位好像是7或者8，最后一个号码是5
select * from People where PeoplePhone like '138[7,8]%5'
--查询出电话号码开头为138的，第四位好像是2-5之间，最后一个号码不是2和3
select * from People where PeoplePhone like '138[2,3,4,5]%[^2,3]'
select * from People where PeoplePhone like '138[2-5]%[^2-3]'
9、with关键字查询
相当于是sql查询中的sql片段
把tt做为 (select * from [Lloyd].[dbo].[ScoreInfo])表，并从tt中选取name=张三的信息
with tt as (select * from [Lloyd].[dbo].[ScoreInfo])
select * from tt where [name]='张三'
10、子查询/exists关键字查询
#子查询
select * from ScoreInfo where id in (select id from ScoreInfo where name='张三')
--查询出分数比张三高的人的信息
select * from ScoreInfo where score >
(select score from ScoreInfo where name='张三')
#exists关键字
select * from ScoreInfo t1 where
exists (select * from ScoreInfo t2 where t1.id=t2.id and t2.name='张三')
11、复制新表/表数据复制
#复制新表
select * into ScoreInfo2 from ScoreInfo
#把另外一个结构相同的表数据复制到指定表中
insert into [Lloyd].[dbo].[ScoreInfo2]
select [name]
,[course]
,[score] from [Lloyd].[dbo].[ScoreInfo]
12、distinct同一列去掉重复
#普通查询某一列
select course from [Lloyd].[dbo].[ScoreInfo] order by course desc
#查询某一列去掉重复
select distinct course from [Lloyd].[dbo].[ScoreInfo] order by course desc
13、排序
升序asc 降序desc 可以多列排序 从左往右优先级
#案例1
SELECT TOP (1000) [id]
,[name]
,[course]
,[score]
FROM [Lloyd].[dbo].[ScoreInfo] order by [name],course desc
#案例2
with tt as(
select top (1000) [id]
,[name]
,[course]
,[score]
from [Lloyd].[dbo].[ScoreInfo] order by [name],course desc)
select * from tt order by course,[name] desc
--查询所欲员工信息，根据名字长度排序（降序）
select * from People order by len(PeopleName) desc
14、聚合查询分组
聚合---一般在汇总的时候需要用到
SQL server中聚合函数主要有：
count:求数量
max:求最大值
min:求最小值
sum:求和
avg:求平均值
#汇总---每个人的总分
select name ,sum(score) 总分 from ScoreInfo group by name
--求平均值
select round(avg(score),2) 总分 from ScoreInfo
--round(25.55555,2)代表保留两位小数值 返回结果是25.56000
--求数量，年龄最大值，年龄最小值，年龄总和，年龄平均值，在一行显示
--给出生日字段名：PeopleBirth
--方案1：
select *,year(getdate())-year(PeopleBirth) from People
select count(*) 数量,
max(year(getdate())-year(PeopleBirth)) 最高年龄,
min(year(getdate())-year(PeopleBirth)) 最低年龄,
sum(year(getdate())-year(PeopleBirth)) 年龄总和,
avg(year(getdate())-year(PeopleBirth)) 平均年龄
from People
--方案2：
--DATEDIFF(year,'2000-1-1','2002-3-3')表示年份差 结果为2
select count(*) 数量,
max(DATEDIFF(year,PeopleBirth,getdate())) 最高年龄,
min(DATEDIFF(year,PeopleBirth,getdate())) 最低年龄,
sum(DATEDIFF(year,PeopleBirth,getdate())) 年龄总和,
avg(DATEDIFF(year,PeopleBirth,getdate())) 平均年龄
from People
15、分组查询
where条件得写在from之后 group之前
--根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
--1985年及以后出生的员工不参与统计
select PeopleAddress 地区,count(*) 员工人数,sum(PeopleSalary) 工资总和,avg(PeopleSalary) 平均工资,
max(PeopleSalary) 最高工资,min(PeopleSalary) 最低工资
from People
where PeopleBirth < '1985-1-1'
group by PeopleAddress
----根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
--要求筛选出员工人数至少在2人及以上的记录，并且1985年及以后出生的员工不参与统计
select PeopleAddress 地区,count(*) 员工人数,sum(PeopleSalary) 工资总和,avg(PeopleSalary) 平均工资,
max(PeopleSalary) 最高工资,min(PeopleSalary) 最低工资
from People
where PeopleBirth < '1985-1-1'
group by PeopleAddress
having count(*) >= 2
16、分页查询
#必须带有主键id，且主键id是标识列，必须是自增的
原理：需要拿出数据库的第5页，就是40-50条记录，首先拿出数据库中的前40条记录的id值，然后再拿出剩余部分的前10条元素
declare @pagesize int; ---每一页数据数量
select @pagesize=5;
declare @pageindex int; ---查询第几页
select @pageindex=3;
select top (@pagesize) *
from ScoreInfo
where id not in
(
---(10*(2-1))为页大小 * (当前第几页-1)
select top (@pagesize*(@pageindex-1)) id from ScoreInfo order by id
)
order by id
#分页查询二
原理：先查询前40条记录，然后获得其id值，如果id值为null的，那么就返回0
然后查询id值大于前40条记录的最大id值的记录
这个查询有一个条件，就是id必须是int类型的
declare @pagesize int;
select @pagesize=2;
declare @pageindex int;
select @pageindex=1;
select top (@pagesize) *
from ScoreInfo
where id >
(
select isnull(max(id),0)
from
(
select top ((@pagesize)*((@pageindex)-1)) id from ScoreInfo order by id
)A
)
order by id
#分页查询三
原理：先把表中的所有数据都按照一个rownumber进行排序，然后查询rownumber大于40的前十条记录
这种方法和oracle中的一种分页方式类似，不过只支持2005版本以上的
declare @pagesize int;
select @pqgesize=10;
declare @pageindex int;
select @pageindex=1;
select top (@pagesize) *
from
(
select row_number() over(over by id) as rownumber, * from ScoreInfo
)A
---(10*(2-1))为页大小*(当前第几页-1)
where rownumber > ((@pagesize)*((@pageindex)-1))
#分页查询四
要求必须再sqlserver2012版本之后方可支持
declare @pagesize int;
select @pagesize=2;
declare @pageindex int;
select @pageindex=2;
select *
from [Lloyd].[dbo].[ScoreInfo] order by id offset (@pagesize*(@pageindex-1)) --间隔多少条开始
rows fetch next (@pagesize) --获取多少条
rows only
17、union/union all操作
可以把查询到的多个数据结构完全相同的表合并起来
union: 自动去重 union all: 不会去重
SELECT TOP (1000) [id]
,[name]
,[course]
,[score]
FROM [Lloyd].[dbo].[ScoreInfo] where [name]='张三'
union all
SELECT TOP (1000) [id]
,[name]
,[course]
,[score]
FROM [Lloyd].[dbo].[ScoreInfo] where [name]='张三'
#行转列------------------------------------------------------------------------------------------------------
select [name],
isnull(sum(case when course='vue' then score else 0 end),0) as 'vue',
isnull(sum(case when course='java' then score else 0 end),0) as 'java',
isnull(sum(case when course='html' then score else 0 end),0) as 'html',
isnull(sum(case when course='js' then score else 0 end),0) as 'js',
isnull(sum(case when course='jq' then score else 0 end),0) as 'jq',
isnull(sum(case when course='上位机' then score else 0 end),0) as '上位机',
isnull(sum(case when course='数学' then score else 0 end),0) as '数学',
isnull(sum(case when course='英语' then score else 0 end),0) as '英语',
isnull(sum(case when course='政治' then score else 0 end),0) as '政治'
from [Lloyd].[dbo].[ScoreInfo] group by [name]
#行转列------------------------------------------------------------------------------------------------------
select [name],
isnull(sum(case when course='vue' then score else 0 end),0) as 'vue',
isnull(sum(case when course='java' then score else 0 end),0) as 'java',
isnull(sum(case when course='html' then score else 0 end),0) as 'html',
isnull(sum(case when course='js' then score else 0 end),0) as 'js',
isnull(sum(case when course='jq' then score else 0 end),0) as 'jq',
isnull(sum(case when course='上位机' then score else 0 end),0) as '上位机',
isnull(sum(case when course='数学' then score else 0 end),0) as '数学',
isnull(sum(case when course='英语' then score else 0 end),0) as '英语',
isnull(sum(case when course='政治' then score else 0 end),0) as '政治'
from [Lloyd].[dbo].[ScoreInfo] group by [name]
以左表为主表进行数据显示，主外键关系找不到的数据null取代
select * from Company c left join SysUser s on c.ID=s.CompanyId
2、内连接查询
内连接查询和简单多表查询共同的特点：不符合主外键关系的数据不会被显示出来
select * from Company c inner join SyeUser s on c.ID=s.CompanyId
3、右连接查询
select * from Company c right join SysUser s on c.ID=s.CompanyId
4、全连接
两张表的数据，无论是否符合关系，都要显示
select * from Company c full join SysUser s on c.ID=s.CompanyId
select A.DeptId 部门编号,A.DeptName 部门名称,B.DeptName 上级部门 from Dept A
inner join Dept B on A.ParentId = B.DeptId
递归语句
就是去查询层级结构的数据存储，查询数据树形结构数据。
with Con(Id, MenuName, ParentId, le) as
(
select Id, MenuName, ParentId, le=1 from [Lloyd].[dbo].[MenuInfo] where Id=11
union all
select a.Id,a.MenuName,a.ParentId,le=le+1 from [Lloyd].[dbo].[MenuInfo] a join Con on a.ParentId=Con.Id
)
select Id,MenuName,ParentId,le from Con
