sql

cheatsheet

Most applications are “state machines” where users interact with data. The most common actions are CRUD: Create, Read, Update, and Delete.

In SQL, these actions correspond to the following commands:

• Create: Creating tables (CREATE TABLE) and adding data (INSERT INTO).
• Read: Viewing data (SELECT).
• Update: Modifying a table’s structure (ALTER TABLE).
• Delete: Removing tables (DROP TABLE).

Usually we use uppercase letters for SQL commands, lowercase for table and field names.

Parentheses are used to group items, and commas separate items in lists.

Semicolon ; are used to end SQL statements.

テーブルの作成 (CREATE TABLE)

Create new tables in the database.

Basic Syntax:

CREATE TABLE table_name (
    field_name1 DATA_TYPE(size) RESTRICTIONS,
    field_name2 DATA_TYPE(size) RESTRICTIONS,
    ...
);

Key Terms:

• Field name: Column name, usually lowercase with underscores
  • フィールド名
• Data type: Type of data to store
  • データ型
• Constraints: Data constraints/limitations
  • 制約

Data Types:

• INT: Integer numbers
  • 数字、整数 (例: 3)
• CHAR(n): Fixed-length string
  • 固定長の文字列 (例: ‘abc’ - 3 characters)
• VARCHAR(n): Variable-length string, max n characters
  • 可変長の文字列 (例: ‘abcdefg’ - 7 characters)
• DATE: Date format
  • 日付 (例: ‘2023-10-01’)
• TIME: Time format
  • 時間 (例: ‘00:00:00’, ‘12:34:56’, ‘23:59:59’)
• DATETIME: Date and time
  • 日時 (例: ‘2023-10-01 12:34:56’)
• TIMESTAMP: Timestamp for recording date/time
  • タイムスタンプ、日時の記録

Constraints:

• PRIMARY KEY: Primary key
  • 主キー
• NOT NULL: Does not allow empty values
  • 空の値を許可しない

Practice Examples:

-- Create users table (数字のid、20文字のname)
CREATE TABLE users (
    id INT,
    name CHAR(20)
);

Creates a table named “users” with integer id field and 20-character name field

-- Create events table (日付のevent_date、時間のstart_time、100文字のdescription)
CREATE TABLE events (
    event_date DATE,
    start_time TIME,
    description CHAR(100)
);

Creates events table with date, time, and description fields

-- Create products table (8文字のcode（主キー）、20文字のtitle、時間のduration)
CREATE TABLE products (
    code CHAR(8) PRIMARY KEY,
    title CHAR(20),
    duration TIME
);

Creates products table with code as primary key

-- Create contacts table (数字のzip、100文字のaddress、20文字のname、15文字のphone、zipとnameにNOT NULL)
CREATE TABLE contacts (
    zip INT NOT NULL,
    address CHAR(100),
    name CHAR(20) NOT NULL,
    phone CHAR(15)
);

Creates contacts table with NOT NULL constraints on zip and name fields

テーブルの変更 (ALTER TABLE)

Modify existing table structure.

Key Terms:

• 変更 (へんこう) = change/modify
• 追加 (ついか) = add
• 削除 (さくじょ) = delete
• 後ろ (うしろ) = after/behind

⚠️ Important: In exams, do not use COLUMN in ADD COLUMN (although it’s correct SQL, it will be marked wrong in class)

Add Field:

ALTER TABLE table_name ADD field_name DATA_TYPE AFTER existing_field;

-- Add email field to users table (nameの後ろ)
ALTER TABLE users ADD email CHAR(50) AFTER name;

Adds an email field after the name field in users table

-- Add created_date field to products table (priceの後ろ)
ALTER TABLE products ADD created_date DATE AFTER price;

Adds a date field after the price field in products table

Change Data Type:

ALTER TABLE table_name MODIFY field_name NEW_DATA_TYPE(size);

-- Change users table id data type to string (idのデータ型を8文字に変更)
ALTER TABLE users MODIFY id CHAR(8);

Changes the users table’s id field data type from integer to 8-character string

-- Change employees table department data type to larger size (departmentのデータ型を40文字に変更)
ALTER TABLE employees MODIFY department CHAR(40);

Changes the employees table’s department field size to 40 characters

Change Field Name:

ALTER TABLE table_name CHANGE old_field_name new_field_name DATA_TYPE(size);

-- Change users table username field name to login_name (usernameをlogin_nameに変更、データ型は15文字)
ALTER TABLE users CHANGE username login_name CHAR(15);

Changes users table’s username field name to login_name and sets data type to 15 characters

-- Change contacts table phone field name to mobile (phoneをmobileに変更、データ型は50文字)
ALTER TABLE contacts CHANGE phone mobile CHAR(50);

Changes contacts table’s phone field name to mobile and sets data type to 50 characters

Delete Field:

ALTER TABLE table_name DROP field_name;

-- Delete age field from users table (ageのフィールドを削除)
ALTER TABLE users DROP age;

Completely removes the age field from users table

-- Delete old_price field from products table (old_priceのフィールドを削除)
ALTER TABLE products DROP old_price;

Removes the old_price field from products table

テーブルの削除 (DROP TABLE)

Delete entire tables from the database.

Basic Syntax:

DROP TABLE table_name;

Practice Examples:

-- Delete old_logs table (old_logsテーブルを削除)
DROP TABLE old_logs;

Completely removes the table named “old_logs”

-- Delete temp_data table (temp_dataテーブルを削除)
DROP TABLE temp_data;

Removes the temp_data table from the database

データの表示 (SELECT)

Retrieve data from tables. You can display specific fields or all fields (*), and use WHERE to specify conditions.

Basic Syntax:

SELECT field_name1, field_name2 FROM table_name WHERE condition;

Display All Fields:

SELECT * FROM table_name;

Practice Examples:

-- Display date, user_id, content from posts table (postsテーブルからdate・user_id・contentを表示)
SELECT date, user_id, content FROM posts;

Displays all data from the specified three fields in posts table

-- Display product_id, title, cost from inventory table (inventoryテーブルからproduct_id・title・costを表示)
SELECT product_id, title, cost FROM inventory;

Displays product ID, title, and cost from inventory table

Conditional Data Display:

-- Display user_id, full_name from members table where status = 2 (statusが2のuser_id・full_nameを表示)
SELECT user_id, full_name FROM members WHERE status = 2;

Displays user_id and full_name from members table where status field value equals 2

-- Display title, brand from inventory table where cost = 120 (costが120のtitle・brandを表示)
SELECT title, brand FROM inventory WHERE cost = 120;

Displays title and brand from inventory table where cost equals 120

Using Multiple and Complex Conditions:

You can combine conditions using AND and OR, and use comparison operators like >, <, >=, <=.

-- Using AND (costが100より大きく、brandが'A')
SELECT title, brand, cost FROM inventory WHERE cost > 100 AND brand = 'A';

Displays items where the cost is greater than 100 AND the brand is ‘A’

-- Using OR (costが50より小さい、またはbrandが'B')
SELECT title, brand, cost FROM inventory WHERE cost < 50 OR brand = 'B';

Displays items where the cost is less than 50 OR the brand is ‘B’

データの追加 (INSERT INTO)

Add new data (insert) into tables.

Basic Syntax:

INSERT INTO table_name (field_name1, field_name2) VALUES (value1, value2);

Practice Examples:

-- Add data to members table: user_id = "alice", full_name = "johnson" (user_idが「alice」、full_nameが「johnson」)
INSERT INTO members (user_id, full_name) VALUES ('alice', 'johnson');

Adds one new member record to members table

-- Add data to songs table: title = "melody", duration = 2:30 (titleが「melody」、durationが2分30秒)
INSERT INTO songs (title, duration) VALUES ('melody', '00:02:30');

Adds song title and duration to songs table (time format: hours:minutes:seconds)