MYSQL入門

個人的リマインド用

参考
【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';