oracle 11g常用命令

修改scott密码：
alter user scott identified by tiger;

查看当前用户有什么表：
select table_name from user_tables;

查看表的结构：
describe 表名

start+路径

spool+路径  spool off;  输出到制定位置的脚本里。

create user xiaoming identified by m123;

password xiaoming;

grant connect to zhangsan; 将角色connect授权给zhangsan用户

create table test(userid varchar2(30),username varchar2(30));

grant resource to zhangsan;

grant create table to zhangsan;授权

remove select on emp to zhangsan;撤销权限

desc test;查询表结构

grant all on emp to zhangsan;

alter user zhangsan account unlock;

关于配置文件：

create profile aaa1 limit failed_login_attempts 3 password_lock_time 2;  创建配置文件

alter user zhangsan profile aaa1; 给配置文件指定用户

drop profile aaa1 cascade; 删除配置文件加参数cascade

create profile myprofile limit password_life_time 10 password_grace_time 2; 创建配置文件

alter user zhangsan profile myprofile; 给配置文件指定用户

create table student (
xh number(4),
xm varchar2(20),
sex char(2),
birthday date,
sal number(7,2)
);

create table classes(
classid number(2),
cname varchar2(40)
);

alter table student add (classid number(2));

desc student;

insert into student values(1,'小明','男','11-12月-1997',2345.11,12);

alter table student modify(sex char(3));

alter session set nls_date_format='yyyy-mm-dd';修改会话，方便日期格式输入

insert into student (xh,xm,sex,birthday) values(3,'aa','女',null);

select * from student where birthday is null;

select * from student where birthday is not null;

update student set sal=sal/2 where sex='男';

savepoint aa;

update student set sal=sal/2,classid=5 where sex='男';

delete from student;(此命令禁止使用)

rollback;回滚操作

savepoint a;

desc dept;

set timing on；可以看到命令执行的时间

insert into users(userid,username,userpss) select * from users;

select count(*) from users;查询记录的条数

select userid from users;

select deptno,job,sal from emp where ename='SMITH';

select sal*13,ename from emp;

select sal*13 "年工资",ename from emp;

select sal*13+comm*13 "年工资",ename from emp;

select sal*13+comm*13 "年工资",ename,comm from emp;

select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp;

select ename,sal from emp where sal>3000;

alter session set nls_date_format="yyyy-mm-dd";

select ename,hiredate from emp where hiredate>'1982-1-1';

select ename,sal from emp where sal>=2000 and sal<=2500;

select ename,sal from emp where ename like 'S%';

select ename,sal from emp where ename like 'S%';select * from emp order by deptno
asc,sal desc;
select ename,sal from emp where ename like '_O%';

select * from emp where empno in(7788,234,456);

select * from emp where mgr is null;

select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';

select * from emp order by sal;

select * from emp order by sal desc;

select * from emp order by deptno asc,hiredate desc;

select ename,(sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪";

select ename,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪";

select max(sal),min(sal) from emp;

select ename,sal from emp where sal=(select max(sal) from emp);

select ename,sal from emp where sal>(select avg(sal) from emp);

select avg(sal),max(sal),deptno from emp group by deptno;

select avg(sal),max(sal),deptno from emp group by deptno order by deptno;

select avg(sal),max(sal),deptno,job from emp group by deptno,job order by deptno;

select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;

select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>1500 order by
avg(sal);

select a1.ename,a1.job,a2.deptno,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;
多表查询

select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal
and a2.hisal;

select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;

select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and
worker.ename='FORD';

select deptno from emp where ename='SMITH';

select * from emp where deptno=(select deptno from emp where ename='SMITH');

select * from emp where job in (select distinct job from emp where deptno=10);

select max(sal) from emp where deptno=30;

select * from emp where sal>(select max(sal) from emp where deptno=30);

select ename,sal,deptno from emp where sal> all (select sal from emp where deptno=30);

select ename,sal,deptno from emp where sal> any (select sal from emp where deptno=30);

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

select deptno,avg(sal) mysal from emp group by deptno;

select a2.ename,a2.sal,a2.deptno from emp a2,(select deptno,avg(sal) mysal from emp
group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;

select a1.*,rownum rn from (select * from emp) a1;

select emp.*,rownum rn from emp;

select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;

select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where
 rn>=6;

create table myemp2 (id,ename,sal) as select empno,ename,sal from emp;

select ename,sal,job from emp where sal>4500 union select ename,sal,job from emp where
job='MANAGER';

select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp
where job='MANAGER'; union all会产生重复项

minux取差集，前面减掉后面

create table kkk (myid number(4),myname varchar2(50),mydept number(5));

insert into kkk (myid,myname,mydept) select empno,ename,deptno from emp where deptno=20;

update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where
ename='SCOTT';

rollback to a2;

set transaction read only;设置事物处理

insert into emp values(8888,'ok','MANAGER',7782,to_date('1988-12-12','yyyy-mm-
dd'),34.34,23.23,10);

select lower(ename) from emp;

select upper(myname) from kkk;

select * from emp where length(ename)=5;

select * from emp where length(ename)=5;

select substr(ename,1,3) from emp;

select upper (substr(ename,1,1)) from emp;

select lower (substr(ename,2,length(ename)-1)) from emp;

select upper (substr(ename,1,1)) || lower (substr(ename,2,length(ename)-1)) from emp;

select replace(myname,'A','我是老虎') from kkk;

select (round(sal)+round(comm))*13 from emp;

select round(sal,1) from emp;

select trunc(comm,1),comm from emp where ename='zhangsan';

select floor(comm),comm from emp where ename='ok';

select mod(10,3) from dual;

select sysdate from dual;

update emp set hiredate='11-11月-2016' where ename='ok';

select sysdate-hiredate "入职天数" from emp;

select hiredate,last_day(hiredate) from emp;

select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

select * from emp where to_char(hiredate,'yyyy')=1980;

select * from emp where to_char(hiredate,'mm')=12;

select sys_context('userenv','db_name') from dual;

show parameter;

exp userid=scott/tiger@orcl tables=(emp) file=f:\oraclebackup\b1.txt exp备份表

exp userid=scott/tiger@orcl tables=(emp) file=f:\oraclebackup\b11.txt rows=n

exp scott/tiger@orcl owner=scott file=f:\oraclebackup\scott.dmp 导出用户的所有数据

exp system/12345678@orcl owner=(system,scott) file=f:\oraclebackup\system.dmp  导出数据
库

exp userid=system/12345678@orcl full=y inctype=complete file=f:\oraclebackup\shujuku.dmp
对数据库做全库备份

imp userid=scott/tiger@orcl tables=(dudu) file=f:\oraclebackup\dudu.dmp 将备份的表导入数
据库

imp userid=system/12345678@orcl tables=(users) file=f:\oraclebackup\users.dmp
touser=scott system将users表导入给scott用户

imp userid=system/12345678@orcl tables=(users) file=f:\oraclebackup\users.dmp system将
scott用户的users表导入给自己，切记！！！

imp userid=scott/tiger@orcl tables=(users) file=f:\oraclebackup\users.dmp rows=n 只导入
表的结构，不导入数据

imp userid=scott/tiger@orcl tables=(users) file=f:\oraclebackup\users.dmp ignore=y 如果
表已存在，只导入数据

imp userid=scott/tiger file=f:\oraclebackup\users.dmp  将备份方案导入给用户scott

imp userid=system/12345678 file=f:\oraclebackup\users.dmp fromuser=system touser=scott
将system用户的备份方案导入给用户scott

imp userid=system/12345678 full=y file=f:\oraclebackup\xxx.dmp  默认情况下，导入数据库时
，会导入所有对象结构和数据。

select table_name from user_tables; 查询当前用户所拥有的表

select table_name from all_tables; 查询当前用户可以访问的所有的表

select table_name from dba_tables; 查询DBA的数据字典

select * from dba_roles;显示数据库角色列表

create tablespace sp001 datafile 'f:\oraclebackup\sp001.dbf' size 20m uniform size 128K;
创建表空间

alter tablespace sp001 offline; 使表空间脱机

alter tablespace sp001 online; 使表空间联机

create table mypart(deptno number(2),dname varchar2(14),loc varchar2(13)) tablespace
sp001;

insert into mypart values(12,'sp0,','beijing');

alter tablespace sp001 read only;

create table goods(goodsid char(8) primary key,
goodname varchar2(30),
unitprice number(10,2) check (unitprice>0),
category varchar2(8),
provider varchar2(30));

create table customer(customerid char(8) primary key,--主键
name varchar2(50) not null,--不为空
address varchar2(50),
email varchar2(50) unique,
sex char(3) default '男' check (sex in ('男','女')),
cardid char(18));

create table purchase (customerid char(8) references customer(customerid),
goodid char(8) references goods(goodsid),
nums number(10) check (nums between 1 and 30));

alter table goods modify goodname not null;

alter table customer add constraint cardunique unique(cardid);

alter table customer add constraint addresscheck check(address in ('东城','西城'));

create index nameindex on customer(name); 创建索引

select * from system_privilege_map order by name;

create user ken identified by m123;

create user tom identified by m123;

grant create session,create table to ken with admin option;

grant create view to ken;

grant create session,create table to tom with admin option; 此处为级联授权

grant create view to ken with admin option;

grant create view to tom;

revoke create session from ken;

grant select on emp to ken;

create role myrole1 not identified;创建自定义角色

grant create session to myrole1 with admin option; 给角色授权

grant select on emp to myrole1;给角色授权

grant update on emp to myrole1

、查看Oracle数据库的用户名和密码的方法：

    运行 cmd 按如下输入命令
    sqlplus / as sysdba ---------以sys登陆          超级用户（sysdba）
    alter user 用户名 account unlock; --------- 解除锁定(必须带“;”号)
    alter user 用户名 identified 密码; -------------修改密码
    然后用你改好的密码登陆就行