MySQLの操作をphpMyAdminでは行ったことがあるが、コマンドでは操作できないという脱初心者向けのコマンドによるMySQLの基本操作を紹介します。
環境はMacでMAMPを使用したものです。MySQLバージョン： 5.5.42
コマンドでMySQLを使用する準備
Macでコマンドを使用するときはターミナルを使用します。シェルはMac標準のbashを使います。
ターミナルを開いた時点ではホームディレクトリ（/User/ユーザー名）がカレントディレクトリです。
MAMPを通常インストールするとMySQLの実行ファイルの場所は「Applications/MAMP/Library/bin/」です。
本来はMySQLの実行ファイルがある場所にカレントディレクトリを移動する必要がありますが、通常はMAMPをインストールすればパスが通っていますのでカレントディレクトリはどこにあってもMySQLは実行できるはずです。
もし、パスが通ってなくて実行できない場合はcdコマンドで以下に移動します。（パスの通し方は別途解説する予定）
cd /Applications/MAMP/Library/bin/
WindowsのXAMPPの場合
WindowsにXAMPPを導入している場合は、コマンドプロンプトでカレントディレクトリを以下に移動してからMySQLにログインします。以降のSQLコマンドはMACの例と同様です。
cd C:¥xampp¥mysql¥bin
MySQLの操作方法
今回のMySQLでデータベースの作成とSQL文の練習内容は以下の通りです。
また、今回作成するデータベース名は「item」でテーブルは「beer」と「maker」の２つを作成します。テーブルの内容は以下の図の通りです。
- rootでMySQLにログイン
- 新規データベースの作成
- 新規ユーザー作成
- テーブルの作成
- SQL文の練習
- テーブル構造の変更
- テーブル結合
rootでMySQLにログイン
rootでMySQLにログインするには以下のようにします。
./mysql -u root -p
ログインに成功すると以下のようになります。
ここから先はmysqlのSQLコマンドになります。
もしMySQLからログアウトする必要が出たときは「exit」コマンドを使用します。
exit
既存のデータベースを確認するSQLコマンド
show databases;
実行結果
SQLコマンドは命令文の後をセミコロン「;」で終わります。
セミコロンをつけないで「return」キーを押すと改行になりますので注意してください。矢印が改行の意味になります。（下図）
間違えて改行した場合はそのまま次の行にセミコロンを入れて「return」キーを押して命令文を実行します。
新規データベースの作成
新規データベースを作成するには次のSQLコマンドを使います。
create database item;
重要：新規データベースを作成するときに、上記のようなコマンドでデータベースを作成するとデータに日本語が含まれていたら文字化けする可能性があります。
それを防止する為には、データベースを作成するときに、文字コードの指定と照合順序を指定しておくことです。データベースを作成してから変更するのは面倒ですから、必ずこの指定はするようにした方が良いでしょう。
文字コードと照合順序を指定して新規データベース作成
create database item default character set utf8 collate utf8_general_ci;
「character set」が 文字コードの指定です。通常は「utf8」を指定します。「utf-8」とするとエラーになります。
また、collateが照合順序になります。通常は「utf8_general_ci」とすると良いでしょう。照合順序を指定していないとリレーショナルの仕組みを使うときなどに不具合がでる可能性があります。
データベースの削除
データベース削除は慎重に行いましょう。
データベースを消すSQLコマンド
drop database item;
ユーザー作成
「dororo」というユーザーを作成して、パスワードは今回はサンプルですから「1234」とします。（実運用では推測されにくいパスワードにします）
さらに「item」データベースに権限を与えます。
grant all on item.* to dororo@localhost identified by '1234';
すでにユーザーを作成済みで権限の付与だけする場合は、 一旦rootになって以下を実行します。
grant all privileges on item.* to dororo@localhost;
別のデータベースを使用
現在「item」データベースを操作していて、同じユーザーで別の「test」データベースを操作する必要が出た場合は、USE [データベース名];コマンドを使用します。
次の例はtestデータベースに変更するコマンドです。
use test;
テーブルの作成
beerテーブル作成SQLコマンド
create table beer( id int not null primary key auto_increment, item_name varchar(255), size int, maker_ID char(10) );
makerテーブル作成SQLコマンド
create table maker( maker_ID char(10), company varchar(255), tel char(20) );
テーブルの確認SQLコマンド
show tables;
テーブルの内容を確認するSQLコマンド
desc beer;
テーブルの削除SQLコマンド
drop table beer;
SQL文の練習
SQL文を学習するにはCRUDに基づいた操作をマスターすることが基本になります。
CRUDとはシステムに必要な4つの主要機能である「生成（Create）」「読み取り（Read）」「更新（Update）」「削除（Delete）」を表したもので、データベース作成において必要になる基本機能となります。
もう少し具体的に表すと、「生成（CreateつまりINSERT文）」「読み取り（ReadつまりSELECT文）」「更新（UPDATE文）」「削除（DELETE文）」をしっかりマスターしておけば良いことになります。
＊通常SQL文の命令は慣例的に大文字で記述しますが、文法上は大文字でも小文字どちらでも構いません。今回コマンド入力は全て小文字で入力した例です。
INSERT文
INSERT文はCRUDの「C」createにあたるもので、テーブルに新しい行を追加するときに使用します。つまりデータの入力に使います。
単一のレコードを追加する場合のSQLコマンド
insert into beer(item_name,size,maker_ID) values('スーパードライ',250,'A1');
複数のレコードを追加する場合のSQLコマンド
beerテーブルに値を追加SQLコマンド
insert into beer(item_name,size,maker_ID) values('スーパードライ',250,'A1'),
('スーパードライ',350,'A1'),
('ドラフト',350,'A1'),
('ドラフト',500,'A1'),
('一番搾り',350,'K1'),
('ラガービール',350,'K1'),
('淡麗',350,'K1'),
('のどごし生',350,'K1'),
('YEBISU',350,'S1'),
('黒ラベル',350,'S1');
makerテーブルに値を追加SQLコマンド
insert into maker(maker_ID,company,tel) values('A1','アサヒビール株式会社','03-5608-xxxx'),
('K1','キリンビール株式会社','03-6837-xxxx'),
('S1','サッポロビール株式会社','0570-20xxxx'),
('ST','サントリービール株式会社','03-5579-xxxx');
SELECT文
SELECT文はCRUDのReadにあたるもので、レコード確認のSQLコマンドになります。
全てのレコードの確認方法
現実的には例えば数万のレコードを全部読み出すことになりますので、データベースにかなりの負荷がかかるコマンドです。
実際に使用することはあまりないかもしれません。
select * from beer;
一部レコードの確認SQLコマンド
こちらはWHERE句で条件を設定して特定のデータを読み出します。通常はこちらが多く使用されるものです。
select item_name,size from beer;
UPDATE文
UPDATE文はCRUDのUpdateにあたるもので、テーブルにすでにあるレコードの更新するSQLコマンドになります。
レコードの更新SQLコマンド
update beer set size = 500 where id = 1;
DELETE文
DELETE文はCRUDのDeleteにあたるもので、テーブルにすでにあるレコードを削除するSQLコマンドになります。
重要：DELETE文を条件なしで事項すると当然テーブルの全てのデータが削除されます。現実的にはこのコマンドを使用することはまずありません。条件の指定はくれぐれも慎重に行う必要があります。
条件付きのレコードの削除SQLコマンド
delete from beer where item_name = 'ドラフト' and size = 500;
様々な条件
レコードの抽出条件SQLコマンド
select * from beer where id >=5;
and条件SQLコマンド
select * from beer where id > 3 and item_name = 'ドラフト';
曖昧条件SQLコマンド
任意の複数文字 %
select * from beer where id >=1 and item_name like 'Y%';
任意の１文字 _ SQLコマンド
select * from beer where id >=1 and item_name like 'YEBIS_';
並べ替えと集計
抽出レコードの並べ替えSQLコマンド
select * from beer order by id desc;
抽出レコードの数を取得SQLコマンド
limit 取得数;
select * from beer order by item_name desc limit 2;
開始位置と取得数（開始位置は０から始まる）SQLコマンド
select * from beer order by item_name desc limit 2,2;
データの集計
レコード件数の取得SQLコマンド
select count(id) from beer;
指定フィールドの合計値SQLコマンド
select sum(id) from user;
指定フィールドの平均値SQLコマンド
select avg(id) from user;
テーブル構造の変更
テーブル名の変更SQLコマンド
alter table beer rename beers;
フィールドの追加SQLコマンド
alter table beers add (stock int);
フィールド追加後のデータ追加はinsert intoではなくupdateを使う
update beers set stock = 1 where id = 1;
フィールド名の変更SQLコマンド
alter table beers change stock stocks int;
データ型のみ変更したい場合SQLコマンド
alter table beers modify stocks int not null;
フィールドを削除SQLコマンド
alter table beers drop stocks;
テーブル結合
内部結合
指定したフィールドの値が一致したら取得
select beer.id,beer.item_name,maker.company,maker.tel
from beer
inner join maker
on beer.maker_ID = maker.maker_ID;
内部結合は指定したフィールドの値が一致した内容のみしか表示されません。
今回のサンプルでは商品テーブルの中のデータはサントリーの商品がありませんので
内部結合で結合したテーブルにはサントリーの社名は出てきません。
外部結合
テーブルの指定したフィールドの値が一致するデータに加えてどちらかのテーブルにしか存在しないデータについても取得する
leftとrightの結果の違いを確認してください。
left outer joinを使用
select beer.id,beer.item_name,maker.company,maker.tel
from beer
left outer join maker
on beer.maker_ID = maker.maker_ID;
right outer joinを使用
select beer.id,beer.item_name,maker.company,maker.tel
from beer
right outer join maker
on beer.maker_ID = maker.maker_ID;
コメントを投稿するにはログインしてください。