【備忘録】SQL　4月3週

ど初心者です。間違いに気付いたら適宜直し、書ききれなかったことは加筆もしていきます。

## SQLとは
クエリ（DBMSに対する問合せ）を実行するための言語

## 句・文など
### select：列を指定 
### from：テーブルを指定
~~~ SQL
-- usersというテーブルのname列を取得
select name
from users;
~~~
~~~ SQL
-- usersテーブルの全列を取得
select * 
from users; 
~~~

### where：取得する**行**に条件をつける
~~~ SQL
select *
from users
where age <= 30; -- 30歳以下のユーザを取得
~~~
#### 演算子一覧
`=`：等号
`<`と`>`：大小比較（日時などにも使用可能）
`!=`：不等号（`<>`と書くことも可能）
`in`：リストの１要素と一致するものだけ取得
`between a and b`：aからbまでの範囲
`like`：ワイルドカードに一致するものを取得
##### `in`の使用例
~~~ SQL
select *
from users
where age in (20,30,40); -- 年齢が20歳、30歳、40歳のいずれかであるユーザを取得
~~~
##### `like`演算子で使えるワイルドカード
`%`：任意の文字列
`_`：任意の１文字
~~~ SQL
select *
from users
where name like '田中%'; -- ワイルドカードを使って「田中」から始まるユーザを取得
~~~
##### 複数条件に一致させたい場合
`AND` `OR` `NOT`などを使用する
~~~ SQL
select *
from users
where name like '__%' AND age <= 20; -- 20歳以下でかつ名前が2文字以上のユーザを取得
~~~

### group by：列名を指定しその列の値ごとにグループを作成
作成したグループに対して、**集約関数**をかけることができる

#### 集約関数
##### 基本（よく使いそう）
`sum`：合計 、`avg`：平均、`min`：最小値、`max`：最大値
##### 便利なやつ
`count`：行数を取得
`concat`：結合した文字列を返す
`round`：四捨五入した値を返す

##### 使用例
~~~ SQL
-- prefecture（県）ごとに売り上げ数（amount）の平均を表示
select prefecture, avg(amount)
from purchase_logs
group by prefecture;
~~~
~~~ SQL
-- prefecture（県）ごとに売り上げ数（amount）が50以上の日数（行数）をカウント
select prefecture, count(amount)
from purchase_logs
where amount > 50
group by prefecture;
~~~

### having：集約した結果に対して条件を付ける
~~~ SQL
-- カテゴリ別で平均価格が200円以上の商品を取得
select category,avg(price) 
from sales
group by category 
having avg(price) > 200;
~~~

### order by：並び順を指定
`asc`：昇順（デフォルト）
`desc`：降順
~~~ SQL
-- 降順で並べる
select *
from sales
order by price desc;
~~~
::: note warn
何も指定しなくてもソートされるが、環境やバージョンによって仕様は変わるため、
並び順が重要な時は、指定しておくべき！
:::

### limit：データの取得数を制限する
条件に一致するデータの取得数を制限することができる
サーバへの負担などを考えると、実用上、最も重要となる
※1000件くらいにしておくのが普通
~~~ SQL
-- 大量のデータが取得される場合でも、1000行までしか取得されない
select *
from billion_logs
limit 1000;
~~~

## 記述順と実行順
`select`の位置だけずれている
|  | 記述順 | 実行順 |
|:-:|:-:|:-:|
| 1 | **select**  | from |
| 2 | from | join系 |
| 3 | join系 | where |
| 4 | where | group by |
| 5 | group by | having |
| 6 | having | **select** |
| 7 | order by | order by |
| 8 | limit | limit |

## 結合処理
２つのテーブルを結合する処理
内部結合と外部結合の２種類がある
### inner join：内部結合（2つの列どうしで一致している行だけ出力）
~~~ SQL
-- productsテーブルとsalesテーブルをproductsのidとsalesのpidで結びつけて結合
select *
from products
inner join sales
    on products.id = selas.pid;
~~~

### outer join：外部結合（一致していない行も出力）
内部結合では、一致しない行があること（片方にしか存在しない値）を出力しない
一方、外部結合では、どちらか一方のテーブルにしかない値も出力する
そのため、どっちに合わせるかで２種類のオプションがある
`left outer join`：左のテーブルに合わせる（`left join`と書いても同じ）
`right outer join`：右にテーブルに合わせる（`right join`と書いても同じ）
~~~ SQL
-- productsテーブルとsalesテーブルをproductsのidとsalesのpidで結びつけて結合
-- ※salesにない商品もテーブルに表示される
select *
from products
left join sales
    on products.id = selas.pid;
~~~

疲れたので詳しくは→[結合処理の参考リンク](https://itsakura.com/sql-left_outer_join)

## テーブルの操作
### create：テーブルを新規作成
~~~ SQL
-- booksという新しいテーブルを追加
-- 列はidとtitleの2種類。列の設定は以下のような意味を持つ
-- idは「int型」「非NULL」「自動で1から順に整数値が入る」「主キー」
-- titleは「255文字までの可変char」「非NULL」
create table books(id int not null auto_increment primary key, title varchar(255) not null);
~~~

### insert：テーブルに行を挿入
`values`の後ろにカンマ区切りで複数並べれば複数挿入することが可能
~~~ SQL
-- id=35,name=田中の行と、id=99,name=九十九の行をusersテーブルに追加
insert into users(id, name) values (35,'田中'),(99,'九十九');
~~~
### update：列を更新（`where`で条件指定可）
~~~ SQL
-- 誕生日が1月1日のユーザだけ年齢(age)を＋１した値で更新
update users set age = age + 1
where  birthday = '0101';
~~~
### delete：行を削除
~~~ SQL
-- usersテーブルでidが300の行を削除
delete from users where id = 300
~~~
`where`をなくせばテーブルの削除になる
~~~ SQL
-- usersテーブルを削除
delete from users
~~~
###