SQL语言的操作：高级查询

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。