本文共 12692 字,大约阅读时间需要 42 分钟。
1.往navicat中注入下面程序:
/*Navicat Premium Data Transfer
Source Server : sss
Source Server Type : MySQL
Source Server Version :80019Source Host : localhost:3306Source Schema : db3
Target Server Type : MySQL
Target Server Version :80019File Encoding :65001Date:16/04/2020 14:49:22
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for class
-- ----------------------------DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(0) NOT NULL AUTO_INCREMENT,
`caption` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`cid`) USING BTREE
) ENGINE= InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT =Dynamic;-- ----------------------------
-- Records of class
-- ----------------------------INSERT INTO `class` VALUES (1, '三年级一班');
INSERT INTO `class` VALUES (2, '三年级二班');
INSERT INTO `class` VALUES (3, '三年级三班');-- ----------------------------
-- Table structure forcourse-- ----------------------------DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(0) NOT NULL AUTO_INCREMENT,
`cname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`teacher_id` int(0) NOT NULL,
PRIMARY KEY (`cid`) USING BTREE,
INDEX `fk_cour_teach`(`teacher_id`) USING BTREE,
CONSTRAINT `fk_cour_teach` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE= InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT =Dynamic;-- ----------------------------
--Records of course-- ----------------------------INSERT INTO `course` VALUES (1, '生物', 1);
INSERT INTO `course` VALUES (2, '地理', 2);
INSERT INTO `course` VALUES (3, '体育', 3);
INSERT INTO `course` VALUES (4, '物理', 2);
INSERT INTO `course` VALUES (5, '历史', 1);
INSERT INTO `course` VALUES (6, '语文', 4);-- ----------------------------
-- Table structure forscore-- ----------------------------DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(0) NOT NULL AUTO_INCREMENT,
`student_id` int(0) NOT NULL,
`coruse_id` int(0) NOT NULL,
`num` int(0) NOT NULL,
PRIMARY KEY (`sid`) USING BTREE,
INDEX `fk_score_student`(`student_id`) USING BTREE,
INDEX `fk_score_course`(`coruse_id`) USING BTREE,
CONSTRAINT `fk_score_course` FOREIGN KEY (`coruse_id`) REFERENCES `course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE= InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT =Dynamic;-- ----------------------------
--Records of score-- ----------------------------INSERT INTO `score` VALUES (1, 1, 3, 56);
INSERT INTO `score` VALUES (2, 3, 2, 64);
INSERT INTO `score` VALUES (3, 1, 2, 89);
INSERT INTO `score` VALUES (4, 2, 4, 45);
INSERT INTO `score` VALUES (5, 3, 4, 89);
INSERT INTO `score` VALUES (6, 1, 5, 65);
INSERT INTO `score` VALUES (7, 2, 3, 78);
INSERT INTO `score` VALUES (8, 4, 3, 89);
INSERT INTO `score` VALUES (9, 5, 2, 99);
INSERT INTO `score` VALUES (10, 6, 4, 85);
INSERT INTO `score` VALUES (11, 5, 4, 77);
INSERT INTO `score` VALUES (12, 2, 5, 66);
INSERT INTO `score` VALUES (13, 1, 4, 54);
INSERT INTO `score` VALUES (14, 1, 1, 89);
INSERT INTO `score` VALUES (15, 1, 6, 47);
INSERT INTO `score` VALUES (16, 2, 2, 85);
INSERT INTO `score` VALUES (17, 3, 1, 86);
INSERT INTO `score` VALUES (18, 3, 5, 76);
INSERT INTO `score` VALUES (19, 5, 1, 88);
INSERT INTO `score` VALUES (20, 6, 2, 35);-- ----------------------------
-- Table structure forstudent-- ----------------------------DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(0) NOT NULL AUTO_INCREMENT,
`sname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`gener` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`class_id` int(0) NOT NULL,
PRIMARY KEY (`sid`) USING BTREE,
INDEX `fk_cla_stu`(`class_id`) USING BTREE,
CONSTRAINT `fk_cla_stu` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE= InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT =Dynamic;-- ----------------------------
--Records of student-- ----------------------------INSERT INTO `student` VALUES (1, '李华', '男', 2);
INSERT INTO `student` VALUES (2, '陈虎', '男', 3);
INSERT INTO `student` VALUES (3, '小明', '女', 1);
INSERT INTO `student` VALUES (4, '李红', '男', 2);
INSERT INTO `student` VALUES (5, '红花', '男', 3);
INSERT INTO `student` VALUES (6, '莉莉', '女', 2);-- ----------------------------
-- Table structure forteacher-- ----------------------------DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(0) NOT NULL AUTO_INCREMENT,
`tname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`tid`) USING BTREE
) ENGINE= InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT =Dynamic;-- ----------------------------
--Records of teacher-- ----------------------------INSERT INTO `teacher` VALUES (1, '李泽华');
INSERT INTO `teacher` VALUES (2, '余华');
INSERT INTO `teacher` VALUES (3, '蒋国');
INSERT INTO `teacher` VALUES (4, '马贵');
SET FOREIGN_KEY_CHECKS= 1;
View Code
2.寻找成绩中大于70的所有同学的姓名和id号:
SELECT student.sid,student.sname,score.num from score left JOIN student on score.student_id=student.sid where score.num>70;
View Code
3.查询平均成绩大于60分的铜须的学号和平时成绩:使用avg()
SELECT student_id ,AVG(num) from score GROUP BY student_id HAVING AVG(num)>60;
View Code
升级版本:里面用到了临时表:就是把某一次筛选的数据用括号括起来然后用as 从新名一个名字
SELECT
B.student_id,student.sname,B.ccc FROM
(SELECT
student_id,
avg( num ) as ccc
FROM
score
GROUP BY
student_id
HAVING
avg( num )> 60) AS B
LEFT JOIN student ON B.student_id= student.sid;
View Code
4.查寻所有同学的学号、姓名、选课数、总成绩:(需要使用到临时表格、sum、count 、left join)
SELECT B.student_id,B.sum1,B.cou1,student.sname FROM
(select student_id ,sum(num) as sum1,count(course_id) as cou1fromscore GROUP BY student_id) as B
left join student on B.student_id=student.sid
View Code
5.查询姓李老师的个数:
SELECT teacher.tname from teacher WHERE tname like '李%'
View Code
6.分组的一个测试实例:
SELECT score.sid,score.student_id,score.num ,course.cname fromscore left join
course on score.course_id=course.cid where course.cname='生物';
View Code
7.查询没有学习过李华老师同学的学号和姓名:
select * from student where sid not in(
select student_idfrom score where course_id in(select course.teacher_idfromcourse left join teacher on
course.teacher_id=teacher.tid)group by student_id)
View Code
8.查询生物课程比物理课程成绩搞的所有学生的学号:
SELECT A.student_id,A.num as '生物',B.num as '物理' from(select*from score left join course on course.cid=score.course_id where course.cname='生物')as A
inner join
(select*from score left join course on course.cid=score.course_id where course.cname='物理')as B
on A.student_id=B.student_id WHERE A.num >B.num
View Code
9.查询学习过001 并且也学习过002 课程的同学的学号和姓名:
SELECT student.sid,student.sname,score.course_id fromstudent LEFT JOIN score
on score.student_id=student.sid where score.course_id=4 or score.course_id=2
View Code
SELECT score.student_id, count(1) from student LEFT JOIN score on score.student_id=student.sid where score.course_id=1 or course_id=2 GROUP BY student_id having count(student_id)>1
View Code
10插寻过李泽华老师所有课程的同学的学号和姓名:(使用了 连表 left join 、一个表中数据在另一个表中数据查找)
SELECT sid,sname from student WHERE sid in(
SELECT student_idfrom score WHERE course_id in(SELECT course.cidfrom course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE teacher.tname='李泽华' ) GROUP BY student_id)
View Code
11.查询课程成绩小于60分的同学的学号和姓名:
SELECT sid ,sname from student WHERE sid in(
select student_idfrom score where num<60)
View Code
12.查询学全所有科目的学号和姓名:注:当要属某个表中元素个数时可以使用 count(元素名称)
SELECT sid,sname from student WHERE sid in(
select student_idfromscore GROUP BY student_id having
count(1)>=(SELECT count(cid) from course))
View Code
13查询没有学全所有课程同学的学号、姓名:
SELECT sid,sname from student WHERE sid in(
select student_idfromscore GROUP BY student_id having
count(1)!=(SELECT count(cid) from course))
View Code
14 查询学号为1的同学所有课程的其他同学的学号和姓名(至少学过一门):
SELECT student_id,sname ,count(course_id) fromstudent left join score
on student.sid=score.student_id where student.sid!=1 andscore.course_idin (select course_id from score where sid=1)
GROUP BY student_id
View Code
15查询与002 号同学学习课程完全相同的其他同学的学号和姓名:
SELECT student_id,count(1) from score where student_id!=2 and course_id in (SELECT course_id from score where student_id=2) GROUP BY
student_id having count(1) =(SELECT count(course_id) from score where student_id=2 )
View Code
16.删除学习李泽华老师的score记录表
delete from score where course_id in(
select course.cidfromcourse left join teacher on
teacher.tid=course.teacher_id where teacher.tname='李泽华')
View Code
17.按平均成绩从低到高显示所有学生的物理、生物、地理三门的课程成绩,按如下方式进行显示:学生ID、语文、数学、英语、有效课程。有效平军分
SELECT student_id,
(select numfrom score as s2 WHERE s2.student_id=s1.student_id and course_id =4)as '物理',
(select numfrom score as s2 WHERE s2.student_id=s1.student_id and course_id=1)as '生物',
(select numfrom score as s2 where s2.student_id=s1.student_id and course_id=2)as '地理'
from score as s1 GROUP By student_id
View Code
升级版:
18.求每个课程的最高成绩、最低成绩、和平均成绩
SELECT course_id ,max(num),min(num) ,avg(num) fromscore
GROUP BY course_id
View Code
20.我们对最低分数线设定要求如果低于某个值设定为0 使用 case when 条件 then 结果 else 结果 end
SELECT course_id ,max(num),min(num) ,avg(num),case WHEN min(num)<50 THEN 0 ELSE min(num) END fromscore
GROUP BY course_id
View Code
21.按各科平均成绩从低到高和及格率的百分比从高到底排序:
SELECT course_id ,avg(num),sum(1),sum(case when num<60then 0else 1 end),sum(case when num<60then 0else 1 end)/sum(1)as '及格率'
fromscore
GROUP BY course_id order by avg(num) desc,'及格率'desc
View Code
22.课程平均成绩从高到低显示(显示任课老师)
SELECT course_id ,avg(num),teacher.tnamefrom score left join course on score.course_id=course.cid left join teacher on course.teacher_id=teacher.tid
GROUP BY course_id order by avg(num) desc
View Code
23.查询各科成绩种前三名的记录:(不考虑成绩并列的情况)
24.查寻每门课程被选修的人数:
select course_id,count(1) from score GROUP BY course_id
View Code
25.查询只选修七门课程的全部学生的学号和姓名:
SELECT student.sid,student.sname,COUNT(1) from student LEFT JOIN score on student.sid=score.student_id GROUP BY course_id
having count(1)=1
View Code
26.查询每门课选修的人数:
SELECT course_id,COUNT(1) from student LEFT JOIN score on student.sid=score.student_id GROUP BY course_id
View Code
27.查询所有男生、女生的人数:
SELECT gener, count(1) from student GROUP BY gener
View Code
28查询姓张学生的名单:
SELECT * from student WHERE sname like '张%'
View Code
29查询同名同姓学生的名单、并统计同名的人数:
SELECT sname,count(1) from student GROUP BY sname
View Code
30查询没门成绩的平均成绩、结果按平均成绩升序排列如果平均成绩相同、按课程号降序排列
SELECT course_id,avg(if(ISNULL(num),0,num) )as avg fromscore GROUP BY course_id ORDER BY avg asc,
course_id desc
View Code
31.查询平时成绩大于85的所有学生的学号、姓名和平均成绩
SELECT student_id,student.sname ,avg( if (ISNULL(num),0,num)) as avg1 fromscore LEFT JOIN student on
student.sid=score.student_id
GROUP BY course_id HAVING avg1>85
View Code
32.查询课程编号为003 且课程成绩在80分以上的同学的学号和姓名
SELECT student.sid ,student.sname from student LEFT JOIN score on student.sid=score.student_id WHERE
score.num>80 and course_id=3
View Code
33.查询课程名为数学,且分数低于60的学生姓名和分数:
SELECT score.num,student.sname from student LEFT JOIN score on student.sid=score.student_id LEFT JOIN course on score.course_id=course.cid WHERE course.cname='物理'
and score.num<60
View Code
34.查询不同课程但成绩相同的学生的学号、课程名、学生成绩:(可以from两张同样的表as 不同的名字)
SELECT s1.student_id ,s1.course_id,s1.num from score as s1 ,score as s2 where s1.num=s2.num ands1.course_id!=s2.course_id
View Code
35查询每门课程中成绩最好的两门:
SELECT score.sid,score.course_id ,score.num,T.first_num,T.second_num fromscore left
join (select sid ,(select numfrom score as s2 where s2.course_id=s1.course_id order by
num desc limit 0,1) as first_num,(select num from score as s2 where s2.course_id=s1.course_id order by
num desc limit1,1) as second_num fromscore as s1 )as T
on score.sid=T.sid
WHERE score.num<=T.first_num and score.num>=T.second_num
View Code
36检索至少选修两门课程的学生学号:
SELECT student_id FROM score GROUP BY student_id having count(student_id)>1
View Code
37查询全部学生都选修的课程的课程号和课程名:
SELECT course_id ,count(1) from score GROUP BY course_id having COUNT(1)=(SELECT COUNT(1) from student)
View Code
38.查询过没有李泽华老师授课的任何一门课程的学生姓名:
SELECT sname from student WHERE sid not in(
select student_idfrom score LEFT JOIN course on score.course_id=course.cid LEFT JOIN
teacher on course.teacher_id=teacher.tid where teacher.tname='李泽华' GROUP BY student_id) GROUP BY sname
View Code
39 查询两门以上不及格课程的同学的学号急平均成绩:
SELECT student_id,count(1) from score where num<60 GROUP BY student_id having COUNT(1)>2
View Code
40.检索 004 课程分数小于60 ,按分数降序排列的同学学号:
SELECT student_id from score WHERE num<60 and course_id =4 ORDER BY num desc
View Code
41 删除002 同学的001 课程的成绩:
DELETE FROM score WHERE course_id=1 and student_id=2
View Code
转载地址:https://blog.csdn.net/weixin_33642922/article/details/113388746 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!