第四章使用SQL语句(二)-优快云博客

本文深入讲解了SQL查询的各种高级技巧，包括数据分组、连接查询、子查询、合并查询等核心概念及其应用方法。通过实例演示了如何使用GROUP BY、HAVING、各种类型的JOIN以及子查询来解决复杂的数据分析问题。

4.4数据分组

在关系数据库中，数据分组是通过使用group by子句，分组函数以及Having子句共同实现的。其中group by 子句用于指定要分组的列，而分组函数则用于显示统计结果（count,avg,sum等），而having子句则用于限制分组显示结果。

4.4.1分组函数

分组函数用于统计表的数据，又称为多行函数，分组函数要与group by 子句结合使用。

MAX，MIN，AVG，SUM，COUNT，VARIANCE，STDDEV

当使用分组函数时，分组函数只能出现在选择列表，ORDER BY和HAVING子句中，而不能出现在WHERE和GROUP BY子句中。

使用分组函数注意事项：

○当使用分组函数时，除了函数COUNT（*）之外，其它分组函数都会忽略NULL行

○当执行SELECT语句时，如果选择列表同时包含列，表达式和分组函数，那么这些列和表达式必须出现在GROUP BY子句中。

○当使用分组函数时，在分组函数中可以指定ALL和DISTINCT选项

4.4.2GROUP　BY和HAVING

语法如下：

SELECT column,group_function FROM table

[WHERE condition] [GROUP BY group_by_expression]

[HAVING group_condition];

使GROUP BY子句，WHERE子句和分组函数需注意以下：

● 分组函数只能出现在选择列表，HAVING子句和ORDER BY子句中

● 如果在SELECT语句中同时包含有GROUP BY,HAVING以ORDER BY子句，则必须将ORDER BY子句放在最后。默认情况下，当使用GROUP BY子句统计数据时，会自动按照分组列的升序方式显示统计结果。通过使ORDER BY子句，可以改变数据分组的排序方式。

● 如果选择列表包含有列，表达式和分组函数，那么这些列和表达式必须出现在GROUP BY子句中，否则会报错。

● 当限制分组显示结果时，必须要使用HAVING子句，而不能在WHERE子句中使用分组函数限制分组显示结果，否则会显示错误信息。

4.4.3 ROLLUP和CUBE和GROUPING和GROUPING SETS

这里引用一篇ITPUB中高手整理的关于分析函数的文章(具体不贴了)

4.5连接查询

连接查询是指基于两个或两个以上表或视图的查询。

１．当使用连接查询时，必须在FROM子句后指定两个或两个以上的表

２．当使用连接查询时，应该在列名前加表名作为前缀。如果不同表之间列名不同可以不必加，否则会产生二义性。

4.5.1 相等连接 (=)

相等连接是指使用相等比较符(=)指定连接条件的连接查询，该种连接查询主要用于检索主从表之间的相关数据。

4.5.2不等连接

不等连接是指在连接条件中使用除相等比较符外的其他比较操作符的连接查询，并且不等连接主要用于在不同表之间显示特定范围的信息。

4.5.3自连接

自连接是指在同一张表之间的连接查谒，它主要用于在自参照表上显示上下级关系或者层次关系。自参照表是指在不同列之间具有参照关系或主从关系的表。

例如：SELECT mangage.ename FROM emp manager,emp worker

WHERE manger.empno=worker.mgr

4.5.4内连接和外连接

内连接用于返回满足连接条件的记录;

外连接是内连接的扩展，它不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的记录。语法格式如下：（此部分也是最较用的）

SELECT　table1.column,table2.column

FROM table1 [INNER|LEFT|RIGHT|FULL] JOIN table2 ON table1.column1=table2.column1

INNER JOIN 表示内连接

LEFT　JOIN　表示左外连接

RIGHT　JOIN　表示右外连接

FULL　JOIN　表示完全外连接

ON子句用于指定连接条件。

注意：如果使用FROM子句指定内，外连接，则必须要使用ON子句指定连接条件;如果使用(+)操作符指定外连接，则必须使用WHERE子句指定连接条件。

表数据：

Create table a (id int,deptname varchar2(10));

Alter table a add constraint primary key pk_a (id);

Create table b (id int,did int,name varchar2(20));

Alter table b add constraint primary key pk_b(id);

Alter table b add constraint foreign key fk_b(id) references a(id);

SQL> select * from a;

ID DEPTNAME

---------- ----------

1 it

2 hr

3 fs

SQL> select * from b;

ID DID NAME

---------- ---------- --------------------

1 1 zxf

2 1 lbb

3 2 dds

１．内联接

内连接用于返回满足连接条件的所有记录。

SQL> select * from a,b where a.id=b.did;

ID DEPTNAME ID DID NAME

---------- ---------- ---------- ---------- -------

1 it 1 1 zxf

1 it 2 1 lbb

2 hr 3 2 dds

SQL> select a.id,a.deptname,b.id,b.did,b.name from a inner join b on a.id=b.did;

ID DEPTNAME ID DID NAME

---------- ---------- ---------- ---------- --------------------

1 it 1 1 zxf

1 it 2 1 lbb

2 hr 3 2 dds

以上两种写法的结果是相同的。

从Oracle9i开始，如果主表的主键列和从表的外部键列名称相同，可以使用NATURAL JOIN关键字自动执行内连接操作。（下面的例子是不对的，它们都用了相同的ID列表作为联接了，但这两列没有关系，：（）

SQL> select * from a natural join b;

ID DEPTNAME DID NAME

---------- ---------- ---------- -------

1 it 1 zxf

2 hr 1 lbb

3 fs 2 dds

２．左外连接

左外连接是指通过指定 LEFT[OUTER] JOIN选项来实现的。当使用左外连接时，不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的连接操作符的左边表其它行。

SQL> select a.id,a.deptname,b.id,b.did,b.name from a left join b

2 on a.id=b.did;

ID DEPTNAME ID DID NAME

---------- ---------- ---------- ---------- --------------------

1 it 1 1 zxf

1 it 2 1 lbb

2 hr 3 2 dds

3 fs

３．右外连接

右外连接是通过指定RIGHT [OUTER] JOIN选项来实现的。当使用右外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的连接操作符右连表的所有行。

（下面的例子有点不太好，凑和看吧，没有体现出右边表的所有行的概念）

SQL>

1 SELECT a.id,a.deptname,b.id,b.did,b.name

2 FROM a right join b

3* ON a.id=b.did

SQL> /

ID DEPTNAME ID DID NAME

---------- ---------- ---------- ---------- ------------

1 it 1 1 zxf

1 it 2 1 lbb

2 hr 3 2 dds

４．完全外连接

完全外连接是通过指定FULL [OUTER] JOIN 选项来实现的，当使用完全外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的所有其它行。

SQL> SELECT a.id,a.deptname,b.id,b.did,b.name

2 FROM a FULL JOIN b

3 on a.id=b.did;

ID DEPTNAME ID DID NAME

---------- ---------- ---------- ---------- -------------

1 it 1 1 zxf

1 it 2 1 lbb

2 hr 3 2 dds

3 fs

５．使用（＋）操作符

Oracle9i前用操作符(+)来完成外连接，Oracle9i后用OUTER JOIN来执行。

SQL> SELECT a.id,a.deptname,b.id,b.did,b.name

2 FROM a,b

3 WHERE a.id(+)=b.did;

ID DEPTNAME ID DID NAME

---------- ---------- ---------- ---------- --------------------

1 it 1 1 zxf

1 it 2 1 lbb

2 hr 3 2 dds

SQL> SELECT a.id,a.deptname,b.id,b.did,b.name

2 FROM a,b

3 where a.id=b.did(+);

ID DEPTNAME ID DID NAME

---------- ---------- ---------- ---------- --------------------

1 it 1 1 zxf

1 it 2 1 lbb

2 hr 3 2 dds

3 fs

▲（＋）操作符只能出现在WHERE子句中，并且不能与OUTER　JOIN语法同时使用

▲当使用（＋）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必段在所有条件中都包含(+)操作符。

▲（＋）操作符只适用于列，而不能用在表达式上。

▲（＋）操作符不能与OR和IN操作符一起使用。

▲（＋）操作符只能用于实现左外连接和右外连接，而不能实现完全外联接。

▲（＋）要放在行数较少的一端

4.6子查询

子查询是指嵌入在其他SQL语句中的SELECT语句，也称为嵌套查询。

注意：当在DDL语句中引用子查询时，可以带有ORDER　BY子句，但是当在WHERE子句，SET子句中引用子查询时，不能带有ORDER BY子句.

○通过在INSERT或CREATE TABLE语句中使用子查询，可以将源表数据Inert到目标表中

○通过在CREATE VIEW或CREATE MATERIALIZED VIEW中使用子查询，可以定义视图或实体化视图所对应的SELECT语句。

○通过在UPDATE语句中使用子查询可以修改一列或多列数据

○通过在WHERE，HAVING，START WITH子句中使用子查询，可以提供条件值。

4.6.1 单行子查询

只返回一行数据的子查询语句01427错误号

SQL> select * from b where did=(select id from a where deptname='it');

ID DID NAME

---------- ---------- --------------------

1 1 zxf

2 1 lbb

如果子查询返回值不唯一会报错如下：

SQL> SELECT * FROM b WHERE did=(SELECT id FROM a);

SELECT * FROM b WHERE did=(SELECT id FROM a)

第 1 行出现错误:

ORA-01427: 单行子查询返回多个行

4.6.2 多行子查询

多行子查询是指返回多行数据的子查询语句。在多行子查询中必须要使用多行比较符(IN,ALL,ANY).

4.6.3 多列子查询

返回多列数据的子查询语句，当多列子查询返回单行数据时，在WHERE子句中可以使用单行比较符，当多列子查询返回多行数据时，在WHERE子句中必须使用多行比较符（IN,ANY，ALL）。

4.6.4 其它子查询 (在创建视图中，FROM中等)

4.7合并查询

UNION,UNION ALL,INTERSECT,MINUS

语法：

SELECT 语句1 [UNION,UNION ALL,INTERSECT,MINUS] SELECT 语句2

UNION:会去除结果集中的重复行

UNION ALL:不会取消重复值

INTERSECT:用于获取两个结果集的交集

MINUS:两个结果集的差集

4.8其他复杂查询

1.层次查询

当表具有层次结构数据时，通过使用层次查询可以更直观的显示数据结果，并显示其数据之间的层次关系。

此部分在实际开发中应用较多，而且相关也比较复杂，建议多找些相关的资料进行一下实验，我对于这部分以前就不知道，丢人L现在多少了解一些，信息都是在网上看到的，我现在很喜欢ITPUB，里面高手真多，向他们看齐！

2.使用CASE表达式

在SQLSERVER与Oracle中均支持CASE表达式

CASE when … then ….

When …. Then …

Else ……

END;

1. 倒途查询

FLASHBACK QUERY

SELECT ename,sal FROM emp AS OF TIMESTAMP to_timestamp(‘’)

Exec DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER();

2. 使用WITH子句重用子查询

对于多次使用相同子查询的复杂查询语句来说，用户可能会将查询语句分成两条语句执行。第一条语句将子查询结果存放到临时表，第二条查询语句使用临时表处理数据。从Oracle9i开始，通过WITH子句可以给子查询指定一个名称，并且使得在一条语句中可以完成所有任务，从而避免了使用临时表。

从网上找一个关于 with clause的使用及说明：

Oracle with语句示例

WITH a AS (SELECT * FROM bd_member WHERE ROWNUM<10),

b AS (SELECT * FROM tp_trade_card)

select A.MEMBER_NAME,

B.CARD_NO

from A,

where A.BD_MEMBER_ID = B.BD_MEMBER_ID

Google Search: Oracle +with 视图就可以得到结果...

Oracle WITH clause

Oracle Tips by Burleson Consulting

About Oracle WITH clause

Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.

The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:

• The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.

• Formally, the “WITH clause” is called subquery factoring

• The SQL “WITH clause” is used when a subquery is executed multiple times

• Also useful for recursive queries (SQL-99, but not Oracle SQL)

To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query.

We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.

The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH

subquery_name

(the aggregation SQL statement)

SELECT

(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH clause”:

WITH

sum_sales AS

select /*+ materialize */

sum(quantity) all_sales from stores

number_stores AS

select /*+ materialize */

count(*) nbr_stores from stores

sales_by_store AS

select /*+ materialize */

store_name, sum(quantity) store_sales from

store natural join sales

SELECT

store_name

FROM

store,

sum_sales,

number_stores,

sales_by_store

where

store_sales > (all_sales / nbr_stores)

;

Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.

It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.

To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy

WITH

subquery_name

(the aggregation SQL statement)

SELECT

(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”:

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/67668/viewspace-310327/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/67668/viewspace-310327/