oracle Sql-2解锁and基础

ytocogupiq

pl/sql执行脚本指令：

@c:\sql.sql

-----------------------------------------------------------
解锁：
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';|
------------------------------------------------------------
建立表相关
创建表：
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)
);


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


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


表的后面增加一个字段
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)

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

修改表里字段类型
ALTER TABLE表名 MODIFY  字段名 字段名描述;

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

创建索引：
http://www.cnblogs.com/huhu456/archive/2009/05/13/1455579.html
create index index_id on  emp(orderID,employeeID)
如果这一列存储的元素彼此都不相同的话，创建索引可以加快查找速度
索引类型以及特点使用：
http://tech.it168.com/db/o/2006-08-18/200608181553820.shtml
1.B*Tree索引
B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数据列（指该列有很多不同的值）时提供了最好的性能。
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）环境下索引叶块的争用。当B*Tree索引中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。
在范围搜索中其不能被使用，例如，where column>value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。
Create index indexname on tablename(columnname[columnname...]) reverse

3.降序索引
是B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序，在某些场合下降序索引将会起作用。
另外一个需要注意的地方是要设置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系统，快速定位行，应建立于高基数列（即列的唯一值除以行数为一个很大的值，存在很少的相同值）。
    （2）反向索引。
    当B*Tree索引中有一列是由递增的序列号产生的话
    B*Tree的衍生产物，应用于特殊场合，在ops环境加序列增加的列上建立，不适合做区域扫描。
    （3）降序索引。
    B*Tree的衍生产物，应用于有降序排列的搜索语句中，索引中储存了降序排列的索引码，提供了快速的降序搜索。
    （4）位图索引。
    位图方式管理的索引，适用于OLAP（在线分析）和DSS（决策处理）系统，应建立于低基数列，适合集中读取，不适合插入和修改，提供比B*Tree索引更节省的空间。
    （5）函数索引。
    B*Tree的衍生产物，应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。

创建视图：
http://blog.csdn.net/qfljg/archive/2009/07/09/4333592.aspx
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

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

http://www.itpub.net/viewthread.php?tid=527157

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

Ø        COUNT
Ø        AVG
Ø        SUM
Ø        MAX
Ø        MIN

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

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

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

AVG和SUM函数只能操作数字型数据，
MIN和MAX函数可用于数字型，字符型数据，日期型数据


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

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

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

数据字典表：

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%';