[MySQL] SQL과 데이터베이스, DML 기본 명령어

---

데이터베이스와 DBMS 개요

• 데이터베이스 (Database): 데이터를 저장하는 공간
• DBMS (Database Management System): 데이터베이스를 관리하는 시스템
• SQL (Structured Query Language): DBMS에서 데이터를 추가, 조회, 수정, 삭제하는 데 사용하는 언어. 데이터베이스에 대한 명령을 내리기 위해 표준화된 언어

CRUD

데이터 조작의 기본 작업을 의미

• Create: 데이터 추가
• Read: 데이터 조회
• Update: 데이터 수정
• Delete: 데이터 삭제

데이터베이스 유형

1. 관계형 데이터베이스 (Relational Database)

   • RDBMS (Relational DBMS): 데이터를 테이블로 구조화하며, 테이블 간 관계를 통해 데이터를 관리
   • SQL: 관계형 데이터베이스에서 데이터를 조작하는 데 사용되는 언어
     (예시: MySQL, PostgreSQL, Oracle, Microsoft SQL Server)

2. NoSQL (Non-relational Database)

   관계형 DB와 다르게 다양한 방식으로 데이터를 저장하며, 대규모 데이터 처리나 비정형 데이터에 유리하다.

   • Document형: JSON 형태로 데이터를 저장
     (예시: MongoDB)
   • Key-Value형: 키-값 쌍으로 데이터를 저장, 빠른 조회 성능
     (예시: Redis)
   • Column Family형: 데이터를 열 단위로 저장
     (예시: Cassandra, HBase)
   • Graph형: 노드와 엣지 구조로 관계를 저장
     (예시: Neo4j)

3. SQLite

   • 경량의 DBMS로 모바일 앱 개발 등에서 자주 사용됨. 서버 없이 애플리케이션에 통합된 형태로 동작

클라우드 DB 서비스

• AWS RDS (Amazon Relational Database Service): 아마존 웹 서비스에서 제공하는 관리형 RDBMS 서비스로, MySQL, PostgreSQL, MariaDB, Oracle, SQL Server 등을 지원한다. 클라우드 환경에서 데이터베이스를 설정, 관리 및 확장하는 데 유용하다.

+ 추가 개념

• ACID: RDBMS에서 트랜잭션의 무결성을 보장하기 위한 특성
  • Atomicity: 원자성 (작업이 모두 성공하거나 모두 실패해야 함)
  • Consistency: 일관성 (트랜잭션 후 데이터의 일관성이 유지됨)
  • Isolation: 독립성 (동시에 실행되는 트랜잭션이 서로 영향을 주지 않음)
  • Durability: 지속성 (트랜잭션 완료 후 데이터는 영구적으로 저장됨)
• CAP 이론: NoSQL 데이터베이스에서의 트레이드오프를 설명하는 이론
  • Consistency (일관성)
  • Availability (가용성)
  • Partition Tolerance (네트워크 분할 허용)

---

RDBMS(Relational Databese Management System)

관계형 테이터베이스라고 하며, 2차원의 테이블 구조로 저장한다.

여러 테이블이 서로 관계가 있다. 테이블간에 관계성이 있다는 특징이 있다.

---

데이터베이스 용어

• ⭐ 테이블 : 데이터를 구성하기 위한 가장 기본적인 단위(행과 열로 구성)
• 행 : 레코드(Record), 테이블의 가로로 배열된 데이터의 집합, 행은 반드시 고유한 식별자인 기본키(primary key)를 가진다.
• 열 : 행에 저장되는 유형의 데이터, 각각의 속성을 나타내며 데이터의 무결성을 보장한다. (나이 열에 문자열이 들어갈 수 없는 등의 속성에 맞지 않는 데이터 유형은 들어갈 수 없다.)
• 기본키(primary key) : 행을 구분할 수 있는 식별자이며, 테이블에서 유일해야 하고 중복 값을 가질 수 없다. null 값이 될 수 없으며, 수정이 되어서도 안된다. 데이터를 수정, 삭제, 조회 시에 사용되며, 다른 테이블과 관계를 맺어 데이터를 가져올 수도 있다.
• 쿼리 : 데이터베이스에서 데이터를 조회, 삭제, 생성, 수정 처리를 위해 사용하는 명령문이며, SQL 이라는 데이터베이스 전용 언어를 사용하여 작성한다.

---

MySQL

오픈소스 관계형 데이터베이스

-- 데이터베이스 생성
create database test_db;

-- 데이터베이스 접속
use test_db;

-- 데이터베이스 접속 다른 방법
CREATE TABLE test_db.students (
  name VARCHAR(255) NOT NULL,
  age INT NOT NULL,
  address VARCHAR(255) NOT NULL
);

테이블 및 데이터 생성

CREATE TABLE students (
  name VARCHAR(255) NOT NULL,
  age INT NOT NULL,
  address VARCHAR(255) NOT NULL
);

INSERT INTO students (name, age, address) VALUES
('이황', 28, '경상북도'),
('정약용', 29, '경기도'),
('김정호', 30, '전라북도'),
('박지원', 31, '전라북도'),
('김홍도', 32, '경기도'),
('신윤복', 33, '서울특별시'),
('김광균', 34, '서울특별시'),
('한용운', 35, '경상남도'),
('박두진', 36, '경기도');

CREATE TABLE classes (
  name VARCHAR(255) NOT NULL,
  class_name VARCHAR(255) NOT NULL
);

INSERT INTO classes (name, class_name) VALUES
('이황', '데이터베이스'),
('이황', '알고리즘'),
('정약용', '데이터베이스'),
('김정호', '자료구조'),
('박지원', '데이터베이스'),
('김홍도', '알고리즘'),
('신윤복', '자료구조'),
('신윤복', '알고리즘'),
('김광균', '데이터베이스'),
('김광균', '자료구조'),
('김광균', '알고리즘');

---

DML(Data Manipulation Language)

데이터베이스에서 데이터를 조작하는데 사용하는 SQL의 하위 집합으로 SQL을 이용하여 데이터를 조회, 삽입, 수정, 삭제하기 위한 문법이다.

SELECT	데이터 조회에 사용
INSERT	데이터 삽입에 사용
UPDATE	데이터 수정에 사용
DELETE	데이터 삭제에 사용

---

조회

SELECT	조회할 열을 지정
FROM	조회할 테이블을 지정
WHERE	조회할 데이터를 필터링

모든 열을 조회

students 테이블에 있는 모든 열을 조회

SELECT *
FROM students;

특정 열을 조회

학생의 이름과 나이만 조회

SELECT name, age
FROM students;

별칭을 사용하여 열 이름 변경

학생의 이름에 해당하는 열을 “col1”로, 나이에 해당하는 열을 “col2”로 변경

SELECT name AS col1, age AS col2
FROM students;

조건을 사용하여 데이터 필터

조건을 만족하는 행만 조회 → 나이가 30살 이상인 학생만 조회

SELECT *
FROM students
WHERE age >= 30;

중복된 행 제거

조회 결과에 중복된 행이 없도록 한다. 주소를 조회하지만 중복은 제거하는 SQL문

SELECT DISTINCT address
FROM students;

예제 실습

-- 학생 테이블에서 20대 정보만 추출하기
SELECT *
FROM students
WHERE age >= 20 AND age < 30;

-- '데이터베이스' 강의 듣는 학생의 이름을 조회
SELECT name
FROM classes
WHERE class_name = '데이터베이스';

-- '김광균' 학생이 듣는 강의명을 조회
SELECT class_name
FROM classes
WHERE name = '김광균';

-- '제주도'에 사는 학생 조회
SELECT name
FROM students
WHERE address = '제주도';

---

삽입

INSERT INTO

테이블에 새로운 행(row)을 삽입할 때 사용한다.

한 행 삽입

테이블에 새로운 학생 정보를 삽입

INSERT INTO students (name, age, address)
VALUES ('김이박', 40, '서울특별시');

문자열은 ‘ ’ 작은 따옴표로감싼다.

한 행 삽입 + 특정 열만

INSERT INTO students (name, age)
VALUES ('신기루', 41);

students 테이블에 새로운 학생 정보를 삽입. name 열에는 ‘신기루’, age 열에는 41, address 열에는 아무런 값도 삽입하지 않으려고 할 때 에러가 난다.

ERROR: 실패한 자료: (신기루, 41, null)"address" 칼럼(해당 릴레이션 "students")의 null 값이 not null 제약조건을 위반했습니다.

address 열에 반드시 데이터를 입력해야하는데 그렇지 않아 발생한 에러. address 열은 not null이라는 속성으로 세팅되어 있던 것이다. not null 속성은 테이블을 생성할 때 정할 수 있다. not null 속성이 아니라먄 NULL(값이 존재하지 않음) 이라는 값이 들어간다.

여러 행 삽입

student 테이블에 세 명의 학생 정보를 삽입

INSERT INTO students (name, age, address)
VALUES ('학생1', 20, '경기도'), ('학생2', 22, '경기도'), ('학생3', 23, '경기도');

SQL문을 실행하면 세 명의 학생 정보가 한 번에 삽입되는데, 여러 행을 한 번에 삽입한다고 해서
bulk insert 라고도 한다.

조회 후 삽입

테이블을 조회한 후에 그 결과를 삽입

INSERT INTO students (name, age, address)
SELECT name, age, address 
FROM students 
WHERE age < 30;

1. students 테이블에서 age가 30미만인 학생만 조회
2. INSERT INTO 에서는 1번에서 조회한 학생 정보를 students 테이블에 삽입

예제 실습

-- '경상남도'에 사는 학생 정보를 조회해서 새로운 row로 저장하기
INSERT INTO students(name, age, address)
SELECT name, age, address
FROM students
WHERE address = '경상남도';

---

📝 safe update mode (안전 업데이트 모드)

13:32:59 UPDATE students SET age = 99 WHERE name = '이황' Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.000 sec

수정을 하려고 할 때 해당 오류가 발생했다. Safe update mode는 실수로 데이터베이스의 중요한 데이터를 변경하지 않도록 보호하기 위해, 기본적으로 WHERE 조건에 키(Primary Key 또는 Unique Key) 컬럼을 포함하지 않으면 UPDATE나 DELETE 명령을 수행할 수 없도록 제한하는 모드이다.

해결 방법으로는

• WHERE 절에 Primary Key나 Unique Key 컬럼을 포함하여 안전하게 업데이트를 진행하는 방법
• Safe update mode를 비활성화하는 방법

이 있는데 실습 진행 중인 관계로 Safe update mode 비활성화 했다.

비활성화 하는 방법으로는

• [ Edit - Preference - SQL Editor - 맨 아래의 Safe Updates 체크 해제]
• SET SQL_SAFE_UPDATES = 0; 코드 직접 입력
  SET SQL_SAFE_UPDATES = 0; 은 safe update mode를 해제하는 코드이고
  SET SQL_SAFE_UPDATES = 1; 은 다시 설정하는 코드이다.

SET SQL_SAFE_UPDATES = 0; -- 0 : sefe update mode 해제 , 1: safe update mode 설정

safe mode를 해제하면 SELECT문에 PK가 아닌 필드를 사용해도 오류가 발생하지 않는다.

---

수정

새로운 행(row)을 수정할 때 사용

UPDATE	수정할 테이블을 지정
SET	데이터 수정
WHERE	수정할 데이터를 필터링

필드 한 개 수정

students 테이블의 학생 나이를 수정

-- '이황' 이라는 이름을 가진 학생의 나이를 99로 수정
UPDATE students
SET age = 99
WHERE name = '이황';

필드 여러 개 수정

students 테이블의 학생 나이와 주소를 수정

UPDATE students
SET age = 10,
		address = '서울특별시'
WHERE name = '정약용';

조건부 수정

students 테이블에서 나이가 33살 미만인 학생들의 주소를 ‘인천광역시’로 수정

UPDATE students
SET address = '인천광역시'
WHERE age < 33;

---

삭제

테이블의 행(row)를 삭제할 때 사용

DELETE FROM	삭제할 테이블을 지정
WHERE	삭제할 데이터를 필터링

특정 행 삭제

DELETE
FROM students
WHERE name = '이황';

‘이황’이라는 이름을 가진 학생을 삭제

여러 행 삭제

DELETE
FROM students
WHERE age BETWEEN 30 AND 33;

나이가 30~33살 사이인 학생을 삭제

모든 행 삭제

DELETE
FROM students;

테이블의 모든 데이터를 삭제

TRUNCATE vs DELETE

“TRUNCATE”와 “DELETE”는 데이터를 삭제한다는 점에서 같지만 전체 데이터를 삭제할 때는 “DELETE”보다 “TRUNCATE”의 속도가 더 빠르다. 그 이유는 TRUNCATE의 경우 테이블을 스캔하는 과정을 거치지 않기 때문이다.

‘속도가 더 빠르면 TRUNCATE 명령어를 사용해야 하는 것인가?’ 생각할 수 있지만, DELETE 명령어는 로그를 남기고 데이터 복구가 가능하다는 특징 때문에 많이 사용한다.

DROP vs TRUNCATE vs DELETE

	DROP(DDL)	TRUNCATE(DDL)	DELETE(DML)
종류	DDL	DDL(일부 DML)	DML
COMMIT	AUTO COMMIT	AUTO COMMIT	사용자 COMMIT
ROLLBACK	불가	불가	가능
명령어 수행시	테이블 정의 삭제	테이블을 최초 생성한 초기 상태로 만들어줌	데이터만 삭제
로그	남기지 않음	남기지 않음	남김
속도	빠름	빠름	느림

예제 실습

-- '자료구조' 강의가 취소되었습니다. 강의 정보를 삭제해주세요.
DELETE 
FROM classes 
WHERE class_name = '자료구조';

-- 나이가 40 이상인 학생 정보를 삭제해주세요.
DELETE 
FROM students 
WHERE age >= 40;

-- 나이가 35세 이상, 37세 이하인 학생 정보를 삭제해주세요 (BETWEEN AND 사용)
DELETE 
FROM students 
WHERE age BETWEEN 35 AND 37;

---

내장 함수

함수란?

데이터베이스에서 함수는 프로그래밍에서의 함수와 역할이 동일하다. 입력 값을 받아 계산(작업) 을 수행하고 결과를 반환하는 구조로 되어 있다. 주로 간단한 연산, 수치 변환 등을 위해 사용한다.

내장 함수란?

DBMS에서 기본적으로 제공하는 함수, 사용자가 별도로 함수를 만들지 않아도 DBMS 를 설치했다면 기본적으로 사용할 수 있는 함수로 내장 함수는 DBMS마다 약간의 차이가 있을 수 있다.

• 숫자 관련 내장함수 sum(), avg(), max(), min(), count()
• 문자열 관련 내장함수 char_length(), concat(), replace()

숫자 관련 내장 함수 - sum(), avg(), max(), min(), count()

SUM : 숫자의 합을 반환

SELECT SUM(age) FROM students;

AVG : 숫자의 평균을 반환

SELECT AVG(age) FROM students;

MAX : 숫자의 최대값을 반환

SELECT MAX(age) FROM students;

MIN : 숫자의 최소값을 반환

SELECT MIN(age) FROM students;

COUNT : 행의 개수를 반환

SELECT COUNT(DISTINCT address) FROM students;

문자열 관련 내장 함수 - char_length(), concat(), replace()

CONCAT : 두 문자열을 연결

SELECT CONCAT(name, address) FROM students;

CHAR_LENGTH : 문자열의 길이를 반환

SELECT address, CHAR_LENGTH(address) FROM students;

LENGTH : 문자열의 byte길이를 반환

SELECT address, LENGTH(address) FROM students;

REPLACE : 특정 문자열을 다른 문자열로 치환

SELECT REPLACE(address, '도', '레') FROM students;

번외)

NOW : 현재의 날짜와 시간을 반환

SELECT NOW();

예제 실습

-- 30대 학생들 중 가장 어린 학생의 나이를 출력해주세요
SELECT MIN(age) 
FROM students
WHERE age BETWEEN 30 AND 39; -- WHERE age >= 30 and age < 40;

-- 20대 학생 나이의 평균을 구하세요
SELECT AVG(age) 
FROM students
WHERE age BETWEEN 20 AND 29;

-- 35세 이상의 학생은 총 몇명인지 출력해주세요
SELECT COUNT(age) 
FROM students
WHERE age >= 35;

-- 가장 작은 price 를 smallPrice 로 생성하기 (테이블명 Products)
SELECT MIN(price) AS smallPrice
FROM products;