oracle数据库学习小记一

数据库函数

一、日常函数

返回字符X的ASCII码：
ASCII(X)

连接字符串X和Y
CONCAT(X,Y)

从X中查找str，可以指定从start开始，也可以指定从n开始
INSTR(X,STR[,START][,N)

返回X的长度
LENGTH(X)

X转换成小写
LOWER(X)

X转换成大写
UPPER(X)

把X的左边截去trim_str字符串，缺省截去空格
LTRIM(X[,TRIM_STR])

把X的右边截去trim_str字符串，缺省截去空格
RTRIM(X[,TRIM_STR])

把X的两边截去trim_str字符串，缺省截去空格
TRIM([TRIM_STR FROM]X)

在X中查找old，并替换成new
REPLACE(X,old,new)

截取字符串
SUBSTR(X,start[,length])

二、数学函数

返回x的绝对值
abs(x)

返回x的二进制（oct返回八进制，hex返回十六进制）
bin(x)

返回大于x的最小整数值
ceiling(x)

返回值e（自然对数的底）的x次方
exp(x)

返回小于x的最大整数值
floor(x)

返回集合中最大的值
greatest(x1,x2,…,xn)

返回集合中最小的值
least(x1,x2,…,xn)

返回x的自然对数
ln(x)

返回x的以y为底的对数
log(x,y)

返回x/y的模（余数）
mod(x,y)

返回pi的值（圆周率）
pi()

返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。
rand()

返回参数x的四舍五入的有y位小数的值
round(x,y)

返回代表数字x的符号的值
sign(x)

返回一个数的平方根
sqrt(x)

返回数字x截短为y位小数的结果
truncate(x,y)

三、聚合函数(常用于group by从句的select查询中)

返回指定列的平均值
avg(col)

返回指定列中非null值的个数
count(col)

返回指定列的最小值
min(col)

返回指定列的最大值
max(col)

返回指定列的所有值之和
sum(col)

返回由属于一组的列值连接组合而成的结果
group_concat(col)

四、字符串函数

返回字符的ascii码值
ascii(char)

返回字符串的比特长度
bit_length(str)

将s1,s2…,sn连接成字符串
concat(s1,s2…,sn)

将s1,s2…,sn连接成字符串，并用sep字符间隔
concat_ws(sep,s1,s2…,sn)

将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
insert(str,x,y,instr)

分析逗号分隔的list列表，如果发现str，返回str在list中的位置
find_in_set(str,list)

返回将字符串str中所有字符改变为小写后的结果
lcase(str)或lower(str)

返回字符串str中最左边的x个字符
left(str,x)

返回字符串str中的长度
length(s)

从字符串str中切掉开头的空格
ltrim(str)

返回子串substr在字符串str中第一次出现的位置
position(substr in str)

用反斜杠转义str中的单引号
quote(str)

返回字符串str重复x次的结果
repeat(str,srchstr,rplcstr)

返回颠倒字符串str的结果
reverse(str)

返回字符串str中最右边的x个字符
right(str,x)

返回字符串str尾部的空格
rtrim(str)

比较字符串s1和s2
strcmp(s1,s2)

去除字符串首部和尾部的所有空格
trim(str)

返回将字符串str中所有字符转变为大写后的结果
ucase(str)或upper(str)

五、日期和时间函数

返回当前的日期
curdate()或current_date()

返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化)
date_add(date,interval int keyword)
如：selectdate_add(current_date,interval 6 month);

依照指定的fmt格式格式化日期date值
date_format(date,fmt)

返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化)
date_sub(date,interval int keyword)
如：selectdate_sub(current_date,interval 6 month);

返回date所代表的一星期中的第几天(1~7)
dayofweek(date)

返回date是一个月的第几天(1~31)
dayofmonth(date)

返回date是一年的第几天(1~366)
dayofyear(date)

返回date的星期名，如：select dayname(current_date);
dayname(date)

根据指定的fmt格式，格式化unix时间戳ts
from_unixtime(ts,fmt)

返回time的小时值(0~23)
hour(time)

返回time的分钟值(0~59)
minute(time)

返回date的月份值(1~12)
month(date)

返回date的月份名
monthname(date)
如：select monthname(current_date) from aaa;

返回当前的日期和时间
now()

返回date在一年中的季度(1~4)
quarter(date)
如select quarter(current_date) from aaa;

返回日期date为一年中第几周(0~53)
week(date)

返回日期date的年份(1000~9999)
year(date)

六、加密函数

aes_encrypt(str,key)
返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用aes_encrypt的结果是一个二进制字符串，以blob类型存储

aes_decrypt(str,key)
返回用密钥key对字符串str利用高级加密标准算法解密后的结果

decode(str,key)
使用key作为密钥解密加密字符串str

encrypt(str,salt)
使用unixcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str

encode(str,key)

使用key作为密钥加密字符串str，调用encode()的结果是一个二进制字符串，它以blob类型存储

md5()
计算字符串str的md5校验和

password(str)
返回字符串str的加密版本，这个加密过程是不可逆转的，和unix密码加密过程使用不同的算法。

sha()
计算字符串str的安全散列算法(sha)校验和

七、控制流函数

mysql有4个函数是用来进行条件操作的，这些函数可以实现sql的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。

mysql控制流函数：

case when[test1] then [result1]…else [default] end如果testn是真，则返回resultn，否则返回default

case [test] when[val1] then [result]…else [default]end 如果test和valn相等，则返回resultn，否则返回default

if(test,t,f) 如果test是真，返回t；否则返回f

ifnull(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2

nullif(arg1,arg2) 如果arg1=arg2返回null；否则返回arg1

这些函数的第一个是ifnull()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是null，函数就会向调用者返回第一个参数；如果是null,将返回第二个参数。

八、格式化函数

依照字符串fmt格式化日期date值
date_format(date,fmt)

把x格式化为以逗号隔开的数字序列，y是结果的小数位数
format(x,y)

返回ip地址的数字表示
inet_aton(ip)

返回数字所代表的ip地址
inet_ntoa(num)

依照字符串fmt格式化时间time值
time_format(time,fmt)

九、类型转化函数

为了进行数据类型转化，mysql提供了cast()函数，它可以把一个值转化为指定的数据类型。类型有：binary,char,date,time,datetime,signed,unsigned

十、系统信息函数

返回当前数据库名
database()

将表达式expr重复运行count次
benchmark(count,expr)

返回当前客户的连接id
connection_id()

返回最后一个select查询进行检索的总行数
found_rows()

返回当前登陆用户名
user()或system_user()

查询出来的字段拼接成一个字符串
WM_CONCAT(tt.c_name)

oracle日常命令

进入数据库：
sqlplus sys/123456 as sysdba

查看表空间：
select * from Dba_Tablespaces

查看表空间有哪些用户：
select distinct owner from dba_segments where tablespace_name =‘HIS_DATA’;

通过用户查询表空间：
select a.tablespace_name,a.* from dba_segments a where owner=‘YHHIS’

查看有哪些用户在连接数据库：
select username,sid,serial# from v$session where username=‘AUTO_NCC_NEW’

查询表空间剩余内容：
SELECT a.tablespace_name ,–表空间
Round(a.bytes/(102410241024),3),-- 大小G
Round(b.bytes/(102410241024)), --已使用G
Round(c.bytes/(102410241024)) ,–剩余G
Round(b.bytes100/a.bytes,3), --使用百分比%
Round(c.bytes100/a.bytes,3) --剩余百分比%
FROM sys.Sm$t{s}_{A}vaila,sys.Sm$ts_Used b,sys.sm$ts_free c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;

两个日期相减计算各类型：
–天
select (to_date( t.A,‘yyyy-mm-dd hh24:mi:ss’)- to_date( t.B,‘yyyy-mm-dd hh24:mi:ss’)) from table t
–小时
select (to_date( t.A,‘yyyy-mm-dd hh24:mi:ss’)- to_date( t.B,‘yyyy-mm-dd hh24:mi:ss’))24 from table t
–分
select (to_date( t.A,‘yyyy-mm-dd hh24:mi:ss’)- to_date( t.B,‘yyyy-mm-dd hh24:mi:ss’))2460 from table t
–秒
select (to_date( t.A,‘yyyy-mm-dd hh24:mi:ss’)- to_date( t.B,‘yyyy-mm-dd hh24:mi:ss’))246060 from table t

误删数据后闪回，找回数据sql：
select * from tablename as of timestamp to_timestamp(‘2023-01-04 15:16:52’,‘yyyy-mm-dd hh24:mi:ss’)

oracle 恢复库指令

创建目录导入导出目前(只需要操作1次)
create directory 目录名 as ‘路径’;

导出用户授目录读写权限(只需要操作1次)
grant read,write on directory 目录名 to test;

导出库：
exp 数据库用户名/数据库密码@127.0.0.1/数据库名 file=‘D:\文件名.dmp’ owner(scott)

导入库：
imp 数据库用户名/数据库密码@127.0.0.1/数据库名 file=‘/u01/backup/文件名.dmp’ ignore=y full=y

创建表空间
create tablespace 表空间名 datafile size 512m autoextend on next 8m maxsize unlimited;–连接数据库下进行
create tablespace flhis datafile ‘D:\app\oracle\oradata\orcl\数据库备份文件名.dbf’ size 512m autoextend on next 8m maxsize unlimited;

创建用户名：
CREATE USER 数据库用户名 IDENTIFIED BY YHJW DEFAULT TABLESPACE 表空间名 TEMPORARY TABLESPACE temp profile DEFAULT;–连接数据库下进行

授权：
grant create session, create table, create sequence, create any view,create any procedure ,unlimited tablespace ,connect,resource,dba to 数据库用户名;–连接数据库下进行

删库用户
drop user 数据库用户名 cascade;

删除表空间
drop tablespace 表空间名 including contents and datafiles;

建表语句

创建表
create table B_CODEITEM_LS
(
c_id VARCHAR2(20) not null,
c_classcode VARCHAR2(20) not null,
c_code VARCHAR2(20) not null,
c_name VARCHAR2(100) not null,
c_note VARCHAR2(1000) not null,
c_sno NUMBER(38) not null,
c_status NUMBER(38) not null,
c_builddate DATE not null,
c_reserved NUMBER(38) not null,
c_num3 NUMBER(14,4) not null,
c_standard_code VARCHAR2(50) not null,
c_co_id VARCHAR2(20) not null

)
tablespace HIS_TEST11
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

创建主键
alter table B_CODEITEM_LS
add constraint PK_B_CODEITEM_LS primary key (C_ID)
using index
tablespace HIS_TEST11
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

创建索引
create sequence SEQ_B_CODEITEM_LS
minvalue 1
maxvalue 99999999999999999999
start with 1
increment by 1
cache 20;

commit;

ftp异地备份数据库

保存文件后改为bat文件，设置定时任务，自动执行
@echo off
Set OrclSid=数据库名
Set ExpUser=数据库用户名
Set ExpPass=数据库地址
Set FileDir=备份的数据库文件目录
Set SysDate=%date:_0,4%%date:5,2%%date:~8,2%
Set FileName=%OrclSid%%SysDate%

@echo * * * * * * * * * * * * * * * * * * * *
@echo * * * * * * * * * * * * * * * * * * * *
@echo * * * * * ORACLE 数据库备份 * * * * * *
@echo * * * * * * * * * * * * * * * * * * * *
@echo * * * * * * * * * * * * * * * * * * * *

@echo 服务名 = %OrclSid%
@echo 用户名 = %ExpUser%
@echo 密 码 = %ExpPass%
@echo 目 录 = %FileDir%
@echo 时 间 = %SysDate%
@echo 文件名 = %FileName%.rar

@echo 开始备份数据库…
exp %ExpUser%/%ExpPass%@%OrclSid% file=%FileDir%/%FileName%.dmp log=%FileDir%/%FileName%.log
::删除7天前的备份。
forfiles /p %FileDir% /s /m xzyydb*.dmp /d -3 /c “cmd /c del @path”
::exit
::pause

::设定连接ftp所需参数
set localdmp=D:\ftp\xzyydb%SysDate%.dmp
set locallog=D:\ftp\xzyydb%SysDate%.logs

::连接ftp
Echo open ftp异地服务器地址 >ftp.up
Echo ftp用户名>>ftp.up
Echo ftp密码>>ftp.up

::打开ftp路径并上传文件
Echo Cd .\ >>ftp.up
Echo binary>>ftp.up
Echo put %localdmp%>>ftp.up
Echo put %locallog%>>ftp.up
Echo bye>>ftp.up
FTP -s:ftp.up
del ftp.up /q

::删除7天以上的文件
set DATADIR=D:\ftpDB
forfiles /p “%DATADIR%” /s /m . /d -3 /c “cmd /c del @path”

exit