——-集合运算————–
union
union会在表连接之后筛掉重复记录,所以在表连接之后会对产生的结果进行排序运算,删除重复的记录然后在返回记录并且按照ascii排序
.
union all 相对于union来讲会将重复的数据也显示出来并且不排序
eg:SELECT e.last_name,d.name from s_emp e,s_dept d
where e.dept_id(+)=d.id union all SELECT e.last_name,d.name from s_emp e,s_dept d where e.dept_id=d.id(+)
—————— minus ————————
第一个结果集合减去第二个结果集中的记录(在第一个结果集合中也存在公共记录)减去公共的记录(要求两张表的字段一致,否则无法执行)
eg:
select last_name,salary from s_emp where rownum<=10 minus
select last_name,salary from s_emp where rownum<5;
———ROWNUM,ROWID———-
1)其中rownum是查询到的结果集后加上去的(oracle特有的),它总是从1开始
2)对于oracle的rownum 不支持> >= = BETWEEN …AND (语法不会出现错误,但是用后查询不到任何记录)只能<、<=、!=这些符号。
rownum的使用例子
select NAME 姓名 FROM MYTEST WHERE ROWNUM>3;-----false
select NAME 姓名 FROM MYTEST WHERE ROWNUM=3;-----false
select NAME 姓名 FROM MYTEST WHERE ROWNUM BETWEEN 1 AND 4; ---false
select NAME 姓名 FROM MYTEST WHERE ROWNUM<5 -----true 选取1-4行数据。
select NAME 姓名,rownum FROM MYTEST WHERE ROWNUM<5----选取1-4行数据并显示rownum列。
ROWID :是对应一个行的数据的对应的磁盘地址。
—————–intersect—————–
用于返回两个结果集的交集。返回两个查询中都出现的记录。
select last_name,salary from s_emp where rownum<10 intersect frownum,last_name,salary from s_semp where rownum<5;
—————–组函数——————-
—–GROUP BY
总用于行集的组函数给了魅族的一个结果(将若干行分为几个组)
组函数出现select 的列和HAVING字句中。
在select声明的GROUP BY子句将行细分为更小的组,ORDER BY永远放在SELECT子句的后边。HAVING子句用来限制ORDER BY之后的结果集。
—–HAVING
—常见的组函数
----AVG(DISTINCT|ALL|N) 求列的平均()在存在多行的基础之上)
----COUNT(DISTINCT|ALL|expr|*) 统计行(记录数)
----MAX(DISTINCT|ALL|expr)求列的最大值
----MIN(DISTINCT|ALL|expr)求列的最小值
----SUM(DISTINCT|ALL|n)求列的总和
----VARIANCE(DISTINCT|ALL|n)求离散查
----STDDEV(DISTINCT|ALL|n)标准差
--DISTINCT 唯一值(不包括重复值)
--ALL 所有值(包括重复值),不包括空值
--n 代表所操作的列为数值类型
--expr 代表所操作的列可以为任意数据类型。
—GROUP BY 子句的使用成因
select id ,last_name,dept_id department from s_emp where dept_id=41;
上边就会把符合条件的行都打印出来,然而好多的dept_id好多都一样。
此时我们可以通过dept_id来分组。将dept_id相同的都分为一组。
eg:SELECT COUNT(*) "number" from MYTEST GROUP BY SEX;这是根据性别来统计人数。切记GROUP BY 并不是任何字段都能进行分组。
严格要求
- –GROUP BY 后边只能跟列名,
- 出现SELECT语句中没有使用组合函数的列必须出现GROUP BY子句中,
- GROUP BY 子句中不必须出现SELECT 中出现的列,但出现了会更有意义。
- GROUP BY 自己中若出现多列时是按照多列联合唯一进行分组。总件用逗号隔开。
eg:select name ,birthday,count(1) from mytest group by name,birthday;
—GROUP BY 容易出错的情况。
- 1)不满足–出现SELECT语句中没有使用组合函数的列必须出现GROUP BY子句中这种情况。
严禁将函数放在条件上即不能将其放在WHERE子句之后。
select DEPT_ID,AVG(SALARY) FROM MYTEST GROUP BY DEPT_ID WHERE AVG(SALARY) >2000;(false)对于上边(2)无法操作的情况我们可以使用HAVING来完成该操作。
select DEPT_ID,AVG(SALARY) FROM MYTEST GROUP BY DEPT_ID HAVING AVG(SALARY)<3000;
–此时HAVING可以代替WHERE作为条件来操作。
HAVING子句的语法,HAVING 放在GROUP BY之后用于进一步限制的条件。
–SELECT 语句的执行顺序
先执行WHERE 子句进行过滤,在进行分组,接着执行组函数,然后执行HAVING子句对分组进行限定,最后是ORDER BY 子句。
SELECT COLUMN,GROUP_FUNCTION FROM TABLE [WHERE CONDITION GROUP BY GROUP_BY_EXPRESSION] [HAVING GROUP_CONDITION ORDER BY COLUMN]
——–HAVING子句示例———-
SELECT TITLE,SUM(SALARY) PAYROLL FROM S_EMP WHERE TITLE NOT LIKE '%VP%'
GROUP BY TITLE HAVING SUM(SALARY)>5000 ORDERY BY SUM(SALARY);
--实例--SELECT NAME,SUM(SALARY) PAYROLL FROM MYTEST WHERE NAME NOT LIKE '%张%' GROUP BY NAME HAVING SUM(SALARY)>6000 ORDER BY SUM(SALARY);
--注意点:(1)如果分组的列为空,那么仍然会进行分组。
-- (2)在select语句中可以不使用组函数而直接进行分组。
(3)使用了HAVING子句之后,必须使用GROUP BY子句。并且也要使用HAVING子句。
(4)如果HAVING子句之后不是组函数而是列,那么GROUP BY 子句必须根据这个列进行分组否则报错。 select dept_id from mytest group by dept_id having dept_id>1;
(5)如果要对组函数结果进行限制,那么必须曹勇GROUP BY子句,并且也使用HAVIGN子句。
————-子查询—————–
子查询是一个完整的SELECT 语句,可以拥有GROUP BY HAVING 子句,可以使用组函数,可以从多个表查询,子查询外围的语句可以使SELECT 语句也可以是其他与句(create table
insert等语句)。
1.——-单行子查询
如果外围的语句是SELECT 语句,则查询内容必须用小括号界定,
根据返回的结果是单个值还是多个值可以分为
单行子查询:使用单行比较符:“=”
多行子查询:使用多行比较符“in”
子查询的位置可以出现在
where子句
having子句
from 子句。
eg:select dept_id,AVG(SALARY) FROM S_EMP GROUP BY DEPT_ID HAVING AVG(SALARY)>(SELECT AVG(SALARY) FROM S_EMP WHERE DEPT_ID =32);
- —–多行子查询的使用:
select last_name,first_name,title from s_emp where dept_id=(select id from name=’xxx’ or region_id=2); 这样会报错,单行返回多行错误。
解决办法(用in代替):select last_name,first_name,title from s_emp where dept_id in(select id from s_dept where name=’XXX’ OR region_id=2);这样就不会报错。并且根据实际情况返回对应的行。
—–子查询的应用——
1)从s_emp表中查找3-5行的记录。
select rownum r,last_name from (select rownum,s.* from s_emp s)
where r between 3 and 5;—切记一定要给rownum定义一个别名,否则,系统认为这是系统的rownum.
2)从s_emp 表中查找第三行的记录。
select name from (select rownum,s.* from s_emp s) where r=3;
3)从s_emp 表中查出薪水最高的三名员工
—注意:传统的select rownum last_name,salary from s_emp where rownum<=3 order by salary desc;
–这样是不对的,因为order by 是最后执行的,相当于对rownum进行排序之后取前三个,并不一定是真正的前三名。
–解决办法:我们可以采用子查询,子查询对薪水进行排序,
select last_name from (select last_name salary from s_emp order by salary desc);where rownum<=3;这样就可以很好的解决改问题。
4)从s_emp表中找出那些工资高于他们所在部门的平均工资的员工。
第一种方法可以采用子查询,子查询中用于获取平均工资,外围函数用于比较子查询的数据。(采取了自连接方式)
select last_name,dept_id,salary from s_emp a where salary>(selec AVG(SALARY) FROM s_emp where dept_id=a.dept_id);
相信你对上边的逻辑有些模糊,不过我们也可以采取如下这种方法来操作。
第二种方法:
select a.last_name,a.salary a.dept_id,b.avgsal from s_emp a,(
select dept_id ,avg(salary) from s_emp group by dept_id) b where
a.dept_id >b.avgsal;通过这种方法相信你会更好的理解。
5)从s_emp表中找出那些工资高于他们所在部门的manager的工资的员工