mysql视图知识点(补充ALGORITHM = MERGE/TEMPTABLE/UNDEFINED)

mysql视图知识点(补充ALGORITHM = MERGE/TEMPTABLE/UNDEFINED)

直接po代码

#mysql中视图知识点补充

#查看表结构

DESC book;

#修改表结构，添加一列，添加到bName列的后面

ALTER TABLE book ADD author VARCHAR(50) AFTER bName;

#修改表结构，添加一列，并且放在最前面

ALTER TABLE book ADD publish VARCHAR(40) FIRST;

#修改列

ALTER TABLE book CHANGE publish publishers VARCHAR(60) FIRST;

#删除列

ALTER TABLE book DROP publishers;

#查看表结构

DESC book;

CREATE TABLE book3 LIKE book;

SELECT * FROM book3;

DESC book3;

INSERT INTO book3 SELECT * FROM book;

SELECT * FROM book3;

#视图VIEW

#视图是由查询结果形成的一张虚拟表

#视图的创建语法 CREATE VIEW 视图名 AS SELECT语句;

CREATE VIEW view1 AS SELECT * FROM book WHERE price > 80;

SHOW TABLES;

SELECT * FROM view1;

#修改视图

ALTER VIEW view1 AS SELECT * FROM book WHERE price > 90;

SELECT * FROM view1;

#

SELECT * FROM view1;

UPDATE book SET price = 166.57 WHERE bid = 3;

SELECT * FROM view1;

#1.对视图的增删改也会影响表

UPDATE view1 SET price = 188.23 WHERE bid = 3;

SELECT * FROM book;

#

CREATE VIEW view2 AS SELECT btypeid,avg(price) AS avg_price FROM book GROUP BY btypeid;

SELECT * FROM view2;

#2.但是视图并不是总能增删改

UPDATE view2 SET avg_price = 137.98 WHERE btypeid = 10;

#

CREATE VIEW view3 AS

SELECT bid, bName,price, bTypeId FROM book ORDER BY bTypeId ASC, price DESC;

SELECT bid, bName,price, bTypeId FROM book GROUP BY bTypeId;

#mysql数据库中允许这种select 后含有非 group by 中的列

SELECT * FROM view3 GROUP BY bTypeId;

SELECT * FROM view3;

##mysql数据库中允许这种select 后含有非 group by 中的列

SELECT bid, bName,price, bTypeId FROM book GROUP BY bTypeId ORDER BY bTypeId ASC, price DESC;

#比较TEMPTABLE和MERGE的区别

#使用ALGORITHM=TEMPTABLE

CREATE ALGORITHM=TEMPTABLE VIEW view4 AS

SELECT bid, bName,price, bTypeId FROM book ORDER BY bTypeId ASC, price DESC;

#

SELECT * FROM view4;

#查询结果为bid是3和6

SELECT * FROM view4 GROUP BY bTypeId;

#使用ALGORITHM=MERGE

CREATE ALGORITHM=MERGE VIEW view5 AS

SELECT bid, bName,price, bTypeId FROM book ORDER BY bTypeId ASC, price DESC;

#

SELECT * FROM view5;

#查询结果为bid是1和5

SELECT * FROM view5 GROUP BY bTypeId;

网址：mysql视图知识点(补充ALGORITHM = MERGE/TEMPTABLE/UNDEFINED) https://www.yuejiaxmz.com/news/view/75871

相关内容

Navicat for MySQL安装及使用教程
【Ruby报错已解决】NoMethodError: undefined method `each‘ for nil:NilClass
MySql错误：mysqld: Can't create directory '/usr/local/mysql/data/
知识图谱与智能家居的结合：为家庭生活提供智能支持
mysql （8）=====用户授权管理
MYSQL启动失败解决方法
Python爬虫山东济南景点数据可视化和景点推荐系统 开题报告
居家养老服务个人护理基本知识——补充版
java连接mysql错误及解决方案整合
园艺生活网站(源码+mysql+文档)

随便看看