[Sql] Chapter 04. SQL 고급 문법

혼공학습단/SQL

[Sql] Chapter 04. SQL 고급 문법

노 코딩 노 라이프 2023. 7. 21. 00:19

4장 : SQL 고급 문법

4-1 MySQL의 데이터 형식

1. 데이터 형식

1-1. 숫자 데이터 형식

INT(INTEGER): 정수형 데이터를 저장하는 데 사용됩니다. MySQL에서는 다양한 크기의 INT 형식을 제공합니다. 기본적으로 INT는 4바이트를 차지하며, 부호 있는 정수(-2,147,483,648부터 2,147,483,647)를 표현할 수 있습니다. 그러나 다음과 같은 다른 크기의 정수 데이터 형식도 있습니다:
TINYINT: 1바이트 크기로 작은 정수(-128부터 127)를 표현합니다.
SMALLINT: 2바이트 크기로 중간 정수(-32,768부터 32,767)를 표현합니다.
MEDIUMINT: 3바이트 크기로 중간 크기의 정수(-8,388,608부터 8,388,607)를 표현합니다.
BIGINT: 8바이트 크기로 큰 정수(-9,223,372,036,854,775,808부터 9,223,372,036,854,775,807)를 표현합니다.
FLOAT: 부동 소수점 수를 저장하는 데 사용됩니다. MySQL에서는 단정밀도 부동 소수점 수를 저장하는 FLOAT 데이터 형식을 지원합니다. FLOAT는 4바이트 크기이며, 대략 7자리의 유효 숫자를 저장할 수 있습니다.
DOUBLE: 배정밀도 부동 소수점 수를 저장하는 데 사용됩니다. DOUBLE은 8바이트 크기이며, 대략 15자리의 유효 숫자를 저장할 수 있습니다.

1-2. 문자열 데이터 형식

CHAR: 고정 길이의 문자열을 저장하는 데 사용됩니다. 지정된 길이만큼 공간을 사용하므로 CHAR 데이터 형식은 VARCHAR보다 더 빠릅니다. 그러나 저장하는 문자열의 길이가 일정하고 미리 알려진 경우에만 권장됩니다.
VARCHAR: 가변 길이의 문자열을 저장하는 데 사용됩니다. VARCHAR은 실제 문자열의 길이에 따라 필요한 만큼만 공간을 사용합니다. 문자열의 길이가 다양하거나 미리 알 수 없는 경우에 유용합니다.
TEXT: 매우 긴 가변 길이의 문자열을 저장하는 데 사용됩니다. VARCHAR와 비슷하지만 더 큰 용량을 저장할 수 있습니다. TEXT는 작성, 읽기 및 갱신 작업에 적합한 데이터 형식입니다.

1-3. 날짜와 시간 데이터 형식

DATE: 날짜(년-월-일)를 저장하는 데 사용됩니다. 'YYYY-MM-DD' 형식으로 값을 저장합니다.
TIME: 시간(시:분:초)을 저장하는 데 사용됩니다. 'HH:MM:SS' 형식으로 값을 저장합니다.
DATETIME: 날짜와 시간을 함께 저장하는 데 사용됩니다. 'YYYY-MM-DD HH:MM:SS' 형식으로 값을 저장합니다.
TIMESTAMP: UNIX 타임스탬프 형식으로 날짜와 시간을 저장하는 데 사용됩니다. 1970년 1월 1일 이후 경과한 초 단위의 값을 저장합니다.

1-4. 불리언 데이터 형식

BOOLEAN, BOOL: 논리값(true 또는 false)을 저장하는 데 사용됩니다. MySQL은 BOOL 형식을 내부적으로 TINYINT(1)로 처리하며, 1 또는 0 값을 저장합니다.

1-5. 이진 데이터 형식

BLOB: 이진 데이터(이미지, 동영상 등)를 저장하는 데 사용됩니다. BLOB는 대용량 이진 데이터를 저장할 수 있는 데이터 형식입니다.

2. 변수의 사용

MySQL에서는 사용자 정의 변수(User-Defined Variables)를 선언하여 값을 할당하고, 이 값을 쿼리에서 사용할 수 있습니다. 이러한 변수를 사용하면 중간 결과를 임시로 저장하거나 쿼리에서 값을 계산하거나 조작하는 데 유용합니다. 변수는 '@' 기호를 접두사로 사용하여 선언하고 사용합니다.

다음은 MySQL에서 변수를 사용하는 기본적인 예시입니다

변수 선언 및 값 할당

-- 변수 선언 및 값 할당
SET @variable_name = value;

변수 사용

-- 변수 사용 예시
SELECT @variable_name;

변수에 쿼리 결과 할당

-- 변수에 쿼리 결과 할당 예시
SELECT column_name INTO @variable_name FROM table_name WHERE condition;

변수로 계산

-- 변수로 계산 예시
SET @num1 = 10;
SET @num2 = 5;
SET @sum = @num1 + @num2;
SELECT @sum;

변수의 값 갱신

-- 변수 값 갱신 예시
SET @counter = 0;
UPDATE table_name SET column_name = @counter := @counter + 1;

변수의 범위
MySQL 변수는 세션(Session) 범위로 유지됩니다. 즉, 세션이 종료되면 변수도 사라집니다. 따라서 다른 세션에서는 같은 변수 이름을 사용하여 접근할 수 없습니다.

변수의 유효성
MySQL 변수는 쿼리 실행 중에 유효합니다. 즉, 변수가 선언된 쿼리의 스코프 내에서만 사용할 수 있습니다.

주의할 점은 MySQL에서 변수를 사용할 때 다음과 같은 제약사항이 있습니다

변수 이름은 '@' 기호로 시작해야 합니다.
변수 이름은 숫자로 시작할 수 없으며, 문자, 숫자, 밑줄('_')을 사용하여 구성할 수 있습니다.
대소문자를 구별합니다. '@variable'과 '@Variable'은 서로 다른 변수입니다.
변수를 사용하면 쿼리의 유연성과 가독성을 향상시킬 수 있으며, 중간 결과를 임시로 저장하거나 값을 계산하는 등의 다양한 용도로 활용할 수 있습니다. 그러나 변수를 사용할 때 적절한 범위와 사용 패턴을 고려하여 의도치 않은 결과가 발생하지 않도록 주의해야 합니다.

3. 데이터 형 변환

MySQL에서는 데이터 형변환(Casting)을 수행하여 데이터를 다른 형식으로 변환할 수 있습니다.
데이터 형변환은 주로 쿼리에서 데이터를 처리하거나 함수 또는 연산에 사용될 때 유용합니다. MySQL에서는 몇 가지 방법으로 데이터 형변환을 할 수 있습니다.

3-1. IMPLICIT 형변환

MySQL은 산술 연산이나 비교 연산 시에 데이터 형변환을 암묵적으로 수행합니다. 예를 들어, 서로 다른 데이터 형식의 숫자를 더하거나 비교하면 MySQL이 자동으로 형변환을 수행하여 일치하는 데이터 형식으로 변환합니다.

3-2. EXPLICIT 형변환 (CAST 함수 사용)
CAST 함수를 사용하여 명시적으로 데이터를 원하는 형식으로 변환할 수 있습니다.

SELECT CAST(column_name AS data_type) FROM table_name;

예를 들어, 숫자를 문자열로 변환하려면 다음과 같이 사용할 수 있습니다

SELECT CAST(123 AS CHAR);

3-3. EXPLICIT 형변환 (CONVERT 함수 사용)
CONVERT 함수를 사용하여 데이터를 명시적으로 원하는 형식으로 변환할 수도 있습니다.

SELECT CONVERT(column_name, data_type) FROM table_name;

CAST와 마찬가지로 데이터를 원하는 형식으로 변환할 수 있습니다

SELECT CONVERT(123, CHAR);

MySQL에서는 여러 데이터 형식 사이에서 형변환을 수행할 수 있으며, 대부분의 기본 데이터 형식 간에는 비교적 간단하게 형변환이 이루어집니다. 그러나 데이터 형변환을 사용할 때 주의해야 할 점은 데이터의 손실이나 잘못된 결과가 발생할 수 있으므로, 데이터 형변환을 수행하기 전에 형변환 결과를 신중히 검토해야 합니다. 데이터 형변환이 불필요한 경우, MySQL이 암묵적으로 형변환을 수행하여 코드를 단순화할 수 있습니다. 하지만 필요한 경우에는 명시적으로 CAST 또는 CONVERT 함수를 사용하여 데이터 형변환을 수행하면 됩니다.

4-2 두 테이블을 묶는 조건

1. 내부 조인

내부 조인은 두 테이블 사이에서 일치하는 값을 가진 행만을 결과로 반환하는 조인 유형입니다. INNER JOIN 키워드를 사용하여 수행됩니다. 내부 조인은 두 테이블에서 일치하는 값을 가진 행들만을 선택하며, 일치하지 않는 행은 결과에 포함되지 않습니다.

SELECT column1, column2
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

2. 외부 조인

외부 조인은 두 테이블 사이에서 일치하지 않는 값을 가진 행들도 결과로 반환하는 조인 유형입니다. LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN 세 가지 유형의 외부 조인이 있습니다.

LEFT JOIN: 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 결과로 반환합니다. 일치하지 않는 경우 오른쪽 테이블의 값은 NULL로 표시됩니다.
RIGHT JOIN: 오른쪽 테이블의 모든 행과 왼쪽 테이블에서 일치하는 행을 결과로 반환합니다. 일치하지 않는 경우 왼쪽 테이블의 값은 NULL로 표시됩니다.
FULL OUTER JOIN: 왼쪽과 오른쪽 테이블의 모든 행과 일치하는 행을 결과로 반환합니다. 일치하지 않는 경우 해당 테이블의 값은 NULL로 표시됩니다.

-- LEFT JOIN 예시
SELECT column1, column2
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

-- RIGHT JOIN 예시
SELECT column1, column2
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

-- FULL OUTER JOIN 예시
SELECT column1, column2
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

3. 기타 조인

Cross Join: Cross Join은 두 테이블의 모든 가능한 조합을 반환합니다. 행 수가 m과 n인 두 테이블을 Cross Join하면 결과는 m x n 행으로 생성됩니다. 일반적으로 조인 조건을 명시하지 않아도 수행됩니다.
Self Join: Self Join은 테이블 자체를 자기 자신과 조인하는 것입니다. 일반적으로 테이블에 대해 별칭(Alias)을 사용하여 구현합니다. 이를 통해 하나의 테이블에서 여러 행 간의 관계를 탐색할 수 있습니다.

-- Cross Join 예시
SELECT column1, column2
FROM table1
CROSS JOIN table2;

-- Self Join 예시
SELECT e.employee_name, m.manager_name
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;

4-3 SQL 프로그래밍

1. IF문

IF 문은 조건에 따라 두 가지 중 하나의 결과를 반환하는 구문입니다.
MySQL에서 IF 문은 IF 함수 형태로 사용될 수도 있으며, 다음과 같은 형식으로 사용됩니다

IF(condition, value_if_true, value_if_false)

condition은 평가할 조건을 나타내며, 해당 조건이 참이면 value_if_true가 반환되고, 조건이 거짓이면 value_if_false가 반환됩니다.

예시코드

SELECT name, age, IF(age >= 18, 'Adult', 'Minor') AS age_group
FROM persons;

2. CASE문

CASE 문은 조건에 따라 여러 가지 경우를 처리하는 구문입니다. MySQL에서는 두 가지 형태의 CASE 문이 있습니다.

단순 CASE 문: 단순 CASE 문은 특정 열과 일치하는 경우에 해당하는 결과를 반환합니다.

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE default_result
END

검색 CASE 문: 검색 CASE 문은 여러 조건을 평가하고 해당하는 경우에 해당하는 결과를 반환합니다.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE default_result
END

예시코드

SELECT name,
       CASE
         WHEN age < 18 THEN 'Minor'
         WHEN age >= 18 AND age < 65 THEN 'Adult'
         ELSE 'Senior'
       END AS age_group
FROM persons;

3. WHILE문

MySQL에서 WHILE 문은 프로시저나 함수 내에서 사용되는 반복문입니다.
조건이 참인 동안 지정된 블록을 반복하여 실행합니다.

예시코드

DELIMITER //
CREATE PROCEDURE example_while_loop()
BEGIN
  DECLARE counter INT DEFAULT 1;

  WHILE counter <= 10 DO
    -- 실행할 코드 작성
    -- 예시: 특정 테이블에 데이터 삽입
    INSERT INTO my_table (column1, column2) VALUES (counter, 'Data ' + counter);
    SET counter = counter + 1;
  END WHILE;
END //
DELIMITER ;

4. 동적SQL

동적 SQL은 실행 시점에 쿼리의 일부를 동적으로 생성하는 기술입니다.
즉, 실행 중에 쿼리에 포함할 조건이나 테이블 이름 등을 동적으로 생성하여 쿼리를 실행합니다.
이는 정적 쿼리로는 표현하기 어려운 동적인 조건을 처리하는 데 유용합니다.

MySQL에서는 PREPARE 문과 EXECUTE 문을 사용하여 동적 SQL을 실행할 수 있습니다.

예시코드

SET @column_name = 'age';
SET @condition = '>= 18';
SET @query = CONCAT('SELECT name FROM persons WHERE ', @column_name, ' ', @condition);

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

동적 SQL은 조건에 따라 쿼리를 유연하게 구성할 수 있지만, 보안 상의 이유로 사용자 입력을 직접 쿼리에 포함시키지 않도록 주의해야 합니다. SQL 삽입 공격을 방지하기 위해 적절한 검증 및 제한을 적용해야 합니다. 동적 SQL 사용 시에는 보안 측면에서 주의를 기울이는 것이 중요합니다!

저작자표시