【SQL入門】単一関数まとめ～文字関数編～

【今回の紹介】

仕事の都合で「oraclesql基礎」を取得することになったのでその学習メモを載せまーす

ちなみにインプットはこの書籍
【オラクル認定資格試験対策書】ORACLE MASTER Bronze[12c SQL基礎](試験番号:1Z0-061)完全詳解+精選問題集(オラクルマスタースタディガイド)

【内容】

SQL関数には多く分けて2つある
　①単一関数
対象のレコード分処理結果を返す
　②グループ関数
　　複数のレコードに対し一つの結果を返す。
　　

その中でも今回まとめたのは、単一関数の中の一つである文字関数について

■SQL関数

■単一関数(対象レコード分返す)

　
　　①文字関数:文字列を引数として文字列または数値を返す
　
　　　(1)UPPER:対象カラムを全て大文字にする
　　　　UPPER　(列名)
　
　　　(2)LOWER:対象カラムを全て小文字にする
　　　　LOWER　(列名)
　
　　　(3)INICAP:対象カラムの頭文字だけ大文字に変換し、それ以外は小文字で返す
　　　　INICAP　(列名)
　
　　　(4)CONCAT:文字列を結合する
　　　　CONCAT (列名1,列名2)
　
　　　(5)SUBSTR:指定した部分文字列をかえす(mからn文字分)
　　　　SUBSTR(列名,m,n)
　
　　　(6)LENGTH:指定したカラムの文字数をかえす
　　　　LENGTH(列名)
　
　　　(7)INSTAR:指定した文字パターンが現れる位置を返す(m文字目からn回目に一致した文字列の位置)
　　　　INSTAR (列名,検索文字,m,n)
　
　　　(8)LPAD:n文字になるように左側に指定の文字を埋め込む
　　　　LPAD　(列名,n,埋め込み文字)
　
　　　(9)RPAD:n文字になるように右側に指定の文字を埋め込む
　　　　RPAD　(列名,n,埋め込み文字)
　
　　　(10)TRIM:前後の削除文字または任意の文字をかえす
　　　　TRIM (削除パターン 削除文字 from 列名)
　　　　　削除パターン
　　　　　　①LEADING:文字列の先頭にある削除文字を削除
　
　　　　　　②TRAILIMG:文字列の末尾にある削除文字を削除

　　　　　　③BOTH:文字列の先頭及び末尾にある削除文字を削除

　　　(11)REPLACE:文字列の置き換えを行う
　

　　②数値関数:数値を引数として数値を戻す

　　③日付関数:日付値を引数とし、日付または数値を戻す

　　④変換関数:引数として受け入れた値を別のデータ型に変換する

　　⑤汎用関数:引数に対してNULL②関数処理を行う

■グル―プ関数（集計結果を1フィールド返す）

【SQL入門】クエリ（sql）実行後、DBMSでなにが行われているのか

【今回の内容】

今回の題材である

「クエリ（sql）実行後、DBMSでなにが行われているのか」

簡易なSQLを作成できるようになってからずっと疑問に思っていた事。

今回自分で調べたのでしまとめました。

情報元については以下のサイトを参考にしました。

どれもわかりやすくSQL初心者の自分でもわかりやすかったです。

■参考

　RDBについて体系的にまとまっている良サイト
　DBのアクセス方法（クエリ発行後、実行計画をたてるまで）
　シーケンシャルスキャンについて
　

【内容】

■ＳＱＬの特徴

　①非手続き型
　「なにをするか」だけ命令すればよい
　　→どこにデータがあるかやアクセス方法を知る必要はない

■ＤＢへのアクセス手続きの中身

クエリの実行計画メモ

　①クエリの渡す

　②パーサ（構文解析）が構文をチェック

　③オプティマイザ（最適化）最適化とコスト評価を行う

　　インプットはカタログマネージャ（統計情報）である
　　カタログマネージャ（統計情報）には
　　・各テーブルのレコード数
　　・インデックス
　　・列内のＮＵＬL数
　　な度が格納されている

　　※インプットが最新化されていないと最適な実行計画を作成できないことがある
　　　最新化されていることをＤＢエンジニアは確認しなくてはならない

　④プラン評価を行う
　　作成された実行計画を評価する
　　

■実行計画の中身

　実行計画の中身は、使用するDBMSによって異なる
　
　※実行計画は、以下のコマンドで確認できる
　
　Oracle　set autotrace traceonly　SQL文
　
　SQL Server　SET SHOWPLAN_TEXT ON　SQL文
　
　MySQL　XPLAIN EXTENDED　SQL文
　
　PostgreSQL　EXPLAIN　SQL文

　共通するのは
　
　①対象オブジェクト
　
　②オブジェクト操作
　
　③対象レコード数

たとえばselect * from テーブル名

といったSQLを発行し、各DBMSの実行計画をみると

シーケンシャルスキャン（順次検索）が行われていることがわかる

■メモ

①クエリ発行後の流れがわかるのはおもしろい

　普段はこういった情報がほしいというクエリを作成し発行する
　だけ。
　だが、今回はそのクエリがどういった流れで解釈され実行されていくのか
　について少し理解が進み、単純におもしろかった。
　実行計画を立てる過程において各DBMSで違いがあるのも興味深くて
　なぜそのような作りの違いが生じたのかその理念とか哲学とかに
　これから触れることができればもっとおもしろいなと思う

②インターネットにもやはり良質な情報があること

　とても当たり前なんだけど、今回改めて実感。
　最近、
　「SQL発行する後の流れが知りたい」
　「なぜクエリにより回答速度がかわるのか」
　とかに興味を持っていた
　いくつか書店を回りＤＢ関連書籍を広げてみても
　基本的なSQLの書き方やDB設計のやり方ばかりで
　なかなか自分の求める情報がなかった
　最下部に記載させていただきますが、
　かなりわかるやすい記事
　またRDBについて体系的に紹介されているサイトが存在し、
　インターネットって便利だなと。

【SQL入門】目的別のSQLメモ～集約関数と月別にレコードを取得する～

【今回の紹介】

ＳＱＬを勉強中なのでその内容をメモレベルで記載します。

ちなみに今回はこの書籍を使用しながら学習しています。

ＳＱＬに関する本は数冊みましたが、これが具体的でわかりやすかったです（初心者目線です）

現場で使えるSQL―Oracle/SQL Server対応 (DB Magazine SELECTION)

【内容】

～目的別～

■ある列の平均・合計・最大・最少を取得したい。

集約関数を使用する。集約関数とはある項目に対し集計を行うもの。

例）列名があるレコードの件数を調べる

select count(列名)　from テーブル名

同様にSUM・AVG・MIN・MAXが使用できる

メモ①カラムがＮＵＬＬの場合はカウントされないで注意

メモ②MAX・MINは日付にも適用できるようなので、最新や最も古いレコードを取得することもできる
　　　

■月別のある列のデータ数を取得したい。

DATE型またはDATETIME型で月別にレコードをグループ化する必要がある

TO_CAHR（）関数又はDATEPART型で抽出し、グループ化を行う

例）

select TO_CHAR(日付,YYYY/MM),count(列名)
　　from テーブル名
　　group by TO_CHAR(日付,YYYY/MM)

SQLを発行しやすいようにデータ型を変換できる変換関数についてはこちらの記事が分かりやすい

参考

【SQL入門】group by,distinctどちらが性能がよいのか

【今回の内容】

前回は基本的なＳＱＬを備忘録で簡単にまとめていたけど、

【SQL入門】基本的なSQLを目的別にまとめてみた - ＦＯＲ ＳＥ

その中に気になることがあったので調べたものをメモとして紹介します。

今の自分の現場では、重複行を絞り込む時に、「distinct」と「group by」を使う人それぞれいました。

distinctとgroup byは同じように重複行を消すという意味では

どちらでもいいのかなーと思っていたけど、

疑問に思ったので少し調べてみました。

【疑問】distinctとgroup by は重複行を消す目的ならどちらを使ってもよいのか？

【結論】 結果としてはどちらも重複行を消すという意味では同じらしい。

だが、一般的にgroup byは集合関数（sumとかcountとか）と一緒に使うので、

そもそもの目的用途が違うのではという話があった。

また、どちらが性能が良いかという事に関しては、賛否両論ある様子。

■distinct>group by派

　　　
　　　なし
　　　

■group by>distinct派

「distinctではソートの処理があり効率が悪い」という主張

[ThinkIT] 第8回：GROUP BYを使用したチューニング (1/2)

爆裂！Ｃ＃野郎: DISTINCT と GROUP BY

■どちらでもない派

group byとdistinct：晶紀の館 第2別館：So-netブログ

■distinctやgroup byの代りにEXISTSを使う派

基礎から理解するデータベースのしくみ（5） | 日経 xTECH（クロステック）

ＯＲＡＣＬＥ／オラクルＳＱＬリファレンス（チューニング）

個人的には、そもそもDISTINCTやgrop byの代わりにEXISTSを使うという意見がおもしろかった。

手早く目的のデータを取得したいときは、手段は問わない（どれを使ってもいい）けど、

性能改善やチューニングを行う際は、こういった知識が必要なのかなと言う意味で。

こんな入門者向けのブログを書いている人がどんな人物で、どんな人生を歩んでいるのか、もし興味があれば読んでいっていただけると幸いです。

☆入社半年の時☆
文系SE(システムエンジニア)の実態―配属後4カ月で思うこと― - ＦＯＲ ＳＥ

☆入社３年目の時☆
文系SE(システムエンジニア)の実態―三年目で思うこと― - ＦＯＲ ＳＥ

☆英語も勉強してました！

【英語】文系SEが、TOEIC350点付近⇒TOEIC850になった話 - ＦＯＲ ＳＥ

☆これから転職を考えている人向け☆

【整理】異業界異職種への退職エントリー（SEから営業へ） - ＦＯＲ ＳＥ

【必読】転職を考えたときにすること 〜IT業界向けの人材紹介会社エージェントが語る〜 - ＦＯＲ ＳＥ

【SQL入門】基本的なSQLを目的別にまとめてみた

【今回の内容】

最近、仕事で簡易なＳＱＬを使うことが多いので、よく使うものをまとめてみました。

使うといっても、かなり基本的なSQLです。

備忘録的なメモとしてまとめておきます。

【内容】

①基本的な列の取得

単一列取得したい

select カラム名　from テーブル名

複数列取得したい

select カラム名, カラム名,　from テーブル名

すべての列を取得したい

すべての列を取得：select *　from テーブル名

②列を条件で指定したい

単一条件取得したい

　　　　　　　　select カラム名　from テーブル名　where カラム名＝'条件'

複数条件取得したい

　　　　　　　　select カラム名　from テーブル名　where カラム名＝'条件' and　カラム名＝'条件'

③列名の値の一部を指定して取得

前方一致
　　　　　　　　
　　　　　　　　select カラム名　from テーブル名 like カラム名='%条件'

後方一致
　　　　　　　　
　　　　　　　　select カラム名　from テーブル名　like カラム名='条件%'

部分一致
　　　　　　　　
　　　　　　　　select カラム名　from テーブル名 like カラム名='%条件%'

④条件を指定して、複数レコードをまとめたい

　　　　select カラム名,sum(合計を出したいカラム名)　from テーブル名　where　カラム名="" group by

　　　　 重複をなくしたいカラム名

または

　　　　select distinct 重複をなくしたいカラム名　from テーブル名　where　カラム名=""

⑤レコードの表示順序を変えたい。

　　　　昇順にしたい
　　　　
　　　　　　　　　select カラム名　from テーブル名　order by 昇順に並べたいカラム名　asc

降順にしたい

select カラム名　from テーブル名　order by 昇順に並べたいカラム名　desc

⑥SQLを見やすくしたい（相関名の指定）

select * from テーブル名　略称

→次から略称.カラム名で略称の指すテーブルを指定できる。複数テーブルを結合しながら
　
発行するＳＱＬはどうしても複雑になりがちなので、テーブル名を短くし、可読性をあげる

⑦複数テーブルから情報を取得したい（テーブル結合）

■特定のカラムが両方のテーブルとも一致するレコードを取得（内部結合）

　　　　　 select カラム名　from テーブルA,テーブルB where A.カラム名=B.カラム名

　
　　　　　　またはselect カラム名　from テーブルA inner join テーブルB on( A.カラム名=B.カラム名)

■特定のカラムが一致しないものも取得したい

　
　　　　　→左のテーブルにあるレコードはすべて取得し、かつ指定したカラム名と一致する右のテーブルのレコード
　
　　　　　があれば取得する（外部結合）

　　　　 select カラム名　from テーブルA left join テーブルB on( A.カラム名=B.カラム名)

※一致しないレコードには空白を表示する

■特定のカラムが一致しないものも取得したい

　→右のテーブルにあるレコードはすべて取得し、かつ指定したカラム名と一致する左のテーブルのレコード

　　　　　　があれば取得する（外部結合）

　　　　select カラム名　from テーブルA right join テーブルB on( A.カラム名=B.カラム名)

⑧あるテーブルした取得した情報をそのまま他のテーブルに対して、問い合わせたい（副問問い合わせ）

select カラム名　from テーブル名A　where カラム名 in (select カラム名　from テーブルB where
　　　　　　　カラム名='')

→inの後のselect文の取得するカラム数が複数の場合は、「in」を使用し、単一の場合は
　　　　　　　　　「カラム名=」
　でよい！！