创建数据库和表的实验代码
1、创建数据库：
create database StuInfo
on primary
(
name=StuInfo,
filename='E:\StuInfo.mdf',
size=3mb,
maxsize=10mb,
filegrowth=1mb
)
log on
(
name=StuInfo_log,
filename='E:\StuInfo_log.ldf',
size=1mb,
maxsize=5mb,
filegrowth=1mb
)
2、创建表
create table T_student
(
s_number char(8)not null,
s_name char(10)not null,
sex char(2),
birthday datetime,
nation nvarchar(10),
politics varchar(10),
department nvarchar(12),
address varchar(60),
postalcode nvarchar(10),
phone varchar(24),
constraint pk_s primary key(s_number), constraint a check(sex='男'or sex='女') )
create table T_course
(
c_number char(4)not null,
c_name char(10)not null,
teacher char(10),
hours int not null,
credit int,
typed nvarchar(12),
constraint pk_c primary key(c_number),
)
create table T_score
(
s_number char(8)not null,
c_number char(4)not null,
score numeric(5,1)
constraint pk_t primary key(s_number,c_number),
constraint b foreign key(s_number)references T_student(s_number), constraint c foreign key(c_number)references T_course(c_number), constraint d check(score between 0 and 100)
)
CREATE TABLE t_score(
S_number char(8) NOT NULL REFERENCES t_student (S_number),
C_number char(4) NOT NULL REFERENCES t_course (C_number),
Score numeric(5, 1) CHECK(Score>=0 and Score<=100),
CONSTRAINT PK_t_score PRIMARY KEY (S_number ,C_number) )
insert into T_student
select'040101','刘致明','男','1985-5-8','汉','党员','工商','北京市崇文区','100018','010********'
union all
select'040102','李宏','男','1984-12-8','汉','群众','工商','西安北大街','710001',''
union all
select'040103','黄芳芳','女','1986-3-16','壮','团员','工商','南宁市大学路','530010','0771*******'
union all
select'040201','田莉莉','女','1985-10-12','回','团员','计算机','西宁市顺安路','600012','138********'
union all
select'040202','王毅','男','1986-3-8','汉','党员','计算机','乌鲁木齐市民主路','','159********'
union all
select'040203','李建国','男','1984-8-23','汉','党员','计算机','广州市西关
路','530002','022********'
union all
select'040204','刘涛','男','1985-10-1','汉','团员','计算机','河南省新乡市','320056','0311*******'
union all
select'040205','郝露','女','1985-8-8','壮','群众','计算机','贵州省遵义市','','07212455487'
union all
select'040301','吴杭','男','1985-7-10','汉','党员','机械
','','','02658369456'
union all
select'040302','李进','男','1985-10-11','汉','团员','机械','河北省石家庄市','110020',''
union all
select'050201','黄家昵','男','1987-5-6','','','计算机
','','','139********'
go
insert into T_course
select'1','SQL Server','蒋宾',72,6,'专业核心课'
union all
select'2','数据库原理','李朝阳',68,5,'专业核心课'
union all
select'3','VB','叶之文',72,6,'专业基础课'
union all
select'4','计算机文化','张思竹',80,7,'公共基础课'
union all
select'5','电子商务','蒋燕',76,6,'专业核心课'
union all
select'6','会计','朱明',80,7,'专业基础课'
union all
select'7','财务软件','王海',72,6,'专业核心课'
union all
select'8','机械制造','黄科美',68,4,'专业基础课'
union all
select'9','photoshop','陈琳',68,5,'专业基础课'
union all
select'10','计算机组装','袁伟',68,4,'专业基础课'
insert into T_score
select'040101','5',92
union all
select'040101','6',85
union all
select'040101','7',88
union all
select'040102','5',48
union all
select'040102','6',60
union all
select'040102','7',52
union all
select'040201','3',90
union all
select'040301','8',75
union all
select'040302','4',55
union all
select'040302','8',66
union all
select'040302','5',92
union all
select'050201','10',91
insert into T_score
values('040104','2',70)
INSERT INTO t_score
VALUES('040102','2',75)
update t_student
set s_number='040105'
where S_number='040101'
delete t_course
where C_number='5'
首先删除T_Score表中C_Number=’5’的所有记录，然后再删除表T_Course中C_Number为5的记录
alter table T_student
ADD CONSTRAINT f CHECK(politics='中共党员'or politics='团员'or politics='民主党派'or politics='群众')
go
ALTER TABLE T_student
ADD CONSTRAINT r DEFAULT('团员')FOR politics