Oracle中right,Oracle常用 - CurtainRight的个人空间 - OSCHINA - 中文开源技术交流社区

一、常用Sql

1、创建用户

createtablespace whc_tbs datafile'E:\oracle\oradata\whcdb\whc.dbf'size100M;

createuserwhc identifiedbywhcdefaulttablespace whc_tbs;

grantconnect,resource,dbatowhc;

2、删除数据文件(dbf文件)

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

3、Oracle减少null

表中最好别有null记录，这会引起全文扫描，建议改成0代替。

4、查询字段类型

select * from cols where  table_name='TBL_SYS_TREE_CODE';//查询字段信息

select   *   from   user_col_comments;//查询字段备注

select   *   from   user_tab_comments;//查询表备注

5、count(*)会查询出所有记录(包括null);select(字段)也会查询出所有记录，但是不包括null

6、排序后，查询出一条记录

select CODE_ID from (

select CODE_ID, rownum from TBL_SYS_TREE_CODE  where PARENT_ID is null  order by last_modify_time desc)

where rownum=1

7、系统表

--USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息

--ALL_*：有关用户可以访问的对象的信息，即用户自己创建的对象的信息加上其他用户创建的对象但该用户有权访问的信息

--DBA_*：有关整个数据库中对象的信息

--查询用户信息

select * from dba_users;//查询所有用户

select * from all_users;//查询所有用户，得到的信息比较少

select * from user_users;//用户自己的信息

select * from dba_sys_privs;//查询权限

select * from all_sys_privs;//此表不存在

select * from user_sys_privs;//查询用户的权限

select * from dba_tab_privs where owner = 'holly';

select * from all_all_tables;//查询所有的表

8、经常查询某一天，或者某一个月的记录，但是不知道Oracle内部的时间转换，有种简单的方式

select * from user where regTime like '2015-12-12%' //查询12月12日的所有记录

select * from user where regTime like '2015-12%' //查询12月份的所有记录

9、设置表、字段注释

comment on table table_name is 'comments_on_tab_information';//设置表备注

comment on column table.column_name is 'comments_on_col_information';//设置字段备注

10、group by/order by/distinct

去重：group by，distinct

group by 查询的结果中必须要有聚合函数，而且分组的字段必须出现在查询结果中‍

排序：order by

group by 在order by 前面，而且order by 中的数据必须在group by中出现。

二、Oracle更一个字段

更改某个字段的类型

有一个表名为tb，字段段名为name，数据类型nchar(20)。

1、假设字段数据为空，则不管改为什么字段类型，可以直接执行：

alter table tb modify (name nvarchar2(20));

2、假设字段有数据，则改为nvarchar2(20)可以直接执行：

alter table tb modify (name nvarchar2(20));

3、假设字段有数据，则改为varchar2(40)执行时会弹出：“ORA-01439:要更改数据类型,则要修改的列必须为空”，这时要用下面方法来解决这个问题：

/*修改原字段名name为name_tmp*/

alter table tb rename column name to name_tmp;

/*增加一个和原字段名同名的字段name*/

alter table tb add name varchar2(40);

/*将原字段name_tmp数据更新到增加的字段name*/

update tb set name=trim(name_tmp);

/*更新完，删除原字段name_tmp*/

alter table tb drop column name_tmp;

总结：

1、当字段没有数据或者要修改的新类型和原类型兼容时，可以直接modify修改。

2、当字段有数据并用要修改的新类型和原类型不兼容时，要间接新建字段来转移。

三、导入导出

1)导出：Tools-Export Tables

1、导出dmp格式

2、导出sql格式

3、导出pie格式

2)导入：Tools-Import Tables

3)命令导入导出：

1、导入

imp username/password@ip:port/Oracle标识grants=y fromuser=源数据用户 touser=备份到哪个用户file=D:\shop.dmp(源文件) log=D:\shop.log(源文件日志)

2、导出

exp username/password@ip:port/Oracle标识 grants=y owner=源数据用户 file=D:\shop.dmp(源文件) log=D:\shop.log(源文件日志)

4)借助Navicat导入导出

1、导入

选中当前用户-》运行sql文件

注意：导入的时候，需要注意修改用户名

2、导出

选中当前用户-》转储sql文件-》数据和结构(结构)。(注意：也可以单表导出)

3、安装Navicat(万能辅助数据库)

工具-》选项(这里的OCI文件必须配套)

四、Oracle添加一个连接标识

1、Oracle在本机的服务。也就是在安装Oracle服务过程中的唯一标识

下图XE就是本机的唯一标识。

OracleServiceXE、OracleXETNSListener是Oracle必须的两个服务

2、tnsnames.ora文件

如果想要为本机的服务添加一个连接

Holly =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))//ip、端口

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME =XE)//Oracle服务的唯一标识

)

)

3、连接数据源

3.1、java

jdbc.url=jdbc\:oracle\:thin\:@172.16.52.1\:1521\:orcl

3.2、pl/sql

172.16.52.1:1521/orcl

3.3、Navicat

连接名自定义：Curtain

IP：172.16.52.1

Service Name：orcl