进阶课程Ⅰ复杂的查询

最新推荐文章于 2024-09-13 22:38:14 发布

feiluyi

最新推荐文章于 2024-09-13 22:38:14 发布

阅读量137

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/feiluyi/article/details/116056636

版权

今天学习资料的内容比较多，我准备拆成两节分两天学习

资料来自：http://datawhale.club/t/topic/475

1.1 什么是视图（what）

这章比较有意思的是提出‘视图'的概念

你以为你select的是一个表但其实你操作的是一个视图。怎么理解呢？

举个栗子

表一：姓名表二：年龄

我们select两表建立连接出来的姓名+年龄表实际上就是视图，而在数据库中的表实际上还是表一和表二

创建之后我们可以直接在这个视图（虚拟表）上做操作

2.1 为什么会有视图（why）

提高效率，可视清晰，不对外公开全部字段保密性强、降低数据的冗余

3.1 创建视图（how）

语法：CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

注意除mysql之外视图中不能实用ORDER BY

大家应该装好mysql了，首先先把product表建了语句直接复制：

CREATE TABLE product
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER ,
 purchase_price INTEGER ,
 regist_date DATE ,
 PRIMARY KEY (product_id));

插入数据：

INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;

首先感受一下我们的表长这样，不然自己YY真的很痛苦

我们来创建一个单表视图：各品类有多少行

创建之后视图就会出现再views这边

再select一下出来看看

wow！完美！所以大家最好是边试边学这个就和好记性不如烂笔头一个道理

跟着材料一起再创建一张表 shop_product

CREATE TABLE shop_product
(shop_id    CHAR(4)       NOT NULL,
 shop_name  VARCHAR(200)  NOT NULL,
 product_id CHAR(4)       NOT NULL,
 quantity   INTEGER       NOT NULL,
 PRIMARY KEY (shop_id, product_id));

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0001',	30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0002',	50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0003',	15);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0002',	30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0003',	120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0004',	20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0006',	10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0007',	40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0003',	20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0004',	50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0006',	90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0007',	70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D',	'福冈',		'0001',	100);

我们需要把product表和shop_product表进行连接需要的表列：品类、售价、售卖地

连接好之后再把创建码往前贴一下

反正记是不可能记住，用的时间长了才行，真用起来想不到就百读呗，所以我感觉还是需要一个整理的文档把语句都放在一起的，待会儿学完这章我就去学习材料里扒拉扒拉

也不知道为什么自己创建的视图怎么看怎么可可爱爱都给我来看看！！！

然后就可以在这个视图上创建查询了

3.2 修改视图

我们要对product_count 表下手了

把计数列变成售价

语法：ALTER VIEW 标名 AS select......

是不是有点简单一学就会

3.3 更新视图

更新视图之后原表里的数据也会被更新，因为视图依赖底表嘛

案例：把办公用品的售价改为5000元，UPDATE productsum SET sale_price = '5000' WHERE product_type = '办公用品';

原表、视图都能被更新，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表

3.4 删除视图

DROP VIEW productSum; 其实右键也可以删除

3.5 子查询

我真的很讨厌这种一层套一层俄罗斯套娃似的子查询，可是没有困难的工作只有勇敢的打工人。我可以的

SELECT product_type, cnt_product
FROM (SELECT *
        FROM (SELECT product_type, 
                     COUNT(*) AS cnt_product
                FROM product 
               GROUP BY product_type) AS productsum
       WHERE cnt_product = 4) AS productsum2;

来一段风中凌乱的子查询，看到这种先看最里面的括号里写的什么。还是拆出来结合实际比较容易理解，不要自己默默yy