day05

一、去重名：DISTINCT

1.格式:

SELECT DISTINCT(列名)
FROM 表名；

二、别名：AS

1.表别名

select a.time,group_concat(d.sname)
from teacher AS a
join course AS b
on a.tno = b.tno
join score AS c
on b.cno = c.cno
jion student AS d
on c.sno = d.sno
where a.tname='oldguo' AND c.score<60
group by a.tno;

2.列别名

select count(distinct(name)) as 个数 from world.city;

三、外连接

left join

左外连接:

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;

四、information_schema.tables

1.元数据

---->'基表'(无法直接查询和修改)
---->DDL 进行元数据修改
---->show,desc(show),information_schema(全局类的统计和查询)

use information_schema;
desc tables;


TABLE_SCHEMA ###表所在的库
TABLE_NAME  ###表名
ENGINE  ###表的存储引擎
TABLE_ROWS ###表的行数
AVG_ROW_LENGTH ###平均行长度
INDEX_LENGTH  ###索引的长度

---	含义
TABLE_SCHEMA	表所在的库
TABLE_NAME	表名
ENGINE	表的存储引擎
TABLE_ROWS	表的行数
AVG_ROW_LENGTH	平均行长度
INDEX_LENGTH	索引的长度

2.例子：

(1)查询整个数据库中所有的库名对应的表名

SELECT table_schema,table_name
FROM information_schema.tables;

(2)查询world和school库下的所有表

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'

(3)查询整个数据库中所有库对应的表名，每个库显示成一行

SELECT table_schema,GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;

(4)统计一下每个库下的表的个数

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

(5)统计一下每个库的真实数据量

select table_schema,sum(avg_row_length*table_rows+index_length)
from information_schema.tables
group by table_schema;

3. information_schema.tables+CONCAT()拼接命令

使用方法：

select user,host from mysql.user

select concat(user,"@",host)
from mysql.user;
或者：
select concat(user,"@","'",host,"'")
from mysql.user;

生产需求1：

模仿下面命令，对整个数据库下的1000张表进行单独备份。

mysqldump -uroot -p123 world city >/tmp/world_city.sql;

解答：

select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql;")
from information_schema.tables;

排除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/abc.sh';

例子：模仿以下语句，批量实现world下所有表的操作语句生成

alter table world.city discard tablespace;

解答：

select concat("alter table ",table_schema,".",table_name," ","discard"," ","tablespace;")
from information_schema.tables
where table_schema='world'

五、show

show命令	含义
show databases;	查看所有数据库名
show tables;	查看当前库下的所有表名
show tables from world	查看world库下的所有表名

show databases;                  ###查看所有数据库名
show tables;                     ###查看当前库下的所有表名
show tables from world;          ###查看world库下的所有表名
show create database;            ###查看建库语句
show create database oldboy;     ###查看oldboyde 建库语句
show grants for root@'localhost' ###查看用户权限信息
show charset                     ###查看所有的字符集
show collation                   ###查看校对规则
show full processlist            ###查看数据库连接状态
show status                      ###查看数据库的整体状态
show status like '%lock%'        ###模糊查看数据库的整体状态
show varables                    ###查查看数据库所有变量情况
show varables like '%innodb%'    ###查看数据库所有变量情况
show engine                      ###查看所有支持存储引擎
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                  ###查看表的索引情况

六、索引

6.1 索引作用

提供了类似书中目录的作用，目的是为了优化查询。

6.2 索引的种类（算法）

(1)B树索引
(2)Hash索引
(3)R树
(4)Full text
(5)GIS

6.3 B树算法普及

B-tree
B+tree (双向指针)在范围查询方面提供了更好的性能
B*tree

6.4 在功能上的分类*****

6.4.1 辅助索引（S）怎么构建B树结构的？

(1) 辅助索引是基于表的列进而生成的
(2)取出索引列的所有值(取出键值)
(3)进行所有键值的排序
(4)将所有的键值按顺序落到BTree索引的叶子节点上
(5)进而生成枝节点和根节点
(6)叶子节点除了存储键值之外，还存储相邻叶子节点的指针，另外还会保存原表数据的指针

6.4.2 聚集索引(C)怎么构建B树结构的？

(1)建表是有主键列(ID)
(2)表中进行数据存储，会按照ID列的顺序，有序的存储一行一行的数据到数据也上(这个动作叫做聚集索引组织)
(3)表中的数据页被作为聚集索引的叶子节点
(4)把叶子节点的主键值生成上层的枝节点和根节点

6.4.3 聚集索引和辅助索引构成区别总结

聚集索引，只能有一个，非空唯一，一般是主键
辅助索引，可能有多个，是配合聚集索引使用的
聚集索引叶子节点，就是磁盘的数据行存储的数据页
MySQL是根据聚集索引，组织存储数据，数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引，只会提取索引键值，进而自动排序生成B树结构

6.5 辅助索引细分

ID	类型	说明
1	单列辅助索引	普通的单列辅助索引
2	联合多列辅助索引(覆盖索引)	多个列作为索引条件，生成索引树，理论是好的，可以大量的回表查询
3	唯一索引	索引列的值都是唯一的

6.6关于索引的高度受什么影响？

ID	影响因素	解决方法
1	数据行多	分表，分库，分布式
2	索引列字符长度	前缀索引
3	char、varchar	变长长度字符串，使用char；定长长度字符串，使用varchar
4	enum	优化索引高度，能用则用