MySQL よく使うsql一覧

MySQLに関した覚えておきたい処理です。これはMySQLの使い方を全く知らなかった私が、最初に覚えた処理のメモを書き起こしたものです。これだけ覚えたおかげでWordPressで独自のテーブルを使うなどなど、やりたかったことがいろいろできるようになました。

起動・終了に関する操作

MySQLの起動や終了に関する処理の記述方法です。

MySQLの起動方法（通常の場合）

MySQLの起動方法はコマンドプロンプトで下記の様に入力します。

mysql -u username -p Enter →　パス入力

エラーが出るときはmysqlがサービス開始していません。コントロールパネル→管理ツール→サービス→MySQL57を選択し、サービスの開始を押してMySQLのサービスを開始します。

MySQLの起動方法（xamppのMySQLに接続する場合）

xamppでPC内にローカルサーバーを構築している場合、xampp配下にあるMySQLにつなぐには以下の手続きを行います。

1. コマンドプロンプトから実行するにはpath（mysql.exeの場所）を指定する必要がある
2. 「コントロールパネル」→「システムとセキュリティ」→「システム」→「システムの詳細設定」で設定ウィンドウが開く
3. 「詳細設定」タブの「環境変数」
4. 「ユーザー環境変数」の「Path」をセレクトして「編集」ボタン
5. 「新規」をクリックし、xamppのmysql.exeがあるpathを記入（通常はC:¥xampp¥mysql¥bin）
6. コマンドプロンプトを起動し、mysql -u ユーザー名 -p Enter

MySQLの終了方法

MySQLを終了するには下記のコマンドを入力します。

quit

データベース・テーブルの全体に関する操作

データベースやテーブルの全体に関する操作の一覧です。

目的のデータベースの使用

MySQLのデータベースを操作するにはまず、下記の様に使用するデータベースを指定します。

use データベース名;

データベースの削除

データベースを削除するには下記の様にコマンドを入力します。

drop database データベース名

テーブルの一覧リストを表示

データベース内のテーブル一覧を確認するには下記の様にコマンドを入力します。

show tables

テーブル作成

新規テーブルを作成するには、上記のuseコマンドでデータベースを指定した後に、下記の様ににコマンドを入力します。

create table テーブル名 (列名 データ型, ....)

データ型には次の種類があります。→ int, double, bool, varchar, char, text, blob, datetime, date, datetime

テーブルの削除

テーブルを削除するには下記の様にコマンドを入力します。

drop table テーブル名

テーブル内の列（カラム）を表示

テーブルのカラムを確認するには下記の様にコマンドを入力します。

show clumns from テーブル名

テーブルの操作その１

テーブルそのものを操作するための処理です（その１）。基本的なものです。

テーブルの定義を変更する

テーブルの定義を変更するには以下の様にalter tableを使います。

テーブル名を変更する場合

alter table 旧テーブル名 rename 新テーブル名;

カラムを追加する場合(firstで先頭に、after カラム名 でその列の後ろに新しいカラムが追加されます)

alter table テーブル名 add カラム名 データ型 （first または after カラム名）

カラムを削除する場合

alter table テーブル名 drop カラム名

カラムのデータ型を変更する場合

alter table テーブル名 modify カラム名 データ型

カラムの順番入れ替え

alter table テーブル名 modify カラム名1 text after カラム名2;

カラムの定義を大きく変更する場合

alter table テーブル名 change カラム名 新カラム名 新データ型

行を追加

テーブル内に行を追加する場合には、下記の様にカラム名と入れる値をペアで指定します。

insert into テーブル名 (カラム名1, カラム名1, ...) values (値1, 値2, ...)

また全てのカラムに値を入れる場合は、下記の様にカラム名を省略することもできます。

insert into テーブル名 values (値1, 値2, .....)

行の削除

行を削除するには、deleteコマンドを使い、whereで削除したい行を絞り込みます。

delete from テーブル名 where 条件

行の更新

行のデータを更新するには、下記の様にsetで更新するカラムおよび値を宣言し、更新対象となる行をwhereで絞り込みます。

update テーブル名 set カラム名 = 新しい値, ... where 条件

テーブルの操作その２

テーブルそのものを操作するための処理です（その２）。これらは使わない場合もあります。

キーの設定

主キーを設定するには下記の様に列の定義設定でprimary keyと追記します。

create table テーブル名(列名 データ型 primary key);

またユニークキーの設定する場合にはunique keyと追記します。

create table テーブル名(列名 データ型 unique kye);

主キーに自動で連番を振る

テーブル作成時に主キーに自動で連番を振るには、下記の例の様に主キーを指定する時にauto_incrementを追記します。

create table users(id int aut_increment primary key);

注意事項としては、auto_incrementは主キーの列しか使用できません。

デフォルトで値を入れる

テーブル作成時にデフォルトの値を入れるには、下記の例の様にカラムを箇所でdefault ‘デフォルト値’と記述します。

create table テーブル名(カラム名 varchar(10) default 'デフォルト値');

データの抽出や表示に関する操作

データを表示したり、抽出する時に使う処理です。

データの表示

テーブル内のデータを表示するには下記の様にコマンドを入力します。

select * from テーブル名

指定のカラムのみを表示する場合は以下の様にカラム名を指定します。カラム名はカンマ区切りで複数指定することができます。

select カラム名 from テーブル名

特定の文字列を含む行を抽出する

特定の文字列を含む行を抜き出したい場合には、以下の様にWhereの後に文字列を指定します。

select 列名 from テーブル名 where '文字列'

where句の指定方法

where句で条件を指定する場合には以下の演算子が使えます。

演算子	意味
=	等しい
<>, !=	等しくない
>=, <=	以上、以下
>, <	より大きい、より小さい
between 値1 and 値2	値１以上かつ値2以下
in (値1, 値2, …)	いずれかの値と等しい
is null	データがnull
is not null	データがnullではない
条件1 and 条件2	両方の条件が真　(&&でも可)
条件1　or 条件2	どちらかの条件が真 (||でも可)
not	否定（!でも可）

あいまい検索

あいまい検索は、where句で以下の様に指定する。%は文字数制限なしの検索、_は１文字の検索

like 'Japa%'
like 'Japa_'

抽出結果の件数を限定する

抽出結果の件数を限定するには以下の様にコマンドを入力する

limit 件数
limit 開始行, 件数

２つ目の指定方法を使う場合は、テーブルの最初の行が0行目であることに注意する。

抽出結果のソート順を指定する

抽出結果のソート順を指定するには抽出するコマンドの最後にborder byで指定する

order by 列名 ascまたはdesc

予約語抽出条件等に使うには

予約語とは、limit, select などもともとSQLのコマンドとして使用方法が決まっている語のこと。これら予約語と同じ文字列を抽出条件等で使用するには下記の例の様にクオートで囲います。

where カラム名 = 'Limit'

グループ化や集計をする場合の記述方法

テーブルのデータをグループ化したり、値を集計したりするときの記述方法です。

値を計算する

値を計算するには、以下の例の様に列名に四則演算などの計算式を記述します。

select 列名/1000 from テーブル名;

↑この例では、指定した列名の値を1000で割った結果が抽出結果として表示されます。

データの集計

データを集計するための標準的なコマンドは以下の通りです。

コマンド	意味
avg(列名)	平均値を集計
count(列名)	行数をカウント count(*)で全行数
max(列名)	最大値を求める
min(列名)	最小値を求める
sum(列名)	合計を集計

グループ化して集計する

グループごとに集計したい場合は、select文および集計コマンドの後に続けてgroup byでグループ化します。

select agv(列名) group by グループ化したい列名

また、上記のコマンドの最後にwith rollupと付け足すと抽出結果の最後に総計も表示されるようになります。

データを取捨選択した後でグループ化する

注意：havingとの使い分けに注意

テーブルからある条件でデータを抽出した後にグループ化するには以下の様にwhereで条件を指定した後にgroup byでグループ化します。

select カラム名1, sum(カラム名1) from テーブル名
where カラム名1 > 1000
group by カラム名2;

次に説明するhavingとの違いに注意してください

グループ化後のテーブルから条件で取捨選択する

グループ化した後で、条件をしていして取捨選択するには下記の様にgroup byでグループ化した後にhavingで取捨選択します。

select カラム名1, sum(カラム名1) as 抽出後のカラム表示名 from テーブル名
group by カラム名1
having 抽出後のカラム表示名 > 1000

前項のwhereでの抽出との違いに注意してください。

日付や時刻に関する処理

日付や時刻に関する処理には以下のようなものがあります。ややこしいので、必要な時にその都度調べればよいと思います。

日時の関数

日時に関するコマンドは以下のようなものがあります。

コマンド	内容
curdate() , current_date(), current_date	現在の日付を返す
durtime() , current_time(), current_time	現在の時間を返す
now(), current_timestamp(), current_timestamp	在の日付と時間を返す
dayname()	曜日の名前を返す
datediff(日付1, 日付2)	日付1と日付2の差を返す
date_add(日付, interval 期間)	日付に期間を加える。期間にははyear, month, day, hour, minute, second　が指定できる。
date_format()	日付を指定内容（※1）に基づいてフォーマットする

※1：日付フォーマットには以下が指定できます。

指定方法	意味	表記例
%Y	年	4桁の年
%y	年	2桁の年
%b	月	Jan … Dec
%c	月	1 … 12
%m	月	01 … 12
%a	曜	Sun … Sat
%d	日	01 … 31
%H	時	01 … 23
%h	時	01 … 12

テーブルの結合

テーブルを結合してデータを抽出する場合などに使う処理や基本的な考え方です。【重要】

テーブルの結合の種類

テーブルの結合には以下の３種があります。

種類	コマンド	機能
直積結合	cross join	テーブルA×テーブルBのテーブルを作成
内部結合	inner join	一致する行のみを結合する
外部結合	left join, right join	合致しない行を含めて結合する

直積結合の書き方cross join

直接結合は以下のどちらかの書き方をします（どちらでも可）。

select * from テーブル名1.列名1, テーブル名2.列名2;
select * from テーブル名1.列名1 cross join テーブル名2.列名2;

内部結合の書き方inner join

内部結合は二つのテーブル間で指定列が一致する行だけが結合されます。下記の例の様に記述します。

select テーブル名1.カラム名1, テーブル名2.カラム名2
from テーブル名1 inner join テーブル名2
on テーブル名1.カラム名3 = テーブル名2.カラム名4;

3行目のonの箇所で、それぞれのテーブルの一致させたいカラムをイコールでつなぎます。

また、onの箇所はusing(共通列名)という記述することもできます（ただし列名と定義が同じである場合）。

外部結合の書き方left join , right join

外部結合を使うと、二つのテーブル間で指定列が合致しない行も含めて結合することができます。記述方法は上記のinner joinの代わりにleft joinまたはright joinを使います。

抽出時のカラム表記を別名にする

抽出結果の表示に、もとのカラム名と異なるカラム名で表記するには以下の例の様に書きます。複数のテーブルを組み合わせてデータを作る場合などに、分かりやすい表記名にするためなどに使います。；

select カラム名 as 表記カラム名 from テーブル名

上記の場合、抽出結果には「表記カラム名」が使われます。

抽出時にテーブルに別名を付ける

下記の例の様にテーブル名 as テーブル別名とすることで、そのクエリ内においてテーブルの別名が使えるようになります。テーブル別名を付ける理由は、複雑なクエリを組む場合に、簡単なテーブル別名にすることでクエリを見やすくするといった効果があります。

select カラム名 from テーブル名 as テーブル別名

外部制約キー（親テーブルに存在する値のみ入力可能とする）

リレーショナルデータベースを作る場合などに、テーブルに入力可能な値を、別のテーブル（ここでは、親テーブルと呼びます）に存在する値のみ許可する場合には、テーブル作成時に以下の様に指定します。

create table student (name varchar(10), classID int, foreign key (classID) references class(ID));

上記の例では、生徒テーブルのクラスＩＤは、親テーブルであるクラステーブルのID列に存在する値のみ入力可となります。

クエリの結果を別のクエリで使う

クエリの結果を別のクエリで使う(多段にする)には、下記の例の様に、whereやfromなどの記述部にサブクエリを()閉じで記述します。

select person, age from members
where age = (select max(age) from members where location='Japan');

---

いやー、結構多いですね。でもこれだけ覚えるとMySQLで結構いろいろなことができるようになると思いますよ。