建表：

CREATE TABLE? [DB.dbo].tableName

(Stud_id int CONSTRAINT? constraintName1? not null primary key,

?Name nvarchar(5) not null,

?Birthday datetime,

?Gender nchar(1),

?Telcode char(12),

?Zipcode char(6) CONSTRAINT constraintName2 CHECK(zipcode like [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]),

?Deptcode tinyint CONSTRAINT constraintName3 check(Deptcode<100),

?Salary money DEFAULT 260

)

?

刪表：
DROP TABLE tableName ;

?修改表：

ALTER TABLE [DB.dbo.]tableName

ADD column_Name nvarchar(20) not null DEFAULT ‘liaihua’;

ALTER TABLE [DB.dbo.]tableName

DROP COLUMN column_Name;

ALTER TABLE [DB.dbo.]tableName

MODIFY column_Name columnType;

?

插入數據：

Insert into tableName[(column1,column2,column3…)] values(value1,value2,value3….);

Insert into tableName[(column1,column2,column3…)] select column1,column2,column3… from tableName2 [where…]

?

更新數據：

Update tableName set column1=value1,column2=value2,column3=value3…..where…

?

刪除數據：

Delete from tableName where ……

?

查詢數據：

Select? [ALL|DISTINCT] column1,column2,column3….. from tableName1[,table2,table3...]

[Where ……]

[Group by column_Name]

[Having …..]

[Order by column_Name [desc|asc]]

?

常用集函數：

SUM();AVG();MIN();MAX();COUNT();

?

SQL Server 支持五種約束：DEFAULT約束（默認約束）；PRIMARY KEY約束（主鍵約束）；CHECK約束（檢查約束）；FOREIGN KEY約束（外鍵約束）；UNIQUE約束；

?

約束的創建：

CREATE TABLE tableName

(column1 Type (null|not null)

[[CONSTRAINT constraintName]?????????????????? -----約束名如果不寫，系統自動加上

{

? PRIMARY KEY [CLUSTERED|NONCLUSTERED]

? |UNIQUE [CLUSTERED|NONCLUSTERED]

? |[FOREIGN KEY] REFERENCES ref_table[(ref_column)]

? |default constraintExpression

}

][,column2……]

)

?

約束的刪除：

ALTER TABLE tableName

DROP CONSTRAINT constraintName;

?

創建視圖：

CREATE VIEW [DB.dbo.]viewName

[(column1,column2,column3…..)]

AS

?selectStatement???????????? -----SQL查詢語句

[With check option]

?

修改視圖：

ALTER VIEW [DB.dbo.]viewName

[column1,column2,column3…..]

AS

selectStatement???????????? -----SQL查詢語句

[With check option]

?

通過視圖修改數據：

Update viewName set column1=value1,column2=value2…

Insert into viewName(column1,column2,column3…)values(value1,value2,value3…..);

Delete from viewName where…

?

創建索引：(非聚集索引可以建多個，聚集索引只可以建一個)

CREATE [UNIQUE][clustered|nonclustered] INDEX indexName

ON [[database.]].owner.]{tableName|viewName}(column1,(column2,column3…))

[WITH

[FILLFACTOR=fillfactor]

[[,]INGORE_DUP_KEY]

[[,]DROP_EXISTING]

[[,]STATISTICS_NORECOMPUTE]

[[,]SORT_IN_TEMPDB]

]

[ON filegroup]

?

Create unique clustered index myIndex on myTable(myColumn) on primary;

?

刪除索引：

DROP INDEX [owner.]tableName.indexName[,[owner.]tableName.indexName…..]

?

添加外鍵：

alter table tableName add CONSTRAINT FK_Cust_ID FOREIGN KEY（Cust_ID） REFERENCES tableName2(Cust_ID) 其中FK_Cust_ID 是外鍵約束的名字

?

創建存儲過程：

CREATE PROC[EDURE] [owner.]procedureName

[{@parameterName parameterType}

[=defaultValue][OUTPUT]

]

[WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FOR REPLICATION]

AS

Sql_statement

?

實例：

Create proc dbo.myProcedure

@StartDate datetime,@EndDate datetime,@Country varchar(20)=’China’

AS

If (StartDate is null or EndDate is null or Country is null)

Begin

???? RAISERROR(‘NULL value are valid’,5,5)

???? Return

End

Select * from dbo.myTable

Where OrderDate between @StartDate and @EndDate

And ShipCountry=@Country

?

?

刪除存儲過程：

DROP PROC [owner.]procedureName[,….n]

?

創建觸發器：

CREATE TRIGGER [owner.]triggerName

On [owner.]table|view

{for|after|instead of} {[insert][,delete][,update]}

[with encryption]

As

If update(column_name)[{and|or}update(column)…]

Sql_statement

?

實例：

Create trigger myTrigger

On myTable

For update

AS

If update(column1)

Begin

???? Raiserror(‘Unanthorized’,10,1)

???? Rollback transaction

end

?

create trigger myTrigger

on myTable

for delete

AS

delete from myTable2 inner join deleted on myTable2.bookId=deleted.bookId

?

?

本篇:SQL Server 語句查詢手冊 上一個:綁架百度谷歌蜘蛛快速更新快照 下一個:sql server 2000中取得最后插入記錄的自動編號ID

轉載于:https://www.cnblogs.com/alon/archive/2009/04/17/1438268.html

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的SQL Server 语句查询手册的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。