MySQL练习（1）

最新推荐文章于 2022-02-12 15:14:53 发布
最新推荐文章于 2022-02-12 15:14:53 发布
阅读量414 收藏 4
点赞数
分类专栏： MySQL 文章标签： MySQL 面试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38426554/article/details/98441058
版权

说明： 本文是本人的练习sql的学习笔记，sql自己写的，并且通过对比网上别人的sql来学习多种方式实现，锻炼自己多种解法的能力，并学习别人的语句的优美的地方。题目等都来自这里，https://blog.csdn.net/fashion2014/article/details/78826299

原理

执行顺序

  1. from自居组装来自不同数据源的数据；
  2. where子句基于指定的条件堆记录行进行帅选
  3. group by 子句将数据划分为多个分组
  4. 使用聚集函数进行计算
  5. 使用having子句帅选分组；
  6. 计算所有的表达式
  7. select所有的字段
  8. order by堆结果集进行排序

UNION用法

union的用法，也就是UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
函数：group_concat()，手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果。

CASE WHEN用法

CASE WHEN condition THEN result 
WHEN condition THEN result 
.............
[WHEN ...] 
[ELSE result] 
END

CASE 子句可以用于任何表达式可以有效存在的地方。 condition 是一个返回boolean 的表达式。 如果结果为真，那么 CASE 表达式的结果就是符合条件的 result。 如果结果为假，那么以相同方式搜寻任何随后的 WHEN 子句。 如果没有 WHEN condition 为真，那么 case 表达式的结果就是在 ELSE 子句里的值。 如果省略了 ELSE 子句而且没有匹配的条件， 结果为 NULL。
或其语法为：
简单Case函数
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘2’ THEN ‘女’
ELSE ‘其他’ END

定义用户变量的方式：select @变量名

对用户变量赋值有两种方式，一种是直接用"=“号，另一种是用”:="号。其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用；当使用select语句对用户变量进行赋值时，只能使用":=“方式，因为在select语句中，”="号被看作是比较操作符
(@i:=@i+1) 也可以写成 @i:=@i+1 ，加括号是为了视觉上看这结构更清楚些。在定义好一个变量后每次查询都会给这个变量自增，而我们每次执行查询语句获取结果后就不需要这个变量自增了，所以要把它重置为0，在表名后用逗号分格下使用 (SELECT @i:=0) as i 就可以了，说下这个as i为什么要这样用，是因为派生表必须需要一个别名，这个就是做它的别名，可以任意字符。

练习

建表语句：

课程表
CREATE TABLE `course`  (
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

老师表
CREATE TABLE `teacher`  (
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

学生表
CREATE TABLE `student`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

成绩表
CREATE TABLE `score`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_score` int(3) DEFAULT NULL,
  PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
测试数据
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');


INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20', '女');


INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '02', 89);

INSERT INTO score VALUES (‘07’, ‘03’, 98);

course
图片
student
图片
成绩表score
图片
老师表 teacher
图片

建议拿张纸把每张表的关系记录下，这样方便写sql的时候思考，如下图

图片

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

– 思路： 这里要理解01课程和02课程都是同一样表，也就是说如果要比较两者的话，得建两个一样的表才能比较

-- 法一
SELECT
	s.*,
	s1.s_score AS "01_score",
	s2.s_score AS "02_score" 
FROM
	student s
	inner JOIN score s1 ON s1.s_id = s.s_id 
	AND s1.c_id = "01"
	LEFT JOIN score s2 ON s2.s_id = s.s_id 
	AND s2.c_id = "02" 
WHERE
	s1.s_score > s2.s_score
-- 法二
SELECT
	s.*,
	s1.s_score AS "01_score",
	s2.s_score AS "02_score" 
FROM
	student s,
	score s1,
	score s2 
WHERE
	s.s_id = s1.s_id 
	AND s.s_id = s2.s_id 
	AND s1.c_id = "01" 
	AND s2.c_id = "02" 
	AND s1.s_score > s2.s_score

结果：
图片

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

这里第一个用inner join 也就是匹配只有学生信息有的数据才显示，第二个则必须用left join，即没匹配的也要显示出来;

SELECT
	s.*,
	s1.s_score AS "01_score",
	s2.s_score AS "02_score" 
FROM
	student s
	inner JOIN score s1 ON s1.s_id = s.s_id 
	AND s1.c_id = "01"
	LEFT JOIN score s2 ON s2.s_id = s.s_id 
	AND s2.c_id = "02" 
WHERE
	s1.s_score < s2.s_score

结果：
图片

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

注意，使用聚合函数的时候，必须要有group by 分组,执行顺序是先分组再过滤，
– having的执行顺序在group by之后，而where执行顺序在group by 之前，而我们是要分组要统计出来的平均值进行比较大于60才显示

select s.s_id,s.s_name, ROUND(AVG(s1.s_score),2)  as s_avg
from student s 
inner  join score s1 on s1.s_id = s.s_id
group by s.s_id
having
s_avg >= 60

结果：
图片

4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩,测试数据中t_id为8的没有成绩

法一：
注意： 这里用到了union的用法，也就是UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

select s.s_id,s.s_name, ROUND(AVG(s1.s_score),2)  as s_avg
from student s 
inner  join score s1 on s1.s_id = s.s_id
group by s.s_id
having
s_avg < 60 
union 
(select s.s_id,s.s_name, '0'  as s_avg
from student s 
where s.s_id not in(select s_id from score ))
法二(相对于法一来说更加简洁)：
SELECT
	s.s_id,
	s.s_name,
	ROUND( IFNULL( AVG( sc.s_score ), 0 ), 2 ) AS s_avg 
FROM
	student s
	LEFT JOIN score sc ON sc.s_id = s.s_id 
GROUP BY
	s.s_id 
HAVING
s_avg < 60

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
注意这里不能用inner join 去连接哦，因为是所有学生，08号学生是没有课程，所以用innerjoin的话，只有匹配到的数据才显示

SELECT
	s.s_id,
	s.s_name,
	count( s1.c_id ) AS sum_course,
	SUM( s1.s_score ) AS sum_score 
FROM
	student s
	LEFT JOIN score s1 ON s1.s_id = s.s_id 
GROUP BY
	s.s_id

结果：
图片

6、查询"李"姓老师的数量

思路，也就是统计出匹配后出来的t_id

SELECT
	count( t_id ) 
FROM
	teacher t 
WHERE
	t.t_name LIKE "张%"

图片

7、查询学过"张三"老师授课的同学的信息

解法一： 以老师授课为主表进行关联

SELECT
	s.* 
FROM
	teacher t
	INNER JOIN course c1 ON c1.t_id = t.t_id
	INNER JOIN score s1 ON s1.c_id = c1.c_id
	INNER JOIN student s ON s.s_id = s1.s_id 
WHERE
	t.t_name = "张三"

解法二： 以学生为主表进行关联

SELECT
	a.* 
FROM
	student a
	JOIN score b ON a.s_id = b.s_id 
WHERE
	b.c_id IN ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三' ) );

图片

8、查询没学过"张三"老师授课的同学的信息

思路： 只要我们找出张三老师教过那些学生，然后在在学生表中排除这些学生，剩下的就是没学过张三老师的学生；----》 变成我们要找出张三老师教过哪些学生的问题，也就是我们可以课程表和老师表关联，找出张三老师教学的课程 c_id------》在成绩表中选出等于该课程的学生id
-------》 学生表中排除选出的这些学生id

SELECT
	s.* 
FROM
	student s 
WHERE
	s.s_id NOT IN (
SELECT
	s1.s_id 
FROM
	score s1 
WHERE
	c_id = ( SELECT c_id FROM course c1  JOIN teacher t ON t.t_id = c1.t_id WHERE t.t_name = "张三" ) 
	)

图片

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

思路： 这里课程有两个条件，并且都是一个字段的问题，那么就要有两个相同的表，我们才可以进行比较；
解法1：

SELECT
	st.* 
FROM
	student st
	INNER JOIN score sc1 ON sc1.s_id = st.s_id
	LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
WHERE
	sc1.c_id = "01" 
	AND sc2.c_id = "02"

解法2

select a.* from 

student a,score b,score c 

where a.s_id = b.s_id  and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';

图片

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

思路： 好像如果是都有的话，我们可以用表连接，如果不是都有的话，那么不能用表连接，而是通过条件判断来获取的

SELECT DISTINCT
	st.* 
FROM
	student st 
WHERE
  st.s_id IN ( SELECT sc1.s_id FROM score sc1 WHERE sc1.c_id = "01" )                   
  AND st.s_id NOT IN ( SELECT sc.s_id FROM score sc WHERE sc.c_id IN ( "02" ) )

图片

CodeBoy975
关注
专栏目录
Mysql练习1
zl962025459的博客
04-28 488
Myslq练习 mysql 语句练习，熟能生巧，今天在网上搜集了一套题目，这里就开始记录我练习的一点一滴。 网上搜的题目 参考链接：https://www.cnblogs.com/SJP666/p/4665605.html 话不多说，开始 题目：ps都能看到答案，我们先抄下来，闭卷考试 表1 Student（学生表） 属性名 数据类型 可否为空 含义- Sno char(3) 否 ...
MySQL练习（01）
qq_43677691的博客
02-25 170
MySQL练习（01） 题目来源：https://sqlzoo.net/   在学了将近一个月的Java编程后，老师推荐了一个网址：https://sqlzoo.net/，说让我们自己练习MySQL。   在网站里“溜达”了一圈后，感觉这个网站确实不错！首先，里面有许多题目可供练习，从最基础的增删改查到多表操作…若果能练习完里面的所有题目，那么你就可以在MySQL领域称霸了！其次，里面的数据是真实的，全部来源于现实。最后，网站中的数据来源非常广泛，社会、人文等应有尽有，在这个网站中学习MySQL,不仅能学
MYSQL基础练习(一)
m0_49197858的博客
02-12 410
配合基础知识入门(一)使用
3-2 面向对象中级练习题
weixin_30378311的博客
03-26 160
练习 练习1：定义MySQL类 要求： 1.对象有id、host、port三个属性 2.定义工具create_id，在实例化时为每个对象随机生成id，保证id唯一 3.提供两种实例化方式，方式一：用户传入host和port 方式二：从配置文件中读取host和port进行实例化 4.为对象定制方法，save和get_obj_by_id，save能自动将对象序列化到文件中，文件路径为配置文...
mysql练习1
marcosyw的博客
12-15 380
1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名 name   kecheng   fenshu 张三    语文       81 张三     数学       75 李四     语文       76 李四     数学       90 王五     语文       81 王五     数学       100 王五     英语       90 法1:
MySql练习（一）
weixin_45338578的博客
09-06 741
目录 一、第一题 二、第二题 一、第一题 1.创建test数据库 2.在test数据库中创建student表，表结构如下图所示(id设置为主键自增长) sql语句：create table student ( id int PRIMARY key auto_increment, name varchar(20), score INT, address VARCHAR(50), useremail VARCHAR(20)...
MySql练习1：创建数据库表news.zip
09-03
在这个"MySql练习1：创建数据库表news.zip"中，我们将探讨如何创建一个名为`news`的数据库表。MySQL是一种关系型数据库管理系统，它允许用户通过SQL（结构化查询语言）来存储、检索和管理数据。 首先，让我们了解...
MySql练习1：数据库设计.zip
09-03
在这个"MySQL练习1：数据库设计"的压缩包中，我们可以预见到一系列关于如何有效设计和构建MySQL数据库的练习。数据库设计是数据库管理的关键组成部分，它涉及到数据模型的创建、表结构的设计以及关系的建立。 在...
MySql练习1：批量插入成绩数据.zip
09-03
MySql练习1：批量插入成绩数据.zip,MySql练习1：批量插入成绩数据.zipMySql练习1：批量插入成绩数据.zipMySql练习1：批量插入成绩数据.zipMySql练习1：批量插入成绩数据.zip
MySQL练习1 参考答案
04-23
MySQL练习1 参考答案 MySQL练习1 参考答案 MySQL练习1 参考答案
MySql练习1：使用纯Java方式连接数据库.zip
09-03
1. **导入必要的库**：在Java源代码中，我们需要导入JDBC相关的类。这通常包括`java.sql.Connection`，`java.sql.DriverManager`，`java.sql.PreparedStatement`和`java.sql.ResultSet`等。例如： ```java import ...
数据库学习笔记和小练习（1）数据定义
pusude的博客
11-03 686
（数据库系统概论第三章学习笔记和小练习） 关系数据库系统支持三级模式结构，其模式，外模式，内模式中的基本对象有模式，表，视图和索引等，因此SQL的数据定义功能包括模式定义、表定义、视图和索引的定义。                   创建                           删除                          修改 模式： create
2021-10-21
weixin_48691931的博客
10-21 772
数据库分组及多表查询 create database ST default character set=utf8 use ST 1、运行老师给出的脚本，创建实验所需的数据库ST，在ST数据库中创建教师表teacher。要求：teacher表包括tid(教师编号)、tname(教师姓名)、sex(性别)、title(职称)，其中tid为int 类型主键，自动增长，tname为非空约束,sex 只能取“男”或“女”，默认值为“男”,职称取值为“教授”、“高级工程师”、“副教授”、“工程师”、“讲师”、“助教”,
mysql练习题1：查询“李”姓老师的基本信息
qq_43824618的博客
03-03 6369
查询表名为“teacher”的“李”姓老师的基本信息； select * from teacher name like "李%"; *符号表示所有，%符号表示通匹； 这样的模糊匹配用 “like” 表示像，一定不能用 “=”。 ...
-- 2、查询“01“课程比“02“课程成绩低的学生的信息及课程分数
Ele_sorriu的博客
10-04 1万+
SELECT st.*, sc.s_score AS '语文', sc2.s_score AS '数学' FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01' LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02' WHERE sc.s_score < sc2.s_score
人工智能面试题
weixin_45192754的博客
07-17 2472
面试内容： 1.yield 的作用？ yield就是保存单签程序执行状态。你使用for循环的时候，每次取一个元素的时候就会计算一次。使用yield的函数叫generator,和iterator一样，他的好处是不用一次计算所有的元素，而是用一次计算一次，可以节省很多空间，generator每次计算需要上一次计算结果，所以用yield，否则使用return，上次的计算结果就没有了。 2.什么是协程？ ...
查询选修了全部课程的学生姓名
L1ak的博客
03-11 5421
首先头脑中有三点概念： 1 。 EXISTS 子查询找到的提交 NOT EXISTS 子查询中 找不到的提交 说明：不要去翻译为存在和不存在，把脑袋搞晕。 2 。 建立程序循环的概念，这是一个动态的查询过程。如 FOR循环 。 3 。 Exists执行的流程Exists首先执行外层查询，再执行内存查询，与IN相反。 流程为首先取出外 层中的第 一...
SQL练习：查询各科成绩前三名的记录
热门推荐
CodeBoy975
08-28 2万+
先给结果sql，再讲思路： SELECT a.s_id, a.c_id, a.s_score FROM score a LEFT JOIN score b ON a.c_id = b.c_id AND a.s_score <= b.s_score GROUP BY a.s_id, a.c_id, a.s_score HAVING COUNT( b.s_id ) &...
MySQL基础查询练习与解答
“MySQL基础练习题及答案，涵盖了从基础查询到复杂的联查和函数应用，适合初学者和有一定基础的学习者进行练习。” 在MySQL数据库管理中，查询是核心操作之一，本练习题旨在帮助学习者熟悉SQL语句的编写，包括选择...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值