当前位置：首页 > news >正文

解锁SQL基础应用新境界：从入门到精通的扩展实践指南(SQL扩展)

news 来源：原创 2024/9/20 9:27:52

作者简介：我是团团儿，是一名专注于云计算领域的专业创作者，感谢大家的关注
座右铭：云端筑梦，数据为翼，探索无限可能，引领云计算新纪元
个人主页：团儿.-CSDN博客

前言：

一．group by

1.group by name n

2.关于group by的sql_mode

3.group_concat

4.concat

5.关于多表连接语法规则 ****

6.别名

7.distinct

8.select 执行顺序

二．扩展类内容-元数据获取 ****

1.元数据介绍及获取介绍

2.information_schema的基本应用 ***

-- 1. 显示业务库和表的信息

-- 2. 以以下模式显示所有的库和表的信息

-- 3. 查询所有innodb引擎的表

-- 4. 统计world下的city表占用空间大小 ****

-- 5. 统计world库数据量总大小 ****

-- 6. 统计每个库的数据量大小，并按数据量从大到小排序 ****

3.配合concat()函数拼接语句或命令

-- 1. 模仿以下语句,进行数据库的分库分表备份。

-- 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;