MySQLDML操作--------多表联合查询实战
1. 背景
创新互联长期为千余家客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为珠海企业提供专业的网站设计制作、网站设计,珠海网站改版等技术服务。拥有十载丰富建站经验和众多成功案例,为您定制开发。
* 多表联合查询是把不同表的记录到一起的一种方式
* 在SQL标准中规划的联合(join)大致分内连接,外连接,全连接。其中外连接又分左外连接,右外连接。
2. 内连接例子 (inner join) [ 员工 --> 部门 ]
* 查看员工表[ employees ]和部门表[ departments ]结构
MySQL> desc employees; +-----------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | sex | enum('M','F') | NO | | NULL | | | age | int(11) | NO | | NULL | | | depart_id | bigint(20) | NO | | NULL | | +-----------+---------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> desc departments; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
* 查看员工表[ employees ]和部门表[ departments ]数据
mysql> select * from employees; +----+-------+-----+-----+-----------+ | id | name | sex | age | depart_id | +----+-------+-----+-----+-----------+ | 1 | tom | M | 25 | 1 | | 2 | jak | F | 35 | 2 | | 3 | lisea | M | 22 | 3 | +----+-------+-----+-----+-----------+ 3 rows in set (0.00 sec) mysql> select * from departments; +----+------+ | id | name | +----+------+ | 1 | dev | | 2 | test | | 3 | ops | +----+------+ 3 rows in set (0.00 sec)
* 查询并显示所有员工id, 姓名,姓别,年龄,所在部门 (方法一)
mysql> select e.id id, e.name name, IF(e.sex = 'M', 'male', 'female') sex, e.age age, d.name -> from employees e, departments d -> where e.depart_id = d.id; +----+-------+--------+-----+------+ | id | name | sex | age | name | +----+-------+--------+-----+------+ | 1 | tom | male | 25 | dev | | 2 | jak | female | 35 | test | | 3 | lisea | male | 22 | ops | +----+-------+--------+-----+------+ 3 rows in set (0.03 sec)
* 查询并显示所有员工id, 姓名,姓别,年龄,所在部门 (方法二) [ 方法一等价于方法二 ]
inner join 可以省写成 join
mysql> select e.id id, e.name name, IF(e.sex = 'M', 'male', 'female') sex, e.age age, d.name -> from employees e inner join departments d -> on e.depart_id = d.id; +----+-------+--------+-----+------+ | id | name | sex | age | name | +----+-------+--------+-----+------+ | 1 | tom | male | 25 | dev | | 2 | jak | female | 35 | test | | 3 | lisea | male | 22 | ops | +----+-------+--------+-----+------+ 3 rows in set (0.00 sec)
3. 外连接例子
左外连接 [ 以左表为基础,左表的全部数据,右表有的组合。右表没有的为null ]
右外连接 [ 以右表为基础,右表的全部数据,左表有的组合。左表没有的为null ]
* 查看a表和b表结构
mysql> desc a; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | data | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> desc b; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | data | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)
* 查看a表和b表数据
mysql> select * from a; +------+ | data | +------+ | 1 | | 2 | | 4 | +------+ 3 rows in set (0.00 sec) mysql> select * from b; +------+ | data | +------+ | 2 | | 4 | | 5 | | 6 | +------+ 4 rows in set (0.01 sec)
* 左外连接查询 (left join) 以a表为基础,显示a表所有数据,b表有的组合,没有显示NULLL
left outer join 可以写成 left join
mysql> select * from a left outer join b on a.data = b.data; +------+------+ | data | data | +------+------+ | 2 | 2 | | 4 | 4 | | 1 | NULL | +------+------+ 3 rows in set (0.00 sec)
* 右外连接查询 以b表为基础,显示b表所有数据,a表有的组合,没有显示NULLL
right outer join 可以写成 right join
mysql> select * from a right outer join b on a.data = b.data; +------+------+ | data | data | +------+------+ | 2 | 2 | | 4 | 4 | | NULL | 5 | | NULL | 6 | +------+------+ 4 rows in set (0.00 sec)
* 完全(交叉)连接查询
没有 where 子句的交叉联接将产生联接所涉及的表的笛卡尔积。
第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
(a和b交叉连接产生3*4=12条记录)
mysql> select * from a corss join b; +------+------+ | data | data | +------+------+ | 1 | 2 | | 2 | 2 | | 4 | 2 | | 1 | 4 | | 2 | 4 | | 4 | 4 | | 1 | 5 | | 2 | 5 | | 4 | 5 | | 1 | 6 | | 2 | 6 | | 4 | 6 | +------+------+ 12 rows in set (0.00 sec)
4. 总结
以需求驱动技术,技术本身没有优略之分,只有业务之分。
本文题目:MySQLDML操作--------多表联合查询实战
本文来源:http://pwwzsj.com/article/ipphsh.html