目录

1.使用数据处理函数

文本处理函数

  • soundex用来匹配发音

日期和时间处理函数

  • 使用where语句进行匹配时，日期默认为"yyyy-mm-dd"，即2015-09-01
  • 若数据中包含具体的时间值，为了可以匹配某一天，可以使用Date函数，只获取日期部分 
select cust_id, order_num
from order
where Date(order_date) = '2005-09-01';

2. 汇总数据

聚集函数

  • 聚集函数运行在行组上，计算和返回单个值

  • AVG函数只能应用一列，会忽略掉NULL值
  • COUNT（*）对表中所有的数据计数，包括NULL；COUNT(column)对特定的列进行计数，忽略NULL
  • MAX和MIN返回最大的或最小一个值或者日期，忽略NULL
  • SUM函数记录相应数目的和

聚集不同的值

  • All参数是默认的
  • 如果想统计不同的值，需要加DISRINCT 
select AVG(DISTINCT prod_price) AS avg_price
from products
where vend_id = 1003;

  • DISTINCT 必须后面跟列名，所以不能用在count(*)中
  • DISTINCT在MIN和MAX中使用没有意义

3. 分组数据

创建分组

select vend_id,count(*) AS num_prods 
from products 
GROUP BY vend_id;

  • GROUP BY 指示mysql进行分组数据，然后对每个组，而不是整个结果集进行聚集
  • GROUP BY子句可以跟任意数量的列，可以为分组提供更细致的控制
  • 所有的NULL都会被放在一个分组
  • 出现在where子句之后，ORDER BY子句之前

过滤分组

  • where过滤的是行而不是分组
  • 而HAVING 支持where的所有用法，过滤的是分组 
select vend_id,count(*) AS num_prods 
from products 
GROUP BY vend_id
HAVING count(*) > 2;

where和having的区别

  • where是在分组前进行过滤
  • having过滤的是分组后的数据

分组和排序

  • group by输出的不一定的分组的顺序
  • 所以如果需要排序，一定要使用order by 
select order_num , sum(quantity*item_price)  AS ordertotal  
from orderitems 
group by order_num  
having sum(quantity*item_price) >=50 
order by  sum(quantity*item_price) ;

  • 利用order by 将 结果按照订单大小由小到大排序

select子句顺序

4. 子查询

  • 嵌套在其他查询中的查询

利用子查询进行过滤

  • 查询TNT2的所有客户 

select cust_id from orders  where  order_num in
( 
select  order_num from orderitems where prod_id = 'TNT2'
);

  • where子句中使用的子查询嵌套没有限制
  • 但是不要使用太多，因为每个子查询都是查询了全部数据，性能比较差

可以作为字段使用的子查询

SELECT cust_name,cust_state,
	(SELECT COUNT(*) 
	FROM  orders o 
	WHERE o.cust_id = c.cust_id
	) AS orders
FROM customers c
ORDER BY cust_name;

5. 联结表

创建联结

  • 规定好所联结的表，以及如何关联 
SELECT vend_name ,prod_name,prod_price
FROM vendors v ,products p
WHERE v.`vend_id` = p.`vend_id`
ORDER BY vend_name,prod_name;

  • 完全限定列名是为了避免二义性
  • 如果没有where的条件，返回的是笛卡尔积

内联结

  • 目前为止使用的联结称为等值联结，基于两个表之间的相等，也称作内连接 
SELECT vend_name ,prod_name,prod_price
FROM vendors v INNER JOIN products p
ON v.`vend_id` = p.`vend_id`
ORDER BY vend_name,prod_name;

  • 这个结果与上述的一样

  • SQL规范应该使用ON，使用where会影响性能

联结多个表

SELECT prod_name, vend_name, prod_name, quantity
FROM orderitems, products, vendors
WHERE products.`vend_id` = vendors.`vend_id` 
	AND  orderitems.`prod_id` = products.`prod_id` 
	AND order_num = 20005;

  • Mysql在运行时关联指定的表处理联结是非常消耗资源的，表联结的越多，性能下降的越快

自连接

  • 使用表别名可以帮助mysql消除二义性
  • 如果在相同的表中处理数据，可以使用自连接 
SELECT p1.prod_id, p1.prod_name 
FROM products p1, products p2
WHERE p1.`vend_id` = p2.`vend_id` AND p2.`prod_id` = 'DTNTR';

  • 使用表名加*可以返回全部的列

外部联结

  • 联结包含了在相关表中没有关联的行，这种叫做外连接
  • 就是这两个行没有任何关系 
SELECT c.`cust_id`, o.`order_num`
FROM customers c INNER JOIN orders o
ON c.`cust_id` = o.`cust_id`;
  • 这是个内连接查询了用户及其所有订单 
SELECT c.`cust_id`, o.`order_num`
FROM customers c LEFT OUTER JOIN orders o
ON c.`cust_id` = o.`cust_id`;
  • 这个是外连接为了检索所有的用户，包括哪些没有订单的用户

6. 使用组合查询

  • 组合查询和where查询工作类似，但是性能在不同的查询中不同，因此可以通过尝试进行选择
  • 使用UNION可以连接数条查询 
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price<=5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);

  • UNION的每个查询必须包含相同的列、表达式或聚集函数
  • 和where一样自动过滤了重复的行，可以使用UNION ALL显示重复的行 
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price<=5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);