Head First SQL 第八章

联接与多张表的操作！

数据库里面有多张表很常见，但是现在要学习的是操控多张表的技术工具。

表里面的某列可能有很多重复出现的值，这时候我们需要预填充比较方便。

还记得从表中检索

status, profession, interests, seeking 值得查询，但是又把数据值过滤为不重复的形式的方法吗？

SELECT status FROM my_contacts
GROUP BY status
ORDER BY status;

记住 ORDER 要最后出现。

=================================================================

但是如果 interests 里面没有原子性，比如一列的某行有多个兴趣，就无法这样 SELECT

=================================================================

成千上万的数据手动输入辛苦又容易出错，所以交给SQL代劳。

利用SUBSTRING_INDEX 函数，将复制第一项兴趣到新建的 interest1 列。

这个函数在第五章有说，可以复习一下：

逗号前的所有内容,这里1表示第一个逗号，如果是2就是第二个逗号前面的内容：

  

SUBSTRING_INDEX 寻找单引号里面的字符串，取出它前面的所有内容

  location是列名称

SELECT SUBSTRING_INDEX(location, ‘,’, 1) FROM my_contacts;

继续，

这里应该这样：

UPDATE my_contacts
SET interest1 = SUBSTRING_INDEX(interests, ',', 1);

现在我们需要把

first 移除，因为已经放在新的interest1 列里面去了。

移除方法是

UPDATE my_contacts
SET interests = SUBSTR(interests, LENGTH(interest1)+2);

抓取兴趣列(interests)中的字符串并返回部分字符串。

SUBSTR接受字符串并切除我们用括号指定的第一部分，返回剩下部分。

下面的 +2是逗号和空格的长度。

所以上述代码从原本的interests 列的左起移除了5+2=7个字符。

======================================================================

创建一个 profession表，里面一列是prof_id ，一列是profession，好几种方法：

1.

CREATE TABLE,然后 SELECT 来 INSERT

CREATE TABLE profession
(id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
profession varchar(20)
);
INSERT INTO profession (profession)
    SELECT profession FROM my_contacts
    CROUP BY profession
    ORDER BY profession;

首先创建带有主键列的 profession表，用VARCHER类型储存职业，然后用 SELECT查询结果填满profession表的profession列。

2.

利用SELECT 进行 CREATE TABLE 然后 ALTER 添加主键。

CREATE TABLE profession AS
    SELECT profession FROM my_contacts
    GROUP BY profession
    ORDER BY profession;
ALTER TABLE profession
ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

首先创建一列只有profession的表，然后填入 SLECT 查询结果，再ALTER修改表添加主键字段。

3.CREATE TABLE的勇士设置主键并利用 SELECT填入数据。

就是说创建 profession表同时设置主键以及另一个 VARCHAR 类型的列来存储职业，同时填入 SELECT 的查询结果。

SQL 具有 AUTO_INCREMENT功能，所以RDBMS知道ID 列需要自动填入，因此只剩一列，也就是 SLECT数据该填入的地方。

CREATE TABLE profession
(
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
profession varchar(20)
) AS
    SELECT profession FROM my_contacts
    GROUP BY profession
    ORDER BY profession;

AS 就是引用某个查询结果安插至另一个表中。P353

========================================================================================

AS到底是怎么回事？

AS可以把 SELECT 的查询结果填入新表中。我们在上面的例子中使用 AS 的时候，其实是要求软件把来自 my_contacts 表的内容当成 SELECT的查询结果，并把结果值存入新建的 profession 表中。

如果我们没有给新表设计两个列， AS 只会创建一列，并采用 与 SELECT 的查询结果相同的列名与数据类型。

另外，SQL 允许我们暂时对表和列赋予新名称，这项功能称为 别名。

创建别名很简单：

CREATE TABLE profession
(
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
mc_prof varchar(20)
) AS
SELECT profession AS mc_prof FROM my_contacts
GROUP BY mc_prof
ORDER BY mc_prof;

多了一个 AS  mc_prof 而已。

别名改变了查询结果中的列名，但是没有改变来源列的名称。别名是临时的。

新表中依然是名为 profession 的列，而不是 mc_prof。

啥时候才需要表的别名呢？

我们马上学习 join ，那时候就需要了，

创建表的别名也是一样的：

SELECT profession AS mc_prof
FROM my_contacts AS mc
GROUP BY mc_prof
ORDER BY mc_prof;

其实也可以省略 AS

SELECT profession mc_prof
FROM my_contacts mc
GROUP BY mc_prof
ORDER BY mc_prof;

效果一样，只要别名紧接着原始表明和列名就行了。P356

=============================================================================

接下来我们要了解 联接 JOIN

假设你有一个储存男孩姓名的表和一个记录男孩们有哪些玩具的表。

看看代码：

注意速记符号，点号前面是表名，点号后面是列名，这里用别名t代替表的全名：

这里的意思是，从boy表中 SELECT boy列，从 toy表中SELECT toy列

CROSS JOIN返回两张表每一行相乘的结果

SELECT t.toy, b.boy
FROM toys AS t
    CROSS JOIN
    boys AS b;

如上图所示，四个男孩，五个玩具，返回了20条记录。P358

最好不要对很大的表做交叉连接。这样查询结果非常庞大。

交叉连接的简略写法：

SELECT toys.toy, boys.boy
FROM toys, boys;

效果一样，用逗号代替 CROSS JOIN

==============================================================================================

内联接：INNER JOIN 利用条件判断中的比较运算符结合两张表的记录。只有联接记录符合条件时才会返回列。

SELECT somecolumns
FROM table1
    INNER JOIN
    table2
ON somecondition;

ON 也可以改成 WHERE，条件式里面可以采用任何一个比较运算符。

下两表中，每个男孩只有一个玩具。

找出每个男孩有什么玩具(内联接-相等联接)：

SELECT boys.boy, toys.toy
FROM boys
INNER JOIN
toys
ON boys.toy_id = toys.toy_id;

内联接-不等联接：

我们可以看出每个男孩没有的玩具

SELECT boys.boy, toys.toy
FROM boys
INNER JOIN
Toys
ON boys.toy_id <> toys.toy_id
ORDER BY boys.boy;

内联接-自然联接：

SELECT boys.boy, toys.toy
FROM boys
NATURAL JOIN
toys;

自然联接会识别出每个表里的相同名称并且返回相符的记录，这里的例子结果和相等联接一样。

P370

=============================================================================================================

然后就是例题和复习了。