1.数据库备份和还原
先进入cmd,然后执行下面的备份和还原语句:
exp U_PURE/U_PURE@192.168.3.80:1521/orcl file=d:\orcl.dmp --备份,U_PURE为远程数据库的用户名密码,orcl为数据库民称 file后面为备份文件存储路劲
imp test1/test1@ORCL file=d:\orcl.dmp fromuser=U_PURE touser=test1 log=d:\imglog.log --还原,test1为本地登入用户名密码,file为备份文件存储路劲
2.创建用户,表空间,授权等基本操作:
-----------------创建表空间-----------------
CREATE TABLESPACE TBS_ZWXXPT_01
DATAFILE 'D:\tablespaces\TBS_ZWXXPT_01.DBF'
SIZE 100M
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED;
------------------创建用户--------------------
create user U_ZWXXPT identified by U_ZWXXPT
default tablespace TBS_ZWXXPT_01;
------------------授权--------------------
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO U_ZWXXPT;
3.修改数据库字符编码:
先cmd中输入sqlplus / as sysdba连接数据库,然后依次输入一下命令修改字符编码:
shutdown immediate; (把database停了)
startup mount; (把database重开去可更改情況)
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;
alter database character set internal_use utf8;(这里是修改成UTF-8的格式编码,ALTER DATABASE character set INTERNAL_USE ZHS16GBK;这是修改成GBK的格式编码)
shutdown immediate;
startup; (重开正常oracle)
4.sql操作
INNER JOIN,LEFT JOIN,RIGHT JOIN用法:
INNER JOIN:SELECT * FROM CHINA_PERSION C INNERJOIN USA_PERSION U ON C.XH=U.XH 会把条件相符的两张表数据都加载出来;
LEFT JOIN:SELECT * FROM CHINA_PERSION C LEFT JOIN USA_PERSION U ON C.XH=U.XH 会把左边主表数据都加载出来,然后又表符合ON条件的又表也会加载出来;
RIGHT JOIN:SELECT * FROM CHINA_PERSION C RIGHTJOIN USA_PERSION U ON C.XH=U.XH 会把右边主表数据都加载出来,然后又表符合ON条件的左表数据也会加载出来;
UNION,UNION ALL用法:
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同:
SELECT NAME AS AA FROM CHINA_PERSION UNION SELECT XH AS AA FROM USA_PERSION