Do it SQL__DO_IT_03_(based SQL Server)

chapter 03 SQL 시작하기

03-1 SELECT 문으로 데이터 검색하기 

주석 작성 방법/ 쿼리 실행 방법/ SELECT 문 특정 열 검색/ 테이블 열 정보확인

SELECT문	주석	Tip
select 열 from 테이블 where 조건 order by 열	-- 한줄 /* */ 여러줄	2개열을 나열하려면 쉼표 구분 대소 문자 구분 체크 자원 소모 고려 index, 조건문 사용
테이블 열정보확인	Alt+f1

 

03-2 WHERE 문으로 조건에 맞는 데이터 검색하기

특정 값 검색/ 비교 연산자 / 논리 연산자

특정 값 검색	비교 연산자	논리 연산자
열 = 조건값	<, >, <>, !=, !<, !>	ALL AND ANY BETWEEN EXIST IN LIKE NOT OR SOME
열 IS NULL 열 IS NOT NULL		OR 반복> IN 대체 소괄호로 우선순위 지정검색 IN, AND 결합 검색

 

 

03-3 ORDER BY 문으로 데이터 정렬하기

ORDERBY 문 열 기준 정렬

desc	asc	top 10 *	OFFSET N ROWS
select * from nasdaq_company where sector IS NOT NULL AND sector <> '' order by industry, sector desc  offset 1000 rows;
FETCH NEXT는 OFFSET과 함께 사용, Order by문을 함께 사용

 

03-4 와일드카드로 문자열 검색하기

LIKE, %로문자열 검색 /  _로문자열 검색 / _ 와 %를 조합한 문자열 검색 / [ ]로문자열 검색/ 와일드카드 사용

LIKE	특수 문자 포함한 문자열 검색	_ 특정 문자열 포함 길이 검색
A%, %A, %A%, AA%, %AA	%, _	A_, _A, _A_
where 열 LIKE 조건값	ESCAPE 이용	select * from nasdaq_company where symbol LIKE '__C%'
SELECT * FROM nasdaq_company  WHERE symbol  LIKE '%AA'	WITH CTE (col_1) AS (  SELECT 'A%BC' UNION ALL  SELECT 'A_BC' UNION ALL   SELECT 'ABC'  )  SELECT * FROM CTE WHERE COl_1 LIKE '%!%%' ESCAPE '!';
이 쿼리의 실행 계획은 nasdaq_company 테이블에 대해 symbol 컬럼이 'AA'로 끝나는 데이터를 검색하는 작업 이를 위해 테이블의 클러스터드 인덱스를 스캔하고 있음, %AA 조건은 인덱스에서 범위 스캔이 아닌 전체 스캔을 유도, 이는 성능상의 영향을 미칠 수 있음	WHERE COl_1 LIKE '%!%%' ESCAPE '!': LIKE 연산자를 사용하여 col_1에서 특정 패턴을 검색 LIKE '%!%%'은 문자열에서 '%' 문자가 포함된 항목을 찾음 일반적으로 %는 와일드카드로서 '임의의 문자 수'를 의미하지만, ESCAPE '!'로 인해 ! 문자가 LIKE 연산에서의 이스케이프 문자로 지정됨 즉, !%는 실제로 % 문자 자체를 의미 %!%% 의미: 첫 번째 %: 임의의 문자 0개 이상 !%            : 실제로 % 문자 두 번째 %: 임의의 문자 0개 이상	Clustered Index Scan이 발생하는 이유는 일반적으로 쿼리가 인덱스를 효율적으로 사용할 수 없거나, 특정 조건에 따라 전체 또는 대규모의 데이터를 검색 nasdaq_company 테이블에서 세 번째 문자가 'C'로 시작하는 모든 심볼(symbol) 값을 찾기 위해 클러스터드 인덱스 스캔을 수행하는 작업 클러스터드 인덱스인 uci_nasdaq_company_1을 통해 테이블을 스캔하고, symbol 값이 LIKE '__C%' 조건에 맞는 레코드를 찾음
NOT LIKE
[ ]로 문자나 문자 범위를 지정 문자열 검색	[A, B, C]% 또는 [A-C]% : 첫 글자가 A 또는 B 또는 C로 시작하는 모든 문자열 검색  %[A. B, C] 또는 %[A-C] : 마지막 글자가 A 또는 B 또는 C로 끝나는 모든 문자열 검색
	nasdaq_company 테이블에서 symbol 값이 'A'로 시작하고, 두 번째 문자가 'A', 'B', 또는 'C'인 값을 찾기 위해 클러스터드 인덱스 SEEK를 사용 인덱스 SEEK는 'A '와 'AD' 사이의 범위를 먼저 찾고, 그 중에서 두 번째 문자가 'A', 'B', 'C'인 값을 추가로 필터링 최종적으로, 조건에 맞는 데이터는 인덱스의 정렬된 순서대로 반환 결과적으로 찾는 데이터 symbol 값이 'AA', 'AB', 또는 'AC'와 같은 값들이 이 쿼리의 결과로 반환
SELECT * FROM nasdaq_company WHERE symbol LIKE 'A[^A, ^B, ^C]';	|--Clustered Index Seek(OBJECT:([DoItSQL].[dbo].[nasdaq_company].[uci_nasdaq_company_1]),  SEEK:([DoItSQL].[dbo].[nasdaq_company].[symbol] >= N'A' AND [DoItSQL].[dbo].[nasdaq_company].[symbol] < N'B'),  WHERE:([DoItSQL].[dbo].[nasdaq_company].[symbol] like N'A[^A, ^B, ^C]') ORDERED FORWARD) [^A, ^B, ^C]: 두 번째 문자는 'A', 'B', 'C'가 아닌 다른 문자 대괄호([]) 안에서 ^는 부정을 나타내며, 즉, 'A', 'B', 'C' 문자를 제외한 다른 문자 의미
	SELECT * FROM nasdaq_company WHERE symbol LIKE 'A[C,P][^T]%W'; A: 문자열은 반드시 'A'로 시작 [C,P]: 두 번째 문자는 'C' 또는 'P' [^T]: 세 번째 문자는 'T'가 아닌 문자, 대괄호 안의 ^는 부정을 의미( 'T'를 제외한 모든 문자를 포함) %: 네 번째 문자부터는 0개 이상의 임의의 문자가 올 수 있음 W: 마지막 문자는 반드시 'W'
퀴즈 5. nasdaq_company 테이블에서 company_name이 apple이라는 글자를 포함하는 목록을 출력	LIKE '%apple%'
퀴즈 6. nasdaq_company 테이블에서 symbol이 AA로 시작하면서 L, Q를 포함하는 목록을 출력	LIKE 'AA%[L,Q]%'
퀴즈 7. nasdaq_company 테이블에서 close_price가 $10 이상, $20 이하이면서 company_name이 A를 포함하지 않으면서, ipo_year가 2017년 이상인 목록을 출력 이때 close_price 내림차순, ipo_year 오름차순으로 출력	WHERE close_price >= 10 AND close_price <= 20   AND company_name NOT LIKE '%A%' AND ipo_year >= 2017 ORDER BY close_price desc, ipo_year ASC;

03-5 데이터 그룹화 다루기 

GROUP BY 문 / HAVING문 / DISTINCT 문

GROUP BY  문
	|--Hash Match  (Aggregate,  HASH:([DoItSQL].[dbo].[nasdaq_company].[industry]),  RESIDUAL:([DoItSQL].[dbo].[nasdaq_company].[industry] = [DoItSQL].[dbo].[nasdaq_company].[industry])) 실행 계획에서 Hash Match(Aggregate) 연산자는 nasdaq_company 테이블의 industry 컬럼을 기준으로 데이터를 해시 알고리즘을 사용해 그룹화하고 집계 각 industry 값에 대해 그룹을 만들고, 그 그룹에 대해 특정 집계 연산이 수행 RESIDUAL 조건은 해시 알고리즘이 올바르게 적용되도록 동일한 industry 값끼리 비교하는 역할을  |--Clustered Index Scan(OBJECT:([DoItSQL].[dbo].[nasdaq_company].[uci_nasdaq_company_1])) 클러스터드 인덱스는 테이블의 기본 인덱스로, 테이블의 모든 데이터가 물리적으로 인덱스 순서대로 저장 따라서 이 연산은 테이블의 데이터를 순서대로 접근
	|--Sort (DISTINCT ORDER BY:  ([DoItSQL].[dbo].[nasdaq_company].[sector] ASC,   [DoItSQL].[dbo].[nasdaq_company].[industry] ASC)) Sort 연산자는 결과 집합을 특정 컬럼에 따라 정렬하는 작업 이 작업은 쿼리 결과를 정렬된 상태로 반환하기 위해 수행 DISTINCT ORDER BY: 이 부분은 정렬뿐만 아니라 중복된 값들을 제거하는 작업도 수행한다는 것을 의미 nasdaq_company 테이블에서 sector와 industry 컬럼을 기준으로 데이터를 오름차순으로 정렬하고, 중복된 레코드를 제거하는 작업을 수행 최종적으로 sector 값이 동일한 레코드들은 industry 값에 따라 다시 정렬되며, 중복된 레코드는 제거되어 하나의 값만 남음
	|--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0))) Compute Scalar는 쿼리의 결과 집합에 대해 추가적인 계산을 수행하는 연산자 이 계산은 쿼리의 열을 변환하거나, 계산된 값을 새로 생성하는 작업을 포함        |--Stream Aggregate(GROUP BY: ([DoItSQL].[dbo].[nasdaq_company].[sector],  [DoItSQL].[dbo].[nasdaq_company].[industry]) DEFINE:([Expr1005]=Count(*))) Stream Aggregate는 데이터가 순차적으로 처리되며, 데이터가 집계 그룹으로 나뉘어지기 때문에 메모리 사용이 비교적 효율적. 이 방식은 큰 데이터 세트에서 집계 작업을 수행할 때 적합 GROUP BY: 이 부분은 데이터를 그룹화하는 기준을 정의 여기서는 sector와 industry 컬럼을 기준으로 데이터를 그룹화 DEFINE: 이 부분은 집계 연산의 결과를 정의 여기서는 Count(*) 집계 함수가 사용

 

HAVING 문	그룹화한 데이터에서 데이터를 필터링 HAVING 문을 사용 HAVING 문 은 WHERE 문과 비슷하지만, WHERE 문은 테이블에 있는 열에 적용하는 것이라면 HAVING 문은 SELECT 문이나 GROUP BY 문에 사용한 열에만 적용
	Clustered Index Scan 연산자는 nasdaq_company 테이블의 uci_nasdaq_company_1 클러스터드 인덱스를 사용하여 industry 컬럼이 'Advertising'인 레코드를 검색 클러스터드 인덱스를 전체적으로 스캔하여 조건에 맞는 데이터를 찾으며, industry가 'Advertising'인 모든 레코드를 반환
	그룹화에 사용한 열 기준으로 데이터 필터링 필터: 처리 중인 행에 지정된 조건을 적용하는 역할 WHERE 절에 정의된 조건을 만족하는 행만 유지하여 결과 집합을 효과적으로 줄임

 

DISTINCT 문	GROUP BY 문을 사용 하지 않고도 중복 데이터를 제거 DISTINCT 문을 사용
	|--Sort(DISTINCT ORDER BY: ([DoItSQL].[dbo].[nasdaq_company].[sector] ASC,  [DoItSQL].[dbo].[nasdaq_company].[industry] ASC)) SQL 실행 계획의 '정렬' 작업은 지정된 기준에 따라 결과 집합을 정렬하는 역할 주어진 실행 계획 세부사항에서: 1. 정렬: 이 작업은 지정된 순서에 따라 결과 집합의 행을 정렬 정렬은 오름차순(ASC) 또는 내림차순(DESC)으로 수행 2. DISTINCT: 이 옵션은 정렬이 적용된 후 결과 집합에 고유한 행만 포함 결과에서 중복 행을 제거
퀴즈 8. ipo_year 그룹별로 등록된 symbol 개수 출력
퀴즈 9. IPO 연도별 등록된 symbol 개수가 20개 이상인 sector 목록 내림차순

 

03-6 테이블 생성하고 데이터 조작하기

데이터베이스 생성 삭제 / 테이블 생성 삭제 / 데이터 삽입.수정. 삭제

/ 외래키 연결 다른 테이블에 검색 결과 입력 / 새 테이블에 검색 결과 입력 

테이블 생성 삭제	데이터 삽입 수정 삭제
외래키로 연결된 데이터 입력, 삭제 부모 테이블 데이터 입력한 뒤, 자식 테이블 같은 데이터 입력
새 테이블에 검색 결과 입력
	|    |--Index Seek( OBJECT:([DoItSQL].[dbo].[stock].[nci_stock_2]),  SEEK:([DoItSQL].[dbo].[stock].[symbol]=N'MSFT' AND  [DoItSQL].[dbo].[stock].[date] >= '2021-01-01 00:00:00.000'  AND [DoItSQL].[dbo].[stock].[date] <= '2021-02-01 00:00:00.000') ORDERED FORWARD) SEEK 조건은 SQL 서버가 인덱스를 통해 검색할 때 어떤 조건을 기반으로 데이터를 찾음 symbol = N'MSFT': symbol 컬럼이 'MSFT'인 데이터를 찾고. 여기서 N은 유니코드 문자열을 의미 date >= '2021-01-01 00:00:00.000': date 컬럼이 '2021-01-01 00:00:00.000' 이상인 데이터 date <= '2021-02-01 00:00:00.000': date 컬럼이 '2021-02-01 00:00:00.000' 이하인 데이터 결론적으로 이 Index Seek는 symbol이 'MSFT'이고, date가 2021년 1월 1일부터 2021년 2월 1일 사이인 데이터만을 인덱스를 통해 효율적으로 검색

 

QUIZ
퀴즈 10. 숫자 형식의 열 3개(col_1, col_2, col_3)를 가진               doit_quiz 테이블을 생성	CREATE TABLE doit_quiz ()
퀴즈 11. col_1, col_2, col_3 열 순서대로 (5, 3, 7)               데이터와(1, 4,9) 데이터를 삽입	INSERT INTO doit_quiz () values (), ()
퀴즈 12.  col_2의 값이 3인 데이터를 5로 수정	UPDATE doit_quiz SET col_2 = 5 where col_2 = 3
퀴즈 13.  col_1의 값이 5인 데이터를 삭제	delete doit_quiz where col_1 = 5
퀴즈 14. doit_quiz 테이블을 삭제	drop table doit_quiz

 03-7 SQL Server에서 다루는 자료형 정리하기

 숫자형/ 문자형/ 날짜형과 시간형