Oracle相关

1.如何创建新用户

在CMD中输入：sqlplus /nolog

　　SQL> conn / as sysdba;

　　SQL>create user 新用户名 identified by 密码

　　SQL> grant dba to 新用户名;

　　SQL> conn 新用户名/密码

注意：当遇到conn / as sysdba登陆数据库报错 提示权限不足时，解决步骤：

    首先，去检查sqlnet.ora 文件中SQLNET.AUTHENTICATION_SERVICES 项的配置信息。在windows 下，SQLNET.AUTHENTICATION_SERVICES 必须设置为NTS 或者ALL 才能使用OS 认证；不设置或者设置为其他任何值都不能使用OS 认证。sqlnet.ora在WINDOWS下位于%ORACLE_HOME%NETWORKADMIN目录。

   其次，右键点击“我的电脑”选择“管理”，在“计算机管理”界面找到“本地用户和组”-“组”。并在相应的页面中找到名字为：ora_dba的组。点击这个ora_dba的组，在弹出页面中查看下当前登录用户是否在这个组内，如果没在，那么点击添加吧。比如我的当前登录用户为“xudso”，如下图红框所示。

不在其中，所以添加完后就可以了！添加完如下图所示。

注意：在输入对象名称后记得点击右侧的“检测名称”按钮。

 

2. Oracle的列转行函数：LISTAGG()

例如：

with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all  
  select 'China' nation ,'Shanghai' city from dual union all  
  select 'China' nation ,'Beijing' city from dual union all  
  select 'USA' nation ,'New York' city from dual union all  
  select 'USA' nation ,'Bostom' city from dual union all  
  select 'Japan' nation ,'Tokyo' city from dual   
)  
select nation,listagg(city,',') within GROUP (order by city)  
from temp  
group by nation

结果为：

3. oracle select limit的方法

选择表中的某一行记录：(理解：rownum是oracle系统顺序分配为从查询返回的行的编号) select * from (select rownum a,t.* from testtab t) where a=2; 不能为： select * from (select rownum,t.* from testtab t) where rownum=2;或 select * from testtab where rownum=2; 返回多行记录： select * from testtab where rownum<=10;返回某段记录：(如取记录表中4-10行) select * from (select rownum no,testtab.* from testtab where rownum<=10) where no>=4; 返回有条件且经过排序的某段记录： select rownum num1,tt.* from (select rownum num,t.* from (select EcodeInfo.* from EcodeInfo where a=1 order by ecode desc) t) tt where num>19 and rownum<20> 以为oracle是先提取记录再排序的，而oracle的rownum是在提取记录就已经生成，它先于排序操作，所以必须使用子查询先排序。 不能为： select * from tsettab where rownum>10; 返回最后一行记录: select * from (select rownum a,t.* from testtab t) where a=(select count(*) from testtab); 返回最后N行记录： select * from (select rownum a,t.* from testtab t) where a=(select count(*)-N from testtab); ---------------- select * from adminrole where rownum<=4 minus select * from adminrole where rownum<2> select * from (select rownum row_id ,b.* from (select a.* from sorttable a order by sortid)b) where row_id between 5 and 9;

4. oracle 显示前两条记录和最后两条记录，中间用省略号

SELECT * FROM
(select to_char(deptno),DNAME,LOC from (select rownum a,t.* from DEPT t) where a<=2
UNION ALL
 select '...','...','...' from dual
UNION ALL
select to_char(DEPTNO),DNAME,LOC from (select rownum a,t.* from DEPT t) where a>(select count(*)-2 from DEPT)
) 
WHERE (select count(*) from DEPT)>4
UNION ALL
select to_char(DEPTNO),DNAME,LOC from  DEPT
WHERE (select count(*) from DEPT)<=4

5.REPLACE(WM_CONCAT(t.组织),',','，')将英文逗号改成中文逗号

to_char(nowdate.出车率*100 ,'fm9999990.00') ||'%'     百分号后面保留两位小数

6.取某一条记录

//取emp表第三行数据select * from (select rownum a, ename from emp) where a = 3;

7.oracle-索引原理

(1)创建索引的目的：

索引中只有一列，io小，所以较快；索引中此列是排序的，二叉查找，提高查询速度。

当访问的数据块少于表中20%的数据时，建议使用索引。

（2）使用索引原因分析

索引是对数据库表中一列或多列的值进行排序的一种结构。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时，若要对表进行查询有2中搜索方式：

第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；

第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

注：当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

（3）什么情况下适合建立索引：

1)表的主键、外键必须有索引。

2)经常与其它表进行连接的表，在连接字段上应该建立索引。

3)经常出现在WHERE子句中的字段，特别是大表的字段，应该建立索引。

4)索引应该建在选择性高的字段上。

5)索引应该建在小字段上，对于大的文本字段甚至超长字段，不适合建索引。

6)复合索引的建立需要进行仔细分析。

7)正确选择复合索引中的主列字段，一般是选择性较好的字段。

8)如果单字段查询很少甚至没有，那么可以建立复合索引；否则考虑单字段索引。

9)如果复合索引中包含的字段经常单独出现在WHERE子句中，那么分解为多个单字段索引。

10)如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段。

11)如果既有单字段索引，又有这几个字段上的复合索引，那么一般可以删除复合索引。

12)频繁进行DML操作(insert、update、delete)的表，不要建立太多的索引。

13)删除无用的索引，避免对执行计划造成负面影响。

（4）索引创建的策略：

1)导入数据后再创建索引。

2)不需要为很小的表创建索引。

3)对于取值范围很小的字段（比如性别字段）应当建立位图索引。

4)限制表中的索引的数目。

5)为索引设置合适的PCTFREE值。

6)存储索引的表空间最好单独设定。

(5)索引的副作用：

一是增加了数据库的存储空间； 

二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

（6）使用索引时需要注意：

1)条件中使用不等于操作（<>,!=),将不会走索引，而是走全表扫描。

2)条件中使用IS NULL 或者IS NOT NULL，也不会走索引，走全表扫描。字段可以使用缺省值。

3)条件中对字段使用函数，也不会走索引，除非是函数索引。

4)比较不匹配的数据类型时，oracle可以帮我们自动做数据类型的转换，但是oracle还是建议我们最好使用转换函数（to_char()、to_date()、to_number()等）做显示的转换。

（7）常用到的一些索引操作：

1)查询一张表里面索引

1	select * from user_indexes where table_name=upper('tableName');

2)查询被索引字段

1	select * from user_ind_columns where index_name=('indexName');

3)给某一字段创建索引

1	create index index_name on table_name(col_name);

4)查看用户下的索引

1 2 3 4 5 6 7

select  * from user_indexes-          -----查看当前用户下的所有索引 select  * from user_indexes where table_name='A';      -----查看当前用户下表A的索引 drop index index_name去掉索引 select index_name,index_type,status,blevel from user_indexes where table_name = '？';           -----查看某一个表的所有索引 select table_name, index_name, column_name, column_position from user_ind_columns where  table_name='？';    ----查看索引的构成

5)建索引

Create unique clustered index 索引名on 表名(字段1)  --单索引

Create index 索引名 on 表名(字段1,字段2)  -------复合索引 

（8）B 树索引（二叉树索引，默认情况下，我们建的索引都是此种类型）

 一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。可以用下图一来描述B树索引的结构。其中，B表示分支节点，而L表示叶子节点。

    对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每 个索引条目（也可以叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所 链接的索引块的地址，该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到，对于 根节点块来说，包含三条记录，分别为（0 B1）、（500 B2）、（1000 B3），它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。

    对于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以 叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所 对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID占用6个字节；如果索引是创建在分区表上的全局索引的话，则该ROWID占用10个字节。

 　　sql:

　　创建索引
　　create index idx_emp1_ename on emp1(ename);
　　创建唯一索引
　　create unique index idx_uq_emp1_empno on emp1(empno) tablespace mypl;

 当用户创建索引时，Oracle 取得所有被索引列的数据并进行排序，之后将排序后索引值和与此值相对应的 rowid 按照从下到上的顺序加载到索引中。例如，以下语句：

CREATE INDEX employees_last_name ON employees(last_name); 

Oracle 先将 employees 表按 last_name 列排序，再将排序后的 列及相应的 rowid 按从下到上的顺序加载到索引中。使用此索引时，Oracle 可以快速地搜索已排序的 last_name 值，并使用相应的 rowid 去定位包含用户所查找的 last_name 值的数据行。 

接下来介绍一个索引查询的流程：

从上往下，第一层为根节点，第二层为分支节点，第三层为叶子节点（包含了列值和rowid）。比如我们的条件为where=29，(补充说明如果被索引的列存储的是字符数据，那么索引值为这些字符数据在当前数据库字符集中的二进制值)就从跟节点开始查询，29在0-500中，指向分支节点最左边第一个分支节点块（也就是B1），就B1中去找，发现29在0-200中，指向叶子节点的L1，于是在L1中找到29的值和响应的rowid。如果只查找索引列的值，就不用根据rowid去表中查找了，如果还要查找值29这行的其他列的值就得根据rowid去表里查查询（这个过程叫做回表查询）。叶子节点还有个双向链表（如图）。在通过索引进行范围扫描时会起作用，比如要查找值29-700，如果当查找到值29的时候，不就会再从跟节点开始查找其他的值，而是根据本叶子节点链表的指向去查找其他的值。

其他索引可参考：oracle索引原理

8. Oracle (内连接)与(外连接)区别

表stu
id name
1, Jack
2, Tom
3, Kity
4, nono


表exam
id grade
1, 56
2, 76
11, 89

内连接(显示两表匹配的id)

select stu.id, exam.id, stu.name, exam.grade from stu inner join exam on stu.id = exam.id
1 1 Jack 56
2 2 Tom 76

左连接(显示join左边表的所有数据)

select stu.id, exam.id, stu.name, exam.grade from stu left join exam on stu.id = exam.id
1 1 Jack 56
2 2 Tom 76
3 NULL Kity NULL
4 NULL nono NULL

右连接(显示join右边表的所有数据)

select stu.id, exam.id, stu.name, exam.grade from stu right join exam on stu.id = exam.id
1 1 Jack 56
2 2 Tom 76
NULL 11 NULL 89

总结：

•  内连接           只连接匹配的行

    外连接分为左外连接，右外连接和全外连接。

• 左外连接        包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
• 右外连接        包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
• 全外连接        包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。 

9.Oracle 删除数据的几种方法

1）DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
2）TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
TRUNCATE 只能对TABLE；DELETE可以是table和view
3）DROP则删除整个表（结构和数据）。TRUNCATE 和DELETE只删除数据表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。