MySQL||数据库查询操作（三）连接查询

建立范例表

为了方便执行操作，我们需要建立一个范例表进行操作，如我们建立一张员工表，表中包含员工的编号，姓名，职务，入职时间，当月收入（美金），当月奖金，系属部门等。

代码如下：

-- 删除数据表  
DROP TABLE IF EXISTS emp ;  
DROP TABLE IF EXISTS dept;    
DROP TABLE IF EXISTS salgrade;  
  
-- 创建数据表
-- 员工表
CREATE TABLE dept (  
    deptno  INT PRIMARY KEY,  -- 部门编号
    dname   VARCHAR(14) ,  -- 部门名称
    loc VARCHAR(13)   -- 部门地址
) ;  
-- 员工表
CREATE TABLE emp (  
    empno   INT PRIMARY KEY,  -- 员工编号
    ename   VARCHAR(10),  -- 员工姓名
    job VARCHAR(9), -- 员工职务
    mgr INT, -- 经理的员工编号
    hiredate DATE,  -- 入职日期
    sal DOUBLE,  -- 员工收入
    comm DOUBLE,  -- 奖金
    deptno  INT -- 部门表外键，代表当前员工属于哪个部门   
);  
alter table emp add foreign key (deptno) references dept (deptno); 
-- 工资等级
CREATE TABLE salgrade (   
    grade INT,   -- 工资等级
    losal DOUBLE,  -- 当前等级最低收入
    hisal DOUBLE   -- 当前等级最高收入
);  
  
-- 插入测试数据 —— dept  
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');  
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');  
INSERT INTO dept VALUES (30,'SALES','CHICAGO');  
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');  
  
-- 插入测试数据 —— emp  
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,str_to_date('17-12-1980','%d-%m-%Y'),800,NULL,20);  
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,str_to_date('20-2-1981','%d-%m-%Y'),1600,300,30);  
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,str_to_date('22-2-1981','%d-%m-%Y'),1250,500,30);  
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,str_to_date('2-4-1981','%d-%m-%Y'),2975,NULL,20);  
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,str_to_date('28-9-1981','%d-%m-%Y'),1250,1400,30);  
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,str_to_date('1-5-1981','%d-%m-%Y'),2850,NULL,30);  
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,str_to_date('9-6-1981','%d-%m-%Y'),2450,NULL,10);  
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,str_to_date('19-04-1987','%d-%m-%Y'),3000,NULL,20);  
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,str_to_date('17-11-1981','%d-%m-%Y'),5000,NULL,10);  
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,str_to_date('8-9-1981','%d-%m-%Y'),1500,0,30);  
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,str_to_date('23-05-1987','%d-%m-%Y'),1100,NULL,20);  
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,str_to_date('3-12-1981','%d-%m-%Y'),950,NULL,30);  
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,str_to_date('3-12-1981','%d-%m-%Y'),3000,NULL,20);  
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,str_to_date('23-1-1982','%d-%m-%Y'),1300,NULL,10);  
  
-- 插入测试数据 —— salgrade  
INSERT INTO salgrade VALUES (1,700,1200);  
INSERT INTO salgrade VALUES (2,1201,1400);  
INSERT INTO salgrade VALUES (3,1401,2000);  
INSERT INTO salgrade VALUES (4,2001,3000);  
INSERT INTO salgrade VALUES (5,3001,9999);  

-- 事务提交  
COMMIT;  

结果如下：

连接查询

通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。

        在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。

        连接可以在SELECT 语句的FROM子句或WHERE子句中建立，似是而非在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。

连接查询有多种方式

• 等值连接：

在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

85标准所定义的FROM子句的连接语法格式为：

FROM (join_table1) (join_table2) WHERE ( join_condition(=) )

92标准所定义的FROM子句的连接语法格式为：

FROM (join_table) (join_type) (join_table) ON (join_condition)

查询范例：

--等值连接 86标准
-- 查询员工基本信息及其所在部门的信息
select * from emp,dept where emp.deptno = dept.deptno
select empno,ename,job,sal,comm,hiredate,deptno,dname,loc from emp,dept where emp.deptno = dept.deptno; -- 这个查询是错误的，因为连接后投影列必须加上表名做前缀，表示列属于哪个表
select emp.empno,emp.ename,emp.job,emp.sal,emp.comm,emp.hiredate,emp.deptno,dept.dname,dept.loc from emp,dept where emp.deptno = dept.deptno;  -- 这个是正确的
select e.empno,e.ename,e.job,e.sal,e.comm,e.hiredate,e.deptno,d.dname,d.loc from emp e,dept d where emp.deptno = dept.deptno -- 一般表名使用别名

--等值连接 92标准
select e.ename,e.job,e,sal,e.comm,d.dname,d.loc from emp e join dept d on e.deptno = d.deptno;

 其他示例可自行实践

非等值连接：

不等值连接查询就是无条件判断，若查询多个表内的数据，其中的数据不会同步，各自把各自的展现出来，没有任何关联。

在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

-- 非等值连接
-- 查询员工姓名，收入信息及其工资的等级
select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
-- 查询员工姓名，收入，经理姓名
select e.empno,e.sal,e.ename 员工姓名,e1.ename 经理姓名 from emp e join emp e1 on e.mgr = e1.empno;
-- 查询员工姓名，收入信息及其工资的等级及员工的经理的姓名
select e.ename,e.sal,e1.ename,s.grade from emp e join salgrade s join emp e1 on (e.sal between s.losal and s.hisal) and e.mgr = e1.empno -- 了解
select e.ename,e.sal,e1.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal join emp e1 on e.mgr = e1.empno -- 推荐

以上的连接最后归结于内连接类型！

 

外连接：

内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

-- 外连接
-- 右外连接
-- 查询员工姓名，收入信息及其工资的所有等级（即便工资等级没有员工也要列出）
select e.ename,e.sal,s.grade from emp e right outer join salgrade s on e.sal between s.losal and s.hisal;
-- 左外连接
select e.ename,e.sal,s.grade from salgrade s left join emp e on e.sal between s.losal and s.hisal;
-- 全外连接
select e.ename,e.sal,s.grade from salgrade s full outer join emp e on e.sal between s.losal and s.hisal; -- 这个会报错，因为mysql不支持全外连接