连接是关系数据库模型的主要特点,也是区别于其他类型数据管理系的一个标志.
通常来说表与表之间的关系不必确定,也就时实体与实体之间的关系不紧密,检索数据时,使用连表操作增强灵活性.可以再连表查询时增加新的字段,为不同实体创建新的表.
即笛卡尔积,将两个表所有的记录进行配对,数据大量冗余,没有实际意义
### 查询 employee 员工表 和 department 部门表的笛卡尔积 # 数据大量冗余 ,没有实际意义 select * from employee,department; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 1 | egon | male | 18 | 200 | 201 | 人力资源 | | 1 | egon | male | 18 | 200 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 203 | 运营 | | 2 | alex | female | 48 | 201 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 2 | alex | female | 48 | 201 | 202 | 销售 | | 2 | alex | female | 48 | 201 | 203 | 运营 | | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 | | 4 | yuanhao | female | 28 | 202 | 200 | 技术 | | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 4 | yuanhao | female | 28 | 202 | 203 | 运营 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 | | 5 | liwenzhou | male | 18 | 200 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 203 | 运营 | | 6 | jingliyang | female | 18 | 204 | 200 | 技术 | | 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 | | 6 | jingliyang | female | 18 | 204 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | 203 | 运营 | +----+------------+--------+------+--------+------+--------------+ 内连接是一种最常用的连接类型,两个表的字段满足的连接条件,只显示两张表中互相匹配的项,其他不匹配项的不显示.
### 将员工表(employee)与部门表(department)进行连接, 员工表通过字段dep_id(部门id) 与 部门表字段id(部门id)进行连接 # 方式一 自连接 (特殊的内连接) 通过where 方式进行连接 select * from employee e ,department d where e.dep_id=d.id ; # 结果 +----+-----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | +----+-----------+--------+------+--------+------+--------------+ # 方式二 通过内连接 ... inner join ... on方式连接 select * from employee e inner join department d on e.dep_id=d.id ; # 结果 +----+-----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | +----+-----------+--------+------+--------+------+--------------+ 左外连接:
语法:A LEFT join B on A.XX=B.XX;
左表存显示所有,右表没有与左表匹配的则为 null.
右外连接:
语法:A RIGHT join B on A.XX=B.XX;
右表存显示所有,左表没有与右表匹配的则为 null.
### 员工表和部门表 # 左外连接 : 与左表没有匹配的则显示空,左表完全显示 select * from employee e left join department d on e.dep_id=d.id; # 结果: +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | #### 重点~~~ +----+------------+--------+------+--------+------+--------------+ # 右外连接 : 与右表没有匹配的则显示空,右表完全显示 select * from employee e right join department d on e.dep_id=d.id; # 结果: +------+-----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+-----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | #### 重点~~ +------+-----------+--------+------+--------+------+--------------+union 关键字
mysql 不支持全外连接 full JOIN
mysql 使用 union 连接左连接和右连接，得到全连接
### union 全连接 # 必须是 左连接 + 右连接 select * from employee e left join department d on e.dep_id=d.id union select * from employee e right join department d on e.dep_id=d.id; # 结果: +------+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | ### 重点 ? ~~~ | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | ### 重点 ? ~~~ +------+------------+--------+------+--------+------+--------------+ ### 全连接 错误用法 # 1. select * from employee e left join department d on e.dep_id=d.id 可以得到 左连接 # 2. select * from department d left join employee e on e.dep_id=d.id 通过调换表的顺序可以得到和右连接一样的效果 # 3. 但是 这样组合起来的东西并不是全连接 select * from employee e left join department d on e.dep_id=d.id union select * from department d left join employee e on e.dep_id=d.id ; # 结果:? 这并不是全连接 +------+--------------+--------+-----------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+--------------+--------+-----------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | 200 | 技术 | 1 | egon | male | 18 | 200 | | 201 | 人力资源 | 2 | alex | female | 48 | 201 | | 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 | | 202 | 销售 | 4 | yuanhao | female | 28 | 202 | | 200 | 技术 | 5 | liwenzhou | male | 18 | 200 | | 203 | 运营 | NULL | NULL | NULL | NULL | NULL | +------+--------------+--------+-----------+--------+------+--------------+准备employee 和 department 表:
#建表 create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into department values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into employee(name,sex,age,dep_id) values ('egon','male',18,200), ('alex','female',48,201), ('wupeiqi','male',38,201), ('yuanhao','female',28,202), ('liwenzhou','male',18,200), ('jingliyang','female',18,204) ;练习题:
# 1. 以 内连接的方式 查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门 select e.name,d.name from employee e inner join department d on e.dep_id=d.id where e.age>25; # 2. 以 内连接的方式 查询employee和department表，年龄大于25 ,并且以age字段的升序方式显示 select * from employee e inner join department d on e.dep_id=d.id where e.age>25 order by e.age; # 3. 使用 自连接方式 查询employee和department表，年龄大于25 ,并且以age字段的升序方式显示 select * from employee e , department d where e.dep_id=d.id and e.age>25 order by e.age;1.子查询是将一个查询语句嵌套另一个语句中
2.内层查询语句的结果,可以作为外层查询的条件(where)
3.子查询中可以包含:IN , NOT , ANY , ALL , EXISTS 和 NOT EXISTS等关键字
4.可以包含比较运算符: = , != , > , < 等
5.通俗:携带 两个select 就是子查询.
6.子查询也可以作为字段(只能是单个值)
exists表示:内层查询语句返回的是一个bool值,True时执行外层查询,False时不执行外层查询
# 1 . department表中存在dept_id=203，执行查询员工表 select * from employee where exists (select * from department where id=203); # 结果:? mysql> select * from employee -> where exists (select * from department where id=203); +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) # 2.department表中存在dept_id=205，执行查询员工表 select * from employee where exists (select * from department where id=205); # 结果:? mysql> select * from employee -> where exists (select * from department where id=205); Empty set (0.00 sec) 1.字段可以使一个子查询,但是这个字段必须是唯一值,否则报错
2.子查询处理可以放在条件中,还可以放在连表中,还可以放在select字段(要求查询的结果必须是一个单行单列的值)中.
## 准备 emp 表数据 create table emp( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龙','male',48,'20101111','teacher',10000,401,1), ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2), ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3) ;示例:select A表name ,B表age(通过关联A表和B表的name字段) from A表
# 查询 emp 表中的name 字段 和 employee 表中的age字段, 两个表的的name字段进行关联 select name as n ,(select age from employee as e where e.name=n ) as employee_Age from emp; # 分析: n字段来自表emp , employee_Age字段来自 employee 字段 +------------+--------------+ | n | employee_Age | +------------+--------------+ | egon | 18 | | alex | 48 | | wupeiqi | 38 | | yuanhao | 28 | | liwenzhou | 18 | | jingliyang | 18 | | jinxin | NULL | | 成龙 | NULL | | 歪歪 | NULL | | 丫丫 | NULL | | 丁丁 | NULL | | 星星 | NULL | | 格格 | NULL | | 张野 | NULL | | 程咬金 | NULL | | 程咬银 | NULL | | 程咬铜 | NULL | | 程咬铁 | NULL | +------------+--------------+转载于:https://www.cnblogs.com/dengl/p/11290985.html
