数据库高级（校招冲刺必备）

数据准备

# 创建db3_2数据库，并指定编码
CREATE DATABASE my_db CHARACTER SET utf8;
# 创建分类表(一方：主表)
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY,
  cname VARCHAR(50)
);

# 创建商品表(多方：从表)
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2), -- 是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
  FOREIGN KEY (category_id) REFERENCES category (cid) -- 添加外键约束
);
# 添加分类数据
INSERT INTO category (cid,cname) VALUES ('c001','家电');
INSERT INTO category (cid,cname) VALUES ('c002','鞋服');
INSERT INTO category (cid,cname) VALUES ('c003','化妆品');
INSERT INTO category (cid,cname) VALUES ('c004','汽车');

# 添加商品数据	
INSERT INTO	products (pid,pname,price,flag,category_id) VALUES ('p001','小米电视机',5000,'1','c001');
INSERT INTO	products (pid,pname,price,flag,category_id) VALUES ('p002','格力空调',3000,'1','c001');
INSERT INTO	products (pid,pname,price,flag,category_id) VALUES ('p003','美的冰箱',4500,'1','c001');
INSERT INTO	products (pid,pname,price,flag,category_id) VALUES ('p004','篮球鞋',800,'1','c002');
INSERT INTO	products (pid,pname,price,flag,category_id) VALUES ('p005','运动裤',200,'1','c002');
INSERT INTO	products (pid,pname,price,flag,category_id) VALUES ('p006','T恤',300,'1','c002');
INSERT INTO	products (pid,pname,price,flag,category_id) VALUES ('p007','冲锋衣',2000,'1','c002');
INSERT INTO	products (pid,pname,price,flag,category_id) VALUES ('p008','神仙水',800,'1','c003');
INSERT INTO	products (pid,pname,price,flag,category_id) VALUES ('p009','大宝',200,'1','c003');

视图

1.视图是一张虚表，数据来源于原始的表，建立在table的基础之上的

2.首先需要创建一张表

3.关键，就是一个select查询语句（过滤掉安全隐患的数据），把他查到的数据作为视图。数据进行映射。

4.数据来源还是对应的table表，称为视图的基表。

视图的使用

视图创建

create view 视图名称 {字段名称列表} as select语句(id,user,age);
create view 视图名称 (e_id,e_anme,e_age) as select语句(id,user,age);

create view:表示创建的是视图

as：视图要执行的操作（视图映射的表中的数据来自于哪里）

select语句：即向视图提供的数据内容

字段名称列表可省略：默认原来的表中的名称。

视图案例

所有的商品信息及其分类作为视图进行映射（用到外连接）

创建视图

首先要明确select语句

1.查询所有商品的分类详情

SELECT * FROM
products p LEFT JOIN category c ON p.`category_id`=c.`cid`;

2.表示为视图

#基于select查询语句将查询到的结果作为视图进行映射
CREATE VIEW products_category_view AS SELECT * FROM
products p LEFT JOIN category c ON p.`category_id`=c.`cid`;

查询视图

直接将视图作为一张表的名称进行操作。注意：视图只有查询操作

需求：查询各个分类下商品的平均价格

两种方式

1.基于表进行查询

SELECT cname AS '商品分类名称',
AVG(p.price) AS '商品平均价格'
FROM
products p LEFT JOIN category c ON p.category_id=c.cid
GROUP BY c.`cname`;

2.基于视图进行查询操作

SELECT cname AS '商品分类名称',
AVG(price) AS '商品平均价格'
FROM products_category_view
GROUP BY cname;

视图优点：大大优化了代码量

需求：查询鞋服分类下最贵商品的全部信息

1.基于表进行查询

#鞋服分类下最贵的商品
SELECT MAX(p.`price`) AS '最贵商品' FROM products p LEFT JOIN category c ON p.`category_id`=c.`cid`
WHERE c.`cname`='鞋服';
#将此值对印的行所有的信息获取到
SELECT * FROM products p LEFT JOIN category c ON p.`category_id`=c.`cid`
WHERE c.`cname`='鞋服' AND p.`price`=(SELECT MAX(p.`price`) AS '最贵商品' FROM products p LEFT JOIN category c ON p.`category_id`=c.`cid`
WHERE c.`cname`='鞋服');

在多表情况下，会频繁使用左右外连接，语句会特别复杂，这只是两个表，三表会更加复杂，所以我们选择：视图！

2.基于视图进行查询操作

SELECT * FROM products_category_view AS pcv WHERE pcv.cname='鞋服'AND 
pcv.`price`=(SELECT MAX(price) FROM products_category_view WHERE cname='鞋服');

视图与表的区别

1.视图是建立在表的基础之上，表中存储的是数据，而视图只是做一个数据的展示

2.视图不可以做数据的更新操作例如update，delete...如果视图可以修改数据，会把表污染掉，我们可以直接删除视图，不会有任何的影响；表如果直接删除，数据会被永久删除，同时影响基于此表创建的视图

视图的作用

1.多表操作查询极为复杂

2.在权限控制时可以使用