50歳からの人生 自分の好きなことやって生きていくことができるのか!? ここは「がっちゃん 」が実際に体験したことを記録し、楽しく人生を過ごせるためのヒントをさぐる研究所です!

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

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

 50歳にして、長期人材育成「情報セキュリティ管理者資格」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;   >  <=  >=  !=<>と同じ)  {列名} が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 ‘_文字;  %は複数文字だが、_は1文字抽出

 WHERE {列名} IS NULL ; NULLを検出するための演算子。

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

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

重複行を1つだけにして表示  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;

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

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

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

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;

こんな風に書くと、並べ替えもしていないので、適当な情報を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で3つ4つと繋げることもできます。

文字の連結: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番目から、2文字とってきて表示します。

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

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

名前の置き換え: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({対象列名});   ※空白も1文字でカウントします。

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

  関数の組み合わせで末尾3文字を表示: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;

四捨五入の結果を小数点第1位まで表示  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;  –上位2~4位を表示して!

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

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コマンドリストをダウンロードできるようにしておきました。よかったらこちらからダウンロードしてくださいね。内容は以下に掲載しています。

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

9b92f64f74532a60df2c2d02a10a924f

スポンサーリンク
最新情報をチェックしよう!