sql 去重求和_SQL知识大全(二):SQL的基础知识你都掌握了吗?
点击上方蓝字关注我们
今天是SQL系列的第二讲,会着重从SQL的基础语法出发,讲解去重,聚合函数,常用的数值处理函数,条件筛选和排序
数据去重Distinct数据去重
从Products中筛选出无重复的vend_id 。
#去除重复值SELECT DISTINCT vend_id FROM Products;
聚合函数
AVG()返回平均值
SELECT AVG(prod_price) AS avg_price FROM Products;
SUM() 汇总求和
SELECT SUM(quantity) AS items_ordered FROM OrderItemsWHERE order_num = 20005;
COUNT()返回数量
#count(*)返回所有值,包括nullSELECT COUNT(*) AS num_custFROM Customers;#count(字段名)只返回该字段不为null的值SELECT COUNT(cust_email) AS num_custFROM Customers;
MAX()/MIN()求最大最小值
SELECT MAX(prod_price) AS max_price FROM Products;SELECT dt, COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avgFROM ProductsGROUP BY dt;
数值处理函数
数值处理函数主要是将数值型的数据转换为其余数,绝对值,余弦值,正切值等,具体用如下:
SELECT dt, COUNT(*) AS num_items, MOD(prod_price) AS price_mod, ROUND(prod_price) AS price_round, ABS(prod_price) AS price_abs, SQRT(prod_price) AS price_sqrtFROM ProductsGROUP BY dt;
条件筛选
WHERE的用法
SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49; #ANDSELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4; #ORSELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;
IN的用法
SELECT prod_name, prod_priceFROM ProductsWHERE vend_id IN ( 'DLL01', 'BRS01' )ORDER BY prod_name;
LIKE相关通配符
1.%通配符
#查找有bean bag的字段SELECT prod_id, prod_nameFROM ProductsWHERE prod_name LIKE '%bean bag%';
2._下划线通配符
SELECT prod_id, prod_nameFROM ProductsWHERE prod_name LIKE '__ inch teddy bear';
3.[]通配符
#寻找J或M开头的姓名SELECT cust_contact FROM CustomersWHERE cust_contact LIKE '[JM]%'ORDER BY cust_contact;#此通配符之前加一个括号可以表示否定,即寻找不是J或M开头的人SELECT cust_contactFROM CustomersWHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;
HAVING分组过滤
#对价格为4元以上的商品统计其被购买次数,并筛选出被购买两次以上的商品SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;
数据排序
ORDER BY 数据排序
SELECT prod_id, prod_price, prod_nameFROM ProductsORDER BY prod_price DESC, prod_name;#分组查询过滤排序SELECT order_num, COUNT(*) AS items FROM OrderItemsGROUP BY order_numHAVING COUNT(*) >= 3ORDER BY items, order_num;
参考书籍:《SQL必知必会》
SQL系列文章持续更新中
往期推荐
SQL知识大全(一):数据库的语言分类你都知道吗?
史上最全的SQL知识点汇总,错过这次在等一年
游戏行业指标体系大全(一)
游戏行业指标体系大全(二)
数据岗知识体系及岗位介绍
分享数据知识,成就数据理想