文章目录
创建部门表和员工表，一对多
#创建数据库
create database company charset utf8mb4;
# 创建dept部门表
create table dept(
deptno tinyint unsigned primary key,
deptname varchar(20),
location varchar(20)
);
#插入部门数据
insert into dept values
(10, 'Accounting', 'New York'),
(20, 'Research','Dallas'),
(30, 'Sales', 'Chicago'),
(40, 'Operations','Boston');
#创建雇员表
create table emp(
empno int unsigned not null primary key auto_increment,
empname varchar(20),
empjob varchar(20),
empleaderno int unsigned,
hiredate date,
salary decimal(10,3),
allowance decimal(10,3),
deptno tinyint unsigned
constraint fk_deptno_emp foreign key(deptno) references dept(deptno)
);
#插入雇员数据
insert into emp values
(7369, 'Smith', 'clerk ', 7902,'1980-12-17',800, null, 20),
(7499, 'Allen', 'salesman', 7698,'1981-02-20', 1600,300,30),
(7521, 'Ward' , 'salesman', 7698, '1981-02-22',1250,500,30),
(7566, 'Jones', 'manager', 7839,'1981-04-02',2975, null, 20),
(7654, 'Maritn', 'salesman', 7698,'1981-09-28',1250, 1400,30),
(7698,'Blake', 'manager', 7839,'1981-05-01', 2850,null, 30),
(7782,'Clark' , 'manager', 7839,'1981-06-09', 2450, null, 10),
(7788, 'Scott', 'analyst', 7566,'1987-04-19',3000, null, 20),
(7839, 'King', 'president', null, '1981-11-17', 5000, null, 10),
(7844, 'Turner' , 'salesman', 7698,'1981-09-08',1500,0,30),
(7876, 'Adams', 'clerk', 7788,'1987-05-23',1100, null, 20),
(7900, 'James', 'clerk', 7698,'1981-12-03', 950, null, 30),
(7902,'Ford', 'analyst', 7566,'1981-12-03', 3000, null, 20),
(7934, 'Miller' , 'clerk', 7782,'1982-01-23', 1300,null, 10);
一、交叉连接（cross join）
交叉连接指的是两张或者多张表进行的笛卡尔积，两张或者多张表中每一行的数据任意组合的结果。
语法：select 查询内容 from table1 cross join table2
例如 select * from emp cross join dept;
mysql> select * from dept cross join emp;
+--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+--------+
| deptno | deptname | location | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno |
+--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+--------+
| 10 | Accounting | New York | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 |
| 20 | Research | Dallas | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 |
| 30 | Sales | Chicago | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 |
| 40 | Operations | Boston | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 |
| 10 | Accounting | New York | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 |
| 20 | Research | Dallas | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 |
| 30 | Sales | Chicago | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 |
| 40 | Operations | Boston | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 |
| 10 | Accounting | New York | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 |
| 20 | Research | Dallas | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 |
| 30 | Sales | Chicago | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 |
| 40 | Operations | Boston | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 |
| 10 | Accounting | New York | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 |
| 20 | Research | Dallas | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 |
| 30 | Sales | Chicago | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 |
| 40 | Operations | Boston | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 |
| 10 | Accounting | New York | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 |
| 20 | Research | Dallas | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 |
| 30 | Sales | Chicago | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 |
| 40 | Operations | Boston | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 |
| 10 | Accounting | New York | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 |
| 20 | Research | Dallas | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 |
| 30 | Sales | Chicago | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 |
| 40 | Operations | Boston | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 |
| 10 | Accounting | New York | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 |
| 20 | Research | Dallas | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 |
| 30 | Sales | Chicago | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 |
| 40 | Operations | Boston | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 |
| 10 | Accounting | New York | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 |
| 20 | Research | Dallas | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 |
| 30 | Sales | Chicago | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 |
| 40 | Operations | Boston | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 |
| 10 | Accounting | New York | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 |
| 20 | Research | Dallas | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 |
| 30 | Sales | Chicago | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 |
| 40 | Operations | Boston | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 |
| 10 | Accounting | New York | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 |
| 20 | Research | Dallas | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 |
| 30 | Sales | Chicago | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 |
| 40 | Operations | Boston | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 |
| 10 | Accounting | New York | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 |
| 20 | Research | Dallas | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 |
| 30 | Sales | Chicago | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 |
| 40 | Operations | Boston | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 |
| 10 | Accounting | New York | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 |
| 20 | Research | Dallas | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 |
| 30 | Sales | Chicago | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 |
| 40 | Operations | Boston | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 |
| 10 | Accounting | New York | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 |
| 20 | Research | Dallas | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 |
| 30 | Sales | Chicago | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 |
| 40 | Operations | Boston | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 |
| 10 | Accounting | New York | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 |
| 20 | Research | Dallas | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 |
| 30 | Sales | Chicago | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 |
| 40 | Operations | Boston | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 |
+--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+--------+
56 rows in set (0.00 sec)
总记录条数：table1记录条数*table2记录条数
总列数：table1列数+table2列数
缺点：有重复的值存在
二、自然连接(natural join)
自然连接是关系表中相同名称的字段进行自动匹配产生的结果，会去除重复的列
语法：select 查询内容 from table1 natural join table2
mysql> select * from dept natural join emp;
+--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+
| deptno | deptname | location | empno | empname | empjob | empleader | hiredate | salary | allowance |
+--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+
| 10 | Accounting | New York | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL |
| 10 | Accounting | New York | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL |
| 10 | Accounting | New York | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL |
| 20 | Research | Dallas | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL |
| 20 | Research | Dallas | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL |
| 20 | Research | Dallas | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL |
| 20 | Research | Dallas | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL |
| 20 | Research | Dallas | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL |
| 30 | Sales | Chicago | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 |
| 30 | Sales | Chicago | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 |
| 30 | Sales | Chicago | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 |
| 30 | Sales | Chicago | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL |
| 30 | Sales | Chicago | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 |
| 30 | Sales | Chicago | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL |
+--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+
14 rows in set (0.01 sec)
查询有奖金的雇员姓名和部门名称
mysql> select empname,deptname from dept natural join emp where allowance is not null;
+---------+----------+
| empname | deptname |
+---------+----------+
| Allen | Sales |
| Ward | Sales |
| Maritn | Sales |
| Turner | Sales |
+---------+----------+
注意：
1.关联的表中必须有相同名称的字段（字段名称相同，字段数据类型相同）
2.将相同名称的字段会去重
3.自然连接可以和where子句一起使用
三、内连接(inner join)
指两张或者多张表中生成的笛卡尔积记录中按给定的连接条件查询出满足条件的结果
3.1 sql92语法
语法：select 查询内容 from table1，table2 where 关联条件 [and] 条件
例如：查询雇员信息，及雇员所在部门的信息
mysql> select * from emp,dept where emp.deptno=dept.deptno;
+-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------+
| empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | deptno | deptname | location |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------+
| 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | 10 | Accounting | New York |
| 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | 10 | Accounting | New York |
| 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | 10 | Accounting | New York |
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | 20 | Research | Dallas |
| 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | 20 | Research | Dallas |
| 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | 20 | Research | Dallas |
| 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | 20 | Research | Dallas |
| 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | 20 | Research | Dallas |
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | 30 | Sales | Chicago |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | 30 | Sales | Chicago |
| 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | 30 | Sales | Chicago |
| 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | 30 | Sales | Chicago |
| 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | 30 | Sales | Chicago |
| 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | 30 | Sales | Chicago |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------+
或者使用别名：select * from emp as e,dept as d where e.deptno=d.deptno
3.2 sql99语法
语法： select 查询内容from table1 inner join table2 on 关联条件 [and] 关联条件可以选择
mysql> select * from emp as e inner join dept as d on e.deptno=d.deptno;
+-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------+
| empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | deptno | deptname | location |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------+
| 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | 10 | Accounting | New York |
| 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | 10 | Accounting | New York |
| 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | 10 | Accounting | New York |
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | 20 | Research | Dallas |
| 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | 20 | Research | Dallas |
| 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | 20 | Research | Dallas |
| 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | 20 | Research | Dallas |
| 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | 20 | Research | Dallas |
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | 30 | Sales | Chicago |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | 30 | Sales | Chicago |
| 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | 30 | Sales | Chicago |
| 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | 30 | Sales | Chicago |
| 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | 30 | Sales | Chicago |
| 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | 30 | Sales | Chicago |
+-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------
查询部门号为30的雇员信及部门名称
mysql> select e.*,d.deptname from emp as e inner join dept as d on e.deptno=d.deptno where d.deptno=30;
+-------+---------+----------+-----------+------------+----------+-----------+--------+----------+
| empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | deptname |
+-------+---------+----------+-----------+------------+----------+-----------+--------+----------+
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | Sales |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | Sales |
| 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | Sales |
| 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | Sales |
| 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | Sales |
| 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | Sales |
+-------+---------+----------+-----------+------------+----------+-----------+--------+----------+
6 rows in set (0.00 sec)
查询雇员姓名，薪资，部门名称
mysql> select empname,salary,deptname from emp as e join dept as d where e.deptno=d.deptno;
+---------+----------+------------+
| empname | salary | deptname |
+---------+----------+------------+
| Clark | 2450.000 | Accounting |
| King | 5000.000 | Accounting |
| Miller | 1300.000 | Accounting |
| Smith | 800.000 | Research |
| Jones | 2975.000 | Research |
| Scott | 3000.000 | Research |
| Adams | 1100.000 | Research |
| Ford | 3000.000 | Research |
| Allen | 1600.000 | Sales |
| Ward | 1250.000 | Sales |
| Maritn | 1250.000 | Sales |
| Blake | 2850.000 | Sales |
| Turner | 1500.000 | Sales |
| James | 950.000 | Sales |
+---------+----------+------------+
14 rows in set (0.00 sec)
查询有奖金的雇员姓名和编号
mysql> select empname, deptname from emp as e join dept as d on e.deptno=d.deptno and e.allowance is not null;
+---------+----------+
| empname | deptname |
+---------+----------+
| Allen | Sales |
| Ward | Sales |
| Maritn | Sales |
| Turner | Sales |
+---------+----------+
查询部门名称第二个字符为e的雇员姓名和部门名称
# where也可以换做and
mysql> select empname,deptname from emp as e inner join dept as d on e.deptno=d.deptno where deptname like '_e%';
+---------+----------+
| empname | deptname |
+---------+----------+
| Smith | Research |
| Jones | Research |
| Scott | Research |
| Adams | Research |
| Ford | Research |
+---------+----------+
查询部门中员工个数>5的部门名称
mysql> select d.deptname from emp as e join dept as d on e.deptno=d.deptno group by e.deptno having count(*)>5;
+----------+
| deptname |
+----------+
| Sales |
+----------+
注意：
1.inner可以省略
2.两张或多张表有相同的字段，字段前需要添加表名，可以使用表名的别名。
四、自连接:关联的两张表是同一张表
例如：查询雇员姓名，雇员薪资，雇员的领导姓名，雇员领导薪资(等值连接查询)
1.用sql92实现：
mysql> select e.empname as '雇员',e.salary,m.empname as '领导',m.salary from emp as e , emp as m where e.empleaderno=m.empno;
+--------+----------+--------+----------+
| 雇员 | salary | 领导 | salary |
+--------+----------+--------+----------+
| Smith | 800.000 | Ford | 3000.000 |
| Allen | 1600.000 | Blake | 2850.000 |
| Ward | 1250.000 | Blake | 2850.000 |
| Jones | 2975.000 | King | 5000.000 |
| Maritn | 1250.000 | Blake | 2850.000 |
| Blake | 2850.000 | King | 5000.000 |
| Clark | 2450.000 | King | 5000.000 |
| Scott | 3000.000 | Jones | 2975.000 |
| Turner | 1500.000 | Blake | 2850.000 |
| Adams | 1100.000 | Scott | 3000.000 |
| James | 950.000 | Blake | 2850.000 |
| Ford | 3000.000 | Jones | 2975.000 |
| Miller | 1300.000 | Clark | 2450.000 |
+--------+----------+--------+----------+
2.用sql99实现
mysql> select e.empname as '雇员',e.salary,m.empname as '领导',m.salary from emp as e inner join emp as m on e.empleaderno=m.empno;
+--------+----------+--------+----------+
| 雇员 | salary | 领导 | salary |
+--------+----------+--------+----------+
| Smith | 800.000 | Ford | 3000.000 |
| Allen | 1600.000 | Blake | 2850.000 |
| Ward | 1250.000 | Blake | 2850.000 |
| Jones | 2975.000 | King | 5000.000 |
| Maritn | 1250.000 | Blake | 2850.000 |
| Blake | 2850.000 | King | 5000.000 |
| Clark | 2450.000 | King | 5000.000 |
| Scott | 3000.000 | Jones | 2975.000 |
| Turner | 1500.000 | Blake | 2850.000 |
| Adams | 1100.000 | Scott | 3000.000 |
| James | 950.000 | Blake | 2850.000 |
| Ford | 3000.000 | Jones | 2975.000 |
| Miller | 1300.000 | Clark | 2450.000 |
+--------+----------+--------+----------+
非等值连接查询
查询雇员姓名，雇员薪资，雇员的领导姓名，雇员领导薪资，且雇员编号大于领导编号。
mysql> select e.empname as '雇员', e.empno as '雇员编号',e.salary,m.empname as '领导',m.empno as '领导编号',m.salary from emp as e , empas m where e.empleaderno=m.empno and e.empno>e.empleaderno;
+--------+--------------+----------+--------+--------------+----------+
| 雇员 | 雇员编号 | salary | 领导 | 领导编号 | salary |
+--------+--------------+----------+--------+--------------+----------+
| Scott | 7788 | 3000.000 | Jones | 7566 | 2975.000 |
| Turner | 7844 | 1500.000 | Blake | 7698 | 2850.000 |
| Adams | 7876 | 1100.000 | Scott | 7788 | 3000.000 |
| James | 7900 | 950.000 | Blake | 7698 | 2850.000 |
| Ford | 7902 | 3000.000 | Jones | 7566 | 2975.000 |
| Miller | 7934 | 1300.000 | Clark | 7782 | 2450.000 |
+--------+--------------+----------+--------+--------------+----------+
五、外连接 left/right out join：
外连接OUTER JOIN不仅能在两张或多张表生成的笛卡儿积记录中筛选出与连接条件相匹配的数据记录，还能根据用户指定保留部分不匹配的记录。按照不匹配记录来源的不同可以将外连接分为左外连接和右外连接。
不仅要查询两张或者多张表中满足条件的记录，还要查询其中某张表不满足连接条件的记录
查询雇员表及雇员所在部门信息
select * from emp e, depts d where e.deptno=d.deptno
没有雇员的部门也要被显示出来
语法
select * from table1 left|right outer join table2 on 连接条件
left join：左外连接 将放到left join左边表中的不满足条件的记录也显示
right join：右外连接 将放到 right join右边比偶中的不满足条件的记录也显示
例如：
查询每位员工的姓名、职位、其领导姓名、领导职位，同时要显示没有领导的雇员也要显示
如下比较内连接与左外连接的区别：
# 内连接
mysql> select e.empname,e.empjob,m.empname,m.empjob from emp as e,emp as m where e.empleaderno=m.empno;
+---------+----------+---------+-----------+
| empname | empjob | empname | empjob |
+---------+----------+---------+-----------+
| Smith | clerk | Ford | analyst |
| Allen | salesman | Blake | manager |
| Ward | salesman | Blake | manager |
| Jones | manager | King | president |
| Maritn | salesman | Blake | manager |
| Blake | manager | King | president |
| Clark | manager | King | president |
| Scott | analyst | Jones | manager |
| Turner | salesman | Blake | manager |
| Adams | clerk | Scott | analyst |
| James | clerk | Blake | manager |
| Ford | analyst | Jones | manager |
| Miller | clerk | Clark | manager |
+---------+----------+---------+-----------+
13 rows in set (0.00 sec)
# 左外连接
mysql> select e.empname,e.empjob,m.empname,m.empjob from emp as e left outer join emp as m on e.empleaderno=m.empno;
+---------+-----------+---------+-----------+
| empname | empjob | empname | empjob |
+---------+-----------+---------+-----------+
| Smith | clerk | Ford | analyst |
| Allen | salesman | Blake | manager |
| Ward | salesman | Blake | manager |
| Jones | manager | King | president |
| Maritn | salesman | Blake | manager |
| Blake | manager | King | president |
| Clark | manager | King | president |
| Scott | analyst | Jones | manager |
| King | president | NULL | NULL |
| Turner | salesman | Blake | manager |
| Adams | clerk | Scott | analyst |
| James | clerk | Blake | manager |
| Ford | analyst | Jones | manager |
| Miller | clerk | Clark | manager |
+---------+-----------+---------+-----------+
14 rows in set (0.00 sec)
可以看到多了一条数据King,因为King的empleaderno 为Null，显示内连接查询时候不会显示出来
查询所有部门的详细信息及每个部门的平均月薪，包含没有员工的部门,并按照平均月薪由高到低排序。
比较内连接和右外连接区别
#内连接
mysql> select d.*,avg(salary) from emp as e inner join dept as d on e.deptno=d.deptno group by d.deptno order by avg(salary) desc;
+--------+------------+----------+--------------+
| deptno | deptname | location | avg(salary) |
+--------+------------+----------+--------------+
| 10 | Accounting | New York | 2916.6666667 |
| 20 | Research | Dallas | 2175.0000000 |
| 30 | Sales | Chicago | 1566.6666667 |
+--------+------------+----------+--------------+
3 rows in set (0.00 sec)
#右外连接
mysql> select d.*,avg(salary) from emp as e right outer join dept as d on e.deptno=d.deptno group by d.deptno order by avg(salary) desc;
+--------+------------+----------+--------------+
| deptno | deptname | location | avg(salary) |
+--------+------------+----------+--------------+
| 10 | Accounting | New York | 2916.6666667 |
| 20 | Research | Dallas | 2175.0000000 |
| 30 | Sales | Chicago | 1566.6666667 |
| 40 | Operations | Boston | NULL |
+--------+------------+----------+--------------+
4 rows in set (0.00 sec)
# 左外连接，效果与右外连接一样
select d.*,avg(salary) from dept as d left outer join emp as e on e.deptno=d.deptno group by d.deptno order by avg(salary) desc;
可以看到右外连接多了一条数据，因为40号部门没有员工，内连接时候不满足条件不会显示出来
六、多表查询练习
1.查询雇员姓名、部门号、部门名称（分别使用SQL92和sQL99完成)
#sql92
select empname,d.deptno,deptname from emp as e,dept as d where e.deptno=d.deptno;
#sql99
select empname,d.deptno,deptname from emp as e inner join dept as d on e.deptno=d.deptno;
2.查询部门编号为30的所有职位的唯一列表，同时显示部门位置，及部门号
mysql> select distinct empjob,d.location,d.deptno from emp as e inner join dept as d on e.deptno=d.deptno and d.deptno=30;
+----------+----------+--------+
| empjob | location | deptno |
+----------+----------+--------+
| salesman | Chicago | 30 |
| manager | Chicago | 30 |
| clerk | Chicago | 30 |
+----------+----------+--------+
3.显示所有在雇员姓名中有a的雇员姓名和部门名称
mysql> select empname,deptname from emp as e inner join dept as d on e.deptno=d.deptno and empname like '%a%';
+---------+------------+
| empname | deptname |
+---------+------------+
| Allen | Sales |
| Ward | Sales |
| Maritn | Sales |
| Blake | Sales |
| Clark | Accounting |
| Adams | Research |
| James | Sales |
+---------+------------+
7 rows in set (0.00 sec)
4.使用内连接写一个查询显示工作在New York的所有雇员姓名、职位、部门名称
mysql> select empname,empjob,deptname from emp as e inner join dept as d on e.deptno=d.deptno and location='New York';
+---------+-----------+------------+
| empname | empjob | deptname |
+---------+-----------+------------+
| Clark | manager | Accounting |
| King | president | Accounting |
| Miller | clerk | Accounting |
+---------+-----------+------------+
3 rows in set (0.00 sec)
5.显示雇员的姓名、雇员号及他们领导的姓名和领导号
mysql> select e.empname as '雇员姓名',e.empno as '雇员编号',m.empname as '领导姓名',m.empno as '领导姓名' from emp as e,emp as m where e.empleaderno=m.empno;
+--------------+--------------+--------------+--------------+
| 雇员姓名 | 雇员编号 | 领导姓名 | 领导姓名 |
+--------------+--------------+--------------+--------------+
| Smith | 7369 | Ford | 7902 |
| Allen | 7499 | Blake | 7698 |
| Ward | 7521 | Blake | 7698 |
| Jones | 7566 | King | 7839 |
| Maritn | 7654 | Blake | 7698 |
| Blake | 7698 | King | 7839 |
| Clark | 7782 | King | 7839 |
| Scott | 7788 | Jones | 7566 |
| Turner | 7844 | Blake | 7698 |
| Adams | 7876 | Scott | 7788 |
| James | 7900 | Blake | 7698 |
| Ford | 7902 | Jones | 7566 |
| Miller | 7934 | Clark | 7782 |
+--------------+--------------+--------------+--------------+
13 rows in set (0.00 sec)
6.查询所有雇员的领导包括雇员king，他没有领导。显示雇员号、雇员姓名和领导号、领导姓名，使用雇员号升序排序
mysql> select e.empno, e.empname,e.empjob,m.empname,m.empjob from emp as e left outer join emp as m on e.empleaderno=m.empno order by e.empno desc;
+-------+---------+-----------+---------+-----------+
| empno | empname | empjob | empname | empjob |
+-------+---------+-----------+---------+-----------+
| 7934 | Miller | clerk | Clark | manager |
| 7902 | Ford | analyst | Jones | manager |
| 7900 | James | clerk | Blake | manager |
| 7876 | Adams | clerk | Scott | analyst |
| 7844 | Turner | salesman | Blake | manager |
| 7839 | King | president | NULL | NULL |
| 7788 | Scott | analyst | Jones | manager |
| 7782 | Clark | manager | King | president |
| 7698 | Blake | manager | King | president |
| 7654 | Maritn | salesman | Blake | manager |
| 7566 | Jones | manager | King | president |
| 7521 | Ward | salesman | Blake | manager |
| 7499 | Allen | salesman | Blake | manager |
| 7369 | Smith | clerk | Ford | analyst |
+-------+---------+-----------+---------+-----------+
14 rows in set (0.00 sec)
7.显示所有雇员的姓名和入职日期，他们在他们领导之前进入公司，连同他们的领导的姓名和入职日期一起显示。
mysql> select e.empname as '雇员姓名',e.hiredate,m.empname as '领导姓名',m.hiredate from emp as e join emp as m on e.empleaderno=m.empno and e.hiredate<m.hiredate;
+--------------+------------+--------------+------------+
| 雇员姓名 | hiredate | 领导姓名 | hiredate |
+--------------+------------+--------------+------------+
| Smith | 1980-12-17 | Ford | 1981-12-03 |
| Allen | 1981-02-20 | Blake | 1981-05-01 |
| Ward | 1981-02-22 | Blake | 1981-05-01 |
| Jones | 1981-04-02 | King | 1981-11-17 |
| Blake | 1981-05-01 | King | 1981-11-17 |
| Clark | 1981-06-09 | King | 1981-11-17 |
+--------------+------------+--------------+------------+
6 rows in set (0.00 sec)
719
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
