OracleSql语句学习(三)

别名：

　①、在SELECT子句中出现的函数或表达式会在结果集中作为字段名，这样的可读性差，因此可以为这样的字段添加别名。

　　②、别名中不能出现空隔，除非是用双引号括起来的）别名中如果希望包含空隔或者区分大小写，那么该别名需要使用双引号括起来

　　③、表和字段都可以起别名的哟(建议别名都用使用AS关键字(可省略)引号括起来，并都采用引号括起来，养成良好的习惯)。
　　例如：SELECT ename,sal*12 "sal" FROM emp_weiyiji

查询条件

使用>,<,>=,<=,!=,<>,=(其中!=和<>是一样的，推荐使用<>，因为<>更通用)
　　SELECT ename,sal FROM emp_weiyiji WHERE sal<2000;
　　SELECT ename,sal,job FROM emp_weiyiji WHERE deptno <> 10;
　　SELECT ename,sal,hiredate FROM emp_weiyiji WHERE hiredate > TO_DATE('1982-1-1','YYYY-MM-DD');

使用AND,OR关键字
　　SELECT ename,sal,job FROM emp_weiyiji WHERE sal>1000 AND job = 'CLERK';
　　SELECT ename,sal,job FROM emp_weiyiji WHERE sal>1000 OR job = 'CLERK';

与，或用来连接多个条件：
　　AND优先级高于OR，所以可以通过加括号来提高OR的优先级
　　SELECT ename,sal,job FROM emp_weiyiji WHERE sal>1000 AND (job = 'SALESMAN'OR job = 'CLERK');

LIKE用于模糊匹配字符串，有两个通配符: 
　　_:表示单一的一个字符 %

　　:表示任意个字符(0-多个)
查看名字第二个字母是A第四个字母是T的？
　　SELECT ename,sal,job FROM emp_weiyiji WHERE ename LIKE '_A_T%';
查看名字最后一个字母是T的？
　　SELECT ename,sal,job FROM emp_weiyiji WHERE ename LIKE '%T';

IN(list),NOT IN(list)判断在列表中或不在列表中他们也常用在子查询中
　　SELECT ename ,job FROM emp_weiyiji WHERE job IN ('MANAGER', 'CLERK');
　　SELECT ename, job FROM emp_weiyiji WHERE deptno NOT IN (10,20);

BETWEEN...AND,,,判断在一个区间范围内
查看工资在1500到3000之间的员工？
　　SELECT ename,sal,deptno FROM emp_weiyiji WHERE sal BETWEEN 1500 AND 3000;

ANY,ALL通常是联合>,>=,<,<=的判断，并且判断的是多个值
　　①>ANY(list)：大于列表之一(大于最小即可)
　　②>ALL(list)：大于列表所有(大于最大的)
　　③<ANY(list)：小于列表之一(小于最大即可)
　　④<ALL(list):小于列表所有(小于最小的)
他们用在子查询的判断中：SELECT ename, sal,deptno FROM emp_weiyiji WHERE sal<ALL(3500,4000,4500);

查询条件中使用表达式和函数
　　查询条件中使用函数：
　　　　SELECT ename,sal,job FROM emp_weiyiji WHERE ename = UPPER('scott');
　　查询条件中使用表达式：
　　　　SELECT ename,sal,job FROM emp_weiyiji WHERE sal*12>50000;

 

 

 DISTINCT关键字

在SELECT子句中使用，用来对指定的字段值去除重复复行。
查看公司有哪些职位？
　　SELECT DISTINCT job FROM emp_weiyiji

多字段去重时，不保证单一的某个字段的值没有重复，而去重原则是这些字段值的组合没有重复行。
　　SELECT DISTINCT job,deptno FROM emp_weiyiji

 

 

ORDER BY 子句（排序）

　　①、ORDER BY子句用来排序结果集，该子句只能写在SELECT语句的最后一个子句上

　　②、ORDER BY 可以根据给定字段进行升序或者降序排列结果集。

　　③、其中ASC为升序，通常不写，因为默认为升序，DESC为降序。
　　　　查看公司员工工资的排名？
　　　　　　SELECT ename,sal,deptno FROM emp_weiyiji ORDER BY sal DESC;降序

多字段排序：
排序是有优先级的，首先按照第一个字段的排序方式对结果排序，当第一个字段的值相同时才按照第二个字段的排序方式排序这些记录。
　　SELECT ename,deptno,sal FROM emp_weiyiji ORDER BY deptno DESC,sal DESC;
查看年薪高于2W并且在10或者20号部门，名字中含有E的员工，按照工资从高到低排列显示。
　　SELECT ename,sal ,deptno FROM emp_weiyiji WHERE sal*12>20000 AND deptno IN(10,20) AND ename LIKE '%E%' ORDER BY sal DESC;

 

 

聚合函数

聚合函数是用来统计数据的 其中包括:MAX,MIN,SUM,AVG,COUNT
　　①MAX,MIN求最大值与最小值

　　②SUM,AVG求总和与平均值 COUNT 统计记录数
　　SELECT MAX(sal),MIN(sal),SUM(sal),AVG(sal) FROM emp_weiyiji;
　　SELECT COUNT(ename) FROM emp_weiyiji;统计记录数

聚合函数都是忽略NULL的
　　SELECT SUM(comm),AVG(comm),COUNT(comm) FROM emp_weiyiji;忽略NULL
　　SELECT SUM(comm),AVG(NVL(comm,0)) FROM emp_weiyiji;先将NULL改成0，然后求平均值

排序的字段若是有NULL值，则NULL被认作为最大值
SELECT comm FROM emp_weiyiji　ORDER BY comm DESC;

当SELECT子句中出现了聚合函数，那么不是聚合函数的其他单独字段都必须出现在GROUP BY子句中，反过来则无要求。
SELECT ename,MAX(sal) FROM emp_weiyiji;此句会报错

 

 

GROUP BY子句(分组)

GROUP BY是配合聚合函数使用的。
GROUP BY允许将结果集安照给定段值一样的记录进行分组，然后配合聚合函数对这些分组的记录分别统计结果。
　　查看每个部门的平均工资？
　　　　SELECT AVG(sal),deptno FROM emp_weiyiji GROUP BY deptno;
　　查看每个职位的最高工资和最低工资
　　　　SELECT MAX(sal),MIN(sal),job FROM emp_weiyiji  GROUP BY job
　　查看平均工资高于2000的的部门具体有哪些？
　　　　SELECT AVG(sal),deptno FROM emp_weiyiji GROUP BY deptno HAVING AVG(sal)>2000
WHERE中不能使用聚合函数当做过滤条件原因在于过滤的时机不对。WHERE是在检索表中数据的时候进行过滤的，所以WHERE是用来确定结果集记录数的。而聚合函数是建立在结果集生成后的数据进行统计的，所以使用聚合函数过滤是在WHERE之后进行的。

HAVING子句

HAVING子句必须出现在GROUP BY子句之后，作用是添加过滤条件来去除不符合的分组。HAVING中可以使用聚合函数作为过滤条件。
　　查看平均工资高于2000的这些部门的最高工资与最低工资是多少？
　　　　SELECT MAX(sal),MIN(sal),deptno FROM emp_weiyiji GROUP BY deptno HAVING AVG(sal)>2000
　　查看最低工资高于800的那些部门各有多少人？
　　　　SELECT COUNT(*),deptno FROM emp_weiyiji GROUP BY deptno HAVING MIN(sal)>800

 

 

关联查询

①、关联查询指的是数据从多张表中联合查询，结果集中的字段来自不同表。关联查询中由于指定表与表的数据的联系的条件称为关联条件。通常关联查询中都要写关联条件，因为不写会产生笛卡尔积，通常情况下都是无意义的结果集，开销巨大。
　　　　SELECT e.ename,e.deptno,d.dname FROM emp_weiyiji e,dept_weiyiji d WHERE e.deptno=d.deptno
②、不满足连接条件的记录是查询不出来的。
　　　　SELECT e.ename,d.dname FROM emp_weiyiji e,dept_weiyiji d
③、N张表关联查询要写至少N-1个连接条件过滤条件必须与连接条件同时成立。
　　查看在NEW YORK工作的员工都有谁？
　　　　SELECT e.ename,e.deptno,d.dname,d.loc FROM emp_weiyiji e, dept_weiyiji d WHERE e.deptno=d.deptno AND d.loc='NEW YORK'
　　查看工资高于2000的员工都有谁？
　　查看该员工的名字，工资，所在部门名字，以及工作所在地？
　　　　SELECT e.ename,e.sal,d.dname,d.loc FROM emp_weiyiji e,dept_weiyiji d WHERE e.deptno=d.deptno AND e.sal>2000

内连接：JOIN
内连接也是关联查询的一种。
　　查看每个员工的名字以及其所在部门名？
　　　　SELECT e.ename,d.dname FROM emp_weiyiji e JOIN dept_weiyiji d ON e.deptno=d.deptno WHERE d.dname='SALES'

外连接
外连接在关联查询中除了可以将满足条件的记录查询出来之外，还可以将不满足连接条件的记录查询出来。
外连接分为：
　　①左外连接：以JOIN左侧表作为驱动表（所有记录都显示）那么当该表记录不满足连接条件时，那么来自右侧表的字段值为NULL。
　　②右外连接，以JOIN右侧表作为驱动表（所有记录都显示）那么当该表记录不满足连接条件时，那么来自左侧表的字段值为NULL。

　　③全外连接。
左外连接：
　　SELECT e.ename,d.dname FROM emp_weiyiji e LEFT OUTER JOIN dept_weiyiji d ON e.deptno=d.deptno
右外连接：
　　SELECT e.ename,d.dname FROM emp_weiyiji e RIGHT OUTER JOIN dept_weiyiji d ON e.deptno=d.deptno
全外连接：
　　SELECT e.ename,d.dname FROM emp_weiyiji e FULL OUTER JOIN dept_weiyiji d ON e.deptno=d.deptno
左右外连接的简便写法：
　　SELECT e.ename,d.dname FROM emp_weiyiji e,dept_weiyiji d  WHERE e.deptno(+)=d.deptno（+）在那边就是哪边外连接，全外连接没有简写方法，只能写上面的

自连接：
自连接即：当前表的记录可以对应当前表自己的多条记录。这种设计是为了解决记录属性一样但是又是存 在父子关系的树状结构数据时使用。
常见的：商品类别
　　SELECT e.ename,m.ename FROM emp_weiyiji e,emp_weiyiji m  WHERE e.mgr=m.empno

　　SELECT e.ename,m.ename FROM emp_weiyiji e JOIN emp_weiyiji m  ON e.mgr = m.empno

　　SELECT e.ename,m.ename,d.loc FROM emp_weiyiji e JOIN emp_weiyiji m ON e.mgr=m.empno JOIN dept_weiyiji d ON e.deptno=d.deptno WHERE e.ename='SMITH'

　　SELECT e.ename,m.ename,d.loc FROM emp_weiyiji e,emp_weiyiji m,dept_weiyiji d WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.ename='SMITH'