Coding Test [2]: SQL 프로그래밍 (SQL Programming)

Development/Coding Test

Coding Test [2]: SQL 프로그래밍 (SQL Programming)

JaehyeonByun 2025. 1. 11. 12:36

SELECT * FROM users WHERE age > 25;

1. MYSQL 설치

2. SELEC 문

MySQL의 SELECT 문은 데이터베이스에서 원하는 데이터를 조회하기 위한 가장 기본적인 SQL 명령어다. 기본 형식은 SELECT 열이름 FROM 테이블이름으로, 특정 열의 데이터를 가져오거나, SELECT *를 사용해 테이블의 모든 열을 조회할 수 있다. 예를 들어, SELECT name, age FROM users는 users 테이블에서 name과 age 열의 데이터를 가져오는 쿼리다. 데이터를 특정 조건에 따라 필터링하려면 WHERE 절을 사용할 수 있으며, 예를 들어 SELECT * FROM users WHERE age > 20은 20세 이상인 사용자만 조회한다. 이처럼 SELECT 문은 데이터 조회의 핵심 명령어로, 다양한 옵션을 통해 데이터를 필터링, 정렬, 그룹화하여 원하는 정보를 효율적으로 검색할 수 있다.

<SELECT...FROM>

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

-- 데이터 삽입
INSERT INTO users (name, age, city) VALUES
('Alice', 25, 'New York'),
('Bob', 30, 'Los Angeles'),
('Charlie', 22, 'Chicago');

-- 모든 데이터 조회
SELECT * FROM users;

-- 특정 열(name, age)만 조회
SELECT name, age FROM users;

-- 조건에 맞는 데이터(age > 25) 조회
SELECT name, city FROM users WHERE age > 25;

테이블에서 필요로 하는 열은 SELECT 열 이름으로 가져오는 것이 가능하고, 여러 개의 열을가져오고 싶을 때는 컴마로 구분한다. 열 이름의 순서는 출력하고 싶은 순서대로 배열이 가능하다.

USE 구문

MySQL의 USE 구문은 데이터베이스를 선택하여 이후의 SQL 명령이 해당 데이터베이스에서 실행되도록 설정하는 데 사용된다. MySQL 서버에 여러 데이터베이스가 있을 경우, 특정 데이터베이스를 지정해야 테이블, 뷰, 프로시저 등을 사용할 수 있다. 기본 형식은 USE 데이터베이스이름이며, 이를 실행하면 지정한 데이터베이스가 활성화된다. 한 번 설정된 데이터베이스는 명시적으로 다른 데이터베이스를 지정하거나 MySQL 세션이 종료될 때까지 유지된다.

SHOW DATABASES;
USE my_database;
USE my_database; SELECT * FROM users;

Workbench에서 직접 선택해서 사용도 가능하다 - [Navigator]의 [Schemas] 탭. 선택된 DB가 employees라면 다음 쿼리는 동일하다.

DROP DATABASE IF EXISTES sqlDB

다음과 같이 문제가 생긴 DB를 초기화할 수 있다. 계속 사용할 쿼리는 SQL 파일로 저장해서 재사용 가능하게 만들고 파일 내용을 불러다 쓰기 전에 모든 쿼리 창을 닫도록 한다.

MySQL의 SELECT ... FROM ... WHERE 구문에서 WHERE 절은 다양한 연산자를 사용해 데이터를 필터링한다. 비교 연산자인 =, !=, >, < 등을 통해 특정 값과 일치하거나 크고 작은 데이터를 조회할 수 있다. 논리 연산자인 AND와 OR은 여러 조건을 조합하며, NOT은 조건을 부정한다. 패턴 매칭 연산자인 LIKE는 특정 문자열 패턴과 일치하는 데이터를, BETWEEN은 범위 내의 데이터를, IN은 특정 값 목록에 포함된 데이터를 필터링한다. 또한, IS NULL과 IS NOT NULL로 NULL 값을 확인할 수 있다. 예를 들어, SELECT * FROM users WHERE age > 25 AND city IN ('New York', 'Chicago')는 age가 25보다 크고 city가 'New York' 또는 'Chicago'인 데이터를 조회한다. 이러한 연산자는 조건을 조합하여 필요한 데이터를 정확하고 효율적으로 검색할 수 있게 한다. LIKE 연산자는 WHERE 절에서 특정 문자열 패턴과 일치하는 데이터를 필터링하는 데 사용된다. 주로 와일드카드 %와 _를 함께 사용하며, %는 0개 이상의 임의의 문자를, _는 정확히 한 개의 임의의 문자를 대체한다.

-- 나이가 25 초과하는 사용자 조회
SELECT * FROM users WHERE age > 25;
-- 이름이 'n'으로 끝나는 사용자 조회
SELECT * FROM users WHERE name LIKE '%n';
-- 이름의 두 번째 문자가 'm'인 사용자 조회
SELECT * FROM users WHERE name LIKE '_m%';

Order BY

MySQL의 ORDER BY 구문은 쿼리 결과를 특정 열(column)을 기준으로 정렬하는 데 사용된다. 기본적으로 오름차순 정렬은 ASC(기본값)로, 내림차순 정렬은 DESC로 지정한다. 여러 열을 기준으로 정렬하려면 열 이름을 쉼표로 구분해 순서를 지정할 수 있다. 예를 들어, SELECT * FROM users ORDER BY age ASC는 나이를 기준으로 오름차순 정렬하며, SELECT * FROM users ORDER BY age DESC, name ASC는 나이를 내림차순, 이름을 오름차순으로 정렬한다. 이를 통해 데이터를 원하는 순서대로 정렬해 가독성과 분석을 높일 수 있다.

-- 테이블 생성 및 데이터 삽입
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

INSERT INTO users (name, age, city) VALUES
('Alice', 25, 'New York'),
('Bob', 30, 'Los Angeles'),
('Charlie', 22, 'Chicago'),
('Dave', 30, 'Seattle');

-- 나이를 기준으로 오름차순 정렬
SELECT * FROM users ORDER BY age ASC;

-- 나이를 내림차순, 같은 나이에서는 이름을 오름차순 정렬
SELECT * FROM users ORDER BY age DESC, name ASC;

MySQL의 DISTINCT와 LIMIT는 쿼리 결과를 필터링하거나 제한하는 데 사용되는 유용한 명령어다.

DISTINCT
DISTINCT는 쿼리 결과에서 중복된 값을 제거하고 고유한 값만 반환한다. 주로 SELECT 문과 함께 사용되며, 특정 열이나 열 조합의 고유값을 얻고자 할 때 활용된다. 예를 들어, SELECT DISTINCT city FROM users는 users 테이블에서 중복된 도시 이름을 제거하고 고유한 도시 이름만 반환한다.
LIMIT
LIMIT는 쿼리 결과에서 반환되는 행(row)의 수를 제한하는 데 사용된다. 데이터가 많을 경우 원하는 개수만 가져오거나, 페이징(paging)을 구현할 때 활용된다. 형식은 LIMIT [시작 행], [개수]로 사용하며, 예를 들어 SELECT * FROM users LIMIT 2는 상위 2개의 행만 반환하고, LIMIT 2, 3은 세 번째 행부터 세 개의 데이터를 반환한다.

-- 테이블 생성 및 데이터 삽입
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50)
);

INSERT INTO users (name, city) VALUES
('Alice', 'New York'),
('Bob', 'Chicago'),
('Charlie', 'New York'),
('Dave', 'Seattle'),
('Eve', 'Chicago');

-- 중복된 도시를 제거하고 고유한 도시만 조회
SELECT DISTINCT city FROM users;

-- 상위 3개의 데이터만 조회
SELECT * FROM users LIMIT 3;

-- 2번째 데이터부터 2개의 데이터만 조회
SELECT * FROM users LIMIT 1, 2;

Group BY

MySQL의 GROUP BY 구문은 데이터를 특정 열의 값을 기준으로 그룹화하고, 각 그룹에 대해 집계 연산(예: COUNT, SUM, AVG, MAX, MIN)을 수행할 때 사용된다. 이를 통해 동일한 값을 가진 데이터를 하나의 그룹으로 묶고, 그룹별 통계나 요약 데이터를 추출할 수 있다. 일반적으로 SELECT 문과 함께 사용되며, HAVING 절을 사용해 그룹화된 데이터에 추가 조건을 적용할 수도 있다.

예를 들어, SELECT city, COUNT(*) FROM users GROUP BY city는 users 테이블에서 각 도시별 사용자 수를 계산한다. 그룹화된 데이터를 필터링하려면 WHERE가 아닌 HAVING을 사용해야 하며, 예를 들어 HAVING COUNT(*) > 1은 사용자 수가 1명 이상인 그룹만 반환한다.

-- 테이블 생성 및 데이터 삽입
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50)
);

INSERT INTO users (name, city) VALUES
('Alice', 'New York'),
('Bob', 'Chicago'),
('Charlie', 'New York'),
('Dave', 'Seattle'),
('Eve', 'Chicago');

-- 도시별 사용자 수 조회
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

-- 사용자 수가 1명 이상인 도시만 조회
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING user_count > 1;

집계 함수설명예제결과 예시

COUNT()	그룹 내 행(row)의 개수를 계산	SELECT city, COUNT(*) FROM users GROUP BY city;
SUM()	그룹 내 특정 열의 합계를 계산	SELECT city, SUM(salary) FROM users GROUP BY city;
AVG()	그룹 내 특정 열의 평균 값을 계산	SELECT city, AVG(age) FROM users GROUP BY city;
MAX()	그룹 내 특정 열의 최대 값을 반환	SELECT city, MAX(age) FROM users GROUP BY city;
MIN()	그룹 내 특정 열의 최소 값을 반환	SELECT city, MIN(age) FROM users GROUP BY city;
GROUP_CONCAT()	그룹 내 모든 값을 하나의 문자열로 연결	SELECT city, GROUP_CONCAT(name) FROM users GROUP BY city;
COUNT(DISTINCT)	그룹 내 중복을 제외한 고유값의 개수를 계산	SELECT city, COUNT(DISTINCT name) FROM users GROUP BY city;

HAVING 절

HAVING 절은 GROUP BY로 그룹화된 데이터에 조건을 적용할 때 사용된다. WHERE 절은 그룹화 전에 개별 행에 조건을 적용하지만, HAVING은 그룹화 후 집계된 결과에 조건을 적용한다. 예를 들어, 특정 조건을 만족하는 그룹만 조회하고 싶을 때 활용된다.

-- 도시별 사용자 수와 전체 사용자 수 조회
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city WITH ROLLUP;

**결과: HAVING은 COUNT(*) > 1 조건에 맞는 그룹(도시)만 반환한다.**

WITH ROLLUP

WITH ROLLUP은 GROUP BY의 확장 기능으로, 그룹화된 데이터의 총계를 계산하여 추가 행으로 반환한다. 이를 통해 각 그룹의 요약 정보뿐만 아니라 전체 데이터의 총합 또는 평균 같은 정보를 함께 볼 수 있다.

-- 도시별 사용자 수와 전체 사용자 수 조회
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city WITH ROLLUP;

NULL 행은 전체 사용자의 합계(총 5명)를 나타낸다.

-- 사용자 수가 1명 이상인 그룹과 총계를 포함하여 조회
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city WITH ROLLUP
HAVING user_count > 1 OR city IS NULL;

DML (Data Manipulation Language)

DML은 데이터베이스의 데이터를 조작하거나 조회하는 데 사용되는 SQL 명령어로, 데이터의 삽입, 수정, 삭제, 조회 등을 수행한다. DML 명령어는 데이터를 다루기 때문에 트랜잭션과 연관이 있으며, 작업 후 COMMIT이나 ROLLBACK으로 변경 사항을 확정하거나 취소할 수 있다. 주요 명령어로는 INSERT, UPDATE, DELETE, SELECT가 있다. 예를 들어, INSERT INTO users (name, age) VALUES ('Alice', 25);는 users 테이블에 데이터를 삽입하며, UPDATE users SET age = 26 WHERE name = 'Alice';는 특정 조건에 맞는 데이터를 수정한다. 또한, DELETE FROM users WHERE age > 30;는 조건에 따라 데이터를 삭제하고, SELECT * FROM users;는 데이터를 조회한다. DML은 데이터의 변화를 직접 다루기 때문에 가장 자주 사용되는 SQL 명령어 그룹이다.

DCL (Data Control Language)

DCL은 데이터베이스의 보안 및 접근 제어를 담당하는 SQL 명령어로, 사용자에게 권한을 부여하거나 제거하는 작업에 사용된다. 주요 명령어로는 GRANT와 REVOKE가 있다. 예를 들어, GRANT SELECT, INSERT ON users TO 'user1';는 user1 사용자에게 users 테이블에 대한 SELECT와 INSERT 권한을 부여한다. 반대로, REVOKE INSERT ON users FROM 'user1';는 user1의 users 테이블에 대한 INSERT 권한을 제거한다. DCL 명령어는 데이터베이스 보안과 다중 사용자 환경에서 중요한 역할을 하며, 데이터에 대한 접근을 효율적으로 관리할 수 있도록 돕는다.

DDL (Data Definition Language)

DDL은 데이터베이스의 구조를 정의하거나 수정하는 데 사용되는 SQL 명령어로, 데이터베이스, 테이블, 인덱스 등의 객체를 생성, 수정, 삭제할 수 있다. 주요 명령어로는 CREATE, ALTER, DROP, TRUNCATE 등이 있다. 예를 들어, CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT);는 users라는 이름의 테이블을 생성한다. 또한, ALTER TABLE users ADD COLUMN city VARCHAR(50);는 테이블에 새로운 열을 추가하며, DROP TABLE users;는 테이블을 삭제한다. TRUNCATE TABLE users;는 테이블의 모든 데이터를 삭제하지만 테이블 구조는 유지한다. DDL 명령어는 데이터베이스의 스키마 설계 및 유지보수에 필수적인 역할을 한다.

MySQL의 INSERT 문

INSERT 문은 MySQL에서 새로운 데이터를 데이터베이스 테이블에 추가하는 데 사용된다. 데이터를 삽입할 때는 테이블 이름과 삽입할 열(컬럼) 이름을 명시한 후, VALUES 키워드를 통해 값을 제공한다. 여러 행을 한 번에 삽입하거나 기본값이 설정된 열에 값을 생략할 수도 있다. 데이터가 테이블 스키마와 일치하지 않으면 오류가 발생하므로 스키마를 이해하고 적절한 데이터를 제공해야 한다.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (name, position, salary)
VALUES ('Alice', 'Developer', 75000.00);

아래는 employees 테이블에 새로운 직원 데이터를 추가하는 예시다. 여기서 id는 자동 증가 열이므로 명시적으로 값을 삽입하지 않아도 된다. 위의 쿼리를 실행하면 employees 테이블에 Alice라는 이름의 개발자가 추가된다.

MySQL의 DELETE 문

DELETE 문은 MySQL에서 데이터베이스 테이블의 특정 행을 삭제하는 데 사용된다. WHERE 절을 사용하여 삭제할 행을 지정하며, 이를 생략하면 테이블의 모든 데이터가 삭제된다. 따라서 항상 WHERE 조건을 명확히 지정하여 데이터 손실을 방지해야 한다. 삭제 작업은 복구가 불가능하므로 주의가 필요하다.

DELETE FROM table_name
WHERE condition;

이 쿼리는 name이 'Alice'인 행을 삭제한다. 만약 모든 데이터를 삭제하고 싶다면 WHERE 절을 생략할 수 있지만, 이는 매우 위험하며 신중해야 한다.

DELETE FROM employees
WHERE name = 'Alice';

위 명령은 테이블의 모든 데이터를 삭제한다. 테이블의 구조는 유지되며, 데이터만 제거된다.

DELETE FROM employees;

INSERT 문을 사용할 때 데이터 무결성을 유지하기 위해 기본 키 또는 유일 키 제약 조건을 준수해야 한다.
DELETE 문을 사용할 때는 WHERE 조건이 명확히 설정되지 않을 경우 데이터 손실 위험이 크다. 데이터 백업을 권장한다.
DELETE 대신 특정 조건을 만족하는 데이터를 모두 제거하려면 TRUNCATE를 사용할 수도 있지만, 이는 테이블의 데이터를 모두 지우면서도 롤백할 수 없다.

'Development > Coding Test' 카테고리의 다른 글

Coding Test [1] : C++ 프로그래밍 (C++ Programming) (0)	2024.12.30
Coding Test [0] : 코딩테스트 준비 (Coding Test Environment) (3)	2024.12.27

현재글Coding Test [2]: SQL 프로그래밍 (SQL Programming)

JaehyeonByun

정리해둔 내용들 티스토리로 보기 좋게 다시 쓰는 중

ㅈ,

Today :
Yesterday :

JaehyeonByun