ms sql一些技巧

1.SQL:复制数据库某一个表到另一个数据库中

  SELECT * INTO 新表 FROM 旧表 (将旧表的结构和数据都复制到新表,不用事先创建新表)
  SELECT * INTO 新表 FROM 旧表 where 1=2(只复制结构不复制数据)
  INSERT INTO 新表 SELECT * FROM 旧表(在新表已经存在的情况下,将旧表数据插入新表)
2.将某一条记录排在第一位。
  order by(case processId when 273 then 0  else 1 end), newid()//按照processId的值来排列,当processId为273,排第一个,然后再按照随机数排列。
3.当数据库出现死锁,或者事务冲突,我们需要先把所以的进程杀死。可采用如下代码。
  
--杀死所有进程
USE master;
GO
DECLARE @SQL VARCHAR(3000);
SET @SQL = '';
SELECT @SQL = @SQL+'; KILL ' + RTRIM(SPID)
FROM [sys].[sysprocesses] AS sps
WHERE [sps].[dbid] = DB_ID('otctrade');
SET @SQL = SUBSTRING(@SQL, 2, LEN(@SQL));
EXEC(@SQL);
GO
ALTER DATABASE otctrade SET MULTI_USER;
GO

4.有时候我们需要将数据库设置为单用户,然后对一些难以解决的数据问题执行checktable方法,这时候数据库会帮助我们解决这些问题,解决完以后,需要设置为多用户,可以用下面的 语句。

use master
declare @databasename varchar(255)
set @databasename='otctrade'
exec sp_dboption @databasename, N'single', N'true' --将目标数据库置为单用户状态
dbcc checktable('[otctrade].[dbo].[PE_U_Article]',REPAIR_ALLOW_DATA_LOSS)
dbcc checktable('[otctrade].[dbo].[PE_U_Article]',REPAIR_REBUILD)
exec sp_dboption @databasename, N'single', N'false'--将目标数据库置为多用户状态

5.当我们要查询符合一些某段时间差里面的记录,可以采用DATEDIFF(day,start_time,end_time)=7这样的表达式,其中day是查询条件,可以是day,hour,month,year等,第二个参数为起始时间,第三个参数为终止时间。

比如:select * from T where DATEDIFF(day,create_time,getdate())>1查询距离create_已经过去了1天以上的记录。

     select * from T where DATEDIFF(hour,create_time,getdate())=1 查询距离create_已经过去了1小时的记录

6.如果sql中的某个字符串中包含了',比如一个字符串叫做she' tea。由于在sql语句中'是一个特殊的符号,所以我们可以采用这样的方式来规避这种影响。即在引号前面在加一个引号,相当于转义。

select * from A where name='she''s tea'

7.递归查询

我们经常会需要做一些递归查询,多数是针对具有父子节点结构的数据。比如部门表,一个部门可能隶属其他部门,这样就会有一个父子节点结构。我们可以采用如下的语句来进行递归查询。

with temp_users as
(select * from department_info where superiors_department_id is null union all select A.* from department_info A,temp_users B where A.superiors_department_id=B.department_id)
select * from temp_users

上述语句就是一个典型的递归查询的sql语句,递归的逻辑在于第二行,首先select * from department_info where superiors_department_id is null这条语句是第一次递归的查询语句,它查出美元上级部门的部门(也就是*部门),而后的内联语句就是递归逻辑,每次都跟temp_users做内联,然后得到的记录结果再加到temp_users里面,最后我们得到的temp_users就是一个完整的且按顺序排列好的总结果集。然后第三条语句我们直接从这个结果集中取数据就好了。

ms sql一些技巧

上述例子是从父级往子级递归,我们也可以从子级往父级进行递归。只需要将上述sql语句做稍微的修改即可实现。

 with temp_users as
(select * from department_info where department_id=1 union all select A.* from department_info A,temp_users B where A.department_id=B.superiors_department_id)
select * from temp_users

相信看了代码很容易就明白了,上述语句的结果集为

ms sql一些技巧

8.按自定义的形式排序

有时候我们除了定义一个或者几个排序字段之外,还需要对一些特殊的记录按特定顺序来排列,比如,我想要按年龄降序排列,但是我又必须把名字叫做A的同学排在第一个,名字叫做B的同学排在第二个,然后才是按年龄的降序排列,这时候我们可以使用case when语句来实现。

 select commodity_class_id as topClassId,commodity_class_name  as topClassName,photo_src as photoSrc,app_photo_src as appPhotoSrc from T_Commodity_Class where is_del=0
and class_kind=1
and class_type=1
order by (case top_class_id when 4 then 1 when 285 then 2 when 286 then 3 when 287 then 4 else 5 end),priority1

注意看第4行代码,我们就能了解这个技能的用处了。

上一篇:Hibernate学习小结


下一篇:《sed的流艺术之二》-linux命令五分钟系列之二十二