创建数据库、表、主外键、各种约束、存储过程、视图、索引、事务使用、触发器、创建登录账号、数据库用户 Sql语句示例
-
-----------创建数据库----------------??
-
user?master??
-
go??
-
if
?exists?(select?*?from?sysdatabases?where?name=
'bankSystem'
)??
-
????drop?database?bankSystem??
-
go??
-
create?database?bankSystem??
-
on?primary??
-
(??
-
????name='bank_data'
,??
-
????filename='D:\bank\bank_data.mdf'
,??
-
????size=5,??
-
????filegrowth=15%??
-
)??
-
log?on??
-
(??
-
????name='bank_log'
,??
-
????filename='D:\bank\bank_log.ldf'
,??
-
????size=5,??
-
????filegrowth=15%??
-
)??
-
go??
-
??
-
----------------建表并添加约束----------------??
-
use?bankSystem??
-
go??
-
---------------------表userInfo--------------??
-
if
?exists?(select?*?from?sysObjects?where?name=
'userInfo'
)??
-
????drop?table?userInfo??
-
go??
-
create?table?userInfo??
-
(??
-
????customerID?int
?identity(1,1)?not?
null
,??
-
????customerName?varchar(30)?not?null
,??
-
????PID?varchar(18)?not?null
,??
-
????telephone?varchar(13)?not?null
,??
-
????address?varchar(50)??
-
)??
-
go??
-
alter?table?userInfo??
-
add?constraint?PK_customID?primary?key?(customerID)??
-
alter?table?userInfo??
-
add?constraint?UQ_pid?unique?(PID)??
-
alter?table?userInfo??
-
add?constraint?CK_pid?check?(pid?like?'4206[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
?or??
-
????????????????????????????pid?like?'4206[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
)??
-
??
-
alter?table?userInfo??????
-
add?constraint?CK_telephone?check?(telephone?like?'13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
?or??
-
????????????????????????????????????telephone?like?'%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
)??
-
------------表cardInfo---------------??
-
if
?exists?(select?*?from?sysObjects?where?name=
'