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