数据库实验四--源码

最新推荐文章于 2024-03-23 12:30:11 发布

竹林海灵

最新推荐文章于 2024-03-23 12:30:11 发布

阅读量465

点赞数

分类专栏：数据库管理和使用

本文链接：https://blog.csdn.net/m0_45953836/article/details/118444430

版权

数据库管理和使用同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

SQL

4 篇文章 0 订阅

订阅专栏

数据库源码


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