プログラ生活

プログラム初学者のためのポイントを書いていこうと思います。たまに脇道それた記事もありますが、息抜きだとおもって気長にお付き合いください。

【SQL Server(連載)】SELECT文 (テーブルのコピー) -11-

www.pon-x.jp

前回の続き

今回は、テーブルのコピーの仕方を紹介したいと思います。

INTO句

テーブルのコピーはINTO句を使って行うことができます。

こんな感じです。

SELECT [カラム]
INTO [追加したいテーブル]
FRON [元のテーブル]

では、ためしにPerson.PersonをPerson.Person_Copyという名前でコピーしてあげます。

SELECT * 
INTO Person.Person_Copy 
FROM Person.Person

こんかかんじで処理件数が表示されます。
f:id:hanabusa-snow:20201126082742p:plain

確認してみます。

SELECT * 
FROM Person.Person_Copy 

f:id:hanabusa-snow:20201126082846p:plain

たしかに、コピーができていました。

応用して、カラムを選択したり、WHEREやJOINを用いたりしてもOKです。

今回はここまで。

【SQL Server(連載)】SELECT文 (部分一致検索) -10-

www.pon-x.jp

前回の続き

今回は、WHERE句において、部分一致検索の方法を紹介したいと思います。

部分一致検索

WHERE句を使って、必要なレコードを抽出する方法はすでに紹介しました。

www.pon-x.jp

しかし、これはイコールをつかって、完全一致する条件でレコードを抽出する方法でした。
一方で、特定の文字列を含むといったり、ある文字から始まるといった条件でレコードを抽出したいといったことがよくあります。

今回は、そんなときに便利な方法を紹介します。

LIKE演算子

LIKE演算子を使うと、部分一致検索を可能にします。

では、Person.PersonPhoneテーブルにおいて、PhoneNumberに「かっこ」が入っているレコードを抽出してみましょう。

SELECT * 
FROM Person.PersonPhone
WHERE PhoneNumber LIKE '%(%'

結果は以下の通り。 f:id:hanabusa-snow:20201125090556p:plain

ここで、「%」をつかっているのがわかります。
「%」は、文字数関係なくなにかしらの文字列があるということを示します。
ですので、LIKE '%(%' というのは、「(」の前後に何かしら文字列があるレコード(なくてもよい)を抽出するのです。




では、次に先頭が「0」のレコードを抽出してみましょう。
LIKE '1%'としてあげます。
詳しくは以下の通り。

SELECT * 
FROM Person.PersonPhone
WHERE PhoneNumber LIKE '1%'

結果は以下の通り。
f:id:hanabusa-snow:20201125091003p:plain







いかがだったでしょうか。
便利なLIKE演算子をぜひご活用あれ!!

【SQL Server(連載)】SELECT文 (存在チェック) -9-

www.pon-x.jp

前回の続き

前回は、サブクエリを勉強しました。
今回は、サブクエリを使って、他のテーブルにデータが存在するかという確認をもって、抽出条件を決める方法を紹介します。

EXISTS

EXISTSは、データが存在するかを確認するものです。
ちなみに、データが存在しないかを確認する場合は、NOT EXISTSを用いればよいということになります。

では、早速やってみようというまえに外部結合の回をおもいだしてください、

www.pon-x.jp

ここで、こんなSQLをかきました。

SELECT * 
FROM Person.Person PP
    LEFT JOIN Sales.PersonCreditCard PC
        ON  PP.BusinessEntityID = PC.BusinessEntityID
WHERE PC.BusinessEntityID IS NULL

これの出力が854件でしたね。

これについて、EXISTSを使って同様の出力を得ることができます。

SELECT * 
FROM Person.Person PP
WHERE 
    NOT EXISTS(
        SELECT * 
        FROM Sales.PersonCreditCard PC 
        WHERE PP.BusinessEntityID = PC.BusinessEntityID
        )

出力は以下の通り。
f:id:hanabusa-snow:20201124085336p:plain
854件ですね。







以上、今回はここまで。

【SQL Server(連載)】SELECT文 (サブクエリ) -8-

www.pon-x.jp

前回の続き

今回は、サブクエリを勉強します。
サブクエリとは入れ子ともいわれ、例えば、親のSELECT文の中に子のSELECT文をいれるなどあります。
今回は、このSELECT文を使ったサブクエリを紹介します。

FROM句をサブクエリにする。

前回、HAVINGを紹介しましたが、サブクエリを使うとHAVINGと同様な出力を得ることができます。

ちなみに、前回のクエリはこちら。

SELECT 
    PersonType,
    COUNT(BusinessEntityID)
FROM Person.Person
WHERE MiddleName IS NOT NULL
GROUP BY PersonType
HAVING COUNT(BusinessEntityID) > 100

これと同じ出力になるのはこちら、

SELECT * 
FROM 
    (
        SELECT 
            PersonType,
            COUNT(BusinessEntityID) AS CNT
        FROM Person.Person
        WHERE MiddleName IS NOT NULL
        GROUP BY PersonType
    ) TBL1
WHERE CNT > 100

FROM句に集計した結果のテーブルを作っておいて、このテーブルをTBL1という名前のテーブルとして、扱います。
これにより、集計されたカラムはWHERE句によって、抽出可能になります。

FROM句以外にも使える。

サブクエリはFROM句以外にも、どこでも扱えます。
ためしに、カラムをサブクエリにしてあげます。

SELECT 
    BusinessEntityID,
    (
        SELECT PhoneNumber 
        FROM Person.PersonPhone PPP 
        WHERE PPP.BusinessEntityID = PP.BusinessEntityID
        ) AS PhoneNumber
FROM Person.Person PP

出力は以下の通り
f:id:hanabusa-snow:20201123202719p:plain
2列目カラムについて、Person.PersonPhoneのBusinessEntityIDをキーにしてレコードを抽出し、PhoneNumberを表示します。

サブクエリはテーブルで返せるものと、スカラで返さなくてはならないものの違いがある。

以下はエラーになります。

SELECT 
    BusinessEntityID,
    (
        SELECT PhoneNumber 
        FROM Person.PersonPhone PPP 
        ) AS PhoneNumber
FROM Person.Person PP

f:id:hanabusa-snow:20201123202915p:plain
FROM句に入れるサブクエリと違うのは、カラムとして出力させるうえでは、結果がスカラ(単一)になる必要があります。
上記のSQLでは、複数の結果を返すので、エラーになります。







以上、今回はここまで。

【SQL Server(連載)】SELECT文 (グループ化) -7-

www.pon-x.jp

前回の続き

今回は、グループ化をやりたいと思います。
以前に、DISTINCTを使いましたが、それとはちょっと違います。
ちなみにDISTICTは、こんな風に書きました。

SELECT DISTINCT PersonType FROM [Person].[Person]

出力はこんな感じ
f:id:hanabusa-snow:20201122215735p:plain

重複を削除するのが、DISTINCTでしたね。

GROUP BY

今回やるのは、重複を削除したうえで、集計をすることを目的とします。
この際集計関数というのがあり、例えば
1. COUNT()・・・データ数 1. SUM()・・・合計 1. AVG()・・・平均 などなどたくさんあるのですが、どれを使うのも書き方は変わらないので、ひとまず今回はCOUNT()を使っていきます。

注意するのは、なにをグループ化して、何を集計するかということです。
この際、COUNTはデータの個数なので、カラムが文字型でも集計できますが、SUMやAVGは数値型でないとエラーになるので注意してください。

では早速やってみよう。
Person.Personテーブルより、PersonTypeのデータごとに、BusinessEntityIDがいくつあるかを集計します。
この際、データの個数はNULL以外を数えるため、データの中身自体は意味がありません。

SELECT 
    PersonType,
    COUNT(BusinessEntityID)
FROM Person.Person
GROUP BY PersonType

出力はこんな感じです。 f:id:hanabusa-snow:20201122215741p:plain
グループ化するカラムをGROUP BYで表してあげる必要があります。

WHEREと組み合わせる際は、GROUP BYより先に書く

例えば、データをWHERE句で限定させたうえで集計させたいなんてことはよくある。
この際、GROUP BYより先にWHERE句を書いておかないとエラーになります。
こんなかんじ

SELECT 
    PersonType,
    COUNT(BusinessEntityID)
FROM Person.Person
WHERE MiddleName IS NOT NULL
GROUP BY PersonType

出力はこんな感じ f:id:hanabusa-snow:20201122215749p:plain
さっきより、件数が減りましたね。

集計結果から出力を制御したいときはHAVINGを使う。

集計結果から、出力させるレコードを限定させるときはHAVINGを使います。
使い方はこんな感じ。

SELECT 
    PersonType,
    COUNT(BusinessEntityID)
FROM Person.Person
WHERE MiddleName IS NOT NULL
GROUP BY PersonType
HAVING COUNT(BusinessEntityID) > 100

出力はこんな感じ
f:id:hanabusa-snow:20201122215754p:plain
集計結果が、100より大きいレコードだけになりましたね。







以上、今回はここまで。

【SQL Server(連載)】SELECT文 (外部結合) -6-

www.pon-x.jp

前回の続き

前回は内部結合の話でした。
結合には外部結合というのがあって、今回はこれを学びます。

外部結合

まず、内部結合のおさらいですが、内部結合はテーブル同士のキーをもとに、一致するレコードだけを抽出しました。
つまり、共通部分ということになります。
図にするとこんな感じです。
f:id:hanabusa-snow:20201121223300p:plain

一方で、外部結合は、左外部結合と右外部結合と完全外部結合があります。

左外部結合

→Aすべての要素に対して、AとBの共通部分のカラムをくっつけてあげる。 f:id:hanabusa-snow:20201121223304p:plain



右外部結合

→左外部結合の逆
f:id:hanabusa-snow:20201121223530p:plain



完全外部結合

→AとBすべてのレコードを抽出。ただし、共通するキーに対してはカラム同士を結合する。
f:id:hanabusa-snow:20201121223306p:plain



左外部結合

左外部結合と右外部結合は、書き順を入れ替えるだけなので左外部結合のみあつかいます。
Person.PersonとSales.PersonCreditCardを.BusinessEntityIDで結合します。

SELECT * 
FROM Person.Person PP
    LEFT JOIN Sales.PersonCreditCard PC
        ON  PP.BusinessEntityID = PC.BusinessEntityID

結果は以下の通り。
f:id:hanabusa-snow:20201121223250p:plain みてのとおり、Aのキーに対してBのキーが見つからないカラムは結合できないので、NULLで埋められる。

これを利用して、AにあってBにないレコードを抽出してみます。
つまりここの部分です。
f:id:hanabusa-snow:20201121224122p:plain

SQLを書いてみます。 WHERE句を加えるだけです。

SELECT * 
FROM Person.Person PP
    LEFT JOIN Sales.PersonCreditCard PC
        ON  PP.BusinessEntityID = PC.BusinessEntityID
WHERE PC.BusinessEntityID IS NULL

結果はこんな感じ。
854件だとわかりました。
f:id:hanabusa-snow:20201121223254p:plain

完全外部結合

完全外部結合は、AとBのすべてのレコードを含めてくれますが、キーが一致するレコードはまとめて1レコードにしてカラムにデータをまとめてくれます。

以下のように書いてみましょう。
結合キーは全く関係ないものですが、FULL JOINだとすべてのレコードを抽出してくれますので、それぞれのテーブルのレコードの和の分だけ出力されます。

SELECT * 
FROM Person.Person PP
    FULL JOIN Sales.PersonCreditCard PC
        ON  PP.BusinessEntityID = PC.CreditCardID

結果は以下の通り。
f:id:hanabusa-snow:20201121225054p:plain
20777件出力できました。







以上、今回はここまで。
次回もまだまだSELECTです。

【SQL Server(連載)】SELECT文 (内部結合) -5-

www.pon-x.jp

前回の続きです。
今回から複数のテーブルを結合して、SELECT文を書いてみたいと思います。

内部結合

テーブル同士を結合するとは、カラム同士で同じ値を持つレコードをつなげることをいいます。
このとき、内部結合と外部結合というのが代表的で、今回は内部結合の話です。

内部結合は、異なるテーブル同士で結合するカラムを指定して、お互いに存在するレコードをみつけだしつなげてくれます。

では、内部結合する前に2つのテーブルを確認してみましょう。
まずは、Person.PersonとPerson.PersonPhoneを比較します。
以下のようにSQLを書いてください。

SELECT * FROM Person.Person 

SELECT * FROM Person.PersonPhone

以下のように出力されます。

f:id:hanabusa-snow:20201120133355p:plain
2つのテーブルにはBusinessEntityIDというのがあり、このカラムをキーにして情報を結合することができます。
(※ちなみにテーブル同士を結合する際は、テーブル定義書という設計書をもとに結合しないと誤った解釈ででーたをつくってしまうことがあるので注意しましょう。)

では、上記のキーで内部結合をします。
内部結合はFROM句のあとに、INNER JOIN でテーブル名をかき、結合するキー(つまりカラム名)をONの後に書きます。
詳しくは以下の通り

SELECT *
FROM Person.Person PP
    INNER JOIN Person.PersonPhone PH
        ON PP.BusinessEntityID = PH.BusinessEntityID

結果は以下の通り
f:id:hanabusa-snow:20201120133217p:plain

エイリアスをつけるとコードがかきやすい。

上記のSQLで、テーブル名の後に何かついているのが気になったかと思います。
例えばこれ => Person.Person PP
このPPをエイリアスといって、あだ名をつけていると思ってください。

これの何がいいかというと、カラムを指定するときに便利になります。
ON句に以下の通り書いてありますね。 ON PP.BusinessEntityID = PH.BusinessEntityID

Person.PersonとPerson.PersonPhoneはお互いに同名のBusinessEntityIDというカラムをもっています。
このとき、テーブル名を指定して
ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID
とかいてもよいのですが、ながったらしいので上記で決めたエイリアスを活用すると、コードが短く済むというわけなのです。

結合できるテーブルは2個とは限らない

次に、うえで内部結合したものに、さらにもうひとつのテーブルを結合してみます。
さらに結合するテーブルはPerson.PhoneNumberTypeです。
まずは、テーブルを確認してみましょう。

SELECT *
FROM Person.Person PP
    INNER JOIN Person.PersonPhone PH
        ON PP.BusinessEntityID = PH.BusinessEntityID

SELECT * FROM Person.PhoneNumberType

f:id:hanabusa-snow:20201120133222p:plain

では、PhoneNumberTypeIDで結合してみようと思います。
SQLは以下の通り

SELECT 
    PP.BusinessEntityID,
    PhoneNumber,
    Name
FROM Person.Person PP
    INNER JOIN Person.PersonPhone PH
        ON PP.BusinessEntityID = PH.BusinessEntityID

    INNER JOIN Person.PhoneNumberType NP
        ON PH.PhoneNumberTypeID = NP.PhoneNumberTypeID 

以下の通り結合できました。 f:id:hanabusa-snow:20201120133231p:plain

ソートしてみよう

上記の出力は、並び順がなにも考慮されていません。
並び替えは、最後にORDER BY [カラム名]で行えます。

例えばこんな感じ

SELECT 
    PP.BusinessEntityID,
    PhoneNumber,
    Name
FROM Person.Person PP
    INNER JOIN Person.PersonPhone PH
        ON PP.BusinessEntityID = PH.BusinessEntityID

    INNER JOIN Person.PhoneNumberType NP
        ON PH.PhoneNumberTypeID = NP.PhoneNumberTypeID 
ORDER BY 
    BusinessEntityID

f:id:hanabusa-snow:20201120133235p:plain
BusinessEntityIDでソートすることができましたね。







以上、次回は外部結合を勉強します。