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;
・重複データを１つ限定で表示（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
MySQL004 コマンド4 (index)
chap6 続き
・最初からデータが入ってるカラム
※データ挿入時のデフォルト値を指定
create table tb3 (gender varchar(10) default '-', name varchar(20) default '名無しさん');
→insert into tb3(gender) values('男');
→insert into tb3(name) values('神崎');
→drop table tb3;
・インデックスの種類
ーB-Tree (Branched Tree)
ービットマップ
ー関数 etc
※create index文でオプションを指定しない場合はB-Treeとなる。
※インデックスはテーブルとは別にデータを保持しているため、
データ追加時はデーブルとインデックス両方にデータが作成され重くなる。
・インデックスの設定
※主キーを設定した場合には、自動でインデックスが作成されている。
create index indexName1 on tb3(name);
・インデックスの確認
show index from tb3;
show index from tb3 ¥G;
※¥Gは、lsの-laオプションみたいなもの
・インデックスの削除
drop index indexName1 on tb3;
・練習
create table tb4 (bang int auto_increment primary key, niti datetime) auto_increment=100;
→insert into tb4(niti) values(now());
■参考
本：基礎からのMySQL
https://www.dbonline.jp/sqlite/index/index1.html
MySQL003 コマンド3 (primaryKey, uniqueKey, 自動採番)
mysql コマンド3 主キー(Primary Key)
■chap6 continue
○主キー(primary key)
ーユニークであること
ー重複がない
ーNULLにできない
・主キーを設定してテーブル作成
create table tb2 (pk int primary key, testClm varchar(10));
→insert into tb2 values(1,'テスト');
○一意キー(unique)
ー重複がない
create table tbUnique(a int unique, b int(10));
○自動採番されるカラム
自動採番カラム作成の条件
1.データ型は整数型
2.auto_incrementを付与
3.主キー[一意キー]でユニークにする
・自動採番テーブル作成
create table sequential (a int auto_increment primary key, b varchar(10));
insert into sequential(b) values('いちご');
insert into sequential(b) values('あおい');
insert into sequential(b) values('らん');
select * from sequential;
・自動採番の初期値を設定
alter table sequential auto_increment=0;
■参考
本：基礎からのMySQL
MySQL002 コマンド2 (alter table)
mysql コマンド2
alter table tableName [modify/add/change/drop] ~
■chap5：データ型とデータ入力
データ型
1.数値
int, tinyint, smallint, mediumint,bigint,float,double,decimal
暫定：整数はint、小数点以下含むものはdoubleを利用
2.文字列
char,varchar,text,longtext
暫定：255文字以下はvarchar、それ以上はtextを利用
3.日付・時刻型
date time, date, year, time
■chap6：カラム構造の変更
1.カラムの定義変更 modify ※既存データの消滅に注意
○データ型
alter table tb1 modify age int(3);
○カラム位置
alter table tb1 modify age int(3) first;
2.カラムの追加 add
○最後
alter table tb1 add birthday datetime;
→insert into tb1 values('emp006','八九寺',10,2001-8-7);→ミス
→update db1.tb1 set birthday='2007-8-7' where num='emp006';
○先頭(first)
alter table tb1 add firstClm varchar(10) first;
○好きな位置(after)
alter table tb1 add freePositionClm varchar(10) after firstClm;
3.カラムの名前+定義変更 change
alter table tb1 change birthday birth date;
4.カラムの削除 drop
alter table tb1 drop firstClm;
alter table tb1 drop freePositionClm;
■参考
本：基礎からのMySQL
