常用SQL语句_常用sql语句

其他范文时间：2020-02-28 05:34:22 收藏本文下载本文

【www.daodoc.com - 其他范文】

常用SQL语句由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“常用sql语句”。

查询：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(游标)table1结构如下idintnamevarchar(50)declare @id intdeclare @name varchar(50)declare cursor1 cursor for--定义游标cursor1select * from table1--使用游标的对象(跟据需要填入select文)9 open cursor1--打开游标

10fetch next from cursor1 into @id,@name--将游标向下移1行，获取的数据放入之前定义的变量@id,@name中

12while @@fetch_status=0--判断是否成功获取数据beginupdate table1 set name=name+'1'where id=@id--进行相应处理(跟据需要填入SQL文)

17fetch next from cursor1 into @id,@name--将游标向下移1行end

20close cursor1--关闭游标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)