第三章 SQL基础应用扩展

1.上节回顾

1.1 group by name

1.2 关于group by的sql_mode

only_full_group_by
说明：

1. 在5.7版本中MySQL sql_mode参数中自带，5.6和8.0都没有

在带有group by 字句的select中，select 后的条件列（非主键列），
要么是group by后的列，要么需要在函数中包裹

1.3 group_concat

列转行聚合函数
mysql> select user,group_concat(host) from mysql.user group by user;

1.4 concat

做列值拼接
mysql> select concat(user,"@",host) from mysql.user;

1.5 关于多表连接语法规则

1.首先找涉及到的所有表
2.找到表和表之间的关联列
3.关联条件写在on后面

A join B on 关联列

1. 所有需要查询的信息放在select后
2. 其他的过滤条件where group by having order by limit 网最后放
   6.注意：对多表连接中，驱动表选择数据行少的表。后续所有表的关联列尽量是主键或唯一键（表设计），至少建立一个索引。

1.6 别名

表别名
列别名
count
oldguo linux a,b,c,d
oldguo python x，y,z
oldboy linux 10
hsw python 11

1.7 distinct

mysql> select count(distinct countrycode)  from city;

1.8 select 执行顺序

select  user ,count(name)  from  表   where  列   group by user  having 列   order by  列 ;

2. 扩展类内容-元数据获取

2.0 元数据介绍及获取介绍
元数据是存储在"基表"中。
通过专用的DDL语句，DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询的试图
show 命令是封装好功能，提供元数据查询基础功能

2.1 information_schema的基本应用

tables 视图的应用

mysql> use information_schema;mysql> desc tables;TABLE_SCHEMA        表所在的库名TABLE_NAME           表名ENGINE                存储引擎TABLE_ROWS          数据行AVG_ROW_LENGTH       平均行长度INDEX_LENGTH        索引长度

例子：

-- 1. 显示所有的库和表的信息

SELECT table_schema,table_name FROM information_schema.tables;

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

-- world     city,country,countrylanguageSELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tablesGROUP BY table_schema;

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

SELECT table_schema,table_name ,ENGINEFROM information_schema.tables WHERE ENGINE='innodb';

-- 4. 统计world下的city表占用空间大小
-- 表的数据量=平均行长度行数+索引长度
-- AVG_ROW_LENGTHTABLE_ROWS+INDEX_LENGTH

SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024FROM information_schema.TABLESWHERE table_schema='world' AND table_name='city';

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

SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024FROM information_schema.TABLESWHERE table_schema='world';

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

SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KBFROM information_schema.TABLESGROUP BY table_schemaORDER BY total_KB DESC ;

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

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

mysqldump -uroot -p123 world city >/bak/world_city.sqlSELECTCONCAT("mysqldump -uroot -p123 ",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.tablesWHERE table_schema='world';

2.3 show介绍

show databases;             查看数据库名
show tables;                查看表名
show create database xx;    查看建库语句
show create table xx;       查看建表语句
show processlist;           查看所有用户连接情况
show charset;               查看支持的字符集
show collation;             查看所有支持的校对规则
show grants for xx;         查看用户的权限信息
show variables like '%xx%'  查看参数信息
show engines;               查看所有支持的存储引擎类型
show index from xxx         查看表的索引信息
show engine innodb status\G 查看innoDB引擎详细状态信息
show binary logs            查看二进制日志的列表信息
show binlog events in ''    查看二进制日志的事件信息
show master status ;        查看mysql当前使用二进制日志信息
show slave status\G         查看从库状态信息
show relaylog events in ''  查看中继日志的事件信息
show status like ''         查看数据库整体状态信息