2008-3-20
1
这是今天做的数据库,感觉都生孰了.技术这东西啊,一定得常用啊.
2![](/Images/OutliningIndicators/None.gif)
3
4![](/Images/OutliningIndicators/None.gif)
5
use master
6![](/Images/OutliningIndicators/None.gif)
7
go
8
/*判断stuDB是否存在,如果存在,则删除,然后再创建数据库*/
9
if exists(select * from sysdatabases where name = 'stuDB')
10![](/Images/OutliningIndicators/None.gif)
11
drop database stuDB
12![](/Images/OutliningIndicators/None.gif)
13
create database stuDB
14![](/Images/OutliningIndicators/None.gif)
15
on primary --主文件组,可省略
16
(
17
name = 'stuDB_data',--主数据库文件的逻辑名
18
filename = 'F:\Sql\stuDB\stuDB_data.mdf',--主数据库文件的物理名
19
size = 5mb,
20
maxsize = 100mb,
21
filegrowth = 15%
22
)
23
log on(
24![](/Images/OutliningIndicators/None.gif)
25
name = 'stuDB_log',
26
filename = 'F:\Sql\stuDB\stuDB_log.ldf',
27
size = 2mb,
28
filegrowth = 1mb
29
)
30
go
31![](/Images/OutliningIndicators/None.gif)
32![](/Images/OutliningIndicators/None.gif)
33
use stuDB
34![](/Images/OutliningIndicators/None.gif)
35
--创建表stuInfo
36
create table stuInfo
37
(
38
stuName varchar(20) not null, --姓名
39
stuNo char(6) not null,--学号
40
stuAge int not null,--年龄
41
stuID numeric(18,0),--身份证号
42
stuSeat smallint identity(1,1),--座位号,自动标识列
43
stuAddress text --住址
44![](/Images/OutliningIndicators/None.gif)
45
)
46
go
47
--创建表stuMarks
48
create table stuMarks
49
(
50
ExamNo char(7) not null,--考试号
51
stuNo char(6) not null,--学号
52
labExam int not null--机试成绩
53
)
54
go
55![](/Images/OutliningIndicators/None.gif)
56
--添加主键约束
57
alter table stuInfo
58
add constraint PK_stuNo primary key(stuNo)
59![](/Images/OutliningIndicators/None.gif)
60
--添加唯一约束
61
alter table stuInfo
62
add constraint UQ_stuID unique(stuID)
63![](/Images/OutliningIndicators/None.gif)
64
--添加默认约束(地址为"地址不祥")
65
alter table stuInfo
66
add constraint DF_stuAddress default('地址不祥')for stuAddress
67![](/Images/OutliningIndicators/None.gif)
68
--添加Check约束(年龄在15到40之间)
69
alter table stuInfo
70
add constraint CK_stuAge check(stuAge between 15 and 40)
71![](/Images/OutliningIndicators/None.gif)
72
--添加外键约束
73
alter table stuMarks
74
add constraint FK_stuNo
75
foreign key (stuNo) references stuInfo(stuNo)
76![](/Images/OutliningIndicators/None.gif)
77
--添加ckeck约束
78
alter table stuMarks
79
add constraint CK_labExam check(labExam between 0 and 100)
80![](/Images/OutliningIndicators/None.gif)
81
go
82![](/Images/OutliningIndicators/None.gif)
83
--创建登录帐户
84![](/Images/OutliningIndicators/None.gif)
85
--创建Windows登录帐户(如果创建的域用户,则用'域名\用户名',如果是Window本地用户,则用'计算机名\用户名')
86
exec sp_grantlogin 'HP-Gzp\administrator'
87![](/Images/OutliningIndicators/None.gif)
88
--创建SQL登录帐户
89
exec sp_addlogin 'gzp','1234'
90![](/Images/OutliningIndicators/None.gif)
91
--创建数据库用户
92![](/Images/OutliningIndicators/None.gif)
93
exec sp_grantdbaccess 'HP-Gzp\administrator','gzp'
94
/*'HP-Gzp\administrator':为登录帐户,'gzp'为添加的数据库用户*/
95![](/Images/OutliningIndicators/None.gif)
96
exec sp_grantdbaccess
97![](/Images/OutliningIndicators/None.gif)
98
--向数据库用户授权
99![](/Images/OutliningIndicators/None.gif)
100
--grant 权限 [on 表名] to 数据库用户
101![](/Images/OutliningIndicators/None.gif)
102
grant select,insert on stuInfo to gzp
103![](/Images/OutliningIndicators/None.gif)
104![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/Images/OutliningIndicators/None.gif)
60
![](/Images/OutliningIndicators/None.gif)
61
![](/Images/OutliningIndicators/None.gif)
62
![](/Images/OutliningIndicators/None.gif)
63
![](/Images/OutliningIndicators/None.gif)
64
![](/Images/OutliningIndicators/None.gif)
65
![](/Images/OutliningIndicators/None.gif)
66
![](/Images/OutliningIndicators/None.gif)
67
![](/Images/OutliningIndicators/None.gif)
68
![](/Images/OutliningIndicators/None.gif)
69
![](/Images/OutliningIndicators/None.gif)
70
![](/Images/OutliningIndicators/None.gif)
71
![](/Images/OutliningIndicators/None.gif)
72
![](/Images/OutliningIndicators/None.gif)
73
![](/Images/OutliningIndicators/None.gif)
74
![](/Images/OutliningIndicators/None.gif)
75
![](/Images/OutliningIndicators/None.gif)
76
![](/Images/OutliningIndicators/None.gif)
77
![](/Images/OutliningIndicators/None.gif)
78
![](/Images/OutliningIndicators/None.gif)
79
![](/Images/OutliningIndicators/None.gif)
80
![](/Images/OutliningIndicators/None.gif)
81
![](/Images/OutliningIndicators/None.gif)
82
![](/Images/OutliningIndicators/None.gif)
83
![](/Images/OutliningIndicators/None.gif)
84
![](/Images/OutliningIndicators/None.gif)
85
![](/Images/OutliningIndicators/None.gif)
86
![](/Images/OutliningIndicators/None.gif)
87
![](/Images/OutliningIndicators/None.gif)
88
![](/Images/OutliningIndicators/None.gif)
89
![](/Images/OutliningIndicators/None.gif)
90
![](/Images/OutliningIndicators/None.gif)
91
![](/Images/OutliningIndicators/None.gif)
92
![](/Images/OutliningIndicators/None.gif)
93
![](/Images/OutliningIndicators/None.gif)
94
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
95
![](/Images/OutliningIndicators/None.gif)
96
![](/Images/OutliningIndicators/None.gif)
97
![](/Images/OutliningIndicators/None.gif)
98
![](/Images/OutliningIndicators/None.gif)
99
![](/Images/OutliningIndicators/None.gif)
100
![](/Images/OutliningIndicators/None.gif)
101
![](/Images/OutliningIndicators/None.gif)
102
![](/Images/OutliningIndicators/None.gif)
103
![](/Images/OutliningIndicators/None.gif)
104
![](/Images/OutliningIndicators/None.gif)