6.1 SELECT문
6.1.1 원하는 데이터를 가져와 주는 기본적인 <SELECT ... FROM>
- SELECT의 구문 형식
select문은 원하는 데이터를 가져오게 할 수 있다.
`user_id`, `name`은 칼럼을 나타낸다. `*`을 사용하면 모든 칼럼을 조회할 수 있다.
`FROM users`는 테이블을 나타낸다.
- USE 구문
6.1.2 특정한 조건의 데이터만 조회하는 <SELECT ... FROM ... WHERE>
- 기본적인 WHERE 절
`WHERE`는 조건을 의미한다. 여기선 `user_id`의 값이 1인 것만 조회한다.
- 관계 연산자의 사용
`OR`를 통해 `user_id`가 1이거나 `name`이 `admin`인 레코드를 조회한다
- BETWEEN ... AND와 IN() 그리고 LIKE
위 예시에선 20과 30 사이의 모든 값을 조회한다.
위 예시에선 1, 5, 7, 10에 속하는 값을 조회한다.
위 예시에선 `J`로 시작하는 문자열을 조회한다
- ANY / ALL / SOME 그리고 서브쿼리(SubQuery, 하위쿼리)
`ANY` 키워드는 적어도 하나가 주어진 비교 조건을 만족할 경우 참을 반환한다. 위 예시에서는 `department_id`가 3인 직원들의 급여보다 더 많은 급여를 받는 모든 직원을 조회하는 예시다.
`ALL` 키워드는 주어진 모든 조건과 일치할 때만 참을 반환한다. 이 쿼리는 `Math` 과목의 모든 시험 점수보다 높은 점수를 받은 학생들을 찾는다. 즉, `Math` 시험의 최고 점수보다도 높은 점수를 받은 학생들만 선택된다.
`SOME` 키워드는 `ANY`와 동일하게 작동하며, 서브 쿼리의 결과 중 적어도 하나가 조건과 일치하면 참을 반환한다. 이 쿼리는 `Math`과목 시험 점수 중 하나 이상과 같거나 그보다 높은 점수를 받은 학생들을 찾는다.
- 원하는 순서대로 정렬하여 출력: ORDER BY
`ORDER BY`를 통해 정렬을 정할 수 있다. 위 커리는 `department` 열에 따라 오름차순으로 결과를 정렬한 다음, 동일한 `department` 값을 가진 행들을 `age` 열의 값에 다라 내림차순으로 다시 정렬한다.
- 중복된 것은 하나만 남기는 DISTINCT
`DISTINCT` 키워드는 중복을 제거한다.
전체 데이터가 이런 경우, `DISTINCT`키워드 없이 `COUNT`를 하면 원하는 결과가 나오지 않는다.
`DISTINCT` 키워드와 함께 사용한 예시를 살펴보자.
우리가 원하는 대로 국가의 개수를 셀 수 있다.
- 출력하는 개수를 제한하는 LIMIT
`LIMIT` 키워드를 통해 특정 수만큼만 결과를 조회할 수 있다.
`LIMIT 3`과 같이 사용하면 첫 번째 행부터 3개의 행을 출력한다.
만약 위와 같이 `LIMIT 1, 3`으로 한다면 1개의 행을 건너뛰고 3개의 행을 출력한다.
- 테이블을 복사하는 CREATE TABLE ... SELECT
해당 구문은 MySQL에서 기존 테이블의 구조와 데이터를 복사하여 새로운 테이블을 생성하는데 사용된다. 이 구문은 선택된 데이터를 바탕으로 새 테이블을 만들 때 유용하며, 원본 테이블의 일부 열만 선택하거나 데이터를 필터링하여 새 테이블에 삽입할 수 있다.
위 예시는 `name`과 `country` 열만을 포함하는 `users_names_countries` 테이블을 생성하는 예시다.
6.1.3 GROUP BY 및 HAVING 그리고 집계 합수
- GROUP BY절
`GROUP BY`절은 SQL 쿼리에서 여러 행들을 그룹화하고, 각 그룹에 대해 집계함수(예: `COUNT`, `MAX`, `SUM`, `AVG` 등)를 적용할 때 사용된다.
- 집계 함수
`COUNT()`, `AVG()`, `MAX()`, `MIN()`이 있다.
count 함수 사용 예시다. 위 쿼리는 각 country의 user 수를 세는 예시다.
위 쿼리는 모든 사용자의 나이 평균 값을 계산한다
위 쿼리는 국가별로 그룹화하여 각 국가에서의 최대 나이와 최소 나이를 구한다
- HAVING 절
`HAVING` 절은 SQL에서 `GROUP BY` 절과 함께 사용되어, 그룹화된 결과에 대한 조건을 지정할 때 사용된다. `WHERE` 절과 유사하게 조건을 지정할 수 있지만, `HAVING`은 그룹화된 결과에 대한 조건 필터링에 사용되며, 주로 집계 함수와 함께 사용된다.
이 쿼리는 country로 그룹화하여 각 국가별 사용자 수를 계산한다. 그리고 `HAVING` 절을 사용하여 그룹화된 결과 중 사용자 수가 2명 초과인 국가만을 결과로 필터링한다.
- ROLLUP 사용 예시
`ROLLUP`은 SQL의 `GROUP BY`절과 함께 사용되어 쿼리 결과에 하위 합계와 총계를 포함할 수 있어, 데이터 분석에서 유용하게 활용된다. 이 기능은 계층적인 데이터 요약 정보를 제공하여, 결과 집합에서 다양한 수준의 집계를 볼 수 있게 한다.
위와 같이 소합계와 총합계를 구할 수 있다. `ROLLUP`을 사용하여 각 제품별 판매량, 각 국가별 모든 제품의 총 판매량, 각 국가별 모든 제품의 총 판매량, 각 연도별 모든 국가와 제품의 총 판매량, 전체 기간 동안 모든 국가와 제품의 총 판매량을 구할 수 있다.
6.1.4 SQL의 분류
SQL은 데이터베이스와 상호작용하기 위해 사용되는 언어로, 크게 DML, DDL, DCL 등 여러 카테고리로 나뉜다. 각각의 카테고리는 데이터베이스 내에서 수행할 수 있는 서로 다른 유형의 작업을 대표한다.
- DML (Data Manipulation Language): DML은 데이터를 조작하고 관리하는 데 사용되는 SQL의 일부이다. 이는 데이터를 조회, 추가, 수정, 삭제하는 등의 작업을 포함한다. `SELECT`, `INSERT`, `UPDATE`, `DELETE` 등이 이에 해당한다.
- DDL(Data Definition Language): DDL은 데이터 구조를 정의하거나 변경하기 위해 사용되는 SQL의 일부이다. 이는 데이터베이스, 테이블, 인덱스 등을 생성, 변경, 삭제하는 작업을 포함한다. `CREATE`, `ALTER`, `DROP`, `TRUNCATE` 등이 이에 해당한다.
- DCL(Data Control Language): DCL은 데이터베이스 시스템에서 데이터 접근 및 보안 권한을 제어하는 데 사용되는 SQL의 일부이다. 이는 사용자에게 데이터에 대한 접근 권한을 부여하거나 취소하는 작업을 포함한다. `GRANT`, `REVOKE`가 이에 해당한다.
6.2 데이터의 변경을 위한 SQL문
6.2.1 데이터의 삽입: INSERT
- INSERT문 기본
`table_name`은 데이터를 삽입할 테이블의 이름이다
`(column1, column2, column3, ...)`은 데이터를 삽입할 테이블의 열 이름이다. 이 부분은 선택 사항이며, 모든 열에 데이터를 삽입하는 경우 생략할 수 있다.
`VALUES (value1, value2, value3, ...)`은 각 열에 삽입할 데이터 값이다. 열의 순서와 데이터 값의 순서는 일치해야 한다.
위와 같은 방법으로 다중행을 입력할 수도 있다.
- 자동으로 증가하는 AUTO_INCREMENT
`AUTO_INCREMENT` 속성은 데이터베이스에서 테이블의 열 값이 자동으로 증가하는 기능이다.
- `AUTO_INCREMENT`를 사용하는 열은 기본키 또는 유니크 키로 설정되어 있어야한다
- `AUTO_INCREMENT`를 사용하는 열은 숫자형(예: INT, BIGINT) 데이터 타입이어야 한다
- `AUTO_INCREMENT`가 설정된 열에 대해서는 값을 명시적으로 제공하지 않아도 된다. NULL 값이 입력된다면 값을 자동으로 지정해줄 것이다
- 아래 명령어로 `AUTO_INCREMENT` 값을 초기화하거나 변경할 수 있다.
- 아래 명령어로 `AUTO_INCREMENT` 값의 증가값을 설정할 수 있다
- 아래 명령어로 어느 숫자까지 증가되었는지 확인할 수도 있다
- 대량의 샘플 데이터 생성
위와 같은 방법으로 대량의 데이터를 한 위치에서 다른 위치로 효율적으로 이동시킬 수 있다.
6.2.2 데이터의 수정: UPDATE
`table_name`은 수정할 데이터가 있는 테이블의 이름이다
`SET column1 = value1, column2 = value2 ...`는 업데이트할 열과 그에 해당하는 새로운 값이다. 하나 이상의 열을 업데이트할 수 있다.
`WHERE condition`은 업데이트할 레코드를 지정하는 조건이다. 이 조건에 맞는 레코드만 업데이트가 된다.
6.2.3 데이터의 삭제: DELETE FROM
`DELETE FROM` 문은 SQL에서 테이블의 레코드를 삭제하기 위해 사용되는 명령어다.
`table_name`은 데이터를 삭제할 테이블의 이름이다
`WHERE condition`은 삭제할 레코드를 지정하는 조건이다
6.2.4 조건부 데이터 입력, 변경
- INSERT IGNORE INTO: 새로운 레코드를 삽입할 때 기존의 중복 키와 충돌이 발생하는 경우, 해당 삽입 작업을 무시하고 오류를 발생시키지 않는다. 대신, 삽입을 시도한 레코드는 무시되고, 프로그램은 다음 작업을 계속 진행한다.
만약 `id`가 1인 레코드가 이미 `users` 테이블에 존재한다면, 새로운 삽입 시도는 무시된다.
- ON DUPLICATE KEY UPDATE: 새로운 레코드를 삽입할 때 기존의 중복 키와 충돌이 발생하는 경우, 지정된 열을 새 값으로 업데이트한다. 이 방법은 중복 데이터를 관리하고, 기존 레코드의 값을 최신 상태로 유지하고자 할 때 유용하다.
만약 `id`가 1인 레코드가 이미 존재한다면, `name`과 `email` 열이 새로운 값으로 업데이트 된다. 이는 기존 레코드를 유지하면서 중복 키 문제를 해결하고 데이터를 최신 상태로 갱신할 수 있다.
6.3 WITH 절과 CTE
6.3.1 WITH절과 CTE 개요
`WITH`절과 `CTE(공통 테이블 표현식, Common Table Expressions)`은 SQL 쿼리를 더 읽기 쉽고 유지 관리하기 쉽게 만들기 위해 사용되는 강력한 기능이다. CTE는 비재귀적 CTE와 재귀적 CTE로 나눌 수 있다. 여기서는 비재귀적 CTE에 대해서만 학습하고 넘어가겠다.
6.3.2 비재귀적 CTE
`CTE_Name`은 CTE에 주어진 이름이다. 이 이름을 사용하여 쿼리 내 다른 부분에서 CTE를 참조할 수 있다.
`(column1, column2 ...)`은 CTE 결과 집합의 열 이름을 선택적으로 지정한다. 이 부분은 생략 가능하며, 생략 시 `SELECT` 문의 열 이름이 사용된다.
`SELECT column1, column2, ... FROM table WHERE condition`은 CTE의 본문으로, 실제 데이터를 선택하는 쿼리다.
이 예시에서 `RegionSales` CTE는 각 지역별 총 판매액을 계산하고, 최종 `SELECT`문은 이 중에서 총 판매액이 1000을 초과하는 지역만을 선택하여 결과를 반환한다.
- CTE와 뷰의 주요 차이점: CTE는 뷰와 용도가 비슷하지만 차이점이 존재한다.
- 수명: CTE는 쿼리 실행 시에만 존재하는 반면, 뷰는 데이터베이스에 지속적으로 존재한다
- 정의 위치: CTE는 쿼리 내부에 정의되어 해당 쿼리에서만 사용할 수 있지만, 뷰는 데이터베이스에 객체로 저장되어 여러 쿼리나 애플리케이션에서 재사용될 수 있다
- 용도: CTE는 주로 복잡한 쿼리를 단순화하고 재귀적 쿼리를 지원하는 데 사용되며, 뷰는 데이터 추상화, 쿼리의 재사용성을 목적으로 한다.
'개발' 카테고리의 다른 글
| [MySQL] 챕터 8. 테이블과 뷰 (0) | 2024.03.10 |
|---|---|
| [MySQL] 챕터 7. SQL 고급 (3) | 2024.03.08 |
| [MySQL] 챕터 5. MySQL 유틸리티 사용법 (0) | 2024.02.29 |
| [MySQL] 챕터 4. 데이터베이스 모델링 (0) | 2024.02.29 |
| [MySQL] 챕터 3. MySQL 전체 운영 실습 (1) | 2024.02.28 |
