SELECT查询的应用（二）

JOIN子句的用法
　　JOIN是很好用的一个SELECT查询的子句，虽然有了它以后你的查询语句变得非常的长，写错的概率也增大了；可是四分之一柱香以后，你就会彻底的爱上它，因为我决定把它的优点全部展示给你！
　　为了便于读者接受，我们还是先来建一个数据库吧。

#货物表
create table goods (
id int auto_increment,
code varchar(10) not null,
name varchar(20),
spec varchar(40),
grade varchar(10),
primary key(id) );

#业务员表
create table staff (
id int auto_increment,
name varchar(20),
depart int not null,
primary key(id) );

#部门表
create table depart (
id int auto_increment,
name varchar(20),
primary key(id) );

#客户表
create table customer (
id int auto_increment,
name varchar(60) not null,
association varchar(20),
tel varchar(30),
fax varchar(30),
addr varchar(80),
postcode varchar(10),
email varchar(40),
primary key(id) );

#销售记录表
create table sales (
id int auto_increment,
inputime datetime not null,
staff int not null,
customer int not null,
good_code varchar(10) not null,
amount decimal(10,2) not null default 0.00,
price decimal(8,2) not null default 0.00,
memo text,
primary key(id) );

#插入数据
insert into goods (code,name,spec,grade)
values (@#A0001@#,@#显示器@#,@#PHILIPS 105B@#,@#优@#);
insert into goods (code,name,spec,grade)
values (@#A0002@#,@#显示器@#,@#PHILIPS 107B@#,@#优@#);
insert into depart (name)
values (@#业务一部@#);
insert into depart (name)
values (@#业务二部@#);
insert into depart (name)
values (@#业务三部@#);
insert into staff (name,depart)
values (@#王老五@#,2);
insert into staff (name,depart)
values (@#张三@#,3);
insert into staff (name,depart)
values (@#李四@#,1);
insert into staff (name,depart)
values (@#赵二楞@#,3);
insert into customer (name,association,tel,fax,email)
values (@#丁胖子电脑公司@#,@#丁胖子@#,@#12345678@#,@#12345679@#,@#fatding@fat.org@#);
insert into sales (inputime,staff,customer,good_code,amount,price)
values (now(),2,1,@#A0001@#,10,1200.00);
insert into sales (inputime,staff,customer,good_code,amount,price)
values (now(),6,2,@#A0001@#,10,1200.00);

　　想必不用我一个一个字段地解释，其中用的字段名字都是很普通的啊。我们这样做的目的是为了在庞大的销售记录表中不要直接记录货物的名称、规格、客户的名称、业务员的姓名等重复性的东西——那样太浪费。我们把所有可能牵扯到的货物、业务员、客户等等分别做为一个表，他们在各自的表中有一个唯一标识的编号，而在销售记录表中，就只填写这个编号。
　　在查看销售记录的时候，要把其中的货物代码转换成它对应的货物名称和规格、等级等等；还有把客户的编号转换成客户的名称；业务员的编号换成他的名字……。我们就用JOIN子句，注意看下面这条查询：

SELECT sales.id,sales.inputime,sales.amount,sales.price,sales.memo,
staff.name as staff,depart.name as depart,customer.name as customer,
goods.name as good_name,goods.spec as good_spec,goods.grade as good_grade
FROM sales INNER JOIN staff ON staff.id=sales.staff
INNER JOIN depart ON depart.id=staff.depart
INNER JOIN customer ON customer.id=sales.customer
INNER JOIN goods ON goods.code=sales.good_code
ORDER BY inputime desc

注意这是不是几条，是一条SELECT语句！！嗯，比较长。由于查询的结果也比较长，写出来大家也不一定能看清楚，所以就请自己试一下吧。查询的结果，各个字段对应的分别是：

inputime 录入的时间
amount 销售数量
price 价格
memo 备注
staff 业务员姓名
depart 业务员所属部门
customer 客户名称
good_name 货物名称
good_spec 货物规格
good_grade 货物等级

　　当sales表中的staff字段的值，在staff表中找不到对应的业务员记录时，这可能是两钟原因造成的：一为误删除了这个业务员，二为这一条销售记录填入sales表时出现了失误。在这种情况下，使用上面的一条查询就不能将这一条记录取出。刚才我建的数据库sales表中故意留了一条业务员ID是6的记录——业务员表中没有ID是6的！所以按照上面的那条查询，就没有查询到这条记录。如果要避免这种情况发生，可以使用“左连接”：无论匹配与否，取出左侧的表中的所有记录，不能匹配的右侧的表的记录一律为NULL。上述的查询应改为：

SELECT sales.id,sales.inputime,sales.amount,sales.price,sales.memo,
staff.name as staff,depart.name as depart,customer.name as customer,
goods.name as good_name,goods.spec as good_spec,goods.grade as good_grade
FROM sales LEFT JOIN staff ON staff.id=sales.staff
LEFT JOIN depart ON depart.id=staff.depart
LEFT JOIN customer ON customer.id=sales.customer
LEFT JOIN goods ON goods.code=sales.good_code
ORDER BY inputime desc

　　再用这个语句查询一次，是不是比刚才查到的多了一条业务员和所属部门是NULL的记录？业务员被误删是应该绝对禁止的，填写sales表时的失误也应该避免。但是一旦发生了，就应该不使它影响到整个销售记录数据的正常存取。所以用“左连接”是必要的。

　　时间仓促，草草成稿；如有不确，务请指正。

原文转自：http://www.ltesting.net