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