高级数据过滤
AND操作符
要通过不止一个列进行果过滤,可以使用AND操作符对WHERE子句附加条件,用在WHERE子句中的关键字
OR 操作符
OR操作符与AND操作符正好相反,它告诉DBMS检索匹配任一条件的行,事实上,许多DBMS在ORWHERE 子句的第一个条件得到满足的情况下就不再计算第二个条件了(在第一个条件满足时,不管第二个条件是否满足,相应的行都将被检索出来)
IN 操作符
In操作符用来指定条件范围,范围中的每个条件都可以进行匹配,in取一组逗号分隔,括在圆括号中的合法值IN 操作符允许我们在 WHERE 子句中规定多个值。
SQL IN 语法
SELECT column_name(s) FROM table_nameWHERE column_nam e IN (value1,value2,...)
IN 操作符实例
SELECT prof_name ,prod_price FROM Products WHERE vend_id IN(‘DLL01’, ’BRSo1’)ORDER BY prod_name
此SELECT 语句检索由供应商DDL01和BRS01制造的所有产品,IN操作符后跟由逗号分隔的合法值,这些值必须在圆括号中.
你会猜测IN 操作符完成了与OR相同的功能,恭喜你答对了,下面的SQL语句完成了与上面的例子相同的工作,
SELECT prod_name,prod_price FROM Products WHERE vend_id=’DDL01’ OR vend_id=’BRS01’ ORDER BY prod_name;
in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');
与
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的结果是相同的。
为什么使用IN操作符呢,优点如下:
1.IN操作符的语法更清楚直观,
2.IN的最大优点是可以包含其他的SELECT语句,能够更动态地建立WHERE子句,
NOT操作符
WHERE子句中的NOT操作符有且只有一个功能,那就是否定其后所跟的任何条件,因为NOT从不单独使用,(他总是与其他操作符一起使用),所以了他的语法与其他操作符有所不同,NOT关键字可以用在要过滤的列强,而不仅是在其后.
实例:
列出除了DLL01之外的所有供应商制造的产品
SELECT prod_name FROM Products WHERE NOT vend_id=’DLL01’ ORDER BY prod_name
这里的NOT否定跟在其后的条件,因此,DBMS不是匹配vend_id为DLL01,而是匹配非DLL01之外的所有东西上面的例子也可以使用<>操作符完成
SELECT prod_name FROM Products WHERE NOT vend_id<>’DLL01’ ORDER BY prod_name
在简单的WHERE子句中NOT没有什么优势,但是在复杂的子句中NOT是非常有用的,例如,在与IN操作符联合使用时,NOT可以非常简单地找出与条件列表不匹配的行
any、all、exists 的使用
1. 数据表
有如下数据表:StudentInfo 学员信息表,表数据如图1 所示:
ID | NAME | SCORE | CLASSNAME |
1 | 张青青 | 78 | S1101 |
2 | 李红 | 54 | S1101 |
3 | 顾小强 | 86 | S1101 |
4 | 陈乔 | 34 | S1102 |
5 | 韩伟 | 99 | S1102 |
图1 学员信息表数据
其中,ID 代表学员编号,NAME 为学员姓名,SCORE 为学员考试成绩,CLASSNAME 为学员所在的班级名称。
2. any 的使用
编写 sql 语句查询:S1101 班哪些学生的成绩高于S1102 班的最低成绩
SELECT NAME FROM studentInfo WHERE classname='S1101' AND score >(SELECT MIN(score) FROM studentInfo WHERE classname='S1102');
除此之外,我们还可以使用any:
SELECT NAME FROM studentInfo WHERE classname='S1101' AND score >ANY(SELECT score FROM studentInfo WHERE classname='S1102');
3. all 的使用
编写sql 语句查询:S1101 班哪些学生的成绩高于S1102 班的最高成绩
SELECT NAME FROM studentInfo WHERE classname='S1101' AND score >(SELECT MAX(score) FROM studentInfo WHERE classname='S1102');
除此之外,我们还可以使用all:
SELECT NAME FROM studentInfo WHERE classname='S1101' AND score >ALL(SELECT score FROM studentInfo WHERE classname='S1102');
4. exists 的使用
EXISTS 的作用比较简单,它只关注它后面的子查询返没返回值,而不在乎返回多少。如果返回,则整个表达式就为真,否则为假。NOT EXISTS 关键字则和EXISTS 作用相反。
假设要查询有没有成绩为 100 的学生,如果有,则将所有学生的成绩都输出,如果没有,
则什么都不输出,我们使用EXISTS 实现,如下:
SELECT name,score FROM studentInfo WHERE EXISTS(SELECT * FROM studentInfo WHERE score=100);
IN与EXISTS区别
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)
1:
select * from A where cc in (select cc from B)
效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc)
效率高,用到了B表上cc列的索引。
相反的
2:
select * from B where cc in (select cc from A)
效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc)
效率低,用到了A表上cc列的索引。
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。
(记录合并)union和union all 的区别
在数据库中,union和union all关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同。
union在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。
如:
select * from test_union1
union
select * from test_union2
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
而union all只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。
从效率上说,union all要比union快很多,所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用union all,如下:
select * from test_union1
union all
select * from test_union2
使用 union/union all 组合查询的结果集有两个最基本的规则:
1。所有查询中的列数和列的顺序必须相同。
2。数据类型必须兼容
SELECT子句
Group By子句
首先讲讲GROUP BY 子句语法:
sql语句Group By用法一则
如果我们的需求变成是要算出每一间店 (store_name) 的营业额 (sales),那怎么办呢?在这个情况下,我们要做到两件事:
第一,我们对于 store_name 及 Sales 这两个栏位都要选出。
第二,我们需要确认所有的 sales 都要依照各个 store_name 来分开算。这个语法为:
SELECT "栏位1", SUM("栏位2") FROM "表格名" GROUP BY "栏位1"
示范
Store_Information 表
store_name | Sales | Date |
Los Angeles | $1500 | Jan-05-1999 |
San Diego | $250 | Jan-07-1999 |
Los Angeles | $300 | Jan-08-1999 |
Boston | $700 | Jan-08-1999 |
我们就打入,
SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name
结果:
store_name | SUM(Sales) |
Los Angeles | $1800 |
San Diego | $250 |
Boston | $700 |
例子2
SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list";
例子如下:
一个销售表sales:
workername | salemoney |
a1 | 3000 |
a2 | 2000 |
a3 | 1000 |
a2 | 3000 |
1.sql要求,查询每个员工的总销售总额:
SELECT workername,SUM(salemoney) FROM sales GROUP BY workername
2.sql要求,查询总销售额最大的员工姓名跟销售额:
SELECT workername,MAX(salemoney) FROM sales GROUP BY workername
3.sql要求,查询总销售额大于等于2000的员工姓名跟销售额:
SELECT workername, SUM(salemoney) FROM sales GROUP BY workername HAVING SUM(salemoney)>=2000
何时使用GROUP BY(心得)
一般业务要求出现 大于,等于,小于,最大,最小等范围词语,具体是使用where 还是having,要看是过滤行还是分组,也可同时使用where 和having
找出具有两个订单以上的顾客
SELECT cust_id ,COUNT(*) AS orders FROM Orders GROUP BYcust_id HAVING COUNT(*) >=2
找出具有两个以上产品且其价格大于等于4的供应商
SELECT vend_id ,count(*) AS num_prods FROM Products WHERE pro_price>=4 GROUP BY vend_id HAVING COUNT(*)>=2
先找出 产品价格大于等于4的产品 再以供应商分组,返回供应商产品个数信息,一般having再以返回的信息作为条件对分组在进行过滤
group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面 ,在select 列表中指定的列要么是group by 子句中指定的列,要么包含聚组函数
说白了就是select 所取出的字段 在分组里必须有,但是排除被合计的字段,比如
select a,b,c from table group by a,b,c这里 group by 里 必须有a,b,c
如果改成 select a,b,sum(c) from table groupby a,b,这里 因为c被合计了 所以按照a,b分组就可以了
同样的合计函数还包括max count 等等!
group by 除聚合函数外,如sum,其他函数,如decode,所修饰的数据库字段都要在group by 后体现到
假如一个公司下面有五个工厂,以公司id和工厂id进行group by,那么出现的结果将会以最小单位来分组,即五条公司名称相同但是工厂名称不同的记录,
Having子句
在select 语句中可以使用group by 子句将查询出的行划分成组,然后,使用聚组函数返回每一个组的汇总信息,另外,可以使用having子句过滤分组,规定包括哪些分组,排除哪些分组,。
当在gropu by 子句中使用having 子句时,查询结果中只返回满足having条件的组。在一个sql语句中可以有where子句和having子句。having 与where 子句类似,均用于设置限定条件,where过滤指定的行,having过滤指定的组,having 子句可以出现聚组函数,在where 子句中不能使用聚组函数。
SELECT deptno,SUM(sal) FROM emp
WHERE sal>1200
GROUP BY deptno
HAVING SUM(sal)>8500
ORDER BY deptno;
一般使用GROUP BY子句时,应该也给出ORDER BY 子句,这是保证数据正确排序的唯一方法
where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行。
having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组。
查询每个部门的每种职位的雇员数
SELECT deptno,job,COUNT(*) FROM emp GROUP BY deptno,job
查出每个供应商的产品数
SELECT vend_id ,COUNT(*) AS num_prods FROM Products GROUP BY vend_id
结果:
vend_id | num_prods |
BRS01 | 3 |
DLL01 | 4 |
FNG01 | 2 |
GRUOP BY 子句指示DBMS按vend_id排序并分组数据,这就会对每个vend_id而不是整个表计算num_prods一次,从输出中可以看出,供应商BRS01有3个产品,供应商DLL01有4个产品,而供应商FNG01有2个产品