SQL入门学习笔记3.0

联接与多张表的操作

1. 对表进行规范化

   

   • 首先创建四个列
     
   • 利用SUBSTRING_INDEX函数，复制第一项兴趣并储存到interest1列
     UPDATE my_contacts SET interest1 = SUBSTRING_INDEX(interests, ',', 1);
     
   • 把interests列中的first删除，使用SUBSTR函数
     UPDATE my_contacts SET interests = SUBSTR(interests, LENGTH(interest1) + 2 );
     
   • 以此类推

2. 创建一个表，并填满没有重复，按字母顺序排列的内容的三种方式

   • CREATE TABLE, 然后利用SELECT 进行INSERT
     CREAT TABLE profession
(
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
profession varchar(20)
);
INSERT INTO profession (profession)
SELECT profession FROM my_contacts
GROUP BY profession
ORDER BY profession;

   • LECT 进行CREATE TABLE，然后ALTER 以添加主键
     （利用SELECT 从my_contacts表的自职业列抓出来的数据创建新的profession表，再用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);

   • CREATE TABLE 的同时设置主键并利用SELECT 填入数据
     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 把SELECT的查询结果填入新表中

3. 列的别名
   在查询中首次使用原始列名的地方后接AS并设定要采用的别名
   SELECT profession AS mc_prof FROM my_contacts
GROUP BY mc_prof
ORDER BY mc_prof;

4. 表的别名
   AS
   SELECT profession AS mc_prof
FROM my_contacts AS mc
GROUP BY mc_prof
ORDER BY mc_prof;

5. 交叉联接（笛卡尔积，交叉积）
   交叉联接有助于找出修正联接的方式
   同时查询玩具表的toy列，与男孩表的boy列
   SELECT t.toy, b.boy FROM toys AS T CROSS JOIN boys AS b;

   内联接就是通过查询中的条件溢出了某些结果数据行后的交叉连接

6. 内连接

   • 相等联接
     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;

子查询

1. 什么是子查询
   需要把甲查询的结果作为乙查询的输入

2. 例如：招聘

   • 选取数据库中需招聘的所有职位——内层查询
     SELECT title FROM job_listings
GROUP BY title ORDER BY title;
   • 查询所有的应聘人是否有符合岗位要求的人——外层查询
     SELECT mc.first_name, mc.last_name, mc.phone, jc.title FROM job_current AS jc NATURAL JOIN my_contacts AS mc WHERE
jc.title IN ('cook', 'Waiter', 'Web Developer');

3. 以子查询进行查询= 外层查询+ 内层查询
   SELECT mc.first_name, mc.last_name, mc.phone, jc.title FROM job_current AS jc NATURAL JOIN my_contacts AS mc WHERE
jc.title IN ( SELECT title FROM job_listings);

4. 外部的查询称为包含查询 或外层查询
   内部的查询就是内层查询， 或者子查询

5. 子查询的构造流程

   • 分解问题
   • 找出能够回答部分问题的查询
   • 继续分解问题
   • 找出串起两个查询的方式

6. 子查询能用做SELECT 语句中选取的列之一
   SELECT mc.first_name, mc.last_name,(SELECT state
FROM zip_code
WHERE mc.zip_code = zip_code) AS state FROM my_contacts mc;

7. 子查询搭配自然联接

8. 非关联子查询

   • 定义
     如果子查询可以独立运行且不会引用外层查询的任何结果，成为未关联子查询

9. 有多个值的非关联子查询：IN, NOT IN

   • 定义：非关联子查询使用IN 或NOT IN 来查询子查询返回的值是否为集合的成员之一

10. 关联子查询

    • 定义： 内层查询的解析需要依赖外层查询的结果

11. 搭配EXISTS 与NOT EXISTS 的子查询