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のテストをして問題点を洗い出してから、本番環境で実行するのが安全。