oracle命令二

6数据库用户
6.1系统常见用户

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

6.2用户管理
Oracle中有个模式（schema）的概念，它是用户的所有数据库对象的集合；一般在创建用户的同时会自动创建一个这样的模式，名称和用户名称一样。
6.2.1查询系统用户
select * from all_users;
或
select * from dba_users; --更详细的用户信息

6.2.2解锁用户
【语法】
ALTER USER 用户名 ACCOUNT UNLOCK;

【示例】解锁hr用户
alter user hr account unlock;

6.2.3创建用户
【语法】
CREATE USER 用户名 IDENTIFIED BY 密码
DEFAULT TABLESPACE 表空间;

【示例】
CREATE USER itcast IDENTIFIED BY itcast
DEFAULT TABLESPACE itcast_ts
TEMPORARY TABLESPACE temp;

6.2.4修改用户密码
【语法】
ALTER USER 用户名 identified by 密码

【示例】
ALTER USER itcast identified by it;

6.2.5删除用户
【语法】
DROP USER 用户名 CASCADE;

【示例】
DROP USER itcast CASCADE;

7DCL数据控制语言
7.1授予
【语法1】
GRANT 角色权限（角色）[,角色权限] TO 用户;

【示例1】
–授予CONNECT和RESOURCE两个角色
GRANT connect, resource TO itcast;

【备注】使用如下语句可以查看resource角色下的权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=‘RESOURCE’

【语法2】
GRANT 操作 ON 模式.对象 TO 用户;

【示例2】
–允许用户查看、更新 EMP 表中的记录
GRANT select,update ON SCOTT.emp TO itcast;

–查看当前用户的系统权限
select * from user_sys_privs;

–查看当前用户的对象权限
select * from user_tab_privs;

–查看当前用户的所有角色
select * from user_role_privs;

7.2撤销

【语法1】
REVOKE 角色权限（角色）[,角色权限] FROM 用户;

【示例1】
–撤销CONNECT和RESOURCE两个角色
REVOKE connect, resource FROM itcast;

【语法2】
REVOKE 操作 ON 模式.对象 FROM 用户;

【示例2】
–撤销用户查看、更新 EMP 表中的记录的操作
REVOKE select,update ON SCOTT.emp FROM itcast;

8DDL数据定义语言
8.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) PRIMARY KEY,
s_name varchar2(20) not null,
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 emp as select * from scott.emp;

create table emp as select empno,ename from scott.emp --表结构只有empno和ename两个字段及该两字段对应的数据

–如果只复制表的结构不复制表的数据则:
create table emp as select * from scott.emp where 1=2;

8.2修改表

【语法1】向表中添加新字段
ALTER TABLE <table_name> ADD (字段1 类型 [NOT NULL],
字段2 类型 [NOT NULL] … );

【示例1】
alter table t_student add (s_age number(3),s_address varchar2(20));

【语法2】修改表中字段
ALTER TABLE <table_name> MODIFY(字段1 类型,字段2 类型 … );

【示例2】
alter table t_student modify(s_name varchar2(50),s_address varchar2(100));

【语法3】删除表中字段
ALTER TABLE <table_name> DROP(字段1,字段2… );

【示例3】
alter table t_student drop(s_age,s_address);

【语法4】修改表字段名称
ALTER TABLE <table_name> RENAME COLUMN 原字段名称 TO 新字段名称;

【示例4】
alter table t_student rename column s_id to s_no;

8.3删除表

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

【示例1】
drop table t_student;

8.4回收站
8.4.1查看回收站

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

8.4.2清空回收站

–清空回收站
purge recyclebin;

8.5oracle数据类型

数据类型 描述
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);

LONG 可变长度的字符数据,其长度可达2G个字节;
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字节.

9DML数据操作语言
9.1新增

【语法1】
INSERT INTO table_name (column1,column2,…)
VALUES ( value1,value2, …);

【示例1】
insert into emp (empno,ename) values(1111,‘itcast’);

【语法2】
INSERT INTO <table_name> <SELECT 语句>;

【示例2】
create table t1 as select * from emp where 1=2;
insert into t1 select * from emp where sal>2000;

9.2修改

【语法1】
UPDATE table_name SET column1=new value,column2=new value,…
WHERE <条件>;

【示例1】
update emp set sal=3000 where ename=‘itcast’;

9.3查询
9.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*8 from dual;

9.3.2伪列rowid
rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录，同一条记录在不同查询中对应的rowid相同。
【用法】
SELECT ROWID,字段名… FROM 表名;

【示例】
select rowid, emp.* from emp;

9.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 (select rownum r,emp. from emp where rownum < 7) where r > 3;

/* 关于排序：由于rownum是查询结果的行编号，排序后这个编号便有可能被打乱，如果需要该编号和排序的结果列表序号保持一致可以如下执行*/
select rownum,t.* from (select empno,ename from emp order by empno desc) t;

（每页2条数据，页码从1开始，所以起始行的行号为 (页码-1)每页长度+1=3,终止行的行号为 页码每页长度=4）：