DBA第六天

第五天回顾

1.1 group by (分组的一个功能,还有去重的功能)

id name age
1 a 10
2 b 12
3 c 15
4 a 12

group by name (以name这一列进行排序,或者出现两个a的时候,排序后就只会显示一个a,对应数值为取平均)

1.2 关于group by的sql_mode

only_full_group_by
说明

1. 在5.7版本中MySQL sql_mode参数中自带,5.6和8.0都没有
2. 在带有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 关联列
4.所有的需要查询的信息放在select后
5.其他的过滤条件 where, group by,having,order by,limit
6.注意:对多表连接中,驱动表选择数据行少的表.后续所有表的关联列尽量是主键或者唯一键(表设计).
至少建立一个索引.

1.6 别名

表别名
列别名

1.7select 执行顺序

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

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

2.0 元数据

元数据是储存在"基表"中.
用过专用的DDL语句,DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_shema中保存了大量元数据查询的试图.
show命令是封装好的功能,提供元数据查询基础功能.

2.1 information_shema的基本应用

tables 视图的应用

mysql>use information_shema;
mysql>desc tables;

TABLE_SCHEMA	表所在的库名
TABLE_NAME	表名
ENGINE	存储引擎
TABLES_ROWS	数据行
AVG_ROW_LENGTH	平均行长度
INDES_LENGTH	索引长度

例子:
1.显示所有的库和表的信息

select table_schema,table_name from information_schema.tables;

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

world  city,country,countrylanguage

select table_schema,group_concat(table_name)
for information_schema.table
group by table_schema;

3.统计以下所有innodb引擎的表

select table_schema,table_name
from information_schema.table
where engine='innodb';

4.统计world库下的city表,占用空间大小.
平均行长度行数+索引长度=实际占用空间大小
AVG_ROW_LENGTHABLES_ROWS+INDES_LENGTH

select table_name,AVG_ROW_LENGTH*ABLES_ROWS+INDES_LENGTH
from information_schema.tables
where tables_schema='world' and table_name='city'

5.统计world库数据量大小

select table_schema,sum((AVG_ROW_LENGTH*ABLES_ROWS+INDES_LENGTH))/1024
from information_schema.tables
where table_schema='world';

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

select table_schema,sum((AVG_ROW_LENGTH*ABLES_ROWS+INDES_LENGTH))/1024 as total_kb
from information_schema.tables
group by table_schema
order by total_kb desc;

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

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

mysqldump -uroot -p123 world city >/bak/world_city.sql

select 
concat("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.tables
where table_schema='world';

2.3 shwo介绍

show databases;	查看数据库名
show create database xx	查看建库语句
show tables;	查看表名
show create table xx	查看建表语句
show processlist;	查看所有用户间接情况
show charset;	查看支持的字符集
show collation;	查看所有支持的校对规则
show grants for xx;	查看用户的权限信息
show variables like ‘%xx%’	查看参数信息
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 ‘’	查看数据库整体状态信息