SQL知识脑图

SQL知识脑图，梳理的内容有条件查询、分组查询、集合查询、相关与非相关子查询、联合查询、子查询、排序查询等方面。

SPL 语言

• 条件查询
  • 14./*条件查询*/ 一.定义 为了满足不同的需求 二.语法结构 SELECT COLUMN1,COLUMN2,COLUMN3 ... FROM TABLE_NAME WHERE CONDITION; 分析： 1).条件查询 = 简单查询 + where关键字 + 条件的代码描述 2).WHERE关键字用于连接简单查询和条件，使条件能够作用于SELECT 后 3).加WHERE关键字，可以过滤表中的数据 4).条件分类：关系运算符 布尔连接 包含查询 IN/NOT IN查询 空值判断 ANY/ALL查询 模糊查询 示例： 查询20号部门的员工信息 SELECT * FROM EMP WHERE DEPTNO=20; 三.关系运算符 1.语法 SELECT COLUMN1,COLUMN2,COLUMN3... FROM TABLE_NAME WHERE COLUMN1=(关系运算符)VALUES; 分析： 1).条件往往是：表中的列 =/>...给定的数值 2).关系运算符： = > < >= <= <>/!= 3).SQL语句不区分大小写，数据比较时严格区分大小写 示例： 查询姓名为SCOTT的员工的信息 SELECT * FROM EMP WHERE ENAME ='PEIQI'; SELECT * from EMP where ENAME ='PEIQI';--有数据 select * FROM EMP WHERE ename ='peiqi';--空值 SELECT * FROM EMP WHERE ENAME <>'PEIQI'; SELECT * FROM EMP WHERE ENAME !='PEIQI'; 查询员工表中工资大于1500的员工信息 SELECT * FROM EMP WHERE SAL >1500; SELECT * FROM EMP WHERE SAL <1500; SELECT * FROM EMP WHERE SAL >=1500; SELECT * FROM EMP WHERE SAL <=1500; SELECT * FROM EMP WHERE SAL =>1500;--报错 无效的关系运算符 SELECT * FROM EMP WHERE SAL =!1500;--报错 缺失表达式 2.注意 1).字符型数据比较时需要加单引号声明字符串，数值型的数据不用 --思考 2).日期怎么比较？？？？ 3).字符型数据比较过程？？？？ 四.布尔连接 1.语法 SELECT COLUMN1,COLUMN2,COLUMN3 ... FROM TABLE_NAME WHERE CONDITION1 AND/OR CONDITION2... ; 分析： 1).AND/OR用于连接多个条件 2).条件和条件之间互不影响 2.AND语句 1).语法 SELECT COLUMN1,COLUMN2,COLUMN3 ... FROM TABLE_NAME WHERE CONDITION1 AND CONDITION2... ; 分析： 1).and：和 一起的意思 2)AND连接多个条件时，只有所有的条件均满足时，才会返回结果 示例： 查询20号部门工资大于2000的员工信息 --查询：* 表：EMP 条件：20号部门 工资大于2000 SELECT * FROM EMP WHERE DEPTNO =20 AND SAL>2000; 查询CLERK工位中20号部门的员工的员工姓名，员工的工资和入职时间 SELECT ENAME,SAL,HIREDATE FROM EMP WHERE JOB='CLERK' AND DEPTNO=20; 2.OR语句 1).语法 SELECT COLUMN1,COLUMN2,COLUMN3 ... FROM TABLE_NAME WHERE CONDITION1 OR CONDITION2... ; 分析： 1).OR：或 或者的意思 2).OR连接多个条件时，只要满足其中一个条件及以上，就会将该条数据返回到结果集中 示例： 查询20号部门或者工资大于2000的员工信息 --查询：* 表：EMP 条件：20号部门 工资大于2000 SELECT * FROM EMP WHERE DEPTNO =20 OR SAL>2000; 查询CLERK工位或者20号部门的员工的员工姓名，员工的工资和入职时间 SELECT ENAME,SAL,HIREDATE FROM EMP WHERE JOB='CLERK' OR DEPTNO=20; 查询岗位等于CLERK和MANAGER的员工的姓名，工位，工资,部门编号 SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE JOB='CLERK' OR JOB='MANAGER'; SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE JOB='CLERK' AND JOB='MANAGER';--不符合题意 2).注意 A.同一个列 不同取值用and连接结果为空，不符合题意 B.错误信息: 语法错误，直接报错(按照错误信息提示修改) 语法正确，数据错误（语句执行时数据比较错误） 3.AND和OR 联合使用 示例： 查询20号部门工作为CLERK或者10号部门工资大于1500的员工信息 --查询 ：员工信息 表：EMP 条件：20号部门工作为CLERK 10号部门工资大于1500 SELECT * FROM EMP WHERE DEPTNO=20 AND JOB='CLERK' OR DEPTNO=10 AND SAL>1500; 查询20号部门或者10号部门工资大于1500 或者30号部门岗位是MANAGER的员工的姓名，薪资，岗位，部门编号 --法一 SELECT ENAME, SAL, JOB, DEPTNO FROM EMP WHERE DEPTNO = 20 AND SAL > 1500 OR DEPTNO = 10 AND SAL > 1500 OR DEPTNO = 30 AND JOB = 'MANAGER'; --法二： SELECT ENAME, SAL, JOB, DEPTNO FROM EMP WHERE DEPTNO = 20 OR DEPTNO = 10 AND SAL > 1500 OR DEPTNO = 30 AND JOB = 'MANAGER';---不符合题意 SELECT ENAME, SAL, JOB, DEPTNO FROM EMP WHERE (DEPTNO = 20 OR DEPTNO = 10) AND SAL > 1500 OR DEPTNO = 30 AND JOB = 'MANAGER'; 注意： AND 和OR 联合使用时，优先执行and语句，再执行OR语句 优先级：()>AND>OR 五.包含查询 1.语法 SELECT COLUMN1,COLUMN2... FROM TABLE_NAME WHERE COLUMN1 BETWEEN VALUE1 AND VALUE2; 分析： 1).BETWEEN ... AND...：在...和...之间，包含边界值 2).比较列写在BETWEEN 之前 示例： 查询工资在1500到2000之间的员工信息(包含1500 和 2000) SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 2000; SELECT * FROM EMP WHERE SAL>=1500 AND SAL<=2000; SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 1500;--空值 SELECT * FROM EMP WHERE SAL>=2000 AND SAL<=1500;--空值 SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 1500;--返回工资为1500的数据 注意： 1)可以和AND 互换 2)VALUE1<=VALUE2 否则返回空值 3)包含边界值 六.空值判断 1.语法 SELECT COLUMN1,COLUMN2... FROM TABLE_NAME WHERE COLUMN1 IS NULL;--判断列为空 SELECT COLUMN1,COLUMN2... FROM TABLE_NAME WHERE COLUMN1 IS NOT NULL;--判断列非空 空值：表示什么都没有 和空字符串含义不一样，oracle数据库将空字符串处理为空值 2.示例 查询EMP表中COMM列为空的数据 SELECT * FROM EMP WHERE COMM=NULL;--结果为空 SELECT * FROM EMP WHERE COMM='';--结果为空 SELECT * FROM EMP WHERE JOB=' ';--空格比较 SELECT * FROM EMP WHERE COMM IS NULL; SELECT * FROM EMP WHERE COMM IS NOT NULL;--非空 3.注意 1).空值参与条件比较时，条件恒不成立 2).空值参与运算，结果恒为空(去空值函数) 去空值函数(NVL()): NVL(参数1，参数2):参数一指要处理的表中的列或数据,参数2指当该列的数据为空时赋予函数的值 函数导入表中的数据，经过函数的运算处理后返回该函数的值,不影响表中的数据 示例： SELECT COMM,NVL(COMM,0) FROM EMP; 查询员工的薪资和奖金的和 SELECT SAL,COMM, SAL+COMM FROM EMP;--空值 SELECT SAL,COMM, SAL-COMM FROM EMP;--空值 SELECT SAL,COMM, SAL*COMM FROM EMP;--空值 SELECT SAL,COMM, SAL/COMM FROM EMP;--报错 除数为0 SELECT SAL,COMM, SAL+NVL(COMM,0) FROM EMP; 七.IN查询 1.语法 SELECT COLUMN1,COLUMN2,COLUMN3... FROM TABLE_NAME WHERE COLUMN1 IN(VALUES); SELECT COLUMN1,COLUMN2,COLUMN3... FROM TABLE_NAME WHERE COLUMN1 NOT IN(VALUES); 分析： 1).IN用于连接比较的列和数据集合 2).集合：一组具有形同属性的数据 3).IN:在...里面 示例： 查询薪资等于1500的员工信息 SELECT * FROM EMP WHERE SAL=1500; SELECT * FROM EMP WHERE SAL IN(1500); 查询薪资等于1500,2000,3000的员工信息 SELECT * FROM EMP WHERE SAL=1500 OR SAL=2000 OR SAL=3000; SELECT * FROM EMP WHERE SAL IN(1500,2000,3000); 2.否定IN查询 示例 查询薪资不等于(1500,2000,3000)的员工信息 SELECT * FROM EMP WHERE SAL<>1500 AND SAL<>2000 AND SAL<>3000; SELECT * FROM EMP WHERE SAL NOT IN(1500,2000,3000); SELECT * FROM EMP WHERE SAL NOT IN(1500,2000,'ASD');--无效数字 3.多列in 查询 示例： 查询部门编号和薪资为(30,1500)或者（20,3000）或者(10,1300)的员工信息 SELECT * FROM EMP WHERE DEPTNO IN(10,20,30) AND SAL IN(1500,3000,1300);--错误的 不符合题意 SELECT * FROM EMP WHERE (DEPTNO,SAL) IN(30,1500);--报错 原因比较列过多 SELECT * FROM EMP WHERE (DEPTNO,SAL) IN((30,1500));--正常执行 -------法一 SELECT * FROM EMP WHERE DEPTNO = 30 AND SAL = 1500 OR DEPTNO = 20 AND SAL = 3000 OR DEPTNO = 10 AND SAL = 1300; ----------法二 SELECT * FROM EMP WHERE (DEPTNO,SAL) IN((30,1500),(20,3000),(10,1300)); 查询部门编号和薪资等于10,1300的员工信息 SELECT * FROM EMP WHERE DEPTNO=10 AND SAL=1300; SELECT * FROM EMP WHERE (DEPTNO,SAL)=((10,1300)); SELECT * FROM EMP WHERE (DEPTNO,SAL)<((10,1300));--报错 其他关系运算符报错 SELECT * FROM EMP WHERE (DEPTNO,SAL)=((10,1300),(20,3000));--报错 注意： 1).IN查询往往是列和多个值的比较 2).IN括号内的数据集合是独立的，不连续 3).in查询和OR语句互换，NOT IN查询和AND语句互换 4).多列IN查询时，注意列的个数和数据集合中值的个数一致 八.ANY/ALL/SOME查询 1.语法 SELECT COLUMN1,COLUMN2,COLUMN3... FROM TABLE_NAME WHERE COLUMN1 =ANY(VALUES); SELECT COLUMN1,COLUMN2,COLUMN3... FROM TABLE_NAME WHERE COLUMN1 =ALL(VALUES); 分析： 1).比较列和ANY/ALL/SOME用关系运算符连接 2).关键字后的括号内跟数据集合 2.ANY语句 1)语法 SELECT COLUMN1,COLUMN2,COLUMN3... FROM TABLE_NAME WHERE COLUMN1 =ANY(VALUES); 分析： 关键字：ANY:任意一个 满足一个即可 2).示例： 查询薪资等于1500,2000,3000的员工信息 SELECT * FROM EMP WHERE SAL=1500 OR SAL=2000 OR SAL=3000; SELECT * FROM EMP WHERE SAL IN(1500,2000,3000); SELECT * FROM EMP WHERE SAL = ANY(1500,2000,3000); SELECT * FROM EMP WHERE SAL >= ANY(1500,2000,3000);--大于往往大于的数据集合中的最小值 SELECT * FROM EMP WHERE SAL <= ANY(1500,2000,3000);--小于往往小于的数据集合中的最大值 SELECT * FROM EMP WHERE (DEPTNO,SAL) >= ANY((30,1500),(20,2000),(20,3000));--报错 SELECT * FROM EMP WHERE (DEPTNO,SAL) = ANY((30,1500),(20,2000),(20,3000)); SELECT * FROM EMP WHERE SAL<> ANY(1500,2000,3000); SELECT * FROM EMP WHERE SAL<>1500 OR SAL<>2000 OR SAL<>3000; 查询工资等于1500或3000的员工信息 SELECT * FROM EMP WHERE SAL =ANY(1500,3000); SELECT * FROM EMP WHERE SAL =SOME(1500,3000); 3.ALL语句 1)语法 SELECT COLUMN1,COLUMN2,COLUMN3... FROM TABLE_NAME WHERE COLUMN1 =ALL(VALUES); 分析： 关键字：ALL:全部的 所有的 2).示例： 查询薪资都不等于1500,2000,3000的员工信息 SELECT * FROM EMP WHERE SAL<>1500 AND SAL<>2000 AND SAL<>3000; SELECT * FROM EMP WHERE SAL NOT IN(1500,2000,3000); SELECT * FROM EMP WHERE SAL <> ALL(1500,2000,3000); SELECT * FROM EMP WHERE SAL = ALL(1500,2000,3000);--返回空值 SELECT * FROM EMP WHERE SAL > ALL(1500,2000,3000);--大于最大值 SELECT * FROM EMP WHERE SAL < ALL(1500,2000,3000);--小于最小值 4.NOT IN中的空值 示例： 查询工资不等于1500,3000,NULL的员工信息 SELECT * FROM EMP WHERE SAL NOT IN(1500,3000,NULL);--错误的 不符合题意的 SELECT * FROM EMP WHERE SAL<>1500 AND SAL<>3000 AND SAL<>NULL;--空值参与条件比较，返回空值 SELECT * FROM EMP WHERE NVL(SAL,0) NOT IN(1500,3000,0);--不符合题意的 SELECT * FROM EMP WHERE SAL NOT IN(1500,3000) AND SAL IS NOT NULL; 注意： 1).ANY和比较列用关系运算符连接 2).ANY: 大于集合中的最小值 小于集合中的最大值 3).ALL: 大于集合中的最大值 小于集合中的最小值 4).IN做否定时，若集合条件中含有空值，返回空值 -
• 分组查询
  • /*分组查询*/ 一.不带条件的分组查询 1.语法： SELECT COLUMN1,COLUMN2... FROM TABLE_NAME (WHERE CONDITION) GROUP BY COLUMN1; 分析： 1).语法:简单查询+（条件）+分组关键字分组 GROUP BY + COLUMN1 以GROUP BY后跟的字段分组 关键字：GROUP BY 2).执行顺序 3).书写规范 4).往往和分组函数联合使用 示例： 查询各部门的员工人数 SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO; SELECT SUM(CASE WHEN DEPTNO=10 THEN 1 END), SUM(CASE WHEN DEPTNO=20 THEN 1 END), SUM(CASE WHEN DEPTNO=30 THEN 1 END) FROM EMP; 统计10号部门的员工人数 SELECT COUNT(*) FROM EMP WHERE DEPTNO=10; 统计10号部门的员工人数和部门编号 SELECT COUNT(*),DEPTNO FROM EMP WHERE DEPTNO=10;--报错 不是单组分组函数 SELECT COUNT(*),DEPTNO FROM EMP WHERE DEPTNO=10 GROUP BY DEPTNO; 注意： 1).分组函数不能和表中的单纯列联合查询 2).若题目中含有其他的列和分组函数，必须使用分组 3).对应关系：一对一 一对多 多对一 多对多 2.分组中SELECT后的字段 示例： 1.查询emp表中各部门的薪资和以及部门编号 SELECT SUM(SAL),DEPTNO FROM EMP GROUP BY DEPTNO; SELECT SUM(SAL),DEPTNO,ENAME FROM EMP GROUP BY DEPTNO;--报错 不是GROUP BY表达式 SELECT SUM(SAL),DEPTNO,ENAME FROM EMP GROUP BY DEPTNO,ENAME; SELECT SUM(SAL),DEPTNO FROM EMP GROUP BY DEPTNO,ENAME; 2.查询emp表中各部门的薪资和以及部门编号 SELECT SUM(SAL),DEPTNO FROM EMP GROUP BY DEPTNO; SELECT SUM(SAL),DEPTNO,CASE WHEN DEPTNO=10 THEN 1 WHEN DEPTNO=20 THEN 2 WHEN DEPTNO=30 THEN 3 END FROM EMP GROUP BY DEPTNO; SELECT SUM(SAL),DEPTNO,CASE WHEN SAL=10 THEN 1 WHEN SAL=20 THEN 2 WHEN SAL=30 THEN 3 END FROM EMP GROUP BY DEPTNO; ----报错 注意： 1).SELECT后的字段必须为：GROUP BY表达式中的分组列 2).SELECT后的字段可以为分组函数 3).SELECT后可以跟分组列中的字段的函数运算(了解) 3.GROUP BY后的字段 示例： 1.单列分组 查询各个部门的员工平均薪资 SELECT AVG(SAL)FROM EMP GROUP BY DEPTNO; 2.多列分组 查询各个部门中各个工种的薪资和，平均薪资 SELECT DEPTNO,JOB,SUM(SAL),AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB;--先部门编号，再工作 SELECT DEPTNO,JOB,SUM(SAL),AVG(SAL) FROM EMP GROUP BY JOB ,DEPTNO;--先工作 再部门编号 注意： 1).多列分组查询时，先按照第一个列分组，若第一个列的值相同 再按照第二个列分组统计， 若第二个列的值相同，在按照第三个列分组统计.....直到分组列全都分组 3.函数运算 示例： 查询emp表中各个部门的最大最小薪资 SELECT DEPTNO,MIN(SAL),MAX(SAL) FROM EMP GROUP BY DEPTNO; SELECT DEPTNO,MIN(SAL),MAX(SAL) FROM EMP GROUP BY NVL(DEPTNO,0);--报错 不是GROUP BY表达式 SELECT NVL(DEPTNO,0),MIN(SAL),MAX(SAL) FROM EMP GROUP BY NVL(DEPTNO,0); 按照年薪分组，查询薪资最大值 SELECT SAL,MAX(SAL) FROM EMP GROUP BY SAL*12;--报错 不是GROUP BY表达式 SELECT SAL*12,MAX(SAL) FROM EMP GROUP BY SAL*12; 注意： 按照函数运算分组，统计的列为经过函数运算后的字段,不能查询原有的字段 4.常量 示例： SELECT SUM(SAL) FROM EMP GROUP BY 1;--可以执行 不分组 SELECT SUM(SAL) FROM EMP GROUP BY 'A';--可以执行 不分组 SELECT SUM(SAL) FROM EMP GROUP BY 1,2,3;--可以执行 不分组 5. 别名 示例： SELECT SUM(SAL),DEPTNO E FROM EMP GROUP BY E;--报错 E标识符无效 SELECT SUM(SAL),DEPTNO FROM EMP E GROUP BY E.DEPTNO; 6.小结 1).多列分组查询时，先按照第一个列分组，若第一个列的值相同 再按照第二个列分组统计， 若第二个列的值相同，在按照第三个列分组统计.....直到分组列全都分组 2).按照函数运算分组，统计的列为经过函数运算后的字段,不能查询原有的字段 3).列别名在分组中无效,表别名有效 4).按照常量分组，实际执行中未分组，统计为表中全部数据 二.带条件的分组查询 1.语法： SELECT COLUMN1,COLUMN2... FROM TABLE_NAME (WHERE CONDITION) GROUP BY COLUMN1 HAVING CONDITION; 解释：HAVING + 条件：用于过滤分组后的数据 示例： 查询各个部门中平均工资大于1600的部门编号及平均工资 SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 1600; 2.HAVING后的字段 示例： 查询各个部门中平均工资大于1600的部门编号及平均工资 SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 1600; 查询各个部门中部门编号大于20的部门编号及平均工资 SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO>20 GROUP BY DEPTNO; SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO>20; SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING SAL>1500;---SAL不是GROUP BY表达式 注意： 1)HAVING后可以是分组函数 2)HAVING后必须为分组列中的列 3.WHERE和HAVING的区别与联系 联系： 1)均作为条件使用，过滤表中数据 2)当HAVING后的字段为分组列中的字段时，可以和WHERE互换(能用where尽量使用where) 区别： 1) WHERE过滤的是FROM后表的数据，HAVING 过滤的是分组后表中的数据 2) HAVING往往和GROUP BY联用过滤数据，WHERE不必和GROUP BY联用 3) HAVING可以加分组函数作为条件，where不能 三.重点 1.掌握排序分组的书写规范 2.GROUP BY后可以加：函数、运算、字段名、常量，不可以跟别名 3.分组中SELECT后可以为： 分组函数（SUM()/AVG()/COUNT()/MIN()/MAX()..）,在分组列内的字段名，用到分组列的函数运算 4.GROUP BY过滤数据时和HAVING联合使用，HAVING加字段名，字段名需为GROUP BY后的排序列、分组函数 5.WHERE 后不能加分组函数 6.WHERE和HAVING的区别与联系 !!!!! 7.执行顺序: FROM ->WHERE ->GROUP BY->HAVING->SELECT->ORDER BY 四.做题步骤 1.分析题目，确定使用的关键字 SELECT FROM WHERE GROUP BY HAVING ORDER BY SELECT: 要查询的内容 FROM : 题目中使用的数据来源（表） WHERE: 除去分组函数的条件 GROUP BY: 题目中是否含有分组统计的含义 HAVING : 分组函数作为条件过滤数据时 ORDER BY: 题目中有没有要求排序 2.将各个关键字的题目描述化为代码描述，写在各个关键字的后面
• 集合查询
  • /*集合查询*/ 一.概念 将不同的数据集合(SQL查询语句)按照集合的规则，拼接一个临时的，新的数据集合(表) 集合包括：并集 交集 差集 --关键字 并集： UNION UNION ALL 交集： INTERSECT 差集： MINUS 二.并集 1.概念 将不同的数据集合按照并集的规则拼接到一起 关键字：UNION UNION ALL 2.语法结构 SELECT 语句 SELECT 语句 UNION UNION ALL SELECT 语句; SELECT 语句; 3.示例 查询工资等于1600或者等于3000或者等于1250的员工信息 SELECT * FROM EMP WHERE SAL =1600 OR SAL=1250 OR SAL=3000; SELECT * FROM EMP WHERE SAL IN(1600 ,1250 ,3000); SELECT * FROM EMP WHERE SAL =ANY(1600 ,1250 ,3000); SELECT * FROM EMP WHERE SAL=1600 UNION SELECT * FROM EMP WHERE SAL=3000 UNION SELECT * FROM EMP WHERE SAL=1250; SELECT * FROM EMP WHERE SAL=1600 UNION ALL SELECT * FROM EMP WHERE SAL=3000 UNION ALL SELECT * FROM EMP WHERE SAL=1250; 4.区别 示例： 查询工资等于1600或者等于3000或者等于1250的员工信息 SELECT ENAME,JOB,SAL FROM EMP WHERE SAL=1600 UNION SELECT ENAME,JOB,SAL FROM EMP WHERE SAL=3000 UNION SELECT ENAME,JOB,SAL FROM EMP WHERE SAL=1250;----按照ENAME排序 SELECT ENAME,JOB,SAL FROM EMP WHERE SAL=1600 UNION ALL SELECT ENAME,JOB,SAL FROM EMP WHERE SAL=3000 UNION ALL SELECT ENAME,JOB,SAL FROM EMP WHERE SAL=1250; --ENAME列无序状态 SELECT SAL,JOB,SAL FROM EMP WHERE SAL=1600 UNION SELECT SAL,JOB,SAL FROM EMP WHERE SAL=3000 UNION SELECT SAL,JOB,SAL FROM EMP WHERE SAL=1250;--按照第一个工资排序 SELECT SAL,JOB,SAL FROM EMP WHERE SAL=1600 UNION ALL SELECT SAL,JOB,SAL FROM EMP WHERE SAL=3000 UNION ALL SELECT SAL,JOB,SAL FROM EMP WHERE SAL=1250;--依然无序状态 查询工资等于3000和1250的员工的薪资，岗位 SELECT SAL,JOB FROM EMP WHERE SAL IN(1250,3000); SELECT SAL,JOB FROM EMP WHERE SAL=1250 UNION SELECT SAL,JOB FROM EMP WHERE SAL=3000; SELECT SAL,JOB FROM EMP WHERE SAL=1250 UNION ALL SELECT SAL,JOB FROM EMP WHERE SAL=3000; 注意： 1).UNION会将结果按照第一列升序排列 ，UNION ALL只是将数据集合并到一起，不排序 2).UNION会将结果去重，UNION ALL不会去重 5.数据列名展示 示例: 查询工资等于3000和1250的员工的薪资，岗位 SELECT SAL,JOB FROM EMP WHERE SAL=1250 UNION SELECT SAL,JOB FROM EMP WHERE SAL=3000; SELECT SAL,JOB FROM EMP WHERE SAL=1250 UNION SELECT SAL S,JOB FROM EMP WHERE SAL=3000;--结果以 SAL JOB 显示 SELECT SAL S,JOB FROM EMP WHERE SAL=1250 UNION SELECT SAL ,JOB FROM EMP WHERE SAL=3000;--结果以 S JOB 显示 --集合操作时，最终的数据展示以第一个集合中的字段为主 6.报错 查询工资等于3000和1250的员工的薪资，岗位 SELECT SAL,JOB FROM EMP WHERE SAL=1250 UNION SELECT SAL,JOB FROM EMP WHERE SAL=3000; SELECT SAL,JOB FROM EMP WHERE SAL=1250 UNION SELECT EMPNO,JOB FROM EMP WHERE SAL=3000; SELECT SAL,JOB FROM EMP WHERE SAL=1250 UNION SELECT ENAME,JOB FROM EMP WHERE SAL=3000;--报错 数据类型不一致 SELECT SAL,JOB FROM EMP WHERE SAL=1250 UNION SELECT SAL,JOB,EMPNO FROM EMP WHERE SAL=3000;--报错 查询块具有不正确的结果列数 --前后两个数据集合的查询字段数据类型和个数一致 三.交集 1.概念 将不同的数据集合按照交集的规则拼接到一起 关键字：INTERSECT 2.语法结构 SELECT 语句 INTERSECT SELECT 语句; 3.示例 查询部门为10且工资大于1500的员工信息 SELECT * FROM EMP WHERE DEPTNO =10 AND SAL>1500; SELECT * FROM EMP WHERE DEPTNO=10 INTERSECT SELECT * FROM EMP WHERE SAL>1500; 4.排序 示例： 查询emp表的数据 SELECT * FROM EMP WHERE DEPTNO IN(20,10) INTERSECT SELECT * FROM EMP WHERE DEPTNO IN(20,30);--按照EMPNO升序排列 SELECT SAL,JOB FROM EMP WHERE DEPTNO IN(20,10) INTERSECT SELECT SAL,JOB FROM EMP WHERE DEPTNO IN(20,30);--按照SAL升序排列 --按照结果中的第一个列升序排列 5.报错 示例： 查询emp表的数据 SELECT * FROM EMP WHERE DEPTNO IN(20,10) INTERSECT SELECT * FROM EMP WHERE DEPTNO IN(20,30);--按照EMPNO升序排列 SELECT SAL,JOB,ENAME FROM EMP WHERE DEPTNO IN(20,10) INTERSECT SELECT ENAME,JOB,SAL FROM EMP WHERE DEPTNO IN(20,30);--数据类型不一致 SELECT SAL,JOB,ENAME FROM EMP WHERE DEPTNO IN(20,10) INTERSECT SELECT SAL,JOB FROM EMP WHERE DEPTNO IN(20,30);--报错 结果列数不一致 --前后两个数据集合的结果要求数据类型和列的个数一致 四.差集 1.概念 将不同的数据集合按照差集的规则拼接到一起 关键字： MINUS 2.语法结构 SELECT 语句 MINUS SELECT 语句; 3.示例 查询10号部门有而20号部门没有的工种 SELECT JOB FROM EMP WHERE DEPTNO=10 MINUS SELECT JOB FROM EMP WHERE DEPTNO=20; 查询20号部门有而10号部门没有的工种 SELECT JOB FROM EMP WHERE DEPTNO=20 MINUS SELECT JOB FROM EMP WHERE DEPTNO=10; --注意：差集操作时，顺序不同结果也不同 4.排序 示例： 查询emp表的数据 SELECT * FROM EMP WHERE DEPTNO IN(20,10) MINUS SELECT * FROM EMP WHERE DEPTNO IN(10);--按照EMPNO升序排列 SELECT SAL,JOB FROM EMP WHERE DEPTNO IN(20,10) MINUS SELECT SAL,JOB FROM EMP WHERE DEPTNO IN(10);--按照SAL升序排列 --按照结果中的第一个列升序排列 5.报错 示例： 查询emp表的数据 SELECT * FROM EMP WHERE DEPTNO IN(20,10) MINUS SELECT * FROM EMP WHERE DEPTNO IN(20,30);--按照EMPNO升序排列 SELECT SAL,JOB,ENAME FROM EMP WHERE DEPTNO IN(20,10) MINUS SELECT ENAME,JOB,SAL FROM EMP WHERE DEPTNO IN(20,30);--数据类型不一致 SELECT SAL,JOB,ENAME FROM EMP WHERE DEPTNO IN(20,10) MINUS SELECT SAL,JOB FROM EMP WHERE DEPTNO IN(20,30);--报错 结果列数不一致 --前后两个数据集合的结果要求数据类型和列的个数一致 五.重点 1.掌握集合的语法，运算规则 2.UNION和UNION ALL的区别： UNION:排序 去重 UNION ALL:不排序 不去重 3.UNION/INTERSECT/MINUS 均会按照第一个列升序排序,去重 4.MINUS 用于查询集合中独有的数据 5.MINUS前后集合的顺序不同影响返回结果 6.集合操作时注意：集合的列的数据类型和个数一致 --附加题 查询10号部门和20号部门不相同的岗位 --查询10号部门独有的工作 SELECT JOB FROM EMP WHERE DEPTNO=10 MINUS SELECT JOB FROM EMP WHERE DEPTNO=20; --查询20号部门独有的工作 SELECT JOB FROM EMP WHERE DEPTNO=20 MINUS SELECT JOB FROM EMP WHERE DEPTNO=10; --将其合并到一起 (SELECT JOB FROM EMP WHERE DEPTNO=10 MINUS SELECT JOB FROM EMP WHERE DEPTNO=20) UNION (SELECT JOB FROM EMP WHERE DEPTNO=20 MINUS SELECT JOB FROM EMP WHERE DEPTNO=10);
• 相关/非相关子查询
  • /*相关非相关子查询*/ 一.定义 不相关子查询： 子查询语句的执行不受主查询语句的影响，子查询语句能够独立运行 相关子查询： 子查询语句的执行受主查询语句的影响，子查询语句不能独立运行 二.EXISTS 1.概念 用于WHERE后，当其后有数据返回，就执行主查询语句，无结果返回，就不执行主查询语句(返回结果为空) 2.语法 SELECT COLUMN1,COLUMN2... FROM TABLE_NAME WHERE EXISTS (SELECT 语句); SELECT COLUMN1,COLUMN2... FROM TABLE_NAME WHERE NOT EXISTS (SELECT 语句); 示例： SELECT EMPNO, ENAME, SAL FROM EMP WHERE EXISTS (SELECT EMPNO FROM EMP WHERE DEPTNO = 20);--正常执行 SELECT EMPNO, ENAME, SAL FROM EMP WHERE EXISTS (SELECT EMPNO FROM EMP WHERE DEPTNO = 40); --返回为空 SELECT EMPNO, ENAME, SAL FROM EMP WHERE EXISTS (SELECT COMM FROM EMP WHERE ENAME='SMITH'); --章程执行 SELECT EMPNO, ENAME, SAL FROM EMP WHERE EXISTS (1500);--缺失SELECT 关键字 3.EXISTS作为相关子查询的使用 示例： 查询smith所在部门的员工信息 SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH'); SELECT * FROM EMP E WHERE EXISTS (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH' AND E.DEPTNO = DEPTNO); 查询工资大于部门内平均薪资的员工信息(了解) SELECT * FROM EMP E WHERE EXISTS (SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING E.DEPTNO = DEPTNO AND E.SAL > AVG(SAL)); 查询在纽约工作的员工的编号，姓名，以及职位 SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'NEW YORK'); SELECT EMPNO, ENAME, JOB FROM EMP WHERE EXISTS (SELECT DEPTNO FROM DEPT WHERE LOC = 'NEW YORK' AND EMP.DEPTNO=DEPTNO); 三.做题步骤 1.要明确比较的列/联系外表和子查询内表的列 2.按照结构把题目中描述的大体用代码写出 主查询语句 子查询语句 3.把比较的列引入子查询中（在子查询条件后直接加AND/OR让内外两表的关系列=/>/<） 4.将两个条件联系以后，根据EXISTS独有的规则判断该条数据符不符合子查询的条件，有无结果返回 决定该条数据是否返回到结果集中 四.重点 1.明确在哪个位置添加两个表的关联条件！！！！！！ 2.非相关子查询可以用在多个位置,相关子查询一般用在WHERE或HAVING子句中作为条件，两者可以发生转换。 3.掌握IN和EXISTS互换的方式 4.相关子查询之所以存在，是因为在很多情况下使用相关子查询效率比较高。！！！ 5.当子查询中的数据量比较大时，用EXISTS，当子查询中的数据量比较小时用 IN 6.在哪些位置可以添加哪中类型的子查询语句 7.掌握两个做题步骤：子查询做题步骤 相关子查询做题步骤 五.练习题 查询工资大于2000的员工所在部门 的员工信息 查询工资全部大于2000的部门的员工信息 查询部门平均工资大于2000的员工的姓名及职位 查询工资大于2000的员工所在部门 的员工信息且部门编号不等于10
• 联合查询/表连接
  • /*联合查询/表连接*/ 一.概述 当查询语句中的数据来源于多个表时，或者一张表不能满足需求时，需要多表连接查询 表连接：将多个数据源中的数据通过表连接的规则将其整合在一张新的，临时的表中，这个过程称之为表连接 分类： 外连接 内连接 交叉连接(笛卡尔积连接) 二.内连接(常用) 1.语法 SELECT COLUMN1,COLUMN2... FROM TABLE1 [INNER] JOIN TABLE2 ON TABLE1.COLUMN=TABLE2.COLUMN; 分析： 1).[INNER] JOIN ->指明两表做内连接操作 2).ON后的条件决定两表的数据关联形式 3).条件往往选择两个表中描述同一事物的列，两表中列的属性一致的列作为关联条件 4).INNER 可以省略 示例： 将表emp和dept做内连接操作 SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO; 2.数据关联 方式：按照内连接得到方式关联表中数据，取出两表中符合关联条件的数据，连接在一起显示， 不符合关联条件的数据直接舍弃 示例： 将表emp和dept做内连接操作 SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO; 三.外连接 1.左外连接(常用) --语法 SELECT COLUMN1,COLUMN2... FROM TABLE1 LEFT [OUTER] JOIN TABLE2 ON TABLE1.COLUMN=TABLE2.COLUMN; 分析： 1).LEFT [OUTER] JOIN->指明两表做左外连接操作 2).ON后的条件决定两表的数据关联形式 3).条件往往选择两个表中描述同一事物的列，两表中列的属性一致的列作为关联条件 4).OUTER 可以省略 示例： emp表和DEPT表做左外连接操作 SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO; --数据关联 方式：取出两表中能关联到一起的数据，若关联不上的数据优先展示左表的数据对应的右表中的列的数据为空值 示例： emp表和DEPT表做左外连接操作 SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO; 2.右外连接 --语法 SELECT COLUMN1,COLUMN2... FROM TABLE1 RIGHT [OUTER] JOIN TABLE2 ON TABLE1.COLUMN=TABLE2.COLUMN; 分析： 1).RIGHT [OUTER] JOIN->指明两表做右外连接操作 2).ON后的条件决定两表的数据关联形式 3).条件往往选择两个表中描述同一事物的列，两表中列的属性一致的列作为关联条件 4).OUTER 可以省略 示例： emp表和DEPT表做右外连接操作 SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO; --数据关联 方式：取出两表中能关联到一起的数据，若关联不上的数据优先展示右表的数据 对应的左表中的列的数据为空值 示例： emp表和DEPT表做左外连接操作 SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO; 3.全外连接(少用) --语法 SELECT COLUMN1,COLUMN2... FROM TABLE1 FULL [OUTER] JOIN TABLE2 ON TABLE1.COLUMN=TABLE2.COLUMN; 分析： 1).FULL [OUTER] JOIN->指明两表做全外连接操作 2).ON后的条件决定两表的数据关联形式 3).条件往往选择两个表中描述同一事物的列，两表中列的属性一致的列作为关联条件 4).OUTER 可以省略 示例： emp表和DEPT表做全外连接操作 SELECT * FROM EMP E FULL OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO; --数据关联 方式：取出两表中能关联到一起的数据， 若左表中有关联不上的数据展示左表的数据 对应的右表中的列的数据为空值 若右表中有关联不上的数据展示右表的数据 对应的左表中的列的数据为空值 示例： emp表和DEPT表做左外连接操作 SELECT * FROM EMP E FULL OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO; SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO UNION SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO; 四.交叉连接 1.语法 SELECT COLUMN1,COLUMN2... FROM TABLE1 CROSS JOIN TABLE2 ; 分析： 1).CROSS JOIN->指明两表做交叉连接操作 2).不需要 ON条件 3).会造成数据量暴涨 示例： 查询EMP表和DEPT表的交叉连接结果 SELECT * FROM EMP CROSS JOIN DEPT; 假设 乒乓球比赛 两两比赛一场 以胜场数决定冠亚军 查询SMITH 的对手名单 SELECT E1.ENAME, E2.ENAME FROM EMP E1 CROSS JOIN EMP E2 WHERE E1.ENAME = 'SMITH' AND E2.ENAME <> 'SMITH'; 五.特殊的连接方式 1.自然连接(不用) 语法： SELECT COLUMN1,COLUMN2... FROM TABLE1 NATURAL JOIN TABLE2; 连接： 会自动寻找表中相同的字段作为连接条件关联数据，并将相同的字段放于第一个列显示，且只有一个 若无相同字段，两表做交叉连接 示例： 以EMP和DEPT表为例 --含有相同字段 SELECT * FROM EMP NATURAL JOIN DEPT;--类似于内连接 --不含有相同字段 SELECT * FROM EMP NATURAL JOIN DEPT;--类似于交叉连接 --弊端：依赖于表中的字段， 若表中字段发生变化影响数据展示 2.USING连接(不用) 语法 SELECT COLUMN1,COLUMN2... FROM TABLE1 INNER JOIN TABLE2 USING(COLUMN1); 连接： 将USING后的字段(两表共有的相同字段)作为连接条件关联数据，并将相同的字段放于第一个列显示，且只有一个 若无相同的字段 报错 示例： 以EMP 和DEPT表为例 SELECT * FROM EMP INNER JOIN DEPT USING(DEPT_NO);--报错 SELECT * FROM EMP INNER JOIN DEPT USING(DEPTNO); --弊端：十分依赖于表中相同的字段 3.自连接 --用于连接的表均为一张表 可以是内连接 外连接 交叉连接 示例： 查询数据 SELECT * FROM EMP E1 INNER JOIN EMP E2 ON E1.EMPNO=E2.EMPNO; 六.oracle数据库独有的连接方式 内连接： SELECT COLUMN1,COLUMN2... FROM TABLE1,TABLE2 WHERE TABLE1.COLUMN=TABLE2.COLUMN; 示例： 以EMP 和DEPT表为例 SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO; 交叉连接： SELECT COLUMN1,COLUMN2... FROM TABLE1,TABLE2 ; 示例： 以EMP 和DEPT表为例 SELECT * FROM EMP E,DEPT D; 外连接 左外连接： SELECT COLUMN1,COLUMN2... FROM TABLE1,TABLE2 WHERE TABLE1.COLUMN=TABLE2.COLUMN(+); 示例： 以EMP 和DEPT表为例 SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO(+); 右外连接： SELECT COLUMN1,COLUMN2... FROM TABLE1,TABLE2 WHERE TABLE1.COLUMN(+)=TABLE2.COLUMN; 示例： 以EMP 和DEPT表为例 SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO; 七.多表连接 以emp DEPT SALGRADE表为例 SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL; SELECT * FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL; 八.练习题 查询EMP表中所有的员工信息以及该员工的领导编号和姓名 统计员工表中工资大于员工本身的工资的员工人数 统计每个部门中工资大于该员工本身的工资的员工人数 九.思考题 1.表连接做题步骤 2.何时必须要用表连接，何时均可 3.什么时候添加别名比较合适 4.集合和联合查询有何区别 5.连接方式的适用情况 十.重点 1.掌握联合查询的书写规范 2.辨别各个连接方式的区别 3.在写关联条件时一定要注意表中列名的关联 4.关联条件并不是只有一个，也并不是只有等于，AND做连接 5.表关联做题步骤一定要掌握！！！！
• 子查询
  • /*子查询*/ 一.概述 查询语句结构： SELECT A FROM B WHERE C GROUP BY D HAVING E ORDER BY F; 子查询：在主查询语句中插入查询语句 特点: 1.子查询语句为一个独立的查询语句(多数情况下) 2.可以嵌套多个子查询语句 3.可以在多个子句中加子查询语句 示例： 查询SMITH所在部门的员工信息 --SELECT FROM WHERE SELECT * FROM EMP FOR UPDATE; SELECT * FROM EMP WHERE DEPTNO = 20;--条件查询 SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH');--子查询语句 二.表中数据分类(不包含空值) 按照表中数据的展现形式不同： 单行单列: 表中只含有一行一列的数据 示例： 查询emp表中SMITH的工资 SELECT SAL FROM EMP WHERE ENAME='SMITH'; 单行多列:表中含有一行多列的数据 示例： 查询emp表中SMITH的工资,工作 SELECT SAL,JOB FROM EMP WHERE ENAME='SMITH'; 多行单列:表中含有多行一列的数据 示例： 查询emp表中工资 SELECT SAL FROM EMP ; 多行多列:表中含有多行多列的数据 示例： 查询emp表的工资,工作 SELECT SAL,JOB FROM EMP; 三.SELECT + 子查询 --单行单列 示例： SELECT (SELECT SAL FROM EMP WHERE ENAME='SMITH') FROM DUAL;--相当于查询常量 能够执行 --单行多列 示例： SELECT (SELECT SAL,JOB FROM EMP WHERE ENAME='SMITH') FROM DUAL;--报错 值过多 SELECT 800,'CLERK' FROM DUAL;--不报错 SELECT (800,'CLERK') FROM DUAL;--报错 --多行单列 示例： SELECT (SELECT SAL FROM EMP) FROM DUAL;--报错 单行子查询返回多个行 --多行多列 示例： SELECT (SELECT SAL,JOB FROM EMP) FROM DUAL;--报错 值过多 SELECT DBMS_RANDOM.VALUE(1,44) FROM DUAL; 四.FROM + 子查询 --单行单列 SELECT * FROM (SELECT EMPNO FROM EMP WHERE SAL>1500);--可以执行 SELECT * FROM (SELECT 1 FROM EMP WHERE SAL>1500);--可以执行 --单行多列 SELECT * FROM (SELECT EMPNO,ENAME FROM EMP WHERE SAL=800);--可以执行 --多行单列 SELECT * FROM (SELECT EMPNO FROM EMP);--可以执行 --多行多列 SELECT * FROM (SELECT EMPNO,ENAME FROM EMP );--可以执行 五.WHERE + 子查询 --单行单列 示例： SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH');--可以执行 SELECT * FROM EMP WHERE DEPTNO=(SELECT ENAME FROM EMP WHERE ENAME='SMITH');--报错 --单行多列 示例： SELECT * FROM EMP WHERE DEPTNO=(SELECT SAL,JOB FROM EMP WHERE ENAME='SMITH');--报错 值过多 SELECT * FROM EMP WHERE (SAL,JOB)=(SELECT SAL,JOB FROM EMP WHERE ENAME='SMITH');--可以执行 --多行单列 示例： SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP); --报错 单行子查询返回多个行 SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP);--可以执行 --多行多列 示例： SELECT * FROM EMP WHERE DEPTNO=(SELECT SAL,JOB FROM EMP);--报错 值过多 SELECT * FROM EMP WHERE (SAL,JOB) IN(SELECT SAL,JOB FROM EMP);--可以执行 六.GROUP BY + 子查询 --单行单列 SELECT * FROM EMP GROUP BY (SELECT SAL FROM EMP WHERE ENAME='SMITH');----不允许出现子查询 七.HAVING + 子查询 --单行单列 SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) = (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');--可以执行 --单行多列 SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) = (SELECT DEPTNO,SAL FROM EMP WHERE ENAME = 'SMITH');--值过多 报错 SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING (DEPTNO,AVG(SAL)) = (SELECT DEPTNO,SAL FROM EMP WHERE ENAME = 'SMITH');--可以执行 --多行单列 SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) = (SELECT SAL FROM EMP );--单行子查询返回多个行 报错 SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) IN (SELECT SAL FROM EMP );--可以执行 --多行多列 SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) = (SELECT DEPTNO, SAL FROM EMP );--值过多 报错 SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING (DEPTNO,AVG(SAL)) IN (SELECT DEPTNO, SAL FROM EMP );--可以执行 八.ORDER BY + 子查询 --单行单列 SELECT * FROM EMP ORDER BY (SELECT ENAME FROM EMP WHERE SAL=800);--可以执行 --单行多列 SELECT * FROM EMP ORDER BY (SELECT SAL,DEPTNO FROM EMP WHERE SAL=800);--报错 值过多 --多行单列 SELECT * FROM EMP ORDER BY (SELECT EMPNO FROM EMP);--报错 单行子查询返回多个行 --多行多列 SELECT * FROM EMP ORDER BY (SELECT EMPNO,ENAME FROM EMP );--报错 注意： 1).SELECT 后只能跟单行单列的子查询语句 FROM后：单行单列 单行多列 多行单列 多行多列 WHERE后：单行单列 单行多列 多行单列 多行多列 GROUP BY后不允许出现子查询语句 HAVING后：单行单列 单行多列 多行单列 多行多列 ORDER BY后：只能加单行单列的子查询语句 2).报错 值过多：往往是子查询返回的列数和主查询语句中提供的列数不一致 单行子查询返回多个行:往往是子查询返回的行数和主查询语句中提供的行数不一致 九.做题步骤 示例： 1.查询30号部门中工资大于20号部门的平均薪资的员工信息 --方法一： --从题目中能得到什么写什么(一个完整的SELECT语句) SELECT AVG(SAL) FROM EMP WHERE DEPTNO=20; --将从题目中提取的SQL结果带入题目中 查询30号部门中工资大于2518.75的员工信息 SELECT * FROM EMP WHERE DEPTNO=30 AND SAL>2518.75; --整合 ：自下而上整合，将值用代码代替，直到第一步 SELECT * FROM EMP WHERE DEPTNO=30 AND SAL>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=20); 2.查询员工表中薪资大于部门平均薪资最大值的员工的员工人数 --方法一 --分析题目，得到已知条件(题目中给的能通过确切的值得到的数据) 部门平均薪资 SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO; --用已知的数据替换题目中的文字描述，一直到得到题目要求的数据为止 --题目为： 查询员工表中薪资大于(数值)最大值的员工的员工人数 查询(数值)最大值 SELECT MAX(AVG_SAL) FROM (SELECT AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO); --题目为： 查询员工表中薪资大于2518.75的员工的员工人数 SELECT COUNT(*) FROM EMP WHERE SAL>2518.75; --整合：自下而上整合代码，将确切的值用代码替换，直到数值替换为已知条件的SQL语句 SELECT COUNT(*) FROM EMP WHERE SAL > (SELECT MAX(AVG_SAL) FROM (SELECT AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO)); --简化 SELECT COUNT(*) FROM EMP WHERE SAL > (SELECT MAX(AVG(SAL)) AVG_SAL FROM EMP GROUP BY DEPTNO); ---方法二： 查询员工表中薪资大于部门平均薪资最大值的员工的员工人数 --分析题目，确定使用的关键字 SELECT FROM WHERE GROUP BY HAVING ORDER BY --将SQL语句的大体结构写出，遇到不确定的值用变量1代替 SELECT COUNT(*) FROM EMP WHERE SAL> 变量1; --将变量1用代码描述， 遇到不确定的值用变量2代替 变量1 = 部门平均薪资最大值 SELECT MAX(平均薪资) FROM (变量2) -- 将变量2用代码描述， 遇到不确定的值用变量3代替 直到代码中没有变量为止 变量2 = 部门平均薪资 SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO; --整合：自上而下整合代码，将变量用代码替换 SELECT COUNT(*) FROM EMP WHERE SAL > (SELECT MAX(AVG_SAL) FROM (SELECT AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO)); ---简化 SELECT COUNT(*) FROM EMP WHERE SAL > (SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO); 十.重点 1. SELECT 后只能跟单行单列的子查询语句 FROM后：单行单列 单行多列 多行单列 多行多列 WHERE后：单行单列 单行多列 多行单列 多行多列 GROUP BY后不允许出现子查询语句 HAVING后：单行单列 单行多列 多行单列 多行多列 ORDER BY后：只能加单行单列的子查询语句 2.报错 值过多：往往是子查询返回的列数和主查询语句中提供的列数不一致 单行子查询返回多个行:往往是子查询返回的行数和主查询语句中提供的行数不一致 3.子查询的做题步骤(会做子查询的习题) !!!!!!!
• 排序查询
  • /*扩展、伪列*/ 1.ROWID --定义 用户在建表时自动生成的一列用于保存数据的物理地址的数据，本身不存在与表中，查询时需要单独查询 该列由18位字符组成的字符串 --特点 不重复，可以用于数据去重(子查询) 提高查询效率(增加一步获取ROWID值的过程) 示例： 查询emp表中全部数据以及对应的ROWID值 SELECT * FROM EMP; SELECT EMP.*,ROWID FROM EMP; 查询AAAUOsAAEAAAALOAAA对应的数据 SELECT * FROM EMP WHERE ROWID='AAAUOsAAEAAAALOAAA'; 2.ROWNUM --定义 根据查询结果生成的一组由1开始以1递增的数组 --特点 ROWNUM作为条件使用时，不能大于等于一个非1的数 可以用于表中数据排序 示例： 查询emp表的数据以及对应的ROWNUM值 SELECT EMP.*,ROWNUM FROM EMP; SELECT EMP.*,ROWNUM FROM EMP WHERE DEPTNO=10 ; 查询emp表中前三条数据 SELECT EMP.*,ROWNUM FROM EMP WHERE ROWNUM<=3; SELECT EMP.*,ROWNUM FROM EMP WHERE ROWNUM<=5; SELECT EMP.*,ROWNUM FROM EMP WHERE ROWNUM=2;--空值 SELECT EMP.*,ROWNUM FROM EMP WHERE ROWNUM>2;--空值 SELECT EMP.*,ROWNUM FROM EMP WHERE ROWNUM>=3.5;--空值 /*排序查询*/ 一.语法结构 语法: SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE_NAME (WHERE CONDITION) ORDER BY COLUMN1 ASC/DESC; 说明：ORDER BY + COLUMN1 按照指定的列名进行排序 ASC/DESC 升序/降序 分析： 1.ORDER BY +COLUMN1 按照指定的列名进行排序 2.ASC 升序排列 DESC 降序排列 3.简单查询+(条件)+排序关键字 + 排序列 4.排序时值相同的排列在一起 5.ASC升序排列时可以省略不写，默认升序排列 6.排序列和排序方式为一组 示例： 查询emp表中的数据，将表中的数据按照工资列升序排列 SELECT * FROM EMP ORDER BY SAL ASC; SELECT * FROM EMP ORDER BY SAL ; 二.排序列的种类 1.单列排序 示例： 查询emp表中的数据，将表中的数据按照工资列升序排列 SELECT * FROM EMP ORDER BY SAL ASC; 2.多列排序 示例： 查询emp表中的数据，将数据按照部门编号升序排列，按照工资降序排列 SELECT * FROM EMP ORDER BY DEPTNO ASC ORDER BY SAL DESC;--报错 SELECT * FROM EMP ORDER BY DEPTNO ASC,SAL DESC; 注意： 1).一个SELECT语句(SELECT FROM WHERE GROUP BY HAVING ORDER BY )六组关键字只能出现一个; 2).排序规则：先按照第一个列排序，当第一个列的值相同时按照第二个列排序.....直到所有的排序列均排序 3.函数运算 示例： 查询emp表中的员工编号，姓名，工资，以及日薪(保留两位小数) 并将表中的数据按照日薪升序排序 SELECT EMPNO,ENAME,SAL,ROUND(SAL/30,2) FROM EMP ORDER BY SAL/30; SELECT EMPNO,ENAME,SAL,ROUND(SAL/30,2) FROM EMP ORDER BY ROUND(SAL/30,2); SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY DEPTNO;--排序列可以是SELECT后未被查询的字段 SELECT DEPTNO,EMPNO,ENAME FROM EMP ORDER BY DEPTNO; SELECT SAL FROM EMP WHERE DEPTNO=20 OR DEPTNO=10 ORDER BY SUM(SAL);--报错 SELECT SUM(SAL) FROM EMP WHERE DEPTNO=20 OR DEPTNO=10 ORDER BY SUM(SAL);--可以执行 4.常量 示例： 查询emp表中部门编号以3开头的员工信息,并将数据按照一个常量升序排列 SELECT * FROM EMP WHERE DEPTNO LIKE '3%' ORDER BY ASC;--缺失表达式 SELECT * FROM EMP WHERE DEPTNO LIKE '3%' ORDER BY 'ENAME' ASC;--能执行 不排序 查询emp表中部门编号以3开头的员工信息,并将数据按照一个常量升序排列 SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE DEPTNO LIKE '3%' ORDER BY 2; SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE DEPTNO LIKE '3%' ORDER BY 3;--按照SELECT后的字段位置排序 SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE DEPTNO LIKE '3%' ORDER BY '2';--不排序 SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE DEPTNO LIKE '3%' ORDER BY 5;--报错 SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE DEPTNO LIKE '3%' ORDER BY '5';---不报错 注意： 1).普通的常量在排序列中，语句能够执行，不排序 2).数字在排序列中，按照该数字对应的SELECT后字段的位置按照该字段排序,数字的值不能超过SELECT后字段的个数 5.别名 示例： 查询EMP表中的姓名，工资，工作，部门编号,并将数据按照别名排序 SELECT ENAME,SAL S,JOB ,DEPTNO FROM EMP ORDER BY S; SELECT ENAME,SAL S,JOB ,DEPTNO FROM EMP WHERE S>1500 ORDER BY S;--报错 S 标识符无效 6.空值 示例： 查询emp表中的姓名 工作 工资 部门编号 奖金列数据，将数据按照奖金升序排列 SELECT ENAME,JOB ,SAL,DEPTNO,COMM FROM EMP ORDER BY COMM; SELECT ENAME,JOB ,SAL,DEPTNO,COMM FROM EMP ORDER BY COMM DESC; 查询EMP表中的数据，将数据按照奖金升序排列，空值排列在第一位 SELECT ENAME,JOB ,SAL,DEPTNO,COMM FROM EMP ORDER BY COMM NULLS FIRST; SELECT ENAME,JOB ,SAL,DEPTNO,COMM FROM EMP ORDER BY COMM DESC NULLS LAST; 注意： 1).空值默认排序时，最大 2).NULLS FIRST->将空值排在第一位 NULLS LAST->将空值排在最后 7.执行顺序 执行顺序：FROM ->WHERE ->SELECT->ORDER BY 书写顺序：SELECT -> FROM ->WHERE ->ORDER BY 示例： rownum示例 SELECT EMP.*,ROWNUM FROM EMP WHERE DEPTNO=30; SELECT EMP.*,ROWNUM FROM EMP WHERE DEPTNO=30 ORDER BY SAL;--ROWNUM列数据为乱序 三.重点 1.掌握排序的书写规范 2.明确ORDER BY 后加的字段的类别（指定的字段名、数字、函数） 3.一个排序列对应着一个排序方式 4.多列排序时，注意数据在排序过程中的比较方式 5.各个关键字的执行顺序---!!!!! 6.ORDER BY +数字，按照SELECT后对应的字段名进行排序，数字的范围必须在SELECT 后所跟列名的 个数范围内 7.别名只在SELECT后起效,where优先于select执行，所以WHERE后不识别别名 8.多列排序时，按照先后顺序排序，如果第一个列值相等，则按照第二个列进行排序..... 9.一个排序列 匹配 一个排序方式 ，两个排序列匹配一个排序方式相当于省略ASC
• 函数补充
  • /*函数补充*/ 1.数据类型 --概念 限制表中列存储的数据，根据不同的定义存储不同的数据 CREATE TABLE TABLE_NAME AS SQL语句;--赋值一张表：表中的字段和数据与SQL语句查询结果一样 CREATE TABLE EMP1 AS SELECT ENAME,JOB FROM EMP WHERE 1=0; --分类 字符型数据: 0~9 A~Z a~z 特殊字符 汉字... VARCHAR2(N):表示该列只能存储字符型数据，存储的数据的最大长度为N 长度可变，范围：1~4000 CHAR(N):表示该列只能存储字符型数据，存储的数据的最大长度为N 长度确定 范围：1~2000 数值型数据: 0~9 NUMBER(参数1[,参数2])：限制该列为数值型数据，只能存储数字 参数1表明：该列数据最大长度为参数1的值，包含参数2个小数,包含参数1-参数2个整数 日期型数据: 日期 DATE: 指明该列只能存储日期型的数据 2.去重函数 DISTINCT: 将查询结果中重复的数据去掉，只保留一个数据 示例： 查询emp表中的工种 SELECT JOB FROM EMP; SELECT DISTINCT JOB FROM EMP; SELECT DISTINCT JOB,DISTINCT SAL FROM EMP;--报错 缺失表达式 SELECT DISTINCT JOB ,SAL FROM EMP; --注意： 1).DISTNCT 只能写在SELECT后，要查询的字段之前，只有一个 2).多列去重时，当且仅当多列数据完全重复时才会去重 3.去空值函数 1).NVL(参数1,值1)：判断参数一的值是否为空，为空将该函数的值赋为值1，不为空将该函数的值赋为参数一的值 2).NVL2(参数1,值1,值2)：判断参数一的值是否为空，不为空将该函数的值赋为值1，为空将该函数的值赋为值2 --参数1：要分析判断是否为空的列 值：判断是否为空后给函数赋予的值 示例： SELECT COMM FROM EMP; SELECT COMM,NVL(COMM,0) FROM EMP; SELECT COMM,NVL(COMM,0),NVL2(COMM,1,0) FROM EMP; 4.大小写函数 UPPER(STR)/LOWER(STR):将括号内的字符串全部大写、小写 示例： 将ASDASD全部小写 SELECT LOWER('ASDASD') FROM DUAL; SELECT UPPER('asdasd') FROM DUAL; 5.判断函数 1).CASE WHEN 条件 THEN 值1 END;--判断当符合条件时，该函数返回值1 结束 示例： 将30号部门的员工标记为销售人员 SELECT DEPTNO,CASE WHEN DEPTNO=30 THEN '销售人员' END FROM EMP; 2).CASE WHEN 条件 THEN 值1 ELSE 值2 END;--判断当符合条件时，该函数返回值1 不符合条件时返回值2 示例： 将30号部门的员工标记为销售人员 其他部门的员工标记为普通员工 SELECT DEPTNO, CASE WHEN DEPTNO = 30 THEN '销售人员' ELSE '普通员工' END FROM EMP; 3).CASE WHEN 条件1 THEN 值1 WHEN 条件2 THEN 值2 WHEN 条件3 THEN 值3 .... END; --判断当符合条件1时，该函数返回值1 不符合条件1符合条件2时返回值2 不符合条件1，2符合条件3时返回值3...结束 示例： 将30号部门的员工标记为销售人员 20部门的员工标记为研究员工 其他部门的员工标记为普通员工 SELECT DEPTNO, CASE WHEN DEPTNO = 30 THEN '销售人员' WHEN DEPTNO=20 THEN '研究人员' END FROM EMP; SELECT DEPTNO, CASE WHEN DEPTNO = 30 THEN '销售人员' WHEN DEPTNO=20 THEN '研究人员' ELSE '普通员工' END FROM EMP; 6.数值处理函数 ROUND(参数[，精度])：将参数(数值)中的数据按照精度进行四舍五入 TRUNC(参数[，精度])：将参数(数值)中的数据按照精度进行截取 示例： SELECT ROUND(123.567) FROM DUAL;--124 SELECT ROUND(123.567,0) FROM DUAL;--124 SELECT ROUND(123.567,1) FROM DUAL;--123.6 SELECT ROUND(123.567,2) FROM DUAL;--123.57 SELECT ROUND(123.567,-1) FROM DUAL;--120 SELECT ROUND(153.567,-2) FROM DUAL;--200 SELECT TRUNC(123.567) FROM DUAL;--123 SELECT TRUNC(123.567,0) FROM DUAL;--123 SELECT TRUNC(123.567,1) FROM DUAL;--123.5 SELECT TRUNC(123.567,2) FROM DUAL;--123.56 SELECT TRUNC(123.567,-1) FROM DUAL;--120 SELECT TRUNC(153.567,-2) FROM DUAL;--100 7.分组函数(聚合函数) --将表中的数据按照组别进行统计，返回结果 MIN(COLUMN):将表中的数据作为一组，返回组中该列的最小值 MAX(COLUMN):将表中的数据作为一组，返回组中该列的最大值 SUM(COLUMN):将表中的数据作为一组，返回组中该列的数值和 AVG(COLUMN):将表中的数据作为一组，返回组中该列的平均值 COUNT(*)/COUNT(COLUMN):将表中的数据作为一组，统计表中的数据条数 COUNT(*):统计表中全部的数据条数，计算空值 COUNT(COLUMN):统计表中该列的数据条数，，不计算空值 示例： 统计表中数据的值 SELECT MIN(COMM),MAX(COMM),SUM(COMM),AVG(COMM),COUNT(*),COUNT(COMM) FROM EMP; SELECT COUNT(*),COUNT(1) FROM EMP;--在数据计算中，两者无区别 查询emp1表中的统计值 SELECT MIN(JOB),MAX(JOB),SUM(JOB),AVG(JOB),COUNT(*),COUNT(JOB) FROM EMP1; 统计emp表数据 SELECT MIN(JOB),MAX(JOB) FROM EMP;--字符型数据 不报错 SELECT MIN(HIREDATE),MAX(HIREDATE) FROM EMP;--日期型数据 不报错 SELECT SUM(JOB),AVG(JOB) FROM EMP;--字符型数据 报错 SELECT SUM(HIREDATE),AVG(HIREDATE) FROM EMP;--日期型数据 报错 注意： 1).若表中含有数值，MIN/MAX/SUM/AVG/COUNT(COLUMN) 不计算空值 ,COUNT(*) 统计空值 2).COUNT(*)统计的表中全部的数据，扫描表中所有的列，COUNT(1)只扫描当前的常量列即可 3)若表为空表，MIN/MAX/SUM/AVG返回空值 COUNT(column)、COUNT(*) 返回0 4).MIN/MAX/COUNT能够处理字符型 数值型 日期型数据 SUM/AVG只能处理数值型数据
• 函数1
  • D:\学习资料\课堂\11月15日-课堂内容
  • 转换函数
    • 1. TO_CHAR
      • /*转换函数*/ TO_CHAR 1.1数值转字符 1.1.1无指定格式：单纯的由数值转换为字符 1.1.2有指定格式：将数值转为字符的同时，指定格式 SELECT 123,TO_CHAR(123) FROM DUAL; --前面是数值 后面是字符 --坑 实际： TO_CHAR 这个函数 实际上是对123进行了转换，而非123.00 进行了转换，123.00在转换之前，就被精简成了123 SELECT '123.00',TO_CHAR(123.00) FROM DUAL ; SELECT '123.00',TO_CHAR(123.00)||'.00' FROM DUAL; SELECT TO_CHAR(123,'999.999') FROM DUAL; -- 123.000 SELECT TO_CHAR(123,'99.99') FROM DUAL; --###### 百位上的放不进去，坑安排少了 SELECT TO_CHAR(123.456,'999.99') FROM DUAL; -- 123.46 四舍五入 SELECT TO_CHAR(123,'9999.9999') FROM DUAL; -- 123.0000 SELECT TO_CHAR(123,'000000.0000') FROM DUAL; -- 000123.0000 举例： SELECT TO_CHAR(123.45,'999.99') FROM DUAL; -- 123.45 SELECT TO_CHAR(123.45,'999.999') FROM DUAL; -- 123.450 SELECT TO_CHAR(123.45,'999999.99') FROM DUAL; -- 123.45 SELECT TO_CHAR(123.45,'999.9') FROM DUAL; -- 123.5 SELECT TO_CHAR(123.45,'999') FROM DUAL; -- 123 SELECT TO_CHAR(123.45,'99.99') FROM DUAL; --###### SELECT TO_CHAR(123.45,'222.22') FROM DUAL; --只能是9或0 报错 只认9或0 SELECT TO_CHAR(123.45,'0999.99') FROM DUAL; -- 0123.45 SELECT TO_CHAR(123.45,'099.99') FROM DUAL; -- 123.45 SELECT TO_CHAR(123.45,'0099.99') FROM DUAL; -- 0123.45 SELECT TO_CHAR(12345.67,'99099.99') FROM DUAL; -- 12345.67 SELECT TO_CHAR(123.45,'9900099.99') FROM DUAL; -- 00123.45 SELECT TO_CHAR(123456.789,'999999.999') FROM DUAL; -- 123456.789 SELECT TO_CHAR(1.349999999999999,'9.9') FROM DUAL; -- 1.3 看后面1位 SELECT TO_CHAR(1.391111111111111,'9.9') FROM DUAL; -- 1.4 1.2日期转字符 拓展：获取当前系统日期的方法 SELECT SYSDATE,SYSTIMESTAMP FROM DUAL; --获取当前日期 SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL; SELECT TO_CHAR(SYSDATE,'YYYY-DD-MM mm dd yyyy dddd mmmm') FROM DUAL; 年份： YYYY：四位的年份 YYY,YY,Y：分别表示后三位、两位以及一位的年份 SELECT TO_CHAR(SYSDATE,'YYYY YYY YY Y') FROM DUAL; SELECT TO_CHAR(SYSDATE,'YYYY'),TO_CHAR(SYSDATE,'YYY'),TO_CHAR(SYSDATE,'YY'),TO_CHAR(SYSDATE,'Y') FROM DUAL; SELECT TO_CHAR(SYSDATE,'YYYYYYYYYY') FROM DUAL; --优先找4位，剩下的找4位 凑4位不足的话，凑3位 如果没有3位 凑2位 季度 Q:一年中第几个季度（一位） SELECT TO_CHAR(SYSDATE,'Q') FROM DUAL; --4 第四季度 SELECT HIREDATE,TO_CHAR(HIREDATE,'Q') FROM EMP; 月份： MM:一年中的第几个月（两位） SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL; 星期： WW:一年中的第几个星期，从第一天开始算起，一个七天算作一周，和星期几无关 IW:一年中的第几个星期，星期一为本周第一天，每年末最后一个周不足四天算至下年第一周，足四天将下年本周的剩余时间一同算作本年最后一周 W:一个月中的第几个星期，从第一天开始算起，一个七天算作一周，和星期几无关 SELECT TO_CHAR(TO_DATE('20210115','YYYYMMDD'),'WW') FROM DUAL; SELECT TO_CHAR(TO_DATE('20210815','YYYYMMDD'),'W') FROM DUAL; SELECT TO_CHAR(TO_DATE('20210104','YYYYMMDD'),'IW') FROM DUAL; --2021年第一周 SELECT TO_CHAR(TO_DATE('20191230','YYYYMMDD'),'IW') FROM DUAL; --2020年第一周 天： D:星期中的第几天 --从星期日开始算第一天 DD:月份中的第几天 --最常用的 DDD:年份中的第几天 SELECT TO_CHAR(SYSDATE,'D'),TO_CHAR(SYSDATE,'DD'),TO_CHAR(SYSDATE,'DDD')FROM DUAL; SELECT TO_CHAR(TO_DATE('20210101','YYYYMMDD'),'DDD'), TO_CHAR(TO_DATE('20211231','YYYYMMDD'),'DDD') FROM DUAL; -- dddd 前3个d是年份中的第几天 后1个d是星期中的第几天 小时： HH,HH12:一天中的第几个小时（12小时制） HH24:一天中的第几个小时（24小时制） SELECT TO_CHAR(SYSDATE,'HH12 AM'),TO_CHAR(SYSDATE,'HH24') FROM DUAL; --无论是AM,PM 告诉数据库把上午和下午拿出来 毫秒： FF:毫秒（默认6位） FF3,FF6:FF后跟数字表示毫秒的显示位数 SELECT TO_CHAR(SYSTIMESTAMP,'FF3') FROM DUAL; --显示前3位
    • 2.TO_NUMBER
      • /* TO_NUMBER*/ 将只含数字的字符串转化为数值，可以加负号和小数点 SELECT TO_NUMBER('123') FROM DUAL; SELECT TO_NUMBER('000123') FROM DUAL; --接收数值按照最简形式接收 SELECT TO_CHAR(0123) FROM DUAL; --数值0123 先转化为123 再转化为字符串 SELECT TO_NUMBER('123.456') FROM DUAL; SELECT TO_NUMBER('-123.456') FROM DUAL; SELECT TO_NUMBER('@123.456') FROM DUAL; -- ERROR 无效数字
    • 3.TO_DATE
      • /*TO_DATE*/ TO_DATE(字符串表示的日期,’与前者对应的日期格式’) SELECT TO_DATE('20210712','YYYYMMDD') FROM DUAL; --完整的年月日 不能写不完整的 SELECT TO_DATE('20210712 104634','YYYYMMDD HH24MISS') FROM DUAL; --完整的年月日时分秒 SELECT TO_DATE('2021','YYYY') FROM DUAL; --禁止 SELECT TO_DATE('07','MM') FROM DUAL; --禁止 SELECT TO_DATE('09','MM') FROM DUAL; --禁止 以上三种写法，系统都会自动补充 不要这样去用
  • 字符型函数
    • 1.替换replace
      • 3.替换 REPLACE(STR,S1,S2)： 该函数包括三个参数，STR-待处理的字符串，S1-将被替换掉的部分，S2-替换后的部分。 换句话说：将STR中的S1替换成S2。这其中S1和S2都是视作一个整体，不能单独看其中的某个字符。 SELECT REPLACE('ABCDEF','AB','CD') FROM DUAL; --AB换成CD CDCDEF SELECT REPLACE('ABCDEF','B','b') FROM DUAL; --AbCDEF SELECT REPLACE('ABCDEF','BC','MN') FROM DUAL; --AMNDEF SELECT REPLACE('ABCDEF','CD','XYZ') FROM DUAL; --ABXYZEF SELECT REPLACE('ABCDEF','ABCDEF','W') FROM DUAL;--W SELECT REPLACE('ABCDEFABCDEF','CD','MN') FROM DUAL; --ABMNEFABMNEF 匹配的目标全部被替换到 SELECT ENAME,REPLACE(ENAME,'A','K') FROM EMP; SELECT REPLACE(REPLACE('ABCDE','A','M'),'D','N') FROM DUAL; --MBCNE SELECT REPLACE(ENAME,'SMITH','HTIMS') FROM EMP; --HTIMS SELECT REPLACE('2019/12/04','/','-') FROM DUAL; --2019-12-04 SELECT REPLACE('2019/12/04','/','') FROM DUAL; --20191204 SELECT REPLACE('2019/12/04','/') FROM DUAL; --20191204 SELECT REPLACE('2019/12/04') FROM DUAL; --ERROR 函数没有足够的参数
    • 2.大小写转换函数upper、lower
      • 2.大小写转换函数 UPPER(STR)：将字符串STR中的所有小写字母转换成大写字母 LOWER(STR) ：将字符串中STR中的所有大写字母转换成小写字母 SELECT UPPER('ABCdef'),LOWER('ABCdef') FROM DUAL; SELECT UPPER('A12BCd#@ef'),LOWER('A12BCd#@ef') FROM DUAL; --仅仅针对字符串的字母，其他内容原样保留 SELECT LOWER(CONCAT(ENAME,JOB)) FROM EMP; 多层函数嵌套是，函数处理的顺序：从内到外
    • 3.连接 CONCAT
      • 1.连接 CONCAT(STR1,STR2) --只能是两个参数 SELECT CONCAT('A','B') FROM DUAL; --把A 和B 拼接在一起 SELECT CONCAT('欢迎顾客：',ENAME) FROM EMP; SELECT CONCAT('欢迎顾客：',ENAME,'光临本店') FROM EMP; --不能有三个参数 SELECT CONCAT(ENAME) FROM EMP; -ERROR 一个参数也不行 SELECT CONCAT(CONCAT('欢迎顾客：',ENAME),'光临本店') FROM EMP; SELECT '欢迎顾客：'||ENAME||'光临本店' FROM EMP;
    • 4.去除TRIM
      • 4.去除函数 --去除空格 --常用 去除其他字符 --不常用 TRIM()：去除指定字符串或字段两侧的空格 --常用 LTRIM()：去除指定字符串或字段左侧的空格 --不常用 LEFT RTRIM()：去除指定字符串或字段右侧的空格 --不常用 RIGHT SELECT REPLACE(' AB C ',' ','') FROM DUAL; --空格替换成空 ABC SELECT REPLACE(' A B C ',' ','') FROM DUAL; --REPLACE 去掉所有的空格 都去掉了 4个空格 SELECT TRIM(' A B C ') FROM DUAL; --A B C 去除两侧的空格 SELECT LTRIM(' A B C ') FROM DUAL; --A B C SELECT RTRIM(' A B C ') FROM DUAL; -- A B C SELECT TO_CHAR('123.456','9999.99') FROM DUAL; -- 123.46 左边有空格 SELECT TRIM(TO_CHAR('123.456','9999.99')) FROM DUAL; --123.46 去两侧空格 SELECT * FROM EMP; SELECT TRIM(JOB) FROM EMP; 完整用法： LTRIM(STR[,S])：去除字符串STR中左侧的S，如果S参数省略，则默认去除空格。 -- S 视为个体 RTRIM(STR[,S])：去除字符串STR中右侧的S，如果S参数省略，则默认去除空格。 -- S 视为个体 TRIM([LEADING|TRAILING|BOTH] [S FROM] STR)：去除字符串STR中的S，具体去除左侧/右侧/两侧，根据参数[LEADING/TRAILING/BOTH] 决定，若省略该参数，默认去除两侧； 参数[S FROM] 决定了被去除的内容，若省略则默认去除空格。 SELECT LTRIM('ABCDCBA','A') FROM DUAL; --BCDCBA SELECT LTRIM('AAAAAAA','A') FROM DUAL; --空值 SELECT TRIM(' A B C ') FROM DUAL; --两侧的空格都没有了 SELECT LTRIM(' A B C ') FROM DUAL; --A B C --原理：从左侧/右侧、两侧（取决于什么函数找到第一个非指定去除字符的内容 SELECT RTRIM('ABCDCBA','AB') FROM DUAL; --ABCDC SELECT LTRIM('ABCDCBA','BCA') FROM DUAL; --DCBA SELECT TRIM(LEADING 'A' FROM 'ABCDCBA'), --去左边A TRIM(TRAILING 'A' FROM 'ABCDCBA'), -- 去右边A TRIM(BOTH 'A' FROM 'ABCDCBA') -- 默认 左右两边的A 都去掉 FROM DUAL; -- 去除内容仅能指定一个字符，不能指定字符串 把A改成AB就报错了 SELECT TRIM(' ABCDCBA ') FROM DUAL; 最最常用的去除用法：使用TRIM函数去除两侧的空格 TRIM(STR)
• 模糊查询
  • /*模糊查询*/ 一.定义 在需求不明确或者给定的条件不具体时，使用模糊查询 二.语法结构 SELECT COLUMN1,COLUMN2,COLUMN3... FROM TABLE_NAME WHERE COLUMN1 LIKE ''; 分析： 1.LIKE: 像...一样 2.单引号内为不确定的条件 特殊符号： %:表示在该位置有任意个任意字符 _:表示在该位置有一个任意字符 示例： 查询姓名中是以A开头的员工信息 SELECT * FROM EMP WHERE ENAME LIKE 'A%'; 三.%语句 示例： 查询姓名中是以A开头的员工信息 SELECT * FROM EMP WHERE ENAME LIKE 'A%'; 查询姓名中是以N结尾的员工信息 SELECT * FROM EMP WHERE ENAME LIKE '%N'; SELECT * FROM EMP WHERE ENAME LIKE '%%N'; 查询姓名中是包含E的员工信息 SELECT * FROM EMP WHERE ENAME LIKE '%E%'; 四._语句 示例： 查询第二位是E的员工信息 SELECT * FROM EMP WHERE ENAME LIKE '_E%'; 查询倒数第二位是Q的员工信息 SELECT * FROM EMP WHERE ENAME LIKE '%Q_'; 查询倒数第二位是Q 或者包含E的员工信息 SELECT * FROM EMP WHERE ENAME LIKE '%Q_' OR ENAME LIKE '%E%'; SELECT * FROM EMP WHERE ENAME (LIKE '%Q_' OR LIKE '%E%');--报错 SELECT * FROM EMP WHERE ENAME LIKE ( '%Q_' OR '%E%');--报错 五.转义字符 1.语法结构 SELECT COLUMN1,COLUMN2,COLUMN3... FROM TABLE_NAME WHERE COLUMN1 LIKE '' ESCAPE ''; 分析： 1).ESCAPE ''用于转义字符，声明单引号内的字符是一个转义字符 2).转义字符用于声明条件中该字符后的%/_等字符为普通字符,且只作用于第一个字符 示例： 查询emp表中以_开头的员工信息 SELECT * FROM EMP WHERE ENAME LIKE '_%';--_含有特殊功能 SELECT * FROM EMP WHERE ENAME LIKE '/_%' ESCAPE '/'; 查询表中姓名以王开头的员工信息 SELECT * FROM EMP WHERE ENAME LIKE '王%'; 查询表中姓名等于PEIQI的员工信息 SELECT * FROM EMP WHERE ENAME LIKE 'PEIQI'; 注意： 1.%和_在LIKE语句中使用时，不是作为字符串使用，而是本身的特殊功能 2.多个%表示 含义一样的 3._可以用于限制某位字符的位置 4.比较列 和 LIKE,比较列、LIKE、ESCAPE为一个整体 六.重点 1.记忆条件查询的语法结构 2.AND/OR用于连接多个条件的，AND优先级高于OR 3.BETWEEN 值1 AND 值2 值1<=值2 包含边界值 4.IN用于条件比较时含有多个值时，in后加的是一个数据集合 5.ANY/ALL:大于ANY 大于最小值 小于ANY小于最大值 大于all大于最大值 小于ALL小于最小值 6.模糊查询：比较列 和 LIKE,比较列、LIKE、ESCAPE为一个整体
• 简单查询
  • 一.sql是什么 1.定义 SQL指结构化查询语言，全称是 Structured Query Language。 SQL 可以访问和处理数据库。 SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织） 标准的计算机语言。 2.功能 SQL 面向数据库执行查询 SQL 可从数据库取回数据 SQL 可在数据库中插入新的记录 SQL 可更新数据库中的数据 SQL 可从数据库删除记录 SQL 可创建新数据库 SQL 可在数据库中创建新表 SQL 可在数据库中创建存储过程 SQL 可在数据库中创建视图 SQL 可以设置表、存储过程和视图的权限.... 二.SQL查询数据 简单查询 条件查询 数据类型 排序分组 子查询 表连接 函数 三.简单查询 1.语法结构 SELECT COLUMN1,COLUMN2,COLUMN3... FROM TABLE_NAME; 分析： 1).SELECT/FROM 查询语句的关键字，不能更改 2).列名放于SELECT后 表名放于FROM 后，内容随便写(表名存在于数据库中，列名存在于表中) 3).SELECT后跟的要查询的内容(列名 常量 函数 查询语句...) 4).FROM 后跟的往往是数据来源(表名 查询语句 数据集合...) 5).英文格式下的分号表示一条SQL语句的结束，逗号用于分割查询中的多个列的内容 示例： 查询emp表中的empno列数据 SELECT EMPNO FROM EMP; SELECT EMPNO FROM EMP；--无效字符 SELECT EMPNO,ENAME FROM EMP; 查询emp表中的员工编号，员工姓名，员工工资列信息 SELECT EMPNO,ENAME,SAL FROM EMP; 2.扩展 --* 号 * 通配符，用于查询语句中表示查询FROM后数据源中的所有数据 示例： 查询emp表的信息 SELECT * FROM EMP; 查询dept表的数据 SELECT * FROM DEPT; 查询工资等级表 SELECT * FROM SALGRADE; --字符串 字符： 0~9 A~Z a~z 特殊字符 汉字 字符串：由字符组成的一串数据,字符串在查询语句中使用时需要加英文格式下的单引号声明 示例： SELECT ASD FROM EMP;--ASD 表示符无效 SELECT 'ASD' FROM EMP; --查询常量 常量：一组确定的数值 包含 数值 字符 日期... 往往写在SELECT 后用于查询常量 在查询常量会自动生成一列名为常量名，置为常量的数据，数据条数和FROM 后的表中的数据条数一致 示例： 在EMP表中查询一个常量123 SELECT EMPNO,123 FROM EMP; SELECT DEPTNO ,123 FROM DEPT; SELECT DEPTNO ,123,'123' FROM DEPT; 变量：一组不确定的值，往往是代码返回的数值 --运算 加减乘除适用于常量 字段之间 示例： 查询一个运算 SELECT 120000/5 FROM EMP; SELECT 120000+5 FROM EMP; SELECT 120000-5 FROM EMP; SELECT 120000*5 FROM EMP; SELECT SAL,SAL/30 FROM EMP; ---DUAL表 DUAL表：一张伪表，本身含有一行一列置为X的数据 往往题目中未指明表名、运算的验算时使用 示例： SELECT * FROM DUAL; SELECT 123*456 FROM DUAL; SELECT 123*456 FROM EMP; 3.别名 列别名 语法： SELECT COLUMN1 [AS] 别名1 ,COLUMN2 [AS] 别名2 ... FROM TABLE_NAME; 分析： 1).列名 AS 别名->用别名代替表中的列名显示 2). AS可以省略，不影响显示 3). 起别名后列名为别名 值依然为该列的数据 示例： 查询emp表中的姓名列，并以姓名显示 SELECT ENAME,ENAME AS 姓名 FROM EMP; SELECT ENAME,ENAME,ENAME,ENAME FROM EMP; SELECT ENAME,ENAME 姓名 FROM EMP; SELECT ENAME,ENAME AS 123 FROM EMP;--报错 SELECT ENAME,ENAME AS 姓_名 FROM EMP;--正常执行 SELECT ENAME,ENAME AS 姓1名 FROM EMP;--正常执行 SELECT ENAME,ENAME AS _姓名 FROM EMP;--报错 SELECT ENAME,ENAME AS 1姓名 FROM EMP;--报错 SELECT ENAME,ENAME AS "_姓名" FROM EMP;--正常执行 SELECT ENAME,ENAME AS "1姓名" FROM EMP;--正常执行 SELECT ENAME AS ASD FROM EMP;--正常执行 SELECT ENAME AS 'ASD' FROM EMP;--报错 SELECT ENAME AS 姓(名 FROM EMP;--报错 SELECT ENAME AS "姓(名" FROM EMP;--报错 表别名 语法： SELECT COLUMN1 ,COLUMN2... FROM TABLE_NAME 别名; 分析： 1).表名 别名->用别名代替表名使用 2).用于SELECT后既含有* 还含有其他列时 示例： 查询emp表的数据 SELECT * FROM EMP E; SELECT E.*,ENAME FROM EMP E; SELECT EMP.*,ENAME FROM EMP; SELECT E.*,ENAME FROM EMP _员工信息表;--无效字符 SELECT "_员工信息表".*,ENAME FROM EMP "_员工信息表";--正常执行 注意：起名规则 1).英文字母、汉字可以直接命名别名 2).若别名以数字或下划线开头,需要添加英文格式下的双引号声明 3).若别名中含有数字或下划线，并不以其开头，可以直接命名 4).若别名中包含[ { (等特殊字符时，需要添加英文格式下的双引号声明 4.拼接符 数据1||数据2： 将数据1 的数据和数据2 的数据拼接到一起显示 示例： 数据和数据拼接 SELECT 123||'ASD' FROM DUAL; 数据和字段 SELECT 123||ENAME FROM EMP; 字段和字段拼接 SELECT EMPNO||ENAME FROM EMP; SELECT '员工的编号为：'||EMPNO||'员工的姓名为：'||ENAME FROM EMP; 四.重点 1.掌握简单查询的语法结构 2.掌握字符串 通配符* 常量 DUAL的使用 3.掌握别名的命名语法和起名规则 4.会用拼接符即可