[MSSQL] 주로 사용하는 SQL문 모음 [계속업데이트]

Microsoft SQL Server 2008 - Express버전은 무료. 단 DB용량이 10G이하

 

▼ S/W 다운로드

Microsoft SQL Server 2008 R2 RTM - Express with Management Tools

(Window Live Login 필요)

정보출처: http://devx.tistory.com/entry/최신-SQL-Server-2008-R2-Express-version

 

▼ Database Backup/Restore

1. backup 파일 만들기
BACKUP DATABASE DBNAME TO disk='c:\YSWL_BOOKING.bak' with init;

-- 2. DB 변경하여 restore 하기
RESTORE DATABASE DBNAME FROM DISK='c:\YSWL_BOOKING.bak'
WITH NORECOVERY,
MOVE 'DBNAME' TO 'C:\Program Files\Microsoft SQL\..\MSSQL\DATA\DBFILE.mdf',

MOVE 'DBNAME_log' TO 'C:\Program Files\Microsoft SQL Server\..\MSSQL\DATA\DBFILE_log.ldf';

 

출처: http://nimba.tistory.com/328

 

▼ 테이블 수정

1) 컬럼 추가

ALTER TABLE TableName ADD [ColumnName] [datatype] IDENTITY(1,1) NOT NULL;

→ IDENTITY(1,1)는 시퀀스 추가를 원할때 넣을 것, 1부터 1씩 증가라는 의미.

▽NOT NULL + 제약조건 추가

ALTER TABLE TBL_NAME ADD LastUpdateDate DATETIME NOT NULL CONSTRAINT DF_TBLNAME_LastUpdateDate DEFAULT GETDATE() WITH VALUES ;

 

2) 컬럼 수정

ALTER TABLE TableName ALTER COLUMN [ColumnName] datetime NOT NULL;

예:) ALTER TABLE EquipFileList ALTER COLUMN FileList  nvarchar(250) NOT NULL;

 

3) 컬럼 삭제

ALTER TABLE TableName  DROP COLUMN [ColumnName];

 

4) 컬럼명 변경

SP_RENAME 'TableName.ColumnName', 'New_ColumnName',  'COLUMN';

 

 

▼ PK/FK 추가/삭제

ALTER TABLE TableName  ADD CONSTRAINT PK명 PRIMARY KEY (컬럼명); 

ALTER TABLE TableName ADD CONSTRAINT FK명 FOREIGN KEY (컬럼명) REFERENCES 테이블명(컬럼명); -- FK추가

ALTER TABLE TableName  DROP CONSTRAINT PK명; -- PK/FK 삭제

 

▼ 제약조건 추가

--디폴트값 추가

ALTER TABLE TableName  ADD CONSTRAINT DF_xxx DEFAULT 기본값 FOR 컬럼명 ;

 

▼ 인덱스

CREATE UNIQUE INDEX UX_인덱스명 ON 테이블명 (컬럼1 ASC, 컬럼2 DESC)

WHERE 컬럼2 = 1 --with조건

 

DROP INDEX UX_인덱스명 ON 테이블명;

 

 

▼ MS-SQL의 데이터 정렬(인코딩) 지정 (일본어)

: SQL Server 데이터 정렬 옵션 (URL)

CI
대소 문자를 구별하지 않는다. 전각 알파벳 대소 문자도 구별하지
  예 : "A"와 "a"를 동일시
CS
대소 문자를 구별한다. 전각 알파벳 대문자, 소문자도 구분
예 : "A"와 "a"를 구별하는
AI
악센트, 탁음, 대중 음악을 구별하지 않는다.
반각 카나의 탁음, 대중 음악도 구별하지
예 : "하", "바", "파"를 구분하지 않는
AS
악센트, 탁음, 대중 음악을 구별한다.
예 : "하", "바", "파"를 구별하는
KS
히라가나와 가타가나를 구분한다. 반각로 구분
KS 대해 KI는 지정하지
예 : "은"을 "이"구분
WS
반자 구분한다. 즉 전각, 반각 구별한다.
WS 대해 WI는 지정하지
예 : "a"와 "a"를 구별하는
BIN
이진 비교한다. 즉 모든 구별한다. .

지정 서식
형식 : Japanese_BIN
형식 : Japanese_ (CI | CS) _ (AI | AS) (_KS | _WS | _KS_WS)
예 : Japanese_BIN
예 : Japanese_CI_AS_KS

 

▼ 컬럼 데이터 정렬(COLLATE) 지정 방법(http://blog.daum.net/z-dream/17280549)

1)DB별 기본값 확인

SELECT name, collation_name FROM sys.databases;

 

2)컬럼별 정렬값 확인

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 

 FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = 'DB명'

 

  AND TABLE_NAME IN ('테이블명')

 

3)컬럼 정렬값 변경

ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 VARCHAR(10) COLLATE 데이터정렬방식 NOT NULL;

ALTER/CREATE DATABASE DB명 COLLATE 데이터정렬방식;

 

▼ 관련 에러코드 1757

의미) 열 '테이블.컬럼명'의 데이터 정렬이 외래 키 ‘FK__XXX..’에 있는 참조 열 ‘테이블.컬럼명’의 데이터 정렬과 다릅니다.

▼ 데이터 정렬 정보 확인하기

SELECT name, collation_name FROM sys.databases WHERE {DB명};

 

▼ DateTime ↔ String 변환 [FORMAT 정보] [MSSQL 날짜 변환(GETDATE, CONVERT)]

SELECT CONVERT(Target형,원본컬럼,스트링포맷)

[DateTime to String] SELECT CONVERT(varchar,getdate(),120);

-- 120: yyyy-mm-dd hh:mm:ss(24h)

-- 112: yyyymmdd

-- 111: yyyy/mm/dd

-- 108: hh:mm:ss

[String to DateTime] SELECT CONVERT(datetime,'2013-06-28 17:28:00',120);

 

SELECT DATEADD(day, -1, GETDATE()), GETDATE() -- 하루전

-- 2017-12-19 10:42:50.780 2017-12-20 10:42:50.780

SELECT DATEADD(hour, -1, GETDATE()), GETDATE() -- 1시간전

--week, year, month, minute, second, millisecond

 

-- 초/밀리초 제거한 하루전

select GETDATE(), CAST(CONVERT(VARCHAR(16), GETDATE()-1, 120) as DateTime) 

 

 

▼ 쿼리가 너무 길어서 보기 힘들때 서브쿼리 메모리에 임시보관하기

WITH VIEW1(임시뷰이름) AS (
  서브쿼리문
)
SELECT * FROM VIEW1; -- 메인쿼리에서 사용

▼ DB프로시저의 시작 코드

--  테이블에 영향을 준 행 수 메시지 출력하지 않도록 한다(∵서버 부하 적게)
-- "(1개 행이 영향을 받음)"와 같은 메시지
SET NOCOUNT ON;  
-- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 행을 읽을 수 있도록 설정한다.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  

▼ 월의 첫날,마지막날짜값 조회

DECLARE @FirstDay DATETIME = CONVERT(DATETIME, '2018-02-01',121); -- 1일
DECLARE @LastDay DATETIME = DATEADD(DD,-1,DATEADD(MM,1,@FirstDay)); -- 말일

SELECT 1 AS StartDay, DATEPART(DD,@LastDay) AS EndDay;

▼ (20.11.19) 페이징 조회 OFFSET (SQL Server 2012버전 이상 지원)

DECLARE @viewCnt int = 50; -- 한 페이지 당 보여줄 ROW 갯수
DECLARE @currentPageNum int = 1; -- 조회할 페이지 번호(1~N)

--▼AS-IS
SELECT ROW_NUMBER() OVER(ORDER BY 컬럼1,컬럼2) as rnum, XXX
   FROM 테이블
WHERE a.rnum BETWEEN ((@currentPageNum-1) * @viewCnt) + 1 AND (@currentPageNum * @viewCnt)
ORDER BY rnum;

--▼TO-BE
SELECT 컬럼1,컬럼2, XXX
   FROM 테이블
ORDER BY 컬럼1,컬럼2
OFFSET (@currentPageNum-1)*@viewCnt ROWS FETCH NEXT @viewCnt ROW ONLY

 

▼ 연결된 서버 생성

-- 연결된 서버 삭제
EXEC sp_dropserver @server = '연결서버별명';

-- 생성&로그인 정보 등록
EXEC sp_addlinkedserver
      @server = '연결서버별명',
      @srvproduct = '',
      @provider = 'SQLOLEDB',
      @datasrc = 'ip',
      @catalog = 'database명';

EXEC sp_addlinkedsrvlogin
      @rmtsrvname= '연결된DB별명',
      @useself= 'false',
      @rmtuser = 'ID',
      @rmtpassword = '패스워드';

-- 결과 확인
SELECT * FROM master.dbo.sysservers;
SELECT * FROM master.sys.linked_logins WHERE remote_name = 'id'