数据库高级数据过滤之SQL操作符与SELECT子句

高级数据过滤

AND操作符

要通过不止一个列进行果过滤,可以使用AND操作符对ＷＨＥＲＥ子句附加条件，用在ＷＨＥＲＥ子句中的关键字

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个产品