SQL①

zero

SQLを学ぶ環境があったので、ここにメモ。備忘録として。

SQLは、データベースを分類する言語。SQLのようなデータベース専用の言語を、データベース言語とも呼ぶ。
データベースを「操作にSQLが必要がどうか」で大きく分けると以下の2種類に分類できる。

  • Relational Database（RDB）

  • NoSQL Database

Relational Database（RDB）は、テーブルと呼ばれる形でデータを整理して保存する。一つのデータベースは複数のテーブルを保存し、RDBでデータを読み出すときは、テーブルからデータ同士を関係付けて扱える。
SQLはRDBを操作するために必須で、RDBはSQLでしか操作できない。
RDBに分類されるデータベースは多くの種類があり、「MySQL」、「PostgreSQL」、「SQLite」、「Oracle Database」などは、全てRDBの種類。


操作の基本は「CRUD」

SQLを使ったデータベースの基本操作には、レコードやテーブルの作成（Create）、読み出し（Read）、更新（Update）、削除（Delete）がある。これらの操作は、それぞれのイニシャルからCRUD（クラッド）と呼ぶ。


SQLで操作できる「データ」の形式

SQLを使うには、テーブルと呼ばれるRDBのデータ形式を理解しておく必要がある。テーブルは、データを表（Table）にして整理した形式。テーブルでは、全てのデータを行と列によって示す。単なるデータ形式の名前ではなく、行と列で管理するデータの塊のこともテーブルと呼ぶ。
データベースではテーブルの列をカラム、行をレコードと呼びます。


SELECT文

SELECT文は、テーブルからレコードを読み出すときにほぼ必ず使う構文。SELECT文は、読み出すテーブルを指定するために、必ずFROM句と組み合わせる。<テーブル名>で指定したテーブルの全てのレコードから、<カラム名>に一致するカラム（列）のデータを読み出し、複数のカラムを指定すると指定した順番にカラムが並んで読み出す。

SELECT <カラム名> FROM <テーブル名>

#例えば、下記のテーブルで国語列を読みだす場合は
SELECT `国語` FROM `成績`
サンプル

また<カラム名>を　*　とすると、全ての列を選択したことになり、テーブルの全てのデータを読み出す。この時、読み出したカラムの並びは、元のテーブルと同じ順番になる。


WHERE句

読み出すレコードを絞り込むのが、WHERE句。

SELECT <カラム名> FROM <テーブル名> WHERE <条件式>

#<条件式>は以下のように記述して、絞り込む条件を指定します。
<カラム名または値> 演算子 <カラム名または値>

先程のテーブルで、例えば国語の点数が60点以上の名前を読み出したい場合は

SELECT `名前` FROM `成績` WHERE `国語`>=60

　と書く。以下にWHERE句の比較の演算子。

= (等しい)
<> (等しくない)
> (左辺が右辺よりも大きい)
< (左辺が右辺よりも小さい)
>= (左辺が右辺と等しいか右辺よりも大きい)
<= (左辺が右辺と等しいか右辺よりも小さい)


AND句、OR句


条件式1、2の両方に合致する
SELECT <カラム名> FROM <テーブル名> WHERE <条件式1> AND <条件式2>

条件式1、または条件式2のどちらかに合致する
SELECT <カラム名> FROM <テーブル名> WHERE <条件式1> OR <条件式2>

AND句やOR句は複数を組み合わせて使えます。
3つの条件式すべてに合致する
SELECT <カラム名> FROM <テーブル名> WHERE <条件式1> AND <条件式2> AND <条件式3>

3つの条件式のどれかに合致する
SELECT <カラム名> FROM <テーブル名> WHERE <条件式1> OR <条件式2> OR <条件式3>

条件式1と条件式2の両方に合致する、または、条件式3に合致する
SELECT <カラム名> FROM <テーブル名> WHERE (<条件式1> AND <条件式2>) OR <条件式3>


ORDER BY句

取得するレコードの並び順を明示的に指定するのがORDER BY句。

SELECT <カラム名> FROM <テーブル名> ORDER BY <カラム名> <ASC または DESC>

カラム名の後にASC（ASCEND：昇順）を指定すると指定したカラムがA→Z(値が小さいもの→値が大きい)順になるように、 DESC（DESCEND：降順）を指定するとZ→A(値が大きい→値が小さい)順になるように、並び替えられたレコードが取得できる。

例のテーブルで国語の点数が低い順に名前取得するなら

SELECT `名前` FROM `成績` ORDER BY `国語` ASC

と書く。


LIMIT句、OFFSET句

LIMIT句は取得する件数を制限。例えば、「成績良い順で（上位）10人のデータを取得する」といった構文が作れる。順番が重要なので、ORDER BY句と併用。

ORDER BY <カラム名> <ASC or DESC> LIMIT 10

OFFSET句は取得開始レコードをずらす。「成績良い順で21位から30位のデータを取得する」という命令は、OFFSET句を使い、「成績良い順に10人を、20人ずらして（21人目から）取得する」といった表現。
実際に「成績が21位から30位のデータを取得する」というSQLの命令は、次のような例になる。

SELECT <カラム名> FROM <テーブル名> ORDER BY `成績` DESC LIMIT 10 OFFSET 20



INSERT文

レコードを追加する。
例えば、データ分析であれば集めたデータを保存する時、アプリ開発であれば、ユーザーの新規登録といった機能実装にINSERT文を使い、レコードを追加する。

INSERT INTO <テーブル名> (<カラム1>, <カラム2>, <カラム3>,...) VALUES (<値1>, <値2>, <値3>,...)

上の構文は、新しいレコードをテーブルに加える操作。
カラム1に値1、カラム2に値2、カラム3に値3...というようにデータを持ったレコードを新しく作成。テーブル内の全ての列にデータが入ったレコードを作成する場合、 (<カラム1>, <カラム2>, <カラム3>,...) の部分は省略できる。ただし、VALUESの後にはテーブルの順番通りにデータを並べなければいけない。


例えばテーブルに零郎(成績はすべて0点)を追加するのであれば、二通り考えられる。

INSERT INTO `成績` (`名前`, `国語`, `数学`, `英語`) VALUES ("零郎", 0, 0, 0)
INSERT INTO `成績` VALUES ("零郎", 0, 0, 0）


UPDATE文

UPDATE文は、データを更新する構文。メールアドレスなどユーザー情報を変更する機能を実装するのに使うことがある。
データを上書きするため、元々あったデータは消えてしまうので、データを消さないようUPDATE文を原則使わないように運用する場合もある。

扱い要注意

UPDATE文は、更新するデータだけでなく、使用ミスにより全てのデータを消してしまう可能性があり。
一般的に、UPDATE文は、SELECT文と同じ様にWHERE句を使って更新するデータを絞りこみ、使用する。

UPDATE <テーブル名> SET <カラム1> = <値1>, <カラム2> = <値2>, ... WHERE <条件式>

<条件式>に合致するレコードのうち、<カラム1>のデータを<値1>に、<カラム2>を<値2>に、...という対応関係で、データを書き換えます。
例えばテーブルで、二郎の英語と数学を100点にする場合は

UPDATE `成績` SET `英語`=100 ,`数学`=100  WHERE `名前`="二郎"

　と書く。


DELETE文

DELETE文は、レコード単位でデータを削除する構文。
DELETE文は条件を指定しないと、テーブル中の全てのレコードを削除してしまう。以下のようにWHERE句を組み合わせて、条件に当てはまるレコードだけを選択削除。

DELETE FROM <テーブル名> WHERE <条件式>

DELETE FROM `成績` WHERE `名前`= "二郎"

レコードまるごとではなく、特定のカラムのデータのみ削除したい場合は、UPDATE文を使用。例えば、「指定したカラムのデータをNULLに上書きする」といった処理。

UPDATE <テーブル名> SET <カラム名> = NULL WHERE <条件式>



CREATE文

CREATE TABLE <テーブル名> (<カラム1> <データ型1>, <カラム2> <データ型2>, ...)

テーブルを作るのに必要な情報は、テーブルの名前とカラムの名前、そして各カラムのデータ型。データベースでは、カラムごとに決められたデータ型しか保存できないのが一般的です。テーブルを作る時、各カラムがどんなデータを保存するかを設定する必要がある。

データベースに保存できるデータ型をいくつか記述。

INT
イント型、整数型などと呼びます。整数の数字データを保存します。Integer（整数）に由来します。
DOUBLE
ダブル型、浮動小数型などと呼びます。小数点を持つ（持つ可能性がある）数値データを保存します。
CHAR
文字列型、固定長文字列型、キャラ型などと呼びます。Character（文字）が由来です。
固定長という呼び方がある通り、文字数を指定して使います。
CHAR(30)とすると、半角30文字のデータを保存するカラムになります。全角（日本語）だと15文字です。

作ったテーブルを表示する場合、SHOW TABLESと実行すると、データベースが保持している全てのテーブルが取得できる。
例として

import requests
import mysql.connector

def getDBAddress():
    url = 'http://sql-service/api/v1/createPod'
    res = requests.post(url)
    return res.text.replace('"', '')
    
IP = getDBAddress()
connection = mysql.connector.connect(
    host=IP,
    port='3306',
    user='ai',
    password='ai',
    database='ai_sql_basic'
)
cursor = connection.cursor()
cursor.execute('''
CREATE TABLE `身体測定結果` (`身長`DOUBLE, `体重`DOUBLE, `座高`DOUBLE)
''')
cursor.execute('''
SHOW TABLES
''')
cursor.fetchall()



ALTER文

ALTER文を使うと、テーブルにカラム（列）を追加できる。
ALTER文は色々な使い方ができる構文。列の追加は、使い方の一つでしかない。

ALTER TABLE <テーブル名> ADD <カラム名> <データ型>

実行すると、テーブルの右側に<カラム名>という列が加わる。このカラムに保存できるデータ型は<データ型>で指定。使えるデータ型は、CREATE文と同様。
例

ALTER TABLE `成績` ADD `社会` INT



RENAME文

テーブルの名前を変更できる。CREATE文やALTER文と同様に、名前の変更に使えるコマンド。
RENAME文でテーブルの名前を変えるには以下のように記述。

RENAME TABLE <テーブル名> TO <新テーブル名>




DROP文

テーブルそのものを削除するDROP文。
テーブルを削除するクエリーを実行する時は、テーブル名に誤りがないかなど、慎重に確認。テーブルを削除すると同時に中のデータも全て消えてしまう。

DROP TABLE テーブル名

不用意にこのクエリーを実行すると、システムが停止して復旧できなくなるといった致命的な事態に陥ってしまう可能性。


SQLの基礎の基礎
さらに記述していく。

いいなと思ったら応援しよう！