SQLの基本操作

MySQL

概要†

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

テーブルの作成†

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

テーブルの削除†

mysql> drop table t_name;

最初から10件を表示†

mysql> select name from t_name limit 10

既存テーブルへフィールドの追加†

mysql> alter table t_name add request_time text;

レコードの挿入†

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

指定したフィールドで並べ替え表示†

mysql> select * from t_name order by request_time;

フィールドのデータ型を確認†

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

フィールドのデータ型を変更†

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)

曖昧検索†

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)

レコードの更新†

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 |
+------+-----------+------------------------------+------------+

レコードの更新（複数カラム）†

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