SQL杂记

发表于 更新于 分类于 ， 阅读次数： 评论数：

一些Oracle数据库的杂乱知识点以及NC产品数据安装步骤的记录

1
2
3
4
5
6
7
8
-- 查询所有表
SELECT * FROM TAB;

-- 链接字段
SELECT ENAME || '的基本工资是' || SAL FROM EMP;

-- 去掉记录中的重复值
SELECT DISTINCT FROM EMP;

oracle的函数部分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*NVL()函数可以将NULL转换未其它值返回*/
SELECT NVL(COMM,0) FROM EMP;

/*DECODE()替换满足条件的值*/
SELECT LOC,DECODE(LOC,'NEW YORK','纽约','CHICAGO','芝加哥','BOSTON','波斯顿','DALLAS','不知道是哪儿') FROM DEPT;

/*ROUND()对数字四舍五入*/
SELECT ROUND(57.4,-1) FROM DUAL;  -- 重点可以负数

/*TRUNC()舍去多余的小数*/
SELECT TRUNC(3.1415926,3) FROM DUAL;

/*MOD()取模*/
SELECT MOD(2.718,3) FROM DUAL;

搭建环境

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/*创建表空间*/
CREATE TABLESPACE NNC_DATA01 DATAFILE 'D:\ORA\oradata\orcl\nnc_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
CREATE TABLESPACE NNC_DATA02 DATAFILE 'D:\ORA\oradata\orcl\nnc_data02.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
CREATE TABLESPACE NNC_DATA03 DATAFILE 'D:\ORA\oradata\orcl\nnc_data03.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
CREATE TABLESPACE NNC_INDEX01 DATAFILE 'D:\ORA\oradata\orcl\nnc_index01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;
CREATE TABLESPACE NNC_INDEX02 DATAFILE 'D:\ORA\oradata\orcl\nnc_index02.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;
CREATE TABLESPACE NNC_INDEX03 DATAFILE 'D:\ORA\oradata\orcl\nnc_index03.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;
ALTER TABLESPACE NNC_DATA01 ADD DATAFILE 'D:\ORA\oradata\orcl\NNC_DATA04.DBF' SIZE 100M AUTOEXTEND ON NEXT 128M MAXSIZE 20480M;
ALTER TABLESPACE NNC_DATA01 ADD DATAFILE 'D:\ORA\oradata\orcl\NNC_DATA05.DBF' SIZE 100M AUTOEXTEND ON NEXT 128M MAXSIZE 20480M;
ALTER TABLESPACE NNC_DATA03 ADD DATAFILE 'D:\ORA\oradata\orcl\NNC_DATA06.DBF' SIZE 100M AUTOEXTEND ON NEXT 128M MAXSIZE 20480M;
ALTER TABLESPACE NNC_DATA03 ADD DATAFILE 'D:\ORA\oradata\orcl\NNC_DATA07.DBF' SIZE 100M AUTOEXTEND ON NEXT 128M MAXSIZE 20480M;

/*创建用户*/
CREATE USER Ncc IDENTIFIED BY 1 DEFAULT TABLESPACE NNC_DATA01 TEMPORARY TABLESPACE temp;
/*给用户授权*/
GRANT connect,dba,resource to Ncc;

/*创建导入的XX*/
create directory dpdump_dir AS 'F:\work\ZY\zybf';
--XX授权码给用户
Grant read,write on directory dpdump_dir to Ncc;

--退出数据库
exit 

--CMD进行导入
impdp Ncc/1@orcl dumpfile=xy.dmp  logfile=xy.log directory=dpdump_dir remap_schema=xyzq_nc65:Ncc

有时候导入并部顺利，这时候就需要做一些别的操作

1
2
3
4
5
6
7
--删除表空间
DROP TABLESPACE NNC_DATA01  INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE NNC_DATA02  INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE NNC_DATA03  INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE NNC_INDEX01  INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE NNC_INDEX02  INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE NNC_INDEX03  INCLUDING CONTENTS AND DATAFILES;

查询当前字符集

1
select * from V$NLS_PARAMETERS;

修改数据库的字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--Telnet到服务器，执行：
$sqlplus /nolog
SQL>conn / as sysdba
/*若此时数据库服务器已启动，则先执行 SHUTDOWN IMMEDIATE 命令关闭数据库服务器，然后执行以下命令:*/
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;
SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE CHARACTER SET ZHS16GBK;
--ERROR at line 1:ORA-12721: operation cannot execute when other sessions are active  
--若出现上面的错误，使用下面的办法进行修改，使用INTERNAL_USE可以跳过超集的检查：

SQL>ALTER DATABASE CHARACTER SET INTERNAL\_USE ZHS16GBK;  
SQL>SHUTDOWN IMMEDIATE  
SQL>STARTUP

修改用户的默认表空间

1
alter user Ncc default tablespace NNC_DATA01;