Category: DB

MySQL data import – CUI

サーバ移転時など、データ移行を行う必要がある。データ量が多すぎてphpMyAdminがタイムアウトになり、ブラウザからインポートできない場合がある。

そういった場合、コマンドラインからインポートすることで実現する。

 

①ログイン
[Linux]
 # mysql -u root -p
Enter password:
 
[Mac]
# /Applications/MAMP/Library/bin/mysql -u root -p
Enter password:
 
②データベース作成

mysql> create database {database_name};

 
③DB指定
mysql> use dbname
Database changed
 
④データファイルを指定してインポート
mysql> source /Users/{myname}/Downloads/data.sql
 
ちなみに
⑤ユーザ作成
mysql> grant all privileges on {database_name}.* to {user_name}@localhost identified by '{password}';

 

[MAMPの場合]
$ cd /Applications/MAMP/Library/bin/
$ ./mysql -u root -p
mysql> SHOW DATABASES;

SQL Use

 

外部結合

外部結合はそれぞれのテーブルの指定した列の値が一致するデータに加えてどちらかのテーブルにしか存在しないデータについても取得します。基本となる構文は次の2つが用意されています。
SELECT table_name.col_name, … lFROM tbl_name1   
  LEFT JOIN tbl_name2  ON tbl_name1.col_name1 = tbl_name2.col_name2
LEFT JOINではFROMの後に書かれたテーブルのデータだけを取得します。
SELECT table_name.col_name, … lFROM tbl_name1   
  RIGHT JOIN tbl_name2  ON tbl_name1.col_name1 = tbl_name2.col_name2
RIGHT JOINではJOINの後に書かれたテーブルのデータだけを取得します。
基本的に内部結合の場合と同じですが、外部結合では結合の対象となっているカラムの値が一致しているデータに加えて、カラムの値がどちらかのテーブルにしかなかった場合でもデータとして取得します。この時、どちらのテーブルのデータを取得するかで2つの構文が用意されています。
 
3テーブルの結合
SELECT table_name.col_name, … lFROM (tbl_name1   
  LEFT JOIN tbl_name2  ON tbl_name1.col_name1 = tbl_name2.col_name2) LEFT JOIN tbl_name3 ON tbl_name1.col_name1 = tbl_name3.col_name3

[Sample]

SELECT * FROM (material LEFT JOIN society ON material.society_id = society.society_id) LEFT JOIN material_option ON material.material_id = material_option.material_id WHERE app_number LIKE '%"abc"%' OR society_name LIKE '%"def"%' ORDER BY material_option.modified_time DESC

なお結合するカラムの名前が同じ場合にはONの代わりにUSINGを使用して次のように記述することもできます。
SELECT table_name.col_name, … lFROM tbl_name1   
  LEFT JOIN tbl_name2  USING ( col_name, col_name2, …)
SELECT table_name.col_name, … lFROM tbl_name1   
  RIGHT JOIN tbl_name2  USING ( col_name, col_name2, …)
「USING (col_name)」というのは「ON tbl_name1.col_name = tbl_name2.col_name」と同じことです。
 

内部結合

SELECT table_name.col_name, … lFROM tbl_name1   
  INNER JOIN tbl_name2  ON tbl_name1.col_name1 = tbl_name2.col_name2
 
 
 
 
 
 
 
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       

SQL Basic

—————————————- SQL文 –————————

データ検索      
コマンド名 説明    
SELECT データの検索    
データ操作文(DML)      
コマンド名 説明    
INSERT 行の新規挿入    
UPDATE 値の更新    
DELETE 行の削除    
データ定義文(DDL)      
コマンド名 説明    
CREATE オブジェクトの作成    
ALTER オブジェクトの変更    
DROP オブジェクトの削除    
RENAME オブジェクト名の変更    
GRANT 権限付与    
REVOKE 権限削除    
AUDIT 監査    
TRUNCATE 表の切り捨て    
トランザクション制御(DCL)      
コマンド名 説明    
COMMIT 更新の確定    
ROLLBACK 更新の取り消し    
SAVEPOINT セーブポイントの設定    
算術演算      
NULL値が含まれていると計算結果はNULLです。    

———————– SELECT文 ————————

SELECT  
■野球選手表■      
選手名 背番号 球団名 ポジション
松井 55 ジャイアンツ 外野手
古田 27 スワローズ 捕手
松坂 18 ライオンズ 投手
桑田 18 ジャイアンツ 投手
中村 5 バッファローズ 内野手
       
SELECT * FROM 野球選手表;
*は全列という意味で、特定の列のみ表示したい場合は列名を書けばよい。
選手名 背番号 球団名 ポジション
松井 55 ジャイアンツ 外野手
古田 27 スワローズ 捕手
松坂 18 ライオンズ 投手
桑田 18 ジャイアンツ 投手
中村 5 バッファローズ 内野手
■結果■すべてのレコードが検索される。   
       
SELECT * FROM 野球選手表 WHERE 選手名 = ’松井’; 
選手名 背番号 球団名 ポジション
松井 55 ジャイアンツ 外野手
■結果■選手名が「松井」だけ検索される。 
       
SELECT 選手名 FROM 野球選手表 WHERE 背番号 = 18; 
選手名      
松坂      
桑田      
■結果■背番号が18の選手名だけが検索される。 
       
SELECT DISTINCT 背番号 FROM 野球選手表 
背番号      
55      
27      
18      
5      
■結果■背番号18が2つ重複してるので1つのみ表示される。 
       
SELECT 選手名 FROM 野球選手表 WHERE NOT  背番号 = 18; 
選手名      
松井      
古田      
中村      
■結果■背番号が18以外の選手名だけが検索されます。 
       
SELECT 選手名 FROM 野球選手表 WHERE 背番号 = 18 AND  球団名 = ’ジャイアンツ’;
選手名      
桑田      
■結果■背番号が18で球団名がジャイアンツの選手名(桑田のみ)が検索されます。 
       
SELECT * FROM 野球選手表 WHERE 球団名 = ’スワローズ’ OR  ポジション = ’内野手’; 
選手名 背番号 球団名 ポジション
古田 27 スワローズ 捕手
中村 5 バッファローズ 内野手
■結果■球団名がスワローズとポジションが内野手のレコードが検索されます。 
※優先順位は「1:NOT→2:AND→3:OR」です。 
       
SELECT * FROM 野球選手表 WHERE 背番号 BETWEEN  10 AND 30; 
選手名 背番号 球団名 ポジション
古田 27 スワローズ 捕手
松坂 18 ライオンズ 投手
桑田 18 ジャイアンツ 投手
■結果■背番号が10~30の選手のレコードが検索されます。 
※「BETWEEN a AND b」は「a以上b以下」です。 
       
SELECT * FROM 野球選手表 WHERE 背番号 NOT BETWEEN  10 AND 30; 
選手名 背番号 球団名 ポジション
中村 5 バッファローズ 内野手
松井 55 ジャイアンツ 外野手
■結果■背番号が10~30以外の選手のレコードが検索されます。 
       
SELECT * FROM 野球選手表 WHERE ポジション IN (’投手’,’捕手’); 
選手名 背番号 球団名 ポジション
古田 27 スワローズ 捕手
松坂 18 ライオンズ 投手
桑田 18 ジャイアンツ 投手
■結果■ポジションが投手か捕手のレコードが検索されます。 
       
SELECT * FROM 野球選手表 WHERE ポジション NOT IN (’投手’,’捕手’); 
選手名 背番号 球団名 ポジション
中村 5 バッファローズ 内野手
松井 55 ジャイアンツ 外野手
■結果■ポジションが投手か捕手以外のレコードが検索されます。 
       
SELECT * FROM 野球選手表 WHERE 選手名 LIKE  ’%田’; 
選手名 背番号 球団名 ポジション
古田 27 スワローズ 捕手
桑田 18 ジャイアンツ 投手
■結果■選手名がX田のレコードが検索されます。
       
SELECT * FROM 野球選手表 WHERE 選手名 IS  NULL; 
選手名 背番号 球団名 ポジション
  99 XXX YYY
  999 XXXX YYYY
■結果■選手名がNULLのレコードが検索されます。(IS NOT NULLにするとNULLではないレコード検索)
—————————— ソート(ORDER BY)について ———————————
ORDER BY
■野球選手表■      
選手名 背番号 球団名 ポジション
松井 55 ジャイアンツ 外野手
古田 27 スワローズ 捕手
西口 13 ライオンズ 投手
桑田 18 ジャイアンツ 投手
中村 5 バッファローズ 内野手
高橋 24 ジャイアンツ 外野手
上原 19 ジャイアンツ 投手
       
SELECT * FROM 野球選手表 WHERE ポジション = ’投手’  ORDER BY 背番号 [ASC] ; 
選手名 背番号 球団名 ポジション
西口 13 ライオンズ 投手
桑田 18 ジャイアンツ 投手
上原 19 ジャイアンツ 投手
■結果■ポジションが投手のレコードが昇順に並びかえられました。 
※最後の「ASC」はデフォルトですので、省略しても同様の結果になる。 
       
SELECT * FROM 野球選手表 WHERE ポジション = ’投手’ ORDER BY 背番号 DESC; 
選手名 背番号 球団名 ポジション
上原 19 ジャイアンツ 投手
桑田 18 ジャイアンツ 投手
西口 13 ライオンズ 投手
■結果■ポジションが投手のレコードが降順に並びかえられました。 
※複数の列を指定する場合は、ORDER BY 背番号,球団名 DESC; のようにコンマで区切る。
(背番号・球団名の順にソートする。)
—————————————– 関数 —————————————-
SUBSTR      
SELECT SUBSTR(’ABCDE 12345’7,2) FROM テーブル名; 
■結果■12が取り出されます。
※空白は1桁としてカウントされます。 
       
INITCAP      
SELECT INITCAP(’hanshin tigers’) FROM テーブル名; 
■結果■Hanshin Tigersと表示されます。
       
UPPER      
SELECT UPPER(’hanshin tigers’) FROM テーブル名; 
■結果■HANSHIN TIGERSと表示されます。 
       
NLV
NULL値をNULL以外に変換します。     
       
数値関数(アルファベット順)     
関数名 説明    
CEIL(a) a以上の最小数を返す    
FLOOR(a) a以上の最大数を返す    
MOD(a,b) a÷bの余り値を返す    
POWER(a,b) aのb乗した値を返す    
SQRT(a) aの平方根を返す    
ROUND(a,b) aを小数点b桁に四捨五入した値を返す    
TRUNC(a,b) aを小数点b桁に切り捨てた値を返す    
—————————————- 結合 —————————————– 
等価結合      
 等価条件に基づいて2つ以上の表から行を返します。 
非等価結合      
 イコール以外の演算子を用いて結合します。 
外部結合      
 (+)を用いて結合します。 
同一表結合      
 表をその表自身と結合させます。 
       
■選手表■      
背番号 選手名 球団名 ポジション
18 桑田 ジャイアンツ 投手
19 上原 ジャイアンツ 投手
24 高橋 ジャイアンツ 外野手
55 松井 ジャイアンツ 外野手
       
■出身校表■      
背番号 出身校    
18 PL学園    
19 大阪体育大    
24 慶応大    
55 星陵高    
       
SELECT 選手表.背番号,選手表.選手名,出身校表.出身校
   FROM 選手表,出身校表 WHERE 選手表.背番号 = 出身校表.背番号; 
背番号 選手名 出身校  
18 桑田 PL学園  
19 上原 大阪体育大  
24 高橋 慶応大  
55 松井 星陵高  
■結果■背番号によって2つの表が関連付けられました。 
————————————– グルーピング ————————————-
グループ関数      
関数名 意味    
AVG 平均値を返す    
COUNT 行数を返す    
MAX 最大値を返す    
MIN 最小値を返す    
SUM 合計値を返す    
       
■選手表■      
球団名 選手名 ポジション 身長
ジャイアンツ 桑田 投手 174
ジャイアンツ 上原 投手 185
ジャイアンツ 高橋 外野手 180
ジャイアンツ 松井 外野手 186
タイガース 井川 投手 185
タイガース 福原 投手 180
タイガース 桧山 外野手 177
タイガース 赤星 外野手 170
       
SELECT 球団名,MAX(身長)FROM 選手表 GROUP BY 球団名; 
球団名 MAX(身長)    
ジャイアンツ 186    
タイガース 185    
■結果■球団名、それぞれの最高身長が検索されます。 
       
SELECT 球団名,ポジション,MAX(身長)FROM 選手表 GROUP BY 球団名,ポジション; 
球団名 ポジション MAX(身長)  
ジャイアンツ 投手 185  
ジャイアンツ 外野手 186  
タイガース 投手 185  
タイガース 外野手 177  
■結果■球団名ごとにポジションの最高身長が検索されます。 
       
SELECT 球団名,ポジション,MAX(身長)FROM 選手表
  HAVING MAX(身長) >= 180 GROUP BY 球団名,ポジション; 
球団名 ポジション MAX(身長)  
ジャイアンツ 投手 185  
ジャイアンツ 外野手 186  
タイガース 投手 185  
■結果■球団名ごとにポジションの最高身長が検索されます。 HAVINGはGROUP BYの前後どちらでもOK 。
※HAVINGはGROUP BYの前後どちらでもOK。
————————————— 副問合せ —————————————
SELECT文の中にあるSELECT文
単一行副問い合わせ     
複数行副問い合わせ     
複数列副問い合わせ     
※ORDER BYは副問い合わせを使用できない     
       
DISTINCT      
以下の記述はDISTINCTと同じ。    
SELECT 列名 FROM 表名1 WHERE 列名 IN (SELECT 列名 FROM 表名2); 
————————————- SQL*Plus —————————————-
SQL文を認識し、実行するためのツール    
●login.sql:SQL*Plus起動時に設定するコマンドファイル 
●COLUMN:列の書式設定や表示幅を定義する 
●DEFINEまたはACCEPTにて変数の事前定義が行なえる 
●UNDEFINE:1度格納したユーザ変数を削除する 
●PL/SQLは「ブロック単位」 
————————————— データ更新 ————————————-
■野球選手表■      
選手名 背番号 球団名 ポジション
松井 55 ジャイアンツ 外野手
西口 13 ライオンズ 投手
中村 5 バッファローズ 内野手
斎藤 11 ジャイアンツ 投手
石井 16 スワローズ 投手
       
①INSERT INTO 野球選手表 (選手名,背番号,球団名,ポジション)
    VALUES(’寺原’,20,’ホークス’,’投手’);
選手名 背番号 球団名 ポジション
松井 55 ジャイアンツ 外野手
西口 13 ライオンズ 投手
中村 5 バッファローズ 内野手
斎藤 11 ジャイアンツ 投手
石井 16 スワローズ 投手
寺原 20 ホークス 投手
■結果■今年入団した寺原投手のレコードが追加されました。 
       
②UPDATE 野球選手表 SET 球団名 = ’ドジャース’ WHERE 選手名 = ’石井’; 
選手名 背番号 球団名 ポジション
松井 55 ジャイアンツ 外野手
西口 13 ライオンズ 投手
中村 5 バッファローズ 内野手
斎藤 11 ジャイアンツ 投手
石井 16 ドジャース 投手
寺原 20 ホークス 投手
■結果■今年FAした石井投手の球団名がドジャースに変更されました。 
       
③DELETE FROM 野球選手表 WHERE 選手名 = ’斎藤’; 
選手名 背番号 球団名 ポジション
松井 55 ジャイアンツ 外野手
西口 13 ライオンズ 投手
中村 5 バッファローズ 内野手
石井 16 ドジャース 投手
寺原 20 ホークス 投手
■結果■今年引退し