Oracle查询

一、单表查询

1、简单条件查询

1）精确查询

SELECT
	* 
FROM
	T_OWNERS 
WHERE
	watermeter = '30408'

2）模糊查询

SELECT
	* 
FROM
	t_owners 
WHERE
	name LIKE '%刘%'

3）and运算符

SELECT
	* 
FROM
	t_owners 
WHERE
	name LIKE '%刘%' 
	AND housenumber LIKE '%5%'

4）or运算符

SELECT
	* 
FROM
	t_owners 
WHERE
	name LIKE '%刘%' 
	OR housenumber LIKE '%5%'

5）范围查询

-- 运算符
SELECT
	* 
FROM
	T_ACCOUNT 
WHERE
	usenum >= 10000 
	AND usenum <= 20000

-- between and
SELECT
	* 
FROM
	T_ACCOUNT 
WHERE
	usenum BETWEEN 10000 
	AND 20000

6）空值查询

-- is null
SELECT
	* 
FROM
	T_PRICETABLE t 
WHERE
	maxnum IS NULL

-- is not null
SELECT
	* 
FROM
	T_PRICETABLE t 
WHERE
	maxnum IS NOT NULL

2、去掉重复记录

SELECT DISTINCT
	* 
FROM
	T_OWNERS

3、排序查询

1）升序排序

SELECT
	* 
FROM
	T_ACCOUNT 
ORDER BY
	usenum

2）降序排序

SELECT
	* 
FROM
	T_ACCOUNT 
ORDER BY
	usenum DESC

4、基于伪列的查询

1）ROWID

ROWID 伪列是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的 标识表中的一行。

SELECT 
	ROWID,
	t.* 
FROM
	T_AREA t

可以通过指定 ROWID 来查询记录

SELECT 
	ROWID,
	t.* 
FROM
	T_AREA t 
WHERE
	ROWID = 'AAAM1uAAGAAAAD8AAC';

2）ROWNUM

ROWNUM 为结果集中每一行标识一个行号，第一行返回 1， 第二行返回 2…

SELECT 
	ROWNUM,
	t.* 
FROM
	T_OWNERTYPE t

5、聚合统计

1）聚合函数

1. 求和 sum

SELECT
	sum( usenum ) 
FROM
	T_ACCOUNT 
WHERE
	year = '2012'

2. 求平均 avg

SELECT
	avg( usenum ) 
FROM
	T_ACCOUNT 
WHERE
	year = '2012'

3. 求最大值 max

SELECT
	max( usenum ) 
FROM
	T_ACCOUNT 
WHERE
	year = '2012'

4. 求最小值 min

SELECT
	count( * ) 
FROM
	T_OWNERS t 
WHERE
	ownertypeid =1

5. 统计记录个数 count

select count(*) from T_OWNERS t where ownertypeid=1

2）分组聚合 group by

select 后一定是分组聚合的条件或者是聚合函数

SELECT
	areaid,
	sum( money ) 
FROM
	t_account 
GROUP BY
	areaid

3）分组后条件查询 having

SELECT
	areaid,
	sum( money ) 
FROM
	t_account 
GROUP BY
	areaid 
HAVING
	sum( money ) > 169000

二、连接查询

1、多表内连接查询

SELECT
	o.id 业主编号,
	o.name 业主名称,
	ot.name 业主类型 
FROM
	T_OWNERS o,
	T_OWNERTYPE ot 
WHERE
	o.ownertypeid = ot.id

SELECT
	o.id 业主编号,
	o.name 业主名称,
	ad.name 地址,
	ot.name 业主类型 
FROM
	T_OWNERS o,
	T_OWNERTYPE ot,
	T_ADDRESS ad 
WHERE
	o.ownertypeid = ot.id 
	AND o.addressid = ad.id

2、左外连接查询

-- SQL1999
SELECT
	ow.id,
	ow.name,
	ac.year,
	ac.month,
	ac.money 
FROM
	T_OWNERS ow
	LEFT JOIN T_ACCOUNT ac ON ow.id = ac.owneruuid

-- ORACLE
SELECT
	ow.id,
	ow.name,
	ac.year,
	ac.month,
	ac.money 
FROM
	T_OWNERS ow,
	T_ACCOUNT ac 
WHERE
	ow.id = ac.owneruuid ( + )

3、右外连接查询

-- SQL1999
SELECT
	ow.id,
	ow.name,
	ac.year,
	ac.month,
	ac.money 
FROM
	T_OWNERS ow
	RIGHT JOIN T_ACCOUNT ac ON ow.id = ac.owneruuid

-- ORACLE
SELECT
	ow.id,
	ow.name,
	ac.year,
	ac.month,
	ac.money 
FROM
	T_OWNERS ow,
	T_ACCOUNT ac 
WHERE
	ow.id ( + ) = ac.owneruuid

三、子查询

1、where子句中的子查询

1）单行子查询

• 子查询只返回一条记录
• 单行操作符（=、>、<…）

SELECT
	* 
FROM
	T_ACCOUNT 
WHERE
	year = '2012' 
	AND month = '01' 
	AND usenum > (
	SELECT
		avg( usenum ) 
	FROM
		T_ACCOUNT 
	WHERE
		year = '2012' 
	AND month = '01' 
	)

2）多行子查询

• 子查询返回了多条记录
• 多行操作符（in 、any、all）

in运算符

select * from T_OWNERS where addressid in ( 1,3,4 )

-- in
SELECT * 
FROM T_OWNERS 
WHERE addressid IN ( SELECT id FROM t_address WHERE name LIKE '%花园%' )

-- not in             
SELECT * 
FROM T_OWNERS 
WHERE addressid NOT IN ( SELECT id FROM t_address WHERE name LIKE '%花园%' )

2、from子句中的子查询

SELECT
	* 
FROM
	(
	SELECT
		o.id 业主编号,
		o.name 业主名称,
		ot.name 业主类型 
	FROM
		T_OWNERS o,
		T_OWNERTYPE ot 
	WHERE
		o.ownertypeid = ot.id 
	) 
WHERE
	业主类型= '居民'

3、select子句中的子查询

select 子句的子查询必须为单行子查询

SELECT
	id,
	name,
	( SELECT name FROM t_address WHERE id = addressid ) addressname 
FROM
	t_owners

四、简单分页查询

在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询

-- 前十条记录
SELECT 
	ROWNUM,
	t.* 
FROM
	T_ACCOUNT t 
WHERE
	ROWNUM <= 10

-- 11至20记录（错误写法）
-- 因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用"大于"符号
SELECT 
	ROWNUM ,
	t.* 
FROM
	T_ACCOUNT t 
WHERE
	ROWNUM > 10 
	AND ROWNUM <= 20

-- 11至20记录（正确写法）
-- 利用子查询
SELECT
	* 
FROM
	(
	SELECT 
		ROWNUM r,
		t.* 
	FROM
		T_ACCOUNT t 
	WHERE
		ROWNUM <= 20
    )
WHERE 
	r > 10

五、单行函数

1、字符函数

1）求字符串长度 LENGTH

dual 伪表：为了补全语法而存在

select length('ABCD') from dual

2）求字符串的子串 SUBSTR

select substr('ABCD',2,2) from dual;

3）字符串拼接 CONCAT

select concat('ABC','D') from dual
select 'ABC'||'D' from dual;

2、数值函数

1）四舍五入函数 ROUND

select round(100.567) from dual

select round(100.567,2) from dual

2）截取函数 TRUNC

select trunc(100.567) from dual

select trunc(100.567,2) from dual

3）取模 MOD

select mod(10,3) from dual

3、日期函数

0）当前日期与时间 sysdate

select sysdate from dual

1）加月函数 ADD_MONTHS

在当前日期基础上加指定的月

select add_months(sysdate,2) from dual

2）求所在月最后一天 LAST_DAY

select last_day(sysdate) from duall

3）日期截取 TRUNC

以2016/10/11为例

select TRUNC(sysdate) from dual

select TRUNC(sysdate,'yyyy') from dual

select TRUNC(sysdate,'mm') from dual

4、转换函数

1）数字转字符串 TO_CHAR

select TO_CHAR(1024) from dual

2）日期转字符串 TO_CHAR

select TO_CHAR(sysdate,'yyyy-mm-dd') from dual

select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

3）字符串转日期 TO_DATE

select TO_DATE('2017-01-01','yyyy-mm-dd') from dual

4）字符串转数字 TO_NUMBER

select to_number('100') from dual

5、其它函数

1）空值处理函数 NVL

NVL（检测的值，如果为 null 的值）

select NVL(NULL,0) from dual

2）空值处理函数 NVL2

NVL2（检测的值，如果不为 null 的值，如果为 null 的值）

SELECT
	PRICE,
	MINNUM,
	NVL2( MAXNUM, to_char( MAXNUM ), '不限' ) 
FROM
	T_PRICETABLE 
WHERE
	OWNERTYPEID =1

3）条件取值 decode

根据条件返回相应值

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,[缺省值])

-- decode 语句实现
SELECT
	name,
	decode(
		ownertypeid,
		1, ' 居 民 ',
		2, ' 行 政 事 业 单 位 ',
		3, '商业' 
	) 类型 
FROM
	T_OWNERS

-- 也可以用 case when then 语句来实现
SELECT
	name,
	(
    CASE ownertypeid 
        WHEN 1 THEN '居民' 
        WHEN 2 THEN '行政事业单位' 
        WHEN 3 THEN'商业' 
        ELSE '其它' 
    END 
    ) 
FROM
	T_OWNERS

-- case when then 语句的另外一种写法
SELECT
	name,
	(
	CASE	
        WHEN ownertypeid = 1 THEN '居民' 
        WHEN ownertypeid = 2 THEN '行政事业' 
        WHEN ownertypeid = 3 THEN '商业' 
	END 
	)
FROM
	T_OWNERS

六、分析函数

1、RANK

相同的值排名相同，排名跳跃

SELECT
	rank ( ) over ( ORDER BY usenum DESC ),
	usenum 
FROM
	T_ACCOUNT

2、DENSE_RANK

相同的值排名相同，排名连续

SELECT
	dense_rank ( ) over ( ORDER BY usenum DESC ),
	usenum 
FROM
	T_ACCOUNT

3、ROW_NUMBER

返回连续的排名，无论值是否相等

SELECT
	row_number ( ) over ( ORDER BY usenum DESC ),
	usenum 
FROM
	T_ACCOUNT

七、集合运算

1、并集运算

UNION ALL 不去掉重复记录

SELECT
	* 
FROM
	t_owners 
WHERE
	id <= 7 UNION ALL SELECT * FROM t_owners WHERE id >=5

UNION 去掉重复记录

SELECT
	* 
FROM
	t_owners 
WHERE
	id <= 7 UNION SELECT * FROM t_owners WHERE id >=5

2、交集运算

SELECT
	* 
FROM
	t_owners 
WHERE
	id <= 7 INTERSECT SELECT * FROM t_owners WHERE id >=5

3、差集运算

SELECT
	* 
FROM
	t_owners 
WHERE
	id <= 7 MINUS SELECT * FROM t_owners WHERE id >=5

可以用 minus 运算符来实现分页

SELECT ROWNUM
	,
	t.* 
FROM
	T_ACCOUNT t 
WHERE
	ROWNUM <= 20 
MINUS
SELECT ROWNUM
	,
	t.* 
FROM
	T_ACCOUNT t 
WHERE
	ROWNUM <= 10