SQL

1.

where和having区别：

where是过滤分组前的数据，having是过滤分组后的数据

表现形式：where必须在group by之前，having必须在group by之后,不能单独存在

 

--eg:查询每个部门工资高于800的平均工资并且平均工资大于2000,

select deptno,avg(sal)

from scott.emp e

where e.sal > 800

group by deptno

having avg(sal)>2000;

 

 

2.

别名,要么叫双引号，要么不加，AS 可以省略

 

3.

--笛卡尔积

SELECT COUNT(*) FROM employee; --14条记录

SELECT COUNT(*) FROM department; --4条记录

SELECT employee.ename,department.dname FROM employee,department;--56条记录

 

4.表关联

 

--表关联

--等值连接

SELECT e.ename,e.job,d.dname,d.loc FROM employee e,department d

WHERE e.deptno=d.deptno

--内连接  等价 等值连接

SELECT e.ename,e.job,d.dname,d.loc FROM employee e [INNER]JOIN department d

ON (e.deptno=d.deptno)

--外连接

--Emp表做驱动表,outer可写可不写:

--左外连接

SELECT e.ename,e.job,d.dname,d.loc FROM employee e LEFT OUTER JOIN department d

ON (e.deptno=d.deptno)--推荐使用，兼容mysql

SELECT e.ename,e.job,d.dname,d.loc FROM employee e,department d

WHERE e.deptno=d.deptno(+)

--右外连接，department作为驱动表

SELECT e.ename,e.job,d.dname,d.loc FROM employee e RIGHT OUTER JOIN department d

ON (e.deptno=d.deptno)--推荐使用，兼容mysql

SELECT e.ename,e.job,d.dname,d.loc FROM employee e,department d

WHERE e.deptno=d.deptno(+)

--全外连接

SELECT e.ename,e.job,d.dname,d.loc FROM employee e FULL OUTER JOIN department d

ON (e.deptno=d.deptno)

--自连接,其实就是站在不同的角度把一张表看成多张表

--查询出员工姓名，员工领导姓名

select e1.ename,e2.ename

from scott.emp e1,scott.emp e2

where e1.mgr = e2.empno;

--查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称

select e1.ename,e2.ename,d1.dname,d2.dname

from scott.emp e1,scott.emp e2,scott.dept d1,scott.dept d2

where e1.mgr = e2.empno

and e1.deptno = d1.deptno

and e2.deptno = d2.deptno;

 

5.聚合函数

 

SELECT DEPTNO,

 MAX(SAL) 最大值,

 MIN(SAL) 最小值,

 AVG(SAL) 平均值,

 SUM(sal) 工资合计,

 count(sal) 总行数,

 count(comm) 获取绩效的人数,

 AVG(COMM) 错误的人均绩效,

 AVG(coalesce(comm, 0)) 正确的人均绩效

FROM emp

group by deptno;

 

由于聚合函数在处理时，会忽略null值，

所以在使用count，avg，min 函数的时候要注意null的处理。

而sum，max不受影响。

 

5.分组查询

---分组查询

---查询出每个部门的平均工资

---分组查询中，出现在group by后面的原始列，才能出现在select后面

---没有出现在group by后面的列，想在select后面，必须加上聚合函数。

---聚合函数有一个特性，可以把多行记录变成一个值。

select e.deptno, avg(e.sal)--, e.ename

from emp e

group by e.deptno;

---查询出平均工资高于2000的部门信息

select e.deptno, avg(e.sal) asal

from emp e

group by e.deptno

having avg(e.sal)>2000;

---所有条件都不能使用别名来判断。

--比如下面的条件语句也不能使用别名当条件

select ename, sal s from emp where sal>1500;

 

---查询出每个部门工资高于800的员工的平均工资

select e.deptno, avg(e.sal) asal

from emp e

where e.sal>800

group by e.deptno;

----where是过滤分组前的数据，having是过滤分组后的数据。

---表现形式：where必须在group by之前，having是在group by之后。

---查询出每个部门工资高于800的员工的平均工资

---然后再查询出平均工资高于2000的部门

select e.deptno, avg(e.sal) asal

from emp e

where e.sal>800

group by e.deptno

having avg(e.sal)>2000;

 

 

5.子查询

 

---子查询

---子查询返回一个值

---查询出工资和SCOTT一样的员工信息

select * from emp where sal in

(select sal from emp where ename = 'SCOTT')

---子查询返回一个集合

---查询出工资和10号部门任意员工一样的员工信息

select * from emp where sal in

(select sal from emp where deptno = 10);

---子查询返回一张表

---查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称

---1，先查询出每个部门最低工资

select deptno, min(sal) msal

from emp

group by deptno;

---2，三表联查，得到最终结果。

select t.deptno, t.msal, e.ename, d.dname

from (select deptno, min(sal) msal

      from emp

      group by deptno) t, emp e, dept d

where t.deptno = e.deptno

and t.msal = e.sal

and e.deptno = d.deptno;

 

6.oracle的SQL执行顺序

 

SQL Select语句完整的执行顺序： 

 

 1、from子句组装来自不同数据源的数据；

 

 2、where子句基于指定的条件对记录行进行筛选；

 

 3、group by子句将数据划分为多个分组；

 

 4、使用聚集函数进行计算；

 

 5、使用having子句筛选分组；

 

 6、计算所有的表达式；

 

 7、select 的字段；

 

 8、使用order by对结果集进行排序。

 

 

7.分页查询

rownum行号。当我们做select操作的时候

每查询出一行记录，就会在该行加一个行号,

行号从1开始，依次递增，不能跳着走。

rownum行号不能写上大于一个正数。

 

Notice:

排序操作会影响rownum的顺序,所以要格外注意（order by 执行顺序在select之后）

 

--有order by分页，第一层先排序，第二层得到行号，第三层范围查询

select * from

(select rownum rn,t1.* from

(select e.* from scott.emp e order by sal desc) t1

where rownum <= 10) t2

where rn>=5 ;

 

 

select * from

(select rownum rn,t1.* from

(select e.* from scott.emp e order by sal desc) t1) t2

where rn>=5

and rn<=10;

 

 

--无order by 会少一层：

 

select * from

(select rownum rn,e.*from scott.emp e ) t1

where rn>=5

and rn<=10;

 

 

8.dual：

虚表，只是为了补全语法，没有任何意义。

 

9.rowid

 

用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。ROWID它是一个伪列，它并不实际存在于表中。它是ORACLE在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的ROWID能找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是通过ROWID来完成的，而且使用ROWID来进行单记录定位速度是最快的。

 

正如您所说,它会在磁盘上物理移动的任何时候发生,例如:

• 导出/导入表

• ALTER TABLE XXXX MOVE

• ALTER TABLE XXXX收缩空间

• FLASHBACK TABLE XXXX

• 拆分分区

• 更新值以使其移动到新分区

• 结合两个分区

如果在索引组织表中,则对主键的更新也会为您提供不同的ROWID.

 

 

10.truncate

 

先删除表，再次创建表。效果等同于删除表中全部记录。

在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。

索引可以提供查询效率，但是会影响增删改效率。

truncate table xxx;