数据库常用命令
数据库常用命令
1.连接
—内连接
select * from dali.test1 a, dali.test2 b where a.a=b.a;
—左连接
select * from dali.test1 a, dali.test2 b where a.a=b.a(+);
—右连接
select * from dali.test1 a, dali.test2 b where a.a(+)=b.a;
—完全连接
select * from dali.test1 a, dali.test2 b where a.a=b.a(+)
union
select * from dali.test1 a, dali.test2 b where a.a(+)=b.a;
—迪卡尔
select * from dali.test1, dali.test2;
2004-5-27 13:05:06
查看评语»»»
2004-5-27 21:09:13 判断是否为空：
在SQl Server中为ISNULL(field1,0)
在Oracle中为NVL(field1,0)
转换
select nvl(to_char(fvisitdate),0) as m from tom_users order by m;
2004-6-2 18:00:13 网上的笔记（一）
一、基础与概念
1、PL/SQL不区分大小写，除非是由引号引起来的字符串。
2、 PL/SQL标识符的命名规则
标识符的最大长度是30个字符，包括字母,数字,$,_,#
不可包含保留字
要以字来打头
不能和同一块中的表中的字段名一样
3、命名规范
sql*plus
substitution variable p_name
variable v_name
constant c_name
sql*plus global variable g_name
exception e_name
4、注销方式
–注释内容
/*……注释内容………….*/
5、只要表达式里有NULL，则整个表达式的值为NULL
BOOLEAN和NULL
这里要注意的是：NULL AND TRUE ==NULL
NULL AND FALSE==FALSE
NULL OR TRUE==TRUE
NULL OR FALSE ==NULL
6、PL/SQL Environment
PL/SQL Blcok—–mon-sql—–procedural statement executor
—–sql————sql statement executor
7、分类
procedures：执行一个动作,做为一个pl/sql来执行，可以返回一个值
function：计算一个值,用于嵌入到表达式中，并必须返回一个值
package：把函数和过程逻辑的关联起来
8、pl/sql block structure
header: Contains the subprogram name, type, and arguments.Only used for subprograms.
Declarative: Contains the local identifiers for the block.
Executable: Contains the SQL statements and PL/SQL control statements.
Exception: Performs actions when errors occur.
9、 DCL和DDL都不被pl/sql支持。DML和commit可以被支持.
10、 一个嵌套块成为封闭块里的一个可执行语句，一个块可嵌套在任何允许放置可执行语句的地方，包括执行部分和异常处理部分。
11、 PL/SQL表达式不能包含组函数，但一个PL/SQL块里的SQL语句可以。
12、 当一个语句中有混合的数据类型时，PL/SQL可以动态转变。如：想把一个NUMBER值存进一个VARCHAR2的变量里，PL/SQL会动态地把NUMBER值转变为VARCHAR2类型的字符值。
13、 PL/SQL表达式可以包含SQL函数。
14、 嵌套块里的语句不能包含一个异常段。
15、 PL/SQL中的SELECT语句必须使用INTO子句。
二、 数据类型：
1) Base Scalar Datatypes•
Ø VARCHAR2 ：在PL/SQL中可以存储2000，在oracle8中是4000字节
Ø NUMBER [(precision, scale)]：当声明时。默认值是null
Ø DATE:存储日期型
Ø CHAR [(maximum_length)]: 如没有指定，数据类型为CHAR的列默认长度为1。这个长度的范围是1到2000。
Ø LONG:用于长度不定，最大值为2G的字符型数据。
Ø Row用来存二进制，在PL/SQL中最大长度是32767，在oracle8中是255字节
Ø LONG RAW：可以容纳raw binary data
Ø BOOLEAN:只能为true or false or null
Ø BINARY_INTEGER
Ø PLS_INTEGER
2) Composite Datatypes
Ø PL/SQL TABLES:
Primary Key：KEY的类型是BINARY_INTEGER
VALUE：真正的值
声明：TYPE type_name IS TABLE OF
{column_type | variable%TYPE | table.column%TYPE} [NOT NULL]
[INDEX BY BINARY_INTEGER];
identifier type_name;
eg1:
TYPE t_nametable IS TABLE OF VARCHAR(2)
INDEX BY BINARY_INTEGER;
引用：t_nametable(1):=’jact’
eg2:
DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
引用：dept_table.row1(1):=’jact’;
Ø PL/SQL RECORDS
定义: TYPE type_name IS RECORD
(field_declaration[, field_declaration]…);
identifier type_name;
TYPE record_name IS RECORD(
name varchar（20）,
id number(9)
)
引用：
student record_name
student.name
student.id
3) 3)Lob: 用来存二进制，最大长度是4GB
CLOB:RECIPE
BLOB:PHOTO（可以容纳4G的二进制）
BFILE:MOVIE把二进制存在一个扩展的文件中。容量是4G
NCLOB
三、PL/SQL Block Structure
DECLARE—Optional
-Variables,Constants,cursors,user-defined exceptions
BEGIN—Mandatory
-SQL statements
-PL/SQL control statements
EXCEPTION-Optional
-Actions to perform when errors occur
END:—Mandatory
Block type
Anonymous:[DECLARE]
BEGIN
……..
[EXCEPTION]
END;
Procedure:PROCEDURE name IS
[DECLARE]
BEGIN
……..
[EXCEPTION]
END;
Function:FUNTION name
RETURN datatype IS
[DECLARE]
BEGIN
……..
RETURN value;
[EXCEPTION]
END;
四、变量
1、变量的赋值：identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
2、变量名number(9,2) NOT NULL :=0; //在声明一个PL/SQL参数时，可以使用赋值运算符 ：= 为该参数赋初值。如果没有为参数赋初值，参数会被设置为null。如果参数被定义NOT NULL约束，那么就必须赋初值。
3、变量的类型：变量名number(9,2);
变量名 saray%type; //这样value的类型就和saray一样，也可以用列名取代saray
变量名parts%rowtype
//parts是表名，这是个记录类型的变量。其内部结构和parts一样
是根据表或视图的列来定义各变量
用表名做为前缀
结构和表的结构一样
4、在PL/SQL调用sqlplus的变量，在变量前面加”：”
:g_monthly_sal := v_sal / 12;
五、控制程序流
1、 loop
程序段
exit [ when 表达式]
end loop;
可以为循环设定标签
EG：
BEGIN
<<Outer_loop>>
LOOP
v_counter := v_counter+1;
EXIT WHEN v_counter>10;
<<Inner_loop>>
LOOP
…
EXIT Outer_loop WHEN total_done = ‘YES’;
— Leave both loops
EXIT WHEN inner_done = ‘YES’;
— Leave inner loop only
…
END LOOP Inner_loop;
…
END LOOP Outer_loop;
END;
2、 while 表达式 loop
程序段
end loop;
3、for I in 1 .. 20 loop
程序段
end loop;
4、if [not]…..then
程序段
elsif ….then
程序段
else
程序段
end if;
5、 message1
<<message1>>
a:=a+1
注意：不可以同外层跳到里层
不可以从一个IF子句跳到另一个IF子句
不可以从异常处理块跳到当前块
6、 select into 为变量赋值
select name into v_name where …
六、游标
游标的本质是SQL语句的一个工作区域，用于处理多行记录集的查询。
分类：Implicit cursors：由DML和PL/SQL的SELECT隐式的定义，不可以使用open,fetch和close去控制这个sql 游标。每一次只处理一行。但是可以使用游标的属性
Explicit cursors：由程序员定义, 显式游标用于返回多于一行的查询
以下讨论的都是显式游标
执行的四个步骤：
1、声明：定义游标的名字和结构，select 中可以使用order by
2、 打开游标：执行查询同时绑定所有涉及到的变量
执行的内容：
为select 分配内存并分析select语句
绑定输入的变量
配置指针在活动集的第一行
注意：如果 查询不返回结果，不会引发PL/SQL的异常，你可以在执行fetch后测试返回的结果
如果游标内的声明包括update。一样会执行行锁定
3、 Fetch：把当前行的值赋给变量，每个fetch都会把游标指针向下移动一行。
如果到了最后一行就会自动退出for loop
4、 关闭：释放活动的集，可以再次使用open
游标的几个属性：
SQL%ROWCOUNT 受最近执行的SQL语句影响的行的数目。（一个整数值）
SQL%FOUND Boolean属性，如果最近的SQL语句影响了一行或多行，其值为
TRUE。
SQL%NOTFOUND Boolean属性，如果最近的SQL语句没有影响任何行，其值为
TRUE。
SQL%ISOPEN 总是为FALSE，原因是PL/SQL总是它们结束执行后立即关闭内隐游标。
例子
EG1:常规用法
CURSOR c1 IS
SELECT empno, ename
FROM emp;
emp_record c1%ROWTYPE;
BEGIN
OPEN c1;
. . .
FETCH c1 INTO emp_record;
EG2:使用for循环实现游标
DECLARE
CURSOR c1 IS
SELECT empno, ename
FROM emp;
emp_record c1%ROWTYPE;
BEGIN
FOR emp_record IN c1 LOOP
— implicit open and implicit fetch occur
IF emp_record.empno = 7839 THEN
…
END LOOP; — implicit close occurs
END;
游标FOR循环立时不需要FETCH语句的。游标打开，在循环中每次重复提取一行，所有的行都处理后，游标会自动关闭。
EG3:不定义游标的方式
BEGIN
FOR emp_record IN ( SELECT empno, ename
FROM emp) LOOP
— implicit open and implicit fetch occur
IF emp_record.empno = 7839 THEN
…
END LOOP; — implicit close occurs
END;
EG4:带变量的游标—— 你必须指定指定参数的数据类型，但不用指定大小
DECLARE
CURSOR c1
(v_deptno NUMBER, v_job VARCHAR2) IS
SELECT empno, ename
FROM emp
WHERE deptno = v_deptno
AND job = v_job;
BEGIN
OPEN c1(10, ‘CLERK’);
…
EG5:FOR UPDATE—-当加上for update则把整个表或字段锁住了。
SELECT … FROM …
FOR UPDATE [OF column_reference][NOWAIT]
DECLARE
CURSOR c1 IS
SELECT empno, ename
FROM emp
FOR UPDATE NOWAIT;
NOWAIT:返回一个oracle的错误信息如果此行给其他的会话锁住了。
EG6：WHERE CURRENT OF
用于在游标中删除和更新当前行
必须使用 FORUPDATE去锁住行
使用WHERE CURRENT OF去指向当前的行
DECLARE
CURSOR c1 IS
SELECT …
FOR UPDATE NOWAIT;
BEGIN
…
FOR emp_record IN c1 LOOP
UPDATE …
WHERE CURRENT OF c1;
…
END LOOP;
COMMIT;
END;
七、处理异常
Predefined Exception
BEGIN SELECT … COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
statement1;
statement2;
WHEN TOO_MANY_ROWS THEN
statement1;
WHEN OTHERS THEN
statement1;
statement2;
statement3;
END;
…….
Non-Predefined Exception
DECLARE
e_products_invalid EXCEPTION;
PRAGMA EXCEPTION_INIT (
e_products_invalid, -2292);
v_message VARCHAR2(50);
BEGIN
. . .
EXCEPTION
WHEN e_products_invalid THEN
:g_message := ‘Product code
specified is not valid.’;
. . .
END;
User-Defined Exception
DECLARE
e_amount_remaining EXCEPTION;
. . .
BEGIN
. . .
RAISE e_amount_remaining;
. . .
EXCEPTION
WHEN e_amount_remaining THEN
:g_message := ‘There is still an amount
in stock.’;
. . .
END;
RAISE_APPLICATION_ERROR
DECLARE
…….
Invalidpart EXCEPTION;
BEGIN
…….
IF SQL%NOTFOUND THEN
RAISE invalidpart;
END IF;
EXCEPTION
WHEN invalidpart THEN
Raise_application_error(-20003,’Invalid Part id #’|| partnum);
WHEN OTHERS THEN
Raise_application_error(-20000,errNum||errMsg);
END
² 使用EXCEPTION关键字在一个PL/SQL块的声明部分声明用户自定义异常
² 使用PL/SQL命令RAISE检测用户自定义异常
² PL/SQL可使用Raise_application_error过程返回一个用户自定义错误数和消息给调用环境。所有的用户自定义错误消息必须在-20000到-20999之间
² PL/SQL程序可以使用WHEN OTHERS 异常处理来处理没有特定处理的所有异常
² PL/SQL程序可以使用特殊的SQLCODE和SQLERRM函数返回最新的oracle错误号码和消息
DECLARE
v_error_code NUMBER;
v_error_message VARCHAR2(255);
BEGIN
…
EXCEPTION
…
WHEN OTHERS THEN
ROLLBACK;
v_error_code := SQLCODE ;
v_error_message := SQLERRM ;
INSERT INTO errors VALUES(v_error_code,
v_error_message);
END;
数据库联接
定义：CREATE DATABASE LINK link_name
CONNECT TO username IDENTIFIED BY password
USING sqlnet_string;
使用：表名@link_name
替代名
CREATE SYNONYM synonym_name FOR reference;
Eg：CREATE SYNONYM backup FOR backup@esal
2004-6-2 18:00:58 网上笔记（二）
一、关系数据库的一些概念
1、主键的值一般不可以改变
2、外键：指向另一个表或本表的主键或唯一键的字段。外键的值一定要和某一主键相同，或者为空。
3、数据库对像：表，视图，序列，索引，同义词，程序（进程，函数，sql和pl/sql数据）4、sql command 类别
data retrieval数据检索:select
data manipulationlanguage (DML)数据操作语言:insert,update,delete
data definition language (DDL)数据定义语言：create,alter,drop,rename,truncate
transaction control事务控制:commit,rollback,savepoint
data control language（DCL）数据控制语言：grant,revoke
DCL 和DDL命令的执行会导致一次隐式提交，之前未提交的操作（包括DML 命令）都会提交写入日志文件，并在适当地时候写入数据文件。
二、SQL的语法
1) 连接号：||
2) 把两个字符连接起来
eg:select game_card_type_id||name from game_card_type;
3) select distinct dept_id,title from emp： 对多个字段的唯一
4) order by desc(降序)
order by asc(升序)
5) where column is (not) null
6) like ‘_a%’ _表示一个字符。％表示多少字符
like ‘%x\_y%’ escape ‘\’:显示包括x_y的字符
7) where table1.column(+)=table2.column
place the operator on the side of the join where there is no value to join to.
8) 联接类型:
equijoin:等式查询
non_equijoin:不等式查询
self:自己和自己建立关联
out join:where a.column=b.column(+)
可以用的操作符是：’=’，’and’，不可以用’or’，’in’
9) COUNT 函数所用的列包含空值时，空值行被忽略。
10) where 后的in any all 的区别
in ：等于子查询的任何一个数
any ：与子查询的每一个值相比
只要比其中一个大（小）就可以了
all:与子查询的所有值相比要比所有的的都大（小）
!=ALL作用跟NOT IN 一样
三、SQL*PLUS的环境(可以在glogin.sql中定义初始参数)
1) START 命令用以执行一个已储存的文件,等同于@
2) SAVE命令用以创建一个文件
3) EDIT命令用以调用编辑器编辑已存文件的内容
4) CHANGE 是SQL*Plus的编辑命令，用于在当前一行把旧文段转换为新文段
5) DEL 命令用以删除文本中一行或多行文字
6) SPOOL命令用以把查询结果储存在一个已有文件中，注意与SAVE区别
7) GET命令用以一个文件的内容写进一个SQL块中
8) SPOOL OUT命令用以把文件的结果发送到系统打印机。
9) set pause off/on:设置页面的滚动。按enter看下一页。
10) PAGESIZE：指定每页显示的行的数值，
11) LONG：设置LONG类型数据显示的最大宽度
12) FEEDBACK：设置查询返回记录的最大值
13) DESCRIBE :用于显示表和视图的结构，同义词，或指定函数和过程的详述。
14) Timing：可以看到语句执行的时间
15) Autotrace：可以看到sql的执行计划，sysdba执行/home/oracle/product/9.2.0/sqlplus/admin/plustrce.sql脚本，而且必须把plustrace角色赋给执行用户。执行用户必须运行/home/oracle/product/9.2.0/rdbms/admin/utlxplan.sql
16) 在各种数据类型中，只有NUMBER数据类型的默认显示是靠右对齐的，而CHAR, DATE和VARCHAR2是靠左对齐的
17) QUIT 是SQL*PLUS命令，用以结束一个SQL*PLUS的对话。
18) ttitle:
ttitle ‘selina’ 设select 的结果的抬头为selina
tti ：显示ttitle的状态
tti off/on
btitle ‘end’ 设结果的尾部
19) Column：
a) column name heading ‘名字’format a15
b) column id justify left format 999999
c) column start_date format a9 null ‘not hired’//当字段为空的时候则显示成not hired
d) column :显示所有的column设置
e) column columnname:显示某一个字段的设置
f) clear column :清除所有column设置
g) column columnname clear:清除某一字段的设置
h) 以上的column可用col代替.clear可以用cle代替
13）定义变量
用&：由用户输入变量值，此变量可以存在于where后，做为整个查询语句的变量。也可以在order by 后。做为字段的变量。也可以放在select 后，做为字段或表达式的变量。
用&&：如果多个地方引用此变量。。只用输入一次
SQL> SELECT empno, ename, job, &&column_name
FROM emp
ORDER BY &column_name;
accept：由用户输入变量值
accept 变量名 datatype prompt ‘告诉用户需要输入的信息：’ hide
引用的时候：&变名
define(undefined)：一开始就定义变量值
四、函数：
1、字符函数
lower:把字符转成小写
upper:把字符转成大写
initcap:把单词的第一个字母变成大写
concat:连接字符 concat(‘good’,’morning’)=goodmoring
SUBSTR (column\expression, m[,n]) 用于对字符串进行截取操作，从第m个位置开始，把其后的连续n个字符的部分截取下来，如果m位负值，则从末尾开始计算。
eg:substr(‘string’,1,3) =str
substr(‘string’-3,3)=ing
INSTR(’String’, ‘r’)=3
LPAD(sal,10,’*') =*******sal
length: 用于返回表达式中的字符数，注意返回的是NUMBER。
NVL(expression1, expression2)
NVL 函数用以把一个空值转换为一个实值,如：
NVL(100/quantity, 0) ,要是quantity为空值，该函数返回一个0。
如果两个字段类型不同必须进行转换。
Min():返回最小值。。如果是字符。。A<a
2、数学函数
round:四舍五入
round(2.566,2)=2.27
round(45,-1)=50
trunc: 截断
trunc(2.566,2)=2.56
trunc(45,-1)=40
mod:
mod(m,n):m-n*flood(m/n) //flood是取整数
3、日期函数:
a) months_between(date1,date2)：算date1和date2之间的月的数量,可以是小数可以是负数
months_between(’01-sep-95’,’11-jan-94’)=1.9774194
b) add_months(date,n)：为date加上N个月，N只可以是整数
c) next_date(date,’char’)：查找date的下一个星期N
next_date(’01-sep-95’,’FRIDAY’)=08-SEP-95
d) last_day(date):查找date月的最后一天。
e) rount(date)：把日期四舍五入
f) rount(25-MAY-95’,’MONTH’)=01-JUN-95
g) rount(25-MAY-95’,’YEAR’)=01-JAN-95
h) trunc(date)：把日期截断
i) trunc (25-MAY-95’,’MONTH’)=01-MAY-95
j) trunc (25-MAY-95’,’YEAR’)=01-JAN-95
k) 日期中RR与YY的区别，RR格式对日期作类似于舍入的操作，YY格式对日期作类似于截取的操作
RR YY
1995 27-oct-95 1995 1995
1995 27-oct-17 2017 1917
2001 27-oct-17 2017 2017
2001 27-oct-95 1995 2095
用法：select to_char(sysdate, ‘YY’) from dual;
select to_char(to_date(’95-11-27′, ‘RR-MM-DD’), ‘YYYY-MM-DD’) from dual;
select to_char(to_date(’95-11-27′, ‘YY-MM-DD’), ‘YYYY-MM-DD’) from dual;
4、转换函数
TO_CHAR：
TO_CHAR(date,’fmt’)：fm前缀用来去除首尾的空字符或0
TO_CHAR(total,’fm$999999’)
如果想转成$0.25,那就要写成fm$9999990.99
可以把日期转换成字符
TO_CHAR(log_time,’MM/YY’)
TO_CHAR(lot_time,’fmdd’’of;’’mm yyyy’)
具体格式如下
HH24:MI:SS AM———–15:24:32 pm
DD’’of’’MONTH———–12 of MAY
Ddspth————————fourteenth
Ddsp————————–fourteen
ddth—————————4th
YYYY———————–1978
MM—————————–12
MONTH————————-MAY
5、group 函数avg,count,max,min,stddev,sum,variance
五、数据字典
用户表：由用户创建，包含用户的内容
数据字典：由系统建立，包含数据库的信息
前缀:
USER_ ：由用户创建,显示用户拥有的所有对象。
ALL_ ：由受权的用户访问, 用户可以访问的对象名。
DBA_ ：由受了DBA权限的人访问，显示数据库的所有对象。
V$ ：由受了DBA权限的人访问，显示数据字典数据库服务器性能信息。通常是DBA用于显示系统的统计表和动态性能表。
数据字典
DICTIONARY数据字典提供用户可以访问的数据字典表和视图的描述。
USER_OBJECTS显示用户拥有的对象。
USER_VIEWS显示用户拥有的视图。
USER_TABLES显示用户拥有的表。
ALL_TABLES显示用户可以访问的表。
ALL_VIEWS显示用户可以访问的视图。
USER_CATALOG显示用户拥有的所有表，视图，同义词和序列。
USER_CONS_COLUMNS显示带约束的列。
DBA_CONS_COLUMNS显示数据库里的所有表，视图和同义词。
USER_TAB_PRIVS_MADE：本用户赋给别的用户赋予权限的表
USER_TAB_PRIVS_RECD：其他用户给本用户赋予权限的表
USER_COL_PRIVS_MADE：本用户赋给别的用户赋予权限的字段
USER_COL_PRIVS_RECD：其他用户给本用户赋予权限的字段
ROLE_SYS_PRIVS:有什么系统权限赋给role
ROLE_TAB_PRIVS:有什么关于表的权限赋给role
USER_ROLE_PRIVS:role和用户的对应表
常用的表
user_objects：用户对象表（存储用户的所有对象）存储以下的类型的数据
Selina Sql>select distinct object_type from user_objects;
INDEX
LOB
PACKAGE
PACKAGE BODY
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TRIGGER
VIEW
User_catalog:用户类表，存储以下的类型的数据
Selina Sql>select distinct table_type from user_catalog;
SEQUENCE
SYNONYM
TABLE
VIEW
六、建立对象
1、 表
a) 基本概念
Ø 数据库会分配一定的空间从而定义表的大小，所以表的大小不用指定。
Ø 表可容纳最多1000列。
Ø 表可在用户使用数据库的同时创建，而且表的结构可在表联机时修改。
Ø RENAME语句的语法 : RENAME old name TO new name; 注意要是表名中包含空格，符号或数字，必须使用双引号
Ø 在CREATE TABLE子句中使用子查询，新表中的列数必须与子查询返回的列数相等，列位置对应。另外，除NOT NULL约束外，新列不会继承源列的所有约束。
Ø 你不能对一列重命名。在表中添加一列时，你不能指定该列的位置，它会被默认放置在最后。你可以为一列增加长度。 通常当一列包含数据时，你不能修改该列的数据类型，不能减少一列的长度，但当列中包含空值或你不改变列的大小时，你可以把CHAR 转换为VARCHAR2数据类型。
Ø 在DROP TABLE table命令后加上CASCADE CONSTRAINTS回把表中的相关约束一并删除。如命令：”DROP TABLE employee CASCADE CONSTRAINTS;”会把employee表中的数据，结构，和相关约束一并删除。
Ø 当你创建一个数据类型为VARCHAR2的列时，必须指定长度。
Ø 为表加注释:comment on table tablename is ‘………’;
Ø COMMENT ON TABLE命令用于在数据字典里添加关于表，视图或快照的注释。
Ø 但你对一表执行了DML语句的INSERT操作时，但没有提交，别人可以同时访问该表，但看不到你所作的修改，由于对象被加锁，所以别人不能作同样的修改。
b) 建表原则
以字母开头
不可以超过30个字
只能由数字,字母,_,$,#组成
用子查询建立表
create table tablename as select …..
c) 删除表
drop table tablename
删除所有的数据
删除所有相关的index
所有待解决的事务会自动提交
不可以回滚
不释放空间
truncate table tablename
删除所有的数据
释放该表所占用的空间
不可以回滚
并存储容量参数重置为定义值
要成功执行TRUNCATE TABLE 命令，你必须拥有该表或拥有DELETE TABLE的系统权限。
2、约束
Ø 约束类别
column level:
NOT NULL
table level:
UNIQUE Key
PRIMARY Key
FOREIGN Key
CHECK
CHECK can be defined either on column level or on table level. Plus, you can say something like:
create table abc
(
column1 number,
column2 number,
constraint less_ck CHECK (column2 < column1)
);
This constraint will make sure column2 is less than column1 when you enter data into the table.
Ø 建立约束
Table constraint level
column,…
[CONSTRAINT constraint_name] constraint_type (column, …),
EG:
CREATE TABLE emp(
empno NUMBER(4),
ename VARCHAR2(10),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO))
CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
REFERENCES dept (deptno)
CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99));
Ø 修改约束
添加：
ALTER TABLE 命令可为一个现有的表添加一个约束，所有约束都是用ADD来添加，但是为一个列添加NOT NULL 约束，只能使用MODIFY子句，但需注意的是，添加NOT NULL约束时，必须符合以下两个条件中的一个：1.表中没有数据 2.添加约束的目标行没有空值。
ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(mgr) REFERENCES emp(empno);
删除：
SQL> ALTER TABLE emp
2 DROP CONSTRAINT emp_mgr_fk;
SQL> ALTER TABLE dept
2 DROP PRIMARY KEY CASCADE;
删除primary key ，而添加CASCADE则表示相关的完整性约束也一并删除。
禁用/启用
SQL> ALTER TABLE emp
2 DISABLE/enable CONSTRAINT emp_empno_pk CASCADE;
Ø 数据字典
USER_CONSTRAINTS
SQL> SELECT constraint_name, constraint_type,
2 search_condition
3 FROM user_constraints
4 WHERE table_name = ‘EMP’;
USER_CONS_COLUMNS(查看被约束的column)
SQL> SELECT constraint_name, column_name
2 FROM user_cons_columns
3 WHERE table_name = ‘EMP’;
Ø 知识点：如果A表中的a列在B表中拥有一个外键约束，在你删除B表记录之前，你必须先删除A表中的所有子记录。如果你尝试在A表中插入一个记录，而B表中不存在它的副记录的会得到一个违反约束的错误。
3、序列
CREATE（ALTER） SEQUENCE name
[INCREMENT BY n] ；指定序列以n递增，如果没指定，默认值1会使用
[START WITH n] ；由n开始计数
[(MAXVALUE n | NOMAXVALUE)] ；设置最大值n或不设置最大值
[(MINVALUE n | NOMINVALUE)] ；设置最小值n或不设置最小值
[(CYCLE | NOCYCLE)] ；当达到最大值时循环或不循环
[(CACHE n | NOCACHE)] ；预分配缓存大小，默认为20
删除序列
drop sequence name
CURRVAL伪列用于在当前序列中检索连续序列号，它能用在UPDATE语句的SET子句中和INSERT语句的VALUES子句中，还可以用在INSERT语句的子查询的SELECT列表中。但不能用于视图的SELECT语句和带HAVING子句的SELECT语句中。
4、视图
a) 建立
修改一个视图最简单的方法是使用带OR REPLACE的 CREATE VIEW语句，这允许视图的旧版本被代替，避免了删除视图和重建视图的必要，要是删除了视图，你必须在新视图上重新给对象授权。
create [or replace][force/noforce] view viewname
as subquery //子查询不可以用order by
[with check option] //则指明只有允许被视图访问的行才能被插入或更新。
[constraint constraintname]
[with read only] //不可以通过view进行表的修改
b) 对view的DML操作的限制
可以在simple view执行DML操作
不可以删除一行如果view 包含
group funtion
a group by clause
the distinct command
不可以修改数据如果view包含
以上说的条件
由表达式定义的列
the rownum pseudocolumn
不可以添加数据，如果view包含：
以上所有的条件
存在非空的字段没在包含在view中
c) 数据字典
USER_VIEWS数据字典显示用户所拥有的视图的描述
ALL_VIEWS数据字典显示用户有权访问的视图的描述
ALL_OBJECTS数据字典显示用户有权访问的对象（包括视图）的信息USER_OBJECTS数据字典显示用户所拥有的对象的描述
5、序列
CREATE SEQUENCE sequence [INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]; //序列放在内存中。使读取更快
eg: SQL> CREATE SEQUENCE dept_deptno
2 INCREMENT BY 1
3 START WITH 91
4 MAXVALUE 100
5 NOCACHE
6 NOCYCLE;
可以在USER_SEQUENCES查看所建立的表
SQL> SELECT sequence_name, min_value, max_value,
2 increment_by, last_number
3 FROM user_sequences;
•The LAST_NUMBER ：显示nextval的值
•Gaps in sequence values can occur when:
–发生rollback
–系统崩溃
–另一个表在用此序列
你必须是the sequence.的拥有者和 对此the sequence.有ALTER privilege 的权限
如果想改开始值的话只有重建序列
6、索引
a) 建立
Ø 自动：当指定表的列为primary key or union时。系统自动为此表建立一个index
Ø 手动：可以为表的非唯一值的列手动建立index
Ø create index indexname on table(column)
Ø 数据字典
用户建立的索引在USER_INDEXES 中(详细的信息)
USER_IND_COLUMNS (列的一些简单信息)
SELECT ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = ‘ ANNOUNCEMENT’;
Ø 建立索引的方针
在where 和join中经常用到的列
列的值范围很大
此列包含很多空值
表很大，但只要其中的2-4％的数据
很多索引不一定能提高速度。
Ø 不建索引的方针
表很小
列不常用来查询
查询结果超过表的2-4％
表常被更新
7、同义词
建立：create synonym 别名 for 原名
CREATE SYNONYM d_sum FOR dept_sum_vu;
删除： DROP SYNONYM d_sum;
在创建同义词时，语句中出现PRIVATE 和OR REPLACE 是无效的
七、安全
数据库的安全：包括系统安全和数据安全
1. 系统权限:访问数据库的权限，用于执行数据定义和数据控制命令，和其他数据导向性行为。CREATE SESSION,CREATE USER, DROP USER, 和 BACKUP ANY TABLE,这些是系统权限
2. 用户系统权限:建立会话,建立表,建立序列,建立视图,建立过程
3. 赋权
grant select on tablename to username (public//给所有用户)
with grant option; //被赋予的人就可以把权限再给别人
GRANT update (dname, loc) ON dept TO scott, manager;
可以具体到列
GRANT ALL ON inventory TO joe;
这命令使用了关键字ALL,向joe授予了在inventory表上ALTER, INDEX, INSERT, REFERENCES, SELECT, UPDATE, 和DELETE的权限。
4. 消权限
revoke select ,insert on tablename from username
REVOKE references
ON inventory
FROM joe
CASCADE CONSTRAINTS;
这命令实现了两个作用：
1 所有由joe创建的FOREIGN KEY 约束被删除
2 他创建 FOREIGN KEY约束的能力被取消了。
撤消只能相对表不能相对字段
如果撤消了某个用户，那么由这个用户为其他用户赋的权限也一起取消
要是你想通过命令REVOKE来回收用户A的某一权限，那你必须是当初的授权人。
5. 知识点：
a) INDEX对象权限可被授予一个用户，但不能授予一个角色（role）。它允许用户使用CREATE INDEX
b) 对象权限REFERENCE只能授予用户，不能授予角色（role）。
c) 只有拥有DBA权限或ALTER UAER权限的用户才能替普通用户修改密码
d) 要能创建用户，你必须拥有CREATE USER权限。要想有权访问数据库，你需拥有CREATE SESSION权限。要想在任何方案（schema）中创建和删除次表，你必须拥有CREATE ANY TABLE 和DROP ANY TABLE权限，要想在自己的方案（schema）中创建和删除次表，你必须拥有CREATE TABLE 和DROP TABLE权限。
e) 如果要删除kate select 表的权限。那么删除者必须是当初给kate赋权限的人
f) CREATE OR REPLACE ROLE rolename是个无效的命令
正确的只有CREATE ROLE rolename
g) 如何一次性删除某个用户的所有对象?
drop user username cascade
八、事务结束标志
明确的提交：
当 commit or rollback command is issued
暗含的的结束：
ddl command,例如create,或dcl command例如 grant
发生死锁
退出sqlplus，系统自动回滚
硬件或系统出错，系统自动回滚
commit后的操作
数据写入数据库
之前的数据会丢失
解除受影响的行的锁
所有的savepoints被释放
savepoint的用法
2. update……………
3. savepoint update
4. insert into …..
rollback to update
2004-6-9 15:57:02 在ORACLE建立一个临时表
create global temporary table PAB_temp as select * from pab
2004-6-10 11:11:24 ORACLE中的临时表
在Oracle8i中，可以创建以下两种临时表：
1。会话特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> )
ON COMMIT PRESERVE ROWS；
2。事务特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> )
ON COMMIT DELETE ROWS；
CREATE GLOBAL TEMPORARY TABLE MyTempTable
所建的临时表虽然是存在的，但是你试一下insert 一条记录然后用别的连接登上去select，记录是空的，明白了吧。
下面两句话再贴一下：
–ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）
–ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。
CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间，而且不同的SESSION之间互相看不到对方的数据
在会话结束后表中的数据自动清空，如果选了DELETE ROWS，则在提交的时候即清空数据，PRESERVE则一直到会话结束
