Oracle数据处理

sql> /*数据库中语言:

     empno ename      job              mgr hiredate         sal       comm     deptno                                  

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

      7369 smith      clerk           7902 17-12月-80       800                    20                                  

      7499 allen      salesman        7698 20-2月 -81      1600        300         30                                  

      7521 ward       salesman        7698 22-2月 -81      1250        500         30                                  

      7566 jones      manager         7839 02-4月 -81      2975                    20                                  

      7654 martin     salesman        7698 28-9月 -81      1250       1400         30                                  

      7698 blake      manager         7839 01-5月 -81      2850                    30                                  

      7782 clark      manager         7839 09-6月 -81      2450                    10                                  

      7788 scott      analyst         7566 13-7月 -87      3000                    20                                  

      7839 king       president            17-11月-81      5000                    10                                  

      7844 turner     salesman        7698 08-9月 -81      1500          0         30                                  

      7876 adams      clerk           7788 13-7月 -87      1100                    20                                  

      7900 james      clerk           7698 03-12月-81       950                    30                                  

      7902 ford       analyst         7566 03-12月-81      3000                    20                                  

      7934 miller     clerk           7782 23-1月 -82      1300                    10                                  

已选择14行。

sql> /*

sql> 数据库中语言：

sql> dml: 数据库操作语言，select insert update delete

sql> ddl: 数据库定义语言：cretae table, create user, create view

sql> dcl: 数据库控制语言： commit,rollback,grant,revoke

sql> */

sql> --插入语句;insert

sql> --往员工表插入一条数据

sql> insert into emp(empno,ename) values(1234,'tom');

已创建 1 行。

sql> desc

用法: describe [schema.]object[@db_link]

sql> desc emp;

 名称                                                              是否为空? 类型

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

 empno                                                             not null number(4)

 ename                                                                      varchar2(10)

 job                                                                        varchar2(9)

 mgr                                                                        number(4)

 hiredate                                                                   date

 sal                                                                        number(7,2)

 comm                                                                       number(7,2)

 deptno                                                                     number(2)

sql> insert into emp values(1235,'mary','saleman',7839,sysdate,1000,1000,10);

sql> --往emp表中插入10个员工信息

sql> --oracle中地址符

sql> insert into emp values(&empno,'&ename','&job',&mgr,&hiredate,&sal,&comm,&deptno);

输入 empno 的值:  2222

输入 ename 的值:  mike

输入 job 的值:  saleman

输入 mgr 的值:  7839

输入 hiredate 的值:  sysdate

输入 sal 的值:  2000

输入 comm 的值:  1500

输入 deptno 的值:  20

原值    1: insert into emp values(&empno,'&ename','&job',&mgr,&hiredate,&sal,&comm,&deptno)

新值    1: insert into emp values(2222,'mike','saleman',7839,sysdate,2000,1500,20)

sql> --关于地址符,对于数字，&empno;对于字符串，需要单引号

sql> /

输入 empno 的值: 

输入 ename 的值: 

输入 job 的值: 

输入 mgr 的值: 

输入 hiredate 的值: 

输入 sal 的值: 

输入 comm 的值: 

输入 deptno 的值: 

新值    1: insert into emp values(,'','',,,,,)

insert into emp values(,'','',,,,,)

                       *

第 1 行出现错误:

ora-00936: 缺失表达式

sql> --在select中使用地址符

sql> select *

  2  from &table;

输入 table 的值:  emp

原值    2: from &table

新值    2: from emp

      1234 tom                                                                                                         

      1235 mary       saleman         7839 12-6月 -11      1000       1000         10                                  

      2222 mike       saleman         7839 12-6月 -11      2000       1500         20                                  

已选择17行。

输入 table 的值:  dept

新值    2: from dept

    deptno dname          loc                                                                                          

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

        10 accounting     new york                                                                                     

        20 research       dallas                                                                                       

        30 sales          chicago                                                                                      

        40 operations     boston                                                                                       

sql> select &col

  2  from emp;

输入 col 的值:  ename

原值    1: select &col

新值    1: select ename

ename                                                                                                                  

----------                                                                                                             

smith                                                                                                                  

allen                                                                                                                  

ward                                                                                                                   

jones                                                                                                                  

martin                                                                                                                 

blake                                                                                                                  

clark                                                                                                                  

scott                                                                                                                  

king                                                                                                                   

turner                                                                                                                 

adams                                                                                                                  

james                                                                                                                  

ford                                                                                                                   

miller                                                                                                                 

tom                                                                                                                    

mary                                                                                                                   

mike                                                                                                                   

sql> --关于空值的插入

sql> -- 隐式插入空值

sql> insert into emp(empno,ename) values(2345,'abc');

sql> select * from emp;

      2345 abc                                                                                                         

已选择18行。

sql> --显示插入空值

sql> insert into emp value(2346,'bbb',null,null,null,null,null,null);

insert into emp value(2346,'bbb',null,null,null,null,null,null)

                      *

ora-00928: 缺失 select 关键字

sql> ed

已写入 file afiedt.buf

  1* insert into emp values(2346,'bbb',null,null,null,null,null,null)

sql> --在插入数据中使用函数

sql> insert into emp(empno,ename,hiredate) values(1122,'ccc', to_date('2011-06-12','yyyy-mm-dd'));

sql> host cls

sql> --一次插入多条记录

sql> --创建一个新表，保存10号部门的员工信息

sql> create table emp10 as select * from emp where 1=2;

表已创建。

sql> select * from emp10;

未选定行

sql> desc emp10;

 empno                                                                      number(4)

sql> --如果where 1=1,创建表的同时，拷贝数据

sql> create table emp101 as select * from emp where 1=1;

sql> select * from emp101

  2  ;

      2346 bbb                                                                                                         

      1122 ccc                             12-6月 -11                                                                  

已选择20行。

sql> insert into emp10 (empno,ename,job,mgr,hiredate,sal,comm,deptno)

  2  select *

  3  from emp

  4  where deptno=10;

已创建4行。

sql> --更新数据: update

sql> --update emp set .... where,如果没有where，作用对象为整张表

sql> --给员工涨工资，涨1块钱

sql> update emp set sal=sal+1;

已更新20行。

      7369 smith      clerk           7902 17-12月-80       801                    20                                  

      7499 allen      salesman        7698 20-2月 -81      1601        300         30                                  

      7521 ward       salesman        7698 22-2月 -81      1251        500         30                                  

      7566 jones      manager         7839 02-4月 -81      2976                    20                                  

      7654 martin     salesman        7698 28-9月 -81      1251       1400         30                                  

      7698 blake      manager         7839 01-5月 -81      2851                    30                                  

      7782 clark      manager         7839 09-6月 -81      2451                    10                                  

      7788 scott      analyst         7566 13-7月 -87      3001                    20                                  

      7839 king       president            17-11月-81      5001                    10                                  

      7844 turner     salesman        7698 08-9月 -81      1501          0         30                                  

      7876 adams      clerk           7788 13-7月 -87      1101                    20                                  

      7900 james      clerk           7698 03-12月-81       951                    30                                  

      7902 ford       analyst         7566 03-12月-81      3001                    20                                  

      7934 miller     clerk           7782 23-1月 -82      1301                    10                                  

      1235 mary       saleman         7839 12-6月 -11      1001       1000         10                                  

      2222 mike       saleman         7839 12-6月 -11      2001       1500         20                                  

sql> --在update中使用地址符

sql> update emp set sal=&newsal where deptno=10;

输入 newsal 的值:  sal+100

原值    1: update emp set sal=&newsal where deptno=10

新值    1: update emp set sal=sal+100 where deptno=10

已更新4行。

      7782 clark      manager         7839 09-6月 -81      2551                    10                                  

      7839 king       president            17-11月-81      5101                    10                                  

      7934 miller     clerk           7782 23-1月 -82      1401                    10                                  

      1235 mary       saleman         7839 12-6月 -11      1101       1000         10                                  

sql> --外键可不可为null？

sql> --1.如果外键不为null，它必须是父表中的某个值

sql> --2.为空，子表中对应的列没有定义not null

sql> --删除表中的数据 delete truncate

sql> 1. delete一条一条删除;truncate:清空表。

sql> 2. delete可以rollback;truncate不可以

sql> 3. delete会产生碎片; truncate不会

sql> 4.

sql> --4. delete 不会释放空间;truncate释放空间

sql> --对比delete和truncate的性能

sql> @c:\sql.sql

提交完成。

sql> select count(*) from testdelete;

  count(*)                                                                                                             

      5000                                                                                                             

sql> set timing on

sql> trucate table testdelete;

sp2-0734: 未知的命令开头 "trucate ta..." - 忽略了剩余的行。

sql> truncate table testdelete;

表被截断。

已用时间:  00: 00: 00.68

sql> drop table testdelete;

表已删除。

已用时间:  00: 00: 00.15

已用时间:  00: 00: 00.01

已用时间:  00: 00: 00.00

已用时间:  00: 00: 00.07

已用时间:  00: 00: 00.06

已用时间:  00: 00: 00.11

已用时间:  00: 00: 00.04

已用时间:  00: 00: 00.09

已用时间:  00: 00: 00.03

已用时间:  00: 00: 00.10

已用时间:  00: 00: 00.17

sql> delete from testdelete;

已删除5000行。

sql> rollback;

回退已完成。

sql> --事务

sql> 开始标志：dml

sql> 显示结束：commit rollback exit(正常退出)

sql> 隐式结束: 系统掉电，系统宕机,非正常退出

sql> insert delete select, create table,grant

sql> --事务实验一：

已用时间:  00: 00: 00.23

sql> delete from emp where empn=1122;

delete from emp where empn=1122

ora-00904: "empn": 标识符无效

sql> delete from emp where empno=1122;

已删除 1 行。

sql> set timing off

sql> update emp set ename='bbb123' where empno=2346;

已更新 1 行。

sql> --使用存储点回滚事务中部分的语句

sql> savepoint a;

保存点已创建。

sql> rollback to a;

sql> commit;

      2346 bbb123                                                                                                      

sql> jdbc中：

sql> conn = jdbcutil.getconection();

sql> conn.setautocommit(false);

sql> sql1

sql> sql2

sql> savepoint sp = conn.setsavepoint();

sql> sql3

sql> sql4

sql> conn.rollback(sp);

sql> spool off