Oracle日常操作与函数

weixin_39865

已于 2024-09-04 21:52:28 修改

阅读量386

点赞数 5

文章标签： oracle 数据库

于 2024-07-22 14:59:25 首次发布

本文链接：https://blog.csdn.net/weixin_39865829/article/details/140609676

版权

文章目录

一、Oracle启动与关闭
二、Oracle查询等
- 1.查看数据库相关信息
- 2.简单备份表
三、Oracle函数
六、其他操作
- 1.merge into 用法
- 2.v $s ql 和 v$ sqlarea

一、Oracle启动与关闭

1.启动Oracle

1.1 手动启动Oracle：登录服务器，切换到oracle用户

su - oracle

1.2 通过 lsnrctl status 命令查看Oracle监听器运行状况

#lsnrctl 监听器运行状况
lsnrctl status

当前监听为关闭状态

在这里插入图片描述

通过 lsnrctl start 启动监听，打开监听服务

#启动监听
lsnrctl start 
#关闭监听
lsnrctl stop

lsnrctl status再次查看，如下当前为已启动正常状态

在这里插入图片描述

1.3 以sysdba用户身份登录Oracle

1.31 #本地直接可以sys身份登录
sqlplus / as sysdba

在这里插入图片描述

1.32 #或者 以管理员身份运行 sqlplus /nolog 回车后，将出现提示符 SQL>然后输入 conn / as sysdba
sqlplus /nolog
#运行sqlplus命令，进入sqlplus环境，nolog参数表示不登陆到数据库服务器
conn / as sysdba

在这里插入图片描述

1.33 #账号密码登录
sqlplus
#登陆普通用户，@实例名可省略
sqlplus 用户名/密码@实例名

在这里插入图片描述

1.4 启动Oracle数据库

#启动命令
startup

在这里插入图片描述

#查看数据库的读写状态 ,当前查出是 读 写
select open_mode from v$database;

在这里插入图片描述

2.关闭Oracle

2.1 关闭数据库实例

shutdown immediate
#或者
shutdown

在这里插入图片描述

2.2 关闭监听器lsnrctl

lsnrctl stop

在这里插入图片描述

二、Oracle查询等

1.查看数据库相关信息

--数据库的信息
select * from v$database;

--实例的信息
select * from v$instance;

--版本信息
select * from v$version;

--所有的表信息
select * FROM dba_tables;

--查看数据库大小
select round(sum(bytes)/1024/1024/1024,2) as size_GB from dba_segments;

2.简单备份表

create table test_new as select * from test;--备份test表结构和数据
create table test_new as select * from test where 1=2; --只备份test表结构
insert into test_new select * from test；--插入test表数据 到test_new

三、Oracle函数

1.字符串函数

字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。

1.1 concat(X,Y)：连接字符串X和Y；或者：||

SELECT concat('Hello','world') FROM dual;--Helloworld

--连接多个字符串
SELECT 'Hello' || ' ' || 'World' || '!' as concatstring FROM dual;

1.2 length(X)：返回X的字符串长度

SELECT length('Hello') FROM dual;--5

1.3 replace(X)：返回X的长度

--replace(xxx,old,new)：在xxx中查找old，并替换成new--ABGG
SELECT replace('ABCD','CD','GG')FROM dual

1.4 lower(X)、upper（X）、initcap（X）：大小写转换

select lower('ORACLE') from dual;--oracle
select upper('oracle') from dual;--ORACLE
select initcap('oraCLE') from dual;--Oracle--initcap('str'):字符串首字母大写，其他全部小写

1.5 substr(‘str’,start,n）：字符串截取

对字符str从位置start开始，往后截取n个字符（字符串str字母下标从1开始）

select substr('oracle',2,4) from dual;--racl

1.6 trim ：剔除字符串、空格

trim ([leading/trailing/both] ‘value’ from ‘str’)：剔除字符串左/右/两边字符value（/空格）
trim(‘str’):不指明剔除方式，只能剔除字符串两边的空格

参数value只能是一个单个字符
leading:从字符串左边开始剔除字符value trailing:从字符串右边开始剔除字符value
both:从字符串两边开始同时剔除字符value

select trim(leading 'h' from 'hhoraclehh') from dual;--oraclehh
select trim(trailing 'h' from 'hhoraclehh') from dual;--hhoracle
select trim(both 'h' from 'hhoraclehh') from dual;--oracle
select trim('   hhoraclehh') from dual;--hhoraclehh

2.数字函数

数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。

2.1 ROUND(X[,Y]): X在第Y位四舍五入

在缺省 y 时，默认 y=0；比如：

ROUND(3.56)=4

y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65

y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400

2.2 TRUNC(x[,y])：直接截取到Y位。

在缺省 y 时，默认 y=0；比如：

TRUNC (3.56)=3

Y是正整数，就是四舍五入到小数点后 y 位。
TRUNC (5.654,2)=5.65

y 是负整数，四舍五入到小数点左边|y|位。
TRUNC (351.654,-2)=300

计算两个日期相差的天数
SELECT TRUNC(TO_DATE('2022-01-01', 'YYYY-MM-DD') - TO_DATE('2020-01-01', 'YYYY-MM-DD')) AS days_difference FROM dual;

计算当前年龄
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('1980-05-20', 'YYYY-MM-DD')) / 12) AS AGE FROM DUAL;

2.3 EXTRACT(ymd FROM d)：提取日期中的特定部分。

ymd为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE
类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。
HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。

例：

SELECT SYSDATE "date",
       EXTRACT(YEAR FROM SYSDATE)"year",
       EXTRACT(MONTH FROM SYSDATE)"month",
       EXTRACT(DAY FROM SYSDATE)"day",
       EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",
       EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",
       EXTRACT(SECOND FROM SYSTIMESTAMP)"second"
FROM dual;

在这里插入图片描述

3. 转换函数

转换函数将值从一种数据类型转换为另外一种数据类型。

3.1 TO_CHAR(d[,fmt]):把日期和数字转换为特定Fmt格式的字符串。

 SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;

在这里插入图片描述

3.2 TO_DATE(x,fmt) ：把一个字符串以fmt格式转换成一个日期类型

SELECT to_date('2023-01-01 00:00:01','yyyy-mm-dd hh24:mi:ss')  "date" FROM dual;

4. 通用函数

4.1 NVL (X,VALUE) ：如果X为空，返回value，否则返回X

4.2 NVL2(x,value1,value2)：如果x为空，返回value2，否则返回value1

4.3 decode：条件判断函数

decode函数的基本语法如下： expr：需要进行比较的表达式。 search1, search2, …：一系列要比较的值。
result1, result2, …：与每个搜索值相对应的结果值。
default：可选，如果没有匹配到任何搜索值，则返回的默认结果值。

 decode(expr, search1, result1, search2, result2, ..., default)

--根据学生的成绩等级，DECODE函数将其转换为相应的评价，如果没有匹配到任何条件，则返回默认值。
SELECT DECODE(grade, 'A', 'Excellent', 'B', 'Good', 'C', 'Average', 'Fail') AS result
FROM student;

4.4 case：流程控制语句流程控制函数

simple case(简单形式) 类似decode函数：

case expr 
		when compare1 then value1
		when compare2 then value2
		when compare3 then value3
		else defualtvalue 
	end alias

decode(expr ,compare1 ,value1,compare2 ,value2,compare3 ,value3,defualtvalue ) alias
--两个语句返回的结果是一样的

searched case(查询形式)：

condition1 是条件表达式与expr>comparevalue1一样最多支持255个参数，其中每对When…Then算作2个参数

	case 
		when condition1 then returnvalue1
		when condition2 then returnvalue2
		when condition3 then returnvalue3
		else defualtvalue 
	end
	case 
		when expr > comparevalue1 then returnvalue1
		when expr = comparevalue2 then returnvalue2*x
		when expr in (comparevalue3 ,comparevalue4 ) then returnvalue3
		else defualtvalue 
	end
实例
select
fname,
fweight,
(case
	when fweight <40 then 'thin'
	when fweight > 50 then 'fat'
	else 'ok'
 end ) as isnormal
from T_person

###4.5 row_number()：排序函数

统计每个部门薪资最高的员工信息（同一个部门的员工按照薪资进行降序排序）：

        第一种写法：row_number() over(partition by 一个或多个分组列 order by 一个或多个排序列 asc/desc) as 别名 
         //如果不写asc/desc的话，默认为asc 
        第二种写法：row_number() over(distribute by 一个或多个分组列 sort by 一个或多个排序列 asc/desc) as 别名
        在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行。
        
       select *, row_number() over(distribute by deptid sort by salary desc) rn from employee;

        //1.distribute by deptid sort by salary desc：按照部门deptid进行分组，每个分组内按照薪资即salary进行降序排序，即同一个部门的员工按照薪资进行降序排序
         //2.分组条件：distribute by deptid        排序条件：sort by salary desc
        //3.rn：为别名，代表每个分组中每行数据的所在序号ID，可用于根据rn序号ID直接获取出每个分组中的第一条数据，作用大。
 
      统计结果
        empid  deptid    sex    salary  rn
        1       10      female  5500.0  1
        2       10      male    4500.0  2
        4       20      male    4800.0  1
        3       20      female  1900.0  2
        7       40      male    44500.0 1
        6       40      female  14500.0 2
        5       40      female  6500.0  3
        9       50      male    7500.0  1
        8       50      male    6500.0  2
 
 
    直接取出rn的编号为1的记录，就是每个部门薪资最高的员工信息（获取出每个分组中薪资最高的员工信息，where条件为rn=1）
        select * 
        from (select *,
        row_number() over(distribute by deptid sort by salary desc) rn from employee) t where t.rn=1;
        //1.distribute by deptid sort by salary desc：按照部门deptid进行分组，每个分组内按照薪资即salary进行降序排序，即同一个部门的员工按照薪资进行降序排序
         //2.分组条件：distribute by deptid        排序条件：sort by salary desc
        //3.rn：为别名，代表每个分组中序号ID。
        //  t.rn=1：表示取每个分组中序号ID为1的数据
 
      统计结果
         empid  deptid    sex    salary  rn
         1       10      female  5500.0  1
        4       20      male    4800.0  1
        7       40      male    44500.0 1
        9       50      male    7500.0  1
	其他语句
	select bz.*,row_number() over(partition by bz.mingcheng order by bz.mingcheng, bz.shuliang desc) as rn
          from bz

5. 聚合函数

5.1 AVG ：平均值

语法：AVG（表达式）

5.2 SUM：求和

语法：SUM(表达式)

-- 对t_shouju表类别字段进行分组，再分别求和 a-e字段，求出 每个类别 a-e字段分别的和
 select y.leibie,
       sum(yb.a) a,
       sum(yb.b) b,
       sum(yb.c) c,
       sum(yb.d) d,
       sum(yb.e) f
  from t_shouju y 
 group by y.leibie

--对zfei表的id进行分组，先decode按分类进行条件判断，最后将jine字段值求和，求出每个id对应的fenlei的jine之和。
 select id,
      sum(decode(fenlei,'126',jine,0)) zfei,
      sum(decode(fenlei,'125',jine,0)) zjine,
      sum(decode(fenlei,'101',jine,0)) yfei,
      sum(decode(fenlei,'102',jine,0)) yfei,
      sum(decode(fenlei,'103',jine,0)) hfei,
      from zfei
      group by id

5.3 MIN、MAX：最小值、最大值

语法：MIN(表达式)、MAX(表达式)

5.4 COUNT：统计数据记录数

语法：COUNT（表达式）

六、其他操作

1.merge into 用法

判断表中有没有符合on（）条件中的数据，有了就执行UPDATE更新数据，没有就INSERT插入数据

MERGE INTO target_table tt  -- 目标表
USING source_table st -- 关联表
ON (tt.id = st.id AND tt.age = st.age ) -- 是否唯一条件，可以是多个 
--WHEN MATCHED AND tt.name <> st.name THEN  -- 1、满足条件--带额外条件，
WHEN MATCHED THEN  -- 2、满足条件-- 不带额外条件
UPDATE SET  -- 执行更新操作
    tt.name = st.name ,
    tt.age = st.age 
 ---以下非必须语句
[ WHEN NOT MATCHED THEN   -- 3、不满足条件, （可选，非必须语句）
INSERT (id, name) VALUES (st.id, st.name)];  -- 执行添加操作，注意没有表名 insert

2.v $s ql 和 v$ sqlarea

v $s ql 和 v$ sqlarea视图一定是常用的视图之一，通常可以用来查sql语句，sql_id，sql执行情况
V $SQ L 在子游标级别上列出了在共享 s ql 区域的统计信息，他将原始 s ql 文本展现为一行。 V$ SQL中的视图信息一般在sql执行的最后进行更新。然而，对于长时间执行的sql，每5秒会更新一次v $s ql 视图。 v$ sql列说明，如没有特别说明，均指子游标，存储的是具体的SQL 和执行计划相关信息，所以他是一个游标视图
v$sqlarea是一个综合视图，提供的是每条sql语句执行的汇总信息。它存储的SQL和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息。

select * from v$sql a where sql_text like '%slq语句%'  ;

select t.first_load_time, t.last_load_time, t.sql_text, t.sql_fulltext
  from v$sqlarea t
 where t.first_load_time between '2024-10-23/12:00:00' and
       '2024-10-22/12:30:00'
 order by t.first_load_time;