SQL チートシート: 重要なコマンドのクイック ガイド

プログラマーやデータ アナリストは、大量のデータを扱うことがよくあります。自由に使える最も強力なツールの 1 つは SQL (構造化照会言語) です。

この SQL チート シートでは、データ型、DML ステートメントと DDL ステートメント、集計関数、文字列関数と日付関数、サブクエリなど、最も一般的な SQL オブジェクトとコマンドを説明します。

これは、リレーショナル データベースを操作するときにいつでも参照できる、すばやく便利なリファレンスとなるように設計されています。

私たちと同じように、チートシートをダウンロードして印刷したり、コンピューターに保存したい場合は、以下からダウンロードできます。

SQL チートシートのダウンロード

目次

• SQL データ型
  • 1. 数値型
  • 2. 日付と時刻のタイプ
  • 3. 文字列型
• SELECT ステートメント
• WHERE句
• ORDER BY句
• SQL での複数のテーブルの結合
  • 1. 内部結合
  • 2. 左結合
  • 3. 右結合
  • 4. 完全結合
• SQL集計関数
  • 1.COUNT関数
  • 2.SUM関数
  • 3. AVG関数
  • 4.MAX関数
  • 5.MIN関数
• 一般的な文字列関数
• 一般的な数値関数
• 一般的な日付関数
• グループ化と所有
  • 1. Group By 句
  • 2. 所有句
  • 簡単なヒント
• サブクエリ
  • 1. IN オペレーター
  • 2. 存在する演算子
  • 3. 任意のオペレーター
  • 4. すべてのオペレーター
• データ操作 (DML)
  • 1. INSERT ステートメント
  • 2. UPDATE文
  • 3. DELETE ステートメント
• DDL を使用したデータベース管理
  • 1.CREATE文
  • 2. ALTER ステートメント
    • 新しい列を追加する
    • 列のデータ型を変更する
    • 列を削除する
    • 一意の制約を追加する
    • テーブル間に外部キーを追加する
  • 3. DROP ステートメント
• 取引
  • 1. 始める
  • 2.コミット
  • 3.ロールバック
• 最終的な考え

SQL データ型

特定のデータベース管理システム (DBMS) のデータ型は異なる場合があります (例: Microsoft SQL Server とMySQL )。ただし、ほとんどのシステムに存在するものがいくつかあります。これらは次の 3 つのカテゴリに分類できます。

1. 数値

2. 日時

3. 弦

1. 数値型

最も一般的な数値型は次のとおりです。

• INTEGER : 小数点のない整数。

• SMALLINT : より小さい範囲の整数

• BIGINT : より広い範囲の整数。

• DECIMAL(p, s) または NUMERIC(p, s) : たとえば、10 進数 (5,2) は 123.45 に適合します。

• REAL : 少なくとも 6 桁の 10 進数の精度を持つ浮動小数点数。

• FLOAT(n) : 少なくとも n 桁の精度を持つ浮動小数点数。

2. 日付と時刻のタイプ

• DATE : 日付値。通常は「YYYY-MM-DD」形式です。

• TIME : 時刻値。通常は「HH:MM:SS」形式です。

• DATETIME または TIMESTAMP : 日付と時刻の値の組み合わせ。

3. 文字列型

• CHAR(n) : n 文字の固定長文字列。

• VARCHAR(n) または CHARACTER VARYING(n) : 可変長文字列。

• TEXT : DBMS によって決定される最大長の可変長文字列。

SELECT ステートメント

SELECT ステートメントは、1 つ以上のテーブルからデータを取得するために使用されます。取得する列とどのテーブルから取得するかを指定できます。基本的な SELECT ステートメントは次のようになります。

SELECT column1, column2
FROM table;

「city」テーブルの「name」列と「country_id」列からすべてのレコードを取得するには、SQL クエリは次のようになります。

SELECT name, country_id
FROM city;

WHERE句

WHERE 句を使用すると、特定の条件に基づいて SELECT ステートメントの結果をフィルタリングできます。

SELECT column1, column2
FROM table
WHERE condition;

「人口」が 1,000,000 を超えるレコードを「city」テーブルから取得するには、クエリは次のようになります。

SELECT name, population
FROM city
WHERE population > 1000000;

ORDER BY句

ORDER BY 句を使用すると、SELECT ステートメントの結果を 1 つ以上の列で並べ替えることができます。結果を昇順 (ASC) または降順 (DESC) で並べ替えることができます。

SELECT column1, column2
FROM table
ORDER BY column1 ASC, column2 DESC;

たとえば、「city」テーブルから「人口」で降順にソートされたレコードを取得するには、クエリは次のようになります。

SELECT name, population
FROM city
ORDER BY population DESC;

SQL での複数のテーブルの結合

SQL には一般的に使用される結合が 4 つあります。

1. 内部結合

2. 左結合

3. 右結合

4. 完全結合

1. 内部結合

INNER JOIN は、両方のテーブルで一致する値を持つレコードを取得します。

アーティストとアルバムのデータベースの例を考えてみましょう。アーティストとアルバムのすべての組み合わせを検索したいとします。これは内部結合です。

SELECT *
FROM artists AS a
INNER JOIN albums AS b
ON a.artist_id = b.artist_id;

INNER JOIN を使用すると、指定されたフィールドに一致する値を持つ行のみが結果として返されます。

2. 左結合

LEFT JOIN は LEFT OUTER JOIN とも呼ばれます。左側のテーブルからすべてのレコードを返し、右側のテーブルから一致したレコードを返します。右側のテーブルに一致するものがない場合、結果には NULL 値が含まれます。

たとえば、すべてのアーティストとそれぞれのアルバム (存在する場合) のリストを取得するには、LEFT JOIN を使用できます。

SELECT *
FROM artists AS a
LEFT JOIN albums AS b
ON a.artist_id = b.artist_id;

このクエリは、アルバム テーブルに関連付けられたアルバムがない場合でも、すべてのアーティストを返します。

3. 右結合

RIGHT JOIN は RIGHT OUTER JOIN とも呼ばれます。右のテーブルからすべてのレコードを返し、左のテーブルから一致したレコードを返します。左側のテーブルに一致するものがない場合、結果には NULL 値が含まれます。

たとえば、すべてのアルバムとそれに関連するアーティスト (存在する場合) に関する情報を取得するには、RIGHT JOIN を使用します。

SELECT *
FROM artists AS a
RIGHT JOIN albums AS b
ON a.artist_id = b.artist_id;

このクエリは、アーティスト テーブルに関連付けられたアーティストがない場合でも、すべてのアルバムを返します。

4. 完全結合

FULL JOIN は FULL OUTER JOIN とも呼ばれます。LEFT 結合と RIGHT 結合の両方の結果が結合されます。つまり、左右のテーブルからすべての行を返し、一致しない場合は欠損値を NULL で埋めます。

アーティストとアルバムのテーブルを使用した例を次に示します。

SELECT *
FROM artists AS a
FULL JOIN albums AS b
ON a.artist_id = b.artist_id;

このクエリは両方のテーブルのすべての行を返し、どちらのテーブルにも一致しない場合は NULL を埋めます。

SQL集計関数

集計関数は、一連の入力値から 1 つの結果を計算するために使用されます。これらは複数の入力を受け取り、単一の出力を返すため、「集約」と呼ばれます。最も一般的なものは次のとおりです。

1. カウント

2. 和

3. 平均

4. マックス

5. 最小

1.COUNT関数

COUNT 関数を使用すると、クエリ結果の行数をカウントできます。この集計関数を使用すると、テーブル内のレコードの合計数、または特定の条件に一致するレコードの数を判断できます。

以下に例を示します。

SELECT COUNT(*) FROM employees;

このクエリは、「employees」テーブル内の従業員の総数を返します。WHERE 句を追加すると結果を絞り込むことができることに注意してください。

SELECT COUNT(*) FROM employees WHERE department = 'HR';

2.SUM関数

The SUM function calculates the total sum of a numeric column. It’s useful when you need to calculate the total value of a particular numeric field. For example, this query returns the total sum of all employee salaries:

SELECT SUM(salary) FROM employees;

3. AVG Function

The AVG function computes the average value of a numeric column. This function is helpful when you want to find the average of a particular numeric field. For instance, this query returns the average salary of all employees:

SELECT AVG(salary) FROM employees;

4. MAX Function

The MAX function finds the maximum value of a column. This is often used to find the highest value in a numeric field or the most recent date in a datetime field. For example, this query returns the highest salary:

SELECT MAX(salary) FROM employees;

5. MIN Function

Lastly, the MIN function helps you find the minimum value of a column. For example, this query returns the lowest salary:

SELECT MIN(salary) FROM employees;

Remember, you can use WHERE clauses in these queries and JOIN with multiple tables.

Common String Functions

Here are the most common string functions that are found in most SQL dialects (the exact syntax can vary):

1. LEN or LENGTH(string): Returns the length of a string.

2. UPPER(string): Converts a string to upper case.

3. LOWER(string): Converts a string to lower case.

4. SUBSTR or SUBSTRING(string, start, length): Extracts a portion from a string.

5. TRIM(string): Removes leading and trailing spaces from a string.

6. LTRIM(string): Removes leading spaces from a string.

7. RTRIM(string): Removes trailing spaces from a string.

Common Numeric Functions

Here are the most common numeric functions that are found in most SQL dialects (the exact syntax can vary):

1. ABS(number): Returns the absolute value of a number.

2. ROUND(number, decimal_places): Rounds a number to a certain number of decimal places.

3. FLOOR(number): Rounds down the number to the nearest integer.

4. CEIL or CEILING(number): Rounds up the number to the nearest integer.

5. RAND(): Returns a random float value from 0 to 1.

6. MOD(n, m): Returns the remainder of n divided by m.

7. POWER(base, exponent): Raises a number to the power of another number.

8. LOG(number): Returns the natural logarithm of a number.

Common Date Functions

Here are the most common date functions that are found in most SQL dialects (the exact syntax can vary):

1. NOW(): Returns the current date and time.

2. DATE(datetime): Extracts the date part of a date or datetime expression.

3. TIME(datetime): Extracts the time part of a date or datetime expression.

4. YEAR(date): Returns the year part.

5. MONTH(date): Returns the month part.

6. DAY(date): Returns the day of the month part.

7. HOUR(time): Returns the hour part from a time.

8. MINUTE(time): Returns the minute part from a time.

9. SECOND(time): Returns the second part from a time.

GROUP BY And HAVING

When working with SQL queries, you may want to further summarize and filter your aggregated data. The GROUP BY and HAVING clauses provide this functionality.

1. Group By Clause

The GROUP BY clause allows you to group rows that share the same values in specified columns. It is commonly used with aggregate functions. This is the syntax:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2;

For example, if you want to calculate the total sales amount for each product category, this is the query:

SELECT product_category, SUM(sales_amount)
FROM sales_data
GROUP BY product_category;

TIP: Combining GROUP BY and COUNT is a good way of finding duplicate values.

2. Having Clause

If you want to filter the aggregated results further, you can use the HAVING clause. The syntax is:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition;

If you want to find product categories with total sales of more than $1,000,000, you would write:

SELECT product_category, SUM(sales_amount)
FROM sales_data
GROUP BY product_category
HAVING SUM(sales_amount) > 1000000;

Quick Tips

• Always use the GROUP BY clause before the HAVING clause.

• The SELECT statement can only contain specified column names, aggregate functions, constants, and expressions.

• When using the HAVING clause, filter conditions should be applied to the aggregate functions rather than directly to the grouped columns.

By understanding and properly applying the GROUP BY and HAVING clauses, you can better organize and analyze your data using SQL.

Subqueries

A subquery is also known as an inner or nested query. This is a query embedded within another SQL statement (such as a SELECT statement) or even inside another subquery.

Subqueries allow you to retrieve data based on the output of another query. The most common operators used with subqueries are:

1. IN

2. EXISTS

3. ANY

4. ALL

1. IN Operator

The IN operator tests if a value is within a set of values generated by the inner query. The syntax for using the IN operator with a subquery is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM other_table);

This returns rows from the outer query where the specified column value matches any of the values provided by the subquery.

Suppose you have an employee table and a departments table. You want to find employees who work in departments based at the head office. Here is a sample query

SELECT first_name, last_name
FROM employee
WHERE department IN (SELECT department FROM departments
                     WHERE location = "HQ");

For a more in-depth look, check out our article on the SQL WHERE IN syntax.

2. EXISTS Operator

The EXISTS operator checks if there is at least one row resulting from the subquery. You can use the EXISTS operator to filter rows based on the existence of related data in another table. This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM other_table WHERE condition);

When the subquery returns at least one row, the EXISTS operator returns true, and the relevant rows from the outer query are included in the result.

3. ANY Operator

The ANY operator is used to compare a value to any value in a set of values provided by a subquery. It’s commonly used with comparison operators like =, <, >, <=, or >=.

This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY (SELECT column_name FROM other_table WHERE condition);

This will return rows from the outer query where the specified column value meets the condition against any value from the subquery.

4. ALL Operator

The ALL operator compares a value to all values within a set of values provided by a subquery. The conditions must be true for every value in the subquery’s result. This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL (SELECT column_name FROM other_table WHERE condition);

This returns rows from the outer query only if the specified column value satisfies the condition against all values in the subquery’s output.

Data Manipulation (DML)

Data Manipulation Language (DML) is a sub-language within SQL for managing and updating data. The most common statements are:

1. INSERT

2. UPDATE

3. DELETE

1. INSERT Statement

The INSERT statement allows you to insert rows into a table. Here’s the basic syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

For example, if you want to insert a new row into a ‘users’ table with columns ‘id’, ‘name’, and ’email’, you would use the following query:

INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', '[email protected]');

2. UPDATE Statement

The UPDATE statement allows you to modify existing row data in a table. This is the syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

For example, if you want to update the email address of a user with the id ‘1’ in the ‘users’ table, your query would look like this:

UPDATE users
SET email = '[email protected]'
WHERE id = 1;

TIP: remember to include a WHERE clause to avoid updating all rows in the table by mistake.

3. DELETE Statement

The DELETE statement allows you to remove rows from a table. Here’s the syntax:

DELETE FROM table_name
WHERE condition;

たとえば、ID が「1」のユーザーを「users」テーブルから削除する場合、クエリは次のようになります。

DELETE FROM users
WHERE id = 1;

ヒント: 削除する行を指定し、テーブル内のすべての行が削除されるのを避けるために、常に WHERE 句を含めます。

DDL を使用したデータベース管理

データ定義言語 (DDL) は、テーブルおよびデータベース自体の作成と変更に使用される SQL サブ言語です。最も一般的な DDL ステートメントは次のとおりです。

1. 作成

2. 変更

3. 落とす

1.CREATE文

CREATE ステートメントを使用すると、新しいテーブル、ビュー、インデックスなどの新しいデータベース オブジェクトを作成できます。新しいテーブルを作成するときは、列、そのデータ型、および制約を定義する必要があります。

注文テーブルの作成例を次に示します。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    product VARCHAR(255) NOT NULL,
    customer_id INT NOT NULL
);

ヒント: テーブル内のデータの整合性を確保するには、適切なデータ型と制約を選択してください。

さらに詳しく知りたい場合は、基本的な SQL テーブル操作に関する記事を参照してください。

2. ALTER ステートメント

ALTER ステートメントは、既存のデータベース オブジェクトを変更するのに役立ちます。一般的な用途には次のようなものがあります。

• 列の追加、変更、または削除。

• 既存のテーブルに制約を追加または削除します。

• 主キーと外部キーを追加します。

新しい列を追加する

ALTER TABLE users ADD COLUMN age INTEGER;

列のデータ型を変更する

ALTER TABLE users ALTER COLUMN age TYPE FLOAT;

列を削除する

ALTER TABLE users DROP COLUMN age;

一意の制約を追加する

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE(email);

テーブル間に外部キーを追加する

ALTER TABLE users ADD FOREIGN KEY (country_id) REFERENCES Country(country_id);

3. DROP ステートメント

DROP ステートメントを使用すると、テーブル、ビュー、インデックスなどのデータベース オブジェクトを削除できます。指定したオブジェクトとそのすべてのデータが完全に削除されるため、使用には注意してください。

以下に例を示します。

DROP TABLE users;

ヒント: DROP ステートメントを実行する前に、適切なバックアップがあることを確認してください。

データ モデリングについて詳しく知りたい場合は、次のビデオをご覧ください。

取引

トランザクションは、特に複数の関連操作が同時に実行される場合に、データベースの整合性を維持する上で重要な役割を果たします。トランザクションの処理には 3 つの基本的な操作があります。

1. 始める

2. 専念

3. ロールバック

1. 始める

BEGIN ステートメントはトランザクションの開始を示します。このコマンドを実行すると、一連の SQL ステートメントの開始点が確立されます。

BEGIN;

2.コミット

変更を完了してデータベースに保存するには、COMMIT ステートメントを使用します。これにより、トランザクション内のすべての操作が正常かつ永続的に実行されることが保証されます。

COMMIT;

アカウント間で資金を移動する古典的な例を使用した完全なトランザクションの例を次に示します。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

3.ロールバック

トランザクションを操作する場合、エラーが発生したときに変更を元に戻す方法を知っておくことも重要です。ROLLBACK ステートメントは、トランザクションの開始以降に行われたすべての変更を元に戻します。

ROLLBACK;

ROLLBACK を使用したエラー処理を伴うトランザクションの例を次に示します。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
IF @@ERROR <> 0
    ROLLBACK;
ELSE
    COMMIT;

最終的な考え

この SQL チートシートで説明したように、SQL には、リレーショナル データベースでデータを作成、操作、クエリできるようにする多数のコマンドと関数が用意されています。

SELECT、INSERT、UPDATE、DELETE などの基本的なコマンドから、JOIN やサブクエリなどのより複雑な構造、さらにはこれまで説明した集計関数に至るまで、SQL は幅広いデータ タスクを処理する柔軟性を提供します。

SQL の学習を加速するために、このチートシートに何度も戻ってきてください。