授業でお世話になったMySQLコマンドリスト

どうも！　職業訓練生のがっちゃんです！

　５０歳にして、長期人材育成「情報セキュリティ管理者資格」2年コースを絶賛受講中です！

　今回は、データベースの授業でお世話になったMySQLの操作で欠かせないコマンドリストを作成してみました。これさえあれば、テーブルの作成、セレクト文でのデータ抽出操作がほぼ全てできます！って、学校の授業で、ですけどね。

　これからデータベースでMySQLの勉強をされる初心者の方へ！ご参考になればと思います。

　このページの最後に、私が授業中に使っていた「MySQLコマンドリスト」のPDFファイルを添付しています。よかったらダウンロードして使ってくださいね。

データベース・テーブルの操作

データベースの実行    use  {データベース名} ;

データベースの一覧の確認  show databases;

表（テーブル）の一覧の確認  show tables

表（テーブル）の作成：create table

 CREATE TABLE ｛table name｝(

　　{列名} {varchar(可変長)もしくはchar（固定長）もしくはinteger} (文字数),

　　{列名} {varchar(可変長)もしくはchar（固定長）もしくはinteger} (文字数),

　　{列名} {varchar(可変長)もしくはchar（固定長）もしくはinteger} (文字数),

　　　　　　　　　　　　　　　　　　　・・・・・（最後の行はカンマ不要）

※{integer}の場合は(文字数)の記入は不要。

)charset=utf8;

注意：作成したテーブルは、データベースを「更新」しないと見れません。

全ての実行文の最後はセミコロン（；）をつけることを忘れずに！

使用例：CREATE TABLE member (

                   Name varchar(64),

                   Ruby varchar(64),

                   Gender char(1),

Birth date,  ←誕生日等の日付設定の場合は date と書く。

Code integer,

Carrier varchar(10)

                     )charset=UTF8;  ←は、日本語を使用するので、UTF8で設定します。

データの登録：insert into

データの登録   INSERT INTO ｛テーブル名｝ VALUES ( ‘文字列はｼﾝｸﾞﾙｸｫｰﾃｰｼｮﾝ’,0,0,’文字列’);

  使用例：  INSERT INTO item VALUES ( ‘うまい棒’ , 20 , 50 ) ;

データの更新：update

情報の更新  UPDATE  ｛テーブル名｝ SET ｛列名｝ ＝ ｛列名｝｛更新条件｝WHERE {列名} ｛絞り込み条件｝；

※必ずWHEREを使って、条件を絞り込まないと大変なことになるので気をつけて！

使用例： UPDATE item SET stock = stock +20 WHERE stock <= 20;

使用例： UPDATE pref SET region = ‘北海道’ , pref = ‘北海道’ WHERE region is null ;

データの削除：delete

情報の削除  DELETE FROM ｛テーブル名｝WHERE {列名} ｛絞り込み条件｝；

使用例：  DELETE FROM item WHERE price < 100;

セレクト文

登録データの確認  SELECT * FROM {テーブル名}  ※ *(ｱｽﾀﾘｽｸ)は、「全部」とか「おまかせ」という意味。

  使用例：  SELECT  *  FROM  weather;

データベースから特定の列を抽出  SELECT ｛取得したい列名｝,｛列名｝・・  FROM ｛テーブル名｝；

  使用例：  SELECT  stock, name FROM item;

列の絞り込み：where

列の抽出の絞り込み  WHERE

使用例： SELECT stock FROM ｛テーブル名｝ WHERE stock > 20； stockが20より多いﾚｺｰﾄﾞを抽出

WHEREの後につける条件式

 WHERE {列名} ＜ １０；   ＞  ＜＝  ＞＝  ！＝（＜＞と同じ）  {列名} が10より小さい（等）ものを表示。

 使用例：WHERE pref ＜＞ prime ;  県名と県庁所在地の名前が違うものを抽出。この場合、数字だけでなく、文字の比較も可能です。

 WHERE ｛列名｝ BETWEEN 20 and 30;   ｛列名｝が、20以上30以下のものを表示（三項演算子）

           WHERE 20<= ｛列名｝ and {列名} <= 30; と同義です。

WHERE {列名} ＞20 or {列名} ＜＝30；  {列名}が20より多いか、{列名}が30以上（論理和）

WHERE NOT｛列名｝＞＝ 20；  ｛列名｝が、20以上ではないものを抽出

※演算子の優先順位：AND  OR  NOTの順で計算され、（）による優先順位の付け替えができます。

 WHERE ｛列名｝ IN （ ‘文字’,’文字’,0 ）;  {列名}の中の、（）内のものを抽出。

  使用例： SELECT * FROM pref WHERE region in ( ‘九州’ , ’北海道’ , ’沖縄’ );

 WHERE  NOT {列名} IN （ 0,0,’’文字,’文字’ ）； ｛列名｝の中の、（）内のもの以外を抽出。

※WHERE  {列名} NOT IN （ 0,0,’’文字,’文字’ ）；でも可能です。

 WHERE ｛列名｝ LIKE ‘%文字%’ ;  文字の一部が含まれているデータを抽出。％は前・後だけでも可。

 WHERE ｛列名｝ LIKE ‘_文字’ ；  ％は複数文字だが、＿は１文字抽出

 WHERE ｛列名｝ IS NULL ； NULLを検出するための演算子。

 ※データベースの世界では、NULLとは「判定不能」「未登録」という意味なので、特殊な演算子でないと抽出できません。 抽出できない例：  WHERE region = null ;

重複データは1つだけ表示：distinct

重複行を１つだけにして表示  SELECT  DISTINCT  {列名} FROM {テーブル名}；

  使用例(1列で抽出)：SELECT DISTINCT region FROM pref;

  使用例(2列で抽出)：SELECT DISTINCT region , pref FROM pref;

列の並び替え：order by

列の並び替え(昇順)  ORDER BY  {列名}  asc;  ※ascの場合のみ省略可能だが通例は記入する。

   使用例： SELECT * FROM weather WHERE fall <= 200 ORDER BY fall asc;

列の並び替え(降順)  ORDER BY  {列名}  desc;

   使用例： SELECT * FROM weather WHERE fall <= 200 ORDER BY fall desc;

※すべてが抽出し終わってから、並び替えをかけるので、必ず最後に書くこと！

※これらの並べ替えは、同一情報（数字等）が入っていた場合、勝手に並べているだけなので、実行するたびに並びが変わってしまいます。

次のように、２つ３つと条件を入れることも可能。これにより精度の高い並び替えになる。

ORDER BY templow desc, temphigh asc, fall desc;    ※左から順に並び替えします。

抽出条件の制限・絞り込み:limit

抽出件数の制限（絞り込み）  LIMIT  {数字};

  使用例：  SELECT * FROM weather WHERE fall <= 200 ORDER BY fall desc  LIMIT 10;

  ※ORDER BYと同じく、同一情報（数字等）が入っていた場合、勝手に並べるので信用性は低いです。

できるだけORDER BYで細かく設定してから使いましょう。

絶対作ってはいけないコマンド例：SELECT * FROM weather WHERE fall <= 200   LIMIT 5;

こんな風に書くと、並べ替えもしていないので、適当な情報を５件抜き出すことになります。

抽出件数の頭出し位置指定  LIMIT  {数字} OFFSET  {数字}；

  使用例：  SELECT・・・・ORDER BY templow desc, temphigh asc, fall desc  LIMIT  5  OFFSET  10;

  ※ゼロ始まりのカウントなので、使用例では、10と指定すると、11番目から表示します。

新たな結果を出力する列名を決める:as

結果を出力する列名の名前を決める  as {自分で決めた名前}

  使用例：  SELECT name, age, age – 1.08 as 謎の計算

 （この場合、普通なら列名が「age-1.08」となるが、asによって列名が「謎の計算」と命名された）

関数コマンド

和集合：union

和集合（複数の結果をつなげて一つの結果にする） ｛SELECT文｝  UNION  ｛SELECT文｝；

  使用例：SELECT * FROM weather WHERE city=’那覇’ UNION SELECT * FROM weather WHERE city=’札幌’；

  ※SELECT文をUNIONで３つ４つと繋げることもできます。

文字の連結：concat

文字列の連結（コンキャット）  CONCAT （{列名} ’  ‘ ,{列名} ‘  ‘ , {列名}）；  ※最後の列名に’  ‘は不要。

  使用例： SELECT name, birth, CONCAT( name, ‘  ’, ruby, ’  ‘ , birth ) as 名前とふりがなと誕生日

文字の切り取り：substr

文字列の切り取り(substring)  SUBSTR（ {対象列名} , {1から始まる文字位置} , {必要な文字数} ）；

  使用例： SELECT name SUBSTR( name, 1, 2 ) as 調整した名前 FROM member; name列の1番目から、２文字とってきて表示します。

  使用例： SELECT name SUBSTR( name, 5, 10 ) as 調整した名前 FROM member; name列の5番目から10文字とってきて表示します。

  ※空欄も１文字としてカウントされます。

名前の置き換え：replace

名前を置き換える REPLACE（{対象列名} , ‘{置き換え対象文字}’ , ‘{置き換える文字}’ ）;

  使用例： SELECT name, REPLACE( name, ‘サンタマリア’ , ‘カルロス’ ) as 置き換えた名前 FROM member WHERE name like ‘%マリア’；

  ※使用例では、name列の「サンタマリア」を「カルロス」という名前に置き換えた。サンタマリアの検索制度を高めるために、like を使用している。

氏名の中間にある空白を消す REPLACE（{対象列名} , ‘ ’ , ‘’ ）;

  使用例： SELECT name, REPLACE( name, ‘ ’ , ‘’ ) as 空白を消した氏名 FROM member;

不要な文字を消す REPLACE({対象列名} , ‘{消したい文字}’ , “”);

  使用例： SELECT adress, REPLACE( adress, ‘@example.com ’ , ‘’ )  FROM member;

文字数をバイト数でカウント：length

文字数をバイト数でカウントする(漢字・かな・カナ=3ﾊﾞｲﾄ、空白=1ﾊﾞｲﾄ)   LENGTH({対象列名})；

  使用例： SELECT name, LENGTH( name ) as 名前のﾊﾞｲﾄ数 FROM member;

文字数を文字数でカウント：char_length

文字数を文字数でカウントする  CHAR＿LENGTH（｛対象列名｝）；   ※空白も１文字でカウントします。

  使用例： SELECT name, CHAR_LENGTH( name ) as 名前の文字数 FROM member; 

  関数の組み合わせで末尾３文字を表示：SELECT name,CHAR_LENGTH(name) , subdtr( name, char_length( name )-2, 3 )FROM member;

本日の日付・時間を取り出す：current_date;  current_time;

  使用例： SELECT current_date , current_time;  ※本日の日付と時間をそれぞれの列に分けて表示します。

  使用例： SELECT current_timestamp;  ※ひとつの列に日付と時間を表示します。

本日の日付から○年後、△か月後の日付を表示  +INTERVAL ｛数字｝ YEAR；    +INTERVAL ｛数字｝MONTH；

  使用例： SELECT current_date, Current_date + INTERVAL 6 year + INTERVAL 8 month;

加算乗除：+-*/

対象列同士で、加算乗除（＋－*/）する  SELECT {列名} ＋or-or*or/ {列名} FROM ｛テーブル名｝；

  使用例： SELECT temphigh + templow FROM weather;

  使用例： SELECT （temphigh – templow）* 1.08 FROM weather;

四捨五入：round

四捨五入の結果を整数で表示  ROUND （ ｛四捨五入したい計算式｝ , 0 ）；

  使用例： SELECT ・・・, ROUND（（temphigh – templow）* 1.08 , 0 ）as 四捨五入 FROM weather;

四捨五入の結果を小数点第１位まで表示  ROUND （ ｛四捨五入したい計算式｝ , 1 ）;

  使用例： SELECT ・・・, ROUND（（temphigh – templow）* 1.08 , 1 ）as 四捨五入 FROM weather;

四捨五入の結果を10の位まで表示  ROUND （ ｛四捨五入したい計算式｝ , -1 ）;

  使用例： SELECT ・・・, ROUND（（temphigh – templow）* 1.08 , -1 ）as 四捨五入 FROM weather;

切り捨て切り上げ：truncate　　floor  ceil

切り捨て  TRUNCATE（ ｛切り捨てしたい計算式｝ , 0 ）;

  使用例： SELECT ・・・, TRUNCATE（（temphigh – templow）* 1.08 , 0 ）as 切り捨て FROM weather;

小数点以下切り捨て  FLOOR（ ｛切り捨てしたい計算式｝ ）;

  使用例： SELECT ・・・, FLOOR（（temphigh – templow）* 1.08 ）as 切り捨て FROM weather;

小数点以下切り上げ  CEIL（ ｛切り上げしたい計算式｝ ）;

  使用例： SELECT ・・・, CEIL（（temphigh – templow）* 1.08 ）as 切り上げ FROM weather;

集計関数：group by

集計関数（列に対する関数） GROUP BY ｛グループ化したい対象列名｝;

使用例： SELECT age, COUNT（age） FROM member GROUP BY age;  ※年齢別のメンバー人数を合計した。

※この場合のcountは、グループ（年齢）ごとの人数をカウントしています。GROUP BY marriage, gender, blood のように、複数設定も可能。その際は、SELECT marriage, gender, bloodと、こちらも書き足そう！

  でないと、せっかく集計した列が表示されなくなります。

集計結果の絞り込み：having

集計結果に対する絞り込み  HAVING ｛対象列名｝｛絞り込み条件｝；

  使用例：  SELECT・・・GROUP BY curry HAVING 人数 ＞＝100  ｶﾚｰの食べ方別で集計し、その人数が100人以上の集計をとってくる。

※havingは必ず、GROUP BYの直下に書くこと！

数を数える：count

数を数える COUNT（｛対象列名｝）；  ※この関数のみ*が使用可能。nullもカウントしてしまうので、使用は好ましくないことが多い。

  使用例： count ( age ) as 人数,  年齢列の人数を数える。

使用例： count(*)  as 人数,  (どこでもいいからカウント取ってきて、という意味になる)

合計・平均・最大・最小：sum  max  min  avg

合計  SUM（｛対象列名｝）；

  使用例： sum( age ),  年齢列の数字を合計する。

平均値の表示  SUM（｛対象列名｝）/ COUNT（｛対象列名｝）；

  使用例： SELECT sum(age) / count(age) FROM member;  年齢列の合計を年齢列の登録者数で割ってメンバー表の平均年齢を算出。

  使用例： SELECT pref, sum(age) / count(age) FROM member GROUP BY pref;  メンバー表の都道府県別の平均年齢を算出。

最大値・最小値  MAX（｛対象列｝）； MIN（{対象列}）；

  使用例： max( age ) , min( age )  年齢列の最高齢と最年少を表示

  ※名前の最大値・最小値も取れるが、文字列数や登録日時などで最大・最小をはかるため、あまり意味がない。

平均値  AVG（｛対象列名｝）；

  使用例： avg( age ),  年齢列の数字の平均値を表示

色々な絞り込みを組み合わせた例

順番を間違えるとエラーがでるのでご注意！

   SELECT curry,  –「カレーの食べ方」列を表示して！

      Count( age ) as 人数,

      Sum( age ) as 合計,

     Avg( age ) as 平均,  –意味はないが関数の中で計算は可能（ 例：avg( age + 100 ), ）

     Min( age ) as 最小,

     Max( age ) as 最大,

     FROM member  –memberというテーブル名から、

     WHERE age <= 40  –元々の表に対する絞り込みなので、絞り込みで生成された列（例：’合計’）を絞り込むことはできません。

     GROUP BY curry –集計項目「カレーの食べ方」列を基準に、上の関数を計算して！

     Having 人数 >= 100  –‘人数’の欄が100人以上を表示して！（集計結果に対する絞り込みはhavingで！）

     Order by 人数 desc  –結果を並び替えるので、必ず末尾に書く。

     Limit 3 offset 1;  –上位２～４位を表示して！

テーブル（元データとなる表）のバックアップを取る

CREATE TABLE member_backup  — バックアップとなるテーブル名を入力

SELECT *　FROM member;  –バックアップしたいテーブル名を入力

バックアップしたテーブル（表）から、データを呼び出して復旧させる。

INSERT INTO member  –データを復旧させたいテーブル名を入力

SELECT * FROM member_backup ;  –バックアップしていたテーブル名を入力

サブクエリ（副問い合わせ）

クエリとは「問い合わせ」という意味ですが、ここでは命令文という意味と思って下さい。SELECT文などで構成される、ひとつの命令文のことです。命令文が複数入って構成される命令文で、（）でくくられた中に入っている（内側の）命令文のことを「サブクエリ」と言います。

   SELECT member.name, member.pref,  — member表の{名前列}と{都道府県列}を表示

( SELECT pref.prime  — pref表の{prime列} を表示

FROM pref  –pref表から

WHERE pref.code = member.code ) as prime  — pref表のcode列とmember表のcode列が一致しているものを絞り込み

FROM member;

※（）の中がサブクリエ（副問い合わせ文）です。pref表の「都道府県code」と、member表の「都道府県code」が一致していれば、pref表のprime列の値（県庁所在地）を、新たに生成した「prime」列に表示。それに連動させて、member表の「name」列と「都道府県」列と、新たに生成した「prime」（県庁所在地）列を表示させる。

  都道府県codeを外部キーとして、member表に、pref表の「県庁所在地」列をドッキングさせたことになります。

  SELECT * FROM pref WHERE prime in ( SELECT weather.city FROM weather );

  weather表のcity列を抽出し、pref表のprime（県庁所在地）列と一致しているものだけ抽出

  ※絞り込み条件に、別の表に存在する情報を追加。

MySQLコマンドリスト

　最後に、私が授業で使っていた手作りMySQLコマンドリストをダウンロードできるようにしておきました。よかったらこちらからダウンロードしてくださいね。内容は以下に掲載しています。

本日も最後までお付き合いいただきまして、ありがとうございました！