SQL Server 강의 4일차

안녕하세요~! 문쑹입니다 :)

오늘부터 본격적으로 SQL의 꽃인 JOIN에 대해 포스팅해보겠습니다!

이전까지는 하나의 테이블을 다루는 작업을 위주로 예제를 작성하였지만 지금부터는 두 개 이상의 테이블을 다뤄보도록 하겠습니다.

JOIN(내부 조인)

JOIN(내부 조인)이란 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것입니다.

INNER JOIN 개념

조인 중 가장 많이 사용되며 일반적인 조인은 이 INNER JOIN(내부조인)을 얘기하는 것이다. 구문형식은 아래와 같습니다.

SELECT <열 목록>
FROM <첫 번째 테이블>
	INNER JOIN <두 번째 테이블>
    ON <조인될 조건>
[WHERE 검색조건]

첫 번째 테이블 기준으로 뒤에 두 번째 테이블이 붙는다.

INNER JOIN결과 첫 번째 테이블인 buyTBL 테이블 뒤에 userTBL 테이블이 붙은 것을 확인할 수 있다.

하지만 열의 항목이 너무 많아서 복잡해 보이니까 원하는 아이디/이름/구매물품/주소/연락처만 추출해보겠습니다.

이전에 배운 AS를 사용하여 별칭을 바꿔서 더 간결하게 표현할 수 있다. 하지만 userID빼고는 u와 b를 붙여 주지 않아도 된다. 왜냐하면 userID는 두 테이블에 전부 존재하지만 다른 항목들은 각각의 테이블에 한 개밖에 없기 때문이다.

이번에는 테이블 세 개를 조인을 해보겠습니다.

학생 테이블, 학생_동아리 테이블, 동아리 테이블을 먼저 생성 해보겠습니다.

CREATE TABLE stdTBL ( -- 학생 테이블
		stdName NVARCHAR(10) NOT NULL PRIMARY KEY,
		addr NCHAR(4) NOT NULL
); 

CREATE TABLE clubTBL ( -- 학생_동아리 테이블
		clubName NVARCHAR(10) NOT NULL PRIMARY KEY,
		roomNo NCHAR(4) NOT NULL
);

CREATE TABLE stdclubTBL ( -- 동아리 테이블
		num INT IDENTITY NOT NULL PRIMARY KEY,
		stdName NVARCHAR(10) NOT NULL
		FOREIGN KEY REFERENCES stdTBL(stdName),
		clubName NVARCHAR(10) NOT NULL
		FOREIGN KEY REFERENCES clubTBL(clubName)
);

INSERT INTO stdTBL VALUES('김범수', '경남'), ('성시경','서울'),('조용필','경기'),('은지원', '경북'),('바비킴', '서울');
INSERT INTO clubTbl VALUES('수영', '101호'), ('바둑', '102호'),('축구', '103호'),('봉사', '104호');
INSERT INTO stdclubTBL VALUES('김범수', '바둑'), ('김범수','축구'),('조용필','축구'),('은지원', '축구'),('은지원', '봉사'),('바비킴', '봉사');

테이블을 만들고 데이터를 입력하였습니다. 이제 학생 테이블, 동아리 테이블, 학생동아리 테이블을 이요해서 학생을 기준으로 학생 이름/지역/가입한 동아리/동아리 이름을 출력해보겠습니다.

SELECT s.stdName, s.addr, c.clubName, c.roomNo
FROM stdTBL AS s
INNER JOIN stdclubTBL AS sc
ON s.stdName = sc.stdName
INNER JOIN clubTBL AS c
ON sc.clubName = c.clubName
ORDER BY s.stdName;

이 쿼리문은 학생동아리 테이블과 학생 테이블의 일대다 관계를 INNER JOIN하고, 또한 학생동아리 테이블과 동아리 테이블의 일대다 관계를 INNER JOIN한 것입니다.

OUTER JOIN(외부 조인)

OUTER JOIN(외부 조인)이란 조건이 만족되지 않는 행까지 출력되는 것입니다. 구문은 아래 형식과 같습니다.

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
<LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
ON <조인될 조건>
[WHERE 검색조건];

외부 조인을 사용하여 구매 기록이 없는 회원을 뽑아보겠습니다.

JOIN의 개념은 정말 어렵기도 하고 저의 설명 실력도 부족한 것 같습니다... 그래서 아래에 링크 하나를 걸었어요 ㅠㅠJOIN을 시각화해주는 프로그램입니다!

sql-joins.leopard.in.ua/

SQL Joins Visualizer

Please select how do you want to do SQL JOIN between two table

sql-joins.leopard.in.ua

UNION [ALL] 개념

UNION은 두 쿼리의 결과를 행으로 합치는 것입니다. 구문은 아래 형식과 같습니다.

SELECT 문장1
	UNION [ALL]
SELECT 문장2

그냥 UNION만 사용했을 때는 데이터가 중복된 값을 제외하고 보여주지만 ALL을 적어주니까 중복된 데이터들도 출력해주네요! 그리고 빨간줄이 있을 때는 왠만해서는 적용이 되니까 무시하고 진행하셔도 될 것 같습니다.

SQL 프로그래밍

IF ... ELSE ...

참과 거짓에 의해서 실행해야 할 구문이 다를 때 사용한다. 구문은 아래 형식과 같습니다.

IF <부울 표현식>		-- 참(True)이라면 문장들1을 수행, 거짓(False)이라면 문장들2를 수행
	BEGIN
    	SQL 문장들1..
    END
ELSE
	BEGIN
    	SQL 문장들2..
    END

DECLARE @var1 INT -- @var1 변수선언
SET @var1 = 100 -- 변수에 값 대입

IF @var1 = 100	-- 만약 @var1이 100이라면, 그리고 SQL에는 '=='이 아닌 '='이다.
BEGIN
		PRINT '@var1이 100이다.'
	END
ELSE
	BEGIN
		PRINT '@var1이 100이 아니다.'
	END

SQL에서는 같다라는 의미는 '=' 한개다. C#과는 다르니까 코딩할 때 주의해야합니다!

CASE 문

DECLARE @point INT = 77, @credit NCHAR(1)

SET @credit =
	CASE
		WHEN (@point >= 90) THEN 'A'
		WHEN (@point >= 90) THEN 'B'
		WHEN (@point >= 90) THEN 'C'
		WHEN (@point >= 90) THEN 'D'
		ELSE 'F'
	END

PRINT N'취득점수 ==> ' + CAST(@point AS NCHAR(3))
PRINT N'학점 ==> ' + @credit

--SELECT '취득점수 ==> ' + CAST(@point AS NCHAR(3))		-- SELECT 문도 사용가능
--SELECT '학점 ==> ' + @credit

이번에는 고객 등급을 알아보는 소스를 작성 해보겠습니다. 이렇게 바로 소스를 작성한다면 좋은거겠지만 그러는게 힘들기 때문에 하나하나 작성을 해나가야 합니다. 처음에는 테이블 불러오기 부터 그룹핑, JOIN ... 순차적으로 작성하시는 것을 추천드립니다.

SELECT u.userID,u.name,SUM(price*amount) AS '총구매액',
	CASE
		WHEN (SUM(price * amount) >= 1500) THEN N'VVIP'
		WHEN (SUM(price * amount) >= 1500) THEN N'VIP'
		WHEN (SUM(price * amount) >= 1500) THEN N'NORMAL'
		ELSE N'유령고객'
	END AS '고객등급'
FROM userTBL AS u
LEFT OUTER JOIN buyTBL AS b
ON u.userID = b.userID
GROUP BY u.userID, u.name
ORDER BY SUM(price*amount) DESC;

WHILE 문

참인 동안에 계속 반복되는 반목문입니다. C#에서 배웠던 개념이랑 똑같습니다.

그럼 바로 예제로 1~100까지의 값을 모두 더하는 간단한 예제를 보겠습니다.

DECLARE @i INT = 1		-- 1 ~ 100까지 증가할 변수
DECLARE @hap BIGINT = 0	-- 더한 값을 누적할 변수

WHILE (@i <= 100)
BEGIN
	SET @hap += @i		-- @hap의 원래의 값에 @i를 더해서 다시 @hap에 넣으라는 의미
	SET @i += 1			-- @i의 원래의 값에 1을 더해서 다시 @i에 넣으라는 의미
END

PRINT @hap;		-- 결괏값 : 5050

결과가 제대로 나왔습니다! 하지만 여기서 7의 배수를 구하는 코드를 살펴 보겠습니다.

DECLARE @i INT = 1		-- 1 ~ 100까지 증가할 변수
DECLARE @hap BIGINT = 0	-- 더한 값을 누적할 변수

WHILE (@i <= 100)
BEGIN
	IF(@i % 7 = 0)
	BEGIN
		PRINT '7의 배수 '+CAST(@i AS CHAR(3))
		SET @i += 1;			-- @i의 원래의 값에 1을 더해서 다시 @i에 넣으라는 의미
	END

	SET @hap += @i;			-- @hap의 원래의 값에 @i를 더해서 다시 @hap에 넣으라는 의미
	IF (@hap > 1000) BREAK;
	SET @i += 1;

END

PRINT @hap;		-- 결괏값 : 1029

EXCL(동적 SQL)

SQL 문을 실행시켜주는 역할을 한다.

USE ShopDB
SELECT * FROM memberTBL;
DECLARE @sql VARCHAR(100);
SET @sql = 'SELECT * FROM memberTBL';
EXEC(@sql);

테이블과 뷰

이전에서 테이블 만드는 것을 많이 해보았지만 테이블은 만드는 방법이 중요한 것이 아니라, 어떻게 모델링(설계)했느냐가 훨씬 중요하기 때문에 다시 한 번 다루도록 하겠습니다.

CREATE DATABASE tableDB;
GO
USE tableDB;
GO

CREATE TABLE userTBL
(
	userID CHAR(8) NOT NULL PRIMARY KEY,
	name NVARCHAR(10) NOT NULL,
	birthYear INT NOT NULL,
	addr NCHAR(2) NOT NULL,
	mobile1 CHAR(3),
	mobile2 CHAR(8),
	height SMALLINT,
	mDate DATE
);

제약조건(Constraint)

데이터의 무결성을 지키기 위한 제한된 조건. 즉, 특정 데이터를 입력할 때 무조건 입력되는 것이 아니라, 어떠한 조건을 만족했을 때에만 입력되도록 제한한다. SQL Server는 데이터의 무결성을 위해서 6가지의 제약조건이 존재한다.

PRIMARY KEY 제약조건
FOREIGN KEY 제약조건
UNIQUE 제약조건
CHECK 제약조건
DEFAULT 정의
NULL 값 허용

기본 키 (Primary Key) 제약 조건

테이블의 각 행들을 구분할 수 있는 식별자이다. 중복될 수 없으며, NULL값이 입력될 수 없다.

기본키로 설정하면 자동으로 클러스터형 인덱스가 생성된다. 기본키는 하나의 열 또는 여러 개의 열을 합쳐서 설정할 수는 있으나, 한 개만 설정할 수 있다. 기본 키는 각 테이블 별로 하나만 존재해야 하지만, 기본 키를 하나의 열로만 구성해야 하는 것은 아니다. 필요에 따라서 두 개 또는 그 이상의 열을 합쳐서 하나의 기본 키로 설정할 수 도 있다.

PRIMARY (prodCode, prodID);

외래 키 (Foreign Key) 제약 조건

두 테이블 간의 관계를 선엄함으로써 데이터의 무결성을 보장해 줌. 외래키 관계를 설정하게 되면 하나의 테이블이 다른 테이블에 의존하게 된다. '외래키 테이블'에 데이터를 입력할 때는 꼭 '기준 테이블'을 참조해서 입력하므로, '기준 테이블'에 이미 데이터가 존재해야만 한다.

FOREIGN KEY(userID) REFERENCES userTBL(useID); -- 외래키에 이름을 지정할 필요가 없을 때

CONSTRAINT FK_userTBL_buyTBL		-- 외래키에 이름을 지정할 때
FOREIGN KEY REFERENCES userTBL(useID);

UNIQUE 제약 조건

UNIQUE 제약 조건은 '중복되지 않는 유일한 값'을 입력해야 하는 것이다. 기본 키(Primary Key)와 비슷하며 차이점은 Null값을 허용.(단, 1개만). 회원 테이블의 예를 든다면 주로 email 주소를 Unique로 설정하는 경우가 많다.

CREATE TABLE userTBL
(
	userID char(8) NOT NULL PRIMARY KEY,
	
	email1 char(30) NULL UNIQUE,

	email2 char(30) NULL			-- 3가지의 경우 결괏값은 모두 같다.
			CONSTRAINT AK_email UNIQUE,

	email3 char(30) NULL
			CONSTRAINT AK_email UNIQUE(email)
);

UNIQE 지정을 소스 코드가 아닌 개체 탐색기에서 해보겠습니다.

위의 인덱스창과 똑같이 데이터를 넣어주세요!

이제 데이터를 넣어보면 잘 들어가는 것을 확인할 수 있습니다.

'SSK' 즉, userID(PK)가 중복이 되어 오류가 발생합니다.

이번엔 userID(PK)와 name을 다르게 했지만 email이 UNIQUE라 제약 조건에 위반이 된다고 오류를 발생시킵니다. 그렇기 때문에 PK와 중복되지 않으면서 Email도 중복이 되지 않아야 합니다.

DEFAULT 정의

DEFAULT는 값을 입력하지 않았을 때, 자동으로 입력되는 기본 값을 정의하는 방법입니다.

userTBL - 디자인 - addr을 클릭해주면 밑에 열 속성이 나타나는데 기본값 또는 바인딩에 '서울'이라고 입력해줍니다. 입력하고 엔터를 누르면 N'서울'로 바뀔거예요. 그리고 저장을 해줍시다!(Ctrl + s)

NULL 값 허용

지금까지 계속 NULL을 사용하였다. NULL 값을 허용하려면 NULL을 허용하지 않으려면 NOT NULL을 사용한다. 하지만, PRIMARY KEY가 설정된 열에는 NULL 값이 있을 수 없으므로, 생략하면 자동으로 NOT NULL로 인식된다.

스키마

개체의 형식은 데이터베이스_이름.스키마_이름.개체_이름 이다.

스키마는 데이터베이스 내에 있는 개체들을 관리하는 묶고 스키마 이름을 생략하면 자동으로 디폴트 스키마인 'dbo'를 자동으로 붙여준다.사용자가 직접 스키마를 생성하고 지정할 수 있다.

USE sqlDB;

SELECT * FROM buyTBL;	--sqlDB에 있는 buyTBL 테이블

SELECT * FROM tableDB.dbo.buyTBL;	--tableDB에 있는 buyTBL 테이블

스키마는 강력하게 묶는 개념이라기보다는 관리의 편리를 위해서 가볍게 테이블을 묶어준다고 생각하는 것이 좋다.

뷰(View)

SELECT문으로 구성된 데이터베이스 개체이며 가상의 테이블(생성한 후에는 테이블과 동일하게 취급) 이다.

뷰의 장점은 보안에 도움이 되고 복잡한 쿼리를 단순화시킬 수 있다.

뷰 생성과 사용 예제

CREATE VIEW v userTBL
AS
	SELECT userID, name, addr, FROM userTBL
GO
	SELECT v_userTBL	-- 뷰를 테이블이라고 생각해도 무방

이만 포스팅을 마치겠습니다! 감사합니다 :)

Hasta Luego~!

'SQL Server' 카테고리의 다른 글

SQL Server 강의 6일차 (0)	2020.06.12
SQL Server 강의 5일차 (0)	2020.06.11
SQL Server 강의 3일차 (0)	2020.06.09
SQL Server 강의 2일차 (0)	2020.06.08
SQL Server 강의 1일차 (1)	2020.06.05

일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

문쑹이의 일지