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');

--数据没有全部提供,必须写列名

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中重复记录非常多的场合.

              复制非重复记录

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

删掉表bak1, 把bak2改名为bak1.

--适合bak1中重复记录非常少的场合.

--直接在bak1中删除.

--查询重复记录中地址最大的那条

select empno, ename, salary,

       max(rowid)

from emp_bak1_ning

group by empno, ename, salary;

--删除除了地址最大的重复记录之外所有的记录.

delete emp_bak1_ning

where rowid not in (

select max(rowid)

from emp_bak1_ning

group by empno, ename, salary);

rownum, rowid是Oracle独有的.

事务 Transaction

commit / rollback

-----事务开始--------------

DML:insert / delete / update

commit(提交,确认),

或者 rollback(回滚,撤销)

-----事务终止--------------

A                B

insert : 1234    select (查不到)

commit          

                 可以查到 1234

update          

                 update(挂起)

commit(释放锁)   修改成功

update(挂起)

                 rollback;

修改成功.

---开始事务-----

update account

set money = money - 500

where id = 'A';

update account

set money = money + 500

where id = 'B';

if (都成功)

         commit;

else

             rollback;

-----事务终止-----

正常退出会话: commit

异常退出会话: rollback

DDL操作: commit

create table temp_ning (id number);

insert into temp_ning values(1);

insert into temp_ning values(2);

--设置保存点,名字是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数据类型,

列2 列2数据类型,

....

);

drop table 表名;

create function f_name()....

drop function f_name;

alter: 修改对象结构

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)

--放大可以

--缩小的话, 如果数据有超过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 列名;

--截取表:把表数据全部删除.

--立即生效,不能rollback

truncate table temp_ning;

delete temp_ning + commit

--三思而后行

drop table ....;

truncate table ...;

三.DCL语言

grant / revoke

--授权

grant select on emp_ning to scott;

--撤销权限

revoke select on emp_ning from scott;

sys/安装时设置

system/安装时设置

scott/tiger(测试用户)

...

openlab/open123

ninglj/*****

四.脚本文件

文档

源代码

数据库脚本文件

1.创建脚本文件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中批处理执行脚本文件

SQL>@ /user/openlab/script_ning.sql

 

数据库在192.168.0.26

数据库的口令:openlab/open123

Oracle的端口号: 1521

Oracle的名字(sid):tarena

sqlplus安装在本地机器上

 

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

SQL>

 

连接数据库的工具:

--首选,命令行工具

sqlplus

--图形工具

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

toad(非官方,收费)

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

补充练习: procedure 过程

PL/SQL:  Procedure Language / SQL

函数(function)

过程(procedure)

包(package)

触发器(trigger)

--输入班号,输出最高分的学生名字和总成绩

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

--匿名块,用来测试过程或函数

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;

--改进版,输入共多少个班,输出每个班的最高成绩的学生姓名和总分

--假设是班号是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个班