「php05-2」の編集履歴（バックアップ）一覧はこちら

php05-2 - (2012/01/22 (日) 00:07:40) の最新版との変更点

追加された行は緑色になります。

削除された行は赤色になります。

[[トップページヘ>http://www34.atwiki.jp/ninja22/pages/21.html]]

＊(8)-2 練習問題
・下記にサンプルデータを記す。これを用いて集計等を行った。
#region(close,←クリックで開く)
【テーブル名：tb】
insert into tb (bang,uria,tuki) values('A103',101,4), 
('A102',54,5), 
('A104',181,4), 
('A101',184,4), 
('A103',17,5), 
('A101',300,5),
('A102',205,6), 
('A104',93,5),  
('A103',12,6), 
('A107',87,6);
#endregion

【テーブル名：tb】
|BGCOLOR(#e4ccff):&bold(){bang}|BGCOLOR(#e4ccff):&bold(){uria}|BGCOLOR(#e4ccff):&bold(){tuki}|
|A103|101|4|
|A102|54|5|
|A104|181|4|
|A101|184|4|
|A103|17|5|
|A101|300|5|
|A102|205|6|
|A104|93|5|
|A103|12|6|
|A107|87|6|

日付をテーブルに挿入
&bgcolor(#000080){&color(#ffffff){insert into hizuke (a) value("2008-5-3");}}



データを表示する
&bgcolor(#000080){&color(#ffffff){select bang, nama from tb1;}}

テーブルのコピー
&bgcolor(#000080){&color(#ffffff){create table tb1A select * from tb1;}}

カラムのデータ型を変更する（互換性によって変更できない場合もある）
&bgcolor(#000080){&color(#ffffff){alter table tb1c modify nama VARCHAR(100);}}

カラムの追加
&bgcolor(#000080){&color(#ffffff){alter table tb1c add umare DATETIME;}}
&bgcolor(#000080){&color(#ffffff){  insert into tb1c values('N111', '松田', 33, '1975-11-10');}}

カラム位置を変更する
先頭に追加する	&bgcolor(#000080){&color(#ffffff){alter table tb1d add umare DATETIME FIRST;}}
その他の位置に	&bgcolor(#000080){&color(#ffffff){alter table tb1d add umare DATETIME after bang;}}
順番の入れ替え	&bgcolor(#000080){&color(#ffffff){alter table tb1d modify umare DATETIME FIRST;}}

カラムの名前とデータ型の変更
&bgcolor(#000080){&color(#ffffff){alter table テーブル名 change 変更前カラム名 変更後カラム名 変更後データ型;}}
&bgcolor(#000080){&color(#ffffff){alter table tb1d change nama name VARCHAR(10);}}

カラムを削除
&bgcolor(#000080){&color(#ffffff){alter table tb1d drop umare;}}

主キーを設定する
&bgcolor(#000080){&color(#ffffff){create table テーブル名 (カラム名 データ型 PRIMARY KEY, カラム名2 データ型2);}}
&bgcolor(#000080){&color(#ffffff){create table itii (a INT PRIMARY KEY, b VARCHAR(10));}}

ユニークキー（重複しないキー）の設定
&bgcolor(#000080){&color(#ffffff){create table uniq(a INT UNIQUE, b VARCHAR(10));}}

自動的に連続番号が入力されるカラムにする
&bgcolor(#000080){&color(#ffffff){create table renzoku (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10));}}
&bgcolor(#000080){&color(#ffffff){insert into renzoku (b) value('子');}}
&bgcolor(#000080){&color(#ffffff){insert into renzoku (b) value('丑');}}
&bgcolor(#000080){&color(#ffffff){insert into renzoku (b) value('寅');}}

連続番号の初期値を設定する
既に入っている値+1から挿入される。
値の初期化　&bgcolor(#000080){&color(#ffffff){alter table テーブル名 AUTO_INCREMENT=0;}}

カラムの初期値を設定する
&bgcolor(#000080){&color(#ffffff){alter table tb1d modify name VARCHAR(10) DEFAULT '氏名未入力';}}

インデックスを設定する
&bgcolor(#000080){&color(#ffffff){create index インデックス名 ON テーブル名(カラム名);}}
&bgcolor(#000080){&color(#ffffff){create index my_ind ON tb1d(bang);}}
インデックスの表示	&bgcolor(#000080){&color(#ffffff){show index from tb1d;}}
　　　見やすさ重視	&bgcolor(#000080){&color(#ffffff){show index from tb1d \G}}

インデックスの削除
&bgcolor(#000080){&color(#ffffff){drop index インデックス名 ON テーブル名;}}

カラム構造とレコードをコピーしてテーブルを作成（auto_incrementはコピーされない）
&bgcolor(#000080){&color(#ffffff){create table 新規テーブル名 select * from 元となるテーブル名;}}
&bgcolor(#000080){&color(#ffffff){create table tb1h select * from tb1;}}

テーブルのカラム構造だけをコピー
&bgcolor(#000080){&color(#ffffff){create table tb1i like tb1;}}

他テーブルのレコードをコピーする
&bgcolor(#000080){&color(#ffffff){insert into tb1i select * from tb1;}}

特定のカラムを選択してコピーする
&bgcolor(#000080){&color(#ffffff){insert into tb1i (nama) select bang from tb1;}}

条件付きテーブル削除
&bgcolor(#000080){&color(#ffffff){drop table if exists tb1a;}}

データベース削除
&bgcolor(#000080){&color(#ffffff){drop database データベース名;}}

レコード（データ）の中身だけ削除
&bgcolor(#000080){&color(#ffffff){delete from テーブル名;}}

＊＊さらに
データ抽出
&bgcolor(#666666){select uria, bang from tb;}

カラム名を別名にして表示
&bgcolor(#666666){select bang as '社員番号', uria as '売上' from tb;}

文字列の結合
&bgcolor(#666666){select concat(bang, nama, 'さん') from tb1;}

文字列操作
右から2文字を取り出す：right関数
&bgcolor(#666666){select right(bang,2) from tb1;}

左から2文字を取り出す：left関数
&bgcolor(#666666){select left(bang,2) from tb1;}

～文字目から△文字取り出す：substring関数
&bgcolor(#666666){select substring(bang,～,△) from tb1;}

繰り返して表示：repeat関数
&bgcolor(#666666){select repeat('-',tosi) from tb1;}

逆さから表示：reverse関数
&bgcolor(#666666){select reverse(nama) from tb1;}

今の時刻を入れる：now関数
&bgcolor(#666666){create table ima (a INT AUTO_INCREMENT PRIMARY KEY ,b DATETIME);}
&bgcolor(#666666){insert into ima (b) values(NOW());}

条件を指定した選択
3件だけ表示
&bgcolor(#666666){select * from tb limit 3;}

uriaが100以上のレコードのみ表示
&bgcolor(#666666){select * from tb where uria>=100;}

uriaが50未満のレコードのみ表示
&bgcolor(#666666){select * from tb where uria<50;}

uriaが50以上100以下のレコードのみ表示
&bgcolor(#666666){select * from tb where uria between 50 and 100;}

uriaが50以下と100以上のレコードのみ表示
&bgcolor(#666666){select * from tb where uria not between 50 and 100;}

tukiが5か6のレコードのみ表示
&bgcolor(#666666){select * from tb where tuki in(5, 6);}

bangの値がA101のレコードのみ表示
&bgcolor(#666666){select * from tb where bang='A101';}

A101を含むレコードのみ表示
&bgcolor(#666666){select * from tb where like 'A101';}
bangの値の最後が1のレコードのみ表示
&bgcolor(#666666){select * from tb where bang like '%1';}
namaの値に川を含むレコードのみ表示
&bgcolor(#666666){select * from tb1 where nama like '%川%';}
namaの値に川を含まないレコードのみ表示
&bgcolor(#666666){select * from tb1 where nama not like '%川%';}

tosiに値が入っていないレコードのみ表示
&bgcolor(#666666){select * from tb1d where tosi is NULL;}
tosiに値が入っているレコードのみ表示
&bgcolor(#666666){select * from tb1d where tosi is not NULL;}

重複したデータを省いて表示
&bgcolor(#666666){select distinct bang from tb;}

複数の条件を指定して表示（or もあり）
uriaが50以上かつ100以下
&bgcolor(#666666){select * from tb where uria>=50 and uria<=100;}
※ANDとORが混ざっているときは、ANDが優先して処理される。括弧で区切るとよい。

条件付け（詳細）
&bgcolor(#666666){select bang, uria,}
&bgcolor(#666666){case}
&bgcolor(#666666){when uria>=100 then '多い'}
&bgcolor(#666666){when uria>=50 then '中くらい'}
&bgcolor(#666666){else '少ない'}
&bgcolor(#666666){end as '評価'}
&bgcolor(#666666){from tb;}


並べ替え
uriaカラムを少ない順（昇順）で並べ替え
&bgcolor(#666666){select * from tb order by uria ASC;}
uriaカラムを多い順（降順）で並べ替え
&bgcolor(#666666){select * from tb order by uria DESC;}

表示するレコードの範囲を決める
：uriaの多い順に4番目と5番目の2レコードを表示
&bgcolor(#666666){select * from tb order by uria DESC limit 2 offset 3;}


グループごとに表示
&bgcolor(#666666){select * from tb group by bang}

グループごとの数を数える
&bgcolor(#666666){select bang,count(*) as '件数' }
&bgcolor(#666666){from tb group by bang;}

グループごとの合計・平均を表示する
&bgcolor(#666666){select bang,sum(uria) as '合計', avg(uria) as '平均'}
&bgcolor(#666666){from tb group by bang;}

条件付きグループで表示
&bgcolor(#666666){select }
&bgcolor(#666666){bang, sum(uria)}
&bgcolor(#666666){from tb}
&bgcolor(#666666){group by bang}
&bgcolor(#666666){having sum(uria)>=200;}

抽出してグループ化する
&bgcolor(#666666){select bang, avg(uria)}
&bgcolor(#666666){from tb}
&bgcolor(#666666){where uria>=50}
&bgcolor(#666666){group by bang;}
↓降順
&bgcolor(#666666){select bang, avg(uria)}
&bgcolor(#666666){from tb}
&bgcolor(#666666){group by bang}
&bgcolor(#666666){order by avg(uria)}
&bgcolor(#666666){DESC;}
↓グループ化
&bgcolor(#666666){select bang, avg(uria)}
&bgcolor(#666666){from tb}
&bgcolor(#666666){where uria>=50}
&bgcolor(#666666){group by bang}
&bgcolor(#666666){order by avg(uria)}
&bgcolor(#666666){DESC;}

※  XAMPP 1.7.7 [PHP: 5.3.8] での設定項目です。





----
#center(){[[前のページ>php03]]　＜　｜　＞　[[次のページ>php05]]}
#center(){◆　◆　◆}
----

[[トップページヘ>http://www34.atwiki.jp/ninja22/pages/21.html]]

＊(8)-3 おさらい練習問題2
・下記にサンプルデータを記す。これを用いて集計等を行った。
#region(close,←クリックで開く)
 create table tb ( 
 id int(11) not null auto_increment, 
 bang varchar(50) not null, 
 uria int, 
 tuki int, 
 primary key (id) 
 ); 
 insert into tb (bang, uria, tuki) values ('A103', 101, 4); 
 insert into tb (bang, uria, tuki) values ('A102', 54, 5); 
 insert into tb (bang, uria, tuki) values ('A104', 181, 4); 
 insert into tb (bang, uria, tuki) values ('A101', 184, 4); 
 insert into tb (bang, uria, tuki) values ('A103', 17, 5); 
 insert into tb (bang, uria, tuki) values ('A101', 300, 5); 
 insert into tb (bang, uria, tuki) values ('A102', 205, 6); 
 insert into tb (bang, uria, tuki) values ('A104', 93, 5); 
 insert into tb (bang, uria, tuki) values ('A103', 12, 6); 
 insert into tb (bang, uria, tuki) values ('A107', 87, 6); 

 create table tb1 ( 
 id int(11) not null auto_increment, 
 bang varchar(50) not null, 
 nama varchar(50) not null, 
 tosi int, 
 primary key (id) 
 ); 
 insert into tb1 (bang, nama, tosi) values ('A101', '佐藤', 40); 
 insert into tb1 (bang, nama, tosi) values ('A102', '高橋', 28); 
 insert into tb1 (bang, nama, tosi) values ('A103', '中川', 20); 
 insert into tb1 (bang, nama, tosi) values ('A104', '渡辺', 23); 
 insert into tb1 (bang, nama, tosi) values ('A105', '西沢', 35); 

 create table tb3 ( 
 id int(11) not null auto_increment, 
 bang varchar(50) not null, 
 ken varchar(50) not null, 
 primary key (id) 
 ); 
 insert into tb3 (bang, ken) values ('A101', '東京都'); 
 insert into tb3 (bang, ken) values ('A102', '埼玉県'); 
 insert into tb3 (bang, ken) values ('A103', '神奈川県'); 
 insert into tb3 (bang, ken) values ('A104', '北海道'); 
 insert into tb3 (bang, ken) values ('A105', '静岡県'); 
#endregion

【tb】
|BGCOLOR(#e4ccff):&bold(){id}|BGCOLOR(#e4ccff):&bold(){bang}|BGCOLOR(#e4ccff):&bold(){uria}|BGCOLOR(#e4ccff):&bold(){tuki}|
|1|A103|101|4|
|2|A102|54|5|
|3|A104|181|4|
|4|A101|184|4|
|5|A103|17|5|
|6|A101|300|5|
|7|A102|205|6|
|8|A104|93|5|
|9|A103|12|6|
|10|A107|87|6|

【tb1】
|BGCOLOR(#e4ccff):&bold(){id}|BGCOLOR(#e4ccff):&bold(){bang}|BGCOLOR(#e4ccff):&bold(){nama}|BGCOLOR(#e4ccff):&bold(){tosi}|
|1|A101|佐藤|40|
|2|A102|高橋|28|
|3|A103|中川|20|
|4|A104|渡辺|23|
|5|A105|西沢|35|

【tb3】
|BGCOLOR(#e4ccff):&bold(){id}|BGCOLOR(#e4ccff):&bold(){bang}|BGCOLOR(#e4ccff):&bold(){ken}|
|1|A101|東京都|
|2|A102|埼玉県|
|3|A103|神奈川県|
|4|A104|北海道|
|5|A105|静岡県|


・&bold(){複数テーブルのレコードを合わせて表示する}
 select カラム名
 from テーブル名
 union
 select カラム名2
 from テーブル名2;
・表示例1
 select * from tb1 union select * from tb2;
・表示例2（わかりやすく括弧でくくった）
 (select * from tb1) union (select * from tb2);
・表示例3（たくさん）
 (select * from tb1) union (select * from tb2) union (select * from tb3);

・&bold(){条件をつけて複数テーブルのレコードを合わせて表示する}（重複省略処理含む）
 (select bang from tb where uria>=200) 
 union (select bang from tb1 where tosi>=35);

・&bold(){複数の抽出結果を合わせて表示}（重複あり）
 (select bang from tb where uria>=200) 
 union all (select bang from tb1 where tosi>=35);

・&bold(){複数のテーブルを結合し表示}（内部結合）
・・複数のテーブルをキーで結合し表示
 select カラム名
 from テーブル1
 join 結合するテーブル2
 on テーブル1のカラム=テーブル2のカラム;

・&bold(){（tbとtb1のカラム「bang」が一致するレコードを結合し表示）}
・・※join = innner join
 select *
 from tb
 join tb1 
 on tb.bang=tb1.bang
↓
・&bold(){usingを使って見やすくする}:using(キーとなるカラム名/共通)
 select *
 from tb
 join tb1 
 using(bang);

・&bold(){合わせて表示例１}
 select tb.bang, tb1.nama, 
 tb.uria
 from tb
 join tb1
 using (bang);

・&bold(){合わせて表示例２}
 select tb.bang as '社員番号', tb1.nama as '氏名', tb.uria as '売上' 
 from tb
 join tb1
 using(bang)
 where tb.uria>=100;
結果
|社員番号|氏名|売上|
|A103|中川|101|
|A104|渡辺|181|
|A101|佐藤|184|
|A101|佐藤|300|
|A102|高橋|205|

・&bold(){合わせて表示（たくさん）}
 select
 tb.bang, tb.uria, tb1.nama, tb3.ken
 from tb
 join tb1
 using(bang)
 join tb3
 using(bang);
結果
|bang|uria|nama|ken|
|A103|101|中川|神奈川県|
|A102|54|高橋|埼玉県|
|A104|181|渡辺|北海道|
|A101|184|佐藤|東京都|
|A103|17|中川|神奈川県|
|A101|300|佐藤|東京都|
|A102|205|高橋|埼玉県|
|A104|93|渡辺|北海道|
|A103|12|中川|神奈川県|

・&bold(){複数のテーブルを結合し表示}（外部結合）
・(1) 左外部結合：left join
・・tb1(右)のbang項目をtb(左)のbang項目に結合し表示
・・※right join = right outer join
 select tb.bang, tb1.nama 
 from tb 
 left join tb1
 using(bang);
結果
|bang|nama|
|A103|中川|
|A102|高橋|
|A104|渡辺|
|A101|佐藤|
|A103|中川|
|A101|佐藤|
|A102|高橋|
|A104|渡辺|
|A103|中川|
|A107|NULL|

・(2) 右外部結合：right join
・・tb(左)のbang項目をtb1(右)のbang項目に結合し表示
・・※left join = left outer join
 select tb.bang, tb1.nama 
 from tb 
 right join tb1
 using(bang);
結果
|bang|nama|
|A101|佐藤|
|A101|佐藤|
|A102|高橋|
|A102|高橋|
|A103|中川|
|A103|中川|
|A103|中川|
|A104|渡辺|
|A104|渡辺|
|NULL|西沢|


※  XAMPP 1.7.7 [PHP: 5.3.8] での設定項目です。





----
#center(){[[前のページ>php03]]　＜　｜　＞　[[次のページ>php05]]}
#center(){◆　◆　◆}
----