シアトル生活はじめました

20年以上すんだ東海岸から西海岸に引っ越してきました。MicrosoftのUniversal Storeで働いてます。

SQL Server 初心者向けハンズオン練習スクリプト

SQLはどちらかというと今まで回避してきたんだけど、仕事でどうしても必要なんで最近はよく使う。昔から習っては忘れ、忘れては習い、の繰り返し。

SQLのコアになるコンセプトを1ページにまとめました。

https://dl.dropboxusercontent.com/u/6812459/Tutorial.ja.sql

リンクと同じ内容を下にもアップします。

まずはSQL Server Management Studio 等で「Test」とう名前のデータベースを作ります。

次にスクリプトManagement Studioで開けて(もしくはこの記事からコピペ)、F5で実行すれば、いろんなSQLのオブジェクトを作っては消し、をやります。スクリプト実行が完了した段階ですべてクリーンにするようにしてます。

「練習」のコードブロックをマウスで選択してF5実行してもいいです。ひとまとまりのコンセプトが理解できるようになってます。

初心者の方は、上から練習をひとつづつ実行していくのがよいかと。

変な状態になったらまたF5で全部実行すれば元に戻ります。

----------------- スクリプトはこの下の行から -----------------

USE Test

-- ******************************** 練習 01 *******************************
-- 目的: テーブルがない場合、新たに作成する。
--
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Session')
BEGIN
CREATE TABLE Session (SessionKey INT, SessionTime DATETIME)
END
SELECT * FROM Session

-- ******************************** 練習 02 *******************************
-- 目的: 行の挿入。
--
INSERT INTO Session VALUES (0, '20120618 10:34:09 AM')
INSERT INTO Session VALUES (0, '20120618 10:34:09 AM')
INSERT INTO Session VALUES (0, '20120618 10:34:09 AM')
SELECT * FROM Session

-- ******************************** 練習 03 *******************************
-- 目的: 行の削除。
-- 備考: テーブルそのものを削除するものではない。
--
DELETE Session
SELECT * FROM Session

-- ******************************** 練習 04 *******************************
-- 目的: 主キーを作成する。
-- 備考: 値は一意的でなくてはならない。
--
ALTER TABLE Session ALTER COLUMN SessionKey INT NOT NULL
GO
ALTER TABLE Session ADD CONSTRAINT pk_Session PRIMARY KEY (SessionKey)
GO
INSERT INTO Session VALUES (0, '20120618 10:34:09 AM')
INSERT INTO Session VALUES (1, '20120618 10:34:09 AM')
INSERT INTO Session VALUES (2, '20120618 10:34:09 AM')
SELECT * FROM Session
DELETE Session

-- ******************************** 練習 05 *******************************
-- 目的: 自動的に値が決められる主キーを作成する。
-- 備考: 例えば1、2、3、のような値が自動生成される。
--
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Session') DROP TABLE Session
GO
CREATE TABLE Session (
SessionKey INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_Session PRIMARY KEY (SessionKey),
SessionTime DATETIME)
GO

INSERT INTO Session VALUES ('20120618 10:34:09 AM')
INSERT INTO Session VALUES ('20120618 10:34:09 AM')
INSERT INTO Session VALUES ('20120618 10:34:09 AM')
SELECT * FROM Session
DELETE Session

-- ******************************** 練習 06 *******************************
-- 目的: 複合キーを作成する。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Session') DROP TABLE Session
GO
CREATE TABLE Session (
SessionKey INT NOT NULL IDENTITY(1,1),
PlayerKey INT NOT NULL,
SessionTime DATETIME,
CONSTRAINT pk_Session PRIMARY KEY (SessionKey, PlayerKey))
GO

INSERT INTO Session VALUES (20, '20120618 10:34:09 AM')
INSERT INTO Session VALUES (22, '20120618 10:34:09 AM')
INSERT INTO Session VALUES (24, '20120618 10:34:09 AM')
SELECT * FROM Session
DELETE Session

-- ******************************** 練習 07 *******************************
-- 目的: SELECTステートメントの例。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Session') DROP TABLE Session
GO
CREATE TABLE Session (
SessionKey INT,
Category varchar(50),
SessionTime DATETIME)
GO

INSERT INTO Session VALUES (1, 'Test', '20120618 10:00:00 AM')
INSERT INTO Session VALUES (2, 'Test', '20120618 10:05:00 AM')
INSERT INTO Session VALUES (3, 'Sample', '20120618 10:10:00 AM')
INSERT INTO Session VALUES (4, 'Demo', '20120618 10:15:10 AM')
INSERT INTO Session VALUES (5, 'Demonstration', '20120618 10:20:10 AM')
INSERT INTO Session VALUES (6, NULL, '20120618 10:25:10 AM')

-- テーブルのすべての行とそのフィールド。
SELECT * FROM Session
-- 特定のフィールドのみ。
SELECT SessionKey FROM Session
-- 特定のフィールドに別の名前を付ける。
SELECT SessionKey as 'Session ID' FROM Session
-- 条件に合う行のみ。
SELECT * FROM Session WHERE SessionTime <= '20120618 10:10:00 AM'
-- 論理ANDを含む条件に合う行のみ。
SELECT * FROM Session WHERE SessionTime > '20120618 10:05:00 AM' AND SessionTime < '20120618 10:20:00 AM'
-- 条件に合った行の、特定のフィールドのみ。
SELECT Category FROM Session WHERE SessionKey = 1
-- NULL判定。
SELECT * FROM Session WHERE Category IS NULL
-- NOT NULL判定。
SELECT * FROM Session WHERE Category IS NOT NULL
-- フィールドが特定の文字列。
SELECT * FROM Session WHERE Category = 'Test'
-- フィールドが特定の文字列で始まる。
SELECT * FROM Session WHERE Category LIKE 'Demo%'
-- フィールドが特定の文字列で終わる。
SELECT * FROM Session WHERE Category LIKE '%le'
-- フィールドが特定の文字列を含む。
SELECT * FROM Session WHERE Category LIKE '%st%'
-- フィールドの値、重複なし。
SELECT DISTINCT Category FROM Session
-- 行の総数。
SELECT COUNT(*) FROM Session
-- 重複のない特定フィールドの値とそれを含む行の総数。
SELECT Category, COUNT(Category) FROM Session GROUP BY Category
-- 行を昇順に並べる。 (1, 2, 3, a, b, c)
SELECT * FROM Session ORDER BY Category
-- 行を降順に並べる。 (3, 2, 1, c, b, a)
SELECT * FROM Session ORDER BY Category DESC

-- ******************************** 練習 08 *******************************
-- 目的: フィールドを更新したり、行を削除したりする。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Session') DROP TABLE Session
GO
CREATE TABLE Session (
SessionKey INT,
Category varchar(50),
SessionTime DATETIME)
GO

INSERT INTO Session VALUES (1, 'Test', '20120618 10:00:00 AM')
INSERT INTO Session VALUES (2, 'Test', '20120618 10:05:00 AM')
INSERT INTO Session VALUES (3, 'Sample', '20120618 10:10:00 AM')
INSERT INTO Session VALUES (4, 'Demo', '20120618 10:15:10 AM')
INSERT INTO Session VALUES (5, 'Demonstration', '20120618 10:20:10 AM')
INSERT INTO Session VALUES (6, NULL, '20120618 10:25:10 AM')

-- Categoryフィールドの値が 'Demo'か'Demonstration'の場合、'Experiment'に変更。
UPDATE Session SET Category = 'Experiment' WHERE Category LIKE 'Demo%'
SELECT * FROM Session

-- すべての行のSessionTimeフィールドから一か月引いた値に変更。
UPDATE Session SET SessionTime = DATEADD(MONTH, -1, SessionTime)
SELECT * FROM Session

-- CategoryフィールドがNULLの行を削除。
DELETE Session WHERE Category IS NULL
SELECT * FROM Session

-- Categoryフィールドが指定された値に当てはまる行を削除。
DELETE Session WHERE SessionKey IN (1, 2, 3)
SELECT * FROM Session

-- ******************************** 練習 09 *******************************
-- 目的: 一時テーブルを使う。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Session') DROP TABLE Session
GO
CREATE TABLE Session (
SessionKey INT,
Category varchar(50),
SessionTime DATETIME)
GO

INSERT INTO Session VALUES (1, 'Test', '20120618 10:00:00 AM')
INSERT INTO Session VALUES (2, 'Test', '20120618 10:05:00 AM')
INSERT INTO Session VALUES (3, 'Sample', '20120618 10:10:00 AM')
INSERT INTO Session VALUES (4, 'Demo', '20120618 10:15:10 AM')
INSERT INTO Session VALUES (5, 'Demonstration', '20120618 10:20:10 AM')
INSERT INTO Session VALUES (6, NULL, '20120618 10:25:10 AM')

IF OBJECT_ID('tempdb..#saved') IS NOT NULL DROP TABLE #saved
GO
CREATE TABLE #saved (Category varchar(50), Count INT)
GO

INSERT INTO #saved SELECT Category, COUNT(Category) FROM Session GROUP BY Category
SELECT * FROM #saved

DROP TABLE #saved


-- ******************************** 練習 10 *******************************
-- 目的: 複数のテーブルを結合する。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Session') DROP TABLE Session
GO
CREATE TABLE Session (
SessionKey INT,
Category varchar(50),
PlayerKey INT,
SessionTime DATETIME)
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Player') DROP TABLE Player
GO
CREATE TABLE Player (
PlayerKey INT,
Name varchar(50),
MembershipStart DATE)
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Admin') DROP TABLE Admin
GO
CREATE TABLE Admin (
AdminKey INT,
Department varchar(50))
GO

INSERT INTO Player VALUES (10, 'Adam', '2015-12-04')
INSERT INTO Player VALUES (20, 'Bob', '2014-09-04')
INSERT INTO Player VALUES (30, 'Chris', '2009-05-15')
INSERT INTO Player VALUES (40, 'Dan', '2013-02-14')
INSERT INTO Player VALUES (50, 'Ed', '2013-02-14')
INSERT INTO Admin VALUES (40, 'Lab')
INSERT INTO Admin VALUES (50, 'Business')

INSERT INTO Session VALUES (1, 'Test', 10, '20120618 10:00:00 AM')
INSERT INTO Session VALUES (2, 'Test', 20, '20120618 10:05:00 AM')
INSERT INTO Session VALUES (3, 'Sample', 20, '20120618 10:10:00 AM')
INSERT INTO Session VALUES (4, 'Demo', 10, '20120618 10:15:10 AM')
INSERT INTO Session VALUES (5, 'Demonstration', 40, '20120618 10:20:10 AM')
INSERT INTO Session VALUES (6, NULL, NULL, '20120618 10:25:10 AM')

-- 二つのテーブルを結合。
SELECT * FROM Session s
JOIN Player p
ON s.PlayerKey = p.PlayerKey

-- PlayerKeyの代わりにPlayerのNameを表示。
SELECT SessionKey, Category, p.Name 'PlayedName' FROM Session s
JOIN Player p
ON s.PlayerKey = p.PlayerKey

-- Bobとう名前のユーザーのSessionのみ。
SELECT * FROM Session s
JOIN Player p
ON s.PlayerKey = p.PlayerKey
WHERE p.Name = 'Bob'

-- 三つのテーブルを結合。
SELECT p.Name, s.SessionTime, a.Department FROM Session s
JOIN Player p
ON s.PlayerKey = p.PlayerKey
JOIN Admin a
ON a.AdminKey = p.PlayerKey

-- 二つのテーブルを結合。今回は右にPlayer、左にSessionテーブルを指定。
SELECT * FROM Player p
JOIN Session s
ON p.PlayerKey = s.PlayerKey

-- 二つのテーブルを結合。仮に右のテーブルと結合できなくても右のテーブルの行は
-- すべて結果に残す。
SELECT * FROM Player p
LEFT OUTER JOIN Session s
ON p.PlayerKey = s.PlayerKey

-- ひとつもSessionと関係していないPlayerのMembershipStartフィールドをNULLにする。
UPDATE Player SET MembershipStart = NULL FROM Player p
LEFT OUTER JOIN Session s
ON p.PlayerKey = s.PlayerKey
WHERE SessionKey IS NULL
SELECT * FROM Player

-- 三つのテーブルを結合。右のテーブルに該当する行がない場合、そのフィールドはすべて
-- NULLになる。そのことを利用してAdminNameフィールドの値を決定する。
SELECT s.SessionTime,
s.Category,
CASE WHEN a.AdminKey IS NULL THEN 'N/A'
ELSE p.Name END as 'AdminName'
FROM Session s
JOIN Player p
ON s.PlayerKey = p.PlayerKey
LEFT OUTER JOIN Admin a
ON a.AdminKey = p.PlayerKey

-- ******************************** 練習 11 *******************************
-- 目的: ヴューを利用して頻繁に使うSELECTステートメントを再利用する。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Session') DROP TABLE Session
GO
CREATE TABLE Session (
SessionKey INT,
Category varchar(50),
PlayerKey INT,
SessionTime DATETIME)
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Player') DROP TABLE Player
GO
CREATE TABLE Player (
PlayerKey INT,
Name varchar(50),
MembershipStart DATE)
GO

INSERT INTO Player VALUES (10, 'Adam', '2015-12-04')
INSERT INTO Player VALUES (20, 'Bob', '2014-09-04')
INSERT INTO Session VALUES (1, 'Test', 10, '20120618 10:00:00 AM')
INSERT INTO Session VALUES (2, 'Test', 20, '20120618 10:05:00 AM')
GO

IF EXISTS(SELECT * FROM sys.views WHERE NAME = 'SessionPayer') DROP VIEW SessionPayer
GO
CREATE VIEW SessionPayer
AS
SELECT SessionKey, Category, p.Name 'PlayedName' FROM Session s
JOIN Player p
ON s.PlayerKey = p.PlayerKey
GO
SELECT * FROM SessionPayer

-- ******************************** 練習 12 *******************************
-- 目的: スカラー値関数を作成する。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Player') DROP TABLE Player
GO
CREATE TABLE Player (
PlayerKey INT,
Name varchar(50),
MembershipStart DATE)
GO

INSERT INTO Player VALUES (10, 'Adam', '2015-12-04')
INSERT INTO Player VALUES (20, 'Bob', '2014-09-04')
INSERT INTO Player VALUES (30, 'Chris', '2009-05-15')
INSERT INTO Player VALUES (40, 'Dan', '2013-02-14')
INSERT INTO Player VALUES (50, 'Ed', '2013-02-14')

IF OBJECT_ID('fnMakeURI') IS NOT NULL DROP FUNCTION fnMakeURI
GO
CREATE FUNCTION fnMakeURI(@name varchar(50))
RETURNS varchar(100)
AS
BEGIN
RETURN (SELECT CONCAT('to:', @name, '@', 'test.com'));
END
GO

SELECT dbo.fnMakeURI(Name) FROM Player

-- ******************************** 練習 13 *******************************
-- 目的: テーブル値関数を作成する。
IF OBJECT_ID('fnGetRateHistory') IS NOT NULL DROP FUNCTION fnGetRateHistory
GO
CREATE FUNCTION fnGetRateHistory(@date DATE)
RETURNS @RateHistory TABLE
(
DateMonth DATE,
Rate FLOAT
)
AS
BEGIN
INSERT INTO @RateHistory VALUES ('2015-01-01', 5.62)
INSERT INTO @RateHistory VALUES ('2015-02-01', 5.64)
INSERT INTO @RateHistory VALUES ('2015-03-01', 5.67)
INSERT INTO @RateHistory VALUES ('2015-04-01', 5.69)
INSERT INTO @RateHistory VALUES ('2015-05-01', 5.71)
INSERT INTO @RateHistory VALUES ('2015-06-01', 5.74)
INSERT INTO @RateHistory VALUES ('2015-07-01', 5.69)
INSERT INTO @RateHistory VALUES ('2015-08-01', 5.65)
INSERT INTO @RateHistory VALUES ('2015-09-01', 5.58)
INSERT INTO @RateHistory VALUES ('2015-10-01', 5.52)
RETURN
END
GO

SELECT * FROM dbo.fnGetRateHistory('2015-01-01')


-- ******************************** 練習 14 *******************************
-- 目的: ストアドプロシージャを作成する。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Session') DROP TABLE Session
GO
CREATE TABLE Session (
SessionKey INT,
Category varchar(50),
PlayerKey INT,
SessionTime DATETIME)
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Player') DROP TABLE Player
GO
CREATE TABLE Player (
PlayerKey INT,
Name varchar(50),
MembershipStart DATE)
GO

INSERT INTO Player VALUES (10, 'Adam', '2015-12-04')
INSERT INTO Player VALUES (20, 'Bob', '2014-09-04')
INSERT INTO Player VALUES (30, 'Chris', '2009-05-15')
INSERT INTO Player VALUES (40, 'Dan', '2013-02-14')
INSERT INTO Player VALUES (50, 'Ed', '2013-02-14')

INSERT INTO Session VALUES (1, 'Test', 10, '20120618 10:00:00 AM')
INSERT INTO Session VALUES (2, 'Test', 20, '20120618 10:05:00 AM')
INSERT INTO Session VALUES (3, 'Sample', 20, '20120618 10:10:00 AM')
INSERT INTO Session VALUES (4, 'Demo', 10, '20120618 10:15:10 AM')
INSERT INTO Session VALUES (5, 'Demonstration', 40, '20120618 10:20:10 AM')
INSERT INTO Session VALUES (6, NULL, NULL, '20120618 10:25:10 AM')

IF (OBJECT_ID('MyProcedure') IS NOT NULL) DROP PROCEDURE MyProcedure
GO
CREATE PROCEDURE MyProcedure
AS
BEGIN
UPDATE Player SET MembershipStart = NULL FROM Player p
LEFT OUTER JOIN Session s
ON p.PlayerKey = s.PlayerKey
WHERE SessionKey IS NULL
END
GO

SELECT * FROM Player -- プロシージャを呼び出す前。
EXEC MyProcedure -- プロシージャを呼び出し。
SELECT * FROM Player -- プロシージャを呼び出した後。

-- ******************************** 練習 15 *******************************
-- 目的: 二つのテーブルをマージする。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GroupX') DROP TABLE GroupX
GO
CREATE TABLE GroupX (
Name varchar(50))
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GroupY') DROP TABLE GroupY
GO
CREATE TABLE GroupY (
Name varchar(50))
GO

INSERT INTO GroupX VALUES ('B')
INSERT INTO GroupX VALUES ('C')

INSERT INTO GroupY VALUES ('A')
INSERT INTO GroupY VALUES ('B')
INSERT INTO GroupY VALUES ('D')

-- ターゲットテーブルにソーステーブルの行が無い場合はターゲットテーブルにINSERTする。
-- 例えば新規申ユーザーのリストからマスターユーザーテーブルに、まだ加えていない場合はINSERTする。
MERGE GroupX t
USING GroupY s
ON t.Name = s.Name
WHEN NOT MATCHED THEN
INSERT VALUES (s.Name);

SELECT * FROM GroupX

DELETE GroupX
DELETE GroupY
GO

INSERT INTO GroupX VALUES ('B')
INSERT INTO GroupX VALUES ('C')

INSERT INTO GroupY VALUES ('A')
INSERT INTO GroupY VALUES ('B')
INSERT INTO GroupY VALUES ('D')

-- ターゲットテーブルにソーステーブルの行がある場合は削除する。
-- 例えばスパム・ユーザーのリストをもとにマスターユーザーテーブルから削除する。
MERGE GroupY t
USING GroupX s
ON t.Name = s.Name
WHEN MATCHED THEN
DELETE;

select * from GroupY


-- ******************************** お掃除 *******************************
IF (OBJECT_ID('MyProcedure') IS NOT NULL) DROP PROCEDURE MyProcedure
IF OBJECT_ID('fnGetRateHistory') IS NOT NULL DROP FUNCTION fnGetRateHistory
IF OBJECT_ID('fnMakeToURI') IS NOT NULL DROP FUNCTION fnMakeURI
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Session') DROP TABLE Session
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Player') DROP TABLE Player
GO