oracle数据库之操作总结
## 连接数据库：
sqlplus test/test#123#@localhost:1521/ORCL
## 查询数据库所有的表：
select table_name from user_tables;
## 查询数据库表的字段名：
desc 表名;
## 设置oracle 客户端的字符集：
- Unix操作:
## 中文字符集 export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"; ## unicode字符集 export NLS_LANG=american_america.AL32UTF8
也可以编辑 bash_profile 文件进行永久设置：
vi .bash_profile NLS_LANG="Simplified Chinese_china".ZHS16GBK export NLS_LANG
## 使 bash_profile 设置生效
source .bash_profile
- Window操作:
## 中文字符集 set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK ## unicode字符集 set NLS_LANG=american_america.AL32UTF8 也可以通过修改注册表键值永久设置 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOMExx/NLS_LANG
## 查看oracle 客户端的字符集：
- Unix操作:
## 查看NLS_LANG
env|grep NLS_LANG
## 修改oracle 服务端的字符集：
## oracle 服务端字符集查询 SQL>select userenv('language') from dual; ## oracle 服务端字符集修改 1.管理员身份登录。 sqlplus /nolog SQL>conn /as sysdba 2.执行以下操作语句，关闭数据库,修改设置。 SQL>shutdown immediate; SQL>startup mount SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION; SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0; 3.开启修改后的数据库，修改字符集为ZHS16GBK。 SQL>alter database open; SQL>ALTER DATABASE character set INTERNAL_USE ZHS16GBK; 4.修改成功，重启数据库，查看当前的字符集。 SQL>shutdown immediate; SQL>startup SQL>select userenv('language') from dual；
## 导出数据表的insert语句
set head off set heading off set termout off set feedback off set term off set echo off set line 400 set pagesize 10000; spool test.sql; select 'insert into test(ID,NAME,SEX) values('||''''||ID||''''||','||''''||NAME||''''||','||''''||SEX||''''||');' from test; select 'commit;' from dual; spool off;
## 查询数据库的实例名
方法一：select instance_name from v$instance; 方法二：show parameter instance
########################################################################################
## 查询数据库的下的全部函数
select object_name from user_objects where object_type='FUNCTION';
## 查看函数内容，引号内的函数名大写
select text from user_source where name='函数名';
## 查询数据库用户下的全部函数
SELECT * FROM dba_objects WHERE object_type='FUNCTION'and owner='用户名';
## 查看用户下函数内容
SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL('FUNCTION', '函数名', '用户名')) FROM dual;
## 创建函数
create or replace function 函数名 函数内容
########################################################################################
## 设置主键自增，创建序列
create sequence TEST_SEQUENCE minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20; CREATE SEQUENCE seqTest INCREMENT BY 1 start with 1 NOMAXvalue NOCYCLE NOCACHE;
## 创建触发器
CREATE OR REPLACE TRIGGER test_tri BEFORE INSERT ON test FOR EACH ROW WHEN(new.id IS NULL) BEGIN select seqTest.nextval into :new.id from dual; END;
## 创建表
drop table test; create table test( ID varchar2(4), stu_name varchar2(4), CONSTRAINTS test_id PRIMARY KEY(ID) );
## 查询所有触发器
select * from user_triggers;
## 查询所有序列
select * from user_sequences;
## 删除触发器
DROP TRIGGER test_tri;
## 删除序列
DROP SEQUENCE seqTest;
## 查询序列当前值
select seqTest.currval from dual;
## 查询序列下一个值
select seqTest.nextval from dual;
########################################################################################
## 数据表添加字段
alter table test add my_name varchar2(4)
## 数据表删除字段
alter table test drop column my_name
## 修改数据表结构
alter table test modify stu_name varchar2(100);
########################################################################################
## 字符串和时间转换
-- 字符串转日期 SELECT to_date('20170531','yyyy/mm/dd') FROM DUAL; SELECT to_date('2019-03-21 00:00:00','yyyy-MM-dd HH24:mi:ss') FROM DUAL; SELECT to_timestamp('20160112 10:00:00', 'yyyymmdd HH:mi:ss') FROM DUAL; -- 日期转字符串： SELECT to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') FROM DUAL; SELECT to_char(sysdate,'YYYY"年"MM"月"DD"日"') FROM DUAL;
例子：
-- 查询某时间段的日志信息 SELECT * FROM ACTION_LOG WHERE LOG_TIME BETWEEN to_date ('2019-03-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND to_date ('2019-03-21 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
