HeadFirstSQL学习笔记（三）

1. 子查询（查询中的查询）

外层查询+内层查询=以子查询进行查询

selectmc.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 fromjbo_listings);  -- 内层

因为查询里使用了=运算符，所以子查询只会返回单一值，特定行和列的交叉点，SQL称之为标量值。

示例：

select last_name,first_name
from my_contacts
where zip_code=(select zip_code
from zip_code where city='Memphis'and state='TN')
-- 这个查询从my_contacts中选出住在Memphis,TN的联络人的姓名
-- 也可以只用联接
select last_name,first_name
from my_contacts
natural join zip_code zc
where zc.city='Memphis'
and state='TN'

SQL子查询规则：

子查询总是位于圆括号里、没有属于自己的分号；

子查询可能出现在查询中的四个地方：select子句、选出columnlist作为其中一列、from子句、having子句。

如何构造子查询

需求：在我所有的联络人里，谁赚钱最多？

分解问题：

My_contacts表中的list_name与last_name列；job_current表的max(salary)

找出分解问题的查询：

select mc.first_name,mc.last_name
from my_contacts as mc;
select max(salary) from job_current;

找出串起两个查询的方式：

-- 需要一个自然连接来找出每个人的薪资信息
select mc.first_name,mc.last_name,jc.salary
from my_contacts as mc
natural join job_current as jc;
-- 接下来加上where子句以连接两段查询
Select mc.first_name,mc.last_name,jc.salary
From my_contacts as mc
Natural join job_current as jc
Where jc.salary=
(select max(jc.salary)from job_current jc);

子查询作为欲选取的列：

子查询能够用作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;
-- 这段用途是查找my_contacts表的每一行，取出每一行的姓、名、州名等信息（关于州名，利用子查询比对my_contacts与zip_code表记录的邮政编码，再从zip_code表中取出州名信息），实际上my_contacts表中没有州名，只有邮编，但是需要的是州名。

子查询放在select语句中，用于表示某个欲选取的列，则一次只能从一列中返回一个值。

子查询搭配自然联接：

selectmc.first_name,mc.last_name,jc.salary  -- 查询的目的是取得姓名与薪资
from
my_contacts as mc nature join job_current as jc
where               -- 只呈现薪资高于andy的人
jc.salary>(jc.salary from my_contactsmc natural join job_current jc where email='andy@xy.com');-- 取得andy薪资的子查询，以供外层查询比较，这部分会首先处理

非关联子查询：如果子查询可以独立运行且不会引用外层查询的任何结果，即称为非关联子查询。内层查询（子查询）先被解释，然后才输出到外层查询。

有多个值的非关联子查询：IN、NOT IN 检查子查询返回的值是否为集合的成员之一。

关联子查询：

内层查询的解析需要外层查询的结果

select mc.first_name,mc.last_name
from my_contacts as mc  -- my_contacts的别名创建在外层查询中
where
3=(
select count(*) from contact_interest
where contact_id=mc.contact_id    -- 子查询也引用了列名mc，外层查询必须先执行，执行后我们才能知道mc.contact_id的值
);   

（这里也就解释了为什么之前的代码要重复写两边as）

子查询与EXISTS\NOT EXISTS

搭配NOT EXISTS的关联子查询：

select mc.first_name firstname,mc.last_name lastname,mc.email
from my_contacts mc
where not exists
(select * from job_currnt jc
where mc.contact_id=jc.contact_id);
-- not exists 负责找出行名与电子邮件地址，他们都未列在job_contact表中

构造子查询的方法：为问题里的各个部分设计小型查询，然后研究这些查询并找出结合它们的方式。

大多数情况下，如果不用子查询，可以改用联接。

 

2. 外联接、自联接与联合

外联结返回某张表的所有行，并带有来自另一张表的条件相符的行。使用内连接时，虽然要比对来自两张表的行，但表的顺序并无影响。

外联结更注重两张表之间的关系。

LEFT OUTER JOIN

接收坐标的所有行，并利用这些行与右表进行匹配。当左表与右表具有一对多关系时，左外连接特别有用。

与内连接的差别：外联结一定会提供数据行，无论该行能否在另一个表中找出相匹配的行。出现NULL是告诉我们没有匹配的行。

select g.girl, t.toy
from toys t   -- 左表
left outer join girls g  -- 右表
on g.toy_id=t.toy_id;

外联接与多个相符结果：

虽然在另一个表中没有相符的记录，但你还是会取得数据行，在匹配出多条记录时就会取出多行。

右外联接

与左外联结完全一样，除了它是用右表与左表比对。

select g.girl,t.toy
from toys t  -- 右表
right outer join girls g -- 左表
on g.toy_id=t.toy_id;

同一个表可以同时作为外联结的左右表

自引用外键：

出于其他目的而用同一张表的主键。比如引用同一张表的id字段，表示出这个id的头领是另一个id

连接表与它自己

select c1.name,c2.name as boss
from clown_info c1
inner join clown_info c2   -- 这里clown_info表会被使用两次,不需要两张相同的表而是使用一张表两次
on c1.boss_id=c2.id;

另一种取得多张表内容的方式：UNION

union根据我们在select中指定的列，把两张或更多张表的查询结果合并至一个表中。

select title from job_current by title
union
select title from job_desired
union
select title from job_listings;

联合规则：

每个select语句中列的数量必须一致，不可以由第一条语句选取了两列，而其他语句却只选取一列；

每个select语句包含的表达式与统计函数也必须相同；

select语句的顺序不重要，不会改变结果；

SQL默认会清楚联合的结果中的重复值，如果需要看到重复数据，可以使用UNION ALL运算符；

列的数据类型必须相同或者可以互相转换，数据会试着转换为相容类型，如果无法转换查询就会失败（如varchar无法转换为整型，所以查询结果会把interger转换成varchar）。

从联合创建表：

create table as 可以捕获union的结果
create table my_union as
select title from job_current union
select title from job_desired
union select title from job_listings;

INTERSECT与EXCEPT

注意：这两个运算符不在MySQL中

使用方式与UNION大致相同，INTERSECT（交集）只会返回同时在第一个和第二个查询中的列，EXCEPT（差集）返回只出现在第一个查询而不在第二个查询中的列。

select title from job_current
intersect
select title from job_desired;

 

select title from job_current
except
select title from job_desired;

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_listing);
-- 可使用inner join 替代包含子查询的where语句：
select mc.first_name,mc.last_name,mc.phone,jc.title
from job_current as jc
natural join my_contacts as mc
inner join job_listings j1
on jc.title;

把自连接变成子查询

变身前：

select c1.name,c2.name as boss
from clown_info c1
inner join clown_info c2 
on c1.boss_id=c2.id;

变身后：

select c1.name,
(select name from clown_info
where c1.boss_id=id)as boss
from clown_info c1;  -- 子查询出现在select的选取列中，且为关联子查询

检查约束：CHECK

约束限定允许插入某个列的值，它与where 子句都使用相同的条件表达式。

create table piggy_bank
(id int auto_increment not null primary key,
 coin char(1) check (coin in('P','N','D','Q'))
) -- 检查硬币值是否以其中之一为单位

注意：MySQL中无法以check强化数据完整性。

alter table my_contacts
add constraint check gender in('M','F');