※혼자 공부하는 SQL (한빛미디어, 우재남 저)를 교재로 공부하였다.
강의 교재라서 쓴 것이지 솔직히 추천할 정도는 아니다.
sql은 보통 대문자로 기재하는데 소문자여도 상관없다.
다 작성하고 깨달은 건데 이건 ChatGPT로 만들어진 게시물이다.
01_MySQL 및 워크벤치 설치 및 시작
MySQL 홈페이지에서 MySQL Community를 다운로드
-
설치 진행 시 Custom으로 진행하여 MySQL Server와 MySQL Workbench, Samples and Example을 선택하여 설치(선택은 +를 계속 눌러 최하위로 가야 선택 가능), 귀찮으면 Full 설치.
-
설치 진행 중 Port관련 오류 발생하면 다른 SQL에서 포트를 사용 중임으로 Port 번호를 변경하여 설치
-
설치 진행 중 Root 비밀번호를 설정하는데 분실하면 재설치해야 함.
-
사실 워크벤치는 필수가 아닌데 이전 쿼리문이나 결과 등을 확인하기엔 좋은데 뭔가 불편하다. 개인적으론 프롬프트 선호
-
SQL 관련 용어 간단 정리
| 용어 | 설명 |
|---|---|
| 데이터베이스 (Database) | 구조화된 데이터의 모음을 저장하고 관리하는 시스템. 여러 테이블과 다른 객체들을 포함할 수 있음. |
| 테이블 (Table) | 데이터를 행과 열의 형태로 저장하는 데이터베이스 객체. 각 열은 필드를 나타내고, 각 행은 레코드(데이터)를 나타냄. |
| 관계 (Relation) | 데이터베이스에서 테이블 간의 상호 작용을 나타내는 개념. 테이블 간의 관계를 통해 데이터를 연결하고 관리함. |
| 스키마 (Schema) | 데이터베이스 구조를 정의하고 관리하기 위한 전체적인 설계도. 스키마는 테이블, 필드, 관계 등의 구조를 명시함. |
| ERD (Entitiy Relationship Diagram) | 엔터티와 그들 간의 관계를 시각적으로 나타내는 다이어그램. 데이터베이스의 구조와 관계를 이해하기 쉽게 표현함. |
| 엔티티 (Entity) | 데이터베이스에서 식별 가능한 개체나 개념. 예를 들어, 고객, 주문, 제품 등의 실제 존재하거나 추상적인 개체를 나타냄. |
| 데이터 (Data) | 정보의 형태로 표현되는 값들의 집합. 데이터베이스 내에서 저장되고 처리되는 정보를 의미함. |
| 열 (Column), 필드 (Field) | 테이블의 수직적인 데이터 요소로, 각 열은 특정 유형의 데이터를 포함하고 해당 데이터의 속성을 정의함. |
| 행 (Row), 레코드 (Record) | 테이블의 수평적인 데이터 요소로, 각 행은 하나의 레코드를 나타냄. 행은 각 필드에 대응하는 실제 데이터 값을 포함함. |
| 기본 키 (Primary Key) | 테이블에서 각 레코드를 고유하게 식별하기 위해 사용되는 열 또는 열의 조합. 기본 키 값은 중복되지 않고 NULL 값을 가질 수 없음. |
| 외래 키 (Foreign Key) | 다른 테이블의 기본 키를 참조하는 열로, 테이블 간의 관계를 정의하는 데 사용됨. 외래 키는 참조 무결성을 유지하기 위해 사용됨. |
| 인덱스 (Index) | 테이블에서 검색 속도를 향상시키기 위해 생성되는 데이터 구조. 인덱스를 통해 특정 열의 값에 빠르게 접근할 수 있음. |
| 쿼리 (Query) | 데이터베이스에서 정보를 검색하거나 조작하기 위해 사용되는 명령문. 쿼리는 데이터의 조회, 수정, 삭제, 추가 등을 수행함. |
| 조인 (Join) | 두 개 이상의 테이블을 연결하여 연관된 데이터를 함께 검색하거나 조작하는 작업. INNER JOIN, LEFT JOIN 등 다양한 유형이 있음. |
| 뷰 (View) | 하나 이상의 테이블에서 유용한 데이터만 포함하는 가상 테이블. 뷰는 실제 데이터를 저장하지 않고 쿼리 결과를 표시함. |
| 트랜잭션 (Transaction) | 데이터베이스에서 수행되는 작업의 논리적인 단위. 트랜잭션은 모두 성공하거나 모두 실패하도록 보장됨. ACID 원칙을 따름. |
ChatGPT의 답변.
02_SQL 기본 문법
- 분류 및 설명
| 용어 | 설명 |
|---|---|
| DDL (Data Definition Language) | 데이터베이스의 구조를 정의하거나 수정하는 작업을 수행하는 SQL 명령어들의 집합. 주로 CREATE, ALTER, DROP 등이 포함됨. |
| DML (Data Manipulation Language) | 데이터를 조회, 삽입, 수정, 삭제하는 작업을 수행하는 SQL 명령어들의 집합. 주로 SELECT, INSERT, UPDATE, DELETE 등이 포함됨. |
| DCL (Data Control Language) | 데이터베이스에 대한 권한을 부여하거나 관리하는 작업을 수행하는 SQL 명령어들의 집합. 주로 GRANT, REVOKE 등이 포함됨. |
| TCL (Transaction Control Language) | 트랜잭션을 관리하는 작업을 수행하는 SQL 명령어들의 집합. 주로 COMMIT, ROLLBACK, SAVEPOINT 등이 포함됨. |
ChatGPT의 응답.
- 유형에 따른 명령어
| 명령어 | 유형 | 설명 |
|---|---|---|
| CREATE | DDL (Data Definition Language) | 새로운 데이터베이스 객체(테이블, 인덱스 등)를 생성하는 명령어. |
| DROP | DDL (Data Definition Language) | 데이터베이스 객체(테이블, 인덱스 등)를 삭제하는 명령어. |
| TRUNCATE | DDL (Data Definition Language) | 테이블의 모든 데이터를 삭제하는 명령어. 테이블 구조는 유지되며, AUTO_INCREMENT 값은 초기화됨. |
| SHOW | DDL (Data Definition Language) | 데이터베이스 객체(테이블, 인덱스 등)에 대한 정보를 표시하는 명령어. |
| DESC | DDL (Data Definition Language) | 테이블의 구조(스키마)를 표시하는 명령어. 테이블의 컬럼명, 데이터 형식, 제약 조건 등의 정보를 확인할 수 있음. |
| SELECT | DML (Data Manipulation Language) | 데이터베이스에서 데이터를 조회하는 명령어. 테이블로부터 데이터를 검색하거나 조작하여 반환함. |
| INSERT | DML (Data Manipulation Language) | 데이터베이스 테이블에 새로운 레코드를 삽입하는 명령어. |
| UPDATE | DML (Data Manipulation Language) | 테이블에 있는 기존 레코드의 데이터를 업데이트하는 명령어. |
| DELETE | DML (Data Manipulation Language) | 테이블에서 특정 조건에 맞는 레코드를 삭제하는 명령어. |
| GRANT | DCL (Data Control Language) | 데이터베이스 사용자에게 특정 권한을 부여하는 명령어. |
| REVOKE | DCL (Data Control Language) | 데이터베이스 사용자로부터 특정 권한을 제거하는 명령어. |
| COMMIT | TCL (Transaction Control Language) | 트랜잭션의 변경 내용을 확정하고 데이터베이스에 영구적으로 적용하는 명령어. |
| ROLLBACK | TCL (Transaction Control Language) | 트랜잭션의 변경 내용을 취소하고 이전 상태로 되돌리는 명령어. |
| SAVEPOINT | TCL (Transaction Control Language) | 트랜잭션 내에서 저장점을 설정하여 나중에 롤백할 위치를 지정하는 명령어. |
ChatGPT 답변.
- 자료형
| 자료형 | 설명 |
|---|---|
| Numeric(숫자형) | |
| TINYINT | 매우 작은 정수를 저장하는 자료형. 보통 1바이트로 표현되며, -128부터 127까지의 범위를 가짐. |
| SMALLINT | 작은 정수를 저장하는 자료형. 2바이트로 표현되며, 대략 -32,768부터 32,767까지의 범위를 가짐. |
| MEDIUMINT | 중간 크기의 정수를 저장하는 자료형. 3바이트로 표현되며, 대략 -8,388,608부터 8,388,607까지의 범위를 가짐. |
| INT or INTEGER | 정수를 저장하는 자료형. 일반적으로 4바이트로 표현되며, 대략 -2,147,483,648부터 2,147,483,647까지의 범위를 가짐. |
| BIGINT | 매우 큰 정수를 저장하는 자료형. 8바이트로 표현되며, 대략 -9,223,372,036,854,775,808부터 9,223,372,036,854,775,807까지의 범위를 가짐. |
| DECIMAL(p, s) | 고정 소수점 숫자를 저장하는 자료형. p는 전체 자릿수, s는 소수점 이하 자릿수를 나타냄. |
| FLOAT(p) | 부동 소수점 숫자를 저장하는 자료형. p는 유효 숫자의 전체 자릿수를 나타냄. |
| DOUBLE(p) | 더 큰 범위의 부동 소수점 숫자를 저장하는 자료형. FLOAT보다 더 많은 유효 자릿수를 허용함. |
| String(문자형) | |
| CHAR(n) | 고정 길이 문자열을 저장하는 자료형. n은 최대 길이를 나타냄. |
| VARCHAR(n) | 가변 길이 문자열을 저장하는 자료형. 최대 길이를 지정하고 실제 저장된 문자열의 길이에 따라 저장 공간을 효율적으로 사용함. |
| TINYTEXT | 작은 크기의 텍스트 데이터를 저장하는 자료형. 최대 길이는 255바이트. |
| TEXT | 중간 크기의 텍스트 데이터를 저장하는 자료형. 최대 길이는 65,535바이트. |
| MEDIUMTEXT | 대용량 텍스트 데이터를 저장하는 자료형. 최대 길이는 16,777,215바이트. |
| LONGTEXT | 매우 큰 크기의 텍스트 데이터를 저장하는 자료형. 최대 길이는 4GB. |
| Date and Time(날짜 및 시간형) | |
| DATE | 날짜를 저장하는 자료형. 'YYYY-MM-DD' 형식으로 저장됨. |
| TIME | 시간을 저장하는 자료형. 'HH:MM:SS' 형식으로 저장됨. |
| DATETIME | 날짜와 시간을 함께 저장하는 자료형. 'YYYY-MM-DD HH:MM:SS' 형식으로 저장됨. |
| TIMESTAMP | 날짜와 시간을 저장하는 자료형. 주로 시간이 업데이트된 시점을 자동으로 기록할 때 사용됨. |
| Miscellaneous(기타) | |
| BOOLEAN | 논리값을 저장하는 자료형. 0 또는 1로 표현됨. |
| ENUM(val1, val2, ...) | 주어진 값 중 하나를 선택하여 저장하는 열거형 자료형. 지정된 값 중 하나만 저장할 수 있음. |
| SET(val1, val2, ...) | 주어진 값들의 집합 중 하나 이상을 선택하여 저장하는 집합형 자료형. 여러 개의 값 중 선택하여 저장할 수 있음. |
| JSON | JSON 데이터를 저장하는 자료형. MySQL 5.7.8 이상부터 지원됨. JSON 데이터를 쿼리 및 조작할 수 있음. |
| Binary(이진형) | |
| BINARY(n) | 고정 길이 이진 데이터를 저장하는 자료형. 최대 길이를 지정함. |
| VARBINARY(n) | 가변 길이 이진 데이터를 저장하는 자료형. 최대 길이를 지정하고 실제 저장된 이진 데이터의 길이에 따라 저장 공간을 효율적으로 사용함. |
| BLOB | 이진 데이터를 저장하는 자료형. 이미지, 동영상 등의 바이너리 데이터를 저장할 수 있음. |
ChatGPT 응답인데 SQL마다 조금씩 다를 수 있으며 MySQL과도 다른 부분이 있음.
연산자
- 산술 연산자 (Arithmetic Operators)
| 연산자 | 설명 | 예시 |
|---|---|---|
| + | 덧셈을 수행 | a + b |
| - | 뺄셈을 수행 | a - b |
| * | 곱셈을 수행 | a * b |
| / | 나눗셈을 수행 | a / b |
| % | 나머지를 계산 | a % b |
| DIV | 나눗셈의 몫을 계산 | a DIV b |
- 비교 연산자 (Comparison Operators)
| 연산자 | 설명 | 예시 |
|---|---|---|
| = | 같음을 나타냄 | a = b |
| <> 또는 != | 다름을 나타냄 | a <> b 또는 a != b |
| > | 초과를 나타냄 | a > b |
| < | 미만을 나타냄 | a < b |
| >= | 이상을 나타냄 | a >= b |
| <= | 이하를 나타냄 | a <= b |
| BETWEEN | 범위에 속하는지를 나타냄 | a BETWEEN x AND y |
| LIKE | 패턴 매칭을 수행함 (와일드카드 사용 가능) | name LIKE 'J%' |
| IN | 목록에 속하는지를 나타냄 | department_id IN (1, 2, 3) |
- 논리 연산자 (Logical Operators)
| 연산자 | 설명 | 예시 |
|---|---|---|
| AND | 모든 조건이 true일 때 true를 반환함 | condition1 AND condition2 |
| OR | 적어도 하나의 조건이 true일 때 true를 반환함 | condition1 OR condition2 |
| NOT | 조건의 결과를 반대로 뒤집음 | NOT condition |
- 비트 연산자 (Bitwise Operators)
| 연산자 | 설명 | 예시 |
|---|---|---|
| & | 비트 AND 연산 | a & b |
| | | 비트 OR 연산 | a | b |
| ^ | 비트 XOR 연산 | a ^ b |
| ~ | 비트 NOT 연산 (단항 연산자) | ~a |
| << | 왼쪽 시프트 연산 (비트를 왼쪽으로 이동) | a << 2 |
| >> | 오른쪽 시프트 연산 (비트를 오른쪽으로 이동) | a >> 1 |
- 기타 연산자
| 연산자 | 설명 | 예시 |
|---|---|---|
| . | 문자열 연결 | CONCAT('Hello', 'World') |
| -> | JSON 객체 또는 배열에서 요소를 추출 | json_column->'$.key' |
| :: | 타입 캐스트 (타입 변환) | '123'::INT |
ChatGPT 답변.
02-01_DB 생성 및 제거
CREATE DATABASE mart_db DEFAULT CHARACTER SET utf8mb4; -- 데이터베이스 생성 및 기본 문자 형식 지정
DROP DATABASE mart_db; -- 데이터베이스 제거
CREATE DATABASE IF NOT EXISTS mart_db; -- 데이터베이스가 존재하지 않는다면 생성
SHOW DATABASES; -- 존재하는 DB 모두 조회
USE mart_db; -- 해당 데이터베이스 사용
CREATE TABLE member ( -- 테이블 생성
mem_id CHAR(4) NOT NULL,
mem_name VARCHAR(8) NOT NULL,
addr CHAR(2) NOT NULL,
phone1 CHAR(3) NULL, -- NULL 미기재 동일
phone2 CHAR(8),
points INT NOT NULL DEFAULT 50,
sign_date DATE
);
ALTER TABLE member -- 테이블 설정 변경
ADD CONSTRAINT
PRIMARY KEY(mem_id);
CREATE TABLE buy
(num INT AUTO_INCREMENT PRIMARY KEY,
mem_id CHAR(4) NOT NULL,
category CHAR(8),
prod_name CHAR(8),
price INT NOT NULL,
amount SMALLINT NOT NULL,
FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);
SHOW tables; -- 데이터베이스의 모든 테이블 조회
desc member; -- 테이블 정보 조회
show create table buy; -- 테이블 생성 정보 조회- 제약 조건
| 조건 | 설명 |
|---|---|
| NOT NULL | 해당 컬럼은 NULL 값을 허용하지 않음. 즉, 반드시 데이터가 존재해야 함. |
| PRIMARY KEY | 해당 컬럼을 기본 키로 지정함. 기본 키는 각 행을 고유하게 식별하는 역할을 수행하며, 중복값과 NULL을 허용하지 않음. |
| UNIQUE | 해당 컬럼에 있는 모든 값이 고유해야 함. 즉, 중복된 값이 허용되지 않음. UNIQUE 제약 조건은 NULL 값을 여러 개 가질 수 있음. |
| DEFAULT value | 컬럼에 기본값을 지정함. 데이터를 INSERT할 때 명시적으로 값이 주어지지 않으면 기본값이 사용됨. |
| AUTO_INCREMENT | 정수형 컬럼에 사용되며, 해당 컬럼에 자동으로 증가하는 값을 할당함. 주로 기본 키로 사용되며, 값이 자동으로 생성됨. |
| CHECK | 해당 컬럼에 저장되는 데이터 값에 대한 조건을 정의함. 지정된 조건에 맞지 않는 데이터는 저장되지 않음. |
| FOREIGN KEY | 다른 테이블의 기본 키를 참조하는 외래 키를 정의함. 참조 무결성을 유지하고 두 테이블 간의 관계를 구성함. |
| INDEX | 해당 컬럼에 대한 인덱스를 생성함. 데이터 검색 속도를 향상시키기 위해 사용되며, WHERE 절에서 자주 사용되는 컬럼에 적용함. |
| REFERENCES | 외래 키 제약 조건에서 참조하는 테이블과 해당 테이블 간의 관계를 정의함. FOREIGN KEY와 함께 사용됨. |
| COLLATE | 문자열 컬럼의 정렬 순서와 비교 방법을 지정함. 다국어 환경에서 문자열 비교에 사용됨. |
| COMMENT | 컬럼에 대한 설명을 추가함. 테이블 구조를 이해하는 데 도움이 되는 주석을 작성할 수 있음. |
ChatGPT 답변.
- 외래키 제약 조건
형식:FOREIGN KEY (column_name) REFERENCES parent_table(primary_key_column) ON 제약조건
| 용어 | 설명 | 예시 |
|---|---|---|
FOREIGN KEY | 외래키를 정의하는 제약 조건 키워드입니다. | FOREIGN KEY (column_name) REFERENCES parent_table(primary_key_column) |
REFERENCES | 외래키가 참조하는 부모 테이블과 해당 테이블의 기본 키(또는 유니크 키)를 지정합니다. | REFERENCES parent_table(primary_key_column) |
ON DELETE CASCADE | 외래키를 참조하는 레코드가 삭제될 때, 이에 따라 해당 외래키를 참조하는 레코드도 삭제됩니다. | ON DELETE CASCADE |
ON UPDATE CASCADE | 외래키를 참조하는 레코드의 기본 키 값이 변경될 때, 이에 따라 해당 외래키 값을 자동으로 업데이트합니다. | ON UPDATE CASCADE |
ON DELETE SET NULL | 외래키를 참조하는 레코드가 삭제될 때, 해당 외래키 값이 NULL로 설정됩니다. | ON DELETE SET NULL |
ON UPDATE SET NULL | 외래키를 참조하는 레코드의 기본 키 값이 변경될 때, 해당 외래키 값을 NULL로 설정합니다. | ON UPDATE SET NULL |
MATCH FULL | 외래키의 모든 컬럼이 참조 테이블의 기본 키에 대응되어야 합니다. | MATCH FULL |
MATCH PARTIAL | 외래키의 일부 컬럼만 참조 테이블의 기본 키에 대응될 수 있습니다. | MATCH PARTIAL |
MATCH SIMPLE | 외래키의 일부 컬럼만 참조 테이블의 기본 키에 대응될 수 있으며, 일치하지 않는 컬럼은 NULL이어도 됩니다. | MATCH SIMPLE |
02-02_입력 및 조회
INSERT INTO member VALUES("HGD", '홍길동', '서울', '02', '34567890', 180, '2024.04.22');
-- 데이터 입력
INSERT INTO member(mem_id, mem_name, addr, sign_date) VALUES("IGJ", '임꺽정', '경기', '2024.03.27');
-- 특정 필드에만 레코드 입력
INSERT INTO member VALUES
("YJS", "유재석", "서울", '02', '12345678', 230, '2023-08-03'),
("GTH", "김태희", "울산", "052", NULL, 13, '2024.02.15'),
('KGJ', "김국진", "강원", NULL, "0123456", 400, '2023.11.04'),
("GHD", "강호동", "경기", '031', '2345678', 120, '2024.06.23');
-- 한번에 다양한 데이터 입력
INSERT INTO buy(mem_id, category, prod_name, price, amount)
VALUES("HGD", NULL, "지갑", 3000, 2),
("HGD", "전자기기", "랩탑", 600000, 1),
("IGJ", "전자기기", "랩탑", 600000, 3),
("YJS", "전자기기", "휴대폰", 360000, 1),
("KGJ", "책", "에세이", 12000, 1),
("GHD", NULL, '반지', 3200, 3);
UPDATE buy set price=650000 where prod_name="랩탑";
-- 레코드 수정
SELECT * FROM member;
SELECT * FROM buy WHERE category='전자기기' ORDER BY price DESC;
SELECT b.mem_id buy_id, b.price AS each_price FROM buy b; -- as: 별칭 지정. 생략 가능.
SELECT * FROM world.city LIMIT 10 OFFSET 30; -- world 디비의 city 테이블 호출
SELECT * FROM member WHERE points BETWEEN 100 AND 300; -- BETWEEN value1 AND value2: value1 이상 value2 이하
SELECT * FROM member where phone2 like '__345%'; -- LIKE: 조건에 맞는 문자열 반환
select prod_name, count(*) from buy group by prod_name having count(*)<2;
DELETE FROM buy WHERE price<10000; -- DELETE: 데이터 제거. 제거 내역이 로그에 남음.
/* 기본 구조
SELECT select_expr
[FROM table_references]
[WHERE where_condition]
[GROUP BY }col_name | expr| position}]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}]
[limit {[offset,] row_count | row_count OFFSET offset}]
*/- select문의 설명 및 실행 순서(ChatGPT 답변)
-
FROM 절:
FROM절에서는 데이터를 조회할 대상 테이블을 지정합니다.- 이 단계에서는 실제 데이터가 저장된 테이블이 결정됩니다.
-
WHERE 절:
WHERE절에서는 특정 조건을 만족하는 행(row)들을 필터링합니다.WHERE절을 사용하여 조건을 지정하면 해당 조건을 만족하는 행만을 대상으로 조회합니다.
-
GROUP BY 절:
GROUP BY절은 특정 열 기준으로 행들을 그룹화합니다.- 그룹화된 데이터를 기반으로 집계 함수를 사용하여 그룹별로 계산할 수 있습니다.
-
HAVING 절:
HAVING절은GROUP BY절과 함께 사용되며, 그룹화된 결과에 조건을 적용하여 특정 그룹을 필터링합니다.WHERE절은 행을 필터링하는 반면,HAVING절은 그룹을 필터링합니다.
-
SELECT 절:
SELECT절은 실제로 조회할 열(columns)을 지정합니다.- 이 단계에서는 필요한 열을 선택하여 조회 결과를 반환합니다.
-
ORDER BY 절:
ORDER BY절은 조회 결과를 정렬합니다.- 지정된 열을 기준으로 결과를 오름차순(ASC)이나 내림차순(DESC)으로 정렬할 수 있습니다.
-
LIMIT 절:
LIMIT절은 조회 결과의 행 수를 제한합니다.- 주로 특정 범위의 결과만을 가져오거나 페이징 처리를 위해 사용됩니다.
03_서브쿼리
- 서브쿼리 종류 및 설명(ChatGPT 답변)
| 서브쿼리 | 설명 | 예시 |
|---|---|---|
| 일반 서브쿼리 (Subquery) | 주 쿼리 안에 포함된 다른 SQL 문장으로, 서브쿼리의 결과를 사용하여 주 쿼리의 조건을 만족시키거나 비교할 때 사용됨. | SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales') |
| 단일행 서브쿼리 (Single-Row Subquery) | 서브쿼리가 단일 행을 반환하는 경우 사용됨. 주로 = 또는 비교 연산자와 함께 사용하여 단일 값을 비교할 때 활용됨. | SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees) |
| 다중행 서브쿼리 (Multi-Row Subquery) | 서브쿼리가 여러 행을 반환하는 경우 사용됨. 주로 IN, ANY, ALL과 함께 사용하여 여러 값을 비교하거나 필터링할 때 활용됨. | SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York') |
| 다중컬럼 서브쿼리 (Multi-Column Subquery) | 서브쿼리가 여러 컬럼을 반환하는 경우 사용됨. 서브쿼리의 결과를 튜플 형태로 비교하거나 조인할 때 활용됨. | SELECT name, salary FROM employees WHERE (department_id, salary) IN (SELECT id, MAX(salary) FROM departments GROUP BY id) |
| 인라인뷰 (Inline View) | FROM 절 안에서 사용되는 서브쿼리로, 서브쿼리의 결과를 가상 테이블로 취급하여 주 쿼리의 조인이나 필터링에 사용됨. | SELECT * FROM (SELECT * FROM employees WHERE salary > 50000) AS high_salary_employees |
| 스칼라 서브쿼리 (Scalar Subquery) | 서브쿼리가 단일 행, 단일 열의 결과를 반환하는 경우 사용됨. 주로 SELECT 목록이나 WHERE 절에서 사용하여 값을 도출할 때 활용됨. | SELECT name, (SELECT MAX(salary) FROM employees WHERE department_id = d.id) AS max_salary FROM departments d |
04_조인과 집합 연산자
| 유형 | 설명 | 예시 |
|---|---|---|
| INNER JOIN | 두 테이블 간의 일치하는 행만을 반환합니다. 두 테이블 간의 공통된 열을 기준으로 조인됩니다. | SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column |
| LEFT (OUTER) JOIN | 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블과 일치하는 행이 있는 경우 조인됩니다. | SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column |
| RIGHT (OUTER) JOIN | 오른쪽 테이블의 모든 행을 반환하고, 왼쪽 테이블과 일치하는 행이 있는 경우 조인됩니다. | SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column |
| FULL OUTER JOIN | 양쪽 테이블의 모든 행을 반환하고, 서로 일치하는 행이 있는 경우 조인됩니다. | SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column |
| CROSS JOIN | 두 테이블의 데카르트 곱(카티션 곱)을 반환합니다. 즉, 모든 가능한 조합을 생성합니다. | SELECT * FROM table1 CROSS JOIN table2 |
| SELF JOIN (셀프 조인) | 동일한 테이블을 조인하여 자기 자신과 관계를 설정합니다. 일반적으로 별칭(alias)을 사용하여 구현됩니다. | SELECT e1.name, e2.manager_id FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.id |
| UNION | 두 개 이상의 SELECT 문의 결과를 결합하여 중복된 행을 제거하고 하나의 결과 집합을 반환합니다. | SELECT column1 FROM table1 UNION SELECT column2 FROM table2 |
| UNION ALL | UNION과 유사하지만 중복된 행을 제거하지 않고 모든 결과를 포함하여 반환합니다. | SELECT column1 FROM table1 UNION ALL SELECT column2 FROM table2 |
| INTERSECT | 두 개의 SELECT 문의 결과 집합 간의 교집합을 반환합니다. 중복된 행은 자동으로 제거됩니다. | SELECT column1 FROM table1 INTERSECT SELECT column2 FROM table2 |
| EXCEPT (또는 MINUS) | 첫 번째 SELECT 문의 결과에서 두 번째 SELECT 문의 결과를 제외한 차집합을 반환합니다. | SELECT column1 FROM table1 EXCEPT SELECT column2 FROM table2 |
ChatGPT 답변.
참고: MySQL은 FULL OUTER JOIN이 없어 LEFT JOIN과 RIGHT JOIN을 UNION 하면 된다.
05_SQL 프로그래밍
이 장은 전체 Chat GPT의 답변으로 생성되었다.
05-01_변수 생성법
MySQL에서 변수를 생성하고 사용하는 방법은 SET 문을 사용하여 변수를 할당하거나, DECLARE 문을 사용하여 변수를 선언하고 초기화할 수 있습니다.
예시 코드:
-- SET 문을 사용한 변수 할당
SET @my_var = 100;
-- DECLARE 문을 사용한 변수 선언 및 초기화
DECLARE my_variable INT DEFAULT 200;05-02_CASE문
CASE 문은 조건에 따라 다른 결과를 반환하는 제어 구조입니다. 주어진 조건에 따라 서로 다른 표현식을 평가하고 반환할 값을 지정할 수 있습니다.
예시 코드:
SELECT
name,
CASE
WHEN age < 18 THEN '미성년자'
WHEN age BETWEEN 18 AND 65 THEN '성인'
ELSE '노인'
END AS age_group
FROM
users;05-03_WHILE문
WHILE 문은 반복적으로 코드 블록을 실행하는 제어 구조입니다. 주어진 조건이 참인 경우에 코드 블록을 반복적으로 실행합니다.
예시 코드:
DELIMITER //
CREATE PROCEDURE ProcessEmployeesWithBonus()
BEGIN
DECLARE emp_id INT;
DECLARE emp_salary DECIMAL(10, 2);
DECLARE emp_bonus DECIMAL(10, 2);
DECLARE done INT DEFAULT 0;
-- 커서 선언: 급여가 특정 기준 이상인 직원 조회
DECLARE cur_employees CURSOR FOR
SELECT id, salary
FROM employees
WHERE salary >= 50000;
-- 커서 열기
OPEN cur_employees;
-- 커서 루프 시작
employee_loop: WHILE done = 0 DO
-- 커서로부터 다음 직원 정보 읽기
FETCH cur_employees INTO emp_id, emp_salary;
-- 커서로부터 읽은 직원 정보가 없으면 루프 종료
IF emp_id IS NULL THEN
SET done = 1;
LEAVE employee_loop;
END IF;
-- 직원에 대한 보너스 계산
IF emp_salary >= 80000 THEN
SET emp_bonus = emp_salary * 0.1; -- 급여의 10% 보너스 지급
ELSE
SET emp_bonus = emp_salary * 0.05; -- 급여의 5% 보너스 지급
END IF;
-- 보너스 지급 내역 출력
SELECT CONCAT('Employee ID ', emp_id, ' received bonus $', emp_bonus) AS message;
END WHILE; -- 커서 루프 종료
-- 커서 닫기
CLOSE cur_employees;
END //
DELIMITER ;위의 예시 코드는 다음과 같은 작업을 수행하는 ProcessEmployeesWithBonus 프로시저를 정의하고 있습니다:
employees테이블에서 급여가 50,000 이상인 직원들을 대상으로 커서를 엽니다.- 커서 루프를 시작하여 각 직원의 정보를 읽습니다.
- 커서로부터 읽은 직원 정보가 없을 때(
emp_id IS NULL), 루프를 종료하고 커서를 닫습니다. - 각 직원의 급여에 따라 보너스를 계산하고, 보너스 지급 내역을 출력합니다.
IF문과LEAVE문을 사용하여 특정 조건에서 루프를 종료합니다.
05-04_USING문
USING 문은 JOIN 조건에 사용되어 별칭(alias)을 지정할 때 유용합니다. 특히, 동일한 열 이름을 가진 테이블을 조인할 때 충돌을 피하기 위해 사용됩니다.
예시 코드:
SELECT
orders.order_id,
customers.customer_name
FROM
orders
JOIN
customers
USING (customer_id);05-05_PREPARE 및 EXECUTE 문
PREPARE 문은 SQL 문을 동적으로 생성하고 준비할 때 사용되며, EXECUTE 문은 준비된 SQL 문을 실행할 때 사용됩니다. 이러한 문은 동적 쿼리를 생성하고 실행할 때 유용합니다.
예시 코드:
-- PREPARE 문을 사용하여 동적 SQL 문 준비
SET @sql = 'SELECT * FROM employees WHERE employee_id = ?';
PREPARE stmt FROM @sql;
-- EXECUTE 문을 사용하여 준비된 SQL 문 실행
SET @employee_id = 1001;
EXECUTE stmt USING @employee_id;
-- PREPARE 문 해제
DEALLOCATE PREPARE stmt;05-06_PROCEDURE
- 프로시저의 정의와 주요 용처
프로시저(Stored Procedure)는 데이터베이스 내에서 저장된 SQL 코드 블록으로, 이름을 가지고 있고 매개변수를 받아 실행될 수 있는 SQL 코드의 집합입니다. 주요 용도와 장점은 다음과 같습니다:
- 코드의 모듈화: 프로시저를 사용하면 복잡한 SQL 쿼리나 로직을 모듈화하여 간단한 명령으로 호출할 수 있습니다.
- 재사용성: 프로시저를 정의하여 필요할 때마다 호출할 수 있으며, 동일한 로직을 반복해서 사용할 수 있습니다.
- 성능 향상: 프로시저는 데이터베이스 서버에 미리 컴파일되어 저장되므로, 반복적인 작업을 서버 측에서 처리하여 네트워크 부하를 줄이고 성능을 향상시킬 수 있습니다.
- 보안 및 권한 관리: 프로시저는 데이터베이스 권한을 통해 호출 및 실행 권한을 제어할 수 있습니다.
프로시저는 주로 데이터베이스 관리, 데이터 추출, 복잡한 데이터 조작 및 트랜잭션 관리 등의 작업에 사용됩니다. 예를 들어, 특정 조건에 따라 데이터를 처리하거나 특정 작업을 수행하는 데 자주 사용됩니다. 또한 프로시저를 사용하여 응용 프로그램과 데이터베이스 간의 인터페이스를 정의하고 효율적인 데이터 접근 및 조작을 달성할 수 있습니다.
프로시저는 데이터베이스 시스템의 기능을 확장하고 데이터 관리 작업을 효율적으로 처리하기 위한 중요한 도구로서, 데이터베이스 개발 및 운영에서 널리 사용되고 있습니다.
예시 코드:
-- DELIMITER 변경
DELIMITER //
-- 직원 정보 조회 프로시저 정의
CREATE PROCEDURE GetEmployeeInfo(IN emp_id INT)
BEGIN
DECLARE emp_name VARCHAR(255);
DECLARE emp_salary DECIMAL(10, 2);
DECLARE emp_dept_id INT;
-- 직원 정보 조회
SELECT name, salary, department_id INTO emp_name, emp_salary, emp_dept_id
FROM employees
WHERE id = emp_id;
-- 조회된 직원 정보 출력
SELECT CONCAT('Employee Name: ', emp_name) AS info;
SELECT CONCAT('Salary: $', emp_salary) AS info;
SELECT CONCAT('Department ID: ', emp_dept_id) AS info;
END //
-- 부서별 직원 목록 조회 프로시저 정의
CREATE PROCEDURE GetEmployeesByDepartment()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE dept_id INT;
DECLARE dept_name VARCHAR(255);
-- 부서별 직원 목록을 조회하기 위한 커서 선언
DECLARE cur_department CURSOR FOR
SELECT id, name FROM departments;
-- 커서를 열고 반복적으로 부서별 직원 목록을 조회
OPEN cur_department;
department_loop: LOOP
FETCH cur_department INTO dept_id, dept_name;
IF done THEN
LEAVE department_loop;
END IF;
-- 각 부서별 직원 목록 출력
SELECT CONCAT('Department: ', dept_name) AS department_info;
SELECT * FROM employees WHERE department_id = dept_id;
END LOOP;
-- 커서 닫기
CLOSE cur_department;
END //
-- DELIMITER 원래대로 복원
DELIMITER ;위의 예시 코드에서는 두 가지 프로시저를 정의하고 있습니다:
- GetEmployeeInfo: 주어진 직원 ID를 사용하여 해당 직원의 이름, 급여, 부서 ID를 조회하고 출력합니다.
- GetEmployeesByDepartment: 모든 부서를 순회하면서 각 부서별 직원 목록을 조회하고 출력합니다. 이 프로시저는 커서를 사용하여 부서 테이블을 반복적으로 조회하여 부서별 직원 목록을 출력합니다.
