본문으로 바로가기

[DB] 2. 운영 실습, SELECT 문

category 개발자과정준비/데이터베이스(DB) 2020. 6. 8. 23:43
반응형

서버 이름은 내 아이피로하지만 나중에 회사가면 개발서버나 회사자체의 서버를 이용하게 될 것임

쉬프트+딜리트로 한줄을 통째로 지울 수 있다.

 

 

지난주에 작성했던 테이블을 다시 불러오자

ShopDB 우클릭 ㅡ> 새 쿼리를 클릭해서 쿼리 창을 연다음, 'SELECT*FROM productTBL; '을 입력해주자
지난 주에 작성했던 테이블로 결과 테이블이 나왔음.

이때 테이블에서 NULL값을 넣었는데 테이블이 Null을 허용했기 때문에 오류가 뜨지 않는다. (테이블을 작성할때 NULL을 체크했냐 안했냐에따라서 다르다)

왼쪽 메뉴에서 본인의 설정했던 자료 타입과 NULL의 여부를 볼 수도 있다

 

 

 

인덱스

책 맨뒤에 찾아보기와 같은 개념(DB에서는 꼭 있어줘야함)

단어를 아는데 단어의 뜻은 찾고싶은데 앞의 목차로는 찾을 수 없다. 그래서 맨 뒤의 찾아보기에서 찾는게 더 편하다.

데이터들은 양이 적으면 이 인덱스가 있거나 없든 큰 문제가 되지 않지만, 수천만~수억 건에 이르는 데이터에서 인덱스 없이 전체 데이터를 찾아본다는 것은 SQL Server 입장에서는 엄청나게 부담스러운 일이 될 것이다.

 

DB에서 개인 키(프라이버리 키)를 지정하면 인덱스가 생긴다.

 

 

 

 

 

 

뷰

뷰는 가상의 테이블.

사용자의 입장에서는 테이블과 같게 보이지만, 뷰는 실제 행 데이터를 가지고 있지 않으며, 테이블에 링크(Link)된 개념이다. 보안적인 부분이 좋아짐

테이블 두 개를 추가해준다
추가한 테이블에 새로고침을 해준다
쿼리창에서 새롭게 만든 쿼리를 출력하면 방금 선택했던 열만 출력된다.

 

 

 

 

저장 프로시저

SQL 서버에서 제공하는 프로시저를 만드는 것임(C#의 메서드같은거)

(참고로, 프로그래밍에서는 메서드 = 함수 = 프로시저 라고 볼 수 있다.)

 

일종의 메서드라고 생각하면된다. BEGIN END는 C#에서 { }이라고 생각하면된다.
왼쪽 메뉴에 저장 프로시저를 새로고침

 

새 쿼리를 열고 EXEC myProc;를 입력해보자

두개의 테이블이 출력되는데 이 구문은 C#에서 Console.WriteLine(); 과 같다고 본다. 메서드를 했지만 리턴한 것이 아니고 화면 그대로 출력되는 것이라 생각하면된다.

 

 

트리거

트리거는 테이블에 부착되어서 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발샣아면 실행되는 코드이다.

 

 

데이터베이스 백업 및 관리

백업은 데이터베이스를 다른 매체에 보관하는 작업, 복원은 DB에 문제가 발생했을때 다른 매체에 백업된 데이터를 이용해서 원상태로 돌려놓는 작업을 말한다.

설치할때 지정했던 백업폴더로 저장된다.

이렇게 행을 삭제하는 사고가 발생했다고 가정해보자.

ShopDB ㅡ> 테스크 ㅡ> 복원 ㅡ> 데이터베이스를 클릭
페이지 선택중에 일반에서 ShopDB를 지정해준다
옵션에서 각 부분에 체크와 체크해제를해주고
삭제 전에 저장했던 상태로 복원을 할 수 있다

 

 

 

 

온라인 설명서

강사님도 사용하지 않으니 패스

 

 

비주얼 스튜디오에서의 연동

서버 탐색기에서 데이터 연결 우클릭 ㅡ> 연결 추가

데이터 소스 선택창에서 MS SQL 서버를 클릭하고 계속을 눌러준다.

서버 탐색기가 없으면 툴바창의 보기에서 꺼낼 수 있다.

 

Ctrl + F5로 실행했을때 DB 테이블이 출력된다

 

 

4장. 데이터베이스 모델링

생략

 

 

5장. SSMS(SQL Server Management Studio) 실행

실행

 

서버에 연결 창

1. 서버 유형 : 데이터 베이스 엔진

2. 서버 이름 : SQL서버 이름을 입력 또는 선택

3. 인증 : SQL Server 인증은 외부에서 SQL Server 2019에 접근하고자 하는 경우에 사용(주로 사용)

디폴트인 windows 인증모드는 SQL Server가 설치된 컴퓨터에서만 SSMS를 사용

4. 사용자 비밀번호 : 

5. 연결 속성 : 특별한 경우가 아니면 지정하지 않아도 됨

 

 

SSMS의 화면 구성

1. 도킹윈도우

2. 개체탐색기

3. 쿼리창 : 쿼리 문장(SQL 구문)을 입력하고 실행하는 텍스트 에디터

 

 

디버깅 기능

VS와 비슷하게 버그를 잡기위해 사용

 

프로파일러(Profiler)

발생하는 각종 이벤트를 추적하고 수집함으로써 현재 어떤 SQL문이 서버의 성능을 떨어뜨리는지 쉽게 확인

 

 

 

사실 5장까지는 아예 빠른 시일내에 기본만 짚고 넘어갔기때문에 빨리 넘어갔음.

 

대신 6장은 무조건 중요하기때문에 천천히라도 해봅시다.

 

 

6장. Transact-SQL 기본

T-SQL의 분류(246p)

1. DML : 데이터를 조작(SELECT선택, INSERT삽입, UPDATE수정, DELETE삭제)하기 위한 언어

CRUD                             R              C              U                D                         

 

2. DDL : 데이터를 정의하는 언어이다. CREATE(만들고), DROP(삭제)하고 ALTER(변경)하는 역할을 한다.

DDL은 트랜잭션을 발생시키지 않는다. 따라서 ROLLBACK(되돌림)이나 완전 적용(COMMIT)을 시킬 수가 없다.

 

3. DCL : 데이터 제어 언어는 사용자에게 어떤 권한을 부여하거나 빼앗을때 주로 사용하는 구문이다.

GRANT/REVOKE/DENY가 해당된다.

 

 

SELECT 문

DB 내의 테이블에서 원하는 정보를 추출하는 명령이다.대괄호는 생략가능하다.

[WITH <common_table_expression>]
SELECT select_list [INTO new_table]
[FROM table_source] [WHERE search_condition]
[GROUP BY group_by expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]

SELECT 열목록

FROM 테이블이름

WHERE 조건

 

USE문

USE 데이터베이스_이름

현재 사용하는 DB를 지정하거나 변경하는 구문

'--' 한줄 주석  또는 /* */ 블록 주석

3개의 구문이 다 똑같은 것을 확인할 수 있다

 

 

 

심플 데이터베이스 생성

쿼리 창에 위의 예제처럼 구문들을 입력(전체 코드는 밑에 참조해두었습니다)
테이블이 새로 생성된 것을 확인할 수 있다.

위에 구문은 이미 만들었기때문에 다시 실행하면 에러가 난다. 그래서 새로 쓴 부분만 드래그해서 실행하는게 좋다.

두 테이블을 추가하고 닫기를 눌러주면 두 테이블이 화면에 뜬다
ShopDB와의 차이를 비교해보면 관계를 맺은것의 차이가있다.(ShopDB는 열쇠로 이어지지 않았음)

 

USE tempdb;
GO
CREATE DATABASE sqlDB;
GO

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), -- 휴대폰의 국번(011, 016, 017, 018, 019, 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 --아이디(FK)
FOREIGN KEY REFERENCES userTbl(userID),
prodName NCHAR(6) NOT NULL, -- 물품명
groupName NCHAR(4) , -- 분류
price INT NOT NULL, -- 단가
amount SMALLINT NOT NULL -- 수량
);
GO

INSERT INTO userTBL VALUES
('LSG', '이승기',1987, '서울', '011', 11111111,
182, '2008-08-08');
/* 
어떤 컬럼을 넣겠다고 명시를 안했기때문에 왼쪽 개체 탐색기의
테이블 열의 순서에 맞춰서 해야함
*/

INSERT INTO userTBL VALUES
	('KBS', '김범수', 1979, '경남', '011', 22222222,
	182, '2012-4-4');

	INSERT INTO userTBL VALUES
	('KKH', '김경호', 1971, '전남', '019', 33333333,
	177, '2007-7-7');

	INSERT INTO userTBL VALUES
	('JYP', '조용필', 1950, '경기', '011', 44444444,
	166, '2009-4-4');

	INSERT INTO userTBL VALUES
	('SSK', '성시경', 1979, '서울', NULL, NULL,
	186, '2013-12-12');

	INSERT INTO userTBL VALUES
	('LBJ', '임재범', 1963, '서울', '016', 66666666,
	182, '2009-9-9');

	INSERT INTO userTBL VALUES
	('YJS', '윤종신', 1969, '경남', NULL, NULL,
	170, '2005-5-5');

	INSERT INTO userTBL VALUES
	('EJw', '은지원', 1972, '경북', '011', 88888888,
	174, '2014-3-3');

	INSERT INTO userTBL VALUES
	('JKW', '조관우', 1965, '경기', '018', 99999999,
	172, '2010-10-10');

	INSERT INTO userTBL VALUES
	('BBK', '바비킴', 1973, '서울', '010', 00000000,
	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

SELECT*FROM userTBL
SELECT*FROM buyTBL


	/*
INSERT INTO userTBL(userID, name, birthYear, addr)
   VALUES ('SMG', '성명건', 1976, '부산');
    -- 순서를 명시했으면 뒤에 VALUES 개수랑 순서가 딱 맞아야함

	INSERT INTO userTBL VALUES
	('KKH', '김경호', 1971, '전남');  
	-- 빈 곳에는 NULL을 넣어야한다. 넣지않으면 오류가 뜬다.
	
	SELECT*FROM userTBL
	*/

테이블이 생성된 것을 확인할 수 있다

 

 

WHERE 절

조회하려는 결과에 특정한 조건을줘서 원하는 테이블의 데이터를 출력할때 사용한다.(참고로 이것을 필터링이라고한다)

WHERE 문으로 원하는 데이터를 확인할 수 있다

'L%'는 L로 시작하는 아무 문자열을 받아서 출력한다.

 

 

관계연산자의 사용(=, <, >, <=, >=, != 등)

위의 예제에서 1970년 이후에 출생하고, 신장이 182 이상인 사람의 아이디와 이름을 조회해보자.

SELECT*
FROM userTBL
WHERE birthYear >=1970
ANd height >= 182;

 

 

1970년 이후에 출생했거나, 신장이 182 이상인 사람의 아이디와 이름을 조회해보자.

SELECT*
FROM userTBL
WHERE birthYear >=1970
OR height >= 182;

 

BETWEEN ... AND  와  IN() 와 LIKE

키가 180~183인 사람을 조회해보자. 이때, AND를 두번 사용해야하지만,

BETWEEN ... AND를 사용해서 코드를 줄일 수 있다.

 

 

 키의 경우 숫자로 구성되어있어서 연속적인 값을 가지고있기 때문에 BETWEEN ... AND를 사용했지만, 지역이 '경남'이거나 '전남'이거나 '경북'인 사람을 찾을 경우에 연속된 값이 아니기때문에 BETWEEN...AND를 사용할 수 없다.

 

IN으로 좀 더 간단하게 표현할 수 있다.

 

 

 

이 조건은 김이 제일 앞글자인 것들을 추출한다. 한 글자와 매치하기위해서는 '_'를 사용한다. 

 

 

 

 

서브 쿼리

쿼리문 안에 쿼리문이 들어 있는 것을 얘기한다.

 

실제로 경남에 사는 사람들의 키 3개에 뭘 비교하는지 몰라서 오류가 뜸
키가 173보다 크거나 같은 사람, 또는 키가 170보다 크거나 같은 사람이 모두 출력된다. 즉, 170보다 크거나 같은 사람이 해당된다.

 

 

 

원하는 순서대로 정렬하여 출력하기 : ORDER BY

기본적으로 오름차순(ASCENDING)으로 정렬된다. 내림차순(DECSENDING)으로 정렬하려면 열 이름 뒤에 DESC라고 적어주면 된다. ORDER BY 절은 쿼리문의 제일 뒤에 와야 한다는 것을 기억하자.

테이블의 순서에 주목해보자
가격이 싼거, 비싼 순서대로 정렬
DESC와 ASC를 같이 써본 예제, 위의 오른쪽 테이블과 비교해서 주목해보자

 

DISTINCT와 TOP(N)과 TABLESAMPLE절

DISTINCT는 중복되는 데이터를 제거해준다.

DISTINCT 문 사용한 예제
TOP(N)구문

 

 

SELECT INTO

SELECT INTO 구문은 테이블을 복사해서 사용할 경우에 주로 사용된다.(219p)

SELECT 복사할열 INTO 새로운테이블 FROM 기존테이블

 

 

 

GROUP BY와 HAVING과 집계 함수

SELECT 형식 중에서 GROUP BY, HAVING 절에 대해 알아보자.

 

GROUP BY은 결과를 그룹으로 묶어준다. 집계 함수(Aggregate Function)는 주로 GROUP BY 절과 함께 쓰이며 데이터를 그룹화(Grouping)해주는 기능을 한다. 집계 함수에 들어가는 필드를 제외하면 꼭 필드를 그룹화해야 결괏값이 올바르게 나온다.

 

일단 새 쿼리에서

SELECT *
FROM buyTBL;  를 쳐서 내가 쓸 테이블을 확인해보고 시작하자.

 

숫자형 타입에 SUM()외의 자주 사용되는 집계함수

- AVG() : 평균을 구한다.

- MIN() : 최솟값을 구한다.

- MAX() : 최대값을 구한다.

- COUNT() : 행의 개수 센다

- COUN_BIG() : 개수를 센다. 단 결과값이 bigint 형이다.

- STDCV() : 표준편차를 구한다.

사용자별, 지역별, 나이별, 출생년도 별로 등등 이 값들이 GROUP BY에 들어가있어야 사용할 수 있다
GROUP BY에 없거나 있을때 결과의 차이
평균 뿐만아니라 MAX, MIN도 똑같이 적용할 수 있다.

 

 

키가 제일 큰 사람과 제일 작은 사람의 키와 회원이름을 출력하는 쿼리를 만들어보자.

 

이렇게 작성하면 오류가 뜨는데, GROUP BY 없이는 별도의 이름 열(name)을 집계 함수와 같이 사용할 수 없다는 의미이다.
정작 제일 작은키나 큰 키를 알기는 쉬운데 이름이 없으니 좀 더 수정이 필요하다
서브 쿼리와 조합하면 키가 제일 큰사람과 작은 사람을 같이 출력할 수 있다

만약 총 구매액이 1000 이상인 사용자에게만 사은품을 증정하고 싶다면, WHERE문으로 조건을 걸어서 사용했겠지만 집계 함수에 대해서 조건을 제한하는것은 헤빙이다

SELECT userID, 
    SUM(price * amount) AS '총 구매액'  -- 필드 없이 쓸때는 그룹바이가 필요없지만 그룹핑할라믄 그룹바이에 맞춰야함
	FROM buyTBL
	--WHERE userID = 'BBK'  -- 필드값, 필드값에 대한 조건(AND,OR,IN)으로 찾는 것들을 필터링할때 사용
	GROUP BY userID
	HAVING SUM(price * amount) >= 1000   -- SUM이나 MIN같은 집계함수는 HAVING으로 사용
	ORDER BY SUM(price * amount) ASC  -- 집계함수들은 ORDER BY로 정렬 가능

WHERE을 쓸때는 테이블 자체의 값에 필터링을 하고싶을때 주로 쓰지만, SUM이나 AVG같이 연산을 했을때의 필터링을 하고싶을때는 HAVING을 쓴다.

 

 

지금 해야하는건 셀렉트문을 숙지하는 것이 중요할듯

내일은 with절 부터 시작

ROLLUP, GROUPING_ID, CUBE() 함수 등등

 

 

 

기타)

쿼리 창 글씨체 바꾸기

글씨에 바꾸는법은 도구 -> 옵션 -> 글씨 및 폰트로 바꿀 수 있는 것을 지난 시간에 설명했다.

새로운 폰트를 다운받고 C , 윈도우s, 폰트 에 옮겨주기

콘솔라스, 굴림, 나눔고딕코딩  폰트 비교

반응형
저작자표시 비영리 변경금지

'개발자과정준비 > 데이터베이스(DB)' 카테고리의 다른 글

[DB] 5. 인덱스, 트랜잭션, 데이터베이스 모델링, 저장 프로시저, 사용자 정의 함수  (0) 2020.06.12
[DB] 4. JOIN, UNION, SQL프로그래밍, 테이블, 뷰  (0) 2020.06.10
[DB] 3. ROLLUP(), WITH절, INSERT문, 데이터형식의 종류, JSON 데이터, PIVOT  (0) 2020.06.09
[DB] 1. DBMS 개요, DB 생성 실습  (0) 2020.06.08
[DB] 0. SQL Server 및 SSMS 설치  (0) 2020.06.07
댓글 , 엮인글
취준생 일지
블로그 이미지 도웅 님의 블로그
MENU
    CATEGORY
    VISITOR 오늘 / 전체

    티스토리툴바