1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 芸能人テーブル
CREATE TABLE celebrities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

-- ファンテーブル
CREATE TABLE fans (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    celebrity_id INT REFERENCES celebrities(id)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 投稿テーブル
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255)
);

-- タグテーブル
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

-- 中間テーブル
CREATE TABLE post_tags (
    post_id INT REFERENCES posts(id),
    tag_id INT REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- ユーザーテーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

-- クレデンシャルテーブル
CREATE TABLE credentials (
    id SERIAL PRIMARY KEY,
    user_id INT UNIQUE REFERENCES users(id),
    password_hash VARCHAR(255)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- ユーザーテーブル
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

-- クレデンシャルテーブル
CREATE TABLE credentials (
    user_id INT PRIMARY KEY,
    password_hash VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- ユーザーテーブル
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

-- クレデンシャルテーブル
CREATE TABLE credentials (
    credential_id SERIAL PRIMARY KEY,
    user_id INT UNIQUE,
    password_hash VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- ユーザーテーブル
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    details_id INT UNIQUE,
    FOREIGN KEY (details_id) REFERENCES user_details(details_id)
);

-- 詳細情報テーブル
CREATE TABLE user_details (
    details_id SERIAL PRIMARY KEY,
    user_id INT UNIQUE,
    address VARCHAR(255),
    phone VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

1
2
3
4
5

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    password_hash VARCHAR(255)
);

1
2
3
4
5
6
7

CREATE TABLE customer_orders (
    customer_id BIGINT,
    customer_name TEXT,
    order_id BIGINT,
    order_date DATE,
    amount DECIMAL
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 基本情報テーブル
CREATE TABLE user_basic (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

-- 詳細情報テーブル
CREATE TABLE user_details (
    user_id INT PRIMARY KEY,
    address VARCHAR(255),
    phone VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user_basic(user_id)
);

1

CREATE INDEX idx_users_username ON users (username);

1

CREATE INDEX idx_department_email ON employees (department_id, email);

1

CLUSTER users USING idx_users_username;

1
2
3
4
5

-- 例: MySQLのInnoDBテーブルで圧縮を有効にする
CREATE TABLE compressed_table (
    id INT PRIMARY KEY,
    data TEXT
) ROW_FORMAT=COMPRESSED;

1
2
3
4
5
6

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INTEGER,
    hire_date DATE
);

1

ALTER TABLE employees ADD COLUMN salary NUMERIC;

1

DROP TABLE employees;

1

SELECT * FROM employees;

1

SELECT name, department_id FROM employees;

1

SELECT * FROM employees WHERE department_id = 10;

1

INSERT INTO employees (name, department_id, hire_date) VALUES ('John Doe', 10, '2023-01-01');

1

UPDATE employees SET salary = 60000 WHERE id = 1;

1

DELETE FROM employees WHERE id = 1;

1

GRANT SELECT, INSERT ON employees TO user_name;

1

REVOKE SELECT, INSERT ON employees FROM user_name;

1

BEGIN;

1

COMMIT;

1

ROLLBACK;

1
2
3
4
5

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;

1

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

1

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 10;

1
2
3

SELECT e.name, d.name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.id;

1
2
3

SELECT e.name, d.name 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.id;

1
2
3

SELECT e.name, d.name 
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.id;

1
2
3

SELECT e.name, d.name 
FROM employees e 
CROSS JOIN departments d;

1
2
3

SELECT name 
FROM employees 
WHERE department_id = (SELECT id FROM departments WHERE name = 'HR');

1

CREATE INDEX idx_department_id ON employees(department_id);

1
2

CREATE VIEW employee_view AS 
SELECT name, hire_date FROM employees;

1
2
3
4

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

1
2

SELECT * FROM employees WHERE department_id IN (10, 20, 30);
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';

1
2
3
4
5
6

SELECT
    department_id,
    SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department_id;

1
2
3

SELECT * FROM BLUE
UNION
SELECT * FROM RED ;

1
2
3

SELECT * FROM BLUE
UNION ALL
SELECT * FROM RED ;

1
2
3
4
5

/* BLUE * RED */

SELECT * FROM BLUE
INTERSECT
SELECT * FROM RED ;

1
2
3
4
5

/* BLUE - RED(BLUEにはあって、REDにはないもの) */

SELECT * FROM BLUE
EXCEPT
SELECT * FROM RED ;

1
2

SELECT DISTINCT column1, column2
FROM table1;

1
2
3

SELECT column1, column2
FROM table1
WHERE column1 IN (SELECT column1 FROM table2);

1
2
3
4
5
6
7

SELECT
  department_id,
  AVG(salary) AS avg_department_salary
FROM
  employees
GROUP BY
  department_id;

1
2
3
4
5
6
7

SELECT
  employee_id,
  department_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM
  employees;

1
2
3

SELECT columns
FROM table1
JOIN table2 ON table1.common_column = table2.common_column;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    employee_id INT,
    project_name VARCHAR(50),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

1
2
3
4
5
6
7

SELECT e.employee_id, e.first_name, p.project_name
FROM employees e
CROSS APPLY (
    SELECT project_name
    FROM projects p
    WHERE e.employee_id = p.employee_id
) p;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

INSERT INTO employees (employee_id, first_name, last_name)
VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith');

INSERT INTO projects (project_id, employee_id, project_name)
VALUES
(1, 1, 'Project A'),
(2, 1, 'Project B'),
(3, 2, 'Project C');

1
2

SELECT * FROM Table1;
SELECT * FROM Table2;

1

SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.fk = t2.id;

1

SELECT * FROM Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.fk = t2.id;

1

SELECT * FROM Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.fk = t2.id;

1

SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.fk = t2.id;

1

SELECT * FROM Table1 t1 CROSS JOIN Table2 t2;

1

SELECT * FROM Table1 t1 WHERE EXISTS (SELECT 1 FROM Table2 t2 WHERE t1.fk = t2.id);

1

SELECT * FROM Table1 t1 WHERE NOT EXISTS (SELECT 1 FROM Table2 t2 WHERE t1.fk = t2.id);

1
2
3

SELECT 列1, 列2, ...
FROM テーブル名
WHERE 列 = (SELECT 列 FROM 他のテーブル WHERE 条件);

1
2
3
4
5
6

-- 
SELECT name, salary 
FROM employees e1 
WHERE salary >= (SELECT AVG(salary) 
                 FROM employees e2 
                 WHERE e1.department_id = e2.department_id);

1
2
3
4
5
6
7
8
9

SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) > 50000
)
GROUP BY department_id;

1
2
3
4
5

SELECT name 
FROM employees 
WHERE department_id = (SELECT department_id 
                       FROM departments 
                       WHERE department_name = 'Sales');

1
2
3
4
5

SELECT name 
FROM employees 
WHERE department_id IN (SELECT department_id 
                        FROM departments 
                        WHERE location_id = 1700);

1
2
3
4
5

SELECT e1.name 
FROM employees e1 
WHERE salary > (SELECT AVG(salary) 
                FROM employees e2 
                WHERE e1.department_id = e2.department_id);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT 
    employee_id, 
    employee_name, 
    salary,
    (SELECT COUNT(*)
     FROM employees e2
     WHERE e2.department_id = e1.department_id AND e2.salary >= e1.salary) AS salary_rank
FROM 
    employees e1
ORDER BY 
    department_id, salary_rank;

1
2
3
4
5
6
7

SELECT name 
FROM employees 
WHERE department_id = (SELECT department_id 
                       FROM departments 
                       WHERE location_id = (SELECT location_id 
                                            FROM locations 
                                            WHERE city = 'Seattle'));

1
2
3

SELECT id, name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

1
2
3
4
5
6
7

WITH AverageSalary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT id, name
FROM employees
WHERE salary > (SELECT avg_salary FROM AverageSalary);

1
2
3
4
5
6

CREATE VIEW high_salary_employees AS
SELECT id, name, salary
FROM employees
WHERE salary > 50000;

SELECT * FROM high_salary_employees;

1
2
3
4
5
6

CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

1
2
3
4
5
6
7
8
9

CREATE VIEW public_employees AS
SELECT id, name, department
FROM employees;

CREATE VIEW hr_employees AS
SELECT id, name, department
FROM employees
WHERE department = 'HR';

1
2

SELECT * FROM public_employees;
SELECT * FROM hr_employees;

1

GRANT SELECT ON public_employees TO user_role;

1
2
3
4
5
6
7
8
9

CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_sales
FROM 
    sales
GROUP BY 
    product_id;

1
2

-- 完全リフレッシュ
REFRESH MATERIALIZED VIEW sales_summary;

1
2
3
4
5
6
7
8
9

BEGIN;

INSERT INTO my_table (id, value) VALUES (1, 'value1');

SAVEPOINT my_savepoint;
INSERT INTO my_table (id, value) VALUES (2, 'value2');
ROLLBACK TO SAVEPOINT my_savepoint;

COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

try:
    # 外側のトランザクションの開始
    session.begin()

    # 最初の操作
    t = MyTable(id=1)
    session.add(t)
    session.commit()

    # ネストされたトランザクションの開始
    with session.begin_nested() as nested:
        try:
            t2 = MyTable(id=2)
            session.add(t2)
            session.commit() # 本当はwithを抜けると自動でcommitされる
        except:
            nested.rollback()

    # 注意: ここですでにcommitしているがsavepointなので、rollbackすると上のt2の変更も消える

    # 他の操作が続く
    t3 = MyTable(id=3, value='value3')
    session.add(t3)
    session.commit()

except:
    session.rollback()
finally:
    session.close()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

try:
    # 外側のトランザクションの開始
    session.begin()

    # 最初の操作
    t = MyTable(id=1)
    session.add(t)
    session.commit()

    # ネストされたトランザクションの開始
    with session.begin_nested() as nested:
        try:
            t2 = MyTable(id=2)
            session.add(t2)
            session.commit() # 本当はwithを抜けると自動でcommitされる
        except:
            nested.rollback()

    # この時点ではまだt1, t2の内容はコミットされない
    session.commit()

    # 新しいトランザクションの開始
    # ここで先行するトランザクションが確定し、t1, t2が保存される
    session.begin()

    # 他の操作が続く
    t3 = MyTable(id=3, value='value3')
    session.add(t3)
    session.commit()
except:
    session.rollback()
finally:
    session.close()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

BEGIN;

-- 口座の残高をチェックし、他のトランザクションが残高を変更しないようにロック
SELECT balance FROM accounts WHERE account_id = 12345 FOR UPDATE;

-- 残高が十分にあるかを確認
-- この間に他のトランザクションが残高を変更することはできない
-- ただし、読み取りは可能

-- 残高が十分にある場合、引き出しを行う
UPDATE accounts SET balance = balance - 100 WHERE account_id = 12345;

COMMIT;

1
2
3
4
5
6
7
8
9

BEGIN;

-- レポート生成のために共有ロックを取得
SELECT * FROM sales_data WHERE report_date = '2024-06-22' FOR SHARE;

-- データを使用してレポートを生成
-- 他のトランザクションが排他ロックを取得してデータを変更するのを防ぐ

COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 準備
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INTEGER,
    salary DECIMAL
);
INSERT INTO employees (name, department_id, salary) VALUES ('Alice', 10, 50000);
INSERT INTO employees (name, department_id, salary) VALUES ('Bob', 20, 60000);

-- Aliceの給料を1.1倍に変更
WITH updated AS (
    UPDATE employees
    SET salary = salary * 1.1
    WHERE department_id = 10
    RETURNING *
)
SELECT COUNT(*) FROM updated;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- トランザクションの開始
BEGIN TRANSACTION;

-- 排他ロックの取得とデータの読み取り
SELECT id, name, price
FROM products
WHERE id = 1
FOR UPDATE;

-- データの更新
UPDATE products
SET price = 120
WHERE id = 1;

-- トランザクションのコミット
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- テーブルを読み取り専用モードでロック
LOCK TABLES task READ;

-- テーブルを読み取り/書き込みモードでロック
LOCK TABLES task WRITE;

-- 例: task.Statusを更新するためのトランザクション
START TRANSACTION;
LOCK TABLES task WRITE;

UPDATE task
SET Status = 'IN_PROGRESS'
WHERE Status = 'WAITING';

-- ロックを解除してトランザクションを終了
UNLOCK TABLES;
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- トランザクションの開始
START TRANSACTION;

-- 特定の行をロック（この場合、FOR UPDATEを使用）
SELECT * FROM task WHERE Status = 'WAITING' FOR UPDATE;

-- 例: task.Statusを更新
UPDATE task
SET Status = 'IN_PROGRESS'
WHERE Status = 'WAITING';

-- トランザクションをコミットしてロックを解除
COMMIT;

1
2
3
4
5
6
7
8

START TRANSACTION;

-- 範囲クエリに対してギャップロックを取得
SELECT * FROM products WHERE price BETWEEN 100 AND 200 FOR UPDATE;

-- このロックにより、他のトランザクションがこの範囲内に新しい行を挿入するのを防ぐ

COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- テーブルを読み取り専用モードでロック
LOCK TABLES task READ;

-- テーブルを読み取り/書き込みモードでロック
LOCK TABLES task WRITE;

-- 例: task.Statusを更新するためのトランザクション
START TRANSACTION;
LOCK TABLES task WRITE;

UPDATE task
SET Status = 'IN_PROGRESS'
WHERE Status = 'WAITING';

-- ロックを解除してトランザクションを終了
UNLOCK TABLES;
COMMIT;

1
2
3
4
5

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

1
2
3
4
5
6
7
8
9

-- シャード1の設定
-- 1, 11, 21
SET @@auto_increment_increment=10;
SET @@auto_increment_offset=1;

-- シャード2の設定
-- 2, 12, 22
SET @@auto_increment_increment=10;
SET @@auto_increment_offset=2;

1
2

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

1
2
3
4
5
6

CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    order_date DATETIME NOT NULL
) /* vtgate={keyspace: ecommerce, sharding: true, shard_key: user_id} */;

1
2
3
4
5

CREATE TABLE locks (
    resource_id VARCHAR(255) PRIMARY KEY,  -- 一意のリソースID
    lock_token VARCHAR(255) NOT NULL,      -- ロックを保持するためのトークン
    expires_at TIMESTAMP NOT NULL          -- ロックの有効期限
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- ロックの取得
INSERT INTO locks (resource_id, lock_token, expires_at)
VALUES ('resource_1', 'token_123', NOW() + INTERVAL '1 minute')
ON CONFLICT (resource_id) DO NOTHING;

-- ロックの解放
DELETE FROM locks WHERE resource_id = 'resource_1' AND lock_token = 'token_123';

-- 有効期限が切れたロックのクリーンアップ:
DELETE FROM locks
WHERE expires_at < NOW();

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

import redis
import uuid

client = redis.StrictRedis(host='localhost', port=6379, db=0)
lock_token = str(uuid.uuid4())

# ロックの取得
if client.setnx('resource_1_lock', lock_token):
    try:
        # リソースに対する操作
        pass
    finally:
        # ロックの解放
        if client.get('resource_1_lock') == lock_token:
            client.delete('resource_1_lock')

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

import com.atomikos.icatch.jta.UserTransactionManager;
import com.atomikos.icatch.jta.UserTransactionImp;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.jta.JtaTransactionManager;

@Configuration
@EnableTransactionManagement
public class TransactionManagerConfig {

    @Bean(initMethod = "init", destroyMethod = "close")
    public UserTransactionManager atomikosTransactionManager() {
        // 分散トランザクションを管理する、Prepare Phaseなどを行う
        UserTransactionManager userTransactionManager = new UserTransactionManager();
        userTransactionManager.setForceShutdown(true);
        return userTransactionManager;
    }

    @Bean
    public UserTransactionImp userTransactionImp() throws Throwable {
        // トランザクションの開始、コミット、ロールバックなどを行う
        UserTransactionImp userTransactionImp = new UserTransactionImp();
        userTransactionImp.setTransactionTimeout(300);
        return userTransactionImp;
    }

    @Bean
    public PlatformTransactionManager transactionManager() throws Throwable {
        return new JtaTransactionManager(userTransactionImp(), atomikosTransactionManager());
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ExampleService {

    @Autowired
    private RepositoryA repositoryA;

    @Autowired
    private RepositoryB repositoryB;

    @Transactional
    public void performDistributedTransaction() {
        // RepositoryAの操作
        repositoryA.save(new EntityA());

        // RepositoryBの操作
        repositoryB.save(new EntityB());
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date DATE,
    amount DECIMAL
);

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    customer_name TEXT
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

WITH total_sales AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY customer_id
)

SELECT c.customer_id, c.customer_name, ts.total_amount
FROM customers c
LEFT JOIN total_sales ts ON c.customer_id = ts.customer_id;

1
2
3
4

type Android  = {type: 'android',  value: string}
type iPhone = {type: 'iphone', value: [number, number]}

type SmartPhone = Android | iPhone

1
2
3
4
5
6
7
8

CREATE TABLE id_sequences (
    name VARCHAR(64) NOT NULL,
    next_id BIGINT NOT NULL,
    PRIMARY KEY (name)
);

-- 初期シーケンス値の挿入
INSERT INTO id_sequences (name, next_id) VALUES ('user_id', 1);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

BEGIN;

-- シーケンス行をロック
SELECT * FROM id_sequences WHERE name = 'user_id' FOR UPDATE;

-- シーケンス番号をインクリメント
UPDATE id_sequences SET next_id = next_id + 1 WHERE name = 'user_id';
-- 新しいシーケンス番号を取得
SELECT next_id - 1 AS user_id FROM id_sequences WHERE name = 'user_id';

-- トランザクションをコミットしてロックを解除
COMMIT;

1
2
3
4

UPDATE products 
SET remaining_amount = remaining_amount - 1
WHERE id = 1
  AND remaining_amount > 0

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

BEGIN;

-- 在庫を減らす前に行をロック
SELECT remaining_amount FROM products
WHERE id = 1
FOR UPDATE;

-- 在庫があるか確認して更新
UPDATE products
SET remaining_amount = remaining_amount - 1
WHERE id = 1
  AND remaining_amount > 0;

COMMIT;

1
2
3

UPDATE posts
SET title = title, content = content, revision_no = revision_no + 1
WHERE post_id = 1 and revision_no = 10;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE dictionary (
    dict_type VARCHAR(50) NOT NULL,
    dict_code VARCHAR(50) NOT NULL,
    dict_value VARCHAR(100) NOT NULL,
    PRIMARY KEY (dict_type, dict_code)
);

-- 国コードの挿入
INSERT INTO dictionary (dict_type, dict_code, dict_value) VALUES
('country', 'US', 'United States'),
('country', 'JP', 'Japan'),
('country', 'DE', 'Germany'),
('country', 'FR', 'France');

-- 言語コードの挿入
INSERT INTO dictionary (dict_type, dict_code, dict_value) VALUES
('language', 'EN', 'English'),
('language', 'JA', 'Japanese'),
('language', 'DE', 'German'),
('language', 'FR', 'French');

-- ステータスコードの挿入
INSERT INTO dictionary (dict_type, dict_code, dict_value) VALUES
('status', 'ACT', 'Active'),
('status', 'INA', 'Inactive'),
('status', 'SUS', 'Suspended'),
('status', 'DEL', 'Deleted');

1

select 1