你的位置:首页 > 数据库

[数据库]小白程序猿实习半年一些常用到的SQL语句记录


1.1数据库操作

1.1.1清理数据库日志文件

 

 

USE [master]  

GO  

ALTER DATABASE [数据库名] SET RECOVERY SIMPLE WITH NO_WAIT  

GO  

ALTER DATABASE [数据库名] SET RECOVERY SIMPLE  

GO  

USE [数据库名]  

GO  

DBCC SHRINKFILE (N'[数据库日志文件名称]' , 0,TRUNCATEONLY)  

GO  

USE [master]  

GO  

ALTER DATABASE [数据库名] SET RECOVERY FULL WITH NO_WAIT  

GO  

ALTER DATABASE [数据库名] SET RECOVERY FULL  

GO 

  

--查询指定数据库的日志文件名称  

USE [数据库名]   

GO  

SELECT name FROM SYS.database_files WHERE type_desc='LOG'  

 

 

 

例:OA数据库清理日志文件

USE [master]  

GO  

ALTER DATABASE OA SET RECOVERY SIMPLE WITH NO_WAIT  

GO  

ALTER DATABASE OA SET RECOVERY SIMPLE  

GO  

USE OA  

GO  

DBCC SHRINKFILE (N'NewPlat_log' , 0,TRUNCATEONLY)  

GO  

USE [master]  

GO  

ALTER DATABASE OA SET RECOVERY FULL WITH NO_WAIT  

GO  

ALTER DATABASE OA SET RECOVERY FULL  

GO  

1.1.2建立连接服务器

Exec sp_droplinkedsrvlogin HFQZ,Null       --删除映射(录与链接服务器上远程登录之间的映射)  

Exec sp_dropserver HFQZ                         --删除远程服务器链接  

EXEC  sp_addlinkedserver  

      @server=' ',--被访问的服务器别名   

      @srvproduct='',  

      @provider='SQLOLEDB',  

      @datasrc=" "   --要访问的服务器  

EXEC sp_addlinkedsrvlogin   

     '  ', --被访问的服务器别名  

     'false',   

      NULL,   

     'sa', --帐号  

     '111111' --密码

 

 

1.1.3 SQL四舍五入

 

1.round() 函数是四舍五入用,第一个参数是我们要被操作的数据,第二个参数是设置我们四舍五入之后小数点后显示几位。

2.numeric 函数的2个参数,第一个表示数据长度,第二个参数表示小数点后位数。

例如:  www.2cto.com  

  select   cast(round(12.5,2)   as   numeric(5,2))  结果:12.50

  select   cast(round(12.555,2)   as   numeric(5,2))  结果:12.56

  select   cast(round(122.5255,2)   as   numeric(5,2)) 结果:122.53

select   cast(round(1222.5255,2)   as   numeric(5,2)) 结果:报错了! 原因是:1222.5255,整数位是4,小数位是2,加起来4+2=6,超出了numeric设置的5位,所以为了保险,可以增减numeric的参数,例如numeric(20,2)。

 

 

1.1.4数据库排序

在SQL Server2005中有如下四个排名函数:

1.row_number() over(order by liename):根据order by后面的字段排序,为每一行给定一个唯一的行号 (一般列表展示用这个)

select row_number() over( order by iname desc ) as rowid ,iname from test

2.Rank():根据order by 后面的字段排序,order by后面的字段值相同序号就相同,序号可能是不连续的(一般成绩排序用这个)

select distinct rank() over( order by iname desc ) as rowid  ,iname,iage from test

3. dense_rank():根据order by 后面的字段排序,order by后面的字段值相同序号就相同,序号是连续的

select distinct dense_rank() over( order by iname desc ) as rowid  ,iname from test

4. ntile():先根据order by 后面对的字段排序,然后将排序结果分成规定的几个组,并为每个组指定一个组号

select distinct  ntile(5)  over( order by iname desc  ) as rowid  ,iname,iage from test order by rowid

1.1.5 触发器的启用与禁止

   禁止: ALTER TABLE 表名 DISABLE TRIGGER all

   启用: ALTER TABLE 表名 enable TRIGGER all

1.2比较复杂的查询语句

1.2.1 数据的拆分

cross apply和outer apply。这两个函数作用是交叉连接。

有一张表aaa,如图所示 

 

 

现需要将name字段中的数据拆分出来。面对这样的case我们两步做。第一步,需要分割字符串;第二步,和id进行关联。通过以上指导方针形成两种sql语句。

cross apply

1.SELECT id,t02.item FROM  dbo.aaa AS t01 CROSS apply dbo.Split(t01.name,',') AS t02--dbo.Split为自定义的字符串分割函数,这个可以自己定义

outer apply

2.SELECT tb01.id,tb02.VALUE FROM( SELECT id,[value] = CONVERT(

OUTER APPLY(SELECT VALUE = N.v.VALUE('.', 'varchar(100)') FROM tb01.[value].nodes('/root/v') N(v) ) AS tb02

 

得到结果为

 

1.2.2数据的合并:

     在2005版本出来之前,数据合并是一件很麻烦的事情而且效率低下。现在具体讲一下05之后的具体做法,即通过操作执行。Case如下:现有一张表bbb,如图所示:

 

SQl方法

SELECT id,stuff((SELECT ','+CLASS         FROM dbo.bbb AS tb01 where tb01.id=tb02.id        FOR 

 FROM  dbo.bbb AS tb02  GROUP BY id

 

STUFF((select ',' +rtrim(target) from PJ_TargetManage where t.t_Id=t_Id order by target for 

结果如下:

 

 

1.2.3 SQL递归查询:

 

with cte as
(
select Id,Pid,DeptName,0 as lvl from Department
where Id = 2
union all
select d.Id,d.Pid,d.DeptName,lvl+1 from cte c inner join Department d
on c.Id = d.Pid
)
select * from cte

 

1.2.4 SQL行转列:

 

select * from(select ExamId,StuNo,SubName,CJScore from ACJ_2014StuScore where ExamId='50ca8b41-cd46-4810-8d76-cf9792eaafa7') b 
pivot(max(CJScore) for SubName in (生物,语文,历史))a 
order by StuNo

select ExamId,StuNo,SubName,CJScore from ACJ_2014StuScore where ExamId='50ca8b41-cd46-4810-8d76-cf9792eaafa7'
order by StuNo 

 

 

 

1.2.5 SQL关于查询时通配符查询不到的问题:

SELECT [f_userName]

  FROM [TLYZOA].[dbo].[T_User_Login]

  WHERE [f_userName] like '%\_%'  escape '\'