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()は列の1つの文字列を見つけ、
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()は2つの日付の差を計算します.
　　Date_Add()高度に柔軟な日付演算関数、Date_Format()はフォーマットされた日付または時間列を返し、Day()は日付の日数部分を返します.
DayOfWeek()は1つの日付に対して対応する曜日を返し、Hour()は1時間の時間部分を返し、Minute()は1時間の分部分を返し、
Month()は1つの時間の月部分を返し、Now()は現在の日付と時間Second()は1つの時間の秒部分を返し、Time()は1つの日付を返す
時間の時間部分、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