1. 首页
  2. 文章中心
  3. Mysql

Mysql | sql语句练习50题（丰富+解析+拓展）

74 阅读 0 评论
我是靠谱客的博主 任性冬瓜，最近开发中收集的这篇文章主要介绍Mysql | sql语句练习50题（丰富+解析+拓展），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一.数据库基础表

--学生表
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL COMMENT '学生学号',
  `s_name` varchar(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
  `s_birth` varchar(20) NOT NULL DEFAULT '' COMMENT '出生年月',
  `s_sex` varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--课程表
CREATE TABLE `course` (
  `c_id` varchar(20) NOT NULL COMMENT '课程编号',
  `c_name` varchar(20) NOT NULL DEFAULT '' COMMENT '课程名称',
  `t_id` varchar(20) NOT NULL COMMENT '教师工号',
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--教师表
CREATE TABLE `teacher` (
  `t_id` varchar(20) NOT NULL COMMENT '教师工号',
  `t_name` varchar(20) NOT NULL DEFAULT '' COMMENT '教师姓名',
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--成绩表
CREATE TABLE `score` (
  `s_id` varchar(20) NOT NULL COMMENT '学生学号',
  `c_id` varchar(20) NOT NULL COMMENT '课程编号',
  `s_score` int(3) DEFAULT NULL COMMENT '成绩分数',
  PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二.基础表及基础数据

/*
Source Database       : testsql

Target Server Type    : MYSQL
Target Server Version : 50728
File Encoding         : 65001

Date: 2020-09-08 19:09:28
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` varchar(20) NOT NULL COMMENT '课程编号',
  `c_name` varchar(20) NOT NULL DEFAULT '' COMMENT '课程名称',
  `t_id` varchar(20) NOT NULL COMMENT '教师工号',
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('KC001', '语文', 'JS002');
INSERT INTO `course` VALUES ('KC002', '数学', 'JS001');
INSERT INTO `course` VALUES ('KC003', '英语', 'JS003');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `s_id` varchar(20) NOT NULL COMMENT '学生学号',
  `c_id` varchar(20) NOT NULL COMMENT '课程编号',
  `s_score` int(3) DEFAULT NULL COMMENT '成绩分数',
  PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('XS2020001', 'KC001', '80');
INSERT INTO `score` VALUES ('XS2020001', 'KC002', '90');
INSERT INTO `score` VALUES ('XS2020001', 'KC003', '99');
INSERT INTO `score` VALUES ('XS2020002', 'KC001', '70');
INSERT INTO `score` VALUES ('XS2020002', 'KC002', '60');
INSERT INTO `score` VALUES ('XS2020002', 'KC003', '80');
INSERT INTO `score` VALUES ('XS2020003', 'KC001', '80');
INSERT INTO `score` VALUES ('XS2020003', 'KC002', '80');
INSERT INTO `score` VALUES ('XS2020003', 'KC003', '80');
INSERT INTO `score` VALUES ('XS2020004', 'KC001', '50');
INSERT INTO `score` VALUES ('XS2020004', 'KC002', '30');
INSERT INTO `score` VALUES ('XS2020004', 'KC003', '20');
INSERT INTO `score` VALUES ('XS2020005', 'KC001', '76');
INSERT INTO `score` VALUES ('XS2020005', 'KC002', '87');
INSERT INTO `score` VALUES ('XS2020006', 'KC001', '31');
INSERT INTO `score` VALUES ('XS2020006', 'KC003', '34');
INSERT INTO `score` VALUES ('XS2020007', 'KC002', '89');
INSERT INTO `score` VALUES ('XS2020007', 'KC003', '98');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL COMMENT '学生学号',
  `s_name` varchar(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
  `s_birth` varchar(20) NOT NULL DEFAULT '' COMMENT '出生年月',
  `s_sex` varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('XS2020001', '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES ('XS2020002', '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES ('XS2020003', '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES ('XS2020004', '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES ('XS2020005', '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES ('XS2020006', '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES ('XS2020007', '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES ('XS2020008', '王菊', '1990-01-20', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `t_id` varchar(20) NOT NULL COMMENT '教师工号',
  `t_name` varchar(20) NOT NULL DEFAULT '' COMMENT '教师姓名',
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('JS001', '张三');
INSERT INTO `teacher` VALUES ('JS002', '李四');
INSERT INTO `teacher` VALUES ('JS003', '王五');

三.例题题目解析

3.1---- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT
  a.*,
  b.`s_score` AS KC001_score,
  c.`s_score` AS KC002_score
FROM
  student AS a
  LEFT JOIN score AS b
    ON a.`s_id` = b.`s_id`
    AND b.`c_id` = 'KC001'
  LEFT JOIN score AS c
    ON a.`s_id` = c.`s_id`
    AND c.`c_id` = 'KC002'
WHERE b.`s_score` > c.`s_score`

在这里插入图片描述

拓展一：别名_AS
1.使用别名可加 AS ，也可以不加
2.在oracle中，数据表别名不能加 AS
3.表别名只在执行查询时使用，并不在返回结果中显示，而列定义别名之后，将返回给客户端显示，显示的结果字段为字段列的别名

拓展二：连接_JOIN
1.内连接（INNER JOIN）是A表的所有行交上B表的所有行得出的结果集;
在这里插入图片描述
2.左外连接是A表的所有行匹配上B表得出的结果集;
在这里插入图片描述
3.右外连接是B表的所有行匹配上A表得出的结果集
在这里插入图片描述
4.全连接是A表的所有行并上B表的所有行得出的结果集
在这里插入图片描述

3.2---- 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT
  a.*,
  b.`s_score` AS KC001_score,
  c.`s_score` AS KC002_score
FROM
  student AS a
  LEFT JOIN score AS b
    ON a.`s_id` = b.`s_id`
    AND b.`c_id` = 'KC001'
  LEFT JOIN score AS c
    ON a.`s_id` = c.`s_id`
    AND c.`c_id` = 'KC002'
WHERE b.`s_score` < c.`s_score`

在这里插入图片描述

3.3---- 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
  a.`s_id`,
  a.`s_name`,
  ROUND (AVG (b.s_score), 2) AS avg_score
FROM
  student AS a
  LEFT JOIN score AS b
    ON a.`s_id` = b.`s_id`
GROUP BY a.`s_id`,
  a.`s_name`
HAVING avg_score > 60;

拓展一：AVG
1.AVG 用来求平均值

拓展二：HAVING、GROUP BY
1.HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集

拓展三：
1.由于本人是根据例题和相关的答案比对练习的，当句子写到group by 前只能查出一条数据；
在这里插入图片描述
2.加上 group by 后呈现全部数据在这里插入图片描述
3.仔细观察后，可以看出，首次的查询显示的是整个“求平均”后的分值，默认显示的编码和姓名分别是第一条。加上group by 才会进行分组，这一点不仅会在 avg 中，count等函数中也会出现。

3.4---- 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SELECT
  a.s_id,
  a.s_name,
  ROUND (AVG(IFNULL(b.s_score, 0)),2) AS avg_score
FROM
  student a
  LEFT JOIN score b
    ON a.s_id = b.s_id
GROUP BY s_id
HAVING avg_score < 60;

在这里插入图片描述

拓展一：ifnull（a，b）
1.如果IFNULL(a,b)，a接收的值为null，则返回b，否则返回a；
2.以下sql语句，大家可以预测下结果，按照IFNULL函数的作用，应该返回0才对，可是结果并不是这样。
SELECT IFNULL(score,0) FROM student WHERE ID = 4;
返回结果，居然是null，与预期的结果0不一致。
3.以下语句返回正确结果0；
SELECT IFNULL((SELECT score FROM student WHERE ID = 4),0);

3.5---- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT
  a.`s_id`,
  a.`s_name`,
  COUNT(b.`s_id`) AS KCSUM,
  IFNULL(SUM(b.`s_score`), 0) AS SCSUM
FROM
  student AS a
  LEFT JOIN score AS b
    ON a.`s_id` = b.`s_id`
GROUP BY a.`s_id`

在这里插入图片描述

拓展一：sql规范

在sqlyog和Navicat中，进行sql书写，在navicat中运行正常，在sqlyog中和服务器中无法运行。最终发现是sql书写的问题，sum与（）中间有个空格，运行出错

3.6----查询"李"姓老师的数量
SELECT COUNT(a.t_id) AS 教师个数 FROM teacher AS a WHERE t_name LIKE '李%';

最后

以上就是任性冬瓜为你收集整理的Mysql | sql语句练习50题（丰富+解析+拓展）的全部内容，希望文章能够帮你解决Mysql | sql语句练习50题（丰富+解析+拓展）所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(49)

相关文章

在ACCES 中如何使用 CASE 语句
在ACCES 中如何使用 CASE 语句
Python标准库：内置函数range(stop) range(start, stop[, step])
Python标准库：内置函数range(stop) range(start, stop[, step])
wordfile.uew UE语法着色文件
wordfile.uew UE语法着色文件
IIF函数
IIF函数
Mysql | sql语句练习50题（丰富+解析+拓展）
Mysql | sql语句练习50题（丰富+解析+拓展）
Access中替代case when的方法
Access中替代case when的方法
access中case when end 的使用
access中case when end 的使用
e.计算列表长度,并输出./用一条语句完成多个条件判断和赋值操作
e.计算列表长度,并输出./用一条语句完成多个条件判断和赋值操作

评论列表共有 0 条评论

立即
投稿
返回
顶部