爬虫做视频网站国建设网站
一：检测数据库网络
telnet 127.0.0.1 3306注意ip和端口后之间是空格，不需要引号
二：SQL语法
1、创建结果集
SELECT '电视机' AS typeUNION 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
在 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填充。
使用这些连接类型可以根据需要灵活地获取相关的数据。
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、日期字段
日期字段使用频率非常高，比如交易表中需要保存多个时间字段，它们的数据类型可能不同，就是同一数据类型，但保存的格式也有可能不同。如下从三个例子展开
- 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、date_time类型
create_time是正经日期类型的字段，需要注意格式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‘，不专业不规范。但筛选时间也是可以直接跟字符串对比的
筛选动态日期数据
-- 将当天日期，转成%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') 以上都返回199、In和Or
In和or都在where语句中使用，当同一个字段跟多个值比较时使用。
比如查询姓名是孙权、曹操、刘备、张飞、刘季的数据，都是name字段，但是筛选多个值
or实现: where name = '孙权' or name = '曹操' or name = '刘备' or name = '张飞'
in实现: where name in (孙权,曹操,刘备,张飞)
在有索引的情况下or的效率比in更高，数据库会优化
三：心得
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】返回
SELECTCOALESCE(COUNT(type), 0) AS '交易笔数',3、本月12点前的数据
笨写法
wherename = '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') )正确写法
wherename = 'sq' andpay_time >= '2024-10-02 00:00:00' andpay_time <= '2024-11-01 00:00:00' andHOUR(pay_time) < 104、当天所有时段数据
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 tblWHERE公共条件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添加条件，缩小查询范围