集合操作主要包括:
- 并操作(UNION)
- 交操作(INTERSECT)
- 差操作(EXCEPT)
(所有需要操作的表都在这里建立:https://blog.csdn.net/qq_44647223/article/details/110249062)
示例1:
查询1#仓库的物资及单价不大于50的物资
select *
from stock
where warehouse='1#仓库'
UNION
select *
from stock
where unit<=50;
示例2:
查询使用物资编号m001或m002的抢修工程的工程号
select prj_num
from out_stock
where mat_num='m001'
UNION
select prj_num
from out_stock
where mat_num='m002';
等价于
select distinct prj_num
from out_stock
where mat_num='m001' or mat_num='m002';
示例3:
查询存在1#仓库且单价不大于50的物资
select *
from stock
where warehouse='1#仓库'
INTERSECT /*MySQL数据库没有实现*/
select *
from stock
where unit<=50;
MySQL数据库关于交集实现方式
select *
from stock where warehouse='1#仓库' AND unit<=50;
注:
MySQL不支持INTERSECT(交)操作符。
MySQL也不支持except(差)操作符