欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle查询操作(增删改查,伪表,高级查询)实例讲解

程序员文章站 2022-04-08 10:28:00
SQL查询操作:增删改查 一、SQL操作符 算术操作符 + - * / 比较操作符 = != < > <= >= BETWEEN…&he...

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。