一、information_schema
1.information_schema介绍
information_schema是一个虚拟库，即linux命令行中无法查看到，它随MySQL的启动而生成。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | <-------就是它了
| mysql |
| oldboy |
| oldguo |
| performance_schema |
| school |
| sys |
| world |
+--------------------+
8 rows in set (0.00 sec)
2.information_schema库的tables表的介绍
mysql> use information_schema; 进入虚拟库
mysql> desc tables; 查看tables表的结构
（截取了重要的几列）
TABLE_SCHEMA 表所在的库 （库名）
TABLE_NAME 表名
ENGINE 表的存储引擎
TABLE_ROWS 表的行数
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引的长度
接下来，一大波面试必问题来袭，坐稳了！
1.查整个数据库中所有的库对应的表名
mysql> select table_schema,table_name from information_schema.tables;
+--------------------+------------------------------------------------------+
| table_schema | table_name |
+--------------------+------------------------------------------------------+
| information_schema | CHARACTER_SETS |
| information_schema | COLLATIONS |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY |
| information_schema | COLUMNS |
......此处省去两千三百九十八行
2.查询world库和school库下的所有表名
mysql> SELECT table_schema,table_name
-> FROM information_schema.tables
-> WHERE table_schema='world'
-> UNION ALL
-> SELECT table_schema,table_name
-> FROM information_schema.tables
-> WHERE table_schema='school';
+--------------+-----------------+
| table_schema | table_name |
+--------------+-----------------+
| world | city |
| world | country |
| world | countrylanguage |
| school | course |
| school | score |
| school | student |
| school | teacher |
+--------------+-----------------+
7 rows in set (0.00 sec)
3.统计整个MySQL中，每个库下有多少表?
mysql> select table_schema,count(table_name)
-> from information_schema.tables
-> group by table_schema;
+--------------------+-------------------+
| table_schema | count(table_name) |
+--------------------+-------------------+
| information_schema | 61 |
| mysql | 32 |
| oldboy | 1 |
| oldguo | 3 |
| performance_schema | 87 |
| school | 4 |
| sys | 101 |
| world | 3 |
+--------------------+-------------------+
8 rows in set (0.00 sec)
4.统计所有库下的所有表所占的具体空间
mysql> select sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 as MB from information_schema.tables;
+-------------+
| MB |
+-------------+
| 47.82204914 |
+-------------+
1 row in set (0.04 sec)
5.统计每一个库占用的具体空间
mysql> SELECT
-> table_schema,
-> COUNT( table_name ),
-> SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS ToTAL_MB
-> FROM information_schema.TABLES
-> GROUP BY table_schema;
+--------------------+---------------------+-------------+
| table_schema | COUNT( table_name ) | ToTAL_MB |
+--------------------+---------------------+-------------+
| information_schema | 61 | NULL |
| mysql | 32 | 2.25533009 |
| oldboy | 1 | 44.71148777 |
| oldguo | 3 | 0.01562405 |
| performance_schema | 87 | 0.00000000 |
| school | 4 | 0.06248379 |
| sys | 101 | 0.01562500 |
| world | 3 | 0.76149845 |
+--------------------+---------------------+-------------+
8 rows in set (0.03 sec)
二、拼接函数
1.就是把语句拼接出来，（中间添加一些自己想要添加的句子）
2.语法：
可变值和不变值之间需要用逗号隔开，
用双引号引起来自己添加的不变值
mysql> select concat("hello world!");
+------------------------+
| concat("hello world!") |
+------------------------+
| hello world! |
+------------------------+
1 row in set (0.00 sec)
举例：
正常查询
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| old | 10.0.0.% |
| oldguo | 10.0.0.% |
| root | 10.0.0.% |
| zhihu | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
7 rows in set (0.00 sec)
我想在user和host中间加一个@符号
mysql> select concat(user,"@",host) from mysql.user;
+-------------------------+
| concat(user,"@",host) |
+-------------------------+
| old@10.0.0.% |
| oldguo@10.0.0.% |
| root@10.0.0.% |
| zhihu@10.0.0.% |
| mysql.session@localhost |
| mysql.sys@localhost |
| root@localhost |
+-------------------------+
7 rows in set (0.00 sec)
1.单库单表备份通用语句
---对整个数据库下的1000张表进行单独备份，
---排除sys，performance，information_schema
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name,"
>/tmp/",table_schema,"_",table_name,".sql")
FROM information_schema.tables
WHERE table_schema NOT IN('sys','performance','information_schema')
INTO OUTFILE '/tmp/bak.sh';
注意，这里会报一个secure-file-priv错，说什么你把数据导出来了，不安全
解决方法：
添加secure-file-priv=/tmp到my.cnf服务端，重启MySQL让其生效即可。
三、运维必会show语句
show databases; 查看所有数据库名
show tables; 查看当前库下的表名
show tables from world; 查看world数据库下的表名
show create database 查看建库语句
show create table 查看建表语句
show grants for root@'localhost' 查看用户权限信息
show charset 查看所有的字符集
show collation 查看校对规则
show full processlist 查看数据库连接情况
show status 查看数据库的整体状态
show status like '%lock%' 模糊查看数据库的整体状态
show variables 查看数据库所有变量情况
show variables like '%innodb%' 查看数据库所有变量情况
show engines 查看所有支持存储引擎
show engine innodb status 查看所有innodb存储引擎状态情况
show binary logs 查看二进制日志情况
show binlog events in 查看二进制日志事件
show relaylog events in 查看relay日志事件
show slave status 查看从库状态
show master status 查看数据库binlog位置信息
show index from 表的索引情况
网友评论