MySQL练习题
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL练习题,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6956字,纯文字阅读大概需要10分钟。
内容图文
![MySQL练习题](/upload/InfoBanner/zyjiaocheng/877/349fe93a5a4640cc82b82a014b4f7c76.jpg)
创建下列表并创建相关约束
问题
1:查询出成绩表,而且student_id 后面要有对应的学生名,course_id 后面要有对应的课程名.
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923715.jpg)
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923761.jpg)
1 SELECT 2 score.sid, 3 score.student_id, 4 student.sname, 5 score.course_id, 6 course.cname, 7 score.number 8 FROM score 9 LEFT JOIN student 10 ON student.sid = score.student_id 11 LEFT JOIN course 12 ON course.cid = score.course_id参考答案
2:查询平均成绩大于60分的同学的学号和平均成绩
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923715.jpg)
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923761.jpg)
1 SELECT student_id,SUM(number)/COUNT(student_id) AS avg_number FROM score GROUP BY student_id HAVING avg_number>60;View Code
3:查询所有同学的学号、姓名、选课数、总成绩
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923715.jpg)
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923761.jpg)
1 SELECT student.sid,student.sname,COUNT(score.student_id),SUM(number) FROM student,score WHERE student.sid=score.student_id GROUP BY score.student_idView Code
4:查询姓“李”的老师的个数
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923715.jpg)
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923761.jpg)
1 SELECT COUNT(*) FROM teacher WHERE tname LIKE "李%";View Code
5:查询学过“李平”老师课的同学的学号、姓名
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923715.jpg)
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923761.jpg)
1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND score.course_id = (SELECT tid FROM teacher WHERE tname='李平老师');View Code
6:查询学过“1”并且也学过编号“2”课程的同学的学号、姓名
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923715.jpg)
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923761.jpg)
1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND score.course_id IN (1,2) GROUP BY student.sname HAVING COUNT(*)=2;View Code
7:查询没学过“叶平”老师课的同学的学号、姓名
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923715.jpg)
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923761.jpg)
1 SELECT 2 sid, 3 sname 4 FROM student 5 WHERE sid NOT IN(SELECT 6 student.sid 7 FROM score, 8 student 9 WHERE student.sid = score.student_id 10 AND score.course_id IN(SELECT 11 tid 12 FROM teacher 13 WHERE tname = '李平老师'));View Code
8:查询有课程成绩小于60分的同学的学号、姓名
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923715.jpg)
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923761.jpg)
1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND number<60 GROUP BY student.sname;View Code
9:查询没有学全所有课的同学的学号、姓名
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923715.jpg)
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923761.jpg)
1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id GROUP BY student.sid HAVING COUNT(*)<(SELECT COUNT(*) FROM course);View Code
创建表和数据
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923715.jpg)
![MySQL练习题 - 文章图片](/upload/getfiles/0001/2021/5/8/20210508062923761.jpg)
1 /* 2 Navicat Premium Data Transfer 3 4 Source Server : localhost 5 Source Server Type : MySQL 6 Source Server Version : 50624 7 Source Host : localhost 8 Source Database : sqlexam 9 10 Target Server Type : MySQL 11 Target Server Version : 50624 12 File Encoding : utf-8 13 14 Date: 10/21/2016 06:46:46 AM 15 */ 16 17 SET NAMES utf8; 18 SET FOREIGN_KEY_CHECKS = 0; 19 20 -- ---------------------------- 21 -- Table structure for `class` 22 -- ---------------------------- 23 DROP TABLE IF EXISTS `class`; 24 CREATE TABLE `class` ( 25 `cid` int(11) NOT NULL AUTO_INCREMENT, 26 `caption` varchar(32) NOT NULL, 27 PRIMARY KEY (`cid`) 28 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 29 30 -- ---------------------------- 31 -- Records of `class` 32 -- ---------------------------- 33 BEGIN; 34 INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); 35 COMMIT; 36 37 -- ---------------------------- 38 -- Table structure for `course` 39 -- ---------------------------- 40 DROP TABLE IF EXISTS `course`; 41 CREATE TABLE `course` ( 42 `cid` int(11) NOT NULL AUTO_INCREMENT, 43 `cname` varchar(32) NOT NULL, 44 `teacher_id` int(11) NOT NULL, 45 PRIMARY KEY (`cid`), 46 KEY `fk_course_teacher` (`teacher_id`), 47 CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) 48 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 49 50 -- ---------------------------- 51 -- Records of `course` 52 -- ---------------------------- 53 BEGIN; 54 INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2'); 55 COMMIT; 56 57 -- ---------------------------- 58 -- Table structure for `score` 59 -- ---------------------------- 60 DROP TABLE IF EXISTS `score`; 61 CREATE TABLE `score` ( 62 `sid` int(11) NOT NULL AUTO_INCREMENT, 63 `student_id` int(11) NOT NULL, 64 `course_id` int(11) NOT NULL, 65 `num` int(11) NOT NULL, 66 PRIMARY KEY (`sid`), 67 KEY `fk_score_student` (`student_id`), 68 KEY `fk_score_course` (`course_id`), 69 CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), 70 CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) 71 ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8; 72 73 -- ---------------------------- 74 -- Records of `score` 75 -- ---------------------------- 76 BEGIN; 77 INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87'); 78 COMMIT; 79 80 -- ---------------------------- 81 -- Table structure for `student` 82 -- ---------------------------- 83 DROP TABLE IF EXISTS `student`; 84 CREATE TABLE `student` ( 85 `sid` int(11) NOT NULL AUTO_INCREMENT, 86 `gender` char(1) NOT NULL, 87 `class_id` int(11) NOT NULL, 88 `sname` varchar(32) NOT NULL, 89 PRIMARY KEY (`sid`), 90 KEY `fk_class` (`class_id`), 91 CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) 92 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; 93 94 -- ---------------------------- 95 -- Records of `student` 96 -- ---------------------------- 97 BEGIN; 98 INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四'); 99 COMMIT; 100 101 -- ---------------------------- 102 -- Table structure for `teacher` 103 -- ---------------------------- 104 DROP TABLE IF EXISTS `teacher`; 105 CREATE TABLE `teacher` ( 106 `tid` int(11) NOT NULL AUTO_INCREMENT, 107 `tname` varchar(32) NOT NULL, 108 PRIMARY KEY (`tid`) 109 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; 110 111 -- ---------------------------- 112 -- Records of `teacher` 113 -- ---------------------------- 114 BEGIN; 115 INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师'); 116 COMMIT; 117 118 SET FOREIGN_KEY_CHECKS = 1;View Code
内容总结
以上是互联网集市为您收集整理的MySQL练习题全部内容,希望文章能够帮你解决MySQL练习题所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。