学生表、教师表以及上课表的建立
一、数据库建立
1.创建teaching数据库
2.利用SQL Server Management Studio创建表student,表结构为
create table student(studentno nchar(11) not null primary key,sname nchar(8) null,sex nchar(2) null default '男' check(sex='男' or sex='女'),birthday datetime null,classno nchar(6) null,point smallint null,phone nchar(12) null,email nvarchar(20) null)
3.利用Transact-SQL语句在数据库teaching中创建表如下:
(1)课程表
create table course(courseno nchar(6) not null ,cname nchar(20) null,type nchar(8) null,period tinyint null,credit numeric(4,1) null,constraint pk_course primary key(courseno))
(2)分数表
create table score(studentno nchar(11) not null ,courseno nchar(6) not null,usually numeric(6,2) null,final numeric(6,2) null,constraint pk_score primary key(studentno,courseno))
(3)教师表
create table teacher(teachno nchar(6) not null primary key,tname nchar(8) null ,major nchar(10) null, prof nchar(10) null,department nchar(12) null)
(4)班级表
create table class(classno nchar(7) not null primary key,classname nchar(12) null,department nchar(12) null,monitor nchar(8) null)
(5)教课表
create table teach_class(teacherno nchar(6) not null ,classno nchar(7) not null,courseno nchar(6) not null,constraint pk_teach_class primary key(teacherno,classno,courseno))
4.为表输入数据。
执行命令:
use teaching
insert into student(studentno,sname,sex,birthday,classno,point,phone,email)values('16122210009','许海兵','男','1996/11/05','160501','789','13623456778','qwe@163.com...')
insert into student(studentno,sname,sex,birthday,classno,point,phone,email)values('16122221324','何影', '女','1997/12/04','160501','879','13178978999','aaa@sina.com...')
insert into student(studentno,sname,sex,birthday,classno,point,phone,email)values('16125111109','敬秉承','男','1998/03/01','160801','789','15678945623','jing@sina.com...')
insert into student(studentno,sname,sex,birthday,classno,point,phone,email)values('16125121107','梁欣', '女','1996/09/03','160502','777','13145678921','bing@126.com...')
insert into student(studentno,sname,sex,birthday,classno,point,phone,email)values('16135222201','夏文斐','女','1997/10/06','160502','867','15978945645','tang@163.com...')
insert into student(studentno,sname,sex,birthday,classno,point