SQL基础--> 数据处理(DML、RETURNING、MERGE INTO)

--=================================================

--SQL基础--> 数据处理(DML、RETURNING、MERGE INTO)

--=================================================

一、常用的DML语句及事物处理

向表中插入数据(INSERT)

更新表中数据(UPDATE)

从表中删除数据(DELETE)

将表中数据和并(MERGE)

控制事务(TRANSACTION)

二、DML 可以在下列条件下执行:

向表中插入数据

修改现存数据

删除现存数据

事务是由完成若干项工作的DML语句组成的。

三、插入数据

INSERT 语句语法:

INSERT INTO table [(column [, column...])]

VALUES (value [, value...]);

使用这种语法一次只能向表中插入一条数据。

为每一列添加一个新值。

按列的默认顺序列出各个列的值。

在INSERT 子句中随意列出列名和他们的值。

字符和日期型数据应包含在单引号中。

--查看emp表的表结构

SQL> DESC emp;

Name Null? Type

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

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(30)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SALARY NUMBER(8,2)

DEPTNO NUMBER(2)

1.向表中插入空值

隐式方式: 在列名表中省略该列的值。

SQL> INSERT INTO emp(empno,ename,job,salary) --列出部分列名

2 VALUES(1234,'Frank','saleman',8000);

1 row created.

SQL> SELECT * FROM emp WHERE ename='Frank';

EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO

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

1234 Frank saleman 8000

显示方式: 在VALUES 子句中指定空值NULL。

注意此处省略了列列表,当列的列表被省略时,则values关键字中应当为所有的字段列提供列值

SQL> INSERT INTO emp VALUES(100,'Jack','manager',null,null,20000,10);

1 row created.

2.插入指定的值

SYSDATE 记录当前系统的日期和时间。

SQL> INSERT into EMP(empno,ename,job,hiredate,salary)

2 VALUES(1235,'Tony','boy',sysdate,7000);

1 row created.

SQL> ALTER SESSION SET nls_date_format= 'yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT * FROM emp WHERE ename = 'Tony';

EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO

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

1235 Tony boy 2010-06-28 13:48:59 7000

插入特定的日期值

SQL> INSERT INTO emp

2 VALUES(1236,'Ben','IT',null,TO_DATE('JUN 28 2010','MON DD YYYY'),3000,20);

3.在列中使用单引号和双引号

--单引号的使用

SQL> INSERT INTO emp VALUES

2 (1238,'Dan','Malley''K','',sysdate,2900,20);

1 row created.

--双引号的使用

SQL> INSERT INTO emp VALUES

2 (1239,'Dane','A "Big" L','',sysdate,2900,20);

1 row created.

SQL> SELECT * FROM emp WHERE ename LIKE 'Dan%';

EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO

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

1238 Dan Malley'K 2010-06-28 14:04:35 2900 20

1239 Dane A "Big" L 2010-06-28 14:07:12 2900 20

4.从其它表中拷贝数据(利用子查询向表中插入数据)

在INSERT 语句中加入子查询。

不必书写VALUES 子句。

子查询中的值列表应与INSERT 子句中的列名对应

--克隆表结构

SQL> CREATE TABLE test AS SELECT * FROM scott.emp WHERE 0 = 1;

Table created.

--使用select 子句插入新值

SQL> INSERT INTO test SELECT * FROM scott.emp;

21 rows created.

--使用select 子句插入部分列值

SQL> INSERT INTO test(empno,ename,job,salary)

2 SELECT empno,ename,job,salary

3 FROM scott.emp

4 WHERE ename LIKE 'Dan%';

2 rows created.

5.利用替代变量向表中插入数据

SQL> INSERT INTO test(empno,ename,job,salary)

2 VALUES(&empno,'&ename','&job',&salary);

Enter value for empno: 1240

Enter value for ename: Andy

Enter value for job: Singer

Enter value for salary: 3600

old 2: VALUES(&empno,'&ename','&job',&salary)

new 2: VALUES(1240,'Andy','Singer',3600)

1 row created.

四、更新数据(UPDATE)

语法:

UPDATE table

SET column = value [, column = value, ...]

[WHERE condition];

可以一次更新多条数据。

使用WHERE 子句指定需要更新的数据,如果省略WHERE子句,则表中的所有数据都将被更新。

1.直接更新

SQL> UPDATE emp SET salary = salary + 100 WHERE ename = 'SCOTT';

1 row updated.

2.在UPDATE语句中使用子查询

SQL> UPDATE emp SET salary =

2 (SELECT salary FROM emp WHERE ename = 'SCOTT')

3 WHERE empno = 7839;

1 row updated.

3.使用多列子查询来修改记录:

SQL> UPDATE emp SET(job,salary)

2 = (SELECT job,salary FROM emp WHERE ename = 'SCOTT')

3 WHERE ename = 'Jack';

1 row updated.

五、删除数据

使用DELETE 语句从表中删除数据。

DELETE [FROM] table [WHERE condition];

1.使用WHERE 子句指定删除的记录,如果省略WHERE子句,则表中的全部数据将被删除。

SQL> DELETE FROM emp WHERE empno = 1234;

1 row deleted.

2.在DELETE 中使用子查询

在DELETE 中使用子查询,使删除基于另一个表中的数据。

SQL> DELETE FROM emp

2 WHERE deptno =

3 (SELECT deptno FROM scott.dept WHERE dname = 'ACCOUNTING');

4 rows deleted.

六、在DML语句中使用WITH CHECK OPTION

子查询可以用来指定DML语句的表和列

WITH CHECK OPTION 关键字可以防止更改不在子查询中的行

--deptno列不在select列表中,故不能被更新

SQL> INSERT INTO

2 (SELECT empno,ename,job,mgr,salary FROM emp

3 WHERE deptno = 20 WITH CHECK OPTION)

4 VALUES(1250,'Smith','Clerk',7902,3000);

(SELECT empno,ename,job,mgr,salary FROM emp

*

ERROR at line 2:

ORA-01402: view WITH CHECK OPTION where-clause violation

七、TRUNCATE TABLE 截断表

删除所有数据,保留表结构

TRUNCATE TABLE语句不能回滚

SQL> TURNCATE TABLE emp;

八、使用默认值

显式默认值

使用DEFAULT 关键字表示默认值

可以使用显示默认值,控制默认值的使用

显示默认值可以在INSERT 和UPDATE 语句中使用

SQL> CREATE TABLE tb

2 (

3 orderid INT PRIMARY KEY,

4 status VARCHAR2(20) DEFAULT 'Delivery' NOT NULL,

5 last_update DATE DEFAULT sysdate

6 );

Table created.

--自动使用默认值

SQL> INSERT INTO tb(orderid) SELECT 10 FROM DUAL;

1 row created.

--指定新值来覆盖默认值

SQL> INSERT INTO tb SELECT 20, 'No Delivery','28-MAY-10' FROM DUAL;

1 row created.

--使用default关键字来设置为默认值

SQL> SELECT * FROM tb;

ORDERID STATUS LAST_UPDA

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

10 Delivery 28-JUN-10

20 No Delivery 28-MAY-10

SQL> UPDATE tb SET status = DEFAULT WHERE orderid = 20;

1 row updated.

SQL> SELECT * FROM tb;

ORDERID STATUS LAST_UPDA

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

10 Delivery 28-JUN-10

20 Delivery 28-MAY-10

九、RETURNING 子句

使用RETURNING子句返回聚合函数的结果集

SQL> VARIABLE avg_salary NUMBER

SQL> UPDATE emp SET salary = salary + 100

2 RETURNING AVG(salary) INTO : avg_salary;

16 rows updated.

SQL> PRINT avg_salary;

AVG_SALARY

----------

2554.6875

十、MERGE INTO

将一个表中的行合并到另一个表中

MERGE INTO 语法:

MERGE INTO table_name table_alias

USING (table|view|sub_query) alias

ON (join condition)

WHEN MATCHED THEN

UPDATE SET

col1 = col_val1,

col2 = col2_val

WHEN NOT MATCHED THEN

INSERT (column_list)

VALUES (column_values);

--创建演示环境

SQL> CONN hr/hr;

Connected.

--从hr.job_history提取唯一的数据并复制到新表job_hs中

SQL> CREATE TABLE job_hs AS

2 SELECT employee_id,start_date,end_date,job_id,department_id FROM job_history jh

3 WHERE end_date =

4 (SELECT MAX(end_date) FROM job_history WHERE employee_id = jh.employee_id);

Table created.

SQL> SELECT * FROM job_hs ORDER BY employee_id;

EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID

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

101 28-OCT-93 15-MAR-97 AC_MGR 110

102 13-JAN-93 24-JUL-98 IT_PROG 60

114 24-MAR-98 31-DEC-99 ST_CLERK 50

122 01-JAN-99 31-DEC-99 ST_CLERK 50

176 01-JAN-99 31-DEC-99 SA_MAN 80

200 01-JUL-94 31-DEC-98 AC_ACCOUNT 90

201 17-FEB-96 19-DEC-99 MK_REP 20

--将hr.employees中的记录当job_hs中存在时,则更新相关项,否则插入到job_hs表中

SQL> MERGE INTO job_hs h

2 USING employees e

3 ON (h.employee_id = e.employee_id)

4 WHEN MATCHED THEN

5 UPDATE

6 SET

7 start_date = sysdate,

8 end_date = sysdate + 100,

9 job_id = e.job_id,

10 department_id = e.department_id

11 WHEN NOT MATCHED THEN

12 INSERT (h.employee_id,h.start_date,h.end_date,h.job_id,h.department_id)

13 VALUES (e.employee_id,e.hire_date,sysdate,e.job_id,e.department_id);

SQL> SELECT COUNT(*) FROM job_hs;

COUNT(*)

----------

107

MERGE INTO 使用注意事项

MERGE INTO子句应指明需要合并的目的表

USING ... ON 子句用于表之间的连接

WHEN MATCHED THEN 子句指明当条件满足时则对目的表执行何种操作(此处是UPDATE操作)

WHEN NOT MATCHED THEN 子句指明当条件不满足时对目的表执行何种操作(此处是INSERT操作)

十一、更多

Oracle 用户、对象权限、系统权限

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

SQL 基础--> 视图(CREATE VIEW)

Oracle 常用目录结构(10g)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

使用copy命令解决LONG类型的困扰(r2第24天)

在oracle的数据类型中,long类型算是一个比较另类的典型,早就不建议使用了,但是在数据字典里还是能看到long 类型的影子。 如果在一些工作中碰到long...

36260
来自专栏性能与架构

Mysql DISTINCT的实现思路

DISTINCT实际上和GROUP BY操作非常相似,只不过是在GROUP BY之后的每组中只取出一条记录而已 所以,DISTINCT的实现方式和GROUP B...

38770
来自专栏java工会

十个mysql语句的优化方法

21460
来自专栏测试开发架构之路

分分钟搞懂union与union all

SQL UNION 操作符 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的...

36390
来自专栏Python攻城狮

PostgreSQL语法、连接

博客地址:https://ask.hellobi.com/blog/zhiji 欢迎大家来交流学习。

14110
来自专栏沃趣科技

innodb存储引擎锁的实现

通常,我们在95%以上的MySQL使用场景中,从一定程度上来讲,就是在使用InnoDB存储引擎,很多时候我们选择使用InnoDB存储引擎的原因,就是因为它支持高...

15950
来自专栏C# 编程

3 - SQL Server 2008 之 使用SQL语句删除约束条件

基本语法为: ALTER TABLE 表名 DROP CONSTRAINT 约束名1,约束名2…… 如果删除一个约束,不需要逗号后的约束名,如果删除两个及两个以...

21600
来自专栏散尽浮华

mysql操作命令梳理(1)-索引

1、创建索引 索引的创建可以在CREATE TABLE语句中进行,也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。以下命令语句分别展...

22360
来自专栏Java呓语

第13.1.1章 语法解释ALTER DATABASE

ALTER DATABASE 允许修改数据库的特征,所有关于数据库的特征都存储在db.opt文件中。你必须先具备该数据库的ALTER特权,才能完成对数据库的AL...

8610
来自专栏Netkiller

数据库安全·用户/角色认证

以下节选择《Netkiller Architect 手札》 地址 http://www.netkiller.cn/architect/ 接下来几周的话题是数据库...

36250

扫码关注云+社区

领取腾讯云代金券