文章目录
一.联结表(join)
1.为什么要把数据分多个表进行存储?
为了避免数据冗余,更加高效地进行数据处理,可伸缩性能好。
可伸缩性:能够适应不断增加的工作量而不失败。
2.创建联结表的原因?
联结是一种机制,用来在一条select语句中关联表,用一条语句检索出存储在多个表中的数据。
1.创建联结表
注意:完全限定列名
- 引用的列可能会出现歧义(同名不同表),此时必须使用完全限定列名,使用表名.列名。
1.1 Where子句的重要性(等值联结与笛卡尔积)
Where子句的作用:
作为过滤条件,只包含那些匹配给定条件的行(联结条件)。
这种联结称为等值联结。
如果不使用Where子句:
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
Where Vendors.vend_id = Products.vend_id;
第一个表中的每一行将与第二个表中的每一行配对,而不管逻辑上是否配在一起。即笛卡尔积,也称交叉联结(cross join)。
1.2 内联接
等同于等值联结,但语法稍微不同。
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
1.3 联结多个表
方法:首先列出表名;然后定义表之间关系。
SELECT vend_name, prod_name, prod_price,quantity
FROM Vendors, Products, OrderItems
Where Vendors.vend_id = Products.vend_id
AND OrderItems.vend_id = Products.vend_id
AND order_num = 20007;
联结的表越多,性能下降的越厉害。
2.创建高级联结
2.1 使用表别名
可以为列名、字段名、表名起别名,其目的是:
- 缩短SQL语句;
- 允许在一条SELECT 语句中多次使用相同的表。
SELECT vend_name, prod_name, prod_price,quantity
FROM Vendors AS V, Products AS P, OrderItems AS O
Where V.vend_id = P.vend_id
AND O.vend_id = P.vend_id
AND order_num = 20007;
注意:
1)Oracle中不支持AS关键字,如要使用别名,则可以直接指定即可:
- Vendors V (而不是Vendors AS V,)
2)表别名只在查询执行中使用,与列别名不一样,表别名不返回到客户端。
2.2 使用不同类型的联结
1.自联结(self join)
方法:使用同一个表的不同别名,将相同表连接在一起
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';
一般情况下,使用自联结而不使用子查询:
- 许多DBMS处理联结远比处理子查询快得多。
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers
WHERE cust_name IN (SELECT cust_name
FROM Customers WHERE cust_contact= 'Jim Jones');
2.自然联结(nature join)
对于联结条件中的列,只返回唯一的一列。
方法:自己在SELECT语句中设定。
3.外联结(outer join)
为何使用:
- 当需要包含那些没有关联上的行的时候
包含:
- left outer join
- right outer join
- full outer join**
Access、MySQL、SQLite 不支持 FULL OUTER JOIN。
4.使用带聚集函数的联结
查询:每个顾客,及顾客的下单数
SELECT Customers.cust_id, COUNT(Orders_order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
总结:
1.使用正确的联结:
- 注意区分内联接inner join 与 外联结 outer join,以及笛卡尔积 cross join
2.注意使用正确的联结语法:
- 应该查看具体的使用文档
3.一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型:
- 这样合法也很有用
- 但应该在一起测试他们之前,分别测试每个联结。
- 也可以使用联结进行反向筛选:左联结+右表中字段为NULL条件
二.组合查询
SQL语句也允许执行多个查询(多条SELECT语句),并将结果作为一个查询结果集返回。
1.UNION规则:
1)必须由两条或两条以上的SELECT语句组成,语句之间用UNION分隔;
2)UNION每个查询必须包含相同的列、表达式或聚集函数(每个列不需要以相同的次序列出)
3)列数据类型必须相同:类型不必完全相同,但必须是DBMS可以隐含转换的类型;可以使用cast()函数转化。
2.包含或取消重复的行
1)UNION 从查询结果集中,自动去除重复的行;
2)如果想返回所有匹配的行:
- 使用UNION ALL
3.对组合查询进行排序
因为不存在使用一种方式对一部分进行排序,再使用另一种方式对另一部分进行排序,所以只能使用一个ORDER BY子句。
在最后一条SELECT子句中,使用ORDER BY子句。
4.其他类型UNION
1)EXCEPT:差集,在表1中但不包含同时在表2中的部分;
检索只在第一个表中存在,而在第二个表中不存在的行
2)INTERSECT:交集,选取表中公共部分
- 默认去除重复部分;
- 改变默认:intersect all
三.操作多个表
第一,查询需求的逻辑必须清楚;
第二,将总体查询,分为多个子模块——子查询;
第三,根据查询逻辑,联结各个子表;
第四,设置总表的查询条件。
四.Hive函数
Hive函数汇总: https://www.iteblog.com/archives/2258.html
重要的常用函数
1.聚合函数:
1)聚合函数分类:
- count():
- count(列名):当将列名作为参数时,不会将NULL计入到内;
- count(*):而使用星号则会将NULL计入到内;
- sum():不受1)的影响,不会考虑NULL行;
- avg():不会将NULL行计算到内;
- min/max 几乎可以使用在任何数据类型
- 与distinct联用:count(distinct …)