MySQL学习笔记（五）
1、拼接字段
拼接 (Concatenate)：将值联结到一起构成单个值。
1 mysql> SELECT Concat(vend_name, '(', vend_country, ')') 2 -> FROM vendors 3 -> ORDER BY vend_name; 4 +-------------------------------------------+ 5 | Concat(vend_name, '(', vend_country, ')') | 6 +-------------------------------------------+ 7 | ACME(USA) | 8 | Anvils R Us(USA) | 9 | Furball Inc.(USA) | 10 | Jet Set(England) | 11 | Jouets Et Ours(France) | 12 | LT Supplies(USA) | 13 +-------------------------------------------+ 14 6 rows in set (0.01 sec)
2、使用别名
别名是一个字段或值的替换名
1 mysql> SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title 2 -> FROM vendors 3 -> ORDER BY vend_name; 4 +------------------------+ 5 | vend_title | 6 +------------------------+ 7 | ACME(USA) | 8 | Anvils R Us(USA) | 9 | Furball Inc.(USA) | 10 | Jet Set(England) | 11 | Jouets Et Ours(France) | 12 | LT Supplies(USA) | 13 +------------------------+ 14 6 rows in set (0.00 sec)
3、执行算术计算
1 mysql> SELECT prod_id, 2 -> quantity, 3 -> item_price, 4 -> quantity*item_price AS price 5 -> FROM orderitems 6 -> WHERE order_num =20005; 7 +---------+----------+------------+-------+ 8 | prod_id | quantity | item_price | price | 9 +---------+----------+------------+-------+ 10 | ANV01 | 10 | 5.99 | 59.90 | 11 | ANV02 | 3 | 9.99 | 29.97 | 12 | TNT2 | 5 | 10.00 | 50.00 | 13 | FB | 1 | 10.00 | 10.00 | 14 +---------+----------+------------+-------+ 15 4 rows in set (0.00 sec)
4、使用函数
文本处理函数
1 mysql> SELECT vend_name, Upper(vend_name) AS vend_name_upcase 2 -> FROM vendors 3 -> ORDER BY vend_name; 4 +----------------+------------------+ 5 | vend_name | vend_name_upcase | 6 +----------------+------------------+ 7 | ACME | ACME | 8 | Anvils R Us | ANVILS R US | 9 | Furball Inc. | FURBALL INC. | 10 | Jet Set | JET SET | 11 | Jouets Et Ours | JOUETS ET OURS | 12 | LT Supplies | LT SUPPLIES | 13 +----------------+------------------+ 14 6 rows in set (0.00 sec)
常用的文本处理函数：Left() 返回串左边的字符，Length() 返回串的长度，Locate() 找出串的一个字串，
Lower() 将串转换为小写，LTrim() 去掉串左边的空格，Right() 返回串右边的字符，RTrim() 去掉串
右边的空格，Soundex() 返回串的SOUNDEX值，SubString() 返回子串的字符，Upper() 将串转换为大写。
1 mysql> SELECT cust_name, cust_contact 2 -> FROM customers 3 -> WHERE Soundex(cust_contact) = Soundex('Y Lie'); 4 +-------------+--------------+ 5 | cust_name | cust_contact | 6 +-------------+--------------+ 7 | Coyote Inc. | Y Lee | 8 +-------------+--------------+ 9 1 row in set (0.00 sec)
日期和时间处理函数
常用日期和时间处理函数：AddDate() 增加一个日期（天、周等），AddTime 增加一个时间（时，分等），CurDate()
返回当前日期，CurTime() 返回当前时间，Date() 返回日期时间的日期部分，DateDiff() 计算两个日期之差，
Date_Add() 高度灵活的日期运算函数，Date_Format() 返回一个格式化的日期或时间串，Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几，Hour() 返回一个时间的小时部分，Minute() 返回一个时间的分钟部分，
Month() 返回一个时间的月份部分，Now() 返回当前日期和时间Second() 返回一个时间的秒部分，Time() 返回一个日期
时间的时间部分，Year() 返回一个时间的年份部分
1 mysql> SELECT cust_id, order_num 2 -> FROM orders 3 -> WHERE Date(order_date) = '2005-09-01'; 4 +---------+-----------+ 5 | cust_id | order_num | 6 +---------+-----------+ 7 | 10001 | 20005 | 8 +---------+-----------+ 9 1 row in set (0.00 sec)
1 mysql> SELECT * 2 FROM orders 3 WHERE Year(order_date) = 2005 AND Month(order_date) = 9; 4 +-----------+---------------------+---------+ 5 | order_num | order_date | cust_id | 6 +-----------+---------------------+---------+ 7 | 20005 | 2005-09-01 00:00:00 | 10001 | 8 | 20006 | 2005-09-12 00:00:00 | 10003 | 9 | 20007 | 2005-09-30 00:00:00 | 10004 | 10 +-----------+---------------------+---------+ 11 3 rows in set (0.00 sec)
5、汇总数据
聚集函数 (aggregate function) 运行在行组上，计算和返回单个值的函数。
AVG() 返回某列的平均值
COUNT() 返回某列的函数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
1 mysql> SELECT AVG(price) AS avg_price 2 -> FROM products; 3 ERROR 1054 (42S22): Unknown column 'price' in 'field list' 4 mysql> SELECT AVG(prod_price) AS avg_price FROM products; 5 +-----------+ 6 | avg_price | 7 +-----------+ 8 | 16.133571 | 9 +-----------+ 10 1 row in set (0.00 sec) 11 12 mysql> SELECT COUNT(*) AS count 13 -> FROM customers; 14 +-------+ 15 | count | 16 +-------+ 17 | 5 | 18 +-------+ 19 1 row in set (0.00 sec) 20 21 mysql> SELECT * FROM customers; 22 +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 23 | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | 24 +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 25 | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | 26 | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | 27 | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | 28 | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | 29 | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | 30 +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 31 5 rows in set (0.01 sec) 32 33 mysql> SELECT MAX(cust_zip) AS max_zip 34 -> FROM customers; 35 +---------+ 36 | max_zip | 37 +---------+ 38 | 88888 | 39 +---------+ 40 1 row in set (0.00 sec)
1 mysql> SELECT SUM(order_item) AS sum_order 2 -> FROM orderitems; 3 +-----------+ 4 | sum_order | 5 +-----------+ 6 | 23 | 7 +-----------+ 8 1 row in set (0.00 sec) 9 10 mysql> SELECT SUM(item_price*quantity) AS sum_price FROM orderitems; 11 +-----------+ 12 | sum_price | 13 +-----------+ 14 | 1368.34 | 15 +-----------+ 16 1 row in set (0.00 sec)
1 mysql> SELECT COUNT(*) AS num_items, 2 MIN(prod_price) AS min_price, 3 MAX(prod_price) AS max_price, 4 SUM(prod_price) AS sum, 5 AVG(DISTINCT prod_price) AS avg_price FROM products; 6 +-----------+-----------+-----------+--------+-----------+ 7 | num_items | min_price | max_price | sum | avg_price | 8 +-----------+-----------+-----------+--------+-----------+ 9 | 14 | 2.50 | 55.00 | 225.87 | 17.780833 | 10 +-----------+-----------+-----------+--------+-----------+ 11 1 row in set (0.29 sec) 12
