oracle的select查询语句(DQL):
语法:
select //查询动作关键字
[distinct|all] //描述列表字段中的数据是否去除记录
select_list //需要查询的字段列表,占位符,可以是多个
from table_name //from关键字,表数据来源,表或者视图
[where_clause] // WHERE 条件部分
[group_by_clause] // 分组条件
[HAVING condition] // 分组的聚合函数
[order_by_clause] // 排序条件
1.字段查询
1.1获取指定字段的数据
(1)普通查询特定字段
SELECT productid,productname,productprice from productinfo;
(2)根据当前模式获取表或视图的列
select scott.productinfo.productid,productinfo.productname,productinfo.productprice from scott.productinfo;
(3)获取所有字段的数据
SELECT * from productinfo;
使用*号的弊端:
(1)执行效率没有明确查询列高;
(2)增加不必要的网络消耗;
(3)在表中增加新的字段时,会造成一些不必要的程序异常。
1.2使用别名替代表中的字段名:
select productid 产品编号, productname AS 产品名称, productprice AS 产品价格 FROM PRODUCTINFO ;
1.3使用表达式操作查询的字段:
select productid,productname,productprice || '*' 1.25 || '=' || productprice*1.25 as new_productprice
from productinfo;
1.4使用函数操作查询的字段:
select productid 产品编号,subStr(productid,1,6) as 截取后的编号,productname as 产品名称,productprice as 产品价格
from productinfo;
1.5去除检索数据中的重复记录:
select distinct category 产品类型 from productinfo ;
2.数据处理
2.1检索出来的数据排序:
在select语句的后面,不管有什么条件限制,排序只能在最后一项。
语法:
order by
{expr | position | c_alias}
[ASC | DESC]
[NULLS FIRST | NULLS LAST]
[,{expr | position | c_alias}
[ASC | DESC]
[NULLS FIRST | NULLS last]
]...
2.2使用升序,降序来处理数据
select productname, quantity from productinfo order by quantity;
2.3排序时对null值得处理
升序时null值在首位:
select productname, quantity from productinfo order by quantity nulls first;
降序时null值在末尾:
select productname, quantity from productinfo order by quantity nulls last ;
2.4使用别名作为查询手段
select productname 产品名称,quantity 产品数量 from productionfo order by 产品数量 nulls last;
2.5 使用表达式作为排序字段
select productname,productprice,quantity,productprice*quantity from productinfo order by productprice*quantity ASC;
备注:null值和其他值相乘相加时,结果还是null;
2.6 使用字段的位置作为排序字段
select productname,productprice,quantity from productinfo order by 3 desc;
2.7使用多个字段排序:
select productname,category,quantity from productinfo order by category asc , 3 desc nulls last;
3.where 设置检索条件
关系操作符:<,<=,>,>=,=,!=,<>.
比较操作符:is null, like,between...end...,in
逻辑操作符:and, or, not
3.1单一条件检索:
where quantity>20
3.2多个条件:
where productprice>=1000 and productprice<=7000;
where productprice between 1000 end 7000;
3.3模糊查询:
%:代替多个字符
_:代替一个字符
例:where productname like '%三星%'
3.4查询条件限制在某个列表范围之内
where category in('0100030002','0100010001')
3.5针对null值得查询
where quantity is null ;
where quantity is not null;
4分组查询:group by和having
4.1group by:
语法:GROUP BY
{ expr /**数据库列名**/
| {rollup | cube}({expr [,expr ]...}) /**group by子句的扩展,可以返回小计和总计记录**/
}
一个字段分组:
select category, avg (productprice) 平均价格 from productionfo group by category;
多个字段分组:
select category 产品类型编码,avg(productprice) 平均价格,origin 产地 from productinfo group by category,origin;
where 子句和分组混用(先筛选在分组):
select category,avg(productprice) 平均价格,origin from productinfo
where productprice > 1000 group by category,origin;
注意:当查询中出现group by子句时,select列表中只能存在分组函数,或出现在group by子句中的字段。
4.2HAVING
having子句通常和group by子句一起使用,限制搜索条件,对group by子句负责。
select category,avg(productprice) 平均价格 from productinfo group by category HAVING avg(productprice)>2000;
5子查询
嵌套在另外一个语句中的select语句,本质上是where后的一个条件表达式。
5.1 子查询返回单行
单一条件子查询:
select productname,productprice
from productinfo
where category=(select category from categoryinfo where category = 'MP3');
多个条件查询:
select productname,productprice from productinfo
where productprice >(select min(productprice) from productinfo)
and productprice<(select max(rpductprice) from productinfo)
5.2子查询返回多行
in:符合任一个都可以
例:select productname,productprice from productinfo
where category in
(select categoryid from categoryinfo where categoryname = '电视' or categoryname = 'MP3');
*any:表示满足子查询结果的任何一个,和<,<=搭配,表示小于等于列表中的最大值,和>,>=配合表示大于等于列表中的最小值。
例:select productname,productprice from productinfo where productprice <
any (select productprice from productinfo where category = '0100030002')
and category <> '0100030002'
some:和any差不多,用在非'='的环境中,表示找出和子查询中任何相当的数据:
例:select productname,productprice from productinfo where productprice =
some (select productprice from productinfo where category = '0100030002') and category <> '0100030002';
*all:表示满足子查询结果的所有结果。和<,<=搭配,表示小于等于列表中的最小值;而和>,>=搭配时表示大于等于列表中的最大值。
例:select productname,productprice from productinfo where productprice <
all(select productprice from productprice from productinfo where category = '0100030002');
6.连接查询:(内连接,外连接,全连接,自连接)
6.1最简单的连接查询,用‘,’ (笛卡尔积,不推荐)
select * from productinfo,categoryinfo;
6.2内连接:(两张表或多张表简单连接,只查询出匹配的记录)
等值连接:‘=’连接
例:select p.productname,p.productproce,c.categoryname
from productinfo p, categoryinfo c
where p.category = c.categoryid;
或:select p.productname,p.productprice,c.categoryname
from productinfo p inner join categoryinfo c
on p.category = c.categoryid;
不等值连接:'>','>=','<=','<','!=','<>','between...and...','in'
例:select p.productname,p.productprice,c.categoryname
from productinfo p inner join categoryinfo c on p.category in c.categoryid;
或:select p.productname,p.productprice,c.categoryname
from productinfo p, categoryinfo c where p.category in c.categoryid;
6.3自连接:自身引用作为另一张表来处理
例:select p.productname,p.productprice,pr.productname,pr.productprice,pr.quantity
from productinfo p, productingo pr
where p.productid != pr.productid
and p.quantity = pr.quantity
and p.rowid < pr.rowid;
或:select p.productname,p.productprice,pr.productname,pr.procudtprice,pr.quantity
from productinfo p join procudtinfo pr
on p.productid != pr.productid
and p.quantity = pr.quantity
and p.rowid < pr.rowid;
6.4外连接:((+)的使用:该操作符总是放在非主表的一方,并且使用where子句,左右连接可用)
左外连接:包含左表全部记录和右表匹配记录
例:select p.productname,p.productprice,p.category,c.categoryid,c.categoryname
from productinfo p left join categoryinfo c on p.category = c.categoryid;
或:select p.productname,p.productprice,p.category,c.categoryid,c.categoryname
from productinfo p , categoryinfo c
where p.category = c.categoryid(+);
右外连接:包含右表全部记录和左表匹配记录
例:select p.productname,p.productprice,p.category,c.categoryid,c.categoryname
from productinfo p right join categoryinfo c on p.category = c.categoryid;
或:select p.productname,p.productprice,p.category,c.categoryid,c.categoryname
from productinfo p,categoryinfo c
where p.category(+) = c.categoryid;
全外连接:所有匹配成功的记录,和左右表中未匹配成功的记录
例:select p.productname,p.procudtprice,p.category,c.categoryid,c.categoryname
from productinfo p full join categoryinfo c on p.category = c.categoryid;