SQL Server 语句查询手册
来源:互联网 发布:北大绩点算法 编辑:程序博客网 时间:2024/05/16 18:01
建表:
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
- SQL Server 语句查询手册
- Sql Server 查询语句
- SQL SERVER 查询语句
- SQL server 查询语句
- SQL Server查询语句
- Sql Server查询语句
- sql语句全能手册(sql server)
- sql server 简单查询语句
- sql server 查询语句积累
- sql server 查询语句基础
- SQl Server 查询语句总结
- sql server游标查询语句
- SQL server经典查询语句
- sql server 语句查询、函数
- SQL Server查询SQL语句的执行时间
- SQL Server SQL高级查询语句小结
- SQL Server SQL高级查询语句小结
- SQL Server SQL高级查询语句小结
- Perl程序设计中常用的函数
- SNS站发布!anders0821.11nn.net
- 关于WinExec(one.exe,SW_SHOW);
- flash全屏在Activex控件上和在Google chrome插件上的区别
- 驾校理论考试满分
- SQL Server 语句查询手册
- Effective C++学习7 条款07:为多态基类声明virtual析构函数
- 与我的灵魂对话
- linux 中得自己的ip地址代码
- Crystal Space 介绍
- 皮毛易懂,精通难求
- 神奇卡马克
- Effective C++读书笔记--Item 1:从四个语言层次理解C++
- sql常用语句