SQL server进阶查询

标签：进阶 ScoreInfo when server course score SQL id select

1、别名，查询结果拼接(可以直接修改类别名称如 id->主键 name->姓名 course->课程 score->分数)

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]='张三'

18、行转列/列转行

#行转列------------------------------------------------------------------------------------------------------
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]

19、连接查询

1、左连接

以左表为主表进行数据显示，主外键关系找不到的数据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

5、自连接

select A.DeptId 部门编号,A.DeptName 部门名称,B.DeptName 上级部门 from Dept A

inner join Dept B on A.ParentId = B.DeptId

20、递归查询

希望能够根据一个父菜单查询出当前菜单下的所有子菜单

递归语句

就是去查询层级结构的数据存储，查询数据树形结构数据。

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

标签：进阶,ScoreInfo,when,server,course,score,SQL,id,select
From： https://www.cnblogs.com/LloydTony/p/17637159.html

1、别名，查询结果拼接(可以直接修改类别名称如 id->主键 name->姓名 course->课程 score->分数)

2、条件查询

3、查询范围

4、null判断

5、查询前多少行/按比例查询结果

6、case when判断

7、in查询

8、模糊查询

9、with关键字查询

10、子查询/exists关键字查询

11、复制新表/表数据复制

12、distinct同一列去掉重复

13、排序

14、聚合查询分组

15、分组查询

16、分页查询

17、union/union all操作

18、行转列/列转行

19、连接查询

1、左连接

2、内连接查询

3、右连接查询

4、全连接

5、自连接

20、递归查询

递归语句

相关文章

赞助商

阅读排行

SQL server进阶查询

1、别名，查询结果拼接(可以直接修改类别名称 如 id->主键 name->姓名 course->课程 score->分数)

2、条件查询

3、查询范围

4、null判断

5、查询前多少行/按比例查询结果

6、case when判断

7、in查询

8、模糊查询

9、with关键字查询

10、子查询/exists关键字查询

11、复制新表/表数据复制

12、distinct同一列去掉重复

13、排序

14、聚合查询分组

15、分组查询

16、分页查询

17、union/union all操作

18、行转列/列转行

19、连接查询

1、左连接

2、内连接查询

3、右连接查询

4、全连接

5、自连接

20、递归查询

递归语句

相关文章

赞助商

阅读排行

1、别名，查询结果拼接(可以直接修改类别名称如 id->主键 name->姓名 course->课程 score->分数)