014 数据库学习笔记--查询 + case + 分页 + 函数 + Union + 行转列 + 列转行 + with片段查询

常用查询方式：

1. select * from tablename
2. select col1,clo2 from tablename where age = 18
3. select col1,clo2 from tablename where  age >=18 and age<=60
4. select col1,clo2 from tablename where  age between 18 and 60
5. select top(100) col1,clo2 from tablename where  age between 18 and 60
6. select top(100) col1,clo2 from tablename where  age is null
7. select top(100) col1,clo2 from tablename where  age is not null
8. select top(50)percent  col1,clo2 from tablename where  age
9. select top(50)percent  col1,clo2 from tablename where  age in (18,19,20)
10. select top(50)percent  col1,clo2 from tablename where  name like '%三%'
11. select top(50)percent  col1,clo2 from tablename where  name like '张%'
12. select NEWID()  as  GUID,GETDATE() as 日期

13. select t.CHECKDOCTOR, t.* from TableSeries t where t.CHECKDOCTOR is null
    select case when t.CHECKDOCTOR is null then 'a' else 'b' end, t.* from TableSeries t where t.CHECKDOCTOR is null

14. select  case when t.CHECKDOCTOR is null then '' else t.CHECKDOCTOR end  as Doctor ,  t.* from TableSeries t where  t.CHECKDOCTOR is null
16. 。。。。。。

--case when
select top(100) student_id, student_name, 
case 
	when student_age <18 then '未成年：' + CONVERT(varchar(50),student_age)+'岁'
	when student_age>=18 and student_age<100 then '成年人：'+ CONVERT(varchar(50),student_age)+'岁'
	else '100以上高寿老人'+ CONVERT(varchar(50),student_age)+'岁'
end as '是否成年人', 
student_sex
from [dbo].[student] order by student_age desc

     

--方式二
select top(100) student_id, student_name, student_age,
case student_sex when '男' then '帅小伙'
				 when '女' then '小美女'
				 else '未知'
end as '性别'
from [dbo].[student] order by student_sex desc

select DATEPART(weekday,GETDATE()) as '周第几天',
case DATEPART(weekday,GETDATE())
when 1 then '星期日'
when 2 then '星期一'
when 3 then '星期二'
when 4 then '星期三'
when 5 then '星期四'
when 6 then '星期五'
when 7 then '星期六'
end as '星期几',
case DATENAME(weekday,GETDATE())
when '星期日'  then 1
when '星期一'  then 2
when '星期二'  then 3
when '星期三'  then 4
when '星期四'  then 5
when '星期五'  then 6
when '星期六'  then 7
end as '星期几'

--in
select * from student where student_name in('kunkun','yangyang')

--like
select * from student where student_name like '%kun%'
select * from student where student_name like '张%'

--with
with test as (select student_id, student_name, student_age, student_sex from student)
select * from test

--子查询
select * from student where student_name in (select student_name from studentbak)

--exists
select * from student where exists (select student_age from studentbak)

--备份表数据，即复制新表
drop table studentbak
select * into studentbak from student
select * from studentbak

--将一个结构相同的表数据复制到指定表中
delete from studentbak
insert studentbak select  student_name, student_age, student_sex from student
select * from studentbak

--distinct同一列去掉重复
select distinct student_name from student 
select distinct student_name,student_age from student

--order by asc|desc
select distinct student_name,student_age from student order by student_name,student_age desc

--group by
select student_sex from student group by student_sex
--select student_name,student_course,sum(student_score) student_score  from scoreinfo group by student_name,student_course

分页查询：

示例一：student_id 标识列，自增长

--分页查询
declare @pagesize int,@pageindex int
select @pagesize =3  -- 每页显示条数
set @pageindex =1 --从第一页开始

select top(@pagesize)* from studentbak 
where student_id not in
(
	select top (@pagesize*(@pageindex-1)) student_id from studentbak order by student_id
)
order by student_id
go

示例二：student_id 标识列，自增长

--分页 + 自定义函数 多语句表值函数
create function GetStudentPage
(
	@pagesize int,
	@pageindex int
)
returns @tempTable table    -- student_id, student_name, student_age, student_sex
(
	student_id int not null primary key,
	student_name varchar(100),
	student_age int,
	student_sex varchar(100)
)
as
begin
-------------------T-SQL 函数体-------------------
--insert into @tempTable(id,name,age,sex) select student_id, student_name, student_age, student_sex from student where student_age = @student_age
insert into @tempTable(student_id, student_name, student_age, student_sex) 
	select top(@pagesize)* from studentbak 
	where student_id not in(select top (@pagesize*(@pageindex-1)) student_id from studentbak order by student_id)order by student_id
--返回结果集
return 
end
go
--调用自定义函数 
select * from dbo.GetStudentPage(3,1)
select * from dbo.GetStudentPage(3,2)

     

 示例三：row_number

--分页 + 自定义函数 多语句表值函数 + row_number
create function GetStudentPageByRownumber
(
	@pagesize int,
	@pageindex int
)
returns @tempTable table    -- student_id, student_name, student_age, student_sex
(
	rownumber int,
	student_id int not null primary key,
	student_name varchar(100),
	student_age int,
	student_sex varchar(100)
)
as
begin
-------------------T-SQL 函数体-------------------
--insert into @tempTable(id,name,age,sex) select student_id, student_name, student_age, student_sex from student where student_age = @student_age
insert into @tempTable(rownumber,student_id, student_name, student_age, student_sex) 
	select top(@pagesize)* from 
	(
		select ROW_NUMBER() over (order by student_age) as rownumber, * from studentbak
	) as Stu
	--where rownumber not in(select top (@pagesize*(@pageindex-1)) student_id from studentbak order by student_id)order by rownumber
	--(10*(2-1))为页数大小 * (当前第几页 - 1)
	where rownumber >(@pagesize * ((@pageindex)-1))
--返回结果集
return 
end
go

--调用自定义函数
select * from dbo.GetStudentPageByRownumber(3,1)
select * from dbo.GetStudentPageByRownumber(3,2)

示例四：

--分页查询 offset
--必须在sqlserver  2012版本之后支持
declare @pagesize int,@pageindex int
select @pagesize =3  -- 每页显示条数
set @pageindex =1 --从第一页开始

select  * from studentbak order by student_id
offset  (@pagesize*(@pageindex-1))--间隔多条开始
rows fetch next(@pagesize) -- 获取多少条
rows only
go

函数方式：

--分页 + 自定义函数 多语句表值函数 + offset + rownumber
create function GetStudentPageByOffset
(
	@pagesize int,
	@pageindex int
)
returns @tempTable table    -- student_id, student_name, student_age, student_sex
(
	rownumber int,
	student_id int not null primary key,
	student_name varchar(100),
	student_age int,
	student_sex varchar(100)
)
as
begin
-------------------T-SQL 函数体-------------------
--insert into @tempTable(id,name,age,sex) select student_id, student_name, student_age, student_sex from student where student_age = @student_age
insert into @tempTable(rownumber,student_id, student_name, student_age, student_sex) 
	--select  * from studentbak order by student_id
	--select ROW_NUMBER() over (order by student_age) as rownumber, * from studentbak order by rownumber
	select  * from 
	(
		select ROW_NUMBER() over (order by student_age) as rownumber, * from studentbak
	) as Stu order by rownumber
		offset  (@pagesize*(@pageindex-1))--间隔多条开始
		rows fetch next(@pagesize) -- 获取多少条
	rows only
--返回结果集
return 
end
go
--调用自定义函数 
select * from dbo.GetStudentPageByOffset(3,1)
select * from dbo.GetStudentPageByOffset(3,2)
go


--分页 + 自定义函数 多语句表值函数 + offset
alter function GetStudentPageByOffset
(
	@pagesize int,
	@pageindex int
)
returns @tempTable table    -- student_id, student_name, student_age, student_sex
(
	student_id int not null primary key,
	student_name varchar(100),
	student_age int,
	student_sex varchar(100)
)
as
begin
-------------------T-SQL 函数体-------------------
--insert into @tempTable(id,name,age,sex) select student_id, student_name, student_age, student_sex from student where student_age = @student_age
insert into @tempTable(student_id, student_name, student_age, student_sex) 
	select  * from studentbak order by student_id
		offset  (@pagesize*(@pageindex-1))--间隔多条开始
		rows fetch next(@pagesize) -- 获取多少条
	rows only
--返回结果集
return 
end
go
--调用自定义函数 
select * from dbo.GetStudentPageByOffset(3,1)
select * from dbo.GetStudentPageByOffset(3,2)

union/union all

--union\union all  合并查询数据
--union 自动去重
select student_name, student_age, student_sex from student
union
select student_name, student_age, student_sex from studentbak
go
--union all 不去重
select student_name, student_age, student_sex from student
union all
select student_name, student_age, student_sex from studentbak
go

行转列：

--行转列
select * from scoreinfo
go
select ROW_NUMBER() over (order by student_name) as rownumber,student_name,
		 --sum(case when student_course ='语文' then student_score else 0 end) as '语文'
		 sum(case when student_course ='语文' then student_score  end) as '语文',  	  --else 0  可省略
		 sum(case when student_course ='数学' then student_score  end) as '数学',	  --else 0  可省略
		 sum(case when student_course ='英语' then student_score  end) as '英语'   	  --else 0  可省略
from scoreinfo group by student_name
--添加 isnull 判断
select ROW_NUMBER() over (order by student_name) as rownumber,student_name,
		--sum(case when student_course ='语文' then student_score else 0 end) as '语文'
		isnull(sum(case when student_course ='语文' then student_score  end),0) as '语文',  	  --else 0  可省略
		isnull(sum(case when student_course ='数学' then student_score  end),0) as '数学',	  --else 0  可省略
		isnull(sum(case when student_course ='英语' then student_score  end),0) as '英语'   	  --else 0  可省略
from scoreinfo group by student_name

列转行：

----------------------------列转行----------------------------
--drop table stuscore
if(OBJECT_ID('stuscore','U')is null) --is not null
create table stuscore(name varchar(38),Chinese decimal(4,1),mathematics decimal(4,1),English decimal(4,1))
insert into stuscore VALUES('张三',82.2,93,90)
insert into stuscore VALUES('李四',87,81.1,99)
insert into stuscore VALUES('王五',93,86.6,90.9)
select * from stuscore
go

--方式一 unpivot 
select name,cource,score
from
	(select name,Chinese as '语文',mathematics as '数学',English as '英语' from stuscore) s
unpivot
	(score for cource in
		(语文,数学,英语)
) as stuscore;
go

--方式二 union 子查询方式
select * from
(
  select name,cource='Chinese',score=Chinese from stuscore
  union all
  select name,cource='mathematics',score=mathematics from stuscore
  union all
  select name,cource='English',score=English  from stuscore
) A order by A.name,
case  A.cource when 'Chinese' then 1
					   when 'mathematics' then 2
					   when 'English' then 3
					
end
go


--with test as (select student_id, student_name, student_age, student_sex from student)
--select * from test
--方式三 
with StuscoreInfo as
(
	  select name,cource='数学',score=mathematics from stuscore
	  union all  
	  select name,cource='语文',score=Chinese from stuscore
	  union all  
	  select name,cource='英文',score=English from stuscore
)
select * from StuscoreInfo order by name

  

 with 片段查询：with关键字查询,相当于是sql查询中的sql片段

with test as (select student_id, student_name, student_age, student_sex from student)
select * from test