SQL学习|基础查询与排列

检索数据

使用SELECT prod_id, prod_name, prod_price FROM products;还可以检索多列数据。

*表示通配符，SELECT * FROM 表检索表中所有列的数据

使用DISTINCT返回唯一值，如SELECT DISTINCT vend_id FROM products;。

使用LIMIT限制输出数量，如SELECT prod_name FROM products LIMIT 5;只显示前5条数据。

还可以指定开始行及行数，SELECT prod_name FROM products LIMIT 5,5;显示从第五行开始的后面5行。

使用完全限定的表名。如SELECT products.prod_name FROM products;，这条语句与前面第一条的效果一样，但是这里指定了一个完全限定的列名。同理，也可以SELECT products.prod_name FROM crashcourse.products;限定数据库。

排序检索数据

• 使用ORDER BY字句对输出排序。
  如，SELECT prod_name FROM products ORDER BY prod_name;表示对输出以字母排序。

• 按多个列排序，
  如，SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;先使用价格排序，再使用名排序。

• 指定排序方向
  如，SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;
  前面是默认按照升序排序，这里使用DESC按照降序排序。

还可以指定某列升序，某列降序，如，SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;对价格降序，产品名升序排列。

另外，ASC是升序排列，但是一般不会使用。

使用ORDER BY 和LIMIT组合可以找到最高或最低的值。
如，SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;找出价格最贵的物品。

过滤数据

过滤数据主要使用WHERE字句指定搜索条件。

• WHERE字句基本用法
  使用select prod_name, prod_price FROM products WHERE prod_price = 2.50;查询指定价格的商品。

• WHERE字句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

• 空值检查
  使用SELECT cust_id FROM customers WHERE cust_email IS NULL;匹配出没有电子邮件的顾客。

• 使用逻辑操作符来联结或改变WHERE子句
  使用SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;找到由供应商1003制造且价格小于10美元的商品。这里用到了AND操作符。

OR操作符同理，表示或。SQL在处理操作符时，会优先处理AND操作符。可使用括号指定运算顺序。

使用SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;搜索供应商在1002到1003之间的所有产品。
这里用到的IN操作符用来指定范围。IN与OR具有相同的功能，但是IN有一些OR没有的优点。
最后，NOT操作符表否定，例如加在IN前面表示不在IN的范围内的内容。

• LIKE操作符
  使用%通配符，使用SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';找出所有以jet开头的产品。
  %也可以在一个子句中多次使用。

使用_字符，_与%的区别就是_只能匹配一个字符。比如SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

使用正则表达式进行搜索

• 基本字符匹配
  一个简单的例子，使用SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;来检索文本中存在1000的行。

稍微做一个修改，将‘1000’改为‘.000’，‘.’是正则表达式中一个特殊的字符，可以用来匹配任意一个字符。
正则表达式匹配不区分大小写如果想要区分大小写，可以使用BINARY关键字。如WHERE prod_name REGEXP BINARY 'JetPack .000'。

• 进行OR匹配
  使用SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;匹配存在1000或2000的串。

• 匹配几个字符之一
  SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;匹配1 Ton或2 Ton或3 Ton。[123]是另一种形式的OR语句，可以看做[1|2|3]的缩写。

• 匹配范围
  使用 SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;匹配ton前面有1-5的串。[1-5]表示一个范围。

• 匹配特殊字符
  匹配特殊字符需要使用\为前导，也就是转义。如匹配.需要使用\.来匹配。 SELECT prod_name FROM products WHERE prod_name REGEXP '\\.' ORDER BY prod_name;
  \\也用来引用元字符。

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

• 匹配多个实例

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1，}）
？	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）

如，SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
这句中，\(匹配(，[0-9]匹配任意数字（这个例子中为1和5），sticks?匹配stick和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现），\)匹配)。没有?，匹配stick和sticks会非常困难。

另一个例子，SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
这里匹配连在一起的四位数字。[:digit:]匹配任意数字，{4}要求它前面的字符，也就是任意数字出现四次。这个例子与SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;的效果相同。

• 定位符

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

例如，想要找一个以小数点开始的数，可以使用SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

创建计算字段

计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

• 拼接字段
  按照名字位置列出供应商。使用SELECT Concat(vend_name,'(',vend_country, ')') FROM vendors ORDER BY vend_name;
  Concat函数用于将字段连接，在其他DBMS中使用+或||来连接。

还可以使用RTrim()函数删除右侧多余空格。 SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country), ')') FROM vendors ORDER BY vend_name;

使用AS来为检索出的字段赋予别名。SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

• 执行算术计算
  汇总订单价格的小例子，使用SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;将订单数量与价格相乘得到总结，并得到一个新的字段expanded_price。

聚集函数

• SQL聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

• AVG()函数
  使用SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;返回特定供应商所提供商品的平均价格。

• COUNT()函数
  使用SELECT COUNT(cust_email) AS num_cust FROM customers;计算有电子邮箱的数目。

• MAX()函数
  使用SELECT MAX(prod_price) AS max_price FROM products;选出价格最高的产品价格。

• MIN()函数
  使用SELECT MIN(prod_price) AS min_price FROM products;选出价格最低的产品价格。

• SUM()函数
  使用SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;检索订购物品的总数。

使用SELECT SUM(item_price * quantity) AS total_price FROM orderitems WHERE order_num = 20005;计算产品总价格。

使用DISTINCT聚集不同的值，SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id=1003;。

组合不同的函数，SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products;

分组数据

• 创建分组
  使用 SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;返回每一个供应商提供的产品数量。

• 过滤分组
  使用HAVING过滤分组，如SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;只保留两个订单以上的分组。

WHERE和HAVING的区别在于WHERE在分组前进行过滤，而HAVING在分组后进行过滤。

也可以同时使用WHERE和HAVING，如使用SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;列出具有两个以上、价格为10以上的产品供应商。

• 分组和排序
  检索订单总价格大于50的产品并分组，将结果排序输出。SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;

在使用聚合函数及GROUP BY子句时，经常出现的错误有：

• 在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
• 在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
• 在WHERE中使用聚合函数 原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING以及ORDER BY子句中使用聚合函数。

参考资料：
[1]《MySQL必知必会》Ben Forta
[2] https://github.com/datawhalechina/team-learning-sql/blob/main/Task02%EF%BC%9A%E5%9F%BA%E7%A1%80%E6%9F%A5%E8%AF%A2%E4%B8%8E%E6%8E%92%E5%BA%8F.md

作业

2.1

select product_name, regist_date from product where regist_date>2009-4-8;

2.2

SELECT *
  FROM product
 WHERE purchase_price = NULL(<>NULL)(>NULL);

三个语句都不能查询出结果，因为NULL不可比较，要查询值为空的记录，可以使用 is null

SELECT *
  FROM product
 WHERE purchase_price IS NULL;

2.3

SELECT
	product_name,
	sale_price,
	purchase_price,
	sale_price - purchase_price as diff_price
FROM
	product 
HAVING
	diff_price >= 500;

2.4

SELECT
	product_name,
	product_type,
	sale_price * 0.9 - purchase_price AS profit 
FROM
	product 
WHERE
	product_type = '办公用品' 
	OR product_type = '厨房用具' 
HAVING
	profit > 100;

这里在having语句中用到了别名，但是标准sql的执行顺序应该是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。所以正常having中是不能使用别名的，但是mysql对此进行了扩展，可以在having中使用别名。因此在其他DBMS中可能会有问题，这里最好还是不要使用这种方法。

2.5

SELECT product_id, SUM（product_name）
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

1、product_name是字符类型，sum只能用于数值类型。
2、GROUP BY语句应该在where语句的后面。

2.6

SELECT
	product_type,
	sum( sale_price ) AS sale_price_sum,
	sum( purchase_price ) AS purchase_price_sum 
FROM
	product 
GROUP BY
	product_type 
HAVING
	sale_price_sum > purchase_price_sum * 1.5;

2.7

SELECT
	* 
FROM
	product 
ORDER BY
IF
	( ISNULL( regist_date ), 0, 1 );

order by colum asc 时，null默认被放在最前
order by colum desc 时，null默认被放在最后
ORDER BY IF(ISNULL(update_date),0,1) null被强制放在最前，不为null的按声明顺序[asc|desc]进行排序
ORDER BY IF(ISNULL(update_date),1,0) null被强制放在最后，不为null的按声明顺序[asc|desc]进行排序
参考文章：https://blog.csdn.net/weixin_30352191/article/details/95654581
或者

SELECT *
FROM product
ORDER BY IFNULL(regist_date, '9999-12-31') DESC, IFNULL(purchase_price, 0);

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。