SQL 첫걸음 - 스터디 3주차

Об этом предложении

📚 스터디 교재 : SQL 첫걸음 (MySQL 기준) 📌 범위 : 5장 집계와 서브쿼리 / 6장 데이터베이스 객체 작성과 삭제 5장. 집계와 서브쿼리 20강. 행 개수 구하기 - COUNT 집계함수 일반 함수는 인수로 값 하나 를 받지만, 집계함수 는 인수로 집합(여러 행) 을 받아 값 하나 로 계산해 반환 → 이를 집계 라고 함 대표 집계함수 5가지: COUNT , SUM , AVG , MIN , MAX COUNT(*) 의 * 는 "모든 열 = 테이블 전체"를 의미 WHERE 구와 함께 쓰면 검색된 행 만 대상으로 개수를 셈 (SELECT 구는 WHERE 구보다 나중에 처리되므로) 집계함수와 NULL 집계함수는 집합 안에 NULL이 있으면 무시 하고 계산 단, COUNT(*) 만은 예외로 NULL도 포함해서 전체 행 수를 셈 DISTINCT로 중복 제거 DISTINCT 는 예약어(열명 아님). SELECT 구에서 지정하면 중복된 행을 제거 반대 개념은 ALL (기본값, 생략 시 ALL로 간주 → 중복 제거 안 함) 집계함수 + DISTINCT "NULL 제외하고 중복 없는 데이터 개수"를 구하려면 집계함수의 인수 에 DISTINCT를 붙인다 (콤마 없음) 21강. COUNT 이외의 집계함수 SUM( [ALL|DISTINCT] 집합) AVG( [ALL|DISTINCT] 집합) MIN( [ALL|DISTINCT] 집합) MAX( [ALL|DISTINCT] 집합) 함수 설명 적용 가능 자료형 SUM 합계 수치형만 AVG 평균 수치형만 MIN 최솟값 수치형, 문자열형, 날짜시간형 MAX 최댓값 수치형, 문자열형, 날짜시간형 SUM , AVG 도 NULL은 무시 하고 계산 (NULL을 0으로 간주하고 싶으면 CASE 로 변환 후 계산) 22강. 그룹화 - GROUP BY SELECT * FROM 테이블명 GROUP BY 열1, 열2, ... GROUP BY 로 지정한 열의 값이 같은 행끼리 하나의 그룹 으로 묶임 GROUP BY 만 쓰고 집계함수를 안 쓰면 DISTINCT 와 비슷한 효과 (중복 제거) 진짜 의미 는 집계함수와 함께 쓸 때 — 그룹마다 하나의 집합으로 집계함수에 넘겨짐 실무 활용: 점포별·상품별·월별 매출 집계 등에 자주 사용 HAVING 구로 조건 지정 집계함수는 WHERE 구에서 사용할 수 없음 (WHERE가 GROUP BY보다 먼저 처리되기 때문) 집계 결과에 조건을 걸고 싶으면 HAVING 구 사용 (GROUP BY 뒤에 기술) 내부 처리 순서 WHERE 구 → GROUP BY 구 → HAVING 구 → SELECT 구 → ORDER BY 구 HAVING도 SELECT보다 먼저 처리되므로 SELECT에서 붙인 별명은 HAVING/GROUP BY에서 쓸 수 없음 (단, MySQL은 예외적으로 허용 — 데이터베이스 제품마다 다름) ORDER BY는 그룹화 이후 처리이므로 집계함수를 문제없이 사용 가능 복수열 그룹화 주의 GROUP BY 에 지정하지 않은 열 은 집계함수 없이 SELECT 구에 쓸 수 없음 (어떤 값을 반환해야 할지 모호하므로 에러) 여러 열로 그룹화하려면: GROUP BY no, quantity 처럼 콤마로 나열 정렬 GROUP BY 만으로는 결과가 정렬되지 않음 → ORDER BY 구를 추가로 사용 23강. 서브쿼리 (SELECT 명령) 서브쿼리 = SQL 명령 안에 괄호로 묶어 지정하는 하부 SELECT 명령 주로 WHERE 구에서 사용되지만 SELECT , FROM , SET 구 등 다양한 곳에서 사용 가능 WHERE 구에서 서브쿼리 사용 열 값이 가장 작은 행을 몰라도 서브쿼리로 찾아서 바로 삭제 스칼라 값 / 스칼라 서브쿼리 SELECT 명령이 값 하나(행 1개 × 열 1개)만 반환 하는 것 = 스칼라 값 스칼라 값을 반환하는 서브쿼리 = 스칼라 서브쿼리 = 연산자로 비교하려면 반드시 스칼라 값끼리 비교해야 함 SELECT 구, SET 구에서 서브쿼리를 쓸 때는 스칼라 서브쿼리 여야 함 WHERE 구에 스칼라 서브쿼리를 쓰면 HAVING 없이도 집계 결과를 조건식으로 사용 가능 MySQL은 FROM 구 생략 가능, Oracle은 FROM DUAL 필요 (DUAL = 시스템이 기본 제공하는 테이블) FROM 구에서 서브쿼리 사용 (중첩구조/네스티드 쿼리) FROM 구의 서브쿼리는 스칼라 값이 아니어도 됨 서브쿼리에는 AS 키워드로 별명을 붙여 사용 (3장의 열 별명과 같은 방식) 여러 단계로 중첩 가능 (실무에서는 잘 안 씀) INSERT 명령과 서브쿼리 INSERT SELECT: VALUES 대신 SELECT 명령으로 여러 행 복사/이동 24강. 상관 서브쿼리 EXISTS (SELECT 문) EXISTS 서브쿼리가 반환하는 행이 있는지 를 조사 (행이 있으면 참, 없으면 거짓 — 스칼라 값일 필요 없음) NOT EXISTS : 반대로 행이 없을 때 참 상관 서브쿼리란 위 예제처럼 부모 쿼리(UPDATE)와 자식인 서브쿼리가 서로 연관 되어 있는 경우 (서브쿼리의 no2 = no 에서 no 는 부모 테이블의 열을 참조) 상관 서브쿼리는 부모 명령과 떼어내 단독으로 실행할 수 없음 (단순 서브쿼리와 차이점) 두 테이블의 열 이름이 같을 경우 테이블명.열명 형식으로 명시 IN 열명 IN (집합) 집합 안에 값이 있으면 참 ( NOT IN 은 반대) 집합 부분에 서브쿼리 도 지정 가능 IN과 NULL 주의 : 집계함수는 NULL을 무시하지만, IN 은 무시하지 않음 — NULL = NULL 은 비교 불가하므로 NULL 비교는 IS NULL 사용. 특히 NOT IN 은 집합 안에 NULL이 있으면 결과가 전부 불명(UNKNOWN) 이 되어버림 6장. 데이터베이스 객체 작성과 삭제 25강. 데이터베이스 객체 데이터베이스 객체 : 테이블, 뷰, 인덱스, 프로시저 등 데이터베이스 안에 정의하는 모든 것 (실체를 가짐) SELECT, INSERT 같은 SQL 명령은 객체가 아님 (실체가 없음) 명명 규칙 기존 이름·예약어와 중복 금지 / 숫자로 시작 불가 / 언더스코어( _ ) 외 기호 사용 불가 / 한글 사용 시 백쿼트( ` )로 감쌈(MySQL) / 시스템이 허용하는 길이 초과 불가 이름은 의미 있게 짓는 것이 좋음 (단순 번호 지양) 같은 스키마 내에서는 객체 종류와 상관없이 이름 중복 불가 (테이블 t1 이 있으면 같은 이름의 뷰도 불가) 스키마 데이터베이스 객체는 스키마 라는 "그릇" 안에 생성됨 → 스키마가 다르면 이름이 같아도 무방 객체 = 스키마 객체 라고도 불림. DDL로 정의 데이터베이스 제품마다 무엇이 스키마가 되는지 다름 (MySQL: CREATE DATABASE 로 만든 데이터베이스 자체가 스키마 / Oracle: 데이터베이스+사용자가 계층적 스키마) 이름 충돌을 막는 그릇 = 네임스페이스 . 스키마와 테이블 모두 네임스페이스 역할을 함 26강. 테이블 작성·삭제·변경 CREATE TABLE 테이블명 (열 정의1, 열 정의2, ...) DROP TABLE 테이블명 ALTER TABLE 테이블명 하부명령 테이블 작성 열명 자료형 [DEFAULT 기본값] [NULL|NOT NULL] 자료형 뒤 NOT NULL 생략 시 기본적으로 NULL 허용 테이블 삭제 DROP TABLE 테이블명 테이블과 저장된 데이터가 모두 삭제 됨. 확인 메시지 없이 바로 실행되므로 주의 테이블 정의는 남기고 데이터만 지우려면 DELETE (행 단위 처리라 느림) 또는 TRUNCATE TABLE (조건 지정 불가, 전체 삭제 전용, 훨씬 빠름) 테이블 변경 (ALTER TABLE) ALTER TABLE 테이블명 변경명령 데이터를 유지한 채 구조만 변경 가능. 크게 1 열 추가·삭제·변경, 2 제약 추가·삭제 로 구분 작업 하부명령 예시 열 추가 ADD ALTER TABLE sample62 ADD newcol INTEGER; 열 속성 변경 MODIFY ALTER TABLE sample62 MODIFY newcol VARCHAR(20); 열 이름 변경 CHANGE ALTER TABLE sample62 CHANGE newcol c VARCHAR(20); 열 삭제 DROP ALTER TABLE sample62 DROP c; ADD 로 열 추가 시 기존 행의 새 열 값은 전부 NULL (기본값 지정 시 그 값으로 채워짐). NOT NULL 제약을 걸며 추가하려면 반드시 기본값을 지정 해야 함 MODIFY 는 열 이름은 못 바꾸지만 자료형·기본값·제약은 변경 가능 (MySQL/Oracle 전용 문법 — 방언 존재) 열 이름 변경은 CHANGE 사용 (Oracle은 RENAME TO ) 실무 활용: 문자열 최대 길이 늘리기( MODIFY ), 열 추가( ADD ) — 단, 열 추가 시 기존 INSERT 문에 영향 없는지 꼭 확인 27강. 제약 테이블에 제약 을 설정해 저장될 데이터를 제한 (예: NOT NULL ) 그 외 대표적 제약: 기본키(Primary Key) 제약 , 외부참조(정합) 제약 열 제약 vs 테이블 제약 하나의 열에 설정하는 제약 = 열 제약 ( NOT NULL , UNIQUE 등) 여러 열에 걸친 제약 = 테이블 제약 (예: 복합 기본키). CONSTRAINT 제약명 으로 이름을 붙여두면 관리가 쉬움 제약 추가/삭제 제약 추가 시 기존 데이터가 제약을 위반하면 에러 발생 (기존 행 먼저 검사) 기본키(Primary Key) 테이블의 행 한 개를 특정 할 수 있는 검색키 → 같은 값 중복 불가 (= 유일성 제약 ) 기본키로 지정할 열은 반드시 NOT NULL 이어야 함 중복되는 값으로 INSERT/UPDATE 시도 시 에러 발생 ( Duplicate entry ... for key 'PRIMARY' ) 복수 열로 기본키 구성 가능 : 이 경우 열들을 조합한 값 이 유일해야 함 (한 열만 보면 중복이어도, 다른 열과 합쳐서 유일하면 OK) 28강. 인덱스 구조 인덱스란 색인 이라고도 함. 역할은 검색 속도 향상 책의 목차·색인처럼, 검색 키워드와 대응하는 데이터 위치를 저장 테이블과 별개의 독립된 객체지만 테이블에 의존 (테이블 삭제 시 인덱스도 함께 삭제됨) 검색 알고리즘 방식 설명 속도 풀 테이블 스캔 처음부터 모든 행을 차례로 조사 데이터 수에 비례해 느려짐 이진 탐색 정렬된 집합을 절반씩 나눠가며 탐색 데이터가 2배 늘어도 비교 횟수는 +1회 데이터베이스 인덱스는 보통 이진 트리(binary tree) 구조로 저장 (그 외 해시 방식도 있음) 각 노드는 왼쪽(작은 값)·오른쪽(큰 값) 가지로 분기, 루트 노드 에서 시작해 가지를 타고 내려가며 탐색 유일성 : 이진 트리는 기본적으로 중복값을 가질 수 없는 구조 → 기본키 제약이 이진 트리 인덱스로 구현되는 경우가 많음 29강. 인덱스 작성과 삭제 CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ...) DROP INDEX 인덱스명 [ON 테이블명] 표준 SQL에는 CREATE INDEX 명령이 없음 (제품 의존적 선택 항목이지만 대부분 지원) Oracle/DB2: 인덱스가 스키마 객체 (스키마 내 이름 중복 불가) / SQL Server·MySQL: 테이블 내 객체 (테이블 내 이름 중복 불가) 인덱스를 만들면 SELECT 검색은 빨라지지만 , INSERT 시 인덱스도 갱신해야 하므로 속도가 다소 느려짐 EXPLAIN EXPLAIN SQL명령 실제로 실행하지 않고 실행계획 (인덱스 사용 여부 등)을 확인하는 명령 (표준 SQL은 아니지만 대부분 제품이 지원) possible_keys : 사용 가능한 인덱스 / key : 실제 사용된 인덱스 (조건에 해당 열이 없으면 둘 다 NULL) 최적화 인덱스 사용 여부는 데이터베이스 내부 최적화 처리 가 실행계획을 세워 결정 값의 종류가 적은 열(예: '예'/'아니오'만 있는 열)은 인덱스를 걸어도 이진 트리 구조가 비효율적 이 되어 품질이 낮음 → 서로 다른 값이 많을수록 인덱스 효율이 좋아짐 30강. 뷰 작성과 삭제 CREATE VIEW 뷰명 AS SELECT문 DROP VIEW 뷰명 뷰란 FROM 구의 서브쿼리에 이름을 붙여 객체화 한 것 SELECT 명령 자체는 객체가 될 수 없지만, 뷰로 등록하면 이름 붙여 관리 가능 뷰를 참조하면 정의된 SELECT 명령의 실행 결과 를 테이블처럼 사용 가능 뷰의 열을 직접 지정할 수도 있음 (지정 안 하면 SELECT 구의 열이 그대로 뷰의 열이 됨) 가상 테이블 뷰는 실체(저장 데이터)가 없는 "가상 테이블" — SELECT 명령만 저장 데이터로 가짐 INSERT/UPDATE/DELETE도 조건에 따라 가능은 하지만, SELECT 전용으로 사용하는 것을 권장 뷰의 약점과 대안 뷰를 참조할 때마다 내부 SELECT 명령이 매번 재실행 됨 → 원본 데이터가 많거나 뷰를 중첩 사용하면 처리 속도 저하 머티리얼라이즈드 뷰(Materialized View) : 결과를 저장장치에 실제로 저장 해두고 재사용 (원본 데이터가 바뀌면 자동으로 재계산). MySQL은 미지원, Oracle·DB2 등에서 사용 가능 상관 서브쿼리처럼 부모와 연관된 쿼리는 뷰로 단독 실행이 안 됨 → 이런 경우 함수 테이블 (인수를 받아 테이블을 반환하는 사용자 정의 함수)로 대체 가능