Oracle查询操作（增删改查，伪表，高级查询）实例讲解

SQL查询操作：增删改查

一、SQL操作符

算术操作符 + - * /

比较操作符 = != < > <= >= BETWEEN……AND IN LINK IS NULL

逻辑操作符 NOT AND OR

连接操作符 || 用来连接字符串

集合操作符 UNION UNION ALL INTERSECT MINUS

示例1：关于 + 与 || 拼接

SQL Server写法： SELECT sid, sname, sex +’生’ FROM tb_name;

Oracle写法： SELECT sid, sname, sex||’生’ FROM tb_name;

提示：

在Sql Server中“+”既有算术作用，也有字符串拼接作用；但在Oracle中，“+”就只有算术作用。

操作符的优先级：

算术 > 连接 > 比较 > NOT > AND > OR

二、SQL语言的组成

数据定义语言DDL ：CREATE、ALTER、DROP

数据操纵语言DML：INSERT、DELETE、UPDATE、SELECT

事物控制语句TCL：COMMIT、ROLLBACK、SAVEPOINT

数据控制语言DCL：GRANT、REVOKE

二、数据操纵语言DML

1. 增加数据

INSERT INTO 表名[(列名1,列名2,……)] VALUES (值1, 值2,……);

示例1：将部门编号为20的员工插入到新表中

INSERT INTO tb_new

SELECT * FROM emp WHERE deptno=20;

示例2：使用union插入多条数据，要借用伪表

INSERT INTO tb_new

SELECT 1,'张三','男' FROM dual UNION

SELECT 2,'李四','女' FROM dual UNION

SELECT 3,'王五','男' FROM dual UNION

SELECT 4,'赵六','女' FROM dual;

2. 删除数据

DELETE FROM 表名 [WHERE <条件>]

示例：删除性别为男的记录

DELETE FROM emp WHERE sex = ‘男’

3. 修改数据

UPDATE 表名 SET 列名=表达式, …… [WHERE <条件>]

示例1：修改emp表中编号7369的薪水sal为1000.

UPDATE emp SET sal=1000 WHERE 编号=’7369’

示例2：修改emp表中编号7369的薪水sal为800，姓名后添加个S。

UPDATE emp SET sal=800, name=name ||’s’ WHERE 编号=’7369’

习题：

--修改emp表中部门编号为30的工资+100。

--将emp表中所有人的工资提升10%。

4. 查询/检索数据

SELECT * FROM 表名

三、查询/检索

1. 简单查询

SELECT * FROM tb_name;

SELECT sid, sname FROM tb_name;

SELECT DISTINCT sname FROM tb_name;

SELECT age*2 FROM tb_name;

SELECT sname AS 姓名 FROM tb_name;

2. 查询中的伪表dual

dual伪表，它确实是一张表，表中只有一个字段

SELECT * FROM dual;

为什么要伪表？

因为Oracle数据库中SQL标准规定，SELECT语句中必须有FROM，就是必须有表名。

SELECT 9*999 FROM dual;

SELECT ‘好好学习’,’天天向上’ FROM dual;

提示：用dual来做一个伪表，也就是数据不在任何一张表的时候使用它。

3. 伪列ROWID、ROWNUM

ROWID 是表中行的存储地址，唯一标识符。

通过ROWID可快速查找到某行。

ROWNUM 类似SQL Server中的ROW_NUMBER()函数，为查询出的每条记录进行连续不间断编号。

在Oracle中一般用于分页。

示例1：查看伪列

SELECT *,ROWID,ROWNUM FROM tb_name;

示例2：求前两条记录（Oracle不支持TOP）

SELECT *,ROWNUM FROM emp WHERE ROWNUM<3;

示例3：求3~5条记录（ROWNUM只能作用于小于号）

SELECT * FROM (

SELECT *,ROWNUM rid FROM emp

) tb WHERE rid>2 AND rid<5;

示例4：求薪水前5的人

SELECT t.*, ROWNUM FROM (

SELECT * FROM emp ORDER BY sal DESC

) t WHERE ROWNUM < 6;

4. 查询子句

4.1 WHERE子句

WHERE是条件查询，WHERE后一般都接查询的条件。

SELECT * FROM tb_name WHERE sex=’男’;

SELECT * FROM tb_name WHERE age<18;

SELECT * FROM tb_name WHERE name LIKE ‘张%’;

4.2 GROUP BY子句

GROUP BY是分组查询，即按某个列分组，一般用于分组统计，常与聚合函数配合使用。

SELECT COUNT(*) FROM tb_name GROUP BY deptno;

4.3 HAVING子句

HAVING是对分组查询后的结查进行过滤。

SELECT COUNT(*) FROM tb_name GROUP BY deptno HAVING COUNT(*)>2;

4.4 ORDER BY子句

ORDER BY是按某列进行升序或降序排列。

SELECT * FROM tb_name ORDER BY birthday DESC;

5. 查询常见函数的使用

5.1 聚合函数

COUNT(*或列名)、SUM(列名)、AVG(列名)、MAX(列名)、MIN(列名)

5.2 NVL(exp1, exp2)

当exp1为NULL，则用exp2替代。与SQL Server中的isNull()相同。

示例：求emp表中每人的收入（薪水+奖金）

SELECT sal+NVL(comm, 0) FROM emp;

注意：

SELECT t.*,ROWNUM FROM emp t ORDER BY sal+NVL(comm,0);

先通过ROWNUM分号，再进行排序，结果会导致ROWNUM混乱。

5.3 NVL(exp1, exp2, exp3)

当exp1为NULL，返回exp2，否则返回exp3

示例：

SELECT sal+NVL(comm, 0, comm+100) FROM emp;

--如果comm为NULL，则用0替代comm的值；否则comm+100。

5.4 IS NULL

Oracle规定NULL不能用=号来判断逻辑关系，须使用IS NULL。

示例：查询没有奖金的人

SELECT * FROM emp WHERE comm IS NULL;

四、高级查询

1. 多表连接查询

笛卡尔积交叉查询：

SELECT e.ename, d.dname FROM emp e, dept d

内联查询：返回多个表*同的数据

SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno;

SELECT * FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;

左、右外联查询：

左外联查询即以左表为准；右外联查询即以右表为准。

怎么判断左表还是右表？前面的表为左表，后面的表为右表。

SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

（+）左外联写法：

SELECT * FROM emp e, dept d WHERE e.deptno=d.deptno(+);

（+）右外联写法：

SELECT * FROM emp e, dept d WHERE e.deptno(+)=d.deptno;

提示： (+)=：放在等号的左边，表示右连接；

=(+)：放在等号的右边，表示左连接；

全联查询：

全联不支持（+）这种写法

SELECT * FROM emp e FULL JOIN ,dept d ON e.deptno=d.deptno;

2. 子查询（嵌套查询）

2.1 简单子查询

简单子查询是指子查询返回一个值时做为外部查询的一个条件。（使用>、=、<等）

SELECT deptno FROM emp WHERE ename=’KING’

示例1：求收入第3~6名的员工

SELECT * FROM (

SELECT t.*, ROWNUM mya FROM (

SELECT * FROM emp ORDER BY sal+NVL(comm,0) DESC

) t

) a WHERE mya BETWEEN 3 AND 6;

--ROW_NUMBER()写法：

SELECT * FROM (

SELECT t.*,ROW_NUMBER() OVER(ORDER BY sal+NVL(comm,0) DESC) myr FROM emp t

) t WHERE myr BETWEEN 3 AND 6;

2.2 IN或NOT IN子查询

IN或NOT IN子查询是子查询返回多个值时使用。

SELECT * FROM tb_name WHERE sid IN (

SELECT sid FROM tb_name WHERE sex = ‘男’

);

2.3 EXISTS或NOT EXISTS子查询

EXISTS 操作对在子查询的结果集中存在的行进行检验。

示例1：查找是否存在emp表

IF EXISTS( SELECT * FROM sysdatabases WHERE name=’emp’ )

示例2：查找至少有一个雇员的经理

SELECT * FROM emp

WHERE EXISTS (

SELECT 'X' FROM employees WHERE r_id =e_id

);

如果返回X,则TRUE；否则FALSE.最后看是不是TRUE，即返回X。