MySql 中的union,join 解析

UNION(并)

关键字UNION会把查询结果集直接合并到一起,同时将会去掉重复记录.
注意 连接的属性的个数一定要相等

建立两张表,音乐戏学生和计算机系的学生

计算机学生表

CREATE TABLE `cstudent` (
    `name` VARCHAR(20) NOT NULL,
    `sex` VARCHAR (6) NOT NULL
) CHARSET = utf8;

音乐系学生表
CREATE TABLE `mstudent` (
    `name` VARCHAR(20) NOT NULL,
    `sex` VARCHAR (6) NOT NULL
)CHARSET = utf8;

插入数据

INSERT INTO `cstudent` VALUES ('小李','男');
INSERT INTO `cstudent` VALUES ('小王','男');
INSERT INTO `cstudent` VALUES ('小高','女');

INSERT INTO `mstudent` VALUES ('小高','女');
INSERT INTO `mstudent` VALUES ('小张','女');
INSERT INTO `mstudent` VALUES ('小赵','男');

使用UNION

SELECT * FROM cstudent
UNION
SELECT * FROM mstudent;

name    sex     
------  --------
小李      男     
小王      男     
小高      女     
小张      女     
小赵      男     

可以看到 小高 女 这条记录只有一条

使用 UNION ALL 不会去掉重复的内容

SELECT * FROM cstudent
UNION ALL
SELECT * FROM mstudent;

name    sex     
------  --------
小李      男     
小王      男     
小高      女     
小高      女     
小张      女     
小赵      男     

可以看到 小高 女 出现了两次

JOIN(连接)

join有内连接,外连接和交叉连接三种方式

首先建立一个部门department表和员工employee表

department表:

CREATE TABLE `department`(
    `dno` INT UNSIGNED comment '部门编号' ,
    `dname` VARCHAR(30) comment '部门名称',
    `dlocation` VARCHAR(30) comment '部门位置',
    PRIMARY KEY (`dno`)
) CHARSET = utf8;

插入数据:

INSERT INTO `department` VALUES (10,'技术部','北京');
INSERT INTO `department` VALUES (20,"运营部","上海");
INSERT INTO `department` VALUES (30,"市场部","深圳");
INSERT INTO `department` VALUES (40,"人事部","广州");
INSERT INTO `department` VALUES (50,"后勤部","上海");

建立emloyee表:

CREATE TABLE `employee`(
    `eno` INT UNSIGNED comment '员工编号' ,
    `ename` VARCHAR(30) NOT NULL comment '员工姓名',
    `eleader` INT UNSIGNED comment '员工领导编号',
    `esalary` INT UNSIGNED comment '员工薪水',
    `dno` INT UNSIGNED comment '员工的部门编号',
    PRIMARY KEY (`eno`)
)CHARSET = utf8;

插入员工信息:

INSERT INTO `employee` VALUES (1000,'小明',1001,999,10);
INSERT INTO `employee` VALUES (1001,'小红',1002,1999,20);
INSERT INTO `employee` VALUES (1002,'小张',1003,2999,30);
INSERT INTO `employee` VALUES (1003,'小李',1004,3499,40);
INSERT INTO `employee` VALUES (1004,'小高',1005,5999,10);
INSERT INTO `employee` VALUES (1005,'Boss',NULL,999999,60);

两个表里的所有数据如下:

department表里的数据为:
    dno  dname      dlocation  
------  ---------  -----------
    10  技术部        北京     
    20  运营部        上海     
    30  市场部        深圳     
    40  人事部        广州     
    50  后勤部        上海     

employee表里的数据为:
   eno  ename   eleader  esalary     dno  
------  ------  -------  -------  --------
  1000  小明         1001      999        10
  1001  小红         1002     1999        20
  1002  小张         1003     2999        30
  1003  小李         1004     3499        40
  1004  小高         1005     5999        10
  1005  Boss       (NULL)   999999        60

内连接

在表关系的笛卡尔积数据记录中,保留表关系中所有匹配的数据记录,舍弃不匹配的数据记录.
内连接一般有自连接,等值连接,不等连接

• 自连接,顾明思议,自己和自己连接
  查询每个员工的姓名,薪水,以及领导姓名

SELECT emp.`ename` , emp.`esalary` ,lea.`ename` leader
FROM employee  emp
INNER JOIN employee lea
ON emp.`eleader` =  lea.`eno`;

查询结果

ename   esalary  leader  
------  -------  --------
小明          999  小红  
小红         1999  小张  
小张         2999  小李  
小李         3499  小高  
小高         5999  Boss  

• 等值连接
  查询每个员工的姓名,部门名称,以及位置

SELECT employee.`ename`, department.`dname`,department.`dlocation`
FROM employee 
INNER JOIN department
ON employee.`dno` = department.`dno`;

查询结果

ename   dname      dlocation  
------  ---------  -----------
小明      技术部        北京     
小高      技术部        北京     
小红      运营部        上海     
小张      市场部        深圳     
小李      人事部        广州     

• 不等连接
  查询员工的编号小于其领导编号的每个员工的编号,员工姓名,领导名字,领导编号

SELECT emp.`ename` ,emp.`eno` ,lea.`ename` leader_name ,lea.`eno` leader_no
FROM employee  emp
INNER JOIN employee lea
ON emp.`eleader` = lea.`eno` AND emp.`eno`<lea.`eno`;

结果

ename      eno  leader_name  leader_no  
------  ------  -----------  -----------
小明        1000  小红                1001
小红        1001  小张                1002
小张        1002  小李                1003
小李        1003  小高                1004
小高        1004  Boss               1005

外连接

在表关系的笛卡尔积数据记录中,不仅保留表关系中所有匹配的数据记录,而且还会保留部分不匹配的数据记录.
外连接分为左外连接,右外连接,和全外连接

• 左外连接也叫左连接 left [outer] join
  查询每个部门的名称,以及每个部门的员工

SELECT department.`dname`,employee.`ename`
FROM  department 
LEFT JOIN employee
ON department.`dno` = employee.`dno` ;

结果

dname      ename   
---------  --------
技术部        小明  
运营部        小红  
市场部        小张  
人事部        小李  
技术部        小高  
后勤部        (NULL)  

• 右外连接也叫右连接 right [outer] join
  查询每个员工名称已经每个员工的部门的名称

SELECT employee.`ename`,department.`dname`
FROM  department 
RIGHT JOIN employee
ON department.`dno` = employee.`dno` ;

结果

ename   dname      
------  -----------
小明      技术部  
小红      运营部  
小张      市场部  
小李      人事部  
小高      技术部  
Boss    (NULL)     

• 全外连接 full [outer] join
  和左外连接,右外连接的用法差不多,就不举例子了