MSSQL基础和高级知识

LIKE

_：可以替代一个字符

LIKE 运算符 描述
WHERE 地址 LIKE 'a%' 查找以“a”开头的任何值
WHERE 地址 LIKE '%a' 查找以“a”结尾的任何值
WHERE 地址 LIKE '%or%' 在任何位置查找任何具有“or”的值
WHERE 地址 LIKE '_r%' 在第二个位置查找任何具有“r”的值
WHERE 地址 LIKE 'a_%_%' 查找以“a”开头且长度至少为 3 个字符的值
WHERE 地址 LIKE 'a%o' 找到以"a"开头，以"o"结尾的值

BETWEEN

SQL 中 between and 是包括边界值的， not between 不包括边界值，不过如果使用 between and 限定日期需要注意，如果 and 后的日期是到天的，那么默认为 00:00:00 例如： and 后的日期为 2018 年 09 月 28 日，就等价于 2018-09-28 00:00:00 ，那么 2018-09-28 11:24:54.000 的数据就查不到了，如果要查到 2018-09-28 这一整天的数据，那么在取值的时候需要加 1 天，即 BETWEEN '2018-06-28' AND '2018-09-29'，这样返回的就是 6 月 28 日(含)到 9 月 28 日(含)的所有数据了。

SELECT INTO 和 INSERT INTO

备份表

我们可以把所有的列都复制到新表中：
SELECT *
INTO newtable [IN externaldb]
FROM table1;

或者只复制希望的列插入到新表中：
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

创建一个新的空表
这里使得返回的数据为空

SELECT * INTO newtable FROM table1 WHERE 1=0;

临时表

 IF OBJECT_ID('Tempdb..#T_Student') IS NOT NULL
 DROP TABLE #T_Student;
 
 SELECT * INTO #newtable FROM oldtable WHERE 1=0;--创建一个相同表架构的临时表
 
 --带自增长id列
  create table #temp_student
        (id int identity,
         sname varchar(20),
         scourceid int
        )

获取数据新的自增长id

IF OBJECT_ID('tempdb..#A') IS NOT NULL
DROP TABLE #A
CREATE TABLE #A(Id INT IDENTITY(1,1),Name Varchar(20))
DECLARE @NewTempId INT
INSERT INTO #A(Name) VALUES('QING')
SELECT @NewTempId = @@IDENTITY
SELECT @NewTempId

WITH AS

WITH CTE AS
(
	SELECT * FROM dbo.BookInfo
)

--SELECT * FROM dbo.BookType;--不能在CTE之前查询
SELECT * FROM CTE
--SELECT * FROM CTE			--不能再使用

约束

唯一键约束
alter table tablename
  add constraint uniquename unique (columnname);

check约束
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

用友U8表约束

--增加多列唯一键约束
 IF OBJECT_ID('PK_CURRENTSTOCK') IS NOT NULL
 BEGIN
	ALTER TABLE dbo.CurrentStock
	DROP CONSTRAINT PK_CURRENTSTOCK
 END
 ELSE
 BEGIN
     ALTER TABLE dbo.CurrentStock
	 ADD CONSTRAINT PK_CURRENTSTOCK UNIQUE (iSodid,iSoType,cVMIVenCode,ItemId,cWhCode,cInvCode,cBatch)
 END

--增加约束，iQuantity>=0
 IF OBJECT_ID('CurrentStock_check_iquantity') IS NOT NULL
 BEGIN
	ALTER TABLE dbo.CurrentStock
	DROP CONSTRAINT CurrentStock_check_iquantity
 END
 ELSE
 BEGIN
     ALTER TABLE dbo.CurrentStock
	 ADD CONSTRAINT CurrentStock_check_iquantity CHECK (iQuantity>=0);
 END
  

修改表和数据库

二、修改字段名：
将Worker表的字段 CreateTime2改为CreateTime
EXEC sp_rename 'Worker.CreateTime2','CreateTime'
 
三、修改字段类型：
alter table 表名 alter column UnitPrice decimal(18, 4) not null
 
三、修改增加字段：
alter table 表名 ADD 字段 类型 NOT NULL Default 0

1.修改数据库
alter table TableName add [列名] [数据类型](大小) not null default ''
alter table [dbo].[Table_Student] alter column StudentName varchar(20) not null
    
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
CLUSTERED：聚集 NONCLUSTERED:非聚集

--------表增加主键约束，主键列使用聚集索引
IF NOT EXISTS( SELECT 1 FROM sysobjects WHERE name ='PK_DispatchList')
 BEGIN
     ALTER TABLE dbo.DispatchList  ADD CONSTRAINT [PK_DispatchList] 
     PRIMARY KEY CLUSTERED ([DLID] ASC)
 END
 
------表增加一个字段 
 IF NOT EXISTS( SELECT 1 FROM syscolumns WHERE id = OBJECT_ID('表名') AND name='字段名称')
BEGIN
    ALTER TABLE 表名 ADD update_time DATETIME NULL;
END

延时执行

下面的语句将延时2s后才执行

--WAITFOR 参数:1delay:时间,2time特定时间
waitfor delay'00:00:02'
print 'hi'

还原和删除表

TRUNCATE TABLE #Table 
DROP TABLE #Table 

语句执行时间

declare @start DATETIME;
declare @end DATETIME;
SET @start = getdate();
select * from TB_Form;
SET @end = getdate();
select datediff(ms,@start,@end);

删除附表已经没有与主表关联的数据

CREATE TABLE #A(id INT,value VARCHAR(10))
CREATE TABLE #B(id INT,value VARCHAR(10))

INSERT INTO #A (id,value)
VALUES  (1, '1'),(2, '2')
          
INSERT INTO #B (id,value)
VALUES  (2, '2'),(3, '3')    

--删除B表 id=3的数据
DELETE  FROM #B WHERE   NOT EXISTS ( SELECT * FROM   #A WHERE  #A.id = #B.id );
    
SELECT * FROM #A;
SELECT * FROM #B;

DROP TABLE #A;
DROP TABLE #B;