【数据库】MySQL 基础 （三）- 表联结

2.2 MySQL 基础 （三）- 表联结

#学习内容#

• MySQL别名

• INNER JOIN

• LEFT JOIN

• CROSS JOIN

• 自连接

• UNION

• 以上几种方式的区别和联系

 

#作业#

项目五：组合两张表 （难度：简单）

在数据库中创建表1和表2，并各插入三行数据（自己造）

表1: Person

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+

| PersonId | int |

| FirstName | varchar |

| LastName | varchar |

+-------------+---------+

PersonId 是上表主键

 

表2: Address

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+t

| AddressId | int |

| PersonId | int |

| City | varchar |

| State | varchar |

+-------------+---------+

AddressId 是上表主键

 

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

 

项目六：删除重复的邮箱（难度：简单）

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

+----+---------+

| Id | Email |

+----+---------+

| 1 | a@b.com |

| 2 | c@d.com |

| 3 | a@b.com |

+----+---------+

Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+

| Id | Email |

+----+------------------+

| 1 | a@b.com |

| 2 | c@d.com |

+----+------------------+

一、学习内容：

MySQL别名

为了更方便的进行查询，当表的名字很长或者执行一些特殊查询时，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，以替代表原来的名称。

SELECT o.city, o.phone FROM offices AS o;

INNER JOIN

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

注释：INNER JOIN 与 JOIN 是相同的。

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

LEFT JOIN

 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

RIGHT JOIN

读取右边数据表的全部数据，即便左边边表无对应数据。

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

CROSS JOIN

交叉连接，得到的结果是两个表的乘积把表A和表B的数据进行一个N*M的组合，即[笛卡尔积]

select * from A cross join B;

自连接

把一张表当成两张表来使用。

mysql有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名

SELECT a.ename '备注aname', b.ename '备注bname' 
FROM emp a LEFT JOIN emp b ON a.leaderid = b.eid;

UNION

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。

多个 SELECT 语句会删除重复的数据。

MySQL UNION 操作符语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

以上几种方式的区别和联系

• INNER JOIN:全连接，求交集，链接字段相等
• LEFT JOIN：左连接，返回左表中所有记录和右表字段相等的记录
• CROSS JOIN：表A和表B的数据进行一个N*M的组合
• 自连接：本质是把一张表当成两张表来使用
• UNION：用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。

作业：

项目五：组合两张表 （难度：简单）

在数据库中创建表1和表2，并各插入三行数据（自己造）

表1: Person

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+

| PersonId | int |

| FirstName | varchar |

| LastName | varchar |

+-------------+---------+

PersonId 是上表主键

 

表2: Address

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+t

| AddressId | int |

| PersonId | int |

| City | varchar |

| State | varchar |

+-------------+---------+

AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State。

 

解答：

1、新建Person表、Address表：

CREATE TABLE `person` (
  `PersonId` int(11) AUTO_INCREMENT,
  `FirstName` varchar(255) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  PRIMARY KEY (`PersonId`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF-8


CREATE TABLE `address` (
  `AddressId` int(11) NOT NULL,
  `PersonId` int(11) NOT NULL,
  `City` varchar(255) NOT NULL,
  `State` varchar(255) NOT NULL,
  PRIMARY KEY (`AddressId`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF-8

结果如下： 

 

2、插入数据：

INSERT INTO Person (PersonId, FirstName, LastName) VALUES (1, 'zhang', 'san');
INSERT INTO Person (PersonId, FirstName, LastName) VALUES (2, 'Li', 'si');
INSERT INTO Person (PersonId, FirstName, LastName) VALUES (3, 'Wang', 'wu');

INSERT INTO Address (AddressId, PersonId, City, State) VALUES (22, 1, 'xian', 'shanxi');
INSERT INTO Address (AddressId, PersonId, City, State) VALUES (3, 2, 'chengdu', 'sichuan');
INSERT INTO Address (AddressId, PersonId, City, State) VALUES (10, 3, 'nanjing', 'jiangsu');

结果如下： 

 3、编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State。

   SELECT p.FirstName, p.LastName, a.City, a.State 
     FROM person p 
LEFT JOIN address a 
       ON p.PersonID = a.PersonID

结果如下：

 

项目六：删除重复的邮箱（难度：简单）

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

+----+---------+

| Id | Email |

+----+---------+

| 1 | a@b.com |

| 2 | c@d.com |

| 3 | a@b.com |

+----+---------+

Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+

| Id | Email |

+----+------------------+

| 1 | a@b.com |

| 2 | c@d.com |

+----+------------------+

解答：

1、创建e-mail表，插入表数据

CREATE TABLE `email` (
  `ID` int(11) NOT NULL,
  `Email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
);

INSERT INTO email VALUES(1,'a@b.com');
INSERT INTO email VALUES(2,'c@d.com');
INSERT INTO email VALUES(3,'a@b.com');

结果：

2、查询语句，删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个：

DELETE e1 FROM email AS e1 ,email AS e2
WHERE e1.Email =e2.Email AND e1.Id > e2.Id;
SELECT Id,Email FROM email;

结果如下：