Oracle相关的配置、操作（附：SQL语句）

**oracle安装，省略。。。****配置oracle环境变量**

NLS_LANG
AMERICAN_AMERICA.AL32UTF8

ORACLE_HOME
D:\oracle\product\11.2.0\dbhome_1

ORACLE_SID
orcl

TNS_ADMIN
D:\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN

**oracle服务**

启动监听服务：lsnrctl start
关闭监听服务：lsnrctl stop
启动核心服务：
net start oracleserviceORCL
关闭核心服务：
net stop OracleServiceORCL
数据库SID是：ITDB,启动命令为：
net stop OracleServiceITDB

**黑屏，进入客户端界面**

在“运行”中输入：
  (1) sqlplus scott/tiger      //不带界面
  (2) sqlplusw scott/tiger //带有窗口界面
  (3) 使用sql/plus第三方客户端

**DDL与DML**

DDL （Data Definition Language 数据定义语言）
create table 创建表    
alter table  修改表   
drop table 删除表   
truncate table 删除表中所有行    
create index 创建索引   
drop index  删除索引 
当执行DDL语句时，在每一条语句前后，oracle都将提交当前的事务。如果用户使用insert命令将记录插入到数据库后，执行了一条DDL语句(如create table)，此时来自insert命令的数据将被提交到数据库。当DDL语句执行完成时，DDL语句会被自动提交，不能回滚。

DML （Data Manipulation Language 数据操作语言）
insert 将记录插入到数据库 
update 修改数据库的记录 
delete 删除数据库的记录 
当执行DML命令如果没有提交，将不会被其他会话看到。除非在DML命令之后执行了DDL命令或DCL命令，或用户退出会话，或终止实例，此时系统会自动发出commit命令，使未提交的DML命令提交。

总结
DDL语句会自动提交
DML语句不会自动提交

**库、表、用户相关操作**

1.查看当前数据库
select * from v$database; //必须以管理员身份登录
或者
show parameter instance_name;
2.查看数据文件
select name from v$datafile;
3.查看日志文件
select member from v$logfile;
4.查看控制文件
select name from v$controlfile;
5.查看sga内存分配情况：
show sga;
6.查看当前用户表情况：
select table_name from user_tables;
7.查看当前用户：
show user;
8.建表语句：
create table classes
(
   cid integer primary key,
   cname varchar2(20)
)
create table student
(
   sid integer primary key,
   sex varchar2(10) check (sex in('M','F')),
   sage integer,
   saddr varchar2(20),
   cid integer references classes(cid)
)
9.查看某个表的所有约束[示例：查看student表的所有约束信息]
select owner,constraint_name,constraint_type,table_name 
from user_constraint where table_name='STUDENT'

也可以表创建完后，再添加列或添加表约束,如:
alter table student add (cid int); //添加一列
alter table student add constraint ck_sex check(sex in('M','F'));//添加检查约束
alter table student add constraint fk_classes 
foreign key (cid) rererences classes(cid);//添加外键约束

10.如何激活scott用户
select username,account_status from dba_users;查看用户表中有没有scott,和他的状态
alter user scott account unlock;解锁scott
解锁之后可能会要求你该密码,到时候你直接把密码改成tiger就可以了。

11、给scott用户赋予最高权限：
conn/as sysdba;
grant dba to scott; 

**oracle完全卸载步骤**

0.运行卸载程序
1. 运行regedit，选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，按del键删除这个入口。 
2. 运行regedit，选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services，滚动 这个列表，删除所有Oracle入口。 
3. 运行refedit，选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application， 删除所有Oracle入口。 
4.  删除环境变量CLASSPATH和PATH中有关Oracle的设定 
5. 从桌面上、STARTUP(启动)组、程序菜单中，删除所有有关Oracle的组和图标 
6.删除与Oracle有关的文件，选择Oracle所在的缺省目录C:\Oracle，删除这个入口目录及所有子目录，并从Windows 2000目录
(一般为C:\WINNT)下删除以下文件ORACLE.INI、oradim73.INI、oradim80.INI、oraodbc.ini等等。 
7. WIN.INI文件中若有[ORACLE]的标记段，删除该段 
8. 如有必要，删除所有Oracle相关的ODBC的DSN 
9. 重新启动计算机删除Oracle所在目录

**导出指定用户数据**

expdp scott/tiger dumpfile="20170818.DMP" owner=(scott);
//不同用户之间导入
//导入数据时候注意末尾不要加入分号
//否则zj账号就会变成zj;
impdp scott/tiger@localhost/orcl dumpfile="20170818.dmp" full=y remap_schema=scott:scott
// full=y 全部

**黑屏导入数据**

imp scott/tiger@localhost/orcl file="D:\20170816.dmp" full=y;
imp scott/tiger@localhost/orcl file="D:\oracle\admin\orcl\dpdump\20170816.dmp" full=y;
imp shenzhen/tiger@localhost/orcl file="D:\oracle\admin\orcl\dpdump\shenzhen.dmp" full=y;

**创建用户删除用户**

drop user itsdba cascade;//删除用户
create user itsdba identified by qwerty;
grant connect,resource to itsdba;
grant dba to itsdba;

**创建表空间、删除表空间**

// 创建
create tablespace ITSOADOCUMENT logging  datafile 'D:\oracle\oradata\orcl\ITSOADOCUMENT.dbf' size 50m  autoextend on  next 50m maxsize 20480m  extent management local; 
// 删除
drop tablespace ITSOADOCUMENT including contents and datafiles;

**找回update或delete过的数据**

select r.FIRST_LOAD_TIME,r.* from v$sqlarea r order by r.FIRST_LOAD_TIME desc ;
 
--查询出有很多操作表的SQL语句，找出你操作过的SQL语句
 update SECURITY_PORTRAIT_REPORTING t
   set t.time = to_date('2017/12/26 12:53:31', 'yyyy-mm-dd hh:mi:ss')
 
--此时。将2018-05-30/18:14:44之前的数据写入新表t_table_recove中
 create table t_table_recove --新表
 as
 select * from SECURITY_PORTRAIT_REPORTING--你操作的那张表 
 as of timestamp to_timestamp('2018-05-30/18:14:44','yyyy-mm-dd hh24:mi:ss');
 
--然后清空原来表（SECURITY_PORTRAIT_REPORTING）中的数据
 delete from SECURITY_PORTRAIT_REPORTING ;
 
--再将新表t_table_recove中的数据写入到原来表（SECURITY_PORTRAIT_REPORTING）中
 insert into SECURITY_PORTRAIT_REPORTING(select * from t_table_recove);
 
--最终数据已恢复2018-05-30/18:14:44之前，即：你操作之前，删除新表t_table_recove即可
 drop table t_table_recove

**with as的使用**

with TT as
 (select distinct h.time, h.title name, h.reference_number content, h.type,
         (to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') -
          to_date(to_char(h.time, 'yyyy-mm-dd'), 'yyyy-mm-dd')) / 365 AS year
    from Security_Portrait_Honorary h where h.id_card = ? order by h.time desc)
select * from TT
 where (type = '国家级奖励' and year < 5)
    or (type = '总公司奖励' and year < 3)
or (type in ('集团公司奖励', '省部级奖励', '段奖励', '其他') and year < 1);

**oracle 字段like多个条件（or关系）REGEXP_LIKE的使用**

select id, name
  from BASE_SECURITY_DEPARTMENT
 where parent_id = '1'
   and REGEXP_LIKE(name, '(客运处|货运处|运输处)$');
>>更多…
and REGEXP_LIKE(字段名, '(匹配串1|匹配串2|...)')--//全模糊匹配
and REGEXP_LIKE(字段名, '^(匹配串1|匹配串2|...)')--//右模糊匹配
and REGEXP_LIKE(字段名, '(匹配串1|匹配串2|...)$')--//左模糊匹配

包括：REGEXP_LIKE
不包括：NOT REGEXP_LIKE

**查询去年、明年同期**

SELECT ADD_MONTHS(sysdate, -12) as 去年的今天,
       ADD_MONTHS(sysdate, 12) as 明年的今天
  FROM dual

**使用instr按指定顺序排序**

select sei.office_name, nvl(ROUND(AVG(sei.COMP_CHECK), 2), 0) avg_score
  from SECURITY_EMPLOYEE_INFO sei
 WHERE sei.office_name in ('运输处', '货运处', '客运处', '机务处', '工务处', '电务处', '车辆处', '供电处')
   and (SEI.ON_GUARD IS NULL OR SEI.ON_GUARD = 0)
 group by sei.office_name
 --order by avg_score desc
 order by instr('运输处, 货运处, 客运处, 机务处, 工务处, 电务处, 车辆处, 供电处', sei.office_name);

**JAVA后台通过遍历List获取参数补全SQL语句（根据具体的条件）**

List<Object> params = new ArrayList<>();
String selectdata="";
 
if (StringUtils.isNotEmpty(zhiwei)) {}
    selectdata += " AND SEI.POSITION = ? ";
    params.add(zhiwei);
}
//选择处室跳转到站段页面时,方法一：
if (officeid != null) {
    String officeName = departmentManager.getDeptNameById(officeid);
    selectdata += "AND SEI.OFFICE_NAME = ? ";
    params.add(officeName);
}
String sqlA = "select * from table" +  selectData;
SQLQuery sqA = dao.getSession().createSQLQuery(sqlA);
sqA.setParameter(0, findDepotName(depotid));
for (int i = 0; i < params.size(); ++i) {
    sqA.setParameter(i+1, params.get(i));
}

**SQL语句中IF ELSE的使用**
 

SELECT IF(c19='1','已评价','未评价')c19 FROM A05;