常用oracle命令
上一篇 / 下一篇 2010-01-07 07:54:45 / 个人分类: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操作
二、显示当前日期: 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操作
相关阅读:
- SQL条件的顺序对数据库性能的影响 (51testing, 2010-3-12)
- 使用Java编写Oracle存储过程 (51testing, 2010-3-15)
- Oracle数据库安全性设计建议 (51testing, 2010-3-15)
- SQL PLUS命令使用大全(转,特有用) (Q_sunny520, 2010-3-16)
- QTP连接oracle操作方法 (Tesherlock, 2010-3-16)