SQLの基本操作
をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
検索
|
最終更新
|
ヘルプ
|
ログイン
]
開始行:
[[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 ...
*指定したフィールドで並べ替え表示 [#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 req...
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_...
+------+------+------------------------------+----------...
| id | name | request_time | time_stam...
+------+------+------------------------------+----------...
| 04 | sato | [30/Aug/2012:22:10:12 +0900 | NUL...
+------+------+------------------------------+----------...
1 row in set (0.00 sec)
*レコードの更新 [#jab5c21a]
mysql> update t_name set id = '13' where id = '04' and r...
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...
+------+-----------+------------------------------+-----...
| 13 | sato | [30/Aug/2012:22:10:12 +0900 | ...
+------+-----------+------------------------------+-----...
*レコードの更新(複数カラム) [#b835667e]
mysql> update t_name set id = '04',time_stamp = '1347200...
終了行:
[[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 ...
*指定したフィールドで並べ替え表示 [#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 req...
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_...
+------+------+------------------------------+----------...
| id | name | request_time | time_stam...
+------+------+------------------------------+----------...
| 04 | sato | [30/Aug/2012:22:10:12 +0900 | NUL...
+------+------+------------------------------+----------...
1 row in set (0.00 sec)
*レコードの更新 [#jab5c21a]
mysql> update t_name set id = '13' where id = '04' and r...
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...
+------+-----------+------------------------------+-----...
| 13 | sato | [30/Aug/2012:22:10:12 +0900 | ...
+------+-----------+------------------------------+-----...
*レコードの更新(複数カラム) [#b835667e]
mysql> update t_name set id = '04',time_stamp = '1347200...
ページ名: