五、计算字段
1、拼接字段
MySQL:使用函数concat
SqlServer:使用加号+
oracle:使用||
SELECT CONCAT(vend_name,'(',vend_country,')') FROM vendors ORDER BY vend_name
使用别名AS
SELECT CONCAT(vend_name,'(',vend_country,')') AS vend_title FROM vendors ORDER BY vend_name
2、算数计算:支持+、—、*、/,用()区分优先级
SELECT prod_id,quantity,item_price,
quantity*item_price AS expanded_price
FROM orderitems WHERE order_num = 20008
六、分组
1、过滤分组
WHERE过滤行,而HAVING过滤分组。
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
SELECT cust_id,COUNT(*) AS num_orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;
SELECT vend_id,prod_price,COUNT(*) AS num_prods FROM products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*)>= 2;//结合where语句
SELECT order_num,COUNT(*) AS items FROM orderitems GROUP BY order_num HAVING COUNT(*)>=3 ORDER BY items//分组没有排序,必须用order by指定排序