检索所学课程包含学生S3所学课程的学生学号
问题描述最新创建表sql代码问题描述
书上的解法感觉很反正常人思维。自己写了一个。
分别有表如下：
教师关系T (T#,TNAME,TITLE)
课程关系C(C#,CNAME,T#)
学生关系S (T#,SNAME,AGE,SEX)
选课关系SC(S#,C#,SCORE)
最新
原本写的是错的，工作了两年现在重新写一遍。
当时宿舍里想了一晚上，结果现在再想一下明明特别简单的一个问题。
只记得当时书上的sql逻辑感觉贼烂，隐隐约约感觉饶了一大圈。
这个迷一样复杂的字段名也是原因
而且这个题目名字也绕的要死，就该叫《查询选了S3同学选的课的学生》
-- 2.再查选的课里有（S3同学选的课程号）的学生学号select distinct `S#` from scwhere sc.`C#` in(-- 1. 先查出S3同学选的所有课程号select `C#` from scwhere `S#` =1)
创建表sql代码
// An highlighted block/*Navicat Premium Data TransferSource Server : localhost_3306Source Server Type : MySQLSource Server Version : 80020Source Host : 127.0.0.1:3306Source Schema : testTarget Server Type : MySQLTarget Server Version : 80020File Encoding : 65001Date: 18/10/ 16:07:29*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for c-- ----------------------------DROP TABLE IF EXISTS `c`;CREATE TABLE `c` (`C#` int(0) NOT NULL,`CNAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`T#` int(0) NOT NULL,PRIMARY KEY (`C#`) USING BTREE,INDEX `T#`(`T#`) USING BTREE,CONSTRAINT `c_ibfk_1` FOREIGN KEY (`T#`) REFERENCES `t` (`T#`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of c-- ----------------------------INSERT INTO `c` VALUES (1, 'python', 1);INSERT INTO `c` VALUES (2, 'C语言', 3);INSERT INTO `c` VALUES (3, 'java设计模式', 4);-- ------------------------------ Table structure for s-- ----------------------------DROP TABLE IF EXISTS `s`;CREATE TABLE `s` (`S#` int(0) NOT NULL,`SNAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`AGE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`SEX` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,PRIMARY KEY (`S#`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of s-- ----------------------------INSERT INTO `s` VALUES (1, 'zjy', '20', '男');INSERT INTO `s` VALUES (2, 'hzr', '20', '男');INSERT INTO `s` VALUES (3, 'wzk', '20', '男');-- ------------------------------ Table structure for sc-- ----------------------------DROP TABLE IF EXISTS `sc`;CREATE TABLE `sc` (`S#` int(0) NOT NULL,`C#` int(0) NOT NULL,`SCORE` int(0) NOT NULL,PRIMARY KEY (`S#`, `C#`) USING BTREE,INDEX `C#`(`C#`) USING BTREE,CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`S#`) REFERENCES `s` (`S#`) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`C#`) REFERENCES `c` (`C#`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of sc-- ----------------------------INSERT INTO `sc` VALUES (1, 1, 100);INSERT INTO `sc` VALUES (1, 2, 100);INSERT INTO `sc` VALUES (1, 3, 100);INSERT INTO `sc` VALUES (2, 2, 50);INSERT INTO `sc` VALUES (3, 1, 50);-- ------------------------------ Table structure for t-- ----------------------------DROP TABLE IF EXISTS `t`;CREATE TABLE `t` (`T#` int(0) NOT NULL,`TNAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`TITLE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,PRIMARY KEY (`T#`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of t-- ----------------------------INSERT INTO `t` VALUES (1, 'llh', '讲师');INSERT INTO `t` VALUES (3, 'wwt', '院长');INSERT INTO `t` VALUES (4, 'sxd', '系主任');SET FOREIGN_KEY_CHECKS = 1;
如果觉得《检索所学课程包含学生S3所学课程的学生学号》对你有帮助，请点赞、收藏，并留下你的观点哦！
