ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PL/SQL学习笔记(五)

PL/SQL学习笔记(五)

原创 Linux操作系统 作者:yanxiaojun_007 时间:2009-05-24 13:32:18 0 删除 编辑
创建和管理表

对象:

表(TABLE)基本的存储单位,由行和列组成。

表名和列名(使用规则):
}3F(Ir @I)w0必须是字母开头;**博客首页 bbA i._ Y
必须是1-30的字符长度;**博客首页%P!h7i%Em-w
只能包括A-Z,a-z,0-9,_,$,#;
l%Zs.mO/vH| }0在同一个用户下不能头重名的对象;**博客首页2XXr4e y$`!SP6v
不能是ORACLE的保留字;

创建需求
&f,m;t!q0{'H,Q0必须有:
^+M-I#e1@Qh*Y veP{0 CREATE TABLE 权限;
.PmA x?0 足够的存储空间;**博客首页)}5U@ht-H
语法:
J7L3`| M\0CREATE TABLE [SCHEMA(方案).]TABLE**博客首页gv Kh%}+pp/E
(COL DATATYPE [DEFAULT默认值][]);

当前用户所有的表**博客首页:a[X.lQ#M
select table_name from user_tables;

当前用户所有的对象:
5c2T`$} hS/L }!a6S0desc user_objects

当前用户对象的别名:**博客首页z%W8[5I9y h'MW
select * from cat;

字段类型:
] GlQ6Q/l"m'P0 VARCHAR(size)   变长字符串类型**博客首页~aq-l-M@MZ
 CHAR(size) 定长字符串类型
j A*a:bl%]$P0 NUMBER(p,s)p位整数,s位小数  **博客首页T'zG9A~!`\_
 DATE **博客首页;B0P A@C v
 DATETIME 秒级最多可以到小数点后的9位
wb8L$hbC0 TIMESTAMP 带有小数秒的日期
:~Q V ?u0}]0  TIMESTAMP WITH TIME ZONE 带时区的类型
4X9c4l3P9{gs eZ0  TIMESTAMP WITH LOCAL TIME ZONE 带时区的并会进行时区转换的类型(同一时间在不        同地区看到的时间)

 INTERVAL YEAR TO MONTH 按年和月的间隔存储的类型
Ho~ \{;S/hG$mh0  INTERVAL ‘123-2’ TEAR(3) TO MONTH**博客首页-F+?u+pp
    **博客首页R;K J0~D$Gq I
 INTERVAL DAY TO SECOND 按天、小时、分和秒的间隔存储的类型**博客首页d8M1~;[w7X'P I}
  INTERVAL 
(Xr8i4j`0 LONG 变长的长字符串类型
q0v Cl$V%Ij9h0 CLOB 字符类型 4GIGABYTES 
Y#GzMJ-V}:G0 RAW  二进制类型与CHAR对应
m.^Zh ~0 LONG RAW 二进制类型与LONG对应
}3dT+jOL&u6^0 BLOB
lBW-^hzh%h*^r0n0 BFILE 以文件的形式存储在操作系统**博客首页:I3{7F?(D1q
 ROWID 表中行的唯一地址(行地址)

方案:一个用户所有对象的命名集合。

如果想访问其他用户或方案的表要加上用户或方案作为前缀。

必须指明:**博客首页z:P4D-I],~tn6X
 表名称;
$n Vza&r(}XNxs_0 列名,列类型和长度;**博客首页 GLDk;P9?rX
用户表:
.X;^Y2x.y s5GU SF0 被用户创建和维护的一些表;**博客首页6`/m K4i a/^
 包括了用户自己的信息;**博客首页cU7|7q6@
数据字典表:
1K5w;F;q'i&]t0 被ORACLE数据库创建和维护的一些表;**博客首页C9Pv/C ?%dJ s
 包括了数据库的信息;

CTAS(子查询建表):

CREATE TABLE table_name**博客首页T(Q)?N0e^
[(col,coltype,..)]**博客首页[[`3{{8FZA
as subquery(子查询);

创建的表的列的数目匹配子查询的列的数目。
'^B f!`B6nU3IU0使用子查询的列的名字和默认值定义表。**博客首页+i5D+A8pFx2m&]t
注:
y)k b$WU:Bs {0*、被创建表的字段名要遵循如果没有字段别名和子查询中没设置别名的话,使用子查询中的列名;**博客首页5K)F]yiK+_
   如果有别名,使用别名;如果有字段列表([(col,coltype,..)]),在被创建的表中使用字段列表;**博客首页z@?FQE0u5Z
*、有字段列表与子查询的列要匹配。
CT*@*{f|'_)XqbfG0*、当没有字段列表的时候,而在子查询中有表达式的时候一定要在表达式后要加上别名。 
4e0s1K5J,M*l9S/~*z&K&Tq0*、只会把属性当中的非空属性复制过来,其他的比如约束条件、关联...都不会复制过来。

使用ALTER TABLE 语句可以:

*、在表中增加一个新列

语法:ALTER TABLE table add (col datatype [default],...,....);
:xkw g*T$e;@0新增加的字段一定是放在表的最后。

*、修改表字段的类型和长度

ALTER TABLE table modify (col datatype [default],...,....);**博客首页KxM)Dk7^:qp#q
对默认值的修改只会影响到新插入的行。
WU/v/Y8F+K NA0如果字段下有值的话,类型的修改成功率很小(要修改数据类型,要修改的列必须为空,即没有数据项)。
2KyT.\+zL W'PY0CHAR类型不能修改长度。

*、删除表字段

ALTER TABLE table DROP COLUMN (COLUMN_NAME_LIST);

9I2版可以修改列名 

*、SET UNUSED 设置字段为不可用。

原理:清楚掉字典信息(撤消存储空间),不可恢复。**博客首页km2CNk#^b8M:p
可以使用 SET UNUSED 选项标记一列或者多列不可用。**博客首页-h8V#i|tW8q.}C
使用DROP SET UNUSED 选项删除被被标记为不可用的列。

语法:**博客首页4u;X'`&q-tUi
ALTER TABLE table SET UNUSED (COLlist多个) 或者  ALTER TABLE table SET UNUSED COLUMN col单个;**博客首页WKrX4Lf P3Y;Z3b j
 **博客首页#|n-~:N8g3uJbe
ALTER TABLE table DROP UNUSED COLUMNS;

删除表:

删除关联:drop table table_name cascade;

改对象名:
I4o*tH(gWiD0 RENAME 对象原名 TO 要改的对象名;**博客首页:I |:\ `~k+`sU,Y
注:**博客首页B:pa+pS `8Y
    必须是对象的所有者才能进行改名的操作。

 

截取:**博客首页)p&F/Gt1y7@3b@$^ B
不能回滚;**博客首页xZIWB)t*L[/TmP~
删除表中所有数据;**博客首页^\zs n{
释放存储空间;
)[z5M#r S0语法: 
V1?H.?E+gfK0TRUNCATE TABLE 表名称;

DELETE 也可以删除所有行,但:
)}4`[3e#[*V EQ0可以回滚。
(\@%~(w6uj8?;I x0不释放存储空间。

 


%U Q;G4iX0aS0给表加注释:COMMENT**博客首页4|] y$}jqKP oj
comment on table table_name is '注释内容';

 

 

**博客首页$o [Q ~[(V1R|B
约束条件:
+bnV3|$Q(C4jG4N0如果经常用到约束条件的话,最好自己命名。
+qlR_3|fc%]@0当定义约束的时候可以将定义的语句作为CREATE TABLE 中的参数的一部分来完成。

表级别约束定义:**博客首页!zr&UnKp|z Q
CONSTRAINT 约束名 约束条件(字段名)

约束在表上强制了规则。
D i-z^2?aK!a0如果有参照的花,约束防止表的删除。**博客首页&]'eJ8{7@Cl
ORACLE支持的约束条件:**博客首页cM)s\fKt
NOT NULL 非空
z:Q3G1^0`ZPEA0         特点:唯一一个只能在列级定义的约束条件。                                                                           **博客首页/xKHr |(T6p
UNIQUE 唯一**博客首页X \L._C6rRxKQu
  允许有空值(空值不做比较);**博客首页NnU'C#flo7H+R4h
 特点:当创建约束的时候,系统会自动创建对应其的索引。**博客首页1x(~n^(X5r_M
PRIMARY KEY 主键
'W*xa dr(e0   特点:当创建约束的时候,系统会自动创建对应其的索引。
r r+s,FV Ua#oq0  在一个表中只允许一个主键。  **博客首页2s_ g8]0J eu
FOREIGN KEY 外键 **博客首页%W0ARj'|T9y!O/Q6n
 外键参照的一定是主表的主键或唯一键;
#u v8gj!b7[:oJ0 保证子表外键字段的值一定是主表中的被参照字段值的真子集;**博客首页 If}f:[g+or
 当主表字段被参照的时候,其值不允许被直接删除。**博客首页7SdJ;M%Z1Z#`Z
CONSTRAINT 约束名 FOREIGN KEY (外键字段名) REFERENCES 主表名(主表字段名);

 如果在字段列表中定义外键就可以不写 FOREIGN KEY 关键字。**博客首页 Q.A/cZX~/M,RW0f:k*Je
 如下格式:CONSTRAINT 约束名 REFERENCES 主表名(主表字段名);


vsC ot"@d0ON DELETE CASCADE  当主表的行被删除的时候,要删除子表中参照主表的行。**博客首页kv)V"BhJ/K
ALTER TABLE TABLE_NAME DROP (PK) CASCADE CONSTRAINTS;把作为主键的字段也同时删除了。

ON DELETE SET NULL 当主表的行被删除的时候,转换子表中的参照值为空。

CHECK

定义一个每行都必须满足的条件。**博客首页KD!V9[8KQP/Ktc
CREATE TABLE table_name
`KAl2Yi0(**博客首页h2|T-C5N?1?-Q
  ....
MW:_u5Mm4r$u0salary number(10,2),**博客首页9|#HW7];lA&z
CONSTRAINT 约束名 CHECK(SALARY>0),
d!m`sa g o P,xv0  .... 
$Q+C&fh;X0e S-lU2k"s0);

约束的使用:

约束的命名:给约束命名或者ORACLE服务器将使用SYS_Cn的格式为约束命名。

创建时期:

在创建表的同时或者在建表之后。

定义级别:

可以在表级定义或列级定义。

在数据字典中可以查看约束。

**博客首页 wUpG*O_(~
使用ALTER TABLE 语句:

*、添加或者删除约束条件,但是不能修改约束条件。**博客首页*}OsR8U oL CF
 就算列名上已经有约束条件,还可以继续添加约束条件的。**博客首页(F q]T{
添加: ALTER TABLE table_name ADD [CONSTRAINT ] 约束名 约束条件(column);
D%{'lV} V&Q0删除: ALTER TABLE table_name drop constraint 约束名; **博客首页1v^/Bm oL
 ALTER TABLE table_name PRIMARY KEY CASCADE;删除主键的时候,不用约束名。**博客首页\'A(G_!b:|7F
*、启动或禁用约束条件
n#M Dy0kl%AANh0 ALTER TABLE table_name Disable constraint 约束名; 禁用**博客首页%g&pBJu'C l
 ALTER TABLE table_name ENABLE constraint 约束名; 启用**博客首页8i7[Pw'fz
            
Y3V jd0g7V(o0*、通过MODIFY添加 NOT NULL 约束条件(因为NOT NULL为列级约束,只能用MODIFY添加)。
r(LnJ!S3}2w`0ALTER TABLE table_name MODIFY(col type NOT NULL);

查看约束条件:**博客首页5Q)VX+x'pou#m2}
//desc user_constraints**博客首页qUjZ7q2u
OWNER 拥有者;
;Ev}sw6]M0CONSTRAINT_NAME 约束名称**博客首页V8R]Z;lO
CONSTRAINT_TYPE 约束类型
Uy{x|Z3h8R0 
g9Dt E$^0SEARCH_CONDITION  check的条件
6o U1^_4?0eQ7S$uN0 

select constraint_name, constraint_type,search_condition,status**博客首页:j7tb(y#xM
from user_constraint where table_name='b';

 

 

                                          **博客首页Y$ru@)w
视图 (VIEW) 一个或多个表的数据集的逻辑表示(虚表,不存储数据)。**博客首页'U%u/bRM u3r
视图不能提高查询的性能。
h${7Ht @uL7]^|0分类:**博客首页Kl)^$L\2`!H
 简单
%uoo3[Q,O?0  数目:一个
k z;}0G cC1O@.I0  函数:不包含**博客首页C!O4N5mb)bef
  分组数据:不包含
ux3Xu#~2U'V/c0  可以做DML操作
8^ H4X2x|T0 复杂**博客首页9l/Z:Fo&\x
  数目:一个或多个**博客首页-|xu!_4?$HZ$wh
  函数:包含**博客首页Sdt.cE'h.Y ?
  分组数据:包含**博客首页F%XK(w;w Ff Jq
  不一定能做DML操作

视图也可以用DESC描述。
FW\ d!] bV0 
o6vGv{I0创建视图:

CREATE [or replace(修改视图)] [force/noforce] VIEW view_name(col coltype ,.......)**博客首页tq0z%sk
as
A g"s1? YQ*Z)d0subquery
9Q1I8inU$d2zj0[WITH CHECK OPTION [CONSTRAINT constraint]]**博客首页3Y1JUU&y2e4L
[WITH READ ONLY[CONSTRAINT constraint]];

USER_VIEWS 关于视图的字典

修改视图:

CREATE OR REPLACE 原视图名 (字段列表)
5QVANchQGR0AS 子查询;

**博客首页VlBhJ_&J:P _K3N2?
包含:**博客首页OQRW~SK
GROUP BY**博客首页8PG5Cl"EVC;| T
DISTINCT**博客首页4] B{ \w'V
ROWNUM**博客首页-Uy z j q
不能对视图进行删除操作;

包含:**博客首页FF*Vh0uX-ui
GROUP BY
}0j+EoV6c ^0DISTINCT
P s#T/_*zGL tO-QI)F0ROWNUM
8EJVV? c%BD0通过表达式定义的列**博客首页?,Y-fOH.N/QW,j
不能对视图进行修改操作;

包含:**博客首页1u`&r6[*mC4Gf-F
GROUP BY
q pArV4`h0DISTINCT**博客首页 O S-P J#Q$ysg}+x
ROWNUM**博客首页U@ V;`.em:r|(C.P
通过表达式定义的列
9qR/ndd0C?ku0在视图中没有包含基表中的 NOT NULL 列**博客首页/HVXn1Kb-NO:\ml^
不能对视图进行插入操作;

使用视图的原因;

为了限制对数据的访问;
0n9Pun)Y0r)w$l0为了使复杂的查询变得简单;**博客首页/DRxVR5I_$HUd)E
提供了数据的独立性;**博客首页t*yJ-O%z9Z{ J
提供了对相同数据的不同显示;

 

使用WITH CHECK OPTION 子句创建视图

创建视图时通过 WITH CHECK OPTION 子句确保执行的DML语句不会引起数据不出现在视图上。
OmH-E0L1x*}#t0在对视图做DML操作的时候,一定要符合WHERE子句中的条件。**博客首页m*}%{1oO;l@ [1?
CREATE OR REPLACE VIEW empvu20 as select * from employees**博客首页&|-B6b xEt$~
where check option constraint [empvu20_ck];

 

WITH READ ONLY
'Pq0D6M6qF8l0s~:|X0不可以进行DML操作;

删除视图:

DROP VIEW view_name;

**博客首页KaB k B r!iC'n3Z
行内视图:
s ~T3N0Tw+pw:j*rt T0是一个在SQL语句中使用的带有别名的子查询,该子查询放在FROM之后;**博客首页6s a{:Ok+s$A P;?
  

TOP-N:
D5~E|3A:tG0select [col_list],rownum rank(排名)
-g6m8h5J,]h9i f0|0from (select [col_list] from table_name order by top-n_col)**博客首页q\5WQ,JD)}
where rownum<=n;

序列(SEPUENCE)产生的顺序数字,单向递增或单向递减,且步长相同。

索引(INDEX)用于提高查询性能。

同义词(SYNONYM)对象的别名。
&NB] { d0Y0create public synonym e for hr.employees;

user_synonyms;

创建同义词要有权限,访问的时候也需要权限。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15037413/viewspace-600897/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-24

  • 博文量
    51
  • 访问量
    51966