1.临时表
1.1定义
临时表:分别是内部临时表和外部临时表
create temporary table 表名
(
字段名 字段类型,
...
);
1.2 临时表简化复杂查询
-- 查询出每个单品的销售数量和销售金额
create temporary table demo.mysales
select itemnumber, -- 用查询的结果直接生成临时表
sum(quantity) as quantity,
sum(salesvalue) as salesvalue
from demo.transactiondetails
group by itemnumber
order by itemnumber;
-- 计算进货数据
create temporary table demo.myimport
select b.itemnumber,sum(b.quantity) as quantity,
sum(b.importvalue) as impor
from demo.importhead a join demo.importdetails b
on (a.listnumber=b.listnumber)
group by b.itemnumber;
-- 计算返厂信息
create temporary table demo.myreturn
select b.itemnumber,sum(b.quantity) as AS quantity,SUM(b.returnvalue) AS returnvalue
FROM demo.returnhead a JOIN demo.returndetails b
ON (a.listnumber=b.listnumber) GROUP BY b.itemnumber;
SELECT
a.itemnumber,
a.goodsname,
ifnull(b.quantity,0) as salesquantity, -- 如果没有销售记录,销售数量设置为0
ifnull(c.quantity,0) as importquantity, -- 如果没有进货,进货数量设为0
ifnull(d.quantity,0) as returnquantity -- 如果没有返厂,返厂数量设为0
FROM
demo.goodsmaster a -- 商品信息表放在左边进行左连接,确保所有的商品都包含在结果集中
LEFT JOIN demo.mysales b
ON (a.itemnumber=b.itemnumber)
LEFT JOIN demo.myimport c
ON (a.itemnumber=c.itemnumber)
LEFT JOIN demo.myreturn d
ON (a.itemnumber=d.itemnumber)
HAVING salesquantity>0 OR importquantity>0 OR returnquantity>0; -- 在结果集中剔除没有销售,没有进货,也没有返厂的商品
+------------+-----------+---------------+----------------+----------------+
| itemnumber | goodsname | salesquantity | importquantity | returnquantity |
+------------+-----------+---------------+----------------+----------------+
| 1 | 书 | 5.000 | 5.000 | 2.000 |
| 2 | 笔 | 5.000 | 5.000 | 1.000 |
| 3 | 橡皮 | 0.000 | 8.000 | 1.000 |
+------------+-----------+---------------+----------------+----------------+
3 rows in set (0.00 sec)
1.3内存表和磁盘临时表
内存临时表
create temporary table demo.mytrans
(
itemnumber int,
groupnumber int,
branchnumber int
) engine = memory;
磁盘临时表
create temporary table demo.mytransdisk
(
itemnumber int,
groupnumber int,
branchnumber int
)
类别 | 优点 | 缺点 |
---|---|---|
内存临时表 | 查询速度快 | 断电,数据容易丢失 |
磁盘表 | 数据不易丢失 | 速度相对比较慢 |
1.4思考题
假设有多个门店,每个门店有多台收款机,每台收款机销售多种商品,请问如何查询每个门店、每台收款机的销售金额占所属门店的销售金额的比率呢?
第一步,先计算门店销售合计
CREATE TEMPORARY TABLE demo.temp
SELECT branchnumber,sum(actualvalue) AS actualvalue
FROM demo.trans
GROUP BY branchnumber;
第二步,再按照门店、收款机,计算合计
CREATE TEMPORARY TABLE demo.temp1
SELECT branchnumber,cashiernumber,sum(actualvalue) AS actualvalue
FROM demo.trans
GROUP BY branchnumber,cashiernumber;
第三步,计算按门店、收款机的销售占比
SELECT a.branchnumber,a.cashiernumber, a.actualvalue/b.actualvalue
FROM demo.temp1 AS a,demo.temp AS b
WHERE (a.branchnumber=b.branchnumber)
ORDER BY a.branchnumber,a.cashiernumber
2.视图
2.1定义
以把一段查询语句作为视图存储在数据库中,在需要的时候,可以把视图看做一个表,对里面的数据进行查询
2.2 视图的基本操作
增加
CREATE [OR REPLACE]
VIEW 视图名称 [(字段列表)]
AS 查询语句
删除
删除视图:
DROP VIEW 视图名;
修改
alter view 视图名
as 查询语句
查看
查看视图:
DESCRIBE 视图名;
注意
子查询:就是嵌套在另一个查询中的查询
派生表:如果我们在查询中把子查询的结果作为一个表来使用,这个表就是派生表
示例
create view demo.trans_goodsmaster as
-> SELECT
a.transdate,
a.itemnumber,
b.goodsname, -- 从商品信息表中获取名称
SUM(a.quantity) AS quantity, -- 统计销售数量
SUM(a.salesvalue) AS salesvalue -- 统计销售金额
FROM
demo.trans AS a
LEFT JOIN
demo.goodsmaster AS b ON (a.itemnumber = b.itemnumber) -- 与商品信息表关联
GROUP BY a.transdate , a.itemnumber; -- 按照销售日期和商品编号分组Query OK, 0 rows affected (0.01 sec)
2.3视图数据的基本操作
1.创建视图
create view demo.view_goodsmaster as
SELECT itemnumber,barcode,goodsname,specification,salesprice FROM demo.goodsmaster;
视图只能包含实际数据表中有的字段
2.插入数据
ALTER VIEW demo.view_goodsmaster
AS
SELECT itemnumber,barcode,goodsname,salesprice -- 只包含实际表中存在的字段
FROM demo.goodsmaster
WHERE salesprice > 50;
3.删除视图中的数据
DELETE FROM demo.view_goodsmaster -- 直接在视图中删除数据
WHERE itemnumber = 5;
4.修改视图中的数据
不建议你对视图的数据进行更新操作, 因为 MySQL 允许用比较复杂的 SQL 查询语句来创建视图(比如 SQL 查询语句中使用了分组和聚合函数,或者是 UION 和 DISTINCT 关键字), 因为 MySQL 没办法精确定位实际数据表中的记录
3.存储过程
3.1定义
应用场景:
数据量大,而且计算过程复杂的场景,就非常适合使用存储过程
优点:
客户端不需要把所有的 SQL 语句通过网络发给服务器,减少了 SQL 语句暴露在网上的风险,也提高了数据查询的安全性
语法
create procedure 存储过程名 ( [ in | out | inout ] 参数名称 类型 ) 程序体
示例
创建存储过程
delimiter //
create procedure demo.dailyoperation (transdate text)
begin -- 开始程序体
declare startdate,enddate datetime; --定义变量
set startdate = date_format(transdte ,'%Y-%m-%d'); -- 给起始时间赋值
set enddate =date_add(startdate,interval 1 day); -- 截止时间为1天以后
delete from demo.dailystatistcs wheresalesdate = startdate;
...
--查询语句
SELECT
LEFT (b.transdate, 10),
a.itemnumber,
SUM (a.quantity), -- 数量总计
SUM (a.salesvalue), -- 金额总计
SUM (a.quantity* c.avgimportprice), -- 计算成本
SUM (a.salesvalue-a.quantity* c.avgimportprice), -- 计算毛利
CASE sum (a.salesvalue) WHEN 0 THEN 0
ELSE round(sum (a.salesvalue-a.quantity* c.avgimportprice)/ sum (a.salesvalue), 4)
END -- 计算毛利率
FROM
demo.transactiondetails AS a
JOIN
demo.transactionhead AS b
ON (a.transactionid = b.transactionid)
JOIN
demo.goodsmaster c
ON (a.itemnumber=c.itemnumber)
WHERE
b.transdate>startdate AND b.transdate GROUP BY
LEFT(b.transdate,10),a.itemnumber
ORDER BY
LEFT(b.transdate,10),a.itemnumber;
END
// -- 语句结束,执行语句
end
//
delimiter ;
调用存储过程
CALL demo.dailyoperation('2020-12-01');
删除存储过程
drop procedure 存储过程
3.2 存储过程的参数
1 | 2 |
---|---|
in | 输入参数 |
out | 输出参数 |
inout | 即可作为输入参数也可以作为输出参数 |
3.3 思考题
1.请写一个简单的存储过程,要求是定义 2 个参数,一个输入参数 a,数据类型是 INT;另一个输出参数是 b,类型是 INT。程序体完成的操作是:b = a + 1
delimiter //
create procedure demo.test(in a int,out b int )
begin
set b=a+1;
end
//
delimiter ;