mysql数据库之视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。
对视图的操作和对普通表的操作一样。
视图具有如下好处:

1.简化复杂的 SQL 操作，比如复杂的连接；
2.只使用实际表的一部分数据；
3.通过只给用户访问视图的权限，保证数据的安全性；
4.更改数据格式和表示。

创建视图的sql语句

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

视图操作示例

单表视图

1.首先创建一个数据库，这一步是必要的。

create database test;
use test;

2.生成表数据，这里引用了hmdp的数据库数据。

create table tb_shop_type
(id          bigint unsigned auto_increment comment '主键'primary key,name        varchar(32)                         null comment '类型名称',icon        varchar(255)                        null comment '图标',sort        int(3) unsigned                     null comment '顺序',create_time timestamp default CURRENT_TIMESTAMP null comment '创建时间',update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '更新时间'
)charset = utf8mb4;INSERT INTO  tb_shop_type (id, name, icon, sort, create_time, update_time) VALUES (1, '美食', '/types/ms.png', 1, '2021-12-22 20:17:47', '2021-12-23 11:24:31');
INSERT INTO  tb_shop_type (id, name, icon, sort, create_time, update_time) VALUES (2, 'KTV', '/types/KTV.png', 2, '2021-12-22 20:18:27', '2021-12-23 11:24:31');
INSERT INTO  tb_shop_type (id, name, icon, sort, create_time, update_time) VALUES (3, '丽人·美发', '/types/lrmf.png', 3, '2021-12-22 20:18:48', '2021-12-23 11:24:31');
INSERT INTO  tb_shop_type (id, name, icon, sort, create_time, update_time) VALUES (4, '健身运动', '/types/jsyd.png', 10, '2021-12-22 20:19:04', '2021-12-23 11:24:31');
INSERT INTO  tb_shop_type (id, name, icon, sort, create_time, update_time) VALUES (5, '按摩·足疗', '/types/amzl.png', 5, '2021-12-22 20:19:27', '2021-12-23 11:24:31');
INSERT INTO  tb_shop_type (id, name, icon, sort, create_time, update_time) VALUES (6, '美容SPA', '/types/spa.png', 6, '2021-12-22 20:19:35', '2021-12-23 11:24:31');
INSERT INTO  tb_shop_type (id, name, icon, sort, create_time, update_time) VALUES (7, '亲子游乐', '/types/qzyl.png', 7, '2021-12-22 20:19:53', '2021-12-23 11:24:31');
INSERT INTO  tb_shop_type (id, name, icon, sort, create_time, update_time) VALUES (8, '酒吧', '/types/jiuba.png', 8, '2021-12-22 20:20:02', '2021-12-23 11:24:31');
INSERT INTO  tb_shop_type (id, name, icon, sort, create_time, update_time) VALUES (9, '轰趴馆', '/types/hpg.png', 9, '2021-12-22 20:20:08', '2021-12-23 11:24:31');
INSERT INTO  tb_shop_type (id, name, icon, sort, create_time, update_time) VALUES (10, '美睫·美甲', '/types/mjmj.png', 4, '2021-12-22 20:21:46', '2021-12-23 11:24:31');

3.创建单表视图,视图引用shop_type表

create view my_shop asselect * from tb_shop_type;

4.查询视图

--- 当然了，这里可以加限制条件，就像查询表一样。
select * from my_shop;

5.执行delete，update，insert.修改视图的同时也会修改表

-- 单表查询的视图可以进行删除行数据和插入行数据 和修改行数据
delete from my_shop where id = 1;
insert into my_shop(id, name, icon, sort) VALUE (1,'展车馆','/types/KTV.png',1);
update my_shop set sort = 11 where id=1; -- 修改视图的同时也会修改表

6.总结
并不是所有的单表视图都可以修改的。遵循一个原则，就是修改视图要能让mysql修改对应表，而不是让mysql去做模糊的动作。

多表视图

1.我们在准备一张表

create table tb_shop
(id          bigint unsigned auto_increment comment '主键'primary key,name        varchar(128)                        not null comment '商铺名称',type_id     bigint unsigned                     not null comment '商铺类型的id',images      varchar(1024)                       not null comment '商铺图片，多个图片以'',''隔开',area        varchar(128)                        null comment '商圈，例如陆家嘴',address     varchar(255)                        not null comment '地址',x           double unsigned                     not null comment '经度',y           double unsigned                     not null comment '维度',avg_price   bigint(10) unsigned                 null comment '均价，取整数',sold        int unsigned zerofill               not null comment '销量',comments    int unsigned zerofill               not null comment '评论数量',score       int(2) unsigned zerofill            not null comment '评分，1~5分，乘10保存，避免小数',open_hours  varchar(32)                         null comment '营业时间，例如 10:00-22:00',create_time timestamp default CURRENT_TIMESTAMP null comment '创建时间',update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '更新时间'
)charset = utf8mb4;create index foreign_key_typeon tb_shop (type_id);INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (1, '103茶餐厅', 1, 'https://qcloud.dpfile.com/pc/jiclIsCKmOI2arxKN1Uf0Hx3PucIJH8q0QSz-Z8llzcN56-_QiKuOvyio1OOxsRtFoXqu0G3iT2T27qat3WhLVEuLYk00OmSS1IdNpm8K8sG4JN9RIm2mTKcbLtc2o2vfCF2ubeXzk49OsGrXt_KYDCngOyCwZK-s3fqawWswzk.jpg,https://qcloud.dpfile.com/pc/IOf6VX3qaBgFXFVgp75w-KKJmWZjFc8GXDU8g9bQC6YGCpAmG00QbfT4vCCBj7njuzFvxlbkWx5uwqY2qcjixFEuLYk00OmSS1IdNpm8K8sG4JN9RIm2mTKcbLtc2o2vmIU_8ZGOT1OjpJmLxG6urQ.jpg', '大关', '金华路锦昌文华苑29号', 120.149192, 30.316078, 80, 4215, 3035, 37, '10:00-22:00', '2021-12-22 18:10:39', '2023-04-06 22:18:52');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (2, '蔡馬洪涛烤肉·老北京铜锅涮羊肉', 1, 'https://p0.meituan.net/bbia/c1870d570e73accbc9fee90b48faca41195272.jpg,http://p0.meituan.net/mogu/397e40c28fc87715b3d5435710a9f88d706914.jpg,https://qcloud.dpfile.com/pc/MZTdRDqCZdbPDUO0Hk6lZENRKzpKRF7kavrkEI99OxqBZTzPfIxa5E33gBfGouhFuzFvxlbkWx5uwqY2qcjixFEuLYk00OmSS1IdNpm8K8sG4JN9RIm2mTKcbLtc2o2vmIU_8ZGOT1OjpJmLxG6urQ.jpg', '拱宸桥/上塘', '上塘路1035号（中国工商银行旁）', 120.151505, 30.333422, 85, 2160, 1460, 46, '11:30-03:00', '2021-12-22 19:00:13', '2022-01-11 16:12:26');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (3, '新白鹿餐厅(运河上街店)', 1, 'https://p0.meituan.net/biztone/694233_1619500156517.jpeg,https://img.meituan.net/msmerchant/876ca8983f7395556eda9ceb064e6bc51840883.png,https://img.meituan.net/msmerchant/86a76ed53c28eff709a36099aefe28b51554088.png', '运河上街', '台州路2号运河上街购物中心F5', 120.151954, 30.32497, 61, 12035, 8045, 47, '10:30-21:00', '2021-12-22 19:10:05', '2022-01-11 16:12:42');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (4, 'Mamala(杭州远洋乐堤港店)', 1, 'https://img.meituan.net/msmerchant/232f8fdf09050838bd33fb24e79f30f9606056.jpg,https://qcloud.dpfile.com/pc/rDe48Xe15nQOHCcEEkmKUp5wEKWbimt-HDeqYRWsYJseXNncvMiXbuED7x1tXqN4uzFvxlbkWx5uwqY2qcjixFEuLYk00OmSS1IdNpm8K8sG4JN9RIm2mTKcbLtc2o2vmIU_8ZGOT1OjpJmLxG6urQ.jpg', '拱宸桥/上塘', '丽水路66号远洋乐堤港商城2期1层B115号', 120.146659, 30.312742, 290, 13519, 9529, 49, '11:00-22:00', '2021-12-22 19:17:15', '2022-01-11 16:12:51');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (5, '海底捞火锅(水晶城购物中心店）', 1, 'https://img.meituan.net/msmerchant/054b5de0ba0b50c18a620cc37482129a45739.jpg,https://img.meituan.net/msmerchant/59b7eff9b60908d52bd4aea9ff356e6d145920.jpg,https://qcloud.dpfile.com/pc/Qe2PTEuvtJ5skpUXKKoW9OQ20qc7nIpHYEqJGBStJx0mpoyeBPQOJE4vOdYZwm9AuzFvxlbkWx5uwqY2qcjixFEuLYk00OmSS1IdNpm8K8sG4JN9RIm2mTKcbLtc2o2vmIU_8ZGOT1OjpJmLxG6urQ.jpg', '大关', '上塘路458号水晶城购物中心F6', 120.15778, 30.310633, 104, 4125, 2764, 49, '10:00-07:00', '2021-12-22 19:20:58', '2022-01-11 16:13:01');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (6, '幸福里老北京涮锅（丝联店）', 1, 'https://img.meituan.net/msmerchant/e71a2d0d693b3033c15522c43e03f09198239.jpg,https://img.meituan.net/msmerchant/9f8a966d60ffba00daf35458522273ca658239.jpg,https://img.meituan.net/msmerchant/ef9ca5ef6c05d381946fe4a9aa7d9808554502.jpg', '拱宸桥/上塘', '金华南路189号丝联166号', 120.148603, 30.318618, 130, 9531, 7324, 46, '11:00-13:50,17:00-20:50', '2021-12-22 19:24:53', '2022-01-11 16:13:09');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (7, '炉鱼(拱墅万达广场店)', 1, 'https://img.meituan.net/msmerchant/909434939a49b36f340523232924402166854.jpg,https://img.meituan.net/msmerchant/32fd2425f12e27db0160e837461c10303700032.jpg,https://img.meituan.net/msmerchant/f7022258ccb8dabef62a0514d3129562871160.jpg', '北部新城', '杭行路666号万达商业中心4幢2单元409室(铺位号4005)', 120.124691, 30.336819, 85, 2631, 1320, 47, '00:00-24:00', '2021-12-22 19:40:52', '2022-01-11 16:13:19');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (8, '浅草屋寿司（运河上街店）', 1, 'https://img.meituan.net/msmerchant/cf3dff697bf7f6e11f4b79c4e7d989e4591290.jpg,https://img.meituan.net/msmerchant/0b463f545355c8d8f021eb2987dcd0c8567811.jpg,https://img.meituan.net/msmerchant/c3c2516939efaf36c4ccc64b0e629fad587907.jpg', '运河上街', '拱墅区金华路80号运河上街B1', 120.150526, 30.325231, 88, 2406, 1206, 46, ' 11:00-21:30', '2021-12-22 19:51:06', '2022-01-11 16:13:25');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (9, '羊老三羊蝎子牛仔排北派炭火锅(运河上街店)', 1, 'https://p0.meituan.net/biztone/163160492_1624251899456.jpeg,https://img.meituan.net/msmerchant/e478eb16f7e31a7f8b29b5e3bab6de205500837.jpg,https://img.meituan.net/msmerchant/6173eb1d18b9d70ace7fdb3f2dd939662884857.jpg', '运河上街', '台州路2号运河上街购物中心F5', 120.150598, 30.325251, 101, 2763, 1363, 44, '11:00-21:30', '2021-12-22 19:53:59', '2022-01-11 16:13:34');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (10, '开乐迪KTV（运河上街店）', 2, 'https://p0.meituan.net/joymerchant/a575fd4adb0b9099c5c410058148b307-674435191.jpg,https://p0.meituan.net/merchantpic/68f11bf850e25e437c5f67decfd694ab2541634.jpg,https://p0.meituan.net/dpdeal/cb3a12225860ba2875e4ea26c6d14fcc197016.jpg', '运河上街', '台州路2号运河上街购物中心F4', 120.149093, 30.324666, 67, 26891, 902, 37, '00:00-24:00', '2021-12-22 20:25:16', '2021-12-22 20:25:16');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (11, 'INLOVE KTV(水晶城店)', 2, 'https://p0.meituan.net/dpmerchantpic/53e74b200211d68988a4f02ae9912c6c1076826.jpg,https://qcloud.dpfile.com/pc/4iWtIvzLzwM2MGgyPu1PCDb4SWEaKqUeHm--YAt1EwR5tn8kypBcqNwHnjg96EvT_Gd2X_f-v9T8Yj4uLt25Gg.jpg,https://qcloud.dpfile.com/pc/WZsJWRI447x1VG2x48Ujgu7vwqksi_9WitdKI4j3jvIgX4MZOpGNaFtM93oSSizbGybIjx5eX6WNgCPvcASYAw.jpg', '水晶城', '上塘路458号水晶城购物中心6层', 120.15853, 30.310002, 75, 35977, 5684, 47, '11:30-06:00', '2021-12-22 20:29:02', '2021-12-22 20:39:00');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (12, '魅(杭州远洋乐堤港店)', 2, 'https://p0.meituan.net/dpmerchantpic/63833f6ba0393e2e8722420ef33f3d40466664.jpg,https://p0.meituan.net/dpmerchantpic/ae3c94cc92c529c4b1d7f68cebed33fa105810.png,', '远洋乐堤港', '丽水路58号远洋乐堤港F4', 120.14983, 30.31211, 88, 6444, 235, 46, '10:00-02:00', '2021-12-22 20:34:34', '2021-12-22 20:34:34');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (13, '讴K拉量贩KTV(北城天地店)', 2, 'https://p1.meituan.net/merchantpic/598c83a8c0d06fe79ca01056e214d345875600.jpg,https://qcloud.dpfile.com/pc/HhvI0YyocYHRfGwJWqPQr34hRGRl4cWdvlNwn3dqghvi4WXlM2FY1te0-7pE3Wb9_Gd2X_f-v9T8Yj4uLt25Gg.jpg,https://qcloud.dpfile.com/pc/F5ZVzZaXFE27kvQzPnaL4V8O9QCpVw2nkzGrxZE8BqXgkfyTpNExfNG5CEPQX4pjGybIjx5eX6WNgCPvcASYAw.jpg', 'D32天阳购物中心', '湖州街567号北城天地5层', 120.130453, 30.327655, 58, 18997, 1857, 41, '12:00-02:00', '2021-12-22 20:38:54', '2021-12-22 20:40:04');
INSERT INTO  tb_shop (id, name, type_id, images, area, address, x, y, avg_price, sold, comments, score, open_hours, create_time, update_time) VALUES (14, '星聚会KTV(拱墅区万达店)', 2, 'https://p0.meituan.net/dpmerchantpic/f4cd6d8d4eb1959c3ea826aa05a552c01840451.jpg,https://p0.meituan.net/dpmerchantpic/2efc07aed856a8ab0fc75c86f4b9b0061655777.jpg,https://qcloud.dpfile.com/pc/zWfzzIorCohKT0bFwsfAlHuayWjI6DBEMPHHncmz36EEMU9f48PuD9VxLLDAjdoU_Gd2X_f-v9T8Yj4uLt25Gg.jpg', '北部新城', '杭行路666号万达广场C座1-2F', 120.128958, 30.337252, 60, 17771, 685, 47, '10:00-22:00', '2021-12-22 20:48:54', '2021-12-22 20:48:54');

2.创建双表视图简单的左外连接

create view my_shop_and_type_view as
select tb_shop.id,tb_shop.type_id,tb_shop.name as shopName,tb_shop_type.name as shopType
from tb_shop left join tb_shop_type
on tb_shop.type_id = tb_shop_type.id;

3.查询视图

select *
from my_shop_and_type_view;

这里就体现出视图的作用了。之前的双表查询现在简化成单表的查询了。
4.更改视图

insert into my_shop_and_type_view value(11,1,'再回楼餐厅','餐厅');
-- [HY000][1471] The target table my_shop_and_type_view of the INSERT is not insertable-into
delete from my_shop_and_type_view where id=1;
-- [HY000][1288] The target table my_shop_and_type_view of the DELETE is not updatable