SQL Server 語句查詢手冊

建表：

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 語句查詢手冊

上一個:綁架百度Google蜘蛛快速更新快照 下一個:sql server 2000中取得最後插入記錄的自動編號ID