ねこきっくぱんちのメモ帳

webの勉強メモです。 HTML,CSS,JavaScript,PHP,Illustrator,Photoshopなど。

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

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

MySQL001 コマンド1

mysql コマンド

■ユーザ作成
・ログイン
mysql -u root -p
mysql -u root -proot
mysql db1 -u root -p

・ユーザ作成
create user kaname identified by ‘madoka’;
※’の文字化けに注意

・権限付与
grant all on *.* to madoka;
全権限付与

・ユーザ削除
drop user madoka;

■chap4
・DB作成
create database db1;

・DB一覧表示
show databases;

・利用するDBの切り替え
use db1;

・現在使っているDBの確認
select database();

・テーブル作成
create table tb1 (num varchar(10), name varchar(10), age int);
create table tb1 (num varchar(10), name varchar(10), age int) charset=cp932;

・テーブル表示
show tables;

文字コードのデフォルト設定確認
show variables like "chara%";

・他のDBへアクセス
select * from db2.table;
※useでDB未洗濯の状態でも実行可

・テーブルのカラム構造表示 Describe:述べる
desc tb1;

・データ挿入
insert into tb1 values('emp001','鹿目',14);
※指定の文字数を超えてもエラーが出ずに消えるため注意
insert into tb1 values('emp004','美樹',14),('emp005','佐倉',12);

・データ確認
select * from db1;
select num, name from tb1;
※カラム間にカンマが必要

・テーブルのコピー
create table tb1A select * from tb1;
※フィールドデータもコピーされる。

・xxx.sqlファイルの実行
source xxx.sql

・->

  • >は「コマンドが完結していない」ことを意味する。

続けて入力するか、;で実行する。

■用語
・デリミタ(delimiter) 区切り文字の意味
・int フィールドのデータ型。整数を保管。
・varchar フィールドのデータ型。文字データを保管。


■注意
・今利用しているDBが何か?を常に意識すること。
・showは他のRDBMSSQLにはなくMySQL特有のコマンド

■参考
本:基礎からのMySQL