Information Security Study
240216 데이터베이스(WHERE, IN, LIKE, BETWEEN, NULL, CHAR와 VARCHAR의 차이, ACID, TCL, 문자/숫자/날짜/변환/NULL 함수) 본문
네트워크 캠퍼스/Database
240216 데이터베이스(WHERE, IN, LIKE, BETWEEN, NULL, CHAR와 VARCHAR의 차이, ACID, TCL, 문자/숫자/날짜/변환/NULL 함수)
gayeon_ 2024. 2. 16. 16:04WHERE절
조건절 WHERE
SQL WHERE절
: 데이터베이스 테이블에서 특정 조건에 맞는 행(row)만 선택하여 조회할 때 사용한다.
- WHERE절은 SELECT, UPDATE, DELETE 등 다양한 SQL문에서 사용된다.
- 데이터베이스에서 원하는 데이터만 검색하여 추출할 수 있다.
- 데이터베이스의 부하를 줄일 수 있다.
WHERE절의 구조
SELECT column1, column2, ...
FROM table_name
WHERE condition;
- 위 구조에서 WHERE절은 WHERE condition으로 이루어져 있다.
- condition: 조건을 나타내는 표현식(expression).
- condition은 비교 연산자(<, >, =, <=, >=, <>)나 논리 연산자(AND, OR, NOT)를 사용하여 구성할 수 있다.
ex) employees 테이블에서 연봉이 5000 이상인 직원들만 선택
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary >= 5000;
WHERE를 데이터 갱신에 사용한 예시
ex) employees 테이블에서 job_id가 IT_PROG인 직원들의 salary 값을 10% 증가
UPDATE employees
SET salary = salary * 1.1
WHERE job_id = 'IT_PROG';
- WHERE job_id = 'IT_PROG' 조건절은 employees 테이블에서 job_id 열의 값이 IT_PROG인 행(row)만 선택하여 salary 값을 10% 증가시킨다.
연산자
WHERE절에서 사용하는 연산자
- 비교 연산자
- 논리 연산자
- IN 연산자
- LIKE 연산자 BETWEEN 연산자
비교 연산자
- =: 값이 같다.
- <> 또는 !=: 값이 다르다.
- <: 값이 작다.
- >: 값이 크다.
- <=: 값이 작거나 같다.
- >=: 값이 크거나 같다.
논리 연산자
- AND: 두 개의 조건이 모두 참일 때 참이다.
- OR: 두 개의 조건 중 하나 이상이 참일 때 참이다.
- NOT: 조건의 결과를 부정한다.
IN 연산자
: 여러 개의 값을 비교한다.
ex) employees 테이블에서 job_id가 IT_PROG 또는 SA_REP인 직원들을 선택
- SELECT employee_id, first_name, last_name, job_id FROM employees WHERE job_id IN ('IT_PROG', 'SA_REP');
- SELECT column1, column2, ... FROM table_name WHERE column_name IN (value1, value2, ...);
SELECT * FROM user_tbl;
SELECT * FROM user_tbl WHERE user_address='경기' OR user_address='대구';
-- IN 문법으로 위 쿼리보다 간결하게 조회하기
SELECT * FROM user_tbl WHERE user_address IN ('경기', '대구');
SELECT * FROM buy_tbl;
-- IN 문법으로 구매내역이 있는 유저만 출력
SELECT * FROM user_tbl WHERE user_num IN (SELECT DISTINCT user_num FROM buy_tbl);
LIKE 연산자
: 문자열 패턴 검색
- %: 0개 이상의 문자와 일치
- _: 한 개의 문자와 일치
ex) employees 테이블에서 last_name 열의 값이 S%로 시작하는 직원들을 선택
- SELECT employee_id, first_name, last_name FROM employees WHERE last_name LIKE 'S%';
- SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
-- LIKE는 패턴 일치 여부를 통해 조회한다.
-- %: 여러글자 매칭
-- _: 1글자 매칭
SELECT * FROM user_tbl WHERE user_name LIKE '%이';
BETWEEN 연산자
: 범위 지정
ex) employees 테이블에서 salary 열의 값이 5000 이상 8000 이하 직원들을 선택
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 5000 AND 8000;
IS NULL 연산자
: 해당 열(column)의 값이 NULL인 행(row)을 선택
ex) employees 테이블에서 commission_pct 열의 값이 NULL인 직원들을 선택
SELECT employee_id, first_name, last_name
FROM employees
WHERE commission_pct IS NULL;
null
: SQL에서 NULL은 아무런 값도 가지지 않는 상태
문자열타입 CHAR와 VARCHAR의 비교
CHAR 타입과 VARCHAR 타입은 문자열 데이터를 저장하는 데 사용한다.
이 두 타입은 서로 다른 방식으로 문자열 데이터를 저장하고 처리한다.
CHAR 타입
- 고정 길이 문자열을 저장
- 열(column)에 저장된 문자열은 정해진 길이를 갖는다
- 고정 길이보다 작은 문자열은 나머지 공간이 공백으로 채워진다
ex) 다음과 같이 employees 테이블에서 first_name 열을 CHAR(10) 타입으로 정의
CREATE TABLE employees (
employee_id INT(6) PRIMARY KEY,
first_name CHAR(10),
last_name VARCHAR(20)
);
first_name 열의 길이는 10
first_name 열에 'John'이라는 문자열을 저장하면 'John '(10자리)으로 저장된다.
CHAR 타입 단점
- 이렇게 공백으로 채워진 문자열은 데이터베이스에 저장될 때 공간을 낭비하게 되는 단점이 있다.
CHAR 타입 장점
- 고정 길이이므로 검색 속도가 빠르다.
- 데이터 저장 시 공간이 확보되므로 데이터가 수정되는 경우 발생하는 오버헤드가 적다.
VARCHAR 타입
- 가변 길이 문자열을 저장
- 열에 저장된 문자열은 실제로 사용한 만큼의 공간만 차지
- 데이터 저장 공간을 절약할 수 있다
ex) employees 테이블에서 last_name 열을 VARCHAR(20) 타입으로 정의한 경우
CREATE TABLE employees (
employee_id INT(6) PRIMARY KEY,
first_name CHAR(10),
last_name VARCHAR(20)
);
- last_name 열의 길이는 20으로 정의
- last_name 열에 'Smith' 문자열을 저장하면 'Smith'(5자리)으로 저장된다.
- 저장된 문자열은 실제로 사용한 만큼의 공간만 차지하므로 데이터 저장 공간을 절약할 수 있다.
트랜잭션의 개념과 ACID
데이터베이스 트랜잭션(Transaction)
: 하나 이상의 데이터베이스 연산이 묶여서 하나의 논리적인 작업 단위로 처리되는 것
트랜잭션은 일련의 연산 중 하나라도 실패하면 전체 연산이 취소되는 원자성(Atomicity)을 갖는다.
트랜잭션의 특성 ACID
- 원자성(Atomicity): 트랜잭션은 일련의 연산 중 하나라도 실패하면 전체 연산이 취소된다.
- 일관성(Consistency): 트랜잭션이 완료되면 데이터베이스의 일관성이 유지된다.
- 격리성(Isolation): 여러 개의 트랜잭션이 동시에 수행될 때 각각의 트랜잭션이 서로에게 영향을 미치지 않는다.
- 지속성(Durability): 트랜잭션이 완료되면 그 결과가 영구적으로 반영된다.
ACID
: 데이터베이스에서 원자성, 일관성, 격리성, 지속성을 보장하기 위한 기본적인 트랜잭션 처리 원칙을 나타내는 약어
MySQL의 TCL
MySQL에서의 TCL(Transction Control Language)
: 트랜잭션을 제어하기 위한 명령어
1) COMMIT
- 현재까지 수행한 트랜잭션을 커밋한다.
- 커밋을 실행하면 변경 내용이 영구적으로 데이터베이스에 적용된다.
2) ROLLBACK
- 현재까지 수행한 트랜잭션을 롤백한다.
- 롤백을 실행하면 변경 내용이 모두 취소되고 이전 상태로 되돌아간다.
3) SAVEPOINT
- 트랜잭션 내에서 저장점을 설정한다.
- 저장점을 설정하면 트랜잭션 중에 롤백할 때 저장점 이후의 변경 내용만 취소할 수 있다.
4) ROLLBACK TO SAVEPOINT
- 설정한 저장점으로 롤백한다.
- 저장점 이후에 수행한 변경 내용만 취소된다.
MySQL에서 TCL을 사용하는 예시 코드
-- 트랜잭션 시작
START TRANSACTION;
-- Customers 테이블에 새로운 데이터 삽입
INSERT INTO Customers (customer_id, customer_name, city)
VALUES (1, 'John', 'New York');
-- Orders 테이블에 새로운 데이터 삽입
INSERT INTO Orders (order_id, customer_id, order_date, total_amount)
VALUES (1, 1, '2023-05-11', 100.00);
-- 만약 데이터 삽입 중 에러가 발생하면 롤백
ROLLBACK;
-- 에러가 발생하지 않으면 커밋
COMMIT;
트랜잭션 실습
-- 트랜잭션은 2개 이상의 각종 쿼리문의 실행을 되돌리거나 영구적으로 반영할 때 사용한다.
-- 연습 테이블 생성
CREATE TABLE bank_account(
act_num INT(5) PRIMARY KEY AUTO_INCREMENT,
act_owner VARCHAR(10) NOT NULL,
balance INT(10) DEFAULT 0 NOT NULL
);
-- 계좌 데이터 2개 삽입
INSERT INTO bank_account VALUES(null, '나구매', 50000);
INSERT INTO bank_account VALUES(null, '가판매', 0);
SELECT * FROM bank_account;
-- 트랜잭션 시작(ROLLBACK; 수행 시 이 지점 이후의 내용을 전부 취소한다.)
START TRANSACTION;
-- 나구매의 돈 30000원 차감
UPDATE bank_account SET balance = (balance-30000) WHERE act_num=1;
-- 가판매 돈 30000원 증가
UPDATE bank_account SET balance = (balance+30000) WHERE act_num=2;
-- 이체내역 초기화
ROLLBACK;
-- TRANSACTION 이전의 상황으로 돌아온다.
SELECT * FROM bank_account;
-- 25000원으로 차감 및 증가하는 쿼리문 작성 + 커밋
START TRANSACTION;
-- 나구매의 돈 25000원 차감
UPDATE bank_account SET balance = (balance-25000) WHERE act_num=1;
-- 가판매 돈 25000원 증가
UPDATE bank_account SET balance = (balance+25000) WHERE act_num=2;
SELECT * FROM bank_account;
-- 커밋 수행
COMMIT;
SELECT * FROM bank_account;
-- 커밋 이후 롤백 (커밋 직후라 돌아갈 지점이 없어져서 복구되지 않는다.)
ROLLBACK;
SELECT * FROM bank_account;
-- SAVEPOINT는 ROLLBACK 해당 지점 전까지는 반영, 해당 지점 이후는 반영 안하는 경우 사용한다.
START TRANSACTION;
-- 나구매의 돈 3000원 차감
UPDATE bank_account SET balance = (balance-3000) WHERE act_num=1;
-- 가판매 돈 3000원 증가
UPDATE bank_account SET balance = (balance+3000) WHERE act_num=2;
-- first_tx 저장 지점 생성, 1번 저장 지점
SAVEPOINT first_tx;
SELECT * FROM bank_account;
-- 나구매의 돈 5000원 차감
UPDATE bank_account SET balance = (balance-5000) WHERE act_num=1;
-- 가판매 돈 5000원 증가
UPDATE bank_account SET balance = (balance+5000) WHERE act_num=2;
-- ROLLBACK;만 호출하면 0번 지점으로 돌아간다.
-- ROLLBACK TO 트랜잭션지점명; 호출 시 해당 트랜잭션 지점으로 돌아간다.
ROLLBACK TO first_tx;
SELECT * FROM bank_account;
ROLLBACK;
FUNCTION
단일행 함수
단일행 함수(Single-row functions)
- 하나의 입력 행(row)에 대해 하나의 결과 값을 반환하는 함수이다.
- 행의 각 값에 대해 연산을 수행하고 결과 값을 반환한다.
- 문자 함수(Character functions): 문자열을 조작하거나 정보를 반환
- 숫자 함수(Numeric functions): 숫자 값을 조작하거나 숫자 정보를 반환
- 날짜 함수(Date functions): 날짜 값을 조작하거나 날짜 정보를 반환
- 변환 함수(Conversion functions): 데이터 유형을 변환하거나 다른 형식으로 표현하는 데 사용
- 일반 함수(General functions): 문자, 숫자 및 날짜 값을 처리
| 분류 | 예시 함수 |
| 문자 함수 | UPPER, LOWER, INITCAP, CONCAT, SUBSTR, LENGTH, REPLACE, TRIM, LPAD, RPAD, INSTR, REGEXP_* |
| 숫자 함수 | ROUND, TRUNC, MOD, CEIL, FLOOR, POWER, ABS, SIGN, SQRT, LN, LOG, EXP, SIN, COS, TAN |
| 날짜 함수 | ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN, NEW_TIME, SYSTIMESTAMP, TRUNC, ROUND |
| 변환 함수 | TO_CHAR, TO_NUMBER, TO_DATE, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_YMINTERVAL, TO_DSINTERVAL |
| 일반 함수 | NVL, NULLIF, COALESCE, DECODE, CASE, GREATEST, LEAST, TRANSLATE |
문자 함수(Character Functions)
문자 함수
- 문자열을 조작하거나 정보를 반환하는 데 사용되는 함수이다.
- 문자열의 길이를 변경하거나 문자열의 일부를 추출하거나 대소문자를 변환할 수 있다.
주요 문자 함수
- UPPER(string) - 문자열의 모든 문자를 대문자로 변환
- LOWER(string) - 문자열의 모든 문자를 소문자로 변환
- INITCAP(string) - 문자열의 첫 글자를 대문자로, 나머지 글자는 소문자로 변환
- CONCAT(string1, string2) - 두 개의 문자열을 연결
- SUBSTR(string, start_position, [length]) - 문자열에서 지정된 위치에서 시작하여 특정 길이만큼의 부분 문자열을 반환
- LENGTH(string) - 문자열의 길이를 반환
- REPLACE(string, search_string, replace_string) - 문자열에서 특정 문자열을 찾아 다른 문자열로 교체
- TRIM([trim_character FROM] string) - 문자열의 앞뒤에서 지정된 문자(또는 공백)를 제거
- LPAD(string, length, [pad_string]) - 문자열의 왼쪽에 특정 문자열을 채워 지정된 길이를 만족
- RPAD(string, length, [pad_string]) - 문자열의 오른쪽에 특정 문자열을 채워 지정된 길이를 만족
- INSTR(string, search_string, [start_position], [nth_occurrence]) - 문자열에서 특정 문자열이 나타나는 위치를 반환
문자 함수 실습
-- 영어 이름 사용자 추가
SELECT * FROM user_tbl;
INSERT INTO user_tbl VALUES
(null, 'alex', 1986, 'NY', 180, '2024-11-01'),
(null, 'Smith', 1992, 'Texas', 171, '2024-11-05'),
(null, 'Emma', 1983, 'Tampa', 158, '2024-10-11'),
(null, 'JANE', 1997, 'LA', 160, '2024-8-05');
-- 문자열 함수로 하나의 컬럼을 여러 형식으로 조회
SELECT
user_name,
UPPER(user_name) AS 대문자유저명,
LOWER(user_name) AS 소문자유저명,
LENGTH(user_name) AS 문자길이,
SUBSTR(user_name, 1 ,2) AS 첫2글자,
CONCAT(user_name, '회원이 존재합니다.') AS 회원목록
FROM user_tbl;
-- 이름이 4글자 이상인 유저만 출력
-- LENGTH()는 byte길이로 글자 수를 산정하므로 한글은 한 글자에 3바이트로 간주
-- 따라서 LENGTH() 대신 CHAR_LENGTH()를 사용하면 언어 상관 없이 글자를 센다.
SELECT * FROM user_tbl WHERE CHAR_LENGTH(user_name) >= 4;
-- 함수 없이 4글자만 뽑는 방법
SELECT * FROM user_tbl WHERE user_name LIKE '____';
-- 함수 없이 4글자 이상만 뽑는 방법
SELECT * FROM user_tbl WHERE user_name LIKE '____%';
숫자 함수(Numeric Functions)
- 숫자 함수는 숫자 값을 조작하거나 숫자 정보를 반환하는 데 사용되는 함수이다.
- 반올림, 절사, 나머지 계산 등 다양한 숫자 관련 작업을 수행할 수 있다.
주요 숫자 함수
- ROUND(number, [decimal_places]) - 숫자를 지정된 소수점 자릿수로 반올림
- TRUNCATE(number, [decimal_places]) - 숫자를 지정된 소수점 자릿수로 절사
- MOD(number1, number2) - 첫 번째 숫자를 두 번째 숫자로 나눈 나머지를 반환
- CEIL(number) - 숫자를 지정된 소수점 자릿수로 올림
- FLOOR(number) - 숫자를 지정된 소수점 자릿수로 내림
- POWER(number, exponent) - 숫자를 지정된 거듭제곱 값으로 계산
- ABS(number) - 숫자의 절대값을 반환
- SIGN(number) - 숫자의 부호를 반환(양수이면 1, 음수이면 -1, 0이면 0을 반환)
- SQRT(number) - 숫자의 제곱근을 반환
- LN(number) - 숫자의 자연로그 값을 반환
- LOG(base, number) - 지정된 밑을 가지는 숫자의 로그 값을 반환합
- EXP(number) - e를 지정된 거듭제곱 값으로 계산
- SIN(number), COS(number), TAN(number) - 숫자의 사인, 코사인, 탄젠트 값을 계산
숫자 함수 실습
-- user_tbl에 소수점 아래를 저장받을 수 있는 컬럼 추가
-- DECIMAL은 고정자리수이믈 반드시 소수점 아래 2자리까지 표기해야 한다.
-- 전체 3자리 중 소수점 아래 2자리, 정수 1자리
ALTER TABLE user_tbl ADD(user_weight DECIMAL(3,2));
ALTER TABLE user_tbl MODIFY user_weight DECIMAL(5,2);
SELECT * FROM user_tbl;
-- 10번 유저 체중 변경
UPDATE user_tbl SET user_weight = 52.12 WHERE user_num = 10;
-- 숫자 함수 사용 예제
SELECT
user_name, user_weight,
ROUND(user_weight, 0) AS 체중반올림,
TRUNCATE(user_weight, 1) AS 체중소수점아래1자리절사,
MOD(user_height, 150) AS 키150으로나눈나머지,
CEIL(user_height) AS 키올림,
FLOOR(user_height) AS 키내림,
SIGN(user_height) AS 양수음수0여부,
SQRT(user_height) AS 키제곱근
FROM user_tbl;
날짜 함수(Date Functions)
날짜 함수
- 날짜 값을 조작하거나 날짜 정보를 반환하는 데 사용되는 함수이다.
- 날짜 간의 차이, 날짜에 일 또는 월을 더하거나 빼는 등 다양한 날짜 관련 작업을 수행할 수 있다.
주요 날짜 함수
- DATE_ADD(date, INTERVAL 개월수 MONTH) - 날짜에 지정된 개월 수를 더한 날짜를 반환
- LAST_DAY(date) - 지정된 날짜가 속한 달의 마지막 날짜를 반환
- TIMESTAMPDIFF(MONTH, date1, date2) - 두 날짜 간의 개월 수 차이를 반환
- DATE_ADD와 TIMESTAMPDIFF는 DAY, MONTH, YEAR 등의 인자를 사용한다.
날짜 함수 사용 예제
-- 날짜 함수 사용 예제
SELECT
user_name, entry_date,
DATE_ADD(entry_date, INTERVAL 3 MONTH) AS _3개월후,
LAST_DAY(entry_date) AS 해당월마지막날짜,
TIMESTAMPDIFF(DAY, entry_date, STR_TO_DATE('20240216', '%Y%m%d')) AS 오늘과일수차이
FROM user_tbl;
변환 함수(Conversion Functions)
변환 함수
데이터 유형을 변환하거나 다른 형식으로 표현하는 데 사용되는 함수이다.
문자열을 숫자 또는 날짜로 변환하거나 숫자 및 날짜를 다른 형식의 문자열로 변환할 수 있다.
주요 변환 함수
- CAST(자료 AS 변환할자료형) - 해당 자료를 변환할 자료형으로 변경
- STR_TO_DATE(string, [format]) - 문자열을 지정된 형식의 날짜로 변환
변환 함수 사용 예제
-- 변환 함수 사용 예제
SELECT
user_num, user_name, entry_date,
DATE_FORMAT(entry_date, '%Y%m%d') AS 일자표현변경,
CAST(user_num AS CHAR) AS 문자로바꾼회원번호
FROM user_tbl;
NULL 관련 함수(Null Functions)
NULL 관련 함수
- NULL 값을 처리하는 데 사용되는 함수이다.
- NULL 값을 기본 값으로 대체하거나 두 개의 표현식 중 NULL이 아닌 값을 선택할 수 있다.
주요 NULL 관련 함수
- IFNULL(expression1, expression2) - expression1이 NULL인 경우 expression2를 반환하고, 그렇지 않으면 expression1을 반환
- NULLIF(expression1, expression2) - expression1과 expression2가 같으면 NULL을 반환하고, 그렇지 않으면 expression1을 반환
- COALESCE(expression1, expression2, ...) - 인수 목록에서 첫 번째 NULL이 아닌 값을 반환, 모든 인수가 NULL이면 NULL을 반환
NULL 함수 사용 예제
-- user_height, user_weight가 NULL인 자료 추가
INSERT INTO user_tbl VALUES(null, '임쿼리', 1996, '제주', null, '2025-01-05', null);
-- IFNULL()로 특정 컬럼의 값이 null인 경우 대체값으로 표현
SELECT
user_name, user_height, user_weight,
IFNULL(user_height, 167) AS _NULL대신평균키,
IFNULL(user_weight, 65) AS _NULL대신평균체중
FROM user_tbl;
'네트워크 캠퍼스 > Database' 카테고리의 다른 글
'네트워크 캠퍼스/Database' Related Articles
more
