太原理工大学软件学院 数据库实验 实验二(2021.4.8)

太原理工大学软件学院 数据库实验 实验二(2021.4.8)

实验内容

以下内容直接全部复制到console窗口即可

需要逐句运行

-- （1）创建Student表
CREATE TABLE Student
 ( Sno CHAR(8) PRIMARY KEY,
 Sname CHAR(8) ,
 Ssex CHAR(2) NOT NULL,
 Sage INT,
 Sdept CHAR(20)
 );
-- （2）创建Course表
CREATE TABLE Course
 ( Cno CHAR(4) PRIMARY KEY,
 Cname CHAR(40) NOT NULL,
 Cpno CHAR(4) ,
 Ccredit SMALLINT,
 );
-- （3）创建SC表
CREATE TABLE SC(
    Sno CHAR(8) FOREIGN KEY (Sno) REFERENCES Student(Sno),
    Cno CHAR(4),
    Grade SMALLINT,
);

-- （4）创建员工表Employee
CREATE TABLE Employee
(
编号 CHAR(8) PRIMARY KEY,
姓名 VARCHAR(8) not null
部门 CHR（40）,
工资 numeric(8,2),
生日 datetime,
职称 char(20),
);
-- 指出该语句中的错误并改正后执行。
-- （5）检查表是否创建成功
-- SELECT * FROM Student
-- SELECT * FROM Course
-- SELECT * FROM SC
-- SELECT * FROM Employee
-- （6）修改表结构及约束
--  增加班级列
ALTER TABLE Student ADD Sclass char(4)
--  修改年龄列
ALTER TABLE Student ALTER COLUMN Sage smallint
--  增加约束
ALTER TABLE Course ADD UNIQUE(Cname)
-- （7）删除表
DROP TABLE Employee


-- （1）为Course表按课程名称创建索引
CREATE INDEX iCname On Course(Cname)
-- （2）为Student表按学生姓名创建唯一索引
CREATE UNIQUE INDEX iSname ON Student(Sname)
-- （3）为SC表按学号和课程号创建聚集索引
CREATE CLUSTERED INDEX iSnoCno On SC(Sno,Cno desc)

-- （4）为Course表按课程号创建唯一索引
CREATE UNIQUE INDEX iSCno ON Course(Cno)


-- 3．创建视图
-- 建立信息系学生的视图：

CREATE VIEW IS_Student
 AS
 SELECT Sno,Sname,Sage FROM Student
 WHERE Sdept= 'IS';





-- （1）插入到Student表

INSERT INTO Student VALUES('20100001','李勇','男',20,'CS','1001'),('20100002','刘晨','女',19,'CS','1001')

INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass) VALUES('20100021','王敏','女',18,'MA','1002'),('20100031','张立','男',19,'IS','1003')

INSERT INTO Student(Sno,Sname,Ssex,sclass) VALUES('20100003','刘洋','女','1001')


-- 检查下列语句中的错误，并改正：
-- INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,sclass) VALUES('20100010',赵斌,'男','19','IS','1005')
-- INSERT INTO Student VALUES('20100022','张明明',19,'男','CS','1002')

INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,sclass) VALUES('20100010','赵斌','男','19','IS','1005')

INSERT INTO Student VALUES('20100022','张明明','男',19,'CS','1002')


-- （2）插入到Course表

INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('1','数据库系统原理', '5',4)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('2','高等数学', null,2)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('3','管理信息系统','1',4)

-- 请写出插入其余行的插入语句，并插入数据。
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('6','数据处理',null,'2')
INSERT INTO Course(cno, cname, cpno, ccredit) values ('7','c语言',null,'4')

-- （3）插入到SC表
INSERT INTO SC VALUES('20100001','1',92)
INSERT INTO SC VALUES('20100002','2',80)
INSERT INTO SC(Sno,Cno) VALUES('20100003','1')
INSERT INTO SC(Sno,Cno,Grade) VALUES('20100010','3',null)

-- 请写出插入其余行的插入语句，并插入数据。
INSERT INTO SC VALUES('20100002','2',85)
INSERT INTO SC VALUES('20100002','3',88)
INSERT INTO SC VALUES('20100002','1',90)








-- （4）多行插入到表中
-- 创建存一个表，保存学生的学号、姓名和年龄：

CREATE TABLE cs_Student
 (
学号 char(8),
姓名 char(8),
 年龄 smallint
 );

-- 插入数据行：
INSERT INTO cs_Student
 SELECT Sno,Sname,Sage
 FROM student Where Sdept='CS';

-- （5）检查插入到表中的数据
SELECT * FROM Student
SELECT * FROM Course
SELECT * FROM SC



-- 2． 修改数据
-- （1）将学生20100001的年龄改为22岁。
UPDATE student SET Sage = 22 WHERE Sno='20100001';

-- （2）将所有学生的年龄增加一岁。
UPDATE Student SET Sage = Sage +1

-- （3）填写赵斌同学的管理信息系统课程的成绩
UPDATE SC SET Grade = 85
 WHERE Sno='20100010' AND Cno='3'

-- （4）将计算机科学系全体学生的成绩加5分
UPDATE sc SET Grade=Grade + 5
 WHERE 'CS'=(select Sdept from student where student.Sno=sc.Sno);

-- （5）请自己完成如下操作
--  将刘晨同学的2号课程成绩修改为80
--  将“20100021”同学的学号修改为“20100025”
update SC set Grade=80
    where Sno='20100002' and Cno=2

update Student set Sno='20100025'
    where Sno='20100021'




-- （1）删除学号为201000022的学生记录
DELETE FROM Student WHERE Sno='20100022'

-- （2）删除学号20100001学生的1号课程选课记录
-- 将选课信息复制到一个临时表tmpSC中：
SELECT * INTO tmpSC FROM SC

-- 在tmpSC中执行删除操作：
DELETE FROM tmpSC WHERE Sno='20100001' and Cno='1'

-- （3）删除临时表中20100002学生的全部选课记录
delete from tmpSC where Sno='20100002'

-- （4）删除计算机科学系所有学生的选课记录
DELETE FROM tmpSC WHERE 'CS'=(select Sdept from student where student.Sno=tmpSC.Sno );

-- （5）删除全部选课记录
DELETE FROM tmpSC




-- 3.3 数据查询操作完成如下查询操作：
-- 1．单表查询
-- （1）按指定目标列查询
-- 查询学生的详细记录：

SELECT * FROM Student;

-- 查询学生的学号、姓名和年龄
SELECT Sno,Sname,Sage FROM Student;

-- 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
SELECT Sname,'Year of Birth: ',2004-Sage,LOWER(Sdept) FROM Student;

-- 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
SELECT Sname, 'Year of Birth:' as BIRTH, 2000-Sage BIRTHDAY, DEPARTMENT = LOWER(Sdept)
FROM Student;

-- (4）取消重复行
-- 查询选修了课程的学生学号：比较ALL和DISTINCT的区别
SELECT Sno FROM SC;
SELECT DISTINCT Sno FROM SC;

-- （5）简单条件查询
-- 查询计算机科学系全体学生的名单
SELECT Sname FROM Student WHERE Sdept='CS';

-- （6）按范围查询
-- 查询年龄在20~23岁之间的学生的姓名、系别和年龄
SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23

-- （7）查询属性值属于指定集合的行
-- 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别
SELECT Sname,Ssex FROM Student WHERE Sdept IN ('IS','MA','CS');

-- （8）模糊查询
-- 查询所有姓刘学生的姓名、学号和性别
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%'

-- （9）查询空值
-- 查询缺少成绩的学生的学号和相应的课程号
SELECT Sno,Cno FROM sc WHERE Grade is null;

-- （10）多重条件查询
-- 查询计算机科学系年龄在岁以下的学生姓名
SELECT Sname FROM student WHERE Sdept='CS' and Sage<20;

-- （11）结果集排序
-- 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
SELECT * FROM Student ORDER BY Sdept,Sage DESC;




-- （12）完成下列查询
-- 查询学生基本信息，结果集属性名使用汉字
select Sno as '学号',Sname as '姓名', Ssex as '性别', Sage as '年龄' ,Sdept as '系名', Sclass as '班级' from Student ;

-- 查询信息系且年龄大于23岁同学的学号和姓名
select Sno,Sname from Student where Sage>23 and Sdept='IS';

-- 查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系
select Sno,Sname,Sno,Sdept from Student where Sage=18 or Sage=17 or Sage=20 or Sage=23;

-- 查询年龄不在21~24岁之间的学生的姓名、系别和年龄
select Sname,Sdept,Sage from Student where Sage>24 or Sage<21;




-- 2． 分组统计 （1）聚集函数的使用
-- 查询学生总人数
SELECT COUNT(*) FROM Student;

-- 查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) FROM SC

-- 查询最高分
SELECT MAX(Grade) FROM SC

-- （2）聚集函数作用于部分行
-- 统计2号课程的总分、均分和最高分
SELECT SUM(grade) 总分,AVG(grade) 均分,MAX(grade) 最高分
FROM sc WHERE Cno='2'

-- （3）分组统计
-- 统计各门课程的选课人数、均分和最高分
select cno 课程号,count(*) 人数,AVG(grade) 均分,MAX(grade) 最高分
from sc group by Cno

-- 统计均分大于90的课程
select cno 课程号,count(*) 人数,AVG(grade) 均分,MAX(grade) 最高分
from sc group by Cno
having AVG(grade) > 90;



-- （4）完成下面的查询
-- 统计每个同学的学号、选课数、平均成绩和最高成绩
select Sno 学号,count(cno) 选课数,AVG(Grade) 平均成绩,max(Grade) 最高成绩 from SC group by Sno

-- 统计每个班的每门课的选课人数、平均成绩和最高成绩
select Sclass 班级, Cno 课程号, count(cno) 选课数,AVG(Grade) 平均成绩,max(Grade) 最高成绩 from SC,Student where Student.Sno=SC.Sno group by Cno,Student.Sclass

-- 3.连接查询（1）在WHERE中指定连接条件
-- 查询每个参加选课的学生信息及其选修课程的情况
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno

-- 查询每一门课的间接先修课
SELECT * FROM course first,course second
WHERE first.Cpno=second.Cno;
SELECT first.Cno,second.Cpno FROM course first,course second
WHERE first.Cpno=second.Cno;

-- （2）在FROM中指定连接条件
-- 查询每个参加选课的学生信息及其选修课程的情况
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student JOIN SC ON (Student.Sno=SC.Sno)

-- （3）使用外连接查询
-- 查询每个学生信息及其选修课程的情况
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)

-- （4）复合条件连接查询
--  查询选修号课程且成绩在90分以上的所有学生
SELECT Student.Sno, Sname
FROM Student join SC ON (Student.Sno = SC.Sno) /* 连接条件*/
WHERE SC.Cno= '2' AND SC.Grade > 90; /* 过滤条件*/

-- （5）多表查询
--  查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

-- （6）完成下列查询
--  查询选修了2号课程的同学的学号和姓名

select Student.Sno,Sname from Student,SC where Student.Sno=Sc.Sno and Cno='2'

-- 查询各门课程的课程号、课程名称以及选课学生的学号
select sc.Cno,Cname,Sno from Course,SC where Course.Cno=SC.Cno

-- 查询选修了数据库系统原理课程的同学的学号和姓名和成绩
select SC.Sno ,Sname,Grade from Course,Student,SC where Sc.Cno=Course.Cno and SC.Sno=Student.Sno and Cname='数据库系统原理'





-- 4.嵌套查询（1）由In引出的子查询
-- 查询与“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept FROM Student
WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname= '刘晨'); （2）由比较运算符引出的子查询

-- 找出每个学生超过他选修课程平均成绩的课程号。
SELECT Sno, Cno FROM SC x
WHERE Grade >= ( SELECT AVG(Grade) FROM SC y
WHERE y.Sno=x.Sno);

-- （3）带修饰符的比较运算符引出的子查询
-- 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
SELECT Sname,Sage FROM Student
WHERE Sage < ALL ( SELECT Sage FROM Student WHERE Sdept= 'CS')
AND Sdept <> 'CS' ;

-- （4）由EXISTS引出的子查询
-- 查询所有选修了1号课程的学生姓名
SELECT Sname FROM Student
WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1')

-- 5.集合查询（1）集合并
-- 查询计算机科学系的学生及年龄不大于19岁的学生
SELECT * FROM Student WHERE Sdept= 'CS'
UNION
SELECT * FROM Student WHERE Sage<=19

-- （2）集合交
--  查询计算机科学系且年龄不大于19岁的学生
SELECT * FROM Student WHERE Sdept='CS'
INTERSECT
SELECT * FROM Student WHERE Sage<=19

-- （3）集合差
--  查询计算机科学系且年龄大于19岁的学生
SELECT * FROM Student WHERE Sdept='CS'
EXCEPT
SELECT * FROM Student WHERE Sage <=19;






-- 3.4 视图操作
-- 建立视图并基于视图进行查询：
-- 1. 创建视图
-- （1）建立学生基本信息视图
CREATE VIEW Student_VIEW(学号,姓名,性别,年龄,系,班级)
AS
SELECT Sno,Sname,Ssex,Sage,Sdept,Sclass
FROM Student;

-- （2）建立学生均分视图
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,avg(Grade)
FROM SC GROUP BY Sno;

-- （3）建立选课信息视图
CREATE VIEW XK_VIEW
AS
SELECT Student.*,Course.*,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

-- 2. 视图查询
-- （1）查询学生基本信息
SELECT * FROM Student_VIEW

-- （2）找出每个学生超过他选修课程平均成绩的课程号
SELECT SC.Sno,Cno,grade
FROM SC, S_G
WHERE SC.Sno = S_G.Sno and Grade >= S_G.Gavg

-- （3）查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Sno,Sname,Cname,Grade
FROM XK_VIEW

-- （4）比较使用视图查询和直接从基表查询的优点