SQL
SQLとは
SQL・・・『Structured Query Language』
リレーショナルデータベースの操作をする言語
命令文の種類
データを定義するDDL(Data Definition Language)
データを操作するDML（Data Manipulation Language)
DDL(データ定義)
Create ・・・ 作成
Alter ・・・ 更新
Drop ・・・ 削除DML(データ操作)
Insert ・・・ 登録
Update ・・・ 更新
Delete ・・・ 削除
Select ・・・ 検索SQL文
MySQLに接続する
『Mysqlにユーザー名がrootで接続する』という意味
※ SequelPro 使用
mysql -u rootSHOW DATABASES文
データベースを一覧で表示する
show databases;CREATE DATABASE文
データベースやテーブルを作成する
create database;USE文
使用するデータベースの指定
use データベース名;SHOW TABLES文
選択したデータベースに存在するテーブルを一覧で表示する
show tables;CREATE TABLE文
テーブルの作成
create table テーブル名 (カラム名 カラム名の型, ……);
例)
create table exmple (id INT, name VARCHAR(255));カラムの型指定
INT ・・・ 数値
VARCHAR(X) ・・・ 最大X文字の文字列show columns from テーブル名;
テーブルの構造の確認
show columns from テーブル名;
ALTER TABLE文
カラムの追加、修正、削除
『追加』
alter table テーブル名 add カラム名 カラム型;
複数の場合
alter table テーブル名 add(カラム名 カラム型, ・・・ )
例)
alter table exmple add(nedann int, stock int)
追加後『show columns from テーブル名;』 で テーブルの中確認『修正』
alter table テーブル名 change 古いカラム 新しいカラム 新しいカラム型;
例)
alter table exmple change nedann price int;『削除』
alter table テーブル名 drop カラム名;
例)
alter table exmple drop price;INSERT文
テーブルにデータを登録
全てのカラムに値をいれる時
insert into テーブル名 values(a, b, c);
例)
insert into exmple values(1, "ポテト", 190);
特定のカラムに値をいれる時
insert into テーブル名(カラム名1, カラム名2) values(a, b);
例)
insert into exmple(id, name) values(2, "ナゲット");
『SHOW columns FROM テーブル名;』 で 変更確認『null』は『default』の設定になる
SELECT文 FROM文
テーブルにある要素を検索する
select 取得したい要素 from テーブル名
例)
select name from exmple;
『exmple』テーブルの『name』のカラムを取得
例)
select * from exmple;
テーブルにあるレコード(列)を全て取得
『*』 ・・・ アスタリスクという。すえてを取得するUPDATE文
データの更新をする
update テーブル名 set 変更内容 where 条件;
例)
update exmple set price = 200 where id = 2;
id2のレコードの『price』を200に変更するDELETE文
データの削除
delete from テーブル名 where 条件;
例)
delete from exmple where id = 2;Where句
レコードの条件を指定して取得する
where 条件
例)
where id = 1
例)
where name = "爆轟"
例)
where id = id <= 5AND演算子
条件を指定し、正を取得する
where A and B
例)
select *
from exmple
where age <= 20 and group = "麦わらの一味"
ageは20以下 かつ 麦わらの一味 のレコードを取得OR演算子
条件のどちらかを含むデータを取得する
where A or B
例)
select *
from exmple
where place <= 5000 or color = "黒"
place が 5000 以下もしくは color が黒のレコードを取得NOT演算子
条件に当てはまらないものを取得する
where not A
例)
select *
from exmple
where not category = "ヴィラン"
category が ヴィランでないレコードを取得BETWEEN演算子
上限と下限を指定して、その範囲を取得する
where カラム名 between 下限 and 上限
例)
select *
from exmple
where age between 20 and 30
age が 20以上 30以下 を 取得IN演算子
カラムに対して条件を指定して、それに含まれるレコードを取得する
where カラム名 in(a, b, c,・・・・)
例)
select *
from exmple
where village in("木の葉隠れの里","砂隠れの里")
village が 木の葉隠れの里、もしくは砂隠れの里のレコードを取得NOT IN演算子
指定した条件を含んでいないレコードを取得する
NULLを指定すると他の情報も正しく取得できない
where カラム名 not in(a, b, c,・・・・)
例)
select *
from exmple
where village not in("木の葉隠れの里","砂隠れの里")
village が 木の葉隠れの里、もしくは砂隠れの里以外のレコードを取得IS NULL演算子
NULLかどうかを確認する演算子
where カラム名 is null
例）
select *
from one_piece
where devil_fruit is null
『devil_fruit(悪魔の実)』 が NULL(食べてない)を取得するIS NOT NULL演算子
NULL以外を探す演算子
where カラム名 is not null
例）
select *
from one_piece
where devil_fruit is not null
『devil_fruit(悪魔の実)』 が NULL じゃないの(能力者)を取得するCONCAT関数
複数の文字列を連結させる関数
concat(文字列1,文字列2,・・・・)
例)
select concat(first_name, last_name)"名前"
from exmple
結果：『first_name → 緑谷』、『last_name → 出久』 が 『緑谷出久』 で連結して取得できるAS句
カラムに別名付けれる
select 取得するデータ as 別名
例)
select concat(first_name, last_name) as "名前"
『as』は省略できるLIKE句
カラムに対して文字列検索をする
select カラム名 from テーブル名 where カラム名 like "検索文字"
例)
（完全一致検索）
select name"名前"
from one_piece
where name like "モンキー"
(前方一致検索)
select name"名前"
from one_piece
where name like "モ%"
(後方一致検索)
select name"名前"
from one_piece
where name like "%ィ"
(曖昧検索)
select name"名前"
from one_piece
where name like "%ル%"
(除外検索)
select name"名前"
from one_piece
where name not like "%ヲ%"
(1文字検索)
select name"名前"
from one_piece
where name like "%ル_"ORDER BY句
昇順、降順にソートする
asc ・・・ 昇順(デフォルト)
desc ・・・ 降順
select カラム名
from テーブル名
order by カラム名 (昇順、降順を指定);
例)
select *
from exmple
order by id desc;
(複数)
select *
from exmple
order by id desc, name asc;
(id が 5 より小さい)
select *
from exmple
where id > 5
order by id desc;
(group by)
select *
from exmple
group by name
order by id desc;
group by と使う際は order by を後に記述するDISTINCTキーワード
指定したカラムの値が重複するレコードを除外してデータを取得する
select distinct カラム名
例)
select *
from exmple
where date = 2020-02-22GROUP BY句
指定したカラムが同じ値を持つデータを1つのグループとしてまとめる
group by カラム名
例）
select *
from exmple
where date = 2020-02-22
group by user_idCOUNT関数
グループ化されたデータに対して使用することができる集計関数
select count(カラム名)
例)
select count(*)
nullであるレコードも取得するnullを除外したい場合はアスタリスクではなくカラムを指定する
SUBSTRING関数
文字の切り取りを行う関数
select substrinf(カラム名, 切り取り開始文字数, 切り取る文字数 )
例)
select substring(name, 1, ３)
from exmple
nameの1文字目か3文字目までをきりとり出力するCHAR_LENGHT関数
文字の長さの計算
・select char_length(カラム名)
・order by char_length(カラム名) desc;
例)
select name
from cexmple order by char_length(name) desc;
nameの文字列数が多い順に並べ替えるSUM関数
同じデータを持つグループの合計を求める
select sum(カラム名)
from テーブル名
例)
select sum(bounty)"懸賞金"
from one_piece
where group = "麦わらの一味"
例)
select group"一味", sum(bounty)"懸賞金"
from one_piece
group by group
+---------+-------------+
| 一味 | 懸賞金 |
+---------+-------------+
|麦わらの一味|31億6100万100 |
| | |
| | |AVG関数
同じデータを持つグループの平均を求める
select avg(カラム名)
from テーブル名
例)
select avg(bounty)"平均"
from one_piece
where group = "麦わらの一味"
例)
select group"一味", sum(bounty)"平均"
from one_piece
group by group
+---------+-------------+
| 一味 | 平均 |
+---------+-------------+
|麦わらの一味| 3億1610万10 |
| | |
| | |MAX関数
同じデータを持つグループの最大値を求める
select max(カラム名)
from テーブル名
例)
select max(bounty)"最大値"
from one_piece
where group = "麦わらの一味"
例)
select name"名前", sum(bounty)"最大値"
from one_piece
+--------------+-------------+
| 名前 | 最大値 |
+--------------+-------------+
| モンキー・D・ルフィ | 15億 |
| | |
| | |MIN関数
同じデータを持つグループの最小値を求める
select min(カラム名)
from テーブル名
例)
select min(bounty)"最小値"
from one_piece
where group = "麦わらの一味"
例)
select name"名前", min(bounty)"最小値"
from one_piece
+------------------+-------------+
| 名前 | 最小値 |
+------------------+-------------+
| トニートニー・チョッパー | 100 |
| | |
| | |JOIN
指定したそれぞれのテーブルのカラムの値が一致するデータを結合すること
FROM句のあとに記述
from テーブル１
join テーブル2 on テーブル１.カラム名1 = テーブル２.カラム名2
例)
select teams.name"所属チーム", players.name"選手名", players.uniform_num"背番号"
from teams
join players on teams.user_id = palyers.id
省略パターン)
select t.name"所属チーム", p.name"選手名", p.uniform_num"背番号"
from teams t
join players p on t.user_id = p.id
別名指定する際は頭文字を指定するのが一般的・内部結合
それぞれのテーブルの指定したカラムの値が一致するものだけを結合
条件にないレコードは除外する
『join』と『inner join』は同じ
from テーブル１
inner join テーブル2 on テーブル１.カラム名1 = テーブル２.カラム名2
例)
select teams.name"所属チーム", players.name"選手名", players.uniform_num"背番号"
from teams
inner join players on teams.user_id = palyers.id・外部結合
それぞれのテーブルの指定したカラムの値が一致するものだけでなく条件にないレコードも全て取得する
『left join』と『right join』の二つが存在する
left join ・・・ 左側を優先して出力する
right join ・・・ 右側を優先して出力する
from テーブル１
left(right) join テーブル2 on テーブル１.カラム名1 = テーブル２.カラム名2
例)
select schedules.play_ball"試合開始",teams1.name"チーム１",teams2.name"チーム2"
from schedules
left join teams teams1 on schedules.my_team_id = teams1.id
left join teams teams2 on schedules.enemy_team_id = teams2.id
where schedules.play_ball between "2020-05-01 00:00:00" and "2020-05-31 23:59:59"
"2020-05-01 00:00:00" から "2020-05-31 23:59:59"までの『teams1』と『teams２』の対戦表を取得するCASE式
条件ごとに表示を変更させる条件分岐式
s
elect
case when 条件 then 条件に当てはまる時の表示内容
else 条件に当てはまらない時の表示内容
end
from テーブル名
例)
select name"Tシャツ", height"身長",
case when 175 <= height then "L"
when height between 165 and 174 then "M"
else "S"
end as "サイズ"
from user
where sex = "男性"
・男性の身長に対してサイズを割り当てる
例)
select
case when sex = "男性" then "男性"
else "女性" end as "性別",
sum(case when 170 <= height then 1 end) as "L",
sum(case when height between 160 and 169 then 1 end) as "M",
sum(case when 160 > height then 1 end) as "S"
from user
group by sex
+-------+----+----+----+
| 性別 | L | M | S |
+-------+----+----+----+
| 男性 | 20 | 30 | 10 |
+-------+----+----+----+
| 女性 | 5 | 20 | 30 |
最後に
駆け出せてないエンジニアです。
間違っていたらTwitterのDMにて教えてください。
追加で学んだことがありましたら更新します。
この記事が気に入ったらサポートをしてみませんか？