MSSQL 데이터베이스 정리 1

MS_SQL

제 기준 SQL Server 2016 with SP2 환경으로 설치하였습니다.

MS_SQL Sample Database

Pubs DB 다운로드 Pubs (책 판매와 출판에 대한 샘플 DB)

Adventure Works DB 다운로드 Adventure Works (자전거 회사에 대한 샘플 DB)

Pubs 데이터베이스 생성

MS_SQL 2016 SSMS(Microsoft SQL Server management Studio) 를 실행하고
다운 받은 instpubs.sql 파일을 열어서 실행 [F5] 시킵니다.

다른 방법으로
명령프롬프트 창에서 다운로드 받은 폴더로 이동해서 아래의 명령어를 실행시켜도 됩니다.

> sqlcmd -i instpubs.sql

Adventure Works 데이터베이스 생성

주의 ) MS_SQL 설치한 버전과 데이터베이스 백업 버전이 다를 경우 복원이 되지 않을 수 있습니다.

다운 받은 AdventureWorks2016.bak
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup 에 파일을 이동하거나 복사해 줍니다.
MS_SQL 2016 SSMS(Microsoft SQL Server management Studio) 를 실행합니다.

연결을 한 후
데이터베이스 에서 오른쪽 마우스 버튼을 눌러 데이터베이스 복원 메뉴를 클릭합니다.
일반 > 원본 > 디바이스 옵션에서 ... 을 클릭하여 백업디바이스 선택 창을 엽니다.
백업미디어 에 추가 버튼을 클릭하여 백업파일을 찾아 확인 버튼을 눌러줍니다.
백업미디어 에 추가된 파일이 뜨는 것을 확인하시고 확인 버튼을 눌러주면
데이터베이스를 복원을 합니다. 데이터베이스를 복원 완료 했다는 창이 뜨면 복원이 완료 된 것입니다. 데이터베이스 쪽에 AdventureWorks2016 데이테베이스가 생성되었는지 확인합니다.

데이터 베이스 삭제

개체 탐색기 에서 데이터 베이스 폴더의 생성된 데이터베이스 (Pubs 혹은 AdventureWorks2016) 를 선택하고 오른쪽 마우스 버튼을 눌러 삭제 메뉴를 클릭하시면 삭제가 됩니다.

데이터베이스 생성

SSMS > 개체 탐색기 에서 데이터 베이스 생성
새 데이터베이스 창에서 데이터 베이스 이름 설정 > TestDB
소유자 > 기본값
확인 버튼을 누르면 TestDB.mdf 와 TestDB.log.ldf 파일이 생성됨

테이블 생성

테이블 생성의 기본 형태 와 예시

-- CREATE TABLE memberTbl (
--   컬럼명1 데이터형식 제약조건(NOT NULL),
--   컬럼명2 데이터형식 제약조건,
--   컬럼명3 데이터형식 제약조건
-- );

-- ex)
CREATE TABLE memberTbl (
  mem_id varchar(10) PRIMARY KEY, -- 영문만 입력되는게 명확하다면 varchar
  mem_name nchar(10) NOT NULL,    -- nchar 는 한글이 들어올 수 있는 경우
  mem_gender char(1) NOT NULL,    -- 영문만 입력되는게 명확하다면 varchar
  mem_join date NOT NULL,         -- date 형식
  mem_address nvarchar(90),       -- null 허용, 한글이 저장되기 때문에 nvarchar
  mem_email varchar(90) NOT NULL  -- 영문만 입력되는게 명확하다면 varchar
);

테이블 컬럼 추가 및 삭제

테이블 컬럼 추가 및 삭제 의 기본 형태 와 예시

-- 추가
-- ALTER TABLE memberTbl
--   ADD 컬럼명1 데이터형식 제약조건(NOT NULL)
-- GO

-- ex) 컬럼 추가 - 새로운 컬럼은 마지막에 추가 됩니다.
ALTER TABLE memberTbl
  ADD mem_point int NULL -- null 허용
GO

ALTER TABLE memberTbl
  -- ADD mem_point int NOT NULL                  -- NOT NULL 을 추가 할 경우 DEFAULT 구문이 없으면 에러 발생
  ADD mem_point int DEFAULT('0') NOT NULL        -- 에러를 없애려면 DEFAULT 값을 주면 됩니다.
  -- ADD mem_point int IDENTITY(1, 1) NOT NULL   -- 자동 증가값 IDENTITY(1, 1) 을 적용해 1씩 증가값을 줄 수도 있습니다. IDENTITY(1, 2) 를 하면 1부터 2씩 증가
GO

-- 여러 열 추가시 ADD 는 한번만 써주면 됩니다.
ALTER TABLE memberTbl
  ADD
    mem_point int NULL,  -- null 허용
    mem_point2 int NULL, -- null 허용
    mem_point3 int NULL  -- null 허용
GO


-- 삭제
-- ALTER TABLE memberTbl
--   DROP COLUMN 컬럼명
-- GO

-- ex) 컬럼 삭제 - 해당 컬럼을 삭제 합니다.
ALTER TABLE memberTbl
  DROP COLUMN mem_point
GO

-- 여러 열 삭제
ALTER TABLE memberTbl
  DROP COLUMN
    mem_point,
    mem_point2,
    mem_point3
GO

테이블 컬럼 데이터 형식 변경

memberTbl 의 mem_point 컬럼의 데이터 타입을 int 에서 smallint 로 변경

ALTER TABLE dbo.memberTbl
  ALTER COLUMN mem_point smallint;

제약조건 삭제

-- 테이블 에 디폴트 값이 있는 컬럼 추가 / nchar 이기 때문에 디폴트 값에서 N'Novice' 라 N을 앞에 써줍니다.
ALTER TABLE dbo.memberTbl
  ADD
    mem_level nchar(10) DEFAULT(N'Novice') NULL

-- 테이블에 디폴트 값이 있는 것을 삭제하려고 하면 에러가 발생
ALTER TABLE dbo.memberTbl
  DROP COLUMN mem_level

-- 어떤 제약 조건이 있는 지 확인 : 제약 조건에서 이름 확인하여 복사
EXEC sp_helpconstraint memberTbl;

-- 제약조건의 이름을 확인하여 제약 조건 먼저 제거
ALTER TABLE dbo.memberTbl
  DROP CONSTRAINT DF__memberTbl__mem_I__42E1EEFE

-- 이제 제거하게 되면 제거가 잘 됩니다.
ALTER TABLE dbo.memberTbl
  DROP COLUMN mem_level

테이블의 컬럼 명 변경 쿼리

-- EXEC sp_rename '기존컬럼명', '새 컬럼명', ['COLUMN' | 'OBJECT']  -- 대괄호 안의 내용은 생략 가능 : COLUMN은 컬럼명 수정, OBJECT 는 테이블명 수정
-- EXEC sp_rename 'memberTbl.mem_point', 'mem_pt', 'COLUMN'
EXEC sp_rename 'memberTbl.mem_point', 'mem_pt'
-- 기존 스크립트 및 저장 프로시저가 있는 경우 테이블의 컬럼명은 수정 되지만 스크립트 내의 써주었던 컬럼명은 수정되지 않으므로 주의가 필요하다.

종속성

개체 탐색기에서 테이블 우 클릭 메뉴에서 종속성 보기 누르면 테이블의 종속성 관계를 볼 수 있다.

종속 개체 확인을 위한 함수 (Function) => 데이터베이스 - 시스템 데이터베이스 - master - 프로그래밍기능 - 함수 - 시스템 함수 - 테이블반환함수

EXEC sp_depends @objname='스키마.개체명(해당테이블)';    -- 쿼리 창을 통해서 종속관계를 볼 수 있다. ( 이후 버전에서 제거 될 수 있음 )

-- sys.dm_sql_referencing_entities
-- sys.dm_sql_referenced_entities

SELECT * FROM sys.dm_sql_referencing_entities ('스키마.개체명(해당테이블)', 'OBJECT');
GO

SELECT * FROM sys.dm_sql_referenced_entities ('스키마.개체명(해당테이블)', 'OBJECT');
GO

개체 타입(유형) 보기

데이터베이스의 테이블 및 뷰, 함수, 트리거 등 개체의 타입을 볼수 있는 쿼리

SELECT * FROM sys.objects;
-- 필요한 것만 뽑아내기
SELECT object_id, name, type, type_desc FROM sys.objects;

종속성의 개체타입 보기

Pubs (책 판매와 출판에 대한 샘플 DB) 에서 확인

-- 필요한 부분만 뽑애내기
SELECT referencing_schema_name, referencing_entity_name, referencing_id FROM sys.dm_sql_referencing_entities('dbo.titleauthor', 'OBJECT')
SELECT object_id, name, type, type_desc FROM sys.objects

-- 위의 필요한 부분들만 뽑아내서 합치기 ( INNER JOIN 사용 )
SELECT a.referencing_schema_name, a.referencing_entity_name, b.name, b.type, b.type_desc
FROM sys.dm_sql_referencing_entities('dbo.titleauthor', 'OBJECT') AS a INNER JOIN sys.objects AS b
  ON a.referencing_id = b.object_id

테이블 구조 확인

EXEC sp_columns 테이블명; -- 테이블의 구조 정보 확인
EXEC sp_help 테이블명;    -- 테이블의 상세 정보 확인

-- 시스템 테이블 쿼리 정보 : 해당 데이터베이스의 테이블의 시스템 테이블 에 sys.columns, sys.tables 위치
SELECT * FROM sys.columns; -- 해당 데이터베이스의 컬럼 정보 - 전체가 다 나오니 WHERE 절과 ORDER BY 절 활용하여 사용
SELECT * FROM sys.tables;  -- 해당 데이터베이스의 테이블 정보

-- 스키마 테이블 컬럼 정보 보기 : 해당 데이터베이스의 뷰 의 시스템 뷰 에 information_schema.columns 위치
SELECT * FROM information_schema.columns

업데이트 쿼리문

-- 기본구조 - 업데이트에서 WHERE 문 필수, 조건절이 없으면 전체 데이터 변경 되므로 주의
UPDATE 테이블명 SET 컬럼명 = 값 WHERE 조건;

-- 디폴트 값이 업데이트 됨, 조건절이 없으면 전체 데이터 변경 되므로 주의
UPDATE dbo.memberTbl SET mem_level=DEFAULT;

-- 널 값으로 업데이트 됨, 조건절이 없으면 전체 데이터 변경 되므로 주의
UPDATE dbo.memberTbl SET mem_level=DEFAULT;

-- 조건절에 해당하는 mem_id='kim' 의 데이터만 변경 됨
UPDATE dbo.memberTbl SET mem_level='EXPERT', mem_point=7000 WHERE mem_id='kim';

널 값 업데이트

-- 기본 구조 , 조건절이 없으면 전체 데이터 변경 되므로 주의
-- UPDATE 테이블명 SET 컬럼명 = ISNULL(컬럼명 , '변경할 값') WHERE 조건;

-- ISNULL : SQL SERVER 내장 함수 사용
UPDATE dbo.memberTbl SET mem_point = ISNULL(mem_point, '100');
-- 바뀐것이 3개라 할지라도 업데이트 할 때 memberTbl 테이블의 개수가 7개 가 있었다면 결과창에서는 7개 행이 영향을 받음이라고 나옴

-- 조회 할 때 값을 보기 좋게 하려면
-- SELECT ISNULL(컬럼명, '값없음') AS 컬럼별칭 FROM 테이블명으로 해줍니다.
SELECT *, ISNULL(mem_level, '값없음') AS 회원레벨 FROM dbo.memberTbl

UPPER(), LOWER() 함수

-- 컬럼의 내용을 대문자로 변경
UPDATE dbo.memberTbl SET mem_level = UPPER(mem_level);
-- 컬럼의 내용을 소문자로 변경
UPDATE dbo.memberTbl SET mem_level = LOWER(mem_level);

-- 조회하거나 바로 보여줄 때 사용 방법
SELECT UPPER('korea seoul'); -- 결과 : KOREA SEOUL
SELECT LOWER('KOREA SEOUL'); -- 결과 : korea seoul

유니코드 형식 데이터 입력

-- 데이터베이스 생성
CREATE DATABASE TestDB2;
GO

-- TestDB2 데이터베이스 사용
USE TestDB2;

-- 테이블 생성
CREATE TABLE dbo.testTable (
  mem_num smallint NOT NULL,
  mem_nation nvarchar(25) NOT NULL,
  mem_hello nvarchar(30) NOT NULL,
  mem_birthday date,
  mem_income_tax money
);

-- INSERT DATA ( 데이터 입력 )
INSERT TestDB2.dbo.testTable
VALUES
  (1, N'한국', N'안녕하세요', '2010-01-01', 1500.00),
  (2, N'미국', N'hello', '2012-05-05', 2500.00),
  (3, N'태국', N'สวัสดี', '2015-03-01', 1700.00),
  (4, N'베트남', N'xin chào', '2010-01-01', 1200.00),
  (5, N'중국', N'你好', '2010-01-01', 2000.00),
  (6, N'일본', N'こんにちは', '2010-01-01', 1000.00);

-- SELECT DATA ( 데이터 조회 )
SELECT * FROM TestDB2.dbo.testTable;

CONSTRAINT ( 제약조건 ) - IDENTITY, PRIMARY KEY, DEFAULT

-- 데이터베이스 생성
CREATE DATABASE TESTDB3;

-- 작업 DB 변경
USE TESTDB3

-- 테이블 생성
CREATE TABLE dbo.sampleTbl (
  COL1  INT           IDENTITY(100, 1)    PRIMARY KEY,  -- 기본값 100에서 1씩 증가값 입력
  COL2  NVARCHAR(20)  DEFAULT '★ ♥ ★'     NULL,         -- 기본값 '★ ♥ ★' 입력
  COL3  NVARCHAR(20)                      NULL,
  COL4  DATE          DEFAULT GETDATE(),                -- 기본값 현재날짜 입력
  COL5  TIME          DEFAULT GETDATE(),                -- 기본값 현재시각 입력
);

-- 데이터 입력
INSERT INTO dbo.sampleTbl(COL3) VALUES (NULL);
INSERT INTO dbo.sampleTbl(COL2, COL3) VALUES ('대한민국', '서울');

-- 데이터 출력
SELECT * FROM dbo.sampleTbl;

-- 데이터베이스 삭제 ( 주의: USE MASTER 로 변경 후 삭제 )
USE MASTER
DROP DATABASE TESTDB3

함수 사용

SELECT * FROM dbo.sampleTbl;
SELECT COL1, COL2, CLO3, COL4, LEFT(COL5, 8) AS '시간' FROM dbo.sampleTbl;
-- CONVERT 함수 사용
SELECT CONVERT(VARCHAR(8), GETDATE(), 108); -- 14:22:15
SELECT CONVERT(VARCHAR(8), GETDATE(), 8);   -- 8자리 시간 표시
SELECT CONVERT(VARCHAR(10), GETDATE(), 10); -- 10자리 월일연 표시
SELECT CONVERT(VARCHAR(8), COL5, 108) AS '시간' FROM dbo.sampleTbl;

CONSTRAINT ( 제약조건 ) - UNIQUE

-- 데이터베이스 생성
CREATE DATABASE TESTDB4;

-- 작업 DB 변경
USE TESTDB4

-- 테이블 생성 ( 직원 인사/퇴사 테이블 )
CREATE TABLE dbo.employeeTbl (
  mem_idx     INT             IDENTITY    PRIMARY KEY,              -- IDENTITY 만 써줘도 IDENTITY(1, 1) 와 같은 의미
  mem_id      CHAR(16)        UNIQUE      NOT NULL,                 -- 영문만 들어오는 경우 CHAR 사용
  mem_name    NVARCHAR(16)                NOT NULL,                 -- 유니코드 지원
  mem_s_date  DATE                        NULL,                     -- 입사일
  mem_e_date  DATE                        NULL,                     -- 퇴사일
  mem_reason  NVARCHAR(50)                DEFAULT N'일신상의 사유'  -- 퇴사사유
  mem_email   VARCHAR(30)     UNIQUE      NOT NULL                  -- 이메일
);

-- 데이터 입력
INSERT INTO dbo.employeeTbl VALUES('joseph', '조셉', '2020-11-10', '2021-12-11', '해외 이민', 'joseph@test.com');
INSERT INTO dbo.employeeTbl (mem_id, mem_name, mem_s_date, mem_email) VALUES('jacob', '제이콥', '2015-10-15', 'jacob@test.com');

INSERT INTO dbo.employeeTbl (mem_id, mem_name, mem_s_date, mem_e_date, mem_reason, mem_email) VALUES ('david', '데이빗', '2018-05-05', '2019-12-20', '이직', 'david@test.com')

-- 데이터 출력
SELECT * FROM dbo.employeeTbl;

-- 데이터베이스 삭제 ( 주의: USE MASTER 로 변경 후 삭제 )
USE MASTER
DROP DATABASE TESTDB4

IDENTITY 값의 간격

입력이 실패 되거나 또는 레코드 삭제에 따라서 IDENTITY 값의 간격이 생길 수 있습니다. ( 같은 입력값을 다시 생성할 때 오류가 발생합니다. 그 때도 IDENTITY 값이 발생하는데 그 때 추가 되었던 부분이 사라진 값으로 적용되어 1, 2, 3 에러 , 4, 5 이런식으로 간격이 발생되어 저장 됩니다. ) 간격이 발생하는 것을 없애려면 SET IDENTITY_INSERT 를 ON 으로 설정

-- IDENTITY 값을 명시적으로 입력하기 위해 옵션 변경 ON/OFF
-- SET IDENTITY_INSERT 테이블명 ON/OFF
SET IDENTITY_INSERT dbo.employeeTbl ON;
-- 간격이 벌어진 부분이 3 이라고 가정했을 때
INSERT INTO dbo.employeeTbl (mem_idx, mem_id, mem_name, mem_s_date, mem_email) VALUES(3, 'jhon', '존', '2017-09-15', 'john@test.com');
SET IDENTITY_INSERT dbo.employeeTbl OFF;