SQL 常用语句

1、表操作。

1.1 现有表增加字段

alter table TableName add  

       columnName1  varchar(2) NULL,
       columnName2 varchar(2) NULL,

       columnName3 varchar(2) NULL

注意：不用加Column关键词，多个字段直接用逗号分隔，不用在外部加框框

--字段增加缺省值

CREATE TABLE #t (id INT, NAME VARCHAR(2) DEFAULT ('a') NOT NULL)
ALTER TABLE #t ADD c VARCHAR(1) DEFAULT('2') NOT NULL

增加约束：ALTER TABLE table1 ADD CONSTRAINT DF_table1 DEFAULT(1)  FOR columnname1

2. 查询存储过程中的某个文本

 

select b.name
from dbo.syscomments a, dbo.sysobjects b
where a.id=b.id and b.xtype='p' and a.text like '%autoprocess%'

查看某个表的某个字段被哪些SP更新

select distinct object_name(referencing_id)from sys.sql_expression_dependencies as sedjoin sys.sql_dependencies as sd on sd.object_id = sed.referencing_idjoin sys.columns as c on c.object_id = sd.referenced_major_id and c.column_id = sd.referenced_minor_idwhere sed.referenced_entity_name = 'tableName' and c.name in ('FieldName') and sd.is_updated = 1

 

3. --循环

DECLARE @LoopRows TABLE (RowNo VARCHAR(20)) 

DELETE @LoopRows

INSERT @LoopRows (RowNo)

SELECT RowNo FROM #table 

 

WHILE (SELECT COUNT(*) FROM @LoopRows)>0

BEGIN

SELECT TOP 1 @RowNo = RowNo FROM @LoopRows

IF (1=2)

  BREAK

ELSE 

  CONTINUE

 

-- do something

 

DELETE @LoopRows WHERE RowNo = @RowNo

END

 

--查看死锁
Deadlock has occurred. Please use below sql statement to check deadlock detalis:
EXEC master.dbo.xp_readerrorlog 0, 1, null, null, '2011-08-04T22:41:18.673', '2011-08-04T22:42:18.673', N'ASC'

 

--导入导出工具

SQL Server数据导入导出工具BCP

 

--数据库连接串

ip地址/实例名,1433 注意端口号在最后，用逗号隔开。如果是访问确实实例名，则可以不用输入实例名。要理解，同一个端口可以访问多个实例名。

--清空日志
DUMP TRANSACTION [GSPShop] WITH NO_LOG

--截断事务日志
BACKUP LOG [GSPShop] WITH NO_LOG

--收缩数据库文件
DBCC SHRINKDATABASE([GSPShop])

--故障恢复模型设置为简单
alter database [GSPShop] set recovery simple

4. 查看数据库错误日志，可以查看是否有死锁情况。

EXEC master.dbo.xp_readerrorlog 0, 1, null, null, '2012-06-28T10:31:15.290', '2012-06-28T10:32:15.290', N'ASC'

2012-06-2810:32:10.960               spid34s deadlock-list

2012-06-28 10:32:10.960               spid34s deadlock victim=process10231708

2012-06-2810:32:10.960               spid34s   process-list

2012-06-2810:32:10.960               spid34s    process id=process10231708 taskpriority=0 logused=1072waitresource=KEY: 7:72057598485069824 (05b6583ad1cd) waittime=47ownerId=25415967969 transactionname=user_transactionlasttranstarted=2012-06-28T10:32:10.423 XDES=0x15a7031950 lockMode=Uschedulerid=5 kpid=13848 status=suspended spid=355 sbid=0 ecid=0 priority=0trancount=2 lastbatchstarted=2012-06-28T10:32:10.423lastbatchcompleted=2012-06-28T10:32:10.423 clientapp=.Net SqlClient DataProvider hostname=INTRANET14 hostpid=3264 loginname=intranetisolationlevel=read committed (2) xactid=25415967969 currentdb=7lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056

--逗号分隔字符串转为行列表

SELECT 1 AS ASIN, '1,2,3' AS upc INTO #QualifyProduct
INSERT into #QualifyProduct SELECT 2 AS ASIN, '4' AS upc

SELECT  a.asin ,

        SUBSTRING(a.upc, b.number,

                  CHARINDEX(',', a.upc + ',', b.number) - b.number) upc

FROM    #QualifyProduct a ,

        master..spt_values b

WHERE   b.[type] = 'p'

        AND b.number BETWEEN 1 AND LEN(a.upc)

        AND SUBSTRING(',' + a.upc, b.number, 1) = ',' ​

原理分析：

DECLARE @t TABLE(id INT, codes VARCHAR(100))
INSERT INTO @t SELECT 1,'1,2' UNION SELECT 2,'3,4' UNION SELECT 3,'5'
SELECT * FROM @t


DECLARE @Number TABLE(number int)
INSERT INTO @Number SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
SELECT * FROM @Number


--原理：利用序列表和字符串进行交集，交集的','号对应index位置来where出要分解的字符串。
--利用这个方法，可以把一个字符串'a,b,c,xxxx,ee,1222'的每一个char进行条件匹配，并识别出对应的index，进行数据的处理。
select a.id, substring(a.codes,b.number, charindex(',', a.codes +',', b.number) - b.number) s 
from @t a, @Number b 
where substring(','+a.codes, b.number, 1) = ',' ORDER BY a.id

--将多行转为一个逗号分隔的字符串

SELECT ', ' + a 
FROM (SELECT 'a1' AS a UNION SELECT 'a2' AS a UNION SELECT 'b1' AS a UNION SELECT 'b2' AS a UNION SELECT 'a3' AS a) a
 FOR XML PATH('')

--相当于C#的replace方法

SELECT STUFF(',a,b',1,1,'')

--行转字符串的完整例子

DECLARE @DeliveryDateOrderItem TABLE (Id int NOT NULL PRIMARY KEY)
INSERT @DeliveryDateOrderItem VALUES(1)
INSERT @DeliveryDateOrderItem VALUES(2)
INSERT @DeliveryDateOrderItem VALUES(3)

DECLARE @ids VARCHAR(max)
SELECT @ids = STUFF(
(SELECT ',' + CONVERT(VARCHAR,id) FROM  @DeliveryDateOrderItem  FOR XML PATH(''))
,1,1,'')
SELECT @ids

-- 如何实现 ：字段1 in (表字段1，表字段2）？

SELECT a.* FROM 
(SELECT 'a1' AS a UNION SELECT 'a2' AS a UNION SELECT 'b1' AS a UNION SELECT 'b2' AS a UNION SELECT 'a3' AS a) a
INNER JOIN (SELECT 'a1' AS a, 'b1' AS b UNION SELECT 'a2' AS a, 'b2' AS b) map ON a.a IN (map.a, map.b)

--outer APPLY 和 cross join 的区别

SELECT * FROM (SELECT 'a.b.c' AS nos UNION ALL SELECT 'c.d.' AS nos) a
outer APPLY dbo.WebFunSplitStringToTable(a.nos,'.') b
/*
OK, 可以在关联里面运行表值函数，并且传入的参数可以从关联表的行中获取。
*/


SELECT * FROM (SELECT 'a.b.c' AS nos UNION ALL SELECT 'c.d.' AS nos) a
cross join dbo.WebFunSplitStringToTable(a.nos,'.') b
/*
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "a.nos" could not be bound.
*/

--merge 用法

CREATE table #t (id int, NAME varchar(200))
INSERT  #t values(1,1)
INSERT  #t values(2,2)

CREATE table #t2 (id int, NAME varchar(200))
INSERT  #t2 values(1,'update')
INSERT  #t2 values(3,'insert')

go

MERGE #t AS d
            USING (SELECT Id, name FROM #t2) AS s(id,name)
            ON S.id = D.id
            WHEN MATCHED 
                THEN UPDATE
                    SET     D.NAME = s.Name
            WHEN NOT MATCHED 
                THEN INSERT ( id, name )
                    VALUES  ( id, name ) 
           OUTPUT $action, Inserted.id, Inserted.name, Deleted.id,Deleted.name;
                    
 SELECT * from #t
 
 DROP TABLE #t
 DROP TABLE #t2