2. it
  3. Oracle 语法总结 (三)

Oracle 语法总结 (三)

mac2022-06-30  23

一，知识点

字段使用别名 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 

浮生夢 举世瞩目 当有一天，有星光刺破黑洞的昏暗，那是我吞吐天地的余晖，代表着我已映照诸天。当有一天，有玄雷划过星空的浩瀚，那是我拳光的劲风，代表着我已回来。当星河列阵，宇宙星海星光齐绽，那是我在笑，我已复苏，我在归来，我已无敌！
最新回复(0)