sql server 中语法定义
创建数据库create DataBase database_name
primary]
[/<filespec/>]
[,<filegroup>]
]
} ]
FOR ATTACH]
<filespec>::=([
FILENAME='os_file_name'
[,SIZE=size]
[,MAXSIZE={max_size|UNLIMITED}]
[,FILEGROUP=growth_increment ]
)[,.......n]
<filegroup>::=FILEGROUP filegroup_name<filespec>[,.......n]
被[]括中的内容表示可有或可不有
表示在括号前面的项可以出现1次或N次
被<>括中的内容表示里面的具体内容会在下文中详细说明
FOR ATTACH 是附加数据库文件
修改数据库
Alter DataBase database
{
Add File <filespace>
|Add Log File <filespace>
|Remove File logical_file_name
|Add FileGroup filegroup_name
|Remove Filegroup filegroup_name
|Modify file <filesapce>
|Modify filegroup file_group_name filegroup_property
}
<filespec>::=([
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
.|owner.] table_name
(
{
<column_definition> | column_name as Computed_column_expression
|<table_constraint>
}
)
<column_definition>::={column_name data_type}
[
[ default constrant_expression] | ]
]
[<column_constraint>][....n]
修改表
Alter Table table
Alter Column column_name new_data_type [(precision [, scale ] ) ]
|Add { | column_name as computed_column_expression}
|drop Column column_name
|Add Constraint constraint_name constraint_definition
|drop Constraint constraint_name
删除表
drop table tablename[,...,n]
主键约束
constraint constraint_name primary key (CLUSTERED|NONCLUTERED) [(字段)]
唯一键约束
constraint constraint_nameunique key (CLUSTERED|NONCLUTERED) [(字段)]
外键约束
constraint constraint_nameforeige key [(字段)] references rel_table[(rel_column)]
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_list
From table_list
]
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中的任何一个
)
where子句的执行顺序>groupby子句的执行顺序>having子句的执行顺序
group_expression
(
ALL 将不符合where子句的内容也进行显示。但在其返回的相应数据中以NULL标记
CUBE
ROLLUP
)
======Transaction编程===============
begin..and ..
if ...else ...
while logical_expression begin......and
case param
when expression1 then
when expression2 then
when expression3 then
else
expression4
end
waitfor delay 时间间隔|time 时间值
goto
游标
Declare 游标名 CURSOR
FOR 选择语句
]
游标的读取方法
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 Index index_name
on Table(column,...,n)
[
WITH
[,FILLFACTOR=filefactor]
[,IGNORE_DUP_KEY]
[,DROP_EXISTING]
[,STATISTICE_NORECOMPUTE]
]
FILLFACTOR填充程度 值1~100
IGNORE_DUP_KEY 是否忽略重复的值
DROP_EXISTING 指定应除去名称相同的索引然后重建
删除索引 drop index table.index
=========创建触发器==============
create trigger trigger_name
on table|view
{for|after|instead of} {[,][,]}
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} [=default]
] [,....n]
as
sql_statement [,...,n]
执行存储过程
exec {[@return_status=] procedure_name [;name]}
[[@parameter=] {value|@variable | } ][,...,n]
例如
exec @returnvalue=GetAvgPbiaodi '参数','参数'
exec sq_helptext GetAvgPbiaodi
exec sq_depends GetAvgPbiaodi
exec sq_help GetAvgPbiaodi
exec sql_rename oldname newname
页:
[1]