mysql經(jīng)典4張表問題詳細(xì)講解
1.數(shù)據(jù)庫表結(jié)構(gòu)關(guān)聯(lián)圖
2.問題:
1、查詢"01"課程比"02"課程成績高的學(xué)生的信息及課程分?jǐn)?shù)
3.查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
4、查詢名字中含有"風(fēng)"字的學(xué)生信息
5、查詢課程名稱為"數(shù)學(xué)",且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)
6、查詢所有學(xué)生的課程及分?jǐn)?shù)情況;
7、查詢沒學(xué)過"張三"老師授課的同學(xué)的信息
8.查詢學(xué)過"張三"老師授課的同學(xué)的信息
9、查詢學(xué)過編號為"01"并且也學(xué)過編號為"02"的課程的同學(xué)的信息
10、查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息
11、查詢沒有學(xué)全所有課程的同學(xué)的信息
12、查詢至少有一門課與學(xué)號為"01"的同學(xué)所學(xué)相同的同學(xué)的信息
13、查詢和"01"號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
14、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
15、查詢出只有兩門課程的全部學(xué)生的學(xué)號和姓名
16、查詢1990年出生的學(xué)生名單(注:Student表中Sage列的類型是datetime)
17、查詢每門課程的平均成績,結(jié)果按平均成績降序排列,平均成績相同時,按課程編號升序排列
18、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分?jǐn)?shù);
19、查詢平均成績大于等于85的所有學(xué)生的學(xué)號、姓名和平均成績
20、查詢不及格的課程
21、查詢課程編號為01且課程成績在80分以上的學(xué)生的學(xué)號和姓名;
22、求每門課程的學(xué)生人數(shù)
23、統(tǒng)計每門課程的學(xué)生選修人數(shù)(超過5人的課程才統(tǒng)計)。要求輸出課程號和選修人數(shù),查詢結(jié)果按人數(shù)降序排列,若人數(shù)相同,按課程號升序排列
24、查詢不同課程成績相同的學(xué)生的學(xué)生編號、課程編號、學(xué)生成績
25、檢索至少選修兩門課程的學(xué)生學(xué)號
26、查詢選修了全部課程的學(xué)生信息
27、查詢各學(xué)生的年齡
28、查詢本月過生日的學(xué)生
29、查詢下月過生日的學(xué)生
30、查詢學(xué)全所有課程的同學(xué)的信息
3.源文件:
/* Navicat Premium Data Transfer Source Server : 127.0.0.1 Source Server Type : MySQL Source Server Version : 50720 Source Host : localhost:3306 Source Schema : work Target Server Type : MySQL Target Server Version : 50720 File Encoding : 65001 Date: 16/02/2022 16:39:35 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT COMMENT '課程編號', `cname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '課程名稱', `tid` int(11) NULL DEFAULT NULL COMMENT '教師編號', PRIMARY KEY (`cid`) USING BTREE, INDEX `tid`(`tid`) USING BTREE, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '課程表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES (1, '語文', 2); INSERT INTO `course` VALUES (2, '數(shù)學(xué)', 1); INSERT INTO `course` VALUES (3, '英語', 3); -- ---------------------------- -- Table structure for sc -- ---------------------------- DROP TABLE IF EXISTS `sc`; CREATE TABLE `sc` ( `sid` int(11) NOT NULL COMMENT '學(xué)生編號', `cid` int(11) NULL DEFAULT NULL COMMENT '課程編號', `score` int(11) NULL DEFAULT NULL COMMENT '分?jǐn)?shù)' ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '成績表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of sc -- ---------------------------- INSERT INTO `sc` VALUES (1, 1, 90); INSERT INTO `sc` VALUES (1, 2, 80); INSERT INTO `sc` VALUES (1, 3, 90); INSERT INTO `sc` VALUES (2, 1, 70); INSERT INTO `sc` VALUES (2, 2, 60); INSERT INTO `sc` VALUES (2, 3, 80); INSERT INTO `sc` VALUES (3, 1, 80); INSERT INTO `sc` VALUES (3, 2, 80); INSERT INTO `sc` VALUES (3, 3, 80); INSERT INTO `sc` VALUES (4, 1, 50); INSERT INTO `sc` VALUES (4, 2, 30); INSERT INTO `sc` VALUES (4, 3, 20); INSERT INTO `sc` VALUES (5, 1, 76); INSERT INTO `sc` VALUES (5, 2, 87); INSERT INTO `sc` VALUES (6, 1, 31); INSERT INTO `sc` VALUES (6, 3, 34); INSERT INTO `sc` VALUES (7, 2, 89); INSERT INTO `sc` VALUES (7, 3, 98); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '學(xué)生編號', `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '學(xué)生姓名', `sage` date NULL DEFAULT NULL COMMENT '出生年月', `ssex` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '學(xué)生性別', PRIMARY KEY (`sid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '學(xué)生表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, '趙雷', '1990-01-01', '男'); INSERT INTO `student` VALUES (2, '錢電', '1990-12-21', '男'); INSERT INTO `student` VALUES (3, '孫風(fēng)', '1990-05-20', '男'); INSERT INTO `student` VALUES (4, '李云', '1990-08-06', '男'); INSERT INTO `student` VALUES (5, '周梅', '1991-12-01', '女'); INSERT INTO `student` VALUES (6, '吳蘭', '1992-03-01', '女'); INSERT INTO `student` VALUES (7, '鄭竹', '1989-07-01', '女'); INSERT INTO `student` VALUES (8, '王菊', '1990-01-20', '女'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT COMMENT '教師編號', `tname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教師姓名', PRIMARY KEY (`tid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '教師表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES (1, '張三'); INSERT INTO `teacher` VALUES (2, '李四'); INSERT INTO `teacher` VALUES (3, '王五'); SET FOREIGN_KEY_CHECKS = 1;
4.答案:
1、查詢"01"課程比"02"課程成績高的學(xué)生的信息及課程分?jǐn)?shù) select s.sid,s.sname,s.sage,s.ssex,sc1.score,sc2.score from student s,sc sc1,sc sc2 where sc1.cid=1 and sc2.cid=2 and sc1.score>sc2.score and sc1.sid=sc2.sid and s.sid=sc1.sid; 3.查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績 select s.sid,s.sname,avg(sc.score) from student s,sc group by s.sid having avg(sc.score)>=60; 4、查詢名字中含有"風(fēng)"字的學(xué)生信息 select * from student where sname like ‘%風(fēng)%'; 5、查詢課程名稱為"數(shù)學(xué)",且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù) select s.sname,score from student s,sc where s.sid=sc.sid and cid=2 and score<60; 6、查詢所有學(xué)生的課程及分?jǐn)?shù)情況; select cname,score from sc,course where sc.cid=course.cid; 7、查詢沒學(xué)過"張三"老師授課的同學(xué)的信息 select s.* from student s where s.sid not in(select sc1.sid from sc sc1,course c,teacher t where t.tid=c.tid and sc1.cid=c.cid and t.tname=‘張三'); 8.查詢學(xué)過"張三"老師授課的同學(xué)的信息 select s.* from student s ,sc sc1,course c,teacher t where s.sid=sc1.sid and sc1.cid=c.cid and c.tid=t.tid and t.tname=‘張三'; 9、查詢學(xué)過編號為"01"并且也學(xué)過編號為"02"的課程的同學(xué)的信息 student(sid) sc(sid cid tid) sc2(sid cid tid) course(cid tid cname) select s.* from student s,sc sc1,sc sc2 where s.sid=sc1.sid and sc1.sid=sc2.sid and sc1.cid=1 and sc2.cid=2; 10、查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息 select distinct s.* from student s,sc sc1,sc sc2,sc sc3 where s.sid=sc1.sid and sc1.sid=sc2.sid and sc1.cid=1 and sc2.cid!=2; 11、查詢沒有學(xué)全所有課程的同學(xué)的信息 select s.* from student s where s.sid not in(select sc1.sid from sc sc1,sc sc2,sc sc3 where sc1.cid=1 and sc2.cid=2 and sc3.cid =3 and sc1.sid=sc2.sid and sc1.sid=sc3.sid) group by s.sid; 12、查詢至少有一門課與學(xué)號為"01"的同學(xué)所學(xué)相同的同學(xué)的信息 select distinct s.* from student s,sc sc1 where s.sid=sc1.sid and sc1.cid in(select cid from sc where sid=1) and s.sid<> 1; 13、查詢和"01"號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息 select s.* from student s where s.sid in(select distinct sc.sid from sc where sid<>1 and sc.cid in(select distinct cid from sc where sid=1)group by sc.sid having count(1)=(select count(1) from sc where s.sid=1)); 14、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名 select s.* from student s where s.sid not in(select sc1.sid from sc sc1,course c,teacher t where sc1.cid=c.cid and c.tid=t.tid and t.tname=‘張三'); 15、查詢出只有兩門課程的全部學(xué)生的學(xué)號和姓名 select s.* from student s,sc group by sc.sid having count(sc.sid)=2 and s.sid=sc.sid; 16、查詢1990年出生的學(xué)生名單(注:Student表中Sage列的類型是datetime) select * from student where sage>=‘1900-01-01' and sage<=‘1900-12-31'; select s.* from student s where s.sage like ‘1900-%';(方法2) 17、查詢每門課程的平均成績,結(jié)果按平均成績降序排列,平均成績相同時,按課程編號升序排列 select sc.cid,avg(score) from sc group by sc.cid order by avg(score) DESC , sc.cid; 18、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分?jǐn)?shù); select s.sname,c.cname,score from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and score>70; 19、查詢平均成績大于等于85的所有學(xué)生的學(xué)號、姓名和平均成績 select s.sname,avg(score) from sc,student s where s.sid=sc.sid group by sc.sid having avg(score)>=85; 20、查詢不及格的課程 select s.sname,c.cname,score from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and score<60; 21、查詢課程編號為01且課程成績在80分以上的學(xué)生的學(xué)號和姓名; select s.sid,s.sname from student s,sc where sc.sid=s.sid and sc.cid=1 and score>80; 22、求每門課程的學(xué)生人數(shù) select cid,count(sid) from sc group by sc.cid; 23、統(tǒng)計每門課程的學(xué)生選修人數(shù)(超過5人的課程才統(tǒng)計)。要求輸出課程號和選修人數(shù),查詢結(jié)果按人數(shù)降序排列,若人數(shù)相同,按課程號升序排列 select cid,count(sid) from sc group by cid having count(sid)>5 order by count(sid),cid ASC; 24、查詢不同課程成績相同的學(xué)生的學(xué)生編號、課程編號、學(xué)生成績 select s1.sid,s2.sid,sc1.cid,sc1.score,sc2.score from student s1,student s2,sc sc1,sc sc2 where s1.sid!=s2.sid and s1.sid=sc1.sid and s2.sid=sc2.sid and sc1.cid!=sc2.cid and sc1.score=sc2.score; 25、檢索至少選修兩門課程的學(xué)生學(xué)號 select sid from sc group by sid having count(cid)>=2; 26、查詢選修了全部課程的學(xué)生信息 select s.* from sc,student s where s.sid=sc.sid group by sid having count (cid)=3; 27、查詢各學(xué)生的年齡 select s.sname,(TO_DAYS(‘2017-09-07')-TO_DAYS(s.sage))/365 as age from student s; 28、查詢本月過生日的學(xué)生 select s.sname from student s where s.sage like ‘_____07%'; 29、查詢下月過生日的學(xué)生 select s.sname from student s where s.sage like ‘_____08%'; 30、查詢學(xué)全所有課程的同學(xué)的信息 select s.* from student s,sc sc1,sc sc2,sc sc3 where sc1.cid=1 and sc2.cid=2 and sc3.cid=3 and sc1.sid=sc2.sid and sc1.sid=sc3.cid and s.sid =sc1.sid group by s.sid;
總結(jié)
到此這篇關(guān)于mysql經(jīng)典4張表問題的文章就介紹到這了,更多相關(guān)mysql 4張表問題內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql百萬數(shù)據(jù)表加索引優(yōu)化的方法
在大數(shù)據(jù)時代,隨著數(shù)據(jù)量的快速增長,對數(shù)據(jù)庫的索引優(yōu)化變得尤為重要,本文主要介紹了mysql百萬數(shù)據(jù)表加索引優(yōu)化的方法,感興趣的可以了解一下2024-02-02MySQL 多表關(guān)聯(lián)一對多查詢實(shí)現(xiàn)取最新一條數(shù)據(jù)的方法示例
這篇文章主要介紹了MySQL 多表關(guān)聯(lián)一對多查詢實(shí)現(xiàn)取最新一條數(shù)據(jù)的方法,結(jié)合實(shí)例形式詳細(xì)分析了MySQL 多表關(guān)聯(lián)一對多查詢實(shí)現(xiàn)取最新一條數(shù)據(jù)相關(guān)原理、實(shí)現(xiàn)方法與操作注意事項(xiàng),需要的朋友可以參考下2020-05-05使用mysqldump導(dǎo)入數(shù)據(jù)和mysqldump增量備份(mysqldump使用方法)
mysqldump常用于MySQL數(shù)據(jù)庫邏輯備份,下面看實(shí)例吧2013-12-12sql format()函數(shù)的用法及簡單實(shí)例
下面小編就為大家?guī)硪黄猻ql format函數(shù)()的用法及簡單實(shí)例。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2016-05-05