《SQL基础教程》笔记（四）
sql基础教程
第六章汇总和分组数据
6.1使用聚合函数
min(expr) 最小值
max（expr)最大值
sum（expr）值的总和
aug（expr)值的平均值（算术平均数）
count（expr）expr中非空值的个数
count（expr）表或集合的行数
6.2创建聚合表达式
聚合表达式不能出现在where子句里：
select title_id
from titles
wheresales=max(sales);
不能在select中混合使用聚合和非聚合:
selecttitle_id,max(sales)
from titles;
可在select子句中使用多个聚合：
selectmin（sales），max（sales）
from titlesl;
除非对于分组列，可以混合：
selecttype,sum(sales)
from titles;
group by type;
不可嵌套聚合函数
可以在子查询中使用聚合表达式：
selecttitle_id,price
from titles
where sales=
select max(sales)from titles;
7.可以在聚合表达式中使用子查询
6.9使用groupby 分组行
select
au_id
count(*) as“num_books”
from title_authors
group by au_id;
group在where之后，orderby 之前
6.10使用having筛选分组
having子句设置groupby 的条件，类似where与select结合
having在group之后，order之前
应用顺序：
1.where子句从from和join子句指定的运算结果筛选行
2.groupby子句对where子句的输出进行分组
3.having子句对分组后的结果筛选行
select
au_id
count(*) as“num_books”
from title.authors
group by au_id
having count(*)>=3;
第7章联结
联结的类型：
交叉联结crossjoin
自然联结naturaljoin
内联结innerjoin
左外联结leftjoin
右外联结rightouter join
全外联结fullouter join
自联结self-join
7.4使用join或where创建联结
join：
selectau_fname,au_lname,a.city
from authors a
inner joinpublishers p
on a.city=p.city
where:
selectau_fname,au_lname,a.city
from authors a,publishers p
where a.city=p.city;
7.5使用crossjoin创建交叉联结
交叉联结返回两个表中行所有可能组合，结果包含第一个表的所有行，第一个表的每一行于第二个表的所有行组合
select
au_id
pub_id
a.state as“au_state”
p.state as“pub_state”
from authors a
cross join publihersp;
where:
select au_id,pub_id,
s.state as“au_state”,
p.state as“pub_state”
from authorsa,publishers p;
检索authors和publishers的所有列：
select *
from authors
cross joinpublishers
where:
select *
fromauthors,publishers;
7.6使用naturaljoin创建自然联结
自然联结是等值联结的一个特例，它比较一个表中的所有的列和其他表中具有相同名称的列的等同性
select
title_id
pob_id,
pub_name
from publishers
natural titles;
7.7使用innerjoin 创建内联结
使用比较操作符匹配两个表的行（基于每一个表同名列的值）
select
a.au_id,
a.au_fname,
a,au_lanme.
ta.title_id
from jointitle_authors ta
on a.au_id =ta.au_id
order by au_idasc,ta.title_id asc
select
a.au_id,
a.au_fname,
a.au_lname,
a.city
a.state
from authors a
inner joinpublishers p
on a.city=p.city
and a.state=p.state
order by a.au_id;
1.使用outerjoin创建外联结
创建左外联结
select columns
from left_table
left []outer] join right_table
on join_conditions
创建全外联结
select columns
from left_table
full [outer] joinright_table
on join_conditions
7.9创建自联结
经常被使用在由有反身联系的表中（reflexxiverelationship)(反身联系是指主健/外健同为一个表中的列或列的组合
创建自连结
select columns
from table[as]alias1
inner join table[as] alias2
on join_conditions
第8章子查询
子查询也称作内查询，包含子查询的有语句被称作外查询
关键字in引出子查询来测试成员资格
select pub_name
from publishers
where pub_id in
(select pub_id
from titles
wheretype='biography');
8.4简单子查询（simplesubquery)也叫非相关子查询（noncorrelatedsubquery)：指能够独立于外部查询的子查询
相关子查询（correlatedsubquery)无法独立于外部查询，它是依赖于外部查询结果的内部查询
相关子查询提供比简单子查询更有力的数据检索机制
简单查询：
select au_id,city
from authors
where city in
('new york','sanfranciscio');
相关子查询
selectcandidate.title_id,
candidate.type,
candidate.salees
from titlescandidate
where sales >=
(select avg (sales)
from titles average
whereavenge.type=canadidate.type)
语义上的等价并不意味着查询的速度相同
8.10all
select tl.type
from titles t1
group by t1.type
having max(t1.sales)>= all
(select 2.0* avg(t2.sales)
from titles t2
wheret1.type=t2.type);
8.11any
selectau_id,au_lname,au_fname,city
form authors
where city=any
(select city
from publishers);
8.12使用exists检测存在性
select pub_name
from publishers
where exists
(select *
from titles t
wheret.pub_id=p.pub_id
andtype='biography');
以下6个查询语义等价
select distincta.au_id
from authors a
inner jointitle.authors ta
on a.au_id=ta.au_id;
select distincta.au_id
from authorsa.title_authors ta
wherea.au_id=ta.au_id;
select auid
from authors a
where au_id in
(select au_id
from title_authors);
select au_id
from authors a
where au_id=any
(select au_id
from title authors)
select au_id
from authors a
where exists
(select *
from title.authorsta
whereaau_id=ta.au_id);
select au_id
from authors a
(select count(*)
from title_authorsta
wherea.au_id=ta.au_id();
第九章集合操作
9.1使用union合并行
union去重，unionall 不去重
select state fromauthors
union
select state frompublishers;
9.2使用intersect查找相同行
select city
from authors
intersect
select city
from publishers;
9.3使用except查找不同行
select city
from authors
except
select city
from publishers;
第10章插入、更新、删除行
10.2使用insert插入行
10.3使用update更新行
update tables
set contract=0;
104使用delete删除行
delete fromtitle.authors
where title_id in
(select title_id
from titles
where pub_id('p01','p04')));
第11章创建、更改和删除行
11.2约束定义了列允许值的规则，dbms使用这些规则自动强制性地保证信息的完整性
11.3
使用createtable创建新表
11.4
使用notnull 禁止空值
11.5
使用default确定默认值
11.6
使用primarykey指定主键
11.7
使用foreignkey 指定外键
11.8
使用unique确保值唯一
11.9
使用check创建检查约束
11.10使用createtemporary table创建临时表
11.11
使用createtable as 利用已存在表创建新表
create globaltemporary table titles2 as
selecttitle_name,sales
from titles
where pub_id='p01';
11.12使用alter table修改表
alter table authors
add email_addresschar(25);
11.13
使用droptable删除表
drop tableroyalitues;
标准的sql允许指明restrict限制（安全）或cascade（级联）（不安全）删除行为