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

 

本文引自http://www.cnblogs.com/alon/archive/2009/04/17/1438268.html