常用oracle命令

一、查看系统表：select * from dual;

二、显示当前日期： select sysdate from dual;

select to_char(hiredate, 'yyyy"年"MM"月"dd"日" HH24:mm:ss') as time from emp;

select to_char(sysdate, 'yyyy"年"MM"月"dd"日" HH:mm:ss') as time from dual;

三、显示当前登录用户：show user; 或 select user from dual;

四、连接用户：conn system/manager;

五、查看表的完整结构

格式：desc 表名;

1. desc dual
2. desc user_tab_columns

六. 查看当前用户拥有哪些表：select * from tab;

常用工具

一、sqlplus(cmd)

二、isqlplus

三、Oracle 自带的 SQL Plus

四. 设置事务自动提交: set autocommit on|off;

三步曲       

一、建空间

详细步骤：
1. create tablespace 空间名
2. datafile '存放目录路径'
3. size 数据文件大小 m
//(可选)4. autoextend on/off (启用或者禁用数据文件)

二、建用户

详细步骤：
1. create user 用户名
2. identified by 用户密码
3. default tablespace 空间名
4. temporary tablespace temp (临时空间名一般用默认的 temp , 当然你也可以自己指定)

三、授权

详细步骤：
1. grant connect to 用户 (为用户授予登陆系统的权限)
2. grant resource to 用户 (为用户授予操作表空间的权限。例如创建表、序列、视图等)

说明：

1. 勿忘 commit !
2. 每个用户都有一个默认的系统表空间（system）和临时表空间（temp）。
3. 不能自己给自己授权。
4. 授于用户很多权限: grant create any table to 用户
5. 更改用户密码: alter user 用户名 identified by 新密码
6. 删除用户: drop user 用户名 当用户拥有表空间(模式)时,必须使用 cascade 关键字进行删除用户.
7. 授权 : grant connect/resource to 用户  撤消: revoke connect/resource from 用户
8. 授予用户对某张表的操作权限: grant select/update/delete/insert on 表名 to 用户  撤消: revoke select/update/delete/insert on 表名 from 用户
9. 获得有关数据库的用户信息，包括用户名，加密后的口令，表空间，配置文件，创建日期和锁定状态等 : select * From sys.user_users; 

建表
一、格式
1. create table 表名
  (
    列名 数值类型 [约束] [是否允许为空],
    ......
  );

说明：
1. 在同一个空间中不能出现相同的表名
2. 同一个表中不能出现相同的列名
2. 列与列之间必须用 ',' 分开,且最后一列不用 ','

修改表
一、格式
1. 更改现有表中列的定义：alter table 表名 modify (列名 数值类型);

2. 向现有表是添加新列：alter table 表名 add (列名 数值类型);

3. 删除现有表中的列：alter table 表名 drop column 列名;

4. 利用现有的表创建新表: create table 新表 as select * from 现有的表
  这样的话新表不仅拥有现有表的结构,还可拥有现有表中所有的记录,即全盘拷贝.
  你也可以只要现有表的结构,而不要现有表的记录.
  例如:
create table 新表 as select * from 现有的表 where 1=2
create table 新表(列名1, 列名2, 列名3...) as select id, name, sex...) from 现有的表

说明：
1.添加多列要用 ',' 隔开

删除表
一、格式
1. drop table 表名

重命表(也重命名视图，序列等)
一．格式
1. rename 旧表名 to 新表名;

重命名表
格式:
alter table 旧表名 rename to 新表名;

学会用数据操纵语言(DML){insert update delete select}

一、格式
1. insert into 表名(列名1,列名2,...) values(列名1的值,列名2的值...);
说明: 列名的个数和类型必须和指定的值的个数的类型相一致!

2. update 表名 set 列1=值,列2=值 ......[where 列=值];
说明: 在实际的应用中我们会加上 where 条件.要不然它会更新我们表中的所有行.所以我们只是更新指定的行.

3. delete from 表名 [where where 列=值] 或者 truncate table 表名(不能加条件)
说明: 在实际的应用中我们会加上 where 条件.要不然它会删除我们表中的所有行.所以我们只是删除指定的行.

4. select * from 表名 [where where 列=值] 或者 select 列1,列2 from 表名 [where where 列=值]
说明: 在实际的应用中我们也会加上 where 条件.要不然它会查询出我们表中的所有行.所以我们只是查询指定的行.

5. 插入来自其它表的记录(但前提是现有的表必须存在且现有的表结构必须与其它表结构相同)
  insert into 现有的表 select * from 其它的表
或者
  insert into 现有的表(列1,列2)  select 列1,列2 from 其它的表

6. 选择无重复的行: select distinct 列名 from 表名

7. 使用别名: 例如 select id as 编号,name as 姓名,pwd as 密码 from userinfo;

8. 伪列: rowid , rownum

一. 使用 distinct 关键字选择无重复的记录

1. select distinct name from userinfo;

2. select count(distinct name) from userinfo;

说明：在使用 distinct 关键字选择无重复的记录的时候，筛选条件是以整张表为标准的。

二. 使用列别名:

1. select id 编号, name 姓名 from userinfo;

2. select id as 编号, name as 姓名 from userinfo;

3. select id as "编号", name as "姓名" from userinfo;

三. 插入日期类型的值(日期类型的值的默认格式为 dd-mm-yyyy 如：01-3月-08 或 01-3月-2008 或 01/3月/2008 或 01/3月/08))

例如：
1. insert into userinfo values(userinfo_seq.nextval,'a','01-3月-2008');
或者
  insert into userinfo values(userinfo_seq.nextval,'b','22-3月-08');

2. insert into userinfo values(userinfo_seq.next2val,'c',to_date('2008/03/01','yyyy-mm-dd'));
或者
  insert into userinfo values(userinfo_seq.nextval,'c',to_date('01/03/2008','dd-mm-yyyy'));

3. insert into userinfo values(userinfo_seq.nextval,'c',to_date('2008-03-01','yyyy-mm-dd'));

4. insert into userinfo values(userinfo_seq.nextval,'c',to_date('03-08-01','mm-yyyy-dd'));

说明：

1. 使用 TO_DATE 函数转换(to_date('日期','格式转换'),日期与格式必须一致)
2. 排除插入 日期类型的值的默认格式，插入其它需要 to_date 函数转换的日期格式时，不能出现中文。

四. 在表中添加主键, 外键约束

1. 添加主键约束：alter table stuinfo add constraint pk_stuinfo_no primary key(no);

2. 添加外键约束：alter table stumarks add constraint fk_stumarks_no foreign key (no) references stuinfo(no);

3. 添加唯一约束：alter table userinfo add constraint uq_userinfo_name unique(name);

4. 添加检查约束: alter table student2 add constraint ck_sex_student2 check(sex = '男' or sex = '女')

5. 删除约束：ALTER TABLE table_name DROP CONSTRAINT constraint_name;

6. 删除主键约束：alter table student drop primary key;

7. 删除约束(包括相关联的约束): alter table student drop constraint pk_student_id cascade;  --cascade 代表一并删除相关联的约束

8. 建表同时建约束：(default 代表缺省值)

create table student
  (
id number primary key,
sid number, foreign key(sid) references score(sid),
name varchar(10) not null unique,
sex char(2) check(sex='男' or sex='女'),
address varchar(20) default '地址不详',
birthday date default sysdate

  );

9. 禁用约束: alter table student disable constraint pk_student_id cascade;  --cascade 代表一并禁用相关联的约束

10. 启用约束：alter table student enable constraint pk_student_id;  --不能用 cascade 一并启用相关联的约束

五．创建索引

1. 索引分类
唯一索引
组合索引
反向键索引
位图索引

2. 索引：是与表关联的可选结构，以加快对表执行sql语句的速度

3. 创建索引
create index 索引名 on 表名(列名)；

示例
create index oditem on order_detail(itemcode);

4. 唯一索引：确保在定义索引的列中，表的任意两行的值都不相同。oracle 自动为表的主键或唯一键列创建唯一索引

  语法
create unique index 索引名 on 表名(列名)；

  示例
create unique index ind2 on order_master

六. 创建序列

1. 格式:

create sequence 序列名称
      [increment by n]   --n是一个整数值，用于指定序列号之间的间隔，默认值为1，n可以为负数，表示降序排列     
      [start with n]    --指定要生成的第一个序列号
      [{maxvalue n/nomaxvalue}]                  --指定序列可以生成的最大值
      [{minvalue n/nominvalue}]                  --指定序列可以生成的最小值
      [{cycle/nocycle}]                          --指定序列在达到最大值或最小值之后，是否可以继续从头开始生成值
      [{cache n/nocache}];                      --使用cache选项可以预先分配一组序列号，并将其保留在内存中

2. 访问序列

创建了序列之后，可以借助于 oracle 提供的 currval 和 nextval 伪列来访问该序列的值，可以从伪列中选择值，但不能操纵他们的值
nextval 创建序列后第一次使用 nextval 时，将返回该序列的初始值，以后将返回新值
currval 返回序列的当前值，即最后一次引用 nextval 时返回的值

示例：
select venseq.nextval from dual;
select venseq.currval from dual;

在insert语句中使用序列值来添加新数据
insert into vendor_master(vencode,venname) values ('V'||venseq.nextval,'vijay');

3. 修改序列

使用 alter sequence 语句来修改序列的定义，包括
--设置或删除 maxvalue 或 minvalue
--修改增量值
--修改缓存中的序列号的数目

语法

alter sequence 序列名
      [increment by n]
      [{maxvalue n/nomaxvalue}]                  --指定序列可以生成的最大值
      [{minvalue n/nominvalue}]                  --指定序列可以生成的最小值
      [{cycle/nocycle}]                          --指定序列在达到最大值或最小值之后，是否可以继续从头开始生成值
      [{cache n/nocache}];
     
注意，alter sequence 语句不能用于修改 start with 参数的值

示例

alter sequence venseq
      maxvalue 15;

4. 删除序列

使用 drop sequence 语句从数据库中删除序列

语法

drop sequence 序列名

示例
drop sequence venseq;

七. 创建视图

格式:

create [or replace][force/noforce] view 视图名
[(alias[,alias]...)]
as
  select语句
[with check opiton[constraint 约束名]]
[with read only[constraint 约束名]]

注意：

or replace 表示如果视图已经存在，此选项将重新创建该视图
force  如果使用此关键字，则无论基表是否存在，都将创建视图
noforce 这是默认值，表示使用此关键字时，进当基表存在时才创建视图
alias  指定由视图的查询所选择的表达式的名称
with check option 此选项指定只能插入或更新视图可以访问的行
with read only    此选项确保不能在此视图上执行任何DML操作