- 子查询(查询中的查询)
外层查询+内层查询=以子查询进行查询
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表中
构造子查询的方法:为问题里的各个部分设计小型查询,然后研究这些查询并找出结合它们的方式。
大多数情况下,如果不用子查询,可以改用联接。
- 外联接、自联接与联合
外联结返回某张表的所有行,并带有来自另一张表的条件相符的行。使用内连接时,虽然要比对来自两张表的行,但表的顺序并无影响。
外联结更注重两张表之间的关系。
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;
- 以联接代替子查询
子查询:
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');