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; #查看表的索引情况