常用的数据库知识（三）

--字段的别名
--当一个SELECT子句中含有函数或者表达式查询出来的结果集中对应该字段的名字就是这个函数表达式
--不易理解，可读性差，为此我们可以为这样的字段单独指定别名使用语法是列的别名跟在列名后，中间
--可以加或不加一个“AS”关键字,别名可以直接写，不必用双引号引起来。但是如果希望别名中区分大
--小写字符，或者别名中包含字符或空格，则必须用双引号引起来。
SELECT sal,sal*12 "yearsal" FROM emp;
--WHERE语句
--使用AND.OR关键字来连接多个条件,AND优先级是比OR高的，所以若希望提高OR优先级可以使用括号。
SELECT ename,sal,job FROM emp
WHERE sal>1000 AND job='CLERK';
SELECT ename,sal,job FROM emp
WHERE sal>1000 OR job='CLERK';
SELECT ename,sal,job FROM emp
WHERE sal>1000 AND job='SALESMAN' OR job='CLERK';
SELECT ename,sal,job FROM emp
WHERE sal>1000 AND job='CLERK' OR job='SALESMAN'


--LIKE用于模糊匹配字符串
--支持两个通配符：
--_:任意一个字符
--%:任意个字符（0-多个）


--查看名字第二个字母是A的员工？
SELECT ename FROM emp WHERE ename LIKE '_A%'
SELECT ename FROM emp WHERE ename LIKE '_A%D'


--IN(list),NOT IN(list)
--判断在列表中或不在列表中
--IN常用在子查询中
SELECT ename,job FROM emp WHERE job IN('MANAGER','CLERK')


SELECT ename,job,sal FROM emp WHERE deptno <> 10 AND sal>1000 AND job IN('CLERK','SALESMAN') AND ename LIKE '%A%' 


--BETWEEN...AND...
--判断在一个范围内,小的在左大的在右
--查看工资在1500到3000之间的员工信息
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000 


--ANY(list)和ALL(list)
--配合>,>=,<,<=一个列表使用
-->ANY(list):大于list中的最小值即可
-->ALL(list):大于list中的最大值即可
--<ANY(list):小于list中的最大值即可
--<ALL(list):小于list中的最小值即可
--ANY,ALL常用在子查询中
SELECT ename,sal,empno FROM emp WHERE sal>ALL(1000,2000,3000)


--查询条件中使用表达式和函数
--查询条件中使用函数
SELECT ename,sal,job FROM emp WHERE ename = UPPER('scoTt');
--查询条件中使用表达式
SELECT ename,job,sal FROM emp WHERE sal*12>100000


--DISTINCT关键字
--用于将结果中指定字段值重复的记录去除,多字段去重时，是这些字段值组合一样的记录不会重复出现
SELECT DISTINCT job FROM emp




--排序结果集
--ORDER BY 子句可以对结果集进行排序或者降序排序
--升序：ASC 不写默认也是升序
--降序：DESC 
--多字段排序时，按照 ORDER BY 后面第一个字段先排序，当第一个字段有重复值时，才按照
--第二个字段的值排序这些记录，以此类推多字段排序时有优先级的
--ORDER BY 只能写在DOL的最后一个子句上
SELECT ename,sal,deptno FROM emp ORDER BY sal
SELECT ename,sal,deptno FROM emp ORDER BY sal DESC


--查看公司的所有职位，并按照职位名称排序
SELECT ename,job FROM emp ORDER BY job


--聚合函数（分组函数、多行函数、集合函数）
--用来统计的。可以将结果按照指定字段值进行统计然后得到一个结果
--其中有四个针对值本身统计的函数
MAX,MIN,AVG,SUM 求最大值，求最小值，求平均值，和总和
SELECT MAX(sal),MIN(sal),AVG(sal),SUM(sal) FROM emp
--还有一个对结果集记录数的统计函数 COUNT
SELECT COUNT(ename) FROM emp
--聚合函数是忽略NULL值的
SELECT AVG(NVL(comm,0)) FROM emp
SELECT COUNT(1) FROM emp
SELECT COUNT(*) FROM emp
--查看20号部门的平均工资？
SELECT AVG(sal) FROM emp WHERE deptno=20
--查看每个部门平均工资？
SELECT AVG(sal),deptno FROM emp GROUP BY deptno


--分组
--GROUP BY 子句
--GROUP BY 子句可以将结果集按照指定的字段值一样的记录看作同一组，配合聚合函数使用
--可以对每组的数据进行统计并得出结果


--当 SELECT 子句中使用了聚合函数时，那么凡不在聚合函数中的单独字段都必须出现在 GROUP
--BY 子句中
SELECT MAX(sal),MIN(sal),job FROM emp GROUP BY job


--多字段分组 
--查看每个部门每种职位各多少人？
SELECT COUNT(*),job,deptno FROM emp GROUP BY deptno,job


--查看每个部门的平均工资，但前提是该部门的平均工资高于2000
SELECT AVG(sal),deptno FROM emp WHERE AVG(sal)>2000 GROUP BY deptno
--上面的SQL语句会报错，WHERE 中不能使用聚合函数作为过滤条件，原因在于过滤的时机不对


--WHERE 是在第一次检索表时，行过滤数据以产生查询结果集时使用的
--而聚合和函数时用来统计，那么前提是有数据所以要将所有数据查询出来才能统计，之后才可以
--进行过滤，这实际是在 WHERE 之后进行的。
--若想使用聚合函数的结果进行过滤，那么需要使用HAVING子句。
--HAVING必须跟在GRUP BY子句之后，作用是添加过滤条件不满足的分组去除，而HAVING
--中允许使用聚合函数作为过滤条件。
SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000


--关联查询
--联合多张表查询数据即关联查询，查询的结果集中的字段来自多张表，关联查询的重点是连接条件
--。数据库是根据连接条件对表中的数据做关联然后查询内容的。


--查看每个员工的名字以及其所在部门的名字？
SELECT ename,dname FROM emp,dept WHERE emp.deptno=dept.deptno
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno
--关联查询中过滤条件要与连接条件同时满足
SELECT e.ename,e.deptno,d.dname,d.deptno
FROM emp e,dept d
WHERE e.deptno = d.deptno AND d.loc = 'NEW YORK'


--关联查询通常加连接条件，不写连接条件会出现‘笛卡尔积’，‘笛卡尔积’通常是一个
--无意义的结果集。笛卡尔积是将关联查旬表中的数据意义连接一遍而产生的结果集，数据量为
--关联查询表数据量的乘积


--内连接
SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno
JOIN XXX ON XXX


--关联查询和内连接忽略不满足条件的记录。


--外链接
--外链接在关联查询中除了可以将满足条件的记录查询出来之外，还可以将不满足条件的
--记录也查询出来。
--外链接分为：
--左外连接：以JOIN左侧的表作为驱动表（所有数据都显示出来），那么当该表某条记录
--不满足连接条件时来自右侧表中的字段值全部为空。
--右外链接，全外连接。
SELECT e.ename,d.dname FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno(+)=d.deptno


自连接
当前表的记录和当前表的多条记录有对应关系
自连接解决数据属性相同，但是又存在上下级关系的树状结构数据使用


查看每个员工的上司的名字？
SELECT e.ename,e.mgr,d.ename,d.empno
FROM emp e JOIN emp d ON e.mgr=d.empno