個人的リマインド用
参考
【MySQL入門決定版】2時間半で学ぶ初心者向けMySQLデータベースチュートリアル【MySQLの基本とSQLの基礎文法の徹底的にマスター】
1
前提
PHPを使う。
目標はデータベースでのCRUD操作。
World Databaseっていう世界の都市情報が入ったデータベースを用意したので、次回はこれを使って操作していく。
2
RDBの特徴
・データの強い生合成
ヘンテコなデータが生まれにくい。
・柔軟な検索
SQLを使うことで思いのままに操ることができる。
検索メインで練習
MYSQLの用語
データベース
テーブルをいくつか集めたもの。
テーブル
データの集まりのこと。表形式で管理されている。
行(レコード)が1つのデータ、列(属性、カラム)がデータの属性で名前と型からなる。
データの取得(SELECT文)
select ID, Name, Poplation from city;
(select カラム from テーブル名)
select * from city; 全て持ってくる
SELECT文の実行結果はテーブルで返ってくる
3
制限
ある条件を満たすレコードのみを取り出す操作(レコードの検索)
データを制限して取得(Where句)
select カラム名1 カラム名2 from テーブル名 where 条件式;
投下演算(=)
与えられた2つの値が等しいかどうかをチェックする
select ID, Name from city where Name = 'Tokyo';
※TRUEだと1を返し、FALSEだと0を返す。そして1のものだけを抽出するので、
select * from city where 1;
これを実行すると全て抽出できる。
4
大小関係での抽出
select * from city where Population >= 1000000;
char型に対しても大小関係で抽出でき、その場合は辞書順になる。
selet * from city where CountryCode > 'Y';
文字列の最初がY含め、それ以降の文字から始まるものを抽出する
等しくないものの抽出(!=)
select * from city where CountryCode != 'JPN';
日本以外を全て抽出
複数の条件で抽出(AND BETWEEN OR IN)
AND BETWEEN
select * from city where CountryCode = 'JPM' and Population > 500000;
select * from city where Population between 400000 and 500000;
select * from city where Population not between 400000 and 500000;
OR in
select * from city where CountryCode = 'JPN' or Population > 500000;
select * from city where CountryCode in ('FRA', 'ITA');
どちらかでも含まれていたら、それを抽出
select * from city where CountryCode not in ('FRA', 'ITA');
FRA,ITA以外を抽出
5
曖昧な文字列の検索
あるキーワードを含んでいる、から始まる(like)
select * from city where Name like 'ab%'
abから始まる都市名を抽出
select * from city where Name like '___'
3文字の都市名を抽出
ワイルドカード文字(% _)
% → 全ての文字代わりに使える万能なもの(0文字以上のあらゆる文字列)
_ → 何かしらの1文字
検索結果の並べ替え(order by)
select * from city where CountryCode = 'JPN' order by Population;
昇順(多くなっていく)
select * from city where CountryCode = 'JPN' order by Population desc;
降順(少なくなっていく)
絞る(limit)
select * from city where CountryCode = 'JPN' order by Population limit 10;
上から10件
6
データベースの作成(create)
create database データベース名;
テーブルの作成(create)
create table users (
id int,
name varchar(10)
);
レコードの作成(insert)
insert into users (id, name) values (1, 'yorozuya');
※上で決めた型じゃなくても挿入でき、長さを超えても勝手に整えられるだけで挿入できる。しかし警告は出してくる。正直、これは嬉しくないのでStrictモード(厳格モード) というものがある。これを使うと入らずに、エラーとしてくれる。使い方は直接ファイルに記述する。
7
テーブルのキー
主キー(PK)
テーブルの中のデータを1つ特定するための属性が主キー。候補キーから1つ選んで主キーとする。
候補キー
複数のデータの集まりから1つのデータを特定できる属性(候補キーは1つとは限らない)
例:都道府県は候補キーだが、県庁所在地も候補キーになれるといったように、複数存在する。
複合キー
ある属性とある属性を組み合わせて、データ1つ特定する。
例:巨人という球団の背番号10とか
ID
レコードを一意に特定するためにシステムによって発行された属性
主キーの設定方法
create table users (
id int primary key,
name varchar(10)
);
複合キーの設定方法
create table users (
id int,
name varchar(10),
primary key(id, name)
);
8
レコードの更新・削除
※型について → int型は32bit、tinyint型は8bit(-128~127)。データベースの容量を4倍削減できるので、100を絶対超えないカラムにはtinyint型を使う。
大量のデータを一度に挿入する
insert into users
(id, name, sex, birthday)
values
(1, '田中太郎', 1, '1987-01-20'),
.
.
.
更新(update)
update users
set name = 'さとう', birthday = '1999-10-20'
where id = 1;
削除(delete)
delete from users where id = 1;
9
集計関数
count,sum,avgなどなど
合計(sum)
select sum(population) from country;
カラム名に名前をつける
select sum(population) as WorldPopulation from country;
※数字を見やすくしてくれる
select format(sum(population), 0) from country;
~特定のまとまりごとに求めたい(group by)
select sum(population) from country group by Continent;
大陸ごとの人口
group byは複数選択できる
group by Continent, GovermentForm
グループ化した後に絞り込み(having)
whereだとグルーピングの前に絞り込み
select sum(population) from country group by Continent
having ContinentPopulation > 10000000;
10
データの結合
どんな時に使うのか(例):東京の人口は日本全体の人口の何％かを求めたい。
まずは東京の人口を抽出。
select name, population from city where name = 'Tokyo';
しかし日本の人口がcityテーブルに入っていない。countryテーブルにある。こんな時に使うのがjoinで横に結後する。
※テーブル同士のつながりのことをリレーションシップという。
cityテーブルの方にはCountryCodeがあり、countryテーブルには同様のデータであるCodeがある。
select * from city join country on city.countrycode = country.code;
結合ができたのでコードを変えて、
select id, city.name, city.population, county.population from city
join country on city.countrycode = country.code
where city.name = 'Tokyo';
最後に割合を求めるコードに
select
id,
city.name,
city.population,
county.population,
city.population / country.population as ratio
from city join country on city.countrycode = country.code
where city.name = 'Tokyo';