oracle SQL语句练习MERGE、模糊查询、排序、

Oracle支持的SQL指令可分为数据操作语言语句、数据定义语言语句、事务控制语句、会话控制语句等几种类型：

1、数据操作语言语句

数据操作语言语句（Data manipulation language，DML）用于进行数据的检索和更新操作。数据检索是数据库应用中使用频率最高的操作类型，因此数据检索的效率对数据库的整体性能影响显著。数据更新包括数据的插入、修改和删除等操作，数据更新操作具有一定的风险性，在其执行过程中DBMS必须保证数据的一致性，以确保数据有效。

SELECT、INSERT、DELETE、UPDATE、MERGE

2、数据定义语言语句

数据定义语言语句（Data definition language，DDL）用于定义数据的格式和形态，比如定义数据表、视图和索引等数据库对象。在建立数据库时用户首先要使用的就是DDL语句。

CREATE、ALTER、DROP、RENAME、TRUNCATE

3、数据控制语言语句

数据控制语言（Data Control Language，DCL）用于对用户授权或撤销其权限，也可使用角色实现对用户的批量授权或撤销权限，在应用开发层面较少用到，

GRANT、REVOKE

4、事务控制语句

事务控制语句（Transaction Control Statement）用于实现数据库事务管理

COMMIT、ROLLBACK、SAVEPOINT

5、会话控制语句

会话控制语句（Session Control Statement）用于动态修改当前用户会话的属性，在应用开发层面极少用到。

ALTER SESSION、SET ROLE

二、SQL基础

1、删除表中数据

使用DELET指令，可删除数据表中已有的数据，例如：

DELETE student; 或：

DELETE FROM student;

这是"DELETE"指令的最基本形式，此操作会删除student表中的所有数据，但表的结构仍存在，即此后还可以对该表进行插入数据操作。

2、查看表结构

使用"DESC"或" DESCRIBE "指令可以查看指定表的结构，包括表中各字段的名称、数据类型、及是否允许为空值等。例如：

DESC student;

DESCRIBE student;

3、删除表

使用"DROP"指令删除已有的数据表，包括表中数据和表的结构，例如：

DROP TABLE student;

删除后student表不再可用。

INSERT INTO myemp (SELECT ename, sal, hiredate FROM emp);

INSERT INTO myemp(name, salary) (SELECT ename, sal FROM emp);

--创建样本表

CREATE TABLE myemp1(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), salary NUMBER(7,2));

CREATE TABLE myemp2(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), salary NUMBER(7,2));

CREATE TABLE myemp3(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), salary NUMBER(7,2));

CREATE TABLE myempOther(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), salary NUMBER(7,2));

INSERT ALL WHEN job='MANAGER' THEN INTO myemp1

WHEN job='CLERK' THEN INTO myemp2

WHEN job='ANALYST' THEN INTO myemp3

ELSE INTO myempOther

SELECT empno, ename, job, sal FROM emp;

修改某一个字段：

ALTER TABLE EMP2 RENAME COLUNM SALARY TO SAL;

========================

MERGE操作

MERGE操作用于实现数据合并——根据条件在表中执行数据的修改或插入操作，如果要插入的记录在目标表中已经存在，则执行更新操作、否则执行插入操作。其语法格式如下：

MERGE INTO dest_table [alias]

USING (source_table | view | sub_query) [alias]

ON (join_condition)

WHEN MATCHED THEN

UPDATE SET column1 = column1_value, column2 = column2_value

WHEN NOT MATCHED THEN

INSERT (column_list) VALUES (column_values);

其中，dest_table用于指定要将数据合并到的目标表，source_table（view，sub_query）为提供数据的源表（视图或子查询），ON子句用于指定合并操作中数据的连接条件。针对源表中的每一条记录，如果在目标表中找到与其相应的记录（符合连接条件的记录），则执行WHEN MATCHED THEN分支的UPDATE语句，修改目标表中相应记录；否则执行WHEN NOT MATCHED THEN分支的INSERT语句，将源表的当前记录插入到目标表中。

下面给出一个直观的例子：

DROP TABLE test1;

DROP TABLE test2;

--创建源数据表test1，插入两条样本数据

CREATE TABLE test1(eid NUMBER(10), name VARCHAR2(20),birth DATE,salary NUMBER(8,2));

INSERT INTO test1 VALUES (1001, '张三', '20-5月-1970', 2300);

INSERT INTO test1 VALUES (1002, '李四', '16-4月-1973', 6600);

--SELECT * FROM test1;

--创建目标数据表test2，插入一条样板数据

CREATE TABLE test2(eid NUMBER(10), name VARCHAR2(20),birth DATE,salary NUMBER(8,2));

INSERT INTO test2 VALUES (1001, '王五', '29-11月-2008', 2300);

--SELECT * FROM test2;

--将源表test1中数据合并到目标表test2

MERGE INTO test2 USING test1 ON(test1.eid = test2.eid ) WHEN matched THEN UPDATE SET name = test1.name, birth = test1.birth, salary = test1.salary WHEN NOT matched THEN INSERT (eid, name, birth, salary) VALUES(test1.eid, test1.name, test1.birth, test1.salary);

--查询合并后目标表test2中数据内容

SELECT * FROM test2;

SQL> SELECT * FROM test2;

EID NAME BIRTH SALARY

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

1001 张三 10-1月 -10 2300

1002 李四 10-1月 -10 6600

可以看出，由于test2表中已存在eid=1001的记录行，本次合并操作中，只是对其进行了修改操作，同时还其中插入了一条新记录（eid=1002）。

需要注意的是，在进行MERGE操作时，WHEN matched THEN分支的UPDATE语句中不允许更新建立表间记录连接关系的参照字段（如上述的eid字段），因为这样会破坏记录间的对应关系。例如，上述MERGE语句如改为如下形式：

MERGE INTO test2 USING test1 ON(test1.eid = test2.eid ) WHEN matched THEN UPDATE SET eid = test1.eid, name = test1.name, birth = test1.birth, salary = test1.salary WHEN NOT matched THEN INSERT (eid, name, birth, salary) VALUES(test1.eid, test1.name, test1.birth, test1.salary);

第 1 行出现错误:

ORA-38104: 无法更新 ON 子句中引用的列: "TEST2"."EID"

===============================================================================

Sql Plus中的自动提交

Sql Plus中执行SQL语句时缺省设置为非自动提交，也可以使用如下指令显式设置是否自动提交：

SET AUTOCOMMIT ON; --设置为自动提交

SET AUTOCOMMIT OFF; --设置为非自动提交

上述设置只在本次数据库连接会话有效，待重启Sql Plus后会恢复到其缺省设置。也可以执行下述命令查看其当前提交方式：

SHOW AUTOCOMMIT;

如返回结果“autocommit OFF”则为非自动提交，如返回“autocommit IMMEDIATE”则为自动提交。

自动提交模式会影响应用程序的性能和事务逻辑，如果应用程序需要事务的完整性，则不能使用自动提交。

====================================

保存点

如果需要，还可以在事务中使用保存点（Savepoint）在当前的事务中的关键点处创建标记，将来就可以回退到指定的标记（保存点）处，以实现事务的部分回滚。例如：

INSERT INTO dept VALUES(55,'Adv','Beijing');

INSERT INTO dept VALUES (56,'Sec','Shanghai');

SAVEPOINT p1;

INSERT INTO dept VALUES (57,'Acc','Tianjin');

---

SELECT * FROM dept;

ROLLBACK TO p1;

SELECT * FROM dept;

=================================

使用连接表达式

在Oracle数据库中，可以使用连接运算符"||"把字段与文本、或其它表达式连接在一起，得到一个新的字符串，实现“合成列”的功能。例如下述语句：

SQL> SELECT '编号:' || empno , ename || '的工资为:', sal || '元' FROM emp;

'编号:'||EMPNO ENAME||'的工资为:' SAL||'元'

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

编号:7369 SMITH的工资为: 800元

编号:7499 ALLEN的工资为: 1600元

编号:7521 WARD的工资为: 1250元

编号:7566 JONES的工资为: 2975元

编号:7654 MARTIN的工资为: 1250元

编号:7698 BLAKE的工资为: 2850元

编号:7782 CLARK的工资为: 2450元

编号:7788 SCOTT的工资为: 3000元

编号:7839 KING的工资为: 5000元

编号:7844 TURNER的工资为: 1500元

编号:7876 ADAMS的工资为: 1100元

编号:7900 JAMES的工资为: 950元

编号:7902 FORD的工资为: 3000元

编号:7934 MILLER的工资为: 1300元

SQL> SELECT 'Dept.'||deptno || ''s name is '|| dname FROM DEPT;

ERROR:

ORA-01756: 引号内的字符串没有正确结束

SQL> SELECT 'Dept.'||deptno || '''s name is '|| dname FROM DEPT;

'DEPT.'||DEPTNO||'''SNAMEIS'||DNAME

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

Dept.10's name is ACCOUNTING

Dept.20's name is RESEARCH

Dept.30's name is SALES

Dept.40's name is OPERATIONS

=====================================

使用别名

在SELECT语句中，可以使用别名来重命名目标表、以及查询结果中的字段（或表达式），以增强可读性。如果别名中使用特殊字符、或者是强制其大小写敏感，则需使用双引号将别名括起来，其它情况禁止使用引号，否则出错；而使用连接表达式时使用的是英文单引号。例如：

SQL> SELECT empno AS 编号, ename 姓名, sal*12 年薪, ename, ename "EnAme",sal*12 "Anual Salary" FROM emp t1;

编号 姓名 年薪 ENAME EnAme Anual Salary

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

7369 SMITH 9600 SMITH SMITH 9600

7499 ALLEN 19200 ALLEN ALLEN 19200

7521 WARD 15000 WARD WARD 15000

7566 JONES 35700 JONES JONES 35700

7654 MARTIN 15000 MARTIN MARTIN 15000

7698 BLAKE 34200 BLAKE BLAKE 34200

7782 CLARK 29400 CLARK CLARK 29400

7788 SCOTT 36000 SCOTT SCOTT 36000

7839 KING 60000 KING KING 60000

7844 TURNER 18000 TURNER TURNER 18000

7876 ADAMS 13200 ADAMS ADAMS 13200

7900 JAMES 11400 JAMES JAMES 11400

7902 FORD 36000 FORD FORD 36000

7934 MILLER 15600 MILLER MILLER 15600

别名"Anual Salary"中包含特殊字符空格，别名"EnAme"则是希望在查询结果中强制规定其大小写拼写格式，因此使用双引号括起来。字段/表达式与其别名之间的关键字"AS"可以省略，表名与其别名之间则不允许使用关键字"AS"。

SQL> SELECT 'sid:' ||sid 学号,'name:' ||name 称谓,age-1 周岁 FROM student;

============================================

表达式中的空值

算术表达式中如果出现空值，则整个表达式结果为空。这是因为空值是未知的、不确定的值，不能简单地当作数值0来处理，因此任何含有空值的算术表达式其运算结果也都是未知的、不确定的。比如，空值乘2的结果仍为空值。在使用"||"运算符的字符串连接表达式中，出现的空值则被当作一个空的（长度为零的）字符串来处理。这是因为Oracle目前处理空字符串的方法与处理空值的方法相同。

学号 称谓 周岁

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

sid:101 name:张三 17

sid:102 name:李四

sid:103 name:王五

sid:104 name: 35

===================================================

去除重复行

在缺省情况下，查询结果中包含所有符合条件的记录行，包括重复行。例如下述语句

SELECT deptno FROM emp;

返回emp表中每一行记录的deptno字段值，其中多次出现重复，如果只是想查看一下当前emp表中所出现/涉及到的部门编号，则可在SELECT语句中使用DISTINCT关键字，以过滤掉查询结果中所有的重复行。例如语句：

SQL> SELECT DEPTNO FROM EMP;

DEPTNO

----------

20

30

30

20

30

30

10

20

10

30

20

30

20

10

已选择14行。

SQL> SELECT DISTINCT DEPTNO FROM EMP;

DEPTNO

----------

30

20

10

DISTINCT的作用范围是后面所有字段的组合，这意味着不会出现重复的字段组合，但组合中的单个字段值则可能出现重复，例如下述SQL语句：

SELECT DISTINCT deptno, job FROM emp;

实现了查询/统计各部门编号及部门内职位设置的功能，

DROP TABLE student;

CREATE TABLE student(

sid VARCHAR2(10),

name VARCHAR2(20),

age NUMBER(3)

);

INSERT INTO student VALUES(101, '张三', 18);

INSERT INTO student VALUES(102, '李四', 25);

INSERT INTO student VALUES(103, '张三', 28);

INSERT INTO student VALUES(103, '张三', 28);

SELECT * FROM student;

SELECT DISTINCT * FROM student;

============================================

按单字段排序

SELECT语句查询结果缺省按照表中记录的物理顺序（即记录的插入顺序）进行排列，也可在SELECT语句中使用ORDER BY 子句对查询结果进行排序，排序方式包括升序（Ascending）和降序（Descending）两种，分别使用关键字ASC和DESC进行标识，缺省为升序。

SQL> SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL;

EMPNO ENAME SAL

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

7369 SMITH 800

7900 JAMES 950

7876 ADAMS 1100

7521 WARD 1250

7654 MARTIN 1250

7934 MILLER 1300

7844 TURNER 1500

7499 ALLEN 1600

7782 CLARK 2450

7698 BLAKE 2850

7566 JONES 2975

7902 FORD 3000

7788 SCOTT 3000

7839 KING 5000

8888

已选择15行。

SQL> SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC;

EMPNO ENAME SAL

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

8888

7839 KING 5000

7902 FORD 3000

7788 SCOTT 3000

7566 JONES 2975

7698 BLAKE 2850

7782 CLARK 2450

7499 ALLEN 1600

7844 TURNER 1500

7934 MILLER 1300

7521 WARD 1250

7654 MARTIN 1250

7876 ADAMS 1100

7900 JAMES 950

7369 SMITH 800

已选择15行。

对于数值型字段的升序排列，是按照字段数值从小到大的顺序排列查询结果中的每一行记录。类似地，文本型字段的升序排列，则是按照文本内容（字符串）的英文字典顺序进行排列；日期/时间型字段的升序排列则是按照日期/时间的从“早”到“晚”的顺序进行排列。降序排列的情形则相反。

按多字段组合排序

如要实现按多字段排序，则可以在在ORDER BY子句中依次给出待排序字段列表，此时应分别设置每个字段的排序方式，例如下述语句：

SQL> SELECT deptno, empno, ename, sal FROM emp ORDER BY deptno, sal;

DEPTNO EMPNO ENAME SAL

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

10 7934 MILLER 1300

10 7782 CLARK 2450

10 7839 KING 5000

20 7369 SMITH 800

20 7876 ADAMS 1100

20 7566 JONES 2975

20 7788 SCOTT 3000

20 7902 FORD 3000

30 7900 JAMES 950

30 7654 MARTIN 1250

30 7521 WARD 1250

30 7844 TURNER 1500

30 7499 ALLEN 1600

30 7698 BLAKE 2850

已选择14行。

SQL> SELECT deptno, empno, ename, sal FROM emp ORDER BY SAL,DEPTNO;

DEPTNO EMPNO ENAME SAL

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

20 7369 SMITH 800

30 7900 JAMES 950

20 7876 ADAMS 1100

30 7521 WARD 1250

30 7654 MARTIN 1250

10 7934 MILLER 1300

30 7844 TURNER 1500

30 7499 ALLEN 1600

10 7782 CLARK 2450

30 7698 BLAKE 2850

20 7566 JONES 2975

20 7788 SCOTT 3000

20 7902 FORD 3000

10 7839 KING 5000

已选择14行。在最前

排序中的空值

Oracle数据库中的查询结果排序，如果排序字段出现空值（NULL），缺省认为NULL是最大值，所以如果是升序（ASC）排序则空值字段所属的记录行排在最后，降序（DESC）排序则排在最前.

SQL> SELECT empno, ename, comm FROM emp WHERE sal<2000 ORDER BY comm;

EMPNO ENAME COMM

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

7844 TURNER 0

7499 ALLEN 300

7521 WARD 500

7654 MARTIN 1400

7369 SMITH

7876 ADAMS

7900 JAMES

7934 MILLER

已选择8行。

SQL> SELECT empno, ename, comm FROM emp WHERE sal<2000 ORDER BY comm DESC;

EMPNO ENAME COMM

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

7369 SMITH

7900 JAMES

7876 ADAMS

7934 MILLER

7654 MARTIN 1400

7521 WARD 500

7499 ALLEN 300

7844 TURNER 0

已选择8行。

Oracle数据库还支持在Order by子句中使用关键字NULLS FIRST或NULLS LAST指定将排序字段为空值的记录行排在结果集的最前或最后（不论采用的是升序还是降序排序方式）

SQL> SELECT empno, ename, comm FROM emp WHERE sal<2000 ORDER BY comm DESC NULLS LAST;

EMPNO ENAME COMM

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

7654 MARTIN 1400

7521 WARD 500

7499 ALLEN 300

7844 TURNER 0

7934 MILLER

7876 ADAMS

7900 JAMES

7369 SMITH

已选择8行。

=======================================

模糊查询

%（百分号）——表示零或多个任意字符的通配符。

_（下划线）——标识单个字符的通配符，可以匹配单个任意字符。

例如，要查询所有姓名以字母为'S'开头的雇员信息，可使用SQL语句：

SQL> SELECT * FROM EMP WHERE ENAME LIKE 'S%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-12月-80 800 20

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

需要注意的是，模式串中除通配符外的字符仍是大小写敏感的，'S%'只能匹配以大写字母'S'开头的字符串，而不能匹配小写字母's'开头的字符串，如'smith'。

SQL> SELECT * FROM EMP WHERE ENAME LIKE '_A%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7900 JAMES CLERK 7698 03-12月-81 950 30

对于模糊查询中的特殊符号可使用ESCAPE标识符来进行转义查找，例如要查找姓名中包含字符'\'的雇员的信息，可以使用如下语句：

SELECT * FROM emp WHERE ename LIKE '%\_%' ESCAPE '\';

其中，ESCAPE '\'指定了字符'\'为转义符，模式串'%\_%'中的'\_'就被转义为、或者说代表的是字符'_'本身，而不再做为通配符使用。

SQL> select empno,ename from emp where ename like '%\_%' ESCAPE '\';

EMPNO ENAME

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

8888 rusky_lu