华南俳烁实业有限公司

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ù)沒(méi)有全部提供,必須寫(xiě)列名

insert into emp_ning(empno, ename,

salary, deptno)

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

--出錯(cuò),沒(méi)有足夠的值

insert into emp_ning(empno, ename, salary)

values(1234,'rose');

--出錯(cuò),值太多了

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ù)記錄非常多的場(chǎng)合.

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

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

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

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

--直接在bak1中刪除.

--查詢(xún)重復(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獨(dú)有的.

事務(wù) Transaction

commit / rollback

-----事務(wù)開(kāi)始--------------

DML:insert / delete / update

commit(提交,確認(rèn)),

或者 rollback(回滾,撤銷(xiāo))

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

A                B

insert : 1234    select (查不到)

commit          

                 可以查到 1234

update          

                 update(掛起)

commit(釋放鎖)   修改成功

update(掛起)

                 rollback;

修改成功.

---開(kāi)始事務(wù)-----

update account

set money = money - 500

where id = 'A';

update account

set money = money + 500

where id = 'B';

if (都成功)

         commit;

else

             rollback;

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

正常退出會(huì)話: commit

異常退出會(huì)話: rollback

DDL操作: commit

create table temp_ning (id number);

insert into temp_ning values(1);

insert into temp_ning values(2);

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

savepoint a;

insert into temp_ning values(3);

insert into temp_ning values(4);

--回滾到指定點(diǎn)

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點(diǎn)之后的保存點(diǎn)被取消

rollback to b;

insert / update / delete

二.DDL操作:

create / drop / alter / truncate

create table 表名(

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

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

....

);

drop table 表名;

create function f_name()....

drop function f_name;

alter: 修改對(duì)象結(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;

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

--放大可以

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

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語(yǔ)言

grant / revoke

--授權(quán)

grant select on emp_ning to scott;

--撤銷(xiāo)權(quán)限

revoke select on emp_ning from scott;

sys/安裝時(shí)設(shè)置

system/安裝時(shí)設(shè)置

scott/tiger(測(cè)試用戶(hù))

...

openlab/open123

ninglj/*****

四.腳本文件

文檔

源代碼

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

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ù)庫(kù)在192.168.0.26

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

Oracle的端口號(hào): 1521

Oracle的名字(sid):tarena

sqlplus安裝在本地機(jī)器上

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

SQL>

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

--首選,命令行工具

sqlplus

--圖形工具

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

toad(非官方,收費(fèi))

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

補(bǔ)充練習(xí): procedure 過(guò)程

PL/SQL:  Procedure Language / SQL

函數(shù)(function)

過(guò)程(procedure)

包(package)

觸發(fā)器(trigger)

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

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;

/

--打開(kāi)輸出,默認(rèn)值是off

SQL>set serveroutput on

--匿名塊,用來(lái)測(cè)試過(guò)程或函數(shù)

SQL>declare

  p_student_name char(20);

  p_total_score number;

begin

  cal_ning(&no, p_student_name,       p_total_score);

  --向控制臺(tái)輸出變量值,System.out.print

  dbms_output.put_line(p_student_name);

  dbms_output.put_line(p_total_score);

end;

/

--可以用&abc符號(hào)表示在運(yùn)行時(shí)輸入變量值

select * from emp_ning

where deptno = &abc;

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

--假設(shè)是班號(hào)是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個(gè)班