sqlsever常用查询

(数据库使用中能不用事务尽量不用事务,事务会锁表,操作多张表可以用事务)

  1. 查询拼接(+)

select stuff((select ','+title from tb for xml path('')),1,1,'')

  2.层层嵌套查询(要对查到的结果重命名)

select jh from(select jh from
(select JH FROM DBO.CHERY)a)b

3.查询函数被调用

select o.name,o.crdate,o.refdate,s.* from sys.all_sql_modules s
left join sysobjects o on o.id=s.object_id where definition like '%这里填函数名称%'

4.类型转换

CAST (expression AS data_type)

(1)两个表达式的数据类型完全相同。
(2)两个表达式可隐性转换。
(3)必须显式转换数据类型。

5.日期变更

DATEADD() 函数在日期中添加或减去指定的时间间隔。

set @aaa=DATEADD(day,-1,GETDATE())----前一天时间

CONVERT() 函数是把日期转换为新数据类型的通用函数。

CONVERT() 函数可以用不同的格式显示日期/时间数据。

CONVERT(data_type(length),data_to_be_converted,style)

data_type(length) 规定目标数据类型(带有可选的长度)。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。其中style用法:


111yy/mm/dd


114hh:mi:ss:mmm(24h)
120 或者 20yyyy-mm-dd hh:mi:ss(24h)
121 或者 21yyyy-mm-dd hh:mi:ss.mmm(24h)
126yyyy-mm-ddThh:mm:ss.mmm(没有空格)

6.获取两个日期间的时间

DATEDIFF() 函数返回两个日期之间的相差时间。

DATEDIFF(DAY,@开始时间,@结束时间)

7.为null则返回0

ISNULL(UnitsOnOrder,0)

8.实现时间截取

LEFT(StatDate,7)-----截取前7位,RIGHT为后。

9.自定义

DECLARE @SDate  DATE

10.时间比

CONVERT(date, left(s.Atime,10))= '2018-11-27'
DATEDIFF(DAY,Atime,'2018-11-28 0:00:00')=0

11.sql中判断的写法是

if @id>0 and @id<40   或者 if @id>0 or @id<40 
DECLARE @publishers VARCHAR(50);2 SELECT * FROM dbo.Book WHERE Publishers=ISNULL(@publishers,Publishers)
DECLARE @update DATETIME
SELECT * FROM A
WHERE @updata=0 OR CreateTime>@update
----当传入参数=0是,就是全部

12.写函数与调用

(1)创建


USE [FT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<QQ>
-- Create date: <>
-- Description:<计算率>
-- =============================================
ALTER FUNCTION [dbo].[f_FloatRate] 
(
@MpriceDECIMAL(10,4),--价 1
@ZpriceDECIMAL(10,4),--价2
@DrateDECIMAL(10,4)=1.0--率3
)
RETURNS DECIMAL(10,4)
AS
BEGIN
DECLARE @R DECIMAL(10,4)
--执行价折前价
Declare @Dprice DECIMAL(10,4)
Set @Dprice=@Zprice/@Drate
--Set @R = (@Mprice-@Zprice/@Drate)/@Zprice
If(@Mprice>@Dprice)
Set @R = (@Mprice-@Dprice)/@Dprice
Else
Set @R = (@Mprice-@Zprice)/@Zprice
RETURN @R
End

(2)调用 

Select dbo.f_FloatRate(Price,Price,@X)

13,对查询数据处理

case t.[status] when 1 then '已打款' when 0 then '未打款' else '未处理' end as newstatus

14,多条件查询,并改变查询结果

select 
case when v.type=0 and v.TriggleStatus=0 then 'inside'
     when v.type=0 and v.TriggleStatus=1 then 'outside' 
     when v.type=1 and v.TriggleStatus=1  then 'in' 
     when v.type=1 and v.TriggleStatus=1  then 'out'
     else 'nostatus' end as'状态'
from t_vabyg v

15.分页查询

方法一:

-- 设置执行时间开始,用来查看性能的
set statistics time on ;
-- 分页查询(通用型)
select * 
from (select top pageSize * 
from (select top (pageIndex*pageSize) * 
from student 
order by sNo asc ) -- 其中里面这层,必须指定按照升序排序,省略的话,查询出的结果是错误的。
as temp_sum_student 
order by sNo desc ) temp_order
order by sNo asc
-- 分页查询第2页,每页有10条记录
select * 
from (select top 10 * 
from (select top 20 * 
from student 
order by sNo asc ) -- 其中里面这层,必须指定按照升序排序,省略的话,查询出的结果是错误的。
as temp_sum_student 
order by sNo desc ) temp_order
order by sNo asc;

方法二:

set statistics time on;
-- 分页查询(通用型)
select top pageSize * 
from student 
where sNo>=
(select max(sNo) 
from (select top ((pageIndex-1)*pageSize+1) sNo
from student 
order by  sNo asc) temp_max_ids) 
order by sNo;
-- 分页查询第2页,每页有10条记录
select top 10 * 
from student 
where sNo>=
(select max(sNo) 
from (select top 11 sNo
from student 
order by  sNo asc) temp_max_ids) 
order by sNo;


方法三:利用row_number关键字2005以上

set statistics time on;
-- 分页查询(通用型)
select top pageSize * 
from (select row_number() 
over(order by sno asc) as rownumber,* 
from student) temp_row
where rownumber>((pageIndex-1)*pageSize);
set statistics time on;
-- 分页查询第2页,每页有10条记录
select top 10 * 
from (select row_number() 
over(order by sno asc) as rownumber,* 
from student) temp_row
where rownumber>10;


方法四:offset /fetch next(2012版本及以上才有)

set statistics time on;
-- 分页查询(通用型)
select * from student
order by sno 
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;
-- 分页查询第2页,每页有10条记录
select * from student
order by sno  
offset 10 rows
fetch next 10 rows only ;

方法五:between and

ALTER proc [dbo].[AS_List]
@idx int, --查询信息
@pagesize int,
@page int,
@counts int output
AS
begin
Declare @recdst int,@recdend int
Set @recdst=@pagesize*(@page-1)+1
Set @recdend=@recdst+@pagesize-1
if @idx=0
begin
select ROW_NUMBER() over(order by addtime desc) rowid, *
into #temp from User with(nolock)  
select * from #temp where rowid between @recdst and @recdend
select @counts=COUNT(1) from #temp
drop table #temp 
end
end

16.查询多个id拼接起来的数据如(1,2,3,6)

---过程:exec f_getapp_mymenuinfo '1,2,3,6'
---创建过程
Create PROC [dbo].[f_getapp_mymenuinfo] 
@firstid VARCHAR(20)--一级分类id
AS
BEGIN
begin
select name,sortid from [Menu](nolock) where [state]=1 
--and PATINDEX('%,' + RTRIM(id) + ',%',',' + @firstid + ',')>0
and CHARINDEX(convert(varchar,id),@firstid)>0 
end
END


17.随机抽取一条记录

利用 SQL Server 的 NewID() 方法就可以很轻松地达到这个目的。

我们要想随机取出 10 条数据,只要加上 TOP 10 就行了:

select top 10 *, NewID() as random from table order by random

18.一行转多行

create table #t(id int,vc varchar(200))
insert #t select 1 ,'S98001,S95463,S27356'
insert #t select 2 ,'T19883,S56765,W34548'


方法一(超耗内存):

select row_number() over(order by (select 1)) as id
,c.value('.','varchar(50)') as list 
from #t a
cross apply(select  cast('<row>'+replace(vc,',','</row><row>')+'</row>' as xml) as xmlcode) C1
cross apply xmlcode.nodes('*') t(c)
id                   list
-------------------- --------------------------------------------------
                   S98001
                   S95463
                   S27356
                   T19883
                   S56765
                   W34548
(6 行受影响)

方法二:

SELECT fs.a
 FROM #t t
 CROSS APPLY dbo.f_split(t.vc,',') fs
dbo.f_split为表值函数
--实现split功能 的函数
ALTER function [dbo].[f_split](@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as 
begin
 declare @i int
 set @SourceSql=rtrim(ltrim(@SourceSql))
 set @i=charindex(@StrSeprate,@SourceSql)
 while @i>=1
 begin
  insert @temp values(left(@SourceSql,@i-1))
  set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
  set @i=charindex(@StrSeprate,@SourceSql)
 end
 if @SourceSql<>''
  insert @temp values(@SourceSql)
 return 
end

19.创建临时表

----定义表变量存储
declare @RoomSoner table
(
[MyIdx] varchar(50) NULL ,
[Type] int NULL ,
[State] int NULL ,
[Location] int NULL
)

20.存储过程查询过滤id及null

 where (Id=@Id or @Id=0 or @Id='' or @Id=null  ) AND Type=1
 AND (@startTime=''or @startTime=null or A.create_time>=@startTime) and
   (@endTime=''or @endTime=null or A.create_time<=@endTime)

21.查询根据时间查询数据

--查询当天:   
select * from info where DateDiff(dd,datetime,getdate())=0     
--查询24小时内的:   
select * from info where DateDiff(hh,datetime,getDate())<=24
--本月记录:   
SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0     -
--本周记录:   
SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0
--查询上月
SELECT * FROM 表 where DATEDIFF(month,inputdate,GETDATE())=1


22.分割查询并批量插入

insert into T_person(Age) SELECT 1 RoleId from dbo.f_split('1,2,4',',') fs;

23.插入并查询id

insert into Role(role_name,create_time,is_del) 
values(@role_name,GETDATE(),@is_del);
select @id=@@identity

24.事物

----参考:https://blog.csdn.net/zhenglianghui163/article/details/79013798
begin try --开启异常捕获
begin tran --开启事务
commit tran  --提交事务
return 1  
end try
begin catch --异常捕获执行
    rollback tran --回滚事务
    return 0
end catch

25.添加列l修改列类型删除列

USE s_DB
GO
alter table Product add  data_state int  not null default 0

---修改列
USE s_DB
GO
ALTER TABLE Bs_Detail ALTER COLUMN flag_pay int

--修改自增列
USE s_DB
GO
--删除字段ID
alter table Bs_Record drop column [id]
---增加ID自动增长字段
alter table Bs_Record add [id] int identity(1,1)

26.查询并将null转为0

select isnull(a,0) from table

27.存储过程链接查询

declare @sql2 varchar(max);  
    create table #member_own
( 
  owncash varchar(max)
)
set @sql2 = 'SELECT owncash   
 FROM OPENQUERY(Link1143, '+'''exec vvvve.dbo.xc_select_own '+'  '
 +cast(@Idx as varchar(20))+''')'  
  INSERT INTO #member_own
  exec(@sql2)

28.快速备份表

原表存在的话这样用:

insert into a select * from b



原表不存在的话这样用:

select * into a from b

29.多行转一行(T_index_Banner表以title字段区分并进行拼接)

SELECT DISTINCT title, STUFF((SELECT ',' + link FROM T_index_Banner WHERE title = T.title FOR XML PATH('')), 1, 1, '') AS
 link FROM T_index_Banner AS T

--将有逗号的数据转换为一个表存储id加数据 
GO
DECLARE @Text VARCHAR(200)
SET @Text = 'test1,test2'
DECLARE @xml XML
SET @xml = CONVERT(XML,''+ REPLACE(@Text,',','')+'')
CREATE TABLE #TMP1
(
    iID VARCHAR(50),
    sText VARCHAR(200)
)
INSERT INTO #TMP1
SELECT NEWID(),b.value
FROM (SELECT @xml AS 'XML') a
CROSS APPLY (SELECT Tb.a.value('.','VARCHAR(200)') as value FROM  a.xml.nodes('/a') AS Tb(a)) b
SELECT * FROM #TMP1
GO
--将一个Table中某一列的数据拼成逗号分隔的形式
GO
    CREATE TABLE #TMP2
    (
        iID INT,
        sText VARCHAR(200)
    )
    INSERT INTO #TMP2( iID, sText )
    VALUES ( 1,  'test1');
    INSERT INTO #TMP2( iID, sText )
    VALUES ( 1,  'test2');
    DECLARE @TMP2RES VARCHAR(200)
    SET @TMP2RES = (SELECT sText+',' FROM #TMP2 FOR XML PATH(''))
    SET @TMP2RES = SUBSTRING(@TMP2RES,0,LEN(@TMP2RES)-1)
    SELECT @TMP2RES
GO

(2)用leftjoin以日期联表进行数据筛选逗号分割

go
create table TableA (AID varchar(2),strBID varchar(9))
insert into TableA
select 'A1',null union all
select 'A2',',B1,B3,' union all
select 'A3',',B1,B2,' union all
select 'A4',',B1,B4,'
go
create table TableB (BID varchar(2),UpdateTime datetime)
insert into TableB
select 'B1','20120130' union all
select 'B2','20120131' union all
select 'B3','20120201' union all
select 'B4','20120202'
go
create function aa(@num int)
returns varchar(100)
as
begin
    declare @sql varchar(100) set @sql=''
    --设置当前日期,正常getdate()即可
    declare @t datetime  set @t='20120203'
    select @sql=@sql+AID+',' from TableA a left join TableB b on charindex(','+BID+',',strBID)>0
    where b.UpdateTime between dateadd(d,-@num,@t) and @t
    select @sql=left(@sql,len(@sql)-1)
    return @sql
end

select dbo.aa(2)
/*
A2,A4
*/

(3)简单查询拼接不需要分组

SELECT a into #T from dbo.f_split('1,2,4',',') fs;
select * from #T;
declare @sql varchar(100) set @sql=''
select @sql=@sql+a.a+',' from #T a
select @sql=left(@sql,len(@sql)-1)
select * from #T where charindex(','+ltrim(a)+',',','+@sql+',')>0

----定义表变量存储
declare @RoomSoner table
(
[MyIdx] varchar(50) NULL ,
[Type] int NULL default(0),
[State] int NULL  default(0),
[Location] int NULL  default(0)
)

insert into @RoomSoner(MyIdx) select a from #t;
select * from @RoomSoner
drop table #T;

30.查询存储过程调用情况

SELECT database_id,* FROM sys.databases WHERE database_id> 4
SELECT 
a.name AS 存储过程名称,
a.create_date AS 创建日期,
a.modify_date AS 修改日期,
b.last_execution_time AS 最后执行日期,
b.execution_count AS 执行次数
FROM sys.procedures a 
LEFT JOIN sys.dm_exec_procedure_stats b ON a.object_id = b.object_id AND b.database_id ='5'
WHERE a.is_ms_shipped =0 --去掉系统存储过程
ORDER BY b.database_id

31.关联更新

update Article set Article.CreateUserId = TaskUser.id from Article,TaskUser  where Article.UserName = TaskUser.account;

32.创建表

CREATE TABLE [dbo].[AppDownStaticDetail](
[id] [bigint] IDENTITY(1,1)  primary key NOT NULL,
[Ip] [nvarchar](300) NOT NULL,
[Type] [int] Not NULL,
[AddTime] datetime NOT NULL
) ON [PRIMARY]

33.使用CTE进行多次查询,CTE是可以在跟随其后的查询中多次引用的

with tmp(id) as (
    select id from users where name like '航%';
)
select * from tmp;
select * from orders where userId in (select id from tmp);

34.分割id到一行

select @menuIds = @menuIds + cast(MenuId as varchar(255)) +','
from AdminRole_Menu (nolock) where RoleId=@roleId
set @menuIds=left(@menuIds,len(@menuIds) - 1)

35.分割的id拼接

首先编写通用分割函数

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  function [dbo].[tableListSplit](@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as 
begin
    declare @i int
    set @SourceSql=rtrim(ltrim(@SourceSql))
    set @i=charindex(@StrSeprate,@SourceSql)
    while @i>=1
    begin
        insert @temp values(left(@SourceSql,@i-1))
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        set @i=charindex(@StrSeprate,@SourceSql)
    end
    if @SourceSql<>''
       insert @temp values(@SourceSql)
    return 
end

在存储过程调用

		DECLARE  @fillid TABLE(
		id INT 
		)
		INSERT INTO @fillid( id )SELECT a  AS id  FROM dbo.tableListSplit('1,2,3',',')
		update Log SET states =1  WHERE id IN ( SELECT id FROM @fillid)



精品软件 | 创客说| 课程 | 用户中心

版权所有:机遇屋在线 Copyright © 2017-2020 aaoit Co., Ltd.

鲁ICP备16042261号