常用查询方式:
- select * from tablename
- select col1,clo2 from tablename where age = 18
- select col1,clo2 from tablename where age >=18 and age<=60
- select col1,clo2 from tablename where age between 18 and 60
- select top(100) col1,clo2 from tablename where age between 18 and 60
- select top(100) col1,clo2 from tablename where age is null
- select top(100) col1,clo2 from tablename where age is not null
- select top(50)percent col1,clo2 from tablename where age
- select top(50)percent col1,clo2 from tablename where age in (18,19,20)
- select top(50)percent col1,clo2 from tablename where name like '%三%'
- select top(50)percent col1,clo2 from tablename where name like '张%'
- select NEWID() as GUID,GETDATE() as 日期
-
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 - select case when t.CHECKDOCTOR is null then '' else t.CHECKDOCTOR end as Doctor , t.* from TableSeries t where t.CHECKDOCTOR is null
- 。。。。。。
--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
博客内容主要用于日常学习记录,内容比较随意,如有问题,还需谅解!!!