Oracle1

1.1         常用命令

命令	说明
show all	查看系统所有变量值
show user	显示当前连接用户
show error	显示错误
desc 表名	显示表的结构；如：desc emp
/* */ --	多行注释 单行注释
/	执行缓冲区中的语句
ed	打开默认编辑器，Windows系统中默认是notepad.exe，把缓冲区中最后一条SQL语句调入afiedt.buf文件中进行编辑（如果提示没有afiedt.buf请使用管理员身份打开SLQ Plus）；常用于语句比较长需要修改时。
spool 文件地址 spool 文件地址 append spool off	假脱机命令；将命令行的内容（从设置后开始的命令行内容）记录到文本。添加append的意思是在原有的文本内容上追加后续的命令行的内容；需要注意的是所有的这些内容都将在spool off之后才记录。如： spool d:\wxz\wxz.txt spool d:\wxz\test.sql append spool off
clear screen 或者 host cls	清屏
exit	退出SQL Plus

 

2              表空间

表空间是数据库中最大的逻辑单位，Oracle数据库采用表空间将相关的逻辑组件组合在一起，一个Oracle数据库至少包含一个表空间。每个表空间由一个或多个数据文件组成，一个数据文件只能与一个表空间相联系。

在每一个数据库中都有一个名为SYSTEM的表空间，即系统表空间，该表空间是在创建数据库或数据库安装时自动创建的，用于存储系统的数据字典表、程序单元、过程、函数、包和触发器等。

 

2.1         表空间类型

永久性表空间：一般保存表、视图、过程和索引等的数据

临时性表空间：只用于保存系统中短期活动的数据

撤销表空间：用来帮助回退未提交的事务数据

2.2         操作与运用

创建表空间

【语法】 CREATE TABLESPACE 表空间名    DATAFILE '数据文件路径' SIZE 大小    [AUTOEXTEND ON] [NEXT 大小]    [MAXSIZE 大小];   【说明】[]里面内容可选项；数据文件路径中若包含目录需要先创建 SIZE为初始表空间大小，单位为K或者M AUTOEXTEND ON 是否自动扩展 NEXT为文件满了后扩展大小 MAXSIZE为文件最大大小，值为数值或UNLIMITED（表示不限大小）   【示例】 CREATE TABLESPACE xzmd_wxz    DATAFILE 'd:\oracledata\wxz01.dbf' SIZE 10M    AUTOEXTEND ON;

 

查询表空间

--管理员角色查看表空间 SELECT file_name,tablespace_name,bytes,autoextensible FROM dba_data_files WHERE tablespace_name=' xzmd_wxz ';

 

修改表空间

【语法】 ALTER TABLESPACE 表空间名    ADD DATAFILE '文件路径' SIZE 大小    [AUTOEXTEND ON] [NEXT 大小]    [MAXSIZE 大小];   【示例】 ALTER TABLESPACE xzmd_wxz ADD DATAFILE'd:\oracledata\wxz02.DBF'SIZE 5M    AUTOEXTEND ON;

 

删除表空间

【语法】 DROP TABLESPACE 表空间名; DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES;   【说明】 第一个删除语句只删除表空间；第二个删除语句则删除表空间及数据文件   【示例】 DROP TABLESPACE xzmd_wxz; DROP TABLESPACE xzmd_wxzINCLUDINGCONTENTSAND DATAFILES;

 

3              数据库用户

3.1         系统常见用户

 

用户	说明
sys	超级用户，主要用来维护系统信息和管理实例，以SYSDBA或SYSOPER角色登录。密码为在安装时设置的管理口令，如一般设置为：orcl
system	默认的系统管理员，拥有DBA权限，通常用来管理Oracle数据库的用户、权限和存储，以Normal方式登录。密码为在安装时设置的管理口令，如一般设置为：orcl
scott	示范用户，使用users表空间。一般该用户默认密码为tiger

 

3.2         用户管理

Oracle中有个模式（schema）的概念，它是用户的所有数据库对象的集合；一般在创建用户的同时会自动创建一个这样的模式，名称和用户名称一样。

3.2.1  查询系统用户

select * from all_users; 或 select * from dba_users; --更详细的用户信息

 

3.2.2  解锁用户

【语法】 ALTER USER 用户名 ACCOUNT UNLOCK;   【示例】解锁hr用户 alter user hr account unlock;

 

3.2.3  创建用户

【语法】 CREATE USER 用户名 IDENTIFIED BY 密码    DEFAULT TABLESPACE 表空间;   【示例】 CREATE USER wxzIDENTIFIEDBY wxz    DEFAULT TABLESPACE xzmd_wxz    TEMPORARY TABLESPACE temp;

 

3.2.4  修改用户密码

【语法】 ALTER USER 用户名 identified by 密码   【示例】 ALTER USER wxzidentifiedby www;

 

3.2.5  删除用户

【语法】 DROP USER 用户名 CASCADE;   【示例】 DROP USER wxz CASCADE;

 

4              DCL数据控制语言

4.1         授予

【语法1】 GRANT 角色权限（角色）[,角色权限] TO 用户;   【示例1】 --授予CONNECT和RESOURCE两个角色 GRANT connect,resourceTO wxz;   【备注】使用如下语句可以查看resource角色下的权限 SELECT * FROM DBA_SYS_PRIVSWHERE GRANTEE='RESOURCE'     【语法2】 GRANT 操作 ON 模式.对象  TO 用户;   【示例2】 --允许用户查看、更新 EMP表中的记录 GRANT select,updateON SCOTT.empTO  wxz;     --查看当前用户的系统权限 select *from user_sys_privs;   --查看当前用户的对象权限 select *from user_tab_privs;   --查看当前用户的所有角色 select *from user_role_privs;

 

4.2         撤销

 

【语法1】 REVOKE 角色权限（角色）[,角色权限] FROM 用户;   【示例1】 --撤销CONNECT和RESOURCE两个角色 REVOKE connect,resourceFROM wxz;     【语法2】 REVOKE 操作 ON 模式.对象  FROM 用户;   【示例2】 --撤销用户查看、更新 EMP表中的记录的操作 REVOKE select,updateON SCOTT.empFROM  wxz;

 

5              DDL数据定义语言

5.1         创建表

 

【语法】 CREATE TABLE <table_name>( column1 DATATYPE [NOT NULL] [PRIMARY KEY], column2 DATATYPE [NOT NULL], ... [constraint <约束名> 约束类型 (要约束的字段) ... ] );   【说明】 DATATYPE --是Oracle的数据类型 NUT NULL --可不可以允许资料有空的（尚未有资料填入） PRIMARY KEY --是本表的主键 constraint --是对表里的字段添加约束.(约束类型有             Check,Unique,Primary key,not null,Foreign key);   【示例】 create table t_student( s_id number(8)PRIMARYKEY, s_name varchar2(20)notnull, s_sex varchar2(8), clsid number(8), constraint u_1 unique(s_name), constraint c_1 check (s_sex in('MALE','FEMALE')) );   --从现有的表创建表及复制其数据 【语法】 CREATE TABLE <table_name> as <SELECT 语句>   【示例】 create table empasselect * from scott.emp;   create table empasselect empno,enamefrom scott.emp--表结构只有empno和ename两个字段及该两字段对应的数据   --如果只复制表的结构不复制表的数据则: create table empasselect * from scott.emp where 1=2;

 

5.2         修改表

 

【语法1】向表中添加新字段 ALTER TABLE <table_name> ADD (字段1 类型 [NOT NULL], 字段2 类型 [NOT NULL] ... );   【示例1】 alter table t_studentadd(s_age number(3),s_addressvarchar2(20));   【语法2】修改表中字段 ALTER TABLE <table_name> MODIFY(字段1 类型,字段2 类型 ... );   【示例2】 alter table t_studentmodify(s_namevarchar2(50),s_addressvarchar2(100));     【语法3】删除表中字段 ALTER TABLE <table_name> DROP(字段1,字段2... );   【示例3】 alter table t_studentdrop(s_age,s_address);     【语法4】修改表字段名称 ALTER TABLE <table_name> RENAME COLUMN 原字段名称 TO 新字段名称;   【示例4】 alter table t_studentrenamecolumn s_idto s_no;

 

5.3         删除表

 

【语法1】 --删除表结构及数据（删除后可在回收站查看并恢复） DROP TABLE <table_name>; --删除表结构及数据（删除后不可在回收站查看并恢复） DROP TABLE <table_name> PURGE;   【示例1】 drop table t_student;

 

5.4         回收站

5.4.1  查看回收站

 

--查看回收站 show recyclebin; 或select* from recyclebin;

 

5.4.2  清空回收站

 

--清空回收站 purge recyclebin;

 

5.5         oracle数据类型

 

数据类型	描述
VARCHAR2(size)	可变长度的字符串,其最大长度为size个字节;size的最大值是4000,而最小值是1;你必须指定一个VARCHAR2的size;
NVARCHAR2(size)	可变长度的字符串,依据所选的国家字符集,其最大长度为size个字符或字节;size的最大值取决于储存每个字符所需的字节数,其上限为4000;你必须指定一个NVARCHAR2的size;
NUMBER(p,s)	精度为p并且数值范围为s的数值;精度p的范围从1到38;数值范围s的范围是从-84到127; 例如:NUMBER(5,2) 表示整数部分最大3位，小数部分为2位；NUMBER(5,-2) 表示数的整数部分最大为7其中对整数的倒数2位为0,前面的取整。NUMBER 表示使用默认值,即等同于NUMBER(5);
DATE	有效日期范围从公元前4712年1月1日到公元后9999年12月31日
RAW(size)	长度为size字节的原始二进制数据,size的最大值为2000字节；你必须为RAW指定一个size;
LONG RAW	可变长度的原始二进制数据，其最长可达2G字节;
CHAR(size)	固定长度的字符数据,其长度为size个字节;size的最大值是2000字节,而最小值和默认值是1;
NCHAR(size)	也是固定长度。根据Unicode标准定义
CLOB	一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最大为4G字节
NCLOB	一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最大为4G字节;储存国家字符集
BLOB	一个二进制大型对象;最大4G字节
BFILE	包含一个大型二进制文件的定位器,其储存在数据库的外面；使得可以以字符流I/O访问存在数据库服务器上的外部LOB;最大大小为4G字节.

 

6              DML数据操作语言

6.1         新增

 

【语法1】 INSERT INTO table_name (column1,column2,...) VALUES ( value1,value2, ...);   【示例1】 insert into emp(empno,ename)values(1111,'wxzz');     【语法2】 INSERT INTO <table_name> <SELECT 语句>;   【示例2】 create table t1asselect * from emp where1=2; insert into t1select* from emp where sal>2000;

 

6.2         修改

 

【语法1】 UPDATE table_name SET column1=new value,column2=new value,... WHERE <条件>;   【示例1】 update emp set sal=3000where ename='wxzz';

 

6.3         查询

6.3.1  伪表dual

DUAL是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。以用它来做很多事情，如：

1. 查看当前用户

select user from dual;

 

2. 用来调用系统函数

--查询系统的当前时间并格式化 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;

 

3. 得到序列的下一个值或当前值

--获得序列seq的下一个值 select seq.nextval from dual;   --获得序列seq的当前值   select seq.currval from dual;

 

4. 可以用做计算器

select 2*8from dual;

 

6.3.2  伪列rowid

rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录，同一条记录在不同查询中对应的rowid相同。

【用法】 SELECT ROWID,字段名... FROM 表名;   【示例】 select rowid, emp.*from emp;

 

6.3.3  伪列rownum

rownum是根据sql查询出的结果给每行分配一个逻辑编号；每次的查询都会有不同的编号。编号从1开始。

【用法】 SELECT ROWNUM,字段名... FROM 表名;   【注意】 ROWNUM 不能使用大于号“>” 即 select rownum, emp.* from emp where rownum > 2 是不对的，没有任何结果   【示例】 select rownum, emp.*from emp;   /* 关于分页：由于不能使用>，所以为了达到分页目的得如下执行；如获取第2页数据（每页3条）*/ select * from (selectrownum r,emp.*from empwhere rownum < 7)where r> 3;     /* 关于排序：由于rownum是查询结果的行编号，排序后这个编号便有可能被打乱，如果需要该编号和排序的结果列表序号保持一致可以如下执行*/ select rownum,t.*from(select empno,enamefrom emporder by empno desc) t;