SQL SERVER 2005练习集（修改数据库文件，修改表数据，约束，视图）

约束

PRIMARY KEY（主键）约束

FOREIGN KEY（外键）约束

UNIQUE （唯一）约束

CHECK （核查）约束

=======================

1、修改数据库名称

use master
go
execute sp_renamedb 'newDemo','Demo'
go

2、数据库文件组更名

use master
go
alter database school
modify filegroup  teachers
name=teacher
go

3、查看文件组属性

use school
go
exec sp_helpfilegroup
go

4、更改数据文件名称

use school
go
alter database school
modify file
(
  name=school2,
  newname=school3
)
go

5、通过sp_helpfile系统存储过程来查看数据文件属性

use  school
go
exec sp_helpfile
go

6、修改数据库school中逻辑名称为school2的数据文件物理位置，将其变更到c：/下

use  master
go
alter database school
modify  file
(
  name=school2
  filename='c:/school2.ndf'    --filename无效
)
go

7、修改数据文件的大小

use school
go
alter database school
modify file
(
  name=school2,
  size=23
)
go

8、添加文件组

alter database school
add filegroup linshi1
go

9、添加数据文件（在linshi1中）

alter database school
add file
(
   name=linshi2,
   filename='D:/SQL  2005/School/Data/linshi2.ndf',
   size=3,
   maxsize=20,
   filegrowth=1
)
to filegroup linshi1
go

10、添加日志文件

alter database school
add log file
(
   name=school_log4,
   filename='D:/SQL  2005/School/Data/school_log4.ldf',
   size=3,
   maxsize=20,
   filegrowth=1
)
go
exec sp_helpfile
go

11、删除文件

--先删除数据文件
alter database school
remove file linshi2
go
--再删除文件组
alter database school
remove filegroup linshi1
go
--最后删除日志文件
alter database school
remove file school_log4
go
exec sp_helpfile
go

12、查看数据库文件组的属性

1 exec sp_helpfilegroup
2 go

13、卸载附加数据库

--卸载数据库
use master
go
execute sp_detach_db  'Demo'
go
--附加数据库
use master
go
exec sp_attach_db 'Demo',
'D:/SQL  2005/DATABASE/DATA/demo.mdf'
go

14、查看系统表

--查看master数据库中存储数据库的系统表
use master
go
select * from sysdatabases
go
--查看数据库school中存储文件组的系统表
use school
go
--select * from sys.filegroups
--go
--查看存储数据文件信息的系统表
select * from sys.database_files
go

15、理解sysobjects系统视图

--1、表中没有存储信息的情况
--在数据库中增加其他文件组
use demo
go
/*alter database demo
add filegroup test
go
--在文件组中增加一个数据文件
alter database demo
add  file
(
  name=test1,
  filename='D:/SQL  2005/DATABASE/DATA/test1.ndf',
  size=10,
  maxsize=20,
  filegrowth=1
)
to  filegroup test
go
--建表
create table worker
(
  id  int,
  [name]  char(20)
)
on  test
go
--删除test1数据文件
alter database demo
remove file test1
go
select * from  worker
go*/
/*当删除数据文件test1后，这个表在数据库中仍然存在，这是因为表的名称存储在sysobjects中，
表的列的定义则存储在sys.columns中，这些系统视图实际上都存储在主文件组内*/
 
--2、表中有存储信息的情况
-- 增加一个数据文件
 
 alter database demo
 add  file 
(
  name=test2,
  filename='D:/SQL  2005/DATABASE/DATA/test2.ndf',
  size=10,
  maxsize=20,
  filegrowth=1
)
to  filegroup test
go
insert into worker
(id ,[name])
values(200601,'Alice')
go
insert into worker
(id ,[name])
values(200602,'Alfred')
go
--删除test2
alter database demo
remove file  test2
go
 
/*当表中有数据以后数据文件就不能够删除,因为数据文件中的内容已经不为空.
  因此我们可以看出表中的真实数据书存储在数据文件中,表的定义则是存储在主文件组中的*/

16、修改表明，表列名

use  school
go
--查看表的属性
exec  sp_help student
go
 
--修改表名称
exec  sp_rename  'student','students'
go
--修改表列的名称
exec  sp_rename  'student.name','学生名'
go

17、修改表结构

use demo
go
--修改列
alter table worker
alter column [name]  char(30)
go
--添加列
alter table worker
add city varchar(10)
go
--select * from worker
--删除列
alter  table  worker
drop column  city
go

18、修改表的数据类型

alter table worker
alter column  id  char(10)
go
insert  into  worker
(id,[name])
values
('200603a','Tom'   )
go
--修改表的数据类型
alter  table  worker
alter  column  id  int
go
/*从上面的列子我们可以看出当字段中存储了字符串数据类型的数值后，此字段就无法转换成数值类型，
  能够将字符串类型的列转换成为数字类型，只能是在此列存储的数值都是数字的情况下*/
--将列变为not null
alter  table worker
alter  column  id  int  not  null
go

列子：

use  school
go
alter  table  学生
add    毕业时间   char(20)
go
--将学生表中毕业日期列数值补全
update  学生
set  毕业时间='2005年毕业'
where  学生编号=2006001
go
update  学生
set  毕业时间='尚未毕业'
where 学生编号=2006002
go

19、添加约束

use  school
go
--在家长表添加带约束的列
alter  table parents
add  salary/*工资*/ money  check (salary>0)
go
--修改家长表中薪水列
update parents
set  salary=100000
where  id=2001
go
 
update  parents
set  salary=-200000
where  id=2002
go
--为学生表添加限制生源地的约束
alter table  学生
add  city  char(10)
go
alter  table 学生
add constraint ck_学生_city  check
(city  in('北京','天津','上海','广州')) 
go
--修改学生表中的信息
update 学生
set  city='北京'
where  学生编号=2006001
go
update 学生
set  city='昆明'
where  学生编号=2006002
go
--两个相互矛盾的CHECK约束
alter  table teachers
add  salary money
go
--约束：限制其薪水>7000
alter  table teachers
add  constraint/*强制*/ ck_teachers_salary_1  check(salary>7000)
go
-- 约束：限制其薪水<6000 
alter  table teachers
add  constraint/*强制*/ ck_teachers_salary_2  check(salary<6000)
go
--更改老师编号为101的薪水
update  teachers
set  salary=4000
where id=101
go
 
update  teachers
set  salary=8000
where id=102
go
/*从中可以看出：SQL  SERVER上不会校验出约束的限制是否正确。约束的作用是叠加的，如果一个列中有多个约束，则
录入的数值就要满足所在列上的所有约束。*/
--DEFAULT约束
--为student表city列定一个默认值约束
alter  table student
add  constraint df_student_city
default('北京') for city
go
--录入记录
insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city)
values
(2006001,'Alice','女',23,1,1,101,2001,'2005年毕业','北京')
go
insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city)
values
(2006002,'Rose','女',24,1,2,102,2001,'尚未毕业','天津')
go
insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city)
values
(2006008,'MR.wang','男',24,1,1,101,2003,'2006年毕业',default)
go
/*一个DEFAULT只能约束一列，不能用于TIMESTAMP，IDENTITY的列*/
--select  * from student
--UNIQUE约束
--在student表中添加cardno列
alter table  student
add  cardno  char(20)
go
--添加身份证号
update student
set cardno='220200030304304033'
where id=2006001
go
update student
set cardno='220200030304304034'
where id=2006002
go
update student
set cardno='220200030304304035'
where id=2006008
go
-- 添加唯一约束
alter table  student
add constraint uk_student_cardno  unique(cardno)
go
insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006009,'Tank','男',25,2,2,103,2004,'2006年毕业','上海','220200030304304033')
go
--primary key和foreign key约束
--创建图书和作者表
create table author/*作者*/
(
  id  int,
  [name]  char(20)
)
go
create table titles
(
  title_no  char(10),
  title_name  char(10),
  author_id  int
)
go
--主键约束
alter table  author
alter  column  id  int  not  null
go
alter table author
add  constraint pk_author_id  primary key(id)
go
--外键约束
alter  table titles
add  constraint fk_titles_author_id  foreign key(author_id)
references  author(id)
go
--========================================================
/*禁用/启用约束*/
--查看约束信息
use  school
go
exec  sp_helpconstraint  student
go
--禁用city列上的约束
alter  table  student
nocheck  constraint ck_student_city
go
--录入一条来自新疆学生的信息
insert  into  student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006009,'Kook','男',20,1,3,102,2004,'尚未毕业','新疆','220200030304304040')
go
--启用city列上的约束
alter  table  student
check  constraint  ck_student_city
go
--录入一条来自新疆学生的信息
insert  into  student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006010,'Took','男',22,1,2,101,2001,'尚未毕业','新疆','220200030304304040')
go
/*对表原始数据不校验添加约束*/
--不校验原始数据的情况下添加约束
alter table  teachers
with  nocheck add  constraint
ck_teacher_teaching_age  check(teaching_age>5)
go
-- 录入教学年龄为6年的教师
insert into  teachers
(id,[name],gender,age,subject,teaching_age,position,salary)
values
(105,'张月','女','28','语文',6,'初级教师',null)
go
-- 录入教学年龄为4年的教师
insert into  teachers
(id,[name],gender,age,subject,teaching_age,position,salary)
values
(106,'张凡','男','28','计算机',4,'初级教师',null)
go
/*CHECK为添加约束时的默认选项,代表的是对原始数据进行校验,NOCHECK代表的是添加新的约束时,对原始数据
不进行校验*/
/*使用级联CASCADE*/
--删除ID为101的老师的信息
delete from  teachers
where  id=101
go
--修改ID为101的老师的信息
update teachers
set  id=110  where  id=101
go
/*通过上面的实验可以看出，编号为101的老师信息，由于被学生表所引用，所以无论删除或修改这个关键
字都会报错，因为违反了信息的完整性。*/
/*为了在更改老师表编号列时,实现在学生表中同步更改老师编号,需要重新维护学生表中的外键约束*/
--删除学生表上与老师表关联的外键约束
alter table  student
drop  constraint fk_student_tescher_id_teacher_id
go
exec  sp_helpconstraint student
go
--在学生表上重新创建带级联关系的外键约束
alter  table  student
add  constraint  fk_student_teacher_id_teacher_id
foreign  key(teacher_id)  references  teachers(id)
on  delete  cascade
go
--;删除老师101的信息
delete  from  teachers
where  id=101
go
select  * from  student
go
select  *  from  teachers
go
/*从上面可以看出,和101有关的信息都被删除了*/
/*创建/使用规则*/
--创建年龄规则
create  rule age_rule
as  @age>0
go
--将规则与学生表列绑定
exec sp_bindrule  age_rule,'student.age'
go
--录入一个学生的信息/
insert into  student
(id,[name],gender,age,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006011,'小明','男',-9,2,103,2004,'尚未毕业','北京',220200030304304342)
go
--将规则从列上解除绑定/删除规则
drop  rule  age_rule
go
/*可以看到运行出错*/
--解除帮定
exec  sp_unbindrule  'student.age'
go
--录入一个学生的信息/
insert into  student
(id,[name],gender,age,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006011,'小海','男',-9,2,103,2004,'尚未毕业','北京',220200030304304342)
go
--删除规则
drop  rule  age_rule
go
/*可以看出,要删除,必须先解除*/
/*创建使用DEFAULT对象*/
--创建默认值对象
create default df_age
as  23
go
--绑定
exec  sp_bindefault  df_age,'student.age'
go
--录入一个学生的信息
insert into  student
(id,[name],gender,age,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006012,'小刚','男',default,2,102,2001,'尚未毕业','天津',220200030304304343)
go
--解除绑定
exec  sp_unbindefault 'student.age'
go
--删除DEFAULT对象
drop  default  df_age
go

20、创建、使用、查询视图

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

--创建名称为v_student_teacher的视图
create  view  v_student_teachers
as
select  s.id  as  'student_id',
s.[name]  as  'student_name',
s.gender  as  'student_gender',
s.age  as  'student_age',
s.grade,
s.class,
s.teacher_id  as  's_teacher_id',
s.parents_id,
s.graduation,
s.city,
s.cardno,
t.id  as  'teacher_id',
t.[name]  as  'teacher_name',
t.gender  as  'teacher_gender',
t.age  as  'teacher_age',
t.subject,
t.teaching_age,
t.position,
t.salary
from  student  as  s
inner  join
teachers  as  t 
on  s.teacher_id=t.id
go
--使用视图
--select  *  from  v_student_teachers
--go
/*创建视图：定义视图的查询不可以包含ORDER  BY、COMPUTE 或 COMPUTE BY句子或INTO关键字。*/
--查询视图
select  *  from  v_student_teachers
go
--创建视图
create  view  v_student  as
select  *  from  student
go
--访问视图
select  * from v_student
go<br>/*对表student进行INSERT,UPDATE操作*/
select * from student
go
select * from v_student
go
--对id为2006011的学生信息进行UPATE更新操作
update   student
set  city='大连'  where  id='2006011'
go
select  * from  student
go
select * from v_student
go
 
/*对表student进行INSERT,UPDATE操作*/
select * from student
go
select * from v_student
go<br>--进行INSERT插入操作
insert  into  student(id,[name],gender,age,graduation)
values(2006014,'小萌','男','23','已经毕业')
go<br>--对id为2006011的学生信息进行UPATE更新操作
update   student
set  city='天津'  where  id='2006011'
go
select  * from  student
go
select * from v_student
go
--对id为2006013的学生信息进行delete删除操作
delete from student
where id='2006013'
go
select * from student
go
select * from v_student
go
--查询学生表与学生老师信息视图
select id,[name],age 
from  student
go
select student_id,student_name,student_age
from v_student_teachers
go <br>--通过视图student_teacher向表student中插入一条数据
insert into v_student_teachers
(student_id,student_name,student_age,graduation)
values
('2006013','小萌','24','已毕业')
go
select id,[name],age
from student
go
select student_id,student_name,student_age
from v_student_teachers*/
--where student_id='2006013'
--通过视图更改数据
select id,[name],age
from student
go
select student_id,student_name,student_age
from v_student_teachers
go
--通过视图student_teacher更改表student中,学生"小萌"的年龄
update v_student_teachers
set student_age='23'
where student_name='小萌'
go
select id,[name],age
from student
go
select student_id,student_name,student_age
from v_student_teachers
go<br>--通过视图删除数据
delete  v_student_teachers
where student_name='小萌'
go
/*说明当一个视图由两个或两个 以上基表构成时，不允许通过视图删除基表中的数据，
 即只能通过单基表视图删除数据*/
 
--分区视图
/*创建三个数据库，并分别在三个数据库上建立表*/
create database jan1998
on primary
(
  name=jan1998_data,
  filename='D:/SQL  2005/DATABASE/DATA/jan1998_data.mdf',
  size=10,
  maxsize=50,
  filegrowth=1
)
log on
(
  name=jan1998_log,
  filename='D:/SQL  2005/DATABASE/Log/jan1998_log.ldf',
  size=10,
  maxsize=15,
  filegrowth=1
)
go
create database feb1998
on primary
(
  name=feb998_data,
  filename='D:/SQL  2005/DATABASE/DATA/feb1998_data.mdf',
  size=10,
  maxsize=50,
  filegrowth=1
)
log on
(
  name=feb1998_log,
  filename='D:/SQL  2005/DATABASE/Log/feb1998_log.ldf',
  size=10,
  maxsize=15,
  filegrowth=1
)
go
create database mar1998
on primary
(
  name=mar1998_data,
  filename='D:/SQL  2005/DATABASE/DATA/mar1998_data.mdf',
  size=10,
  maxsize=50,
  filegrowth=1
)
log on
(
  name=mar1998_log,
  filename='D:/SQL  2005/DATABASE/Log/mar1998_log.ldf',
  size=10,
  maxsize=15,
  filegrowth=1
)
go
--建立1~3月份的销售表
use  jan1998
go
create table jan1998sales
(
  orderid int,
  customerid  int  not null,
  orderdate  datetime null,
    check(datepart(yy,orderdate)=1998),
  ordermonth int
    check(ordermonth=1),
  deliverydate  datetime  null,
    check(datepart(mm,deliverydate)=1),
  constraint OrderIDMonth primary  key(orderid,ordermonth) 
)
go
use feb1998
go
create table feb1998sales
(
  orderid  int,
  customerid  int  not null,
  orderdate  datetime null,
    check(datepart(yy,orderdate)=1998),
  ordermonth int
    check(ordermonth=2),
  deliverydate  datetime  null,
    check(datepart(mm,deliverydate)=2),
  constraint OrderIDMonth primary  key(orderid,ordermonth)
)
go
use mar1998
go
create table mar1998sales
(
  orderid  int,
  customerid  int  not null,
  orderdate  datetime null,
    check(datepart(yy,orderdate)=1998),
  ordermonth int
    check(ordermonth=3),
  deliverydate  datetime  null,
    check(datepart(mm,deliverydate)=3),
  constraint OrderIDMonth primary  key(orderid,ordermonth)
)
go<br>--插入数据记录
use jan1998
go
insert  into  jan1998sales
values(1,101,convert(datetime,'01/15/1998',101),'1',convert(datetime,'01/15/1998'))
go
insert  into  jan1998sales
values(2,101,convert(datetime,'01/11/1998',101),'1',convert(datetime,'01/17/1998'))
go
use  feb1998
go
insert  into  feb1998sales
values(3,103,convert(datetime,'02/21/1998',101),'2',convert(datetime,'02/11/1998'))
go
use  mar1998
go
insert  into  mar1998sales
values(6,108,convert(datetime,'03/07/1998',101),'3',convert(datetime,'03/13/1998'))
go
insert  into  mar1998sales
values(7,108,convert(datetime,'03/09/1998',101),'3',convert(datetime,'03/13/1998'))
go
insert  into  mar1998sales
values(8,108,convert(datetime,'03/10/1998',101),'3',convert(datetime,'03/13/1998'))
go
use jan1998
go
select * from jan1998sales
go
use feb1998
go
select * from feb1998sales
go
use mar1998
go
select * from mar1998sales
go<br>/*建立1~3月份的分区视图*/
use jan1998/*怎么用多个数据库？？*/<br>go
create view  year1998sales
as
select * from jan1998sales
union all
select * from feb1998sales
union all
select * from mar1998sales
go
/*查询1~3月份的分区视图*/
use jan1988/*怎么用多个数据库？？*/<br>go
--建立索引视图基表,将ANSI_NULLS设置为ON
use  school
go
select * from year1998sales
where ordermonth  in (1,3)
go
set ansi_nulls on
create table student_idx
(
 id  int not null primary key,
 [name] char(30) ,
 age char(10)
)
go<br>--向基表插入数据
insert into student_idx
values
(2006012,'小辉','27')
go<br>--建立索引视图将ANSI_NULLS,QUOTED_IDENTIFIER设置为ON
set ansi_nulls on
go
set quoted_identifier on
go
create view  dbo.v_student_idx
with schemabinding /*架构绑定将视图绑定到基础基表的架构*/
as
select s.id  as id,s.name  as [name],s.age  as age
from  dbo.student_idx  as  s
go<br>/*在视图建立索引,将ANSI_NULLS,ANSI_PADDING,ANSI_WARNING,CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER设置为ON,
将NUMERIC_ROUNDABORT设置为OFF*/
set ansi_nulls,ansi_padding,ansi_warnings,concat_null_yields_null,quoted_identifier on;
set numeric_roundabort off
create unique clustered  index idx_v_student_idx
on v_student_idx(id)
go
/*修改/删除视图*/
--修改视图v_student_teachers
alter view v_student_teachers
as
select s.id as 'student_id',
s.[name] as 'student_name',
s.gender as 'student_gender',
s.age as 'student_age',
s.grade as 'student_grade',
s.class,
s.teacher_id as 's_teacher_id',
s.parents_id,
s.graduation,
s.city,
s.cardno,
t.id as 'teacher_id',
t.[name] as 'teacher_name',
t.gender as 'teacher_gender',
t.age as 'teacher_age',
t.subject,
t.teaching_age,
t.position,
t.salary
from student as s
inner join
teachers  as t  on s.teacher_id=t.id
go
/*通过上面的操作,视图就被修改为代码包含SELECT语句结构*/
--重命名视图
use school
  go
  exec sp_rename  'v_student_teachers' , 'student_teachers'
  go
/*新名字就是student_teachers*/
--删除视图
--drop view v_student_teachers
--go
/*视图与DEFAULT的使用*/
--创建一个具有DEFAULT属性的表
create table student_default
(
 id  int,
 [name] char(20),
 gender char(2),
 age char(10),
 grade int default 1 /*使用DEFAULT属性创建的列*/
)
go
--插入数据
INSERT into student_default
values (1,'小刚','男','20',2)
--查询
select * from student_default
go
--创建以具有DEFAULT属性列的表为基表的视图
create view v_student_default
as
select * from student_default
go
--通过视图向基表中插入数据,但DEFAULT列不进行插入数值
insert into v_student_default(id,[name],gender,age)
values(1,'小萌','男','22')
go
--查询
select * from student_default
go
select * from v_student_default
go