目录标题
- 实验三 DMDBMS表查询操作
- 一、实验目的:
- 二、实验要求:
- 三、实验重点和难点:
- 四、实验内容:
- 五、实验步骤与结果:
- 1. 表创建
- 2. 数据填充
- 3. 数据查询
- 3.1查出选修了2003这门课(sco),不及格的同学的名字(stuinfo)
- 3.2查出选修了2003这门课不及格的(sco),但是2004这门课及格了的学生的姓名(stuinfo)
- 3.3查询教师所有的专业及不重复的depart列
- 3.4查询score表中成绩在60-80之间的所有记录
- 3.5查询score表中成绩为85,67或87的记录
- 3.6查询student表中95031班,或性别为女的同学的记录
- 3.7以class降序查询student表的所有记录
- 3.8查询95031班的学生人数
- 3.9查询score表中最高分的学生学号和课程号
- 3.10查询每门课的平均分
- 3.11查询score表中至少有两名学生选修的并以3开头的课
- 3.12查询所有学生的sname,cno和degree列
- 3.13查询所有学生的sname,cname和degree列
- 3.14查询95031班学生每门课的平均分
- 3.15查询选修‘3-105’课程的成绩高于105号学生的'3-105’课程成绩的所有同学的记录
- 3.16查询成绩高于学号为‘109’,课程号为'3-105’的成绩的所有记录
- 3.17查询吴教师任课的学生成绩
- 3.19查询选修课程为3-105,且成绩高于选修编号为3-106课程的同学的cno,sno和degree
- 3.20查询student表中不姓‘成’的同学的记录
- 3.21查询男教师及其所上的课程
实验三 DMDBMS表查询操作
一、实验目的:
1.掌握查询中操作符的应用;
2.掌握对复杂查询的查询要求拆解分析;
3.掌握SQL语言对数据库信息及用户信息进行查询;
4.理解和掌握SQL语言: 能够熟练地使用distinct、AS some_name(别名)、join、group by、order by、having、limit、like相关命令对表数据进行复杂查询。
5.完成对范例数据的复杂查询,包括对表数据的查询、去重、分组、数据限制、连接查询等。
二、实验要求:
1.掌握对复杂查询的查询要求拆解分析
2.掌握查询中操作符的应用
3.熟悉DQL语言中各参数的使用方法及应用场景
4.理解和掌握SQL语言: 能够熟练地使用distinct、AS some_name(别名)、join、group by、order by、having、limit、like相关命令对表数据进行复杂查询。
5.熟悉例题查询思路。
三、实验重点和难点:
实验重点:别名的使用、条件查询、连接查询、子查询、数据分组、数据排序
实验难点:连接查询、子查询、分组查询、函数的使用
四、实验内容:
- 数据库信息、用户信息查询
- 表数据查询
2.1.去重查询
2.2. 查询列的选择
2.3.别名的使用
2.4. 条件查询
2.5. 连接查询
2.6. 子查询
2.7. 数据分组
2.8.数据排序
2.9.数据限制
五、实验步骤与结果:
说明:每一部分实验内容,请描述实验过程,并搭配实验结果截图
1. 表创建
//截图SQL指令及输入结果
数据准备:
创建Stuinfo、Sco、Student、Teacher、Course、Score表,并根据实际情况合理的定义主键、联合主键、外键以及相关约束。
表结构如下:
1、Stuinfo
2、Sco
3、Student
4、Teacher
5、Course
6、Score
create table Stuinfo(
SSNO INT NOT NULL,
SNAME VARCHAR(20) DEFAULT NULL,
SAGE INT DEFAULT NULL,
PRIMARY KEY(SSNO)
);
create table Sco(
SNO INT ,
CNO INT DEFAULT NULL,
DEGREE INT DEFAULT NULL,
FOREIGN KEY(SNO) REFERENCES Stuinfo(SSNO)
);
create TABLE Student(
SNO VARCHAR(20) NOT NULL,
SNAME VARCHAR(20) NOT NULL,
SSEX VARCHAR(20) NOT NULL,
SBIRTH DATETIME(6),
CLASS VARCHAR(20),
PRIMARY KEY(SNO)
);
create TABLE Teacher(
TNO VARCHAR(20) NOT NULL,
TNAME VARCHAR(20) NOT NULL,
TSEX VARCHAR(20) NOT NULL,
TBIR DATETIME(6),
PROF VARCHAR(20),
DEPART VARCHAR(20) NOT NULL,
PRIMARY KEY(TNO)
);
create table Course(
CNO VARCHAR(20) NOT NULL,
CNAME VARCHAR(20) NOT NULL,
TNO VARCHAR(20) NOT NULL,
PRIMARY KEY(CNO),
FOREIGN KEY(TNO) REFERENCES Teacher(TNO)
);
create table Score(
SNO VARCHAR(20) NOT NULL,
CNO VARCHAR(20) NOT NULL,
DEGREE DECIMAL,
FOREIGN KEY(CNO) REFERENCES Course(CNO),
FOREIGN KEY(SNO) REFERENCES Student(SNO)
);
2. 数据填充
//截图SQL指令及输入结果
按照如下信息,将数据插入到对应表中,若无法插入请说明原因
Stuinfo:
1001,aa,12
1002,bb,16
1003,cc,14
1004,dd,14
1005,ee,15
insert INTO EXP3.STUINFO(SSNO,SNAME,SAGE) VALUES (1001,'aa',12);
insert INTO EXP3.STUINFO(SSNO,SNAME,SAGE) VALUES (1002,'bb',13);
insert INTO EXP3.STUINFO(SSNO,SNAME,SAGE) VALUES (1003,'cc',14);
insert INTO EXP3.STUINFO(SSNO,SNAME,SAGE) VALUES (1004,'dd',15);
insert INTO EXP3.STUINFO(SSNO,SNAME,SAGE) VALUES (1005,'ee',16);
Sco:
1001,2003,60
1002,2003,53
1003,2003,48
1003,2004,85
1004,2004,96
1005,2003,88
1006,2004,77
insert INTO EXP3.Sco(SNO,CNO,DEGREE) VALUES (1001,2003,60);
insert INTO EXP3.Sco(SNO,CNO,DEGREE) VALUES (1002,2003,53);
insert INTO EXP3.Sco(SNO,CNO,DEGREE) VALUES (1003,2003,48);
insert INTO EXP3.Sco(SNO,CNO,DEGREE) VALUES (1003,2004,85);
insert INTO EXP3.Sco(SNO,CNO,DEGREE) VALUES (1004,2004,96);
insert INTO EXP3.Sco(SNO,CNO,DEGREE) VALUES (1005,2003,88);
insert INTO EXP3.Sco(SNO,CNO,DEGREE) VALUES (1006,2004,77);
第七句产生问题:违反外键约束
Student:
101,朱超,男,1997-09-01,95033
102,刘法治,男,1998-10-02,95031
103,成文彬,女,1997-01-01,95033
104,罗聪,男,1996-11-01,95033
105,董俊杰,女,1975-02-01,95031
106,李衡,女,1998-06-01,95031
insert INTO EXP3.Student VALUES ('101','朱超','男','1997-09-01','95033');
insert INTO EXP3.Student VALUES ('102','刘法治','男','1998-10-02','95031');
insert INTO EXP3.Student VALUES ('103','成文彬','女','1997-01-01','95033');
insert INTO EXP3.Student VALUES ('104','罗聪','男','1996-11-01','95033');
insert INTO EXP3.Student VALUES ('105','董俊杰','女','1975-02-01','95031');
insert INTO EXP3.Student VALUES ('106','李衡','女','1998-06-01','95031');
Teacher:
804,李,男,1985-12-02,副教授,计算机系
805,王,女,1975-10-02,助教,计算机系
806,张,男,1995-11-02,教授,电子工程
807,吴,女,1997-1-02,讲师,计算机系
insert INTO EXP3.Teacher VALUES ('804','李','男','1985-12-02','副教授','计算机系');
insert INTO EXP3.Teacher VALUES ('805','王','女','1975-10-02','助教','计算机系');
insert INTO EXP3.Teacher VALUES ('806','张','男','1995-11-02','教授','电子工程');
insert INTO EXP3.Teacher VALUES ('807','吴','女','1997-1-02','讲师','计算机系');
Course:
3-106,高等数学,804
3-105,C语言,805
4-107,操作系统,806
5-108,大数据,807
insert INTO EXP3.Course VALUES ('3-106','高等数学','804');
insert INTO EXP3.Course VALUES ('3-105','C语言','805');
insert INTO EXP3.Course VALUES ('4-107','操作系统','806');
insert INTO EXP3.Course VALUES ('5-108','大数据','807');
Score:
103,3-106,80
103,3-105,85
104,3-106,64
104,4-107,91
105,5-108,78
105,3-106,65
105,3-105,67
106,3-106,87
106,4-107,56
107,5-108,76
107,4-107,87
108,5-108,98
insert INTO EXP3.Score VALUES ('103','3-106',80);
insert INTO EXP3.Score VALUES ('103','3-105',85);
insert INTO EXP3.Score VALUES ('104','3-106',64);
insert INTO EXP3.Score VALUES ('104','4-107',91);
insert INTO EXP3.Score VALUES ('105','5-108',78);
insert INTO EXP3.Score VALUES ('105','3-106',65);
insert INTO EXP3.Score VALUES ('105','3-105',67);
insert INTO EXP3.Score VALUES ('106','3-106',87);
insert INTO EXP3.Score VALUES ('106','4-107',56);
insert INTO EXP3.Score VALUES ('107','5-108',76);
insert INTO EXP3.Score VALUES ('107','4-107',87);
insert INTO EXP3.Score VALUES ('108','5-108',98);
句10,11,12错误:违反外键约束
3. 数据查询
//截图SQL指令及输入结果
3.1查出选修了2003这门课(sco),不及格的同学的名字(stuinfo)
select SNAME FROM EXP3.STUINFO WHERE
SSNO in (select SNO FROM EXP3.SCO WHERE
(SCO.CNO = 2003 AND SCO.DEGREE < 60));
3.2查出选修了2003这门课不及格的(sco),但是2004这门课及格了的学生的姓名(stuinfo)
select SNAME FROM EXP3.STUINFO WHERE
SSNO in (select SNO FROM EXP3.SCO WHERE
SNO IN
(SELECT SNO FROM EXP3.SCO WHERE SCO.CNO = 2003 AND SCO.DEGREE < 60 ) AND (SELECT SNO WHERE
(
SCO.CNO = 2004 AND SCO.DEGREE >= 60
))
);
下列查询涉及的表均为Student、Teacher、Course、Score
3.3查询教师所有的专业及不重复的depart列
select PROF FROM EXP3.TEACHER;
select DISTINCT DEPART FROM EXP3.TEACHER;
3.4查询score表中成绩在60-80之间的所有记录
select * FROM EXP3.SCORE WHERE SCORE.DEGREE BETWEEN 60 AND 80;
3.5查询score表中成绩为85,67或87的记录
select * FROM EXP3.SCORE WHERE SCORE.DEGREE IN (85,67,87);
3.6查询student表中95031班,或性别为女的同学的记录
select * FROM EXP3.STUDENT WHERE STUDENT.CLASS = '95031' OR STUDENT.SSEX = '女';
3.7以class降序查询student表的所有记录
select * FROM EXP3.STUDENT ORDER BY STUDENT.CLASS DESC ;
3.8查询95031班的学生人数
select COUNT(CLASS) FROM EXP3.STUDENT WHERE STUDENT.CLASS='95031';
3.9查询score表中最高分的学生学号和课程号
select SNO,CNO FROM EXP3.SCORE WHERE SCORE.DEGREE in (SELECT MAX(SCORE.DEGREE) FROM EXP3.SCORE) ;
3.10查询每门课的平均分
SELECT SCORE.CNO,AVG(DEGREE) AS AVG FROM EXP3.SCORE GROUP BY SCORE.CNO;
3.11查询score表中至少有两名学生选修的并以3开头的课
SELECT DISTINCT CNO FROM EXP3.SCORE WHERE
CNO IN(
SELECT CNO FROM(
SELECT CNO,COUNT(SCORE.CNO)as CNT FROM EXP3.SCORE GROUP BY SCORE.CNO) WHERE CNT >= 2)
AND CNO LIKE '3%';
3.12查询所有学生的sname,cno和degree列
select SNAME,CNO,DEGREE FROM STUDENT INNER JOIN EXP3.SCORE ON STUDENT.SNO=SCORE.SNO;
3.13查询所有学生的sname,cname和degree列
select SNAME,CNAME,DEGREE FROM STUDENT
INNER JOIN EXP3.SCORE ON STUDENT.SNO=SCORE.SNO
INNER JOIN EXP3.COURSE ON SCORE.CNO = COURSE.CNO;
3.14查询95031班学生每门课的平均分
select CNAME,AVG(DEGREE)AS AVG FROM ( SELECT SNAME,CNAME,DEGREE FROM STUDENT
INNER JOIN EXP3.SCORE ON STUDENT.SNO = SCORE.SNO
INNER JOIN EXP3.COURSE ON SCORE.CNO = COURSE.CNO
WHERE STUDENT.CLASS = '95031')GROUP BY CNAME;
3.15查询选修‘3-105’课程的成绩高于105号学生的’3-105’课程成绩的所有同学的记录
SELECT * FROM EXP3.SCORE WHERE
SCORE.CNO = '3-105'
AND
SCORE.DEGREE > (select MAX(DEGREE) FROM (select DEGREE FROM EXP3.SCORE WHERE SCORE.CNO='3-105' AND SCORE.SNO='105'));
3.16查询成绩高于学号为‘109’,课程号为’3-105’的成绩的所有记录
SELECT * FROM EXP3.SCORE WHERE
SCORE.DEGREE > (select MAX(DEGREE) FROM (select DEGREE FROM EXP3.SCORE WHERE SCORE.CNO='3-105' AND SCORE.SNO='109'));
3.17查询吴教师任课的学生成绩
select SNAME,COURSE.CNO,CNAME,DEGREE FROM STUDENT
INNER JOIN EXP3.SCORE ON STUDENT.SNO=SCORE.SNO
INNER JOIN EXP3.COURSE ON SCORE.CNO = COURSE.CNO
INNER JOIN EXP3.TEACHER ON COURSE.TNO = TEACHER.TNO
WHERE TEACHER.TNAME = '吴';
#### 3.18查询选修某门课程的同学人数大于2人的教师的名字
SELECT DISTINCT TT FROM (
SELECT SCORE.CNO AS SC,TEACHER.TNAME AS TT FROM (EXP3.SCORE
INNER JOIN EXP3.COURSE ON SCORE.CNO=COURSE.CNO
INNER JOIN EXP3.TEACHER ON COURSE.TNO = TEACHER.TNO))
WHERE TT IN (
SELECT TT FROM (
SELECT TT,COUNT(SC) AS CNT FROM (
SELECT SCORE.CNO AS SC,TEACHER.TNAME AS TT FROM (EXP3.SCORE
INNER JOIN EXP3.COURSE ON SCORE.CNO=COURSE.CNO
INNER JOIN EXP3.TEACHER ON COURSE.TNO = TEACHER.TNO))
GROUP BY SC
)WHERE CNT >=2
)
3.19查询选修课程为3-105,且成绩高于选修编号为3-106课程的同学的cno,sno和degree
select CNO,SNO,DEGREE from EXP3.SCORE WHERE
CNO='3-105'
AND
DEGREE > all(SELECT DEGREE FROM SCORE where SCORE.CNO='3-106');
3.20查询student表中不姓‘成’的同学的记录
SELECT * FROM EXP3.STUDENT WHERE NOT STUDENT.SNAME LIKE '成';
3.21查询男教师及其所上的课程
select TEACHER.TNO,TNAME,COURSE.CNO,CNAME FROM EXP3.TEACHER
INNER JOIN EXP3.COURSE ON COURSE.TNO = TEACHER.TNO
WHERE TEACHER.TSEX= '男';