常用Sql语句积累(二)

内连接和Exists的使用. 

--根据用户的手机号码,寻找是否有授权的车辆.显示车辆信息
alter proc up_GetCars
 @vchMobile varchar(20)
 as
 select a.* from 
  carbaseinfo a
  inner join users b
  on a.mobileid = b.mobileid 
  where 
          --b.scanmobileid ='13706686388'
   b.scanmobileid = @vchMobile  --OK
   and exists(
    select 1 
    from users 
    where mobileid in
    
    (select scanmobileid
    from users
    where mobileid = a.mobileid) 
         )

   and exists
    (
    select 1 
    from UsersSq
    where mobile = a.mobileid and sqmobile = b.scanmobileid   
    )
   order by a.createtime 
go  

2.SqlServer的datatime类型用bigint来替换,这样该日期在oracle中一样使用.
select mobileid,convert(datetime,firsttime,120) from   Location_Users  where mobileid = '13454000201'
update  Location_Users set firsttime = convert(bigint,getdate(),120) where mobileid = '13454000201'

3.删除日志

DUMP TRANSACTION study WITH NO_LOG

压缩日志及数据库文件大小

/*--特别注意

请按步骤进行,未进行前面的步骤,请不要做后面的步骤
否则可能损坏你的数据库.

一般不建议做第4,6两步
第4步不安全,有可能损坏数据库或丢失数据
第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.
--*/

--下面的所有库名都指你要处理的数据库的库名

1.清空日志
DUMP  TRANSACTION  库名  WITH  NO_LOG   

2.截断事务日志：
BACKUP LOG 库名 WITH NO_LOG

3.收缩数据库文件(如果不压缩,数据库的文件不会减小
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了

也可以用SQL语句来完成
--收缩数据库
DBCC SHRINKDATABASE(库名)

--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles
DBCC SHRINKFILE(1)

4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)
a.分离数据库:
企业管理器--服务器--数据库--右键--分离数据库

b.在我的电脑中删除LOG文件

c.附加数据库:
企业管理器--服务器--数据库--右键--附加数据库

此法将生成新的LOG，大小只有500多K

或用代码： 
下面的示例分离 pubs，然后将 pubs 中的一个文件附加到当前服务器。

a.分离
EXEC sp_detach_db @dbname = '库名'

b.删除日志文件

c.再附加
EXEC sp_attach_single_file_db @dbname = '库名', 
   @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\库名.mdf'

5.为了以后能自动收缩,做如下设置:
企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"

--SQL语句设置方式:
EXEC sp_dboption '库名', 'autoshrink', 'TRUE'

6.如果想以后不让它日志增长得太大
企业管理器--服务器--右键数据库--属性--事务日志
--将文件增长限制为xM(x是你允许的最大数据文件大小)

--SQL语句的设置方式:
alter database 库名 modify file(name=逻辑文件名,maxsize=20)

或
打开数据库属性--选项，将模式改为--简单
然后右键数据库-所有任务-收缩数据库。

游标使用


declare @mobile varchar(32)
declare @service varchar(10)
declare @fee varchar(10)

DECLARE abc CURSOR FOR
select   mobileid,servicetype ,freecode from activeuser where ServiceType = '-xxx' and 
 mobileid not in (select usernumber from f_submit_temp where servicetype = '-xxx')

OPEN abc 
fetch next from abc 
into @mobile,@service,@fee
while (@@FETCH_STATUS = 0)
begin

 exec SP_ADDMobileMonthMuteSubmitMessage 88,@service,'0','099009','99999',@mobile,@mobile,''
 insert into f_submit_temp(usernumber,servicetype) values(@mobile,@service)
 waitfor delay '00:00:00.500'

fetch next from abc
into @mobile,@service,@fee
end
close abc
DEALLOCATE abc

table1构成: id,mobile (id是自动编号)
现在通过存储过程来向表table1里插入记录:
存储过程里的输入参数:mobiles 是多个mobile的集合(之间用逗号分开,如: "5678909876,34567889,12345678")
现在是想把mobiles里的所有mobile(不在table1里的)插入到table1里.

这里使用循环分割字符串了....偶都不会....

谢谢了!
declare @str varchar(8000)
set @str = '5678909876,34567889,12345678'

...

while charindex(',',@str)>0
begin
    insert into mobiles select left(@str,charindex(',',@str)-1)
    where not exists(select 1 from mobiles where 列名=left(@str,charindex(',',@str)-1))
    set @str = stuff(@str,1,charindex(',',@str),'')
end

insert into mobiles select @str
where not exists(select 1 from mobiles where 列名=@str)

...
方法二
--创建函数
create function f_split(@str varchar(8000))
returns @tb table(num int)
as
begin
      while charindex(',',@str)>0
      begin
            insert @tb
            select left(@str,charindex(',',@str)-1)

            set @str=stuff(@str,1,charindex(',',@str),'')
      end
      
      insert @tb select @str
      return
end 
go

--测试
declare @s varchar(100)
select @s='1,2,3,4,5,6,7,8'
select * from f_split(@s)

通过表b来更新a表

--UPDATE LocationUser By LocationTypeUser   ;LocationUser   can't as a 
UPDATE LocationUser   
 SET User_AccessMode = b.servicecount
 from 
  
  (SELECT TypeUser_MobileId, COUNT(TypeUser_MobileId) AS 'servicecount' 
   FROM LocationTypeUser 
   WHERE (TypeUser_Status = 0) 
   GROUP BY TypeUser_MobileId
  ) as b 
 where User_Mobileid = b.TypeUser_MobileId

分组的字段/where 字段/select 字段
---'where department' can have the column where is not exists in 'select departmen;but 'select department' can't hvae column where not in 'group departemnt' except statistic column

SELECT TypeUser_MobileId, COUNT(TypeUser_MobileId) AS 'servicecount' 
 FROM LocationTypeUser 
 WHERE (TypeUser_Status = 0) --TypeUser_Status OK
 GROUP BY TypeUser_MobileId

千万数量级分页存储过程 **
http://blog.csdn.net/evafly920/archive/2006/03/03/614813.aspx
CREATE PROCEDURE CN5135_SP_Pagination
/*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句，不带Where 
7.Group :Group语句,不带Group By
效果演示：http://www.cn5135.com/_App/Enterprise/QueryResult.aspx
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o 
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
获得自动编号

不好办,只有用存储过程来插入新记录,如:
create procedure eosp_CreatebdAccVsCorp
(
@fDistFeeDeptID Int,
@fCoalCorpID Int
)
as
insert into bdAccVsCorps (FDistFeeDeptID, FCoalCorpID) values (@fDistFeeDeptID, @fCoalCorpID)
return @@identity
GO
或者
strSql="INSERT INTO ...; SELECT @@IDENTITY"

年月统计

select datepart(year,createtime) as 'year', datepart(month,createtime) as 'month',count(mobileid) as '数量'
from Media_answer
where createtime >= '2005-4-29' and accessnumber = '05555001'
group by datepart(year,createtime), datepart(month,createtime)
order by datepart(year,createtime), datepart(month,createtime)

select convert(varchar(7),createtime,120) as 'year-month',count(mobileid) as '数量'
from Media_answer
where createtime >= '2005-4-29' and accessnumber = '05555001'
group by convert(varchar(7),createtime,120)
order by convert(varchar(7),createtime,120)

特殊分组统计,小于某个值的为一组
select a.类型,a.年月,count(1) as '数量'
 from
  (
  select convert(varchar(7),createtime,120) as '年月',src_userid,
    case when count(src_userid)<=10 then '小于10' 
           when count(src_userid)>10 and count(src_userid)<=30 then '小于30' 
    when count(src_userid)>30 and count(src_userid)<=100 then '小于100' 
    when count(src_userid)>100 and count(src_userid)<500 then '小于500' 
    when count(src_userid)>500 and count(src_userid)<=1000 then '小于1000' 
    when count(src_userid)>1000 then '大于1000' 
    end as '类型'
   from his_deliver 
   where dst_userid ='05555' and UPPER(substring(message,1,2)) ='CX'
   AND CREATETIME>'2006-05-08'
   group by convert(varchar(7),createtime,120),src_userid
   --order by convert(varchar(7),createtime,120)
   --having count(src_userid)>10 and count(src_userid)<=30
  )  as a
 group by a.年月,a.类型
 order by a.类型

type 的值为0或1,province 和city 相同的按照type来统计price的和.
结果如下：
Provice city price1(type=0)的和,price2(type=1)的和
－－－－－－－－－－－－－－－－

CREATE TABLE [PriceStatistic] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [Province] [int] NULL ,
 [City] [int] NULL ,
 [Type] [int] NULL ,
 [Price] [int] NULL 
) ON [PRIMARY]
GO

select Province,City,
       Price1=sum(case type when 0 then Price else 0 end),
       Price2=sum(case type when 1 then Price else 0 end)
from PriceStatistic
group by Province,City

分组的问题:

DELETE FROM  TEST
DELeTE FROM Test2
DROP TABLE TEST
DROP TABLE TEST2
CREATE TABLE [dbo].[Test](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Type] [int] NULL,
 [Type2] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Test2](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [YearId] [int] NULL,
 [Type] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [Total] [int] NULL
) ON [PRIMARY]

INSERT Test (Type,Type2 ,Name)
 SELECT '1','A,B','Name2'
 union all
 SELECT '2','C,D,E','Name3'
INSERT Test2 (YearId,Type,Total)
 SELECT 1,'A',10
 union all
 SELECT 1,'B',10
 union all
 SELECT 2,'C',10
 union all
 SELECT 2,'D',30
 union all
 SELECT 2,'F',10
 union all
 SELECT 1,'B',10

按照 Test.Type,Test2.YearId分组,求Total的和.
如果 Type2.Type 是Test.Type里的一部分，那么此Test2的记录就属于Test.Type里的一组(以逗号来判断是否在里面)

要求结果：
Test.Type  Test2.Year_id,Test2.Total
1                1                30       
2                2                40 
-----------------------------------------------
select * from test
select  * from test2

SELECT A.TYPE,B.YearId,sum(B.total)
 FROM Test AS A
 inner join Test2 AS B ON charIndex(','+B.Type+',',','+A.Type2+',')>0
 GROUP BY A.Type, B.YearId

--自动编号的表,暂时取消自动编号功能,方便插入指定编号的数据.
--允许将显式值插入表的标识列中。
SET IDENTITY_INSERT sys_parameter_key ON 
insert into Datable1 (parameter_key_id,parameter_key,status) values (100,'HOTEL_PRICE','ENABLE')
insert into DatableChild1 (parameter_text,parameter_value,status,parameter_key_id) values ('HOTEL_PRICE','30','ENABLE',100)
SET IDENTITY_INSERT sys_parameter_key OFF


/*FULL JOIN 实现两行转一行.
每个Name对应两个TYPE的记录,现在需要一条记录获得一个name对应的type及相关数据
*/
--测试数据
CREATE TABLE [dbo].[Test](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [NAME] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [NameDate] [datetime] NULL,
 [TYPESTR] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert test
 select 'name1',getdate(),'type1' union all
 select 'name2',getdate(),'type1' union all
 select 'name3',getdate() ,'type1' union all
 select 'name1',getdate(),'type2' union all
 select 'name2',getdate(),'type2' union all
 select 'name3',getdate() ,'type2' union all
 select 'name4',getdate(),'type1' union all
 select 'name5',getdate() ,'type2'

--SQL
select a.name as name1 ,a.typestr as typestr1,a.namedate as namedate1,b.name as name2 ,b.typestr as typestr2,b.namedate as namedate2
from 
 (select * from test  where typestr = 'type1') as a  
 full join (select * from test  where typestr = 'type2') as b on a.name = b.name
--RESULT 
name1 type1 2006-12-28 11:32:06.437 name1 type2 2006-12-28 11:32:06.437
name2 type1 2006-12-28 11:32:06.437 name2 type2 2006-12-28 11:32:06.437
name3 type1 2006-12-28 11:32:06.437 name3 type2 2006-12-28 11:32:06.437
NULL NULL NULL name5 type2 2006-12-28 11:32:06.437
name4 type1 2006-12-28 11:32:06.437 NULL NULL NULL

--捕捉异常
BEGIN TRY
 select 1/0
    EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage,
  ERROR_LINE() as ERROR_LINE,
  ERROR_SEVERITY() as ERROR_SEVERITY,
  ERROR_STATE() as ERROR_STATE,
  ERROR_PROCEDURE() as ERROR_PROCEDURE;
END CATCH;

--获得插入记录的id(自增列)
declare @tbl table
(
  Id int identity(1,1) primary key not null,
    name varchar(10)
)
insert @tbl 
OUTPUT INSERTED.id
select 'name1'

--获得某个字段长度最长的记录及其字段长度
select city,len(city)
from city1
where not exists(select 1 from city1 as a where len(a.city)>len(city1.city))

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16436858/viewspace-609109/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-609109/