数据库源码
IF NOT EXISTS (
SELECT NAME
FROM SYS.DATABASES
WHERE NAME = N'EDUC'
)
USE EDUC
-- student 表的建立
IF OBJECT_ID('student', 'U')IS NOT NULL -- U = Table (user-defined)
DROP TABLE student
CREATE TABLE student
(
sno char(8) primary key NOT NULL ,
sname char(8) NOT NULL,
sex char(4) NULL, -- 因为使用汉字问题修改char的长度
native char(20) NULL,
birthday date NULL,
dno char(10) NULL, -- 因为使用汉字问题修改char的长度
spno char(8) NULL, -- foreign key references spon_table(spno)
classno char(4) NULL,
entime smallint NULL,
home varchar(40) NULL,
tel varchar(40) NULL,
);
GO
INSERT INTO student -- 插入学生信息
([sno],[sname],[sex],[native],[birthday],[dno],[spno],[classno],[entime],[home],[tel])
VALUES
( N'001', N'张某某', N'男,',N'银河系', N'2020.6.6', N'软件工程', NULL, N'19-1', N'2019', N'M78星云光之国', N'110'),
( N'002', N'李某某', N'男,',N'银河系', N'2020.6.6', N'计算机',NULL, N'19-1', N'2018', N'M78星云光之国', N'110'),
( N'003', N'劳某某', N'男,',N'银河系', N'2020.6.6', N'软件工程', NULL, N'19-1', N'1999', N'M78星云光之国', N'110'),
( N'004', N'贤某某', N'男,',N'银河系', N'2020.6.6', N'网络工程',NULL, N'19-1', N'2019', N'M78星云光之国', N'110'),
( N'005', N'韦某某', N'男,',N'银河系', N'2020.6.6', N'计算机',NULL, N'19-1', N'2019', N'M78星云光之国', N'110'),
( N'007', N'王朝', N'男,',N'银河系', N'2020.6.6', N'计算机',NULL, N'19-1', N'1998', N'M78星云光之国', N'110'),
( N'009', N'张三', N'男,',N'银河系', N'2020.6.6', N'计算机',NULL, N'19-1', N'2019', N'M78星云光之国', N'110')
GO
SELECT * FROM student; -- 选择全部列的内容展示
--同上方法
--课程表的建立
IF OBJECT_ID('course', 'U') IS NOT NULL
DROP TABLE course
GO
CREATE TABLE course
(
sno char(10) NOT NULL primary key(sno),
spno int NULL, --foreign key references spon_table(spno)
cname char(20) NOT NULL,
ctno tinyint NULL,
experiment tinyint NULL,
lecture tinyint NULL,
semester tinyint NULL,
credit tinyint NULL,
cpno varchar(120) null,
-- foreign key(spno) references spon_table(spno)--tb_Users表的ID作为tb_UserAndRole表的外键
);
INSERT INTO course
([sno],[spno],[cname],[ctno],[experiment],[lecture],[semester],[credit],[cpno])
VALUES
( N'001',10086,N'高数', N'6', N'66', N'60', N'1', N'7',N'大学物理'),
( N'002',10086, N'计算机组成原理', N'6', N'66', N'60', N'1', N'3',N'大学物理'),
( N'003',10086, N'大学英语3', N'6', N'66', N'60', N'1', N'3',N'高等数学'),
( N'004',10086, N'数字逻辑', N'6', N'66', N'60', N'1', N'3',N'大学语文'),
( N'005',10086, N'算法设计分析', N'6', N'66', N'60', N'1', N'3',N'马克思'),
( N'006',10086, N'Java面向对象程序设计', N'6', N'66', N'60', N'1', N'3',N'数据结构'),
( N'007',10086, N'软件工程', N'6', N'66', N'60', N'1', N'3',N'大学物理'),
( N'008',10086,N'C++原理', N'6', N'66', N'60', N'1 ', N'4',N'大学物理'),
( N'009',10086,N'数据库系统概论', N'6', N'66', N'60', N'1 ', N'4',N'大学物理')
GO
--alter table course add cpno varchar(20)
SELECT * FROM course;
--学生选课表的建立STUDENT_COURSE
IF OBJECT_ID('STUDENT_COURSE', 'U') IS NOT NULL
DROP TABLE STUDENT_COURSE
GO
CREATE TABLE student_course
(
sno char(10) NOT NULL,
tcid smallint NOT NULL,
score tinyint NULL,
);
GO
INSERT INTO student_course
([sno],[tcid],[score])
VALUES
( N'001', N'110', N'66'),
( N'002', N'120', N'77'),
( N'003', N'119', N'88'),
( N'004', N'333', N'100'),
( N'005', N'333', N'120'),
( N'006', N'333', N'200'),
( N'007', N'333', N'77'),
( N'008', N'333', N'99'),
( N'009', N'333', N'92')
GO
SELECT * FROM student_course;
--teacher_course 表
IF OBJECT_ID('teacher', 'U') IS NOT NULL
DROP TABLE teacher
GO
CREATE TABLE teacher
(
tno char(8) NOT NULL,
tname char(8) NOT NULL,
sex [NVARCHAR](10) NOT NULL,
dno [NVARCHAR](10) NULL,
pno [NVARCHAR](30) NOT NULL,
home [NVARCHAR](10) NULL,
zipcode [NVARCHAR](10) NULL,
tel varchar(40) NULL,
emainl varchar(40) NULL
);
GO
INSERT INTO teacher
([tno],[tname],[sex],[dno],[pno],[home],[zipcode],[tel],[emainl])
VALUES
( N'001', N'666', N'1008611', N'19-1', N'678', N'大三', N'1', N'6','66677788qq.com'),
( N'001', N'666', N'1008611', N'19-1', N'678', N'大三', N'1', N'6','66677788qq.com'),
( N'001', N'666', N'1008611', N'19-1', N'678', N'大三', N'1', N'6','66677788qq.com'),
( N'001', N'666', N'1008611', N'19-1', N'678', N'大三', N'1', N'6','66677788qq.com'),
( N'001', N'666', N'1008611', N'19-1', N'678', N'大三', N'1', N'6','66677788qq.com')
GO
SELECT * FROM teacher;
--teacher_course 表
IF OBJECT_ID('teacher_course', 'U') IS NOT NULL
DROP TABLE teacher_course
GO
CREATE TABLE teacher_course
(
tcid smallint PRIMARY KEY NOT NULL,
tno char(10) NULL,
spno char(10) NOT NULL,
classno char(10) NULL,
cno char(100) NOT NULL,
semester char(6) NULL,
classtime varchar(40) NULL,
classroom varchar(40) NULL,
weektime tinyint NULL
);
GO
INSERT INTO teacher_course
([tcid],[tno],[spno],[classno],[cno],[semester],[classtime],[classroom],[weektime])
VALUES
( N'001', N'666', N'1008611', N'19-1', N'678', N'大三', N'1', N'6','88'),
( N'002', N'666', N'1008611', N'19-1', N'678', N'大三', N'1', N'6','88'),
( N'003', N'666', N'1008611', N'19-1', N'678', N'大三', N'1', N'6','88'),
( N'004', N'666', N'1008611', N'19-1', N'678', N'大三', N'1', N'6','88'),
( N'005', N'666', N'1008611', N'19-1', N'678', N'大三', N'1', N'6','88')
GO
SELECT * FROM teacher_course;
第一部分代码
--① 求计算机系的学生学号和姓名
select sno,sname from student where dno = '计算机'
--② 求选修了课程的学生学号
select distinct sno from student_course
--③ 求选修C1 课程的学生学号和成绩，如果成绩相同则按学号的升序排列；
-- order by tsage desc --降序排序并要求对查询结果按成绩的降序排列，
--
-- order by tsage asc --升序排序
select distinct sno , score from student_course
where tcid >= 100
order by score desc,sno asc
--④ 求选修课程C1 且成绩在80－90 之间的学生学号和成绩，并将成绩乘以
--系数0.75 输出；
select distinct sno,score * 0.8 as chengji from student_course
where score >= 80 and score < 90
--⑤ 求计算机系和数学系的姓张的学生的信息；
select * from student where sname like '张%' and dno = '计算机' or dno = '数学'
--⑥ 求缺少了成绩的学生的学号和课程号。
select distinct sno,tcid from student_course where score = ''
--⑦ 将2000 以后的成绩大于90 分的学生成绩存入永久成绩表；将2000年以
--前的成绩存入临时成绩表中。
--永久成绩表
IF OBJECT_ID('asd', 'U')IS NOT NULL -- U = Table (user-defined)
DROP TABLE asd
select score into asd from student_course inner join student on
student_course.sno=student.sno where score>90 and entime>2000
SELECT * FROM asd;
--临时成绩表
IF OBJECT_ID('tempdb..#aa', 'U') IS NOT NULL -- U = Table (user-defined)
DROP TABLE #aa
select score into #aa from student_course inner join student on
student_course.sno=student.sno where entime < 2000
SELECT * FROM #aa;
第二部分代码
--2. 连接查询操作 对EDUC 数据库实现以下查询：
--① 查询每个学生的情况以及他（她）所选修的课程；
select * from student_course inner join student on
student_course.sno=student.sno
--② 求学生的学号、姓名、选修的课程名及成绩；
select a.sno,sname,tcid,score from student as a inner join student_course as b on
a.sno=b.sno
--③ 求选修C1 课程且成绩在90 分以上的学生学号、姓名及成绩；
select a.sno, a.cname,b.score from course as a inner join student_course as b on a.sno = b.sno where b.score > 90
--④ 查询每一门课的间接先行课，即代表先修课的先修课，由于表不存在cpno，故自行建立
select cname,cpno from course