mysql如何显示ddl_Mysql DDL语句之视图

Mysql 视图是一个虚拟表，内容由 select 查询语句定义, 同真实的表数据一致, 但是视图并不在数据库中以存储的数据值形式存在。

试图引用自定义查询表的字段, 并且在引用试图时动态生成, 对其所引用的基础表来说 Mysql 视图的作用类似于筛选。

toc

定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。

视图查询没有任何限制，通过它们进行数据修改时的限制也很少。

视图是存储在数据库中的 SQL 查询语句，它主要出于两种原因：

安全原因, 视图可以隐藏一些敏感的信息。

简化查询, 使复杂的查询易于理解和使用

创建视图

创建单表视图环境准备

## 创建表

mysql> use test;

mysql> create table t2(

id int primary key AUTO_INCREMENT not null,

name varchar(30) not null,

sex enum('man','gril') default 'man' not null,

time date not null,

post varchar(50) not null,

job varchar(100),

salary double(15,2) not null,

dep_id int

);

## 插入数据

mysql> insert into t2(name,sex,time,post,job,salary,dep_id) values

('jack','man','20180202','it','teach',5000,100),

('tom','man','20180203','it','teach',5500,100),

('robin','man','20180202','it','teach',8000,100),

('alice','gril','20180202','it','teach',7200,100),

('lisi','man','20180202','hr','hrcc',600,101),

('harry','man','20180202','hr', NULL,6000,101),

('trf','gril','20180206','sale','salecc',20000,102),

('test','gril','20180205','sale','salecc',2200,102),

('dog','man','20180205','sale', NULL,2200,102),

('alex','man','20180205','sale','',2200,102);

## 查看表结构

mysql> desc t2;

+--------+--------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+--------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(30) | NO | | NULL | |

| sex | enum('man','gril') | NO | | man | |

| time | date | NO | | NULL | |

| post | varchar(50) | NO | | NULL | |

| job | varchar(100) | YES | | NULL | |

| salary | double(15,2) | NO | | NULL | |

| dep_id | int(11) | YES | | NULL | |

+--------+--------------------+------+-----+---------+----------------+

## 查询所有数据

mysql> select * from t2;

+----+-------+------+------------+------+--------+----------+--------+

| id | name | sex | time | post | job | salary | dep_id |

+----+-------+------+------------+------+--------+----------+--------+

| 1 | jack | man | 2018-02-02 | it | teach | 5000.00 | 100 |

| 2 | tom | man | 2018-02-03 | it | teach | 5500.00 | 100 |

| 3 | robin | man | 2018-02-02 | it | teach | 8000.00 | 100 |

| 4 | alice | gril | 2018-02-02 | it | teach | 7200.00 | 100 |

| 5 | lisi | man | 2018-02-02 | hr | hrcc | 600.00 | 101 |

| 6 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 101 |

| 7 | trf | gril | 2018-02-06 | sale | salecc | 20000.00 | 102 |

| 8 | test | gril | 2018-02-05 | sale | salecc | 2200.00 | 102 |

| 9 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 102 |

| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 102 |

+----+-------+------+------------+------+--------+----------+--------+

创建单表视图

语法：CREATE VIEW 视图名 AS SELECT 语句;

## 创建视图，只能看到姓名，性别，部门，职责

mysql> create view view_t2 as select name,sex,post,job from test.t2;

## 查看视图表结构

mysql> desc view_t2;

+-------+--------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------------+------+-----+---------+-------+

| name | varchar(30) | NO | | NULL | |

| sex | enum('man','gril') | NO | | man | |

| post | varchar(50) | NO | | NULL | |

| job | varchar(100) | YES | | NULL | |

+-------+--------------------+------+-----+---------+-------+

## 查看视图所有内容

mysql> select * from view_t2;

+-------+------+------+--------+

| name | sex | post | job |

+-------+------+------+--------+

| jack | man | it | teach |

| tom | man | it | teach |

| robin | man | it | teach |

| alice | gril | it | teach |

| lisi | man | hr | hrcc |

| harry | man | hr | NULL |

| trf | gril | sale | salecc |

| test | gril | sale | salecc |

| dog | man | sale | NULL |

| alex | man | sale | |

+-------+------+------+--------+

创建多表视图环境准备

mysql> create database shop;

mysql> use shop;

## 创建产品表

mysql> create table product(

id int unsigned auto_increment primary key not null,

name varchar(60) not null,

price double not null

);

## 插入产品数据

mysql> insert into product(name,price) values

('apple',5),

('balane',6),

('pear',7);

## 创建销售表

mysql> create table purchase(

id int unsigned auto_increment primary key not null,

name varchar(60) not null,

quantity int not null default 0,

gen_time datetime not null

);

## 插入销售数据

mysql> insert into purchase(name,quantity,gen_time) values

('apple',7,now()),

('pear',10,now());

## 查询产品卖出金额

mysql> select product.name,product.price,purchase.quantity,product.price * purchase.quantity as total_value from product,purchase where product.name = purchase.name;

+-------+-------+----------+-------------+

| name | price | quantity | total_value |

+-------+-------+----------+-------------+

| apple | 5 | 7 | 35 |

| pear | 7 | 10 | 70 |

+-------+-------+----------+-------------+

创建多表视图

创建视图

mysql> create view total_product as select product.name,product.price,purchase.quantity,product.price * purchase.quantity as total from purchase,product where purchase.name = product.name;

## 查询视图数据

mysql> select * from total_product;

+-------+-------+----------+-------+

| name | price | quantity | total |

+-------+-------+----------+-------+

| apple | 5 | 7 | 35 |

| pear | 7 | 10 | 70 |

+-------+-------+----------+-------+

## 再次卖出产品后

mysql> insert into purchase(name,quantity,gen_time) values ('balane',20,now());

## 再次查询视图数据

+--------+-------+----------+-------+

| name | price | quantity | total |

+--------+-------+----------+-------+

| apple | 5 | 7 | 35 |

| balane | 6 | 20 | 120 |

| pear | 7 | 10 | 70 |

+--------+-------+----------+-------+

查看视图

查看视图名

SHOW TABLES 视图名;

查看视图详细信息

SHOW TABLE STATUS FROM 视图名\G

查看视图定义信息

SHOW CREATE VIEW 视图名\G

查看视图表结构

DESC 视图名;

修改视图

语法：ALTER VIEW 视图名 AS 新 SELECT 语句

mysql> use test;

mysql> alter view view_t2 as select name,post,job from test.t2;

mysql> desc view_t2;

+-------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| name | varchar(30) | NO | | NULL | |

| post | varchar(50) | NO | | NULL | |

| job | varchar(100) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

mysql> select * from view_t2;

+-------+------+--------+

| name | post | job |

+-------+------+--------+

| jack | it | teach |

| tom | it | teach |

| robin | it | teach |

| alice | it | teach |

| lisi | hr | hrcc |

| harry | hr | NULL |

| trf | sale | salecc |

| test | sale | salecc |

| dog | sale | NULL |

| alex | sale | |

+-------+------+--------+

删除视图

语法：DROP VIEW view_name [,view_name]…;

mysql> drop view view_t2;