Oracle学习笔记

maiaimei 2024/4/23 Oracle

https://www.oracle.com/database/technologies/appdev/sql.html (opens new window)

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ (opens new window)

# DDL

Oracle数据库中，表名、列名、索引名的长度最多是30个字符。

在Oracle中，您可以使用以下查询来查看为表创建的约束（包括外键、主键、唯一约束、非空约束等）和索引：

查看约束：

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'YOUR_TABLE_NAME';

1
2
3

查看索引：

SELECT index_name, index_type
FROM user_indexes
WHERE table_name = 'YOUR_TABLE_NAME';

1
2
3

# 数据类型

# number(p,s)

p：精度位，precision，是有效数字的总位数（即小数点左边最多有p-s位有效数字），取值范围是1~38，用字符*表示38。 s：小数位，scale，是小数点右边数字的位数，取值范围是-84~127。

当 s>0时：精确到小数点后s位，并四舍五入。再校验有效位是否满足<=p。

当 s<0时：精确到小数点前s位，并四舍五入。再校验有效位是否满足 <= p + |s|。

当s=0时：此时number表示整数。

# 内置函数

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Functions.html (opens new window)

# 单行函数

字符函数

大小写控制函数

字符控制函数

-- 字符串索引从 1 开始。从第2位开始取4个字符。
select substr('HelloWorld',2,4) from dual; -- ello 
select instr('HelloWorld','w') from dual; -- 0
-- 去掉首尾指定字符
select trim('H' from 'HelloHWorldH') from dual; -- elloHWorld
-- 将指定字符全部替换为目标字符
select replace('abcdb','b','m') from dual; -- amcdm

1
2
3
4
5
6
7

数值函数

-- 四舍五入
select round(45.926) from dual; -- 46
select round(45.926,2) from dual; -- 46.93

-- 截断
select trunc(45.926,2) from dual; -- 45.92

-- 求余
select mod(1600,500) from dual; -- 100

1
2
3
4
5
6
7
8
9

日期函数

select sysdate from dual;

-- 日期加上或减去一个数字，结果仍为日期
select sysdate-1,sysdate,sysdate+1 from dual;

-- 两个日期相减，结果为它们之间相差的天数
select (sysdate+1)-sysdate from dual;

1
2
3
4
5
6
7

转换函数
通用函数

# 多行函数

# 分页查询

CREATE TABLE people  (
  id varchar2(10),
  name varchar2(10),
  sex varchar2(10),
  birthday char(8)
);
INSERT INTO people VALUES ('1', '小明', '男', '19890101');
INSERT INTO people VALUES ('2', '小红', '女', '19901002');
INSERT INTO people VALUES ('3', '小黄', '男', '19870716');
INSERT INTO people VALUES ('4', '小白', '女', '19861201');
INSERT INTO people VALUES ('5', '小蓝', '男', '19920518');
INSERT INTO people VALUES ('6', '小紫', '女', '19821122');

1
2
3
4
5
6
7
8
9
10
11
12

rownum是oracle特有的一个关键字。

rownum可以用于限制返回查询的总行数，且rownum不可以以任何表的名称作为前缀。

select rownum as rn,p.* from people p

对于基表，在insert记录时，oracle就按照insert的顺序，将rownum分配给每一行记录，因此在select一个基表的时候，rownum的排序是根据insert记录的顺序显示的。

select t.* 
from (
  select rownum as rn, p.* 
  from people p
) t 
where t.rn >= 1 and t.rn <= 2;

1
2
3
4
5
6

对于子查询，则rownum的顺序是根据子查询的查询顺序进行动态分配的。

select tt.id,tt.name,tt.sex,tt.birthday 
from (
	select rownum as rn,t.*
	from (
	  select * 
	  from people
	  order by birthday
	) t 
) tt
where tt.rn >= 1 and tt.rn <= 2;

1
2
3
4
5
6
7
8
9
10

rownum对于等于某值的查询条件：如果想找到第一条查询数据，可以使用rownum=1作为查询条件，但是想找到第二条查询数据，使用rownum=2则查不到数据，原因是：rownum都是从1开始，但是1以上的自然数与rownum做等于时，都认为是false条件，所以无法查询到rownum=n (n>1的自然数)。

select * from people where rownum = 1;
select * from people where rownum = 2; --查不到数据

1
2

rownum对于大于某值的查询条件：要是想查询出第二行以后的记录，直接使用rownum>2是查不出数据的，原因是rownum是一个总是以1开始的伪例，rownum>n (n>1的自然数)依然不成立。可以使用子查询来解决，注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。

select * from people where rownum > 2;  --查不到数据
select * from (
    select rownum as rn,p.* from people p
) t where t.rn > 2;

1
2
3
4

rownum对于小于某值的查询条件： rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。

select * from people where rownum < 3

注意：对于查询rownum在某区间的数据，必须使用子查询，例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句，先让它返回小于等于三的记录行，然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。

select tt.id,tt.name,tt.sex,tt.birthday 
from (
	select rownum as rn,t.*
	from (
	  select * 
	  from people
	  order by birthday
	) t 
    where rownum <= 2
) tt
where tt.rn >= 1;

1
2
3
4
5
6
7
8
9
10
11

Windows实用技巧 Usage of nested tables in package

初冬十月的笔记

Choose mode