MySQL（基本操作）
テーブルの作成
create table my_items(id int, item_name text, price int);
- my_itemsというテーブルを作成
- idをint型で作成
- item_nameをtext型で作成
- priceをint型で作成
- 下記のように追加される↓↓↓
データの挿入
insert into my_items set id=1, item_name="いちご", price=200;
- 「my_items」というテーブルにデータを挿入
- 「id」が1、「item_name」がいちご、「price」が200のデータを追加
- 列として存在する値を追加しないといけない
- 下記のように追加される↓↓↓
別の記入方法
insert into my_items values(2, "もも", 500);
- たくさんデータを挿入する場合はこの方法！
- 「my_items」というテーブルにデータを挿入
- 「id」が2、「item_name」がもも、「price」が500のデータを追加
- テーブルの値の順に指定すると追加される（型があっている必要あり）
- 下記のように追加される↓↓↓
上記の書き方はカラム名を省略した書き方になっている
insert into my_item（id, item_name, price）） values(2, "もも", 500);
- 省略せずに書くとこのような形になる
データの更新
update my_items set price=180 where id=1;
- 「my_items」というテーブルにある「id」1の値に対して
「price」を180に変更 - 「where」は条件を指定するもの→「idというカラムが1の値に対して～」
- 「いちご」のpriceが200→180に変わる
複数の値も変更できる
update my_items set price=180, item_name="とちおとめ" where id=1;
- 複数変更することも可能
- これだとpriceとitem_nameの値が更新される
データの削除
delete from my_items where id=1;
- 「my_items」というテーブルから
- 「idが1の値を」削除する
- updateとdeleteはwhereの指定が無いとすべて対象になってしまうので注意！
データの検索
select item_name from my_items;
- 「my_items」テーブルの中で
- 「item_name」というカラムを表示する
複数表示もできる
select item_name, price from my_items
- 「my_items」テーブルの中で
- 「item_name」と「price」というカラムを表示する
すべてのカラム表示もできる
select * from my_items;
- 「my_items」テーブルの中で
- すべてのカラムを表示する
条件指定もできる
select * from my_items where id=1
- idが1のすべてのカラムを表示
プライマリキーの設定
- 対象のカラムをプライマリキーに設定
- 今回の場合「id」を指定
- 鍵アイコンが付いているものがプライマリキー
オートインクリメント
- idの設定を変更する（AIにチェック）
- これだけで自動採番が設定
- オートインクリメントはプライマリキーに設定するのが一般的
- idの項目を入れずに商品を追加しても自動採番でidが記入される
データを削除した後、追加したデータの挙動は？
- id3のデータを削除した後、メロンを追加した時の挙動↓↓↓
- メロンのidは3にならずに5が割り当てらる
テーブル構造の変更
- id、item_name、priceに加えて新しいカラムを追加したい場合
- ↑priceの後ろに追加
↑記入欄出てくるの追加したものを記入、保存
↑追加される
where構文について
SELECT * FROM `my_items` WHERE id>1;
- whereの指定は「id=1」だけじゃなく「id>1」のように不等号も使える
- この場合2以上のidのものを表示
- 「id>=1」「id!=1」なども使える
値の一部を検索したい場合
- keywordに追加した「甘い」というテキストを持つ値をselectで表示させたい
select * from my_items where keyword="甘い"
- これでは出ない↑↑↑
- 「keyword="赤い,甘い,ケーキ"」と記入すると出る
select * from my_items where keyword like "%甘い%"
- 「甘い」を検索する場合はこの形にする↑↑↑
- like構文は「%」が必要
- 「%」はなにが記入されていてもよい、という記述、上記の場合、「赤い」の前後に何が記述されていてもよい状態
and、orなども使える
select * from my_items where price > 100 and price < 200
select * from my_items where price > 100 aor price < 200
データの並び替え（order by）
select * from my_items order by price asc;
- 「my_items」テーブルの中で「price」を昇順に並び替え
- 「asc」昇順（小さい順）
- 「desc」降順（大きい順）
ascは省略もできる
select * from my_items order by price;
whereでの絞り込み
select * from my_items where price > 100 order by price;
- order byにwhere組み合わせる場合はorder byより前にwhere条件加える
数字以外（テキスト）での並び替え
- 並び替えたい場合、ひらがな、カタカナ、漢字混ぜることはできないので、フリガナのカラムを追加する必要あり
- こんな感じでカナを統一したカラムを追加する（item_name_kana）↓↓↓
select * from my_items order by item_name_kana;
- その上で上記order byを実行するとカタカナの順番（アイウエオ順）で並んでくれる
相対的な情報と絶対的な情報
- ↑↑↑↑人気ランキングの「rank」を追加したが、この値は他の商品との相対的な関係性によって決まる値なので本来は扱わないほうがよい
- salseというカラムを追加する（売れた数を記入する）
select * from my_items order by sales desc;
- 売れた順に表示することができる
- 売れた数は商品にとって絶対的な情報になる
- 絶対的な情報を並べ替えることで他の値が変わると順番を変更して表示することができる
- ランキングを作りたいからといって「rank」というカラムを作るのではなく、どのように表示させるかを考える必要がある
SQLの計算機能
【count】
select count(*)from my_items
- countを*に指定すると「5」という数字が表示される
- これはデータの件数を表示してくれている
【sum】
select sum(price)from my_items
- priceの値をすべて合算した値が出る
【min】【max】
select min(sales)from my_items
- salesの中で一番小さい値が表示される
- maxは最大値
【avg】
select avg(price) from my_items
- priceの平均値
テーブルのリレーション
- 商品のデータとして生産者のデータを追加したい
- カラムを追加してもよいが（上の「maker」の値）、生産者の情報（住所、連絡先）なども追加したくなった場合 その都度情報を追加していくのはスムーズではない
- 別のテーブルを作って管理するのがよさそう
- 「makers」というテーブルを新しく作ってデータを追加した
- 3つの生産者情報を挿入した
商品テーブルと結合する
- まず商品テーブルにmakersのidを追加したい（idの次に追加する）
- ↓↓↓カラムを追加して、商品に生産者IDを追記する（ここは適当に割り振っている）
下記のSQL文でテーブルが結びついた状態で表示ができる
select * from makers, my_items where makers.id = my_items.maker_id;
-
select * from makers, my_items→「makers」と「my_items」のテーブルを表示 -
where makers.id = my_items.maker_id→「makersのid」と「my_itemsのmaker_id」が一致したデータのみを対象とする - つまり「makers」と「my_items」のテーブルの中で「makersのid」と「my_itemsのmaker_id」が一致したデータのみを表示する
少し省略した書き方
select * from makers m, my_items i where m.id = i.maker_id;
-
select * from makers m, my_items i→「makers」を「m」、「my_items」を「i」に格納 -
where m.id = i.maker_id;格納（省略）したテーブルを参照している形
group by
- 新しいテーブル「carts」を追加
- よき具合にデータを追加する（item_idが商品、countが売れた数）
select * from my_items, carts where my_items.id = carts.item_id;
- 「いちご」が4個、「バナナ」が8個、「ブルーベリー」が2個、「いちご」が4個、「ブルーベリー」が2個売れた、ということがわかる
売れた数の合計を表示
select sum(count) from carts;
- 売れた数のすべての合計が20とわかる
商品ごとの売れた合計数
select item_id, sum(count) from carts group by item_id;
-
select item_id, sum(count)「item_id」と「売上合計数」を表示する -
from carts group by item_id;→cartsのテーブルから「item_id」を、ひとまとめにした状態で - つまり→cartsのテーブルから、「item_id」と「売上合計数」を表示する。
※「売上合計数」は「item_id」をひとまとめにした状態で。 - group byの位置がとてもむずかしく感じる
ひとまとめにしない場合の書き方は…？
select item_id, count from carts
- cartsテーブルから「item_id」と「count」を表示
リレーションを利用した表示パターン
※むずかしい
select item_id, item_name, sum(count) from my_items, carts
where my_items.id=carts.item_id group by item_id;
- ※「my_items」「carts」がテーブル名
-
select item_id, item_name,→「item_id」と「item_name」を表示 - 「item_name」は「my_items」から引っ張ってくる
- 「item_id」「sum(count)」は「zarts」から引っ張ってくる
-
where my_items.id=carts.item_idで「my_items」の「id」と「carts」の「item_id」を結び付けている - ここもgroup byの位置がむずかしい
left join / right join【外部結合】
- これまでの結合表示だと、cartsテーブルで1つも売れていない商品は表示されない状態だった
- 具体的には「なし」と「メロン」↓↓↓
left joinで外部結合する
select item_name, sum(count) from my_items left join carts on my_items.id=carts.item_id
group by item_name;
- NULLは0と考えてよい
-
select item_name, sum(count)→「item_name」と「sum(count)」を表示 -
from my_items left join carts→「my_items」のテーブルは「carts」と結びつかなくてもすべて表示
※これによって「なし」「メロン」がcartsテーブルに無くても「my_items」テーブルあるので表示されている -
on my_items.id=carts.item_id→whereの代わりに「on」になっているだけで意味は同じ
「my_items」の「id」と、「carts」の「item_id」を結び付ける（どのカラム同士を繋げるか）
※left joinを使った場合は「on」になる -
group by item_name;→前の項目だとここは「item_name」ではなく「item_id」だった - SQLの特性でselect構文あるカラムでないとgroup byにできない、というルールがある。
※item_idだとエラーになる
inner join
- left joinで記述している箇所をinner joinに変更すると、、
select item_name, sum(count) from my_items inner join carts on my_items.id=carts.item_id
group by item_name
- カンマ区切りで書くのと同じ内容になっている
select item_name, sum(count) from my_items, carts where my_items.id=carts.item_id
group by item_name
- 「inner join」→「,」
- 「on」→「where」
- このように変えるのと同じ状態
- どちらでもよいが、カンマの方がややわかりやすい
その他のSQL構文
distinct
select distinct item_id from carts;
- ↑↑↑item_idを重複なしで表示する
select item_id from carts
- ↑↑↑普通に表示したらこのようにばらばらに表示される
between
select * from my_items where price>=150 and price<=1000
select * from my_items where price between 150 and 1000;
- どちらも同じ表示内容になる
in
select * from my_items where id=1 or id=3;
select * from my_items where id in (1,3)
- どちらも同じ内容になる
- idが1か3のデータを表示
limit
select * from my_items;
- ↑↑↑これだとテーブル全部表示される。
- 現状数が少ないので問題ないが、膨大なテーブルになると表示が多くなりすぎる
select * from my_items limit 2;
- 全体から2件だけ表示
スタート位置変えられる
select * from my_items limit 2,3;
- ちょっとややこしいけど、2件データを飛ばして、3件目から3件表示、となる
as
select sum(count) from carts;
- countで表示するが、何の値なのかがわからない場合がある
select sum(count) as sales_count from carts
- asで値の名前を指定してあげることで指定した名前で値が表示される
3つのテーブルのリレーション
select m.name, i.item_name, sum(c.count) as sales_count
from makers m, my_items i left join carts c
on i.id=c.item_id
where m.id=i.maker_id
group by m.name, i.item_name
order by sales_count desc;
- 【select】「m（makers）」テーブルのname、「i（my_items）」テーブルのitem_name、「c（carts）」テーブルのcountの合計を「sales_count」として、表示
- 【from】どのテーブルか？→「makers」を「m」、「my_item」を「i」、left joinで「carts」を「c」として対象
↑↑↑※「my_item」と「carts」の2つがleft joinで結ばれている。「carts」に無い要素でも「my_item」の要素は表示する - 【on】「i（my_items）」テーブルのidと「c（carts）」テーブルのitem_idを結び付ける
- 【where】「m（makers）」テーブルのidと「i（my_items）」テーブルのmaker_idを対象にする
- 【group by】「m（makers）」テーブルのnameと「i（my_items）」テーブルのitem_nameはひとまとめにする
- 【order by】sales_countを降順（大きい順）にする
データベースのエクスポート・インポート
- データベースの情報をファイルとして保存
- バックアップや、新規で作成する場合などにデータを再利用できる
- バックアップしたい一番上の階層を選択して「エクスポート」
- 「詳細」を押して、すべてのテーブルにチェックが入っていることを確認
- ファイルのフォーマットは任意。JSONなどもあるがSQLファイルで問題ない？
- インポートは新しくテーブルを作成して、インポート、バックアップしたファイルを選択。
