5、视图（VIEW）

什么是视图？

• 视图是一种虚拟存在的表，基于 SQL 语句的结果集的可视化的表。本身是不具有数据的，占用很少的内存空间。
• 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。视图的创建和删除不影响对应的基表。
• 在数据库中，视图不会保存数据，数据真正保存在数据表中。
• 视图包含行和列，就像一个真实的表。视图中的字段是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的SELECT语句

创建视图

CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

精简版:

CREATE VIEW 视图名称 AS 查询语句;

查看视图

-- 查看数据库的表对象、视图对象
SHOW TABLES;
-- 查看视图的结构
DESC / DESCRIBE 视图名称;
-- 查看视图的属性信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称';
-- 查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;

删除视图

-- 删除视图只是删除视图的定义，并不会删除基表的数据
DROP VIEW IF EXISTS 视图名称;

示例：列出流水表详细信息

• 创建账号表

  CREATE TABLE account(
    account_id VARCHAR(36)   PRIMARY KEY, -- 使用UUID生成随机的唯一值
    account_name VARCHAR(15)   NOT NULL,
    balance      DECIMAL(10,2)  NOT NULL, 
    reg_date     DATE          NOT NULL
  );

• 插入两条记录到账号表

  INSERT INTO account(account_id,account_name,balance,reg_date) VALUES(UUID(),'张三',1000,CURDATE());
  INSERT INTO account(account_id,account_name,balance,reg_date) VALUES(UUID(),'李四',2000,CURDATE());

　  　

• 创建流水表

  create table transrecord(
      trans_id   varchar(36) primary key,  -- 使用UUID_SHORT函数生成随机的唯一值
       sender_id     varchar(36),
      receiver_id    varchar(36),
      trans_balance  decimal(10,2),
      trans_datetime  datetime
  );

• 插入测试记录到账号表 

  INSERT INTO transrecord(trans_id,sender_id,receiver_id,trans_balance,trans_datetime)
  VALUES(
  UUID_SHORT(),
  'aec59761-ab3b-11ec-b6ed-0a0027000018', -- 张三
  'aec8301d-ab3b-11ec-b6ed-0a0027000018', -- 李四
  200,
  SYSDATE());
  
  INSERT INTO transrecord(trans_id,sender_id,receiver_id,trans_balance,trans_datetime)
          VALUES(
  UUID_SHORT(),
  'aec8301d-ab3b-11ec-b6ed-0a0027000018', -- 李四
  'aec59761-ab3b-11ec-b6ed-0a0027000018', -- 张三
  100,
  SYSDATE());

　　

•  示例列出流水表详细信息

  -- 流水号发送者ID发送者姓名收款者ID 收款者姓名转账金额转账时间
  SELECT a.trans_id,
  a.sender_id,b.account_name,
  a.receiver_id,c.account_name,
  a.trans_balance,a.trans_datetime
  FROM transrecord a INNER JOIN account b ON a.sender_id=b.account_id
  INNER JOIN account c ON a.receiver_id=c.account_id;

         

• 查询指定用户的转出详情

  SELECT a.trans_id,
  a.sender_id,b.account_name,
  a.receiver_id,c.account_name,
  a.trans_balance,a.trans_datetime
  FROM transrecord a INNER JOIN account b ON a.sender_id=b.account_id
  INNER JOIN account c ON a.receiver_id=c.account_id
  where a.sender_id='aec8301d-ab3b-11ec-b6ed-0a0027000018';

  

• 查询指定用户的转入详情--转入

  SELECT a.trans_id,
  a.sender_id,b.account_name,
  a.receiver_id,c.account_name,
  a.trans_balance,a.trans_datetime
  FROM transrecord a INNER JOIN account b ON a.sender_id=b.account_id
  INNER JOIN account c ON a.receiver_id=c.account_id
  where a.receiver_id='aec8301d-ab3b-11ec-b6ed-0a0027000018';

   

  

示例：查询流水账详情

创建视图

create or replace view view_transrecord
as 
select  a.trans_id as transId,
a.sender_id as senderId,
b.account_name as sendName,
a.receiver_id as receiverId,
c.account_name as receiverName,
a.trans_balance as transBalance,
a.trans_datetime as transDatetime
FROM transrecord a INNER JOIN account b ON a.sender_id=b.account_id
INNER JOIN account c ON a.receiver_id=c.account_id;

①查询流水账详情

select *from view_transrecord;

②查询指定用户的转出详情

select *from view_transrecord
where senderId = 'aec8301d-ab3b-11ec-b6ed-0a0027000018';

③查询指定用户的转入详情

select *from view_transrecord
where receiverID = 'aec59761-ab3b-11ec-b6ed-0a0027000018';