oracle脚本命令

 

启动命令

sqlplus/sqlplusw                               

Scott  普通用户  密码为 tiger

------------------------->>>

sys 、system 、 dba

Sys  密码为 change_on_install

System  密码为  manager

------------------------->>>

 Conn  scott/tiger;

  conn  system/manager @ [as sysdba / sysoper] 特权用户

  show  user; 显示当前用户

  Show  error 显示错误信息。

  dis;        退出连接

  exit;       退出系统

  passw;     修改密码

  Start / @  如：start d:\d.sql; 执行sql脚本    

  edit      编辑

  【spool d:\b.sql;

   select  * from emp;

   spool off;】

  【&         交换式命令

   selelct * from  emp where ='&job';】

  【linesize  设定显示数据的宽度

   Show  linesize;

   set linesize  50;】

  【pagesize   ----------------------------------------》》》》》》》sql分页

   set pagesize  2;】

------------------------------->>>

  【create  user  xuy  identified  by m123;       【密码必须以字母开头】。

   只有system有创建和修改用户的权限平[dba]】

   drop  user 如果要删除用户已经创建好的表须带上一个参数【 cascade  】。

------------------------------->>>

权限:系统权限/对象权限

      系统权限：建库、表(creat  session  ),预定义角色【connect】，（系统自动）

      对象权限：用户对其他的数据对象操作的权限。 表、视图、存储、触发器。

系统权限:[create  session]

      角色[connect]：预定义角色、自定义角色。

     【dba           

      resource[表空间]

      】                 ----->>>connect

表空间： default 或  temporary 、 tablespace     确定默认表空间、临时表空间

表空间建立：

Create  tablespace    epet

Datafile  ' e:\epet.dbf '

Size  100m

Autoextend  on  next  32m  maxsize  unlimited

Logging 

Extend management  local

Segment  space  management  auto;

 

用户角色授权：

    【conn  system/manager;    [须切换为系统权限方能授权]

     grant connect  to xcy; 】

---------

resource：授权(建表权利)

     【conn  /manager;   [须切换为系统权限方能授权]

      grant ressystemource  to xcy;】 

desc  xcy     查看表结构

------------------------------->>>

对象授权:

  select/ insert/ update/ delete/   [all]  create index  >>>】

---------

  【conn  scott/m123;     切换到用户

   grant  select  on  emp   to xuy;  把scott的emp表权限交给xuy;】

  【conn xcy/m1234;

   select * from   scott.emp  】

  【grant  all(insert、delete、update、select) on emp to xcy】对emp表的权限

收回权限：

     Revoke   select  on  emp  from  xcy;

对权限的维护：(用户授权的传递)

------>>如果是对象权限就加入 with  grant option

    conn xuy/m123;

    grant   select  on  xuy.test  to  xiaoming;

------>>如果是系统权限

    system给xiaoming权限时：

    grant connect  to  xiaoming  with admin option;    

【注意：如果system收回权限则xuy、xiaoming的权限一同收回】

使用profile管理用户口令(口令限制)---------------->>>

    资源限制命令集合，当建立数据库时，oracle会自动建立名为default的profile。当建立用户没有指定porfile选项，oracle就会将default分配给用户。

  (1)帐户锁定：

概述：指定该用户登录时最多可以输入密码的次数，也可以指定用户的锁定的时间一般用DBA的身份去执行该命令。

create  profile  aaa  limit

failed_login_attempts  3  password_lock_time 2; 用户3次登陆失败密码锁定时间为2天。

Alter  user  admin  profile  aaa;

出现错误：the account  is locked;

[权限system]

  (2)帐户解锁：        [权限 dba]

 Alter  user  admin  account  unlock;  

  (3)终止口令：        [权限dba]

为了让用户定期修改密码可以使用终止口令来完成，每隔10天，宽限期为2天。超过权限指令，无权修改。

Create  profile  myprofile   limit  password_life_time  10  password_grace_time 2;

Alter  user  admin   profile   myprofile;

口令历史：

希望用户在修改密码时，不能使用以前使用过的密码。限制权限的时间为12天。

Create  profile_password_history  limit password_life_time 10password_grace_time 2 password_reuse_time 10  指定口令可重用的时间为10天。

Drop  profile  password_history      [cascade级联]

 

 

数据类型：

Char                      number

Varchar2                   number(5,2)

Club                      number(5)

Nchar                     【日期类型】 【date  、timestamp】

Date   的格式是   02-12月-2020

Alter  session  set  nls_date_format=‘yyyy-mm-dd’；------------------------------------------------->>>>>>>>

Nvarchar2                  【图片】【 blob 】存放声音、图片。

Nvarchar2  varchar2  

------------------------------------------------------------>>>>>

建立表的一些指令：

【Alter  table student  add（classid    number(0)）;  添加一列

Alter table  student   modify  (  xxxx  varcha2);  修改

Alter table  student  drop  column  'sal'[字段];  删除

】---->>>空表数据

 

Rename  studnet  to stu.

Drop  table  stu;

Select  * from student  where brithday is null;

删除表:---------------------------------------------------------------------------------------->>>数据回滚

      Drop  table  student；写日志，可以恢复。

【

删之前:

Savepoint   points ；回滚点。

 Delete  from  student;

Rollback  to  points;

】注意：一旦提交‘commit’savepoint将无法回滚。

----------------------------------------------------------------------------->>>>>>>>>>>>>首先干的事情。

创建序列：(自动增长)

Create  sequence  seq_name

Start  with   1

Increment  by 1

Nocycle   无循环

Nomaxvalue  没有最大值

Cache  10;     缓存为10

------------------------->------------------------------>------------------------------>----------------------------->

Truncate  table   student

使用算术表达式：

Select ename‘姓名’，sal*23  as‘年收入’from emp；

------------------------------------------------------>>>>>>

处理空值：Select  ename  ||  ‘is  a ’||   job  from emp；

----------------------------------------------------------------------->>>>>

 

创建索引：

 Create [unqiue /bitmap] index  index_name   on    table_name

 (colunm_name/  expresssionl  asc/ desc  ,column_name2  / expressional  asc // desc)

[ reverse] 创建反向建索引

 

导出：

Exp  system/manager@accp file=d:\dd.dmp owner=admin 

或者: exp  system/manager@accp file=d:\ dd.dmp tabels=.....

 

Epet

密码：bdqn

 

Imp system/manager@accp file=d:\dd.dmp  full =y

 

在选择列中：如果有列、表达式、和分组函数，那么这些列和表达式就必须出现自  group by 子句中，否则就会出错。 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;

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)>2000 order by  avg(sal); 

Select   a1.dname,a2.ename ,a2.sal  from  dept a1, emp a2  where  a1.deptno=a2.deptno;

Select   a1.dname,a2.ename ,a2.sal  from  dept a1, emp a2  where  a1.deptno=a2.deptno and a1.deptno=10;

Select  a1.dname,  a2.ename,  a2.sal  from  dept  a1, emp  a2  where  a1.deptno=a2.deptno  and  a1.deptno=10;

Select  a1.ename,  a1.sal,  a2.grade  fom  emp a1, salgrade a2  where  a1.sal  between  a2. Losal  and  a2.hisal;    ------>>>>select  * from  salgrade;

Select  a1.ename  a2.dname ,  a1.sal  from emp a1 , dept a2  where  a1.deptno=a2.deptno  order  by  a1.deptno;

自连接：在同一张表的连接查询：

Select  worder.ename,  boss.ename  from  emp worker  ,emp boss  where  worker.mgr =boss.empno  and  worker.ename=' ford';

----------------------------------------------------------------------------------------------------------------------

多行子查询：

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

Select  ename ,sal, deptno  from emp where sal > all (select  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 > any (select  sal  from  emp  where  deptno=30);

多列查询：

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

表的处理：

Create  or  replace procedure sp_proc is

Begin

End

Exec  过程名

Call  过程名

                     【过程

      块-------》》》     函数

                      触发器

                      包】

----------------------------------------------------------------------------------->>>>>>>

变量 v_sal

常量 c_rate

游标     Emp_cursor

例外   e_error

---------------------------------------------------------------------------------->>>>>>>>

Orcale的分页：

select  * from (Select  a1.*, rownum rn from  (select * from  emp ) a1 where rownum <=10) where  r n>=6; ---------------------(指定查询列的修改，只需修改括号内的内容)

Select  * from (select a1.* , rownum  rn from (select ename ,  sal  from  emp order  by  sal   desc) a1 where  rownum <=9 ) where rn >4;

用查询结果创建新表：

Create  table  mytable (id ,name ,sal, job, deptno) as select empno, ename, sal,  job ,deptno  from emp;

-----------------------------------------------------------------------------------------------------<<<<<<<<<<

行迁移：

Insert  into kkk (....... )select .......from emp where deptno=10;

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

Oracle事物处理：

例如java程序

.......

Conn ct=DriverManager(......);

ct.setAutoCommit(false); 如果为true就卓行提交

Statement sm=ct.createStatement( );           

sm.executeUpdate(........_);

sm.executeUpdate(.......);

Conn.commit();                                           

在异常处理中回滚

Conn.rollback( );

---------------------------------------------------------------------->>>>>>>>>>>>

只读事务：

在系统管理员下创建：system

Set  transation  read only；

Insert  inot ...........

-----------------------------------

Scott登录后将无法查看已插入的信息

Select * from  scott.emp

--------------------------------------------

首字母大写

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

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

-----------------------------------------------------------

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

--------------------------------------------------------------->>>>>>>

Select  su bstr  (ename , 1 , 3 )  from  emp;      从第一位开始截取，取3位。

联合使用

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

 

Replace替换函数

Select  replace （ename ,' A' , 'ADSFADFA' ） from  emp ;

数学函数：

Round 该函数用于截取四舍五入

Trunc   该函数用于截取数字

Mod

Floor

Ceil

例如:

Select   (round(sal)+round(comm) )* 13 from emp; where  ename =' ' ;

Select   (round(sal , 1)  sal  from emp; where  ename =' ' ;

Select  mod(10, 3） from dual

 

 

 

 

 

 

启动命令

sqlplus/sqlplusw                               

Scott  普通用户  密码为 tiger

------------------------->>>

sys 、system 、 dba

Sys  密码为 change_on_install

System  密码为  manager

------------------------->>>

 Conn  scott/tiger;

  conn  system/manager @ [as sysdba / sysoper] 特权用户

  show  user; 显示当前用户

  Show  error 显示错误信息。

  dis;        退出连接

  exit;       退出系统

  passw;     修改密码

  Start / @  如：start d:\d.sql; 执行sql脚本    

  edit      编辑

  【spool d:\b.sql;

   select  * from emp;

   spool off;】

  【&         交换式命令

   selelct * from  emp where ='&job';】

  【linesize  设定显示数据的宽度

   Show  linesize;

   set linesize  50;】

  【pagesize   ----------------------------------------》》》》》》》sql分页

   set pagesize  2;】

------------------------------->>>

  【create  user  xuy  identified  by m123;       【密码必须以字母开头】。

   只有system有创建和修改用户的权限平[dba]】

   drop  user 如果要删除用户已经创建好的表须带上一个参数【 cascade  】。

------------------------------->>>

权限:系统权限/对象权限

      系统权限：建库、表(creat  session  ),预定义角色【connect】，（系统自动）

      对象权限：用户对其他的数据对象操作的权限。 表、视图、存储、触发器。

系统权限:[create  session]

      角色[connect]：预定义角色、自定义角色。

     【dba           

      resource[表空间]

      】                 ----->>>connect

表空间： default 或  temporary 、 tablespace     确定默认表空间、临时表空间

表空间建立：

Create  tablespace    epet

Datafile  ' e:\epet.dbf '

Size  100m

Autoextend  on  next  32m  maxsize  unlimited

Logging 

Extend management  local

Segment  space  management  auto;

 

用户角色授权：

    【conn  system/manager;    [须切换为系统权限方能授权]

     grant connect  to xcy; 】

---------

resource：授权(建表权利)

     【conn  /manager;   [须切换为系统权限方能授权]

      grant ressystemource  to xcy;】 

desc  xcy     查看表结构

------------------------------->>>

对象授权:

  select/ insert/ update/ delete/   [all]  create index  >>>】

---------

  【conn  scott/m123;     切换到用户

   grant  select  on  emp   to xuy;  把scott的emp表权限交给xuy;】

  【conn xcy/m1234;

   select * from   scott.emp  】

  【grant  all(insert、delete、update、select) on emp to xcy】对emp表的权限

收回权限：

     Revoke   select  on  emp  from  xcy;

对权限的维护：(用户授权的传递)

------>>如果是对象权限就加入 with  grant option

    conn xuy/m123;

    grant   select  on  xuy.test  to  xiaoming;

------>>如果是系统权限

    system给xiaoming权限时：

    grant connect  to  xiaoming  with admin option;    

【注意：如果system收回权限则xuy、xiaoming的权限一同收回】

使用profile管理用户口令(口令限制)---------------->>>

    资源限制命令集合，当建立数据库时，oracle会自动建立名为default的profile。当建立用户没有指定porfile选项，oracle就会将default分配给用户。

  (1)帐户锁定：

概述：指定该用户登录时最多可以输入密码的次数，也可以指定用户的锁定的时间一般用DBA的身份去执行该命令。

create  profile  aaa  limit

failed_login_attempts  3  password_lock_time 2; 用户3次登陆失败密码锁定时间为2天。

Alter  user  admin  profile  aaa;

出现错误：the account  is locked;

[权限system]

  (2)帐户解锁：        [权限 dba]

 Alter  user  admin  account  unlock;  

  (3)终止口令：        [权限dba]

为了让用户定期修改密码可以使用终止口令来完成，每隔10天，宽限期为2天。超过权限指令，无权修改。

Create  profile  myprofile   limit  password_life_time  10  password_grace_time 2;

Alter  user  admin   profile   myprofile;

口令历史：

希望用户在修改密码时，不能使用以前使用过的密码。限制权限的时间为12天。

Create  profile_password_history  limit password_life_time 10password_grace_time 2 password_reuse_time 10  指定口令可重用的时间为10天。

Drop  profile  password_history      [cascade级联]

 

 

数据类型：

Char                      number

Varchar2                   number(5,2)

Club                      number(5)

Nchar                     【日期类型】 【date  、timestamp】

Date   的格式是   02-12月-2020

Alter  session  set  nls_date_format=‘yyyy-mm-dd’；------------------------------------------------->>>>>>>>

Nvarchar2                  【图片】【 blob 】存放声音、图片。

Nvarchar2  varchar2  

------------------------------------------------------------>>>>>

建立表的一些指令：

【Alter  table student  add（classid    number(0)）;  添加一列

Alter table  student   modify  (  xxxx  varcha2);  修改

Alter table  student  drop  column  'sal'[字段];  删除

】---->>>空表数据

 

Rename  studnet  to stu.

Drop  table  stu;

Select  * from student  where brithday is null;

删除表:---------------------------------------------------------------------------------------->>>数据回滚

      Drop  table  student；写日志，可以恢复。

【

删之前:

Savepoint   points ；回滚点。

 Delete  from  student;

Rollback  to  points;

】注意：一旦提交‘commit’savepoint将无法回滚。

----------------------------------------------------------------------------->>>>>>>>>>>>>首先干的事情。

创建序列：(自动增长)

Create  sequence  seq_name

Start  with   1

Increment  by 1

Nocycle   无循环

Nomaxvalue  没有最大值

Cache  10;     缓存为10

------------------------->------------------------------>------------------------------>----------------------------->

Truncate  table   student

使用算术表达式：

Select ename‘姓名’，sal*23  as‘年收入’from emp；

------------------------------------------------------>>>>>>

处理空值：Select  ename  ||  ‘is  a ’||   job  from emp；

----------------------------------------------------------------------->>>>>

 

创建索引：

 Create [unqiue /bitmap] index  index_name   on    table_name

 (colunm_name/  expresssionl  asc/ desc  ,column_name2  / expressional  asc // desc)

[ reverse] 创建反向建索引

 

导出：

Exp  system/manager@accp file=d:\dd.dmp owner=admin 

或者: exp  system/manager@accp file=d:\ dd.dmp tabels=.....

 

Epet

密码：bdqn

 

Imp system/manager@accp file=d:\dd.dmp  full =y

 

在选择列中：如果有列、表达式、和分组函数，那么这些列和表达式就必须出现自  group by 子句中，否则就会出错。 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;

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)>2000 order by  avg(sal); 

Select   a1.dname,a2.ename ,a2.sal  from  dept a1, emp a2  where  a1.deptno=a2.deptno;

Select   a1.dname,a2.ename ,a2.sal  from  dept a1, emp a2  where  a1.deptno=a2.deptno and a1.deptno=10;

Select  a1.dname,  a2.ename,  a2.sal  from  dept  a1, emp  a2  where  a1.deptno=a2.deptno  and  a1.deptno=10;

Select  a1.ename,  a1.sal,  a2.grade  fom  emp a1, salgrade a2  where  a1.sal  between  a2. Losal  and  a2.hisal;    ------>>>>select  * from  salgrade;

Select  a1.ename  a2.dname ,  a1.sal  from emp a1 , dept a2  where  a1.deptno=a2.deptno  order  by  a1.deptno;

自连接：在同一张表的连接查询：

Select  worder.ename,  boss.ename  from  emp worker  ,emp boss  where  worker.mgr =boss.empno  and  worker.ename=' ford';

----------------------------------------------------------------------------------------------------------------------

多行子查询：

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

Select  ename ,sal, deptno  from emp where sal > all (select  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 > any (select  sal  from  emp  where  deptno=30);

多列查询：

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

表的处理：

Create  or  replace procedure sp_proc is

Begin

End

Exec  过程名

Call  过程名

                     【过程

      块-------》》》     函数

                      触发器

                      包】

----------------------------------------------------------------------------------->>>>>>>

变量 v_sal

常量 c_rate

游标     Emp_cursor

例外   e_error

---------------------------------------------------------------------------------->>>>>>>>

Orcale的分页：

select  * from (Select  a1.*, rownum rn from  (select * from  emp ) a1 where rownum <=10) where  r n>=6; ---------------------(指定查询列的修改，只需修改括号内的内容)

Select  * from (select a1.* , rownum  rn from (select ename ,  sal  from  emp order  by  sal   desc) a1 where  rownum <=9 ) where rn >4;

用查询结果创建新表：

Create  table  mytable (id ,name ,sal, job, deptno) as select empno, ename, sal,  job ,deptno  from emp;

-----------------------------------------------------------------------------------------------------<<<<<<<<<<

行迁移：

Insert  into kkk (....... )select .......from emp where deptno=10;

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

Oracle事物处理：

例如java程序

.......

Conn ct=DriverManager(......);

ct.setAutoCommit(false); 如果为true就卓行提交

Statement sm=ct.createStatement( );           

sm.executeUpdate(........_);

sm.executeUpdate(.......);

Conn.commit();                                           

在异常处理中回滚

Conn.rollback( );

---------------------------------------------------------------------->>>>>>>>>>>>

只读事务：

在系统管理员下创建：system

Set  transation  read only；

Insert  inot ...........

-----------------------------------

Scott登录后将无法查看已插入的信息

Select * from  scott.emp

--------------------------------------------

首字母大写

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

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

-----------------------------------------------------------

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

--------------------------------------------------------------->>>>>>>

Select  su bstr  (ename , 1 , 3 )  from  emp;      从第一位开始截取，取3位。

联合使用

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

 

Replace替换函数

Select  replace （ename ,' A' , 'ADSFADFA' ） from  emp ;

数学函数：

Round 该函数用于截取四舍五入

Trunc   该函数用于截取数字

Mod

Floor

Ceil

例如:

Select   (round(sal)+round(comm) )* 13 from emp; where  ename =' ' ;

Select   (round(sal , 1)  sal  from emp; where  ename =' ' ;

Select  mod(10, 3） from dual