查询：select * from table1 where 范围
select * from table where table001=\'JSBQF050\' and table002=\'1307050002\' and table003=\'0020\' and table004=\'0030\'
select * from table where table001 like \'A0%\'
删除：delete from table1 where 范围
delete from table where table001=\'JSBQF050\' and table002=\'1307050002\' and table003=\'0020\' and table004=\'0030\'
更新：update table1 set field1=value1 where 范围
update table set table020=\'3\' , table021=\'2\' where table001=\'JSBQF050\' and table002=\'1307050002\'
排序：select * from table_1 order by field1,field2 descdesc是降序排列，asc是升序排列
between为查询某字段的指定范围，限制查询数据范围时包括了边界值，not between不包括边界值
select * from table1 where time between time1 and time2
删除表中的重复记录
收缩数据库
--重建索引
Dbcc reindex
Dbcc indexdefrag
--收缩数据库和日志
Dbcc shrinkdb
Dbcc shrinkfile
压缩数据库
Dbcc shrinkdatabase(dbname)
组合：UNIOM—两个表table_1和table_2并消除表中所有重复行而派生出一个新的表table_3（有重复的话只显示一次），table_3的每一行不是来自table_1就是来自table_2
UNION ALL—两个表able_1和table_2派生出一个新的表table_3，新表中包含两个表中所有信息，包括重复项
Select * from table_1 UNION Select * from table_2
Select userID,username from table_1 UNION select useID,usename from table_2
批量查询删除
用declare语句设定变量。
declare @tableID_1 nvarchar(30)
declare @tableID_2 nvarchar(40)
set @tableID_1=\'list1\'
set @tableID_2=\'list2\'
delete from resda where resda001 =@tableID_1 and resda002=@tableID_2
delete from resdb where resdb001 =@tableID_1 and resdb002=@tableID_2
delete from resdc where resdc001 =@tableID_1 and resdc002=@tableID_2
delete from resdd where resdd001 =@tableID_1 and resdd002=@tableID_2
delete from resde where resde001 =@tableID_1 and resde002=@tableID_2
delete from resdf where resdf002 =@tableID_1 and resdf003=@tableID_2
delete from resdh where resdh002 =@tableID_1 and resdh003=@tableID_2
delete from resdk where resdk001 =@tableID_1 and resdk002=@tableID_2
delete from resdl where resdl001 =@tableID_1 and resdl002=@tableID_2
设置模糊查询，例如将下边语句中的GLBQF034改为查询GLBQF03%
declare @tableID_1 nvarchar(30)
set @tableID_1=\'list1\'
select * from table where table001 =@tableID_1
修改后的语句为
declare @tableID_1 nvarchar(30)
set @tableID_1=\' list\'
select * from table where table001 like (@tableID_1+’%’)
查询/删除语句。（in后边查询的结果不止一个只能用in）
declare @ProceID NVARCHAR(200)
set @ProceID=\'0001483a-f0b4-4fc1-98b2-9efd358f8253\'
Select * FROMdbo.PDM_WFM_ProceTaskPerformWHERETaskID in (select ID from PDM_WFM_ProceTask where ProceID=@ProceID)
cursor(游标)
1 table1结构如下
2 idint
3 namevarchar(50)
5 declare @id int
6 declare @name varchar(50)
7 declare cursor1 cursor for--定义游标cursor1
8 select * from table1--使用游标的对象(跟据需要填入select文) 9 open cursor1--打开游标
10
11 fetch next from cursor1 into @id,@name--将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
12
13 while @@fetch_status=0--判断是否成功获取数据
14 begin
15 update table1 set name=name+\'1\'
16 where id=@id--进行相应处理(跟据需要填入SQL文)
17
18 fetch next from cursor1 into @id,@name--将游标向下移1行
19 end
20
21 close cursor1--关闭游标
22 deallocate cursor1
实例：
DECLARE @ProceID NVARCHAR(200)
DECLARE @ProceName NVARCHAR(200)
DECLARE curDelProce CURSOR FOR
SELECT ID,Name from dbo.PDM_WFM_Proce WHERE Name LIKE \' table%\'
OPEN curDelProce
FETCH NEXT FROM curDelProceINTO @ProceID,@ProceName
WHILE (@@fetch_status=0)
BEGIN
SET @ProceName=\'示例语句:\'+@ProceName PRINT @ProceName
select * FROM dbo.PDM_WFM_ProceTaskPerformWHERETaskID IN(SELECT ID FROM PDM_WFM_ProceTask WHERE ProceID = @ProceID)
select * FROMdbo.PDM_WFM_ProcePerformInfo WHERE ProceID = @ProceID
select * FROMdbo.PDM_WFM_ProceTask WHERE ProceID = @ProceIDselect *FROM dbo.PDM_WFM_ProceAttachs WHERE ProceID = @ProceID select *FROM dbo.PDM_WFM_Proce WHERE ID = @ProceID
；
FETCH NEXT FROM curDelProceINTO@ProceID,@ProceName
END --WHILE
CLOSE curDelProce
DEALLOCATE curDelProce
两个表关联查询
left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
inner join :内连接，又叫等值连接，只返回两个表中连接字段相等的行。
full join:外连接，返回两个表中的行：left join + right join
cro join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。
declare @a table(a int,b int)
declare @b table(a int,b int)
insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)
select * from @a
select * from @b
--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a --右：
select * from @a Aa right join @b Bb on Aa.a=Bb.a --内
select * from @a Aa inner join @b Bb on Aa.a=Bb.a --外：
select * from @a Aa full join @b Bb on Aa.a=Bb.a --交叉连接
select * from @acro join @b
