SQL数据库之查询练习

昨天数据库上机课老师让我们把书上的例子实战了一遍，我稍微整理了下方便今后复习。
这是三张关系表：

一、单表查询
1、查询指定的列

（1）select mat_num,mat_name,speci
from stock;
（2）select mat_num,mat_name,speci,warehouse
from stock;

2、查询全部的列

select *
from stock;

3、查询经过计算的值

select prj_name,start_date,end_Date,DATEDIFF(day,start_date,end_date)
from salvaging;

4、增加一列常量值

select prj_name,'抢修天数',DATEDIFF(day,start_date,end_date)
from salvaging;

5、显示列标题

select prj_name 项目名称,start_date 开始日期,end_Date 结束日期,
DATEDIFF(day,start_date,end_date)抢修天数
from salvaging;

6、用这个语句查询，含有重复值

select warehouse
from stock;

等价于

select all warehouse
from stock;

7、指定distinct短语去除重复值

select distinct warehouse
from stock;

8、通过where子句查询满足条件的元组
（1）比较大小的查询

select mat_num,mat_name,speci,amount
from stock
where warehouse='供电局1#仓库';
 
select mat_num,amount,unit
from stock
where unit<80;

或使用not

select mat_num,amount,unit
from stock
where not unit>=80;

(2)确定范围的查询

between…and和not between…and
在某个范围 between…and

select mat_num,amount,unit
from stock
where unit between 50 and 100;

等价于

select mat_num,amount,unit
from stock
where unit >=50 and unit <=100;

不在某个范围 not between…and

select mat_num,amount,unit
from stock
where unit not between 50 and 100;

(3)确定集合的查询 使用逻辑运算符in
在某个集合内

select mat_num,amount,unit
from stock
where warehouse in ('供电局1#仓库','供电局2#仓库');

等价于

select mat_num,amount,unit
from stock
where warehouse='供电局1#仓库' or warehouse='供电局2#仓库';

不在某个集合内

select mat_num,amount,unit
from stock
where warehouse not in ('供电局1#仓库','供电局2#仓库');

等价于

select mat_num,amount,unit
from stock
where warehouse!='供电局1#仓库' and warehouse!='供电局2#仓库';

(4)字符匹配的查询 like用于查询指定列名与匹配串常量匹配的元组
如果like后的匹配字符串不含通配符，则可用 = 代替like谓词
用 != 代替not like谓词

select *
from stock
where warehouse like '供电局1#仓库';

等价于

select *
from stock
where warehouse = '供电局1#仓库';

%代表任意长度的字符串

select mat_num,mat_name,speci
from stock
where mat_name like '%绝缘电线';

查询物资名称中第三、四个字为“绝缘”的物资编号等
_代表任意单个字符

select mat_num,mat_name,speci
from stock
where mat_name like '__绝缘%';

查询所有不带“绝缘”两个字的物资编号等

select mat_num,mat_name,speci
from stock
where mat_name  not like '%绝缘%';

若有需要，可使用ESCAPE关键字对通配符进行转义

select *
from stock
where mat_name like '%户外\_真空%' escape '\';

(5)涉及空值的查询
不能用(= !=) 必须使用专门的判断NULL值的子句来完成
判断为空 IS NULL

select mat_num,mat_name
from stock
where unit is null;

(6)多重条件查询
使用AND和OR组成多条件查询

select mat_num,warehouse,amount
from stock
where mat_name='护套绝缘电线' and speci='BVV-120';

9、对查询结果进行排序 order by …
若为降序，使用DESC

select mat_num,unit
from stock
where mat_name='护套绝缘电线' order by unit desc;

若为升序，使用ASC 或者默认为升序，不添加关键字

select *
from stock
order by warehouse DESC ,amount;

（空值被认为是最小的）
10、TOP子句的用法
TOP n 子句，在查询结果中输出前面的n条记录

select top 2 *
from stock
order by amount desc;

TOP 你PERCENT 子句：显示占记录总数的n%条记录

select top 30 percent mat_num,mat_name,speci,amount
from stock
order by amount ;

11、聚集函数
MAX( ) MIN( ) AVG( ) COUNT( )
WHERE子句不能使用聚集函数作为条件表达式

select MAX(amount),MIN(amount),avg(amount)
from out_stock
where mat_num='m001' ;

12、对查询结果进行分组
GROUP BY 和聚集函数 COUNT( )配合使用

select prj_num 项目号,count(*)物资种类
from out_stock
group by prj_num;

HAVING 对分组后的结果进行过滤（作用于组），通常和GROUP BY配合使用

select prj_num 项目号
from out_stock
group by prj_num
having count(*)>=2;

GROUP BY 子句的分组字段也可以包含多个属性列名

select department,mat_num,count(distinct prj_num) 项目个数,sum(amount)领取数量
from out_stock
group by department,mat_num;

ROLLUP不会统计GROUP BY子句的最后一个分组字段的小计

select department,mat_num,count(distinct prj_num) 项目个数,sum(amount)领取数量
from out_stock
group by department,mat_num
with rollup;

CUBE关键字

select department,mat_num,count(distinct prj_num) 项目个数,sum(amount)领取数量
from out_stock
group by department,mat_num
with cube;

二、多表联合查询
连接查询
（1）等值和非等值连接查询

select salvaging. * ,out_stock.*
from salvaging,out_stock
where salvaging.prj_num=out_stock.prj_num;

使用自然连接，去除重复的属性列

select salvaging.prj_num,prj_name,start_date,end_Date,prj_status,mat_num,amount,get_date,department
from salvaging,out_stock
where salvaging.prj_num=out_stock.prj_num;

（2）外连接查询
左外连接：LEFT OUT JOIN

select salvaging.prj_num,prj_name,start_date,end_Date,prj_status,mat_num,amount,get_date,department
from salvaging LEFT OUTER JOIN out_stock ON(salvaging.prj_num=out_stock.prj_num);