导读： 1. 日期和时间处理函数AddDate()增加一个日期AddTime()增加一个时间CurDate()返回当前日期CurTime()返回当前时间Date()返回...
1. 日期和时间处理函数
AddDate() 增加一个日期
AddTime() 增加一个时间
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateFiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期
Hour() 返回时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分
select cust_id, order_num from orders where order_date = '2005-09-01';
Datetime 类型只匹配日期 2005-09-01 11:30:05
select cust_id, order_num from orders where Date(order_date) = '2005-09-01';
检索2005年9月份所有订单
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;
select cust_id, order_num from orders
-> where Date(order_date) >= '2005-09-01' and Date(order_date) <= '2005-09-30';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10003 | 20006 |
| 10004 | 20007 |
+---------+-----------+
2. 聚集函数 运行在行组上，计算和返回单个值的函数
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列之和
AVG() 忽略NULL 值的行
select AVG(prod_price) as avg_price from products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
COUNT()
COUNT(*) 对表中行的数目进行计数，不管表列中包含的是空（NULL），还是非空值。
COUNT(column)对表中特定劣种具有值的进行计数，忽略NULL 值。
select count(cust_email) as num_cust from customers;
+----------+
| num_cust |
+----------+
| 3 |
+----------+
MAX() MIN()
select max(prod_price) as max_price, prod_name from products;
+-----------+--------------+
| max_price | prod_name |
+-----------+--------------+
| 55 | .5 ton anvil |
+-----------+--------------+
SUM()
select sum(quantity) as items_ordered from orderitems where order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 19 |
+---------------+
SUM 也可以用来合计计算值
select sum(quantity*item_price) as total_price from orderitems where order_num = 20005;
+-------------+
| total_price |
+-------------+
| 149.87 |
+-------------+
聚集不同值 DISTINCT
select AVG(distinct prod_price) as avg_price from products where vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998 |
+-----------+
组合聚集函数
select count(*) as num_items,
-> min(prod_price) as price_min,
-> max(prod_price) as price_max,
-> avg(prod_price) as price_avg
-> from products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.5 | 55 | 16.133571 |
+-----------+-----------+-----------+-----------+
3. 分组数据
select vend_id, count(*) as num_prods from products group by vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
使用 with rollup 可以得到每个分组以及每个分组汇总级别的值。
select vend_id, count(*) as num_prods from products group by vend_id with rollup;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
| NULL | 14 |
+---------+-----------+
过滤分组
select vend_id, count(*) as num_prods from products group by vend_id having count(*) >= 3;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1003 | 7 |
+---------+-----------+
WHERE 不起作用，因为过滤式基于分组聚集值而不是特定行值的。
WHERE 在分组前过滤，HAVING 在分组后过滤。
select vend_id, count(*) as num_prods from products where prod_price >= 10 group by
-> vend_id having count(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1003 | 4 |
| 1005 | 2 |
+---------+-----------+
分组和排序
检索总计订单价格大于等于50的订单的订单号和总计订单价格
select order_num, sum(quantity*item_price) as ordertotal from orderitems group by
-> order_num having sum(quantity*item_price) >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20005 | 149.87 |
| 20006 | 55.00 |
| 20007 | 1000.00 |
| 20008 | 125.00 |
+-----------+------------+
等价：
select order_num, sum(quantity*item_price) as ordertotal from orderitems group by
-> order_num having ordertotal >= 50;
4. 子查询
列出订购物品TNT2的所有客户
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'));
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
显示customers 表中每个客户的订单总数。
select cust_name, cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id)
-> as orders from customers order by cust_name;
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+--------+
5. 联结表
不要忘记WHERE 字句，所有的联结都有WHERE 字句。
内联结（等值联结）
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 cust_name, cust_contact from customers, orders, orderitems
-> where customers.cust_id = orders.cust_id
-> and orderitems.order_num = orders.order_num
-> and prod_id = 'TNT2';
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
使用表别名
select Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') as vend_title
-> from vendors order by vend_name;
+-------------------------+
| vend_title |
+-------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+-------------------------+
自联结（通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句）
查找物品ID 为DINTR的其他物品
select prod_id, prod_name from products
-> where vend_id = (select vend_id from products where prod_id = 'DTNTR');
等价
select p1.prod_id, p1.prod_name from
-> products as p1, products as p2 where
-> p1.vend_id = p2.vend_id
-> and p2.prod_id = 'DTNTR';
外部联结
分为左外部联结和右外部联结
select customers.cust_id, orders.order_num from
-> customers left outer join orders on
-> customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10002 | NULL |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
使用带聚集函数的联结
select customers.cust_name, customers.cust_id, COUNT(orders.order_num) as num_ord
-> from customers inner join orders
-> on customers.cust_id = orders.cust_id group by customers.cust_id;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+
左外联结
select customers.cust_name, customers.cust_id, COUNT(orders.order_num) as num_ord
-> from customers left outer join orders
-> on customers.cust_id = orders.cust_id group by customers.cust_id;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Mouse House | 10002 | 0 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+
