最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

SQL知识大全2:SQL的基础知识代码详解

XAMPP案例 admin 60浏览 0评论

今天是SQL系列的第二讲,会着重从SQL的基础语法出发,讲解去重,聚合函数,常用的数值处理函数,条件筛选和排序

zzzzzu4

数据去重

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 OrderItems
WHERE order_num = 20005; 

COUNT()返回数量

#count(*)返回所有值,包括null
SELECT COUNT(*) AS num_cust
FROM Customers;
#count(字段名)只返回该字段不为null的值
SELECT COUNT(cust_email) AS num_cust
FROM 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_avg
FROM Products
GROUP 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_sqrt
FROM Products
GROUP BY dt;
条件筛选

WHERE的用法

SELECT prod_name, prod_price FROM Products
 WHERE prod_price = 3.49; 
#AND
SELECT prod_id, prod_price, prod_name 
FROM Products 
WHERE vend_id = 'DLL01' AND prod_price <= 4; 
#OR
SELECT prod_name, prod_price
 FROM Products
 WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;

IN的用法

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;

LIKE相关通配符

1.%通配符

#查找有bean bag的字段
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

2._下划线通配符

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

3.[]通配符

#寻找J或M开头的姓名
SELECT cust_contact 
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
#此通配符之前加一个括号可以表示否定,即寻找不是J或M开头的人
SELECT cust_contact
FROM Customers
WHERE 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_name
FROM Products
ORDER BY prod_price DESC, prod_name;
#分组查询过滤排序
SELECT order_num, COUNT(*) AS items FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

参考书籍:《SQL必知必会》

转载请注明:XAMPP中文组官网 » SQL知识大全2:SQL的基础知识代码详解