实际在数据库里面演示一下把
在mysql的practice数据库中建几个表来学习一下
use practice;
create table MyTable1(
ID varchar(5) primary key,
Name varchar(20) not null);
insert into MyTable1(ID,Name) values("1","经验");
insert into MyTable1(ID,Name) values("2","娜娜");
insert into MyTable1(ID,Name) values("3","大姐");
insert into MyTable1(ID,Name) values("5","测试1");
create table MyTable2(
ID varchar(5) primary key,
Name varchar(20) not null);
insert into MyTable2(ID,Name) values("1","经验");
insert into MyTable2(ID,Name) values("2","包包");
insert into MyTable2(ID,Name) values("3","胖胖");
insert into MyTable2(ID,Name) values("4","测试2");
mysql> select * from MyTable1;
+----+---------+
| ID | Name |
+----+---------+
| 1 | 经验 |
| 2 | 娜娜 |
| 3 | 大姐 |
| 5 | 测试1 |
+----+---------+
mysql> select * from MyTable2;
+----+---------+
| ID | Name |
+----+---------+
| 1 | 经验 |
| 2 | 包包 |
| 3 | 胖胖 |
| 4 | 测试2 |
+----+---------+
left join是以表1的记录为基础,即左表的记录全部显示出来,
而表2只显示符合搜索条件的记录显示出来,
即右表根据条件显示,那这里的条件就是a.ID=b.ID,记录不足的会以NULL填充显示。
mysql> select * from MyTable1 a left join MyTable2 b on a.ID=b.ID;
+----+---------+------+--------+
| ID | Name | ID | Name |
+----+---------+------+--------+
| 1 | 经验 | 1 | 经验 |
| 2 | 娜娜 | 2 | 包包 |
| 3 | 大姐 | 3 | 胖胖 |
| 5 | 测试1 | NULL | NULL |
+----+---------+------+--------+
right join执行的效果刚好和left join执行的效果相反,
即以右表的数据为基础,显示右表全部数据,而只显示左表符合搜索条件的数据。
mysql> select * from MyTable1 a right join MyTable2 b on a.ID=b.ID;
+------+--------+----+---------+
| ID | Name | ID | Name |
+------+--------+----+---------+
| 1 | 经验 | 1 | 经验 |
| 2 | 娜娜 | 2 | 包包 |
| 3 | 大姐 | 3 | 胖胖 |
| NULL | NULL | 4 | 测试2 |
+------+--------+----+---------+
inner join不以任何数据为基础,只显示符合搜索条件的数据。
mysql> select * from MyTable1 a inner join MyTable2 b on a.ID=b.ID;
+----+--------+----+--------+
| ID | Name | ID | Name |
+----+--------+----+--------+
| 1 | 经验 | 1 | 经验 |
| 2 | 娜娜 | 2 | 包包 |
| 3 | 大姐 | 3 | 胖胖 |
+----+--------+----+--------+
以上是基础了解一下,后面再来仔细说说
表连接,更确切的说时是inner joins内连接
内连接仅选出两张表中互相匹配的记录,因此会导致有时我们需要的记录没有包含进来
再拿两个表来举一下例子
政党表party(Code,Name,Leader) 议员表msp(Name,Party,Constituency)
Code: 政党代码 Name: 议员名
Name: 政党名称 Party: 议员所在政党代码
Leader: 政党领袖 Constituency: 选区
SELECT msp.name , party.name FROM party JOIN msp WHERE ON code=party
这个sql语句用了join也就是inner join ,本来是想列出所有议员的名字和他所属的政党
但是查询的结果可能不全,因为如果有议员不属于任何政党,即他们的政党字段(party)为空值
这时议员表中的party字段在政党表中找不到对应的记录作匹配
即FROM party JOIN msp WHERE ON code=party没有把该记录连接起来,而是过滤出去了
在该语句中,表party在join的左边,称为左表;表msp在join的右边,所以称为右表
SELECT msp.name , party.name FROM party LEFT JOIN msp ON party.code=msp.party
这个查询结果列出了所有议员和政党,包含没有议员的政党,不包括没有政党的议员
SELECT msp.name , party.name FROM party RIGHT JOIN msp ON party.code=msp.party
这个查询结果列出了所有议员和政党,包含没有政党的议员,不包括没有议员的政党
要想查询包含没有议员的政党,又要包含没有政党的议员,用全连接!!
SELECT msp.name , party.name FROM party FULL JOIN msp ON party.code=msp.party
来来来,再继续深入一下
再建两个表
CREATE TABLE votemaster(
id int(5) primary key,
voteTitle varchar(20) not null,
voteSum int(20) not null
);
INSERT INTO votemaster(id,voteTitle,voteSum) values(1,"对我们的满意程度",20);
INSERT INTO votemaster(id,voteTitle,voteSum) values(2,"从哪里知道我们的",16);
INSERT INTO votemaster(id,voteTitle,voteSum) values(3,"aaaaa",0);
INSERT INTO votemaster(id,voteTitle,voteSum) values(4,"bbbbb",0);
INSERT INTO votemaster(id,voteTitle,voteSum) values(5,"ccccc",0);
INSERT INTO votemaster(id,voteTitle,voteSum) values(6,"ddddd",0);
CREATE TABLE voter(
id int(5),
ip varchar(20),
voteTime timestamp,
voteNum int(20)
);
INSERT INTO voter(id,ip,voteTime,voteNum) values(1,"222.125.35.66","2013-09-09 23:42:22",1);
INSERT INTO voter(id,ip,voteTime,voteNum) values(1,"127.0.0.1","2014-09-07 13:22:32",12);
INSERT INTO voter(id,ip,voteTime,voteNum) values(2,"127.0.0.1","2015-02-09 10:11:22",11);
mysql> select * from voter;
+------+---------------+---------------------+---------+
| id | ip | voteTime | voteNum |
+------+---------------+---------------------+---------+
| 1 | 222.125.35.66 | 2013-09-09 23:42:22 | 1 |
| 1 | 127.0.0.1 | 2014-09-07 13:22:32 | 12 |
| 2 | 127.0.0.1 | 2015-02-09 10:11:22 | 11 |
+------+---------------+---------------------+---------+
mysql> select * from votemaster;
+----+--------------------------+---------+
| id | voteTitle | voteSum |
+----+--------------------------+---------+
| 1 | 对我们的满意程度 | 20 |
| 2 | 从哪里知道我们的 | 16 |
| 3 | aaaaa | 0 |
| 4 | bbbbb | 0 |
| 5 | ccccc | 0 |
| 6 | ddddd | 0 |
+----+--------------------------+---------+
votemaster为投票主表
voter为投票者信息表--记录投票人IP及对应投票类型
左右连接实际说是我们联合查询的结果以哪个表为准~
1.右连接right join 或 right outer join
我们以右边表voter为准,则左表(votemaster)中的记录只有当其id在voter表中存在时才会显示出来
mysql> SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm RIGHT JOIN voter as vt ON vm.id=vt.id ;
+------+--------------------------+---------------+
| id | voteTitle | ip |
+------+--------------------------+---------------+
| 1 | 对我们的满意程度 | 222.125.35.66 |
| 1 | 对我们的满意程度 | 127.0.0.1 |
| 2 | 从哪里知道我们的 | 127.0.0.1 |
+------+--------------------------+---------------+
2.左连接left join 或 left outer join
mysql> SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm LEFT JOIN voter as vt ON vm.id=vt.id ;
+----+--------------------------+---------------+
| id | voteTitle | ip |
+----+--------------------------+---------------+
| 1 | 对我们的满意程度 | 222.125.35.66 |
| 1 | 对我们的满意程度 | 127.0.0.1 |
| 2 | 从哪里知道我们的 | 127.0.0.1 |
| 3 | aaaaa | NULL |
| 4 | bbbbb | NULL |
| 5 | ccccc | NULL |
| 6 | ddddd | NULL |
+----+--------------------------+---------------+
3.内连接inner join 或join
mysql> SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm JOIN voter as vt ON vm.id=vt.id ;
+----+--------------------------+---------------+
| id | voteTitle | ip |
+----+--------------------------+---------------+
| 1 | 对我们的满意程度 | 222.125.35.66 |
| 1 | 对我们的满意程度 | 127.0.0.1 |
| 2 | 从哪里知道我们的 | 127.0.0.1 |
+----+--------------------------+---------------+
4.全连接full join 或 full outer join
注意!!mysql不支持full join,但还是有方法的
left join + union(可去除重复数据)+ right join
select * from A left join B on A.id = B.id (where 条件)
union
select * from A right join B on A.id = B.id (where条件);
在这个问题里
SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm LEFT JOIN voter as vt ON vm.id=vt.id
UNION
SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm RIGHT JOIN voter as vt ON vm.id=vt.id ;
5.交叉连接[完全连接]cross join [不带where条件的]
没有where子句的交叉联结将产生联结所涉及的表的笛卡儿积
第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小(votemaster和voter交叉连接产生6*3=18条记录)
SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm CROSS JOIN voter as vt ;[这里没有where条件]
这个语句等价于SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm,voter as vt ;
+----+--------------------------+---------------+
| id | voteTitle | ip |
+----+--------------------------+---------------+
| 1 | 对我们的满意程度 | 222.125.35.66 |
| 1 | 对我们的满意程度 | 127.0.0.1 |
| 1 | 对我们的满意程度 | 127.0.0.1 |
| 2 | 从哪里知道我们的 | 222.125.35.66 |
| 2 | 从哪里知道我们的 | 127.0.0.1 |
| 2 | 从哪里知道我们的 | 127.0.0.1 |
| 3 | aaaaa | 222.125.35.66 |
| 3 | aaaaa | 127.0.0.1 |
| 3 | aaaaa | 127.0.0.1 |
| 4 | bbbbb | 222.125.35.66 |
| 4 | bbbbb | 127.0.0.1 |
| 4 | bbbbb | 127.0.0.1 |
| 5 | ccccc | 222.125.35.66 |
| 5 | ccccc | 127.0.0.1 |
| 5 | ccccc | 127.0.0.1 |
| 6 | ddddd | 222.125.35.66 |
| 6 | ddddd | 127.0.0.1 |
| 6 | ddddd | 127.0.0.1 |
+----+--------------------------+---------------+
6.自连接
这个要再建一个表来演示一下
这里有一个表 Dep
这是一个部门表,里面存放了部门及其上级部门,但都放在了同一张表中
假设现在需要用sql查询出各部门及其上级部门
不用自连接也可以
SELECT DepartmentName 部门名 ,
(SELECT DepartmentName FROM Dep as inDep WHERE indep.DepID=outDep.pID) 上级部门名
FROM Dep as outDep
在这个查询中使用了一个子查询完成对上级部门名的查询,如果使用自连接,结构上感觉会清晰很多
自连接:
SELECT dep.DepartmentName 部门名 , jDep.DepartmentName 上级部门名
FROM Dep dep LEFT JOIN Dep jDep ON dep.pID=jDep.depID
其实就是一个表,自己和自己连接
这里除了使用自连接外,还使用了左连接,因为省电力没有上级部门,
如果使用内连接,就会把这条记录过滤掉,因为没有匹配的上级部门
自连接用的比较多的就是对权形结构的查询,类似上表