Oracle 常见问题解答

以前搜集的一个Oracle比较常见问题的列表,忘记了是从哪来的关于 SELECT N
问题有感于一些网友多次咨询和讨论选取某些指定行数据的问题,
我写了下面这样的简单说明, 请大家指正.这里描述的 SELECT N
包括这样几种情况:1. 选取TOP N行记录2. 选取N1-N2行记录3. 选取FOOT
N行记录当然需要考虑是否有ORDER BY子句的情况,
下面试以系统视图CAT为例分别说明.注: A. 为没有ORDER BY的情况B. 有ORDER
BY的情况1. 选取 TOP N 行记录A. SELECT * FROM CAT WHERE ROWNUM=NB.
SELECT * FROM( SELECT * FROM CAT ORDER BY TABLE_TYPE )WHERE
ROWNUM=N2. 选取N1-N2行记录A. SELECT TABLE_NAME,TABLE_TYPE FROM( SELECT
ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )WHERE ROWSEQ BETWEEN N1
AND N2;或:SELECT * FROM CAT WHERE ROWNUM=N2MINUSSELECT * FROM CAT
WHERE ROWNUMN1B. SELECT TABLE_NAME,TABLE_TYPE FROM( SELECT ROWNUM
ROWSEQ,X.* FROM (SELECT * FROM CAT ORDER BY TABLE_TYPE) X)WHERE
ROWSEQ BETWEEN N1+1 AND N2;3. 选取FOOT
N行记录这里是说明不知道记录集的记录个数的情况, 如果已知,
用上面2的方法即可A. SELECT TABLE_NAME,TABLE_TYPE FROM( SELECT ROWNUM
ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )WHERE ROWSEQ ( SELECT
COUNT(*)-N FROM CAT )B. SELECT TABLE_NAME,TABLE_TYPE FROM( SELECT
ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY
TABLE_TYPE)WHERE ROWSEQ ( SELECT COUNT(*)-N FROM CAT )或SELECT * FROM
( SELECT TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE DESC)
WHERE ROWNUM=N以上在ORACLE8.1.5 for Windows2000pro 上测试通过– end
–oracle FAQ(1) from chao_ping
1.快速整理破碎的表(在Oracle8i里边才可以这样使用) ALTER TABLE table_name
MOVE ( TABLESPACE XXX); 如何移动一张表所在的表空间 方法一: 1. Export
这张表 2. Drop这张表 3. Create table xxx tablespace xxx; 4. Imp
Ignore=y
还要注意的一点是,所有要读取这张表的PL/SQL储存过程都会失效。需要重新编译。
1. SELECT * FROM DBA_OBJECTS WHERE STATUS = ‘INVALID’; 2.
对这些包,函数,过程重新编译。 方法二: 仅对Oracle8i适用。
使用下面的语句: ALTER TABLE table_name MOVE TABLESPACE
new_tablespace; 这样的话,所有的约束、索引、触发器都不会受到影响。
但是需要rebuild这个标上的所有索引。
2.怎样直接进入sql*plus而不用输入用户名,密码: sqlplus /nolog; sqlplus
username/password@connect_string 3.怎样快速重建索引: alter index xxx
rebuild storage(); alter index xxx coalesce; 4.
为什么我看不到dbms_output的结果? SET SERVEROUTPUT ON 5.
进行一次大的事务以后,已经COMMIT了,但为什么我的回滚段还是那样大?
因为没有设置OPTIMAL的值,所以不会自动收缩。 可以用alter rollback segment
shrink to Xm;来手工进行收缩。 6. 为什么要使用VARCHAR2,而不用CHAR?
A.CHAR只支持2000字节长,而VARCHAR2支持4000字节的长度,适用性更好 B.
CHAR
占用更多的存储空间,定义多长,它就占用多长的空间,插入字符后面自动加空格填充;而VARCHAR2不论定义多长,都只使用实际插入的长度。
7. 为什么从不同的数据字典看,表/索引所占用的空间不一样? SQL select
blocks , empty_blocks from dba_tables where table_name=’表名’; BLOCKS
EMPTY_BLOCKS ———- ———— 1575 1524 SQL select
bytes,blocks,extents from dba_segments where segment_name=’表名’;
BYTES BLOCKS EXTENTS ———- – ——— ———- 6348800 3100 1
这是因为第一个数据库视图DBA_TABLES的BLOCKS列是指实际上使用的BLOCK数目,还有一些BLOCK虽然被占用了,但是没有数据存在,不计入里边。而在DBA_SEGMENTS这个数据库视图里边,BLOCKS列是指这个表总共占用的BLOCK的数目,包括有数据和没有数据的BLOCK总量。如果把第一个视图里边的BLOCKS和EMPTY_BLOCKS地总和加起来,正好等于第二个视图的BLOCKS列的大小。
8. 怎样把数据库的一张,多张表存为一个普通的文本文件?
可以在SQL*Plus里边用SPOOL命令把选出来的数据保存在SPOOL指定的文件里边。
9. 怎样从一张表里删除重复的记录 SQL SELECT * FROM EMP; EMP_ID
OFFICE_ID EMPNAME 305 12 ELLISON, GEORGE 305 12 MERCURIO, JASON 128 17
SIMPSON, LINDA 305 22 JACKSON, DREW
使用下面的SQL语句来识别那些重复的记录: SQL SELECT COUNT(*), EMP_ID,
OFFICE_ID FROM EMP GROUP BY EMP_ID, OFFICE_ID HAVING COUNT(*) 1;
结果如下: COUNT(*) EMP_ID OFFICE_ID 2 305 12 Table Example, with
duplicate values: SQL SELECT * FROM EMP; EMP_ID OFFICE_ID EMPNAME 305
12 ELLISON, GEORGE 305 12 MERCURIO, JASON 128 17 SIMPSON, LINDA 305 22
JACKSON, DREW 使用下面的语句来删除重复的记录: SQL DELETE FROM EMP A
WHERE (EMP_ID, OFFICE_ID, 2) IN (SELECT EMP_ID, OFFICE_ID,
decode(count(*),1,1,2) FROM EMP B WHERE A.EMP_ID=B.EMP_ID AND
A.OFFICE_ID = B.OFFICE_ID GROUP BY EMP_ID, OFFICE_ID); 10.
怎样在SQL*PLUS里想数据库插入特殊字符? 可以使用CHR函数。 11.
怎样删除一个列? 在Oracle8i里边,可以直接Drop一个列。语法为alter table
table_name drop column_name;
但是注意要在initsid.ora里边设定compatible=8.1.0以上。 12.
怎样重命名一个列? 1 alter table “table_name” add (new_column_name
data_type); 2 update table_name set new_column_name =
old_column_name where rowid=rowid; 3 alter table table_name drop
column old_column_name; 13. 怎样快速清空一张表? Truncate table
table_name; 14. 怎样为事务指定一个大的回滚段? Set transaction use
rollback segment rbs_name; 15.
怎样知道一张表上有那些权限赋予了哪些人,给他们了什么权限? select *
from dba_tab_privs where table_name=’表名’; 16.
怎么发现是谁锁住了你需要的一张表? Select object_id from
v$locked_object; Select object_name, object_type from dba_objects
where object_id=”;
每次清空一张表的时候,,这张表的存储参数NEXT自动复位到最后被删除的那个extent的大小。同样,如果显式地从一张表里边释放空间,NEXT参数也会自动被设置成最后被释放的那个extent的大小。
在SQL*Plus里边可以为一个事务指定一个回滚段:这在有大的事务将要发生的话时候还是很有用的。使用下面的语句可以为这个事务指定一个回滚段:
SQLSET TRANSACTION USE ROLLABCK SEGMENT 回滚段名称;
还可以在PL/SQL里边为一个事务指定一个回滚段。这个需要使用Oracle提供的包:DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT(‘回滚段名称’);
在有些平台上的Oracle,在启动的时候会自动生成一个sgadefSID.dbf,用这个文件是否存在就可以判断一个实例是否在运行。这个文件包含了SGA在内存中的地址。在数据库关闭的时候,Oracle会自动删除这个文件。但是在Oracle8i里边,这个文件不再存在了。需要使用新的判断方式来断定究竟某个实例是否在运行。比如PS命令。
在Oracle7里边,想要知道数据文件是否可以自动扩展,必须从sys.filext$这张表里边查取,但是在Oracle8里边,从dba_data_files里边就可以知道数据文件是否可以自动扩展了。
从Oracle8i开始,可以创建另一类数据库一级的触发器,比如数据库启动、关闭,用户登录、注销等事务,都可以触发这个事件的发生,从而作某些记录。在数据库一级定义的触发器会在所有用户相应事件发生的时候触发,而在Schema一级定义的触发器只有在某个特定用户的相应事件发生的时候才会触发。
从Oracle8i开始,多了一种关闭数据库的方式:SHUTDOWN
TRANSACTIONAL。这种方式允许所有的用户提交它们的工作。但是一旦提交之后就马上被切断联接,等所有用户都完成了各自的事务,shutdown就开始了。
从Oracle8开始,可以创建临时表,这些表的定义对于所有该用户的会话都是可以看到的,但是每个会话查询、插入、删除的数据和别的会话查询、插入、删除的数据都是不相关的。就像每个会话都分别有这样一份表一样。
从Oracle8i开始,对于那些没有进行分区的表,可以不用IMP/EXP就可以快速重组。但是这需要两倍于该表容量的表空间。这个语句就是:
ALTER TABLE TB_NAME MOVE TABLESPACE TS_NAME;
在Oracle8i里边可以创建反序索引。。由于反序索引的相邻键值不是存放在物理相邻的位置,因此只有全索引扫描或者通过单个列这一类语句才能够有效利用这些索引。这类反序索引在Oracle并行服务器上能够较好地协调不同实例对数据库的修改,可以在一定程度上提高系统性能。
从Oracle8开始,$instance视图可以查获许多有用的信息:比如主机名称,实例名,启动时间,版本号等。
临时表空间里边创建的临时段只有在shutdown地时候才会被释放。
但是在permanent表空间里边创建的临时段在一个事务结束之后就会被释放,有Smon进程来完成这个任务。oracle
FAQ(2) from chao_ping 关于OPTIMAL参数
optimal是用于限制回滚段大小的一个存储参数。在执行一个长的事务之后,那个事务所使用的回滚段会比较大,而设置了Optimal这个参数以后,一旦事务提交结束,回滚段自动收缩到Optimal所指定的大小。
如果你的系统中有许多长时间运行的事务的话,那么应该把回滚段的Optimal参数设置的比较大一点。这样有利于保持回滚段表空间的连续性。否则不断的扩张、收缩会使表空间更加破碎。
如果系统中主要的事务都是短时间的,那么应该把回滚段设置的比较小一些,这样有利于让回滚段里面的信息可以存储在SGA里边,以利于提高系统性能。
回滚段的Optimal参数可以在创建回滚段的时候指定,也可以用 ALTER ROLLBACK
SEGMENT SEGMENT_NAME OPTIMAL XX M;这样来重新设定。 Oracle8i 里边的
ALTER SESSION SET CURRENT_SCHEMA= 可以用来更改当前的用户模式。
Oracle公司已经宣称,不再支持server manager,这个工具自从Oracle
6.0开始,就一直是管理Oracle数据库的主要工具。现在,SQL*Plus替代了Server
Manager的地位,因此,Server
Manager中相应的功能也都集成到了SQL*Plus之中。
SQL*Plus新增加的主要命令是startup, shutdown, archive
log,和recover。当然,标准的SQL语法仍然是支持的了,比如一系列的CREATE,
ALTER等语句。但是也对其中的一些有了一些改变,比如原来不支持的ALTER
DATABASE OPEN,ALTER DATABASE MOUNT,ALTER DATABASE BACKUP 等句子。
对于SET命令,也多了一些新的选项,用来包含一些如自动恢复等。SHOW命令也开始可以用来直接显示参数SHOW
PARAMETER ,SHOW SGA等。而这些,原来都只是在Server
Manager里面才有的功能。
Oracle8i仍旧保留了尽人皆知的INTERNAL账户,但是要记住,这主要是为了向后兼容。INTERNAL账户的功能现在开始有SYSDBA,SYSOPER这两个角色来支持了。INTERNAL/SYS的口令可以用下面这个方法来进行修改:
O/S Prompt orapwd password=some password
当数据库刚刚创建的时候,SYS的密码默认为change_on_install,而SYSTEM的密码是manager,而INTERNAL则根本就没有密码。因此,创建完数据库之后,第一件要做的事情就是改变以上三个用户的口令。INTERNAL的口令可以用前面提到过的方法来改变,而SYSTEM,SYS则可以直接用ALTER
USER username IDENTIFIED BY
password;来更改。注意,在Oracle8i开始,ALTER USER SYS IDENTIFIED BY
password;同时也会更改INTERNAL的密码,如果你为INTERNAL设置了密码的话,同时,马上把SYSDBA,SYSOPER这两个角色授予负责管理这个数据库的用户。对于那些使用INTERNAL来连接数据库的脚本,也要相应的作一些修改。
启动和关闭数据库: 要从SQL*Plus里边启动数据库,请按照以下的步骤进行:
O/S Promptsqlplus /nolog SQL connect scott/tiger as SYSDBA SQL startup
如果希望用不同于默认得参数来启动数据库,可以使用下面代参数的启动命令:
SQL startup PFILE=init.ora file to be used
有时候需要启动数据库,但是不让普通用户进入,比如为了平衡IO,需要移动一个数据文件的位置,这时候就需要改变默认得启动选项:
SQL startup mount
当完成了维护任务之后,可以选择关闭数据库然后再重新按照正常方式打开,或者直接在SQL*Plus里边输入下面的命令,Oracle就可以开始正常运行了:
SQL alter database open
有时候需要创建一个新的数据库,或者需要重建控制文件,就需要用下面的语句:
SQL startup nomount
有时候,数据库难以正常启动,就可以考虑使用下面的办法来强迫启动:使用FORCE选项,STARTUP
FORCE实际上相当于一个SHUTDOWN ABORT然后再STARTUP这样一个过程。 SQL
startup force SHUTDOWN这个命令也有好几种参数可以选择:
正常关闭是等待所有用户都从系统退出以后,再正常关闭系统。这是一种最最理想的关闭数据库的方式。一般都应该使用这种方式来正常关闭数据库。
SQL shutdown 在Oracle8i开始,新加了一个关闭选项:SHUTDOWN
TRANSACTIONAL。这允许所有用户都完成它们的事务,一旦事务提交,马上被断开连接。这样既保证了用户不会丢失它们的事务,也保证了数据库可以及时关闭,进行必要的维护操作。这种方式关闭的话,下次系统启动之后,也不用进行实例一级的恢复。比下面提到的另外两种方式都要理想。
SQL shutdown transactional SHUTDOWN
IMMEDIATE是马上中止用户的当前事务,并不等这些事务完成,回滚这些用户的当前事务。但是如果有一些事务很久没有提交的话,那么SHUTDOWN
IMMEDIATE或许就不像说得那样IMMEDIATE了。可能也要花很多时间来回滚这些事务。
SQL shutdown immediate 在Oracle8i里边最后一种关闭方式是SHUTDOWN
ABORT。这种关闭方式和直接关闭计算机的电源其实没有太多的区别。任何当前连接的用户都被马上断开联接,在下次实例再次启动的时候,必须进行实例一级的恢复,用以回滚没有提交的事务。
SQL shutdown abort ALTER TABLE table_name
MOVE之后,表上的索引标志为UNUSABLE? 在Oracle8i开始,可以直接使用alter
table table_name move [tablespace tablespace_name];
来为一张表移动到另一个表空间,或者重新组织表的存储方式,以减少碎片。但是,这样使用过之后,所有这张表上的索引都将被标志为unusable。这是因为MOVE一张表之后,表中列对应的物理位置都改变了,就是所有行的ROWID都变化了,而这张表的索引就用到了其中行的ROWID。由于Oracle不会自动更新索引对应的ROWID,这时候,索引上的ROWID就指向了错误的地方。因此,索引被标志为UNUSABLE。这时候,你就需要手工重建索引。可以使用下面的语法来重建索引:
ALTER INDEX index_name
REBUILD;当然,还可以为索引指定特定的合适的存储参数,来优化索引的存储。或许Oracle之所以没有自动维护索引,就是为了让你可以为索引指定合适的存储参数。
如何远程安装Oracle:
如果需要从PC机上的X-window客户端安装Unix上的Oracle系统,要注意下面这一点:Oracle8i使用的是Universal
Installer,使用了Java技术,必须在图形界面下安装。如果是远程安装,必须设置一下什么地方来显示Universal
Installer的图形界面:使用 $DISPLAY=workstation_name:0.0 $export DISPLAY
举一个例子,你的PC机的IP地址是150.150.4.128,机器名字叫做test,那么就可以使用下面的语法来进行为安装作准备工作:
$DSIPLAY=150.150.4.128:0.0 $export DISPLAY
或者使用下面的语法,但是必须这个test机器的信息写在hosts文件里边:
DSIPLAY=test:0.0 $export DISPLAY

立此存照

Oracle入门

数据库的基本概念

数据  data    数据库  DataBase(DB) 数据库管理系统 DataBase Management
System(DBMS)

数据库管理员 DataBaseAdministrator(DBA) 数据库系统
DataBaseSystem(DBS)

数据模型 DataBaseModule(DB) 关系数据库Relational DataBase (RDB)

关系数据库管理系统 Relational DataBase Management System(RDBMS)

系统全局区SGA

关系数据库中使用数据表来存储数据,数据表由行和列组成,行通常叫做记录,列通常叫做字段。

Oracle是分布式数据库。

Oracle数据库是一个数据的集合,该集合被视为一个逻辑单元。

Oracle逻辑组件:包括表空间、段、区、数据块、模式对象。

表空间是Oracle数据库内部最高层次的存储逻辑结构。

创建用户自己的表空间:

语法:CREATE TABLESPACE tablespacename

         DATAFILE ‘filename’ [SIZE  INTEGER K|M|][AUTOEXTEND OFF|ON]

 

创建新用户并授权权限

CREATE USER username

IDENTIFIED BY password

[DEFAULT TABLESPASE tablespace]

[TEMPORARY TABLESPACE  tablespace]

 

授权用户权限                                                       

GRANT CONNECT  TO username;         GRANT RESOURCE TO username;

修改用户口令:ALTER  USER  username;   IDENTIFIEN BY  newpwd;

删除用户: DROP  USER  username  CASCADE;    级联删除关键字

 

数据库表对象

数据库的三个范式:

1、1NF 
指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即不能有重复的属性。

2、2NF
满足第一范式的基础上,要求数据库表中的每一个实例或者行必须可以被唯一地区分;要求实体的属性完全依赖于主关键字。即第二范式就是属性完全依赖于主键。

3、3NF
满足第二范式的基础上,要求数据库表中不包含已在其他表中已包含的非主关键字信息;也就是说属性不依赖于其他非主属性。

SQL ——Structured Query Language

命令:

数据定义语言DDL—-DataDefinition Language:CREATE   ALERT  DROP

数据操纵语言DML—– data manipulation language:INSERT   SELECT  DELETE 
UPDATE 

事务控制语言TCL----Transaction Control Language :COMMIT  SAVEPOINT  ROLLBACK

数据控制语言DCL-----DataControlLanguage:GRANT  REVOKE  

创建简单的数据库表对象

语法:

CREATE TABLE table

(column datatype [, column datatype [, …]] );

示例:

CREATE TABLE STUDENT

(STUDENT_ID  NUMBER, NAME  VARCHAR2(20) , MAJOR  VARCHAR2(20) ,
BIRTHDAY DATE);

 

数据完整性的作用就是强制要求数据库中只能接受正确的、合理的数据,防止错误的或无效的数据被插入到表中。

Oracle
约束:实体完整性约束、域完整性约束、引用完整性约束、自定义完整性约束

约束:是一种命名对象

1、 NOT NULL 约束 —确保列不包含空值。默认情况下列是没有定义NOT
NULL约束的。

示例:CREATE TABLE teacher

 ( teacher_id NUMBER,

 name VARCHAR2(10) NOT NULL,

  zhicheng VARCHAR2(20),  zhiwu VARCHAR2(20));

2、
UNIQUE约束—–要求列或者列的组合是唯一的。只有UNIQUE约束时,是允许列有空值的,但只能有一个空值。

示例:CREATE TABLE teacher

  ( teacher_id NUMBER,name VARCHAR2(10),

  zhicheng VARCHAR2(20),zhiwu VARCHAR2(20),

  CONSTRAINT teacher_name UNIQUE(name));

3、 PRIMARY KEY
主键约束—–为表创建一个主键。每一个表中只能创建一个主键。强制列或列的组合的值具有唯一性。不允许有空值或重复的值。

示例:CREATE TABLE teacher

 (  teacher_id NUMBER,name VARCHAR2(10),

   zhicheng VARCHAR2(20),zhiwu VARCHAR2(20),

CONSTRAINT teacher_pk PRIMARY KEY(teacher_id),

CONSTRAINT teacher_name UNIQUE(name));

 

4、 外键约束FROEIGN KEY
—-指明一个列或列的组合作为一个外键,并且引用于其他表的主键。

示例:CREATE TABLE student

 ( student_id NUMBER,name VARCHAR2(10),

  major VARCHAR2(20),birthday DATE,

  teacher_id NUMBER,

  CONSTRAINT student_pk foreign key(teacher_id)

 REFERENCES teacher(teacher_id));

 

5、 检查约束 CHECK —–CHECK关键字后面的括号中是检查条件

示例:CREATE TABLE newstudent

 ( student_id NUMBER,name VARCHAR2(10),

 major VARCHAR2(20),age NUMBER,

CONSTRAINT newstudent_ck check(age<25)

);

 

修改表

语法:

澳门新浦京娱乐游戏,ALERT TABLE table

{

ADD { field datatype | CONSTRAINT multified }

| DROP {COLUMN field | CONSTRAINT  multified }

| RENAME {COLUMN|CONSTRAINT} oldname TO newname

| MODIFY  field datatype

}

其中:

table 表示表的名称。

filed 表示要添加或删除列的名称

datatype 表示该列的数据类型及其长度。

oldname、newname 分别表示原名字和新名字

CONSTRAINT multified  表示添加或删除的约束

ADD 表示添加列或约束

DROP表示删除列或约束

MODIFY 表示修改列

RENAME 对列或约束进行重命名

示例:完成对表STUDENT的修改

添加主键约束:

ALTER TABLE  STUDENT   ADD CONSTRAINT STUDENT_PK  PRIMARY KEY
(STUDENT_ID);

添加一个新列age类型为NUMBER:

ALTER TABLE STUDENT   ADD AGE NUMBER;

修改name列的大小有VARCHAR2(10)改变为VARCHAR2(20):

ALTER  TABLE STUDENT  MODIFY  NAME VARCHAR2(20);

删除表:DROP TABLE STUDENT;

对表重命名:

RENAME  OLDTABLENAME  TO  NEWTABLENAME;

 

SQLPlus下清屏clear screen或者clea scre

—-创建表EMPLOYEES—-

CREATER TABLE EMPLOYEES (EMPID,NAME,AGE,GENDER,ADDRESS);

—-修改表—–

ALTER TABLE EMPLOYEES ADD CONSTRAINT AGE_PK CHECK(AGE>0);

修改约束:ALTER TABLE EMPLOYEES MODIFY AGE CHECK(AGE>18);

添加非空约束:ALTER TABLE EMPLOYEES MODIFY NAME CONSTRAINT NAME_NOTNULL
NOT NULL;

添加列(或字段):ALTER TABLE EMPLOYEES ADD JOB VARCHAR2(6) UNIQUE;

向字段JOB添加数据:UPDATE EMPLOYEES SET JOB=’Writer’ WHERE EMPID=1;

 

SQL语言基础

SQL中表达式由变量、运算符、常量组成。

变量一般就是表中的列名,常量为固定不变的数值符号,字符型和DATE常量需要用单引号括起来,数值型和二进制字符串不需要。

运算符:有算术运算符、关系运算符、逻辑运算符、通配运算符(通配运算符常和LIKE关键字配合使用)

 

运算符

含义

示例

=

等于

AuthorID=5

>

大于

Price>50

<

小于

Quantity<20

>=

大于等于

Age>=50

<=

小于等于

Price<=100

<>

不等于

Birthday<>‘1970-10-10’

%

任意长度的字符串

Email Like ‘%@%.com’

‘_’

任意一个字符

AuthorName Like ‘张_’

and

连接的两个条件表达式都为True,表达式结果才为True,否则为False

A > 90 and B >100

or

连接的两个条件表达式中有一个为True,则表达式结果就为True

A >90 or B >100

not

否定条件

not (A > 90)

运算顺序优先级:NOT>算术表达式>条件表达式>AND > OR.

数据操纵语言:

INSERT 命令:INSERT INTO TABLE [(COLUMN[,COLUMN,…..])] VALUES
(VALUE[,VALUE….]);

其中:“[]”代表可选的;

table 表示添加记录的表名

column[, column…]  表示添加数据的列名,如果有多列用逗号隔开

value[,value…] 添加的具体数据,如果有多个值用逗号隔开

DESC STUDENT;—–查看表的结构在SQL PLUS环境下。.

INSERT INTO 语句:

INSERT INTO STUDENT    VALUES
(3,’黄晓明’,’计算机信息与工程’,’08-9月-76’,1);

另一种写法:

INSERT INTO STUDENT (student_id,name,major,birthday,teacher_id)

 VALUES(3,’黄晓明’,’计算机信息与工程’,’08-9月-75′,1);

在执行INSERT语句时应注意以下问题:

1、值列表的个数必须与列名数目保持一致。

2、值列表中值的数据类型、精度要与对应的列类型保持一致。

3、可以不指定列名,但值列表中值的顺序应该与表中字段顺序保持一致。

4、添加的值必须符合表的约束条件,否则不能添加成功。

UPDATE命令:

语法:UPDATE TABLENAME  SET  COLUMN=VALUE,[,COLUMN=VALUE,…][WHERE
CONDITION];

其中:

1、column = value
为必选项,用于更新表中某列数据,在SET后面可以出现多个,只需用逗号隔开。

2、Where关键字是可选的,用来限定条件,如果Update语句不限定条件,表中所有数据行都将被更新。

注意事项:1、可以更新主键列数据,但应保证更新后的主键列数据不能出现重复信息,否则将更新失败!

2、可以更新表中外键列数据,但应保证新数据在主键表的中应事先存在。

DELETE 命令:

语法:DELETE  [FROM]  TABLE [WHERE CONDITION];

使用Delete删除数据时,不能删除主键值被引用的数据行。

事务是单个的工作单元。

如果某个事务成功,则在该事务中进行的所有数据更改均会提交,成为数据库中的永久组成部分。

如果事务遇到错误且必须取消或回滚,则所有数据更改均被清除。一个逻辑工作单元必须有ACID属性,只有这样才能成为一个事务。

1、数据库事务(Database Transaction)
,是指作为单个逻辑工作单元执行的一系列操作。

2、事务处理可以确保除非事务性单元内的所有操作都成功完成,否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元,可以简化错误恢复并使应用程序更加可靠。

3、事务只有在提交(COMMIT)后,对数据库的更改才可以永久保持,事务在回滚(ROLLBACK)后,将事务未提交的操作恢复到事务开始的状态。

事务的特征:原子性ATOMICITY、一致性CONSISTENCY、隔离性ISOLATION、持续性DURABILITY。

常用命令:

COMMIT:此命令用于提交并结束事务处理,只用使用COMMIT命令,才可以对数据库执行永久性操作;

ROLLBACK:
此命令用来撤销在当前事务中完成的操作,可以回滚整个事务处理,一遍撤销有SQL语句做出的所有修改,也可以将事务回滚到某个保存点,以回滚该保存点后的修改

SAVEPOINT :此命令保存点类似于标记,用于标记事务中可以应用回滚的点。

示例:

UPDATE STUENT SET BIRTHDAY= ’07-8月-80’ WHERE STUDENT_ID=1;

SAVEPOINT MARK1;

DELETE STUDENT  WHERE STUDENT_ID=1;

ROLLBACK  TO  SAVEPOINT MARK1;

COMMIT;

数据控制语言为用户提供权限控制命令。

Oracle用户权限有两种类型:

系统权限:允许用户执行某些数据库操作

对象权限:允许用户对某一个特定对象执行特定操作(SELECT   UPDATE  INSERT
DELETE ALL)

授权GRANT命令:

语法:GRANT   PRIVILEGES  ON  OBJECT_NAME TO  USERNAME;

示例:

将student表的select、update权限授予用户anbo

GRANT SELECT,UPDATE ON STUDENT TO  ANBO;

如果用’WITH  GRANT
POTION’授予用户权限,则接受该权限的用户可以将此权限授予其他用户。

GRANT  ALL ON  STUDENT TO ANBO   WITH  GRANT  OPTION;

REVOKE命令:撤销已授予用户的权限,使用REVOKE命令

示例:将已授权账户为ANBO的用户all 权限撤销:  REVOKE  ALL ON  STUDENT 
TO  ANBO;

GRANT  SELECT ON SCOTT.EMP TO
ANBO;    —–将scott.emp表的select权限授予anbo用户。

alter user scott account
unlock;          ———为scott账户开锁

alter user scott identified by orcl;       
  ——为scott账户重新设置密码;

DROP TABLE WANSHI.EMP;                               删除表

CREATE TABLE WANSHI.EMP AS SELECT * FROM SCOTT.EMP; -复制表

在SQLplus下切换用户 使用DISCON  和 CONN 更换账户

设置sqlplus模式显示行宽度(当前连接生效):

show linesize; –查看当前的linesize宽度

set linesize 300; –设置linesize宽度

在SQLPlus下设置输出宽度 SET LINESIZE NUMBER;  NUMBER为宽度像素。

设置sqlplus模式显示总行数(当前连接生效):

show pagesize; –查看目前的pagesize

set pagesize 300; –设置pagesize为300

 

           

SQL查询语句        

SELECT  [DISTINCT]  select_list      

FROM   table_name

[WHERE子句]

[ORDER BY  子句 [ASC | DESC]]

示例:

–查询数据表中部分的列:

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP;

—使用*查询所有列

SELECT * FROM EMP;

—使用AS 子句将列转别名

SELECT EMPNO AS 员工编号, ENAME AS 姓名, SAL AS 薪水, DEPTNO AS 部门编号
FROM EMP;

—使用||连接多个字段,合并成一列

SELECT ENAME ||’的薪水是’||SAL ||’元’ FROM EMP;

—使用DISTINCT关键字屏蔽重复数据

SELECT DISTINCT DEPTNO FROM EMP;

—-使用ROWID ROWNUM 伪列

SELECT ROWID, ENAME FROM EMP;

SELECT ROWNUM ,ENAME FROM EMP;

Oracle
中伪列就像一个表列,但是它并没有存储在表中,伪列可以从表中查询,但不能插入、更新和删除它们的值。常用的伪列有ROWID和ROWNUM。

1、ROWID
是表中行的存储地址,该地址可以唯一地标识数据库中的一行,可以使用 ROWID
伪列快速地定位表中的一行

2、ROWNUM 是查询返回的结果集中行的序号,可以使用它来限制查询返回的行数

查询中的条件

1、 使用WHERE 过滤部分行数据

SELECT * FROM EMP WHERE ENAME=’MILLER’;

2、 使用IS NULL 查询空数据

SELECT * FROM EMP WHERE COMM IS NULL;

3、 LIKE进行模糊查询  通常与通配符配合使用

WHERE<列名> [NOT] LIKE <字符表达式>

SELECT * FROM EMP WHERE ENAME LIKE ‘%M%’;

4、 WHERE <列名> IN <[常量列表]>

其中:<常量列表>中各常量值用逗号隔开

示例:SELECT * FROM EMP WHERE DEPTNO IN (10,20);

等价于:SELECT * FROM EMP WHERE DEPTNO= 10 OR DEPTNO=20;

5、 WHERE <列名> [NOT] BETWEEN <起始表达式> AND
<结束表达式>;

注意:起始表达式和结束表达式不能颠倒。

示例:SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

SELECT * FROM emp   WHERE hiredate

between to_date(‘1981-01-01′,’YYYY-MM-DD’)

and to_date(‘1982-01-01′,’YYYY-MM-DD’);

6、排序:使用关键字 ORDER BY 子句 默认是升序   使用DESC为降序,ASC 为升序

      SELECT * FROM EMP ORDER BY SAL ASC,COMM DESC;

日期函数:

函数

说明

示例

输出结果

ADD_MONTHS

返回给指定的日

期加上指定的月

数后的日期值

SELECT  ADD_MONTHS(sysdate,2) FROM dual

13-11月-10

MONTHS_BETWEEN

返回两个日期之

间的月数

SELECT   MONTHS_BETWEEN(’08-5月-10’, ’08-5月-09’) FROM   dual;

12

LAST_DAY

返回指定日期当

月的最后一天

SELECT  LAST_DAY(SYSDATE)  FROM dual;

30-9月-10

NEXT_DAY

返回指定的下一

个星期几的日期

SELECT   NEXT_DAY(SYSDATE,’星期二’) FROM dual;

14-9月-10

 

 

字符函数:

函数

说明

示例

输出结果

INITCAP(char)

首字母大写

select   initcap(‘hello’) from dual;

Hello

LOWER(char)

转换成小写

select   lower(ename) from emp where empno=7369

smith

UPPER(char)

转换成大写

select   upper(‘jack’) from dual;

JACK

LTRIM(char,char)

左裁剪

select   ltrim( ‘abcdefgh’,’ab’) from dual;

cdefgh

RTRIM(char,char)

右裁剪

select   rtrim( ‘abcdefgh’,’gh’) from dual;

abcdef

TRIM(char   FROM trim_char)

裁剪

SELECT TRIM   ( ‘9’ FROM ‘99998769789999’) FROM dual

876978

TRANSLATE(char,from,to)

按字符翻译

select   translate(‘jack’,’abcd’ ,’1234′) from dual;

j13k

REPLACE(char,from,to)

按字符串翻译

select   replace(‘jack and jue’ ,’j’,’bl’) from dual;

black and   blue

INSTR(char,substr)

查找字符串位置

select   instr (‘HelloWorld’,’o’) from dual;

5

SUBSTR(char,pos,len)

取字符串

select   substr(‘I am tiger.’,6,5) from dual;

tiger

CONCAT(char1,char2)

连接字符串

select   concat (‘Hello’,’ world’) from dual;

Hello world

LENGTH(str)

返回字符串的长度

select   length (ename) from emp where empno=7369;

5

数字函数

函数

说明

示例

输出结果

ABS(n)

取绝对值

select abs(-25) from dual;

25

CEIL(n)

向上取整

select ceil(44.1) from dual;

45

FLOOR(n)

向下取整

select floor(44.8) from dual;

44

POWER(m,n)

m的n次幂

select power(3,2) from dual;

9

MOD(m,n)

取余数

select mod(10,3) from dual;

1

ROUND(m,n)

四舍五入

select round(100.256,2) from dual;

100.26

TRUNC(m,n)

截断

select trunc(100.256,2) from dual;

100.25

SQRT(n)

取算术平方根

select sqrt(4) from dual;

2

转换函数

函数

说明

示例

输出结果

TO_CHAR

将日期或数字转换成字符

SELECT   TO_CHAR(sysdate,’YYYY"年"MM"月"DD"日"’)   FROM dual

2010年09月13日

TO_DATE

将字符转换

成日期

SELECT   TO_DATE(‘2007-12-06’ , ‘yyyy-mm-dd’) FROM dual;

06-12月-07

TO_NUMBER

将字符转换

成数字

SELECT   SQRT(TO_NUMBER(‘100’)) FROM dual;

10

 

分页查询和多表连接查询

聚合函数:对表一组记录进行操作,每组只返回一个值。(行==记录)

SUM()函数: SELECT SUM(SAL) FROM EMP;

MIN()函数:SELECT MIN(SAL) FROM EMP;

MAX()函数: SELECT MAX(SAL) FROM EMP;

MAX和MIN除了计算数值列,还可以用于计算字符型以及日期时间类型数据列

AVG()函数: SELECT AVG(SAL) FROM EMP;

COUNT()函数和COUNT(*)函数: SELECT COUNT(COMM) FROM EMP;

           COUNT(列名):返回指定列非空值个数。

           COUNT()函数 获取每组中非空值的个数

           SELECT COUNT(*) FROM EMP; 获取每组中所有数据的个数;

分组统计:该功能使用GROUP BY 子句实现

语法:

SELECT 字段列表 [聚合函数] FROM 表 

[WHERE 条件] [GROUP BY <字段列表>][HAVING 条件];

注意: SELECT语句中除了聚合函数外所有列,应写在GROUP BY
语句后面,否则将出现错误;

字段列表中可以有多个字段,各字段用逗号隔开。

示例:SELECT DEPTNO,ROUND(AVG(SAL),4) FROM EMP GROUP BY DEPTNO;

SELECT DEPTNO,ROUND(AVG(SAL),4) AS “平均” FROM EMP GROUP BY DEPTNO;

SELECT DEPTNO,ROUND(AVG(SAL),4)平均 FROM EMP GROUP BY DEPTNO;

SELECT DEPTNO,ROUND(AVG(SAL),4) AS 平均 FROM EMP GROUP BY DEPTNO;

HAVING子句的使用:对分组后的数据进行筛选。

示例:

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2000;

基本查询语法总结:

SELECT 字段列表 [聚合函数] FROM 表 [WHERE 条件] [GROUP BY
<字段列表>]

           [HAVING 条件] [ORDEY BY 字段列表 ASC|DESC];

子查询:就是在查询语句内的查询语句,就是在SELECT语句中还有SELECT语句。

语法:SELECT <列名> FROM 表 (SELECT 子句)

           WHERE 子句(SELECT 子句)

                 GROUP BY 子句

                      HAVING 子句(SELECT 子句)

                 [ORDEY BY 排序列];

示例:SELECT * FROM EMP

WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC=’NEW YORK’);

示例:SELECT ROWNUM,E.* FROM EMP E WHERE ROWNUM<=10;

     
注意:ROWNUM条件只能是<=或<某一个值,不能使用>=或>符号;

示例:SELECT * FROM (SELECT ROWNM RNUM,E.* FROM EMP E )

WHERE RNUM BETWEEN 6 AND 10;

WHERE 后面的RNUM不能写成 RNUM <= 10 AND RNUM >=6;

连接查询:多表连接查询
是将多个表中的数据进行关联组合,从中获取所需的数据信息。

在Oracle中在做连接查询时,首先会对关联的表做笛卡尔积得操作。

笛卡尔积是由两个关系相乘而来,也可理解为两个表的乘积,结果依然是一张表。

                
多表连接查询可以分为:交叉连接、等值连接、外连接、子连接等等

 

示例:SELECT e.ename,e.deptno,d.deptno, d.loc

FROM emp e,dept d

where e.deptno=d.deptno;

 

左外连接: SELECT <列名> FROM 左表  LEFT  [OUTER]
JOIN 
右表

ON 左表.列名  条件运算符  右表.列名

[WHERE 条件]

[ORDER BY  排序列]

示例:SELECT e.ename,e.sal,d.dname FROM emp e LEFT OUTER JOIN dept d

ON e.deptno=d.deptno;

右外连接:SELECT <列名>FROM左表  RIGHT  [OUTER]
JOIN 
右表

ON左表.列名  条件运算符  右表.列名

[WHERE 条件]

[ORDER BY  排序列]

示例:SELECT e.ename,e.sal,d.dname FROM emp e RIGHT OUTER JOIN dept
d

ON e.deptno=d.deptno;

子连接:一种比较特殊的情况,查询的字段全部来自一张表

同一张表两次加载到内存中,形成理论上的两张表完成连接

示例:SELECT  distinct e2.empno,e2.ename FROM emp e1 ,emp e2   WHERE
e1.mgr=e2.empno ;

这里少了一章  同义词 序列  视图  索引

数据库对象

同义词是数据库对象的一个别名,可以是表、视图、序列、过程、函数、程序包及其他同义词等。其作用是简化SQL语句,隐藏对象,提供对对象的公共访问等等

使用同义词前要确保用户拥有访问对象的权限。

创建同义词的语法:

CREATE  [OR REPLACE]  [PUBLIC]SYNONYM synonym_name FOR OBJECT;

其中:SYNONYM 是创建同义词的名称;OBJECT 表示Oracle的对象;

PUBLIC 表示常见共有同义词; OR REPLACE
表示在同义词存在的情况下替换该同义词

私有同义词是普通用户创建的,并且只有创建该私有同义词的用户能使用。且不可同当前模式的对象名称相同;前提是用户要拥有CREATE
SYNONSYM系统权限。

例:scott授权给用户zhang创建emp表的同义词权限

首先登陆scott用户的数据库,执行命令:GRANT ALL ON EMP TO ZHANG;

然后登陆zhang用户,执行命令:CREATE SYNONYM  EMP FOR SCOTT.EMP;

在执行命令:SELECT * FROM EMP;访问scott用户的emp表了。

公有同义词:由数据库管理员创建。

以system用户身份登陆数据库,执行命令:CREATE PUBLIC  SYNONYM
EMP_NEW_NAME FOR SCOTT.EMP;

在将公有同义词EMP_NEW_NAME访问权限授权给zhang用户:GRANT ALL ON
EMP_NEW_NAME TO ZHANG;

在以zhang的身份登陆数据库访问:SELECT * FROM EMP_NEW_NAME;

删除同义词要有相应的权限,删除命令是:DROP [PUBLIC] SYNONYM
synonym_name;

序列:Oracle提供的一个对象,
用来生成唯一、连续的整数。序列通常用来自动生成主键或唯一的值。语法:

CREATE SEQUENCE  SQE_NAME  [START WITH INTEGER] [INCREMENT BY
INTEGER]

[MAXVALUE INTEGER | NOMAXVALUE] [MINVALUE INTEGER | NOMINVALUE]

    [CYCLE | NOCYCLE] [CACHE INTEGER | NOCACHE]

创建和访问序列:序列创建好后,被调用为某些表生成序列号,序列值的生成和查询需要和NEXTVAL和CURRVAL关联使用;NEXTVAL产生并返回下一个序列值,CURRVAL只会返回当前序列的值;对于序列的第一个操作必须使用NEXTVAL。

示例:CREATE SEQUENCE std_seq

    START WITH 1

    INCREMENT BY 10

    MAXVALUE 2000

    MINVALUE 1

    CYCLE

    CACHE 10;

例如:SELECT STD_SEQ.NEXTVAL  FROM DUAL;

SELECT STD_SEQ.CURRVAL  FROM DUAL;

更改序列:除了不能修改序列的起始值外,其他参数都可以修改。

语法: ALTER  SEQUENCE  SQE_NAME   [INCREMENT  BY INTEGER] [
MAXVALUE INTEGER | NOMAXVALUE]

[MINVALUE INTEGER | NOMINVALUE]  [CYCLE | NOCYCLE] [CACHE INTEGER |
NOCACHE];

删除序列:DROP SEQUENCE  SQE_NAME;

视图:可以被看成虚拟表或者存储查询,创建视图所依据的表称为“基表”。

语法:

CREATE [OR REPLACE] [FORCE] VIEW          —-OR REPLACE
如果视图存在,将重新创建该视图

    view_name [(alias[, alias]…)]
      —-FORCE:无论基表是否存在,都将创建视图

    AS select_statement                    —-NOFORCE:
默认值,当基表存在时才创建视图

  [WITH CHECK OPTION]       —-WITH CHECK OPTION
指定只能插入或更新视图可以访问的行

  [WITH READ ONLY];         —-WITH READ ONLY
确保不能在此视图上执行任何DML操作

如果是一个简单视图,仅涉及一张表,且SELECT子句不包含函数表达式,则能通过该视图对表的数据执行DML操作。

如果视图中包括连接运算符、DISTINCT、集合运算符、聚合函数、GROUP BY
等的复杂视图,不能对该视图执行DML操作。

CREATE OR REPLACE VIEW EMP_DEPT_VIEW

AS

SELECT EMPNO,ENAME,HIREDATE,DNAME FROM EMP E,DEPT D WHERE
E.DEPTNO=D.DEPTNO;

在视图上也可以使用修改数据的DML语句,如INSERT、UPDATE和DELETE

视图上的DML语句有如下限制:

只能修改一个底层的基表

如果修改违反了基表的约束条件,则无法更新视图

如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY
子句,则将无法更新视图

如果视图包含伪列或表达式,则将无法更新视图

删除视图:DROP  VIEW VIEW_NAME;

索引:是与表关联的可选机构,就像书的目录一样。能够快速的访问数据。它只影响执行的速度。

语法:

CREATE INDEX IND_NAME ON TABLE_NAME (COLUMN_LIST) [TABLESPACE
TBS_NAME]

IND_NAME:指所创建索引的名称。

TABLE_NAME:为之创建索引的表名

COLUMN_LIST:在其上创建索引的列名列表,可以基于多个列创建索引

TBS_NAME 为索引指定表空间。

索引有多种类型:唯一索引、组合索引、反向键索引、位图索引、函数索引。

唯一索引可以确保在定义索引的列中,表的任意两行的值都不相同。

语法:CREATE  UNIQUE INDEX  IND_NAME ON TABLE_NAME(COLUMN);

组合索引:在表中多个列上创建索引,组合索引中列的顺序是任意的,不必是表中相邻的列。

语法: CREATE  INDEX IND_NAME  ON TABLE_NAME(COLUMN1,COLUMN2,…);

反向键索引:

反向键索引反转索引列键值的每个字节

通常建立在值是连续增长的列上,使数据均匀地分布在整个索引上

创建索引时使用REVERSE关键字

语法:CREATE  INDEX IND_NAME  ON TABLE_NAME(COLUMN) REVERSE;

位图索引:优点是适合用于低基数列,不直接存储ROWID,而是存储字节位到ROWID的映射。

语法:CREATE BITMAP  INDEX  IND_NAME  ON TABLE_NAME (COLUMN)  ;

函数索引:基于一个或多个列上的函数或表达式创建的索引

表达式中不能出现聚合函数

不能在LOB类型的列上创建

创建时必须具有 QUERY REWRITE 权限

示例: CREATE  INDEX EMP_INDEX  ON EMP( NVL(SAL,0) + NVL(COMM,0) );

索引中的分区:Oracle允许对索引分区,索引分区可存储在不同的表空间中。

共两个类型:局部分区索引和全局分区索引。

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图