20000字干货笔记,一天搞定Mysql~(下)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 今天给大家分享的是我学习Mysql记录的详细笔记,有基础知识,也有实战案例

表示所有的 ALL

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


-- 只需对上一道题稍作修改。
-- ALL: 符合SQL语句中的所有条件。
-- 也就是说,在 3-105 每一行成绩中,都要大于从 3-245 筛选出来全部行才算符合条件。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL(
    SELECT degree FROM score WHERE c_no = '3-245'
);
+------+-------+--------+
| 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 |
+------+-------+--------+


复制表的数据作为条件查询


查询某课程成绩比该课程平均成绩低的score表。


-- 查询平均分
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
b表
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     87.6667 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
-- 查询 score 表
select * from score;
a表
+------+-------+--------+
| 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 |
+------+-------+--------+
-- 将表 b 作用于表 a 中查询数据
-- score a (b): 将表声明为 a (b),
-- 如此就能用 a.c_no = b.c_no 作为条件执行查询了。
SELECT * FROM score a WHERE degree < ( 
    (SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no)
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+


子查询 - 4


查询所有任课 ( 在 course 表里有课程 ) 教师的 namedepartment


SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);
+--------+-----------------+
| name   | department      |
+--------+-----------------+
| 李诚   | 计算机系        |
| 王萍   | 计算机系        |
| 刘冰   | 电子工程系      |
| 张旭   | 电子工程系      |
+--------+-----------------+


条件加组筛选


查询 student 表中至少有 2 名男生的 class


-- 查看学生表信息
SELECT * FROM student;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾华      | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 103 | 王丽      | 女  | 1976-01-23 | 95033 |
| 104 | 李军      | 男  | 1976-02-20 | 95033 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 107 | 王尼玛    | 男  | 1976-02-20 | 95033 |
| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |
| 110 | 张飞      | 男  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+
-- 只查询性别为男,然后按 class 分组,并限制 class 行大于 1。
select * from student where sex='男'
group by class
having count(class)>=2;
+-----+------+-----+------------+-------+
| no  | name | sex | birthday   | class |
+-----+------+-----+------------+-------+
| 101 | 曾华 | 男  | 1977-09-01 | 95033 |
| 102 | 匡明 | 男  | 1975-10-02 | 95031 |
+-----+------+-----+------------+-------+


NOT LIKE 模糊查询取反


查询 student 表中不姓 "王" 的同学记录。


-- NOT: 取反
-- LIKE: 模糊查询
mysql> SELECT * FROM student WHERE name NOT LIKE '王%';
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾华      | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 104 | 李军      | 男  | 1976-02-20 | 95033 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |
| 110 | 张飞      | 男  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+


YEAR 与 NOW 函数


查询 student 表中每个学生的姓名和年龄。


select name, year(now())-year(birthday) as age
from student;
+-----------+------+
| name      | age  |
+-----------+------+
| 曾华      |   42 |
| 匡明      |   44 |
| 王丽      |   43 |
| 李军      |   43 |
| 王芳      |   44 |
| 陆军      |   45 |
| 王尼玛    |   43 |
| 张全蛋    |   44 |
| 赵铁柱    |   45 |
| 张飞      |   45 |
+-----------+------+


MAX 与 MIN 函数


查询 student 表中最大和最小的 birthday 值。


SELECT MAX(birthday), MIN(birthday) FROM student;
+---------------+---------------+
| MAX(birthday) | MIN(birthday) |
+---------------+---------------+
| 1977-09-01    | 1974-06-03    |
+---------------+---------------+


多段排序


classbirthday 从大到小的顺序查询 student 表。


SELECT * FROM student ORDER BY class DESC, birthday DESC;
+-----+--------+-----+------------+-------+
| no  | name   | sex | birthday   | class |
+-----+--------+-----+------------+-------+
| 110 | 张飞   | 男  | 1974-06-03 | 95038 |
| 101 | 曾华   | 男  | 1977-09-01 | 95033 |
| 104 | 李军   | 男  | 1976-02-20 | 95033 |
| 107 | 王尼玛 | 男  | 1976-02-20 | 95033 |
| 103 | 王丽   | 女  | 1976-01-23 | 95033 |
| 102 | 匡明   | 男  | 1975-10-02 | 95031 |
| 105 | 王芳   | 女  | 1975-02-10 | 95031 |
| 108 | 张全蛋 | 男  | 1975-02-10 | 95031 |
| 106 | 陆军   | 男  | 1974-06-03 | 95031 |
| 109 | 赵铁柱 | 男  | 1974-06-03 | 95031 |
+-----+--------+-----+------------+-------+


子查询 - 5


查询 "男" 教师及其所上的课程。


SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = '男');
+-------+--------------+------+
| no    | name         | t_no |
+-------+--------------+------+
| 3-245 | 操作系统     | 804  |
| 6-166 | 数字电路     | 856  |
+-------+--------------+------+


MAX 函数与子查询


查询最高分同学的 score 表。


-- 找出最高成绩(该查询只能有一个结果)
SELECT MAX(degree) FROM score;
-- 根据上面的条件筛选出所有最高成绩表,
-- 该查询可能有多个结果,假设 degree 值多次符合条件。
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
+------+-------+--------+


子查询 - 6


查询和 "李军" 同性别的所有同学 name


select name from student where sex = (
SELECT sex FROM student where name='李军'
);
+--------+
| name   |
+--------+
| 曾华   |
| 匡明   |
| 李军   |
| 陆军   |
| 王尼玛 |
| 张全蛋 |
| 赵铁柱 |
| 张飞   |
+--------+


子查询 - 7


查询和 "李军" 同性别且同班的同学 name


SELECT name, sex, class FROM student WHERE sex = (
    SELECT sex FROM student WHERE name = '李军'
) AND class = (
    SELECT class FROM student WHERE name = '李军'
);
+-----------+-----+-------+
| name      | sex | class |
+-----------+-----+-------+
| 曾华      | 男  | 95033 |
| 李军      | 男  | 95033 |
| 王尼玛    | 男  | 95033 |
+-----------+-----+-------+


子查询 - 8


查询所有选修 "计算机导论" 课程的 "男" 同学成绩表。


需要的 "计算机导论" 和性别为 "男" 的编号可以在 coursestudent 表中找到。


SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE name = '计算机导论'
) AND s_no IN (
    SELECT no FROM student WHERE sex = '男'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 104  | 3-105 |     89 |
| 109  | 3-105 |     76 |
+------+-------+--------+


按等级查询


建立一个 grade 表代表学生的成绩等级,并插入数据:


CREATE TABLE grade (
    low INT(3),
    upp INT(3),
    grade char(1)
);
INSERT INTO grade VALUES (90, 100, 'A');
INSERT INTO grade VALUES (80, 89, 'B');
INSERT INTO grade VALUES (70, 79, 'C');
INSERT INTO grade VALUES (60, 69, 'D');
INSERT INTO grade VALUES (0, 59, 'E');
SELECT * FROM grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+


查询所有学生的 s_noc_nograde 列。


思路是,使用区间 ( BETWEEN ) 查询,判断学生的成绩 ( degree ) 在 grade 表的 lowupp 之间。


SELECT s_no, c_no, grade FROM score, grade
WHERE degree BETWEEN low AND upp;
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 101  | 3-105 | A     |
| 102  | 3-105 | A     |
| 103  | 3-105 | A     |
| 103  | 3-245 | B     |
| 103  | 6-166 | B     |
| 104  | 3-105 | B     |
| 105  | 3-105 | B     |
| 105  | 3-245 | C     |
| 105  | 6-166 | C     |
| 109  | 3-105 | C     |
| 109  | 3-245 | D     |
| 109  | 6-166 | B     |
+------+-------+-------+

连接查询


准备用于测试连接查询的数据:


CREATE DATABASE testJoin;
CREATE TABLE person (
    id INT,
    name VARCHAR(20),
    cardId INT
);
CREATE TABLE card (
    id INT,
    name VARCHAR(20)
);
INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
SELECT * FROM card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+
INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);
SELECT * FROM person;
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+
|    1| 张三   |      1 |
|  2 | 李四   |      3 |
|3 | 王五   |      6 |
+------+--------+--------+


分析两张表发现,person 表并没有为 cardId 字段设置一个在 card 表中对应的 id 外键。如果设置了的话,personcardId 字段值为 6 的行就插不进去,因为该 cardId 值在 card 表中并没有。


内连接


要查询这两张表中有关系的数据,可以使用 INNER JOIN ( 内连接 ) 将它们连接在一起。


-- INNER JOIN: 表示为内连接,将两张表拼接在一起。
-- on: 表示要执行某个条件。
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+
-- 将 INNER 关键字省略掉,结果也是一样的。
-- SELECT * FROM person JOIN card on person.cardId = card.id;注意:card 的整张表被连接到了右边。

左外连接


完整显示左边的表 ( person ) ,右边的表如果符合条件就显示,不符合则补 NULL


-- LEFT JOIN 也叫做 LEFT OUTER JOIN,用这两种方式的查询结果是一样的。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+


右外链接


完整显示右边的表 ( card ) ,左边的表如果符合条件就显示,不符合则补 NULL


SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+


全外链接


完整显示两张表的全部数据。


-- MySQL 不支持这种语法的全外连接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出现错误:
-- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'
-- MySQL全连接语法,使用 UNION 将两张表合并在一起。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
Java 关系型数据库 MySQL
自动化测试项目实战笔记(一):JDK、Tomcat、MySQL、Jpress环境安装和搭建
这篇文章是关于自动化测试项目实战笔记,涵盖了JDK、Tomcat、MySQL、Jpress环境的安装和搭建过程,以及测试用例和常见问题总结。
49 1
自动化测试项目实战笔记(一):JDK、Tomcat、MySQL、Jpress环境安装和搭建
|
3月前
|
SQL 存储 关系型数据库
运维笔记.MySQL.基于mysqldump数据备份与恢复
运维笔记.MySQL.基于mysqldump数据备份与恢复
53 0
|
3月前
|
SQL 关系型数据库 MySQL
【go笔记】使用sqlx操作MySQL
【go笔记】使用sqlx操作MySQL
|
5月前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
索引查找从顶层节点开始查找,通过key值,也就是主键的值进行比较,最终定位到存储数据的叶子节点上面,从叶子节点取出响应的数据。
58 0
Mysql优化之索引相关介绍(笔记)
|
5月前
|
存储 关系型数据库 MySQL
技术笔记:MySQL数据库优化详解(收藏)
技术笔记:MySQL数据库优化详解(收藏)
58 0
|
5月前
|
SQL 关系型数据库 MySQL
技术笔记:python连接mysql数据库
技术笔记:python连接mysql数据库
89 0
|
5月前
|
SQL 存储 关系型数据库
技术笔记:MYSQL常用基本SQL语句总结
技术笔记:MYSQL常用基本SQL语句总结
37 0
|
5月前
|
SQL 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
这段内容涵盖了创建MySQL用户表的SQL语句,创建一个包含`username`、`age`和`dept`字段的联合索引,以及关于联合索引查询时遵循的最左前缀原则的解释。
44 0
|
5月前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
MySQL的InnoDB存储引擎中,索引节点默认大小为16KB(16384字节)。查询每个节点大小可执行`SHOW GLOBAL STATUS LIKE &#39;Innodb_page_size&#39;;`。索引节点大小影响B+树的效率,更高的层数意味着更多的I/O操作。当数据量超过2000万条时,建议分表以减少查询延迟和I/O次数。B+树高度为3时,根据节点数据结构,可以计算出能存放的数据量。
38 0
|
5月前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
**摘要:** 索引是数据库中用于加速数据检索的排好序的数据结构,例如MySQL常用B+树。没有索引时,查询需全表扫描,而使用索引则减少扫描次数,提高效率。例如,二叉树、红黑树和B树是常见数据结构,但MySQL选择B+树作为默认索引,因为它能避免非叶子节点存储数据,减少磁盘I/O操作,适合大数据量存储,并提供顺序访问的优势。
40 0