MySQL学习笔记（五）

1、拼接字段

　　拼接 (Concatenate)：将值联结到一起构成单个值。

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、使用别名

　　别名是一个字段或值的替换名

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、执行算术计算

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、使用函数

　　文本处理函数

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()　　将串转换为大写。

mysql> SELECT cust_name, cust_contact2 -> FROM customers3 -> 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()　　返回一个时间的年份部分

mysql> SELECT cust_id, order_num2 -> FROM orders3 -> 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)

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()　　　　　　　　　   返回某列值之和

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 count13 -> 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_zip34 -> FROM customers;35 +---------+36 | max_zip |37 +---------+38 | 88888|39 +---------+40 1 row in set (0.00 sec)

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)

mysql> SELECT COUNT(*) AS num_items, 2MIN(prod_price) AS min_price, 3MAX(prod_price) AS max_price, 4SUM(prod_price) AS sum, 5AVG(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