数据库笔记-SQLday03

--DQL语句：在查询语句中SELECT后面可以使用表达式或者函数，但是这样一来结果集中该字段的名字就是直接使用表达式或者函数，不易读取，可以为该字段添加别名。
SELECT LOWER(ename) AS 姓名, sal*12 AS 薪资 FROM emp_wensq--AS后跟别名，汉字双引号可加可不加
SELECT lower(ename) ename, sal*12 from emp_wensq
--查询职员表中薪水低于2000元的职员信息
SELECT ename,sal FROM emp_wensq
WHERE sal<2000;
--查询职员表中不属于部门10的职员信息（!=等价于<> 建议使用所有数据库都通用的<>）
SELECT ename,sal,job FROM emp_wensq
WHERE daptno<>10;
--查询职员表中在1982年1月1日以后入职的职员信息，比较日期类型数据
SELECT ename,sal,hiredate FROM emp_wensq
WHERE hiredate>TO_DATE('1982-1-1','YYYY-MM-DD');
--年薪大于2000
SELECT ename,sal FROM emp_wensq
WHERE sal*12>20000;


--使用AND与OR连接多个条件判断
SELECT ename,sal,JOB FROM emp_wensq
WHERE sal>1000 AND job = 'CLERK';


SELECT ename,sal,job FROM emp_wensq
WHERE sal>1000 OR job = 'CLERK';
--查看工资高于1000的职位是SALESMAN或CLERK的员工？
SELECT ename,job,sal FROM emp_wensq
WHERE sal>1000 and (job='SALESMAN' OR job = 'CLERK');
--AND的优先级高于OR的。
SELECT * FROM emp_wensq
--LIKE条件（模糊查询）%：表示0到多个字符_：标识单个字符
--查看名字第二个字母是"A"的员工?
SELECT ename,JOB FROM emp_wensq
WHERE ename LIKE '_A%';
--查看第二个字母是C第四了字母是T的员工?
SELECT ename,JOB FROM emp_wensq
WHERE ename LIKE '_C_T%'


--IN和NOT IN
--判断字段的值是否在列表中或不在列表中
--查询职位是MANAGER或者CLERK的员工
SELECT ename,JOB FROM emp_wensq
WHERE JOB IN('MANAGER','CLERK');
--查询不是部门10或20的员工
SELECT ename,JOB FROM emp_wensq
WHERE deptno NOT IN(10,20);
--IN和NOT IN常用在判断子查询的结果中使用


--BETWEEN…AND…操作符用来查询符合某个值域范围条件的数据,对字符类型和日期类型数据也同样适用
--查询薪水在1500-3000之间的职员信息
SELECT ename,sal FROM emp_wensq
WHERE sal BETWEEN 1500 AND 3000;


--使用IS NULL和IS NOT NULL
--空值NULL是一个特殊的值，比较的时候不能使用”=”号，必须使用IS NULL，否则不能得到正确的结果。例如查询哪些职员的奖金数据为NULL：
SELECT ename, sal, comm FROM emp_wensq
WHERE comm IS NULL;


--在比较运算符中，可以出现ALL和ANY，表示“全部”和“任一”，但是ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。其中：
--  > ANY(list) : 大于最小
--  < ANY(list)：小于最大
--  > ALL(list)：大于最大
--  < ALL(list)：小于最小
--通常列表中的内容不是固定值，而是一个查询语句的结果集，所以常用于 子查询
SELECT empno,ename,JOB,sal,deptno FROM emp_wensq
WHERE sal>ANY(3500,4000,4500);


--DISTINCT 去除重复行
--查看公司共有多少种职位?
SELECT DISTINCT JOB
FROM emp_wensq


--DISTINCT关键字必须跟在SELECT关键子之后，对多列去重，不能保证每列都没有重复值，只会对这些列的组合去重
SELECT DISTINCT job,deptno
FROM emp_wensq


--排序
--ORDER BY子句
--ORDER BY子句只能定义在SELECT语句的最后，可以对查询出来的结果集按照指定字段值的升序或者降序将结果集排序，排序有两种
--升序:ASC 可以不写，默认就是升序
--降序：DESC
SELECT ename,sal
FROM emp_wensq
ORDER BY sal DESC


--NULL被认为是最大值
--按照奖金排序


SELECT ename,comm
FROM emp_wensq
ORDER BY comm DESC


--ORDER BY可以对多字段排序，但是排序是先按照第一个字段的排序后，若第一个字段有重复值时才按照第二个字段排序，以此类推。
SELECT ENAME,DEPTNO,SAL
FROM emp_wensq
ORDER BY deptno DESC,sal DESC


--聚合函数：聚合函数会将多条记录对应的字段值进行统计，然后得出一个结果。
--查看公司最高工资与最低工资?
SELECT MAX(sal),MIN(sal)
FROM EMP_WENSQ


--查看平均值与总和
SELECT AVG(sal),SUM(sal)
FROM EMP_WENSQ


--查看公司总共多少员工?
SELECT COUNT(ENAME)
FROM EMP_WENSQ


--聚合函数忽略NULL值记录
--查看公司平均奖金?
SELECT  AVG(COMM),SUM(COMM)FROM emp_wensq--运行结果550 2200，实质上平均值只计算了4个，忽略了NULL


SELECT AVG(NVL(comm,0))FROM emp_wensq--运算结果157.142857142857142857142857142857142857


--查看一张表中有多少记录?
SELECT COUNT(*)FROM emp_wensq--运行结果14


--分组
--GROUP BY子句
--该子句为了配合聚合函数使用，可以将表中记录按照指定字段的值相同的划分为一组，然后按组统计结果。


--查看每个部门的平均工资？
SELECT AVG(sal),deptno FROM emp_wensq
GROUP BY deptno


--当SELECT语句中含有聚合函数时，那么凡不在聚合函数中其他字段，必须出现在GROUP BY句子中，反过来不是必须的。
--查看每个职位的最高工资?
SELECT MAX(sal)FROM emp_wensq
GROUP BY job
--查看每个职位工多少人?
SELECT COUNT(*),job FROM EMP_WENSQ
GROUP BY job


--GROUP BY也该按照多个字段分组，分组原则是这些字段值组合相同的记录看做一组
--查看每个部门每种职位各多少人?
SELECT COUNT(*),deptno,job FROM emp_wensq
GROUP BY deptno,job


--查看平均工资高于2000的部门的具体平均工资?
SELECT AVG(sal),deptno FROM emp_wensq
WHERE AVG(sal)>2000                --报错
GROUP BY deptno
--WHERE中不能使用分组函数，原因在于过滤时机不对。分组函数是在分完组进行统计结果的，这说明已经得到了表中数据，
--那么WHERE就已经执行过了。所以应当使用HAVING子句是建立在分组基础上，将统计结果进行过滤使用的。
--HAVING必须跟在GROUP BY子句之后。
SELECT AVG(SAL),DEPTNO FROM EMP_WENSQ
GROUP BY DEPTNO
HAVING AVG(SAL)>2000


--查看平均工资高于2000的部门的最高工资与最低工资?
SELECT MAX(SAL),MIN(SAL),DEPTNO FROM EMP_WENSQ
GROUP BY DEPTNO
HAVING AVG(sal)>2000


--关联查询
--查看SELECT部门的员工信息?


SELECT ename,sal,job FROM emp_wensq,dept_wensq
WHERE emp_wensq.deptno=dept_wensq.deptno
AND dept_wensq.dname='SALES'


--查看SCOTT在什么地方工作?
SELECT d.loc
FROM emp_wensq e,dept_wensq d
WHERE e.deptno=d.deptno
AND e.ename='SCOTT'


--查看在DALLAS工作的有哪些职位?
SELECT e.job
FROM emp_wensq e,dept_wensq d
WHERE e.deptno=d.deptno
AND d.loc='DALLAS'


--关联查询必须写连接条件，若不指定连接条件则会出现笛卡尔集，这是一个
--无意义的结果集，对资源消耗极大。所以，N张表联合查询，至少要写N-1个连接条件。
SELECT e.ename,d.dname
FROM emp_wensq e,dept_wensq d


--内连接
SELECT e.ename,e.sal,d.dname
FROM emp_wensq e JOIN dept_wensq d
ON e.deptno=d.deptno                --ON里面写条件
WHERE e.sal>2000                    --写过滤内容


UPDATE emp_wensq
SET deptno=50
WHERE ename='SCOTT'


--外链接可以不满足连接条件的记录也查询出来，外链接分为左外连接，右外连接和全外连接
--左外连接：以JOIN左面的表作为驱动表，该表上的所有记录都会显出在结果集中
--但是在结果集中来自JOIN右侧表中的字段，若该记录不满足连接条件那么则为NULL。
SELECT e.ename,d.dname
FROM emp_wensq e LEFT OUTER JOIN dept_wensq d  --LEFT左--RIG
HT右--FULL全
ON e.deptno=d.deptno


SELECT e.ename,d.dname
FROM emp_wensq e,dept_wensq d
WHERE e.deptno(+)=d.deptno


SELECT e.ename,d.dname
FROM emp_wensq e,dept_wensq d
WHERE e.deptno=d.deptno(+)--加号在那边那边“补”NULL


--自连接：
--当需要保存的数据是相同类型的记录，但是自身又存在上下级关系时（也就是所谓的树状结构数据），
--设计表的时候应当使用自连接。
--自连接：
--自己表中的一条记录可以对应自己表中的多条记录。


--查看每个员工以及其领导的名字?
SELECT e.ename,m.ename
FROM emp_wensq e,emp_wensq m
WHERE e.mgr=m.empno


SELECT * FROM emp_wensq
SELECT * FROM dept_wensq