MySQL011 データの編集(外部結合:OUTER JOIN, LEFT/RIGHT)
chap10 つづき
■複数のテーブルの"すべてのレコード"を表示する(外部結合)
ーキーが一致しているか否か?が内部・外部結合の違い
ー全データを表示させる際、左右どちらかを主にする(左外部/右外部結合)
・LEFT JOIN / left outer join
select tb10.bang,tb11.nama from tb10
left join tb11 using(bang);
select tb10.bang,tb11.nama from tb10
left outer join tb11 using(bang);
・RIGHT JOIN
select tb10.bang,tb11.nama from tb10
right join tb11 using(bang);
・FULL OUTER JOIN
ここでは扱わない。
■自己結合
select * from tb11 join tb11; //Error.
select * from tb11 as a join tb11 as b; //aliasを付与で回避
順位付け
select a.nama, a.tosi, count(*)
from tb11 as a join tb11 as b
where a.tosi<=b.tosi
group by a.bang;
■参考
本:基礎からのMySQL
MySQL010 データの編集(内部結合 INNER JOIN)
chap10 つづき
■複数のテーブルを結合して表示する(内部結合:INNER JOIN)
ー複数のテーブルを何らかのキーで結びつけて処理するのが「結合」
ーunionは'レコード'を結合
ーjoin テーブル名 on キーとなるカラム
ーレコードが一致しなくても全て出す場合は外部結合(OUTER JOIN)
select * from tb10 join tb11 on tb10.bang = tb11.bang;
select * from tb10 inner join tb11 on tb10.bang = tb11.bang;
・カラムを選んで表示
select tb10.bang,tb11.nama,tb10.uria from tb10
join tb11 on tb10.bang=tb11.bang;
・テーブル名にエイリアスを使う
select x.bang,y.nama,x.uria from tb10 as x
join tb11 as y on x.bang=y.bang;
・USINGをつかってON部分を見やすくする
select tb10.bang,tb11.nama,tb10.uria from tb10
join tb11 using(bang);
・結合テーブルからwhereで抽出
select tb10.bang,tb11.nama,tb10.uria from tb10
join tb11 using(bang)
where tb10.uria>=100;
・複数テーブルを内部結合
select tb10.bang,tb10.uria,tb13.ken from tb10
join tb11 using(bang)
join tb13 using(bang);
■参考
本:基礎からのMySQL
MySQL009 データの編集(UNION, UNION ALL)
chap10
■複数テーブルのレコードを合わせて表示する
<事前準備:サンプルテーブル 4つ>
create table tb10 select * from chap8;
create table tb11 (bang varchar(10),nama varchar(10),tosi int);
insert into tb11 values('A101','佐藤',40);
insert into tb11 values('A102','高橋',28);
insert into tb11 values('A103','中川',20);
insert into tb11 values('A104','渡辺',23);
insert into tb11 values('A105','西沢',35);
create table tb12 (bang varchar(10),nama varchar(10),tosi int);
insert into tb12 values('A106','中村',26);
insert into tb12 values('A107','田中',24);
insert into tb12 values('A108','鈴木',23);
insert into tb12 values('A109','村井',25);
insert into tb12 values('A110','吉田',27);
create table tb13 (bang varchar(10),ken varchar(10));
insert into tb13 values('A101','東京都');
insert into tb13 values('A102','埼玉県');
insert into tb13 values('A103','神奈川県');
insert into tb13 values('A104','北海道');
insert into tb13 values('A105','静岡県');
・複数の抽出結果を合わせて表示(UNION)
■UNION
・複数テーブルから抽出データを収集する。
※基本的にデータ型は一致する必要はあるが、集められることもある。
select * from tb11 union select * from tb12;
(select * from tb11) union (select * from tb12);
・3つ以上のテーブルから抽出データを収集する。
select * from tb11 where bang='A102'
union select * from tb11 where bang='A103'
union select * from tb11 where bang='A104'
union select * from tb11 where bang='A105';
→select * from tb11 where bang not in ('A101');
→select * from tb11 where bang in ('A102','A103','A104','A105');
・条件付きで複数の抽出結果を表示する(重複する)
select bang from tb10 where uria>=200
union
select bang from tb11 where tosi>=35;
※重複データは省かれる
・条件付きで複数の抽出結果を表示する(重複しない) union all ★
select bang from tb10 where uria>=200
union all
select bang from tb11 where tosi>=35;
■参考
本:基礎からのMySQL
MySQL008 データの編集(update, delete)
chap9
■データの修正(UPDATE)
・カラムのデータを全て修正する
[構文]
update テーブル set カラム = 値;
alter table chap9 add bikou varchar(100);
→update chap9 set bikou='備考無し';
※[--sefe-updates]オプション
MySQLモニタ起動時にsafeオプションをつけると、
一意のカラムに対するwhere条件のないupdateやdeleteが実行できなくなる。
・条件一致したレコードのカラムだけ修正する
update chap9 set bikou='優秀' where uria>=100;
・下位3件のみ修正
update chap9 set bikou='ガンガレ!'
order by uria
limit 3;
→select * from chap9 order by uria;
→alter table chap9 drop bikou;
■条件に一致したレコードのコピー
・特定レコードのコピー
create table tb_A101
select * from chap9
where bang='A101'; //where like bang 'A101';
・順番に並べてコピー
create table tb2to5 select * from chap9
order by uria desc limit 4 offset 1;
■レコードの削除
delete from tb9 where age like 14;
delete from emp where bang like 1 limit 1;
delete from chap9 order by uria desc limit 3;
・今より5年前 //now() - interval 5 year
create table new_zaiko
select * from zaiko
where c > now() - interval 5 year;
MySQL007 様々な条件で抽出(2)
chap8 続き
■複数条件を指定した選択(AND/OR)
・AND
select * from chap8 where uria>=50 and uria<= 100;
select * from chap8 where bang like '%1' and tuki=4;
・OR
select * from chap8 where uria<50 or uria>200;
・複合
select * from chap8 where bang like '%1' and tuki=4 or uria>=200;
select * from chap8 where (uria>200 or bang like '%1') and tuki=4;
※ANDとORが混ざっている時は、ANDが優先される ★注意
AorB and Cの場合は、(AorB) and Cと()でくくる。
■CASE WHEN
条件によって入力する値を変化させる場合
select bang,uria,
case
when uria>200 then '多い!!!'
when uria>=50 then '普通'
else '少ない...'
end as '評価'
from chap8;
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
SQLの実行順序
FROM → WHERE → GROUPBY → HAVING → SELECT → ORDERBY
a.抽出してからのグループ化(where->groupBy)
select bang,avg(uria) from chap8 where uria>=50 group by bang;
b.グループ化してから並べる(groupBy->having)
select bang,avg(uria) from chap8 group by bang order by avg(uria) desc;
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
■並び替え(ORDER BY asc/desc, limit/offset, GROUP BY)
・昇順
select * from chap8 order by uria; //デフォルトはasc;
select * from chap8 order by uria asc;
・降順
select * from chap8 order by uria desc;
select * from chap8 order by uria desc limit 5;
・範囲指定(OFFSET)
select * from chap8 order by uria desc limit 2 offset 3;
■グループごとに表示
select * from chap8 group by bang;
select count(*) from chap8 group by bang;
select
bang,count(*) as '件数'
from chap8
group by bang;
・グループごとの合計・平均を表示
select
bang,sum(uria) as 売上合計
from chap8
group by bang;
select
bang,avg(uria)
from chap8
group by bang;
・条件付きグループ表示(having)
・having:グループ化した値の抽出条件を設定する。
※抽出してからのグループ化ではない。
[構文]
select 集計したカラム from テーブル名 group by グループ化するカラム having 条件;
select bang,sum(uria) as '売上合計 200万以上' from chap8 group by bang
having sum(uria)>200;
・練習
select bang,avg(uria)
from chap8
where uria>50
group by bang
order by avg(uria) desc;
1.
select concat('合計は',sum(uria),'万円です') as '売上' from chap8;
2.
select bang,avg(uria),tuki
from chap8
group by bang
having avg(uria)>=120
order by avg(uria) desc;
■参考
本:基礎からのMySQL
http://dev.classmethod.jp/server-side/db/difference-where-and-having/
MySQL006 様々な条件で抽出
MySQL006 様々な条件で抽出
chap8
■データ準備
create table chap8 (bang varchar(10), uria int, tuki int);
insert into chap8 values('A103',101,4);
insert into chap8 values('A102',54,5);
insert into chap8 values('A104',181,4);
insert into chap8 values('A101',184,4);
insert into chap8 values('A103',17,5);
insert into chap8 values('A101',300,5);
insert into chap8 values('A102',205,6);
insert into chap8 values('A104',93,5);
insert into chap8 values('A103',12,6);
insert into chap8 values('A107',87,6);
create table chap8_bk select * from chap8;
■データ抽出
・カラムを入れ替えて表示
select uria,bang from chap8;
・エイリアスの利用
select bang as 社員番号, uria as 売上 from chap8;
・カラム値を計算して表示
select uria*10000 as '売上(円)' from chap8;
select uria/tuki from chap8;
・関数の利用
select avg(uria) from chap8; //平均値
select sum(uria) from chap8; //合計値
select count(uria) from chap8; //個数
select pi(); //円周率
select version();
select database();
select user();
select charset('この文字');
select concat(bang,uria,'さん') from chap8;
→concatenate(こんきゃっとねいと)
文字列操作
select right(bang,2) from chap8;
select left(bang,2) from chap8;
select substring(bang,2,3) from chap8; //n番目からn個表示
select reverse(bang) from chap8; //逆さ文字
select repeat('.',tuki) from chap8;
日付・時刻
create table ima (a int auto_increment primary key, b datetime);
→insert into ima(b) values(now());
■条件を指定した選択
数値
・レコード数を限定(LIMIT)
select bang from chap8 limit 3;
・where
select * from chap8 where uria>=100;
・in, between, not
select * from chap8 where tuki<>4; //4以外
select * from chap8 where uria between 50 and 100;
select * from chap8 where uria not between 50 and 200;
select * from chap8 where tuki in (5,6);
select * from chap8 where
文字列
select * from chap8 where bang='A101';
select * from chap8 where bang like 'A101';
select * from chap8 where bang like '長_県'; //長野、長崎
%:任意の文字列が該当
_:任意の1文字が該当
・NULL/ not NULL
insert into chap8(uria) values(000);
select * from chap8 where tuki is null;
select * from chap8 where tuki is not null;
・重複データを1つ限定で表示(DISTINCT)
select distinct bang from chap8;
■参考
本:基礎からのMySQL
MySQL005 コマンド5 (コピー)
chap7
・カラム構造+データのコピー
ーselectした結果から構造・データをコピーし新規テーブル作成する。
ーauto_incrementなど一部属性はコピーされない ★注意
create table tbcp1 select * from tb1;
・カラム構造のコピー
ー構造のみコピー
ーデータはコピーしない
create table tbcp2 like tb1;
・データのコピー
insert into tbcp2 select * from tb1;
・特定のカラムを選択してコピー
insert into tbcp3(num) select num from tb1;
※カラム名,データ型が一致していることが条件
~~~
・データベースの削除
drop database db1;
・テーブル削除
drop table tbcp3;
drop table if exists tbcp3;
・レコードの全削除
delete from tbcp3;
・warningを確認
show warnings ¥g;
~~~
・直接cmdやターミナルから実行する
※未検証
mysqladmin -u root -proot create db3;
mysqladmin -u root -proot drop db3;
mysql db3 -u root -proot -e 'select * from tb1';
■参考
本:基礎からのMySQL