テーブルの作成
CREATE TABLE user (
id int(10) NOT NULL AUTO_INCREMENT,
name varchar(50),
created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
modified_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
テーブル詳細・CREATE TABLEの確認
mysql> SHOW CREATE TABLE user;
+--------+-----------------------------------------------------------------------
| Table | Create Table
+--------+-----------------------------------------------------------------------
| user | CREATE TABLE user (
id int(10) NOT NULL AUTO_INCREMENT,
name varchar(50),
created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
modified_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------
1 row in set (0.00 sec)
テーブルの削除
mysql> DROP TABLE user;
Query OK, 0 rows affected (0.20 sec)
テーブル一覧の取得
mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| user |
+------------------+
1 row in set (0.00 sec)
レコードの挿入 1
mysql> INSERT INTO user (name, created_at, modified_at)
VALUES ('user1', '2000-01-01 10:10:10', NOW());
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM user;
+----+-------+---------------------+---------------------+
| id | name | created_at | modified_at |
+----+-------+---------------------+---------------------+
| 1 | user1 | 2000-01-01 10:10:10 | 2014-01-10 19:48:57 |
+----+-------+---------------------+---------------------+
1 row in set (0.00 sec)
レコードの挿入 2
mysql> INSERT INTO user VALUES (100, 'user1', '2001-01-01 10:10:10', NOW())
, (101, 'user2', '2001-01-01 10:10:10', NOW());
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM user;
+-----+-------+---------------------+---------------------+
| id | name | created_at | modified_at |
+-----+-------+---------------------+---------------------+
| 100 | user1 | 2001-01-01 10:10:10 | 2014-01-10 21:01:27 |
| 101 | user2 | 2001-01-01 10:10:10 | 2014-01-10 21:01:27 |
+-----+-------+---------------------+---------------------+
2 rows in set (0.00 sec)
レコードの更新
mysql> UPDATE user SET modified_at = NOW() WHERE id = 1;
Query OK, 1 row affected (0.23 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM user;
+----+-------+---------------------+---------------------+
| id | name | created_at | modified_at |
+----+-------+---------------------+---------------------+
| 1 | user1 | 2014-01-10 21:01:54 | 2014-01-10 21:05:33 |
+----+-------+---------------------+---------------------+
1 rows in set (0.00 sec)
レコードの削除
mysql> DELETE FROM user WHERE id = 1;
Query OK, 1 row affected (0.16 sec)
mysql> SELECT * FROM user;
Empty set (0.00 sec)
参考サイト