Oracle 数据库操作

1.创建数据库文件：

create tablespace [database_name] logging datafile 'F:\oracle\database_name.dbf' size 500m autoextend on next 500m maxsize 10240m extent management local;

2.创建数据库临时文件：

create temporary tablespace database_name_temp tempfile 'F:\oracle\database_name_temp.dbf' size 500m autoextend on next 500m maxsize 10240m extent management local;

3.创建用户与上述两个文件形成映射关系：

create user username identified by password default tablespace database_name temporary tablespace database_name_temp;

通过授权方式来创建用户：

grant connect,resource to test identified by test;

4.添加用户权限：

grant connect,resource,dba to username;//角色权限
grant create session to username;//登录权限

5.删除数据库：

conn username/password sysdba;
drop tablespace database_name including contents and datafiles;
drop tablespace database_name_temp including contents and datafiles;

6.删除用户：

drop user username cascade;

7.导入数据库：

imp username/password@localhost:1521/orcl file='database_file.dmp' full=y;
imp username/password@localhost:1521/orcl file=f:\databasename.dmp full=y ignore=y statistics=none buffer=819200;//ignore:忽视错误；statistics=none:exp和imp的版本存在差异；full=y:全部导入；buffer:设置缓冲区，默认为4k

impdp test/test@localhost:1521/orcl directory=dir_dmp dumpfile=skct_20200805.dmp remap_schema=skct:test

如果要导入的数据库没有表空间，则在impdp语句后加上参数：TRANSFORM=segment_attributes:n

关键字	说明	默认
USERID	用户名/口令
FULL	导入整个文件	(N)
BUFFER	数据缓冲区大小
FROMUSER	所有人用户名列表
FILE	输入文件	(EXPDAT.DMP)
TOUSER	用户名列表
SHOW	只列出文件内容	(N)
TABLES	表名列表
IGNORE	忽略创建错误	(N)
RECORDLENGTH	IO记录的长度
GRANTS	导入权限	(Y)
INCTYPE	增量导入类型
INDEXES	导入索引	(Y)
COMMIT	提交数组插入	(N)
ROWS	导入数据行	(Y)
PARFILE	参数文件名
LOG	屏幕输出的日志文件
CONSTRAINTS	导入限制	(Y)
DESTROY	覆盖表空间数据文件	(N)
INDEXFILE	将表/索引信息写入指定的文件
SKIP_UNUSABLE_INDEXES	跳过不可用索引的维护	(N)
FEEDBACK	每 x 行显示进度
TOID_NOVALIDATE	跳过指定类型 ID 的验证
FILESIZE	每个转储文件的最大大小
STATISTICS	始终导入预计算的统计信息
RESUMABLE	在遇到有关空间的错误时挂起
RESUMABLE_NAME	用来标识可恢复语句的文本字符串
RESUMABLE_TIMEOUT	RESUMABLE 的等待时间
COMPILE	编译过程, 程序包和函数	(Y)
STREAMS_CONFIGURATION	导入 Streams 的一般元数据	(Y)
STREAMS_INSTANITATION	导入 Streams 的实例化元数据	(N)
TRANSPORT_TABLESPACE	导入可传输的表空间元数据
TABLESPACES	将要传输到数据库的表空间
DATAFILES	将要传输到数据库的数据文件
TTS_OWNERS	拥有可传输表空间集中数据的用户

8.导出数据库：

导出数据库：

exp username/password@orcl file=d:/a.dmp full=y;//本地库
exp username/password@orcl file=d:/a.dmp owner=username;//本地库(根据用户名导出数据库)
exp username/password@IP:1521/orcl file=d:/a.dmp full=y;//远程库
exp username/password@orcl file=d:\t_20200611.dmp owner='username' statistics=none indexes=n buffer=5120000

--如果导出出现问题，可以使用以下方法：
--1.创建 DIRECTORY
create directory dir_dp as 'd:/dmp';
--2.导出
expdp username/password@localhost:1521/orcl directory=dir_dp dumpfile=a.dmp schemas=username;

关键字	说明	默认
USERID	用户名/口令
FULL	导出整个文件	(N)
BUFFER	数据缓冲区的大小
OWNER	导出指定的所有者用户名列表
FILE	输出文件	(EXPDAT.DMP)
TABLES	导出指定的表名列表
COMPRESS	是否压缩导出的文件	(Y)
RECORDLENGTH	IO 记录的长度
GRANTS	导出权限	(Y)
INCTYPE	增量导出类型
INDEXES	导出索引	(Y)
RECORD	跟踪增量导出	(Y)
ROWS	导出数据行	(Y)
PARFILE	参数文件名
CONSTRAINTS	导出限制	(Y)
CONSISTENT	交叉表一致性
LOG	屏幕输出的日志文件
STATISTICS	分析对象(ESTIMATE)
DIRECT	直接路径	(N)
TRIGGERS	导出触发器	(Y)
FEEDBACK	显示每 x 行 (0) 的进度
FILESIZE	各转储文件的最大尺寸
QUERY	选定导出表子集的子句
TRANSPORT_TABLESPACE	导出可传输的表空间元数据	(N)
TABLESPACES	导出指定的表空间列表

运行 sql 文件：

@f:\oracle-all.sql
start f:\oracle-all.sql

9.查看数据库表 user 中有哪些字段：

select * from user_col_comments where table_name='user';

10.查看当前用户的所有视图：

select * from user_views;

创建带有错误的视图：

create force view viewName as select * from userTable;//此 userTable 可以不存在

11.查看视图 test 定义语句：

select dbms_metadata.get_ddl('VIEW','test') from dual;
select text from all_views where view_name='test';

12.添加列

alter table tablename add (field VARCHAR2(50) NULL,field VARCHAR(2) NULL,...);

13.删除列

ALTER TABLE tablename DROP(field1,field2);

14.设置列可以为空

alter table tablename modify field null;

15.查看约束

select * from user_constraints;

-- 删除唯一约束条件
 alter table [tablename] DROP CONSTRAINT [CONSTRAINT_NAME]

16.创建序列

create sequence hq01_seq start with 1 increment by 1;

17.查看序列

select * from user_sequences;

18.创建触发器

create or replace trigger [triggername]
   before insert on [tablename]
      for each row
         when(new.[primaryKey] is null)
   begin
      select [createNewSequence].nextval into:NEW.[primaryKey] from dual;
end;

-- 删除触发器
DROP TRIGGER trigger_name;

19.比较两个时间段是否有重叠

SELECT COUNT(*) FROM HQ02 WHERE AHQ022=#ahq022#
		AND  (AHQ025,AHQ026)
		OVERLAPS
		(TO_DATE(#传入的开始时间参数#,'yyyy-MM-dd hh24:mi:ss'),TO_DATE(#传入的结束时间参数#,'yyyy-MM-dd hh24:mi:ss'))//需要注意的是时间的格式转换

 SELECT * FROM dual
     WHERE 
        (to_date('20170101','YYYYMMDD'),to_date('20190101','YYYYMMDD'))
     OVERLAPS
        (to_date('20160101','YYYYMMDD'),to_date('20170101','YYYYMMDD'));

//包体中数据的判断
SELECT COUNT(*) FROM HQ02 WHERE AHQ022=PRM_AHQ022 AND (AHQ025,AHQ026) OVERLAPS 	(PRM_AHQ025,PRM_AHQ026)

20.CREATE TYPE 定义新的用户定义数据类型。

CREATE OR REPLACE TYPE TABLE_AAC002_ISJY IS TABLE OF TYPE_AAC002_ISJY;

21.常见单行函数

分类：字符串相关，数字相关，日期相关，类型转换相关，NVL 函数。

字符串相关函数：

length(str) 			  --获得字符串 str 的字符长度（按字符计，汉字、英文、数字都是1个字符）
lengthb(str)              --获得字符串 str 的字符长度（按字节计，汉字是3个字节，英文1个字节）
concat(str1,str2)		  --相当于 str1||str2 的功能，字符串拼接
CONVERT( string1, char_set_to [, char_set_from] )  --返回特定字符集中的字符串值
INITCAP( string1 ) --string1 ：字符串参数，其中每个单词中的第一个字符将转换为大写字母，其余所有字符转换为小写字母。
substr(str,begin,length)  --截取字符串，从下标 begin 开始，截取 length 位
substr(str,begin,length) --按字节截取，不足1个汉字长度的，返回两个空格；多余1个汉字少于2个汉字，返回截取值+空格
instr(str1,str2,begin)    --在 str1 里查找 str2 ，从下标 begin 开始查找，找到返回 str2 首字母所在的下标，否则返回 -1
lower(str)/upper(str) 	   --将 str 转换成小写/大写
trim  					  --去除左右两边指定字符串
ltrim 					  --去除左边指定字符串
rtrim 					  --去除右边指定字符串
replace(str,old,new)  	    --替换字符串中指定字符为新的字符串

函数	说明	案例	结果
ASCII(X)	求字符X的ASCII码	select ASCII(‘A’) FROM DUAL;	65
CHR(X)	求ASCII码对应的字符	select CHR(65) FROM DUAL;	‘A’
LENGTH(X)	求字符串X的长度	select LENGTH(‘ORACLE技术圈’)from DUAL;	9
CONCATA(X,Y)	返回连接两个字符串X和Y的结果	select CONCAT(‘ORACLE’,‘技术圈’) from DUAL;	ORACLE技术圈
INSTR(X,Y[,START])	查找字符串X中字符串Y的位置，可以指定从Start位置开始搜索，不填默认从头开始	SELECT INSTR(‘ORACLE技术圈’,‘技术’) FROM DUAL;	7
LOWER(X)	把字符串X中大写字母转换为小写	SELECT LOWER(‘ORACLE技术圈’) FROM DUAL;	oracle技术圈
UPPER(X)	把字符串X中小写字母转换为大写	SELECT UPPER(‘Oracle技术圈’) FROM DUAL;	ORACLE技术圈
INITCAP(X)	把字符串X中所有单词首字母转换为大写，其余小写。	SELECT INITCAP('ORACLE is good ') FROM DUAL;	Oracle Is Good
LTRIM(X[,Y])	去掉字符串X左边的Y字符串，Y不填时，默认的是字符串X左边去空格	SELECT LTRIM(‘–ORACLE技术圈’,‘-’) FROM DUAL;	ORACLE技术圈
RTRIM(X[,Y])	去掉字符串X右边的Y字符串，Y不填时，默认的是字符串X右边去空格	SELECT RTRIM(‘ORACLE技术圈–’,‘-’) FROM DUAL;	ORACLE技术圈
TRIM(X[,Y])	去掉字符串X两边的Y字符串，Y不填时，默认的是字符串X左右去空格	SELECT TRIM(‘–ORACLE技术圈–’,‘-’) FROM DUAL;	ORACLE技术圈
REPLACE(X,old,new）	查找字符串X中old字符，并利用new字符替换	SELECT REPLACE(‘ORACLE技术圈’,‘技术圈’,‘技术交流’) FROM DUAL;	ORACLE技术交流
SUBSTR(X,start[,length])	截取字符串X，从start位置（其中start是从1开始）开始截取长度为length的字符串，length不填默认为截取到字符串X末尾	SELECT SUBSTR(‘ORACLE技术圈’,1,6) FROM DUAL;	ORACLE
RPAD(X,length[,Y])	对字符串X进行右补字符Y使字符串长度达到length长度	SELECT RPAD(‘ORACLE’,9,‘-’) from DUAL;	ORACLE—
LPAD(X,length[,Y])	对字符串X进行左补字符Y使字符串长度达到length长度	SELECT LPAD(‘ORACLE’,9,‘-’) from DUAL;	—ORACLE

CONVERT 函数返回特定字符集中的字符串值。 可用的字符集是：

字符集	描述
US7ASCII	美国 7 位 ASCII 字符集
WE8DEC	西欧 8 位字符集
WE8HP	惠普西欧 Laserjet 8 位字符集
F7DEC	DEC 法语 7 位字符集
WE8EBCDIC500	IBM 西欧 EBCDIC 代码第 500 页
WE8PC850	IBM PC 代码第 850 页
WE8ISO8859P1	ISO 8859-1 西欧 8 位字符集

22.日期相关函数

sysdate 						 --表示当前系统时间
add_months(myDate,num) 			 --对 myDate 做月份的算数运算
last_day(myDate) 				 --月份最后一天函数:获取 myDate 所在月份的最后1天
NEXT_DAY(r,c)                    --指定日期后一周的日期函数:：返回指定R日期的后一周的与r日期字符（c：表示星期几）对应的日期。
months_between(myDate1,myDate2)  --返回两个日期间的月份数：计算两个日期之间相隔几个月，返回值为小数
ADD_MONTHS（r,n）                --给日期加上指定的月份函数：该函数返回在指定日期r上加上一个月份数n后的日期。
trunc(myDate,日期格式字符串) 	    --根据指定日期格式对 myDate 日期做截断【可选日期格式字符串：year-按年截断，month-按月截断，day-星期，省略格式字符串表示截断时分秒】
ROUND（r[,f]）                    --日期截取函数：将日期r按f的格式进行四舍五入。如果f不填，则四舍五入到最近的一天。
EXTRACT（time）                   --返回指定日期中特定部分的函数：返回指定time时间当中的年、月、日、分等日期部分。

日期的四舍五入：

round(date,format) 	--date 为指定日期，format 为指定格式，默认为 format 为 ddd 四舍五入到某天
year 				--四舍五入到某年的1月1日
month 				--四舍五入到某月的1日
ddd 				--四舍五入到某天
day 				--四舍五入到某周的周日

23.数字相关函数

mod(num1,num2) 		  --取模，num1 % num2
trunc(n,length) 	  --对数字 n 进行截断，精度到小数点后 length 位
round(n1,length) 	  --对数字 n 四舍五入精度到小数点后 length 位
abs(num) 			  --对 num 取绝对值
dbms_random.random()  --获取随机数，产生一个很大的随机数（可正，可负）

函数	解释	案例	结果
ABS(X)	求数值X的绝对值	select abs(-9) from dual;	9
COS(X)	求数值X的余弦	select cos(1) from dual;	0.54030230586814
ACOS(X)	求数值X的反余弦	select acos(1) from dual;	0
CEIL(X)	求大于或等于数值X的最小值	select ceil(7.8) from dual;	8
FLOOR(X)	求小于或等于数值X的最大值	select floor(7.8) from dual;	7
log(x,y)	求x为底y的对数	select log(2,8) from dual;	3
mod(x,y)	求x除以y的余数	select mod(13,4) from dual;	1