代码创建完整数据库系统，创建数据库、创建模式、输入数据、注释全面，易懂

##本人根据所学题目所编写，在SQL2012上完美运行，其他SQL也可以兼容
/使用时将带有##号的标题去掉

/ 创建数据库

create /创建数据库/
database jwglxt --创建主文件
on
primary(
name = jwglxt_data,
filename = ‘e:\SQL\jwglxt\jwglxt.mdf’,
size = 10mb,
maxsize = unlimited,
filegrowth = 10%
)

log --创建日志文件
on(
name = jwglxt_log,
filename = ‘e:\SQL\jwglxt\jwglxt.ldf’,
size = 1mb,
maxsize = 5mb,
filegrowth = 1mb
)

创建模式

/创建模式/
use
jwglxt
go
create schema school --authorization
go

创建表

先创建班级信息表然后再创建学生信息表Student
–（1）创建班级信息表Class
if exists(
select name from sys.tables
where name = ‘Class’
)
drop table Class
go
–删去之后然后重新创建表Class
create table Class(
Clno char(6) primary key, --班级编号
Clname varchar(20) not null, --班级名称
Special varchar(20), --专业
Dno char(8) --院系编号
)
go

–（2）创建学生信息表Student
if exists( /防止重复执行时提示错误/ --判断系统表中是否存在Student的表，如果存在删去
select name from sys.tables
where name = ‘Student’
)
drop table Student --(删除默认为restrict)
go
–删去之后然后重新创建表Student
create table Student( --学生信息表Student
Sno char(8) primary key, --学号
Clno char(6) not null, --班级编号
Sname char(8) not null , --姓名
Ssex char(2) check(Ssex in(‘男’,‘女’)), --性别
Sbir datetime, --出生日期
Sage smallint, --年龄
foreign key(Clno)references Class(Clno), --表级完整性约束条件，Clno是外码，被参照是class
constraint ck_Sage check(Sage >= 0 and Sage <= 200) --出生日期约束在0-200
)
go

–（3）课程信息表Course
create table Course(
Cno char(5) primary key, --课程编号
Cname varchar(20) not null, --课程名称
Credits Numeric(3,1) --学分
)

–（4）选修课程情况表SC
create table SC(
Sno char(8), --学号
Cno char(5), --课程号
Score Numeric(5,1), --成绩
foreign key(Sno) references Student(Sno), --表级完整性约束条件，Sno是外码，被参照是class
foreign key (Cno) references Course(Cno)
)
go

–(5)老师信息表Teacher
create table Teacher(
Tno char(8) primary key, --老师编号
Tname char(8) not null, --老师姓名
Tsex char(2) check(Tsex in(‘男’,‘女’)), --性别
Tbir datetime, --出生日期
Ttitle char(10), --职称
Dno char(8), --院系编号
Tjbgz money, --基本工资
Tgwjt money, --岗位津贴
Sfgz money, --实发工资
)

–(6)教师任课情况表Teaching
create table Teaching(
Tno char(8), --教师编号
Cno char(5), --课程号
foreign key(Tno) references Teacher(Tno), --Tno的外键，参照Teacher的Tno
foreign key (Cno) references Course(Cno)
)
go

–(7)院系情况表Department
create table Department(
Dno char(8) Primary key, --院系编号
Dname char(20) --院系名称
)

---

在表中输入信息

/向表中输入信息/

–（1）输入班级信息表Class
insert
into Class(Clno,Clname,Special,Dno)
values(‘200701’,‘07网络’,‘计算机网络技术’,‘0001’);

insert
into Class(Clno,Clname,Special,Dno)
values(‘200702’,‘08电信’,‘电子信息工程’,‘0002’);

insert
into Class(Clno,Clname,Special,Dno)
values(‘200801’,‘08软件’,‘计算机软件技术’,‘0001’);

insert
into Class(Clno,Clname,Special,Dno)
values(‘200802’,‘08网络’,‘计算机网络技术’,‘0001’);

insert
into Class(Clno,Clname,Special,Dno)
values(‘200901’,‘09通信’,‘通信工程’,‘0002’);

INSERT
INTO Class(Clno,Clname,Special,Dno)
values(‘201001’,‘10计技’,‘计算机科学与技术’,‘0001’);

–（2）输入学生信息表Student
insert
into Student–(Sno,Clno,Sname,Ssex,Sbir,Sage)
values(‘20070101’,‘200701’,‘关鹏’,‘男’,‘1986-10-10 00:00:00’,‘25’);

insert
into Student(Sno,Clno,Sname,Ssex,Sbir,Sage)
values(‘20070102’,‘200701’,‘王红’,‘女’,‘1988-12-12 00:00:00’,‘23’);

insert
into Student(Sno,Clno,Sname,Ssex,Sbir,Sage)
values(‘20070103’,‘200701’,‘赵丽丽’,‘女’,‘1989-11-02 00:00:00’,‘28’);

insert
into Student(Sno,Clno,Sname,Ssex,Sbir,Sage)
values(‘20080101’,‘200801’,‘赵良民’,‘男’,‘1985-12-25 00:00:00’,‘26’);

insert
into Student(Sno,Clno,Sname,Ssex,Sbir,Sage)
values(‘20080102’,‘200801’,‘覃天’,‘男’,‘1983-10-09 00:00:00’,‘28’);

insert
into Student(Sno,Clno,Sname,Ssex,Sbir,Sage)
values(‘20080103’,‘200801’,‘张维明’,‘女’,‘1984-02-12 00:00:00’,‘27’);

insert
into Student(Sno,Clno,Sname,Ssex,Sbir,Sage)
values(‘20080104’,‘200801’,‘王一夫’,‘男’,‘1987-04-05 00:00:00’,‘24’);

insert
into Student
values(‘20080201’,‘200802’,‘罗东明’,‘男’,‘1982-01-10 00:00:00’,‘29’);

insert
into Student
values(‘20080202’,‘200802’,‘莫芳芳’,‘女’,‘1984-08-09 00:00:00’,‘27’);

insert
into Student
values (‘20080203’,‘200802’,‘李静’,‘女’,‘1984-10-10 00:00:00’,‘27’);

insert
into Student
values (‘20090101’,‘200901’,‘黄威’,‘男’,‘1991-02-05 00:00:00’,‘20’);

insert
into Student
values(‘20090102’,‘200901’,‘潘峰’,‘男’,‘1998-12-06 00:00:00’,‘19’);

insert
into Student
values(‘20090103’,‘200901’,‘丁乙’,‘女’,‘1993-01-01 00:00:00’,‘18’);

insert
into Student(Sno,Clno,Sname,Ssex,Sbir,Sage)
values(‘20100101’,‘201001’,‘丙丁’,‘男’,‘1994-6-28 00:00:00’,‘20’);

insert
into Student
values(‘20100102’,‘201001’,‘方蓉’,‘女’,‘1992-8-20 00:00:00’,‘19’);

---

–（3）输入课程信息表
insert
into Course(Cno,Cname,Credits)
values(‘01001’,‘计算机文件基础’,‘5.0’);

insert
into Course(Cno,Cname,Credits)
values(‘01002’,‘高等数学’,‘4.0’);

insert
into Course(Cno,Cname,Credits)
values(‘01003’,‘大学物理’,‘3.0’);

insert
into Course
values(‘02001’,‘C语言程序设计’,‘4.0’);

insert
into Course
values(‘02002’,‘数据库原理及应用’,‘5.0’);

insert
into Course
values(‘02003’,‘计算机网络’,‘4.0’);

insert
into Course
values(‘03001’,‘大学英语’,‘6.0’);

insert
into Course
values(‘03002’,‘DB%Design’,‘2.0’);

insert
into Course
values(‘03003’,‘DBADesign’,‘1.0’);

---

–（4）选修课程情况表SC
insert
into SC(Sno,Cno,Score)
values(‘20070101’,‘01001’,‘78.0’);

insert
into SC(Sno,Cno,Score)
values(‘20070102’,‘02002’,‘85.0’);

insert
into SC(Sno,Cno,Score)
values(‘20070103’,‘01001’,‘96.0’);

insert
into SC(Sno,Cno,Score)
values(‘20080101’,‘01001’,‘91.0’);

insert
into SC(Sno,Cno,Score)
values(‘20080101’,‘01002’,‘86.0’);

insert
into SC(Sno,Cno,Score)
values(‘20080101’,‘01003’,‘89.0’);

insert
into SC(Sno,Cno,Score)
values(‘20080101’,‘02001’,‘86.0’);

insert
into SC(Sno,Cno,Score)
values(‘20080101’,‘02002’,‘56.0’);

insert
into SC
values(‘20080101’,‘02003’,‘50.0’);

insert
into SC
values(‘20080102’,‘01001’,‘65.0’);

insert
into SC
values(‘20080102’,‘01002’,‘80.0’);

insert
into SC(Sno,Cno,Score)
values(‘20080201’,‘02003’,null); --将这个同学的这门课程的成绩置空

---

–（5）老师信息表Teacher
insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘01001’,‘覃明生’,‘男’,‘1970-01-10 00:00:00’,‘教授’,‘0001’,‘1200’,‘890’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘01002’,‘李闲’,‘女’,‘1968-05-15 00:00:00’,‘副教授’,‘0001’,‘1300’,‘780’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘01003’,‘赵尚志’,‘男’,‘1959-10-01 00:00:00’,‘教授’,‘0001’,‘1500’,‘1100’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘01004’,‘李向阳’,‘女’,‘1972-08-23 00:00:00’,‘副教授’,‘0001’,‘1700’,‘1209’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘01005’,‘李莉’,‘女’,‘1971-12-20 00:00:00’,‘副教授’,‘0001’,‘1600’,‘1320’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘02001’,‘王大有’,‘男’,‘1978-05-06 00:00:00’,‘副教授’,‘0002’,‘1400’,‘670’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘02002’,‘张东亭’,‘男’,‘1983-11-12 00:00:00’,‘讲师’,‘0002’,‘1800’,‘800’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘02003’,‘许明生’,‘女’,‘1982-11-12 00:00:00’,‘讲师’,‘0002’,‘1900’,‘1300’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘02004’,‘王一山’,‘男’,‘1985-04-12 00:00:00’,‘讲师’,‘0002’,‘2005’,‘1700’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘02005’,‘李明’,‘男’,‘1905-4-29 00:00:00’,‘讲师’,‘0003’,‘1800’,‘780’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘02006’,‘王琼’,‘女’,‘1905-05-05 00:00:00’,‘副教授’,‘0002’,‘2500’,‘1300’);

insert
into Teacher(Tno,Tname,Tsex,Tbir,Ttitle,Dno,Tjbgz,Tgwjt)
values(‘02007’,‘李娜’,‘女’,‘1905-05-05 00:00:00’,‘副教授’,‘0002’,‘2500’,‘1300’);

---

–（6）教师任课情况Teaching
insert
into Teaching(Tno,Cno)
values(‘01001’,‘01001’);

insert
into Teaching(Tno,Cno)
values(‘01001’,‘01002’);

insert
into Teaching(Tno,Cno)
values(‘01002’,‘02001’);

insert
into Teaching(Tno,Cno)
values(‘01003’,‘02002’);

insert
into Teaching(Tno,Cno)
values(‘01004’,‘02003’);

---

–(7)院系情况表Department
insert
into Department(Dno,Dname)
values(‘0001’,‘计算机系’);

insert
into Department(Dno,Dname)
values(‘0002’,‘物理与电子信息工程’);

insert
into Department(Dno,Dname)
values(‘0003’,‘生物与化学工程系’);

insert
into Department(Dno,Dname)
values(‘0004’,‘数学系’);

insert
into Department
values(‘0005’,‘中文系’);

insert
into Department
values(‘0006’,‘体育系’);

insert
into Department
values(‘0007’,‘外语系’);

insert
into Department
values(‘0008’,‘艺术系’);

---

–目前结束！！！

注意：

网络资源海量，本人无法有效辨认是否侵权，若有侵权，请联系作者删除内容。感谢您的访问。