1、查询oracle中所有用户信息

select * from dba_users;

2、只查询用户和密码

select username,password from dba_users;

3、查询当前用户信息

select * from dba_ustats;

4、查询用户可以访问的视图文本

select * from dba_varrays;

5、查看用户或角色所拥有的角色

select * from dba_role_privs;   

select * from user_role_privs;

6、查看用户或角色系统权限(直接赋值给用户或角色的系统权限)

select * from dba_sys_privs;   

select * from user_sys_privs; (查看当前用户所拥有的权限)

sqlplus/ as sysdba

-- create new user

CREATE USER test IDENTIFIED BY t123;

-- grant priviledges

GRANT CONNECT, RESOURCE, DBA TO test;

--alter password

alter user test identified by t123;

--oracle对表空间 USERS 无权限

alter user 用户名 quota unlimited on users;

--删除用户

drop user 用户名 cascade;   ---cascade 级联

--授予权限

-- 分配用户 Sam 创建表，创建序列，创建存储过程和创建视图的权限

grant create table,create sequence,create view,create procedure, update

to test

--去除用户权限

-- 去除用户 Sam 创建视图的权限

revoke create view from test;

CREATE TABLE order_items

  (

    order_id   NUMBER( 12, 0 )                                , -- fk

    item_id    NUMBER( 12, 0 )                                ,

    product_id NUMBER( 12, 0 ) NOT NULL                       , -- fk

    quantity   NUMBER( 8, 2 ) NOT NULL                        ,

    unit_price NUMBER( 8, 2 ) NOT NULL                        ,

    CONSTRAINT pk_order_items

      PRIMARY KEY( order_id, item_id ),

    CONSTRAINT fk_order_items_products

      FOREIGN KEY( product_id )

      REFERENCES products( product_id )

      ON DELETE CASCADE,

    CONSTRAINT fk_order_items_orders

      FOREIGN KEY( order_id )

      REFERENCES orders( order_id )

      ON DELETE CASCADE

  );

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

-- Name	       : OT (Oracle Tutorial) Sample Database

-- Link	       : http://www.oracletutorial.com/oracle-sample-database/

-- Version     : 1.0

-- Last Updated: July-28-2017

-- Copyright   : Copyright ?2017 by www.oracletutorial.com. All Rights Reserved.

-- Notice      : Use this sample database for the educational purpose only.

--               Credit the site oracletutorial.com explitly in your materials that

--               use this sample database.

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

-- disable FK constraints

ALTER TABLE order_items DISABLE CONSTRAINT fk_order_items_products;

ALTER TABLE order_items DISABLE CONSTRAINT fk_order_items_orders;

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

--  OT

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

REM INSERTING into OT.ORDER_ITEMS

SET DEFINE OFF;

Insert into OT.ORDER_ITEMS (ORDER_ID,ITEM_ID,PRODUCT_ID,QUANTITY,UNIT_PRICE) values (70,7,32,132,469.99);

Insert into OT.ORDER_ITEMS (ORDER_ID,ITEM_ID,PRODUCT_ID,QUANTITY,UNIT_PRICE) values (73,5,192,124,519.99);

-- enable FK constraints

ALTER TABLE order_items ENABLE CONSTRAINT fk_order_items_products;

ALTER TABLE order_items ENABLE CONSTRAINT fk_order_items_orders;

一、标准update语法（常用、速度可能最慢）

-- 当更新的表示单个或者被更新的字段不需要关联表带过来，此法是最好的选择。

update  a

set a.c2= (select b.c2from  b where a.c1=b.c1)

where exists   (select 1 from   b where a.c1=b.c1)

二、内联视图更新（关联主键字段，速度较快）

-- inline view更新法就是更新一个临时建立的视图。

-- 方案：更新一个临时建立的视图。要求B表的主键字段必须在where条件中，并且是以=号来关联被更新表。

update (select a.c2 as ac2,b.c2 as bc2 from a, b where a.c1=b.c1 and a.c3=’2011’) as M

set ac2=bc2

三、merge更新法 （关联字段非主键时，速度较快）

merge是oracle特有的语句，语法如下：

MERGE INTO table_name alias1 --主表，即需要被修改的表

USING (table | view | sub_query) alias2 --从表，即来源表

ON (join condition) --连接条件

WHEN MATCHED THEN --在匹配的记录中进行

  UPDATE table_name SET col1 = col_val1, col2 = col2_val --更改主表信息

WHEN NOT MATCHED THEN --在不匹配的情况下，筛选从表记录插入到主表【可选】

  INSERT (column_list) VALUES (column_values);

merge into a

using b

 on (a.c1=b.c1 and a.c3=’2011’)

   when matched then

update a.c2=b.c2

四、快速游标更新法（复杂逻辑时，效率很高）

begin

    for cur in (select a.rowid  ,b.c2 from  a, b where a.c1=b.c1 and a.c3=’2011’ )  loop

        UPDATE a  set c2=cur.c2  where rowid=cur.rowid;

    end loop;

end;

# 删表

DROP TABLE ot.order_items;

# 删行

delete from 表名 where 条件

constraint ca_pk primary key()

constraint c2 foreign key () references b()

select a,b, row_number() over(partition by a order by b) as c from aaa group by a

select a,b,rownum as id from aaa

update AAA set B=B*0.1;

commit;

update aaa set b=rownum where a='x';

commit;

update a

set (x,y)=(select x,f from a,b where a.id=b.id)

where exists(select 1 from a,b where a.id=b.id)

merge into a

using b

on

when matched then

 update b set

when not matched then

	insert () values()

insert into aaa(a,b)

values( 'z', 1);

commit;

insert into aaa(a,b)

(

select a, (select avg(b) from aaa)

from aaa

where a <> 'x'

);

commit;

-- 查找与001号同学所学课程完全相同的其他同学的学号和姓名

select t4.sid, t4.name

from (

	select sid, count(1) c1

	from course t

	where c1.sid <> '001'

	group by c1.sid

) t1, (

	select sid, count(1) as c2

	from course t

	where cid in (select cid from course where sid='001')

	group by sid

) t2, (

	select count(1) c3

	from course t

	where sid='001'

) t3, (

	select sid, name

	from student t

) t4

where t1.sid = t2.sid and t1.c1 = t2.c2 and t1.c1 = t3.c3 and t1.sid = t4.sid