Oracle基础二

数据表基本操作

 

数据库的数据逻辑上是存储在表空间中，物理上是存储在数据文件中。

创建数据表

CREATE TABLE TABLE1(ID NUMBER(3) NOT NULL,NAME VARCHAR2(20) NULL);

或

CREATE TABLE TABLE1 AS SELECT ID，NAME FROM TABLE2；

注意：如果查询涉及到Long数据类型，那么CREATE TABLE ...AS SELECT 不会工作。

 

修改数据表

增加列

ALTER TABLE TABLE1 ADD createDate DATE；

修改列

ALTER TABLE TABLE1 MODIFY NAME VARCHAR(30); 

注意：大部分时候列的值为空才能修改。

删除数据列

ALTER TABLE TABLE1 DROP COLUMN NAME ;

 

 

更改表名

RENAME oldname TO newname;

 

 

删除表

DROP TABLE  TABLE1；

 

清空表数据

TRUNCATE TABLE  TABLE1；

 

注意区别

区别
1。delete from后面可以写条件，truncate不可以
2。delete from记录是一条条删的，所删除的每行记录都会进日志，而truncate一次性删掉整个页，因此日至里面只记录页释放，简言之，delete from更新日志，truncate基本不，所用的事务日志空间较少
3。delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页
4。当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。
5。如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变，而truncate后，种子会恢复初始
6。truncate不会触发delete的触发器，因为truncate操作不记录各个行删除

 

 

约束

非空约束 NO TNULL（简单不用演示）

唯一 UNIQUE

column_name data_type constraint 约束名 unique//单列数据

constraint 约束名(多列) unique using index tablespace(table_name) storage(stored clause)

添加约束

alter table 表名 add constraint 约束名 unique(列名) using index tablespace 表空间名

 

主键 PRIMARY

列名后面直接加上主键约束

itemid varchar2(20) constraint pk_items primary key

如果主键约束保护多个列，必须作为一个表约束创建

constraint pk_items primary key(itemid,itemname)  using index tablespace(table_name) storage(stored clause)

禁用或删除

alter table items drop primary key;

alter table items disable primary key

 

外键 FOREIGN KEY

alter table invency

add constraint fk_invency_items

foreign key(itemid) references items(itemid);

 

条件 CHECK

 条件列约束不保护log数据类型和数据列和对象、嵌套表、varry、ref等

constraint chk_status check(status in('正常','出差','休假'));

 

约束的维护

禁用约束 alter table 表名 disable constraint 约束名

启用约束 alter table 表名 enable constraint 约束名

删除约束 alter table 表名 drop constraint 约束名 

序列的创建

create sequence sequence1

increment by 1 

start with 1

maxvalue 10000

minvalue 1

nocycle

cache 20或nocache

noorder //不指定排序

；

sequence1.currval

sequence1.nextval

drop sequence sequence1；

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

索引

如果没有建立索引的话，那么查询该表的任何记录都会通过顺序地逐行扫描得到，这会导致大量的磁盘i/o，从而会大大降低数据库系统的效率，索引通常用于提高查询效率。当然过多的索引也会使更新、插入、删除操作花费更多的时间。

B-tree索引（如唯一索引、主键索引）也就是B-树索引。基本上这些索引存储的是所在的列值以及用来查找自身行的指向实际数据表的指针。

注意：如果索引是创建在多个列上的话，那么第一列非常重要。第一列会单独扫描。后面列不会。

函数索引（基于函数建立的）

反转键索引（不能对位图索引和索引组织表进行反转键索引）

位图索引（为相异值很少的列创建的），一般而言，只有当对表中值相宜度较小的多个不同的列都使用位图索引，这样位图索引才会有用。

位图连接索引

压缩索引

降序索引（可以显著优化order by x,y,z desc子句查询）

分区索引

其他：索引组织表、簇索引、域索引、隐藏索引、虚拟索引等

创建索引

创建不唯一索引

create index idx_itemname on item(name) tablespace 表空间名;

创建唯一索引

create unique index idx_itemid on item(id) tablespace 表空间名;

创建位图索引

create bitmap index idx_itemsdeleted on item(isdeleted) tablespace 表空间名;

创建反转键索引

create unique index reidx_delivey on delivey(did,date) tablespace 表空间名 reverse;

创建函数索引（既可以是普通的B-树索引，也可以是位图索引）

create index idx_sub on item(substr(itemid,1,4))tablespace 表空间名；

注意：

导入数据后再创建索引

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

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

限制表中的索引数目

为索引设置合适的pctfree值

存储索引的表空间最好单独设置

使用索引

全表扫描（Full table scan）：就是顺序地访问表中的每条记录。oracle采用一次读入多个数据块的方式优化全表扫描。

索引扫描（index scan）：可以采用基于rowid的方式访问以提高访问表的效率。rowid包含了表中记录的物理位置。oracle采用索引实现了数据和存放数据的物理位置rowid之间的联系。通常索引提供了快速访问rowid的方法，因此基于索引列的查询就可以得到性能上的提高。

索引唯一扫描（index unique scan）

索引范围扫描(index range scan)使用于2种情况：范围的索引、非唯一性的索引

注意:全表扫描需要读入的数据块数等于该表的实际数据块数

oracle评价使用索引的代价时有2个重要的数据

CF：每读入一个索引块要对应读入多少个数据块

FF：sql语句所选择的结果集占总的数据量的百分比

一般的估算公式是：FF*(CF+索引块个数)

索引不是越多越好

很多时候，单列索引不如复合索引

用于多表连接的字段，加上索引列会很有用

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

Meger更新

meger into t1 using t on(条件)

when matched then ××××

when not matched then ××××

 oracle常用函数和命令

  1、set linesize 100;  设置长度
 2、set pagesize 30;   设置每页显示数目
 3、em a.sql  打开记事本 
 4、@ a 执行文件a中的代码，可指定文件的路径 @d:a.txt
 5、conn 用户名/密码  根据用户名和密码连接数据库 如果连接超级管理员(sys) 则应加上as sysdba;
 6、show user; 显示当前连接的用户
 7、select * from tab;  得到当前用户下的所有表
 8、desc temp; 查看表结构
 9、/  继续执行上一个查询语句
    clear scr;  清屏

字符函数
10、select upper('coolszy') from dual; 将小写字母转换成大写，dual 为一虚表
11、select lower('KUKA') from dual; 将大写字母转换成小写
12、select initcap('kuka') from dual; 将首字母大写
13、select concat('Hello',' world') from dual; 连接字符串，但没有||好用select concat('Hello','world') from dual;
14、select substr('hello',1,3) from dual; 截取字符串
15、select length('hello') from dual; 求字符串长度
16、select replace('hello','l','x') from dual; 替换字符串
17、select substr('hello',-3,3) from dual; 截取后三位

补充：trim、lpad、rpad、instr

数值函数
18、select round(789.536) from dual; 四舍五入，舍去小数
19、select round(789.536,2) from dual; 保留两位小数
20、select round(789.536,-1) from dual; 对整数进行四舍五入
21、select trunc(789.536) from dual; 舍去小数，但不进位
22、select trunc(789.536,2) from dual;
23、select trunc(789.536,-2) from dual;
24、select mod(10,3) from dual; 返回10%3的结果

补充：abs、ceil、floor、power、rand、sqrt、

日期函数
25、select sysdate from dual; 返回当前日期
26、select months_between(sysdate,'16-6月 -08') from dual; 返回之间的月数
27、select add_months(sysdate,4) from dual; 在日期上加上月数
28、select next_day(sysdate,'星期一') from dual; 求下一个星期一
29、select last_day(sysdate) from dual; 求本月的最后一天

补充、trunc、round

其中sysdate的格式

CC:世纪

WW：为一年中的周

W：为一个月中的周

D：为星期几

DDD：为一年中的第几天

YEAR：为年

MONTH：为月

DAY：为日

 

转换函数
30、select to_char(sysdate,'yyyy') year,to_char(sysdate,'mm'),to_char(sysdate,'dd') from dual;
31、select to_char(sysdate,'yyyy-mm-dd') from dual;
32、select to_char(sysdate,'fmyyyy-mm-dd') from dual; 取消月 日 前面的0
33、select to_char('20394','99,999') from dual; 分割钱 9表示格式
34、select to_char('2034','L99,999') from dual; 加上钱币符号
35、select to_number('123')*to_number('2') from dual;
36、select to_date('1988-07-04','yyyy-mm-dd') from dual;

其中日期格式

YYYY:完整的年份号码

YEAR:当前的年份拼出来

RR：年份中阿拉伯数字的最后2位

MM：2位数的月份

MONTH:当前的月份拼出来

MON：月份的前3个字母

DY：用3个字母来表示当前的星期数

DAY：当前星期数的全拼

DD：数字的月份日期值

HH24：二十四进制的小时数

MM：分钟数

SS：秒数

数字的显示格式

9 ：代表一个数字

L：强制显示一个当地的货币符号

0：强迫0显示

.：显示一个小数点

$：显示美元符号

，：显示一个千位分隔符号

 

to_number函数

to_date函数

decode函数

decode（exp,sl,r1,s2,r2....sn,rn）可以理解成增强型的if else

greatest(n1,n2,n3,...n)返回序列中最大的值

least(n1,n2,n3,...n)返回序列中最小的值

nullif(c1,c2)如果两个指定的表达式相等，则返回空值。

nvl(c1,c2)如果c1为NULL，则NVL函数返回c2的值，否则返回c1的值。

注意事项：c1和c2必须为同一数据类型，除非显示的使用TO_CHAR函数。

nvl2(c1,c2,c3)如果参数表达式c1值为NULL，则NVL2()函数返回参数表达式c3的值；如果参数表达式c1值不为NULL，则NVL2()函数返回参数表达式c2的值。

 

 

 

 

 

通用函数
37、select nvl(null,0) from dual; 如果为null，则用0代替
38、select decode(1,1,'内容是1',2,'内容是2',3,'内容是3') from dual; 类似于 switch...case...

事务处理
39、commit;  提交事务
40、rollback; 回滚事务

41、select rownum from table; 在没一列前面显示行号
42、drop table 表名 cascade constraint
    on delete casecade 当父表中的内容被删除后，子表中的内容也被删除

43、desc表名 显示表的结构
44、create user [username] identified by [password] 创建新的用户
45、grant 权限1、权限2...to 用户  给创建用户权限
 ex:grant create session to [username] 此时只能连接到数据库
    grant connect,resource to [username] 此时权限能满足要求
46、alter user [username] identified by [password] 修改用户密码
47、alter user [username] password expired 下次登录时提示修改密码
48、alter user [username] account lock  锁住用户
49、alter user [username] account unlock  解锁锁用户
50、grant select,delete on scott.emp to [username] 把scott下emp表的两个权限给用户
51、revoke select ,delete on scott.emo from [username] 回收权限

 

 定量谓词

some、any、all

in 不如 exists 不如 连接查询

 

 

概念

 

子查询(标量子查询、比较子查询、相关子查询、嵌套子查询)

集合操作（union、union all、intersect、minus）

注意

对于blob、clob、bfile、varry或嵌套表类型的列，不能使用集合操作符

对于long类型的列，不能使用集合操作符union、intersect、minus

多表连接

基本连接(多个表之间用逗号隔开)

交叉连接（cross join）实际就是一个 inner join on true

自连接（对同一个表进行多次的引用）

自然连接 natural join

条件连接（有on条件）主要有内连接、外连接（左外、右外）、全外连接

层次化查询

select [level],exp......

from table_name

[where 条件]

[start with 开始条件]

[connect by prior 连接的条件]