Day05 information_scherma讲解

1. distinct 合并函数：

对单个或多个列的去重，可单独使用

执行过程：

8.0版本以前， 对列的值先排序，再去重

例：

3306 [school]>select distinct(cno) from sc;
+------+
| cno  |
+------+
| 1001 |
| 1002 |
| 1003 |
+------+
3 rows in set (0.00 sec)

union [distinct]合并
union all 不合并（性能更好，不用排序）

2. 别名（as）:简化字符串（在一个表中不能重复）（临时生效）

应用场景：
（1）表别名（不能重复）

teacher as te（推荐使用）
teacher te

例：

select teacher.tname,GROUP_CONCAT(student.sname)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='oldguo' and sc.score<60
group by teacher.tno;

设置别名的：
select te.tname,GROUP_CONCAT(st.sname)
from teacher as te
join course as co
on te.tno=co.tno
join sc
on co.cno=sc.cno
join student as st
on sc.sno=st.sno
where te.tname='oldguo' and sc.score<60
group by te.tno;

（2）列别名（给列设置啥名，就显示啥名）

3306 [school]>select count(distinct(name)) as 个数 from world.city;
+--------+
| 个数   |
+--------+
|   3998 |
+--------+
1 row in set (0.00 sec)

调用列别名的子句：

hivaing
rder by

3. 外连接 left（效果与内连接一样）

左外连接：

除了把有关联的数据拿出来外，还会把左边的数据也拿出来，右边无关的数据以NULL填充并显示。
还可以对内连接查询起到优化作用
写法:
优化内连接写法：
3306 [world]>select a.name,b.name,b.surfacearea
    -> from city as a
    -> left join country as b
    -> on a.countrycode=b.code
    -> where a.population<100;
+-----------+----------+-------------+
| name      | name     | surfacearea |
+-----------+----------+-------------+
| Adamstown | Pitcairn |       49.00 |
+-----------+----------+-------------+
1 row in set (0.00 sec)
 左外连接写法：
select a.name,b.name,b.surfacearea from city as a left join country as b on a.coun.countrycode=b.code and a.population<100 limit 10;
+----------------+------+-------------+
| name           | name | surfacearea |
+----------------+------+-------------+
| Kabul          | NULL |        NULL |
| Qandahar       | NULL |        NULL |
| Herat          | NULL |        NULL |
| Mazar-e-Sharif | NULL |        NULL |
| Amsterdam      | NULL |        NULL |
| Rotterdam      | NULL |        NULL |
| Haag           | NULL |        NULL |
| Utrecht        | NULL |        NULL |
| Eindhoven      | NULL |        NULL |
| Tilburg        | NULL |        NULL |
+----------------+------+-------------+
10 rows in set (0.00 sec)

右外连接 right：

除了把有关联的数据拿出来外，还会把右边的数据也拿出来，然后形成一个新表，左边无关联的数据以NULL的形式显示。
写法：
3306 [world]>select a.name,b.name,b.surfacearea from city as a right join country as b on a.countrycode=b.code and a.population<100 limit 10;
+------+----------------------+-------------+
| name | name                 | surfacearea |
+------+----------------------+-------------+
| NULL | Aruba                |      193.00 |
| NULL | Afghanistan          |   652090.00 |
| NULL | Angola               |  1246700.00 |
| NULL | Anguilla             |       96.00 |
| NULL | Albania              |    28748.00 |
| NULL | Andorra              |      468.00 |
| NULL | Netherlands Antilles |      800.00 |
| NULL | United Arab Emirates |    83600.00 |
| NULL | Argentina            |  2780400.00 |
| NULL | Armenia              |    29800.00 |
+------+----------------------+-------------+
10 rows in set (0.00 sec)

4. information_scherma（虚拟库，数据库启动时自动生成的视图）的基本使用

什么是视图？

视图也可以理解为别名
创建视图关键字：
create view as
例子：
create view t as select a.name,b.name,b.surfacearea from city as a left join country as b on a.countrycode=b.code where a.population<100;

select * from t;

4.1 information_scherma.tables

什么是元数据

元数据存放在基表中，也叫元数据表。基表无法查询和修改的。
还有一部分也保存的状态类元数据：ibdata1
可以通过DDL修改基表（元数据）。
也可以间接查看，show(片面的，只能看当前库下的表)、desc、information_schema（全局类的统计和查询）

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

-- 例子：information_chema
-- 要查询整个数据库下所有的库对应的表的名字，每个库显示成一行

use information_schema;
select table_schema,group_concat(table_name)
from information_schema.`TABLES`
group by table_schema;

--- 统计一下每个库下表的个数

select table_schema,count(table_name)
from information_schema.`TABLES`
group by table_schema;

+--------------------+-------------------+
| table_schema       | count(table_name) |
+--------------------+-------------------+
| information_schema |                61 |
| mysql              |                31 |
| oldboy             |                 1 |
| oldguo             |                 3 |
| performance_schema |                87 |
| school             |                 7 |
| sys                |               101 |
| world              |                 3 |
+--------------------+-------------------+
8 rows in set (0.00 sec)

（面试题：）-- 统计一下每个库的真实数据量
每张表数据量=平均行长度x行数+索引长度

select table_schema,count(table_name),sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)
from information_schema.tables
group by table_schema;

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;

统计数据库中的总数据大小

SELECT
SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS ToTAL_MB 
FROM information_schema.TABLES;

concat()拼接命令使用

3306 [information_schema]>select concat(user,"@","'",host,"'") from mysql.user;
+-------------------------------+
| concat(user,"@","'",host,"'") |
+-------------------------------+
| oldboy@'10.0.0.%'             |
| oldguo@'10.0.0.%'             |
| root@'10.0.0.%'               |
| zhihu@'10.0.0.%'              |
| oldboy@'172.16.1.%'           |
| mysql.session@'localhost'     |
| mysql.sys@'localhost'         |
| root@'localhost'              |
+-------------------------------+
8 rows in set (0.00 sec)

模仿以上命令，对整个数据库下的1000张表进行单独备份,排除sys,performance,information_schema

vim /etc/my.cnf
secure-file-priv=/tmp
systemctl restart mysqld

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/data_bak.sh';

[root@db01 /tmp]# head -5 data_bak.sh 
mysqldump -uroot -p123 mysql columns_priv>/tmp/mysql_columns_priv.sql
mysqldump -uroot -p123 mysql db>/tmp/mysql_db.sql
mysqldump -uroot -p123 mysql engine_cost>/tmp/mysql_engine_cost.sql
mysqldump -uroot -p123 mysql event>/tmp/mysql_event.sql
mysqldump -uroot -p123 mysql func>/tmp/mysql_func.sql

模仿一下语句，批量实现world库下所有表的操作语句生成

alter table world.city discard tablespace;

select concat("alter table ",table_schema,".",table_name,"discard tablespacse;")
from information_schema.`TABLES`
where table_schema='world'
into outfile '/tmp/discard.sql';

5. show

show databases; #查看所有数据库名
show tables; #查看当前库下的表名
show tables from world; #查看world数据库下的表名
show create databales; #查看建库语句
show create tables; #查看建表语句
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 binglog events in; #查看二进制日志事件
show relaylog events in; #查看relay日志事件
show slave status; #查看从库状态
show master status; #查看数据看binglog位置信息
show index from; #查看表的索引情况