說明備份sql server 創建 備份數據的 device
USE master
EXEC sp_addumpdevice
disk
testBack
c:\mssql
backup\MyNwind_
dat
開始 備份
BACKUP DATABASE pubs TO testBack
說明增加一個列 Alter table tabname add column col type
注
列增加後將不能刪除
DB
中列加上後數據類型也不能改變
唯一能改變的是增加varchar類型的長度
說明添加主鍵 Alter table tabname add primary key(col)
說明
刪除主鍵
Alter table tabname drop primary key(col)
說明復制表(只復制結構源表名a 新表名b) (Access可用)
法一
select * into b from a where
<>
法二
select top
* into b from a
說明拷貝表(拷貝數據源表名a 目標表名b) (Access可用)
insert into b(a b c) select def from b;
說明跨數據庫之間表的拷貝(具體數據使用絕對路徑) (Access可用)
insert into b(a b c) select def from b in 具體數據庫 where 條件
例子from b in &ServerMapPath()&\datamdb & where
說明顯示文章提交人和最後回復時間
select atitleausernamebadddate from table a(select max(adddate) adddate from table where tabletitle=atitle) b
說明外連接查詢(表名a 表名b)
select aa ab ac bc bd bf from a LEFT OUT JOIN b ON aa = bc
說明在線視圖查詢(表名a )
select * from (SELECT abc FROM a) T where ta > ;
說明between的用法between限制查詢數據范圍時包括了邊界值not between不包括
select * from table where time between time and time
select abc from table where a not between 數值 and 數值
說明日程安排提前五分鐘提醒
SQL: select * from 日程安排 where datediff(minutef開始時間getdate())>
說明一條sql 語句搞定數據庫分頁
select top b* from (select top 主鍵字段排序字段 from 表名 order by 排序字段 desc) a表名 b where b主鍵字段 = a主鍵字段 order by a排序字段
說明前條記錄
select top * form table where 范圍
說明選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息
select abc from tablename ta where a=(select max(a) from tablename tb where tbb=tab)
說明包括所有在 TableA 中但不在 TableB和TableC 中的行並消除所有重復行而派生出一個結果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
說明隨機取出條數據
select top * from tablename order by newid()
說明隨機選擇記錄
select newid()
說明刪除重復記錄
Delete from tablename where id not in (select max(id) from tablename group by colcol)
說明列出數據庫裡所有的表名
select name from sysobjects where type=U
說明列出表裡的所有的
select name from syscolumns where id=object_id(TableName)
[] [] []
From:http://tw.wingwit.com/Article/program/SQL/201311/16330.html
