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

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

2022-05-08 149
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 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
数据不吹牛
目录
相关文章
老板这功能得加钱
|
4月前
|
SQL 存储 关系型数据库
Mysql优化提高笔记整理，来自于一位鹅厂大佬的笔记，阿里P7亲自教你
Mysql优化提高笔记整理，来自于一位鹅厂大佬的笔记，阿里P7亲自教你
老板这功能得加钱
42 0
jcLee95
|
1月前
|
SQL 存储 关系型数据库
运维笔记.MySQL.基于mysqldump数据备份与恢复
运维笔记.MySQL.基于mysqldump数据备份与恢复
jcLee95
37 0
花酒锄作田
|
1月前
|
SQL 关系型数据库 MySQL
【go笔记】使用sqlx操作MySQL
【go笔记】使用sqlx操作MySQL
花酒锄作田
31 0
敖小军
|
3月前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
索引查找从顶层节点开始查找，通过key值，也就是主键的值进行比较，最终定位到存储数据的叶子节点上面，从叶子节点取出响应的数据。
敖小军
53 0
Mysql优化之索引相关介绍(笔记)
hnrk7epeorhrk
|
3月前
|
存储 关系型数据库 MySQL
技术笔记：MySQL数据库优化详解(收藏)
技术笔记：MySQL数据库优化详解(收藏)
hnrk7epeorhrk
48 0
hnrk7epeorhrk
|
3月前
|
SQL 关系型数据库 MySQL
技术笔记：python连接mysql数据库
技术笔记：python连接mysql数据库
hnrk7epeorhrk
40 0
hnrk7epeorhrk
|
3月前
|
SQL 存储 关系型数据库
技术笔记：MYSQL常用基本SQL语句总结
技术笔记：MYSQL常用基本SQL语句总结
hnrk7epeorhrk
29 0
敖小军
|
3月前
|
SQL 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
这段内容涵盖了创建MySQL用户表的SQL语句，创建一个包含`username`、`age`和`dept`字段的联合索引，以及关于联合索引查询时遵循的最左前缀原则的解释。
敖小军
38 0
敖小军
|
3月前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
MySQL的InnoDB存储引擎中，索引节点默认大小为16KB（16384字节）。查询每个节点大小可执行`SHOW GLOBAL STATUS LIKE 'Innodb_page_size';`。索引节点大小影响B+树的效率，更高的层数意味着更多的I/O操作。当数据量超过2000万条时，建议分表以减少查询延迟和I/O次数。B+树高度为3时，根据节点数据结构，可以计算出能存放的数据量。
敖小军
27 0
敖小军
|
3月前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
**摘要：** 索引是数据库中用于加速数据检索的排好序的数据结构，例如MySQL常用B+树。没有索引时，查询需全表扫描，而使用索引则减少扫描次数，提高效率。例如，二叉树、红黑树和B树是常见数据结构，但MySQL选择B+树作为默认索引，因为它能避免非叶子节点存储数据，减少磁盘I/O操作，适合大数据量存储，并提供顺序访问的优势。
敖小军
31 0

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    RDS数据库为慧联无限数据库运维减负
  • 3
    第15节：MySQL层事务提交流程简析
  • 4
    linux忘记mysql5.7 root密码
  • 5
    误删除mysql中的所用用户，如何重新设置root账号和密码
  • 6
    zabbix版本升级3.0.4 修复mysql漏洞（debian7）
  • 7
    自己写的一个MYSQL监控show global status工具用于查看指定时间内变化的指标
  • 8
    MySQL数据库基础：各类窗口函数操作一文详解
  • 9
    Win10+VS2015+EF6.0+MySQL5.6+MVC环境部署和排错
  • 10
    MySQL学习笔记之约束条件
  • 1
    【Mysql】在多表查询过程中如果已经起了别名，就不能通过表名限定字段
    63
  • 2
    【MySQL-11】多表查询全解-【多表关系/内外自连接/子查询/多表查询案例链接】（可cv代码＆案例演示）
    80
  • 3
    【Mysql-12】一文解读【事务】-【基本操作/四大特性/并发事务问题/事务隔离级别】
    68
  • 4
    【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
    440
  • 5
    【MySQL-10】DCL-数据控制语言-【管理用户＆权限控制】 （语法语句＆案例演示＆可cv案例代码）
    48
  • 6
    【MySQL-9】一文带你搞定 外键约束＆其【更新/删除行为】（可cv代码＆案例演示）
    157
  • 7
    【MySQL】DQL-案例练习-DQL基本介绍＆语法＆执行顺序（代码演示）
    46
  • 8
    【MySQL】DQL-排序查询-语法＆注意事项＆可cv例题语句
    46
  • 9
    【MySQL】DQL-排序查询-语法＆排序方式＆注意事项＆可cv例题语句
    48
  • 10
    【MySQL】DQL-分组查询-语法＆where与having的区别＆注意事项＆可cv例题语句
    61

    • 相关课程

    更多
  • 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
    • 下一篇
    通义千问API入门教程