Oracle 语法总结 (三)

原创

day_day_up_cium 2022-02-11 15:16:19 博主文章分类：database ©著作权

©著作权归作者所有：来自51CTO博客作者day_day_up_cium的原创作品，请联系作者获取转载授权，否则将追究法律责任

一，知识点

字段使用别名
SELECT子句中查询的内容中若是函数或者表达式
那么在结果集中对应的该字段的名字就是这个函数或者表达式
可读性或变得特别差，为此为这样的字段添加别名
结果集中该字段的名字就是该字段的别名
--1
别名不区分大小写，希望大小写或者空格加上双引号
SELECT ename name,sal salary FROM emp_liwc
SELECT ename "name",sal "salary" FROM emp_liwc
--2
<,>,>=,<=,!=,<>,=
SELECT ename,sal,hiredate FROM emp_liwc WHERE hiredate>TO_DATE('1981-1-1','YYYY-MM-DD')
OR,AND(AND的优先级大于OR,有需要时加括号)
SELECT ename,sal,job FROM emp_liwc WHERE sal>1000 AND JOB = 'CLERK'
SELECT ename,sal,job FROM emp_liwc WHERE sal>1000 OR JOB = 'CLERK'
SELECT ename,sal,job FROM emp_liwc WHERE sal>1000 AND (JOB = 'CLERK' OR JOB = 'MILLER')
--3
LIKE（模糊查询）
%表示任意字符
_表示一个字符
查看名字有A的员工
SELECT ename,job,sal FROM emp_liwc WHERE ename LIKE('%A%')
查看第二是O的员工
SELECT ename,job,sal FROM emp_liwc WHERE ename LIKE '_O%'
查看第二是O的员工倒数第二是E的员工
SELECT ename,job,sal FROM emp_liwc WHERE ename LIKE('_O%')AND ename LIKE('%E_')
SELECT ename,job,sal FROM emp_liwc WHERE ename LIKE('_O%E_')
--4
IN,NOT IN(等于其中之一，不等于其中之一,常用在子查询中)
SELECT ename,JOB FROM emp_liwc WHERE job IN('MANAGER','CLERK')
SELECT ename,JOB FROM emp_liwc WHERE job NOT IN('MANAGER','CLERK')
--5
BETWEEN...AND...
判断在一个区间范围内
SELECT ename，sal FROM emp_liwc WHERE sal BETWEEN 1500 AND 3000
--6
ANY(List)和ALL(List)
ANY,ALL是配合>,>=,<,<=使用的
>ANY(LIST)大于列表中的其中之一（大于最小）
>ALL(LIST)大于列表中的其中之一（大于最大）
<ANY(LIST)小于列表中的其中之一（小于最大）
<ALL(LIST)小于列表中的其中之一（小于最小）
通常用于子查询中，定值无意义
SELECT empno,ename,job,sal,deptno FROM emp_liwc WHERE sal>ANY(3000,4000,4500);
SELECT empno,ename,job,sal,deptno FROM emp_liwc WHERE sal<ALL(3000,4000,4500);
SELECT empno,ename,job,sal,deptno FROM emp_liwc WHERE sal<ANY(3000,4000,4500);
SELECT empno,ename,job,sal,deptno FROM emp_liwc WHERE sal>ALL(3000,4000,4500)
--7
DISTINCT(关键字过滤重复)
SELECT DISTINCT JOB FROM emp_liwc
SELECT DISTINCT deptno FROM emp_liwc
ORA-00936: 缺失表达式
SELECT ename,DISTINCT deptno FROM emp_liwc
对多个字段去重，不保证每个字段
一定没有重复值，原则是字段的组合没有重复记录
SELECT DISTINCT job,deptno FROM emp_liwc
--8
ORDER BY子句
ORDER BY子句是对结果集按照指定字段的值进行排序
ORDER BY支持ASC,DESC
ASC为升序不写默认升序
DESC降序
需要注意ORDER BY只能定义在DQL语句的最后一个子句上
SELECT ename,job,sal FROM emp_liwc ORDER BY sal
SELECT ename,job,sal FROM emp_liwc ORDER BY sal ASC
SELECT ename,job,sal FROM emp_liwc ORDER BY sal DESC
字母顺序
SELECT ename,deptno FROM emp_liwc ORDER BY ename DESC
多字段,排序优先级，首先将第一个按照标
准行排序，当地定义字段有重复值得时候
相同记录之间在按照第二字段的标准进行
排序，以此类推，假如有null值，认为为字段值
SELECT ename,job,sal,deptno FROM emp_liwc ORDER BY sal ASC,deptno DESC
ORDER BY在最后字段上否则：ORA-00933: SQL 命令未正确结束
SELECT ename,job,sal,deptno FROM emp_liwc WHERE deptno = 20 ORDER BY sal DESC
null最大
SELECT ename,comm FROM emp_liwc ORDER BY comm
--9
聚合函数(都忽略null)，又称为：多行函数，分组函数
是用来对机关进行统计的
其中4个针对统计的：MAX,MIN,AVG,SUM
还有一个针对计数的统计(不为null的值)：COUNT
查看公司的最高工资
SELECT MAX(sal),MIN(sal),ROUND(AVG(sal)),SUM(sal)FROM emp_liwc
查看公司有多少人
SELECT COUNT(ename) FROM emp_liwc
忽略null
SELECT COUNT(comm),SUM(comm) FROM emp_liwc--4 2200
SELECT COUNT(*) FROM emp_liwc--14
SELECT COUNT(1) FROM emp_liwc--14
奖金平均值忽略null出现问题
SELECT AVG(comm)FROM emp_liwc--550
SELECT AVG(NVL(comm,0))FROM emp_liwc--157
GROUP BY(分组)
按照指定字段值进行分组，配合聚合函数组内进行操作
每个部门平均工资
SELECT AVG(sal),deptno FROM emp_liwc GROUP BY deptno
每个职位的最高工资
当 SELECT 含有聚合函数时，凡是不在聚合函数中的字段，都要出现在 GROUP BY 子句中
SELECT MAX(sal),job FROM emp_liwc GROUP BY job
按照多字段进行分组时原则：这些字段值都一样记录划分为一组
查看同部门的同职位员工有多少人
SELECT COUNT(*),deptno,job FROM emp_liwc GROUP BY job,deptno
查看每个部门平均工资高于2000
--SELECT AVG(sal),deptno FROM emp_liwc GROUP BY deptno WHERE AVG(sal)>2000
WHERE 不能使用聚合函数作为过滤条件
原因是过滤时机不对
WHERE 是逐行过滤的，满足条件的形成结果集
而使用聚合函数进行过滤的前提是分组统计
分组时建立在结果集上的，而WHERE是用来形成结果集时的过滤
所以使用聚合函数过滤应当在WHERE之后进行的
HVING子句，可以使用聚合函数进行过滤
必须跟在GROUP BY 语句后面（不定义GROUP不能单独使用HVING）
添加过滤条件去除不满足条件的分组
SELECT AVG(sal),deptno FROM emp_liwc GROUP BY deptno HAVING AVG(sal)>2000
查看平均工资高于2000的那些部门的最高工资
SELECT MAX(sal),deptno FROM emp_liwc GROUP BY deptno HAVING AVG(sal)>2000
--10
关联查询
指关联多张表联合查询记录，结果集中地字段可能来自多张表
关键点在于连接条件，数据库根据他找到对应关系，以便查询
查询每个员工的名字以及所在部门的名字
--
SELECT ename,dname FROM emp_liwc,dept_liwc WHERE emp_lwc.deptno = dept_lwc.deptno
当某个字段在查询时发现多个表的存在，要求必须指明该字段属于那张表，可以通过表明.字段名的形式标注
也可以添加别名，在通过别名.字段名的形式标注，降低SQL语句的复杂度，增加可读性
SELECT emp_lwc.ename,dept_lwc.dname FROM emp_liwc,dept_liwc WHERE emp_lwc.deptno = dept_lwc.deptno
查询关联表中的要求所有过滤条件必须与关联条件同时成立
SELECT e.ename,d.dname,d.loc FROM emp_liwc e,dept_liwc d WHERE d.deptno = e.deptno AND d.loc = 'NEW YORK'
职位是MANAGER员工在哪个城市
SELECT d.loc FROM emp_liwc e,dept_liwc d WHERE d.deptno = e.deptno AND e.job = 'MANAGER'
N张表关联查询，至少有N-1个关联条件
不指定连接条件，或连接条件无效会产生笛卡尔积，通常是一个无意义的集应当避免
SELECT e.ename,d.dname FROM emp_liwc e,dept_liwc d
--
内连接（关联查询一种比较好）多张表写多个表
SELECT e.ename,d.dname FROM emp_liwc e JOIN dept_liwc d ON d.deptno = e.deptno WHERE d.loc = 'NEW YORK'
关联查询中，不满足连接条件的记录都不会被查询出来
外链接进行关联查询中，除了可以将满足条件的记录显示出来
不满足的也会查询出来
分为：左外链接，右外链接，全外链接
左外链接已JOIN左侧表作为驱动表，该表的所有记录都会被查询出来，不满足连接条件时，右侧表为null
SELECT e.ename,d.dname FROM emp_liwc e LEFT|RIGHT|FULL| OUTER JOIN dept_liwc d ON d.deptno = e.deptno WHERE d.loc = 'NEW YORK'
UPDATE emp_liwc set deptno=50 WHERE ename = 'SCOTT'
SELECT e.ename,d.dname FROM emp_liwc e LEFT OUTER JOIN dept_liwc d ON d.deptno = e.deptno
--
自连接
当前表值得一条记录对应自己表的多条记录
上下级关系
SELECT e.ename,m.ename FROM emp_liwc e,emp_liwc m WHERE e.mgr = m.empno
SELECT e.ename,m.ename FROM emp_liwc e LEFT OUTER JOIN emp_liwc m ON e.mgr = m.empno

二，例子

1:查看工资高于2000的员工
SELECT ename,sal FROM emp_liwc WHERE sal>2000
2:查看不是"CLERK"职位的员工
SELECT ename FROM emp_liwc WHERE job<>'CLERK'
3:查看工资在1000-2500之间的员工
SELECT ename,sal FROM emp_liwc WHERE sal BETWEEN 1000 AND 2500
4:查看名字是以K结尾的员工
SELECT ename FROM emp_liwc WHERE ename LIKE('%K')
5:查看20,30号部门的员工
SELECT ename,deptno FROM emp_liwc WHERE deptno IN(20,30)
6:查看奖金为NULL的员工
SELECT ename,comm FROM emp_liwc WHERE comm IS NULL
7:查看年薪高于20000的员工
SELECT ename,sal FROM emp_liwc WHERE sal*12>20000
8:查看公司共有多少种职位
SELECT DISTINCT JOB FROM emp_liwc
9:按部门号从小到大排列查看员工
SELECT deptno FROM emp_liwc ORDER BY deptno
10:查看每个部门的最高，最低，平均工资，和工资总和
SELECT MAX(sal),MIN(sal),AVG(sal),SUM(sal)FROM emp_liwc GROUP BY deptno
11:查看平均工资高于2000的部门的最低薪水
SELECT MIN(sal)FROM emp_liwc GROUP BY deptno HAVING AVG(sal)>2000
12:查看在NEWYORK工作的员工
SELECT e.ename FROM emp_liwc e,dept_liwc d WHERE e.deptno = d.deptno AND d.loc = 'NEW YORK'
SELECT e.ename FROM emp_liwc e JOIN dept_liwc d ON e.deptno = d.deptno WHERE d.loc = 'NEW YORK'
13:查看所有员工及所在部门信息，若该员工没有部门，则
部门信息以NULL显示
SELECT e.ename,e.deptno,d.deptno FROM emp_liwc e LEFT OUTER JOIN dept_liwc d ON e.deptno = d.deptno
14:查看ALLEN的上司是谁
SELECT e.ename,m.ename FROM emp_liwc e,emp_liwc m WHERE e.mgr = m.empno AND e.ename = 'ALLEN'
15:查看SMITH上司在哪个城市工作?
SELECT d.loc FROM emp_liwc e,emp_liwc m,dept_liwc d WHERE e.mgr = m.empno AND e.deptno = d.deptno And e.ename = 'SMITH'
SELECT d.loc FROM emp_liwc e JOIN emp_liwc m ON e.mgr = m.empno JOIN dept_liwc d ON m.deptno = d.deptno WHERE e.ename = 'SMITH'
16:查看平均工资高于2000的那些部门名字以及所在城市?
SELECT d.dname,d.loc FROM emp_liwc e JOIN dept_liwc d ON e.deptno = d.deptno GROUP BY d.dname,d.loc HAVING AVG(e.sal)>2000
17:在NEW YORK工作的员工有多少人?
SELECT COUNT(*) FROM emp_liwc e,dept_liwc d WHERE e.deptno = d.deptno AND d.loc = 'NEW YORK'
18:在DALLAS工作的员工的平均工资是多少?
SELECT AVG(sal) FROM emp_liwc e,dept_liwc d WHERE e.deptno = d.deptno AND d.loc = 'DALLAS'
SELECT * FROM dept_liwc
SELECT * FROM emp_liwc