はじめに

Railsなどを中心に勉強中のエンジニア初心者が他の記事を参考にしたり、実際に実装してみたりして、アウトプットの一環としてまとめたものです。
間違っていることもあると思われるので、その際は指摘いただけると幸いです。

SELECT文

テーブルのデータ一覧を取得する

select * from users;

AS句で列名を置き換える

-- 列名を置き換える
select name as 氏名, age as 年齢 from users;

-- 列名を置き換える（asは省略可能）
select name 氏名, age 年齢 from users;

WHERE句（条件検索）

条件を指定してデータを取得する

select name, age from users where age >= 30

値が等しいデータを取得する

-- ageカラムが30
select * from users where age = 30;

-- nameカラムがユーザー
select * from users where name = 'ユーザー';

値の大小比較でデータを取得する

-- ageカラムが30より大きい
select * from users where age > 30;

-- ageカラムが30より小さい
select * from users where age < 30;

値が等しくないデータを取得する

-- ageカラムが30でない
select * from users where age <> 30;

-- ageカラムが30でない
select * from users where age != 30;

複数の値を指定してデータを取得する

-- ageが29,30,33の行を取得
select * from users where age in(29, 30, 33);

-- ageが29,30,33ではない行を取得
select * from users where age not in(29, 30, 33);

値がNULL（またはNULLではない）のデータを取得する

-- ageがnullではない（nullを条件にするときは、!=や<>といった書き方ができないため注意）
select * from users where age is not null;

-- priceがnull
select * from users where age is null;

値が特定の範囲内のデータを取得する

-- ageが20~30までの値
select * from users where age between 20 and 30;
select * from users where age >= 20 and price <= 30;

LIKE句（パターンマッチング）

使用する文字の意味

'%' -- 0文字以上の任意の文字列
'_' -- 任意の１文字

～で始まるデータを取得する

-- 「大」から始まる行
select * from users where last_name like '大%'

～を含むデータを取得する

-- 「大」を含む行
select * from users where last_name like '%大%';

～で終わるデータを取得する

-- 「子」で終わる行
select * from users where first_name like '%子';

文字数とパターンマッチングに該当するデータを取得する

-- 名前が３文字で「子」で終わる行
select * from users where first_name like '__子';

LIMIT句

10件のみデータを取得する

select * from users limit 10;

11件目から10件のデータを取得する

select * from users limit 10, 10;

WHERE句と合わせて使う

select * from users where age = 20 limit 10;

データの集計

特定カラムの合計値（SUM）を取得する

-- 合計値を計算 SUM
select sum(salary) from users;

特定カラムの平均値（AGV）を取得する

-- ageカラムの平均値を計算
select avg(age) from users;

特定カラムの最小・最大（MIN、MAX）を取得する

-- 最小値を求める MIN
select min(age) from users;

-- 最大値を求める MAX
select max(age) from users;

対象レコードを数える

-- テーブルのレコード数を数える
select count(*) from users;

-- ageカラムが30のユーザーレコードを数える
select count(*) from users where users.age= 30;

一意な値を抽出する（DISTINCT）

-- ユニークユーザーレコードを数える
select count(distinct user_id) from users_access where request_month = "2020-01-01";

GROUP BY句でグルーピングして集計する

-- company_idでグルーピングする
select company_id, count(*) from users group by company_id;

HAVING句でテーブルデータを集約する

select request_month, count(distinct user_id) from access_logs
where request_month >= "2017-01-01" and request_month < "2018-01-01"
group by request_month
having count(distinct user_id) > 100;

データの並び替え

値が大きい順

select * from users order by age desc;

値が小さい順

-- 値が小さい順に並び替え（ascは省略可能）
select * from users order by age asc;
select * from users order by age;

複数の条件で並び替え

-- ageの高い順に並び替え（ageが同じ場合はid順とする）
select * from users order by age desc, id asc;

演算と関数

四則演算

-- 和
select 10 + 3;

-- 差
select 10 - 3;

-- 積
select 10 * 3;

-- 商
select 10 / 3;

余りを求める

select 10 % 3;

NULLを含んだ計算

-- nullを含んだ計算はすべてnullになる
select 10 + null; # => null
select 10 - null; # => null
select 10 * null; # => null
select 10 / null; # => null
select 10 % null; # => null

絶対値を取得する

-- 絶対値の取得
select abs(100);
select abs(-100);

四捨五入する

-- round(xxx,y)は少数第y+1位で四捨五入される
select id, name, round(price * 1.08, 0) from products;

文字列を連結する

select concat(last_name, ' ', first_name, 'さん') from users;

日時を取得する

-- 現在の日付の取得
select current_date();

-- 現在の時刻の取得
select current_timestamp();

日時を計算する

-- n日後の日付 d + n
select current_date() + 3; # この書き方は月を跨ぐと正しく処理されないため注意
select current_date() + interval 3 day;

-- n日前の日付 d - n
select current_date() - interval 35 day;

-- x時間後の時刻 interval 'x hour'
select current_timestamp() + interval 24 hour;

-- x時間前の時刻 - interval 'x hour'
select current_timestamp() - interval 24 hour;

日時で絞り込んでデータを取得する

-- 2017年1月分のみ抽出
select * from users where extract(year_month from created_at) = 201701;
-- 2017年のみ抽出
select * from users where extract(year from created_at) = 2017;
-- 1月のみ抽出
select * from users where extract(month from created_at) = 1;

テーブルの結合（内部結合）

テーブル同士を内部結合（inner join）してデータを一括で取得する

-- usersテーブルのcompanies_idとcompaniesテーブルのidで結合
select users.name, companies.name from users
inner join companies on users.companies_id = companies.id;

-- 内部結合省略形（join）
select users.name, companies.name from users
join companies on users.companies_id = companies.id;

テーブルの結合（外部結合）

テーブル同士を外部結合（left outer join）してデータを一括で取得する

存在しない値はnullになるため注意

-- 外部結合（left outer join）
select users.name, companies.name from users
left outer join companies on users.companies_id = companies.id;

-- 外部結合省略形（left join）
select users.name, companies.name from users
left join companies on users.companies_id = companies.id;

テーブルの結合（3つ以上）

3つ以上のテーブルを結合してデータを取得する

-- 3つのテーブルの結合
select users.name, companies.name, orders.name from users
inner join companies on users.companies_id = companies.id
inner join orders on users.order_id = orders.id;

多対多の関係性のテーブルを結合してデータを取得する

-- 多対多
select products.id, products.name, categories.name from products
inner join products_categories on products.id = products_categories.product_id
inner join categories on products_categories.category_id = categories.id;

テーブルの足し算

重複行を削除してテーブルを足し合わせる（union）

select name, age, email from free_users
union
select name, age, email from paid_users;

重複行を削除しないでテーブルを足し合わせる（union all）

select name, age, email from free_users
union all
select name, age, email from paid_users;

条件で絞り込んでテーブルを足し合わせる

-- 条件で絞り込んだテーブルの足し算
select name, age, email from free_users where age = 20
union all
select name, age, email from paid_users where age = 30
order by age;

ビュー

ビューを作成する

create view company_user_counts(name, count)
as
select company.name, count(*) count from users
inner join companies on users.company_id = companies.id
group by users.company_id;

ビューを利用する

select name, count from company_user_counts;

ビューを削除する

drop view company_user_counts;

サブクエリ

サブクエリを使用してデータを取得する

-- 社員数が1000人以上の企業に勤めているユーザーを取得する
select id, name, email from users
where id in(select user_id from companies where number_of_employees >= 1000);

スカラサブクエリを使用してデータを取得する

-- 全ユーザーの平均年齢よりも高い年齢のユーザーを取得
select * from users
where age> (select avg(age) from users);

条件分岐

caseで条件分岐させる

-- scoreの平均値によってユーザーランクを表示し、ランクごとに並び替える
select
	id as user_id,
  avg(score) as avg_score,
  case
		when avg(score) >= 90 then "A"
    when avg(score) >= 70 then "B"
    else "C"
  end as user_rank
from users
order by user_rank;

レコードの追加

レコードを追加する

insert into users (name, age) values("user", 30);

列名を省略してレコードを追加する（テーブルの全列に対して値を指定する必要がある）

insert users values(20, "user", 30);

複数レコードを追加する

insert into users (name, age) values ("user1", 20), ("user2", 30), ("user3", 40);

レコードの更新

レコードを更新する

update users set age = age + 1;

特定のレコードを更新する

update users set name = "update_user", age = 40 where id = 100;

更新条件にサブクエリを使ってレコードを更新する

update users set salary = salary * 1.5
where id in
	(
	select user_id from sales
	group by user_id
	having count(*) >= 10
	);

安全のため大量のデータ更新ができないようになっている

set sql_safe_updates = 0;

レコードの削除

テーブル内の全てのレコードを削除する

delete from users;

条件を指定してレコードを削除する

delete from users where id = 100;

where句を指定し忘れるとテーブル全体が削除対象になるため注意

削除条件にサブクエリを使用してデータを削除する

delete from profiles
where id not in(select profile_id from users);

データベースの操作

データベースの一覧を取得する

show databases;

データベースを作成する

create database book_store;

データベースを削除する

drop database book_store;

テーブルの一覧を取得する

-- 利用するデータベースを指定
use book_store;

-- テーブル一覧を取得
show tables;

テーブルを作成する

-- テーブルの作成
create table books(id int not null auto_increment primary key, title varchar(255) not null);

テーブルの構造を確認する

show columns from books;

テーブルの構造を変更する（列の挿入）

alter table books add price int after id;

テーブルの構造を変更する（列名の変更）

alter table books change price unit_price int;

テーブルの構造を変更する（列を削除する）

alter table books drop unit_price;

テーブルを削除する

drop table books;

SQL書き方のコツ

-- 上記のSQLを書く場合
-- 1　まずは一覧を取得
select * from users;

-- 2　内部結合を追記
select * from users inner join conmanies on users.company_id = conmanies.id;

-- 3 条件の絞り込みを追記
select * from users inner join conmanies on users.company_id = conmanies.id
where users.company_id = 1;

-- 4 取得したいカラムを絞り込み
select users.id, users.name, companies.company_id
from users inner join conmanies on users.company_id = conmanies.id
where users.company_id = 1;

-- 5 カラムに名称を付与（必要時）
select users.id ユーザーID, users.name ユーザー名, companies.name 会社名
from users inner join conmanies on users.company_id = conmanies.id
where users.company_id = 1;

-- 6 並び替える
select users.id ユーザーID, users.name ユーザー名, companies.name 会社名
from users inner join conmanies on users.company_id = conmanies.id
where users.company_id = 1
order by company_id;

参考

最後に

いかがでしたでしょうか。
ここ違うよ！でしたり、こうした方がいいよ！などがあればコメントいただけると幸いです。

他にも下記のような記事を投稿しております。
興味がありましたら、ぜひご覧ください。