Java从入门到工作4 - MySQL

sun_qqq

已于 2025-03-25 11:57:12 修改

阅读量666

点赞数 14

分类专栏： JAVA从入门到实战文章标签： java mysql 开发语言

于 2024-12-13 17:30:45 首次发布

本文链接：https://blog.youkuaiyun.com/sun_qqq/article/details/144455775

版权

JAVA从入门到实战专栏收录该内容

6 篇文章

订阅专栏

一：检测数据库网络

telnet 127.0.0.1 3306

注意ip和端口后之间是空格，不需要引号

二：SQL语法

1、创建结果集

SELECT '电视机' AS type
     UNION SELECT '电冰箱'
     UNION SELECT '洗衣机'
     UNION SELECT '空调'
     UNION SELECT '电脑'
     UNION SELECT '热水器'
     UNION SELECT '吸油烟机'
     UNION SELECT '吸尘器'

这段 SQL 查询用于创建一个结果集，其中包含多个固定值。具体理解如下：

SELECT '电视机' AS type：这一行创建第一条记录，返回一个值 '电视机' 并将其命名为 type。
UNION：这个关键字用于合并多个 SQL 语句的结果集。它确保结果中的每一条记录都是唯一的（默认情况下会去掉重复记录）。
SELECT '电冰箱'，SELECT '洗衣机'，等：每一条 UNION 之后的 SELECT 语句都返回一个新的固定值，表示不同的电器品类。

最终，这段 SQL 查询生成的结果是一个包含以下内容的单列结果集：

电视机
电冰箱
洗衣机
空调
电脑
热水器
吸油烟机
吸尘器

用途：这个查询通常用于构建一个临时的表或列表，其中包含预定义的数据项，这在分析、报告或合并查询时非常有用。结合其他表进行连接查询时，可以确保结果集的完整性，尤其是在需要确保所有选定项目都包含在结果中时。

2、JOIN

2.1、三种Join的区别

在 MySQL 中，JOIN 操作用于根据两个或多个表之间的相关列进行组合。常见的连接类型包括 INNER JOIN（通常简称为 JOIN）、LEFT JOIN 和 RIGHT JOIN。它们的区别如下：

INNER JOIN（或简写为 JOIN）：
- 仅返回两个表中匹配的行。
- 如果某个表中没有与另一个表匹配的行，则该行不会出现在结果集中。
- ```
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.for_id;
```
LEFT JOIN（或称为 LEFT OUTER JOIN）：
- 返回左表（LEFT JOIN 前的表）中的所有行，即使右表中没有与之匹配的行。
- 如果右表中没有匹配的行，结果集中该行的右表列将显示为 NULL。
- ```
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.for_id;
```
RIGHT JOIN（或称为 RIGHT OUTER JOIN）：
- 返回右表（RIGHT 前的表）中的所有行，即使左表中没有与之匹配的行。
- 如果左表中没有匹配的行，结果集中该行的左表列将显示为 NULL
- ```
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.for_id;
```

总结：

INNER JOIN 只返回匹配的行。
LEFT JOIN 返回左表的所有行以及右表中匹配的行，右表不匹配的部分用 NULL 填充。
RIGHT JOIN 返回右表的所有行以及左表中匹配的行，左表不匹配的部分用 NULL 填充。

使用这些连接类型可以根据需要灵活地获取相关的数据。

2.2、极端的循环次数

在没有索引的情况下，执行 A LEFT JOIN B 时的复杂度确实是 O(m * n)，通常的处理方式是对 A 的每一条记录进行循环，然后对于每一条 A 的记录，再在其内部嵌套循环 B。这样，最终处理的次数是 A 的记录数乘以 B 的记录数。

2.3、联表后的条数？

A LEFT JOIN B 如果 B 中的每条记录都与 A 中的同一条记录（例如第一条）匹配，则那一条 A 的记录会重复出现对应 B 中的每一条记录。这样，如果 A 中有 10 条记录，B 中有 3 条记录，联接结果是：

第一条 A 记录与 3 条 B 记录匹配，出现 3 次
其余的 9 条 A 记录与 B 中的记录不匹配，依然返回 9 次（可能为空）

即当 B 中的每条数据都与同一条来自 A 的数据匹配时，联接结果最少10条，最多可以达到 12 条。

因此

INNER JOIN：结果集最少 0 条，最多 min(m, n) 条。
LEFT JOIN：结果集最少为 m 条，最多通常为 m + n 条（在特定匹配情况下也可能达到）。

2.4、多次左联的执行逻辑

a
left join b
        on a.no=b.m_no 
left join  c
        on b.actv=c.id

如上左联2次，LEFT JOIN 的执行顺序是先将表 A与表 B连接，然后再将结果与表 C连接。

具体情况如下：

先关联 A 和 B：如果 A 中的某条记录在 B 中找不到匹配（即 ON 条件不满足），则结果集中这一行仍会包含来自 A 的数据，而与 B 相关的字段则将为 NULL。
再关联 C：在将 A 和 B 的结果集进行下一步关联时，如果 B 中的某行数据为 NULL（意味着与 A 没有匹配），对于后续的 LEFT JOIN 到 C，在连接的条件中 b.actv会是 NULL，因此也无法找到匹配的记录，结果中将对应 C 的关联字段也为 NULL。

因此，如果 B 关联不上 A，则在最终结果中会导致与 C 的关联结果为空，相关字段显示为 NULL。

3、执行顺序

3.1、SQL执行顺序

在MySQL中，SQL查询的执行顺序通常为：

FROM：从哪张表中获取数据。
JOIN：连接其他表，形成结果集。
WHERE：过滤数据，删除不符合条件的行。
GROUP BY：根据指定列对结果集进行分组。
HAVING：对分组后的结果进行过滤（在GROUP BY之后）。
SELECT：选择要显示的列。
ORDER BY：对结果集进行排序。
LIMIT：限制返回的行数。

这就是SQL查询的标准执行顺序。

from / join 解决从哪里查
where / group by / having 筛选处理数据
select 拿到哪些数据
order by / limit 处理拿到的数据

3.2、阅读SQL顺序

知道了SQL的执行顺序，就知道如何快速的阅读sql

select的内容先不看，
先从from、join开始看，先知道是查哪些表。
where也可以先不看，它只是过滤部分行
再看group by，这时基本能够理解SQL的整体任务了
回头去看where 、select、order by等细节了

4、使用别名

不完全是。只能在 ORDER BY 和 HAVING 中使用前面 SELECT 子句中的别名，而在 WHERE 和 GROUP BY 中不能使用这些别名。具体来说：

可以使用别名：ORDER BY 和 HAVING。
不能使用别名：WHERE 和 GROUP BY。

5、执行计划

MySQL执行计划的主要列及其含义如下：

id：查询的标识符，表示查询的顺序和层级。
select_type：查询的类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
table：当前操作的表名。
type：连接类型，表示访问表的方式，如 all（全表扫描）、index（索引扫），range（范围扫描）等。类型越优越越好。
possible_keys：可能使用的索引列表。
key：实际使用的索引。
key_len：索引长度，表示使用的字节数。
ref：与索引匹配的字段或常量。
rows：估算需要扫描的行数。
filtered：估算在输出结果中，符合条件的行的百分比。
Extra：额外信息，如 Using index、Using where、Using filesort 等，提供查询优化的线索。

这些列的组合可以帮助分析并优化查询性能。

6、索引

以下情况可能会触发命中索引：

WHERE 子句：当查询中使用的条件字段在索引中时，会触发索引命中，尤其是相等条件和范围条件。
JOIN 条件：连接查询中涉及到的字段如果有索引，也会导致索引的使用。
GROUP BY 子句：GROUP BY中字段有索引时，可以加速分组操作。
HAVING 子句：在 HAVING 中使用的字段如果有索引，可以提高筛选效率（但通常在 WHERE 中能更早筛选）。
ORDER BY 子句：如果ORDER BY中字段有索引，查询可以利用索引直接排序，避免额外的排序操作。
UNIQUE 和 FOREIGN KEY 约束：这些约束条件会自动创建索引，从而支持快速查找。
子查询中的条件：如果子查询涉及到的字段有索引，也可能触发索引命中。

确保索引设计合理，选择合适的字段建立索引，有助于提高查询性能。

7、数据类型

7.1、日期字段

mysql中的日期类型有如下5种

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

时间字段使用频率非常高，同一个表中需要保存多个时间相关的字段，它们的数据类型可能不同，比如如下

create_time：数据类型为date_time，保存格式为 2024-09-15 16:20:16
modi_time: 数据类型为date, 保存格式为 2024-09-15
play_time：数据类型为 varchar，保存格式为 2024-10-14 15:55:47
play_date：数据类型为 varchar，保存格式为 20241014

简而言之，

无论时间字段存储为 VARCHAR 还是 DATETIME，在比较时都可以直接与时间字符串进行比较，只要确保时间字符串的格式正确（如 'YYYY-MM-DD HH:MM:SS'）。对于 VARCHAR 字段，关键是保持格式的一致性以确保正确的比较。

从底层上讲，

对于数据类型为varchar的字段跟字符串比较，是基于字典序比较的。即一个个字符按顺序轮流对比的，所以这种比较只有在数据格式保持一致时才有效。但不建议在日期时间数据中使用 VARCHAR 类型，因为这样会导致潜在的错误和性能问题。

对于数据类型为Date的字段跟字符串比较，在进行比较时并不是基于字典序比较，而是基于日期的内在表示进行比较。这种比较方式确保了日期的正确性，避免了诸如字符串比较可能带来的错误。可以确保更准确和高效的比较操作。

7.2、日期比较

7.2.1、datetime类型

datetime是正经日期类型的字段，需要注意格式YYYY-MM-DD

筛选动态日期数据

create_time >= CURDATE() and create_time < CURDATE() + interval 1 day

筛选固定日期数据

筛选某一天前的数据，create_time < ='2023-09-30'即可

create_time 数据类型如果为date_time，上面将自动转为2023-09-30 00:00，只能取到29号数据

create_time 数据类型如果为date类型，上面就是2023-09-30，能取到30号数据

7.2.2、varchar类型

play_date存的字符串如’20241015‘，不专业不规范。

字符串对比是基于字典序比较的。即一个个字符按顺序轮流对比的，所以这种比较只有在数据格式保持一致时才有效。即需要把CURDATE()转为跟库里存的数据一样的拼接方式。

筛选动态日期数据

-- 将当天日期，转成%Y%m%d格式
consume.play_date = DATE_FORMAT(CURDATE(), '%Y%m%d') 
consume.play_date > DATE_FORMAT(CURDATE(), '%Y%m%d') 

// 虽然如下varchar跟date也能直接比。但是效率低70%且无法保证正确性
consume.play_date = CURDATE()

筛选固定日期数据

-- 将当天日期，转成%Y%m%d格式
consume.play_date = '20241028' 
consume.play_date > '20241028'

8、函数

8.1、DATE_FORMAT()

接收日期类型，返回字符串类型

DATE_FORMAT(CURDATE(), '%Y%m%d')

8.2、CURDATE()

CURDATE() 返回的是当前日期，格式为 YYYY-MM-DD，类型为 DATE

今天 CURDATE() ，格式为 2024-10-15 00:00:00

明天 CURDATE() + interval 1 day，格式为 2024-10-16 00:00:00

8.3、hour()

获取时间中的小时数，时间是varchar类型和dateTime类型都行，但需要注意格式hh:mm:ss

SELECT Hour('2024-10-27 19:22:33') 
SELECT Hour('19:22:33')   

以上都返回19

9、In和Or

In和or都在where语句中使用，当同一个字段跟多个值比较时使用。

比如查询姓名是孙权、曹操、刘备、张飞、刘季的数据，都是name字段，但是筛选多个值

or实现: where name = '孙权' or name = '曹操' or name = '刘备' or name = '张飞'

in实现: where name in (孙权,曹操,刘备,张飞)

在有索引的情况下or的效率比in更高，数据库会优化

10、limit

limit基础用法有2种，直接限制条数，和限制数据范围

10.1、限制范围

要在 SQL 查询中进行分页，获取第 8 到 16 条数据，可以使用 LIMIT 子句。具体可以按照以下方式修改查询：

LIMIT 8, 8

说明：

LIMIT 8, 8 的含义是从第 9 条记录开始（偏移 8），获取 8 条记录（返回第 9 到第 16 条）。
LIMIT 的第一个值是偏移量，第二个值是要返回的记录数。

三：心得

1、select关键词

此关键词决定sql输出的字段

select * 表示输出所有字段

2、SQL思路

写语句前，先分析需求，需要查出什么字段，从哪个表查，需不需要分组

3、关键字

select 用来确定返回那些字段，

where 用来过滤要返回的数据，

group by 用来合并要返回的数据

4、SQL慢查询优化

大佬的总结：Docs

四：小应用

1、百分比处理

先把小数*100，然后用ROUND方法保留0位小数，然后用CONCAT拼上“%”

CONCAT(
    ROUND(data*100,0),
    '%'
)

2、无数据补0返回

品类无数据【补0】返回

SELECT
    COALESCE(COUNT(type), 0) AS '交易笔数',

3、本月12点前的数据

笨写法

where
    name = 'sq' and
    (
        (pay_time >= '2024-10-02 00:00:00' AND pay_time <= '2024-10-02 12:00:00') or 
        (pay_time >= '2024-10-03 00:00:00' AND pay_time <= '2024-10-03 12:00:00') or 
        (pay_time >= '2024-10-04 00:00:00' AND pay_time <= '2024-10-04 12:00:00') or 
        (pay_time >= '2024-10-05 00:00:00' AND pay_time <= '2024-10-05 12:00:00') or 
        (pay_time >= '2024-10-06 00:00:00' AND pay_time <= '2024-10-06 12:00:00') 
    )

正确写法

where
    name = 'sq' and
    pay_time >= '2024-10-02 00:00:00' and
    pay_time <= '2024-11-01 00:00:00' and
    HOUR(pay_time) < 10

4、当天所有时段数据

SELECT 
    DATE_FORMAT(consume.pay_time, '%Y-%m-%d %H:00:00') 小时,
    COUNT(1) AS '订单',
FROM 
    ... 
WHERE 
    x_time >= CURDATE() -- 今天的开始时间
    AND consume.x_time < CURDATE() + INTERVAL 1 DAY -- 明天的开始时间
GROUP BY 
    DATE_FORMAT(x_time, '%Y-%m-%d %H:00:00')
ORDER BY 
    小时

小时	订单
2024/12/13 15:00	258
2024/12/13 16:00	16

在上述 SQL 查询中，使用 DATE_FORMAT(consume.x_time, '%Y-%m-%d %H:00:00') 作为分组字段，结果表示每个小时的开始时间。因此：

查询中的 2024/12/13 15:00 的结果代表的是 2024/12/13 15:00:00 到 2024/12/13 15:59:59 这一时间段的交易数据。

五：SQL优化

1、大表联小表翻译

可将某个表分组后再外连的辨别要点：

语句顺序为：table1 left join table2 left join table3 where...group by...limit
where/group by/order by压根不涉及某个外连表的字段

如下为具体示例

主表是大表100w数据，二表3000数据，三表3000数据

左联两个小表，为了映射字段名称

操作数据为100w*3000*3000最后取20条数据

改造思路：聚合和排序在大表先做好，再关联小表

操作数据为20*3000*3000

2、合并SQL

当一个sql需要查n个指标，n个指标因where条件不同可能JOIN了很多个子查询。

当数据量很大时，多个子查询可能就导致多次扫表，效率很低。

这时可以看下where中是否大部分条件是相同的，可以将共用的条件抽出来放在一个where中。然后在select中使用case when来使用那些各个指标特殊的条件。

这样只扫一次表，可能会提高查询效率。最起码SQL看起来简洁很多，便于维护与下一步的优化。

 SELECT 
        COUNT(CASE WHEN status = 4 THEN 1 END) AS 指标一,
        COUNT(CASE WHEN name is not null THEN 1 END) AS 指标二
    FROM 
        tbl
    WHERE
        公共条件

3、备份数据库

统计类的SQL非常消耗数据库资源，最好还是搞个备份库专门服务统计类查询，实现数据隔离，避免将业务库查崩。

3.1、如何实现备份库数据同步？

通过数据库审计功能，实现业务库跟备份库的数据同步问题。怎么理解数据库审计？

数据库审计是对数据库活动进行监控和记录的过程。其主要目的是确保数据的安全性、合规性和完整性。审计功能通常包括以下几个方面：

访问监控：记录哪些用户访问了数据库、何时访问、执行了哪些操作。
变更跟踪：监控数据的插入、更新和删除操作，确保能够追溯数据变化的来源。
合规性审核：验证数据库操作是否符合规定的安全和合规性标准。
异常检测：识别潜在的安全威胁或违规行为。

在业务库与备份库的数据同步中，数据库审计可以帮助确保同步过程的透明性和可靠性，及时发现和纠正同步过程中可能出现的问题。通过审计日志，可以追踪到哪些数据被同步、是否存在失败或异常操作，从而提供数据一致性的保障。

4、避免在where里使用函数

如果该字段有索引，函数会使索引失效！

实例1：where中某字段有索引避免使用函数

如下示例，sale_city为字符串有索引。同一个处理，两种写法。

第一种索引失效，第二种索引命中

where SUBSTRING(a.city, 1, 4) = 4113
where a.city like '4102%'

实例2：where中某字段无索引也避免使用函数

如下示例，create_time日期类型有索引。同一个处理，两种写法。

第一种索引失效，第二种索引命中

where DATE_FORMAT(create_time, '%Y-%m-%d') = DATE_FORMAT(CURDATE(), '%Y-%m-%d')
where create_time >= CURDATE() and create_time < CURDATE() + interval 1 day

如果 create_time 列没有索引，仍然建议去掉对 create_time 的函数调用。原因如下：

全表扫描：使用 SUBSTRING 或其他函数将导致全表扫描，这会显著降低性能，因为数据库无法利用任何索引进行加速查询。
明确的范围条件：通过直接比较 create_time，可以增加效率，尤其是在大量数据的情况下。使用条件 create_time >= CURDATE() AND create_time < CURDATE() + INTERVAL 1 DAY 可以帮助数据库优化器更好地执行查询。

即使没有索引，去掉函数调用仍对查询性能有很大帮助。这是一个普遍的优化建议，特别是在处理大数据集时。可以在之后考虑为 create_time 列创建索引，以进一步提高查询效率。

5、数据偏移

数据偏移会导致索引失效，能想到的就是强制索引

force index (key)

6、如何定位慢查询原因？

当拿到一个慢sql，从哪里下手去定位呢？

1、看执行计划，指针是否命中？

如果没命中，结合看查询结果，是不是数据偏移？

2、如果有子查询，一层层看执行结果

如果内层查询，某些数据对外层无用。针对无用数据，想办法where添加条件，缩小查询范围

六：Linux使用mysql

1、安装mysql

Linux Centos内网环境中安装mysql5.7详细安装过程_猿小白的技术博客_51CTO博客

Linux Centos内网环境中安装mysql5.7详细安装过程_内网服务器安装mysql-优快云博客

选择mysql版本时，可以这么选

云服务器不要忘了配置安全组，开放3306端口。

2、安全防护

安装mysql后，需要重置root账号的默认密码。

root账户一定要设一个巨复杂的密码，一定不要对外全放，要设置账号的ip白名单。否则黑客会停你的服务，删你的库，教你做人。

3、开发外网访问

Linux上安装MySQL后，默认情况下只能通过localhost（本地）访问。如果要允许外网访问，需要进行以下设置：

修改MySQL配置文件：
- 找到MySQL配置文件，一般是/etc/my.cnf或/etc/mysql/my.cnf。
- 在配置文件中找到bind-address这一行，默认可能是127.0.0.1，将其改为0.0.0.0，或者注释掉这一行，以允许来自所有IP的连接。

设置用户的IP白名单：

// 给ier账号开一个190.66.266.166的ip
CREATE USER '账号'@'IP' IDENTIFIED BY '密码';
GRANT ALL PRIVILEGES ON *.* TO '账号'@'IP' WITH GRANT OPTION;
FLUSH PRIVILEGES;

如上两步都是必要的，第1步允许所有IP连接，第2步给用户赋登录权限。

4、数据库命令

普通查询

# 使用密码登录
mysql -u root -p

# 所有数据库
show databases

# 查询各个用户的host
USE mysql;
SELECT user, host FROM user;

# 创建一个账号，并限制该账号的登录IP #
# 创建一个新用户，并指定该用户可以从特定的IP地址连接
# 授予其所有数据库和表的所有权限，数据库的创建、删除和更改。允许将其拥有的权限授予其他用户

CREATE USER '账号'@'IP' IDENTIFIED BY '密码';
GRANT ALL PRIVILEGES ON *.* TO '账号'@'IP' WITH GRANT OPTION;
FLUSH PRIVILEGES;

忘记root密码情况下，修改root密码。并更改root账号的登录限制

# 启动安全模式，不需要密码登录
# 使用找到的路径运行 mysqld 或 mysqld_safe
sudo /usr/local/mysql/bin/mysqld --skip-grant-tables &

# 登录 MySQL，不需要密码
mysql -u root

# 给Host为localhost的root用户设置新的密码。
USE mysql;
UPDATE user SET authentication_string=PASSWORD('密码') WHERE User='root' AND Host='localhost';

# 将原来可以从任何IP（Host='%'）访问改为只允许localhost访问（Host='localhost'）
UPDATE user SET host='localhost' WHERE user='root' AND host='%';

# 刷新权限：
FLUSH PRIVILEGES;

# 停止 MySQL 服务
sudo service mysqld stop

# 重新启动 MySQL 服务
sudo service mysqld start