SQL

RDBMS : Relational Database Management System
　(OSS)　MySQL、MariaDB、PostgreSQL
　(商用)　Oracle Database（Oracle）、SQL Server（Microsoft）、DB2（IBM）
KVS : Key-Value Store
　(OSS)　memcached、Redis
OODB : Object Oriented Database
XMLDB : XML Database

SQL <= SEZUEL（IBM）が由来という説
標準SQL　　国際標準
SQLの方言　各DB製品でしか使えない方言が存在する

基本的なSQLの記述ルール
・大文字小文字は区別されない　→　最近は小文字が多い
・SQL文の最後には ; セミコロンを書く
　　例）select * from users where age >= 20;
・文字と日付はシングルクオートで囲う
　　例）select * from users where name = ‘masa’;
　　例）select * from users where created_at >= ‘2024-05-30 00:00:00’;

RDBMS基本用語
　データベース、テーブル、行（レコード、ロウ）、列（フィールド、カラム）
　クエリ（query, 問い合わせ）　データの検索や更新、削除、抽出などの要求をDBに送信すること。クエリを投げる。
データ型
　主なデータ型
　　数値型
　　　int型（整数）　2**32　-2,147,483,648～2,147,483,647
　　　　unsighned int（符号なし整数）　2**32　0 ～ 4,294,967,295　正の整数だけ扱うことで、扱える値の範囲が変わる
　　　tinyint型（とても小さな整数）2**8　-128～127
　　　　tinyint(1)　真偽値 true(1) / false(0)を扱うことができる。
　　　　　　　　　MySQLでは 0 と null (データが存在しない)がfalseで、空文字はtrue。それ以外の値も全てtrue。
　　　float型（小さい(単精度)浮動小数点数）　-3.402823466E+38～-1.175494351E-38、0、1.175494351E-38～3.402823466E+38
　　　double型（普通(倍精度)浮動小数点数）　-2.2250738585072014E-308～-1.7976931348623157E+308、0、2.2250738585072014E-308～1.7976931348623157E+308
　　文字列型
　　　char型　MAX255　2**8　固定長の文字列　　定義方法：char(5)
　　　varchar型　MAX255　2**8　可変長の文字列　　定義方法：varchar(255)
　　　text型　MAX65535　2**16　長い文字列
　　日付・時刻型
　　　date型　　　日付　　　　’1000-01-01′ から ‘9999-12-31’
　　　datetime型　日付と時刻　’1000-01-01 00:00:00.000000′ から ‘9999-12-31 23:59:59.999999’
　　　time型　　　時刻　　　　’-838:59:59′ から ‘838:59:59’

環境構築

macOS
①homebrew
②Google Cloud Platform無料枠

Windows
①XAMPP
②MAMP
③Google Cloud Platform無料枠

MySQL

オラクル社のRDBMS。MySQL Community Editionを無料で利用できる。シェアが高い。

MySQL Workbench

　データベースの設計・開発・管理の為にMySQLから配布されている公式GUIツール

SQL文の基礎

use mydb;  # mydbの実行

select column1,column2 from table;

コメントアウト
--
/* */

select
 sum(expr)
 avg(expr)
 min(expr)
 max(expr)
 count(* | expr | distinct expr)

記述順序（必ずこの順序で書く必要がある）

select (expr)
from (table)
join on  # 結合処理 (inner join, left(outer)join) 複数結合できる
where (expr)  # and, or などで区切る
group by  # 例 select expr ～ group by expr;
having  # whereと同じ書き方
 ---
order by ()  # asc, desc  # 複数時は優先順通りに,で区切って書く
limit  # 例 (0),100

演算子

+
-
*
/
%
abs()  # 絶対値の取得
round(arg, n)  # 小数点第n位で四捨五入

/*文字列連結*/
a || b
select concat(arg1, ' ', arg2, '文字列') from table;

/*現在の日付*/
select current_date();
/*現在の時刻*/
select current_time();
/*現在の日時*/
select current_timestamp();
/*現在から3日後の日付*/
select current_time() + interval 3 day;
/*現在から1時間前の日時*/
select current_timestamp() - interval 1 hour;
/*現在から5分後の日時*/
select current_timestamp() + interval 5 minute; 
/*現在から10秒前の日時*/
select current_timestamp() - interval 10 second;

/*extract  日付や時刻の特定の部分で取り出す*/
/*2024年だけ取り出す*/
select * from table where extract(year from column)=2024;
/*6月だけ取り出す*/
select * from table where extract(month from column)=6;
/*2024年6月だけ取り出す*/
select * from table where extract(year_month from column)=202406;

テーブルの結合

# 内部結合 inner join 例1
select
    u.id as ユーザID,
    u.last_name as 氏,
    u.first_name as 名,
    p.name as 都道府県
from
    users u  # 変数uに代入
inner join
    prefectures p  # 変数pに代入
on
    u.prefecture_id = p.id
where
    u.gender = 2;


# 内部結合 inner join 例2
select
    o.id order_id,
    o.order_time order_time,
    o.amount amount,
    u.id user_id,
    u.last_name last_name,
    u.first_name first_name,
    p.name pref_name
from
    orders o
inner join
    users u
on
    o.user_id = u.id
inner join
    prefectures p
on
    u.prefecture_id = p.id
where
    u.prefecture_id = 13
    and o.order_time >= '2017-01-01 00:00:00'
    and o.order_time < '2017-02-01 00:00:00'
order by
    order_id;


# 多対多の結合には中間テーブルを使用する
select
    p.id product_id,
    p.name product_name,
    c.name category_name
from
    products p
inner join products_categories pc
    on p.id = pc.product_id
inner join categories c
    on pc.category_id = c.id
where
    p.id = 3;


# 複数テーブルを同じカラムで縦に結合する
select
    email,
    last_name,
    first_name,
    gender
from
    users
where gender = 1
union (all)  # デフォ：重複行は削除される　all：重複行も全て表示する
select
    email,
    last_name,
    first_name,
    gender
from
    admin_users
where gender =2
order by gender;

※where, group by, having は中に入れることができるが、
　order by, limit は外で1回限り。

ビュー
　select文の中身を定型化して、使いまわす（パフォーマンスが落ちることがあるので注意）
　関数を作るのと同じみたい。

create view view名(<ビューの列名1>,<ビューの列名2>,...)
    as
select文

/*
create view prefecture_user_counts(name,count)
as
select
    p.name name,
    count(*) count
from
    users u
inner join prefectures p
    on u.prefecture_id = p.id
group by u.prefecture_id;
*/

select
    name,
    count
from
    prefecture_user_counts;

ビューの制約事項
　更新系（insert, delete, update）に制約がある
・いくつかの条件を満たす場合のみ、ビューに対する更新が可能（ぇ・・
１．select句にdistinctが含まれていない
２．from句に含まれるテーブルが一つだけである
３．group by句を使用していない
４．having句を使用していない
５．集約されたビューではない
　　※ビューとテーブルの更新は連動して行われるため、集約されたビューは更新不可。

ビューの内容確認　難しそう・・

サブクエリ
　日々の業務改善のデータ分析に役立つデータが、データベースから直接SQLで取り出せる
　複雑な問い合わせができる
　where句の中で使うことが多いが、select句、from句、having句など様々な場所で使える
　例）全商品の平均単価より、高い商品を取得
　　　商品別の平均販売数量よりも、多く売れている日を取得
　　　商品カテゴリ毎に、平均単価を取得
　　　2017年12月に、商品を購入して”いない”ユーザーを取得

# where句で使うサブクエリ
select
    列名, ....
from
    テーブル名
where
    列名 演算子
    (
    select 列名 from テーブル名2 ...
    )
;


# 指定した条件を「省く」とき not in(select - from - where -)
select
    id,
    last_name,
    email
from
    users
where
    id not in
    (
    select      # ここからサブクエリ
        user_id
    from
        orders
    where
        order_time >= '2017-12-01 00:00:00'
        and order_time < '2018-01-01 00:00:00'
    )
;


# スカラ・サブクエリ（単一行を返すselect文）使用例
select
    *
from
    products
where
    price >
    (
    select
        avg(price)
    from
        products
    )
order by price desc, id asc
;

条件分岐　case ～ end 式
　case式の中身では ; は使えない

# 例1
select
    u.id as user_id,
    count(*) as num,
  - case
 |      when count(*) >= 5 then 'A'
 |      when count(*) >= 2 then 'B'
 |      else 'C'
  - end as user_rank  # case式にカラム名を付けるときはend行に！
from
	users as u
inner join orders as o
	on u.id = o.user_id
group by u.id
order by user_rank, num desc
;


# 例2
select
    p.id,
    p.name,
    case
        when sum(od.product_qty) is null then 0
        else sum(od.product_qty)
    end as qty_total,
    case
        when sum(od.product_qty) >= 20 then 'A'
        when sum(od.product_qty) >= 10 then 'B'
        else 'C'
    end as sell_rank
from
    products p
left join order_details od
    on p.id = od.product_id
group by p.id
order by sell_rank, qty_total desc, id
;

nullを0に置き換える方法

select
    p.id,
    p.name,
  - case
 |      when sum(pd.product_qty) is null then 0
 |      else sum(pd.product_qty)
  - end as num
from
    products as p
left join order_details as pd
    on p.id = pd.product_id
group by p.id
;

表示形式を変更する方法

date_format(対象カラム, '%Y%m')

データの更新

行を追加する

# (列リスト)を指定する場合
insert into テーブル名 (カラム1, カラム2) values ('文字列', 数値)

# (列リスト)を省く場合は、全列文のvalues('','','')を順番通りに記載する必要がある
inser into テーブル名 values (数値, '文字列', 数値)

# 複数行を一気に追加する　▼oracleDBでは動作しない
insert into テーブル名 (カラム1, カラム2)
values
    ('文字列', 数値),
    ('文字列', 数値),
    ('文字列', 数値)

データを更新する

# 表全体を一気に更新（あまりやらない…）
update テーブル名 set 列1 = 値1, [列2 = 値2]
[where 条件式];

set sql_safe_updates = 0;  # 制限解除（実業務で使う場合は注意！）
update products set price = price / 0.9;

# 条件に合致したレコードだけ更新
update products set name = 'SQL入門' where id =3;

# 1行の複数列を一気に更新
update products
set
    name = 'SQL入門1',
    price = 1000
where id = 3;

# 更新条件にサブクエリを使う(条件に合致するレコードだけ更新する)
update products
set price = price * 1.05
where id in
    (
    select
        product_id
    from
        order_details
    group by product_id
    having
        sum(product_qty) >= 10
	);

行を削除する

# 全レコードを一気に削除
delete from テーブル名;

　※deleteで削除したデータは、基本的にはもとに戻せない。
　※大量のデータをdeleteするときに、予想外に時間が掛かる場合がある。
　　10万件を超えるあたりから要注意。

# 条件を指定して行の削除
delete from テーブル名 where 条件;

　※うっかりwhere句を指定し忘れると、テーブル全体が削除対象になってしまうので要注意。

# 削除条件にサブクエリを使う(条件に合致するレコードだけ削除する)　▼DB2では動作しない
delete from products
where id not in
    (
    select 
        product_id
    from
        order_details
    group by product_id
    )
;

データベース構造の操作

データベースの追加・削除

# データベース一覧
show databases;

# データベースの追加
create database データベース名;

# データベースの削除
drop databese データベース名;

データベースの命名ルール（ルールに従わないとエラーになる）
・名前の最初の文字は半角アルファベットであること
・alpha-numeric（記号は _ アンスコのみ）
・つけた人しかわからない名前は「マジックナンバー」と呼ばれ、実業務では嫌われる

テーブルの追加・削除

# 使用するデータベースを選択
use データベース名;

# テーブル一覧
show tables;

# テーブルの追加
例）id列（行のID）、title（本のタイトルを格納する）
create table books(id int not null auto_increment primary key, title varchar(255) not null);
- int: 整数型
- not null: nullを許可しない
- auto_increment: idを自動的に振る
- primary key: 主キーに設定する
- varchar(255): 最大255文字の可変長文字列型

# テーブルの削除
drop table books;

カラムの追加・名称変更・削除

# カラム一覧
show columns from テーブル名;

# カラムの追加
alter table テーブル名 add カラム名 データ型 after カラム名;

# カラム名の変更
alter table テーブル名 change 旧列名 新列名 データ型;

# カラムの削除
alter table テーブル名 drop 列名;

操作の注意点

alter table, drop table, drop database等

・操作は基本的には取り消せない
　特に、実務において本番環境を操作する時は、サービスをメンテナンスモードにして、バックアップを取ってから実行するのが安全。

・想定外に時間がかかりシステムトラブルになる場合も
　テスト環境でalter tableのテストをして問題点を洗い出してから、本番環境で実行するのが安全。