はじめに
SQLの基本的な構文をまとめてみます。
バージョン
- MySQL8.0
SQL構文
- DBを作成する
create database if not exsists DB名
character set=utf8mb4
collate=utf8mb4
encryption='N';
create database if not exsists DB名
→ DBが既に存在しない場合に作成する。
character set=utf8mb4
→ 文字コードを指定する。
collate=utf8mb4_bin
collateは照合順序と言われる。
encryotion
暗号化を意味する。
Yを指定した場合は暗号化される。
- DBの一覧を表示する
show databases;
- DBを削除する
drop database DB名;
- テーブルを作成する
create table テーブル名 (カラム 型), (カラム 型);
- テーブル一覧を表示する
show tables;
- カラムを表示する
show columns from テーブル名;
- テーブルを削除する
drop table テーブル名;
- カラムを追加する
alter table テーブル名 add column カラム 型名;
オプションで追加する場所を指定できる。
first ・・・ 一番最初に追加する。
after カラム名・・・ カラム名の後に追加する。
- カラム名を変更する
・カラム１をカラム２に変更する
alter table テーブル名 rename column カラム` to カラム2;
- 属性を変更する
alter table テーブル名 modify column カラム名 型名;
- カラムを削除する
alter table テーブル名 drop column カラム名;
- データを投入する
insert into テーブル名 values(
'データ'
'データ'
);
- 特定のカラムにデータを投入する
insert into テーブル名(カラム名) values(データ);
- テーブルデータを表示する
select * from テーブル名;
特定のカラムの場合
select カラム名 from テーブル名;
- not null制約を設定する
alter table テーブル名 modify カラム 型 not null;
- テーブルデータを削除する
trancate テーブル名;
- カラムデータを削除する
delete from テーブル名 where カラム名(pk) = データ番号
- プライマリーキーの設定
alter table modify カラム名 int primary key;
- プライマリーキーに連番を振る
alter table テーブル名 modify カラム名 int auto_increment;
- データを変更する
update テーブル名 set カラム名='変更後のデータ' where カラム名(pk) = データ番号;
where構文
- 特定のデータを表示する
select * from テーブル名 where 条件;
- 条件を
and/orを使用して絞り込む
select * from テーブル名 where 条件 and 条件;
select * from テーブル名 where 条件 or 条件;
- あいまい検索をする
前方一致
select * from テーブル名 where カラム名 "%キーワード";
後方一致
select * from テーブル名 where カラム名 "キーワード%";
部分一致
select * from テーブル名 where カラム名 "%キーワード%";
order by句
- 昇順に並び替える
select * from テーブル名 order by カラム名 asc;(省略可)
- 降順に並び替える
select * from テーブル名 order by カラム名 desc;
limit句
・表示する件数を制限する
10件のみ表示する
select * from テーブル名 limit 10;
10〜20件表示する
select * from テーブル名 limit 10, 20;
where、order by、limitを複合して条件を指定する
select * from テーブル名 where カラム名 like "データ%" order by id desc limit 10
他のテーブルからデータを取得する
前提としてこのような商品テーブルとカテゴリーテーブルの２つのテーブルがあることを想定します。
商品テーブル
| id | カテゴリー | 商品名 | 価格 |
|---|---|---|---|
| 1 | 1 | ワンピース | 8000 |
| 2 | 2 | サッカーシューズ | 5000 |
| 3 | 3 | キャップ | 2000 |
| 4 | 4 | iphone | 70000 |
- カテゴリーテーブル
| id | カテゴリー名 |
|---|---|
| 1 | 洋服 |
| 2 | 靴 |
| 3 | 帽子 |
| 4 | null |
- 内部結合でカテゴリー名を取得する
select * from 商品 inner join カテゴリー on 商品.カテゴリー = カテゴリー.id;
まず、 inner join カテゴリーで連結したいテーブル名を指定。
次に、商品テーブルのカテゴリーidとカテゴリーテーブルのidと一致するカテゴリー名を取得する。
- where句を用いた場合
select * from 商品 inner join カテゴリー on 商品.カテゴリー = カテゴリー.id where 条件;
これでカテゴリー名を取得することができました。
しかし、これでは商品のiphone は取得できません。inner joinはどちらのテーブルにも存在するものを取得するためカテゴリーに属していないiphoneは表示されません。
そこで次に、外部結合を行って表示させます。
- 外部結合でカテゴリー名を取得する
select * from 商品 left join カテゴリー
on 商品.カテゴリー = カテゴリー.id
商品 left join カテゴリー では、商品とカテゴリーを左右に並べた際に、商品テーブルにカテゴリーテーブルを結合するという意味になります。
left joinの反対にright joinがありますが、これはleft joinとは逆の動きになります。
select * from 商品 right join カテゴリー on 商品.カテゴリー = カテゴリー.id;
カテゴリーテーブルに商品テーブルを結合するという意味になり、カテゴリーテーブルを全て表示し、商品テーブルは存在した場合に表示するという動きになります。
以上です。