【MySQL】4.表联结

1.MySQL别名

1.1列别名

别名是一个字段或值的替换名。别名用AS关键字赋予。比如：

SELECT PTRIM(vend_name)+'("PTRIM(vend_country)+')'
       AS vend_title
FROM Vendors
ORDER BY vend_name;

1.2表别名

SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做主要有两个理由：

（1）缩短SQL语句

（2）允许在一条SELECT语句中多次使用相同的表

比如：

SELECT cust_name,cust_contact
FROM Customers AS C,Orders AS 0,OrderItems AS OI
WHERE C.cust_id=0.cust_id
 AND OI.order_num=0.order_num
 AND prod_id='RGAN01';

上面句子中FROM子句中的三个表全都有别名。

注意表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户端。

 

2.内联结 INNER JOIN

内联结也称为等值联结，它基于两个表之间的相等测试，我们可以明确指定联结的类型。比如：

SELECT vend_name,prod_nmae,prod_price
FROM Vendors INNER JOIN Products
ON Venders.vend_id=Products.vend_id

这里，两个表之间的关系是以INNER JOIN指定的部分FROM子句。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。

 

3.OUTER JOIN

OUTER JOIN 也叫外联结,许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。

比如：计算平均销售规模，包括那些至今尚未下订单的顾客。这个例子中，联结包含了在相关表中没有关联行的行。

下面的SELECT语句给出了一个简单的内联结，它检索所有顾客及其订单：

SELECT Customers.cust_id,Orders.order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id=Oders.cust_id

外联结语法类似。要检索包括没有订单顾客在内的所有顾客，可如下进行：

SELECT Customers.cust_id,Order.cust_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id=Orders.cust_id;

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表，RIGHT指出的是OUTER JOIN右边的表，LEFT指出的是OUTER JOIN右边的表，我们一般用LEFT，上面例子从Customer表中选择所有行。

 

4.CROSS JOIN

CROSS JOIN又称为笛卡尔乘积，实际上是把两个表乘起来。

语法：

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]

或者：SELECT * FROM [TABLE 1], [TABLE 2]

乘法细节参考：https://blog.csdn.net/qq_26320085/article/details/78644523

 

5.自联结

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句，虽然最终结果相同，但其速度快得多。

比如我们要给首先找出与Jim同一公司的所有顾客，从子查询语句：

SELECT cust_id,cust_name,cust_contact
FROM Customers
WHERE cust_name=(SELECT cust_name
                 FROM Customers
                 WHERE cust_contact='Jim');

我们用自联结语句时需要用表别名，如下：

SELECT c1.cust_id,c1.cust_name,c1.cust_contact
FROM Customers AS c1,Customers AS c2
WHERE c1.cust_name=c2.cust_name
AND c2.cust_contact='Jim';

 

6.UNION

6.1组合查询

多数SQL查询只包含从一个或多个表中返回数据的单条SELECT语句。但是SQL也允许执行多个查询（多个SELECT语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（UNION）或复合查询。

主要有两种情况需要使用组合查询：

（1）在一个查询中从不同的表返回结构数据；

（2）对一个表执行多个查询，按一个查询返回数据。

6.2使用UNION

使用UNION比较简单，所要做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION。

比如我们要把下面两条查询组合：

SELECT cust_name,cust
FROM Customers
WHERE cust_state IN('IL','IN','MI');

SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_name='Fun4All';

我们可以如下组合：

SELECT cust_name,cust
FROM Customers
WHERE cust_state IN('IL','IN','MI')
UNION
SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_name='Fun4All';

UNION指示执行这两条SELECT语句，并把输出组合成一个查询结果集。

这里有与上面相同的查询语句：

SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_state IN('IL','IN','MI')
OR cust_name='Fun4ALL';

 

7.项目实战

7.1项目五 组合两张表

任务要求：

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

 

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

（1）新建表：

CREATE TABLE Person(
PersonID INT PRIMARY KEY,
FirstName VARCHAR(10),
LastName VARCHAR(10)
);

CREATE TABLE Address(
AddressId INT PRIMARY KEY,
PersonId INT,
City VARCHAR(10),
State VARCHAR(10)
);

（2）插入数据

INSERT INTO Person VALUE('1','wang','xiaoming');
INSERT INTO Person VALUE('2','wangm','xiaomingm');
INSERT INTO Person VALUE('3','wangl','xiaomingq');

INSERT INTO Address VALUE('10','1','chengdu','china');
INSERT INTO Address VALUE('11','2','chengduC','china');
INSERT INTO Address VALUE('12','4','chengduB','china');

（3）查询

SELECT Person.FirstName,Person.LastName,Address.City,Address.State
FROM Person LEFT OUTER JOIN Address
ON Person.PersonId=Address.PersonId;

 

结果：

 

7.2项目六：删除重复的邮箱

任务要求：

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

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

（1）创建表：

CREATE TABLE email(
Id INT PRIMARY KEY,
Email VARCHAR(10)
);

（2）插入行：

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

（3）删除和查询

DELETE a.*
FROM email AS a,email AS  b
WHERE a.Email=b.Email
AND a.ID>b.ID;

SELECT *
FROM email;

结果如下：