MySQL学习笔记六

最新推荐文章于 2024-09-16 11:23:08 发布

原创最新推荐文章于 2024-09-16 11:23:08 发布 · 3.1k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

SQL 专栏收录该内容

7 篇文章

订阅专栏

本文是关于MySQL的学习笔记，重点介绍了CASE WHEN语句用于根据成绩划分等级并计数，AS用于数据持久化创建新表，以及触发器的使用，包括在插入、删除和更新订单时自动调整商品库存的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、case when

case when的用为：从原有数据新增出新的数据

利用旧数据，形成新的数据进行输出（如：形成新的分类进行输出）

需求：根据成绩分等级，成绩<60的为C等，成绩在[60，80]为B等，成绩>80为A等

这里的等级用case来设置输出,case when ... then... when...then...else...end

select sid,(case 
            when score<60 then 'C'
            when score>=60 and score<=80 then 'B'
            else 'A'
            end) as level
from sc;

上面是用case when来根据成绩输出等级，那么如何一步到位对等级进行计数呢，可以利用sum/count + case when，每一个等级利用一次case when及聚合函数，三个等级利用三次

需求：根据成绩划分等级并根据等级计数

记得每个case要有一个end做结尾

select sum(case when score<60 then 1 else 0 end) as C,
       sum(case when score>=60 and score<=80 then 1 else 0 end) as B
       count(case when score>80 then 1 else null end) as A
from sc;

二、保存结果表-as/like

数据的持久化：as是直接复制，like是复制表结构

1.as-直接复制

create table tablename as ... 这里...是一个表结果输出，不能是一个表

drop table if exists `re1`;
create table re1 as
    select sum(case when score<60 then 1 else 0 end) as C,
       sum(case when score>=60 and score<=80 then 1 else 0 end) as B
       count(case when score>80 then 1 else null end) as A
    from sc;

drop table if exists `sc2`;
create table sc2 as 
    select * from sc;

2.like-复制表结构

create table tablename like tablename 这里tablename是一个实体表，不能是一个结果

drop table if exists commodity;
create table commoditytype like ishop1.commoditytype;

三、触发器

监控表，当被监控的表的数据发生变化时自动执行某SQL语句，此时用到了触发器。

触发器的应用场景：①每当增加一个顾客到某个数据表时，需要检查他的电话号码格式是否正确；②每当增加一个订单时，从库存中减去相应的数量；③无论何时删除一行，都在某个数据表中保留一个副本。

即上述的例子都是需要在某个表发生更改时自动处理。

delete、insert、update语句支持触发器，自动执行一条SQL语句，其他语句不支持触发器。

创建触发器要素：唯一的触发器名称、触发器关联的表（被监控的表）、触发器响应的活动（insert、delete、update）、触发器何时执行（after、before）；

1.语法

创建：CREATE TRIGGER tri_name AFTER|BEFORE insert|delete|update ON table_name FOR EACH ROW SQL(begin sql; end$)

删除：DROP TRIGGER tri_name;

2.修改命令结束标识符

使用触发器需要修改命令结束标识符，并且创建过后要把命令结束标识符更改回来

delimiter $
delimiter ;

3.查看触发器

#所有触发器
show triggers \G;

#在triggers表中查看触发器信息
SELECT * FROM information_schema.triggers WHERE trigger_name='trigger_name' \G;
SELECT * FROM information_schema.triggers WHERE trigger_name='tri_2' \G;

4.示例

需求：在新增订单时，即order表新增数据(insert)，商品表相应的商品库存应减去相应数量商品

sql把insert里的数据抽象为new对象，把delete里的数据抽象为old对象

监控的表--order 新增订单--insert (into) 触发器修改的表--commodity 触发器的操作--update

delimiter $
create trigger tri_1 
after insert on `order`
for each row
begin
update commodity set c_num=c_num-new.o_num where c_id=new.o_cid;
end$
delimiter ;

select c_id,c_name,c_num from commodity where c_id=19;
select * from `order`;
insert into `order` (o_cuid,o_cid,o_num) values (1,19,4); select * from `order`;
select c_id,c_name,c_num from commodity where c_id=19;

select c_id,c_name,c_num from commodity where c_id=19;
+------+------------------+-------+
| c_id | c_name           | c_num |
+------+------------------+-------+
|   19 | hellokitty手机链 |    40 |
+------+------------------+-------+

 select * from `order`;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|    1 |      1 |    12 |     3 |
|    2 |      1 |    23 |     4 |
|    3 |      2 |     5 |     5 |
|    4 |      2 |    15 |     2 |
|    6 |      3 |    41 |     5 |
|    7 |      4 |    41 |    10 |
|    8 |      4 |    22 |    10 |
+------+--------+-------+-------+

delimiter $
create trigger tri_1 
after insert on `order`
for each row
begin
update commodity set c_num=c_num-new.o_num where c_id=new.o_cid;
end$

delimiter ;
insert into `order` (o_cuid,o_cid,o_num) values (1,19,4); select * from `order`;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|    1 |      1 |    12 |     3 |
|    2 |      1 |    23 |     4 |
|    3 |      2 |     5 |     5 |
|    4 |      2 |    15 |     2 |
|    6 |      3 |    41 |     5 |
|    7 |      4 |    41 |    10 |
|    8 |      4 |    22 |    10 |
|   13 |      1 |    19 |     4 |
+------+--------+-------+-------+
8 rows in set (0.00 sec)

select c_id,c_name,c_num from commodity where c_id=19;
+------+------------------+-------+
| c_id | c_name           | c_num |
+------+------------------+-------+
|   19 | hellokitty手机链 |    36 |
+------+------------------+-------+
1 row in set (0.00 sec)

需求：顾客删除订单

监控的表--order 删除订单--delete(from) 触发器修改的表--commodity 触发器的操作--update

delimiter $
create trigger tri_2
after delete on `order`
for each row
begin
update commodity set c_num=c_num+old.o_num where c_id=old.o_cid;
end $
delimiter ;

select * from `order`;
select c_id,c_name,c_num from commodity where c_id=19;
delete from `order` where o_id=13;
select * from `order`;
select c_id,c_name,c_num from commodity where c_id=19;

mysql> delimiter $
mysql> create trigger tri_2
    -> after delete on `order`
    -> for each row
    -> begin
    -> update commodity set c_num=c_num+old.o_num where c_id=old.o_cid;
    -> end $
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> select * from `order`;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|    1 |      1 |    12 |     3 |
|    2 |      1 |    23 |     4 |
|    3 |      2 |     5 |     5 |
|    4 |      2 |    15 |     2 |
|    6 |      3 |    41 |     5 |
|    7 |      4 |    41 |    10 |
|    8 |      4 |    22 |    10 |
|   13 |      1 |    19 |     4 |
+------+--------+-------+-------+
8 rows in set (0.01 sec)

mysql> select c_id,c_name,c_num from commodity where c_id=19;
+------+------------------+-------+
| c_id | c_name           | c_num |
+------+------------------+-------+
|   19 | hellokitty手机链 |    36 |
+------+------------------+-------+
1 row in set (0.00 sec)

mysql> delete from `order` where o_id=13;
Query OK, 1 row affected (0.01 sec)

mysql> select c_id,c_name,c_num from commodity where c_id=19;
+------+------------------+-------+
| c_id | c_name           | c_num |
+------+------------------+-------+
|   19 | hellokitty手机链 |    40 |
+------+------------------+-------+
1 row in set (0.00 sec)

需求：用户修改订单数据同时也修改相应的商品库存

order表的更新可以想象成，先删旧的 old，再加新的 new

则对于commodity，等于把 old的加上恢复成原始数据，再把更新后的库存new减掉

delimiter $
create trigger tri_3
after update on `order`
for each row
begin
update commodity set c_num=c_num+old.o_num-new.o_num where c_id=new.o_cid;
end $
delimiter ;

select c_id,c_name,c_num from commodity where c_id=19;
select * from `order`;
insert into `order` (o_cuid,o_cid,o_num) values (1,19,10);
select * from `order` where o_id=14;
select c_id,c_name,c_num from commodity where c_id=19;

update `order` set o_num=5 where o_id=14;
select * from `order` where o_id=14;
select c_id,c_name,c_num from commodity where c_id=19;

delimiter $
create trigger tri_3
after update on `order`
for each row
begin
update commodity set c_num=c_num+old.o_num-new.o_num where c_id=new.o_cid;
end $
delimiter ;

select c_id,c_name,c_num from commodity where c_id=19;
+------+------------------+-------+
| c_id | c_name           | c_num |
+------+------------------+-------+
|   19 | hellokitty手机链 |    40 |
+------+------------------+-------+
1 row in set (0.00 sec)

select * from `order`;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|    1 |      1 |    12 |     3 |
|    2 |      1 |    23 |     4 |
|    3 |      2 |     5 |     5 |
|    4 |      2 |    15 |     2 |
|    6 |      3 |    41 |     5 |
|    7 |      4 |    41 |    10 |
|    8 |      4 |    22 |    10 |
+------+--------+-------+-------+
7 rows in set (0.01 sec)

insert into `order` (o_cuid,o_cid,o_num) values (1,19,10);
select * from `order` ;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|    1 |      1 |    12 |     3 |
|    2 |      1 |    23 |     4 |
|    3 |      2 |     5 |     5 |
|    4 |      2 |    15 |     2 |
|    6 |      3 |    41 |     5 |
|    7 |      4 |    41 |    10 |
|    8 |      4 |    22 |    10 |
|   14 |      1 |    19 |    10 |
+------+--------+-------+-------+
8 rows in set (0.00 sec)

select * from `order` where o_id=14;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|   14 |      1 |    19 |    10 |
+------+--------+-------+-------+
1 row in set (0.00 sec)

select c_id,c_name,c_num from commodity where c_id=19;
+------+------------------+-------+
| c_id | c_name           | c_num |
+------+------------------+-------+
|   19 | hellokitty手机链 |    30 |
+------+------------------+-------+
1 row in set (0.00 sec)

update `order` set o_num=5 where o_id=14;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select * from `order` where o_id=14;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|   14 |      1 |    19 |     5 |
+------+--------+-------+-------+
1 row in set (0.00 sec)

select c_id,c_name,c_num from commodity where c_id=19;
+------+------------------+-------+
| c_id | c_name           | c_num |
+------+------------------+-------+
|   19 | hellokitty手机链 |    35 |
+------+------------------+-------+
1 row in set (0.00 sec)