mysql 三天分组查询_3mysql第三天查询的指令补充-白红宇

mysql 三天分组查询_3mysql第三天查询的指令补充

阅读量：1540 次

发布时间：2019-04-21

本文共 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