MySQL よく使うsql一覧
MySQLに関した覚えておきたい処理です。これはMySQLの使い方を全く知らなかった私が、最初に覚えた処理のメモを書き起こしたものです。これだけ覚えたおかげでWordPressで独自のテーブルを使うなどなど、やりたかったことがいろいろできるようになました。
起動・終了に関する操作
MySQLの起動や終了に関する処理の記述方法です。
MySQLの起動方法(通常の場合)
MySQLの起動方法はコマンドプロンプトで下記の様に入力します。
mysql -u username -p Enter → パス入力
エラーが出るときはmysqlがサービス開始していません。コントロールパネル→管理ツール→サービス→MySQL57を選択し、サービスの開始を押してMySQLのサービスを開始します。
MySQLの起動方法(xamppのMySQLに接続する場合)
xamppでPC内にローカルサーバーを構築している場合、xampp配下にあるMySQLにつなぐには以下の手続きを行います。
- コマンドプロンプトから実行するにはpath(mysql.exeの場所)を指定する必要がある
- 「コントロールパネル」→「システムとセキュリティ」→「システム」→「システムの詳細設定」で設定ウィンドウが開く
- 「詳細設定」タブの「環境変数」
- 「ユーザー環境変数」の「Path」をセレクトして「編集」ボタン
- 「新規」をクリックし、xamppのmysql.exeがあるpathを記入(通常は
C:¥xampp¥mysql¥bin
) - コマンドプロンプトを起動し、
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 テーブル名
テーブルの操作その1
テーブルそのものを操作するための処理です(その1)。基本的なものです。
テーブルの定義を変更する
テーブルの定義を変更するには以下の様に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 条件
テーブルの操作その2
テーブルそのものを操作するための処理です(その2)。これらは使わない場合もあります。
キーの設定
主キーを設定するには下記の様に列の定義設定でprimary keyと追記します。
create table テーブル名(列名 データ型 primary key);
またユニークキーの設定する場合にはunique keyと追記します。
create table テーブル名(列名 データ型 unique kye);
補足:主キーとユニークキーの違い
主キーの列はNullが許されず、全てのデータが重複不可。ユニークキーの列はNullが許され(Nullは重複可)、Null以外のデータは重複不可。
主キーに自動で連番を振る
テーブル作成時に主キーに自動で連番を振るには、下記の例の様に主キーを指定する時に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 | 値1以上かつ値2以下 |
in (値1, 値2, …) | いずれかの値と等しい |
is null | データがnull |
is not null | データがnullではない |
条件1 and 条件2 | 両方の条件が真 (&&でも可) |
条件1 or 条件2 | どちらかの条件が真 (||でも可) |
not | 否定(!でも可) |
あいまい検索
あいまい検索は、where句で以下の様に指定する。%は文字数制限なしの検索、_は1文字の検索
like 'Japa%'
like 'Japa_'
抽出結果の件数を限定する
抽出結果の件数を限定するには以下の様にコマンドを入力する
limit 件数
limit 開始行, 件数
2つ目の指定方法を使う場合は、テーブルの最初の行が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 |
テーブルの結合
テーブルを結合してデータを抽出する場合などに使う処理や基本的な考え方です。【重要】
テーブルの結合の種類
テーブルの結合には以下の3種があります。
種類 | コマンド | 機能 |
---|---|---|
直積結合 | 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を使います。
left joinとright joinの違い
- left joinは、fromの後に書かれたテーブルを中心に結合する。
- right joinは、joinの後に書かれたテーブルを中心に結合する。
混在して使用せずに、leftかrightかどちらかに統一した方が覚えやすいです。
抽出時のカラム表記を別名にする
抽出結果の表示に、もとのカラム名と異なるカラム名で表記するには以下の例の様に書きます。複数のテーブルを組み合わせてデータを作る場合などに、分かりやすい表記名にするためなどに使います。;
select カラム名 as 表記カラム名 from テーブル名
上記の場合、抽出結果には「表記カラム名」が使われます。
抽出時にテーブルに別名を付ける
下記の例の様にテーブル名 as テーブル別名とすることで、そのクエリ内においてテーブルの別名が使えるようになります。テーブル別名を付ける理由は、複雑なクエリを組む場合に、簡単なテーブル別名にすることでクエリを見やすくするといった効果があります。
select カラム名 from テーブル名 as テーブル別名
外部制約キー(親テーブルに存在する値のみ入力可能とする)
リレーショナルデータベースを作る場合などに、テーブルに入力可能な値を、別のテーブル(ここでは、親テーブルと呼びます)に存在する値のみ許可する場合には、テーブル作成時に以下の様に指定します。
create table student (name varchar(10), classID int, foreign key (classID) references class(ID));
上記の例では、生徒テーブルのクラスIDは、親テーブルであるクラステーブルのID列に存在する値のみ入力可となります。
クエリの結果を別のクエリで使う
クエリの結果を別のクエリで使う(多段にする)には、下記の例の様に、whereやfromなどの記述部にサブクエリを()閉じで記述します。
select person, age from members
where age = (select max(age) from members where location='Japan');
いやー、結構多いですね。でもこれだけ覚えるとMySQLで結構いろいろなことができるようになると思いますよ。
コメント
記事No.403