TASK4--MySQL链接

20190427更新，着重看最后一个题

一、MySQL别名

为表取别名：查询数据时，如果表名很长，使用起来不方便，此时，就可以为表取一个别名，用这个别名来代替表的名称
SELECT * FROM 表名 [AS] 别名；
为字段取别名:在查询数据时，为了使显示的查询结果更加直观，可以为字段取一个别名
SELECT 字段名 [AS] 别名 [，字段名 [AS] 别名，……] FROM 表名；

注意：

1.AS关键字在指定别名的时候可以省略不写

2.不能将字段的别名作为查询条件来使用，但是可以用表的别名

联结（以列为单位对表进行联结，水平方向）

二、INNER JOIN 

可以用where a.id=b.id实现同样的效果，但是一般还是用inner join

from table1 as a INNER JOIN table2 as b on a.id=b.id

select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price
from shopproduct as sp 
inner join product as p
on sp.product_id = p.product_id;

-- 要点1.from子句 
（from shopproduct as sp inner join product as p）这个才是来源 

-- 要点2：on 子句指定联结Key,必须在from和where之间 
-- on sp.product_id = p.product_id。联结条件可以用=/</>/<=/between等谓词

select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price
from shopproduct as sp 
inner join product as p
on sp.product_id = p.product_id
where sp.shop_id ='000A';

-- 联结的过时语法 
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price  
FROM ShopProduct SP, Product P 
WHERE SP.product_id = P.product_id   
AND SP.shop_id = '000A';

-- 要点3：在select子句中指定的列带别名 

 

三、LEFT JOIN、RIGHT JOIN

from table1 as LEFT JOIN table2 as b on a.id=b.id

左外连接（Left join）属于外连接的一种，另外还有右外连接（Right Join），全外连接（Full Join）；
左外连接（Left join）：除了返回两表中满足条件的元组以外，还返回左侧表中不匹配元组，右侧表中以空值（NULL）替代；

四、CROSS JOIN

from table1 as a CROSS JOIN table2 as b 

select sp.shop_id,sp.shop_name,sp.product_id,p.product_name
from shopproduct as sp 
cross join product as p;

CROSS JOIN子句从连接的表返回行的笛卡儿乘积。假设使用CROSS JOIN连接两个表。 结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。 当连接的表之间没有关系时，会使用这种情况。要特别注意的是，如果每个表有1000行，那么结果集中就有1000 x 1000 = 1,000,000行，那么数据量是非常巨大的。
注意：

1.CROSS JOIN子句不具有连接条件。

2.如果添加了WHERE子句，如果T1和T2有关系，则CROSS JOIN的工作方式与INNER JOIN子句类似，如以下查询所示

SELECT 
    *
FROM
    T1
        CROSS JOIN
    T2
WHERE
    T1.id = T2.id;

3.使用案例见：https://www.yiibai.com/mysql/cross-join.html 

五、自连接

有时，一些特殊的查询需要对同一个关系进行连接查询，成为表的自身连接： 即一张表看成是两张表。

 

 

六、UNION

表的加法 union(增加的是记录 竖直方向）和联结不一样

select product_id,product_name from product
union
select product_id,product_name from product2;

-- 集合运算的注意事项 
1.作为运算对象的记录的列数必须相同 
2.作为运算对象的记录的列的类型必须一致 （一定要不同类型的列是，可以使用类型转换函数cast)
3.可以使用任何select语句，但order by子句只能在最后使用一次 (where/group by /having)
 

select product_id,product_name from product where product_type = '厨房用具'
union
select product_id,product_name from product2 where product_type = '厨房用具'
order  by product_id;

4.集合运算符会除去重复的记录 （也可与包含重复的记录all）

select product_id,product_name from product
union all
select product_id,product_name from product2;

 

-- Join新增知识点
SELECT companies.permalink AS companies_permalink,
       companies.name AS companies_name,
       acquisitions.company_permalink AS acquisitions_permalink,
       acquisitions.acquired_at AS acquired_date
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink
   AND acquisitions.company_permalink != '/company/1000memories'
 ORDER BY 1
-- 注意on后面跟了and，你可以想象成where 但是和where不同的是：on...and是在连接之前发生的
-- 如果后面使用where则是在连接之后发生的

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

INNER JOIN产生的结果是两张表的交集，LEFT JOIN产生左表的完全集，右表中若没有匹配则以null值取代。CROSS JOIN将两张表数据进行一个N*M组合，即笛卡尔积。自连接是对于自身的操作，将自己当成两张表操作来提取需要的信息。UNION操作符是用于连接两个以上的SELECT语句，它和INNER JOIN的区别是一个是连接两张表，另一个是连接两个SELECT语句。
联系：都是对行操作

作业：

 

第一个代码

CREATE TABLE Person
(
PersonId INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName  VARCHAR(50) NOT NULL
);

CREATE TABLE Address
(
AddressId INT NOT NULL PRIMARY KEY,
PersonId   INT NOT NULL ,
City   VARCHAR(50) NOT NULL,
State  VARCHAR(50) NOT NULL);
AddressId INT NOT NULL PRIMARY KEY,
PersonId INT NOT NULL ,
City   VARCHAR(50) NOT NULL,
State  VARCHAR(50) NOT NULL
);

INSERT INTO Person(PersonId,FirstName,LastName) VALUES(1,'Lin','Qi');
INSERT INTO Person(PersonId,FirstName,LastName) VALUES(2,'Zhang','Guorong');
INSERT INTO Person(PersonId,FirstName,LastName) VALUES(3,'Deng','ZiQi');



INSERT INTO Address(AddressId,PersonId,City,State) VALUES(1,2,'Guangzhou','Zhongshan');
INSERT INTO Address(AddressId,PersonId,City,State) VALUES(2,1,'Shandong','Jining');

SELECT * FROM Person;

SELECT * FROM Address;

SELECT a.Firstname, a.LastName ,b.City,b.State 
  FROM Person as a
  LEFT JOIN Address as b
    ON a.PersonId=b.PersonId;

 结果：

第二题代码：

CREATE TABLE email
(
Id INT NOT NULL PRIMARY KEY,
Email VARCHAR(50) NOT NULL
);


AddressId INT NOT NULL PRIMARY KEY,
PersonId INT NOT NULL ,
City   VARCHAR(50) NOT NULL,
State  VARCHAR(50) NOT NULL
);

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


SELECT Id,Email 
FROM email 
WHERE id in(
           SELECT MIN(Id) 
             FROM email 
			GROUP BY email HAVING COUNT(Id) > 1
            )
UNION 
SELECT Id,Email 
FROM email 
GROUP BY Email HAVING COUNT(Id) = 1;
            

第二题写的麻烦了，看看答案有什么好思路，再更新 

DELETE a 
  FROM email a,email  b
 WHERE a.Email=b.Email
  AND a.id > b.id; 

SELECT * FROM email;
#################
DELETE a 
  FROM email a,email  b
 WHERE a.Email=b.Email
  AND a.id <> b.id;     -- 这个语句删去了重复的导致最后只剩一行

SELECT * FROM email;