2008-03-27
1 今天写的创建数据库的语句，主要有是一些有关如何往已经存在的表中添加新的列，如何删除已经存在的列有一些知识，如何创建登录，创建数据库用户，给数据库用户赋予权限，以及如何删除登录，删除数据库用户，今天刚自己学到的，在这里记录一下今天的劳动成果.
2
3
4
5 use master
6
7 go
8 /*判断stuDB是否存在,如果存在,则删除,然后再创建数据库*/
9 if exists(select * from sysdatabases where name = 'stuDB')
10
11 drop database stuDB
12 exec xp_cmdshell 'mkdir F:\Sql\stuDB'
13 create database stuDB
14
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
25 name = 'stuDB_log',
26 filename = 'F:\Sql\stuDB\stuDB_log.ldf',
27 size = 2mb,
28 filegrowth = 1mb
29 )
30 go
31
32
33 use stuDB
34
35 --创建表stuInfo
36 create table stuInfo
37 (
38 stuName varchar(20) not null, --姓名
39 stuNo char(6) not null,--学号
40 stuSex char(2) not null,--性别
41 stuAge int not null,--年龄
42 stuSeat smallint identity(1,1),--座位号,自动标识列
43 stuAddress text --住址
44
45 )
46
47
48 --添加主键约束
49 alter table stuInfo
50 add constraint PK_stuNo primary key(stuNo)
51 alter table stuInfo
52 add constraint CK_stuNo check (stuNo like 's253__')
53 --删除约束条件
54 --alter table stuInfo drop constraint ck_stuNo
55
56 --添加默认约束(地址为"地址不祥")
57 alter table stuInfo
58 add constraint DF_stuAddress default('地址不祥')for stuAddress
59
60 --添加Check约束(年龄在15到40之间)
61 alter table stuInfo
62 add constraint CK_stuAge check(stuAge between 15 and 40)
63
64 go
65
66 --创建表stuMarks
67 create table stuMarks
68 (
69 ExamNo char(7) not null,--考试号
70 stuNo char(6) not null,--学号
71 labExam int not null--机试成绩
72 )
73 go
74
75
76
77 --添加外键约束
78 alter table stuMarks
79 add constraint FK_stuNo
80 foreign key (stuNo) references stuInfo(stuNo)
81
82 --添加ckeck约束
83 alter table stuMarks
84 add constraint CK_labExam check(labExam between 0 and 100)
85
86 go
87
88 --创建登录帐户
89
90 --创建Windows登录帐户(如果创建的域用户,则用'域名\用户名',如果是Window本地用户,则用'计算机名\用户名')
91 exec sp_grantlogin 'HP-Gzp\administrator'
92
93 --创建SQL登录帐户
94 exec sp_addlogin 'gzp','1234'
95 go
96
97
98 --删除用户登录
99 drop login gzp
100
101 --创建数据库用户
102
103 exec sp_grantdbaccess 'HP-Gzp\administrator','gzpAdmin'
104 /*'HP-Gzp\administrator':为登录帐户,'gzp'为添加的数据库登录帐户*/
105 go
106
107 exec sp_grantdbaccess 'gzp' ,'gzp1'
108 go
109 --从当前数据库中删除数据库用户
110 exec sp_dropuser 'gzp1';
111 go
112 --从当前数据库中删除用户
113 --drop user gzp1
114
115 --删除数据库用户时，依赖于该用户的权限和别名也会删除。
116
117 exec sp_revokedbaccess 'gzp1';
118 go
119
120 --使用 sp_helpuser 将显示一个可从当前数据库中删除的用户名的列表
121 exec sp_helpuser
122 go
123
124
125
126 --向数据库用户授权
127
128 --grant 权限 [on 表名] to 数据库用户
129
130 grant select,insert on stuInfo to gzp1
131
132 --添加SQL帐户
133
134 use studb
135 go
136 --添加班主任帐户
137 exec sp_addlogin 'banzhuren' ,'111111'
138 go
139
140 exec sp_grantdbaccess 'banzhuren'
141 go
142 grant select ,insert ,update,delete on stuInfo to banzhuren
143 --添加教员帐户
144 exec sp_addlogin 'jiaoyuan','123456'
145 go
146
147 exec sp_grantdbaccess 'jiaoyuan'
148 go
149
150 grant select on stuInfo to jiaoyuan
151 go
152
153
154 --用语句添加删除表中的列(当然要先有一个数据库了用create database dataName创建数据库)
155 --先创建一个用于测试的表
156 create table test
157 (
158 column1 char(20)
159 )
160
161 go
162 --添加一个列column2
163 alter table test
164 add column2 varchar(20) null
165 go
166
167 --添加约束条件
168 alter table test
169 add constraint df_column2 default('aa') for column2
170 go
171 --要删除列,应该先删除该列的约束条件
172 alter table test
173 drop constraint df_column2
174 go
175
176 --删除列
177 alter table test
178 drop column column2
179 go
180
181 --修改表中列的类型
182 --先插入一行测试数据
183 insert into test (column1) values ('fd')
184 /*修改列的类型(注意:修改类型时,已经存在的数据应该
185 与要修改成的数据类型一致或者能够转换成要修改为的类型,否则会轮换失败)*/
186 alter table test alter column column1 varchar(20)
187 --删除表
188 drop table test
189
190
191
192
193
194
195
1 今天写的创建数据库的语句，主要有是一些有关如何往已经存在的表中添加新的列，如何删除已经存在的列有一些知识，如何创建登录，创建数据库用户，给数据库用户赋予权限，以及如何删除登录，删除数据库用户，今天刚自己学到的，在这里记录一下今天的劳动成果.
2
3
4
5 use master
6
7 go
8 /*判断stuDB是否存在,如果存在,则删除,然后再创建数据库*/
9 if exists(select * from sysdatabases where name = 'stuDB')
10
11 drop database stuDB
12 exec xp_cmdshell 'mkdir F:\Sql\stuDB'
13 create database stuDB
14
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
25 name = 'stuDB_log',
26 filename = 'F:\Sql\stuDB\stuDB_log.ldf',
27 size = 2mb,
28 filegrowth = 1mb
29 )
30 go
31
32
33 use stuDB
34
35 --创建表stuInfo
36 create table stuInfo
37 (
38 stuName varchar(20) not null, --姓名
39 stuNo char(6) not null,--学号
40 stuSex char(2) not null,--性别
41 stuAge int not null,--年龄
42 stuSeat smallint identity(1,1),--座位号,自动标识列
43 stuAddress text --住址
44
45 )
46
47
48 --添加主键约束
49 alter table stuInfo
50 add constraint PK_stuNo primary key(stuNo)
51 alter table stuInfo
52 add constraint CK_stuNo check (stuNo like 's253__')
53 --删除约束条件
54 --alter table stuInfo drop constraint ck_stuNo
55
56 --添加默认约束(地址为"地址不祥")
57 alter table stuInfo
58 add constraint DF_stuAddress default('地址不祥')for stuAddress
59
60 --添加Check约束(年龄在15到40之间)
61 alter table stuInfo
62 add constraint CK_stuAge check(stuAge between 15 and 40)
63
64 go
65
66 --创建表stuMarks
67 create table stuMarks
68 (
69 ExamNo char(7) not null,--考试号
70 stuNo char(6) not null,--学号
71 labExam int not null--机试成绩
72 )
73 go
74
75
76
77 --添加外键约束
78 alter table stuMarks
79 add constraint FK_stuNo
80 foreign key (stuNo) references stuInfo(stuNo)
81
82 --添加ckeck约束
83 alter table stuMarks
84 add constraint CK_labExam check(labExam between 0 and 100)
85
86 go
87
88 --创建登录帐户
89
90 --创建Windows登录帐户(如果创建的域用户,则用'域名\用户名',如果是Window本地用户,则用'计算机名\用户名')
91 exec sp_grantlogin 'HP-Gzp\administrator'
92
93 --创建SQL登录帐户
94 exec sp_addlogin 'gzp','1234'
95 go
96
97
98 --删除用户登录
99 drop login gzp
100
101 --创建数据库用户
102
103 exec sp_grantdbaccess 'HP-Gzp\administrator','gzpAdmin'
104 /*'HP-Gzp\administrator':为登录帐户,'gzp'为添加的数据库登录帐户*/
105 go
106
107 exec sp_grantdbaccess 'gzp' ,'gzp1'
108 go
109 --从当前数据库中删除数据库用户
110 exec sp_dropuser 'gzp1';
111 go
112 --从当前数据库中删除用户
113 --drop user gzp1
114
115 --删除数据库用户时，依赖于该用户的权限和别名也会删除。
116
117 exec sp_revokedbaccess 'gzp1';
118 go
119
120 --使用 sp_helpuser 将显示一个可从当前数据库中删除的用户名的列表
121 exec sp_helpuser
122 go
123
124
125
126 --向数据库用户授权
127
128 --grant 权限 [on 表名] to 数据库用户
129
130 grant select,insert on stuInfo to gzp1
131
132 --添加SQL帐户
133
134 use studb
135 go
136 --添加班主任帐户
137 exec sp_addlogin 'banzhuren' ,'111111'
138 go
139
140 exec sp_grantdbaccess 'banzhuren'
141 go
142 grant select ,insert ,update,delete on stuInfo to banzhuren
143 --添加教员帐户
144 exec sp_addlogin 'jiaoyuan','123456'
145 go
146
147 exec sp_grantdbaccess 'jiaoyuan'
148 go
149
150 grant select on stuInfo to jiaoyuan
151 go
152
153
154 --用语句添加删除表中的列(当然要先有一个数据库了用create database dataName创建数据库)
155 --先创建一个用于测试的表
156 create table test
157 (
158 column1 char(20)
159 )
160
161 go
162 --添加一个列column2
163 alter table test
164 add column2 varchar(20) null
165 go
166
167 --添加约束条件
168 alter table test
169 add constraint df_column2 default('aa') for column2
170 go
171 --要删除列,应该先删除该列的约束条件
172 alter table test
173 drop constraint df_column2
174 go
175
176 --删除列
177 alter table test
178 drop column column2
179 go
180
181 --修改表中列的类型
182 --先插入一行测试数据
183 insert into test (column1) values ('fd')
184 /*修改列的类型(注意:修改类型时,已经存在的数据应该
185 与要修改成的数据类型一致或者能够转换成要修改为的类型,否则会轮换失败)*/
186 alter table test alter column column1 varchar(20)
187 --删除表
188 drop table test
189
190
191
192
193
194
195
