SQL基础语法(4)

这篇blog是SQL基础语法，只是作为我平时查阅和备份，仅适合初学者学习阅读，高手绕行。
SQL基础语法（1）访问http://fuchangle.iteye.com/blog/1772395
SQL基础语法（2）访问http://fuchangle.iteye.com/blog/1772523
SQL基础语法（3）访问http://fuchangle.iteye.com/blog/1772779

多表连接查询和子查询
很多时候，我们需要选择的数据并不是来自一个表，而是来自多个表，这就

需要使用多表连接查询。例如对于上面的student_table和teacher_table两

个数据表，如果我们希望查询出所有学生以及他的老师名字，这就从两个表

中取数据。
多表连接查询有两种规则，较早的SQL92规范支持如下几种多表连接查询：
1.等值连接。
2.非等值连接。
3.外连接。
4.广义笛卡儿积。
SQL99规则提供了可读性更好的多表连接语法，并提供更多类型的连接查询

，SQL99支持如下几个多表连接查询：
1.交叉连接。
2.自然连接。
3.使用using子句连接。
4.使用on子句的连接
5.全外连接或左右连接。

SQL92的多表连接语法比较简洁，这种语法把多个数据表放在from之后，多
个表之间以逗号隔开；连接条件放在where之后，与查询条件之间用and逻辑
运算符连接。如果连接条件要求两列值相等，则称为等值连接；否则称为非
等值连接，如果没有任何连接条件，则被称为广义笛卡儿积。SQL92中多表
连接查询的语法格式如下:

select column1,column2...
from table1,table2...
[where join_condition]

多表连接查询中可能出现两个或多个数据列具有相同的列名，则需要在这些
同名列之间使用表名或表别名前缀作为限制，避免系统混淆。
注：实际上所有的列都可以增加表名或前缀，只是进行单循环时绝不可能出
现同名列，所以系统不可能混淆。所以通常省略表名前缀。
如下SQL语句查询出所有学生资料以及对应的老师姓名：

select s.*, teacher_name
--指定多个数据表，并指定表列名
from studnet_table s, teacher_table t
--使用where指定连接条件
where s.java_teacher = t.teacher_id;

如果求广义笛卡尔积，则where子句后没有任何连接条件，则广义笛卡尔积
的结果会有n*m条记录。只要把where后的连接条件去掉就可以得到广义笛卡
尔积，如:

select s.*, teacher_name
--指定多个数据表，并指定表列名
from studnet_table s, teacher_table t;
与此类似的，非等值连接的执行如下：
select s.*, teacher_name
--指定多个数据表，并指定表列名
from studnet_table s, teacher_table t
--使用where指定连接条件
where s.java_teacher > t.teacher_id;

如果还需要对记录进行过滤，则将过滤条件和连接条件使用and连接，如下

select s.*, teacher_name
--指定多个数据表，并指定表列名
from studnet_table s, teacher_table t
--使用where指定连接条件
where s.java_teacher = t.teacher_id and student_name is not null;

MySQL不支持SQL92中左外连接、右外连接，但我们还有必要了解一下SQL92
中左外连接。右外连接，SQL92中的外连接就是在连接条件的列名后增加括
号包起来的外连接符（+或*，不同的数据库有一定的区别），当外连接符出
现在左侧时称为左外连接符，出现在右侧时则称为右连接符。
除此之外，还有一种自连接：自连接只是连接的一种用法，并不是一种连接
类型，不管SQL92，还是SQL99都可以使用自连接查询。

create table emp_table
(
	emp_id int auto_increment primary key,
	emp_name varchar(255),
	manager_id int,
	foreign key(manager_id) references emp_table(emp_id)
);

insert into emp_table values(null, '唐僧', null);
insert into emp_table values(null, '孙悟空', 1);
insert into emp_table values(null, '猪八戒', 1);
insert into emp_table values(null, '沙僧', 1);

所谓自连接就是需要把一个表当成两个表来用，这就需要为一个表起两个别
名，而且查询中用的所有数据列都要加表别名前缀，因为两个表的数据列完
全相同，例：

select emp.emp_id, emp.emp_name 员工名, mgr.emp_name 经理名
from emp_table emp, emp_table mgr
where emp.manager_id = mgr.emp_id;

SQL99的连接查询
SQL99的连接查询与SQL92的连接查询原理基本相似，不同是SQL99连接查询
的可读性更强：查询用的多个数据表显式使用xxx join连接，而不是直接依
次排列在from之后，from后只需要放一个数据表；连接条件不再放在where
之后，而是提供了专门的连接条件子句。
交叉连接：cross join，交叉连接效果是SQL92中的广义笛卡儿积，所以交
叉连接无需使用任何连接条件，例：
自然连接：natural join，自然连接表面上看起来也无需指定连接条件，但
自然连接是有连接条件的，自然连接会以连个表中同名列作为连接条件；如
果两个表中没有同名列，则自然连接与交叉连接效果完全一样-因为没有连
接条件。

select s.*, teacher name
from student_table s
natural join teacher_table t;

on子句连接：这是最常用的连接方式，SQL99语法的连接条件放在on子句中
指定，而且每个on子句只指定一个连接条件。这意味着：如果需要进行N表
连接，则需要有N-1个join...on对，如下：

select s.*, teacher_name
from student_table s
join teacher_table t
on s.java_teacher = t.teacher_id;

使用on子句的连接完全可以代替SQL92中的等值连接、非等值连接，因为on
子句的连接条件除了等值条件之外，也可以是非等值条件

select s.*, teacher_name
from student_table s
join teacher_table t
on s.java_teacher > t.teacher_id;

子查询
子查询就是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。对
于一个普通查询语句而言，子查询可以出现在两个位置：
1.出现在from语句后当成数据表，这种用法也被称为行内视图，因为该子查
询实质上就是一个临时视图。
2.出现在where条件后作为过滤条件的值。
使用子查询时有如下几个注意点：
1.子查询要用括号括起来。
2.子查询当成数据表时(出现在from后)，可以为该子查询起别名，尤其是要
作为前缀来限定数据列时，必须给子查询起别名。
3.子查询当过滤条件时，将子查询放在比较运算符的右边，这样可以增强查
询的可读性。
4.子查询当过滤条件时，单行子查询使用单行运算符，多行子查询使用多行
运算符。

对于子查询当数据表是完全把子查询当数据表来用，只是把之前的表名变成
查询(也可以为子查询起别名)，其他部分与普通查询没有任何区别，下面
SQL语句示范了把子查询当成数据表的用法：

select *
from (select * from student_table) s
where s.java_teacher > 1;

子查询当数据表的用法更确切地说当成视图，我们可以吧上面SQL语句理解
成在执行查询时创建了一个临时视图，该视图名为t，所以这种临时创建的
视图也被称为行内视图，理解了这种子查询的实质后，不难知道这种子查询
完全可以代替查询语句中的数据表，包括在多表连接查询中使用这种子查询

还有一种情况，我们可以把子查询当成where条件的值，如果子查询返回单
行、单列值,则被当成一个标量使用，也就可以使用单行记录比较运算符。
如：

select *
from student_table
where java_teacher >
--返回单行、单列的子查询可以当成标量值使用
(select teacher_id
from teacher_table
where teacher_name = 'Yeeku');

如果子查询返回多个值，则需要使用in、any和all等关键字，in可以单独使
用，此时可以把子查询返回的多个值当成一个值列表，如下SQL语句所示:

select * 
from student_table
where student_id in
(select teacher_id
from teacher_table);

上面查询语句中的子查询将返回多个值，这多个值将被当成一个值列表，只
要sudent_id与该值列表中任意一个相等，就可以选出这条记录。