SQL Server 강의 2일차

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

오늘은 SQL Server의 툴과 유틸리티를 사용하는 법에 대해서 다뤄보겠습니다. VS(Visual Studio)와 SSMS를 연결하는 작업도 해보겠습니다 :)

SELECT문

SELECT의 기본 형식은 SELECT 열이름 FROM 테이블 이름 WHERE 조건 입니다.

SELECT * FROM productTBL;

SQL문에서 *의 뜻은 ALL이라는 뜻입니다. 즉, productTBL 테이블로부터 모든 것을 선택해서 보여달라는 뜻입니다.

실행은 F5를 눌러서 실행 시켜주시면 됩니다.

테이블을 만들 때 NULL을 허용한다고 체크가 되있으면 NULL을 넣으셔도 상관없습니다.

데이터베이스 개체의 활용

인덱스

인덱스란 대부분의 책에 붙어 있는 '찾아보기(또는 색인)'와 같은 개념입니다. 데이터들은 양이 적으면 이 인덱스가 있든지 없든지 큰 문제가 되지 않지만, 수천만~수억 건에 이르는 데이터에서 인덱스 없이 전체 데이터를 찾아본다는 것은 SQL Server 입장에서는 엄청나게 부담스러운 일이 될 것입니다.

memberTBL 테이블과 producrTBL 테이블을 비교해보면 인덱스가 기본키(Primary Key)의 생성여부에 따라 나오는 것을 확인 할 수 있다. 자세한건 추후에 다루도록 하겠습니다.

뷰(View)

뷰(View)란 가상의 테이블, 사용자의 입장에서는 테이블과 같게 보이지만, 뷰는 실제 행 데이터를 가지고 있지 않으며, 테이블에 링크(Link)된 개념입니다.

3번째 줄 select 문에서 *(ALL)이 아닌 memberID, memberAddress만 보이게끔 작성하였다. 확인 후, 왼쪽 개체 탐색기에서 뷰에서 새 뷰를 만들어 줍니다.

만들고 싶은 View테이블을 추가해 줍니다.

memberID, memberAddress 두개를 체크한 후 뷰를 만들어 줍니다.

뷰를 새로 고침해보면 V_memberTBL이라는 만들었던 View테이블이 생성되었습니다.

결과가 똑같은 것을 확인할 수 있습니다. 빨간줄이 생겨도 괜찮으니 실행(F5)를 눌려서 실행시켜 주셔도 됩니다.

저장 프로시저(Stored Procedure)

저장 프로시저(Stored Procedure)는 SQL Server에서 제공하는 프로그래밍 기능입니다. 즉, SQL 문을 하나로 묶어서 편리하게 사용하는 기능입니다.

개체 탐색기 - 프로그래밍 기능 - 저장 프로시저 에서 새로 만들어 줍니다.

그림1의 소스코드를 그림2 소스코드로 바꿔주세요! 그리고 여기서는 Ctrl+s를 눌러서 저장을 해주시면 안되고 실행(F5)을 해주셔야 저장이 됩니다. Ctrl+s를 눌러서 저장하면 File로 만들어 집니다!

개체 탐색기 - 프로그래밍 기능 - 저장 프로시저 새로고침을 하면 테이블이 생성이 되었습니다.

명령문을 실행 시켜보면 그림2에서 설정해주었던 테이블의 필드를 확인할 수 있습니다.

트리거(Trigger)

트리거(Trigger)란 테이블에 부착되어서, 테이블에 INSERT나 UPDATE 또는 DELECT 작업이 발생하면 실행되는 코드입니다.

백업과 복원

백업은 데이터베이스를 다른 매체에 보관하는 작업, 복원은 백업된 데이터를 원상태로 돌려 놓는 작업을 뜻합니다.

처음에 만들었던 SQLDATA - BACKUP폴더에 저장되는 것을 확인할 수 있습니다.

저장된 위치에 백업파일이 생성된 것을 확인할 수 있습니다.

이제 백업파일을 활용해 복원을 해보겠습니다. 아무 컬럼 하나를 지워주세요.

ShopDB - 테스크 - 복원 - 데이터베이스로 가주세요

백업된 날짜, 데이터베이스 여러가지 나옵니다.

페이지선택 - 옵션을 위와같이 설정해주세요 이렇게 안하면 복원이 안된다고 합니다 :)

memberTBL 테이블을 다시 확인 해보니 삭제 되었던게 복구가 되었습니다!

다음으로 Visual Studio로 가서 새로운 프로젝트를 만들어주겠습니다.

윈도우 폼(.Net Framwork)를 만들어주세요.

서버 추가 - 데이터 연결 - 연결 추가

그리고 이전에 설치가 안 되어 있다면 그냥 확인을 눌러서 설치를 진행한 후 이어가시면 됩니다. 그리고 세번째 Microsoft SQL Server을 선택하고 계속을 눌러주세요.

ssms에서 만들었던 ShopDB를 연결해주는 작업입니다. 그리고 확인을 하기 전에 꼭 연결 테스트를 누른 다음 확인을 눌러주세요!

DataGridView를 마우스로 드래그해서 윈도우 폼에 뿌려주세요!

그럼 회색 창이 나옵니다! 이 부분에 저희의 DB내용이 뿌려질 거예요

그리드뷰의 맨 오른쪽 위에 조그만한 플레이 버튼을 클릭해서 데이터 소스선택에서 링크된 프로젝트 데이터 소스 추가를 클릭해주세요.

데이터 베이스 - 데이터 세트를 다음 해주면 이전에 했던 연결 추가가 또 나오게 됩니다.

새 연결을 눌러주세요 이전의 DB가 설정되있지만 새 연결로 다시 연결하는 것을 추천드립니다.

위에서 입력했던 정보를 똑같이 입력해주세요!

연결을 추가한 후 위와 같이 체크를 해주세요 그럼 다음 버튼이 활성화가 됩니다. 그리고 문자열을 표시했을때 내용이 나오지 않는다면 DB연결에 문제가 발생했다는 뜻입니다.

테이블을 펼쳐서 memberTBL 테이블을 선택해줍니다.

그리드 뷰에 개체들이 생성되었습니다! 이제 디버그 하지 않고 시작(Ctrl + F5)을 눌러주세요.

짠! 그럼 SSMS에서 만들었던 DB의 내용들이 나오는 것을 확인할 수 있습니다.

SQL Server의 화면 구성을 잠깐 살펴보겠습니다.

강의 1일차에서 입력했던 정보들이 쿼리문으로 작성되잇는 것을 확인할 수 있습니다.

Transact-SQL

T-SQL의 분류

SQL 문은 크게 DML, DDL, DCL로 분류한다.

DML(Data Manipulation Language) - 데이터 조작 언어. 선택, 삽입, 수정, 삭제를 하는데 사용된다. SQL 문 중에 SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당된다.
DDL(Data Definition Language) - 데이터 정의 언어. 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할을 한다. SQL 문 중에 CREATE, DROP, ALTER 등이 해당된다.
DCL(Data Control Language) - 데이터 제어 언어. 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용한다. SQL 문 중에 GRANT, REVOKE, DENY가 이 구문에 해당된다.

*실무 용어 - CRUD (Create[SELECT], Request[INSERT], Update, Delete)

SELECT와 FROM

USE ShopDB; -- 사용할 DB로 이동.
GO -- GO는 옵션, 한줄 주석입니다.

SELECT memberID, memberName, memberAddress
FROM ShopDB.dbo.memberTBL; -- 정확한 DB위치를 적어주면 어디서든 연결이 가능하다.

/*
이부분은 여러줄
주석입니다
C#과 동일합니다
*/

userTBL 회원 테이블을 실행 시킵니다.

생성된 userTBL 테이블의 디자인을 선택해주면 1일차에서 저희가 클릭으로만 생성했던 모습과 동일하게 소스 코드로 작성한 테이블이 만들어졌습니다.

USE sqlDB;
CREATE TABLE userTBL -- 회원 테이블
(
	userID char(8) NOT NULL PRIMARY KEY, -- 사용자 아이디
	name NVARCHAR(10) NOT NULL, -- 이름
	birthYear INT NOT NULL, -- 출생 연도
	addr NCHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만 입력)
	mobile1 CHAR(3), -- 휴대폰의 국번(010)
	mobile2 CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈 제외)
	height SMALLINT, -- 키
	mDate DATE -- 회원 가입일
);
GO

CREATE TABLE buyTBL -- 회원 구매 테이블
(
	num INT IDENTITY NOT NULL PRIMARY KEY, -- 순번(PK)
	userID CHAR(8) NOT NULL
	    FOREIGN KEY REFERENCES userTBL(userID),  -- 아이디(FK)
	prodName NCHAR(6) NOT NULL, -- 물품명
	groupName NCHAR(4), -- 분류
	price INT NOT NULL, -- 단가
	amount SMALLINT NOT NULL -- 수량
);
GO

회원 테이블과 회원 구매 테이블 소스 코드입니다.

작성한 소스 코드를 사용하여 다이어그램을 그려 보겠습니다.

모든 테이블을 다 추가해주세요.

이름을 지정해주고 저장해줍니다.

PK(기본키)와 FK로 연결을 해주니 선이 하나가 생기면서 참조해주었다는 메세지가 나옵니다. 이것이 관계형 데이터 베이스입니다.

저희가 이전에 만들었던 ShopDB에서 보면 참조된 것이 없는 것이 보이시나요? 위의 sqlDB테이블과의 차이점은 FK(외래키)로 참조를 해주었느냐 아니냐의 차이입니다.

INSERT INTO userTBL VALUES('LSG', '이승기',1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO userTBL VALUES('KBS', '김범수',1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO userTBL VALUES('KKH', '김경호',1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO userTBL VALUES('JYP', '조용필',1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO userTBL VALUES('SSK', '성시경',1979, '서울', NULL, NULL, 186, '2013-12-12');
INSERT INTO userTBL VALUES('LJB', '임재범',1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO userTBL VALUES('YJS', '윤종신',1969, '경남', NULL, NULL, 170, '2005-5-5');
INSERT INTO userTBL VALUES('EJW', '은지원',1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO userTBL VALUES('JKW', '조관우',1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO userTBL VALUES('BBK', '바비킴',1973, '서울', '010', '0000000', 176, '2013-5-5');
GO

INSERT INTO buyTBL VALUES('KBS', '운동화', NULL, 30, 2);
INSERT INTO buyTBL VALUES('KBS', '노트북', '전자', 1000, 1);
INSERT INTO buyTBL VALUES('JYP', '모니터', '전자', 200, 1);
INSERT INTO buyTBL VALUES('BBK', '모니터', '전자', 200, 5);
INSERT INTO buyTBL VALUES('KBS', '청바지', '의류', 50, 3);
INSERT INTO buyTBL VALUES('BBK', '메모리', '전자', 80, 10);
INSERT INTO buyTBL VALUES('SSK', '책', '서적', 15, 5);
INSERT INTO buyTBL VALUES('EJW', '책', '서적', 15, 2);
INSERT INTO buyTBL VALUES('EJW', '청바지', '의류', 50, 1);
INSERT INTO buyTBL VALUES('BBK', '운동화', NULL, 30, 2);
INSERT INTO buyTBL VALUES('EJW', '책', '서적', 15, 1);
INSERT INTO buyTBL VALUES('BBK', '운동화', NULL, 30, 2);
GO

INSERT 문을 사용하여 각각의 데이터를 넣어보겠습니다. 문자형에 데이터를 입력하려면 ' '(작은따옴표)로 묶어줘야 한다.

실행한 결과 입니다. 데이터에 NULL도 적용이 잘 됬고 오류 없이 데이터가 전부 들어갔습니다.

특정한 조건의 데이터만 조회하는 <SELECT ... FROM ... WHERE>

기본적인 WHERE 절

SELECT 필드이름 FROM 테이블이름 WHERE 조건식;

관계 연산자의 사용

조건에 맞는 결괏값이 '이승기', '성시경'이 나오는 것을 확인할 수 있다.

BETWEEN ... AND 와 IN()와 LIKE

키가 180~183인 사람을 조회한 결과이다. 비교 연산자를 빼고 BETWEEN ... AND를 써서 동일하게 값을 얻어 낼 수 있다.

OR 연산자와 동일하게 연속적인 값이 아닌 이산적인 값을 위해 IN()을 사용할 수 있다.

위의 조건은 '경'으로 시작하는 주소를 무엇이든(%) 허용한다는 의미이다. '경'이 맨 앞 글자인 것들을 추출한다.

그리고 한 글자와 매치하기 위해서는 '_'를 사용한다. '김' 뒤의 언더바(_) 두개를 넣고 성이 '김'씨인 사람을 다 출력하는 문구이다.

서브쿼리

서브쿼리(SubQuery)란 쿼리문 안에 또 쿼리문이 들어 있는 것을 얘기한다.

첫번째 SELECT 문은 키가 177이상인 사람을 출력하고 두번째 SELECT 문은 이름이 '김경호'의 키를 출력한다.

키가 177이상인 '김경호'를 키 177에 두번째 SELECT문을 대입하면 아래와 같이 된다. 이때, 김경호의 기준이 1명이였기 때문에 가능한 대입식이다. 예를 들어, '경남'에 사는 사람들을 출력하는 SELECT 문을 했다면 2개 이상이 나와서 오류를 발생시킨다.

출력된 결과를 보면 동일하다는 것을 알 수 있다.

위에서 배운 IN() 절을 사용하면 OR과 같은 역할을 하기 때문에 각각 비교하여 결과를 도출한다.

173과 170이라는 두 개의 값을 반환하기 때문에 오류가 발생한다. 그래서 필요한 구문이 ANY인데 173보다 크고 170보다 큰 사람을 출력해라는 뜻이되는데 결국은 170보다 큰 사람을 출력해달라는 것과 같다.

ORDER BY

ORDER BY 절은 결과물에 대해 영향을 미치지는 않지만, 결과가 출력되는 순서를 조절하는 구문이다. 그리고 ORDER BY 절은 쿼리문의 제일 뒤에 와야 한다는 것을 주의해야한다.

가격(price)을 기준으로 내림차순(DESC), 오름차순(ASC)으로 출력한다는 뜻이다.

왼쪽 그림은 price로 내림차순을 하였고 오른쪽 그림은 가격순으로 내림차순을 한 후 다시 userID로 오름차순을 해서 출력해주었다.

DISTINCT와 TOP(N)과 TABLESAMPLE 절

DISTINCT를 사용하여 중복되는 값을 하나로 묶어준다.

수 만개의 데이터가 있을 때 상위 N개만 보고 싶을때 TOP(N)을 사용하여 보고 싶은 만큼만 볼 수 있다. 내림차순을 사용하여 뒤에서 N개 만큼도 볼 수 있다.

새로운 테이블이 생성 되었다. buyTBL 테이블로 부터 buyTBL_new로 데이터를 복사한다는 뜻이다.

GROUP BY

이 절이 하는 역할은 말 그대로 그룹으로 묶어주는 역항을 한다.

사용자(userID)별로 구매한 개수(amount)를 합쳐서 출력하는 것이다. 이럴 경우에는 집계 함수인 SUM()과 GROUP BY절을 사용하면 된다. 사용자 별로 GROUP BY로 묶어준 후에 SUM()함수로 구매 개수를 합치면 된다. 하지만 SUM의 결과 열에는 제목이 없다. 이럴때는 AS 별칭을(alias)를 사용해서 문제를 해결할 수 있다.

집계 함수

SUM() 외에 자주 사용되는 집계 함수입니다.

함수명	설명
AVG()	평균을 구한다.
MIN()	최소값을 구한다
MAX()	최대값을 구한다.
COUNT()	행의 개수를 센다.
COUNT_BIG()	개수를 센다. 단, 결괏값이 bigint형이다.
STDEV()	표준편차를 구한다.
VAR()	분산을 구한다.

Having

위에서 했던 SUM()을 다시 사용해서 사용자별 총 구매액을 구해보면 아래와 같다.

하지만 오류 메세지가 나타난다. 집계함수는 WHERE 절에 나타날 수 없다는 이야기다. 이럴때 사용하는 것이 HAVING 절이다. WHERE과 비슷한 개념으로 조건을 제한하는 것이지만, 집계함수에 대해서 조건을 제한하는 것이다. 그리고 HAVING 절은 꼭 GROUP BY 절 다음에 나와야한다. 순서가 바뀌면 안 된다.

총 구매액이 적은 사용자부터 나타내려면 ORDER BY를 사용하면 된다.

SELECT userID as '사용자', SUM(price*amount) as '총 구매액'
FROM buyTBL
--WHERE SUM(price*amount) > 1000 --집계함수에서는 사용 불가능
GROUP BY userID
HAVING SUM(price*amount) >= 1000
ORDER BY SUM(price*amount) ASC;

하.하.하 양이 정말 많네요! 강사님도 바뀌고 하루 8시간씩 수업을 들으면서 정리를 하는거다 보니 양이 많아서 보는데 어려울 수도 있지만 저의 취업을 위해서 남겨둬야겠어요! 그럼 이만 포스팅을 마치겠습니다 감사합니다 :)

Hast Luego~!

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

SQL Server 강의 5일차 (0)	2020.06.11
SQL Server 강의 4일차 (0)	2020.06.10
SQL Server 강의 3일차 (0)	2020.06.09
SQL Server 강의 1일차 (1)	2020.06.05
SQL Server 및 SSMS(SQL-Server-Management-Studio) 설치 방법 (2)	2020.06.05

문쑹이의 일지