精通SQL【经典SQL语句大全】(2)

一、连接的概念：

是指一个从多个表中的数据进行的查询。连接一般使用表的主键和外键。

连接类型：

等连接、不等连接、外连接、自连接

二、Cartesian product ：

指的是当JOIN条件被省略或无效时，所有表的行(交叉)都被SELECT出来的现象。

Cartesian product可以产生大量的记录，除非是你有意如此，否则应该加上某种条件限制。

SQL> SELECT name, last_name

2 FROM s_dept, s_emp;

300 rows selected. 其中一个表12行，一个表25行。

三、简单连接查询：

SELECT table.column, table.column...

FROM table1, table2

WHERE table1.column1 = table2.column2;

如：SQL> SELECT s_st_name, s_emp.dept_id,

2 s_

3 FROM s_emp, s_dept

4 WHERE s_emp.dept_id = s_dept.id;

注意：表前缀的重要性：

SQL> SELECT s_dept.id ”Department ID”,

2 s_region.id ”Region ID”,

3 s_ ”Region Name”

4 FROM s_dept, s_region

5 WHERE s_dept.region_id = s_region.id;

在WHERE 段中，如果没有前缀，两个表中都有ID字段，就显得的模棱两可，AMBIGUOUS。

这在实际中应该尽量避免。

WHERE 字段中，还可以有其他的连接条件，如在上例中，加上：

INITCAP(s_st_name) = ’Menchu’;

再如：WHERE s_emp.dept_id = s_dept.id AND s_dept.region_id = s_region.id AND s_mission_pct > 0;

四、表别名ALIAS：

SQL【经典SQL语句大全】

1、使用别名进行多表查询 。

2、仅在这个查询中生效，一旦用了表别名，就不能再用表的原有的名字进行连接。

实例：

SQL> SELECT ”Customer Name”,

2 c.region_id ”Region ID”,

3 ”Region Name”

4 FROM s_customer c, s_region r

5 WHERE c.region_id = r.id;

别名最多可以30个字符，但当然越少越好。最好也能容易识别。

五、非等连接

非等连接一般用在没有明确的等量关系的两个表；

最简单的说：非等连接就是在连接中没有“=”出现的连接。

SQL> SELECT e.ename, e.job, e.sal, s.grade

2 FROM emp e, salgrade s

3 WHERE e.sal BETWEEN s.losal AND s.hisal;

说明：Create a non-equijoin to evaluate an employee’s salary grade. The salary 必须在另一个表中最高和最低之间。 其他操作符<= >= 也可以实现，但是BETWEEN是非常简单实用的。

BETWEEN ....AND是指闭区间的，这点要注意 ，请大家测试。

六、外连接

语法结构：SELECT table.column, table.column

FROM table1, table2

WHERE table1.column = table2.column(+);

实例：

SQL> SELECT st_name, e.id,

2 FROM s_emp e, s_customer c

3 WHERE e.id (+) = c.sales_rep_id

4 ORDER BY e.id;

显示.....，即使有的客户没有销售代表。

* 可以理解为有+号的一边出现了NULL，也可以做为合法的条件。

外连接的限制：

1、外连接符只能出现在信息缺少的那边。

2、在条件中，不能用 IN 或者 OR做连接符。

七、自连接

同一个表中使用连接符进行查询；

FROM 的后面用同一个表的两个别名。

实例：

SQL> SELECT st_name||’ works for ’||

2 st_name

3 FROM s_emp worker, s_emp manager

4 WHERE worker.manager_id = manager.id;

意味着：一个员工的经理ID匹配了经理的员工号，但这个像绕口令的连接方式并不常用。

以后我们会见到一种 子查询：

select last_name from s_emp where salary=(select max(salary) from s_emp)

也可以看作是一种变向的自连接，但通常我们将其

SQL【经典SQL语句大全】

课程 四 组函数

本课重点：

1、了解可用的组函数

2、说明每个组函数的使用方法

3、使用GROUP BY

4、通过HAVING来限制返回组

注意：以下实例中标点均为英文半角

一、概念：

组函数是指按每组返回结果的函数。

组函数可以出现在SELECT和HAVING 字段中。

GROUP BY把SELECT 的结果集分成几个小组。

HAVING 来限制返回组，对RESULT SET而言。

二、组函数：(#号的函数不做重点)

1、AVG

2、COUNT

3、MAX

4、MIN

5、STDDEV #

6、SUM

7、VARIANCE #

语法：

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

实例1：一个混合实例，说明所有问题：

SQL> SELECT AVG(salary), MAX(salary), MIN(salary),

2 SUM(salary)

3 FROM s_emp

4 WHERE UPPER(title) LIKE ’SALES%’;

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)

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

1476 1525 1400 7380

说明：很多函数，我们在讲函数的已经向大家介绍过，但在此为何叫分组函数呢，主要是因为它们可以与GROUP BY来形成对不同组的计算，相当于在很多值中进行挑选。

* MIN MAX函数可以接任何数据类型。

如果是MIN(last_name), MAX(last_name)，返回的是什么呢？

千万记住，不是指LAST_NAME的长度，而是指在FIRST字母的前后顺序，第一个相同，然后比较第二个，如：xdopt > cssingkdkdk > adopt > acccc

实例2：

SQL【经典SQL语句大全】

SQL> SELECT COUNT(commission_pct)

2 FROM s_emp

3 WHERE dept_id = 31;

返回所有非空行个数

三、GROUP BY的应用：

先看一个简单实例：

SQL> SELECT credit_rating, COUNT(*) ”# Cust”

2 FROM s_customer

3 GROUP BY credit_rating;

注意这里别名的应用，复习一下从前的课程，加了引号后，就可以用特殊字符，但也仅有三个：#$_，什么对象的名字都如此。当然空格也是可以的。

复杂实例：

SQL> SELECT title, SUM(salary) PAYROLL

2 FROM s_emp

3 WHERE title NOT LIKE ’VP%’

4 GROUP BY title

5 ORDER BY SUM(salary);

这里要注意一下几个CLAUSE的先后次序。

WHERE在这里主要是做参与分组的记录的限制。

**另外，如果要选取出来一个不加组函数的列，如上面的TITLE，就要把这个列GROUP BY !否则要出错的!信息为：ERROR at line 1:

ORA-00937: not a single-group group function

理论很简单，如果不GROUP BY TITLE，显示哪一个呢？这个在试题中经常出现。

结论：不加分组函数修饰的列必定要出现在GROUP BY 里。

错误实例：

SQL> SELECT dept_id, AVG(salary)

2 FROM s_emp

3 WHERE AVG(salary) > 2000

4 GROUP BY dept_id;

WHERE AVG(salary) > 2000

*

ERROR at line 3: