LoginSignup
0
4

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.

@lowgain(老害 おじさん)

ＳＱＬ Server入門

Posted at

セッションの目的

SQLの基本的な概念を理解する。
T-SQLを用いた基本的なクエリの書き方を学ぶ。
SQL Serverでのデータの基本的な取得方法をマスターする。

アジェンダ

SQLとは何か？

  1. リレーショナルデータベースとは#1
    SQLの役割
    T-SQLとは
    基本的なSELECT文の構造

  2. SELECT句
    FROM句
    サンプルデータを使用した実習
    WHERE句を使用したフィルタリング

  3. 基本的な比較演算子
    複数の条件の組み合わせ
    ソート機能：ORDER BY句

  4. データのソート方法
    複数の列でのソート
    列のエイリアスと関数

  5. ASを使用したエイリアスの作成
    T-SQL内の一般的な関数（例：UPPER(), LOWER(), LEN()）

  6. TOP句の使用方法

7．OFFSET ... FETCH

初めに

1. リレーショナルデータベースとは？

リレーショナルデータベースは、データをテーブルの形で保存するデータベースの一種です。このテーブルは行と列から成り立っています。

2. カラムとは？

カラムは、テーブルの「列」のことを指します。例えば、学生の情報を保存するテーブルを考えた場合、以下のようなカラムを持つことが考えられます

学生ID
名前
年齢
メールアドレス
上記の各カラムは、学生の特定の属性を表しています。

3. カラムの特性

データ型：各カラムは、保存されるデータの型を持っています。例えば、年齢は整数型、メールアドレスは文字列型など。
ユニーク性：特定のカラム（例：学生ID）は、テーブル内でユニーク（一意）な値を持つことが必要な場合があります。
Null許容：カラムにはデータが保存されていない場合、Null（値がない状態）として設定することができます。ただし、Nullを許容しないカラムもあります。
デフォルト値：カラムにはデフォルトの値を設定することができます。データが明示的に入力されない場合、このデフォルト値が自動的に設定されます。

4. まとめ

RDBのカラムは、テーブルの中の一つの「列」を表しており、それぞれのカラムは特定のデータ型と属性を持っています。データベースを設計する際、どのようなカラムを持つテーブルが必要かを考え、それに応じてカラムのデータ型や属性を設定することが大切です。

SELECT句とは？

SELECT句は、データベースから情報を取得する際に使用するSQLの基本的なコマンドです。具体的には、データベース内のテーブルから必要なカラム（列）のデータを選択して取得するための命令です。

基本構文

以下は、SELECTの基本構文です。

SELECT [カラム名1, カラム名2, ...]
FROM [テーブル名]
WHERE [条件];

カラム名: 取得したいテーブルのカラム（列）の名前。
テーブル名: データを取得したいテーブルの名前。
条件: データを取得する際の条件（オプション）。
例
以下のようなテーブルEmployeesを考えます。

employee_id first_name last_name salary
1 Taro Yamada 50000
2 Hanako Suzuki 55000
3 Ichiro Tanaka 53000

Employeesの生成

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    first_name NVARCHAR(50),
    last_name NVARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO Employees (employee_id, first_name, last_name, salary)
VALUES 
(1, 'Taro', 'Yamada', 50000),
(2, 'Hanako', 'Suzuki', 55000),
(3, 'Ichiro', 'Tanaka', 53000);

FROM句

FROM句は、どのテーブルからデータを取得するかを指定するための命令です。

例

SELECT first_name, last_name
FROM Employees;

この例では、Employeesテーブルからデータを取得しています。

SELECT句

全てのカラムを取得する場合

SELECT *
FROM Employees;

「*」 は「全てのカラムを意味します。結果として上記の全ての行とカラムを取得します。

特定のカラムのみを取得する場合

SELECT first_name, last_name
FROM Employees;

このクエリは、first_nameとlast_nameのみを取得します。

WHERE句

WHERE句は、特定の条件を満たす行のみを取得するためのフィルタリングを行う命令です。

基本構文

SELECT [カラム名1, カラム名2, ...]
FROM [テーブル名]
WHERE [条件];

例
salaryが52,000より大きい従業員のデータのみを取得してください。

SELECT *
FROM Employees
WHERE salary > 52000;

first_nameが"Taro"の従業員のデータを取得してください。

SELECT *
FROM Employees
WHERE first_name = 'Taro';

まとめ
SELECTは、データベースからデータを"選択"して取得するコマンドです。
必要なカラムや条件を指定して、特定のデータのみを取得することができます。
SELECT句はSQLの中心となる部分であり、データの取得や分析の基本となります。
データベースの操作は、このSELECT句を始めとする様々な命令を組み合わせることで、非常に複雑なデータ操作や分析を行うことができます。

基本的な演算子

基本的な比較演算子

=　　 等しい
<> 　 等しくない
< 　　小さい
> 　　大きい
<= 　 以下
>= 　 以上

複数の条件の組み合わせ

AND すべての条件が真の場合に真を返します。
例: age > 20 AND name = 'John'

OR 条件のいずれかが真の場合に真を返します。
例: age < 20 OR age > 60

NOT 条件が真でない場合に真を返します。
例: NOT age = 30

デモ1
Employees テーブルから、25歳以上の従業員のリストを取得します。

SELECT *
FROM Employees
WHERE age >= 25;

同じテーブルから、名前が "John" または "Jane" の従業員のリストを取得します。

SELECT *
FROM Employees
WHERE first_name IN ('John', 'Jane');

または

SELECT *
FROM Employees
WHERE first_name = 'John' OR first_name = 'Jane';

25歳未満、または50歳以上の従業員のリストを取得します。

SELECT *
FROM Employees
WHERE age < 25 OR age >= 50;

ORDER BY句

ORDER BY句は、SQLのSELECT文において、取得した結果セットの並び順を指定するための句です。これにより、結果セットを特定のカラムの値に基づいて昇順（ASC）または降順（DESC）でソートすることができます。

基本的な使用方法

SELECT [カラム名1, カラム名2, ...]
FROM [テーブル名]
ORDER BY [ソートするカラム名] [ASC | DESC];

ASC: 昇順でのソート。これはデフォルトのソート順序です。
DESC: 降順でのソート。

例

従業員の名前（first_name）で昇順にソート

SELECT first_name, last_name
FROM Employees
ORDER BY first_name ASC;

従業員の給料（salary）で降順にソート

SELECT first_name, last_name, salary
FROM Employees
ORDER BY salary DESC;

複数の列でのソート

ORDER BY句では、カンマを使用して複数のカラムを指定し、その順序で結果セットをソートすることができます。例えば、姓（last_name）で昇順にソートし、それに続いて名前（first_name）で昇順にソートする場合は次のようになります。

SELECT first_name, last_name
FROM Employees
ORDER BY last_name ASC, first_name ASC;

注意点

ORDER BY句はSELECT文の最後に配置する必要があります。
ソートに使用するカラムは、SELECT文で指定したカラムである必要はありません。つまり、表示されないカラムでもソートの基準として使用できます。

まとめ

ORDER BY句は、SQLクエリの結果を特定の順序で表示するための非常に有用なツールです。適切に使用することで、データの解析や表示が効率的に行えます。

エイリアス

エイリアスとは？

エイリアスは、SQLクエリの中でテーブルやカラムに一時的な名前（ニックネーム）を割り当てるためのものです。エイリアスは、クエリの可読性を向上させるためや、複数のテーブルをJOINする際にテーブル名の衝突を避けるため、または計算や関数を使用した結果に名前を付ける際などに使用されます。

カラムのエイリアス

カラムのエイリアスは、特にSELECT句内でよく使用されます。

使用方法

SELECT [カラム名] AS [エイリアス名]
FROM [テーブル名];

例

SELECT first_name AS FName, last_name AS LName
FROM Employees;

このクエリは、first_nameというカラム名をFNameという名前で、last_nameをLNameという名前で表示します。

テーブルのエイリアス

テーブルのエイリアスは、特にJOIN操作やサブクエリでのテーブル参照を簡略化するために使用されます。

使用方法

SELECT [エイリアス名].[カラム名]
FROM [テーブル名] AS [エイリアス名];

例

SELECT E.FName, D.department_name
FROM Employees AS E
JOIN Departments AS D ON E.department_id = D.id;

このクエリは、EmployeesテーブルとDepartmentsテーブルをJOINし、それぞれのテーブルをEおよびDというエイリアスで参照しています。

エイリアスの主な利点は、クエリの可読性を向上させること、特に複雑なクエリや複数のテーブルをJOINする場合にクエリをシンプルに保つことです。T-SQLでは、ASキーワードを使用してエイリアスを定義することが標準的ですが、ASキーワードを省略してもエイリアスを定義することができます。

出力数の制御

TOP句

関数

  1. 文字列関数
    CONCAT(): ２つ以上の文字列を結合します。
    LEN() 文字列の長さを返します。
    UPPER() / LOWER(): 文字列を大文字/小文字に変換します。
    LTRIM() / RTRIM(): 文字列の左端/右端の空白を削除します。
    SUBSTRING(): 文字列の部分文字列を抽出します。
  2. 数値関数
    ABS(): 絶対値を返します。
    ROUND(): 数値を指定した桁数で四捨五入します。
    CEILING() / FLOOR(): 数値を切り上げ/切り捨てします。
    SQRT(): 平方根を計算します。
  3. 日付・時間関数
    GETDATE(): 現在の日付と時間を返します。
    DATEDIFF(): 二つの日付間の差を計算します。
    DATEADD(): 指定した日数、月数、年数を日付に加算/減算します。
    FORMAT(): 日付を指定した形式でフォーマットします。
  4. 変換関数
    CAST() / CONVERT(): 一つのデータ型を別のデータ型に変換します。
    ISNULL(): 式がNULLであれば別の値を返します。
  5. 集約関数
    AVG(): 平均値を計算します。
    COUNT(): 行の数をカウントします。
    SUM(): 合計値を計算します。
    MAX() / MIN(): 最大値/最小値を返します。
  6. 論理関数
    CASE: 条件に基づく値を返します。
    IIF(): IF-ELSEのような論理テストを実施し、２つの値のうちの一つを返します。
    CHOOSE(): インデックスに基づいてリストから値を返します。
    上記はT-SQLの関数のごく一部です。T-SQLは、これらの基本的な関数を超えて、更に高度な処理や操作をサポートするための多くの関数や機能を提供しています。必要に応じて公式のドキュメントやリファレンスを参照することで、より詳しい情報や使用方法を確認することができます。

例
文字列の大文字変換

SELECT UPPER([カラム名])
FROM [テーブル名];

年齢の平均値計算

SELECT AVG(age)
FROM Employees;

これらの機能を組み合わせることで、SQLクエリを非常に柔軟に作成することができます。特定のニーズに合わせてデータを取得、変換、ソートする能力は、効果的なデータ分析のための基本です。

JOIN

JOINは、RDBMSの中核をなす強力な操作の一つであり、複数のテーブルの行を関連付けることで、一つの結果セットを取得するための手法です。T-SQLを含む多くのSQL方言で利用できます。
https://learn.microsoft.com/ja-jp/sql/relational-databases/performance/joins?view=sql-server-ver16
以下は、JOINの主なタイプとその使用方法に関する説明です。

INNER JOIN

最も一般的なJOINタイプ。2つのテーブル間の指定されたカラムの値が一致する行のみを取得します。

SELECT A.column1, B.column2, ...
FROM tableA A
INNER JOIN tableB B ON A.common_column = B.common_column;

LEFT JOIN (または LEFT OUTER JOIN)

左テーブルのすべての行と、右テーブルの一致する行を取得します。もし一致する行が右テーブルにない場合、その部分はNULLとして表示されます。

SELECT A.column1, B.column2, ...
FROM tableA A
LEFT JOIN tableB B ON A.common_column = B.common_column;

RIGHT JOIN (または RIGHT OUTER JOIN)

右テーブルのすべての行と、左テーブルの一致する行を取得します。もし一致する行が左テーブルにない場合、その部分はNULLとして表示されます。

SELECT A.column1, B.column2, ...
FROM tableA A
RIGHT JOIN tableB B ON A.common_column = B.common_column;

FULL JOIN (または FULL OUTER JOIN)

左右のテーブルのすべての行を取得します。一致しない行がある場合、その部分はNULLとして表示されます。

SELECT A.column1, B.column2, ...
FROM tableA A
FULL JOIN tableB B ON A.common_column = B.common_column;

CROSS JOIN

2つのテーブルのすべての組み合わせを生成します。これはテーブル間の直積を取得する操作です。

SELECT A.column1, B.column2, ...
FROM tableA A
CROSS JOIN tableB B;

JOINの注意点
JOINを使用する際は、パフォーマンスへの影響を考慮することが重要です。不要なカラムを取得したり、適切なインデックスがない大きなテーブルをJOINすると、クエリの実行時間が大幅に増加する可能性があります。
JOINの条件を正しく指定することが重要です。誤った条件を指定すると、期待される結果が得られない、または不完全な結果が得られることがあります。
T-SQLでのJOIN操作は非常に強力であり、複数のテーブル間の関係を利用して、複雑なデータ解析やレポートの作成を可能にします

演習

1. テーブル定義 (DDL)

-- Employees テーブルの DDL
CREATE TABLE Employees (
    employeeID INT PRIMARY KEY,
    firstName VARCHAR(50),
    lastName VARCHAR(50),
    departmentID INT
);

-- Departments テーブルの DDL
CREATE TABLE Departments (
    departmentID INT PRIMARY KEY,
    departmentName VARCHAR(100)
);

2. データの挿入

-- Departments テーブルにデータを挿入
INSERT INTO Departments (departmentID, departmentName)
VALUES (1, 'IT'),
       (2, 'Sales'),
       (3, 'HR');

-- Employees テーブルにデータを挿入
INSERT INTO Employees (employeeID, firstName, lastName, departmentID)
VALUES (1, 'John', 'Doe', 1),
       (2, 'Jane', 'Smith', 2),
       (3, 'Alice', 'Johnson', 2),
       (4, 'Bob', 'Brown', 3),
       (5, 'Charlie', 'Davis', 1);

3. 演習

i)
EmployeesテーブルとDepartmentsテーブルをJOINし、従業員の名前と所属する部門名を取得してください。
ii)
Sales部門に所属するすべての従業員の名前を取得してください。
iii)
部門に所属していない従業員の名前を取得してください。

答え
i)

SELECT firstName, lastName, departmentName
FROM Employees E
INNER JOIN Departments D ON E.departmentID = D.departmentID;

ii)

SELECT firstName, lastName
FROM Employees E
INNER JOIN Departments D ON E.departmentID = D.departmentID
WHERE D.departmentName = 'Sales';

iii)

SELECT firstName, lastName
FROM Employees E
LEFT JOIN Departments D ON E.departmentID = D.departmentID
WHERE D.departmentName IS N

おまけ
CROSS JOIN

SELECT *
FROM Employees 
CROSS JOIN Departments ;

SELECT *
FROM Employees 
CROSS JOIN Departments 
where Employees.departmentID = Departments.departmentID;

出力範囲の制御

TOP句

T-SQLにおけるTOP句は、クエリの結果セットから指定された数または指定されたパーセンテージの行だけを返すために使用されます。これは、特に大量のデータを持つテーブルからサンプルデータを取得する場合や、最も新しいデータや最も古いデータのみを取得する場合など、結果セットの一部だけを取得する必要がある場合に非常に便利です。

基本的な使用方法

数値での指定

SELECT TOP 10 * FROM Employees;

上記のクエリはEmployeesテーブルから最初の10行だけを返します。

パーセンテージでの指定

SELECT TOP(10) PERCENT * FROM Employees;

上記のクエリはEmployeesテーブルの全行数の10%の行を返します。

ORDER BYとの組み合わせ
TOP句はしばしばORDER BY句と組み合わせて使用されます。これにより、最大または最小の値を持つ行や、最も新しいまたは最も古い日付の行など、特定の基準に基づいて上位の行を取得することができます。

例

-- 最も新しい10件の注文を取得
SELECT TOP 10 * FROM Orders
ORDER BY OrderDate DESC;

WITH TIESオプション
TOP句にWITH TIESを追加すると、ORDER BY句で指定されたカラムの値が最後の行と同じである追加の行が結果に含まれるようになります。これは、ランキングやスコアが同じ行をすべて取得したい場合などに便利です。

例

-- スコアがトップ3の学生を取得（同じスコアの学生も全て取得）
SELECT TOP 3 WITH TIES * FROM Students
ORDER BY Score DESC;

注意点
TOP句はT-SQLの特有のものであり、他のSQLデータベースでは異なる方法（例：LIMIT句）で同様の操作が提供されていることが多いです。
TOP句を使用する際は、返される行の順序が重要である場合は必ずORDER BY句と組み合わせるようにしてください。そうしないと、どの行が返されるかは不確定となります。
以上が、T-SQLのTOP句の基本的な解説です。

OFFSETとFETCH

SQL Server 2012以降、T-SQLはページングを効率的にサポートするための新しい構文、OFFSETとFETCHを導入しました。これを使ってページングを実装する方法について解説します。

OFFSET ... FETCH の基本的な使用方法

基本的な構文

SELECT [column_names]
FROM [table_name]
ORDER BY [order_column]
OFFSET [offset_rows] ROWS
FETCH NEXT [fetch_rows] ROWS ONLY;

例
10行目から20行目までのデータを取得する場合のクエリ

SELECT *
FROM Employees
ORDER BY EmployeeID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

ページングの例
ページサイズが10行の場合、3ページ目のデータを取得するクエリは以下のようになります

SELECT *
FROM Employees
ORDER BY EmployeeID
OFFSET 20 ROWS  -- (3-1)*10
FETCH NEXT 10 ROWS ONLY;

注意点
OFFSETとFETCHを使用する場合は、必ずORDER BY句が必要です。
OFFSETだけを使用して、指定した数の行をスキップすることも可能です。ただし、通常、FETCHと組み合わせて使用するのが一般的です。
まとめ
OFFSETとFETCHを利用することで、SQL Serverではページングが簡単かつ効率的に行えるようになりました。Webサービスやアプリケーションの開発において、これらの構文を駆使することでユーザーエクスペリエンスを向上させることができます。

次回

集計操作

T-SQLにおける集計操作に関する基本

基本的な集計関数

T-SQLには、データの集計に役立つ多くの組み込み関数があります。以下はその中で最も一般的に使用される関数のリストです

COUNT(): 件数をカウントします。
SUM(): 値の合計を計算します。
AVG(): 平均値を計算します。
MIN(): 最小値を取得します。
MAX(): 最大値を取得します。
例

SELECT 
    COUNT(*) AS TotalEmployees,
    AVG(Salary) AS AverageSalary,
    SUM(Salary) AS TotalSalary,
    MAX(Salary) AS HighestSalary,
    MIN(Salary) AS LowestSalary
FROM Employees;

GROUP BY句

GROUP BY句は、指定したカラムの値ごとに行をグループ化し、それぞれのグループに対して集計操作を行うために使用されます。

例
各部門の従業員数を取得

SELECT DepartmentID, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID;

HAVING句

HAVING句は、GROUP BY句と一緒に使用され、グループ化されたデータに対してフィルタリングを行います。WHERE句とは異なり、HAVING句は集計関数の結果に基づいてフィルタリングを行うことができます。

例
平均給与が50000以上の部門のみを取得

SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) >= 50000;

DISTINCTキーワード

DISTINCTキーワードは、特定のカラムのユニークな値のみを取得するために使用されます。

例：すべてのユニークな部門IDを取得

SELECT DISTINCT DepartmentID
FROM Employees;

まとめ
T-SQLの集計関数と句を利用することで、データの集計や分析を効率的に行うことができます。特に、ビジネスの意思決定やレポート作成などのタスクにおいて、これらの機能は非常に価値があります。

0
4
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
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?