Oracle-DQL（数据查询）笔记

目录

 一、基本查询语句

二、过滤与排序

三、单行函数

四、多表查询

五、分组函数

六、子查询

---

• DML：Data manipulation language 数据操纵语言
• DDL：Data Definition language 数据定义语言
• DCL：Data Controller language 数据控制语言

DML用于查询和修改数据记录：insert、update、delete、select

DDL用于创建、修改、删除数据库对象：create table、alter table、drop table、create index、drop index...

DCL用来控制数据库的访问：grant授权、revoke撤销访问权限、commit提交事务处理、

rollback事务回退处理、savepoint设置保存点、lock对数据库的特定部分进行锁定

 一、基本查询语句

SQL> select sysdate,sysdate+1 from dual;

sysdate:查询系统时间

dual:是一个伪表

时间可以与数字运算，单位为天

注：

--空值不同于0，凡是null参与的运算，结果还是null；

字符串连接：使用||

select 'a'||'b' from dual;

字符串:字符串可以是字符、数字、日期，

日期和字符串只能在单引号中出现；

别名：使用双引号或不用，可以使用双引号“job id”将两个单词作为别名；

字符大小写敏感、日期格式敏感。'Hello'、'2022-02-01'

二、过滤与排序

1、DISTINCT

去重查询

SELECT

DISTINCT column_1

FROM table_name;

多列时表示多列的组合去重查询；DISTINCT将NULL值视为重复值。

2、BETEEN AND 表示双闭区间；

3、LIKE 模糊匹配

expresion [NOT] LIKE pattern [ ESCAPE escape_characters ]

％：匹配零个或多个字符的任何字符串。

_：匹配任何单个字符

ESCAPE子句允许查找包含一个或多个通配符的字符串

LIKE '%25!%%' ESCAPE '!' --查询包含25%的字符串

4、is null

5、ORDER BY

desc降序、默认asc升序

SELECT

column_1, column_2, column_3, ...

FROM table_name

ORDER BY

column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],

column_2 [ASC | DESC] [NULLS FIRST | NULLS LAST],

nulls first--null值排在前面

请注意，ORDER BY子句总是SELECT语句中的最后一个子句

ORDER BY子句可在一列上应用一个函数或别名，例如字符串函数，数学函数等，并按函数的结果对数据进行排序。 

Fetch 限制行数（12C以上版本有效）

 

三、单行函数

指一行输入，输出一行

多行函数指：输入多行，输出一行，即分组函数；

字符函数

lower()、upper()大小写转换

initcap()首字母大写

concat('a','b')字符串连接

substr('hello',1,4)hell

length('hello')字符串长度5

instr('hello','e')某字符在字符串中首次出现的位置2

lpad(salary,10,'*')数值为10位，不足左边补*；******2400

rpad()不足右边补*

trim('H' from 'Hello') ello 默认去掉空格，首尾去掉

replace('abcd','b','m') amcd

SQL中字符的索引是从1开始的，java从0开始

数字函数

round(45.123,1) --45.1 四舍五入

trunc(45.123,1) --45.1 截断

mod(8,3) --2 求余数

日期函数

两个日期相减返回日期之间相差的天数。

months_between()两个日期相差的月数

add_months()指定日期加上若干月数

next_day 指定日期的下一个星期对应的日期 next_day(sysdate,'星期二')

last_day 本月最后一天

round 日期四舍五入

trunc 日期截断

转换函数：显式和隐式

Oracle自动完成下列转换

源数据类型	目标数据类型
varchar2 or char	number
varchar2 or char	date
number	varchar2
date	varchar2

显示转换：to_date 字符串转日期

to_char:数值或日期 转字符串

to_number:字符串转数字

to_char函数对数字的转换，常用的几种格式：

9 数字

0 零

$ 美元

L 本地货币符号

. 小数点

， 千位符

eg: select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

--2023年05月22日 16:40:42

通用函数 适用于任何数据类型，同时适用null

nvl(expr1,expr2) 将null转换成一个已知的值

nvl2(expr1,expr2,expr2) 当expr1不为null，结果为expr2,否则为expr3

nullif(expr1,expr2) 相等返回null,不等返回expr1

coalesce(expr1,expr2,....,exprn) 与nvl类似，可以同时处理交替的多个值

条件表达式

在SQL中使用if-then-else逻辑

使用两种方法：

--case 表达式

--decode函数

SQL CASE 表达式是一种通用的条件表达式，类似于其它语言中的 if/else 语句。

1、CASE子句可以用于任何表达式可以有效存在的地方。 condition 是一个返回boolean 的表达式。 如果结果为真，那么 CASE 表达式的结果就是符合条件的 result。

 如果结果为假，那么以相同方式搜寻任何随后的 WHEN 子句。 如果没有 WHEN condition 为真，那么 case 表达式的结果就是在 ELSE 子句里的值。 如果省略了 ELSE 子句而且没有匹配的条件， 结果为 NULL。

2、CASE 字段 WHEN 预期值 THEN 结果1 ELSE 结果2 END

如果字段值等于预期值，则返回结果1，否则返回结果2。

1、case when 条件1 then 返回值1 when 条件2 then 返回值2 end。

2、case 字段 when 值 then 返回值 when 值2 then 返回值2 end。

decode(depart_id,10,sal*1.1,

20,sal*1.2,

30,sal*1.3) new_sal

四、多表查询

等值连接：select a.id,a.b_id,b.name from a,b where a.b_id=b.id;

注：连接n个表，至少需要n-1个连接条件

非等值连接：也是两个表的关联查询。

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。

select a.id,a.b_id,b.name from a,b where a.b_id=b.id;--(内连接)

select a.id,a.b_id,b.name from a join b using(b_id);

select a.id,a.b_id,b.name from a join b on a.b_id=b.id;

外连接：两个表在连接过程中除了返回满足连接条件的行，还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。

select a.id,a.b_id,b.name from a,b where a.b_id=b.id(+); --左外连接

select a.id,a.b_id,b.name from a left outer join b on a.b_id=b.id; --左外连接

select a.id,a.b_id,b.name from a full outer join b on a.b_id=b.id; --满外连接

自连接：两个相同的表自连接

注：

natural join -- 有局限性，会自动连接两个表中相同的列，不能指定连接的字段。

join...using --有局限性，只能连接两个表中字段名、类型一样的表。

五、分组函数

GROUP BY分组

GROUP BY子句为每个分组返回一行。

GROUP BY子句通常与AVG()，COUNT()，MAX()，MIN()，SUM()、STDDEV()标准差、之类的集合函数一起使用。

注：count(expr) 只计算expr非null的行数。使用nvl函数使分组函数无法忽略NULL值。

count(distinct expr)返回expr非空且不重复的记录总数。

不能在where子句中使用组函数，

HAVING 子句：可以使用组函数，过滤由GROUP BY子句返回的分组。

Group by rollup(A ,B)

产生的分组种数：3种；

第一种：group by A,B

第二种：group by A

第三种：group by NULL

返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。

六、子查询

子查询要包含在括号内；

将子查询放在比较条件的右侧。

单行操作符对应单行子查询，多行操作符对应多行子查询。

单行子查询支持：= < > >= <= <>

多行子查询支持：in、any(和子查询返回的某个值比较）all(和子查询返回的所有值比较)

ANY运算符

ANY运算符用于将值与子查询返回的值或结果集列表进行比较

operator ANY ( v1, v2, v3)

operator ANY ( subquery)

ANY运算符前面必须有一个运算符，例如：=，!=，>，>=，<，<=。

列表或子查询必须用圆括号包围。

SELECT * FROM table_name

WHERE c > ANY ( v1, v2, v3 ); --即比最小的大

等价于

SELECT * FROM table_name

WHERE c > v1 OR c > v2 OR c > v3;

ALL运算符

WHERE c > all ( v1, v2, v3); --即比最大的大