第3.18课 上课 MySQL数据库进阶， 多表查询
3_18
MySQL数据库进阶
多表查询
连接条件可在FROM 或 WHERE 子句中指定，
通常在 FROM 子句中指定联接条件。
也可以通过 WHERE 和 HAVING 子句包含搜索条件，以进一步筛选联接条件所选的行。
测试数据
-- 用户表
CREATE TABLE e_user (
id int PRIMARY KEY AUTO_INCREMENT,
t_name varchar(10) DEFAULT NULL
);
-- 订单表
CREATE TABLE e_order (
id int(11) PRIMARY KEY AUTO_INCREMENT,
u_id int(11), #references e_user(id),
order_code varchar(20) DEFAULT NULL,
create_date timestamp DEFAULT NOW()
);
-- 先插入主表再插入从表数据
insert into e_user values(1,'lis');
insert into e_user values(2,'tom');
insert into e_user values(3,'admin');
insert into e_user values(4,'mary');
insert into e_order values(null,1,'电视剧','2009-09-22');
insert into e_order values(null,1,'空调','2009-09-22');
insert into e_order values(null,1,'笔记本','2009-09-22');
insert into e_order values(null,2,'手机','2009-09-22');
insert into e_order values(null,2,'电池','2009-09-22');
insert into e_order values(null,3,'薯片','2009-09-22');
交叉连接：cross join
笛卡尔连接
select * from e_user,e_order;
1.笛卡尔积在下列情况下产生：
1）忽略了一个连接条件；
2）一个连接条件失效；
3）第一张表的所有行和第二张表的所有行连接；
2.为了避免笛卡尔积的产生，通常包含一个有效连接条件的WHERE子句。
限定使用二义性列名
1.用表前缀在多张表中限定列名；
2.用表前缀提高系统性能；
3.用列别名区分不同表中同名的列；
内连接
内连接（典型的连接运算有 = ，<> 之类的比较运算符）。
内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行。
例如，查询获取 用户表 和 订单表 中用户id相同的所有行。
自然连接：natural join
在连接条件中使用等于=运算符比较被连接列的列值，但会删除连接表中重复列。
select * from e_order as o natural join e_user as u where u.id = o.u_id;
等值连接：=
使用等于=比较连接列的列值，在查询结果中列出接连表中的所有列，包括其中的重复列。
-- inner join 内连接 只连接匹配的行
select u.*, o.order_code from e_user u inner join e_order o on u.id = o.u_id;
-- 上面的等效下面的语句
select u.*, o.order_code from e_user u join e_order o where u.id = o.u_id;
select u.*, o.order_code from e_user u, e_order o where u.id = o.u_id;
非等值连接：!=、<>
在连接条件中，可以使用其他比较运算符，比较被连接列的列值，如：<、>、!= <>等。
select * from e_user u inner join e_order o where u.id <> o.u_id;
外连接
左外连接：left outer join
左外连接要遍历左表的所有记录，右表没有的用null表示。
-- 左连接 将左边表的数据全部查询出来
select u.*, o.order_code from e_user u left join e_order o on u.id = o.u_id;
-- 或
select u.*, o.order_code from e_user u left outer join e_order o on u.id = o.u_id;
右外连接：right outer join
右外连接要遍历右表所有的记录，左表没有的用null表示。
-- 右连接 将右边表的数据全部查询出来
select u.*, o.order_code from e_user u right join e_order o on u.id = o.u_id;
-- 或
select u.*, o.order_code from e_user u right outer join e_order o on u.id = o.u_id;
自连接
自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，即将一张表看成多张表来做连接，从而能够得到一些特殊的数据。
例如：在emp中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。下面我们需要将每一个员工自己的名字和经理的名字都找出来。这时候我们该怎么做呢？
-- 自连接
create table t_emp(
emp_id int primary key,
emp_name varchar(20),
manager_id int references t_emp(emp_id)
);
insert into t_emp values(1, '飞刀客', null);
insert into t_emp values(2, '中神通', 1);
insert into t_emp values(3, '踏雪', 1);
insert into t_emp values(4, '挨刀妹', 1);
select * from t_emp;
-- 查询飞刀客手下员工
select worker.emp_name 员工 ,mgr.emp_name 领导
from t_emp worker, t_emp mgr
where worker.manager_id = mgr.emp_id;
浙公网安备 33010602011771号