|
创建数据库TestTask，student表，teacher表，course表，score表 create database TestTask go use TestTask go create table student ( Sno varchar(20) not null primary key ,--学号 Sname varchar(20) not null,--学生姓名 Ssex varchar(20) not null, --学生性别 Sbirthday datetime,--学生出生年月 Class varchar(20)--学生所在班级 ) insert into student values('108','曾华','男','1977-09-01','95033') insert into student values('105','匡明','男','1975-10-02','95031') insert into student values('107','王丽','女','1976-01-23','95033') insert into student values('101','李军','男','1976-02-20','95033') insert into student values('109','王芳','女','1975-02-10','95031') insert into student values('103','陆君','男','1974-06-03','95031') select * from student go create table teacher--老师 ( Tno varchar(20) not null primary key ,--教工编号（主码） Tname varchar(20) not null,--教工姓名 Tsex varchar(20) not null, --教工性别 Tbirthday datetime,--教工出生年月 Prof varchar(20),--职称 Depart varchar(20) not null--教工所在部门 ) insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系') insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系') insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系') insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系') select * from teacher go create table Course--课程 ( Cno varchar(20) not null primary key ,--课程号 Cname varchar(20) not null,--课程名称 Tno varchar(20) not null references teacher(Tno), --教工编号（外码） ) insert into course values('3-105','计算机导论','825') insert into course values('3-245','操作系统','804') insert into course values('6-166','数字电路','856') insert into course values('9-888','高等数学','831') select * from Course go create table Score--分数 ( Sno varchar(20) not null references student(Sno), --学号（外码） Cno varchar(20) not null references Course(Cno), --课程号（外码） primary key(Sno,Cno), Degree Decimal(4,1),--成绩 ) insert into score values('103','3-245','86') insert into score values('105','3-245','75') insert into score values('109','3-245','68') insert into score values('103','3-105','92') insert into score values('105','3-105','88') insert into score values('109','3-105','76') insert into score values('101','3-105','64') insert into score values('107','3-105','91') insert into score values('108','3-105','78') insert into score values('101','6-166','85') insert into score values('107','6-166','79') insert into score values('108','6-166','81') select * from Score 运行结果：
|
|
|
