常用函数
to_char(time_stamp / (60 * 60 * 24) + to_date(’1970-01-01 08:00:00’, ‘yyyy-mm-dd hh:mi:ss’), ‘yyyy-mm-dd hh24:mi:ss’)
-- 1. SELECT 子句确定数据列; WHERE 子句控制数据行; ORDER BY子句永远最后执行;
-- 2. DECODE(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
SELECT ename, job, DECODE(job, 'CLERK', '办事员', 'SALESMAN', '销售')
FROM emp;
-- 3. NVL(eExpression1, eExpression2)；
--if eExpression1 is null: return eExpression2; elif eExpression1 is not null: return eExpression1;
SELECT empno, ename, job, sal,
NVL(comm, 0), (sal + nvl(comm, 0)) * 12 AS income
FROM emp;
-- 4. 时间操作 sysdate,天
SELECT SYSDATE,TO_CHAR(SYSDATE+7,'yyyy-mm-dd HH24:MI:SS') FROM DUAL; --加1星期
SELECT SYSDATE,TO_CHAR(SYSDATE+1,'yyyy-mm-dd HH24:MI:SS') FROM DUAL; --加1天
SELECT SYSDATE,TO_CHAR(SYSDATE+1/24,'yyyy-mm-dd HH24:MI:SS') FROM DUAL; --加1小时
SELECT SYSDATE,TO_CHAR(SYSDATE+1/24/60,'yyyy-mm-dd HH24:MI:SS') FROM DUAL; --加1分钟
SELECT SYSDATE,TO_CHAR(SYSDATE+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') FROM DUAL; --加1秒
TO_CHAR(TIME_STAMP/(60*60*24)+
TO_DATE('1970-01-01 08:00:00','yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
--查看日志量
SELECT TO_CHAR(t.event_time, 'yyyy/mm/dd') every_date, COUNT(1) 数量
FROM tfa_alarm t
WHERE t.event_time >= TO_DATE('2021/05/01', 'yyyy/mm/dd')
AND t.event_time < TO_DATE('2021/06/01', 'yyyy/mm/dd')
GROUP BY TO_CHAR(t.event_time, 'yyyy/mm/dd') // 每一天/月
ORDER BY every_date;
/*
SELECT TO_CHAR(t.event_time, 'yyyy/mm/dd') every_date, COUNT(1) 数量
FROM tfa_alarm t
WHERE t.event_time >= TO_DATE(to_char(trunc(SYSDATE-5),'yyyy/mm/dd'), 'yyyy/mm/dd')
AND t.event_time < TO_DATE(to_char(trunc(SYSDATE),'yyyy/mm/dd'), 'yyyy/mm/dd')
GROUP BY TO_CHAR(t.event_time, 'yyyy/mm/dd')
ORDER BY every_date;
*/
多表查询
点击查看代码
-- 1. 等值连接
SELECT e.empno, e.ename, e.job, e.sal, d.deptno, d.dname, s.grade
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno
BETWEEN s.losal
AND s.hisal;
-- 2. 左连接/右连接/
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+); --返回左表中的全部数据，再加上左表表和右表匹配后的数据；
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e
LEFT JOIN dept d
ON e.dpetno = d.deptno;
-- +号放在哪边就表示在哪边补空；
SELECT column_name(s)
FROM table_name1
[INNER|LEFT|RIGHT] JOIN table_name2
ON table_name1.column_name = table_name2.column_name
-- 3. GROUP BY
※※ 如果查询不使用GROUP BY子句，那么select的子句中只允许出现【统计函数】，其他任何字段不允许出现；
SELECT empno, count(*) FROM emp; ❌
SELECT count(*) FROM emp; ✔️
※※ 如果查询中使用了GROUP BY子句，SELECT子句中只允许出现分组字段、统计函数，其他任何字段都不允许出现；
SELECT ename,job,count(*) FROM emp GROUP BY job; ❌
SELECT job,count(*) FROM emp GROUP BY job; ✔️
※ 统计函数允许嵌套，但是嵌套之后的SELECT子句里面只允许出现嵌套函数，而不允许出现任何字段，包括分组字段；
SELECT deptno,max(avg(sal)) FROM emp GROUP BY deptno; ❌
SELECT max(avg(sal)) FROM emp GROUP BY deptno; ✔️
WITH temp AS(
SELECT '中国' nation ,'江苏' city FROM dual
UNION all
SELECT '中国' nation ,'上海' city FROM dual
UNION all
SELECT '中国' nation ,'北京' city FROM dual
UNION all
SELECT '美国' nation ,'纽约' city FROM dual
UNION all
SELECT '美国' nation ,'波士顿' city FROM dual
UNION ALL
SELECT '日本' nation ,'东京' city FROM dual
)
SELECT nation, listagg(city,',') WITHIN GROUP (ORDER BY city) AS Cities
FROM temp
GROUP BY nation
子查询
-- 找出所有员工中，工资最低的那个员工；
SELECT first_name, salary
FROM s_emp
WHERE salary = ( SELECT min(salary) FROM s_emp);
SELECT e.empno, e.ename
FROM emp e
JOIN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL) m
ON e.empno=m.mgr;
SELECT a.int_id,a.userlabel,a.object_class,a.city_id,a.machroom_id,
b.maintainer,b.ip_address,b.area_type,b.professional_type,b.room_id,
c.city_name
FROM objects_trans a
RIGHT JOIN olt b
ON (a.object_class = 2011 and a.rmuid = b.rmuid)
LEFT JOIN (SELECT DISTINCT city_name,city_id FROM region_city_local) c
ON a.city_id= c.city_id
-- 技巧
SELECT （子查询）
FROM （子查询）
SELECT (SELECT o.userlabel anode_name
FROM port p, objects_trans o
WHERE p.resourcesid = a.start_PORT_CUID
AND p.related_equip_id = o.int_id) anode_name --始端网元名称
FROM mv_z_sdh_tw_route_mz a
--分页导出3000W数据
SELECT /*+ FIRST_ROWS */
*
FROM (SELECT ROWNUM RN, A.*
FROM (SELECT C.INT_ID AS 唯一ID,
C.CIRCUIT_NAME AS 电路名称,
C.ANODE_NAME AS A端网元名称,
--C.ANODE_PORT_ID AS A端端口,
(SELECT P.USERLABEL
FROM PORT P
WHERE P.INT_ID = (C.ANODE_PORT_ID)) AS A端端口名称,
C.BNODE_NAME AS B端网元名称,
--C.BNODE_PORT_ID AS B端端口,
(SELECT P.USERLABEL
FROM PORT P
WHERE P.INT_ID = (C.BNODE_PORT_ID)) AS B端端口名称,
C.COL_ROUTE AS 路由顺序,
C.IS_BACKUP AS 主备关系
FROM CIRCUIT_ROUTE C
WHERE C.CIRCUIT_NAME LIKE '%KA%') A
WHERE ROWNUM <= 10)
WHERE RN >= 1
CRUD
点击查看代码
-- 1. CREATE
CREATE TABLE tmp_xy_20201109(
fp0 number NOT NULL,
event_time DATE NOT NULL
);
-- 2. 只是复制数据，把查询的结构建一个表
CREATE TABLE AS SELECT * FROM sx_alarm_fourteenth_num WHERE 1=2;
--产生与原表相同的表结构，包括主键和索引，数据需用INSERT INTO 语句
CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;
RENAME tmp_xy_20211109 TO tmp_xy_20211110
ALTER TABLE tmp_xy_20211109 ADD (ne_label VARCHAR2(255) )
UPDATE network_cloud_data set 列名1=值1, 列名2=值2, ... WHERE 条件
UPDATE kpidb.tpd_nssf_q_sx t
SET t.ne_id = NVL(
(SELECT max(o.int_id) FROM nrmdb.objects o
WHERE o.userlabel=(
SELECT tt.userlabel
FROM kpidb.network_cloud_data tt
WHERE tt.ne_id=t.ne_id AND tt.type_id=3107)
), t.ne_id
)
--3. MERGE INTO
MERGE INTO 目标表 a
USING 源表 b
ON (a.条件字段1=b.条件字段1 AND a.条件字段2=b.条件字段2 ...)
WHEN MATCHED THEN UPDATE SET a.更新字段=b.字段
WHEN not MATCHED THEN INSERT INTO a(字段1, 字段2, ...)VALUES(值1, 值2,...)
-- 4. INSERT INTO
INSERT INTO 表名称 VALUES(值1, 值2, ....)
INSERT INTO tbl_name1 (field1, field2, ...)
SELECT value1, value2, ... FROM tbl_name2
-- 5.SELECT INTO 语句可用于创建表的备份复件
SELECT * | SELECT LastName, FirstName
INTO Persons_backup | INTO Persons_backup
FROM Persons | FROM Persons
-- 6.DELETE TRUNCATE DROP
--除去表内的数据，但并不删除表本身
TRUNCATE TABLE 表名称 等效于 DELETE FROM table_name
DROP DATABASE 数据库名称 --删库跑路
-- 查找表中多余的重复记录（多个字段） https://www.cnblogs.com/chendezhen/p/14813677.html
SELECT * FROM vitae a
WHERE (a.peopleId, a.seq) IN (SELECT peopleId, seq FROM vitae
GROUP BY peopleId, seq HAVING count(*) > 1)
-- 删除表中多余的重复记录（多个字段），只留有rowid最小的记录
DELETE FROM vitae a
WHERE (a.peopleId, a.seq) IN (SELECT peopleId, seq FROM vitae
GROUP BY peopleId, seq HAVING count(*) > 1)
AND rowid NOT IN (SELECT MIN(rowid) FROM vitae GROUP BY peopleId, seq HAVING count(*)>1)
存储过程
1. 参考
Oracle数据库存储过程的创建及调用
Oracle 执行存储过程五种方法（带参数& 不带参数）
2. 结构
- 变量的类型：
- 普通型：变量名 变量类型（变量长度）
例如：v_name varchar2(20);
直接赋值语句 := 比如: v_name := ‘zhangsan’;
SELECT 'Hello World' INTO V_ADDR FROM DUAL;
-
引用型：变量的类型和长度取决于表中字段的类型和长度，通过 表名.列名%TYPE 指定变量的类型和长度
例如：v_name emp.ename%TYPE; -
记录型：接受表中的一整行记录,相当于Java中的一个对象语法：变量名称 表名 %ROWTYPE
例如：v_emp emp%rowtype;
DECLARE
-- 1. 声明变量、游标
I INTEGER;
BEGIN
-- 2. 执行语句
-- 3. [异常处理]
END;
3. 例子
待续。。。
Extra
- JDBC 格式
# Oracle JDBC Thin using an SID
sqlplus / as sysdba
select value from v$parameter where name='instance_name';
jdbc:oracle:thin:@host:port:SID
# Oracle JDBC Thin using a ServiceName
sqlplus / as sysdba
select value from v$parameter where name='service_names';
jdbc:oracle:thin:@//host:port/service_name
# Oracle JDBC Thin using a TNSName
jdbc:oracle:thin:@TNSName
jdbc:oracle:thin:@(
DESCRIPTION=(
ADDRESS_LIST=(
ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.91)(PORT=1521)
)
)
(CONNECT_DATA=(SERVICE_NAME=orcl))
)
- DBLINK
Oracle DBLINK 简单使用
-- Create database link
create public database link LK_KPIDB
connect to KPIDB
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.205.223.10)(PORT = 1621))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhzyjkdb)
)
)';
3、练习题
SELECT TOTAL.TABLESPACE_NAME,
ROUND(TOTAL.MB, 2) AS TOTAL_MB,
ROUND(TOTAL.MB - FREE.MB, 2) AS USED_MB,
ROUND((1 - FREE.MB / TOTAL.MB) * 100, 2) || '%' AS USED_PCT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AS MB
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) FREE,
(SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AS MB
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) TOTAL
WHERE FREE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME
ORDER BY USED_PCT DESC;
本文来自博客园，作者：anyu967，转载请注明原文链接：https://www.cnblogs.com/anyu967/articles/17317049.html
浙公网安备 33010602011771号