MySQL メモ
MySQL
テーブル一覧
mysql> show tables;
+-------------------------------------------+
| Tables_in_thws |
+-------------------------------------------+
| cdr |
| cdr_status |
+-------------------------------------------+
2 rows in set (0.00 sec)
+-------------------------------------------+
| Tables_in_thws |
+-------------------------------------------+
| cdr |
| cdr_status |
+-------------------------------------------+
2 rows in set (0.00 sec)
テーブルレイアウトの確認
mysql> desc cdr;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| calldate | datetime | NO | | | |
| clid | varchar(80) | NO | | | |
:
| uniqueid | varchar(32) | NO | | | |
| userfield | varchar(255) | NO | | | |
+-------------+--------------+------+-----+---------+-------+
16 rows in set (0.00 sec)
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| calldate | datetime | NO | | | |
| clid | varchar(80) | NO | | | |
:
| uniqueid | varchar(32) | NO | | | |
| userfield | varchar(255) | NO | | | |
+-------------+--------------+------+-----+---------+-------+
16 rows in set (0.00 sec)
カラムの追加
cdr テーブルに、uniqueid カラムの後に status カラム(varchar(1))を追加します。
mysql> ALTER TABLE cdr ADD status varchar(1) AFTER uniqueid;
Query OK, 20 rows affected (0.11 sec)
Records: 20 Duplicates: 0 Warnings: 0
Query OK, 20 rows affected (0.11 sec)
Records: 20 Duplicates: 0 Warnings: 0
カラムの削除
cdr テーブルの status カラムを削除します。
mysql> ALTER TABLE cdr DROP status;
Query OK, 20 rows affected (0.08 sec)
Records: 20 Duplicates: 0 Warnings: 0
Query OK, 20 rows affected (0.08 sec)
Records: 20 Duplicates: 0 Warnings: 0
データベースの作成
mysql> CREATE DATABASE cntnts_mgr;
テーブルの作成
mysql> CREATE TABLE cntnts_info (
unq_id BIGINT NOT NULL AUTO_INCREMENT,
prsn_unq_id BIGINT NULL,
cntnts varchar(255) NOT NULL,
entry_dttm datetime NOT NULL,
PRIMARY KEY (unq_id)
);
unq_id BIGINT NOT NULL AUTO_INCREMENT,
prsn_unq_id BIGINT NULL,
cntnts varchar(255) NOT NULL,
entry_dttm datetime NOT NULL,
PRIMARY KEY (unq_id)
);
AUTO_INCREMENT(自動採番)
mysql> CREATE TABLE fruits
(
unq_id BIGINT NOT NULL AUTO_INCREMENT
, name VARCHAR(30) NOT NULL
, PRIMARY KEY (unq_id)
);
mysql> INSERT INTO fruits (name)
VALUES ("apple"),("orange"),("grape");
mysql> SELECT * FROM fruits;
+--------+--------+
| unq_id | name |
+--------+--------+
| 1 | apple |
| 2 | orange |
| 3 | grape |
+--------+--------+
3 rows in set (0.00 sec)
mysql> SELECT LAST_INSERT_ID() FROM dual;
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
(
unq_id BIGINT NOT NULL AUTO_INCREMENT
, name VARCHAR(30) NOT NULL
, PRIMARY KEY (unq_id)
);
mysql> INSERT INTO fruits (name)
VALUES ("apple"),("orange"),("grape");
mysql> SELECT * FROM fruits;
+--------+--------+
| unq_id | name |
+--------+--------+
| 1 | apple |
| 2 | orange |
| 3 | grape |
+--------+--------+
3 rows in set (0.00 sec)
mysql> SELECT LAST_INSERT_ID() FROM dual;
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
LAST_INSERT_ID() は連番の最初の数を返します。
AUTO_INCREMENT の初期値の設定は ALTER TABLE で行います。
mysql> ALTER TABLE fruits AUTO_INCREMENT=1000;
Query OK, 3 rows affected (0.53 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 3 rows affected (0.53 sec)
Records: 3 Duplicates: 0 Warnings: 0
後から AUTO_INCREMENT を追加したい場合、ALTER TABLE で行います。
mysql> ALTER TABLE fruits CHANGE un
q_id unq_id BIGINT NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
AUTO_INCREMENT は1つのテーブルに1つだけしか設定できないようです。 例えば、新たに AUTO_INCREMENT の項目を追加しようとすると次のエラーが出ます。
mysql>ALTER TABLE fruits ADD COLUMN unq_no BIGINT NOT NULL AUTO_INCREME
NT;
ERROR 1075 (42000): Incorrect table definition; there can be only one a
uto column and it must be defined as a key
NT;
ERROR 1075 (42000): Incorrect table definition; there can be only one a
uto column and it must be defined as a key
項目追加後、ALTER TABLE で AUTO_INCREMENT を付けようとしても同じエラーがでました。
mysql>ALTER TABLE fruits ADD COLUMN unq_no BIGINT NOT NULL;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>ALTER TABLE fruits CHANGE unq_no unq_no BIGINT NOT NULL AUTO_INCR
EMENT;
ERROR 1075 (42000): Incorrect table definition; there can be only one a
uto column and it must be defined as a key
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>ALTER TABLE fruits CHANGE unq_no unq_no BIGINT NOT NULL AUTO_INCR
EMENT;
ERROR 1075 (42000): Incorrect table definition; there can be only one a
uto column and it must be defined as a key
外部キー制約の無効化
外部キー制約を無効化したい場合、FOREIGN_KEY_CHECKS に 0 を設定します。
mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
外部キー制約を有効にしたい場合、FOREIGN_KEY_CHECKS に 1 を設定します。
mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Copyright (C) 2013 ymlib.com