- 作者简介：我是团团儿，是一名专注于云计算领域的专业创作者，感谢大家的关注
- 座右铭： 云端筑梦，数据为翼，探索无限可能，引领云计算新纪元
- 个人主页：团儿.-CSDN博客
目录
-- 4. 统计world下的city表占用空间大小 ****
-- 6. 统计每个库的数据量大小，并按数据量从大到小排序 ****
-- 2. 模仿以下语句,进行批量生成对world库下所有表进行操作
前言：
在信息爆炸的时代，数据已成为驱动业务决策、优化运营流程、以及创新服务模式的核心动力。而SQL（Structured Query Language），作为与数据库交互的基石语言，其重要性不言而喻。无论是数据分析师、数据科学家、软件开发者，还是任何需要处理数据的专业人士，掌握SQL都是通往数据世界的必备钥匙。
本章《解锁SQL基础应用新境界：从入门到精通的扩展实践指南》旨在为读者提供一个从SQL基础扎实起步，逐步迈向高级应用的全面指南。我们深知，仅仅掌握SQL的基本语法和查询操作是远远不够的，在实际工作中，面对复杂多变的数据需求，如何高效、准确地运用SQL进行数据提取、转换、加载（ETL），以及进行数据分析与可视化，才是衡量一个数据从业者能力的关键。
因此，本章覆盖了SQL在数据处理、性能优化、高级查询技巧的扩展应用。我们希望通过一系列精心设计的案例和实践练习，帮助读者不仅“知其然”，更“知其所以然”，从而在解决实际问题的过程中，不断提升自己的SQL应用能力和数据思维。
一．group by
1.group by name n
2.关于group by的sql_mode
only_full_group_by
说明：
在5.7版本中MySQL sql_mode参数中自带，5.6没有
在带有group by 字句的select中，select 后的条件列（非主键列），要么是group by后的列，要么需要在函数中包裹
实例：导入world库
通过百度网盘分享的文件：world.sql
链接: https://pan.baidu.com/s/1wf9kYbVzQnMnyk5KnFrCKQ 提取码: 9y8c
use world;
select name,population from city group by district;#报错违反only_full_group_by
关闭only_full_group_by：
select @@sql_mode;#查看当前sql_mode;
显示信息为：
only_full_group_by,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
仅复制only_full_group_by后面的字段，然后修改mysql配置文件。
vim /etc/my.cnf在[mysqld]中添加一行：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION保存退出
systemctl restart mysqld再次查看
select name,population from city group by district;则不会报错，直接执行
3.group_concat
列转行聚合函数
将某个分组内的多个值合并为一个字符串
select user,group_concat(host) from mysql.user group by user;4.concat
做列值拼接，用于将两个或多个字符串值连接成一个新的字符串
select concat(user,"@",host) from mysql.user;5.distinct
DISTINCT关键字用于返回唯一不同的值。当你对查询结果应用DISTINCT时，它会从结果集中移除重复的行，只保留唯一的行
select count(distinct countrycode) from city;
注：虽然CONCAT和DISTINCT在功能上有所不同，但它们可以在同一个查询中结合使用，以实现特定的数据处理需求。然而，需要注意的是，DISTINCT是应用于整个选择列表的，而不是单独应用于CONCAT的结果。
例：
-- 假设我们想要获取employees表中所有不同的first_name和last_name的组合，并将它们连接成一个字符串 -- 但由于DISTINCT是应用于整个选择列表的，我们不能直接对CONCAT的结果使用DISTINCT（除非使用子查询或窗口函数等高级技术） -- 下面的查询实际上不会按预期工作，因为它会尝试对整个CONCAT结果应用DISTINCT，而不是对原始列 -- SELECT DISTINCT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; -- 这将返回所有不同的全名组合，但通常这不是我们想要的 --
一个更实用的方法是，如果你想要基于某些连接后的值进行去重，你可能需要使用子查询或GROUP BY（但GROUP BY通常用于聚合函数）
-- 例如，如果你想要知道有哪些不同的全名组合（尽管这通常不是去重的直接用途） SELECT full_name FROM ( SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees ) AS subquery GROUP BY full_name; -- 这里GROUP BY实际上是多余的，因为子查询已经生成了唯一的full_name，但它展示了如何结合使用这些概念 -- 或者，如果你只是想要去重的全名列表，直接使用子查询就足够了 SELECT DISTINCT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
6.别名
表别名 from 表名 as 表别名
列别名 select 列名 as 列别名
7.关于多表连接语法规则 ****
首先找涉及到的所有表
找到表和表之间的关联列
关联条件写在on后面 A join B on 关联列
所有需要查询的信息放在select后
其他的过滤条件where group by having order by limit 往最后放
注意：对多表连接中，驱动表选择数据行少的表。后续所有表的关联列尽量是主键或唯一键（表设计），至少建立一个索引。
8.select 执行顺序
select user ,count(name) from 表 where 列 group by user having 聚合函数 order by 列 limit 3 offset 1 ;
=========================================================================
二．扩展类内容-元数据获取 ****
1.元数据介绍及获取介绍
元数据是存储在"基表"中。
通过专用的DDL语句，DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询的视图
show 命令是封装好功能，提供元数据查询基础功能
2.information_schema的基本应用 ***
tables 视图的应用
use information_schema;
desc tables;TABLE_SCHEMA 表所在的库名
TABLE_NAME 表名
ENGINE 存储引擎
TABLE_ROWS 数据行
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度
例子：
USE information_schema;
DESC TABLES;
-- 1. 显示业务库和表的信息
-- 2. 以以下模式 显示所有的库和表的信息
-- world city,country,countrylanguage
select table_schema,group_concat(table_name) from tables where table_schema not in ('sys','mysql','information_schema','performance_schema') group by table_schema;
-- 3. 查询所有innodb引擎的表
SELECT table_schema,table_name ,ENGINE
FROM information_schema.tables
WHERE ENGINE='innodb';-- 4. 统计world下的city表占用空间大小 ****
-- 表的数据量=平均行长度*行数+索引长度
-- AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024
FROM information_schema.TABLES
WHERE table_schema='world' AND table_name='city';
-- 5. 统计world库数据量总大小 ****
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024
FROM information_schema.TABLES
WHERE table_schema='world';
-- 6. 统计每个库的数据量大小，并按数据量从大到小排序 ****
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY total_KB DESC ;
3.配合concat()函数拼接语句或命令
例子：
-- 1. 模仿以下语句,进行数据库的分库分表备份。
备份world库下city表：
mkdir -p /bak/
mysqldump -uroot world city >/bak/world_city.sql查看
SELECT
CONCAT("mysqldump -uroot -p ",table_schema," ",table_name
," >/bak/",table_schema,"_",table_name,".sql")
FROM information_schema.tables;
-- 2. 模仿以下语句,进行批量生成对world库下所有表进行操作
ALTER TABLE world.city DISCARD TABLESPACE;
SELECT
CONCAT("ALTER TABLE ",table_schema,".",table_name," DISCARD TABLESPACE;")
FROM information_schema.tables
WHERE table_schema='world';
期待您的关注~
773
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
