2025年数据库上机实验七（多表连接查询、子查询）

大家好，我是讯享网，很高兴认识大家。

在Student表和score表上进行查询。Student表和score表的定义如下表所示：

讯享网

score表数据如下：

1.查询操作：

1）查询李五一的考试科目和考试成绩

2）查询所有学生的信息和考试信息

3）计算每个学生的总成绩（需显示学生姓名）

4) 计算计算机成绩低于95分的学生的信息

5）查询同时参加计算机和英语考试的学生的信息

6）从student表和score表中查询学生的学号，然后合并查询结果

7）查询姓张或姓王同学的姓名、院系、考试科目和成绩

8) 查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。

1）查询李五一的考试科目和考试成绩 SELECT a.Name,b.Cname,b.Grade FROM Student AS a JOIN score AS b ON a.Num=b.Num WHERE a.Name='李五一'; 2）查询所有学生的信息和考试信息 SELECT * FROM Student AS a JOIN score AS b ON a.Num=b.Num; 3）计算每个学生的总成绩（需显示学生姓名） SELECT a.Name, SUM(Grade) FROM Student AS a JOIN score AS b ON a.Num=b.Num GROUP BY a.Num; 4) 计算计算机成绩低于95分的学生的信息 SELECT a.Num,a.Name,a.Dept,a.Address,a.Birthday ,b.Grade,b.Cname FROM Student AS a JOIN score AS b ON a.Num=b.Num WHERE b.Grade<95 AND b.Cname='计算机'; 5）查询同时参加计算机和英语考试的学生的信息 SELECT a.Num,a.Name,a.Dept,a.Address,a.Birthday FROM Student AS a JOIN score AS b ON a.Num=b.Num JOIN score AS c ON b.Num=c.Num WHERE (b.Cname='计算机' AND c.Cname='英语') ; 6）从student表和score表中查询学生的学号，然后合并查询结果 SELECT Num FROM Student UNION SELECT Num FROM score; 7）查询姓张或姓王同学的姓名、院系、考试科目和成绩 SELECT a.Name,a.Dept,b.Cname,b.Grade FROM student AS a JOIN score AS b ON a.Num=b.Num WHERE a.name LIKE '张%' OR a.Name LIKE '王%'; 8) 查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩 SELECT a.Address,a.Name,(DATE_FORMAT(NOW(),'%Y')-a.Birthday) AS age,a.Dept,b.Cname,b.Grade FROM Student AS a JOIN score AS b ON a.Num=b.Num WHERE a.Address LIKE '湖南省%';

讯享网

2.利用子查询实现第一题中的4） 5） 7） 8）

讯享网4）计算计算机成绩低于95分的学生的信息 SELECT Num,Name,Dept,Address,Birthday FROM Student WHERE Num IN (SELECT Num FROM score WHERE Cname='计算机' AND Grade<95); 5）查询同时参加计算机和英语考试的学生的信息 SELECT Num,Name,Dept,Address,Birthday FROM Student WHERE Num IN (SELECT Num FROM score WHERE Cname='英语') AND Num IN (SELECT Num FROM score WHERE Cname='计算机'); 7）查询姓张或姓王同学的姓名、院系、考试科目和成绩 SELECT a.Name,a.Dept,b.Cname,b.Grade FROM (SELECT Num,Name,Dept FROM Student WHERE Name LIKE '张%' OR Name LIKE '王%') AS a JOIN score AS b ON a.Num=b.Num; 8) 查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩 SELECT a.Address,a.Name,(DATE_FORMAT(NOW(),'%Y')-a.Birthday) AS age,a.Dept,b.Cname,b.Grade FROM (SELECT Num,Name,Dept,Birthday,Address FROM Student WHERE Address LIKE '湖南省%') AS a JOIN score AS b ON a.Num=b.Num;

3.学生选课关系数据库C ,S,SC表分别如下：

1)检索不选修任何课程的学生的学号

2)查询选课两门课的学生的姓名

3)查询学习课程号为'C2',成绩为第一名的学生的姓名

4)查询选修C2课程成绩大于该课平均成绩的学生的姓名学号成绩

1)检索不选修任何课程的学生的学号 SELECT * FROM S WHERE SNO NOT IN( SELECT DISTINCT SNO FROM SC); 2)查询选课两门课的学生的姓名 SELECT SN FROM S WHERE SNO IN (SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(*)=2); 3)查询学习课程号为'C2',成绩为第一名的学生的姓名 SELECT SN FROM S WHERE SNO= (SELECT SNO FROM SC WHERE CNO='C2' ORDER BY SCORE DESC LIMIT 1); 4)查询选修C2课程成绩大于该课平均成绩的学生的姓名学号成绩 SELECT SN,S.SNO,SCORE FROM S,SC WHERE CNO='C2' AND SCORE>( SELECT AVG(SCORE) FROM SC WHERE CNO='C2' HAVING S.SNO=SC.SNO);

多表连接源码：

讯享网-- 创建省份表 CREATE TABLE provinces( id TINYINT UNSIGNED AUTO_INCREMENT KEY, proName VARCHAR(10) NOT NULL UNIQUE ); INSERT provinces(proName) VALUES('北京'), ('上海'), ('深圳'); -- 管理员admin id username email proId CREATE TABLE admin( id TINYINT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL UNIQUE, email VARCHAR(50) NOT NULL DEFAULT '@.com', proId TINYINT UNSIGNED NOT NULL ); INSERT admin(username,proId) VALUES('king',1); INSERT admin(username,proId) VALUES('queen',2); -- 商品分类cate id cateName cateDesc CREATE TABLE cate( id TINYINT UNSIGNED AUTO_INCREMENT KEY, cateName VARCHAR(50) NOT NULL UNIQUE, cateDesc VARCHAR(100) NOT NULL DEFAULT '' ); INSERT cate(cateName) VALUES('母婴'); INSERT cate(cateName) VALUES('服装'); INSERT cate(cateName) VALUES('电子'); -- 商品表products id productName, price,cateId CREATE TABLE products( id INT UNSIGNED AUTO_INCREMENT KEY, productName VARCHAR(50) NOT NULL UNIQUE, price FLOAT(8,2) NOT NULL DEFAULT 0, cateId TINYINT UNSIGNED NOT NULL, adminId TINYINT UNSIGNED NOT NULL ); INSERT products(productName,price,cateId,adminId) VALUES('iphone9',9888,3,1), ('adidas',388,2,2), ('nike',888,2,2), ('奶瓶',288,1,1); -- 练习1：查询products id productName price --- cate cateName SELECT p.id, p.productName, p.price, c.cateName FROM products AS p JOIN cate AS c ON p.cateId = c.id; -- 练习2：查询管理员 id username email -- provinces proName SELECT a.id,a.username,a.email,pro.proName FROM admin AS a JOIN provinces AS pro ON a.proId=pro.id; -- 练习3：查询 products id productName price -- cate cateName -- admin username email SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email FROM products AS p JOIN cate AS c ON p.cateId=c.id JOIN admin AS a ON p.adminId=a.id; -- 练习4：查询 价格低于1000元商品的products id productName price -- cate cateName -- admin username email 按价格的降序排列，并且只显示前两条 SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email FROM products AS p JOIN cate AS c ON p.cateId=c.id JOIN admin AS a ON p.adminId=a.id WHERE p.price < 1000 ORDER BY p.price DESC LIMIT 2 ; -- 练习5：查询products id productName price -- cate cateName -- admin username email -- provinces proName SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email,pro.proName FROM products AS p JOIN cate AS c ON p.cateId=c.id JOIN admin AS a ON p.adminId=a.id JOIN provinces AS pro ON a.proId=pro.id;

连接查询源码：

dep表 +----+-----------------+-----------------------------+ | id | depName | depDesc | +----+-----------------+-----------------------------+ | 1 | 大数部 | 大学数学基础课 | | 2 | 信计部 | 信息与计算科学学科 | | 3 | 应用数学部 | 数学建模与科学计算 | | 4 | 光科 | 物理光信息 | +----+-----------------+-----------------------------+ emp表 +----+----------+-----+--------+--------+-------+ | id | username | age | sex | addr | depId| +----+----------+-----+--------+--------+-------+ | 1 | king | 24 | 保密 | 北京 | 1 | | 2 | queen | 25 | 保密 | 北京 | 2 | | 3 | imooc | 26 | 保密 | 北京 | 1 | | 4 | lily | 27 | 保密 | 北京 | 1 | | 5 | rose | 28 | 保密 | 北京 | 3 | | 6 | john | 29 | 保密 | 北京 | 3 | +----+----------+-----+--------+--------+-------+ CREATE TABLE emp( id INT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号', age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄', sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别', addr VARCHAR(20) NOT NULL DEFAULT '北京', depId TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号' )ENGINE=INNODB CHARSET=UTF8; INSERT emp(username,age,depId) VALUES('king',24,1), ('queen',25,2), ('imooc',26,1), ('lily',27,1), ('rose',28,3), ('john',29,3); CREATE TABLE dep( id TINYINT UNSIGNED AUTO_INCREMENT KEY, depName VARCHAR(50) NOT NULL UNIQUE, depDesc VARCHAR(100) NOT NULL DEFAULT '' )ENGINE=INNODB CHARSET=UTF8; INSERT dep(depName,depDesc) VALUES('大数部','大学数学基础课'), ('信计部','信息与计算科学学科'), ('应用数学部','数学建模与科学计算'), ('光科','物理光信息'); -- 做笛卡尔积 SELECT emp.id,emp.username,emp.age,dep.id,dep.depName,dep.depDesc FROM emp,dep; --内连接 SELECT e.id,e.username,e.age,d.depName FROM emp AS e INNER JOIN dep AS d ON e.depId=d.id; -- 测试左外连接 INSERT emp(username,age,depId)VALUES('测试用户',39,7); SELECT e.id,e.username,e.age,d.depName,d.depDesc FROM emp AS e LEFT OUTER JOIN dep AS d ON e.depId=d.id; -- 测试右外连接 SELECT e.id,e.username,e.age,d.depName,d.depDesc FROM emp AS e RIGHT JOIN dep AS d ON e.depId=d.id;

子查询源码：

讯享网CREATE TABLE S( SNO CHAR(6) PRIMARY KEY, SN VARCHAR(20) NOT NULL, AGE TINYINT UNSIGNED NOT NULL, DEPT VARCHAR(20) ); INSERT S VALUES('S1','李立勇',20,'CS'), ('S2','刘蓝',23,'IS'), ('S3','周小花',18,'MA'), ('S4','张立伟',19,'IS'), ('S5','王世明',19,'IS'), ('S6','陈思思',19,'MS'); CREATE TABLE C( CNO VARCHAR(6) PRIMARY KEY, CN VARCHAR(10) NOT NULL, CPNO VARCHAR(6) ); INSERT C VALUES('C1','数据库','C2'), ('C2','离散数学',NULL), ('C3','操作系统','C4'), ('C4','数据结构','C2'); CREATE TABLE SC( SNO CHAR(6), CNO VARCHAR(6), SCORE INT NOT NULL, CONSTRAINT S_C_P PRIMARY KEY(SNO,CNO), CONSTRAINT S_F FOREIGN KEY(SNO) REFERENCES S(SNO), CONSTRAINT C_F FOREIGN KEY(CNO) REFERENCES C(CNO) ); INSERT SC VALUES('S1','C1',85), ('S1','C2',90), ('S1','C3',89), ('S1','C4',88), ('S2','C2',78), ('S2','C3',85), ('S3','C2',68), ('S3','C3',78), ('S3','C4',75), ('S4','C1',69), ('S4','C2',82), ('S4','C4',73), ('S5','C1',92), ('S5','C4',86); -- 查询与“张立伟”在同一个系学习的学生 SELECT SNO,SN,DEPT FROM S WHERE DEPT= (SELECT DEPT FROM S WHERE SN='张立伟'); SELECT S1.SNO,S1.SN,S1.DEPT FROM S AS S1 JOIN S AS S2 ON S1.DEPT=S2.DEPT WHERE S2.SN='张立伟'; -- 查询选修了课程名为“数据库”的学生学号和姓名 SELECT SNO,SN FROM S WHERE SNO IN (SELECT SNO FROM SC WHERE CNO IN (SELECT CNO FROM C WHERE CN='数据库') ); SELECT S.SNO,S.SN FROM S JOIN SC ON SC.SNO=S.SNO JOIN C ON C.CNO=SC.CNO WHERE C.CN='数据库'; -- 找出每个学生超过他选修课程平均成绩的课程号 SELECT SNO,CNO FROM SC AS X WHERE SCORE>=( SELECT AVG(SCORE) FROM SC AS Y GROUP BY Y.SNO HAVING Y.SNO=X.SNO); -- 查询选修了C1课程的学生的学号姓名 SELECT SNO,SN FROM S WHERE EXISTS (SELECT * FROM SC WHERE SC.SNO=S.SNO AND CNO='C1'); SELECT S.SNO,S.SN FROM S JOIN SC ON S.SNO=SC.SNO WHERE SC.CNO='C1'; -- 查询没有选修1号课程的学生姓名 SELECT SNO,SN FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.SNO=S.SNO AND CNO='C1'); -- 其它 -- 创建一个user1表,id username CREATE TABLE user1( id int UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) )SELECT id,username FROM emp; -- 将stu表中id=3的用户名写入到user1表中 INSERT user1(username) SELECT username FROM stu where id=3; -- 创建象user1一样的表user2 CREATE TABLE user2 LIKE user1; INSERT user2(username) SELECT username FROM stu; -- 将stu表中的tiancai用户名添加到user2表中 INSERT user2 SET username=(SELECT username FROM stu WHERE id=5); -- 去掉字段的重复值 SELECT DISTINCT(username) FROM user2; -- 将user1和user2数据合并到一起 --去重 SELECT username FROM user1 UNION SELECT username FROM user2; --不去重 SELECT username FROM user1 UNION ALL SELECT username FROM user2;