MySQL

about

视图的定义

视图是虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义；同真实表(基表)一样，视图包含一系列带有名称的字段和记录，在使用视图时动态生成。视图的数据变化会影响到基表，基表的数据变化也会影响到视图(insert、update、delete)。

另外，创建视图需要有create view权限，并且查询的列有select权限，使用create or update or alter修改视图，还需要有相应的drop权限。

视图可以查询、修改和删除，但不允许通过视图向基表插入数据

视图的作用

对其中所引用的基础表来说，视图的做用类似于筛选，定义视图的筛选可以来自当前或者其他数据库的一个或多个表，也可以是其他视图；通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。

一、视图基本操作

1.1.创建视图

 -- 基本语法
2 CREATE VIEW 视图名称 AS SQL语句;
3 
4 -- 示例：查询中国所有城市信息,只展示前10条
5 CREATE VIEW v1 AS
6 SELECT * FROM city WHERE countrycode='CHN' LIMIT 10;

1.2 、查询视图

-- 查询 v1 视图
SELECT * FROM v1;

1. 3. 修改视图

ALTER VIEW 视图名称 as SQL语句;

1.4 删除视图

DROP VIEW 视图名称;

二、information_schema

information_schema是视图库(虚拟库)：

USE information_schema;
SHOW TABLES;    -- 返回了一堆视图

表由两部分组成：

• 元数据，表相关信息+字段信息(属性，约束)。
• 数据行，就是普通的记录了

元数据单独存储在"基表"中，是我们无法直接访问的。但MySQL提供了DDL、DCL来进行对元数据修改；提供了information_schema和SHOW语句查询元数据。

MySQL5.7版本中，共有information_schema、performance_schema、sys三张视图库。但在MySQL早期版本中，只有information_schema视图库，后来方便，就把一些复杂的操作封装了一下，这就是performance_schema视图库，再后来又有了sys库，直到如今的版本中的三张视图库。

2.1 常用操作

这次，我们主要对information_schema.TABLES表进行学习，这个表存储了整个数据库中所有表的元数据。

-- information_schema.TABLES中常用的字段
DESC infoRmation_schema.TABLES;
TABLE_SCHEMA    -- 库名
TABLE_NAME        -- 表名
NEGINE            -- 引擎
TABLE_ROWS        -- 表行数
AVG_ROW_LENGTH    -- 表中行平均长度(字节)
INDEX_LENGTH    -- 索引的占用空间大小(字节)

知道了上面常用的字段之后，来看看我们平常用它来做什么

-- 查询information_schema.TABLES表信息
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,INDEX_LENGTH
FROM information_schema.TABLES;

-- 查询整个数据库中所有库和对应的表信息
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;

-- 查询所有使用innodb引擎的表及所在的库
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE
FROM information_schema.TABLES
WHERE ENGINE='innodb';

-- 统计指定数据库(world)下每张表的磁盘空间占用
SELECT  TABLE_SCHEMA,TABLE_NAME,(TABLE_ROWS * AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 AS 'size(KB)'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='world';

-- 统计所有数据库的总磁盘空间占用
SELECT COUNT(TABLE_SCHEMA) AS '数据库个数',SUM((TABLE_ROWS * AVG_ROW_LENGTH + INDEX_LENGTH) / 1024) AS 'size(KB)'
FROM information_schema.TABLES;

 

2.2 show命令

前面，我们使用SELECT命令对information_schema.TABLES表一顿操作；那么SHOW命令就是对常用的视图操作进行封装，便于操作，其实它本质上也是对information_schema库进行操作。

下面列举一些常用的SHOW命令：

show  databases;                    -- 查看所有数据库
show tables;                        -- 查看当前库的所有表
show TABLES FROM                    -- 查看某个指定库下的表
show create database world            -- 查看建库语句
show create table world.city        -- 查看建表语句
show grants for root@'localhost'    -- 查看用户的权限信息
show charset;                        -- 查看字符集
show collation                        -- 查看校对规则
show processlist;                    -- 查看数据库连接情况
show index from                        -- 表的索引情况
show status                         -- 数据库状态查看
show STATUS LIKE '%lock%';        -- 模糊查询数据库某些状态
show VARIABLES                    -- 查看所有配置信息
show variables LIKE '%lock%';     -- 查看部分配置信息
show engines                    -- 查看支持的所有的存储引擎
show engine innodb statusG        -- 查看InnoDB引擎相关的状态信息
show binary logs                -- 列举所有的二进制日志
show master status                -- 查看数据库的日志位置信息
show binlog evnets in            -- 查看二进制日志事件
show slave status G            -- 查看从库状态
show RELAYLOG EVENTS             -- 查看从库relaylog事件信息
desc (show colums from city)     -- 查看表的列定义信息
-- 不知道更多，请使用help
help show