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.

@Katsu8998(Katsu 8998)

DBとSQLの基本知識

Last updated at Posted at 2022-12-06

データベースとは

構造的に大量にデータを保管し、複数人によるデータ更新を可能にする特徴を持つ。
そのため、一目でデータを把握でき、効率よくデータを取り扱える。
データベースはDBMSによって管理されている。
DBMSの代表例はMySQL、PostgreSQL、SQLite。
DBMSを使用することで、手軽にデータの管理や操作を行うことができる。

データベースの構成要素

テーブル：データを格納する場所。テーブルは、行と列で構成されている。1つの行が1件のデータ、1つの列がデータの1要素に対応している。
リレーション：複数のテーブルを共通づけて管理すること。また共通するデータは分けて管理する。

問い合わせ言語

代表的な言語はSQL

SQLの基本ルール

記述の途中で改行や半角の空白を挿入することが可能。
文中にコメントを入力可能
予約後は大文字、小文字区別されない

命令語の種類

データ操作言語(DML)

SELECT, INSERT, DELETE, UPDATE

データ定義言語(DDL)

CREATE, DROP, ALTER

データ制御言語(DCL)

start transanction, ユーザーに権限を与える

SQLの基本文法

すべての情報を取り出す
SELECT * FROM テーブル名;
SELECT：取得する
*：すべてのデータが対象範囲を意味する

クエリ

SQLのコードのことを指す言葉。

ビュー

SQLのコード入力結果。

外部キー

他のテーブルと連携させるようにする。
外部キーを設定することで、ほかのレコードから参照されている場合、削除不可能。
また、参照元は参照先の値しか設定できない。

参照アクション

CASCADE: 参照先が削除又は更新されると、参照元はそれに伴って削除又は更新される。

インデックス

検索で特定のレコードをスピーディーに見つけるのに使用される仕組み。

インデックスの種類

Index：通常のインデックス
Unique: 重複不許可
Spatial: 空間を示すカラムに設定するインデックス
Fulltext:全文検索に設定するインデックス

ALTER Table sales Add index(goods);

インデックスが使用されているか確認する

EXPLAIN SELECT * FROM sales WHERE goods = 'ガンダム';

一部だけ取り出す

SELECT 列名 FROM テーブル名

一部の行だけを取り出す

SELECT * FROM テーブル名 Where 条件式;
Whereを使用することで、特定の行だけを取得できる
条件式には、Javaのような不等号を使用して表現する
a = b, a > b, a < b, a >= b, a <= b, a <> b

複数の条件を組み合わせる

SELECT * FROM テーブル名 Where 条件式 AND 条件式;
SELECT * FROM テーブル名 Where 条件式 OR 条件式;

Exists

レコードの存在を確認するために使用される比較式

SELECT * FROM goods WHERE Exists 
(SELECT * FROM sales WHERE goods.num = sales.record);

条件式と列指定を組み合わせる

SELECT 列名 FROM テーブル名 Where 条件式;

データ件数を調べる

SELECT COUNT(*) FROM テーブル名;

条件に一致したデータ件数を調べる

SELECT COUNT(*) FROM テーブル名 Where 条件式;

データを並び替えて取得する（昇順)

SELECT * FROM テーブル名 ORDER BY 列名;
ORDER BYでデータを並び替えて取得することができる。

データを並び替えて取得する（降順)

SELECT * FROM テーブル名 ORDER BY 列名　DESC;
DESCはdescendingの略称。

上位3件だけを取得する

SELECT * FROM テーブル名 ORDER BY 列名　DESC LIMIT 3;
末尾にLIMITを足すことで、上位の一部データを取得できる。

グループごとのデータ数を調べる

SELECT 列名A, COUNT(*) FROM players GROUP BY 列名B;
列名Bを基準してデータを集計し、 カテゴリーごとに分けて列名Aの数をカウントし表示する

データの追加

INSERT INTO テーブル名(データを追加する列名) VALUES(追加する値やデータ)
「データを追加する列名」の数や位置と「追加する値やデータ」の数と位置は一致する必要がある。

複数データの追加

INSERT INTO テーブル名(データを追加する列名) VALUES(追加する値やデータ),
(追加する値やデータ)

データの更新

UPDATE テーブル名 SET 列名 = 変更後の値;

データの削除

DELETE FROM テーブル名 WHERE 条件式;

テーブル結合

条件が一致するデータのみ表示

SELECT 選択リスト FROM テーブルA INNER JOIN テーブルB ON 両テーブルの結合条件

テーブルAのすべてのデータを表示

SELECT 選択リスト FROM テーブルA LEFT JOIN テーブルB ON 両テーブルの結合条件

テーブルBのすべてのデータを表示

SELECT 選択リスト FROM テーブルA RIGHT JOIN テーブルB ON 両テーブルの結合条件

結合したテーブルから指定列のデータを抽出

SELECT 列名 FROM テーブルA INNER JOIN テーブルB ON 両テーブルの結合条件

結合したテーブルから条件に合った行だけを抽出

SELECT 列名 FROM テーブルA INNER JOIN テーブルB ON 両テーブルの結合条件 WHERE 条件式

結合したテーブルからグループごとのデータ数を集計

SELECT 列名, COUNT(*) FROM テーブルA INNER JOIN テーブルB ON 両テーブルの結合条件 GROUP BY 列名

日時のデータを日付だけ取得する

SELECT DATE(列名) FROM テーブルA;

期間を指定し、その間のデータを取得する

SELECT DATE(列名A) FROM テーブルA
WHERE DATE(列名A) BETWEEN 'xxxxxxx' AND 'xxxxxxx';

月別データを取得する

SELECT DATE_FORMAT(列名A, '%Y-%m') FROM テーブル名
GROUP BY DATE_FORMAT(列名A, '%Y-%m') ;

index インデックスが使用されているのかを確認する

EXPLAIN SELECT * FROM sales WHERE state = 'NY';

view　

作成

CREATE VIEW salesView AS
SELECT * FROM sales INNER JOIN purchase ON sales.company = purchase.company;

参照

SELECT * FROM salesView;

viewの更新

UPDATE salesView SET price=100 WHERE id=20220202;

列名を別の名前に変更する

SELECT 列名 AS 任意の名前 FROM テーブル;

NULLの判定

SELECT * FROM テーブル WHERE 列名 IS NULL;
IS NULL でNULLであることを証明する
NOT IS NULL でNULLでないことを証明する。

重複を除外

SELECT DISTINCT 列名 FROM テーブル;

合計値を算出

SELECT SUM(列名) FROM　テーブル名;

平均値を算出

SELECT AVG(列名) FROM　テーブル名;

最大値を求める

SELECT MAX(列名) FROM テーブル名;

最小値を求める

SELECT MIN(列名) FROM テーブル名;

データの絞り込み

SELECT
tableA. COLUM_A AS ユーザーID,
SUM(tableB.COLUM_B) AS 合計,
AVG(tableB.COLUM_B) AS 平均
FROM tableA
INNER JOIN tableB ON tableA.COLUM_X = tableB.COLUM_X
GROUP BY tableA.COLUM_A
HAVING SUM(tableB.COLUM_B)>=5000;

HAVINGでデータを絞り込むことができる。WHEREでも指定のデータを取得可能だが、GROUP BYの後ろで使用できない。そのため、集積結果から絞りこむにはHAVINGを使用する。

処理手順

  1. INNER JOINでtableAとtableBを連結。条件に一致した行を一つ一つつなぐ。
  2. GROUP BYでCOLUM_Aをグループ化する。
  3. tableB.COLUM_Bを集計・計算後に、HAVINGで5000以上のデータをCOLUM_Bから取得
  4. グループ化したtableAを基準にして、値の合計と平均を表示

四則演算

プログラミング言語同様、+,-,/,*,()で四則演算可能
SELECT
DATE(MAX(COLUM_start))-DATE(MIN(COLUM_end))
FROM tableA
GROUP BY ID;

現在日時を求める

CURRENT_DATE() AS 現在日時

現在の年だけを求める

Year(CURRENT_DATE()) AS 現在日時

2つの日時の期間を求める

TIMESTAMPDIFF(YEAR, (指定日), (現在の日時))

テーブルから部分的に一致する行を取り出す

SELECT * FROM tableA
WHERE COLUM_A LIKE '%XX'

%は、任意の0文字以上の文字列
_は、任意の１文字

サブクエリ

他のSQL文の一部分として登場するSELECT文。()で囲って記述する。
FROMの後ろはテーブルが来るが、(SELECT...)でサブクエリを持ってくることもできる。クエリを入れ子にし、内側のクエリが値を生成し、それを外側のクエリの述語が評価・TRUE かどうか判断する。

例)
SELECT * FROM (SELECT...) AS 任意のサブクエリ名;
また()内の任意のサブクエリ名は、サブクエリの外側で使用できる。
()AS 任意のサブクエリ名は、サブクエリにかかっている。

WHERE句にサブクエリを入力

SELECT * FROM tableA
WHERE COLUM_A = ((サブクエリ));
サブクエリ名でカウントやグループ化できる。

SELECT句に入力

SELECT (サブクエリ) AS (サブクエリ名) FROM tableA;

CASE文

パターン1
CASE
WHEN (条件式1) THEN (出力1)
WHEN (条件式2) THEN (出力2)
ElSE (出力3)
END AS COLUM名

パターン2
CASE　評価する式や列
WHEN 値1 THEN (値1の時の出力内容)
WHEN (値2) THEN (値2の時の出力内容)
ElSE (それ以外のケースにおける出力内容)
END AS COLUM名

クロス集計

データのグループごとに上と横に見出しがつくようにできる。

トランザクション

トランザクションとは

SQL文が複数ある場合、すべてが成功してから処理を確定すること。
例えば、ATMでA銀行口座のお金をB口座に送金する場合、SQL文ではA口座から残高を減らす要求とB口座の残高を増やす要求が発生。A口座の残高を減らした後に、ATMに問題が起きた場合、B口座の残高が更新されないままになってしまう。このような出来事を避けるためにトランザクションがある。

トランザクション宣言

con.setAutoComit(false);

処理要求の確定

con.commit();

要求をキャンセル

con.rollback();

START TRANSACTION;
INSERT INTO sales(id,output,date) VALUES
(2017901,30,2017/09/01);
COMMIT; //(またはROLLBACK)

ロック

テーブルロック

共有ロック

START TRANSACTION;
LOCK TABLES sales READ;
SELECT * FROM sales;
UNLOCK TABLES sales;
COMMIT(or ROLLBACK);

排他ロック

START TRANSACTION;
LOCK TABLES sales WRITE;
SELECT * FROM sales;
UNLOCK TABLES sales;
COMMIT(or ROLLBACK);

レコードロック

共有ロック

START TRANSACTION;
LOCK TABLES sales LOCK IN SHARE MODE;
SELECT * FROM sales;
UNLOCK TABLES sales;
COMMIT(or ROLLBACK);

排他ロック

START TRANSACTION;
LOCK TABLES sales FOR UPDATE;
SELECT * FROM sales;
UNLOCK TABLES sales;
COMMIT(or ROLLBACK);

ストアドルーチン

ストアドプロシージャ

//引数なし
DELMITER //
CREATE PROCEDURE salespack()
BEGIN
INSERT INTO goods (id, productName, price) VALUES 
('20220101', pencil, 300);
END //

//　プロシージャの実行
CALL salespack();

//プロシージャの削除
DROP PROCEDURE salespack();

//引数あり
DELIMITER//
CREATE PROCEDURE salespack(company VARCHAR(30))
BEGIN 
SELECT * FROM sales WHERE companyName = company;
END //

//実行
CALL salespack('HATANAKA');

ストアドファンクション

DELIMITER //
CREATE FUNCTION register(animal VARCHAR (60))
RETURNS VARCHAR (60)
BEGIN 
INSERT INTO poleanimal(name) VALUES(animal);
RETURN 'registered'
END //

// 実行
SELECT register('polebare');

トリガー

DELIMITER //
CREATE TRIGGER salesTrigger
AFTER DELETE
ON sales FOR EACH ROW
BEGIN 
UPDATE invoice SET price = 0 WHERE company = sales.company;
END //

// 実行
DELETE FROM sales WHERE company = 'ABC company';

単一テーブルの作成

ステップ1

テーブル名、列名、データ型を決定。
設定できる値を宣言する項目（制約）を追加。
テーブルにどのような列があり、どのような値を保存できるかを定めた構造定義をスキーマという

ステップ2

主キーを設定する

ステップ3

オートインクリメントを構成する
オートインクリメントとは、MySQLには自動的に連番を作る機能

制約

UNIQUE制約：ほかのレコードと重複する値を不可にする
NOT NULL制約：NULL値での入力は不可
外部キー制約：ほかのテーブルと結びつけるときに用いる制約

コマンドプロンプトでMySQLを動かす

接続

mysql -u root -p

切断

quit

現状況確認

status;

コマンドプロンプトでデータベースの確認

Show Databases;

コマンドプロンプトでデータベース作成

CREATE DATABASE Test;

コマンドプロンプトでデータベース選択

USE Test01;

コマンドプロンプトでテーブル作成

CREATE TABLE テーブル名(列名A 型...);
CREATE TABLE Test01;
CREATE TABLE customer(id int primary key, name varchar(255), age int);
primary keyと入力することでidを主キーと指定。

テーブルにデータ追加

INSERT INTO Test01(id, name) VALUES(1,text);

テーブルからデータを取得

SELECT * FROM Test01;

テーブルの削除

DROP TABLE テーブル名;

JDBCを操作するパターン

JDBCドライバーのロード

Class.forName("com.mysql.cj.jdbc.Driver");

データベースの接続

Connection con = DriverManager.getConnection ("jdbc:mysql://localhost/データベース名","root","パスワード");

SQL 文をデータベースへ送るひな形を作成

Statement st = con.createStatement();
Statement オブジェクトは、Connection のメソッド createStatement を用いて生成される。

ひな形を利用して、SQL文をMySQLに送信

String query = "INSERT INTO customer values (" + id +",'" + name +"'," + age + ")";
st.executeUpdate(query);

ResultSetを使い、結果表からデータを取り出す

query = "SELECT id, name, age from customer";
ResultSet rs = st.executeQuery(query);

結果表の処理

while(rs.next()) {
変数A = rs.getInt("列名");
System.out.println(変数A);
}
next()で注目行を一つずつ前に進める。
get()で必要な列のデータを取り出す。
int型の列を取り出したい場合、getInt。文字列型の列を取り出したい場合、getString。

while(rs.next()) {
id1 = rs.getInt("id");
name1 = rs.getString("name");
age1 = rs.getInt("age");
System.out.println(id1 + " " + name1 + " " + age1);
}

ScannerでEclipseでの入力をデータベースに反映させる

int id;
String query;
id = sc.nextInt();
query = "UPDATE customer set name = 'A' Where id =" + id;

参考記事
https://www.techscore.com/tech/sql/SQL7/
https://qiita.com/Marny/items/eea9667066dbbaa900aa

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?