Mysql 常用語句實戰（2）

前置 sql 語句

用來建立表、插入資料

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `Cno` int(11) NOT NULL COMMENT '課程編号',
  `Cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '課程名稱',
  `Tno` int(11) NULL DEFAULT NULL COMMENT '老師編号',
  PRIMARY KEY (`Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '數學', 1);
INSERT INTO `course` VALUES (2, '國文', 2);
INSERT INTO `course` VALUES (3, '英文', 1);

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `Sno` int(11) NOT NULL COMMENT '學号',
  `Cno` int(11) NOT NULL COMMENT '課程編号',
  `score` int(255) NULL DEFAULT NULL COMMENT '分數',
  PRIMARY KEY (`Sno`, `Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (1, 1, 99);
INSERT INTO `sc` VALUES (1, 2, 80);
INSERT INTO `sc` VALUES (1, 3, 50);
INSERT INTO `sc` VALUES (2, 1, 70);
INSERT INTO `sc` VALUES (2, 2, 90);
INSERT INTO `sc` VALUES (3, 1, 90);
INSERT INTO `sc` VALUES (4, 1, 60);
INSERT INTO `sc` VALUES (4, 2, 50);
INSERT INTO `sc` VALUES (4, 3, 40);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `Sno` int(255) NOT NULL COMMENT '學号',
  `Sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `Sage` int(255) NULL DEFAULT NULL COMMENT '年齡',
  `Ssex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`Sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '張三豐', 108, '男');
INSERT INTO `student` VALUES (2, '李小龍', 20, '男');
INSERT INTO `student` VALUES (3, '小龍女', 17, '女');
INSERT INTO `student` VALUES (4, '白發魔女', 18, '女');
INSERT INTO `student` VALUES (5, '韋小寶', 19, '男');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `Tno` int(11) NOT NULL COMMENT '老師編号',
  `Tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '老師名稱',
  PRIMARY KEY (`Tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '無崖子');
INSERT INTO `teacher` VALUES (2, '孤獨求敗');
INSERT INTO `teacher` VALUES (3, '洪七公');

SET FOREIGN_KEY_CHECKS = 1;      

題目清單

1、查詢年齡小于18歲的學員資訊 

2、查詢無崖子授課資訊

3、查詢沒有參與任意課程的學生資訊

4、查詢無崖子每個授課課程的學員人數 統計 + 分組 

5、查詢張三豐數學成績

6、查詢出國文最高分【待完成】

7、查詢沒有參與國文考試的學生資訊

8、查詢語數外三門成績的平均分

9、查詢報名孤獨求敗老師課程的學生資訊

10、沒有報名孤獨求敗老師課程的學生資訊

答案清單

#1 查詢年齡小于18歲的學員資訊
select * from student where Sage<18;

#2 查詢無崖子授課資訊
select * from teacher t join course c on c.Tno = t.Tno where t.Tname="無崖子";

#3 查詢沒有參與任意課程的學生資訊
select * from student s left join sc  on s.Sno = sc.Sno where sc.score is null;

#4 查詢無崖子每個授課課程的學員人數 統計 + 分組 
select t.Tname,c.Cname,count(1)  as "學員人數" from teacher t join course c on t.Tno = c.Tno join sc on sc.Cno = c.Cno group by sc.Cno HAVING t.Tname="無崖子";

#5 查詢張三豐數學成績
select s.Sname,c.Cname,sc.score from  student s join sc on s.Sno = sc.Sno join course c on c.Cno = sc.Cno where s.Sname="張三豐" and c.Cname="數學";

#6 查詢出國文最高分【待完成】
select s.Sname,max(sc.score) as "分數" from sc join course c on sc.Cno = c.Cno join student s on s.Sno = sc.Sno where c.Cname="國文";

#7 查詢沒有參與國文考試的學生資訊
select * from student s join sc on sc.Sno = s.Sno right join course c on c.Cno = sc.Cno and c.Cname = "國文" and sc.score is null;
select * from course  c join sc on c.Cno = sc.Cno and c.Cname="國文" right join student s on s.Sno = sc.Sno where sc.score is null;

#8 查詢語數外三門成績的平均分 
select c.Cname,avg(sc.score) from course c join sc on c.Cno = sc.Cno GROUP BY c.Cno;


#9 查詢報名孤獨求敗老師課程的學生資訊
select * from student s join sc on sc.Sno = s.Sno join  course c on c.Cno= sc.Cno join teacher t on t.Tno = c.Tno where t.Tname="孤獨求敗" ;
select * from teacher t join course c on c.Tno = t.Tno and t.Tname = "孤獨求敗" join sc on c.Cno = sc.Cno right join student s on s.Sno = sc.Sno where sc.score is not null;

#10 沒有報名孤獨求敗老師課程的學生資訊
select * from teacher t join course c on c.Tno = t.Tno and t.Tname = "孤獨求敗" join sc on c.Cno = sc.Cno right join student s on s.Sno = sc.Sno where sc.score is null;