LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@tatatanuki(たぬき)

SQLまとめ

Posted at

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

基本的なこと

  • コメントの書き方
--コメントを書く方法
select * 
from '家計簿'

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

  • asは多くの製品で省略可能

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

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

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

nullの比較方法
where animal_name is null --nullの時
where animal_name in not null --nullでない時

  • 曖昧検索
    %：任意の0文字以上
    _：任意の1文字
例
where name like '_郎'

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

  • 文字列の連結

文字列 || 文字列

比較演算子について

  • between

-- 100円 ~ 300円の商品が取得される
where item_price between 100 and 300
  • in,not in
--どれかに合致する値を検索
where item_name in ('クラゲグミ','カナリアチョコ')


--どれにも合致しない値を検索
where item_name in ('クジラキャンディー','なまこアイス')
  • any,all
--どれかより小さい値を検索 
where price < any(400, 500 ,1200)
--100 → OK, 1100 → OK


--どれよりもより小さい
where price < all(400, 500 ,1200)
--100 → OK, 1100 → NG

DBMSによってはサブクエリでしか使えない

  • 比較演算子の優先順位

() > not > and > or


SELECT文

  • distinct

重複を除外する

重複の除外
select distinct name, price ...
-- nameについて重複が除外される
  • order by

並び替え

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

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

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


  • offset, fetch

行数の限定

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

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

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

joinと違い2テーブルのデータが縦に並ぶ

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

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

計算式

  • select句に記述できるものは3つ

列名
計算式
固定値

select
select price,        --列名
       price + 100,  --計算式
       'タピオカ'      --固定値
  • update,insertでも計算式は使える
update,insert
insert into '商品'(price)
values (1000 + 105)     

update '商品'
set price = price + 100 -- 全ての金額を +100
  • case演算子
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言語で書いたりできる

  • 文字列の長さを得る関数
length
select length(user_name) -- 文字数を返す

  • 空白を除去する関数

chra(10)に'abc'という文字列を挿入するときに、'abc____'というように10文字になるように空白が挿入される。これを除去するために使う。

trim
select trim(user_name)   -- 左右から空白を削除
select ltrim(user_name)  -- 左から空白を削除
select rtrim(user_name)  -- 右から空白を削除

  • 文字列を置換する関数

replace(置換対象の文字列,置換前の部分文字列,置換後の部分文字列)
->置換処理された後の文字列

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

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

  • 文字列の一部を抽出する関数

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

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

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

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

  • 文字列を連結する関数

concant(文字列, 文字列[, 文字列...])
->連結後の文字列

select concant(name,':' || '商品説明')

'宇宙グミ：最近人気です'
'猫アイス：かわいい猫のアイスです'
  • 指定桁数で四捨五入する関数

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

round
select round(price, -2)

380 -> 400
-- 0が小数点第一位。-2は十の位
  • 指定桁数で切り捨てる

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

  • べき乗を計算する関数

power (数値を表す列, 指数部分)
-> 計算結果

power
select poswe(price , 2)

100 -> 10,000
50  -> 2,500

  • 現在の日付を得る関数

current_timestamp -> (年,月,日,時,分,秒)
curren_date             ->(年,月,日)
current_time            ->(時,分,秒)


  • データ型を変換する関数

cast(変換する値 as 変換する型) -> 変換後の値

cast
cast(price as varchar(20) + '円')

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

  • 最初に登場するNULlでない値を返す関数

coalesce(列や式1, 列や式2, ....)
->引数のうち最初に現れたNULLでない引数
※coalesce(コアレス)

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

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

  • from句のないselect句

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


グループ化

  • 代表的な集計関数

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

  • count(*) と count(カラム)の違い
select count(*) -- null行もカウントする
select count(name) -- name がnullの行はカウントしない
  • 重複を除いた集計
select count(distinct category) from items
-- deistinct で重複を取り除いてから集計する
  • ちょっとした応用
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の後に実行

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


サブクエリ

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

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

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?