他的最新文章
他的热门文章
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)他的最新文章
他的热门文章
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)时间： 13:23:03
&&&& 阅读：428
&&&& 评论：
&&&& 收藏：0
标签：&&&&&&&&&&&&&&&&&&&&&&&&&&&1. SQL基础查询1.1 基本查询语句? SELECT用于指定要查询的列，如果要查询所有列，在SELECT后面使用“*”号表示，如果查询特定的列，可以直接后面跟指定的列名，列名之间用”,”逗号隔开。演示：查询所有列SELECT * FROM演示：查询具体列SELECT ename,sal FROM? FROM表示从哪张表查询内容，案例如上。? AS用于给列名起别名，或者表示计算结果的含义，也可以不加AS关键字。演示：查看员工一年薪资并起别名为SalarySELECT empno AS id,sal*12 "Salary" FROM? WHERE表示条件查询，当查询条件中和数字比较时，可以使用单引号引起也可以不用。当和字符及日期类型的数据比较，则必须用单引号引起。演示：和数字比较SELECT ename,sal FROM emp WHERE sal &1000; --不加单引号SELECT ename,sal FROM emp WHERE sal&‘1000‘; --加单引号演示：和字符及日期比较SELECT ename,sal FROM emp WHERE ename=‘MARTIN‘;演示：和日期比较SELECT ename,hiredate FROM emp WHERE hiredate=‘17-12月-80‘;1.2 查询条件? &，&，&=，&=，!=，&&，=在WHERE子句中，可以使用比较运算符来做查询。!=和&&都表示不等于，一般&&比较常用。演示：查询部门号不是10的员工SELECT ename,deptno FROM emp WHERE deptno!=10;SELECT ename,deptno FROM emp WHERE deptno&&10;? AND，ORl AND表示且l OR表示或演示：查询薪资大于1000并且职位是CLERK的员工SELECT ename,sal,job FROM emp WHERE sal&1000 AND job=‘CLERK‘;演示：查询薪资大于1000或职位是CLERK的员工SELECT ename,sal,job FROM emp WHERE sal&1000 OR job=‘CLERK‘;? LIKE模糊查询，需要借助两个通配符：l %：表示0到多个字符l _：表示单个字符演示：查询姓名第二个字符是A的员工信息SELECT * FROM emp WHERE ename LIKE ‘_A%‘;? IN，NOT IN在WHERE子句中可以用比较操作符IN(list)来取出符合列表范围中的数据，其中list表示值列表，当列或表达式匹配于列表中任何一个值时，条件为TRUE，该条记录被显示出来。NOT IN(list)表示取出不符合此列表中的数据记录。演示：查询职位是MANAGE和CLERK的员工SELECT ename,sal,job FROM emp WHERE job IN (‘MANAGER‘,‘CLERK‘);演示：查询部门不是10和20的员工SELECT ename,sal,deptno FROM emp WHERE deptno NOT IN (10,20);? BETWEEN...AND...用来查询符合某个范围条件的数据，通常用在数字类型的数据范围上，对字符类型和日期类型也同样适用。演示：查询薪资在内的员工SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000;? IS NULL，IS NOT NULLNULL空值比较的时候，不能使用“=”号，需要使用IS NULL或IS NOT NULL。演示：查询绩效为NULL的员工SELECT ename,comm FROM emp WHERE comm IS NULL;? ANY，ALL在比较运算符中，ANY表示任意一个满足条件，ALL表示全部满足条件，需要配合运算符来使用。l &ANY：大于最小l &ANY：小于最大l &ALL：大于最大l &ALL：小于最小演示：查询薪资大于的员工（即薪资大于1000的）SELECT ename,sal FROM emp WHERE sal&ANY();演示：查询薪资小于的员工（即薪资小于1500的）SELECT ename,sal FROM emp WHERE sal&ANY();演示：查询薪资大于的员工（即薪资大于1500的）SELECT ename,sal FROM emp WHERE sal&ALL();演示：查询薪资小于的员工（即薪资小于1000的）SELECT ename,sal FROM emp WHERE sal&ALL();演示：查询薪资比职位是“SALEMAN“的人高的员工信息，即比任意一个SALEMAN薪资高的员工SELECT ename,sal FROM emp WHERE sal&ANY(SELECT sal FROM emp WHERE job=‘SALESMAN‘);? 表达式和函数在查询时，可以使用算术表达式(+-*/)，表达式符合四则运算的默认优先级，改变优先级可以使用括号。算术运算主要针对数字类型的数据，对日期也有效，表示在一个日期值上加或减多少天。查询条件中使用字符串函数UPPER，将条件的字符串转换大写后再比较。演示：查询年薪大于10万并且姓名是WARD的员工信息SELECT * FROM emp WHERE sal*12&10000 AND ename=UPPER(‘ward‘);? DISTINCT用来过滤重复，数据表中有可能存储相同数据的行，当执行查询操作时，默认情况会显示所有行，不管查询结果是否有重复数据。当重复数据没有实际意义，经常需要去掉重复值。演示：查询员工的部门编码有哪些SELECT DISTINCT deptno FROM演示：查询员工的部门编码和职位有哪些SELECT DISTINCT deptno,job FROM提示：这样表示deptno和job组合起来的不重复。1.3 排序? ORDER BY对查询出的数据按一定规则进行排序操作，默认按升序排列。需要注意的是ORDER BY必须出现在SELECT中的最后一个子句。演示：对职员薪资进行排序SELECT ename,sal FROM emp ORDER BY? ASC，DESCl ASC：按升序排列l DESC：按降序排列注意：NULL值视为最大，如果不写ASC或DESC则默认为ASC。演示：员工绩效降序排列SELECT ename,comm FROM emp ORDER BY? 多列排序当多列作为排序标准时，首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推。多列排序不管正序还是倒序，每个列都需要单独设置排序方式。演示：按部门绩效升序和薪资降序排序SELECT ename,comm,sal FROM emp ORDER BY comm ASC,sal DESC;演示：按comm和sal默认排序SELECT ename,comm,sal FROM emp ORDER BY comm,1.4 聚合函数查询时需要做一些数据统计，如查询职员表中各部门职员的平均薪水，员工人数等。当需要统计的数据并不能在职员表里直观列出，而是需要根据现有的数据计算得到结果，这种功能可以使用聚合函数来实现，即：将表的全部数据划分为几组数据，每组数据统计出一个结果。因为是多行数据参与运算返回一行结果，也称为分组函数、多行函数、集合函数。关键字如下：l GROUP BY：按什么分组l HAVING：进一步限制分组结果? MAX，MIN用来取得列或表达式的最大、最小值，可以用来统计任何数据类型，包括数字、字符和日期。演示：获取10部门中工资最高和最低是多少SELECT MAX(sal),MIN(sal) FROM emp WHERE deptno=10;演示：获取入职日期最晚和最早的SELECT MAX(hiredate),MIN(hiredate) FROM? AVG，SUMl AVG：统计列或表达式的平均值l SUM：统计列或表达式的和注意：这两个函数只能操作数字类型，并忽略NULL值。演示：获取平均工资和工资总和SELECT AVG(sal),SUM(sal) FROM? COUNT用来计算表中的记录条数，同样忽略NULL值。演示：获取一共有多少员工SELECT COUNT(*) AS total_emp FROM? NULL值处理聚合函数忽略NULL值，即当emp表中comm列有NULL值就被忽略。演示：列出平均绩效，忽略NULL值SELECT AVG(comm),SUM(comm),COUNT(sal) FROM可以看出，这个平均绩效是不对的，因为NULL值被忽略了。演示：列出平均绩效不忽略NULL值SELECT AVG(NVL(comm,0)),SUM(comm),COUNT(sal) FROM1.5 分组? GROUP BY用于分组。演示：查询每个部门的员工数SELECT COUNT(*),deptno FROM emp GROUP BY演示：查询同部门同职位的员工数SELECT COUNT(*),deptno,job FROM emp GROUP BY deptno,演示：查询每个部门的平均工资SELECT AVG(sal),deptno FROM emp GROUP BY演示：查询平均工资高于2000的部门SELECT deptno FROM emp WHERE AVG(sal)&2000 GROUP BY --错误原因：因为WHERE中是不能使用聚合函数作为过滤条件的，原因在于过滤的时机不对，WHERE是在检索表中数据时逐行进行过滤的，将满足条件的记录查询出来生成结果集，聚合函数是用来统计结果集中的数据，所以要先将数据查询出来才可以进行，为此用聚合函数的结果是在WHERE之后进行的，这就需要用到下面的HAVING子句。? HAVING用来对分组后的结果进一步限制，必须跟在GROUP BY后面，不能单独存在。演示：查询平均工资高于2000的部门SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)&2000;演示：查看每个部门的最高薪资且将大于4000的显示出来SELECT MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)&4000;? 查询语句执行顺序当一条查询语句中包含所有的子句，执行的顺序依照下列子句的次序：l FROM子句：执行顺序从后往前，从右往左。数据量较少的表尽量放在后面l WHERE子句：执行顺序从上往下，从右往左。将能过滤掉最大数量记录的条件写在WHERE子句的最右l GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤l HAVING子句：消耗资源，尽量避免使用，HAVING会在检索出所有记录之后才对结果集进行过滤，需要排序等操作l SELECT子句：少用*号，尽量取字段名。Oracle在解析的过程中，通过查询数据字典将*号依次转换成所有的列名，消耗时间。l ORDER BY子句：执行顺序从左往右，消耗资源2. SQL关联查询2.1 关联基础实际应用中需要的数据经常会需要查询两个或两个以上的表，这种查询两个或两个以上数据表或视图的查询叫做连接查询，连接查询通常建立在存在相互关系的父子表之间。演示：查询在NEW YORK工作的员工SELECT ename,loc FROM emp,dept WHERE emp.deptno=dept.deptno AND loc=‘NEW YORK‘;? 笛卡尔积笛卡尔积指做关联操作的每个表的每一行都和其它表的每一行做组合，即缺少关联查询条件的时候产生。演示：笛卡尔积SELECT ename,loc FROM emp,? 等值相连等值连接是连接查询中最常见的一种，通常是在有主键关联关系的表间建立，将连接条件设定为有关系的列，使用等号“=”连接相关的表。演示：查看部门号是10并且在SELECT ename,deptno,loc FROM emp,dept WHERE emp.deptno=dept.deptno AND loc=‘NEW YORK‘; --错误原因：未明确定义列，即deptno不明确，因为两个表中都有deptno列，所以关联查询一般需要使用到别名。SELECT e.ename,e.deptno,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND d.loc=‘NEW YORK‘;2.2 关联查询? 内连接内连接返回两个关联表中所有满足连接条件的记录。演示：查询员工的姓名和所在部门的名字SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.或者写成下面形式：SELECT e.ename,d.dname FROM emp e JOIN dept d ON(e.deptno=d.deptno);? 外连接外连接不仅返回满足连接条件的记录，还将返回不满足连接条件的记录。外连接分为：左外连接、右外连接、全外连接。l 左外连接：左外连接：以 JOIN 左侧表作为驱动表（所有数据都要显示的表），那么当驱动表中某条记录不满足连接条件时则右侧表中的字段全部取值为NULL。l 右外连接：以 JOIN 右侧表作为驱动表（所有数据都要显示的表），那么当驱动表中某条记录不满足连接条件时则右侧表中的字段全部取值为NULL。l 全外连接：以 JOIN 两侧表作为驱动表（所有数据都要显示的表），那么当驱动表中某条记录不满足连接条件时则右侧表中的字段全部取值为NULL，即左外连接与右外连接的总和。演示：左外连接SELECT e.ename,d.dname FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.或者SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno(+); --左外连接，+号在哪，那边写NULL演示：右外连接SELECT e.ename,d.dname FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.或者：SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno(+)=d. --右外连接这里有个NULL值是因为没有员工是40号部门的。演示：全外连接SELECT e.ename,d.dname FROM emp e FULL OUTER JOIN dept d ON e.deptno=d.? 自连接自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自于单表中的多个列，表中的列参照同一个表中的其它列的情况称作自参照表。自连接是通过将表用别名虚拟成两个表的方式实现，可以是等值或不等值连接。演示：查询每个员工以及其上司的名字SELECT e1.ename,e2.ename FROM emp e1,emp e2 WHERE e1.mgr=e2.empno(+);或者：SELECT e1.ename,e2.ename FROM emp e1 JOIN emp e2 ON e1.mgr=e2.empno(+);3. SQL高级查询3.1 子查询子查询：嵌入在其它SQL语句中的SELECT语句，大部分情况出现在WHERE子句中。子查找嵌入的语句称作主查询或父查询。主查询可以是SELECT语句，也可以是其它类型的语句，如DML或DDL语句。根据返回结构的不同，子查询可以分为单行子查询、多行子查询和多列子查询。? 子查询在WHERE子句中案例：查询和SCOTT同职位的员工SELECT ename,job FROM emp WHERE job=(SELECT job FROM emp WHERE ename=‘SCOTT‘);案例：查询薪水比整个机构平均薪水高的员工SELECT ename,sal FROM emp WHERE sal&(SELECT AVG(sal) FROM emp);注意：如果子查询返回多行，主查询中要使用多行比较符，包括IN、ALL、ANY。其中ALL和ANY不能单独使用，需要配合单行比较操作符&、&=、&、&=一起使用，如下案例。案例：查询部门中有SALESMAN但职位不是SALESMAN的员工信息SELECT empno,ename,job,deptno FROM empWHERE deptno IN(SELECT deptno FROM emp WHERE job=‘SALESMAN‘)AND job&&‘SALESMAN‘;注意：在子查询中需要引用到主查询的字段数据，使用EXISTS关键字。EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE。如果查询没有结果，则返回FALSE，如下案例。案例：查询哪些有员工的部门信息SELECT deptno,dname FROM dept d WHERE EXISTS (SELECT * FROM emp e WHERE d.deptno=e.deptno );? 子查询在HAVING部分案例：查询最低薪水高于部门30的最低薪水的部门信息SELECT deptno,MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal)&(SELECT MIN(sal) FROM emp WHERE deptno=30);? 子查询在FROM部分在查询语句中，FROM子句用来指定要查询的表。如果在一个子查询的结果中继续查询，则子查询出现在FROM子句中，这个子查询也称作行内视图或匿名视图。这时把子查询当做视图对待，但视图没有名字，只能在当前SQL语句中有效。案例：查询薪水比本部门平均薪水高的员工信息SELECT e.deptno,e.ename,e.salFROM emp e,(SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) xWHERE e.deptno=x.deptno AND e.sal&x.avg_sal ORDER BY e.? 子查询在SELECT部分把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式，使用更灵活。案例：SELECT e.ename,e.sal,e.deptno,(SELECT d.deptno FROM dept d WHERE d.deptno=e.deptno) deptno FROM3.2 分页查询? ROWNUMROWNUM被称为伪列，用于返回标识行数据顺序的数字。案例：SELECT ROWNUM,empno,ename,sal FROM注意：ROWNUM只能从1开始计数，不能从结果集中直接截取，如下案例。案例：查询ROWNUM大于3的列SELECT ROWNUM,empno,ename,sal FROM emp WHERE ROWNUM&3;因为从1开始，所以查询大于3或者等于3的列都没有内容，但是可以查询小于3的列。SELECT ROWNUM,empno,ename,sal FROM emp WHERE ROWNUM&3;如果需要利用ROWNUM截取结果集中的部分数据，需要用到行内视图。案例：查询8-10列的信息SELECT * FROM (SELECT ROWNUM rn,e.*FROM emp e) WHERE rn BETWEEN 8 AND 10;? 使用子查询进行分页分页策略是指每次只读取一页的数据。每次换页，取下一页的数据。在Oracle中利用ROWNUM功能来分页。分页查询公式：PageN:(n-1)*pageSize+1至n*pageSize? 分页与ORDER BY案例：按薪水倒序排列，取出结果集中第8到第10条记录SELECT * FROM (SELECT ROWNUM rn,t.* FROM (SELECT empno,ename,sal FROM emp ORDER BY sal DESC)
t )WHERE rn BETWEEN 8 AND 10;3.3 DECODE函数? DECODE函数基本语法DECODE函数语法：DECODE (expr, search1, result1[, search2, result2…][, default])用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值。default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。案例：查询职员表，根据职员的职位计算奖励金额，当职位分别是’MANAGER’、’ANALYST’、’SALESMAN’时，奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值SELECT ename,job,sal,DECODE(job,‘MANAGER‘,sal*1.2,‘ANALYST‘,sal*1.1,‘SALESMAN‘,sal*1.5,sal) bonus FROM和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。SELECT ename,job,sal,CASE job WHEN ‘MANAGER‘ THEN sal*1.2 WHEN ‘ANALYST‘ THEN sal*1.1 WHEN ‘SALESMAN‘ THEN sal*1.5 ELSE sal END bonus FROM? DECODE函数在分组查询中的应用OECODE函数可以按字段内容分组。案例：计算职位的人数，analyst/manager职位属于vip，其余是普通员工operation,这种功能无法用GROUP BY简单实现。用decode的实现方式SELECT DECODE(job,‘ANALYST‘,‘VIP‘,‘MANAGER‘,‘VIP‘,‘OPERATION‘) job,COUNT(1) job_cntFROM emp GROUP BY DECODE(job,‘ANALYST‘,‘VIP‘,‘MANAGER‘,‘VIP‘,‘OPERATION‘);DECODE函数也可以按字段内容排列。案例：Dept表中按”研发部”、“市场部”、“销售部”排序，用普通的select语句，无法按照字面数据排序SELECT deptno,dname,loc FROM dept ORDER BY DECODE(dname,‘研发部‘,1,‘市场部‘,2,‘销售部‘,3),3.4 排序函数? ROW_NUMBER语法：ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2);表示根据col1分组，在分组内部根据col2排序。此此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。ROWNUM是伪列， ROW_NUMBER功能更强，可以直接从结果集中取出子集。案例：按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码SELECT deptno,ename,empno,ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY empno) AS emp_idFROM? RANK语法：RANK() OVER(PARTITION BY col1 ORDER BY col2);表示根据col1分组，在分组内部根据col2排名，相同的数据返回相同排名。特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位排名是第四名。和ROW_NUMBER的区别是结果有重复值，而ROW_NUMBER没有。案例：按照部门编码分组，同组内按薪水倒序排序，相同薪水则按奖金数正序排序，并给予组内等级，用Rank_ID表示SELECT deptno,ename,sal comm,RANK() OVER (PARTITION BY deptnoORDER BY sal DESC,comm) "Rank_ID"FROM? DENSE_RANK语法：DENSE_RANK() OVER(PARTITION BY col1 ORDER BY col2);表示根据col1分组，在分组内部根据col排名，相同的数据返回相同排名。特点是连续排序，如果有并列第二，下一个排序将是3，这一点是和RANK的不同，RANK是跳跃排序。案例：关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，列出员工的部门名字、姓名、薪水SELECT d.dname,e.ename,e.sal,DENSE_RANK()OVER (PARTITION BY e.deptno ORDER BY e.sal)AS drankFROM emp e join dept d ON e.deptno = d.3.5 高级分组函数ROLLUP、CUBE、GROUPING SETS运算符是GROUP BY子句的扩展，可以生成与使用UNION ALL来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询。语法：l GROUP BY ROLLUP(a,b,c)l GROUP BY CUBE(a,b,c)l GROUP BY GROUPING SETS(a,b,c)? ROLLUP案例：SELECT ename,deptno,comm,SUM(sal) FROM emp GROUP BY ROLLUP(ename,deptno,comm);等价于：SELECT ename,deptno,comm,SUM(sal) FROM emp GROUP BY ename,deptno,commUNION ALLSELECT ename,deptno,NULL,SUM(sal) FROM emp GROUP BY ename,deptnoUNION ALLSELECT ename,NULL,NULL,SUM(sal) FROM emp GROUP BY enameUNION ALLSELECT NULL,NULL,NULL,SUM(sal) FROM对ROLLUP的列从右到左以一次少一列的方式进行分组知道所有列都去掉后的分组（也就是全表分组）。对于n个参数的ROLLUP有n+1次分组。? CUBE语法:GROUP BY CUBE(a,b,c)对于CUBE的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合，对于n个参数的CUBE有2的n次方分组。如果GROUP BY CUBE(a,b,c)，首先对(a,b,c)进行GROUP BY，然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，最后对全表进行GROUP BY操作，所以一共是2^3=8次分组。SELECT a,b,c,SUM(d) FROM test GROUP BY CUBE(a,b,c);等价于：SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,cUNION ALLSELECT a,b,NULL,SUM(d) FROM test GROUP BY a,bUNION ALLSELECT a,NULL,c,SUM(d) FROM test GROUP BY a,cUNION ALLSELECT a,NULL,NULL,SUM(d) FROM test GROUP BY aUNION ALLSELECT NULL,b,c,SUM(d) FROM test GROUP BY b,cUNION ALLSELECT NULL,b,NULL,SUM(d) FROM test GROUP BY bUNION ALLSELECT NULL,NULL,c,SUM(d) FROM test GROUP BY cUNION ALLSELECT NULL,NULL,NULL,SUM(d) FROM虽说运行结果等价，但其内部运行机制不同，其效率远高于UNION ALL。? GROUPING SETSGROUPING SETS运算符可以生成与使用单个GROUP BY、ROLLUP或CUBE运算符所生成的结果集相同的结果集，但是使用更灵活。如果不需要获得由完备的 ROLLUP 或 CUBE 运算符生成的全部分组，则可以使用 GROUPING SETS 仅指定所需的分组。GROUPING SETS 列表可以包含重复的分组。分组方式：l 使用GROUP BY GROUPING SETS(a,b,c)，则对(a),(b),(c)进行GROUP BYl 使用GROUP BY GROUPING SETS((a,b),c), 则对(a,b),(c)进行GROUP BYl GROUPING BY GROUPING SET(a,a) , 则对(a)进行2次GROUP BY, GROUPING SETS的参数允许重复3.6 集合操作? UNION、UNION ALL用来获取两个或两个以上结果集的并集。l UNION操作符会自动去掉合并后的重复记录l UNION ALL返回两个结果集中的所有行，包括重复的行l UNION操作符对查询结果排序，UNION ALL不排序案例：合并职位是MANAGER的员工和薪水大于2500的员工合集，使用UNIONSELECT ename,job,sal FROM emp WHERE job=‘MANAGER‘UNIONSELECT ename,job,sal FROM empWHERE sal &2500;案例：使用UNION ALL实现上面案例SELECT ename,job,sal FROM emp WHERE job=‘MANAGER‘UNION ALLSELECT ename,job,sal FROM empWHERE sal &2500;? INTERSECTINTERSECT函数获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出。使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。案例：显示职位是MANAGER的员工和薪水大于2500的员工交集SELECT ename,job,sal FROM emp WHERE job=‘MANAGER‘INTERSECTSELECT ename,job,sal FROM empWHERE sal &2500;? MINUSMINUS函数获取两个结果集的差集，只有在第一个结果集中存在，在第二个结果中不存在的数据，才能够被显示出来，也就是结果集1减去结果集2的结果。案例：显示职位是MANAGER但薪水低于2500的员工记录SELECT ename,job,sal FROM emp WHERE job=‘MANAGER‘MINUSSELECT ename,job,sal FROM empWHERE sal &2500;标签：&&&&&&&&&&&&&&&&&&&&&&&&&&&原文地址：http://www.cnblogs.com/jmwm/p/7289829.html
&&国之画&&&& &&&&chrome插件&&
版权所有 京ICP备号-2
迷上了代码！Sql练习及答案_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
Sql练习及答案
你可能喜欢字段名between下限值and上限值
时间： 0:22:43
&&&&java企业级项目开发班&&&&oracle10g&&&&课堂笔记&&&&日&&&&&&&&目录&&&&&&&&第一章：oracle10g介绍&&&&一、oracle10g安装&&&&二、oracle10g设置命令&&&&开始—〉运行输入sqlplus打开oracel操作客户端登录用户：scott密码:tiger在输入用户名：用户名/密码注销登录：quit可以退出当前登录状态从当前账户切换到另一个指定账户上connect用户名/密码对一个账户进行锁定和解锁操作第一步：以管理员的身份登录，然后去解锁普通锁定的用户&&&&说明需要登录的用户被锁定，必须通过解锁后才能登录解锁命令：alteruser普通账户名accountunlock|unlock：解锁lock：锁定注意：必须在管理员身份登录后才能进行解锁或锁定操作清理屏幕hostcls将当前屏幕清理对命令窗口进行属性的修改：为了方便显示数据将命令窗口的宽：300个字符，行：9999设置查询数据显示的一行字符数：setlinesize300;注意：每一次进入客户端进行数据显示的时候，都必须重新设定&&&&设置一页显示行数&&&&&&&&setpagesize30;注意：每一次进入客户端进行数据显示的时候，都必须重新设定若system用户被锁定，怎么样来进行解锁锁定systemSystem/managerAlterusersystemaccountlock；解锁systemSystem/managerassysdbaAlteruse登录sys账户Sys/adminassysoperSql语言：sql89、sql92、sql99结构化的查询语言：定义功能（DDL）:createdropalter主要对于结构进行修改操作功能（DML）:insertupdatedelete主要对记录进行修改查询功能（DQL）:select查询控制功能（DCL）:&&&&第二章：SQL查询语言的语法&&&&Scott用户下表结构：雇员表字段名EmpnoEnameJobMgrHiredateSalCommDeptno部门表字段名DeptnoDnameLoc工资等级表字段名GradeLosal类型NumberNumber字段中文说明工资等级最低工资类型Number(2)Varchar2(14)Varchar2(13)字段中文说明部门编号部门名称地址类型Number(4)主键Varchar2(10)Valurchar2(9)Number(4)dateNumber(7,2)Number(7,2)Number(2)字段中文说明雇员编号雇员姓名职称领导编号入职日期工资奖金部门编号&&&&&&&&HisalSQL查询语言：&&&&Number&&&&最高工资&&&&一、简单查询&&&&SELECT[ALL|DISTINCT]字段名列表|常量|表达式Select所求内容：查询结果中的列Select所求内容from来自的表名列表&&&&查询出雇员表的所有信息&&&&Select*&&&&*:代表指定表的所有字段名列表selectempno,ename,job,mgr,hiredate,sal,comm,Select后面：可以跟指定的字段名列表，每一个字段之间用逗号隔开&&&&查询出雇员姓名和工资信息（投影）&&&&selectenameas雇员姓名,sal工资&&&&&&&&Select后面查询某一个字段，给结果字段重新取名（别名）A)字段名as别名B)字段名空格别名可以一个表取别名Selecta.empno,a.通常给表名取别名：表名空格别名给字段取别名，字段名as别名&&&&Ed命令的使用（可以将sql语句写在指定的文本文件中，然后引入执行）&&&&Ed回车会自动修改上一条执行过sql语句，并且打开文件后可以对语句进行修改，修改后保存按/回车就可以执行修改好的命令Ed文件名回车文件名：是一指定的文件，若文件存在，直接打开，若不存在会提示创建该文件可以引入外部写好sql语句文件直接执行@文件名回车&&&&查询出雇员表的职称，重复出现只显示一次&&&&SeleSelect[all|distinct]：all:默认情况，将重复记录显示出来Distinct：去掉查询结果中的重复记录重复记录：水平上一横数据都一模一样&&&&&&&&将雇员表按下列格式显示出来&&&&雇员编号为：7369雇员姓名：jack职称是：manager入职日期：select'empno:'||empno||'ename:'||ename||'job:'||job||'mgr:'||mgr||'sal:'||Select后面可以出现常量,和其他不连接用“||”该常量所在位置，是结果中一个列，并且其值是其本身&&&&查询出雇员的年薪&&&&selectenameas雇员姓名,sal*12as年薪fromempSelect后面可能出现表达式，查询不会改变原表中的值，若结果中的值需要改变时，必须在select后面跟表达式&&&&二、限定查询&&&&格式：select所求内容from表名列表[where筛选条件]所求内容：distinct字段名列表常量表达式&&&&查询出雇员工资大于或等于3000的所有雇员信息&&&&思路：筛选条件：sal=3000所求内容：empno,ename,job,mgr,hiredate,sal,comm,deptno所用的表：empselectempno,ename,job,mgr,hiredate,sal,comm,deptnofromempwheresal=3000;书写条件：字段名运算符具体的值&&&&查询出JONES员工的信息&&&&思路：筛选条件：ename=‘JONES’所求内容：*所用的表：empSelect*fromempwhereename=‘JONES’注意问题：1.查询的值是字符型，写值的时候必须使用定界符单引号内2.查询值必须分大小写，sql语句书写的其他地方部分大小写&&&&&&&&查询出工资大于等于1000并小于等于2500之间的所有员工信息&&&&思路：筛选条件：sal=1000andsal=2500所求内容：*所用的表：empselect*fromempwheresal=1000andsal=2500与运算：必须两个条件都要同时满足的记录才会显示在结果中条件1and条件2and条件3&&&&查询出工资在3000以上，或职称为MANAGER的所有职工&&&&筛序条件：sal3000orjob=‘MANAGER’所求内容：*所用的表：empselect*fromempwheresal3000orjob='MANAGER';或运算：表示只要满足其中一个条件即可条件1or条件2or条件3&&&&查询出职称为MANAGER或SALESMAN，并且工资在之间（含）的雇员信息。&&&&思路：筛选条件：(job=’MANAGER’ORJOB=’SALESMAN’)AND(SAL=2000ANDSAL=3000)所求内容：*所用的表：emp&&&&select*fromempwhere(job='MANAGER'ORJOB='SALESMAN')AND(SAL=800ANDSAL=3000);注意：运算过程中and优先级别高于or的，注意加括号来改变优先级别设置日期显示的格式：altersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';&&&&查询出奖金为空的所有员工信息&&&&筛选条件:commisnullSelect*fr&&&&&&&&查询奖金不为空的雇员信息Select*frome&&&&查询出职称为MANAGER,SALESMAN,CLERK的雇员信息做法1：Select*fromempwherejob='MANAGER'ORJOB='SALESMAN'ORJOB='CLERK';做法2：Select*fromempwherejobin('MANAGER','SALESMAN','CLERK');字段名in|notin(值的列表)In：属于Notin：不属于查询出以雇员姓名中包含有N字符的所有雇员信息select*fromempwhereenamelike'%N%';模糊查询条件：字段名like‘带有通配符的字段值’通配符：%：可以和任意多个字符相匹配（匹配即相等）_:可以和任意一个字符相匹配&&&&查询出雇员姓名中第二个字符为I的所有雇员信息select*fromempwhereenamelike'_I%';&&&&&&&&查询出工资在之间的职工姓名和职称思路：筛选条件：sal=1000andsal=3000所求内容：ename，job所用的表：empSelectename，jobfromempwheresal=1000andsal=3000&&&&查询出工资在之间（含）的所有雇员信息&&&&字段名between下限值and上限值：写在where后面，表示指定字段的值在上下限值之间并且包含上下限值。select*fromempwheresal=1000andsal=2000;select*fromempwheresalbetween1000and2000;&&&&Oracle数据库中的单行函数&&&&单行函数：只对一个值进行操作&&&&&&&&单行函数的语法单行函数名(字段名|表达式,[参数1,参数2,])参数说明:Fuction_name函数名称column列名expression表达式arg1,arg2,参数单行函数分类:字符函数：接收字符输入并返回字符或数值数值函数：接受数据输入并返回数值日期函数：对日期型数据进行操作转换函数：从一种数据类型转换为另外一种数据类型通用函数：NVA函数DECODE函数&&&&字符函数：接收字符输入并返回字符或数值1、lower(字符表达式)&&&&函数原型：lower(字符表达式)返回：字符串功能：将指定字符串转化为小写注意：伪表dual例题1：selectlower('HELLOWORLD!')&&&&例题2：查询出job中含有G字母的雇员姓名,职称（g字母大小写不区分）selectenameas雇员姓名,jobas职称fromempwherelower(job)like'%g%';&&&&2、upper(字符表达式)&&&&函数原型：upper(字符表达式)返回值类型：字符型功能：将小字母转换为大写字母selectupper('helloworld!')&&&&&&&&3、initcap(字符表达式)&&&&函数原型：initcap(字符表达式)返回值类型：字符型功能：将制定字符串的首字母转换为大写字母，其余字符小写selectinitcap('helloWORLD!')&&&&4、concat(字符串表达式1,字符串表达式2)&&&&函数原型：concat(字符串表达式1,字符串表达式2)返回值类型：字符串功能：将指定两个字符串进行收尾连接，最终成为一个字符串和字符表达式1||字符表达式2等价，字符常量必须写在一对单引号之间selectconcat('hello','world')&&&&select'hello'||'world'&&&&selectconcat(ename,job)&&&&&&&&5、substr(字符表达式,起始位置[,长度])&&&&函数原型：substr(字符表达式,起始位置[,长度])功能：从指定的字符表达式中取出从起始位置到规定长度的子串注意：1）改函数若省略[,长度]，从起始位置一直取到字符串的尾部2）起始位置：可以为正式时，从左边开始计数，若为负数时，从右边开始计数若起始位置的值为0或1的时候，从左边第一个开始取子串（包含起始位置）3）column字段名formata列宽功能：设置指定字段的显示列宽，一遍将列名显示完全&&&&&&&&6、length(字符表达式)&&&&函数原型：length(字符表达式)返回值类型：数值型功能：返回指定字符串的长度注意：汉字并不是两个宽度&&&&查询出雇员名字有6个字符组成的所有雇员信息select*fromempwherelength(ename)=6;&&&&7、replace(字符表达式1,字符表达式2,字符表达式3)&&&&函数原型：replace(字符表达式1,字符表达式2,字符表达式3)返回值类型：字符型功能：在字符表达式1中寻找到字符表达式2的内容时用字符表达式3来替换注意：1)该函数区分大小写2)将字符表达式3用空串来代替时，可以删除字符表达式1中的指定字符表达式2&&&&&&&&8、chr(整数)&&&&函数原型：chr(整数)返回值类型：字符型功能：将指定整数作为ASCII码返回对应的一个字符selectchr(65)&&&&9、ascii(字符)&&&&函数原型：ascii(字符)返回值类型：数值型功能：将指定字符的ASCII码返回selectascii('A')&&&&数值型函数&&&&函数&&&&ABS（value）CEIL（value）&&&&说明&&&&返回指定数值的绝对值返回大于或等于Value的最小整数值&&&&&&&&FLOOR（value）COS（value）COSH（value）EXP（value）LN（value）LOG（x,y）POWER（value,exponent）ROUND（value,precision）MOD（value,divisor）SQRT（value）trunc(value)&&&&返回小于或等于value的最大整数值求余弦值求反余弦值返回以e为底的指数值返回以value为底的自然对数返回以x为底y的对数求value的exponent的指数值按value的precision精度四舍五入取余返回value的平方根取整数部分，不会进行四舍五入操作&&&&1、abs(数值)&&&&函数原型功能例题Abs(数值表达式)返回数值表达式的绝对值selectabs(-100)&&&&2、ceil(数值表达式)&&&&函数原型功能例子ceil(数值表达式)大于或等于数值表达式的最小整数&&&&3、Floot(数值表达式)&&&&函数原型功能例题Floot(数值表达式)小于或等于数值表达式的最大整数&&&&&&&&power(数值表达式1，数值表达式2)&&&&函数原型功能例题power(数值表达式1，数值表达式2)求数值表达式1的数值表达式2次方&&&&Round(数值表达式,整数表达式)&&&&函数原型功能例子Round(数值表达式,整数表达式)将数值表达式1，保留整数个小数后得到的结果整数表达式2：个位为0，往整数部分走是负数，往小数部分走是正数表示&&&&Mod(value,divisor)&&&&函数原型功能例题Mod(被除数,除数)返回两个数的余数&&&&&&&&SQRT（value）&&&&函数原型功能例题SQRT（数值表达式）求出数值表达式的算术平方根&&&&日期函数&&&&函数&&&&ADD_MONTHS（date,count）LAST_DAY(date)MONTHS_BETWEEN(date1,date2)NEW_TIME(date,’this’,’orther’)NEXT_DAY(date,’day’)SYSDATECURRENT_TIMESTAMP()&&&&说明&&&&指定日期date上增加count个月返回日期date所在月的最后一天返回date1到date2之间相隔的月数指date由this时区转为other时区返回指定日期或最后一个星期几的日期.day为星期几获取当前的系统日期获取当前的系统日期及时间&&&&add_months(日期表达式1,数值表达式)&&&&函数原型功能返回值类型注意例题1）add_months(日期表达式1,数值表达式)在日期表达式的基础上加上数值表达式个月份后的那个日期日期型Selectsysdatefromdual；返回当前系统的日期今天日期基础上加1个月后是那个日期&&&&2、Last_day(日期表达式)&&&&函数原型功能Last_day(日期表达式)返回日期表达式当前月份最后一天的日期&&&&&&&&返回值&&&&日期型&&&&Months_between(日期表达式1，日期表达式2)&&&&函数原型功能：返回值类型例题Months_between(日期表达式1，日期表达式2)求出日期表达式1和日期表达式2之间所相差的月数数值型&&&&NEXT_DAY(日期表达式,’指定星期几’)&&&&函数原型功能例题NEXT_DAY(日期表达式,’指定星期几’)指定日期表达式下一个星期的指定星期几的日期返回值类型是，日期&&&&类型转换函数To_char()&&&&将数字或日期转化为字符串函数原型功能To_char(日期表达式|数值表达式,’格式’)将数字或日期转化为字符串&&&&&&&&当参数为日期时&&&&to_char()在使用的时候要用到格式控制的符,格式控制符不区分大小写字母年:Y,年份为四位数，故应写成:YYYY或yyyy月:M,月份为两位数,故应写成:MM或mm日:D，日为两位数，故应写成:DD或dd格式前加fm表示去掉前导0日期当格式用‘yyyy’单独可以将指定日期的年份取出‘mm’取出月份‘dd’取出日部分1）格式可以用9或0来作为格式的限定2）若数值表示的货币，可以中格式前加$符号，显示值时，显示前导$3）若要显示￥货币符，应用本地货币符，中前面加前导L4）可以用000,000,000来添加数值的千位符显示字符串类型&&&&当参数为数值型时返回值类型&&&&&&&&to_char()在使用的时候要用到格式控制的符,格式控制符不区分大小写字母年:Y,年份为四位数，故应写成:YYYY或yyyy月:M,月份为两位数,故应写成:MM或mm日:D，日为两位数，故应写成:DD或dd&&&&To_number()&&&&将当前的固定格式的字符串转化为数字能够转换的字符（0—9的数字字符正负号，小数点，e字母看情况）&&&&To_date()&&&&将当前固定格式的字符串转化为日期&&&&To_date(字符表达式,格式)&&&&查询结果的排序：?ORDERBY排序字段名1DESC|ASC,排序字段名2DESC|ASC?排序对最后的结果进行排序，ORDERBY一般写中SQL语句的最后一个部分?若排序字段有多个时，从左到右依次，若第一个排序字段有相同值时，然后按第二个排序字段区进行排序。?默认是升序排序（ASC，也可以不写ASC）按工资的降序显示所有雇员信息select*f先按部门的升序，部门相同的按工资的降序排序，若工资相同时再按入职时间升序显示出雇员信息select*fromemporderbydeptnoasc,saldesc,&&&&&&&&SQL强化训练&&&&选择部门30中的所有职工筛选条件：deptno=30所求内容：emp.*来自的表：empSelect所求内容from来自的表where筛选条件selectemp.*fromempwheredeptno=30;列出所有业务员(CLERK)的姓名，编号，和部门编号筛选条件：job=‘CLERK’所求内容：ename,empno,deptno所用的表：empselectename,empno,deptnofromempwherejob='CLERK';找出奖金高于薪金的员工筛选条件：commsal所求内容：emp.*来自的表：empSelectemp.*找出奖金高于薪金的60%的员工筛选条件：commsal*0.6所求内容：*来自的表：empSelect*fromempwherecommsal*0.6;找出部门10中所有经理(MANAGER)和部门20中所有业务员(CLERK)的详细资料筛选条件：(deptno=10andjob=’MANAGER’)所求内容：*来自的表:empselect*fromempwhere(deptno=10andjob='MANAGER')or(deptno=20andjob='CLERK');找出部门10中所有经理(MANAGER),部门20中所有业务员(CLERK),既不是经理又不是业务员但其薪水大于等于2000的所有员工的详细资料筛选条件：(deptno=10andjob=’MANAGER’)OR(deptno=20andSAL=2000)所求内容:*来自的表：empSELECT*FROMEMPWHERE(DEPTNO=10ANDJOB='MANAGER')OR(DEPTNO=20ANDJOB='CLERK')OR(JOB!='MANAGER'ANDJOB!='CLERK'ANDSAL=2000);找出收取佣金的员工的不同工作筛选条件：SALISNOTNULL所求内容：JOB所用的表：empSELECTDISTINCTJOBFROMEMPWHERESALISNOTNULL;找出不收取佣金或收取的佣金低于1000的员工筛选条件：salisnullorsal1000所求内容：*job=’CLERK’)OR(job!=’MANAGER’ANDJOB!=’CLERK’ANDor(deptno=20andjob=‘CLERK’)&&&&&&&&所用的表：empselect*fromempwheresalisnullorsal1000;找出各月倒数第3天受雇的所有员工筛选条件：hiredate=LAST_DAY(HIREDATE)-2所求内容:*来自的表：empselect*fromempwherehiredate=last_day(hiredate)-2;找出早于12年前受雇的员工筛选条件：trunc(months_between(sysdate,hiredate)/12)12select*fromempwheretrunc(months_between(sysdate,hiredate)/12)12;以首字母大写的方式显示所有员工的姓名selectinitcap(ename)显示正好为5个字符的员工姓名selectenamefromempwherelength(ename)=5;显示不带有”R”的员工姓名select*fromempwhereNOT(enamelike'%R%');select*fromempwhereenamenotlike'%R%';&&&&显示所有员工姓名的前三个字符selectsubstr(ename,1,3)显示所有员工的姓名，并用’a’替换所有’A’selectreplace(ename,'A','a')显示满10年服务年限的员工姓名和受雇日期selectename,hiredatefromempwheretrunc(months_between(sysdate,hiredate)/12)=10;显示员工的详细资料，按姓名由大到小排序select*fro显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面selectename,hiredatefromeselect*fromemporderbytrunc(months_between(sysdate,hiredate)/12)显示所有员工的姓名，工作和薪金，按工作降序排列，若工作相同则按薪金升序排序selectename,job,salfromemporderbyjobdesc,显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在的月排序，若月份相同，则将最早年份排在最前面selectename,to_char(hiredate,'yyyy')asyear,trunc(months_between(sysdate,hiredate))asmonthfromemporderbyto_char(hiredate,'fmmm'),selectename,to_char(hiredate,'yyyy')asyear,trunc(months_between(sysdate,hiredate))asmonthfromemporderbyto_char(hiredate,'fmmm'),2&&&&&&&&显示一个月为30天的情况所员工的日薪金，忽略余数selecttrunc(sal/30)as日薪金&&&&找出在(任何年份)2月受聘的所有员工selct*fromempwhereto_char(hiredate,'fmmm')='2';对每个员工，显示其加入公司的天数selecttrunc(sysdate-hiredate)as入职天数显示姓名中任意位置包含“A”的所有员工姓名selectenamefromempwhereenamelike'%A%';以年月日方式显示所有员工的服务年限selecttrunc(months_between(sysdate,hiredate)/12)asyear,trunc(mod(months_between(sysdate,hiredate),12))month,trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate)))&&&&三、多表查询&&&&查询格式SELECT所求内容FROM表1,表2…WHERE连接条件AND筛选条件用到多张表时，需要重表之间的连接连接（内连接（等值连接和自然连接）和外连接（左连接，右连接，全连接））连接：将多张有联系的表组合称为一张&&&&例题1：查询出查询每一个雇员的雇员信息和工资等级信息所求内容：emp.*,grade所用的表：emp,salgrade连接条件：sal=losalandsal=hisalSelectemp.*,gradefromemp,salgradewheresal=losalandsal=例题2：查询出雇员姓名以及雇员所做的部门名筛选条件：所求内容:ename,dname所用的表：emp,dept连接条件：emp.deptno=dept.deptnoselecta.ename,b.dnamefromempa,deptbwherea.deptno=b.&&&&&&&&例题3：查询出雇员姓名，所做部门名，工资等级筛选条件：所求内容：ename,dname,grade所用的表：emp,dept,salgrade连接条件：emp和dept：emp.deptno=dept.deptnoEmp和grade：sal=losalandsal=hisalselectename,dname,gradefromemp,dept,salgradewhere(emp.deptno=dept.deptno)and(sal=losalandsal=hisal);例题4：查询出工资等级为5的所有职工所中的部门信息筛选条件：grade=5所求内容：dept.*所用的表：1）从筛选条件来自的表salgrade2）从所求内容中来自动表dept3）两张表若需要连接，就必须有一张中间表emp才能将上面两张表连接起来，中间表也要包括进来selecta.*fromdepta,empb,salgradecwherea.deptno=b.deptnoand(b.sal=c.losalandb.sal=c.hisal)andgrade=5;例题5：查询出地址中NEWYORK工作的雇员信息例题6:查询出地址中NEWYORK工作的雇员姓名和工资等级四、左、右连接查询左连接查询：以左边表为标准查询内容，左边表的所有记录都会出现的查询结果中，若中右边有对应记录则对应出现，没有对应右边表字段值出用null来填充补齐。改变：左边表名.公共字段名=右边表名.公共字段名(+)例题：将dept、emp两张表进行左连接查询select*fromdept,empwheredept.deptno=emp.deptno(+);右连接：以右边表为标准，去连接左边表，右边表的所有记录都会出现在查询结果中，有对应则对应，没有对应的左边表字段值用null值填充。连接条件：左边表名.公共字段名(+)=右边表名.公共字段名例题：将dept、emp两张表进行右连接查询select*fromdept,empwheredept.deptno(+)=emp.&&&&&&&&SQL：1999SQL定义(了解)&&&&SELECTtable1.column,table2.columnFROMtable1[CROSSJOINtable2]|[NATURALJOINtable2]|[JOINtable2USING(column_name)]|[JOINtable2ON(table1.column_name=table2.column_name)]|[LEFT|RIGHT|FULLOUTERJOINtable2ON(table1.column_name=table2.column_name)]&&&&1、交叉连接(CROSSJOIN):迪卡尔积运算92标准：select*fromemp,99标准：select*2、table1[INNERJOINtable2ON(table1.column_name=table2.column_name)]、&&&&内连接（实质就是等值连接）例题：Emp表和dept表进行内连接（列相加，记录相对应）92标准：select*fromemp,deptwhereemp.deptno=dept.格式：select所求内容from表1,表2where表1.公共字段名=表2.公共字段名99标准：select*fromempinnerjoindeptonemp.deptno=dept.格式：select所求内容from表1[INNER]JOIN表2ON表1.公共字段名=表2.公共字段名select*fromempjoindeptonemp.deptno=dept.格式：select所求内容from表1JOIN表2ON表1.公共字段名=表2.公共字段名99标准：select*fromempjoindeptusing(deptno);格式：select所求内容from表1Join表2USING(公共字段名)用USING(公共字段名)来代替连接条件&&&&3、自然连接(NATURALJOIN)：是等值连接基础上去掉一个重复的列所得到结果就是自然连接得到结果例题：写emp和dept两张进行自然连接92标准：selectemp.*,dname,locfromemp,deptwhereemp.deptno=dept.99标准：select*fr4、外连接（左连接，右连接，全连接）Table1LEFT|RIGHT|FULL[OUTER]JOINtable2左连接/右连接/全连接1、左连接92标准：select*fromemp,deptwhereemp.deptno=dept.deptno(+);格式：select所求内容from表1，表2where表1.公共字段名=表2.公共字段名(+)99标准：select*fromempleftjoindeptonemp.deptno=dept.select所求内容from表1left[outer]join表2on表1.公共字段名=表2.公共字段名2、右连接92标准：select*fromemp,deptwhereemp.deptno(+)=dept.格式：select所求内容from表1，表2where表1.公共字段名(+)=表2.公共字段名99标准：select*fromemprightjoindeptonemp.deptno=dept.select所求内容from表1right[outer]join表2on表1.公共字段名=表2.公共字段名3、全连接92标准：没有表达方式&&&&&&&&99标准：select*fromempfulljoindeptonemp.deptno=dept.select所求内容from表1full[outer]join表2on表1.公共字段名=表2.公共字段名&&&&四、嵌套查询&&&&将某一个查询的结果作为另一查询的条件值，查询成为嵌套查询Select所求内容from所求内容来自的表where公共字段名IN|NOTIN(SELECT公共字段名from筛选条件所涉及的表where筛选条件and其他条件)and其他条件若子查询前面：===时，子查询的结果只能有一个容易子查询前面：in|notin，子查询的结果又多个1）例题查询出地址在NEWYORK的雇员编号，雇员姓名A)先将地址在NEWYORK部门编号是多少selectdeptnofromdeptwhereloc='NEWYORK';B)在通过上面的部门编号求出所有该部门的雇员信息select*fromempwheredeptno=10;select*fromempwheredeptno=(selectdeptnofromdeptwhereloc='NEWYORK');2)查询出工资雇员编号为7788雇员的工资高于的所有雇员信息select*fromempwheresal=(selectsalfromempwhereempno=7788);3）查询出和雇员编号为雇员在同一个部门的雇员信息select*fromempwheredeptnoin(selectdeptnofromempwhereempno=7788orempno=7839);4）查询出不和雇员编号为雇员在同一个部门的雇员信息select*fromempwheredeptnonotin(selectdeptnofromempwhereempno=7788orempno=7839);&&&&五、组函数计算查询&&&&组函数：对一组数据进行操作的函数SUM()求和函数AVG()求平均值MAX()求最大值MIN()求最小值COUNT()统计应用组函数：例题：计算出所有雇员的工资总和selectsum(sal)assum_&&&&&&&&例题：求出所有雇员的平均工资selectavg(sal)asavg_&&&&例题：求出所有雇员中最低工资和最高工资selectmax(sal)asmax_sal,min(sal)asmin_&&&&例题：统计出雇员表中出现的部门有多少个（重复值统计问题）参数中加：distinct重复值只统计一次selectcount(distinctdeptno)ascount_&&&&例题：查询出工资高于平均工资的所有雇员信息select*fromempwheresal(selectavg(sal)fromemp);&&&&注意：当组函数作为条件时，不能写中where后面，只能写groupby部分的having后面作为分组所满足的条件。例题：查询出工资都高于20部门的所有员工的雇员信息。select*fromempwheresal(selectmax(sal)fromempwheredeptno=20);&&&&&&&&例题：查询出工资等级为5级的雇员的所在部门名称内容.分析：筛选条件：grade=5所求内容：dname所用的表：所求内容涉及的字段来自的表dept筛选条件涉及字段来自的表salgradeDept和salgrade两张表没有直接联系，必须使用中间表emp后，两张表才能连接，查询时必须包含中间表。方法1：思想：将dept，salgrade，emp三张表等值连接成为一张表后，进行条件和所内容的筛选selectdistinctdnamefromdept,emp,salgradewheredept.deptno=emp.deptnoand(sal=losalandsal=hisal)andgrade=5;方法2：思想：将dept和emp先连接成为一张表后，再和salgrade进行嵌套查询selectdistinctdnamefromdept,empwheredept.deptno=emp.deptnoandsal=(selectlosalfromsalgradewheregrade=5)andsal=(selecthisalfromsalgradewheregrade=5);方法3：思想：将emp和salgrade连接成为一张表后和dept表进行嵌套查询selectdistinctdnamefromdeptwheredeptnoin(selectdeptnofromemp,salgradewheresal=losalandsal=hisalandgrade=5);方法4：思想：将dept表，与emp和salgrade两张表连接后形成一张表别名叫t，在用dept和t表进行连接，最终求出内容。selectdistincta.dnamefromdepta,(select*fromemp,salgradewheresal=losalandsal=hisal)twherea.deptno=t.deptnoandgrade=5;&&&&六、量词查询&&&&量词：any、some，all例题：查询出工资高于30中任何一个职工工资的雇员信息理解：只要工资高于部门30中职工工资最低一个即可select*fromempwheresal(selectmin(sal)fromempwheredeptno=30);select*fromempwheresalany(selectsalfromempwheredeptno=30)；select*fromempwheresalsome(selectsalfromempwheredeptno=30);注明：any和some，代替子查询结果中最小的值例题：查询出工资都高于部门为20的所有职工工资的雇员信息理解：只要工资高于部门20的最高工资即可select*fromempwheresal(selectmax(sal)fromempwheredeptno=20);select*fromempwheresalall(selectsalfromempwheredeptno=20);注意：all代替子查询结果中的最大值例题：查询出还没有职工的部门信息若dept表中的deptno没有中emp中出现过，说明该部门目前没有职工select*fromdeptwheredeptnonotin(selectdeptnofromemp);select*fromdept,empwheredept.deptno=emp.deptno(+)andemp.selectdept.*fromdeptleftjoinempondept.deptno=emp.deptnowhereemp.注意：反查询思想：若需要查询的内容，给出正面条件不足，但可以确定发面的情况下&&&&&&&&七、分组查询&&&&Groupby分组字段名列表[having分组所满足的条件]例题：求出部门号为20的的平均工资selectavg(sal)fromempwheredeptno=20;&&&&例题：查询出每一个部门的平均工资。selectdeptno,avg(sal)&&&&例题：查询出每一个职位的平均工资，最高工资，最低工资selectjob,avg(sal)avg_sal,max(sal)max_sal,min(sal)min_sal,count(job)&&&&例题：查询出部门编号为10雇员的信息（部门编号，职称，平均工资）selectdeptno,job,avg(sal)avg_salfromempwheredeptno=10groupbydeptno,&&&&理解：查询出每一个部门中每一个职位的平均工资，分组字段就会出现多个，先按部门分组，再按职称去分组，两个字段之间用逗号隔开。例题：查询出平均工资最高部门平均工资。selectmax(avg(sal))&&&&注意：组函数可以嵌套使用，一旦嵌套使用必须进行分组操作，分组字段不能再所求内容中出现例题：查询出平均工资高于2000的所有部门信息selectdept.deptno,dept.dname,dept.locfromdept,empwheredept.deptno=emp.deptnogroupby&&&&&&&&dept.deptno,dept.dname,dept.lochavingavg(sal)2000;select*fromdeptwheredeptnoin(selectdeptnofrom(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)twheret.avg_sal2000);selectdept.*fromdept,(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)twheredept.deptno=t.deptnoandavg_sal2000;&&&&注意：若需要用having分组满足得到条件时，必须采用分组having必须和groupby连用筛选条件：对from后面的表的字段进行筛选连接条件：对from后面的表进行连接操作分组所满足的条件：对from后面表的字段进行再次计算后来作为条件（对查询结果再进行第二次筛选）&&&&八、内外层相互嵌套查询&&&&例题：查询出每一个部门工资最高的一位雇员信息（内外层相互嵌套查询）selectwai.*fromempwaiwheresal=(selectmax(sal)fromempneiwherenei.deptno=wai.deptno)内层查询需要外层查询表给其提供一个值后，才能完成内层查询外层查询需要内层查询的查询结果，才能完成&&&&查询语句总结：&&&&92标准：Select[distinct|all]字段名列表常量表达式组函数|单行函数From表名|完整的查询语句别名|视图Where连接条件(等值|左连接|右连接)and其他筛选条件Groupby分组字段名列表[having分组所满足的条件]Orderby排序字段名1|编号DESC|ASC,排序字段名2DESC|ASC99标准：Select[distinct|all]字段名列表常量表达式组函数|单行函数From表名1[CROSSJOIN表名2]|[NATURALJOIN表名2]|[INNERJOIN表名2]|[LEFTJOIN表名2]|[RIGHTJOIN表名2]|[FULLJOIN表名2]&&&&ON连接条件|USING(公共字段名)]WHERE筛选条件&&&&Groupby分组字段名列表[having分组所满足的条件]Orderby排序字段名1|编号DESC|ASC,排序字段名2DESC|ASC子查询|嵌套查询结构Select所求内容from来自的表名列表where连接条件AND公共字段名IN|NOTIN|||=|=|=&&&&&&&&(SELECT公共字段名from来自的表名列表where连接条件and其他筛选条件)and其他筛选你条件Groupby分组字段名列表[having分组所满足的条件]Orderby排序字段名1|编号DESC|ASC,排序字段名2DESC|ASC&&&&&&&&