Oracle怎么用dql查,Oracle学习(四) --- DQL语法

标准DQL语法

select distinct * | 列名 as 别名, 列表2 as 别名2... | 聚合函数

from 表名 as 别名, 表名2 as 别名2 ,....

where 查询条件

group by 分组字段 having 分组条件

order by 排序字段 asc | desc,....

1、查询 -- 单表查询

1.1、简单查询练习

-- 1 查询水表编号为30408的业主记录

select * from t_owners where watermeter = "30408";

-- 使用表的别名

select * from t_owners ow where ow.watermeter = "30408";

-- 2 查询业主名称包含“刘”的业主记录

--- like 语句 %匹配多个字符 _匹配1个字段

select * from t_owners where name like "%刘%";

-- 3 查询业主名称包含“刘”的并且门牌号包含5的业主记录

select * from t_owners where name like "%刘%" and housenumber like "%5%";

-- 4 查询业主名称包含“刘”的或者门牌号包含5的业主记录

select * from t_owners where name like "%刘%" or housenumber like "%5%";

-- 5 查询业主名称包含“刘”的或者门牌号包含5的业主记录，并且地址编号为3的记录。

--- and 优先级 高于 or，如果先执行or，需要使用小括号

select * from t_owners where (name like "%刘%" or housenumber like "%5%") and addressid = 3;

-- 6 查询台账记录中用水字数大于等于10000，并且小于等于20000的记录

--- 关系运算符： > >= < <= == <>

select * from t_account where usenum >= 10000 and usenum <= 20000;

--- 字段 between ... and ...

select * from t_account where usenum between 10000 and 20000;

-- 7 查询T_PRICETABLE表中MAXNUM为空的记录

select * from t_pricetable where maxnum is null;

-- 8 查询T_PRICETABLE表中MAXNUM不为空的记录

select * from t_pricetable where maxnum is not null;

1.2、去重复和排序

-- 去重复

-- 需求：查询业主表中的地址ID,不重复显示

select distinct addressid from t_owners;

select distinct(addressid) from t_owners;

-- 排序

-- select ... order by 字段 asc|desc ,字段2 asc|desc,....

--需求：对T_ACCOUNT表按使用量进行升序排序

select * from t_account order by usenum asc

--需求：对T_ACCOUNT表按使用量进行降序排序

select * from t_account order by usenum desc

--需求：对T_ACCOUNT表 按照month降序，如果相同按照usenum进行升序

select * from t_account order by month desc , usenum asc ;

1.3、伪列

伪劣是oracle中独有的，伪劣也是真实存在的列，用于查询操作，不能增删改操作。

常见伪列

描述

rowid

物理文件上唯一区别记录的唯一标识
用途：用于区分重复数据

rownum

在查询的结果集中，ROWNUM为结果集中每一行标识一个行号
用途：在Oracle进行分页

rowid

在mysql表中存在数据相同记录，如果对某一条进行操作，讲修改所有的数据。

Oracle使用rowid区别每一条数据，不会存在操作一条，影响多条的情况。

rownum

-- 查询

select rownum,t_account.* from t_account;

1.4、聚合函数

ORACLE的聚合统计是通过分组函数来实现的，与MYSQL一致。

聚合函数：通过提供函数，讲查询结果处理成一行一列数据。

特点：聚合函数不计算null值

聚合函数

描述

sum

求和

avg

平均

max

最大值

min

最小值

count

计数

-- 聚合函数

--1 统计2012年所有用户的用水量总和

select sum(usenum) from t_account where year = "2012";

--2 统计2012年所有用水量(字数)的平均值

insert into t_account values( seq_account.nextval,2,1,3,"2012","12",95076,99324,null,1,sysdate,44.51,"1",to_date("2014-01-14","yyyy-MM-dd"),2 );

select avg(usenum) from t_account where year = "2012";

--3 统计2012年最高用水量(字数)

select max(usenum) from t_account;

--4 统计2012年最低用水量(字数)

select min(usenum) from t_account;

--5 统计记录个数 count

select count(id) from t_account;

2、查询 -- 连接查询

笛卡尔积：两个表乘积，所有的数据最大集(开发无用)

select * from A , B;

内连接

隐式内连接

select * from A , B where a.id = b.id

显示内连接

select * from A inner join B a.id = b.aid

外连接

左外连接：查询左表(A)所有数据，如果条件成立显示右边(B)的数据，否则显示null

select * from A left outer join B on a.id = b.aid

右外连接：查询右表(B)所有数据，如果条件成立显示左边(A)的数据，否则显示null

select * from A right outer join B on a.id = b.aid

2.1、内连接查询练习

--连接查询

-- 1查询显示业主编号，业主名称，业主类型名称

--- 隐式内连接

select ow.id,ow.name,ot.name from t_owners ow , t_ownertype ot

where ow.ownertypeid = ot.id;

--- 显示内连接

select ow.id,ow.name,ot.name from t_owners ow

inner join t_ownertype ot on ow.ownertypeid = ot.id;

-- 2查询显示业主编号，业主名称、地址和业主类型

--- 隐式内连接

select ow.id,ow.name,ad.name,ot.name from t_owners ow , t_ownertype ot , t_address ad

where ow.ownertypeid = ot.id and ow.addressid = ad.id;

select ow.id,ow.name as 业主名称,ad.name 地址名称,ot.name 业主类型名称 from t_owners ow , t_ownertype ot , t_address ad

where ow.ownertypeid = ot.id and ow.addressid = ad.id;

--- 显示内连接

select ow.id,ow.name,ad.name,ot.name from t_owners ow

inner join t_address ad on ow.addressid = ad.id

inner join t_ownertype ot on ow.ownertypeid = ot.id;

-- 3查询显示业主编号、业主名称、地址、所属区域、业主分类

--- 隐式内连接

select ow.id,ow.name 业主名称,ad.name 地址,ar.name 所属区域,ot.name 业主分类 from t_owners ow , t_ownertype ot , t_address ad , t_area ar

where ow.ownertypeid = ot.id and ow.addressid = ad.id and ad.areaid = ar.id

--- 显示内连接

select ow.id,ow.name 业主名称,ad.name 地址,ar.name 所属区域,ot.name 业主分类 from t_owners ow

inner join t_ownertype ot on ow.ownertypeid = ot.id

inner join t_address ad on ow.addressid = ad.id

inner join t_area ar on ad.areaid = ar.id;

2.2、左外连接

-- 左外链接

--需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名。

select ow.id,ow.name,ac.year,ac.month,ac.money from t_owners ow

left outer join t_account ac on ow.id = ac.ownerid

2.3、右外连接

--需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果账务记录没有对应的业主信息也要列出记录。

--- 准备数据，修改将 t_account 表 ownerid 非空约束去掉

insert into t_account values( seq_account.nextval,null,1,3,"2012","12",95076,99324,0,1,sysdate,44.51,"1",to_date("2014-01-14","yyyy-MM-dd"),2 );

select ow.id,ow.name,ac.year,ac.month,ac.money from t_owners ow right outer join t_account ac on ow.id = ac.ownerid

2.4、Oracle左外连接特殊用法(了解)

在内连接基础上，使用(+) 转换 左外连接

-- Oracle 班级簿左外连接

select ow.id,ow.name,ac.year, ac.month ,ac.money from t_owners ow , t_account ac where ow.id = ac.ownerid (+);

用法：

左外连接：在连接条件处，右表条件字段添加(+)

3、子查询

3.1、概述

子查询：一个select语句，作为另一条select语句语法的一部分。

select语句语法：

select distinct * | 字段 from 表名

where 查询条件

group by 分组字段 having 分组条件

order by 排序字段 asc | desc

3.2、单行子查询

编写步骤，将一个需求拆分成多个子需求，依次完成每一个子需求，最后将组合子需求

-- 查询2012年1月用水量大于平均值的台账记录

-- 1 用水量平均值

select avg(usenum) from t_account where year="2012" and month = "01"

-- 2 查询2012年1月所有用水量

select * from t_account where year="2012" and month = "01"

-- 3 合并

select * from t_account where year="2012" and month = "01" and usenum > 20009.5

select * from t_account where year="2012" and month = "01" and usenum > (

select avg(usenum) from t_account where year="2012" and month = "01"

)

3.3、多行子查询

--查询2012年台账中，使用量大于2012年3月最大使用量的台账数据

-- 方式1：求最大值

-- 1. 求2012年3月最大使用量

select max(usenum) from t_account where year = "2012" and month = "03"

-- 2. 查询2012年台账 大于 13808

select * from t_account where year = "2012" and usenum > 13808

-- 3. 整合

select * from t_account where year = "2012" and usenum > (

select max(usenum) from t_account where year = "2012" and month = "03")

-- 方式2：使用all运算符

-- 1. 求2012年3月所有使用量

select usenum from t_account where year = "2012" and month = "03"

-- 2. 查询2012年台账 大于 3月所有使用量

select * from t_account where year = "2012" and usenum > all (13808,13390)

-- 3. 整合

select * from t_account where year = "2012" and usenum > all (select usenum from t_account where year = "2012" and month = "03")

3.4、嵌套子查询

嵌套子查询：在子查询中再次嵌入子查询

-- 查询在海淀区的小区名字中含有花园的业主记录

-- 1. 查询区域id，名称为“海淀” --多个海淀

select id from t_area where name = "海淀"

-- 2. 查询地址id，条件：名称含“花园” 和 区域id

select id from t_address where name like "%花园%" and areaid in (1)

-- 3 组合 1+2

select id from t_address where name like "%花园%" and areaid in (

select id from t_area where name = "海淀"

)

-- 4. 查询业主，条件：一组地址id

select * from t_owners where addressid in (1)

-- 5. 组合

select * from t_owners where addressid in (

select id from t_address where name like "%花园%" and areaid in (

select id from t_area where name = "海淀"

)

)

--- 只有一个海淀

select id from t_address where name like "%花园%" and areaid = (

select id from t_area where name = "海淀"

)

3.5、标量子查询

标量子查询：子查询的语句执行的结果直接作为主查询的结果显示

-- 查询台账表中的用户年用水量的总和 以及 年平均用水量

-- 1. 查询用水量总和 137868

select sum(usenum) from t_account

-- 2. 查询用水量平均值 5514.72

select avg(usenum) from t_account

-- 3. 将两个不相关的数据，使用虚表 dual 组合在一起

select (137868) as 总和,(5514.72) as 平均 from dual

select (

select sum(usenum) from t_account

) as 总和,(

select avg(usenum) from t_account

) as 平均 from dual

3.6、相关子查询

相关子查询：子查询依赖外面的主查询的结果

练习1：3表练习

-- 查询显示业主编号，业主名称、地址和业主类型

-- 1. 查询业主编号，名称

select id,name,addressid,ownertypeid from t_owners

-- 2. 根据地址id，查询地址

select name from t_address where id = 1

-- 3. 根据类型id，查询业主类型

select name from t_ownertype where id = 1

-- 4. 组合

select ow.id,ow.name,(

select name from t_address where id = ow.addressid

) 地址名称 ,ownertypeid from t_owners ow

select ow.id,ow.name,(

select name from t_address where id = ow.addressid

) 地址名称 ,(

select name from t_ownertype where id = ow.ownertypeid

) from t_owners ow

练习2：4表练习

-- 查询显示业主编号、业主名称、地址、所属区域、业主分类

-- 1. 查询业主编号，名称

select id,name,addressid,ownertypeid, ar.name from t_owners

-- 2. 根据地址id，查询地址

select name from t_address where id = 1

-- 3. 根据类型id，查询业主类型

select name from t_ownertype where id = 1

-- 4. 根据区域id，查询区域

select ar.name from t_area ar, t_address ad where ar.id = ad.areaid and ad.id = 1

-- 5 组合

select ow.id, ow.name,(

select name from t_address where id = ow.addressid

) ,(

select name from t_ownertype where id = ow.ownertypeid

) , (

select ar.name from t_area ar, t_address ad where ar.id = ad.areaid and ad.id = ow.addressid

) from t_owners ow