《MySQL必知必会》1-17章

#拼接成name(location)形式
SELECT Concat(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;

#删除数据右面的空格;RTrim右,LTrim左,Trim两边
SELECT Concat(RTrim(vend_name),'(',RTRIM(vend_country),')')
FROM vendors
ORDER BY vend_name;

#创建别名或导出列
SELECT Concat(vend_name,'(',vend_country,')') AS
vend_titles
FROM vendors
ORDER BY vend_name;

#对检索出来的数据进行算术计算
SELECT prod_id,quantity,item_price,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num=20005;

#将文本转换为大写Lower(),Upper()文本处理函数
SELECT vend_name,Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

#匹配类似发音
SELECT cust_name,cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y.Lie');

#日期和时间处理函数，日期处理函数
SELECT cust_id,order_num
FROM orders
WHERE order_date = '2005-09-01';

#排除既包含日期又包含时间
SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';

#检索某个月所有日期的订单
SELECT cust_id,order_num
FROM orders
WHERE Date(order_date)  BETWEEN '2005-09-01' AND '2005-09-30';
#不用即每个月的天数
SELECT cust_id,order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

#计算所有产品的平均值,AVG()函数
SELECT AVG(prod_price) AS avg_price
FROM products;
#计算特定行产品的平均值
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
#只计算价格不同的
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;


#对行计数COUNT()函数
SELECT COUNT(*) AS num_cust
FROM customers;
#对特定列中具有值的行进行计数
SELECT COUNT(cust_email) AS num_cust
FROM customers;

#返回指定列中的最大值MAX()
SELECT MAX(prod_price) AS max_price
FROM products;
#返回指定列值的和,此处还要求是20005号的(由WHERE语句保证)
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
#计算值
SELECT SUM(quantity*item_price) AS total_price
FROM orderitems
WHERE order_num = 20005;

#组合聚集函数
SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
			 AVG(prod_price) AS price_avg
FROM products;

#GROUP BY子句创建分组
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

#HAVING过滤分组
SELECT vend_id,prod_price,COUNT(*) AS num
FROM products
GROUP BY vend_id
HAVING COUNT(*)<7;

#HAVING分组以后用ORDER BY 排序
SELECT vend_id,prod_price,COUNT(*) AS num
FROM products
GROUP BY vend_id
HAVING COUNT(*)<7
ORDER BY prod_price;
#同上一个
SELECT vend_id,SUM(prod_price) AS price
FROM products
GROUP BY vend_id
HAVING price>1
ORDER BY price;

#使用子查询
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

SELECT cust_id
FROM orders
WHERE order_num = 20005 OR order_num = 20007;
#等同于WHERE order_num IN(20005,20007)

SELECT *
FROM customers
WHERE cust_id=10001 OR cust_id=10004;
#三个子查询组合为一个查询
SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN(SELECT cust_id
FROM orders
WHERE order_num IN(SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'))
#使用联结
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE prod_id = 'TNT2'
AND orders.order_num = orderitems.order_num
AND customers.cust_id=orders.cust_id;

#作为计算字段使用子查询
SELECT cust_name,cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_nameg;

#创建联结
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;
#等值联结（内部联结）
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id

#联结多个列表
SELECT prod_name,vend_name,prod_price,quantity
FROM orderitems,products,vendors 
WHERE products.vend_id = vendors.vend_id
AND order_num = 20005
AND orderitems.prod_id = products.prod_id
#AND order_num = 20005

#使用UNION语句(UNION ALL 不消除重复)
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id,prod_price
#若使用ORDER BY只能用一个