MySQL4 多表查询 内连接

多表查询

数据准备

CREATE DATABASE  db4;
USE db4;
-- 创建部门表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);

-- 创建员工表
create table if not exists emp(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
);

-- 给dept表添加数据
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部');
 
-- 给emp表添加数据
insert into emp values('1','乔峰',20, '1001');
insert into emp values('2','段誉',21, '1001');
insert into emp values('3','虚竹',23, '1001');
insert into emp values('4','阿紫',18, '1001');
insert into emp values('5','扫地僧',85, '1002');
insert into emp values('6','李秋水',33, '1002');
insert into emp values('7','鸠摩智',50, '1002'); 
insert into emp values('8','天山童姥',60, '1003');
insert into emp values('9','慕容博',58, '1003');
--注意，此处部门表中没有存在1005对应的部门，当然部门1004在此出没有员工，
--只是为了演示效果
insert into emp values('10','丁春秋',71, '1005');

SELECT * FROM emp;
SELECT * FROM dept;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

一对一 一般需要表合并
一对多
多对多 需要从表

内连接

笛卡尔积

-- 笛卡尔积 A表m条,B表n条,查询结果为m*n条 
SELECT *
FROM emp,dept;
1
2
3

隐藏内连接

-- 隐式内连接(SQL92标准)
-- SELECT 字段列表 FROM 表1,表2... WHERE 条件;
1
2

-- 隐式内连接
SELECT *
FROM emp,dept
WHERE dept.deptno=emp.dept_id;
1
2
3
4

显式内连接

-- 显式内连接(SQL99标准) []包括的可以省略不写
-- SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
1
2

-- 显式内连接
SELECT *
FROM emp INNER join dept
ON dept.deptno=emp.dept_id;
1
2
3
4

内连接应用：

-- 查询在研发部的员工信息
SELECT *
FROM emp,dept
WHERE dept.deptno=emp.dept_id AND dept.NAME='研发部';

SELECT *
FROM emp INNER join dept
ON dept.deptno=emp.dept_id AND dept.NAME='研发部';

-- 查询每个部门的员工数,并升序
SELECT NAME,COUNT(*) AS con
FROM emp,dept
WHERE emp.dept_id=dept.deptno
GROUP BY deptno,NAME
ORDER BY con;

-- 查询人数大于3的部门,并按照人数进行降序
SELECT NAME,COUNT(*) AS con
FROM emp,dept
WHERE emp.dept_id=dept.deptno
GROUP BY deptno,NAME
HAVING con>=3
ORDER BY con DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23