SQLの概要とおもな使いかた
SQL（ゼロからはじめるデータベース操作)をよんでまとめました。
はじめに
データベースを管理するコンピュータシステムをDatabse Management System(DBMS)という
DBMSの種類
- Hierarchical Database(階層型データベース)
- Relational Database(RDB)
- Object Oriented Database(OODB: オブジェクト指向データベース)
- XML Database(XMLDB)
- Key-Value Store(KVS: キー・バリュー型データストア)
RDBMSのシステム構成
RDBMSとは、データベースからデータを取り出したり、データを書き換えたりするサーバの一種。
クライアントが、RDBMSに欲しいデータをSQL文にして送信する。要求を受け取ったRDBMSがデータベースにアクセスし要求されたデータを返す仕組み。
SQL
SQL(Structured Query Language)はデータベースを操作するための言語であり、ISO(国際標準化機構)で定められた標準規格、標準SQLがある。
標準SQLを覚えることで、さばざまなSDBMSでSQL文を書くことができる。
SQLは3つに分類される。
1.DDL(Data Definition Language)
データ定義言語。テーブルなどのオブジェクトを作成する
CREATE, DROP, ALTER
2.DML(Data Manipulation Language)
データ操作言語。データの挿入、更新、削除などを行う。
SELECT, INSERT, UPDATE, DELETE
3.DCL(Data Control Language)
データ制御言語。トランザクション（互いに関連する複数の処理をまとめた、一連の処理）関連のコマンド
COMMIT, ROLLBACK, GRANT, REVOKE
ルール
文の最後にセミコロンをつける。定数はシングルクォーテーションで囲む。
ユーザの作成と削除
$ createuser <username> $ dropuser <username> CREATE USER <username>; DROP USER <username>;
例
postgres=# CREATE USER ymotodesu2;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
kabos | Create DB | {}
pgadmin | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
posuser | | {}
ymotodesu2 | | {}
postgres=# DROP USER ymotodesu2;
DROP ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
kabos | Create DB | {}
pgadmin | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
posuser | | {}
データベース作成と削除
CREATE DATABASE <Database name>; DROP DATABASE <Database name>; $ createdb <database name> (createdb -O <username> <databasename>) $ dropdb <database name>
例
postgres=# create database ymotodb;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
dbtest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
kabosdb | kabos | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sample | pgadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | posuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
ymotodb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(8 rows)
postgres=# drop database ymotodb;
DROP DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
dbtest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
kabosdb | kabos | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sample | pgadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | posuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)
postgres@tk2-212-15593:~$ createdb test1
postgres@tk2-212-15593:~$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-----------+----------+-------------+-------------+-----------------------
dbtest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
kabosdb | kabos | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sample | pgadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
testdb | posuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
ymotodb | ymotodesu | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(9 rows)
postgres@tk2-212-15593:~$ dropdb test1
postgres@tk2-212-15593:~$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-----------+----------+-------------+-------------+-----------------------
dbtest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
kabosdb | kabos | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sample | pgadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | posuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
ymotodb | ymotodesu | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(8 rows)
データベース一覧
$ psql -l # \l
テーブルの作成
CREATE TABLE <テーブル名> (<列名1> <データ型> <列の制約>, <列名2> <データ型> <列の制約>, <列名3> <データ型> <列の制約>, .. . <テーブルの制約1>, <テーブルの制約2>,... );
例
ymotodb=# create table Personal_information ymotodb-# (id CHAR(4) NOT NULL, ymotodb(# name TEXT NOT NULL, ymotodb(# age INTEGER, ymotodb(# PRIMARY KEY (id)); CREATE TABLE
データ型
- CHAR : 文字列。CHAR(200)のように最大長を指定する。文字列が指定した長さに満たない場合、残りに半角スペースが入る。
- VARCHAR : CHARと同じだが、指定した長さに満たなくても半角スペースで埋めない。
テーブルの削除
DROP TABLE <テーブル名>;
テーブルの検索
テーブル一覧
\dt
列を出力
SELECT <列名>..., FROM <テーブル名>;
すべての列を出力
SELECT * FROM <テーブル名>;
列に別名をつける
SELECT <列名1> AS <別名1>,
<列名2> AS <別名2>,
...
FROM <テーブル名>;
データの登録(INSERT)
INSERT INTO <テーブル名> (列1, 列2, 列3, ...) VALUES (値1, 値2, 値3, ...);
※文字列には挿入する値にシングルクォーテーションをつける
※INSERT文は一回の実行で1行を挿入する
例
ymotodb=# select * from personal_information
ymotodb-# ;
id | name | age
----+------+-----
(0 rows)
ymotodb=# insert into personal_information (id, name, age) values (0001, yamamoto, 45);
ERROR: column "yamamoto" does not exist
LINE 1: ...ersonal_information (id, name, age) values (0001, yamamoto, ...
^
ymotodb=# insert into personal_information (id, name, age) values ('0001', 'yamamoto', 45);
INSERT 0 1
ymotodb=# select * from personal_information
;
id | name | age
------+----------+-----
0001 | yamamoto | 45
(1 row)
列リストの省略
テーブル名のあとの列リストは、テーブルの全列に対してinsertを行うばあい省略することができる。省略時は、valueの値が左から順に各列に割り当てられる
例
ymotodb=# insert into personal_information values ('0002', 'satou', 57);
INSERT 0 1
ymotodb=# select * from personal_information ;
id | name | age
------+----------+-----
0001 | yamamoto | 45
0002 | satou | 57
(2 rows)
他のテーブルのデータをコピー
INSERT INTO <テーブル名> (<列名>...) SELECT <列名> FROM <テーブル名>
例
ymotodb=# select * from personal_informationcp ; id | name | age ----+------+----- (0 rows) ymotodb=# insert into personal_informationcp (id, name, age) ymotodb-# select id, name, age from personal_information; INSERT 0 2 ymotodb=# select * from personal_informationcp ; id | name | age ------+----------+----- 0001 | yamamoto | 45 0002 | satou | 57 (2 rows)
データの削除(DELETE文)
テーブルの中身を空にする
DELETE FROM <テーブル名>;
一部の行だけを削除する
DELETE FROM <テーブル名> WHERE <条件>;
例
ymotodb=# select * from personal_information; id | name | age ------+----------+----- 0001 | yamamoto | 45 0002 | satou | 57 0003 | tanaka | 20 (3 rows) ymotodb=# DELETE FROM personal_information ymotodb-# WHERE age <= 20; DELETE 1 ymotodb=# select * from personal_information; id | name | age ------+----------+----- 0001 | yamamoto | 45 0002 | satou | 57 (2 rows)
データの更新(UPDATE文)
UPDATE <テーブル名> SET <列名> = <式> WHERE <条件>
例
ymotodb=# select * from personal_information; id | name | age ------+----------+----- 0001 | yamamoto | 45 0002 | satou | 57 0003 | yamada | 32 0004 | katou | 22 (4 rows) ymotodb=# UPDATE personal_information ymotodb-# SET name = 'コアラ' ymotodb-# WHERE id = '0004'; UPDATE 1 ymotodb=# select * from personal_information; id | name | age ------+----------+----- 0001 | yamamoto | 45 0002 | satou | 57 0003 | yamada | 32 0004 | コアラ | 22 (4 rows)
トランザクション
トランザクションとは
- セットで実行する更新処理のまとまり 複数の操作をまとめて連続で行う。
- COMMIT(処理の確定)とROLLBACK(処理の取り消し)がある
書き方
BEGIN TRANSACTION # トランザクション開始文 DML文; DML文; . . トランザクション終了文(COMMIT または ROLLBACK);
COMMIT
トランザクション内の処理をすべて反映してトランザクションを終了するコマンド
ROLLBACK
トランザクション内の処理をすべて破棄し、トランザクションを終了するコマンド
COMMIT例
ymotodb=# select * from personal_information; id | name | age ------+----------+----- 0001 | yamamoto | 45 0002 | satou | 57 0003 | yamada | 32 0004 | コアラ | 22 (4 rows) ymotodb=# BEGIN TRANSACTION; BEGIN ymotodb=# UPDATE personal_information ymotodb-# SET name = 'ゴリラ' ymotodb-# WHERE age = 45; UPDATE 1 ymotodb=# UPDATE personal_information ymotodb-# SET age = 78 ymotodb-# WHERE id = '0002'; UPDATE 1 ymotodb=# COMMIT; COMMIT ymotodb=# select * from personal_information; id | name | age ------+--------+----- 0003 | yamada | 32 0004 | コアラ | 22 0001 | ゴリラ | 45 0002 | satou | 78 (4 rows)
ROLLBACK例
ymotodb=# select * from personal_information ymotodb-# ; id | name | age ------+----------+----- 0001 | yamamoto | 45 0002 | satou | 57 0003 | コアラ | 20 0004 | ラッコ | 11 (4 rows) ymotodb=# BEGIN TRANSACTION; BEGIN ymotodb=# UPDATE personal_information ymotodb-# SET age = age - 2 ymotodb-# WHERE name = 'ラッコ'; UPDATE 1 ymotodb=# UPDATE personal_information ymotodb-# SET name = 'suzuki' ymotodb-# WHERE id = '0003'; UPDATE 1 ymotodb=# ROLLBACK; ROLLBACK ymotodb=# select * from personal_information ; id | name | age ------+----------+----- 0001 | yamamoto | 45 0002 | satou | 57 0003 | コアラ | 20 0004 | ラッコ | 11 (4 rows)
