おさらい 途中 新しく作ったデータベースに旧dbから値をコピーする復習を行なった free78
Tuesday,July,23,2013
最初はsadachi_productsに格納されている値を他のデーターベースのテーブルにコピーしようと考えた。
○テーブルの中身の表示 (sadachi_products)*追加したり編集したりする前に、この時点の値を他のデーターベースへバックアップしたい(移動)。
mysql> select * from sadachika_shop.sadachi_products; free62
sada_id sada_name sada_category
1 牛乳 飲み物
2 たこ焼き 食べ物
3 りんご 果物
4 ワイン 飲み物
5 ピザ 食べ物
6 ブドウ 果物
7 にんじん 野菜
8 コーラ 飲み物
9 牛丼 食べ物
10 キューイフルーツ 果物
11 ほうれん草 野菜
12 ビール 飲み物
13 天丼 食べ物
1 牛乳 Drink
2 たこ焼き Food
3 りんご Fruit
4 ワイン Drink
5 ピザ Food
6 ブドウ Fruit
7 にんじん Vegetables
8 コーラ Drink
9 牛丼 Food
10 キューイフルーツ Fruit
11 ほうれん草 Vegetables
12 ビール Drink
13 天丼 Food
そこで、データーベースの設定を見ていくことにした
○データベースを表示させる。
MySQL> show databases;
Database
sadachika_copy
sadachika_shop *ここに有るテーブルを[sadachika_copy]へ持っていく
*値を違うデーターベースに持っていくには、一応データーベースの詳細を確認しておいた方がいいよね
*特に文字セットと、照合順序
*確認した限りでは、いつも使っているdbは、文字セットが[utf8]、照合順序が[utf8_general_ci]でした。
*テーブルの設定をそのまま持って行ってもいいのだけど、デーブルの詳細も確認してみよう。
*テーブルの設定を持っていくほどのものがない、ほとんどデフォルトですね。プライマリーもユニークキーもなし、データ無しの書き込みもOK デフォルトもそのまま
○テーブルの構造を見る
mysql> show columns from sadachika_shop.sadachi_products; *columns(新聞など印刷物の)縦の段; 欄.
Field Type Null Key Default Extra
sada_id int(11) YES NULL *カラム名sada_id INT型(おそらく数字のみ入力可能)
sada_name varchar(20) YES NULL *カラム名sada_name VARCHAR型 文字による値を格納する、INT型は数字しか格納できない
sada_category varchar(20) YES NULL *カラム名sada_category VARCHAR型 文字による値を格納する
○作成したテーブル(sadachi_products)の定義を確認してみる エンジンが分りません？ENGINE=InnoDB
mysql> show create table sadachika_shop.sadachi_products\G
*********** 1. row **************
Table: sadachi_products
Create Table: CREATE TABLE 'sadachi_products'(
'sada_id' int(11) DEFAULT NULL, *カムラの名前sada_id、INT型
'sada_name' varchar(20) DEFAULT NULL, *カムラの名前sada_name、VARCHAR型
'sada_category' varchar(20) DEFAULT NULL *カムラの名前sada_category、VARCHAR型 デフォルト値はNULL 例えば→default(sada_category)と入力すると= NULLが表示される。格納はされないよね、値が無いんだから。
) ENGINE=InnoDB DEFAULT CHARSET=utf8 *やばい、エンジンの意味が分らない *utf8はデータベース作成で設定した文字セット[utf8]
1 row in set (0.02 sec) *例えば、こんな感じ、忘れないために書いておくmysql> create database sadachika_cp932db character set cp932;
*↑デフォルトはInnoDBだが、指定する場合のためにメモを残しておく InnoDBを変えたりするには、mysql> create table sadachika_novel(sada_id int) engine=MyISAM; *INT型 MyISAMと入力
○テーブルの詳細情報を取得する
mysql> show table status\G
公式SHOW TABLE STATUS LIKE 'pattern';
SHOW TABLE STATUS LIKE 'sadachi_uniquetest'\G';
SHOW TABLE STATUS LIKE 'sadachi_uniquetest'\G
公式SHOW TABLE STATUS FROM db_name LIKE 'pattern';
SHOW TABLE STATUS FROM sadachika_books LIKE 'sadachi_uniquetest';
SHOW TABLE STATUS FROM sadachika_books LIKE 'sadachi_uniquetest'\G
SHOW TABLE STATUS FROM sadachika_shop LIKE 'sadachi_products'\G
**************** 1.row **********************
Name: sadachi_products
Engine: InnoDB *この設定がわからない？どういう性能なの？
Version: 10
Row_format: Compact
Rows: 25
Avg_row_length: 655
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2013-07-10 10:08:19
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci *文字セット「utf8」のデフォルト照合順序は「utf8_general_ci」照合順序というのは複数の値を比較する時に、どのように比較するのかを定義しているもの
Checksum: NULL
Create_options:
Comment:
○○データーベースの詳細表示
↓db名SQLが作成 ↓テーブル名SQLが勝手に作成
MySQL> SELECT * FROM information_schema.schemata;
↑全カラム名を表示schema_name, default_character_set_name, default_collation_name
↑*schema_nameカラムにはデータベース名が格納されている
↑*default_character_set_nameカラムには文字セット名が格納されている
↑*default_collation_nameカラムには照合順序が格納されている。
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
def information_schema utf8 utf8_general_ci NULL
def book utf8 utf8_general_ci NULL
def goods utf8 utf8_general_ci NULL
def mysql utf8 utf8_general_ci NULL
def performance_schema utf8 utf8_general_ci NULL
def personal utf8 utf8_general_ci NULL
def sadachika utf8 utf8_general_ci NULL
def sadachika_books utf8 utf8_general_ci NULL *文字セットが[utf8]照合順序が[utf8_general_ci]
def sadachika_copy utf8 utf8_general_ci NULL *文字セットが[utf8]照合順序が[utf8_general_ci]
def sadachika_cp932db cp932 cp932_japanese_ci NULL
def sadachika_eucjpdb eucjpms eucjpms_bin NULL
edf sadachika_shop utf8 utf8_general_ci NULL *文字セットが[utf8]照合順序が[utf8_general_ci]
○○データーベースの詳細表示
↓db名自動作 ↓MySQLがテーブルを自動で作る(データーベースの情報を格納)
mysql> select * from information_schema.schemata
-> where schema_name = 'sadachika_shop'\G
***************** 1. row *******************
CATALOG_NAME:def
SCHEMA_NAME:sadachika_shop *データベース名
DEFAULT_CHARACTER_SET_NAME:utf8
DEFAULT_COLLATION_NAME:utf8_general_ci *「_ci」は大文字と小文字を区別しない。
SQL_PATH:NULL
1 row in set (0.13 sec)
○データベースを表示させる。
MySQL> show databases;
Database
sadachika_copy
sadachika_shop
○特定のデータベース内のテーブルを表示させる
SHOW TABLES FROM sadachika_copy; *テーブルがまったく無い様子
Empty set (0.33 set)
○本家テーブルを作成
mysql> create table sadachika_copy.sadachi_copyproducts(sada_copyname varchar(20) unique, sada_copycount int); *カラム名sada_copyname VARCHAR型 ユニークキー設定(重複を許さない), カラム名sada_copycount INT型
Query OK, 0 rows affected (0.70 sec)
○特定のデータベース内のテーブルを表示させる
SHOW TABLES FROM sadachika_copy;
Tables_in_sadachika_copy
sadachi_copyproducts *テーブルが増えた
1 row in set (0.00 set)
○カラムの構造を見る
mysql> show columns from sadachika_copy.sadachi_copyproducts;
Field Type Null Key Default Extra
sada_copyname varchar(20) YES UNI NULL *Key項目はUNI、ユニーク。
sada_copycount int(11) YES NULL
2 rows in set (0.13 sec)
○テーブルの中身の表示 (sadachi_copyproducts)
mysql> select * from sadachika_copy.sadachi_copyproducts;
Empty set (0.00 set) *現在カラムに値が格納されていない
○○○本家のテーブルを移さなくても、そのままカラムも全てこぴーしちゃお 「と、思ったんだけどテーブル、カラムの設定のみコピーできるみたい、したがって値のコピーは別の方法を検討しよう」
いろいろなテーブルからカラムの値をコピーしたい AS句とかつけてカラム名変更したりとか
○テーブルの中身の表示
mysql> select * from sadachika_shop.sadachi_sourcetable;
sada_id sada_cate sada_name
1 1 牛乳
2 1 紅茶
3 2 牛丼
4 2 天丼
5 2 親子丼
6 1 お茶
7 3 マグロ
8 4 オレンジ
9 4 りんご
10 3 さんま
11 3 鯛
12 1 水
13 4 パイナップル
14 3 うなぎ
15 3 カレイ
16 1 スプライト
○カラムの構造を見る
mysql> show columns from sadachika_shop.sadachi_sourcetable;
Field Type Null Key Default Extra
sada_id int(11) NO PRI NULL *Key項目はPRI *カラム名sada_id int(11)型
sada_cate int(11) YES NULL *カラム名sada_cate int(11)型
sada_name varchar(10) YES NULL *カラム名sada_name VARCHAR(10)型 ←十ってめずらしい。
3 rows in set (0.13 sec)
○作成したテーブル(sadachi_sourcetable)の定義を確認してみる エンジンが分りません？ENGINE=InnoDB iniファイルのデフォルトが元になってそう
mysql> show create table sadachika_shop.sadachi_sourcetable\G
*********** 1. row **************
Table: sadachi_sourcetable
Create Table: CREATE TABLE 'sadachi_sourcetable'(
'sada_id' int(11) NOT NULL, *カムラの名前sada_id、 INT型
'sada_cate' int(11) DEFAULT NULL, *カムラの名前sada_cate、INT型 ↓十
'sada_name' varchar(10) DEFAULT NULL, *カムラの名前sada_name、VARCHA(10)R型 デフォルト値はNULL 例えば→default(sada_category)と入力すると= NULLが表示される。格納はされないよね、値が無いんだから。
PRIMARY KEY ('sada_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 *やばい、エンジンの意味が分らない *utf8はデータベース作成で設定した文字セット[utf8]
1 row in set (0.02 sec) *例えば、こんな感じ、忘れないために書いておくmysql> create database sadachika_cp932db character set cp932;
*↑デフォルトはInnoDBだが、指定する場合のためにメモを残しておく InnoDBを変えたりするには、mysql> create table sadachika_novel(sada_id int) engine=MyISAM; *INT型 MyISAMと入力
○○○元テーブル(sadachi_sourcetable)設定をそのままに新しいテーブル(sadachi_copysourcetable)を作る
mysql> create table sadachika_copy.sadachi_copysourcetable like sadachika_shop.sadachi_sourcetable;
↑新しいデータベース名 ↑新テーブル名 ↑元データベース名 ↑元テーブル名
Query OK, 0 rows affected (0.14 sec)
○○テーブルの中身の表示 ↓新しいデータベース名 ↓新テーブル名
mysql> select * from sadachika_copy.sadachi_copysourcetable; *設定のみコピーし、値のコピーは行なわれていない
Empty set (0.01 sec)
○特定のデータベース内のテーブルを表示させる
SHOW TABLES FROM sadachika_copy;
Tables_in_sadachika_copy
sadachi_copyproducts
sadachi_copysourcetable *テーブルが増えた
1 row in set (0.00 set)
○作成したテーブル(sadachi_copysourcetable)の定義を確認してみる
全ての設定項目のコピーが完了しています。
(sadachika_shop.sadachi_sourcetable) → (sadachika_copy.sadachi_copysourcetable)
mysql> show create table sadachika_copy.sadachi_copysourcetable\G
*********** 1. row **************
Table: sadachi_copysourcetable
Create Table: CREATE TABLE 'sadachi_copysourcetable'(
'sada_id' int(11) NOT NULL, *カムラの名前sada_id、 INT型
'sada_cate' int(11) DEFAULT NULL, *カムラの名前sada_cate、INT型 ↓十
'sada_name' varchar(10) DEFAULT NULL, *カムラの名前sada_name、VARCHA(10)R型 デフォルト値はNULL 例えば→default(sada_category)と入力すると= NULLが表示される。格納はされないよね、値が無いんだから。
PRIMARY KEY ('sada_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 *やばい、エンジンの意味が分らない *utf8はデータベース作成で設定した文字セット[utf8]
1 row in set (0.00 sec) *例えば、こんな感じ、忘れないために書いておくmysql> create database sadachika_cp932db character set cp932;
*↑デフォルトはInnoDBだが、指定する場合のためにメモを残しておく InnoDBを変えたりするには、mysql> create table sadachika_novel(sada_id int) engine=MyISAM; *INT型 MyISAMと入力
○テーブルの中身の表示
mysql> select * from sadachika_shop.sadachi_sourcetable;
sada_id sada_cate sada_name
1 1 牛乳
2 1 紅茶
3 2 牛丼
4 2 天丼
5 2 親子丼
6 1 お茶
7 3 マグロ
8 4 オレンジ
9 4 りんご
10 3 さんま
11 3 鯛
12 1 水
13 4 パイナップル
14 3 うなぎ
15 3 カレイ
16 1 スプライト
○○○元テーブルのカラムを指定して新しいテーブルにコピーする free57
mysql> create table sadachika_copy.sadachi_newcopyproductname select sada_id, sada_name, sada_cate from sadachika_shop.sadachi_sourcetable;
↑新データベース名 ↑新テーブル名 ↑元カラム名 並ぶ順番を入れ替えています ↑元データベース名 ↑元テーブル名名
Query OK, 16 rows affected (0.48 sec)
Records: 16 Duplicates: 0 Warnings: 0
○特定のデータベース内のテーブルを表示させる
SHOW TABLES FROM sadachika_copy;
Tables_in_sadachika_copy
sadachi_copyproducts
sadachi_copysourcetable
sadachi_newcopyproductname *テーブルが増えた
3 rows in set (0.00 set)
○カラムの構造を見る
mysql> show columns from sadachika_copy.sadachi_newcopyproductname;
Field Type Null Key Default Extra
sada_id int(11) NO NULL *もとはKey項目PRIがあったが無くなっている *カラム名sada_id int(11)型
sada_name varchar(10) YES NULL *順番は入れ変わりました。*カラム名sada_name VARCHAR(10)型 ←十ってめずらしい。
sada_cate int(11) YES NULL *カラム名sada_cate int(11)型
3 rows in set (0.13 sec)
○テーブルの中身の表示 カラムの表示の順番を変えました。
mysql> select * from sadachika_copy.sadachi_newcopyproductname;
sada_id sada_name sada_cate
1 牛乳 1
2 紅茶 1
3 牛丼 2
4 天丼 2
5 親子丼 2
6 お茶 1
7 マグロ 3
8 オレンジ 4
9 りんご 4
10 さんま 3
11 鯛 3
12 水 1
13 パイナップル 4
14 うなぎ 3
15 カレイ 3
16 スプライト 1
○プライマリーキー設定が無くなってしまったので、改めて設定し直します。
VARCHAR型なら、unique その他のオプション default 'データなし', default 0, not null(データなし を許さない)
INT型なら、primary key
おさらいメモ
○カラム定義だけを変更する
mysql> alter table sadachika_shop.sadachi_food modify hirayama_id smallint;
↑対象のカラム ↑*変更したい型を入力する {元はINT型 not null(データ無しを許さない)} → SMALLINT型へ変更を行なう
○カラム名の変更、データ型の変更
mysql> alter table sadachika_shop.sadachi_food change sada_id hirayama_id int not null; *名前だけ変更するのであってもあわせて型を入力する。
↑旧カラム名 ↑新しく変更するカラム名(hirayama_id)
○○これを使う カラム名の変更、データ型の変更 ↓旧カラム名 ↓新しく変更するカラム名(sada_copyid)
mysql> alter table sadachika_copy.sadachi_newcopyproductname change sada_id sada_copyid int(11) not null primary key; *プライマリーキー設定、データ無しを許可しない設定。*名前だけ変更するのであってもあわせて型を入力する。
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
↓旧カラム名 ↓新しく変更するカラム名(sada_copyname)
mysql> alter table sadachika_copy.sadachi_newcopyproductname change sada_name sada_copyname varchar(20) unique;
↑二十 ↑ユニークキー
繰り返してしまった
mysql> alter table sadachika_copy.sadachi_newcopyproductname change sada_copyname sada_name varchar(20) unique;
mysql> alter table sadachika_copy.sadachi_newcopyproductname change sada_name sada_copyname varchar(20) unique;
Query OK, 16 rows affected (0.27 sec)
Records: 16 Duplicates: 0 Warnings: 0
Query OK, 16 rows affected, 1 warning (0.27 sec)
Records: 16 Duplicates: 0 Warnings: 1
mysql> alter table sadachika_copy.sadachi_newcopyproductname change sada_cate sada_copycate int(11);
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
○テーブルの中身の表示 カラムの表示の順番を変えました。
mysql> select * from sadachika_copy.sadachi_newcopyproductname;
sada_copyid sada_copyname sada_copycate *カラム名が変わったsada_id→sada_copyid, sada_name→sada_copyname, sada_cate→sada_copycate
1 牛乳 1
2 紅茶 1
3 牛丼 2
4 天丼 2
5 親子丼 2
6 お茶 1
7 マグロ 3
8 オレンジ 4
9 りんご 4
10 さんま 3
11 鯛 3
12 水 1
13 パイナップル 4
14 うなぎ 3
15 カレイ 3
16 スプライト 1
○カラムの構造を見る
mysql> show columns from sadachika_copy.sadachi_newcopyproductname;
Field Type Null Key Default Extra
sada_copyid int(11) NO PRI NULL *もとはKey項目PRI *カラム名sada_copyid int(11)型
sada_copyname varchar(20) YES UNI NULL *順番は入れ変わりました。*カラム名sada_copyname VARCHAR(20)型 10→20。
sada_copycate int(11) YES NULL *カラム名sada_copycate int(11)型
3 rows in set (0.01 sec)
○特定のデータベース内のテーブルを表示させる
SHOW TABLES FROM sadachika_copy;
Tables_in_sadachika_copy
sadachi_copyproducts
sadachi_copysourcetable
sadachi_newcopyproductname
3 rows in set (0.00 set)
○テーブルの中身の表示
mysql> select * from sadachika_copy.sadachi_copyproducts; *オリジナル、ただし値は格納されていない
Empty set (0.00 sec)
mysql> select * from sadachika_copy.sadachi_copysourcetable; *設定のみコピーし、値のコピーは行なわれていない 元は○○○テーブル(sadachi_sourcetable)設定をそのままに新しいテーブル(sadachi_copysourcetable)を作っくた
Empty set (0.00 sec)
mysql> select * from sadachika_copy.sadachi_newcopyproductname; *元はsadachika_shop.sadachi_sourcetableの値を移動しカラムの順番名前を変更した
Empty set (0.00 sec)
////////////////////////疲れた、日を変えたい//////////////////////////////
sadachika_shop.sadachi_products;の値をcopy.db内のどこかのテーブルの格納したいな
インサートでいいのかな？free70
トリガーもビューもおさらいしたいな。
○他のテーブルから、本家のテーブルに値を格納する
本家テーブルのsada_nameカラムにはユニーク設定が施されているので重複の値を格納できない
そこでエラーを発生させないIGNOREを入れるプログラムを作成する
↓IGNOREを入れる
mysql> insert ignore into sadachika_copy.sadachi_copysourcetable select hira_name, hira_count from sadachika_shop.hiraya_yamaguchisales;
↑格納する本家テーブル名 ↑分家カラム↑分家カラム ↑分家テーブル名
mysql> select * from sadachika_shop.sadachi_products;
sada_id sada_name sada_category
1 牛乳 飲み物
2 たこ焼き 食べ物
3 りんご 果物
○テーブルの構造を見る
mysql> show columns from sadachika_shop.sadachi_products; *columns(新聞など印刷物の)縦の段; 欄.
Field Type Null Key Default Extra
sada_id int(11) YES NULL *カラム名sada_id INT型(おそらく数字のみ入力可能)
sada_name varchar(20) YES NULL *カラム名sada_name VARCHAR型 文字による値を格納する、INT型は数字しか格納できない
sada_category varchar(20) YES NULL
○カラムの構造を見る
mysql> show columns from sadachika_copy.sadachi_copysourcetable;
Field Type Null Key Default Extra
sada_id int(11) NO PRI NULL *Key項目はPRI *カラム名sada_id int(11)型
sada_cate int(11) YES NULL *カラム名sada_cate int(11)型
sada_name varchar(10) YES NULL *カラム名sada_name VARCHAR(10)型 ←十ってめずらしい。
3 rows in set (0.13 sec)
* 参考にしたサイトは→ こちら です。
これは赤色の文字例です。
これは青色の文字例です。