Mysql_Note4

1.临时表

1.1定义

临时表:分别是内部临时表和外部临时表

create temporary table 表名
(
字段名 字段类型,
...
);

1
2
3
4
5
6

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;
1
2
3
4
5
6
7
8

-- 计算进货数据
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;
1
2
3
4
5
6
7

-- 计算返厂信息
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;
1
2
3
4
5

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

1.3内存表和磁盘临时表

内存临时表

create temporary table demo.mytrans
(
itemnumber int,
groupnumber int,
branchnumber int    
) engine = memory;
1
2
3
4
5
6

磁盘临时表

create temporary table demo.mytransdisk
(
itemnumber int,
groupnumber int,
branchnumber int    
)
1
2
3
4
5
6

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.视图

2.1定义

以把一段查询语句作为视图存储在数据库中,在需要的时候,可以把视图看做一个表,对里面的数据进行查询

2.2 视图的基本操作

增加

CREATE [OR REPLACE]
VIEW 视图名称 [(字段列表)]
AS 查询语句
1
2
3

删除

删除视图：
DROP VIEW 视图名;
1
2

修改

alter view 视图名
as 查询语句
1
2

查看

查看视图：
DESCRIBE 视图名；
1
2

注意

子查询:就是嵌套在另一个查询中的查询

派生表:如果我们在查询中把子查询的结果作为一个表来使用,这个表就是派生表

示例

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)
1
2
3
4
5
6
7
8
9
10
11
12

2.3视图数据的基本操作

1.创建视图

create view demo.view_goodsmaster as 
SELECT itemnumber,barcode,goodsname,specification,salesprice FROM demo.goodsmaster;
1
2

视图只能包含实际数据表中有的字段

2.插入数据

ALTER VIEW demo.view_goodsmaster
AS
SELECT itemnumber,barcode,goodsname,salesprice -- 只包含实际表中存在的字段
FROM demo.goodsmaster
WHERE salesprice > 50;
1
2
3
4
5

3.删除视图中的数据

DELETE FROM demo.view_goodsmaster -- 直接在视图中删除数据
WHERE itemnumber = 5;
1
2

4.修改视图中的数据

不建议你对视图的数据进行更新操作, 因为 MySQL 允许用比较复杂的 SQL 查询语句来创建视图(比如 SQL 查询语句中使用了分组和聚合函数,或者是 UION 和 DISTINCT 关键字), 因为 MySQL 没办法精确定位实际数据表中的记录

3.存储过程

3.1定义

应用场景:

数据量大,而且计算过程复杂的场景,就非常适合使用存储过程

优点:

客户端不需要把所有的 SQL 语句通过网络发给服务器,减少了 SQL 语句暴露在网上的风险,也提高了数据查询的安全性

语法

create procedure 存储过程名 ( [ in | out | inout ] 参数名称 类型 ) 程序体
1

示例

创建存储过程

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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

调用存储过程

CALL demo.dailyoperation('2020-12-01');
1

删除存储过程

drop procedure 存储过程
1

3.2 存储过程的参数

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 ;
1
2
3
4
5
6
7