SQLまとめ

何気なく使っていたSQLについて改めて勉強しました。
知らなかったことや曖昧にしていた部分についてまとめました。




## 基本的なこと
* コメントの書き方
```sql
--コメントを書く方法
select * 
from '家計簿'
```

<br>


* 基本命令は4つだけ
    * 選択系：SELECT
    * 更新系：UPDATE,DELETE,INSERT

<br>


* asは多くの製品で省略可能
<br>


* 実際の開発環境では
 ` select * from ~ ` 
という記述方法は避ける 

    * 仕様変更に弱い
    * 処理が重くなる
 

* NULLは比較演算子で比較できない

```sql:nullの比較方法
where animal_name is null --nullの時
where animal_name in not null --nullでない時
```
<br>


* 曖昧検索
    `%：任意の0文字以上`
    `_：任意の1文字`


```sql:例
where name like '_郎'

```

>結果
○：太郎、次郎
✖︎：ケンシ郎、権三郎

* 文字列の連結
> 文字列 || 文字列


## 比較演算子について
* between
 
```sql

-- 100円 ~ 300円の商品が取得される
where item_price between 100 and 300

```


* in,not in
```sql
--どれかに合致する値を検索
where item_name in ('クラゲグミ','カナリアチョコ')


--どれにも合致しない値を検索
where item_name in ('クジラキャンディー','なまこアイス')

```

* any,all
```sql
--どれかより小さい値を検索 
where price < any(400, 500 ,1200)
--100 → OK, 1100 → OK


--どれよりもより小さい
where price < all(400, 500 ,1200)
--100 → OK, 1100 → NG
```
:::note warn
DBMSによってはサブクエリでしか使えない
:::

* 比較演算子の優先順位
> () > not > and > or


<br>

## SELECT文
* distinct
>重複を除外する

```sql:重複の除外
select distinct name, price ...
-- nameについて重複が除外される 
```
* order by
> 並び替え

```sql:複数条件の並び替え
order by 'カラム1' asc 'カラム'2 desc
-- カラム1で昇順にしたあと、同じものはカラム2で降順にする

order by 1 asc 2 desc
-- カラムの番号でもOK
```

:::note warn
カラム番号の場合selectの順番で変更される
:::

<br>


* offset, fetch

>行数の限定

```sql:行数の限定
select * 
from 'テーブル名'
offset 10 rows
fetch next 5 rows only
-- 先頭から11~15番めのみ取得
```

* union,except,intersect
>union:和集合, except:差集合, intersect:積集合

```sql:union
select 'カラム1','カラム2','カラム3'
from 'テーブルA'
union all
select 'カラム1','カラム2','カラム3'
from 'テーブルB'
-- テーブルAとテーブルBが縦に並ぶ
-- all を抜くと重複は削除される
```
`joinと違い2テーブルのデータが縦に並ぶ`

::: note warn
・列数と型が揃ってないといけない
・order by 句は最後のselectに使う。
:::

```sql:except
select 'カラム1','カラム2','カラム3'
from 'テーブルA'
except all
select 'カラム1','カラム2','カラム3'
from 'テーブルB'
-- テーブルAからテーブルBを引いたレコードが表示される
-- all を抜くと重複は削除される
```
```sql:intersect
select 'カラム1','カラム2','カラム3'
from 'テーブルA'
intersect all
select 'カラム1','カラム2','カラム3'
from 'テーブルB'
-- テーブルAとテーブルBの共通のレコードが表示される。
-- all を抜くと重複は削除される
```

## 計算式
* select句に記述できるものは3つ
> 列名
計算式
固定値

```sql:select
select price,        --列名
       price + 100,  --計算式
       'タピオカ'      --固定値
```

* update,insertでも計算式は使える
```sql:update,insert
insert into '商品'(price)
values (1000 + 105)     

update '商品'
set price = price + 100 -- 全ての金額を +100
```



* case演算子

```sql:case演算子
-- case文①
case when '値1' then '値1のときに返す値'
     when '値2' then '値2のときに返す値'....
     else 'デフォルト値'
end

-- 実例
select category
    case category when 'アイスクリーム' then 'お菓子'
                  when 'ハンバーグ'    then 'ご飯' 
    end as '食べ物の分類'
from '給食'

-- case文②
case when '条件1' then '条件1のときに返す値'
     when '条件2' then '条件2のときに返す値'
     else 'デフォルト値'
end

--実例
select price
    case when price < 100   then '安物'
         when price < 1000  then '普通'
         when price < 10000 then '高級品'
         else '最高級品'
    end as '商品の分類'
from '商品'
```

## 関数
* ストアドプロシージャ
>　複数のSQL文をプログラムのようにまとめたもの。DBMSによってはjavaやC言語で書いたりできる


* 文字列の長さを得る関数
```sql:length
select length(user_name) -- 文字数を返す
```
<br>

* 空白を除去する関数

> chra(10)に'abc'という文字列を挿入するときに、'abc____'というように10文字になるように空白が挿入される。これを除去するために使う。 
```sql:trim
select trim(user_name)   -- 左右から空白を削除
select ltrim(user_name)  -- 左から空白を削除
select rtrim(user_name)  -- 右から空白を削除
```
<br>


* 文字列を置換する関数
>replace(置換対象の文字列,置換前の部分文字列,置換後の部分文字列)
->置換処理された後の文字列

```sql:replace
update items
set '商品説明' = replace('商品説明','なのであーる','です')

'10年連続売り上げNo.1なのであーる' --変更前
'10年連続売り上げNo.1です' --変更後
```
<br>


* 文字列の一部を抽出する関数
> substring (文字列を表す列, 抽出を開始する列, 抽出する文字列)
-> 抽出された部分文字列


>substring (文字列を表す列, 抽出を開始する列, 抽出する文字列)
-> 抽出された部分文字列

```sql:substring / substr
select from items
where substring(name, 1, 3) like '%世界%'

-- １〜３文字目に'世界'が入っているものを抽出
```
<br>

* 文字列を連結する関数
> concant(文字列, 文字列[, 文字列...])
->連結後の文字列
```sql:
select concant(name,':' || '商品説明')

'宇宙グミ：最近人気です'
'猫アイス：かわいい猫のアイスです'
```

* 指定桁数で四捨五入する関数
>round(数値を表す列, 有効とする桁数)
-> 四捨五入した値

```sql:round
select round(price, -2)

380 -> 400
-- 0が小数点第一位。-2は十の位
```

* 指定桁数で切り捨てる
> trunc(数値を表す列, 有効とする桁数)
-> 切り捨てた値

* べき乗を計算する関数
> power (数値を表す列, 指数部分)
-> 計算結果

```sql:power
select poswe(price , 2)

100 -> 10,000
50  -> 2,500
```
<br>

* 現在の日付を得る関数
> current_timestamp -> (年,月,日,時,分,秒)
curren_date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  ->(年,月,日)
current_time &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;->(時,分,秒)

<br>


* データ型を変換する関数 
> cast(変換する値 as 変換する型) -> 変換後の値

```sql:cast
cast(price as varchar(20) + '円')

-- '200円' みたいな感じの文字列になる
```

<br>

* 最初に登場するNULlでない値を返す関数
> coalesce(列や式1, 列や式2, ....)
->引数のうち最初に現れたNULLでない引数
※coalesce(コアレス)


```sql:coalesce
select coalesce (null , null, 'A')  --結果は 'A'
select coalesce (null , null, null) --結果は null 
```
```sql:coalesceの応用
select coalesce (memo,'メモはnullです') 
-- memo列において、第一引数である列の値があればそのまま出力。
-- なければ第二引数である 'メモはnullです'が出力される。
```

:::note warn
全ての引数の型は一致させておく必要がある
:::

* from句のないselect句
>select 式や値
※Oracle DB とDb2はfrom句無いとエラーになる

<br>


## グループ化


* 代表的な集計関数

> SUM：合計
MAX：最大値
MIN：最小値
AVG：平均
COUNT：行数のカウント


* count(*) と count(カラム)の違い
```sql:
select count(*) -- null行もカウントする
select count(name) -- name がnullの行はカウントしない

```

* 重複を除いた集計
```sql
select count(distinct category) from items
-- deistinct で重複を取り除いてから集計する

```

* ちょっとした応用
```sql
select max(price) min(price) from items
-- 結果が1行に集約されれば良いのでmaxとmin同時に出力することも可能

select avg(coalesce(price,0)) from items
-- nullを0として集計したいときにcoalesceを使用する

```

* where と having の違い
>where：group byの前に実行
having：group byの後に実行


:::note info
havingとwhereで絞れる条件が同じなら、whereで絞った方が処理が少ない。
->group byの作業をする前にwhereで行を減らせるから　
:::
<br>


## サブクエリ


* サブクエリは3パターン　

>・単一の値(スカラー)の代わり
>・複数の値(ベクター)の代わり
>・表の値(マトリックス)の代わり