Oracle 笔记 day04
Oracle day4
20120217
=====================
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个班
