昨天数据库上机课老师让我们把书上的例子实战了一遍,我稍微整理了下方便今后复习。
这是三张关系表:
一、单表查询
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);