【函数】
函数处理字段
#文本处理函数
UPPER() --大写
LOWER() --小写
SOUNDEX() --读音类似
LENGTH() --字符串长度
#日期和时间处理函数
SELECT order_code
FROM order_table
WHERE DATEPART(yy,order_date)=2012
#to_char()函数提取日期成分，MySQL可用year()函数提取年份
SELECT order_num
FROM order_table
WHERE to_number(to_char(order_date,'YYYY'))=2012; --to_char提取日期成分to_number转化为数值
SELECT order_num
FROM order_table
WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31--2012'); --字符串转日期
#数值处理函数
ABS() --绝对值
COS() --余弦
EXP() --指数值
PI() --圆周率
SIN() --正弦
SQRT() --平方根
TAN() --正切
聚集函数汇总数据
#平均数AVG()
SELECT AVG(a) AS avg_a
FROM tableA
WHERE a='DLL01';
#计数
SELECT COUNT(*) AS num_cust --对所有行计数，不管是否NULL值
FROM tableA;
SELECT COUNT(a) AS num_a --指定了列名，会忽略NULL值
FROM tableA;
#最值
MAX()和MIN()，忽略NULL值
#总值SUM()，忽略NULL值
SELECT SUM(price*quantity) AS total_price
FROM tableA
WHERE order_code=20008
#以上聚集函数只包含不同值
SELECT AVG(DISTINCT order_price) AS avg_price
FROM tableA
WHERE id='DLL01';
【分组】
两个子句：GROUP BY 和 HAVING
创建、过滤分组
#GROUP BY创建分组HAVING过滤分组
SELECT vend_id， COUNT(*) AS num_prods
FROM tableA
WHERE price>=4
GROUP BY vend_id
HAVING COUNT(*)>=2;
ORDER BY price --GROUP BY在WHERE之后在ORDER BY之前
SELECT子句顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
【子查询】
子查询与过滤
#子查询顺序为从内而外
SELECT order_num
FROM tableA
WHERE id='DLL01'；
(输出20007和20008)
SELECT id
FROM tableB
WHERE order_num IN（20007，20008）；
#合并为子查询。只能查询单个列
SELECT id
FROM tableB
WHERE order_num IN (SELECT order_num FROM tableA WHERE id='DLL01')
计算字段使用子查询
SELECT cust_name,
cust_state
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id=Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
#Orders.cust_id=Customers.cust_id完全限定列名，在两张表中名字相同列拿出来比较，防止歧义
