- 作者简介:我是团团儿,是一名专注于云计算领域的专业创作者,感谢大家的关注
- 座右铭: 云端筑梦,数据为翼,探索无限可能,引领云计算新纪元
- 个人主页:团儿.-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';
期待您的关注~