[大数据]Oracle基础语法回顾

order by

作用：进行分组排序
用法：

ASC: 升序（默认
DESC：降序
NULLS FIRST：null值置于开始
NULLS LAST：null值至于结束

0.基本用法
SELECT
    column_1,
    column_2,
    column_3,
    ...
FROM
    table_name
ORDER BY
    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],

1. 按列排序行示例
SELECT name,address,credit_limit
FROM customers
ORDER BY name ASC;

2. 按多个列排序行示例
SELECT first_name, last_name
FROM contacts
ORDER BY first_name, last_name DESC; #按first_name升序，last_name降序排列

3. 按列位置排序行示例
SELECT name, credit_limit,address
FROM customers
ORDER BY 2 DESC, 1;#按第二列降序，第二列升序排序

4.按函数或表达式排序数据
SELECT customer_id, name
FROM customers
ORDER BY UPPER( name );#UPPER()函数区分大小写。按名字大小写顺序排列

注意

ORDER BY子句总是SELECT语句中的最后一个子句

distinct

作用：过滤相同值
用法：

0.基本用法
SELECT
    DISTINCT column_1,
    column_2,
        ...
FROM
    table_name;

1.distinct在一列上的应用
SELECT DISTINCT first_name
FROM contacts
ORDER BY first_name;

2.distinct在多列上的应用
SELECT 
DISTINCT a,
b
FROM demo 
ORDER BY a #按(a,b)组合来确定唯一性

注意：

DISTINCT子句只能在SELECT语句中使用
DISTINCT将NULL视为重复值
? DISTINCT不是SQL标准的UNIQUE的同义词。总是使用DISTINCT而不使用UNIQUE是一个好的习惯。

where

作用：过滤条件返回行记录
用法：

0.基础用法
SELECT
    column_1,
    column_2,
    ...
FROM
    table_name
WHERE
    search_condition
ORDER BY
    column_1,
    column_2;

1.利用简单运算符来查询
SELECT a, b
FROM demo
WHERE a = 1;

2.使用比较运算符来查询
SELECT *
FROM demo
WHERE c < ANY(a, b, c);

3.使用组合逻辑查询
SELECT *
FROM demo
WHERE a > b AND b > SOME(b, c)

4.使用between and 查询两个值之间的行记录
SELECT a
FROM demo
WHERE a BETWEEN 200 AND 300;

5.使用in 查询符号列表中的行记录
SELECT a
FROM demo
WHERE a IN (200, 300, 500)

6.使用like 模糊查询
SELECT a
FROM demo
WHERE a LIKE "mohu%"

注意：

运算符

and 运算符优先级最高，所以 or 和 and 结合使用时，若想 or 先执行，需要使用括号
any 和 some 完全等价

fetch

作用：

限制查询返回行数
用法：

0.基本用法
SELECT a
FROM demo
WHERE a in (3, 5, 6)
ORDER BY a DESC
FETCH NEXT 5 ROWS ONLY;#返回结果前5行

在oracle11g以下版本中等价于
SELECT a
FROM demo
WHERE a in (3, 5, 6) AND rownum <= 5
ORDER BY a DESC

*1.增强用法

[ OFFSET offset ROWS]
 FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]

>OFFSET子句
	OFFSET子句指定在行限制开始之前要跳过行数。OFFSET子句是可选的。 如果跳过它，则偏移量为0，行限制从第一行开始计算。
	偏移量必须是一个数字或一个表达式，其值为一个数字。偏移量遵守以下规则：
		偏移量为负值，则将其视为0。
		偏移量为NULL或大于查询返回的行数，则不返回任何行。
		偏移量包含一个分数，则分数部分被截断。
>FETCH子句
	FETCH子句指定要返回的行数或百分比。使用百分比要加关键字PERCENT。
	以下子句的行为和产生的结果相同：
		FETCH NEXT 1 ROWS
		FETCH FIRST 1 ROW
>ONLY | WITH TIES选项
	ONLY仅返回FETCH NEXT(或FIRST)后的行数或行数的百分比。
	WITH TIES返回与最后一行相同的排序键的多余行，导致最终结果不值指定行数。所以则必须在查询中指定一个ORDER BY子句。如果不这样做，查询将不会返回额外的行。

注意：

oracle12c以上版本才可以运行fetch子句

like

作用：
查询匹配模式的值
用法：

0.基础用法
SELECT a
FROM demo
WHERE b LIKE '_li';#匹配以li结尾的长度为3的字符,区分大小写

#％(百分号)匹配零个或多个字符的任何字符串。
#_(下划线)匹配任何单个字符。

1.结合upper()、lower()函数使用
SELECT a
FROM demo
WHERE UPPER( b ) LIKE '_li'; #匹配以li结尾的长度为3的的字符，不区分大小写

2.结合 not 使用
SELECT a
FROM demo
WHERE UPPER( b ) NOT LIKE '_li';#匹配不以li结尾的长度为3的的字符，不区分大小写

3.结合 escape 使用
SELECT a
FROM demo 
WHERE b LIKE '%25!%%' ESCAPE '!';#匹配含有25%的字符

#escape可以指定标识符，从而达到查找通配符的字符串

注意:

group by

作用：

按行或者表达式的值将行组合到分组汇总的行中，为每个分组返回一行
用法：

0.基本用法
SELECT a,b
FROM demo
WHERE a != 1
GROUP BY a,b; #查询a不等于1，a，b的组合分组 

1.结合聚合函数使用
SELECT a, COUNT(b)
FROM demo
WHERE a != 1
GROUP BY a,b; #查询a不等于1，a，b的组合分组中a和b的数目

*2.结合表达式使用
SELECT
    EXTRACT(YEAR FROM order_date) YEAR,
    COUNT( order_id )
FROM
    orders
GROUP BY
    EXTRACT(YEAR FROM order_date)
ORDER BY  YEAR;

4.结合函数rollup使用
SELECT a, COUNT(b)
FROM demo
WHERE a != 1
GROUP BY ROLLUP(a,b); #分组情况分为（a,b）(a, )(a, null)

注意

group by 语句要放在 from 子句后， where 子句前面
group by 查询结果只能包含聚合和分组的列，所以要注意 select 语句查询的列名
GROUP BY子句通常与AVG()，COUNT()，MAX()，MIN()和SUM()之类的集合函数一起使用。

having

作用：

与group by连用，用来过滤分组后的条件
用法：

0.基本用法
SELECT a
FROM demo
GROUP BY a
HAVING a != 1 #过滤 a!=1 的分组

注意：

HAVING子句过滤分组的行，而WHERE子句过滤行。
使用不带GROUP BY子句的HAVING子句，则HAVING子句将像WHERE子句那样工作。

exists

作用:

测试结果集中是否存在行（数据）
用法:

0.基础用法
SELECT *
FROM demo
WHERE EXISTS (subquery);#如果subquery结果集存在数据，查询demo中所有数据

注意:

exists 可以用于 updata、insert
exists 可以和null值比较，但是 in 不可以 ，例如subquery就可以是select null from demo where a = 1 #返回符合条件的列，每列值为null