一、别名设置
统计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位置信息