Microsoft SQL Server 튜토리얼
Microsoft SQL Server 시작하기

비고

버전

INSERT / SELECT / UPDATE / DELETE : 데이터 조작 언어의 기초

D의 ATA M의 anipulation 리터 anguage (짧은 DML)는 다음과 같은 작업이 포함 INSERT , UPDATE 및 DELETE :

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld

이 스크립트에서는 몇 가지 기본 쿼리를 보여주기 위해 테이블 을 만듭니다 .

다음 예는 테이블 을 쿼리 하는 방법을 보여줍니다 .

USE Northwind;
GO
SELECT TOP 10 * FROM Customers 
ORDER BY CompanyName

Northwind 데이터베이스에서 CompanyName 열로 정렬 된 Customer 테이블의 처음 10 개 레코드를 선택합니다 (Microsoft의 샘플 데이터베이스 중 하나인데 여기 에서 다운로드 할 수 있음).

참고 Use Northwind; 모든 후속 쿼리에 대한 기본 데이터베이스를 변경합니다. [Database]. [Schema]. [Table] 형식의 정규화 된 구문을 사용하여 데이터베이스를 계속 참조 할 수 있습니다.

SELECT TOP 10 * FROM Northwind.dbo.Customers 
ORDER BY CompanyName

SELECT TOP 10 * FROM Pubs.dbo.Authors
ORDER BY City

다른 데이터베이스의 데이터를 쿼리 할 때 유용합니다. "사이에"지정되는 dbo 는 스키마라고하며 정규화 된 구문을 사용하는 동안 지정해야합니다. 데이터베이스 내에서 폴더로 생각할 수 있습니다. dbo 는 기본 스키마입니다. 기본 스키마는 생략 될 수 있습니다. 다른 모든 사용자 정의 스키마를 지정해야합니다.

데이터베이스 테이블에 예약어 (예 : Date )라는 이름의 열이 있으면 다음과 같이 열 이름을 대괄호로 묶어야합니다.

-- descending order
SELECT TOP 10 [Date] FROM dbo.MyLogTable
ORDER BY [Date] DESC

열 이름에 이름에 공백이있는 경우에도 마찬가지입니다 (권장하지 않음). 대체 구문은 대괄호 대신 큰 따옴표를 사용하는 것입니다 (예 :

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
order by "Date" desc 

동등하지만 그렇게 일반적으로 사용되지는 않습니다. 큰 따옴표와 작은 따옴표의 차이점을 확인하십시오. 작은 따옴표는 문자열에 사용됩니다. 즉

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
where UserId='johndoe'
order by "Date" desc 

올바른 구문입니다. T-SQL에는 NChar 및 NVarchar 데이터 형식에 대한 N 접두사가 있습니다 (예 :

SELECT TOP 10 * FROM Northwind.dbo.Customers 
WHERE CompanyName LIKE N'AL%'
ORDER BY CompanyName

AL 시작하는 회사 이름을 가진 모든 회사를 반환합니다 ( % 는 와일드 카드입니다. DOS 명령 행에서 별표 ( DIR AL* )를 사용하는 것처럼 사용하십시오 (예 : DIR AL* ). LIKE 경우 몇 가지 와일드 카드를 사용할 수 있습니다. 자세한 내용을 보려면 여기 를 클릭 하십시오.

조인

조인은 하나의 단일 테이블에 존재하지 않지만 여러 테이블에있는 필드를 쿼리하려는 경우에 유용합니다. 예 : Northwind 데이터베이스의 Region 테이블에서 모든 열을 쿼리하려고합니다. 하지만 다른 테이블 인 Region 저장되어있는 RegionDescription 도 필요하다는 것을 RegionDescription . 그러나 다음과 RgionID 정보를 단일 쿼리로 결합하는 데 사용할 수있는 공통 키 RgionID 가 있습니다 ( Top 5 는 처음 5 개 행을 반환하고 모든 행을 가져 RgionID 는 않습니다).

SELECT TOP 5 Territories.*, 
    Regions.RegionDescription 
FROM Territories 
INNER JOIN Region 
    ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription

모든 열이 표시됩니다 Territories 플러스 RegionDescription 에서 열 Region . 결과는 TerritoryDescription 의해 정렬됩니다.

테이블 별칭

쿼리에 두 개 이상의 테이블에 대한 참조가 필요하면 테이블 별칭을 사용하는 것이 유용 할 수 있습니다. 테이블 별칭은 전체 테이블 이름 대신 사용할 수있는 테이블에 대한 속기 참조이며 입력 및 편집을 줄일 수 있습니다. 별칭 사용 구문은 다음과 같습니다.

<TableName> [as] <alias>

어디 as 선택적 키워드입니다. 예를 들어 이전 쿼리는 다음과 같이 다시 작성할 수 있습니다.

SELECT TOP 5 t.*, 
    r.RegionDescription 
FROM Territories t
INNER JOIN Region r 
    ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription

별명은 동일한 테이블을 두 x 사용하더라도 u 리의 모든 테이블에 대해 고유해야합니다. 예를 들어, Employee 테이블에 SupervisorId 필드가 포함 된 경우이 쿼리를 사용하여 직원과 감독자의 이름을 반환 할 수 있습니다.

SELECT e.*, 
    s.Name as SupervisorName -- Rename the field for output
FROM Employee e
INNER JOIN Employee s
    ON e.SupervisorId = s.EmployeeId
WHERE e.EmployeeId = 111

노조

이전에 보았 듯이 Join은 다른 테이블 소스의 열을 추가합니다. 하지만 서로 다른 소스의 행을 결합하려면 어떻게해야할까요? 이 경우 UNION을 사용할 수 있습니다. 파티를 계획하고 있으며 직원뿐만 아니라 고객을 초대하기를 원한다고 가정 해보십시오. 그런 다음이 쿼리를 실행하여 수행 할 수 있습니다.

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
UNION
SELECT ContactName, Address, City FROM Customers

직원과 고객의 이름, 주소 및 도시를 단일 테이블로 반환합니다. 중복 행 (있는 경우)이 자동으로 제거됩니다 (이 작업을 원하지 않으면 대신 UNION ALL 사용하십시오). 열 번호, 열 이름, 순서 및 데이터 형식이 유니온의 일부인 모든 SELECT 문에서 일치해야합니다. 첫 번째 SELECT가 FirstName 과 LastName 을 Employee에서 ContactName 으로 결합하는 이유입니다.

표 변수

테이블 변수를 사용하기 위해 임시 데이터 (특히 저장 프로 시저)를 처리해야하는 경우 유용 할 수 있습니다. "실제"테이블과 테이블 변수의 차이는 임시 처리를 위해 메모리에 존재한다는 것입니다.

예:

DECLARE @Region TABLE
(
  RegionID int, 
  RegionDescription NChar(50)
)

메모리에 테이블을 만듭니다. 이 경우 @ 접두사는 변수이기 때문에 필수 항목입니다. 위에서 언급 한 모든 DML 작업을 수행하여 행을 삽입, 삭제 및 선택할 수 있습니다.

INSERT INTO @Region values(3,'Northern')
INSERT INTO @Region values(4,'Southern')

하지만 일반적으로 실제 테이블을 기반으로 데이터를 채 웁니다.

INSERT INTO @Region
SELECT * FROM dbo.Region WHERE RegionID>2;

실제 테이블 dbo.Region 에서 필터링 된 값을 읽고이를 메모리 테이블 @Region 삽입합니다.이 테이블을 추가 처리에 사용할 수 있습니다. 예를 들어, 다음과 같은 조인에서 사용할 수 있습니다.

SELECT * FROM Territories t
JOIN @Region r on t.RegionID=r.RegionID

이 경우 모든 Northern 와 Southern 지역이 반환됩니다. 더 자세한 정보는 여기 에서 찾을 수 있습니다 . 임시 테이블은 해당 주제에 대한 자세한 내용을 보려면 여기를 참조하십시오 .

참고 : 테이블 변수에 데이터 행의 수는 더 많은 양의 데이터로 작업 할 경우, 100 이하이다 임시 테이블, 또는 임시 테이블을 사용하는 경우 Microsoft는 대신 테이블 변수를 사용하는 것이 좋습니다.

인쇄

출력 콘솔에 메시지를 표시합니다. SQL Server Management Studio를 사용하면 결과 탭이 아닌 메시지 탭에 다음과 같이 표시됩니다.

PRINT 'Hello World!';

테이블의 모든 행과 열 선택

통사론:

SELECT *
FROM table_name

별표 연산자 * 사용하면 표의 모든 열을 선택하기위한 바로 가기로 사용됩니다. 이 SELECT 문에는 필터링 조건을 지정하기 위해 WHERE 절이 없기 때문에 모든 행도 선택됩니다.

테이블에 별칭을 추가 한 경우에도 동일한 방식으로 작동합니다 (예 : e .

SELECT *
FROM Employees AS e

또는 특정 테이블에서 모두 선택하려면 별칭 + ". *"을 사용할 수 있습니다.

SELECT e.*, d.DepartmentName
FROM Employees AS e
    INNER JOIN Department AS d 
        ON e.DepartmentID = d.DepartmentID

데이터베이스 개체는 정규화 된 이름을 사용하여 액세스 할 수도 있습니다.

SELECT * FROM [server_name].[database_name].[schema_name].[table_name]

서버 및 / 또는 데이터베이스 이름을 변경하면 잘못된 개체 이름으로 인해 정규화 된 이름을 사용하는 쿼리가 더 이상 실행되지 않으므로 반드시 권장되는 것은 아닙니다.

쿼리가 단일 서버, 데이터베이스 및 스키마에서 각각 실행되는 경우 대부분의 경우 table_name 앞의 필드를 생략 할 수 있습니다. 그러나 데이터베이스에 여러 스키마가있는 것이 일반적이며 이러한 경우 가능한 경우 스키마 이름을 생략해서는 안됩니다.

경고 : 프로덕션 코드 나 저장 프로 시저에서 SELECT * 를 사용하면 나중에 테이블에 새 열이 추가되거나 열이 테이블에 다시 정렬 될 때 문제가 발생할 수 있습니다. 특히 코드에서 열의 순서에 대해 간단한 가정을하고, 또는 리턴 된 컬럼의 수. 따라서 프로덕션 코드의 SELECT 문에 항상 명시 적으로 열 이름을 지정하는 것이 더 안전합니다.

SELECT col1, col2, col3
FROM table_name

조건과 일치하는 행 선택

일반적으로 구문은 다음과 같습니다.

SELECT <column names>
FROM <table name>
WHERE <condition>

예 :

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith'

조건은 복잡 할 수 있습니다.

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith' AND (City = 'New York' OR City = 'Los Angeles')

특정 행 업데이트

UPDATE HelloWorlds
SET HelloWorld = 'HELLO WORLD!!!'
WHERE Id = 5

위의 코드는 "HelloWorld"필드의 값을 "HELLO WORLD !!!"로 업데이트합니다. HelloWorlds 테이블에서 "Id = 5"레코드의 경우.

참고 : 업데이트 문에서 "where"절을 사용하여 요구 사항이 다를 때까지 전체 테이블을 업데이트하지 않는 것이 좋습니다.

모든 행을 업데이트하십시오.

간단한 업데이트 형식은 테이블의 주어진 필드에서 모든 값을 증가시키는 것입니다. 그렇게하기 위해서는 필드와 증분 값을 정의해야합니다.

다음은 Score 필드를 1 씩 증가시키는 예제입니다 (모든 행에서).

UPDATE Scores
SET score = score + 1  

실수로 테이블의 모든 행 에 대한 UPDATE를 사용하여 특정 행 에 대한 UPDATE를 수행하면 데이터가 손상 될 수 있으므로 위험 할 수 있습니다.

코드 주석

Transact-SQL은 두 가지 형식의 주석 작성을 지원합니다. 주석은 데이터베이스 엔진에서 무시되며 사람들이 읽을 수 있도록 만들어졌습니다.

주석 앞에는 -- 가 붙으며 새로운 줄이 나올 때까지 무시됩니다.

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;

슬래시 별 주석은 /* 시작하고 */ 끝납니다. 이러한 구분 기호 사이의 모든 텍스트는 주석 블록으로 간주됩니다.

/* This is
a multi-line
comment block. */
SELECT Id = 1, [Message] = 'First row'
UNION ALL
SELECT 2, 'Second row'
/* This is a one liner */
SELECT 'More';

슬래시 별 주석에는 SQL 문에 줄 바꿈 문자가 없으면 주석을 사용할 수있는 장점이 있습니다. 이 문제는 문제 해결 중에 SQL이 캡처 될 때 발생할 수 있습니다.

슬래시 별 주석은 중첩 될 수 있으며 슬래시 별 주석 내부의 시작 /* 은 */ 로 끝나야 유효합니다. 다음 코드는 오류를 발생시킵니다.

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/

따옴표 안에 댓글의 시작으로 간주되지만 슬래시 별. 따라서 다른 닫는 별표로 끝내야합니다. 올바른 방법은

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/  */

기본 서버 정보 검색

SELECT @@VERSION

인스턴스에서 실행중인 MS SQL Server의 버전을 반환합니다.

SELECT @@SERVERNAME

MS SQL Server 인스턴스의 이름을 반환합니다.

SELECT @@SERVICENAME

MS SQL Server가 실행중인 Windows 서비스의 이름을 반환합니다.

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

SQL Server가 실행중인 컴퓨터의 실제 이름을 반환합니다. 장애 조치 클러스터에서 노드를 식별하는 데 유용합니다.

SELECT * FROM fn_virtualservernodes();

장애 조치 클러스터에서는 SQL Server를 실행할 수있는 모든 노드를 반환합니다. 클러스터가 아니면 아무것도 반환하지 않습니다.

거래를 사용하여 안전하게 데이터 변경하기

데이터를 변경할 때마다 DML (Data Manipulation Language) 명령에서 변경 내용을 트랜잭션으로 래핑 할 수 있습니다. DML에는 UPDATE , TRUNCATE , INSERT 및 DELETE 됩니다. 올바른 데이터를 변경하고 있는지 확인하는 방법 중 하나는 트랜잭션을 사용하는 것입니다.

DML 변경 사항은 영향을받는 행을 잠급니다. 트랜잭션을 시작할 때 트랜잭션을 종료해야합니다. 그렇지 않으면 DML에서 변경된 모든 개체가 트랜잭션을 시작한 사람에 의해 잠겨 있습니다. ROLLBACK 또는 COMMIT 를 사용하여 트랜잭션을 종료 할 수 있습니다. ROLLBACK 은 트랜잭션 내의 모든 것을 원래 상태로 되돌립니다. COMMIT 은 다른 DML 문없이 변경 사항을 실행 취소 할 수없는 최종 상태로 데이터를 배치합니다.

예:

--Create a test table

USE [your database]
GO
CREATE TABLE test_transaction (column_1 varchar(10))
GO

INSERT INTO 
 dbo.test_transaction
        ( column_1 )
VALUES
        ( 'a' )

BEGIN TRANSACTION --This is the beginning of your transaction

UPDATE dbo.test_transaction
SET column_1 = 'B'
OUTPUT INSERTED.*
WHERE column_1 = 'A'
  

ROLLBACK TRANSACTION  --Rollback will undo your changes
           --Alternatively, use COMMIT to save your results

SELECT * FROM dbo.test_transaction   --View the table after your changes have been run

DROP TABLE dbo.test_transaction

노트:

• 이것은 오류 처리를 포함하지 않는 단순화 된 예 입니다. 그러나 모든 데이터베이스 작업이 실패 할 수 있으므로 예외가 발생합니다. 다음은 그러한 필수적인 오류 처리가 어떻게 보이는지에 대한 예제 입니다. 오류 핸들러없이 트랜잭션 을 사용하면 안됩니다 . 그렇지 않으면 트랜잭션을 알 수없는 상태로 둘 수 있습니다.
• 격리 수준 에 따라 트랜잭션은 조회되거나 변경되는 데이터에 잠금을 설정합니다. 데이터베이스의 레코드를 잠그고 다른 병렬 실행 트랜잭션과의 교착 상태 로 이어질 수 있으므로 트랜잭션이 오랜 시간 실행되지 않도록해야합니다. 거래에 캡슐화 된 작업을 가능한 한 짧게 유지하고 잠금중인 데이터의 양에 대한 영향을 최소화하십시오.

모든 행 삭제

DELETE
FROM Helloworlds

이렇게하면 테이블의 모든 데이터가 삭제됩니다. 이 코드를 실행하면 테이블에 행이 없습니다. DROP TABLE 과 달리, 이것은 테이블 자체와 그 구조를 보존하며 계속해서 그 테이블에 새로운 행을 삽입 할 수 있습니다.

테이블의 모든 행을 삭제하는 또 다른 방법은 다음과 같이 잘라냅니다.

TRUNCATE TABLE HelloWords

DELETE 연산의 차이점은 다음과 같습니다.

1. 자르기 작업이 트랜잭션 로그 파일에 저장되지 않습니다.
2. IDENTITY 필드가 있으면이 값이 재설정됩니다.
3. TRUNCATE는 전체 테이블에 적용될 수 있으며 일부 테이블에는 적용되지 않습니다 ( DELETE 명령을 사용하면 WHERE 절을 연결할 수 있음)

TRUNCATE의 제한 사항

1. FOREIGN KEY 참조가있는 경우 테이블을 TRUNCATE 할 수 없습니다.
2. 테이블이 INDEXED VIEW 참여한 경우
3. TRANSACTIONAL REPLICATION 또는 MERGE REPLICATION 을 사용하여 테이블이 게시 된 경우
4. 테이블에 정의 된 트리거는 실행되지 않습니다.

[sic]

TRUNCATE TABLE

TRUNCATE TABLE Helloworlds 

이 코드는 Helloworlds 테이블에서 모든 데이터를 삭제합니다. 테이블 자르기는 Delete from Table 코드 Delete from Table 와 거의 비슷합니다. 차이점은 Truncate와 함께 where 절을 사용할 수 없다는 것입니다. Truncate 테이블은 트랜잭션 로그 공간을 적게 사용하므로 삭제보다 나은 것으로 간주됩니다.

ID 열이 있으면 초기 시드 값으로 재설정됩니다 (예 : 자동 증가 ID가 1에서 다시 시작됨). ID C 럼이 다른 테이블에서 외부 키로 g 용되는 경우, 이는 모순이 될 수 있습니다.

새 테이블 만들기 및 이전 테이블에서 레코드 삽입

SELECT * INTO NewTable FROM OldTable

이전 테이블 구조로 새 테이블을 만들고 모든 테이블을 새 테이블에 삽입합니다.

몇 가지 제한 사항

1. 테이블 변수 또는 테이블 반환 매개 변수를 새 테이블로 지정할 수 없습니다.
2. 소스 테이블이 파티션 된 경우에도 SELECT ... INTO를 사용하여 파티션 된 테이블을 작성할 수 없습니다. SELECT ... INTO는 소스 테이블의 파티션 구성표를 사용하지 않습니다. 대신 기본 파일 그룹에 새 테이블이 만들어집니다. 분할 된 테이블에 행을 삽입하려면 먼저 분할 된 테이블을 만든 다음 INSERT INTO ... SELECT FROM 문을 사용해야합니다.
3. 소스 테이블에 정의 된 인덱스, 제한 조건 및 트리거는 새 테이블로 전송되지 않으며 SELECT ... INTO 문에서 지정 될 수도 없습니다. 이러한 객체가 필요한 경우 SELECT ... INTO 문을 실행 한 후 객체를 만들 수 있습니다.
4. ORDER BY 절을 지정해도 행이 지정된 순서로 삽입되는 것은 아닙니다. 선택 목록에 스파 스 열이 포함되어 있으면 스파 스 열 속성이 새 테이블의 열로 전송되지 않습니다. 새로운 테이블에이 속성이 필요한 경우 SELECT ... INTO 문을 실행 한 후 열 정의를 변경하여이 속성을 포함합니다.
5. 계산 된 열이 선택 목록에 포함되면 새 테이블의 해당 열은 계산 열이 아닙니다. 새 열의 값은 SELECT ... INTO가 실행될 때 계산 된 값입니다.

[ sic ]

테이블 행 수 얻기

다음 예는 table_name 이 조회하려는 테이블로 바뀌면 데이터베이스의 특정 테이블에 대한 전체 행 수를 찾는 데 사용할 수 있습니다.

SELECT COUNT(*) AS [TotalRowCount] FROM table_name;

또한 다음 스크립트를 사용하여 테이블의 HEAP (index_id = 0) 또는 클러스터 된 클러스터 인덱스 (index_id = 1)를 기반으로 테이블의 파티션에 다시 가입하여 모든 테이블의 행 수를 얻을 수도 있습니다.

SELECT  [Tables].name                AS [TableName],
        SUM( [Partitions].[rows] )    AS [TotalRowCount]
FROM    sys.tables AS [Tables]
JOIN    sys.partitions AS [Partitions]
    ON  [Tables].[object_id]    =    [Partitions].[object_id]
    AND [Partitions].index_id IN ( 0, 1 )
--WHERE    [Tables].name = N'table name' /* uncomment to look for a specific table */
GROUP BY    [Tables].name;

이는 여분의 파티션이 추가되지 않는 한 모든 테이블이 기본적으로 단일 파티션 테이블이므로 가능합니다. 이 스크립트는 또한 테이블 행에 대한 읽기 / 쓰기 작업을 방해하지 않는 이점이 있습니다.