oracle Sql-2解锁and基础

[u][color=blue]pl/sql执行脚本指令[/color]：[/u]

@c:\sql.sql

-----------------------------------------------------------
[color=blue][u]解锁：[/u][/color]
select t2.username,t2.sid,t2.serial#,t2.logon_time
from
v$locked_object t1,
v$session t2
where t1.session_id=t2.sid
order by t2.logon_time;

alter system kill session 'sid,serial#';

eg:alter system kill session '146,21177';|
------------------------------------------------------------
建立表相关
[b]创建表[/b]：
CREATE TABLE students
(
student_id VARCHAR2(10) NOT NULL,
student_name VARCHAR2(30) NOT NULL,
college_major VARCHAR2(15) NOT NULL,
status VARCHAR2(20) NOT NULL,
state VARCHAR2(2),
license_no VARCHAR2(30)
);


[b]主键[/b]：
alter table students add ADD CONSTRAINT pk_students PRIMARY KEY (student_id)
USING INDEX TABLESPACE student_index;//创建主键索引 默认创建主键时oracle建立一索引


[b]创建外键[/b]：
ALTER TABLE students ADD CONSTRAINT fk_students_state
FOREIGN KEY (state) REFERENCES state_lookup (state);


[b]表的后面增加一个字段[/b]
ALTER TABLE表名 ADD 字段名 字段名描述;
alter table PUB_INFO_FILES add info_inst_id number(12);
增减多个字段：

修改表中的某列名字
Alter table 表名 rename column 字段1 to 字段2


删除表中的某个字段：
alter table xxx drop comumn comumn_name；


创建唯一：
ALTER TABLE students
ADD CONSTRAINT uk_students_license
UNIQUE (state, license_no)
USING INDEX TABLESPACE student_index;

创建check:
ALTER TABLE students
ADD CONSTRAINT ck_students_st_lic
CHECK ((state IS NULL AND license_no IS NULL) OR
(state IS NOT NULL AND license_no is NOT NULL));

delete truncate
delete:数据库操作语言(dml)，
truncate:是数据库定义语言(ddl)

[b]修改表字段长度[/b]
alter table pub_query modify MSQl varchar(500);

[b]修改表里字段类型[/b]
ALTER TABLE表名 MODIFY 字段名 字段名描述;

改变表的名称
RENAME 表名1 TO 表名2;

[b][color=blue]创建索引：
http://www.cnblogs.com/huhu456/archive/2009/05/13/1455579.html[/color][/b]
create index index_id on emp(orderID,employeeID)
如果这一列存储的元素彼此都不相同的话，创建索引可以加快查找速度
索引类型以及特点使用：
[color=gray]http://tech.it168.com/db/o/2006-08-18/200608181553820.shtml[/color]
1.B*Tree索引
B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数据列（[color=blue]指该列有很多不同的值[/color]）时提供了最好的性能。
B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址。
create index ..on tabname(字段名)
Create index indexname on tablename(columnname[columnname...])


2.反向索引
反向索引是B*Tree索引的一个分支.Oracle推出它的主要目的就是为了降低在并行服务器（Oracle Parallel Server）环境下索引叶块的争用。[color=blue]当B*Tree索引中有一列是由递增的序列号产生的话[/color]，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。
在范围搜索中其不能被使用，例如，where column>value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。
Create index indexname on tablename(columnname[columnname...]) reverse

3.降序索引
是B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序，[color=blue]在某些场合下降序索引将会起作用[/color]。
另外一个需要注意的地方是要设置init.ora里面的compatible参数为8.1.0或以上，否则创建时desc关键字将被忽略。
create index test.ind_desc on tabname(a desc);
Create index indexname on tablename(columnname DESC[columnname...])


4.位图索引
位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低基数列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。
Create BITMAP index indexname on tablename(columnname[columnname...])

5.函数索引
B*Tree的衍生产物，应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。
Create index indexname on tablename(functionname(columnname))

总之，

（1）常规索引，多用于oltp系统，[color=blue]快速定位行，应建立于高基数列[/color]（即列的唯一值除以行数为一个很大的值，存在很少的相同值）。
（2）反向索引。
[color=blue]当B*Tree索引中有一列是由递增的序列号产生的话[/color]
B*Tree的衍生产物，应用于特殊场合，在ops环境加序列增加的列上建立，不适合做区域扫描。
（3）降序索引。
B*Tree的衍生产物，[color=blue]应用于有降序排列的搜索语句中[/color]，索引中储存了降序排列的索引码，提供了快速的降序搜索。
（4）位图索引。
位图方式管理的索引，适用于OLAP（在线分析）和DSS（决策处理）系统，[color=blue]应建立于低基数列[/color]，适合集中读取，不适合插入和修改，提供比B*Tree索引更节省的空间。
（5）函数索引。
B*Tree的衍生产物，[color=blue]应用于查询语句条件列上包含函数的情况[/color]，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。

[color=blue][b]创建视图：
http://blog.csdn.net/qfljg/archive/2009/07/09/4333592.aspx[/b][/color]
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name

[(alias[, alias]...)]

AS subquery(sql语句)
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]


视图的定义原则：
1.视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询；
2.在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用
ORDER BY 子句；
3.如果没有为CHECK OPTION约束命名，系统会自动为之命名，形式为SYS_Cn;
4.OR REPLACE选项可以不删除原视图便可更改其定义并重建，或重新授予对象
权限。


hql不等于符号：<> , is null、is not null
sql:
!= 不等于（非 SQL-92 标准）
<> 不等于 以后用它
is null is not null

--------------------------------------------------
[color=gray]
http://www.itpub.net/viewthread.php?tid=527157[/color]

ORACLE常用的分组函数有以下五个：

Ø COUNT
Ø AVG
Ø SUM
Ø MAX
Ø MIN

COUNT(*)返回表中所有的行包括空行和重复的行。
COUNT(mgr) 返回表中所有mgr为非空的行。

ØAVG（[DISTINCT|ALL]表达式）： 该函数返回表达式的平均值。
ØSUM（[DISTINCT|ALL]表达式）：该函数返回表达式的总合。

ØMAX（[DISTINCT|ALL]表达式）：该函数返回表达式的最大值。
ØMIN（[DISTINCT|ALL]表达式）：该函数返回表达式的最值。

[color=blue]AVG和SUM函数只能操作数字型数据，
MIN和MAX函数可用于数字型，字符型数据，日期型数据[/color]


[b][color=blue]在oracle中''和null都被看成空值，
create table test(c1 varchar2,c2 number,c3 date);
下面两个插入数据的语句是一样的
insert into test values('','','');
insert into test values(null,null,null);[/color][/b]

《从实践中学习Oracle/SQL》，清华大学出版社出版

---------------------------------------------------

[table]
|[u][color=darkblue]数据字典表：[/color][/u]
|
|
|
|tab,tabs,all_tables:
|
|
|
|所有用户都拥有若干个表
|
|
|
|用命令select table_name from user_tables 可以查看当前连接的用户的所有表的名称。
|
|
|
|用命令select table_name from all_tables 可以查看所有用户的所有表的名称。（如果权限允许的话）
|
|
|
|
|
|dba_tables 所有的tables
|
|all_tables 当前用户所能看到的所有的tables
|
|user_tables 当前用户的有的tables
|
|tab tabs好象是user_tables的synonym
|
|
|
|SQL> select t.* from tab t where t.tname like '%ARE%';
|
|TNAME TABTYPE CLUSTERID
|
|------------------------------ ------- ----------
|
|AREA TABLE
|
|
|
|SQL> select t.* from tabs t where t.table_name like '%ARE%';
|
|more..
|
|
|
|SQL> select * from all_tables where owner='PIM' and table_name like '%ARE%';|
[/table]