亚洲欧洲国产欧美一区精品,激情五月亚洲色五月,最新精品国偷自产在线婷婷,欧美婷婷丁香五月天社区

DML: insert / update / delete

DDL: create / drop / alter /      truncate

DCL: grant / revoke

一 DML

1.insert

insert into dept_ning

values(55, 'market', 'beijing');

insert into dept_ning

(deptno,  dname, location)

values(56, 'research', 'beijing');

--數(shù)據(jù)沒有全部提供,必須寫列名

insert into emp_ning(empno, ename,

salary, deptno)

values(1234,'rose',5000,10,500);

--出錯,沒有足夠的值

insert into emp_ning(empno, ename, salary)

values(1234,'rose');

--出錯,值太多了

insert into emp_ning(empno, ename, salary)

values(1234,'rose',5000,500);

insert into emp_ning(empno, ename,

salary, hiredate)

values(1234, 'rose', 5000,

to_date('2012/01/01','yyyy/mm/dd'));

create table emp_bak

as

select * from emp_ning where deptno = 10;

delete from emp_bak;

insert into emp_bak (select * from emp_ning  where deptno = 20);

create table myObjects  as select * from all_objects where rownum < 1000;

delete myObjects;

insert into myObjects (select * from all_objects where rownum < 10000);

2.update

update 表名

set 列1 = 新值1,列2 = 新值2,...

where 條件;

update emp_ning set bonus = 1000, deptno = 20, job = 'Programmer' where empno = 1234;

3.delete

delete emp_ning where empno = 1234;

delete emp_ning where deptno = 10;

delete emp_ning;

select count(*) from emp_ning;

rollback;

select count(*) from emp_ning;

create table emp_bak1_ning

as

select empno, ename,salary

from emp_ning

where deptno = 30;

--適合bak1中重復(fù)記錄非常多的場合.

              復(fù)制非重復(fù)記錄

emp_bak1_ning--------------> bak2

刪掉表bak1, 把bak2改名為bak1.

--適合bak1中重復(fù)記錄非常少的場合.

--直接在bak1中刪除.

--查詢重復(fù)記錄中地址最大的那條

select empno, ename, salary,

       max(rowid)

from emp_bak1_ning

group by empno, ename, salary;

--刪除除了地址最大的重復(fù)記錄之外所有的記錄.

delete emp_bak1_ning

where rowid not in (

select max(rowid)

from emp_bak1_ning

group by empno, ename, salary);

rownum, rowid是Oracle獨有的.

事務(wù) Transaction

commit / rollback

-----事務(wù)開始--------------

DML:insert / delete / update

commit(提交,確認),

或者 rollback(回滾,撤銷)

-----事務(wù)終止--------------

A                B

insert : 1234    select (查不到)

commit          

                 可以查到 1234

update          

                 update(掛起)

commit(釋放鎖)   修改成功

update(掛起)

                 rollback;

修改成功.

---開始事務(wù)-----

update account

set money = money - 500

where id = 'A';

update account

set money = money + 500

where id = 'B';

if (都成功)

         commit;

else

             rollback;

-----事務(wù)終止-----

正常退出會話: commit

異常退出會話: rollback

DDL操作: commit

create table temp_ning (id number);

insert into temp_ning values(1);

insert into temp_ning values(2);

--設(shè)置保存點,名字是a(自定義)

savepoint a;

insert into temp_ning values(3);

insert into temp_ning values(4);

--回滾到指定點

rollback to a;

select * from temp_ning;--2條記錄

insert into temp_ning values(100);

savepoint b;

insert into temp_ning values(5);

savepoint c;

insert into temp_ning values(6);

--b點之后的保存點被取消

rollback to b;

insert / update / delete

二.DDL操作:

create / drop / alter / truncate

create table 表名(

列1 列1數(shù)據(jù)類型,

列2 列2數(shù)據(jù)類型,

....

);

drop table 表名;

create function f_name()....

drop function f_name;

alter: 修改對象結(jié)構(gòu)

create table temp_ning(id number);

--增加列,只能加在最后.

alter table temp_ning  add (name char(20));

alter table temp_ning add (age number(3));

--改列名

 alter table temp_ning

 rename column name to stuname;

--改列的類型char(20)->char(10)

--放大可以

--縮小的話, 如果數(shù)據(jù)有超過10個字符的,不能修改.

id    stuname        age

1     tom             18

2     zhangsan        20

--刪除列age

alter table temp_ning

drop column age;

alter table 表名

--add (name char(20));

--rename column 舊列名 to 新列名;

--modify (age number(3));

--drop column 列名;

--截取表:把表數(shù)據(jù)全部刪除.

--立即生效,不能rollback

truncate table temp_ning;

delete temp_ning + commit

--三思而后行

drop table ....;

truncate table ...;

三.DCL語言

grant / revoke

--授權(quán)

grant select on emp_ning to scott;

--撤銷權(quán)限

revoke select on emp_ning from scott;

sys/安裝時設(shè)置

system/安裝時設(shè)置

scott/tiger(測試用戶)

...

openlab/open123

ninglj/*****

四.腳本文件

文檔

源代碼

數(shù)據(jù)庫腳本文件

1.創(chuàng)建腳本文件script_ning.sql

$vi script_ning.sql

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

drop table temp_ning;

create table temp_ning(id number);

insert into temp_ning values(1);

commit;

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

esc鍵-> :wq

2.sqlplus中批處理執(zhí)行腳本文件

SQL>@ /user/openlab/script_ning.sql

數(shù)據(jù)庫在192.168.0.26

數(shù)據(jù)庫的口令:openlab/open123

Oracle的端口號: 1521

Oracle的名字(sid):tarena

sqlplus安裝在本地機器上

C:>sqlplus openlab/open123@192.168.0.26:1521/tarena

SQL>

連接數(shù)據(jù)庫的工具:

--首選,命令行工具

sqlplus

--圖形工具

pl/sql developer(非官方,免費)

toad(非官方,收費)

sql developer(官方,免費,11g以上)

補充練習(xí): procedure 過程

PL/SQL:  Procedure Language / SQL

函數(shù)(function)

過程(procedure)

包(package)

觸發(fā)器(trigger)

--輸入班號,輸出最高分的學(xué)生名字和總成績

create or replace procedure cal_ning(

 p_class_id in number, p_student_name out char,

 p_total_score out number

)

is

begin

  select student_name, total_score

  into  p_student_name, p_total_score

  from (

    select stu.student_name,

           sum(per.test_score) total_score

    from t_student_ning stu join t_performance_ning per

    on stu.student_id = per.student_id

    where stu.class_id = p_class_id

    group by stu.student_name

    order by total_score desc)

  where rownum < 2;

end;

/

--打開輸出,默認值是off

SQL>set serveroutput on

--匿名塊,用來測試過程或函數(shù)

SQL>declare

  p_student_name char(20);

  p_total_score number;

begin

  cal_ning(&no, p_student_name,       p_total_score);

  --向控制臺輸出變量值,System.out.print

  dbms_output.put_line(p_student_name);

  dbms_output.put_line(p_total_score);

end;

/

--可以用&abc符號表示在運行時輸入變量值

select * from emp_ning

where deptno = &abc;

--改進版,輸入共多少個班,輸出每個班的最高成績的學(xué)生姓名和總分

--假設(shè)是班號是1-p_class_num

create or replace procedure cal_ning1(

   p_class_num in number)

is

  p_student_name char(20);

  p_total_score number;

begin

  for i in 1..p_class_num loop

    select student_name, total_score

    into  p_student_name, p_total_score

    from (

      select stu.student_name,

           sum(per.test_score) total_score

      from t_student_ning stu join t_performance_ning per

      on stu.student_id = per.student_id

      where stu.class_id = i

      group by stu.student_name

      order by total_score desc)

    where rownum < 2;

    dbms_output.put_line

        (p_student_name || ', ' || p_total_score);

  end loop;

end;

/

SQL>exec cal_ning1(2); --2個班