mysql自连接查询的sql语句 数据库自连接查询

　　若一个数据库同时涉及两个以上的表，则称之为连接查询。连接查询包括内连接查询、自连接查询、外连接查询。

　　1.等值与非等值连接查询

　　连接查询的一般格式为：select 表名1.列名,表名2.列名 from 表名1，表名2 where 表名1.列名 <比较运算符> 表名2.列名，其中比较运算符包括=、>、<、>=、<=、！=等。内连接又包含等值连接，非等值连接，自然连接。

　　建立两张表，产品表product和产品分类表category。产品表product包括产品号pno,产品分类号cno，产品名称pname,产品分类表category包括分类号cno和分类名称cname，接下来的示例均基于这两张表实现。先建表并插入数据。

create table category(
    cno int not null,
    cname varchar(20) not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table product(
    pno int not null,
    cno int not null,
    pname varchar(20) not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into product values(1,11,'老人与海');
insert into product values(1,12,'森马服装');
insert into product values(2,13,'老干妈');
insert into product values(2,14,'剪刀');

insert into category values(11,'书本');
insert into category values(12,'衣物');
insert into category values(13,'食品');
insert into category values(13,'食品');
insert into category values(15,'办公用品');

 运算符为“=”的连接称为等值连接，等值连接从category和product中取出属性值相等的那些元素。

等值连接
select product.*,category.* from product,category where product.cno = category.cno;

　　等值连接查询到的结果：

　　属性名之前都加上前缀，是为了避免混淆。如果属性名再参加各连接的表中是唯一的，则可以忽略前缀。

　　非等值连接是运算符除了“=”以外的连接方式，不做示例。

　　自然连接是特殊的等值连接。它取出属性相同的元素，并且在结果中把重复的属性列去掉。

自然连接
select pno,product.cno,pname,cname from product,category where product.cno = category.cno;

　　自然连接查询到的结果：

　　

　   2.自连接

　　连接操作不仅可以在两个表之间进行，也可以是一个表与自己进行连接，称为表的自身连接。

自连接
select FIRST.pno,SECOND.cno from product FIRST,product SECOND where FIRST.pno=SECOND.pno;

　　自连接查询结果:

　　3.外连接

　　外连接包括左外连接，右外连接，全外连接。

　　在等值连接中，属性值不相同的那些元素被舍弃了。把被舍弃的元素保存下来，就叫做外连接。只保留左表中被舍弃的元素，叫做左外链接。

左外连接
select pno,product.cno,pname,cname from product left outer join category on(product.cno=category.cno);

　　左外连接的查询结果:

　　可以看出，多了cno=14这一项，在category中没有对应的值，所以对应的cname的值为null。

　　右外连接保留右表中被舍弃的元素。

右外连接
select pno,category.cno,pname,cname from product right outer join category on(product.cno=category.cno);

　　右外连接的查询结果：

全外连接则是保留所有本应删除的元素。mysql不支持全连接，可以用union all将左连接和右连接结合起来。

全外连接
select product.*,category.* from product left outer join category on (product.cno=category.cno)
UNION ALL
select product.*,category.* from product right outer join category on (product.cno=category.cno);

　　全外连接的查询结果：