SELECT は1 つ以上のテーブルからレコードを選択して取り出すときに使用します。
各 select_expression は、取り出すカラムを表します。
SELECT は、どのテーブルも参照することなく、計算によって求められたレコードを取り出すときにも使用できます。


mysql> select
-> TABLE_NAME,
-> YEAR(CREATE_TIME) AS Year,
-> MONTH(CREATE_TIME) AS Month,
-> DAY(CREATE_TIME) AS DAY
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> Order by Year DESC,Month DESC, DAY DESC;
+----------------+------+-------+------+
| TABLE_NAME | Year | Month | DAY |
+----------------+------+-------+------+
| A10 | 2009 | 7 | 23 |
| innodbtable | 2009 | 7 | 23 |
| T10 | 2009 | 7 | 23 |
| innodb_monitor | 2009 | 7 | 21 |
+----------------+------+-------+------+
4 rows in set (0.00 sec)

mysql>

table2

COLLATIONによってOrder byの結果も変わってきます。
(CI / CS / Binなど)


mysql> select
-> TABLE_NAME
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> order by TABLE_NAME
-> COLLATE utf8_general_ci;

+----------------+
| TABLE_NAME |
+----------------+
| A10 |
| a10 |
| innodbtable |
| innodb_monitor |
| t10 |
| T10 |
+----------------+
6 rows in set (0.00 sec)

mysql> select
-> TABLE_NAME
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> order by TABLE_NAME
-> COLLATE utf8_bin;
+----------------+
| TABLE_NAME |
+----------------+
| A10 |
| T10 |
| a10 |
| innodb_monitor |
| innodbtable |
| t10 |
+----------------+
6 rows in set (0.00 sec)

mysql>

cs

LIMIT 句


mysql> select
-> TABLE_NAME,
-> CREATE_TIME
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> Order by CREATE_TIME
-> LIMIT 1;
+----------------+---------------------+
| TABLE_NAME | CREATE_TIME |
+----------------+---------------------+
| innodb_monitor | 2009-07-21 18:08:11 |
+----------------+---------------------+
1 row in set (0.00 sec)


choko

GROUP BY


mysql> select
-> ENGINE,
-> COUNT(*) AS Total
-> from information_schema.TABLES
-> where ENGINE IS NOT NULL
-> GROUP BY ENGINE;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| CSV | 2 |
| InnoDB | 12 |
| MEMORY | 20 |
| MyISAM | 134 |
+--------+-------+
4 rows in set (0.01 sec)

mysql>

mysql> SELECT
-> ENGINE,
-> COUNT(*) AS Total
-> FROM information_schema.TABLES
-> GROUP BY ENGINE
-> HAVING ENGINE='MYISAM' OR ENGINE='INNODB'
-> ORDER BY Total DESC;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| MyISAM | 134 |
| InnoDB | 12 |
+--------+-------+
2 rows in set (0.01 sec)

mysql>

groupby

having

参考サイト:
6.4.1. SELECT 構文

Comments are closed.

Post Navigation