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; |
| |
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; |