操丛数据

DML(Data Manipulation Language)：数据操纵语言命令使用户能够查询数据库以及操作已有数据库中的数据。如INSERT、DELETE、UPDATE、SELECT、MERGE(插入、删除、修改、检索、合并)等都是DML操作。好，下面我们对INSERT、DELETE、UPDATE、SELECT、MERGE做详细介绍。一、INSERT　　INSERT语句用来向表,分区或视图中添加行。可以向单表或者多个表方法中添加数据行。单表插入将会向一个表中插入一行数据,这行数据可以显式地列出插入也可以通过一个子查询来获取。多表插入将会向一个或多个表插入行,并且会通过子查询获取值来计算所插入行的值。1、单表插入:VALUES子句、SELECT子句　命令语法如下： INSERT INTO 表 [(列 [, 列...])]　VALUES　(值 [, 值...]); INSERT INTO  　表[(列 [, 列...])]　SELECT子句; 其中，SELECT子句返回多少行，就是表中插入多少行。 下面我们来做实验：　（1）第一：用VALUES子句单行插入　　实现单表插入，每一列的值都显式地输入。如果你要插入表中所定义的所有列的值，那么列的列表是可选的。但是，如果你只是提供部分列的值，则必须在列的列表中指明所需的列名。好的做法是不管是不是需要插入所有列的值，都把所有列的列表列出来。这样做就像该语句的自述文件一样，并且也可以减少将来别人要插入一个新列到表中的时候可能出现错误。hr@OCM> INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (118, 'IT DBA', 100, 1700);1 row created.hr@OCM> commit;Commit complete.当我们插入的值超过列长度定义的大小时会报下面的错误，错误很明显actual: 31, maximum: 30hr@OCM> INSERT INTO departments(department_id, department_name, manager_id, location_id)   2  VALUES (120, LPAD('IT_DEVELOP',31,'0'), 100, 1700);VALUES (120, LPAD('IT_DEVELOP',31,'0'), 100, 1700)             *ERROR at line 2:ORA-12899: value too large for column "HR"."DEPARTMENTS"."DEPARTMENT_NAME" (actual: 31, maximum: 30)当我们插入的值少于表中所定义的所有列的值就会报下面的错误：not enough valueshr@OCM> INSERT INTO     departments VALUES (121,'IT_TEST',1700);INSERT INTO     departments VALUES (121,'IT_TEST',1700)                *ERROR at line 1:ORA-00947: not enough values（2）第二：SELECT子句　首先定义一个空表hr@OCM> create table emp as select * from employees where 1=2;Table created.hr@OCM> select * from emp;no rows selected　然后用SELECT子句多行插入hr@OCM> INSERT INTO emp(employee_id, last_name,email,hire_date,salary,job_id, commission_pct)  2    SELECT employee_id, last_name,email,hire_date, salary,job_id,commission_pct  3    FROM   employees  4    WHERE  job_id LIKE '%REP%';33 rows created.通过子查询来实现插入，这是一个非常灵活的选项。所写的子查询可以返回一行或多行数据。返回的每一行都会用来生成需要插入的新行的列值。根据你的需要这个子查询可以很简单也可以很复杂。比如：hr@OCM> INSERT INTO emp(employee_id, last_name,email,hire_date,salary, commission_pct)  2    SELECT employee_id, last_name,email,hire_date, salary,commission_pct  3    FROM   employees  4    WHERE  job_id LIKE '%REP%';INSERT INTO emp(employee_id, last_name,email,hire_date,salary, commission_pct)*ERROR at line 1:ORA-01400: cannot insert NULL into ("HR"."EMP"."JOB_ID")job_id这一例在子查询中并没有占据一列并且我们也没有将它包括在列表中。因为我们没有包含这一列，它的值将会是null。注意如果job_id列具有非空约束，那么就会返一个约束错误（如上报错），语句的执行也会失败。（3）第三：子查询的进一步使用 我们甚至可以把“Insert into 表名”这其中的表名，换为一个子查询，比如：hr@OCM> INSERT INTO(SELECT employee_id, last_name,email, hire_date, job_id, salary, department_id FROM   employees)   2  VALUES (99999, 'Taylor', 'DTAYLOR',TO_DATE('07-JUN-99', 'DD-MON-RR'),'ST_CLERK', 5000, 50);1 row created.当然，这没什么意义，这个输入和insert into (employee_id, last_name,email, hire_date, job_id, salary,department_id)从功能上讲，没任何不同。后面的Values部分，同样可以换为一个子查询：hr@OCM> insert into employees (employee_id, last_name,email, hire_date, job_id, salary,department_id) select employee_id, last_name,email, hire_date, job_id, salary,department_id from emp;1 row created.我们还可以在INTO后的子查询中使用条件：hr@OCM> INSERT INTO(SELECT employee_id, last_name,email, hire_date, job_id, salary, department_id FROM   employees where department_id=50) VALUES (99999, 'Taylor', 'DTAYLOR',TO_DATE('07-JUN-99', 'DD-MON-RR'),'ST_CLERK', 5000, 40);1 row created. 不过，到现在为止，这个条件没有意义，因为我条件定的是where department_id=50，但我后面输入的department_id为40，插入照样成功了。有一个选项，可以让这个条件发挥作用：WITH  CHECK  OPTION 。它通常加在条件的后面，如下：hr@OCM> INSERT INTO(SELECT employee_id, last_name,email, hire_date, job_id, salary, department_id FROM   employees where department_id=50 WITH  CHECK  OPTION) VALUES (99999, 'Taylor', 'DTAYLOR',TO_DATE('07-JUN-99', 'DD-MON-RR'),'ST_CLERK', 5000, 40);ERROR at line 1:ORA-01402: view WITH CHECK OPTION where-clause violation这样，department_id为40，就插入不进去了。hr@OCM> INSERT INTO(SELECT employee_id, last_name,email, hire_date, job_id, salary, department_id FROM   employees where department_id=50 WITH  CHECK  OPTION) VALUES (99999, 'Taylor', 'DTAYLOR',TO_DATE('07-JUN-99', 'DD-MON-RR'),'ST_CLERK', 5000, 50);1 row created.department_id为50的行可以成功插入。在Insert into后的子查询的主要目的，就是这样的通过With check option，我们可限制插入进表中的数据。2、多表插入 我们来看下面这个例子看一下一个子查询返回的数据行是如何被用来插入多个表中的，好我们来建三个表分别是：small_customers、medium_customers、large_customers。我们想要按照每位消费者所下订单的总金额来将数据分别插入这些表。子查询将每一位消费者的order_total列求和来确定刻消费者的消费金额是小（所有订单的累加金额小于10000）、中等（介于10000与99999.99）还是大(大于等于100000),然后按照条件将这些行插入对应的表中。gyj@OCM>  create table small_customers(customer_id number,sum_orders number);Table created.gyj@OCM> create table medium_customers(customer_id number,sum_orders number);Table created.gyj@OCM> create table large_customers(customer_id number,sum_orders number);Table created.gyj@OCM>  create table orders(customer_id number,order_total number);Table created. gyj@OCM> insert into orders values(1,200); gyj@OCM> insert into orders values(1,400); gyj@OCM> insert into orders values(2,50000); gyj@OCM> insert into orders values(2,80000); gyj@OCM> insert into orders values(3,200000); gyj@OCM> insert into orders values(3,2000); gyj@OCM> commit;gyj@OCM> insert all  2    when sum_orders  3    into small_customers  4    when sum_orders >= 10000 and sum_orders  5    into medium_customers  6    else  7    into large_customers  8    select customer_id,sum(order_total) sum_orders  9    from orders 10    group by customer_id;  commit;3 rows created.gyj@OCM> Commit complete.gyj@OCM> select * from small_customers;CUSTOMER_ID SUM_ORDERS----------- ----------          1        600gyj@OCM> select * from medium_customers;CUSTOMER_ID SUM_ORDERS----------- ----------          2     130000gyj@OCM> select * from large_customers;CUSTOMER_ID SUM_ORDERS----------- ----------          3     202000注意Insert关键字后面用ALL还是FIRST,视具体情况而定。二、UPDATE更新语句的语法是UPDATE  表名  SET 列名1＝值，列名2＝值，………… WHERE 条件。UPDATE语句的作用是改变表中原有行的列值。这个语句的语法由3个部分组成:UPDATE、SET和WHERE。UPDATE子句用来指定要更新的表，SET子句用来指明哪些列改变了以及调整的值，WHERE子句用来按条件筛选需要更新的行。WHERE子句是可选的，如果忽略了这个子句的话，更新操作将针对指定表中的所有行进行。1、使用表达式更新一个单列的值 gyj@OCM> create table emp(empno number primary key,empname varchar2(10),deptno number,salary number,commission_pct number);Table created.gyj@OCM> insert into emp values(1,'Tom',10,5000,0.2);1 row created.gyj@OCM> insert into emp values(2,'Joe',10,5000,0.2);1 row created.gyj@OCM> insert into emp values(3,'lewis',20,5000,0.2);1 row created.gyj@OCM>  commit;Commit complete.gyj@OCM> update emp set salary=salary*1.1 where deptno=10;2 rows updated.gyj@OCM> commit;Commit complete.2、通过子查询更新一个单列的值gyj@OCM> create table emp2(empno number primary key,empname varchar2(10),deptno number,salary number,commission_pct number);Table created.gyj@OCM> insert into emp2 values(1,'rose',10,8000,0.1);1 row created.gyj@OCM> insert into emp2 values(2,'alan',20,8000,0.1);1 row created.gyj@OCM> insert into emp2 values(3,'jemy',30,8000,0.1);1 row created.gyj@OCM>  commit;Commit complete.gyj@OCM> update emp a set salary=(select salary from emp2 b where a.empno=b.empno and a.salary != b.salary) where deptno=10;2 rows updated.gyj@OCM> commit;Commit complete.3、通过在where子句使用子查询确定要更新的数据行来更新单列值gyj@OCM>  create table department(deptno number,deptname varchar2(10));Table created.gyj@OCM>  insert into department values(10,'HR');1 row created.gyj@OCM> insert into department values(20,'SALES');1 row created.gyj@OCM> insert into department values(30,'DEV');1 row created.gyj@OCM> commit;Commit complete.gyj@OCM> update emp a set salary=salary*1.1  where deptno in (select deptno from department  where deptname='HR');2 rows updated.gyj@OCM> commit;Commit complete.4、通过使用SELECT语句定义表列的值来更新表gyj@OCM> update (select a.salary,b.salary new_sal from emp a,emp2 b where a.empno=b.empno and a.deptno=10) set salary= new_sal;2 rows updated.gyj@OCM> commit;Commit complete. 5、通过子查询更新多列gyj@OCM> update emp a set (salary,commission_pct) = (select b.salary,b.commission_pct from emp2 b where a.empno=b.empno) where deptno=10;  2 rows updated.gyj@OCM> commit;Commit complete.三、DELETE语法格式：DELETE [FROM]  表 [WHERE 条件];DELETE语句用来从表中移除数据行。该语句的语法结构由3部分组成：DELETE、FROM和WHERE。DELETE关键字是单独列出的。除非使用提示(hint),没有其它选项与DELETE关键字相结合。FROM子句用来指定哪个表中删除数据行，这个表可直接指定也可以通过子查询来确定。WHERE子句提供筛选条件有助于确定哪些行是要删除的。如果忽略了WHERE子句，删除操作将删除指定表中的所有数据行。1、使用WHERE子句中的筛选条件来指定表中删除行gyj@OCM> delete from emp2 where deptno=10;1 row deleted.gyj@OCM> commit;Commit complete.2、使用FROM子句的子查询来删除行gyj@OCM> delete from (select * from emp2 where deptno=20);1 row deleted.gyj@OCM> commit;Commit complete.3、使用WHERE子句中的子查询来从指定表中删除行gyj@OCM>  delete from emp2 where deptno in (select deptno from department where deptname='DEV');1 row deleted.gyj@OCM> commit;Commit complete.四、TRUNCATE1、语法：TRUNCTAE TABLE 表名；在DELETE后不加条件，如“DELETE 表名”，这条语句就可以删除表中的所有行，但是如果你的确要删除所有行的话，有一个执行速度更快的方法，就是截断表命令，这个命令是DDL命令，也就是说，在删除所有行后，不需提交。同样的，如果在删除所有行后，你后悔了，那没办法，谁让你用TRUNCATE删除所有行了。DELETE删除行后，是可以通过ROLLBACK回滚操作，以恢复被删除的行。TRUNCATE操作如下：gyj@OCM> select * from emp;     EMPNO EMPNAME        DEPTNO     SALARY COMMISSION_PCT---------- ---------- ---------- ---------- --------------         1 Tom                10       8000             .1         2 Joe                10       8000             .1         3 lewis              20       5000             .2gyj@OCM> truncate table emp;Table truncated.gyj@OCM> select * from emp;no rows selected2、truncate与delete区别   （1）tuncate会降高水位（HWM）到初始位置   （2）tuncate是DDL,delete是DML   （3）tuncate会释放段的空间，delete不会释空间　（4）dba_objects->DATA_OBJECT_ID五、Merge语句Merge的语法格式如下：MERGE INTO  表名 [别名]USING (表名| 子查询) [别名] ON  (连接条件)WHEN MATCHED THENUPDATE SET 列1 = 值, 列2 = 值，……WHEN NOT MATCHED THENINSERT (列名表) VALUES (值列表);Merge语句具有按条件获取要更新或插入到表中的数据行，然后从1个或多个源头对表进行更新或者向表中插入行两方面的能力。它最经常被用在数据仓库中来移动大量的数据，但它的应用不仅限于数据仓库环境下。这个语句提供的一个很大的附加值在于你可以很方便地把多个操作结合成一个。这就使你可以避免使用多个INSERT、UPDATE及DELETE语句。 Merge语句比较复杂，为了能深入理解它，我们一起来做测试：gyj@OCM> create table dept60_bonuses  2  (employee_id number  3  ,bonus_amt number);Table created.gyj@OCM> insert into dept60_bonuses values (103, 0);1 row created.gyj@OCM> insert into dept60_bonuses values (104, 100);1 row created.gyj@OCM> insert into dept60_bonuses values (105, 0);1 row created.gyj@OCM> commit;Commit complete.gyj@OCM> select employee_id, last_name, salary  2  from hr.employees  3  where department_id = 60 ;EMPLOYEE_ID LAST_NAME                     SALARY----------- ------------------------- ----------        103 Hunold                          9000        104 Ernst                           6000        105 Austin                          4800        106 Pataballa                       4800        107 Lorentz                         4200gyj@OCM> select * from dept60_bonuses;EMPLOYEE_ID  BONUS_AMT----------- ----------        103          0        104        100        105          0gyj@OCM> merge into dept60_bonuses b  2  using (  3  select employee_id, salary, department_id  4  from hr.employees  5  where department_id = 60) e  6  on (b.employee_id = e.employee_id)  7  when matched then  8  update set b.bonus_amt = e.salary * 0.2  9  where b.bonus_amt = 0 10  delete where (e.salary > 7500) 11  when not matched then 12  insert (b.employee_id, b.bonus_amt) 13  values (e.employee_id, e.salary * 0.1) 14  where (e.salary4 rows merged.gyj@OCM> select * from dept60_bonuses;EMPLOYEE_ID  BONUS_AMT----------- ----------        104        100        105        960        106        480        107        420gyj@OCM> rollback ;Rollback complete.Merge语句完成了下面的这些事情。插入了两行(员工id 106和107）。更新了一行(员工id 105)删除了一行（员工id 103）一行保持不变(员工id 104)如果没有Merge语句，你必须最少写3条不同的语句来完成同样的事情。六、事务1、什么是事务（1）事务的定义是一个独立的逻辑工作单元：它由特定的一系列必须作为一个整体一起成功或失败的SQL语句组成。（2）事务可以由多个数据操作语言(data manipulation language,DML)语句组成，但只能含有一个数据定义语言(data definition language,DDL)语句。2、事务的ACID特征（1）原子性(Atomicity)     事务中的所有动作要么都发生，要么都不发生（2）一致性(Consistency)     事务将数据库从一种状态转变为下一种一致状态（3）隔离性(Isolation)    一个事务的影响在该事务提交前对其他事务都不可见（4）持久性(Durability)      事务一旦提交，其结果就是永久性的 3、事务的隔离级别 （1）ANSI/ISO SQL标准定义了4个不同的事务隔离级别    隔离级别              脏读             不可重复读      幻读    READ UNCOMMITTED     允许                允许          允许    READ COMMITTED       Ｘ                  允许          允许    REPEATABLE READ      Ｘ                  Ｘ            允许    SERIALIZABLE　　　　 Ｘ                  Ｘ　　　　　　Ｘ       （2）Oracle的隔离级别    ①COMMIT;--不可重复读和幻读    ②SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;--允许更新，可重复读    ③SET TRANSACTION READ ONLY;--不允许更新，可重复读4、事务的控制语句（1）、Commit(提交);  ①默认是以commit write wait immediate处理提交  ②也可以使用Write Nowait来进行异步提交：ALTER SESSION SET COMMIT_WRITE = NOWAIT;（2）Rollback(回滚);gyj@OCM> select * from t2;        ID NAME           SALARY---------- ---------- ----------         1 gyj1             5000         2 gyj2             8000         3 gyj3gyj@OCM> delete from t2;3 rows deleted.gyj@OCM> select * from t2;no rows selectedgyj@OCM> rollback;Rollback complete.gyj@OCM> select * from t2;        ID NAME           SALARY---------- ---------- ----------         1 gyj1             5000         2 gyj2             8000         3 gyj3（3）Savepoint(保存点) 回滚命令不一定每次都回滚的事务的开始。有时可能需要只回滚事务中的一部分操作。这就要用到保留点。                                                           gyj@OCM> select * from t2;        ID NAME           SALARY---------- ---------- ----------         1 gyj1             5000         2 gyj2             8000         3 gyj3gyj@OCM> delete from t2 where id=1;1 row deleted.gyj@OCM> savepoint t1;Savepoint created.gyj@OCM> delete from t2 where id=2;1 row deleted.gyj@OCM> savepoint t2;Savepoint created.gyj@OCM> delete from t2 where id=3;1 row deleted.gyj@OCM> savepoint t3;Savepoint created.gyj@OCM> select * from t2;no rows selectedgyj@OCM> rollback to t2;Rollback complete.gyj@OCM> select * from t2;        ID NAME           SALARY---------- ---------- ----------         3 gyj3（4）Set Transaction(设置事务)    (1)set transaction read only;      --注意：SYS用户并不受SET TRANSACTION READ ONLY的影响    (2) set transaction isolation level serializable（5）Set Constraints(设置约束)　　(1)set constraint cons_t1 immediate;　　(2)set constraint cons_t1 deferred;5、隐式提交和回滚（1）DDL　　 两次提交！！！（2）退出　　①异常退出--回滚,有时提交    ②正常退出--提交6、分布式事务（1）Oracle中分布式事务的关键是数据库链接(database link)。　select * from t1@annother_database;    create synonym t1 for t1@another_database;    update local_table set x=10;    update remoter_table@another_database set y=20;    commit;（2）2PC分布式协议　 ①分布式协调器   ②RECO:恢复进程,能够自动解决分布事务中的故障7、自治事务（1）自治事务定义：    在你的主事务中，你可以选择能够从其他事务中进行调用的独立事物。自治事务可以提交或回滚其修改而不影响调用它的主事务。（2）自治事务主要用在:    ①错误日志    ②某些情况下的审计（3）创建自治订单记录事务 ①建订单表和订单日志表gyj@OCM> create table order_log  2       (customer_id number not null,  3        order_id    number not null,  4        order_date  date   not null,  5        order_outcome varchar2(10),  6        constraint order_log_pk primary key(customer_id,order_id,order_date)  7        );Table created.gyj@OCM>  create table order_info  2       (streamid      number not null,  3        customerid    number not null,  4        opmoney       number not null,  5        optime        date   not null,  6        constraint order_info_pk primary key(streamid)  7        );Table created.②建自治订单记录事务gyj@OCM>  create or replace procedure record_new_order(p_customer_id in number,p_order_id in number)  2        as  3       pragma autonomous_transaction;  4       begin  5         insert into order_log(customer_id,order_id,order_date) values (p_customer_id,p_order_id,sysdate);  6         commit;  7       end;  8       /Procedure created.③订单交易gyj@OCM>  create or replace procedure kzcz214001(customer_id in number,  2                                         order_id    in number,  3                                         opmoney     in number) as  4     begin  5       insert into order_info values(customer_id,order_id,opmoney,sysdate);  6       record_new_order(customer_id, order_id);  7       rollback;  8     end;  9     /Procedure created.④执行过程gyj@OCM>  exec kzcz214001(0002,0004,100);PL/SQL procedure successfully completed.查下面两个表已达到了想要的目的，记录了订单操作日志，而记录单订数据！gyj@OCM> select * from order_log;CUSTOMER_ID   ORDER_ID ORDER_DAT ORDER_OUTC----------- ---------- --------- ----------          2          4 25-FEB-13gyj@OCM> select * from order_info;no rows selected七、一致性读有关一致读，我们举一个非常简单的例子：步1：以GYJ用户连接到数据库（这一步也可以叫打开一个会话）步2：删除T3中的所有行：gyj@OCM> delete from t2;3 rows deleted.步3：仍以GYJ用户，再打开一个会话（也就是说仍以GYJ用户连接到数据库）步4：在第二个会话中，显示T2gyj@OCM> select * from t2;        ID NAME           SALARY---------- ---------- ----------         1 gyj1             5000         2 gyj2             8000         3 gyj3我先用DELETE删除了T2中的行，在另一会话中，我仍可以看到T2中的行，这就是因为在删除行前，ORACLE把前映像也就是未删除时的值，存进了回滚段中。在另一会话中读取T2时，是到回滚段中读取的T2表中数据。这就是一致读。在其他数据库中，会话2的“select * from t2”，是要被阻塞的。直到会话中的事务提交或回滚。读一致性保证在相同的数据:读不阻塞写，写不阻塞读！                                                                **********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********QQ: 252803295Email:[email protected]尖峰官网：http://www.jianfengedu.com尖峰淘宝：http://jianfengedu.taobao.comWEIBO：http://weibo.com/guoyJoe0218尖峰OCP认证考试群297227448 尖峰OCM认证考试群99606943尖峰MySQL研究院群314746420尖峰JAVA研究院群315405063  尖峰Hadoop研究院群366294602尖峰线上技术分享群252296815尖峰SQL优化研究院群250057366