SQL Server 2000常用命令,語法使用方法

2013/2/6 17:34:421506 閱讀

(1) 數據記錄篩選：

sql="select * from 數據表 where 字段名=字段值 order by 字段名 [desc]"
sql="select * from 數據表 where 字段名 like '%字段值%' order by 字段名 [desc]"
sql="select top 10 * from 數據表 where 字段名 order by 字段名 [desc]"
sql="select * from 數據表 where 字段名 in ('值1','值2','值3')"
sql="select * from 數據表 where 字段名 between 值1 and 值2"

(2) 更新數據記錄：

sql="update 數據表 set 字段名=字段值 where 條件表達式"
sql="update 數據表 set 字段1=值1,字段2=值2 …… 字段n=值n where 條件表達式"

(3) 刪除數據記錄：

sql="delete from 數據表 where 條件表達式"
sql="delete from 數據表" (將數據表所有記錄刪除)

(4) 添加數據記錄：

sql="insert into 數據表 (字段1,字段2,字段3 …) values (值1,值2,值3 …)"
sql="insert into 目標數據表 select * from 源數據表" (把源數據表的記錄添加到目標數據表)

(5) 數據記錄統(tǒng)計函數：

AVG(字段名) 得出一個表格欄平均值
COUNT(*|字段名) 對數據行數的統(tǒng)計或對某一欄有值的數據行數統(tǒng)計
MAX(字段名) 取得一個表格欄最大的值
MIN(字段名) 取得一個表格欄最小的值
SUM(字段名) 把數據欄的值相加

引用以上函數的方法：

sp_helpdb 查看數據庫

go

use

數據庫名

go

select * from sysobjects where xtype='u' 查看數據庫中有什么數據表

go

sp_help 數據表名 查看數據表的結構

go

sql="select sum(字段名) as 別名 from 數據表 where 條件表達式"

set rs=conn.excute(sql)

用 rs("別名") 獲取統(tǒng)的計值，其它函數運用同上。

(5) 數據表的建立和刪除：

CREATE TABLE 數據表名稱(字段1 類型1(長度),字段2 類型2(長度) …… )

例：CREATE TABLE tab01(name varchar(50),datetime default now())

DROP TABLE 數據表名稱 (永久性刪除一個數據表)

4. 記錄集對象的方法：

rs.movenext 將記錄指針從當前的位置向下移一行
rs.moveprevious 將記錄指針從當前的位置向上移一行
rs.movefirst 將記錄指針移到數據表第一行
rs.movelast 將記錄指針移到數據表最后一行
rs.absoluteposition=N 將記錄指針移到數據表第N行
rs.absolutepage=N 將記錄指針移到第N頁的第一行
rs.pagesize=N 設置每頁為N條記錄

２、更改表格

ALTER TABLE table_name

ADD COLUMN column_name DATATYPE

說明：增加一個欄位（沒有刪除某個欄位的語法。

ALTER TABLE table_name

ADD PRIMARY KEY (column_name)

說明：更改表得的定義把某個欄位設為主鍵。

ALTER TABLE table_name

DROP PRIMARY KEY (column_name)

說明：把主鍵的定義刪除。

３、建立索引

CREATE INDEX index_name ON table_name (column_name)

說明：對某個表格的欄位建立索引以增加查詢時的速度。

４、刪除

DROP table_name

DROP index_name

二、的資料形態(tài) DATATYPEs

smallint

16 位元的整數。

interger

32 位元的整數。

decimal(p,s)

p 精確值和 s 大小的十進位整數，精確值p是指全部有幾個數(digits)大小值，s是指小數

點後有幾位數。如果沒有特別指定，則系統(tǒng)會設為 p=5; s=0 。

float

32位元的實數。

double

64位元的實數。

char(n)

n 長度的字串，n不能超過 254。

varchar(n)

長度不固定且其最大長度為 n 的字串，n不能超過 4000。

graphic(n)

和 char(n) 一樣，不過其單位是兩個字元 double-bytes， n不能超過127。這個形態(tài)是為

了支援兩個字元長度的字體，例如中文字。

vargraphic(n)

可變長度且其最大長度為 n 的雙字元字串，n不能超過 2000。

date

包含了 年份、月份、日期。

time

包含了 小時、分鐘、秒。

timestamp

包含了 年、月、日、時、分、秒、千分之一秒。

三、資料操作 ＤＭＬ （Data Manipulation Language)

資料定義好之後接下來的就是資料的操作。資料的操作不外乎增加資料（insert)、查詢資料（query）、更改資料（update) 、刪除資料（delete）四種模式，以下分 別介紹他們的語法：

１、增加資料：

INSERT INTO table_name (column1,column2,...)

valueS ( value1,value2, ...)

說明：

1.若沒有指定column 系統(tǒng)則會按表格內的欄位順序填入資料。

2.欄位的資料形態(tài)和所填入的資料必須吻合。

3.table_name 也可以是景觀 view_name。

INSERT INTO table_name (column1,column2,...)

SELECT columnx,columny,... FROM another_table

說明：也可以經過一個子查詢（subquery）把別的表格的資料填入。

２、查詢資料：

基本查詢

SELECT column1,columns2,...

FROM table_name

說明：把table_name 的特定欄位資料全部列出來

SELECT *

FROM table_name

WHERE column1 = xxx

[AND column2 > yyy] [OR column3 <> zzz]

三)交叉連接

交叉連接不帶WHERE 子句，它返回被連接的兩個表所有數據行的笛卡爾積，返回到結果集合中的數

據行數等于第一個表中符合查詢條件的數據行數乘以第二個表中符合查詢條件的數據行數。

例，titles表中有6類圖書，而publishers表中有8家出版社，則下列交叉連接檢索到的記錄數將等

于6*8=48行。

SELECT type,pub_name

FROM titles CROSS JOIN publishers

ORDER BY type

UNION運算符可以將兩個或兩個以上上SELECT語句的查詢結果集合合并成一個結果集合顯示，即執(zhí)行聯合查詢。UNION的語法格式為：

select_statement

UNION [ALL] selectstatement

[UNION [ALL] selectstatement][…n]

其中selectstatement為待聯合的SELECT查詢語句。

ALL選項表示將所有行合并到結果集合中。不指定該項時，被聯合查詢結果集合中的重復行將只保留一

行。

聯合查詢時，查詢結果的列標題為第一個查詢語句的列標題。因此，要定義列標題必須在第一個查詢語

句中定義。要對聯合查詢結果排序時，也必須使用第一查詢語句中的列名、列標題或者列序號。

在使用UNION 運算符時，應保證每個聯合查詢語句的選擇列表中有相同數量的表達式，并且每個查詢選

擇表達式應具有相同的數據類型，或是可以自動將它們轉換為相同的數據類型。在自動轉換時，對于數值類

型，系統(tǒng)將低精度的數據類型轉換為高精度的數據類型。

在包括多個查詢的UNION語句中，其執(zhí)行順序是自左至右，使用括號可以改變這一執(zhí)行順序。例如：

查詢1 UNION (查詢2 UNION 查詢3)

INSERT語句

用戶可以用INSERT語句將一行記錄插入到指定的一個表中。例如，要將雇員John Smith的記錄插入到本例的表中，可以使用如下語句：

INSERT INTO EMPLOYEES valueS

('Smith','John','1980-06-10',

'Los Angles',16,45000);

通過這樣的INSERT語句，系統(tǒng)將試著將這些值填入到相應的列中。這些列按照我們創(chuàng)建表時定義的順序排列。在本例中，第一個值“Smith”將填到第一個列LAST_NAME中；第二個值“John”將填到第二列FIRST_NAME中……以此類推。

我們說過系統(tǒng)會“試著”將值填入，除了執(zhí)行規(guī)則之外它還要進行類型檢查。如果類型不符（如將一個字符串填入到類型為數字的列中），系統(tǒng)將拒絕這一次操作并返回一個錯誤信息。

如果SQL拒絕了你所填入的一列值，語句中其他各列的值也不會填入。這是因為SQL提供對事務的支持。一次事務將數據庫從一種一致性轉移到另一種一致性。如果事務的某一部分失敗，則整個事務都會失敗，系統(tǒng)將會被恢復（或稱之為回退）到此事務之前的狀態(tài)。

回到原來的INSERT的例子，請注意所有的整形十進制數都不需要用單引號引起來，而字符串和日期類型的值都要用單引號來區(qū)別。為了增加可讀性而在數字間插入逗號將會引起錯誤。記住，在SQL中逗號是元素的分隔符。

同樣要注意輸入文字值時要使用單引號。雙引號用來封裝限界標識符。

對于日期類型，我們必須使用SQL標準日期格式（yyyy-mm-dd）



--設置用戶訪問數據庫的形式 Alter database TestAA set SINGLE_USER with rollback immediate --設置單用戶訪問數據庫

Alter database TestAA set RESTRICTED_USER with rollback immediate

RESTRICTED_USER 只允許 db_owner 固定數據庫角色成員以及 dbcreator 和 sysadmin 固定服務器角色成員連接到數據庫，不過對連接數沒有限制。在 ALTER DATABASE 語句的終止子句所指定的時間范圍內，所有數據庫連接都將被斷開。在數據庫轉換到 RESTRICTED_USER 狀態(tài)后，不合格用戶所做的連接嘗試將被拒絕。

Alter database TestAA set MULTI_USER with rollback immediate --設置用戶正常訪問

--設置數據庫恢復模式

sql 2008 不支持nolog 和 truncate_only 如果要截斷日志的話，需要先把數據庫搞成簡單模型，然后在收縮日志，在改成完全模式。備份一次數據庫。

Alter database set recovery {simple/full/bulk_logged}

dbcc shrinkfile('filename',10)

--導出數據到文本

--EXEC master..xp_cmdshell 'bcp "Select cdoc_id,csubject from cmsdbquery..tbl_article" queryout c:\DT.txt -c -SXXXXX -Usa -PXXXXX'

--跟蹤腳本需要打開的選項

set statistics io on

set statistics profile on

set statistics time on

--釋放過程高速緩存將導致重新編譯某些部分

DBCC FREEPROCCACHE

--從緩沖池中刪除所有清除緩沖區(qū)

不用關閉和重啟服務器或者服務

DBCC DROPCLEANBUFFERS

--以報表形式顯示有關過程高速緩存的信息

DBCC PROCCACHE

--顯示指定表上的指定目標的當前分布統(tǒng)計信息

DBCC SHOW_STATISTICS ( table , target )

--從遠程服務器打開數據庫連接取數據

SELECT top 20 *

FROM OPENDATASOURCE(

'SQLOLEDB',

'Data Source=xxx.xxx.xx.x,port;User ID=sa;Password=xxxxxxx'

).[product].dbo.tableaa

data source 好像不能有中括號，好多網友的opendatasource不能用就是這個原因

SELECT *

FROM OpenDataSource

('Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\bbb.xls";User ID=;Password=;Extended properties=Excel 5.0')...[sheet1$]

文本的查詢

文件必須是,號分割文件

HDR=YES 的時候,第一行被認為是字段名

HDR=NO 的時候,第一行被認為是數據

select * from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Text;HDR=NO;DATABASE=C:\' --c:\是目錄

,aaa#txt) --文件名

BULK INSERT xxxx_trs.dbo.xxxx_day

FROM 'F:\BI_DsssDayAddin_cms_IN.csv'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n'

)

--查看sql版本

SELECT SERVERPROPERTY('productversion'),SERVERPROPERTY('productlevel'),SERVERPROPERTY('edition')

--初始化標識列

DBCC CHECKIDENT (ProductPicture, RESEED, 1)

--數據庫2個比較常用的函數

DATABASEPROPERTY 返回數據庫狀態(tài)的值

select DATABASEPROPERTY(dbname, 'issingleuser') 返回數據庫是不是單用戶模式

HAS_DBACCESS ( 'database_name' )

返回信息，說明用戶是否可以訪問指定的數據庫

--T終端用戶的人

Query user 查詢當前登錄用戶

LogOff sessionid 注銷會話

Tsdiscon sessionid 斷開某個會話的連接

命令行下需要先建立一個ipc$會話

net use \\10.168.0.21\c$ "XX" /user:administrator

query user /server 10.168.0.21

tsdiscon 2 /server 10.168.0.21

創(chuàng)建與刪除SQL約束或字段約束。 SQL約束控制

1)禁止所有表約束的SQL

select'alter table '+name+' nocheck constraint all'from sysobjects where type='U'

2)刪除所有表數據的SQL

select'TRUNCATE TABLE '+name from sysobjects where type='U'

3)恢復所有表約束的SQL

select'alter table '+name+' check constraint all'from sysobjects where type='U'

4)刪除某字段的約束

declare@namevarchar(100)

--DF為約束名稱前綴

select@name=b.name from syscolumns a,sysobjects b where a.id=object_id('表名') and b.id=a.cdefault and a.name='字段名'and b.name like'DF%'

--刪除約束

altertable 表名 dropconstraint@name

--為字段添加新默認值和約束

ALTERTABLE 表名 ADDCONSTRAINT@name DEFAULT (0) FOR[字段名] 對字段約束進行更改

--刪除約束

ALTERTABLE tablename

DropCONSTRAINT 約束名

--修改表中已經存在的列的屬性（不包括約束，但可以為主鍵或遞增或唯一）

ALTERTABLE tablename

altercolumn 列名 intnotnull

--添加列的約束

ALTERTABLE tablename

ADDCONSTRAINT DF_tablename_列名 DEFAULT(0) FOR 列名

--添加范圍約束

altertable tablename add check(性別 in ('M','F'))







創(chuàng)建一個庫

CREATE DATABASE g2 ON PRIMARY

( NAME = 'g2', FILENAME = 'E:\benet\data1\g1.mdf' , SIZE = 307200KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = 'g2_log', FILENAME = 'E:\benet\data1\g1_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

創(chuàng)建一個表

CREATE TABLE biao2

(

id int NOT NULL,

xingming char(10) NOT NULL,

xingbie float(53) NOT NULL,

nianling int NOT NULL,

chengji float(53) NULL

)

查詢

select * from biao1

where chengji between 400 and 600

select * from biao1

where nianling like 55

select * from biao1

where nianling between 10 and 100

select * from biao1

where id = 5

insert into biao1

values (6 ,'原遲',1 , 100 , 1542.22)

update biao1

set chengji = chengji + 1000

select * from biao1

delete

from biao1

where id = 1

select * from biao1

select * from biao1 排序

order by no asc

update biao2

set zongji = shuxue + yuwen + huaxue

where xingming = 'a'

insert into biao1

values

(7,'骨血松濤',1 , 700)

select * from biao1

where xingming like '%血%'

select distinct xingbie from biao1

alter table biao1

drop column chengji 刪除一列

alter table biao2

add

liehao int

select * from biao1,biao2

where biao1.id=biao2.id 將表一和表二聯系起來，建立主索引， 注意小數點的應用

select * from biao1

inner join biao2

on biao1.id=biao2.id 和上面的一樣

查詢時間

select * from biao2

where riqi between '1993-01-01 00:00:00.000' and '1994-6-29 00:00:00.000'

select xingming from biao2

where bumen ='xiaoshou' and xingbie ='na'

delete

from biao2

where xingming='jinpeng'

select id ,xingming into biao2_backup

from biao2

where xingbie='na' 備份選擇的數據

create view shitu as

select xingming ,riqi

from biao2

where xingbie = 'na' 創(chuàng)建一個自己匹配的視圖

視圖相當于是一個符合你的目的的一個查詢結果集.

他不占用空間,方便你以后的查找.

多文件還原代碼

ESTORE DATABASE [benet] FROM DISK = N'G:\bak\wz.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

GO

RESTORE DATABASE [benet] FROM DISK = N'G:\bak\cy.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

GO

恢復數據庫 benet 來自文件 DISK = N'G:\bak\cy.bak' with 文件數1個，

NORECOVERY 指定不發(fā)生回滾。從而使前滾按順序在下一條語句中繼續(xù)進行。

在這種情況下，還原順序可還原其他備份，并執(zhí)行前滾。

REPLACE覆蓋

A. 還原完整數據庫

注意：

MyAdvWorks 數據庫僅供舉例說明。

以下示例還原完整數據庫備份。

復制代碼

RESTORE DATABASE MyAdvWorks

FROM MyAdvWorks_1

注意：

對于使用完全恢復模式或大容量日志恢復模式的數據庫，在大多數情況下，SQL Server 2005 都要求您在還原數據庫前備份日志尾部。有關詳細信息，請參閱尾日志備份。

B. 還原完整數據庫備份和差異備份

以下示例還原完整數據庫備份后還原差異備份。另外，以下示例還說明如何還原媒體上的另一個備份集。差異備份追加到包含完整數據庫備份的備份設備上。

復制代碼

RESTORE DATABASE MyAdvWorks

FROM MyAdvWorks_1

WITH NORECOVERY

RESTORE DATABASE MyAdvWorks

FROM MyAdvWorks_1

WITH FILE = 2

C. 使用 RESTART 語法還原數據庫

以下示例使用 RESTART 選項重新啟動因服務器電源故障而中斷的 RESTORE 操作。

復制代碼

-- This database RESTORE halted prematurely due to power failure.

RESTORE DATABASE MyAdvWorks

FROM MyAdvWorks_1

-- Here is the RESTORE RESTART operation.

RESTORE DATABASE MyAdvWorks

FROM MyAdvWorks_1 WITH RESTART

D. 還原數據庫并移動文件

以下示例還原完整數據庫和事務日志，并將已還原的數據庫移動到 C:\Program Files\Microsoft SQL Server\MSSQL\Data 目錄下。

復制代碼

RESTORE DATABASE MyAdvWorks

FROM MyAdvWorks_1

WITH NORECOVERY,

MOVE 'MyAdvWorks' TO

'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf',

MOVE 'MyAdvWorksLog1'

TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf'

RESTORE LOG MyAdvWorks

FROM MyAdvWorksLog1

WITH RECOVERY

E. 使用 BACKUP 和 RESTORE 創(chuàng)建數據庫的副本

以下示例使用 BACKUP 和 RESTORE 語句創(chuàng)建 AdventureWorks 數據庫的副本。MOVE 語句使數據和日志文件還原到指定的位置。RESTORE FILELISTONLY 語句用于確定待還原數據庫內的文件數及名稱。該數據庫的新副本稱為 TestDB。有關詳細信息，請參閱 RESTORE FILELISTONLY (Transact-SQL)。

復制代碼

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.bak'

RESTORE FILELISTONLY

FROM DISK = 'C:\AdventureWorks.bak'

RESTORE DATABASE TestDB

FROM DISK = 'C:\AdventureWorks.bak'

WITH MOVE 'AdventureWorks_Data' TO 'C:\testdb.mdf',

MOVE 'AdventureWorks_Log' TO 'C:\testdb.ldf'

GO

F. 使用 STOPAT 語法還原到時間點和使用多個設備進行還原

以下示例將數據庫還原到它在 2005 年 4 月 15 日中午 12 點時的狀態(tài)，并顯示涉及多個日志和多個備份設備的還原操作。

復制代碼

RESTORE DATABASE MyAdvWorks

FROM MyAdvWorks_1, MyAdvWorks_2

WITH NORECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM'

RESTORE LOG MyAdvWorks

FROM MyAdvWorksLog1

WITH NORECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM'

RESTORE LOG MyAdvWorks

FROM MyAdvWorksLog2

WITH RECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM'

G. 將事務日志還原到標記

以下示例將事務日志還原到名為 ListPriceUpdate 的標記事務中的標記處。

復制代碼

USE AdventureWorks

GO

BEGIN TRANSACTION ListPriceUpdate

WITH MARK 'UPDATE Product list prices'

GO

UPDATE Production.Product

SET ListPrice = ListPrice * 1.10

WHERE ProductNumber LIKE 'BK-%'

GO

COMMIT TRANSACTION ListPriceUpdate

GO

-- Time passes. Regular database

-- and log backups are taken.

-- An error occurs.

USE master

GO

RESTORE DATABASE AdventureWorks

FROM AdvWorks1

WITH FILE = 3, NORECOVERY

GO

RESTORE LOG AdventureWorks

FROM AdvWorks1

WITH FILE = 4,

STOPATMARK = 'ListPriceUpdate'

H. 使用 TAPE 語法還原

以下示例從 TAPE 備份設備還原完整數據庫備份。

復制代碼

RESTORE DATABASE MyAdvWorks

FROM TAPE = '\\.\tape0'

I. 使用 FILE 和 FILEGROUP 語法還原

以下示例還原一個包含兩個文件、一個文件組和一個事務日志的數據庫。

復制代碼

RESTORE DATABASE MyAdvWorks

FILE = 'MyAdvWorks_data_1',

FILE = 'MyAdvWorks_data_2',

FILEGROUP = 'new_customers'

FROM MyAdvWorks_1

WITH NORECOVERY

-- Restore the log backup.

RESTORE LOG MyAdvWorks

FROM MyAdvWorksLog1

J. 恢復到數據庫快照

以下示例將數據庫恢復到數據庫快照。此示例假定該數據庫當前僅存在一個快照。有關創(chuàng)建此數據庫快照的示例，請參閱如何創(chuàng)建數據庫快照 (Transact-SQL)。

注意：

恢復到快照將刪除所有全文目錄。

復制代碼

USE master

RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';

GO

下一條：asp統(tǒng)計字符串中中文字符的個數

上一條：asp替換空格正則表達式