期中考试数据库上机复习提纲.doc

实验1 第四章 1.创建数据库 create database sales on primary ( name=sale, filename=c:\data\sale.mdf, size=4, maxsize=10, filegrowth=5%), ( name=sale1, filename=c:\data\sale1.ndf, size=3, maxsize=10, filegrowth=1), filegroup sale_1 ( name=sale2, filename=c:\data\sale_2.ndf, size=3, maxsize=10, filegrowth=1) log on ( name=sale_log, filename=c:\file\sale.ldf, size=3, maxsize=10, filegrowth=1) 2.对数据库的修改 (1)更改数据库名称 alter database sales modify name=newsale (2)向数据库添加文件 alter database newsale add file ( name=sale3, filename=c:\data\sale3.mdf, size=4, maxsize=10, filegrowth=5% ) (3)添加文件组 alter database newsale add filegroup sele_2 (4)添加日志文件 alter database newsale add log file 删除文件 alter database newsale remove file sale2 实验2 第五章和第八章 1创建数据库new create database new 2创建学生表student(sno,sname,sex,age,dept,phone) sno主键，sname惟一，sex默认男，年龄17-22之间，dept只能是“计算机、电子、管理”，phone五位数字score(sno,cno,degree) 要求：sno外键，级联删除，级联修改；sno和cno主键，degree不能为负数use new create table student ( sno int primary key, sname char(10) unique, sex char(2) default 男, age int constraint cehck_age check(age between 17 and 22), dept char(20) check ( dept in(计算机,电子,管理)), phone char(5) check ( phone like [0-9][0-9][0-9][0-9][0-9]) ) create table score ( sno int foreign key (sno) references student(sno) on delete cascade on update cascade, cno char(20) constraint g_key primary key(sno,cno), degree int check ( degree0) ) 3 插入相应的值（主要查看默认值的输入） insert new.dbo.student values (1,a,,20,电子,12345) insert new.dbo.student values (2,b,女,21,计算机,22345) insert new.dbo.student values (3,v,女,18,管理,22345) insert new.dbo.score values (1,数学,96) insert new.dbo.score values (1,语文,97) insert new.dbo.score values (2,英语,93) insert new.dbo.score values (3,数学,94) 4 插入违反约束的值，查看提示信息 insert new.dbo.score values (4,数学,96) insert new.dbo.student values (1,s,,20,电子,12345) insert new.dbo.student