mysql多表查询加limit_MySQL多表查询⼀、准备
建表与数据准备:
# 建表
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
('nick','male',18,200),
('jason','female',48,201),
('sean','male',38,201),
('tank','female',28,202),
('oscar','male',18,200),
('mac','female',18,204)
;
# 查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |deepest
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+ mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | ⼈⼒资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | nick | male | 18 | 200 |
| 2 | jason | female | 48 | 201 |
| 3 | sean | male | 38 | 201 |
| 4 | tank | female | 28 | 202 |
| 5 | oscar | male | 18 | 200 |
| 6 | mac | female | 18 | 204 |
+----+------------+--------+------+--------+
表department与employee
⼆、多表连接查询
重点:外链接语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
2.1 交叉连接
不适⽤任何匹配条件。⽣成笛卡尔积
mysql> select * from employee,department;
+----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name |
shell脚本百度云+----+------------+--------+------+--------+------+--------------+ | 1 | nick | male | 18 | 200 | 200 | 技术 |
| 1 | nick | male | 18 | 200 | 201 | ⼈⼒资源 |
| 1 | nick | male | 18 | 200 | 202 | 销售 |
| 1 | nick | male | 18 | 200 | 203 | 运营 |
| 2 | jason | female | 48 | 201 | 200 | 技术 |
| 2 | jason | female | 48 | 201 | 201 | ⼈⼒资源 |
| 2 | jason | female | 48 | 201 | 202 | 销售 |
| 2 | jason | female | 48 | 201 | 203 | 运营 |
| 3 | sean | male | 38 | 201 | 200 | 技术 |
| 3 | sean | male | 38 | 201 | 201 | ⼈⼒资源 |
java常量池在方法区还是堆| 3 | sean | male | 38 | 201 | 202 | 销售 |
| 3 | sean | male | 38 | 201 | 203 | 运营 |
| 4 | tank | female | 28 | 202 | 200 | 技术 |
| 4 | tank | female | 28 | 202 | 201 | ⼈⼒资源 |
| 4 | tank | female | 28 | 202 | 202 | 销售 |
| 4 | tank | female | 28 | 202 | 203 | 运营 |adobe after effects
| 5 | oscar | male | 18 | 200 | 200 | 技术 |
| 5 | oscar | male | 18 | 200 | 201 | ⼈⼒资源 |
| 5 | oscar | male | 18 | 200 | 202 | 销售 |
| 5 | oscar | male | 18 | 200 | 203 | 运营 |
| 6 | mac | female | 18 | 204 | 200 | 技术 |
| 6 | mac | female | 18 | 204 | 201 | ⼈⼒资源 |
| 6 | mac | female | 18 | 204 | 202 | 销售 |
| 6 | mac | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+
2.2 内连接
只连接匹配的⾏
# 两张表共有的部分,相当于利⽤条件从笛卡尔积结果中筛选出了正确的结果
# department没有204这个部门,因⽽employee表中关于204这条员⼯信息没有匹配出来
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+-----------+------+--------+--------------+
| id | name | age | sex | name |
+----+-----------+------+--------+--------------+
| 1 | nick | 18 | male | 技术 |编辑器公章
| 2 | jason | 48 | female | ⼈⼒资源 |
| 3 | sean | 38 | male | ⼈⼒资源 |
| 4 | tank | 28 | female | 销售 |
| 5 | oscar | 18 | male | 技术 |
+----+-----------+------+--------+--------------+
# 上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
2.3 外链接之左连接
优先显⽰左表全部记录
# 以左表为准,即出所有员⼯信息,当然包括没有部门的员⼯
# 本质就是:在内连接的基础上增加左边有右边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+------------+--------------+
| id | name | depart_name |
+----+------------+--------------+
| 1 | nick | 技术 |
| 5 | oscar | 技术 |
| 2 | jason | ⼈⼒资源 |
| 3 | sean | ⼈⼒资源 |
| 4 | tank | 销售 |
| 6 | mac | NULL |
+----+------------+--------------+
2.4 外链接之右连接
优先显⽰右表全部记录
# 以右表为准,即出所有部门信息,包括没有员⼯的部门
# 本质就是:在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-----------+--------------+
| id | name | depart_name |
+------+-----------+--------------+
| 1 | nick | 技术 |
| 2 | jason | ⼈⼒资源 |
| 3 | sean | ⼈⼒资源 |
| 4 | tank | 销售 |
| 5 | oscar | 技术 |
| NULL | NULL | 运营 |
+------+-----------+--------------+
2.5 全外连接
显⽰左右两个表全部记录
全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
# 注意:mysql不⽀持全外连接 full JOIN
# 强调:mysql可以使⽤此种⽅式间接实现全外连接
select * from employee left join department on employee.dep_id = department.id
mysql语句多表查询union
select * from employee right join department on employee.dep_id = department.id
;
# 查看结果
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| 1 | nick | male | 18 | 200 | 200 | 技术 |
| 5 | oscar | male | 18 | 200 | 200 | 技术 |
| 2 | jason | female | 48 | 201 | 201 | ⼈⼒资源 |
| 3 | sean | male | 38 | 201 | 201 | ⼈⼒资源 |
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论