初心者向けSQLクエリ リスト

割と自分に向けた記事なので、可読性などは重視していない。


■Windowsコマンドプロンプトの文字コード変更方法
文字コードをUTF8に設定する場合
chcp 65001

データベースを作成する
create database データベース名;

データベース一覧を表示する
show databases;

使うデータベースを指定する
use データベース名

現在使用しているデータベースを表示する
select database();

テーブルを作成する
create table テーブル名(カラム名1 データ型1, カラム名 データ型2...);

すべてのテーブルを表示する
show tables;

テーブルのカラム構造を表示する(desc = describe, 説明する)
desc テーブル名;

データをテーブルに挿入する
insert into テーブル名(データ1, データ2...);

カラム名を指定してデータをテーブルに挿入する
insert into テーブル名 (カラム名1, カラム名2...) values (データ1, データ2...)

複数のデータを一度にテーブルに挿入する
insert into テーブル名(カラム名1, カラム名2...) values (データ1, データ2),
(データ1,データ2...), (データ1,データ2...)...;

カラムごとのデータを表示する
select カラム名1, カラム名2... from テーブル名;

すべてのカラムのデータを表示する
select * from テーブル名;

カラムのデータ型を変更する
alter table テーブル名 modify カラム名 データ型;

カラムのデータ型を変更する（not nullを付与したい）
alter table テーブル名 modify カラム名 データ型 not null;

カラムを追加する
alter table テーブル名 add カラム名 データ型;

カラムを先頭に追加する
alter table テーブル名 modify カラム名 データ型 first;

カラム名・データ型・位置を変更する
alter table テーブル名 change 変更前カラム名 変更後カラム名 変更後データ型

カラムを削除する
alter table テーブル名 drop カラム名;

主キーを設定してテーブルを作成する
create table テーブル名(カラム名 データ型 primary key)

自動で連続した番号が割り振られるカラムを設定する(データ型には大抵INTが入りそう)
create table テーブル名(カラム名1 データ型1 auto_increment, カラム名2 データ型2...)

連続番号を初期化する
※テーブル内のレコードを全消去してレコードを追加すると、消去前の最大値+1の番号が割り振られてしまう
※テーブル内のレコードを全消去してから使う
alter table テーブル名 auto_increment=0;

カラムの初期値を設定する
alter table テーブル名(カラム名 データ型 default 初期値...);

インデックスを作成する
create index インデックス名 on テーブル名(カラム名);

インデックスを確認する
show index from テーブル名;

インデックスを確認する（カラムごとの表示）
show index from テーブル名\G  (Gは必ず大文字)

インデックスの削除
drop index インデックス名 on テーブル名;

テーブルのカラム構造とレコードをコピーしてテーブルを作成する
（auto_incrementなどは反映されない）
create table 新規テーブル名 select * from コピー元のテーブル名;

テーブルのカラム構造だけをコピーする
create table 新規デーブル名 like コピー元のテーブル名;

テーブルのすべてのレコードを、同じカラム構造の他のテーブルにコピーする
insert into コピー先のテーブル名 select * from コピー元のテーブル名;

テーブルの中身を空にする
truncate table テーブル名;

テーブルを削除する
drop table テーブル名;

指定したテーブルが存在する場合は削除する
drop table if exists テーブル名;

データベースの削除
drop database データベース名;

テーブルを残してレコードだけ削除する
delete from テーブル名;

カラム名をエイリアスにする
select カラム名 asエイリアス from テーブル名;

レコードを処理して表示する（例 10000倍してhogeというエイリアスで表示）
select カラム名*10000 as hogehoge from テーブル名;

文字列を結合して表示する
select concat(引数1, 引数2, 引数3...);

右からn文字取り出して表示する（左から：left)
select right(カラム名, n) from テーブル名;

n番目からm個取り出して表示する
select substring(カラム名, n, m) from テーブル名;

"str"をi回繰り返して表示する(iがカラムを指す場合は末尾にfrom テーブル名）
select repeat("str", i);

逆さから表示する
select reverse(カラム名) from tb1;

表示するレコード数に制限をかける
select カラム名 from テーブル名 limit レコード数;

条件に一致したレコードだけを表示する
select カラム名 from テーブル名 where 条件式;

条件によって出力するリテラルを変化
select
case
when 条件式 then 出力するリテラル
when 条件式 then 出力するリテラル...
else 出力するリテラル
end
from テーブル名;

レコードを昇順に表示する
select * from テーブル名 order by キーとなるカラム名;

レコードを降順に表示する
select * from テーブル名 order by カラム名 desc;

表示するレコードの範囲を制限する
select カラム名 from テーブル名 limit 表示するレコード数 offset 表示開始レコード;

グループごとに表示する
selectカラム名 from テーブル名 group by グループ化するカラム名;

カラムのデータをすべて修正する（既にデータがある場合は上書きされる）
update テーブル名 set カラム名 = 設定する値;

条件に一致したレコードだけ修正する
update テーブル名 set カラム名 = 設定する値 where 条件;

（INT型のカラムに対して）下位n個のカラムだけ修正する
update テーブル名 set カラム名 = 設定する値 order by INT型のカラム limit n;

(INT型のカラムに対して）昇順（asc）や降順(desc)に表示
select * from テーブル名 order by カラム名 asc;

既存のテーブルの一部をコピーして新しいテーブルを作成する
（既存のテーブルへコピーする場合は create table ではなくinsert into）
create table 新テーブル名 select * from 旧テーブル名 where カラム名 like 値;

既存のテーブルの全部をコピーして新しいテーブルを作成する（バックアップをとる）
create table 新テーブル名 select * from 旧テーブル名;

条件に一致するレコードを削除する
delete from テーブル名 where 条件;

外部ファイルからデータをインポートする(import, いｍぽーと)
load data infile "db1\\tb2.csv" into table tb2 fields terminated by ",";

複数の抽出結果をあわせて表示する
select * from テーブル名 where 条件 union select * from テーブル名 where 条件;

複数の抽出結果をあわせて表示する（重複を許容、UNION ALLを用いる）
select * from テーブル名 where 条件 union all select * from テーブル名 where 条件;

複数の抽出結果をあわせて表示する1
（内部結合 on ～の条件が真のものだけ結合して、select ～のカラムを表示)
select テーブル名.カラム名, テーブル名.カラム名... from 結合するテーブル名1 join 結合するテーブル名2 on テーブル名1.カラム名 = テーブル名2.カラム名

複数の抽出結果をあわせて表示する2
(where～の条件に一致するレコードだけ表示する)
(from～はカンマで区切ってもよいし、joinで区切ってもよい)

select テーブル名.カラム名... from テーブル名1, テーブル名2, ... where テーブル名1.カラム名 = テーブル名2.カラム名;

複数の抽出結果をあわせて表示する3
select テーブル名.カラム名... from 結合するテーブル名1, 結合するテーブル名2, ... where テーブル名1.カラム名 = テーブル名2.カラム名

共通項となるカラム名が一致しているとき、複数の抽出結果をあわせて表示する
select テーブル名.カラム名... from 結合するテーブル名1 join 結合するテーブル名2 using(カラム名);


複数の抽出結果のうち、whereで指定した条件で更に抽出する(ex. 100以上で抽出）
select テーブル名.カラム名... from テーブル名1 join テーブル名2 using(カラム名) where テーブル名.カラム名 >=100;

複数のテーブルを結合して抽出する
select * from テーブル名1 join テーブル名2 where 結合条件 join where 結合条件....;


選択したテーブルに共通項が存在しているものだけを表示→内部結合
選択したテーブルに共通項がなくても表示→外部結合

左外部結合（left）右外部結合(right)して結果を表示する
select カラム名 from テーブル名1 left join 結合するテーブル2 on テーブル1カラム名 = テーブル2カラム名;

自己結合して表示（エイリアスを用いる）
select カラム名 from テーブル名1 as エイリアス1 join テーブル名2 as エイリアス2;

自己結合して表示（エイリアスを用いる）
（asを省略して表記するがカンマで区切る）
select カラム名 from テーブル名1 エイリアス1, テーブル名2 エイリアス2, ... ;

サブクエリを使って最大値をもつレコードを表示する

select * from tb where カラム名 in (select max(カラム名) from テーブル名);


https://blanche-toile.com/web/mysql-like-search
//末尾一致検索の場合
SELECT * FROM テーブル名 WHERE フィールド名 LIKE '%文字列'
 
//前方一致検索の場合
SELECT * FROM テーブル名 WHERE フィールド名 LIKE '文字列%'
 
//部分一致検索（どこかに含まれていれば良い）の場合
SELECT * FROM テーブル名 WHERE フィールド名 LIKE '%文字列%'
 
//jで始まりtで終わる任意の長さの文字列の場合
SELECT * FROM テーブル名 WHERE フィールド名 LIKE 'j%t'