AI 助理
备案控制台
开发者社区 数据库 文章 正文

20000字干货笔记，一天搞定Mysql~（中）

2022-05-08 154
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 今天给大家分享的是我学习Mysql记录的详细笔记，有基础知识，也有实战案例

三表关联查询


查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree 。


只有score表中关联学生的学号和课堂号，我们只要围绕着score这张表查询就好了。


SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+


只要把 s_no 和 c_no 替换成 student 和 course 表中对应的 name 字段值就好了。


首先把 s_no 替换成 student 表中的 name 字段：


SELECT name, c_no, degree FROM student, score WHERE student.no = score.s_no;
+-----------+-------+--------+
| name      | c_no  | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 赵铁柱    | 3-105 |     76 |
| 赵铁柱    | 3-245 |     68 |
| 赵铁柱    | 6-166 |     81 |
+-----------+-------+--------+


再把c_no替换成course表中的name字段：


-- 课程表
SELECT no, name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+
-- 由于字段名存在重复，使用 "表名.字段名 as 别名" 代替。
SELECT student.name as s_name, course.name as c_name, degree
FROM student, score, course
WHERE student.NO = score.s_no
AND score.c_no = course.no;


子查询加分组求平均分


查询95031班学生每门课程的平均成绩。


在score表中根据student表的学生编号筛选出学生的课堂号和成绩：


-- IN (..): 将筛选出的学生号当做 s_no 的条件查询
SELECT s_no, c_no, degree FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031');
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+


这时只要将c_no分组一下就能得出95031班学生每门课的平均成绩：


SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031')
GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+


子查询 - 1


查询在 3-105 课程中，所有成绩高于 109 号同学的记录。


先用子查询查找出109同学在3-105中的成绩


select * from score
where c_no ='3-105' and s_no='109'


然后再以课程3-105为条件，查找成绩大76的记录


select * from score
where c_no = '3-105'
and degree>
(select degree from score
where c_no = '3-105' and s_no='109');


子查询 - 2

查询所有成绩高于 109 号同学的 3-105 课程成绩记录。


-- 不限制课程号，只要成绩大于109号同学的3-105课程成绩就可以。
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');


YEAR 函数与带 IN 关键字查询


查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列。


select no, name, birthday from student
where year(birthday) in (
select year(birthday) from student
where no in (101,108));


多层嵌套子查询

查询 '张旭' 教师任课的学生成绩表。


用的三张表teacher、course、score，首先找到教师编号：


SELECT NO FROM teacher WHERE NAME = '张旭'


通过sourse表找到该教师课程号：


select no from course
where t_no = (SELECT NO FROM teacher WHERE NAME = '张旭')


通过筛选出的课程号查询成绩表：


select * from score where c_no = (
select no from course where t_no = (
select no from teacher
where name = '张旭'));


多表查询

查询某选修课程多于5个同学的教师姓名。


首先在teacher表中，根据no字段来判断该教师的同一门课程是否有至少5名学员选修：


-- 查询 teacher 表
SELECT no, name FROM teacher;
+-----+--------+
| no  | name   |
+-----+--------+
| 804 | 李诚   |
| 825 | 王萍   |
| 831 | 刘冰   |
| 856 | 张旭   |
+-----+--------+
SELECT name FROM teacher WHERE no IN (
-- 在这里找到对应的条件
);


查看和教师编号有有关的表的信息：


SELECT * FROM course;
-- t_no: 教师编号
+-------+-----------------+------+
| no    | name            | t_no |
+-------+-----------------+------+
| 3-105 | 计算机导论      | 825  |
| 3-245 | 操作系统        | 804  |
| 6-166 | 数字电路        | 856  |
| 9-888 | 高等数学        | 831  |
+-------+-----------------+------+


我们已经找到和教师编号有关的字段就在course表中，但是还无法知道哪门课程至少有5名学生选修，所以还需要根据score表来查询：


-- 在此之前向 score 插入一些数据，以便丰富查询条件。
INSERT INTO score VALUES ('101', '3-105', '90');
INSERT INTO score VALUES ('102', '3-105', '91');
INSERT INTO score VALUES ('104', '3-105', '89');
-- 查询 score 表
SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+
-- 在 score 表中将 c_no 作为分组，并且限制 c_no 持有至少 5 条数据。
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5;
+-------+
| c_no  |
+-------+
| 3-105 |
+-------+


根据筛选出来的课程号，找出在某课程中，拥有至少5名学员的教师编号：


SELECT t_no FROM course WHERE no IN (
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
);
+------+
| t_no |
+------+
| 825  |
+------+
在teacher表中，根据筛选出来的教师编号找到教师姓名：SELECT name FROM teacher WHERE no IN (    
        -- 最终条件    
        SELECT t_no FROM course WHERE no IN (
        SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5    
        )
    );


子查询 - 3


查询 “计算机系” 课程的成绩表。


思路是，先找出teacher表中所有计算机系课程的编号，根据这个编号查询course表中的课程编号，再用课程编号查找score表


-- 通过 teacher 表查询所有 `计算机系` 的教师编号
SELECT no, name, department FROM teacher WHERE department = '计算机系'
+-----+--------+--------------+
| no  | name   | department   |
+-----+--------+--------------+
| 804 | 李诚   | 计算机系     |
| 825 | 王萍   | 计算机系     |
+-----+--------+--------------+
-- 通过 course 表查询该教师的课程编号
SELECT no FROM course WHERE t_no IN (
    SELECT no FROM teacher WHERE department = '计算机系'
);
+-------+
| no    |
+-------+
| 3-245 |
| 3-105 |
+-------+
-- 根据筛选出来的课程号查询成绩表
SELECT * FROM score WHERE c_no IN (
    SELECT no FROM course WHERE t_no IN (
        SELECT no FROM teacher WHERE department = '计算机系'    
    )
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+


UNION 和 NOT IN 的使用


查询计算机系与电子工程系中的不同职称的教师。


+-----+------+-----+------------+------------+------------+
| no  | name | sex | birthday   | profession | department |
+-----+------+-----+------------+------------+------------+
| 804 | 李诚 | 男  | 1958-12-02 | 副教授     | 计算机系   |
| 825 | 王萍 | 女  | 1972-05-05 | 助教       | 计算机系   |
| 831 | 刘冰 | 女  | 1977-08-14 | 助教       | 电子工程系 |
| 856 | 张旭 | 男  | 1969-03-12 | 讲师       | 电子工程系 |
+-----+------+-----+------------+------------+------------+
-- NOT: 代表逻辑非
SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN (
   SELECT profession FROM teacher WHERE department = '电子工程系'
)
-- 合并两个集
UNION
SELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '计算机系'
);


ANY 表示至少一个 - DESC ( 降序 )


查询课程 3-105 且成绩 至少 高于 3-245 的 score 表。


SELECT * FROM score WHERE c_no = '3-105';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+
SELECT * FROM score WHERE c_no = '3-245';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
+------+-------+--------+
-- ANY: 符合SQL语句中的任意条件。
-- 也就是说，在 3-105 成绩中，只要有一个大于从 3-245 筛选出来的任意行就符合条件，
-- 最后根据降序查询结果。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(
    SELECT degree FROM score WHERE c_no = '3-245'
) ORDER BY degree DESC;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 102  | 3-105 |     91 |
| 101  | 3-105 |     90 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+
文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
关键词：
云数据库 RDS MySQL 版笔记
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
数据不吹牛
目录
相关文章
zzy的aly
|
18天前
|
Java 关系型数据库 MySQL
自动化测试项目实战笔记(一)：JDK、Tomcat、MySQL、Jpress环境安装和搭建
这篇文章是关于自动化测试项目实战笔记，涵盖了JDK、Tomcat、MySQL、Jpress环境的安装和搭建过程，以及测试用例和常见问题总结。
zzy的aly
33 1
自动化测试项目实战笔记(一)：JDK、Tomcat、MySQL、Jpress环境安装和搭建
jcLee95
|
3月前
|
SQL 存储 关系型数据库
运维笔记.MySQL.基于mysqldump数据备份与恢复
运维笔记.MySQL.基于mysqldump数据备份与恢复
jcLee95
48 0
花酒锄作田
|
3月前
|
SQL 关系型数据库 MySQL
【go笔记】使用sqlx操作MySQL
【go笔记】使用sqlx操作MySQL
花酒锄作田
39 0
敖小军
|
5月前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
索引查找从顶层节点开始查找，通过key值，也就是主键的值进行比较，最终定位到存储数据的叶子节点上面，从叶子节点取出响应的数据。
敖小军
57 0
Mysql优化之索引相关介绍(笔记)
hnrk7epeorhrk
|
5月前
|
存储 关系型数据库 MySQL
技术笔记：MySQL数据库优化详解(收藏)
技术笔记：MySQL数据库优化详解(收藏)
hnrk7epeorhrk
55 0
hnrk7epeorhrk
|
5月前
|
SQL 关系型数据库 MySQL
技术笔记：python连接mysql数据库
技术笔记：python连接mysql数据库
hnrk7epeorhrk
61 0
hnrk7epeorhrk
|
5月前
|
SQL 存储 关系型数据库
技术笔记：MYSQL常用基本SQL语句总结
技术笔记：MYSQL常用基本SQL语句总结
hnrk7epeorhrk
37 0
敖小军
|
5月前
|
SQL 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
这段内容涵盖了创建MySQL用户表的SQL语句，创建一个包含`username`、`age`和`dept`字段的联合索引，以及关于联合索引查询时遵循的最左前缀原则的解释。
敖小军
44 0
敖小军
|
5月前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
MySQL的InnoDB存储引擎中，索引节点默认大小为16KB（16384字节）。查询每个节点大小可执行`SHOW GLOBAL STATUS LIKE 'Innodb_page_size';`。索引节点大小影响B+树的效率，更高的层数意味着更多的I/O操作。当数据量超过2000万条时，建议分表以减少查询延迟和I/O次数。B+树高度为3时，根据节点数据结构，可以计算出能存放的数据量。
敖小军
36 0
敖小军
|
5月前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
**摘要：** 索引是数据库中用于加速数据检索的排好序的数据结构，例如MySQL常用B+树。没有索引时，查询需全表扫描，而使用索引则减少扫描次数，提高效率。例如，二叉树、红黑树和B树是常见数据结构，但MySQL选择B+树作为默认索引，因为它能避免非叶子节点存储数据，减少磁盘I/O操作，适合大数据量存储，并提供顺序访问的优势。
敖小军
40 0

热门文章

最新文章

  • 1
    MySQL数据表生成ER图
  • 2
    RDS for SQL server 空间问题排查汇总
  • 3
    [已解决]踩过的坑之mysql连接报“Communications link failure”错误
  • 4
    FlinkSQL 实时采集Kafka内容到MySQL（实战记录）
  • 5
    mysql 优化语句
  • 6
    mysql备份工具之xtrabackup
  • 7
    mysql----其他小技巧
  • 8
    MySQL5.7.17的简单配置文件
  • 9
    MySQL学习笔记之命令总结
  • 10
    【MySQL】ERROR 1005 (HY000): Can't create table' (errno: 150)
  • 1
    轻松入门Mysql：MySQL性能优化与监控，解锁进销存系统的潜力（23）
    121
  • 2
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
    459
  • 3
    轻松入门MySQL：数据库设计之范式规范，优化企业管理系统效率（21）
    139
  • 4
    轻松入门MySQL：揭秘MySQL游标，数据处理的神秘利器（16）
    112
  • 5
    轻松入门MySQL：加速进销存！利用MySQL存储过程轻松优化每日销售统计（15）
    159
  • 6
    轻松入门MySQL：保障数据完整性，MySQL事务在进销存管理系统中的应用（12）
    71
  • 7
    轻松入门MySQL：优化进销存管理，掌握MySQL索引，提升系统效率（11）
    131
  • 8
    轻松入门MySQL：精准查询，巧用WHERE与HAVING，数据库查询如虎添翼（7）
    122
  • 9
    轻松入门MySQL：玩转数据表的增、删、改、查（4）
    72
  • 10
    轻松入门MySQL：深入学习数据库表管理，创建、修改、约束、建议与性能优化（3）
    109

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月