HeadFirstSQL学习笔记（三）

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

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

selectmc.first_name,mc.last_name,mc.phone,jc.title -- 外层from job_current as jc natural join my_contacts as mcwherejc.title in (select title fromjbo_listings);  -- 内层

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

示例：

select last_name,first_namefrom my_contactswhere zip_code=(select zip_codefrom zip_code where city='Memphis'and state='TN')-- 这个查询从my_contacts中选出住在Memphis,TN的联络人的姓名-- 也可以只用联接select last_name,first_namefrom my_contactsnatural join zip_code zcwhere 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_namefrom my_contacts as mc;select max(salary) from job_current;

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

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

子查询作为欲选取的列：

子查询能够用作select语句中选取的列之一：

select mc.first_name,mc.last_name,(select statefrom zip_codewhere mc.zip_code=zip_code)as statefrom my_contacts mc;-- 这段用途是查找my_contacts表的每一行，取出每一行的姓、名、州名等信息（关于州名，利用子查询比对my_contacts与zip_code表记录的邮政编码，再从zip_code表中取出州名信息），实际上my_contacts表中没有州名，只有邮编，但是需要的是州名。

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

子查询搭配自然联接：

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

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

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

关联子查询：

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

select mc.first_name,mc.last_namefrom my_contacts as mc  -- my_contacts的别名创建在外层查询中where3=(select count(*) from contact_interestwhere 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.emailfrom my_contacts mcwhere not exists(select * from job_currnt jcwhere mc.contact_id=jc.contact_id);-- not exists 负责找出行名与电子邮件地址，他们都未列在job_contact表中

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

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

 

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

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

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

LEFT OUTER JOIN

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

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

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

外联接与多个相符结果：

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

右外联接

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

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

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

自引用外键：

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

连接表与它自己

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

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

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

select title from job_current by titleunionselect title from job_desiredunionselect title from job_listings;

联合规则：

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

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

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

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

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

从联合创建表：

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

INTERSECT与EXCEPT

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

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

select title from job_currentintersectselect title from job_desired;

 

select title from job_currentexceptselect title from job_desired;

3. 以联接代替子查询

子查询：

select mc.first_name,mc.last_name,mc.phone,jc.titlefrom job_current as jcnatural join my_contacts as mcwhere jc.title in ( select title from job_listing);-- 可使用inner join 替代包含子查询的where语句：select mc.first_name,mc.last_name,mc.phone,jc.titlefrom job_current as jcnatural join my_contacts as mcinner join job_listings j1on jc.title;

把自连接变成子查询

变身前：

select c1.name,c2.name as bossfrom clown_info c1inner join clown_info c2 on c1.boss_id=c2.id;

变身后：

select c1.name,(select name from clown_infowhere c1.boss_id=id)as bossfrom 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_contactsadd constraint check gender in('M','F');