IT備忘録 SQL 備忘録

MySQL 自分の備忘録

投稿日:2019年9月27日 更新日:

予備知識

  • ポートのデフォルトは3306
  • 大文字小文字は区別されない。混じっていても実行できる。区別する場合はbinaryをつける(下記参照)
  • データベースごとに作業ユーザを設定するのが一般的
  • order byでnullは一番上に、外すにはwhereでis not nullを指定する
  • selectでつけた別名はorderby区でも使える

 

ログインする

useしたいデータベースがあれば引数で指定できる。下記の場合はmyapp

 

mysqlコマンドの一覧を表示する

 

現在ログインしているユーザ情報を表示する

 

データベースを表示する。

information_schema/performance_schema/mysqlはmysqlが管理しているものなので、うっかり消さないように注意すること。

 

データベースを作成する

 

データベースを削除する

 

選択されているDBを確認する

 

データベースを選択する

 

ユーザを作成・設定する

localhostはローカルホストからアクセスしてくるユーザという意味。identified byはパスワードを設定する用語。dbuser01/dbuser01にした。

権限を与える grant命令

allは全ての権限を与える。onでDB名を指定、*が全てのテーブルに対してという意味。
これでrootユーザを抜けて、一般ユーザで入ってもデータベースにアクセスできる。実際は入ってみるとshow databases;で確認しても権限を与えられたDBしか見れない。select user();で確認してもちゃんと一般ユーザの名前が表示される。

ユーザを削除する

localhostはどこからアクセスしているのかを示す。

ユーザ一覧を表示する

コマンドを書かれた外部ファイルを作る。

テキストファイルを作るのだが、拡張子をsqlにする。

ファイル名は、create_myapp.sqlと仮定して

外部ファイルを実行する

書き方は2通りでsqlにログインしていない状態から、sqlファイルがあるカレントディレクトリにCDして実行

もしくはmysqlにrootでログインしてから

コメントの書き方

テーブルを作成するSQl例

テーブルやビューの一覧を取得する

useでデータベースを選んでおいたうえでshow tables;これでviewも一緒に見ることができる。

テーブルの構造を確認する

テーブルを削除する

MySQLが扱えるデータ型

数値number(正も負も)

  • 整数はint
  • 浮動小数点はfloat
  • floatより精度の高いものはdouble

数値number(整数のみ)

  • int unsigned

文字列string

  • 固定長ならchar
  • 可変長ならvarcharかtext
  • 長さがわからない可変長はtext

商品コードのようなものはchar、メールアドレスのように長さが変わるものはvarcharなどの使い分けをするとよい。

日付型date/time

  • 日付ならdate
  • 時間ならtime
  • 日時ならdatetime ‘2017-07-22 17:22:33’のように直感的に表現できる

真偽値true/false

  • boolean true1/false0

enum型

複数の文字列から1つだけを格納できるデータ型。この中の1つしか格納できない。これ以外のデータを格納しようとすると、何も代入されない。ブランクになる。有効なデータ以外を無効にすることができる。また自動的に配列のように1,2,3の値が添え字として使えるので、whereでrank = 3のように指定することもできる。

set型

enum型は1つしか選べないが、set型は複数選ぶことができる。値を代入するときはカンマ区切りで渡す。

レコードの挿入

フィールドの制限

  • not null – nullを許さない
  • unique – 重複を許さない
  • primary key レコードを一意に特定できるフィールド
  • auto_increment 自動採番

カラムを追加する

alter table テーブル名が基本、その後にaddやdropやchangeなど何をするのかが来る。

カラムを削除する

カラムのデータ型を変える

テーブル名を変更する

usersテーブルをpersonsに変更する

大文字・小文字厳密に判定するbinary演算子

binaryをつけたら、tanakaは含まれない。

任意の文字ワイルドカード

件数を絞る

上から3件だけ表示する

取り出す行を指定する

最初の3行を除外して、3件取り出す

スコアの上位3名を表示する

レコードの更新

カンマ区切りでフィールド名と値を区切る

レコードの削除

条件式を入れないと全て消えてしまうので注意

四則演算

四捨五入関数round

切り捨て関数floor

切り上げ関数ceil

ランダム値関数rand

0.9404359906357117のような小数点数を返す

rand関数を使って抽選

文字列の長さを調べるlength関数

文字列の一部を取り出すsubstr関数

文字を全て大文字にするupper関数

文字を全て小文字にするlower関数

文字列を連結するconcat関数

別名をつけるAS

別名はorderby区でも使える

条件分岐をするif文

scoreを比較して3つ目のカラムにOKやNGを表示する。

条件分岐をするcase文

case文の最後はendで締める。asを使って別名をつける方がベター。下記はtypeという別名。

whenのところに直接書く書き方もある。

抽出結果をテーブルにする(テーブルのコピーもできる)

create tableをしつつ、asをつけて後ろにselect文。asは省略できる。

テーブルのコピー

構造だけをコピーしてテーブルを作成する

行数をカウントする

nullがあるカラムを指定するとそれは除外される。全体の行を取得する場合はcount(*)とする

合計、最小、最大、平均値を表示

重複したレコードを除いて表示 distinct

例えば、チームに分かれた選手一覧があったとして、何のチームがあるのか一覧で表示したい場合

グループ集計 group by

チームごとの合計スコア

グループ集計からの抽出 having

whereと一緒に使った場合は、where条件で集計した後にhavingで抽出することになる。

サブクエリ

集計用の別テーブルを作る手間を省ける。

抽出条件に名前をつけてテーブルのように扱うview

どうやってViewが作られたかを確認する

もしテーブルやビューがあったら削除する

一覧の処理を必ずまとめて行いたいトランザクションtransaction

start transactionからcommitという処理は必ずまとめて行われる保証がされる。ちなみに下記2行目のupdateをupdataにして、やってみると、1行目は成功して、2行目は失敗だけど、両方の数字は変わらなかった。transactionをつけなければ、確かに1行目だけ実行されてしまった。なるほど。

索引を設定する(データ抽出が早くなる効果)

ちなみにPrimaryKeyには必ず索引が作られる。索引はデータの追加や更新処理を行うたびに作り直されるので、逆にそちらの処理が遅くなるデメリットもあるので、考えて索引を付けるようにすること。アプリケーションの速度が遅い場合は索引を付けたり外したりして調整してみるのも良い。

テーブルに作られた索引を確認する

索引が使われているか確認する

索引を設置したscoreをwhere文で使うと、explainのpossible_keysで使っているのが解る。逆にnameをwhere文で使うと、explainで何も表示されない。

索引の削除

内部結合のinner join

2つのテーブルに共通のデータだけを取得する方法。カラムを指定する場合は、テーブル名を前につけるが、同じものが無い場合はつけなくてもよい。

外部結合のouter join

2つのテーブルで一致しないデータも含めてデータを取得する方法。外部結合はどちらのテーブルを軸にするかで構文が異なる。尚、outerは省略できる。

外部キー制約

ウチでいう、JNF010が無いのに、JNF020が作られるのはおかしい。それをそもそもそういった変なデータが登録できない制約をかける。設定するときは関連づけるカラムの型が一致していないとダメ。これを設定してからいわゆるJNF020側に一方的に追加した場合、エラーメッセージが表示され、登録されない。データの整合性をとるには便利な仕組み。そして外部キーが設定されていれば、削除することも難しくなる。

外部キー制約を削除

直前に挿入されたレコードのIDを調べる last_insert_id()

下記の例ではcommentsのpost_idに使っている。これはpostsのincreamentのIDと同じ値なので、直前の値を取得すればOK。comments内の1つ前のIDをincrement的に取得するのではないので注意。ちなみにこの下に同じinsert文を付けるのはダメ。すると、commentsに挿入したIDを取得してしまうから。あくまで直前のIDということ。

トリガーを使う

テーブルに処理を施したのをイベントとして処理を施す。使い方次第で、insert/update/deleteの前や後に処理を追加することができる。
afterではなくbeforeにすることで処理前のデータを取得できる。

トリガーを削除する

トリガーの一覧を表示する

\Gをつけると見やすく表示してくれる。小文字のGはエラーになる。

区切り文字;を変更する

終わったらまたdelimiterで元に戻しておくこと。

1つのトリガーで複数の処理を行う

トリガーの中で処理前や処理後のカラムの値を取得する

postsテーブルをupdateした後に処理が走る。oldとnewで値を取得できる。

NOTを使う

いつもwhere文の否定は<>で判定しているが、NOTを使うのも悪くないかも。

最大で何件データを取得するかLIMIT

使い方は簡単。5件ならばLIMIT 5と入力すればいい。ORDER BYとLIMITを併用する場合は、LIMITの方を後にする。

重複データを省くDISTINCT

使い方はSELECTの後に指定するだけ。いままで括ったことなかったけど、カッコでくくるらしい。

今まで項目をまとめるという意味でGROUP BYでやっていたけど、よく考えたらDISTINCTでいいんだよな。グルーピングして金額を集計するとかそういのじゃなくて、単なる項目をリスト化するだけならこちらの方が使いやすそう。GROUP BYでまとめたけど、特に集計したい項目が無い場合はこちら。

NULLのデータはカウントされない

たとえばCOUNT関数しようと思っても、NULLがあるカラムのNULLデータはカウントされない。NULLも含めてカウントしたいのならCOUNT(*)にすること。そうすればレコード数でカウントしてくれる。

SQLの実行順序

Where->Group by->関数->Having

HAVINGの注意点

Whereで絞り込み、Groupbyで集計された後のデータをさらに絞り込む場合に使う。指定するカラムは必ずグループ化された後のカラムを使うこと。

 

 

 

 

 

 

 

データベースの照合順序を表示する

select * from information_schema.schemata;

( utf8mb4_general_ci / utf8_general_ci )

 

スポンサーリンク




スポンサーリンク




-IT備忘録, SQL, 備忘録

執筆者:


comment

Your email address will not be published. Required fields are marked *

関連記事

AS400/IBMi PDF印刷(仮)

ゴメンなさい。書きかけです。   〇〇〇〇〇.wsをテキストエディタで開いて、USERDEFAULT=Yを末行に追加すると、印刷のページ設定が触れるようになる。

AS400/IBMi 便利に思ったこと備忘録

収拾つかなくなりそうな匂いがプンプンしますが、自分のために便利だとおもったことを随時更新していきます。   論理ファイルでフィールドをつなげる CONCAT 物理ファイルで例えば支店コード2 …

Photoshopで画像が開けない。要求された操作を完了できません。プログラムエラーです。

社内で回ってきた画像をPhotoshopで開いて加工しようとしたのだが、   要求された操作を完了できません。プログラムエラーです。   と表示されて開けないことがあった。 エラー …

AS400/IBMi キー操作やファンクションキーまとめ

備忘録です。追記していきますので書きかけです。   ファンクションキーまとめ   半角カナの文字化け 半角カナが文字化けしているときがある。文字コードの変換のショートカットキーらし …

Windows NASの接続を解除したいときやってみること

NAS(ネットワークアタッチドストレージ)の、とあるフォルダに、とあるIDで一旦アクセスすると、別の権限が必要なフォルダにアクセスしなおそうとしたときに、アクセスできません!と怒られる。   …

Adsense広告


文字で検索

人気記事

なぜだか人気記事

読んでほしい順に