[문과 코린이의 IT 기록장] MSSQL - CONSTRAINT 예제, IDENTITY, SELECT 쿼리의 기본 종합정리

[문과 코린이의 IT 기록장] MSSQL - CONSTRAINT 예제, IDENTITY, SELECT 쿼리의 기본 종합정리

[문과 코린이의 IT 기록장] MSSQL - CONSTRAINT 예제, IDENTITY, SELECT 쿼리의 기본 종합정리

 

---

< 참고 강의 영상 >

MSSQL Server 2016 기반의 데이터베이스 입문에서 활용까지 Part.2 대시보드 - 인프런 | 강의 (inflearn.com)

MSSQL Server 2016 기반의 데이터베이스 입문에서 활용까지 Part.2 - 인프런 | 강의

2022.01.19 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - SQL Server 데이터 형식 (SQL Server 데이터 형식, char vs varchar, nchar vs nvarchar, 정수 데이터 크기와 범위, Boolean, 날짜, 시간 데이터 형식)

[문과 코린이의 IT 기록장] MSSQL - SQL Server 데이터 형식 (SQL Server 데이터 형식, char vs varchar, nchar vs n

2022.01.20 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - 테이블/열 생성 및 삭제 (SQL 데이터베이스 언어, CREATE TABLE 쿼리, ALTER TABLE, 글꼴 변경, 동시에 여러개 열 추가 및 삭제 방법, RENAME COLUMN, 종속성 보기, Inner Join)

[문과 코린이의 IT 기록장] MSSQL - 테이블/열 생성 및 삭제 (SQL 데이터베이스 언어, CREATE TABLE 쿼리,

2022.01.20 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - 데이터베이스 변경 (Datatype Alteration, Table Structure , UPDATE, 제약조건, ISNULL example, 데이터베이스 생성/삭제, 유니코드 형식 데이터 입력)

[문과 코린이의 IT 기록장] MSSQL - 데이터베이스 변경 (Datatype Alteration, Table Structure , UPDATE, 제약조

 

---

1.  CONSTRAINT 예제

 * constraint : 제약, 제한, 통제

- 테이블 생성시 여러 제약 조건을 컬럼에 걸어서 만들 수 있다.

- 이는 쿼리 / 개체 탐색기를 통해서 만들 수 있다.

 * 따라서 쿼리 창을 통해, 데이터베이스 및 테이블 생성에서부터 자료 입력, 출력, 데이터베이스 삭제까지 일련의 과정을 반복 연습하는 과정이 필요하다. 이 과정에서 필요한 함수들도 사용해보면 좋다.

 

1) CONSTRAINT 예제 1

- IDENTITY / PRIMARY KEY / DEFAULT

# 기존 테이블에 데이터가 있는 경우 : DEFAULT제약과 IDENTITY속성을 사용하여 추가한다.  a. DEFAULT는 열에 기본 값을 저장해주므로, DEFAULT('') NOT NULL 이처럼 사용한다.  * ''(값이 없는 값)을 넣어놓는 방법.  b. IDENTITY 속성은 자동 증가 값을 저장해주므로, IDENTITY(1,1) NOT NULL 이처럼 사용한다.  ex. 새로운 열의 레코드에 자동 증가값 저장 : (1,1) -> 1,2,3,4... / (1,2) -> 1,3,5,7...

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

-- 작업 DB 변경
USE TESTDB3;

-- 테이블 생성
CREATE TABLE dbo.sampleTbl(
	COL1		INT		IDENTITY(100,1)		PRIMARY KEY,	-- IDENTITY = IDENTITY(1,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);
 -- sampleTbl의 COL3만 데이터를 입력해줄 경우, 나머지는 DEFAULT값으로 자연스럽게 입력됨
INSERT INTO dbo.sampleTbl(COL2,COL3) VALUES('대한민국','서울');

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

 -- 데이터베이스 삭제
 USE MASTER;
 DROP DATABASE TESTDB3; -- DATABASE는 주의해야함.

 

2) CONSTRAINT 예제 2 

- 함수 사용 : LEFT / CONVERT

 -- LEFT 함수 사용
 SELECT COL1, COL2, COL3, COL4, LEFT(COL5,8) AS '타임' FROM dbo.sampleTbl; -- left함수 : 왼쪽부터 8개까지만 출력

결과 1

 -- CONVERT 함수 사용
 -- 데이터형식을 다른 형식으로 바꿔주는 함수
 -- 사용법 : CONVERT(데이터타입, 변경대상, 변경하고자 하는 형식);
 -- 코드넘버(변경하고자 하는 형식)에 따라 결과가 달라진다.
 SELECT GETDATE() AS 현재시각;
 SELECT CONVERT(VARCHAR(8), GETDATE(), 108); -- 108(시,분,초)로 보여주는 코드형식을 출력함.
 SELECT CONVERT(VARCHAR(8),GETDATE(),8);
 SELECT CONVERT(VARCHAR(8),GETDATE(),10);
 SELECT CONVERT(VARCHAR(8),COL5,108) AS '타임' FROM dbo.sampleTbl;

결과 2

3) CONSTRAINT 예제 3

- UNIQUE

-- ex. 테이블 생성 -> 직원 입사/퇴사 테이블
CREATE TABLE dbo.employeeTbl(
	mem_idx		INT				IDENTITY	 PRIMARY KEY,
	mem_id		CHAR(16)		UNIQUE		NOT NULL,					-- 영문만 들어오는 경우
	 -- PRIMARY KEY는 두번 사용 X. (중복되는 값 없도록 하기 위해서는)
	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(
	'fireman', 
	'파이어맨', 
	'2010-12-12', 
	'2018-12-25', 
	'해외이민', '
	fireman@test.com'
);

INSERT INTO dbo.employeeTbl(
	mem_id,
	mem_name,
	mem_s_date,
	mem_email
)
VALUES(
	'antman',
	'앤트맨',
	'2010-12-22',
	'antman@naver.com'
);

-- 만약 NOT NULL로 제약조건이 설정된다면, 꼭 값을 입력해줘야 한다.
-- INSERT INTO dbo.employeeTbl(mem_id) VALUES('가나다'); -- ! 입력 오류 발생 !



-- 데이터 출력
SELECT * FROM dbo.employeeTbl ORDER BY mem_id; -- mem_id를 기준으로 오름차순 정렬

UNIQUE 속성을 통해, 중복된 값을 입력하려고 하면 나오는 오류창

---

2. IDENTITY

1) IDENTITY 값의 간격

- 입력 실패 또는 레코드 삭제시에는 ID열의 값이 간격이 생길 수 있다.

중간 3,4번 삭제 되었을 때, 값에 있어 간격이 생기는 현상

- 특정 INSERT문이 쿼리 수행시 실패하거나, 또는 해당 INSERT문이 롤백이 되는 경우에, 그 과정에서 사용된 고유 ID값은 손실이 되며 다시 생성되지 않는다.

- 그 결과로서 그 다음에 입력되는 새 레코드의 고유 ID값은 앞 레코드와의 간격이 발생할 수 있다.

 

2) IDENTITY 값 수정

- 입력 실패 또는 삭제시, 테이블에 IDENTITY 조건 열이 있는 경우, 각 레코드 값 사이에 간격이 발생할 수 있으며, 이 간격을 없애는 방법이 존재한다. (그러나 사용은 권장하지 않는다.)

SET IDENTITY_INSERT 테이블명 ON
-- IDENTITY값을 명시적으로 입력하기 위한 옵션 변경
-- 원래 IDENTITY값은 입력할 수 없음. (자동으로 입력됨)

 

---

3. SELECT 쿼리의 기본 종합정리

 * 데이터베이스 - SQL이 굉장히 많은 부분을 차지함. 그 중에서도 SELECT쿼리는 굉장히 중요함.

1) 기본 형태

SELECT stor_id, ord_num, qty, title_id FROM dbo.sales;

2) WHERE 조건절

SELECT * FROM dbo.sales WHERE stor_id = 6380;
-- WHERE조건절은 레코드(행)에 대한 조건을 거는 것이라고 생각해도 좋음

3) * (모든 열을 다 가져오기)

SELECT * FROM dbo.sales;

4) 검색할 열의 순서를 지정

SELECT ord_date, ord_num, stor_id FROM dbo.sales;
-- 앞에서부터 검색해줌

5) AS(별칭 사용)

SELECT ord_date AS 주문날짜, qty AS 판매수량 FROM dbo.sales;

6) AS 키워드는 생략이 가능하다.

SELECT ord_date '주문날짜' FROM dbo.sales;

7) 새로운 열을 추가하는 방법

- 마치 원래 있었던 테이블 열처럼 사용한다.

SELECT '판매수량' AS 새열, qty FROM dbo.sales;
-- 판매수량이 새열이라는 열의 데이터로 들어감.

8) 정렬 

- 정렬은 문자, 숫자, 날짜 모두 가능하다.

- 숫자는 크기에 따라, 문자는 알파벳순으로 되어진다.

SELECT * FROM dbo.sales ORDER BY qty; -- 오름차순(ASC)정렬은 생략 가능.
SELECT * FROM dbo.sales ORDER BY qty DESC; -- 내림차순(DESC)정렬은 표기해줘야함.

 ex 1. 고용일이 가장 오래된 사람순으로 출력하라

SELECT frame, name, hire_date FROM dbo.employee ORDER BY hire_date ASC;

ex 2. job_lvl은 내림차순으로, 같은 lvl의 사람들 중 job_id는 오름차순으로 출력하시오.

SELECT * FROM dbo.employee ORDER BY job_lvl DESC, job_id;

9) 중복 데이터 걸러내는 방법 (DISTINCT)

SELECT city FROM dbo.authors; -- 모두 출력
-- 열에 중복 데이터 걸러내서 출력하는 방법 (중복되지 않게 하나만 출력하도록)
SELECT DISTINCT city AS '도시' FROM dbo.authors ORDER BY city;

10) Top n (상위 몇 개의 레코드만 가져오고 싶다면)

- Top  n : 정해진 개수만큼만 레코드를 가져와준다.

SELECT TOP 5 * FROM dbo.sales ORDER BY qty DESC; 
SELECT TOP 5 tilte_id, qty AS 판매수량 FROM dbo.sales ORDER BY qty DESC;

- Problem : n개 까지로 설정시 n개항 뒤로 같은 값을 가지는 행들이 있다면 잘려진다.

 ex. Top 3 (100 / 90 / 80점 학생 1,2,3순위) - 이 세명만 출력함. 이후에 80점인 학생이 3명 더 있더라도 잘려버림.

- Solution : WITH TIES절을 사용한다.

SELECT TOP 6 WITH TIES stor_id, qty FROM dbo.sales ORDER BY qty DESC;
-- 같은 값을 가진다면 같이 묶어서 출력하라.

11) Percent (전체 비율에 맞는 레코드를 가져오고 싶다면)

ex. 해당 테이블에 전체 레코드가 18개 존재할 때, 그 중 50%의 레코드(9개)만 출력하고 싶을 때 사용

SELECT TOP 50 Percent * FROM dbo.titles;

12) Top(n) 활용

- Top(n) 형태로도 사용할 수 있으며, 변수로도 사용 가능하다.

 * (n)은 정수형 변수 또는 실수형 변수 모두 가능하다.

DECLARE @n INT = 6; -- 변수 선언
SELECT TOP(@n) stor_id, qty FROM dbo.sales ORDER BY qty DESC; -- TOP(@n) = Top 6
SELECT TOP(@n) WITH TIES stor_id, qty FROM dbo.sales ORDER BY qty DESC;

ex. 학생 테이블에서 성적 상위 0.1% 학생들만 출력하시오.

DELCARE @a FLOAT = 0.1;
SELECT TOP(@a) Percent * FROM Person.Address;

---

* 유의사항 - 아직 공부하고 있는 문과생 코린이가, 정리해서 남겨놓은 정리 및 필기노트입니다. - 정확하지 않거나, 틀린 점이 있을 수 있으니, 유의해서 봐주시면 감사하겠습니다. - 혹시 잘못된 점을 발견하셨다면, 댓글로 친절하게 남겨주시면 감사하겠습니다 :)