どうも! 職業訓練生のがっちゃんです!
50歳にして、長期人材育成「情報セキュリティ管理者資格」2年コースを絶賛受講中です!
今回は、データベースの授業でお世話になったMySQLの操作で欠かせないコマンドリストを作成してみました。これさえあれば、テーブルの作成、セレクト文でのデータ抽出操作がほぼ全てできます!って、学校の授業で、ですけどね。
これからデータベースでMySQLの勉強をされる初心者の方へ!ご参考になればと思います。
このページの最後に、私が授業中に使っていた「MySQLコマンドリスト」のPDFファイルを添付しています。よかったらダウンロードして使ってくださいね。
- 1 データベース・テーブルの操作
- 2 セレクト文
- 2.1 列の絞り込み:where
- 2.2 重複データは1つだけ表示:distinct
- 2.3 列の並び替え:order by
- 2.4 抽出条件の制限・絞り込み:limit
- 2.5 新たな結果を出力する列名を決める:as
- 2.6 関数コマンド
- 2.6.1 和集合:union
- 2.6.2 文字の連結:concat
- 2.6.3 文字の切り取り:substr
- 2.6.4 名前の置き換え:replace
- 2.6.5 文字数をバイト数でカウント:length
- 2.6.6 文字数を文字数でカウント:char_length
- 2.6.7 本日の日付・時間を取り出す:current_date; current_time;
- 2.6.8 加算乗除:+-*/
- 2.6.9 四捨五入:round
- 2.6.10 切り捨て切り上げ:truncate floor ceil
- 2.6.11 集計関数:group by
- 2.6.12 集計結果の絞り込み:having
- 2.6.13 数を数える:count
- 2.6.14 合計・平均・最大・最小:sum max min avg
- 2.7 色々な絞り込みを組み合わせた例
- 3 テーブル(元データとなる表)のバックアップを取る
- 4 バックアップしたテーブル(表)から、データを呼び出して復旧させる。
- 5 サブクエリ(副問い合わせ)
- 6 MySQLコマンドリスト
データベース・テーブルの操作
データベースの実行 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