おさらい テーブルに格納されている値をテーブル名ごと他のデータベースにコピーする。
その後、値をアップデートしたり、上書きしたりといろいろテストした。
上書きされる側と上書きする側を計算式で足したいのだがやり方がわからなかった。 free82
Friday,July,26,2013
○○元テーブルのカラムを指定して新しいテーブルに値をコピーする 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
mysql> create table sadachika_copy.sadachi_copyproducts select * from sadachika_shop.sadachi_products;
↑新データベース名 ↑新テーブル名 ↑全てのカラムを選択 ↑元カラム名
Query OK, 13 rows affected (0.13 sec)
Records: 13 Duplicates: 0 Warnings: 0
○テーブルの中身の表示
select * from sadachika_copy.sadachi_copyproducts;
sada_id sada_name sada_category sada_price sada_releasedate
1 牛乳 飲み物 198 2013-10-18
2 たこ焼き 食べ物 100 2012-07-25
3 りんご 果物 150 2013-01-08
4 ワイン 飲み物 250 2013-03-12
5 ピザ 食べ物 450 2011-05-03
6 ブドウ 果物 300 2013-07-16
7 にんじん 野菜 35 2013-03-17
8 コーラ 飲み物 98 2012-05-21
9 牛丼 食べ物 350 2011-02-25
10 キューイフルーツ 果物 160 2013-09-19
11 ほうれん草 野菜 145 2013-04-25
12 ビール 飲み物 220 2012-07-01
13 天丼 食べ物 460 2013-06-25
○カラムの構造を見る
mysql> show columns from sadachika_copy.sadachi_copyproducts;
Field Type Null Key Default Extra
sada_id int(11) NO NULL
sada_name varchar(20) YES NULL
sada_category varchar(20) YES NULL
sada_price int(11) YES NULL
sada_releasedate date YES NULL
5 rows in set (0.00 sec)
○カラム名の変更、データ型の変更 ↓旧カラム名 ↓新しく変更するカラム名(sada_copyid)
mysql> alter table sadachika_copy.sadachi_copyproducts change sada_id sada_copyid int(11) not null primary key; *プライマリーキー設定、データ無しを許可しない設定。*名前だけ変更するのであってもあわせて型を入力する。
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from sadachika_copy.sadachi_copyproducts;
Field Type Null Key Default Extra
sada_copyid int(11) NO PRI NULL *Key項目にPRIが表示された
sada_name varchar(20) YES NULL
sada_category varchar(20) YES NULL
sada_price int(11) YES NULL
sada_releasedate date YES NULL
5 rows in set (0.00 sec)
○○「sada_releasedate」カラムの値が'20120101を含む、以降 20130101を含む、まで'のデータだけを取得します。
mysql> select * from sadachika_copy.sadachi_copyproducts where sada_releasedate >= 20120101 and sada_releasedate <= 20130101;
sada_copyid sada_name sada_category sada_price sada_releasedate
2 たこ焼き 食べ物 100 2012-07-25
8 コーラ 飲み物 98 2012-05-21
12 ビール 飲み物 220 2012-07-01
3 rows in set (0.00 sec)
○○削除する桁数と並べ替え カラムsada_releasedate の値が10120601より大きく20130101より小さければ削除する。
mysql> delete from sadachika_copy.sadachi_copyproducts where sada_releasedate >= 20120101 and sada_releasedate <= 20130101;
Query OK, 3 rows affected (0.02 sec)
select * from sadachika_copy.sadachi_copyproducts;
sada_copyid sada_name sada_category sada_price sada_releasedate
1 牛乳 飲み物 198 2013-10-18
*削除された
3 りんご 果物 150 2013-01-08
4 ワイン 飲み物 250 2013-03-12
5 ピザ 食べ物 450 2011-05-03
6 ブドウ 果物 300 2013-07-16
7 にんじん 野菜 35 2013-03-17
*削除された
9 牛丼 食べ物 350 2011-02-25
10 キューイフルーツ 果物 160 2013-09-19
11 ほうれん草 野菜 145 2013-04-25
*削除された
13 天丼 食べ物 460 2013-06-25
○○「sada_releasedate」カラムの値が'20130102を含む、以降 20131017を含む、まで'のデータだけを取得します。
mysql> select * from sadachika_copy.sadachi_copyproducts where sada_releasedate >= 20130102 and sada_releasedate <= 20131017;
sada_copyid sada_name sada_category sada_price sada_releasedate
3 りんご 果物 150 2013-01-08
4 ワイン 飲み物 250 2013-03-12
6 ブドウ 果物 300 2013-07-16
7 にんじん 野菜 35 2013-03-17
10 キューイフルーツ 果物 160 2013-09-19
11 ほうれん草 野菜 145 2013-04-25
13 天丼 食べ物 460 2013-06-25
7 rows in set (0.00 sec)
○○削除する桁数と並べ替え カラムsada_releasedate の値が20130102より大きく20131017より小さければ削除する。
mysql> delete from sadachika_copy.sadachi_copyproducts where sada_releasedate >= 20130102 and sada_releasedate <= 20131017;
Query OK, 7 rows affected (0.00 sec)
select * from sadachika_copy.sadachi_copyproducts;
sada_copyid sada_name sada_category sada_price sada_releasedate
1 牛乳 飲み物 198 2013-10-18
5 ピザ 食べ物 450 2011-05-03
9 牛丼 食べ物 350 2011-02-25
○○削除する桁数と並べ替え カラムsada_copyid の値が1と同じか大きく、9と同じか多きければ削除する。
mysql> delete from sadachika_copy.sadachi_copyproducts where sada_copyid >= 1 and sada_copyid <=9;
Query OK, 3 rows affected (0.00 sec)
select * from sadachika_copy.sadachi_copyproducts;
Empty set (0.00 sec)
元に戻す
○○他のテーブルから、本家のテーブルに値を格納する
本家テーブルのsada_nameカラムにはユニーク設定が施されているので重複の値を格納できない
そこでエラーを発生させないIGNOREを入れるプログラムを作成する
↓IGNOREを入れる
mysql> insert ignore into sadachika_copy.sadachi_copyproducts select sada_id, sada_name, sada_category, sada_price, sada_releasedate from sadachika_shop.sadachi_products;
↑格納する新データベース名 ↑格納新テーブル名 ↑旧カラム↑旧カラム ↑旧カラム ↑旧db名 ↑旧テーブル名
Query OK, 13 rows affected (0.03 sec)
Records: 13 Duplicates: 0 Warnings: 0
select * from sadachika_copy.sadachi_copyproducts;
sada_copyid sada_name sada_category sada_price sada_releasedate
1 牛乳 飲み物 198 2013-10-18
2 たこ焼き 食べ物 100 2012-07-25
3 りんご 果物 150 2013-01-08
4 ワイン 飲み物 250 2013-03-12
5 ピザ 食べ物 450 2011-05-03
6 ブドウ 果物 300 2013-07-16
7 にんじん 野菜 35 2013-03-17
8 コーラ 飲み物 98 2012-05-21
9 牛丼 食べ物 350 2011-02-25
10 キューイフルーツ 果物 160 2013-09-19
11 ほうれん草 野菜 145 2013-04-25
12 ビール 飲み物 220 2012-07-01
13 天丼 食べ物 460 2013-06-25
上書きテスト
おさらいメモ。ユニークキーに設定すれば、この文も使用できるようになる。
○ON DUPLICATE KEY UPDATE句を使用して、重複する値の上書き格納を行なう
mysql> insert into sadachika_copy.sadachi_copyproducts select sada_id, sada_name, sada_category, sada_price, sada_releasedate from sadachika_shop.sadachi_products
-> on duplicate key update sada_copyid=values(sada_copyid), sada_name=values(sada_name), sada_category=values(sada_category), sada_price=values(sada_price), sada_releasedate=values(sada_releasedate);
↑新カラム ↑新カラム ↑新カラム
Query OK, 13 rows affected (0.00 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> insert into sadachika_copy.sadachi_copyproducts select sada_id, sada_name, sada_category, sada_price, sada_releasedate from sadachika_shop.sadachi_products
-> on duplicate key update sada_copyid=values(sada_copyid), sada_name=values(sada_name), sada_category=values(sada_category), sada_price=values(sada_price)+1, sada_releasedate=values(sada_releasedate);
↑新カラム ↑新カラム ↑新カラム ↑１コピーしたかどうか分らないので目印としてプラス1
Query OK, 13 rows affected (0.00 sec)
Records: 13 Duplicates: 0 Warnings: 0
ERROR mysql> insert into sadachika_copy.sadachi_copyproducts select sada_id, sada_name, sada_category, sada_price, sada_releasedate from sadachika_shop.sadachi_products
-> on duplicate key update sada_copyid=values(sada_copyid), sada_name=values(sada_name), sada_category=values(sada_category), sada_price=values(sada_price)+sada_price, sada_releasedate=values(sada_releasedate);
↑新カラム ↑新カラム ↑新カラム ↑上書き重複テスト なぜか失敗
Query OK, 13 rows affected (0.00 sec)
Records: 13 Duplicates: 0 Warnings: 0
○アップデート
mysql> update sadachika_copy.sadachi_copyproducts set sada_name = '定近', sada_category = 'ピープル',sada_releasedate = 19990909 where sada_copyid = 2;
Query OK, 1 row affected (0.25 sec)
Rows matched: 1 Changed: 1 Warnings: 0
○○削除する桁数と並べ替え カラムsada_copyid の値が6と同じか大きく、8と同じか多きければ削除する。
mysql> delete from sadachika_copy.sadachi_copyproducts where sada_copyid >= 6 and sada_copyid <=8;
Query OK, 3 rows affected (0.00 sec)
select * from sadachika_copy.sadachi_copyproducts;
sada_copyid sada_name sada_category sada_price sada_releasedate
1 牛乳 飲み物 199 2013-10-18
2 定近 ピープル 101 1999-09-09 *値が変更された
3 りんご 果物 151 2013-01-08
4 ワイン 飲み物 251 2013-03-12
5 ピザ 食べ物 451 2011-05-03
*削除された
*削除された
*削除された
9 牛丼 食べ物 351 2011-02-25
10 キューイフルーツ 果物 161 2013-09-19
11 ほうれん草 野菜 146 2013-04-25
12 ビール 飲み物 221 2012-07-01
13 天丼 食べ物 461 2013-06-25
○値を元に戻す
mysql> insert into sadachika_copy.sadachi_copyproducts select sada_id, sada_name, sada_category, sada_price, sada_releasedate from sadachika_shop.sadachi_products
-> on duplicate key update sada_copyid=values(sada_copyid), sada_name=values(sada_name), sada_category=values(sada_category), sada_price=values(sada_price), sada_releasedate=values(sada_releasedate);
↑新カラム ↑新カラム ↑新カラム
Query OK, 23 rows affected (0.03 sec)
Records: 13 Duplicates: 10 Warnings: 0
select * from sadachika_copy.sadachi_copyproducts;
sada_copyid sada_name sada_category sada_price sada_releasedate
1 牛乳 飲み物 198 2013-10-18
2 たこ焼き 食べ物 100 2012-07-25
3 りんご 果物 150 2013-01-08
4 ワイン 飲み物 250 2013-03-12
5 ピザ 食べ物 450 2011-05-03
6 ブドウ 果物 300 2013-07-16
7 にんじん 野菜 35 2013-03-17
8 コーラ 飲み物 98 2012-05-21
9 牛丼 食べ物 350 2011-02-25
10 キューイフルーツ 果物 160 2013-09-19
11 ほうれん草 野菜 145 2013-04-25
12 ビール 飲み物 220 2012-07-01
13 天丼 食べ物 460 2013-06-25
ERROR mysql> insert into sadachika_copy.sadachi_copyproducts select sada_id, sada_name, sada_category, sada_price, sada_releasedate from sadachika_shop.sadachi_products
-> on duplicate key update sada_copyid=values(sada_copyid), sada_name=values(sada_name), sada_category=values(sada_category), sada_price=sada_price+1, sada_releasedate=values(sada_releasedate);
↑新カラム ↑新カラム ↑新カラム ↑上書き重複テスト なぜか失敗
free70.69
////////////////////////////番外編//////////////////////////////////////
カラムに格納してある値をテーブルごとまるまるコピーしようとしたところ、同じテーブル名が存在した。
テーブルの名前を変える。
○データベースを表示させる
show databases;
○データベースに接続する
USE sadachika_shop;
○テーブルを表示させる。
SHOW TABLES;
○テーブルの中身の表示
mysql> select * from sadachika_shop.sadachi_goods;
○特定のデータベース内のテーブルを表示させる
SHOW TABLES FROM sadachika_copy;
Tables_in_sadachika_copy
sadachi_copyproducts*データを格納していない
sadachi_copysourcetable
sadachi_newcopyproductname
Empty set (0.33 set)
○○テーブルの名前を変更する ↓新しいテーブル名
mysql> alter table sadachika_copy.sadachi_copyproducts rename to sadachika_copy.sadachi_nodatatable;
Query OK, 0 rows affected (0.03 sec)
○テーブルの削除場合はこんな感じ
↓テーブル
mysql> drop table sadachika_shop.sadachi_drink15; *ビューをテーブルを削除する式では、削除できない
ERROR 1051 (42S02): Unknown table 'sadachika_shop.sadachi_drink15'
○特定のデータベース内のテーブルを表示させる
SHOW TABLES FROM sadachika_copy;
Tables_in_sadachika_copy
sadachi_copysourcetable
sadachi_newcopyproductname
sadachi_nodatatable *テーブル名が変更された
select * from sadachika_copy.sadachi_copyproducts; → select * from sadachika_copy.sadachi_nodatatable;
select * from sadachika_copy.sadachi_copysourcetable;
select * from sadachika_copy.sadachi_newcopyproductname;
* 参考にしたサイトは→ こちら です。
これは赤色の文字例です。
これは青色の文字例です。