SQL必知必会（2）

将之前学习的数据库知识在整理下，主要是看的《SQL必知必会》。这本书不愧是经典，入门数据库真的完全足够啦！

image

分组数据

group by

分组数据主要是靠group by 和 having子句来实现的。

select vend_id, count(*) as num_prods   -- * 表示统计总数，起别名
from products
group by vend_id

group by子句的相关规定：

1. 子句中可以包含任意数目的列
2. 子句中列出的每列都必须是检索列或者有效的表达式，不能是聚集函数
3. 子句中不可带有长度可变的数据类型
4. 如果分组列中含有NULL行，不管是一行还是多行，都当做一行进行返回
5. group by子句必须在where语句之后，在order by子句之前

过滤分组having

where指定的过滤行而不是分组；having支持所有的where操作符。

• where过滤行
• having过滤分组

select cust_id, count(*) as orders 
from orders 
group by cust_id   -- 先分组
having count(*) >= 2;  -- 再过滤分组

笔记：where在分组前进行过滤；having在数据分组后进行过滤

select vend_id, count(*) as num_prods
from products
where prod_price >= 4   -- 过滤价格
group by vend_id
having count(*) >= 2;  -- 过滤分组之后的计数

笔记：having和group by应该结合使用；where子句值标准的行级过滤。

Order by 和group by 区别

Order by	group by
对产生的输出排序	对行分组，但是输出可能不是分组的顺序
任意的列都可以使用	只能使用选择列或者表达式列
不一定需要	如果是和聚合函数一起使用列（表达式），必须使用

大多数情况下，group by分组的数据确实是以分组顺序输出的。

group by和order by子句最好结合使用

select order_num, count(*) as items 
from orderitems
group by order_num    --分组数据
having count(*) >= 3  -- 筛选
order by items, order_num  -- 将分组之后的结果进行排序，默认是升序

select子句顺序

1. select
2. from
3. where
4. group by
5. having
6. order by

子查询

利用子查询进行过滤

select cust_id  -- 2. 从已经查询出的order_num查询出cust_id
from orders
where order_num in (   
    select order_num   -- 1. 先查询出order_num
  from orderitems 
  where prod_id = 'RGANO1'
)

笔记：

1. 子查询总是从里向外执行；
2. 将子查询分解成多行，同时进行适当的缩进
3. 不能嵌套太多的子查询

创建计算字段使用子查询

select cust_name,
             cust_state,
             (select count(*)   -- 统计总数
       from orders
       where orders.cust_id=customers.cust_id) as orders  --子查询创建计算字段orders
from customers
order by cust_name;

笔记：在select语句中操作多个表，应该使用完全限制列名orders.cust_id=customers.cust_id来避免歧义。

联结表

SQL最强大的功能之一就是在查询的过程中使用联结表。联结是一种机制，用来在一条select语句中关联表。

创建联结

select vend_name, prod_name, prod_price
from vendors, products   -- 需要联结的两个表
where vendor.cust_id = products.cust_id  -- 通过两个表中的相同字段进行联结

笔记：必须有where子句。如果没有，则返回的是笛卡尔积（没有联结条件的表返回的结果，有时候也称之为叉联结cross join）。

内连接inner join

内连接也叫等值连接，基于两个表之间的等值测试。

select vend_name, prod_name, prod_price
from Vendors inner join Products   -- 内连接：inner join ... on ...
on Vendors.vend_id = Products.vend_id

高级连接

使用表别名

select cust_name, cust_contact
from customers as C, orders as O, orderitems as OI   -- 内连接的表起别名
where C.cust_id = O.cust_id
and OI.order_num = O.order_num
and prod_id = 'RGAN01';

自连接

要求：给和Jim Jones同一公司的所有顾客发邮件

子查询实现

select cust_id, cust_name, cust_contact   -- 2. 再从customers表中找出该公司的相关人员信息
from customers
where cust_name = (select cust_name   -- 1. 利用子查询先锁定JJ工作的公司名字
                                 from Customers
                                     where cust_contact = 'Jim Jones');

自连接实现

select C1.cust_id, C1.cust_name, C1.cust_contact  
from customers as C1, customers as C2
where C1.cust_name = C2.cust_name
and C2.cust_contact = 'Jim Jones';

外连接

外连接中包含了那些在相关表中没有关联行的行，包含两种情况：

• 左连接
• 右连接

select customers.cust_id, orders.order_num
from customers left outer join orders   -- 左连接
on customers.cust_id = orders.cust_id;  -- customers表在前面

select customers.cust_id, orders.order_num
from customers right outer join orders   -- 右连接
on orders.cust_id = customers.cust_id;

在使用outer join时候，必须指定left或者right关键。

带有聚集函数的连接

select customers.cust_id, 
             count(order.order_num) as num_ord  -- 筛选两个信息，一个带上别名
from customers inner join orders  -- 通过内连接方式
on customers.cust_id = orders.cust_id   -- 连接的条件指定
group by customers.cust_id;  -- 分组条件指定

总结

1. 一般使用内连接，外连接也是有效的
2. 提供连接条件，否则得到的是笛卡尔积
3. 一个联结中可以包含多个表；每个联结可以采用不同的联结类型