[MS SQL SERVER] 기본적인 DDL 구문 정리

 

데이터베이스

/* Database 생성 */
CREATE DATABASE UserTemp;	

/* Database List 출력 */
EXEC sp_databases;

/* Database 삭제 */
IF EXISTS(select * from sys.databases where name='UserTemp')
DROP DATABASE UserTemp

 

테이블

/* 테이블 생성 */
USE UserLabs;
GO
CREATE TABLE dbo.UserTbl (
	USERID INT PRIMARY KEY IDENTITY (1, 1),
	NAME VARCHAR(10),
	AGE INT,
	ADDR VARCHAR(20)
) 

CREATE TABLE dbo.UserSales (
	USERID INT PRIMARY KEY IDENTITY (1, 1),
	ITEMNAME VARCHAR(10),
	ITEMCODE INT,
	AMOUNT VARCHAR(20),
	FOREIGN KEY (USERID) REFERENCES dbo.UserTbl (USERID)	--참조테이블의 KEY값만 지정가능
) 

/* 컬럼 추가 */
ALTER TABLE dbo.UserTbl ADD GENDER VARCHAR(1)


/* 컬럼 수정 */
ALTER TABLE dbo.UserTbl ALTER COLUMN NAME VARCHAR(20)	--VARCHAR(10) -> VARCHAR(20)
ALTER TABLE dbo.UserTbl ALTER COLUMN AGE SMALLINT		--INT -> SMALLINT
ALTER TABLE dbo.UserTbl ALTER COLUMN NAME VARCHAR(20) NOT NULL	--NOT NULL 옵션 추가

/* 컬럼 삭제 */
ALTER TABLE dbo.UserTbl DROP COLUMN AGE

/* 제약조건 삭제 */
ALTER TABLE dbo.UserSales DROP CONSTRAINT FK__UserSales__USERI__36B12243

/* 계산된 컬럼 */
ALTER TABLE dbo.UserTbl ADD NAME_AGE AS (NAME + '/' + CONVERT(VARCHAR(10),AGE));

INSERT INTO UserTbl 
	(NAME, AGE, ADDR)
OUTPUT inserted.USERID
VALUES ('Ken', 20, 'Seattle');

SELECT * FROM dbo.UserTbl

CREATE TABLE dbo.UserTbl (
	USERID INT PRIMARY KEY IDENTITY (1, 1),
	NAME VARCHAR(10),
	AGE INT,
	ADDR VARCHAR(20),
	NAME_AGE AS NAME + '/' + CONVERT(VARCHAR(10),AGE)
) 

--PERSISTED 옵션
ALTER TABLE dbo.UserTbl ADD NAME_AGE2 AS (NAME + '/' + AGE) PERSISTED;

--기본 컬럼을 계산컬럼으로 변경하려면 삭제후 재생성
ALTER TABLE dbo.Products DROP COLUMN RetailValue
GO
ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5)

/* 테이블 리네임 */
EXEC sp_rename 'UserTbl', 'UserTbl_TEMP'

SELECT * FROM dbo.UserTbl
SELECT * FROM dbo.UserTbl_TEMP

/*
 sp_rename - index가 그대로 옮겨감
 select into - index는 옮겨가지 않으므로 설정해줘야함
*/

/* 테이블 삭제 */
--테이블 삭제
IF OBJECT_ID('UserLabs.dbo.UserTbl_TEMP') IS NOT NULL	
	DROP TABLE UserTbl_TEMP

DROP TABLE IF EXISTS UserTbl_TEMP	--2016 이상 버전


--TRAUNCATE VS DELETE
/*
좋은점
1. 트랜잭션 로그를 남기지 않는다
2. LOCK이 적게 걸린다
3. IDENTITY 값이 리셋된다
*/

 

스키마

USE UserTemp;
GO

/* 스키마 생성 */
CREATE SCHEMA TEST_SCHEMA
GO

/* 스키마 조회 */
SELECT 
    s.name AS schema_name, 
    u.name AS schema_owner
FROM 
    sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY 
    s.name;
    
/* 스키마 수정 */
ALTER SCHEMA TEST_SCHEMA TRANSFER OBJECT:: DBO.UserTbl
ALTER SCHEMA TEST_SCHEMA TRANSFER OBJECT:: DBO.UserTbl2

SELECT * FROM UserTemp.DBO.UserTbl
SELECT * FROM UserTemp.TEST_SCHEMA.UserTbl

/* 스키마 삭제 */
DROP SCHEMA TEST_SCHEMA

Cannot drop schema 'TEST_SCHEMA' because it is being referenced by object 'UserTbl'.
DROP TABLE TEST_SCHEMA.UserTbl

뷰

view 좋은점
1. 제한된 컬럼으로 view를 생성할 경우 테이블의 모든 데이터를 노출하지 않아도 된다.
2. 테이블로 쿼리할 경우 복잡하지만 view로 한번만 생성해 놓으면 간단하다.

/* 뷰 생성 */
SELECT * FROM  [AdventureWorks2014].[Person].[Person]
SELECT * FROM [AdventureWorks2014].[Person].[PersonPhone]

USE UserLabs
Go
CREATE VIEW dbo.PersonInfo
AS 
SELECT A.BusinessEntityID, A.FirstName, B.PhoneNumber
FROM [AdventureWorks2014].[Person].[Person] A
	JOIN [AdventureWorks2014].[Person].[PersonPhone] B
	ON A.BusinessEntityID = B.BusinessEntityID

SELECT * FROM UserLabs.dbo.PersonInfo

/* 뷰 조회 */
SELECT 
	OBJECT_SCHEMA_NAME(v.object_id) schema_name,
	v.name
FROM 
	sys.views as v;

SELECT 
	OBJECT_SCHEMA_NAME(o.object_id) schema_name,
	o.name
FROM
	sys.objects as o
WHERE
	o.type = 'V';
    
--뷰를 조회할 수 있는 프로시저 생성
CREATE PROC dbo.usp_list_views(
	@schema_name AS VARCHAR(MAX)  = NULL,
	@view_name AS VARCHAR(MAX) = NULL
)
AS
SELECT 
	OBJECT_SCHEMA_NAME(v.object_id) schema_name,
	v.name view_name
FROM 
	sys.views as v
WHERE 
	(@schema_name IS NULL OR 
	OBJECT_SCHEMA_NAME(v.object_id) LIKE '%' + @schema_name + '%') AND
	(@view_name IS NULL OR
	v.name LIKE '%' + @view_name + '%');


EXEC usp_list_views @view_name = 'PersonInfo'
EXEC usp_list_views @view_name = 'Person'	--like로 검색함

/* 뷰 리네임 */
EXEC sp_rename @objname = 'PersonInfo',
			@newname = 'v_PersonInfo';
            
/* 뷰 정보확인 */
SELECT
    definition,
    uses_ansi_nulls,
    uses_quoted_identifier,
    is_schema_bound
FROM
    sys.sql_modules
WHERE
    object_id = object_id('v_PersonInfo');

EXEC sp_helptext 'dbo.v_PersonInfo' ;

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.v_PersonInfo')) view_info;

/* 뷰 삭제 */
DROP VIEW dbo.v_PersonInfo

/* Indexed View */
SELECT BusinessEntityID, FirstName
INTO UserLabs.dbo.Person
FROM  [AdventureWorks2014].[Person].[Person]
--(19972 row(s) affected)

SELECT BusinessEntityID, PhoneNumber
INTO UserLabs.dbo.PersonPhone
FROM [AdventureWorks2014].[Person].[PersonPhone]
--(19972 row(s) affected)

drop view v_PersonInfo

--디비명을 입력하면 안됨
USE UserLabs
Go
CREATE VIEW dbo.v_PersonInfo WITH SCHEMABINDING
AS 
SELECT A.BusinessEntityID, A.FirstName, B.PhoneNumber
FROM dbo.Person A
	JOIN dbo.PersonPhone B
	ON A.BusinessEntityID = B.BusinessEntityID

	
SELECT * FROM UserLabs.dbo.v_PersonInfo
CREATE UNIQUE CLUSTERED INDEX PK_BusinessID ON v_PersonInfo