一、别名设置

统计city表行数

 mysql> select count(*) from world.city;
 +----------+
 | count(*) |
 +----------+
 |     4079 |
 +----------+
 mysql> select count(distinct(name)) from world.city;
 +-----------------------+
 | count(distinct(name)) |
 +-----------------------+
 |                  3998 |
 +-----------------------+

distinct先排序，再去重（8.0以前）

先查看总行数，然后使用distinct去重，如果数量不一致说明不能使用此列作为唯一主键
uniall 只多条语句结果一样的时候才可以使用，多个结果集结合形成一张临时表

表别名影响

 group by子句-->
 select后执行条件-->
 having子句-->
 order by-->
 group by之后才会显示select后执行的语句

列别名影响

 having子句-->
 order by-->

列别名

 mysql> select count(distinct(name)) as '省/州' from world.city;
 +---------+
 | 省/州   |
 +---------+
 |    3998 |
 +---------+

表别名

 from student AS stu

二、information_schema 视图

1.元数据：文件的属性， 表的属性 库的属性 存放在‘基表’ 无法直接查询和修改。只能通过DDL create ater drop 来进行修改。

show desc information schema 全局类的统计和查询

 use information_schema
 desc tables;
 tables 存放着全局内所有表的信息
 TABLE_SCHEMA       表所在的库
 TABLE_NAME          表明
 TABLE_TYPE             表类型
 ENGINE           表的存储引擎
 TABLE_ROWS       表行数
 AVG_ROW_LENGTH      平均行长度
 INDEX_LENGTH       索引长度

2.查询整个数据库中所有的库对应的表的表名

 SELECT table_schema,table_name 
 FROM 
 information_schema.tables;

3.统计每个库下的表的个数

 mysql> SELECT table_schema,COUNT(table_name)
-> FROM
-> information_schema.tables
-> GROUP BY table_schema;
 +--------------------+-------------------+
 | table_schema       | COUNT(table_name) |
 +--------------------+-------------------+
 | information_schema |                61 |
 | mysql              |                31 |
 | oldchen            |                 5 |
 | performance_schema |                87 |
 | school             |                 4 |
 | sys                |               101 |
 | world              |                 3 |
 +--------------------+-------------------+

4.统计每个库的真实数据量

 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              |                31 |  2.25533295 |
 | oldchen            |                 5 | 44.84346104 |
 | performance_schema |                87 |  0.00000000 |
 | school             |                 4 |  0.06248188 |
 | sys                |               101 |  0.01562500 |
 | world              |                 3 |  0.76149845 |
 +--------------------+-------------------+-------------+

三、拼接函数

concat 拼接函数 添加的内容用双引号分隔
1.通过拼接函数 生成对整个数据库下的所有表进行单独备份。
实例：mysqldump -uroot -p123 world city >/tmp/world_city.sql

 mysql> 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'') limit 5;
 +-------------------------------------------------------------------------------------------------------+
 | concat("mysqldump -uroot -p123 ",table_schema," ",table_name,">/tmp/",table_schema,table_name,'.sql') |
 +-------------------------------------------------------------------------------------------------------+
 | mysqldump -uroot -p123 mysql columns_priv>/tmp/mysqlcolumns_priv.sql                                  |
 | mysqldump -uroot -p123 mysql db>/tmp/mysqldb.sql                                                      |
 | mysqldump -uroot -p123 mysql engine_cost>/tmp/mysqlengine_cost.sql                                    |
 | mysqldump -uroot -p123 mysql event>/tmp/mysqlevent.sql                                                |
 | mysqldump -uroot -p123 mysql func>/tmp/mysqlfunc.sql                                                  |
 +-------------------------------------------------------------------------------------------------------+

加上 into outfile ‘/tmp/bak.sh' 可以将输出的内容追加到文件

2.批量实现world下所有表的操作
alter table world.city discard tablespace;

 mysql> select concat("alter table ",table_schema,".",table_name," discarrd tablespace") from information_schema.tables where table_schema='worldd';
 +--------------------------------------------------------------------------+
 | concat("alter table ",table_schema,".",table_name," discard tablespace") |
 +--------------------------------------------------------------------------+
 | alter table world.city discard tablespace                                |
 | alter table world.country discard tablespace                             |
 | alter table world.countrylanguage discard tablespace                     |
 +--------------------------------------------------------------------------+

四、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位置信息