SqlServer常用命令与写法

目录

当前版本Microsoft SQL Server 2014 (SP3)Enterprise Edition

1、备份表

sqlserver不支持create table 表名 as select 的写法

select * into 备份的表名 from 原表名;

2、创建临时表

2.1 本地临时表，只能有创建者使用(断开连接会自动删除)
 create table #tableName（column1 dataType,...)
2.2 全局临时表，所有用户都能使用（使用drop table ##tableName 删除）
 create table ##tableName（column1 dataType,...）
2.3 使用一条WITH子句来指定临时结果表
with 临时表名(列名) as (select 字段 from 表)
select * from 临时表名;--这可以写select、update、insert、delete、create view
eg:
with tmp1(name) as(SELECT name FROM TEST_1)
select tmp1.name from tmp1 

可以使用上述2.1、2.2方法创建出临时表再往里面insert数据，还可以直接通过

select 字段 into #tableName；

来直接创建临时表 --相当于create as的写法。

3、查询

在写一段select语句的时候要在表名后面加上WITH (NOLOCK)

减少锁和不受排它锁影响来减少阻塞，从而提高并发时的性能。

SQL Server 中WITH (NOLOCK)浅析https://www.cnblogs.com/kerrycode/p/3946268.html

增加列

alter table 表名  add 列名 类型;

修改列的数据类型

alter table 表名  alter column 列名 新的类型;

删除列

alter table 表名  drop column 列名,列名,....;

4、日期相关

4.1 计算上月的日期

DATEADD(datepart,number,date)   

参数含义：date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间,此数是正数，对于过去的时间，此数是负数 。datepart值如下：

select DATEADD(month,-1,'2021-05-01'); --'2021-04-01'
 获取当前时间
SELECT GETDATE();

4.2 获取月初月末

select EOMONTH(GETDATE())                         AS '本月最后一天'  
       ,EOMONTH(GETDATE(),1)                       AS '下月最后一天'
       ,EOMONTH(GETDATE(),-1)                      AS '上个月最后一天'
       ,FORMAT(EOMONTH (getdate()),'yyyy-MM-01')   AS '本月第一天'
       ,FORMAT(EOMONTH (getdate(),-1),'yyyy-MM-01')AS '上个月第一天'

 4.3 两个日期的差值

select datediff(day,getdate()-1,getdate()); 结果为1，前面的日期减后面的日期

4.4 日期格式变换 ——CONVERT() 函数

CONVERT(data_type(length),data_to_be_converted,style) 

参数含义：data_type(length) 规定目标数据类型（带有可选的长度）。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。 style值如下

select convert(varchar(20),created,120) from 表 with(nolock); 2019-10-12 18:46:43
select convert(varchar(10),created,120) from 表 with(nolock);-2019-10-12

 5、trim 函数:去除数据中的空格。  rtrim代表去除右边的空格，ltrim代表去除左边的空格

查看数据库	exec sp_databases;
查看表信息	exec sp_tables ’表名’; SELECT * FROM sys.objects WHERE name='表名'
查看列信息	exec sp_columns 表名; select * from syscolumns where id=object_id('表名') and name='列名' sp_help 表名 （相当于desc）
查看索引	exec sp_helpIndex 表名;
查看约束名称	exec sp_helpConstraint 表名;
查看存储过程的名字	exec sp_stored_procedures;
查看存储过程创建、定义语句	exec sp_helptext '存储过程名';

6、CROSS APPLY 与OUTER APPLY的用法

create table test_1(
name varchar(100)
,age varchar(100)
);
insert into test_1 values ('张三',15);
insert into test_1 values ('李四',16);
insert into test_1 values ('王五',17);
insert into test_1 values ('张六',21);

create table test_2(
 name varchar(100)
,id varchar(100)
);
insert into test_2 values ('张三',18);
insert into test_2 values ('张三',1);
insert into test_2 values ('李四',19);
insert into test_2 values ('王五',20);

 

 CROSS APPLY 相当于join

SELECT *
  FROM TEST_1 AS C
 CROSS APPLY (SELECT  TOP 1 *
                FROM TEST_2 AS O
               WHERE C.NAME=O.NAME
               ORDER BY ID ASC
			 ) AS CA
取TEST_1表与TEST_2表使用name字段进行join关联，取TEST_2表id的第一条
--------------相当于row_number的写法-------------
SELECT 	T.NAME1,T.AGE,T.NAME,T.ID
  FROM(SELECT ROW_NUMBER() OVER(PARTITION BY O.NAME ORDER BY O.ID ASC) AS RN
             ,C.NAME AS NAME1,C.AGE,O.NAME,O.ID
         FROM TEST_1 AS C
		 JOIN TEST_2 AS O
		   ON C.NAME=O.NAME
      )T
 WHERE RN=1
 order by T.ID ASC

 OUTER APPLY相当于left join

SELECT *
  FROM TEST_1 AS C
 OUTER APPLY (SELECT  TOP 1 *
                FROM TEST_2 AS O
               WHERE C.NAME=O.NAME
               ORDER BY ID ASC
							) AS CA;

（sqlserver2012开始支持contract的用法）

7、STUFF的用法

7.1 用来删除指定长度的字符，并在指定的起点处插入另一组字符。

STUFF(字符串, 从第几个字符开始删除, 删除的字符数, 拼接的字符串)

从第二个字符开始删除一个字符，插入【拼接】字段
SELECT STUFF('abcdef', 2, 1, '拼接'); a拼接cdef

测试数据还是用刚才的test_!和test_2表

SELECT TEST_1.NAME
,STUFF(
       (SELECT ',' + CAST(TEST_2.ID AS VARCHAR) 
          FROM TEST_2 WITH (NOLOCK)
         WHERE 1 = 1
           AND TEST_1.NAME = TEST_2.NAME --相当于left join
           ORDER BY TEST_2.ID
           FOR XML PATH('')
				),1,1,'')--把第一个【,】去掉
FROM TEST_1
group by TEST_1.NAME --防止重复
;

 可以看到TEST_2中有两条NAME是张三的id，使用逗号拼接成了1,18

参考书籍《SQL Server实用教程 SQL Server 2014版》