细谈MySQL的一下经典问题（一）

2023-10-24 20:44
文章标签 mysql 问题 经典 一下 database 细谈

本文主要是介绍细谈MySQL的一下经典问题（一），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

在这里插入图片描述

在MySQL中INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么区别？

在MySQL中，INNER JOIN、LEFT JOIN（或LEFT OUTER JOIN）、RIGHT JOIN（或RIGHT OUTER JOIN）和 FULL JOIN（或FULL OUTER JOIN）是用于将两个或多个表连接的不同方式。它们之间的主要区别在于连接的方式和结果集中包含的行。

  1. INNER JOIN：

    • INNER JOIN 只返回两个表中匹配条件的行。
    • 如果没有匹配的行，这些行将不包括在结果集中。
    • INNER JOIN是最常用的连接类型。

    示例： 假设有两个表，customers和 orders，我们想找出哪些顾客下了订单。

    SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

  2. LEFT JOIN (LEFT OUTER JOIN)：

    • LEFT JOIN 返回左表的所有行以及与右表匹配的行。
    • 如果没有匹配的行，对于右表的列，将会包含 NULL 值。

    示例： 找出所有的顾客以及他们可能下的订单。

    SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

  3. RIGHT JOIN (RIGHT OUTER JOIN)：

    • RIGHT JOIN 返回右表的所有行以及与左表匹配的行。
    • 如果没有匹配的行，对于左表的列，将会包含 NULL 值。

    示例： 找出所有的订单以及他们可能对应的顾客。

    SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

  4. FULL JOIN (FULL OUTER JOIN)：

    • FULL JOIN 返回左表和右表的所有行。
    • 如果没有匹配的行，对于不匹配的一侧，对应的列将包含 NULL 值。

    示例： 找出所有的顾客和订单，包括那些没有匹配的部分。

    SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;

示例结果和分析：

假设我们有以下数据：

customers 表：

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Customer A    |
| 2           | Customer B    |
| 3           | Customer C    |
+-------------+---------------+

orders 表：

+----------+-------------+
| order_id | customer_id |
+----------+-------------+
| 1001     | 1           |
| 1002     | 1           |
| 1003     | 3           |
| 1004     | 4           |
+----------+-------------+

针对不同类型的连接：

  • INNER JOIN 示例结果：
+-------------+---------------+----------+
| customer_id | customer_name | order_id |
+-------------+---------------+----------+
| 1           | Customer A    | 1001     |
| 1           | Customer A    | 1002     |
| 3           | Customer C    | 1003     |
+-------------+---------------+----------+
  • LEFT JOIN 示例结果：
+-------------+---------------+----------+
| customer_id | customer_name | order_id |
+-------------+---------------+----------+
| 1           | Customer A    | 1001     |
| 1           | Customer A    | 1002     |
| 2           | Customer B    | NULL     |
| 3           | Customer C    | 1003     |
+-------------+---------------+----------+
  • RIGHT JOIN 示例结果：
+-------------+---------------+----------+
| customer_id | customer_name | order_id |
+-------------+---------------+----------+
| 1           | Customer A    | 1001     |
| 1           | Customer A    | 1002     |
| 3           | Customer C    | 1003     |
| 4           | NULL          | 1004     |
+-------------+---------------+----------+
  • FULL JOIN 示例结果：
+-------------+---------------+----------+
| customer_id | customer_name | order_id |
+-------------+---------------+----------+
| 1           | Customer A    | 1001     |
| 1           | Customer A    | 1002     |
| 2           | Customer B    | NULL     |
| 3           | Customer C    | 1003     |
| 4           | NULL          | 1004     |
+-------------+---------------+----------+

使用 datetime 还是 timestamp 字段,那个是最优解？

在数据库中使用 datetime 还是 timestamp 字段的选择取决于你的具体需求。每种类型都有其优点和用途，以下是它们的区别和适用场景：

  1. datetime：

    • 存储日期和时间，包括年、月、日、时、分和秒。
    • 不受时区的影响，保存的时间是确切的时间点。
    • 适用于需要存储特定日期和时间的情况，例如日历事件、生日等，而不考虑时区。

  2. timestamp：

    • 存储日期和时间，包括年、月、日、时、分、秒，以及毫秒级的小数部分。
    • 受时区的影响，会自动转换为UTC时间并根据时区进行存储和检索。
    • 适用于需要跟踪事件的发生时间，并考虑时区的情况，例如日志、订单时间戳等。

以下是使用这两种字段的示例以及结果和分析：

使用 datetime：

假设我们有一个 events 表，其中存储了一些日历事件的日期和时间：

CREATE TABLE events (event_id INT PRIMARY KEY,event_name VARCHAR(255),event_datetime DATETIME
);-- 插入示例数据
INSERT INTO events (event_id, event_name, event_datetime)
VALUES(1, 'Meeting', '2023-10-25 14:00:00'),(2, 'Birthday', '2023-11-15 08:00:00'),(3, 'Appointment', '2023-12-05 16:30:00');

查询示例，返回所有事件的详细信息，不考虑时区：

SELECT event_id, event_name, event_datetime
FROM events;

结果：

+----------+-------------+---------------------+
| event_id | event_name  | event_datetime      |
+----------+-------------+---------------------+
| 1        | Meeting     | 2023-10-25 14:00:00 |
| 2        | Birthday    | 2023-11-15 08:00:00 |
| 3        | Appointment | 2023-12-05 16:30:00 |
+----------+-------------+---------------------+

使用 timestamp：

假设我们有一个 log 表，其中存储了系统日志的时间戳，考虑了时区的影响：

CREATE TABLE log (log_id INT PRIMARY KEY,message TEXT,log_timestamp TIMESTAMP
);-- 插入示例数据
INSERT INTO log (log_id, message, log_timestamp)
VALUES(1, 'Error occurred', '2023-10-25 14:00:00'),(2, 'User login', '2023-11-15 08:00:00'),(3, 'Data update', '2023-12-05 16:30:00');

查询示例，返回所有日志的时间戳，并考虑了时区：

SELECT log_id, message, log_timestamp
FROM log;

结果：

+-------+----------------+---------------------+
| log_id | message        | log_timestamp       |
+-------+----------------+---------------------+
| 1     | Error occurred | 2023-10-25 14:00:00 |
| 2     | User login     | 2023-11-15 08:00:00 |
| 3     | Data update    | 2023-12-05 16:30:00 |
+-------+----------------+---------------------+

总结：

  • 使用 datetime 适用于不需要考虑时区的情况，例如日历事件或生日。
  • 使用 timestamp 适用于需要考虑时区的情况，例如日志或时间戳，因为它会自动转换为UTC时间并存储。

MyISAM 与 InnoDB各自的使用场景

MyISAM和InnoDB是两种不同的存储引擎，用于管理MySQL数据库中的数据表，它们各自具有一些特性和适用场景。

MyISAM:

MyISAM是MySQL的一种存储引擎，具有以下特点：

  1. 不支持事务： MyISAM表不支持事务处理，这意味着它不具备ACID特性（原子性、一致性、隔离性、持久性）。

  2. 表级锁： MyISAM使用表级锁定，而不是行级锁定。这意味着在写入数据时，其他会话无法访问整个表，可能导致性能问题。

  3. 较快的读取操作： MyISAM通常在读取操作上更快，特别是对于查询密集型应用。

  4. 支持全文本搜索： MyISAM表支持全文本搜索，适用于需要进行文本搜索的应用。

适用场景示例：

假设我们有一个用于存储博客文章的表：

CREATE TABLE blog_posts (post_id INT PRIMARY KEY,title VARCHAR(255),content TEXT,post_date DATE
) ENGINE=MyISAM;

这里，使用MyISAM是因为博客文章表通常是只读的，不需要支持事务，而且可能需要进行全文本搜索。当需要执行大量读取和搜索操作时，MyISAM可能是一个不错的选择。

InnoDB:

InnoDB是另一种MySQL的存储引擎，具有以下特点：

  1. 支持事务： InnoDB支持事务处理，可以确保数据的完整性，适用于需要事务支持的应用。

  2. 行级锁： InnoDB使用行级锁定，这意味着在写入数据时只锁定涉及的行，而不是整个表，从而提高并发性能。

  3. 外键约束： InnoDB支持外键约束，可以确保引用完整性，这对于数据一致性非常重要。

  4. 较慢的读取操作： InnoDB的读取性能通常比MyISAM略低，但在写入和并发操作上更强大。

适用场景示例：

假设我们有一个在线商店的订单表，需要支持事务和外键约束来确保订单和产品的一致性：

CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT,order_date DATE,FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) ENGINE=InnoDB;

这里，使用InnoDB是因为订单表需要支持事务，以确保在订单和客户之间的数据一致性，并且需要外键约束来保证引用完整性。

总结：

  • 使用MyISAM适用于只读或读多写少的表，无需事务支持，可能需要全文本搜索。
  • 使用InnoDB适用于需要事务支持、外键约束和更好的并发性能的表，尤其是对于写入密集型应用。

这篇关于细谈MySQL的一下经典问题（一）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/277654。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
好题——hdu2522（小数问题：求1/n的第一个循环节）

好题——hdu2522（小数问题：求1/n的第一个循环节）

好喜欢这题，第一次做小数问题，一开始真心没思路，然后参考了网上的一些资料。 知识点***********************************无限不循环小数即无理数，不能写作两整数之比*****************************（一开始没想到，小学没学好） 此题1/n肯定是一个有限循环小数，了解这些后就能做此题了。 按照除法的机制，用一个函数表示出来就可以了，代码如下
阅读更多...
hdu1043（八数码问题，广搜 + hash（实现状态压缩） ）

hdu1043（八数码问题，广搜 + hash（实现状态压缩） ）

利用康拓展开将一个排列映射成一个自然数，然后就变成了普通的广搜题。 #include<iostream>#include<algorithm>#include<string>#include<stack>#include<queue>#include<map>#include<stdio.h>#include<stdlib.h>#include<ctype.h>#inclu
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
购买磨轮平衡机时应该注意什么问题和技巧

购买磨轮平衡机时应该注意什么问题和技巧

在购买磨轮平衡机时，您应该注意以下几个关键点： 平衡精度 平衡精度是衡量平衡机性能的核心指标，直接影响到不平衡量的检测与校准的准确性，从而决定磨轮的振动和噪声水平。高精度的平衡机能显著减少振动和噪声，提高磨削加工的精度。 转速范围 宽广的转速范围意味着平衡机能够处理更多种类的磨轮，适应不同的工作条件和规格要求。 振动监测能力 振动监测能力是评估平衡机性能的重要因素。通过传感器实时监
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
缓存雪崩问题

缓存雪崩问题

缓存雪崩是缓存中大量key失效后当高并发到来时导致大量请求到数据库，瞬间耗尽数据库资源，导致数据库无法使用。 解决方案： 1、使用锁进行控制 2、对同一类型信息的key设置不同的过期时间 3、缓存预热 1. 什么是缓存雪崩 缓存雪崩是指在短时间内，大量缓存数据同时失效，导致所有请求直接涌向数据库，瞬间增加数据库的负载压力，可能导致数据库性能下降甚至崩溃。这种情况往往发生在缓存中大量 k
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2