Mysql 基础练习大全(建表到基本查询)

已于 2022-11-09 17:10:15 修改
阅读量3.6k 收藏 8
点赞数 3
分类专栏： 数据库 工具 文章标签： 数据库 java sql
于 2022-11-04 14:58:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jam_yin/article/details/127687446
版权

 开篇:

最近打算把sql 练习下,故找了个"SQL题大全",每个sql 都有执行, 希望看到这篇文章的你也能练习一下, 相信每一分学习都会有收获,让学习使我们感到充实.

文中习题参考自: https://blog.csdn.net/cpc784221489/article/details/105102005

在原有基础上对表做了稍微改动,所有SQL都重新执行了一遍,部分函数要参照具体版本,下面是数据库表DDL:

/*
 Navicat Premium Data Transfer

 Source Server         : local
 Source Server Type    : MySQL
 Source Server Version : 50724
 Source Host           : localhost:3306
 Source Schema         : ken

 Target Server Type    : MySQL
 Target Server Version : 50724
 File Encoding         : 65001

 Date: 01/11/2022 11:16:08
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `id` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT,
  `cid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `cname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '课程名称',
  `tid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '教师表编号',
  `create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `update_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  `is_delete` tinyint(1) NULL DEFAULT 0 COMMENT '是否有效: 0: 无效; 1有效',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '01', '语文', '02', 'admin', '2022-11-01 11:02:38', NULL, '2022-11-01 11:02:38', 0);
INSERT INTO `course` VALUES (2, '02', '数学', '01', 'admin', '2022-11-01 11:02:56', NULL, '2022-11-01 11:02:56', 0);
INSERT INTO `course` VALUES (3, '03', '英语', '03', 'admin', '2022-11-01 11:03:11', NULL, '2022-11-01 11:03:11', 0);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT COMMENT '主键ID',
  `sid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学号',
  `sname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '学生姓名',
  `sex` tinyint(1) UNSIGNED NULL DEFAULT 0 COMMENT '0: 未知; 1: 男; 2:女',
  `birthday` date NULL DEFAULT NULL COMMENT '生日',
  `image_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '头像',
  `create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `update_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  `is_delete` tinyint(1) NULL DEFAULT 0 COMMENT '是否有效: 0: 无效; 1有效',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '00', '果酱', 1, '2012-11-01', 'http:/oss.anyitech.ltd/ws_oss_service/storage/file_download/7318f730c05211ec92cf577b05c735c0.jpg', 'admin', '2022-11-01 10:31:19', 'admin', '2022-11-01 10:31:19', 0);
INSERT INTO `student` VALUES (2, '01', '赵倩倩', 1, '1990-01-01', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
INSERT INTO `student` VALUES (3, '02', '钱滴滴', 1, '1990-12-21', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
INSERT INTO `student` VALUES (4, '03', '孙芊芊', 1, '1990-05-20', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
INSERT INTO `student` VALUES (5, '04', '李芸芸', 1, '1990-08-06', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
INSERT INTO `student` VALUES (6, '05', '周五', 2, '1991-12-01', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
INSERT INTO `student` VALUES (7, '06', '吴优', 2, '1992-03-01', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
INSERT INTO `student` VALUES (8, '07', '郑板桥', 2, '1989-07-01', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
INSERT INTO `student` VALUES (9, '08', '王天一', 2, '1990-01-20', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);

-- ----------------------------
-- Table structure for student_course
-- ----------------------------
DROP TABLE IF EXISTS `student_course`;
CREATE TABLE `student_course`  (
  `id` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT COMMENT '主键id',
  `sid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学生编号',
  `cid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '课程编号',
  `score` int(255) NULL DEFAULT NULL COMMENT '得分',
  `create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `update_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  `is_delete` tinyint(1) NULL DEFAULT 0 COMMENT '是否有效: 0: 无效; 1有效',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student_course
-- ----------------------------
INSERT INTO `student_course` VALUES (1, '01', '01', 80, 'admin', '2022-11-01 11:10:56', NULL, '2022-11-01 11:10:56', 0);
INSERT INTO `student_course` VALUES (2, '01', '02', 90, 'admin', '2022-11-01 11:11:06', NULL, '2022-11-01 11:11:06', 0);
INSERT INTO `student_course` VALUES (3, '01', '03', 99, 'admin', '2022-11-01 11:11:17', NULL, '2022-11-01 11:11:17', 0);
INSERT INTO `student_course` VALUES (4, '02', '01', 70, 'admin', '2022-11-01 11:11:29', NULL, '2022-11-01 11:11:29', 0);
INSERT INTO `student_course` VALUES (5, '02', '02', 60, 'admin', '2022-11-01 11:11:43', NULL, '2022-11-01 11:11:43', 0);
INSERT INTO `student_course` VALUES (6, '02', '03', 80, 'admin', '2022-11-01 11:11:51', NULL, '2022-11-01 11:11:51', 0);
INSERT INTO `student_course` VALUES (7, '03', '01', 80, 'admin', '2022-11-01 11:12:06', NULL, '2022-11-01 11:12:06', 0);
INSERT INTO `student_course` VALUES (8, '03', '02', 80, 'admin', '2022-11-01 11:12:18', NULL, '2022-11-01 11:12:18', 0);
INSERT INTO `student_course` VALUES (9, '03', '03', 80, 'admin', '2022-11-01 11:12:24', NULL, '2022-11-01 11:12:24', 0);
INSERT INTO `student_course` VALUES (10, '04', '01', 50, 'admin', '2022-11-01 11:12:33', NULL, '2022-11-01 11:12:33', 0);
INSERT INTO `student_course` VALUES (11, '04', '02', 30, 'admin', '2022-11-01 11:12:41', NULL, '2022-11-01 11:12:41', 0);
INSERT INTO `student_course` VALUES (12, '04', '03', 20, 'admin', '2022-11-01 11:12:49', NULL, '2022-11-01 11:12:49', 0);
INSERT INTO `student_course` VALUES (13, '05', '01', 76, 'admin', '2022-11-01 11:12:58', NULL, '2022-11-01 11:12:58', 0);
INSERT INTO `student_course` VALUES (14, '05', '02', 87, 'admin', '2022-11-01 11:13:05', NULL, '2022-11-01 11:13:05', 0);
INSERT INTO `student_course` VALUES (15, '06', '01', 31, 'admin', '2022-11-01 11:13:20', NULL, '2022-11-01 11:13:20', 0);
INSERT INTO `student_course` VALUES (16, '06', '03', 34, 'admin', '2022-11-01 11:13:29', NULL, '2022-11-01 11:13:29', 0);
INSERT INTO `student_course` VALUES (17, '07', '02', 89, 'admin', '2022-11-01 11:13:39', NULL, '2022-11-01 11:13:39', 0);
INSERT INTO `student_course` VALUES (18, '07', '03', 98, 'admin', '2022-11-01 11:13:47', NULL, '2022-11-01 11:13:47', 0);

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `id` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT,
  `tid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '教师表 id',
  `tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '课程名称',
  `create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `update_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  `is_delete` tinyint(1) NULL DEFAULT 0 COMMENT '是否有效: 0: 无效; 1有效',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '01', '张三', 'admin', '2022-11-01 11:05:28', NULL, '2022-11-01 11:05:28', 0);
INSERT INTO `teacher` VALUES (2, '02', '李四', 'admin', '2022-11-01 11:08:57', NULL, '2022-11-01 11:08:57', 0);
INSERT INTO `teacher` VALUES (3, '03', '王五', 'admin', '2022-11-01 11:09:06', NULL, '2022-11-01 11:09:06', 0);

SET FOREIGN_KEY_CHECKS = 1;

# Mysql 查询训练
USE ken;
select * from ken.student;
select * from ken.teacher;
select * from ken.course;
select * from ken.student_course;

 执行完,检查下数据是否都正确插入:

1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

# 1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
SELECT
	s.sid '学生学号', s.sname '学生姓名',
	CASE s.sex
	WHEN 1 THEN
		'男'
	WHEN 2 THEN
		'女'
	ELSE
		'未知'
	END '性别',
a.score AS '语文分数', b.score AS '数学分数'
FROM
	student s
INNER JOIN student_course AS a ON s.sid = a.sid
AND a.cid = '01'
INNER JOIN student_course AS b ON s.sid = b.sid
AND b.cid = '02'
LEFT JOIN course c on a.cid = c.cid
WHERE a.score > b.score;
# Tip1:表的自联结 通常如果一张表中不同列之间的信息要对比的时候需要进行自联结,如本题中的"01"课程分数与"02"分数对比.

1.1 查询同时学习了" 01 “课程和” 02 "课程的学生

# 1.1 查询同时学习了" 01 “课程和” 02 "课程的学生
SELECT
	s.sname '学生姓名'
FROM
	student_course a
inner JOIN student_course b ON a.sid = b.sid
AND a.cid = '01'
AND b.cid = '02'
left join ken.student s on a.sid = s.sid;

# Tip1 ：表的自联结 通常如果一张表中不同列之间的信息要对比的时候需要进行自联结，如本题中的“01”课程与“02”分数对比。
# Tip2 ：INNER JOIN 如果要求两列信息同时存在的情况，需要inner join，它可以保证某列信息为null，则该行为空。

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为null)

# 1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为null)
SELECT
	*
FROM
	student_course a
LEFT JOIN student_course b ON a.Cid = '01'
AND a.Sid = b.Sid
AND b.Cid = '02';

# 1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
SELECT
	*
FROM
	student_course a
RIGHT JOIN student_course b ON a.Cid = '01'
AND a.Sid = b.Sid
AND b.Cid = '02';

 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

# 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select sc.sid,s.sname, avg(sc.score)  avgScore from student_course sc left join student s on sc.sid = s.sid group by sc.sid having avg(sc.score) >= 60;
select sc.sid,s.sname, avg(sc.score)  avgScore from student_course sc, student s where sc.sid = s.sid group by sc.sid having avg(sc.score) >= 60;
# Tip1 ：group by
# Tip2 ：执行顺序，from，where，group by having select order by limit
# Tip3 ：select 中的别名 having中可以使用。

 

 3. 查询在 SC 表存在成绩的学生信息

# 3. 查询在 SC 表存在成绩的学生信息
select * from student where sid in(select sc.sid from student_course sc where score is not null);
SELECT DISTINCT
	s.*
FROM
	student s
WHERE
	s.Sid IN (SELECT sc.Sid FROM student_course sc);
# Tip1 子查询，根据一个表中的信息来查找另一个表中的信息，这里记得查找的id的关键词要用in来包含。而非 =。
# Tip2 distinct 的使用

 

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null)

# 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null)
select  s.sid, s.sname, count(sc.sid), sum(score) from student s left join student_course sc on  s.sid = sc.sid group by s.sid;
-- 注意: 上面要用count(sc.sid), 要是用count(1)就错了
SELECT
	s.Sid,
	s.Sname,
	count(sc.Cid) AS course_count,
	sum(sc.score) AS score_sum
FROM
	student s
LEFT JOIN student_course sc ON s.Sid = sc.Sid
GROUP BY
	s.Sid;
# Tip1 left join 与 on 、where之间的关系，如何才能返回null

 6. 查询学过「张三」老师授课的同学的信息

# 6. 查询学过「张三」老师授课的同学的信息
select s.* from student s, student_course sc, course c, teacher t where s.sid = sc. sid and sc.cid = c.cid and c.tid = t.tid and t.tname ='张三';
SELECT
	s.*
FROM
	student s
WHERE
	s.Sid IN (
		SELECT
			sc.Sid
		FROM
			student_course sc inner join
			course AS c
ON
			sc.Cid = c.Cid
inner join
			teacher AS t

		ON c.Tid = t.Tid
		WHERE t.Tname = '张三'
	);

 

  7. 查询没有学全所有课程的同学的信息

# ☆☆☆ 7. 查询没有学全所有课程的同学的信息
SELECT
	s.*
FROM
	student s
WHERE
	s.Sid NOT IN (
		SELECT
			s.Sid
		FROM
			student s,
			student_course sc
		WHERE
			s.Sid = sc.Sid
		GROUP BY
			s.Sid
		HAVING
			count(1) = (SELECT count(1) FROM course)
	);
# 注意不能按下面这样写,会把没学的学生给漏掉,注意取反逻辑:
# select s.* from student_course sc, student s where sc.sid =s.sid group by sid having count(sc.cid)< (select count(1) from course);
# 可以换成左连接:
select s.* from student s left join student_course sc on sc.sid =s.sid group by sid having count(sc.cid)< (select count(1) from course);

 

 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

# ☆☆8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT s.* FROM student s WHERE s.Sid IN(
    select sc.sid from student_course sc where sc.cid in(select cid from student_course sc1 where sc1.sid = '01')
);

 

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

# ☆☆☆☆☆9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
explain SELECT s.* FROM student s WHERE s.Sid IN(
    select a.sid from (select sid ,group_concat(cid) courses from student_course sc group by sc.sid) a where a.courses =(
    select group_concat(cid) from student_course sc group by sc.sid having sc.sid ='01')
);

10. 查询没学过"张三"老师讲授的任一门课程的学生

# ☆☆☆☆10. 查询没学过"张三"老师讲授的任一门课程的学生
 SELECT s.sid, s.sname FROM student s WHERE s.Sid not IN(
     select sc.sid from student_course sc where sc.cid IN (
        select cid  from course c left join teacher t on c.tid = t.tid where t.tname ='张三')
);

SELECT
	s.Sname
FROM
	student s
WHERE
	s.Sid NOT IN (
		SELECT
			sc.Sid
		FROM
			student_course sc,
			course c,
			teacher t
		WHERE
			sc.Cid = c.Cid
		AND c.Tid = t.Tid
		AND t.Tname = '张三'
	);

 11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

# ☆☆☆11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
 SELECT s.sid, s.sname FROM student s WHERE s.Sid IN (
    select sid from student_course sc where sc.score < 60 group by sc.sid having count(1)>= 2
);
SELECT
	s.Sid,
	s.Sname,
	avg(sc.score) AS 平均成绩
FROM
	student s,
	student_course sc
WHERE
	s.Sid = sc.Sid
AND sc.score < 60
GROUP BY
	s.Sid
HAVING
	count(sc.score) >= 2;
# Tip 1、 from后可以派生虚拟表但是要在括号外给别名  2、聚合函数必须group by

 

12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

# 12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
 SELECT s.* FROM student s WHERE s.Sid IN (
    select sid from student_course sc where sc.cid = '01' and sc.score <60
);

SELECT s.*, sc.score FROM student s, student_course sc WHERE s.sid = sc.sid and sc.cid = '01' and sc.score <60 order by sc.score desc;

 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 ???

# 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 ???
select sc.sid, sum(sc.score), avg(sc.score) from student_course sc group by sc.sid order by avg(sc.score) desc;

SELECT sc1.score as score_01,sc2.score as score_02,sc3.score as score_03,avg(sc.score)as 平均成绩
FROM student_course sc,student_course sc1,student_course sc2,student_course sc3
WHERE sc.Sid = sc1.Sid
and sc.Sid = sc1.Sid
and sc.Sid = sc2.Sid
and sc.Sid = sc3.Sid
and sc1.Cid = '01'
and sc2.Cid = '02'
and sc3.Cid = '03'
GROUP BY sc.Sid
ORDER BY 平均成绩 desc;

 

 14. 查询各科成绩最高分、最低分和平均分

# ☆☆☆☆☆14. 查询各科成绩最高分、最低分和平均分：
select sc.cid, max(sc.score), min(sc.score), avg(sc.score) from student_course sc group by sc.cid;

SELECT
	c.Cid,
	c.Cname,
	max(sc.score) AS 最高分,
	min(sc.score) AS 最低分,
	avg(sc.score) AS 平均分,
	count(sc.sid) AS 选修人数,
sum(case when sc.score >=60 then 1 else 0 end)/count(sc.sid) as 及格率,
sum(case when sc.score >=70 and sc.score <=80 then 1 else 0 end)/count(sc.sid)as 中等率,
sum(case when sc.score >=80 and sc.score <=90 then 1 else 0 end)/count(sc.sid) as 优良率,
sum(case when sc.score >=90 and sc.score <=100 then 1 else 0 end)/count(sc.sid) as 优秀率
FROM
	course c,
	student_course sc
WHERE
	c.Cid = sc.Cid
GROUP BY
	c.Cid
ORDER BY
	选修人数 DESC,c.Cid asc;
# Tip 1.遇到区间或率的，想起来case when then else end  2.可以多重排序中间逗号相隔

 

 15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

# ☆☆☆☆☆15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
SELECT s.*, rank_01,rank_02,rank_03,rank_total FROM student s LEFT JOIN
(SELECT Sid,rank () over (PARTITION BY Cid ORDER BY score DESC) AS rank_01 FROM student_course sc WHERE Cid = '01') AS a ON a.Sid = s.Sid LEFT JOIN
(SELECT Sid,rank () over (PARTITION BY Cid ORDER BY score DESC) AS rank_02 FROM student_course sc WHERE Cid = '02') AS b ON b.Sid = s.Sid LEFT JOIN
(SELECT Sid,rank () over (PARTITION BY Cid ORDER BY score DESC) AS rank_03 FROM student_course sc WHERE Cid = '03') AS c ON c.Sid = s.Sid LEFT JOIN
(SELECT Sid,rank () over (ORDER BY avg(score) DESC) AS rank_total FROM student_course sc GROUP BY Sid) AS d ON d.Sid = s.Sid
ORDER BY rank_total ASC;
-- 5.7.24-log       Mysql 8.0之前是不支持 partition by 语法的。
select version();
# TIPs: 1、row_num()、rank()、dense_rank()等窗口分析函数的应用。2、select 连接字段(例sid),rank() over (partition by 字段1 order by 字段2 desc) as rank
#       3、自联结+rank()函数的结合运用。

# 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

#16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT
	Sid,
	sum(sc.score) AS 总成绩,
	rank () over (ORDER BY sum(sc.score) DESC) AS rank0
FROM
	student_course sc
GROUP BY
	Sid;
# 注意: 以上语法5.7版本是不支持的.
SELECT
	t.*, @rank := @rank + 1 AS 排名
FROM
	(SELECT @rank := 0) r,
	(
		SELECT
			sc.sid,
	        sum(sc.score) AS totalScore
		FROM
			student_course sc
		group by sc.sid
	) AS t order by  t.totalScore desc;

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

# 17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT c.cid,c.cname,
       count(sc.sid) as '选修人数',
concat(round(sum(CASE WHEN sc.score >= 85 THEN 1 ELSE 0 END) / count(sc.sid)*100,2),'%') AS '[85-100]%',
concat(round(sum(CASE WHEN sc.score >= 70 AND sc.score < 85 THEN 1 ELSE 0 END) / count(sc.sid)*100,2),'%') AS '[70-85)%',
concat(round(sum(CASE WHEN sc.score >= 60 AND sc.score < 70 THEN 1 ELSE 0 END) / count(sc.sid)*100,2),'%') AS '[60-70)%',
concat(round(sum(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) / count(sc.sid)*100,2),'%') AS '[0-60)%'
FROM course c, student_course sc
WHERE sc.cid = c.cid GROUP BY sc.cid;
# -- 舍掉小数点后几位: select concat(truncate(0.8799*100,2),'%')  结果 87.00%
# -- 四舍五入: select concat(round(0.8799*100,2),'%')  结果 88.00%
# -- 舍掉小数点后几位: select concat(floor(0.8799*100),'%')  结果 87%

 18、查询各科成绩前三名的记录

# 18、查询各科成绩前三名的记录
SELECT * FROM
(SELECT *, rank () over (PARTITION BY Cid ORDER BY score DESC) AS grade
FROM student_course sc) AS t
WHERE t.grade <= 3;
# 注意: 以上语法5.7版本是不支持的.
# 如果要对rank的结果进行进一步选择的话，采用结构select * from (select rank()) where 的句式
# partition by 是在保留所有记录的情况下，按字段顺序排序，类似于excel的筛选功能。

# 19. 查询每门课程被选修的学生数 [上面17 已经查出来]

# 20. 查询出只选修两门课程的学生学号和姓名
select s.sid, s.sname, count(cid) AS 'courseCount' from student_course sc, student s where sc.sid = s.sid group by sid having count(cid) = 2;

 

21. 查询男生、女生人数

# 21. 查询男生、女生人数
select CASE WHEN s.sex = 1 THEN '男' WHEN s.sex = 2 THEN '女' ELSE '未知' END as '性别', count(1) AS 'count' from student s group by s.sex;

22. 查询名字中含有「果」字的学生信息

#22. 查询名字中含有「果」字的学生信息
SELECT * FROM student WHERE Sname LIKE '%果%';

23. 查询同名同性学生名单，并统计同名人数

# 23. 查询同名同性学生名单，并统计同名人数
-- 2个名字一样的,所以 count(1)> 1
select Sname, count(1) from student group by Sname having  count(1)> 1;

24. 查询 1990 年出生的学生名单

# 24. 查询 1990 年出生的学生名单
select sname as '姓名', CASE WHEN s.sex = 1 THEN '男' WHEN s.sex = 2 THEN '女' ELSE '未知' END as '性别' from student s where year(birthday) = 1990;

 25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

# 25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT SC.cid, avg(sc.score) FROM student_course sc group by SC.cid ORDER BY avg(sc.score) DESC,SC.cid;

 

 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

# 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select s.sid, s.sname, avg(sc.score) from student_course sc left join student s on sc.sid = s.sid group by sc.sid having avg(sc.score) >=85;
SELECT
	s.Sid,
	s.Sname,
	AVG(sc.score) AS 平均成绩
FROM
	student s,
	student_course sc
WHERE
	s.Sid = sc.Sid
GROUP BY
	sc.Sid
HAVING
	平均成绩 >= 85;

 

 27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

# 27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select s.sname, sc.score from student_course sc, student s where sc.sid = s.sid and sc.cid =(
    select c.cid from course c where c.cname = '数学') and sc.score < 60;
SELECT
	s.Sname,
	sc.score
FROM
	student s,
	student_course sc,
	course c
WHERE
	s.Sid = sc.Sid
AND sc.Cid = c.Cid
AND c.Cname = '数学'
AND sc.score <= 60;

 

 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

# 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT DISTINCT
	s.sid, s.sname, c.cname, sc.score
FROM
	student s
LEFT JOIN student_course sc ON s.Sid = sc.Sid
LEFT JOIN course c ON sc.Cid = c.Cid order by s.sid;
# 要查询每一科的成绩所以不能用group by 分组, group by分组要排序的话 排序字段要是聚合函数,如avg(),sum(),max()...

 

 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

# 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select sc.sid, s.sname, c.cid, c.cname, sc.score FROM student_course sc,student s, course c where sc.sid = s.sid and sc.cid =c.cid and sc.score >70;

 

 30.查询有学生不及格的课程信息（课程id和课程名字）

# 30.查询有学生不及格的课程信息（课程id和课程名字）
select distinct sc.cid, c.cname from student_course sc, course c where sc.cid =c.cid and sc.score < 60;
select distinct sc.cid, c.cname from student_course sc right join course c on sc.cid =c.cid and sc.score < 60;
# 注: 上面即使是右连接也要使用 distinct

 

 31. 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名

# 31. 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
SELECT
	s.Sid,
	s.Sname
FROM
	student s,
	student_course sc
WHERE
	s.Sid = sc.Sid
AND sc.Cid = '01'
AND sc.score >= 80;

 

32. 查每门课程的学生人数

# 32. 查每门课程的学生人数
SELECT
	c.Cname,
	count(1) AS '选课人数'
FROM
	course c,
	student_course sc
WHERE
	c.Cid = sc.Cid
GROUP BY
	sc.Cid;

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

# 33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select sc.sid, s.sname, max(sc.score) from student_course sc, course c, teacher t, student s where sc.cid = c.cid and c.tid = t.tid and sc.sid = s.sid and t.tname = '张三';

SELECT
	s.*, sc.score
FROM
	student s,
	student_course sc,
	course c,
	teacher t
WHERE
	s.Sid = sc.Sid
AND c.Cid = sc.Cid
AND c.Tid = t.Tid
AND t.Tname = '张三'
HAVING
	sc.score = max(sc.score);
# 对于求最高或最低的情况两种思路 1. having score =max(score) 2.score = (select score from order by score desc limit 1) 。
# 对于求排名第二或其他情况的则取上面的第二种方法。

 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

# 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT
	sc.Sid, s.sname,
	sc.Cid, c.cname,
	sc.score
FROM
	student_course sc,
	student_course sc1,
	student s,
	course c
WHERE
	sc.sid = sc1.Sid and sc.sid =s.sid and sc.cid = c.cid
AND sc.Cid <> sc1.Cid
AND sc.score = sc1.score;

 

 36. 查询每门课程成绩最好的前两名

# 36. 查询每门课程成绩最好的前两名
SELECT * FROM(
SELECT *, rank () over (PARTITION BY Cid ORDER BY score DESC) AS grade
FROM student_course sc) AS t
WHERE t.grade < 3 ORDER BY Cid
# 以上窗口函数 5.7 不支持
-- 5.7.24-log
select version();
# 遇到选前几名的肯定要用窗口函数，谨记，如果对前几名有限制的select * from（select rank() over(partition by order by )as grade from ）as where grade 模式

37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）

# 37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）
SELECT
	cid,
	count(1)
FROM
	student_course sc
GROUP BY
	Cid
HAVING
	count(1) > 5;

38. 检索至少选修两门课程的学生学号

# 38. 检索至少选修两门课程的学生学号
SELECT
	Sid
FROM
	student_course
GROUP BY
	Sid
HAVING
	count(1) > 2;

39. 查询选修了全部课程的学生信息

# 39. 查询选修了全部课程的学生信息
SELECT
	s.sid, s.sname, count(sc.cid) num
FROM
	student s,
	student_course sc
WHERE
	s.Sid = sc.Sid
GROUP BY
	sc.Sid
HAVING
	num = (SELECT count(1) FROM course);

40. 查询各学生的年龄，只按年份来算

# 40. 查询各学生的年龄，只按年份来算
SELECT
	s.id, s.sname, YEAR (s.birthday), YEAR (now()) - YEAR (s.birthday) AS age
FROM
	student s;

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

# 41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT
	s.id, s.sname,  (
		CASE WHEN DAY (now()) < DAY (s.birthday) THEN
			YEAR (now()) - YEAR (s.birthday) - 1
		ELSE
			YEAR (now()) - YEAR (s.birthday)
		END
	) AS age
FROM
	student s;

SELECT
	s.id, s.sname,  (
    IF(DAY(now()) < DAY(s.birthday), YEAR(now()) - YEAR(s.birthday) - 1, YEAR(now()) - YEAR(s.birthday))
	) AS age
FROM
	student s;

42. 查询本周过生日的学生

# 42. 查询本周过生日的学生
SELECT
	s.id, s.sname, WEEK (now())
FROM
	student s
WHERE
	WEEK (s.birthday) = WEEK (now()) ;

43. 查询下周过生日的学生

# 43. 查询下周过生日的学生
SELECT
	s.id, s.sname, WEEK (now())+1 as 'nextWeek'
FROM
	student s
WHERE
	WEEK (s.birthday) = WEEK (now()) +1;

 44. 查询本月过生日的学生

# 44. 查询本月过生日的学生
SELECT
	s.id, s.sname, month (now())
FROM
	student s
WHERE
	month (s.birthday) = month (now()) ;

  结语:

最后推荐一款mysql 连接工具:  datagrip, 它 和idea 类似的设计,好用到爆!

一叶飘零_sweeeet
关注
专栏目录
mysql数据库部门表_MySQL数据库单表练习
weixin_35677065的博客
02-03 1161
练习1CREATE TABLE product(pid INT PRIMARY KEY,#主键IDpname VARCHAR(20),#商品名称price DOUBLE,#商品价格category_name VARCHAR(32)#商品分类名称);INSERT INTO product(pid,pname,price,category_name) VALUES(1,'联想电脑',5000,'电脑办...
Mysql 基础练习大全(建表到基本查询)​
07-27
### MySQL基础练习大全知识点概述 #### 一、查询语法示例 **1. SELECT语句的基本用法** - **定义**: `SELECT`语句用于从数据库中的表中选取数据。 - **示例**: 从`student`表中选择所有列。 ```sql SELECT * ...
MySQL单表查询练习
12-07
MySQL单表查询练习题
MySQL库表练习
JohnnyG2000的博客
04-20 1500
MySQL小练习准备工作1、创建数据库school，字符集为utf82、在school数据库中创建Student和Score表3、授权用户tom，密码mysql，能够从任何地方登录并管理数据库school。4、使用mysql客户端登录服务器，重置root密码 准备工作 [root@localhost ~]# vim /etc/my.cnf [root@localhost ~]# cat /etc/my.cnf # For advice on how to change settings please see
MYSQL单表练习
gx1469131213的博客
10-23 646
利用MYSQL完成单表操作
mysql数据库表练习_MySQL数据库单表练习
weixin_33621208的博客
02-04 1171
练习1CREATE TABLE product(pid INT PRIMARY KEY,#主键IDpname VARCHAR(20),#商品名称price DOUBLE,#商品价格category_name VARCHAR(32)#商品分类名称);INSERT INTO product(pid,pname,price,category_name) VALUES(1,'联想电脑',5000,'电脑办...
MySQL基础练习之north-wind 34题 建表导入
最新发布
08-02
MySQL基础练习之north-wind 34题 建表导入
mysql基础练习题（查询，建表，修改……）
06-13
### MySQL基础练习题详解 #### 1. 创建个人数据库 - **题目要求**：使用自己...以上是对给定MySQL基础练习题的详细解答，通过这些练习可以帮助初学者更好地理解和掌握SQL的基本操作，同时也能够提高实际应用中的技能。
mysql建表字段，相关内容 MySQL学习练习
07-27
在"mysqllearn-master"这个项目中，你可能会找到关于这些概念的实际示例和练习，通过实践加深对MySQL建表字段和相关概念的理解。通过编写和执行SQL脚本，你可以亲手创建表，体验这些特性在实际应用中的效果。此外，...
MySQL常用练手题目集合 MySQL查询练习题（50题） 共18页.pdf
08-21
MySQL查询练习题旨在帮助用户熟悉和提升SQL查询技巧，涵盖了多方面的数据库操作，如联接查询、聚合函数、条件查询、子查询等。以下是对这些题目的详细解析： 1. **建表**： - 学生表：可能包含学生ID、姓名、性别...
mysql 从简单到复杂练习
09-07
mysql从简单到复杂，熟练练习文档中语句，可以应对大多数面试
mysql操作练习的表数据
03-17
练习mysql操作的习题 还有答案 见链接 http://blog.csdn.net/qq_33665647/article/details/62888383
Mysql单表练习
J731814009的博客
12-10 508
Mysql单表练习
MySQL数据库基础练习
清醒云思的博客
08-11 219
本文介绍了如何使用SQL语言创建和操作不同的数据表。首先，使用USE语句选择要使用的数据库，例如使用名为myschool的数据库。然后，使用CREATE TABLE语句创建各种表格，如学生表、课程表、成绩表和年级表。每个表格都包含了不同的列和数据类型，并设置了一些约束条件。文章还给出了每个表格的具体创建语句。
mysql技能训练_Mysql 练习 总结
weixin_34734156的博客
01-20 171
insert into Course values ('9-888','高等数学','831');#---------------Teacher--添加信息----insert into Teacher values ('804','李成','男','1958-09-01','副教授','计算机系');insert into Teacher values ('856','张旭','男','1969...
MySql sql语句训练（一）
哦哦一人
09-28 318
一直在做前端的我某一天如果做后端那我能不能做好？ 一个这样的问题缠绕在我脑海中，于是我开始分析后端开发最需要的是什么 首先 我们应该知道web中为什么分前端和后端 前端，简单来说就是显示数据和样式，把数据库的数据直接展示到页面。 而后端就是处理数据 操作数据库。 而这段话中最重要的一点就是----数据 前端的数据是需要数据库和后端支持，后端的数据也是需要数据库来支持 而对于我来说，只要对数据库的操作到位，那我也是一个合格的后端开发 （以上理解仅为我个人的理解，可能不对，但是对我来说就是这个意思） 但对于我
MySQL SQL100道基础练习题
walk on the road of linux
07-06 2147
MySQL 基础练习104道练习题，附带答案
MySQL练习题
2301_81097039的博客
01-19 1980
-1.学生表--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表--CId 课程编号,Cname 课程名称,TId 教师编号--3.教师表--TId 教师编号,Tname 教师姓名--4.成绩表--SId 学生编号,CId 课程编号,score 分数。
MySQL入门练习1
ZZU_LGY
04-26 833
写数据库的题多是一件美事 1.查找最大值 题目链接 # 方法一 SELECT max(gpa) FROM user_profile WHERE university = '复旦大学'; # 方法二 SELECT gpa FROM user_profile WHERE university = "复旦大学" ORDER BY gpa DESC LIMIT 1; 2.计算男生人数以及平均GPA 题目链接 SELECT COUNT(gender) AS male_num, round(AVG(gpa),1
SQL教程：MySQL数据库建表、查询与修改操作
本篇文档是关于MySQL数据库操作的练习作业，主要涵盖了建表、查询和修改等基本操作。首先，文档开始部分设置了一些SQL模式参数，如字符集为UTF-8，关闭了默认的唯一性和外键检查，以及设置了SQL模式为"NO_AUTO_VALUE...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值