[toc]

データベースの基本操作

ここではデータベース名「database_name」としています。

データベースの作成(CREATE DATABASE)

CREATE DATABASE データベース名;

create database database_name;

CREATE DATABASE IF NOT EXISTS データベース名;
「IF NOT EXISTS」をつけることで、
もし「データベース名」というデータベースが存在していなかったら新しく作ってね
ということになる。

create database if not exists database_name;

データベースの削除(DROP DATABASE)

DROP DATABASE データベース名;

drop database database_name;

DROP DATABASE IF EXISTS データベース名;
「IF EXISTS」をつけることで、
もし「データベース名」というデータベースが存在していたら削除してね
ということになる。

drop database if exists database_name;

データベースの一覧を表示(SHOW)

show databases;

データベースを選択(USE)

テーブルを作る前に、データベースを選択しておく。

USE データベース名

use database_name

テーブルの作成と削除

ここでは主に「table_name」というテーブル名とします。

テーブルの一覧を表示

show tables;

データ型リスト

データ種別 区分 データ型名
数値 整数 INTEGER(INT)
少数 DECIMAL
文字列 固定長 CHAR
可変長 VARCHAR
日付、時刻 DATETIME 、 DATE 、 TIME

新規テーブルの作成(CREATE TABLE)

CREATE TABLE テーブル名 (カラム名1 カラム名1のデータ型 制約, カラム名2 カラム名2のデータ型 制約);

create table table_name (
	id int primary key,
	name varchar(20),
	age int,
	gender char(1)
);

SQL文は一行で書いても改行してもいい。
上記は「id」「name」「age」「gender」の4カラム用意。
複数カラムはカンマ(,)で区切る。
最後のカラム指定の時にカンマをつけるとエラーになるので注意。
(上記の場合、gender char(1) の後ろにはカンマはいらない)
idにprimary key（主キー）制約を設定してます。

データベースには異常な値が格納されないように、テーブル作成時に制約を定めることができる。
色々あるので以下で紹介。

初期値設定(DEFAULT制約)

カラムにデフォルト値を設定することもできる

CREATE TABLE テーブル名 (
	カラム名1 カラム名1のデータ型 DEFAULT デフォルト値,
	カラム名2 カラム名2のデータ型
);

空欄不可(NOT NULL制約)

列指定にNOT NULL制約を加えるとその列にはNULL値が格納できない。

CREATE TABLE テーブル名 (
	カラム名1 カラム名1のデータ型 NOT NULL,
	カラム名2 カラム名2のデータ型
);

UNIQUE制約

列指定にUNIQUE制約を加えるとその列の値は重複が許されない。

CREATE TABLE テーブル名 (
	カラム名1 カラム名1のデータ型 UNIQUE,
	カラム名2 カラム名2のデータ型
);

CHECK制約(MySQL8移行で対応)

CHECK制約で指定した条件と格納される値が一致しているかどうかチェックする

CREATE TABLE テーブル名 (
	カラム名1 カラム名1のデータ型 CHECK (条件式),
	カラム名2 カラム名2のデータ型
);
【例1】カラム1には「10」以上の数字しかいれない
create table table_name (
	col1 int check ( col1 >= 10 ),
	col2 char(2)
);

【例2】複数指定の場合：カラム1には「10」以上「30」以下の数字しかいれない
create table table_name (
	col1 int check ( col1 >= 10 and col1 <= 30 ),
	col2 char(2)
);

PRIMARY KEY(主キー)制約

プライマリーキーとは、行を一意に識別するための項目。
なんのこっちゃなので簡単に言うと、データを特定するための目印のようなもの。
一意とは、重複のないオンリーワンってこと。

CREATE TABLE テーブル名 (カラム名1 カラム名1のデータ型 PRIMARY KEY AUTO_INCREMENT);

※primary key の後ろに「auto_increment」をつけると自動連番になるのでinsert文から省ける。

プライマリーキーが指定されたカラムには空欄や他との重複は許されないので、大量のデータが入ったテーブルの中でただ一つのデータを特定することができる。
学校で使ってた「出席番号」がそれにあたる。
クラスの中に「同じ出席番号」の人はいなかったですよね。みんな一人ひとり別の「出席番号」が割り振られていました。

ただし、他のクラスが混じってきたら「出席番号」の被りが出てくる。
そんな時には「複合主キー」

複合主キー

上記に続き「出席番号」で話を続けます。
「出席番号」だけを主キーに設定したら、「クラスが『A』組の出席番号『１』番」と「クラスが『B』組の出席番号『１』番」で主キーが重複してしまいエラーになる。
なので、「クラス」と「出席番号」をセットにして主キーにすることで重複を避ける。

CREATE TABLE テーブル名 (
	カラム名1 カラム名1のデータ型,
	カラム名2 カラム名2のデータ型,
	カラム名3 カラム名3のデータ型,
	PRIMARY KEY(カラム名1, カラム名2)
);
create table table_name (
	studentID int,
	className char(1),
	studentName varchar(20),
	primary key(studentID, className)
);

FOREIGN KEY(外部キー)制約

親テーブルのデータを子テーブルで参照する時に、
親テーブルに存在している値を子テーブルでも使ってね。それ以外は使ったら駄目だよ。
という制約。

親テーブル
CREATE TABLE 親テーブル名 (親カラム名1 親カラム名1のデータ型 PRIMARY KEY);

子テーブル
CREATE TABLE 子テーブル名 (
	子カラム名1 子カラム名1のデータ型,
	子カラム名2 子カラム名2のデータ型,
	FOREIGN KEY (参照元の子カラム名) REFERENCES 親テーブル名(参照先の親カラム名)
);

テーブルの削除(DROP TABLE)

テーブル内のデータを削除しても、テーブル自体は残ったままです。
テーブル自体を削除する場合、

DROP TABLE テーブル名;
DROP TABLE IF EXISTS テーブル名;

drop table if exists table_name;

テーブル構造の変更(ALTER TABLE)

作成済みのテーブル構造を変更することができる。

●新しいカラムを追加
ALTER TABLE テーブル名 ADD カラム名 データ型 制約 追加する位置;

追加する位置の指定がなければテーブルの一番最後に追加される。

【例1】テーブルの最初の列に追加する場合
alter table table_name add new_col int first;

【例2】カラム名「id」の次に追加する場合
alter table table_name add new_col int after id;

●カラムを削除
ALTER TABLE テーブル名 DROP カラム名;

alter table table_name drop new_col;

データの追加と削除

データの追加(INSERT)

INSERT INTO テーブル名 (カラム名1, カラム名2, カラム名3) VALUES (カラム名1のデータ, カラム名2のデータ, カラム名3のデータ);

insert into table_name (id, name, age) values (1, "山田太郎", 24);

カラム名の数とカラムに対応したデータの数は一致していないといけない。
指定の無いカラムにはデフォルト値が入る。
テーブルに含まれるすべてのカラムに対してデータを追加する場合、下記のようにカラム名の指定を省略できる。

insert into table_name values (2, "山田花子", 12, "F");

データの更新(UPDATE)

UPDATE テーブル名 SET 変更したいカラム名 = 変更後の値 WHERE 条件式;
※WHERE句で指定しないと全てのデータが更新されてしまうので注意！

【例】idが2のデータのageの値を15に変更する
update table_name set age = 15 where id = 2;

データの削除(DELETE, TRUNCATE TABLE)

DELETE FROM テーブル名 WHERE 条件式;
※WHERE句で指定しないと全てのデータが削除されてしまうので注意！

【例】idが2のデータを削除
delete from table_name where id = 2;

テーブル内の全てのデータを削除する場合

delete from table_name;

上記でも全て削除できるが、データが1件ずつ削除されるのでデータ量が多いと時間がかかる。下記を使うとテーブル内の全てのデータを一気にまるっと削除できるので早い。

TRUNCATE TABLE テーブル名;

を使うとテーブル内の全てのデータを一気にまるっと削除できるので早い。

テーブル内のデータを削除してもテーブル自体は残ったままなので、
テーブルごと削除したい場合は「テーブルの削除(DROP TABLE)」を参考にしてください。

データの取得

登録されたデータを表示(SELECT文)

SELECT 表示させたいカラム名 FROM テーブル名;

select id, name from table_name;

全てのカラム名を指示する場合は下記のように書ける。

select * from table_name;

カラム名を別名で表示させる(AS句)

SELECT 表示させたいカラム名 AS 別名 FROM テーブル名;

select name as 名前, age as 年齢 from table_name;

データの絞り込み(WHERE句)

SELECT 表示させたいカラム名 FROM テーブル名 WHERE 条件式;

【例1】カラム名「age」が 12 のデータを取得する場合
select * from table_name where age = 12;

【例2】「10歳以上の女性」のデータを取得する場合
select * from table_name where age >= 10 and gender = "F";

データの並べ替え(ORDER BY句)

SELECT 表示させたいカラム名 FROM テーブル名 ORDER BY 並べ替えたいカラム名 並べ替えの順番;

並べ替えの順番は「ASC」なら昇順、「DESC」なら降順になる。
記述の省略も可能で、省略した場合はASCになる。
カンマ区切りで複数ソートも可能

【例1】カラム名「gender」のソート
select * from table_name order by gender;

【例2】カラム名「gender」のソート後、カラム名「age」を降順でソート
select * from table_name order by gender, age desc;

指定した件数だけデータを取得(LIMIT句)

SELECT 表示させたいカラム名 FROM テーブル名 LIMIT 取得数;

【例1】最大5つのデータを取得
select * from table_name limit 5;

【例1】3番目のデータから最大5つのデータを取得
select * from table_name limit 2, 5;

重複したレコードを除外(DISTINCT)

SELECT DISTINCT 表示させたいカラム名 FROM テーブル名;

演算子リスト

演算子 使用例 意味
= a = b a と b は等しい
<> a <> b a と b は等しくない
< a < b a は b よりも小さい(bを含まない：未満)
> a > b a は b よりも大きい(bを含まない：超過)
<= a <= b a は b よりも小さいか等しい(bを含む：以下)
>= a >= b a は b よりも大きいか等しい(bを含む：以上)
IS NULL a IS NULL aはNULLである
IS NOT NULL a IS NOT NULL aはNULLでない

パターンマッチング(LIKE演算子)

WHERE句で絞り込む時に特殊文字「%」か「_」を使ってパターンマッチングをすることができる。

SELECT 表示させたいカラム名 FROM テーブル名 WHERE パターンを探したいカラム名 LIKE パターン文字列;

特殊文字
「%」 →　任意の0文字以上の文字列
「 _ 」 →　任意の1文字

【例】カラム名「name」から、aで始まり 0文字以上の任意の文字列が入った後に b がきてその後任意の1文字で終わるような文字列を探す
select * from table_name where name like "a%b_";

指定した値の範囲に収まっているか(BETWEEN演算子)

WHERE句で絞り込む時にカラムの値が指定した範囲に収まっているものを取得できる。

SELECT 表示させたいカラム名 FROM テーブル名 WHERE 絞り込みたいカラム名 BETWEEN 値A and 値B;

【例】カラム名「age」が10以上30以下に当てはまるデータを抽出。
select * from table_name where age between 10 and 30;

指定した値のリストと合致するか(IN・NOT IN演算子)

●IN演算子
複数の候補から合致するデータを抽出できる。
SELECT 表示させたいカラム名 FROM テーブル名 WHERE 絞り込みたいカラム名 IN (値1, 値2, 値3);

【例】カラム名「age」が「3,5,7」のいずれかに当てはまるデータを抽出。
select * from table_name where age in (3, 5, 7);

●NOT IN演算子
複数の候補のどれとも合致しないデータを抽出できる。
SELECT 表示させたいカラム名 FROM テーブル名 WHERE 絞り込みたいカラム名 NOT IN (値1, 値2, 値3);

条件分岐(CASE演算子)

--単純CASE
CASE 式や例
	WHEN 値1 THEN 値1の時に返す値
	WHEN 値2 THEN 値2の時に返す値
	ELSE 上記以外の時に返す値
END

--検索CASE
CASE
	WHEN 条件1 THEN 条件1の時に返す値
	WHEN 条件2 THEN 条件2の時に返す値
	ELSE 上記以外の時に返す値
END
--単純CASE
select id, name,
	case gender
		when 'M' then '男'
		when 'F' then '女'
		else '不明'
	end as '性別'
from table_name;

--検索CASE
select id, name,
	case
		when gender = 'M' then '男'
		when gender = 'F' then/ '女'
		else '不明'
	end as '性別'
from table_name;

文字列の長さを取得(LENGTH関数)

LENGTH(長さを取得する文字列)

select name, length(name) from table_name;

LENGTH関数はバイト数単位なので、マルチバイトの文字数を取得する場合は「CHAR_LENGTH」を使う。

select name, char_length(name) from table_name;

文字の置換(REPLACE関数)

REPLACE(置換対象の文字列, 置換前の文字列, 置換後の文字列)

update table_name set country = replace(country, "日本", "JAPAN");

文字の連結(CONCAT関数)

CONCAT(文字列, 文字列…)

select concat(name, "様") from table_name;

四捨五入(ROUND関数)

ROUND(数値を表すカラム, 有効桁数)

切り捨て(TRUNCATE関数)

TRUNCATE(数値を表すカラム, 有効桁数)

乗算(POWER関数)

POWER(数値を表すカラム, 何乗するかを指定)

現在の日時(CURRENT_)

●現在の日付(YYYY-MM-DD)
CURRENT_DATE

●現在の時刻(HH:MM:SS)
CURRENT_TIME

●日時
CURRENT_TIMESTAMP

代表的な集計関数

分類 関数名 内容
集計 SUM 合計値を求める
MAX 最大値を求める
MIN 最小値を求める
AVG 平均値を求める
計数 COUNT 行数をカウントする 
select sum(score) from table_name;

COUNT関数

●検索結果の行数(NULLを含む)
COUNT(*)

●検索結果の指定列に関する行数(NULLを含まない)
COUNT(列)

グループごとの集計(GROUP BY)

SELECT グループ化の基準列名,
集計関数
FROM テーブル名
(WHERE 絞り込み条件)
GROUP BY グループ化の基準列名;
select classname,
avg(score)
from table_name
group by classname;

グループ集計後の絞り込み(HAVING)

GROUP BYで集計した後はWHERE句ではなくHAVING句で絞り込む。

SELECT グループ化の基準列名,
集計関数
FROM テーブル名
(WHERE 元の表に対する絞り込み条件)
GROUP BY グループ化の基準列名
HAVING 集計結果に対する絞り込み条件;
--「score」カラムの平均値が80以上のデータを取得
select classname,
avg(score)
from table_name
group by classname
having avg(score) >= 80;

データの結合

別々のSELECT文を結合して取得(UNION)

SELECT 表示させたいカラム名 FROM テーブル名 UNION SELECT 表示させたいカラム名 FROM テーブル名;

それぞれのSELECT文で取得するデータのカラム数と、同じ位置にあるカラムのデータ型は一致している必要がある。
取得したデータが結合されると重複行は削除される。
重複行も表示したい場合はUNIONの後に ALL を指定。

select name, age from test_table
union all
select staffName, staffAge from staff_table;

左外部結合(LEFT JOIN)

SELECT 表示させたいテーブル名.表示させたいカラム名, 表示させたいテーブル名2.表示させたいカラム名2
FROM 左側の基準となるテーブル
LEFT JOIN 右側にくっつくテーブル
ON 結合の条件
select student_table.studentID, student_table.name, class_table.name
from student_table 
left join class_table
on student_table.class = class_table.classID;