Mysql学习---SQL测试题之表结构
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql学习---SQL测试题之表结构,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含12428字,纯文字阅读大概需要18分钟。
内容图文
![Mysql学习---SQL测试题之表结构](/upload/InfoBanner/zyjiaocheng/1057/ee2fcfedce044de1969bbb6bcd904338.jpg)
创建表结果和数据准备[直接执行即可]
![技术分享图片](/upload/getfiles/default/2022/11/4/20221104113708388.jpg)
![技术分享图片](/upload/getfiles/default/2022/11/4/20221104113708799.jpg)
1 /* 2 Navicat MySQL Data Transfer 3 4 Source Server : ftl1012 5 Source Server Version : 50617 6 Source Host : localhost:3306 7 Source Database : test_python 8 9 Target Server Type : MYSQL 10 Target Server Version : 50617 11 File Encoding : 65001 12 13 Date: 2017-12-30 13:12:57 14 */ 15 16 SET FOREIGN_KEY_CHECKS=0; 17 18-- ---------------------------- 19-- Table structure for class 20-- ---------------------------- 21DROPTABLEIFEXISTS `class`; 22CREATETABLE `class` ( 23 `cid` int(11) NOTNULL AUTO_INCREMENT, 24 `caption` varchar(32) NOTNULL, 25PRIMARYKEY (`cid`) 26 ) ENGINE=InnoDB AUTO_INCREMENT=5DEFAULT CHARSET=utf8; 27 28-- ---------------------------- 29-- Records of class 30-- ---------------------------- 31INSERTINTO `class` VALUES (‘1‘, ‘三年二班‘); 32INSERTINTO `class` VALUES (‘2‘, ‘三年三班‘); 33INSERTINTO `class` VALUES (‘3‘, ‘一年二班‘); 34INSERTINTO `class` VALUES (‘4‘, ‘二年九班‘); 35 36-- ---------------------------- 37-- Table structure for course 38-- ---------------------------- 39DROPTABLEIFEXISTS `course`; 40CREATETABLE `course` ( 41 `cid` int(11) NOTNULL AUTO_INCREMENT, 42 `cname` varchar(32) NOTNULL, 43 `teacher_id` int(11) NOTNULL, 44PRIMARYKEY (`cid`), 45KEY `fk_course_teacher` (`teacher_id`), 46CONSTRAINT `fk_course_teacher` FOREIGNKEY (`teacher_id`) REFERENCES `teacher` (`tid`) 47 ) ENGINE=InnoDB AUTO_INCREMENT=5DEFAULT CHARSET=utf8; 48 49-- ---------------------------- 50-- Records of course 51-- ---------------------------- 52INSERTINTO `course` VALUES (‘1‘, ‘生物‘, ‘1‘); 53INSERTINTO `course` VALUES (‘2‘, ‘物理‘, ‘2‘); 54INSERTINTO `course` VALUES (‘3‘, ‘体育‘, ‘3‘); 55INSERTINTO `course` VALUES (‘4‘, ‘美术‘, ‘2‘); 56 57-- ---------------------------- 58-- Table structure for score 59-- ---------------------------- 60DROPTABLEIFEXISTS `score`; 61CREATETABLE `score` ( 62 `sid` int(11) NOTNULL AUTO_INCREMENT, 63 `student_id` int(11) NOTNULL, 64 `course_id` int(11) NOTNULL, 65 `num` int(11) NOTNULL, 66PRIMARYKEY (`sid`), 67KEY `fk_score_student` (`student_id`), 68KEY `fk_score_course` (`course_id`), 69CONSTRAINT `fk_score_course` FOREIGNKEY (`course_id`) REFERENCES `course` (`cid`), 70CONSTRAINT `fk_score_student` FOREIGNKEY (`student_id`) REFERENCES `student` (`sid`) 71 ) ENGINE=InnoDB AUTO_INCREMENT=53DEFAULT CHARSET=utf8; 72 73-- ---------------------------- 74-- Records of score 75-- ---------------------------- 76INSERTINTO `score` VALUES (‘1‘, ‘1‘, ‘1‘, ‘10‘); 77INSERTINTO `score` VALUES (‘2‘, ‘1‘, ‘2‘, ‘9‘); 78INSERTINTO `score` VALUES (‘5‘, ‘1‘, ‘4‘, ‘66‘); 79INSERTINTO `score` VALUES (‘6‘, ‘2‘, ‘1‘, ‘8‘); 80INSERTINTO `score` VALUES (‘8‘, ‘2‘, ‘3‘, ‘68‘); 81INSERTINTO `score` VALUES (‘9‘, ‘2‘, ‘4‘, ‘99‘); 82INSERTINTO `score` VALUES (‘10‘, ‘3‘, ‘1‘, ‘77‘); 83INSERTINTO `score` VALUES (‘11‘, ‘3‘, ‘2‘, ‘66‘); 84INSERTINTO `score` VALUES (‘12‘, ‘3‘, ‘3‘, ‘87‘); 85INSERTINTO `score` VALUES (‘13‘, ‘3‘, ‘4‘, ‘99‘); 86INSERTINTO `score` VALUES (‘14‘, ‘4‘, ‘1‘, ‘79‘); 87INSERTINTO `score` VALUES (‘15‘, ‘4‘, ‘2‘, ‘11‘); 88INSERTINTO `score` VALUES (‘16‘, ‘4‘, ‘3‘, ‘67‘); 89INSERTINTO `score` VALUES (‘17‘, ‘4‘, ‘4‘, ‘100‘); 90INSERTINTO `score` VALUES (‘18‘, ‘5‘, ‘1‘, ‘79‘); 91INSERTINTO `score` VALUES (‘19‘, ‘5‘, ‘2‘, ‘11‘); 92INSERTINTO `score` VALUES (‘20‘, ‘5‘, ‘3‘, ‘67‘); 93INSERTINTO `score` VALUES (‘21‘, ‘5‘, ‘4‘, ‘100‘); 94INSERTINTO `score` VALUES (‘22‘, ‘6‘, ‘1‘, ‘9‘); 95INSERTINTO `score` VALUES (‘23‘, ‘6‘, ‘2‘, ‘100‘); 96INSERTINTO `score` VALUES (‘24‘, ‘6‘, ‘3‘, ‘67‘); 97INSERTINTO `score` VALUES (‘25‘, ‘6‘, ‘4‘, ‘100‘); 98INSERTINTO `score` VALUES (‘26‘, ‘7‘, ‘1‘, ‘9‘); 99INSERTINTO `score` VALUES (‘27‘, ‘7‘, ‘2‘, ‘100‘); 100INSERTINTO `score` VALUES (‘28‘, ‘7‘, ‘3‘, ‘67‘); 101INSERTINTO `score` VALUES (‘29‘, ‘7‘, ‘4‘, ‘88‘); 102INSERTINTO `score` VALUES (‘30‘, ‘8‘, ‘1‘, ‘9‘); 103INSERTINTO `score` VALUES (‘31‘, ‘8‘, ‘2‘, ‘100‘); 104INSERTINTO `score` VALUES (‘32‘, ‘8‘, ‘3‘, ‘67‘); 105INSERTINTO `score` VALUES (‘33‘, ‘8‘, ‘4‘, ‘88‘); 106INSERTINTO `score` VALUES (‘34‘, ‘9‘, ‘1‘, ‘91‘); 107INSERTINTO `score` VALUES (‘35‘, ‘9‘, ‘2‘, ‘88‘); 108INSERTINTO `score` VALUES (‘36‘, ‘9‘, ‘3‘, ‘67‘); 109INSERTINTO `score` VALUES (‘37‘, ‘9‘, ‘4‘, ‘22‘); 110INSERTINTO `score` VALUES (‘38‘, ‘10‘, ‘1‘, ‘90‘); 111INSERTINTO `score` VALUES (‘39‘, ‘10‘, ‘2‘, ‘77‘); 112INSERTINTO `score` VALUES (‘40‘, ‘10‘, ‘3‘, ‘43‘); 113INSERTINTO `score` VALUES (‘41‘, ‘10‘, ‘4‘, ‘87‘); 114INSERTINTO `score` VALUES (‘42‘, ‘11‘, ‘1‘, ‘90‘); 115INSERTINTO `score` VALUES (‘43‘, ‘11‘, ‘2‘, ‘77‘); 116INSERTINTO `score` VALUES (‘44‘, ‘11‘, ‘3‘, ‘43‘); 117INSERTINTO `score` VALUES (‘45‘, ‘11‘, ‘4‘, ‘87‘); 118INSERTINTO `score` VALUES (‘46‘, ‘12‘, ‘1‘, ‘90‘); 119INSERTINTO `score` VALUES (‘47‘, ‘12‘, ‘2‘, ‘77‘); 120INSERTINTO `score` VALUES (‘48‘, ‘12‘, ‘3‘, ‘43‘); 121INSERTINTO `score` VALUES (‘49‘, ‘12‘, ‘4‘, ‘87‘); 122INSERTINTO `score` VALUES (‘52‘, ‘13‘, ‘3‘, ‘87‘); 123124-- ----------------------------125-- Table structure for student126-- ----------------------------127DROPTABLEIFEXISTS `student`; 128CREATETABLE `student` ( 129 `sid` int(11) NOTNULL AUTO_INCREMENT, 130 `gender` char(1) NOTNULL, 131 `class_id` int(11) NOTNULL, 132 `sname` varchar(32) NOTNULL, 133PRIMARYKEY (`sid`), 134KEY `fk_class` (`class_id`), 135CONSTRAINT `fk_class` FOREIGNKEY (`class_id`) REFERENCES `class` (`cid`) 136 ) ENGINE=InnoDB AUTO_INCREMENT=17DEFAULT CHARSET=utf8; 137138-- ----------------------------139-- Records of student140-- ----------------------------141INSERTINTO `student` VALUES (‘1‘, ‘男‘, ‘1‘, ‘理解‘); 142INSERTINTO `student` VALUES (‘2‘, ‘女‘, ‘1‘, ‘钢蛋‘); 143INSERTINTO `student` VALUES (‘3‘, ‘男‘, ‘1‘, ‘张三‘); 144INSERTINTO `student` VALUES (‘4‘, ‘男‘, ‘1‘, ‘张一‘); 145INSERTINTO `student` VALUES (‘5‘, ‘女‘, ‘1‘, ‘张二‘); 146INSERTINTO `student` VALUES (‘6‘, ‘男‘, ‘1‘, ‘张四‘); 147INSERTINTO `student` VALUES (‘7‘, ‘女‘, ‘2‘, ‘铁锤‘); 148INSERTINTO `student` VALUES (‘8‘, ‘男‘, ‘2‘, ‘李三‘); 149INSERTINTO `student` VALUES (‘9‘, ‘男‘, ‘2‘, ‘李一‘); 150INSERTINTO `student` VALUES (‘10‘, ‘女‘, ‘2‘, ‘李二‘); 151INSERTINTO `student` VALUES (‘11‘, ‘男‘, ‘2‘, ‘李四‘); 152INSERTINTO `student` VALUES (‘12‘, ‘女‘, ‘3‘, ‘如花‘); 153INSERTINTO `student` VALUES (‘13‘, ‘男‘, ‘3‘, ‘刘三‘); 154INSERTINTO `student` VALUES (‘14‘, ‘男‘, ‘3‘, ‘刘一‘); 155INSERTINTO `student` VALUES (‘15‘, ‘女‘, ‘3‘, ‘刘二‘); 156INSERTINTO `student` VALUES (‘16‘, ‘男‘, ‘3‘, ‘刘四‘); 157158-- ----------------------------159-- Table structure for teacher160-- ----------------------------161DROPTABLEIFEXISTS `teacher`; 162CREATETABLE `teacher` ( 163 `tid` int(11) NOTNULL AUTO_INCREMENT, 164 `tname` varchar(32) NOTNULL, 165PRIMARYKEY (`tid`) 166 ) ENGINE=InnoDB AUTO_INCREMENT=6DEFAULT CHARSET=utf8; 167168-- ----------------------------169-- Records of teacher170-- ----------------------------171INSERTINTO `teacher` VALUES (‘1‘, ‘张磊老师‘); 172INSERTINTO `teacher` VALUES (‘2‘, ‘李平老师‘); 173INSERTINTO `teacher` VALUES (‘3‘, ‘刘海燕老师‘); 174INSERTINTO `teacher` VALUES (‘4‘, ‘朱云海老师‘); 175INSERTINTO `teacher` VALUES (‘5‘, ‘李杰老师‘);
经验: join, 临时表的学习[in 的使用,右边只能输出一个]
-- 查询平均成绩大于60分的同学的学号和平均成绩,按照平均成绩降序排序 操作score张表关联student查姓名
方案一[FTL]: select * from (select student_id, avg(num) from score group by student_id having avg(num) > 60) as T left join student on student.sid = T.student_id; 方案二: select score.student_id, student.sname, avg(num),max(num),min(num) from score LEFT JOIN student on student.sid = score.student_id GROUP BY score.student_id having avg(num) > 60 order by avg(num) desc;
-- 查询所有同学的学号、姓名、选课数、总成绩;
SELECT a.sid, a.sname, SUM(s.num) as zongchengji, count(s.course_id) as xuankeshu from score s LEFT JOIN student a on a.sid = s.student_id GROUP BY s.student_id
-- 查询姓“李”的老师的个数;
方案一[FTL]:select count(tname) from teacher where tname like ‘李%‘; 方案二:select count(1) from teacher where tname like ‘李%‘;
-- 查询没学过“李平”老师课的同学的学号、姓名;
方案一[FTL]:局限性:不能查其他表中的内容 -- 没学过 ==> not int ==> sid not in 成绩表 -- 利用成绩表查找课程信息 ==>关联老师的信息 -- 李平老师 ==> where tname like ‘李平‘ select student.sid, student.sname from student where sid not in ( select student_id from score LEFT JOIN course on score.course_id = course.cid LEFT JOIN teacher on teacher.tid = course.teacher_id where tname like ‘李平%‘ ); 方案二: 思路: 先查到“李平老师”老师教的所有课ID 获取选过课的所有学生ID 学生表中筛选 select * from student where sid not in ( select DISTINCT student_id from score where score.course_id in ( select cid from course left join teacher on course.teacher_id = teacher.tid where tname = ‘李平老师‘ ) )
-- 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
方案一[FTL]: -- 选出课程为1或者2的课 ==> 关联student表查询 SELECT sid,sname from student where sid in (select DISTINCT student_id from score where score.course_id in (select cid from course where cid in (1,2))) 方案二: SELECT sid, sname from (SELECT student_id, count(student_id) FROM (SELECT student_id, course_id FROM score WHERE course_id = 1 OR course_id = 2) AS B GROUP BY student_id HAVING count(student_id) > 1 ) as C LEFT JOIN student on student.sid = C.student_id; 方案三: select student_id,sname from (select student_id,course_id from score where course_id = 1 or course_id = 2) as B left join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 1;
-- 查询没有学全所有课的同学的学号、姓名;
-- 查询没有学全所有课的同学的学号、姓名; -- 所有课 ==> count(1) from course -- 同学的学号、姓名 ==> sid, sname from student -- 没有学全所有课 ==> count(course_id) 方案二: SELECT student.sid, sname from student LEFT JOIN score on score.student_id = student.sid group by student_id HAVING count(course_id) = (select count(1) from course)
-- 查询学过“李平老师“所教的所有课的同学的学号、姓名;
-- 查询学过“李平老师“所教的所有课的同学的学号、姓名; -- 找到李平老师教过的课程ID 方案一[FTL]: SELECT sid, sname FROM student where sid in (SELECT student_id FROM score WHERE course_id IN (SELECT cid FROM course LEFT JOIN teacher ON teacher_id = tid WHERE teacher.tname LIKE ‘%李平%‘ )) 方案二: select sid, sname from (select student_id from score where course_id in (select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname=‘李平老师‘) group by student_id) as B left JOIN student on student.sid = B.student_id
-- 查询所有课程成绩小于60分的同学的学号、姓名;
-- 查询所有课程成绩小于60分的同学的学号、姓名; 方案一[FTL]: select * from ( select student_id, num from score LEFT JOIN student on student.sid = score.student_id where num < 60 ORDER BY num asc ) as B LEFT JOIN student on B.student_id = student.sid
-- 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
-- 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名 方案一[FTL_有错误,未解决] select student.sid, sname from (SELECT * from score where course_id in ( select course_id from score where student_id = 1 )) as B LEFT JOIN student on B.sid = student.sid 方案二: 思路: 获取 001 同学选择的所有课程 获取课程在其中的所有人以及所有课程 根据学生筛选,获取所有学生信息 再与学生表连接,获取姓名 select student_id, sname, count(course_id) from score LEFT JOIN student on student.sid = score.student_id where student_id != 1 and course_id in ( select course_id from score where course_id = 1) GROUP BY student_id
-- 查询至少学过学号为“001”同学所有课数目相同的其他同学学号和姓名
-- 查询至少学过学号为“001”同学所有课数目相同的其他同学学号和姓名 -- 001 所学习的课程 -- 其他人的课 >= 001里面的课 方案一[FTL]: select sid, sname from (select student_id, course_id from score where course_id in (select course_id from score where student_id = 1) group by student_id HAVING count(1) = (select count(1) from score WHERE student_id = 1) ) as B LEFT JOIN student on student.sid = B.student_id GROUP BY student_id
-- 查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;【高难度】
-- 查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;【高难度】 -- 002号学习的课程 -- count(1) == 002的课程count(1) -- left JOIN student 拼接姓名和学号 -- 数量相同学生ID select student_id, course_id, count(1) from score GROUP BY student_id having count(1) = (select count(1) from score where student_id = 2 ) -- 课程相同 select student_id, course_id from score where course_id in (select DISTINCT course_id from score where student_id = 2) GROUP BY student_id -- 综合完成 select student_id,sname from score left join student on score.student_id = student.sid where student_id in (select student_id from score where student_id != 1 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)) and course_id in (select course_id from score where student_id = 1) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)
-- 删除学习“叶平”老师课的score表记录;
delete from score where course_id in (
select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.name = ‘叶平‘)
[更多参考] http://www.cnblogs.com/wupeiqi/articles/5729934.html
[更多参考] http://www.cnblogs.com/wupeiqi/articles/5748496.html
原文:https://www.cnblogs.com/ftl1012/p/9385161.html
内容总结
以上是互联网集市为您收集整理的Mysql学习---SQL测试题之表结构全部内容,希望文章能够帮你解决Mysql学习---SQL测试题之表结构所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。