MySQL

概要 [#f0db92b1]

よく使うであろうごくごく基本的なSQL文を記載しておきます。

テーブルの作成 [#yf0be901]

 mysql> create table t_name(id text,name text);

テーブルの削除 [#w5f4ac60]

 mysql> drop table t_name;

最初から10件を表示 [#ee0469df]

 mysql> select name from t_name limit 10

既存テーブルへフィールドの追加 [#r504df6d]

 mysql> alter table t_name add request_time text;

レコードの挿入 [#nf11f22f]

 mysql> insert into t_name (id,name,request_time) values ('02','suzuki',' [29/Aug/2012:21:08:12 +0900]');

指定したフィールドで並べ替え表示 [#ia0e1f42]

 mysql> select * from t_name order by request_time;

フィールドのデータ型を確認 [#f8ecceb0]

 mysql> describe test01;
 +--------------+------+------+-----+---------+-------+
 | Field        | Type | Null | Key | Default | Extra |
 +--------------+------+------+-----+---------+-------+
 | id           | text | YES  |     | NULL    |       |
 | name         | text | YES  |     | NULL    |       |
 | request_time | text | YES  |     | NULL    |       |
 +--------------+------+------+-----+---------+-------+

フィールドのデータ型を変更 [#y6e1575b]

 mysql> alter table t_name change column request_time request_time char(28); <-- change column 既存フィールド名　新フィールド名　データ型
 Query OK, 13 rows affected, 13 warnings (0.02 sec)
 Records: 13  Duplicates: 0  Warnings: 13

 mysql> describe t_name;
 +--------------+----------+------+-----+---------+-------+
 | Field        | Type     | Null | Key | Default | Extra |
 +--------------+----------+------+-----+---------+-------+
 | id           | text     | YES  |     | NULL    |       |
 | name         | text     | YES  |     | NULL    |       |
 | request_time | char(28) | YES  |     | NULL    |       |
 +--------------+----------+------+-----+---------+-------+
 3 rows in set (0.00 sec)

曖昧検索 [#cc4d1f11]

 mysql> select * from t_name where id = '04' and request_time like '%2012:22%';
 +------+------+------------------------------+------------+
 | id   | name | request_time                 | time_stamp |
 +------+------+------------------------------+------------+
 | 04   | sato |  [30/Aug/2012:22:10:12 +0900 |       NULL |
 +------+------+------------------------------+------------+
 1 row in set (0.00 sec)

レコードの更新 [#jab5c21a]

 mysql> update t_name set id = '13' where id = '04' and request_time like '%2012:22%';
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

 mysql> select * from t_name;
 +------+-----------+------------------------------+------------+
 | id   | name      | request_time                 | time_stamp |
 +------+-----------+------------------------------+------------+
 | 13   | sato      |  [30/Aug/2012:22:10:12 +0900 |       NULL |
 +------+-----------+------------------------------+------------+

レコードの更新（複数カラム） [#b835667e]

 mysql> update t_name set id = '04',time_stamp = '1347200000' where id = '4';