当前位置：首页 > article >正文

oracle实例

article 2025/3/18 3:37:35

1：返回限定行数，并且每次查询返回的值是随机的

SELECT * FROM 
(
SELECT ename ,job FROM EMPLOYEE
order by dbms_random.value()
)
WHERE rownum <= 5 ;

2:把空值替换成现实值

SELECT COALESCE(comm,0) FROM EMPLOYEE;

3:排序

3.1：order by 3 desc/asc

按照第三列排序，从1开始从左往右计数

3.2：如果需要按照员工工号升序，薪资降序

SELECT * FROM employee 
ORDER BY empno ,sal desc;

两列用，隔开,如果查询里面有distinct、group by 不能使用查询列以外的列排序

3.3：按照员工姓名最后三位排序

SELECT ename ,job FROM EMPLOYEE
ORDER BY substr(ename,length(ename)-2);

3.4:按照薪资升序排序把空值放在最后

SELECT ename,sal,comm FROM
(SELECT ename,sal,comm case when comm is null then 0 else 1 end as is_null
FROM EMPLOYEE)
ORDER BY is_null desc ,comm;

或者

SELECT * FROM EMPLOYEE ORDER BY comm nulls last ;

如果是 nulls first 是把空值放在前面

如果响应降序排列，就在comm后面加上desc

3.5:如果职业是CLERK那就按照奖金排序，如果是其他职业就按照工资排序

SELECT * FROM EMPLOYEE ORDER BY CASE WHEN job ='CLERK' then comm else sal end ;l

4:合并两个表的信息

union过滤重复union all包含重复，如果没有特殊要求不能直接用union替换union all

4.1两个表的相同行

SELECT ename ,empno,job,sal,deptno
FROM emp
WHERE (ename,job,sal) in 
(select ename,job,sal from emp intersect select ename,job,sal from V);

intersect 交集

4.2:源表和另一个表中不一样的数据

SELECT EMPNO FROM EMP
minus
SELECT EMPNO FROM USER;

数据类型必须一样，minus也可以用not in ，如果混入null就都是null 要结合not exists

SELECT u.empno FROM USER u
WHERE not exists(SELECT NULL FROM EMPLOYEE e WHERE u.empno = e.empno);

NOT EXISTS 通常用于在主查询中排除与子查询结果匹配的行。

它与 EXISTS 操作符相反，EXISTS 用于检查子查询是否返回了至少一行。

5:默认值

create table d (id integer default 0);

创建表d ， id列（default 默认值）为0

6：把一个表的数据复制到另一个表

create table a as select * from b ;

可以加where条件

6.1 删除全表

delete from employee;

6.2 删除指定记录

delete from employee where empno ='0001';

6.3:删除重复记录

delete feom employee where id not in (select min (id) from employee group by name );
--保留工号最小的，如果想保留最大的工号就换成max()

删除名称重复的记录保留工号最小的记录。

7：创建分隔列表

select deptno ,
ltrim(sys_connect_by_path(ename,','),',')emps
from (select deptno,ename,row_number()over(partition by deptno order by empno) rn,  
count(*) over (partition by depnno)cnt from emp)
where level =cnt
start with rn =1
connect by prior deptno = deptno and prior rn =rn-1

PARTITION BY 是一个用于分析函数的关键字，它将查询返回的数据集按照指定的列进行分区，以便在每个分区内进行独立的计算。这种分区操作能够让分析函数在每个分区内执行，而不是在整个结果集上执行，从而实现更灵活的数据分析。

sys_connect_by_path内置函数创建分隔列表
ROW_NUMBER()这是一个窗口函数，它的作用是为每一行数据分配一个唯一的序号。

PARTITION BY deptno。这部分的作用是将数据按照 deptno 列的值分成多个逻辑分区。每个分区独立处理，相当于把数据按部门分组。

level是Oracle中用于递归查询的伪列，表示递归的层级。这里确保只取到每个部门的最后一行，因为此时level等于员工总数cnt

start with rn = 1：递归查询的起点是每个部门的第一行（行号为1的员工）。

connect by prior deptno = deptno and prior rn = rn - 1：递归条件，表示基于前一行的部门编号相同，且前一行的行号比当前行的行号少1。

sys_connect_by_path函数将递归路径中的员工名字用逗号连接起来，ltrim用于去掉最前面多余的逗号。

sys_connect_by_path(ename, ',')：这个函数会将递归路径中的ename值用逗号连接起来，但会在最前面多加一个逗号。例如，如果路径是'Alice,Bob,Charlie'，sys_connect_by_path会返回',Alice,Bob,Charlie'。

ltrim(..., ',')：去掉最前面的逗号，使结果变为'Alice,Bob,Charlie'

查看全文

http://www.kler.cn/a/589231.html