sql server 中语法定义

创建数据库

create DataBase database_name

[on [primary]

[/<filespec/>[1,.......n]]

[,<filegroup>[1,.......n]]

]

[LOG ON {<filespec>[1,.......n]} ]

[FOR LOAD|FOR ATTACH]

<filespec>::=([

[NAME=logical_filename,]

FILENAME='os_file_name'

[,SIZE=size]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROUP=growth_increment ]

)[,.......n]

<filegroup>::=FILEGROUP filegroup_name<filespec>[,.......n]

被[]括中的内容表示可有或可不有

[1,....n]表示在括号前面的项可以出现1次或N次

被<>括中的内容表示里面的具体内容会在下文中详细说明

FOR ATTACH 是附加数据库文件

修改数据库

Alter DataBase database

{

Add File <filespace> [1,2,3....n] [To filegroup filegroup_name]

|Add Log File <filespace> [1,2,3....n]

|Remove File logical_file_name

|Add FileGroup filegroup_name

|Remove Filegroup filegroup_name

|Modify file <filesapce>

|Modify filegroup file_group_name filegroup_property

}

<filespec>::=([

[NAME=logical_filename,]

FILENAME='os_file_name'

[,SIZE=size]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROUP=growth_increment ]

)[,.......n]

Modify filegroup file_group_name filegroup_property 中的filegroup_property可以设置的值为：ReadOnly(文件组只读)

,ReadWrite（读写）,Default（将文件组设置为默认的文件组。只能设置一个默认文件组）

}

删除数据库

drop database database_name

创建表

CreateTable

[database_name.[owner].|owner.] table_name

(

{

<column_definition> | column_name as Computed_column_expression

|<table_constraint>

}[1,2,....,n]

)

[on {filegroup|default}]

[textimage_on {filegroup|default}]

<column_definition>::={column_name data_type}

[

[ default constrant_expression] | [identity [(seed,increment ) ] ]

]

[<column_constraint>][....n]

修改表

Alter Table table

Alter Column column_name new_data_type [(precision [, scale ] ) ] [null|no null]

|Add {[column_definition] | column_name as computed_column_expression}

|drop Column column_name[1,...,n]

|Add Constraint constraint_name constraint_definition [1,....,n]

|drop Constraint constraint_name [1,...,n]

删除表

drop table tablename[,...,n]

主键约束

constraint constraint_name primary key (CLUSTERED|NONCLUTERED) [(字段[1,...,n])]

唯一键约束

constraint constraint_nameunique key (CLUSTERED|NONCLUTERED) [(字段[1,...,n])]

外键约束

constraint constraint_nameforeige key [(字段[1,...,n])] references rel_table[(rel_column)] [on delete {cascade|no action}] [on update {CASCADE|NO ACTION}] [NOT FOR REPLICATION]

NOT FOR REPLICATION:如果添加了这个选项，那么在复制数据的时候将忽略该外键约束

cascade：将发生在主键上的改变级联到应用键

no action：同上。拒绝任何变动

核查约束

constraint constraint_name check('check_expression')

例如 constraint constraint1 check('sex' in ('男',‘女’))

创建规则

create RULE rule_name as condition_expression

condition_expression:@param logical_expression

绑定规则

sp_bindrule [@rulename=] 'rule_name',[@objname=] 'object_name'

例如 create rule salary

as

@para_name between 1000 and 5000

exec sq_bindrule 'salary','员工.工资' 员工工资范围在1000~5000之间

创建默认值

create Default default

as constraint_expression

绑定默认值

sq_binddefault [@defaultname=]'default_name',[@objname=]'object_name'

例如:

create default salary

as

1000

exec sq_binddefault 'salary','员工.工资'

========SELECT=============

Select [select_expression]select_list

[INTO new_table_name]

From table_list

[Where search_constraint]

[Group By group_expression group_by_list]

[Having search_conditions]

[Order by order_list [ASC|DESC]]

INTO new_table_name:将获得的信息插入到表中

select_expression(

distinct 不返回重复的数据行

top num 选择返回数据的顶部num行

top num precent 选择返回数据的顶部百分比

top num关键字==set rowcount n

)

from_search_conditions

(

比较（=,>,<,>=,<=,<>,!>,!<,!=）

范围（between .. and .. , not between .... and ...）

列表(in(A,B,C,D,E) )

模式（like expression）expression:%任何串字符;_任何一个字符;[A-Z]A到Z中的任何一个;[^A-Z]不是A和Z中的任何一个

)

where子句的执行顺序>groupby子句的执行顺序>having子句的执行顺序

group_expression

(

ALL 将不符合where子句的内容也进行显示。但在其返回的相应数据中以NULL标记

CUBE

ROLLUP

)

======Transaction编程===============

begin..and ..

if ...else ...

while logical_expression begin...[break|continue]...and

case param

when expression1 then

when expression2 then

when expression3 then

else

expression4

end

waitfor delay 时间间隔|time 时间值

goto

游标

Declare 游标名 CURSOR

[LOCAL|GLOBLE]

[FORWARD_ONLY|SCROLL]

[READONLY]

FOR 选择语句

[FOR UPDATE [OF 字段1，字段2]]

游标的读取方法

FIRST

LAST

PRIOR

NEXT

RELATIVE 相对

ABSOLUTE 绝对

例子

declare mycursor CURCOR

for

select * from customers

open mycursor

fetch next from mycursor

while @@fetch_status=0

begin

fetch next from mycursor

end

close mycursor

deallocate mycursor

fetch next from cursorName into @parameter1,@parameter2 从游标中读取数据存入变量中

======创建索引=========

create [unique] [CLUSTERED|NONCLUSTERED] Index index_name

on Table(column,...,n)

[

WITH [PAD_INDEX]

[,FILLFACTOR=filefactor]

[,IGNORE_DUP_KEY]

[,DROP_EXISTING]

[,STATISTICE_NORECOMPUTE]

]

[on filegroup]

FILLFACTOR填充程度 值1~100

IGNORE_DUP_KEY 是否忽略重复的值

DROP_EXISTING 指定应除去名称相同的索引然后重建

删除索引 drop index table.index[1,....,n]

=========创建触发器==============

create trigger trigger_name

on table|view

[with encryption]

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

as

sql_statement [....n]

(缓存表inserted,deleted)update=delete+insert

exec sp_helptrigger 'table'[,type]

exec sp_helptext 'trigger_name'

exec sp_help 'trigger_name'

exec sp_rename 'old_trigger_name','new_trigger_name'

drop trigger trigger_name[,...,n]

例如

create trigger trigger_salary_change

on pmanager

after update

as

if update(工资)

begin

if(select max(abs(inserted.工资-deleted.工资))

from inserted join deleted

on inserted.负责人ID =deleted.负责人ID)>2000

begin

print '工资变动不能超过2000'

ROLLBACK TRANSACTION

end

end

else

print '工资没有变！'

===========存储过程===========

create procedure procedure_name [;name]

[

{@parameter data_type} [varying] [=default] [output]

] [,....n]

[with {recomplie|encryption|recompile,encryption}]

[for replication]

as

sql_statement [,...,n]

执行存储过程

exec {[@return_status=] procedure_name [;name]}

[[@parameter=] {value|@variable [output] | [default] } ][,...,n]

例如

exec @returnvalue=GetAvgPbiaodi '参数','参数'

exec sq_helptext GetAvgPbiaodi

exec sq_depends GetAvgPbiaodi

exec sq_help GetAvgPbiaodi

exec sql_rename oldname newname