MySQL + MySQL Workbench 설치방법 (윈도우)
MySQL + MySQL Workbench 설치방법 (맥)
MySQL Workbench 사용방법
SQL의 주요 언어 유형
MySQL User 데이터
MySQL 데이터베이스 Schema 구성 (DDL)
MySQL 데이터 타입
SQL (DML) 기초
테이블 생성 - CREATE TABLE (DDL)
CREATE TABLE users (
user_id INTEGER PRIMARY KEY AUTO_INCREMENT,
username TEXT NOT NULL,
email TEXT NOT NULL,
age INTEGER
);
데이터 생성 - INSERT INTO
1. 기본적인 INSERT 문
가장 기본적인 형태로 모든 컬럼에 값을 지정하여 레코드를 추가합니다.
INSERT INTO users (username, email, age) VALUES ('john_doe', 'john@example.com', 25);
2. 모든 컬럼에 값을 지정하지 않는 경우
일부 컬럼에만 값을 지정하고 나머지는 기본값 또는 NULL 값을 가지도록 할 수 있습니다.
INSERT INTO users (username, email) VALUES ('jane_doe', 'jane@example.com');
3. 다수의 레코드 한 번에 추가
여러 개의 레코드를 한 번에 추가할 수 있습니다.
INSERT INTO users (username, email, age) VALUES
('alice', 'alice@example.com', 30),
('bob', 'bob@example.com', 28),
('charlie', 'charlie@example.com', 35);
4. 컬럼의 일부만 선택하여 추가
특정 컬럼만 선택하여 값을 추가할 수 있습니다.
INSERT INTO users (username, email) VALUES
('david', 'david@example.com'),
('elena', 'elena@example.com');
5. 중복된 레코드 피하기
중복된 값이 있는 경우 레코드를 추가하지 않고 에러를 방지합니다.
INSERT IGNORE INTO users (username, email, age) VALUES ('john_doe', 'john@example.com', 25);
6. 중복된 레코드 업데이트
중복된 값이 있는 경우 해당 레코드를 업데이트합니다.
INSERT INTO users (username, email, age) VALUES ('john_doe', 'john@example.com', 25)
ON DUPLICATE KEY UPDATE age = 25;
7. AUTO_INCREMENT 컬럼 다루기
AUTO_INCREMENT를 가진 컬럼은 자동으로 증가하는 값을 가지며, 명시적으로 값을 지정하지 않습니다.
INSERT INTO users (username, email) VALUES ('frank', 'frank@example.com');
이러한 INSERT 쿼리문을 적절히 활용하여 데이터를 효과적으로 데이터베이스에 추가할 수 있습니다.
8. VALUES 또는 SET을 사용하여 여러 레코드를 동시에 삽입
한 번의 INSERT 문에서 여러 개의 VALUES 또는 SET 절을 사용하여 여러 레코드를 동시에 삽입할 수 있습니다.
INSERT INTO users (username, email, age)
VALUES ('john', 'john@example.com', 25),
('jane', 'jane@example.com', 28),
('bob', 'bob@example.com', 30);
9. SET 문을 사용한 추가
SET을 사용하여 컬럼에 여러 값들을 설정할 수 있습니다.
INSERT INTO users SET username='john', email='john@example.com', age=25;
데이터 조회 - SELECT FROM
(1) 기본적인 조회 - SELECT
SQL
-- 모든 컬럼 조회
SELECT * FROM users;
-- 특정 컬럼만 조회
SELECT user_id, username, email FROM users;
(2) 중복 데이터 삭제 - DISTINCT
-- 중복 제거한 나이 조회
SELECT DISTINCT age FROM users;
(3) 일시적으로 추가 컬럼 만들기 - AS
-- 나이와 나이에 100을 곱한 값을 조회
SELECT age, age * 100 FROM users;
-- AS를 사용하여 새로운 컬럼명 정의
SELECT age, age * 100 AS age100 FROM users;
(4) 데이터 정렬하기 - ORDER BY
-- 나이순으로 오름차순 정렬
SELECT * FROM users ORDER BY age;
-- 나이순으로 내림차순 정렬
SELECT * FROM users ORDER BY age DESC;
-- 여러 기준으로 정렬 (ASC: 오름차순, DESC: 내림차순)
SELECT * FROM users ORDER BY age ASC, created DESC;
(5) 조건문 - WHERE
-- 특정 조건에 맞는 데이터 조회
SELECT * FROM users WHERE age = 30;
-- 특정 조건 이상 데이터 조회
SELECT * FROM users WHERE age >= 30;
-- AND, OR를 사용한 복합 조건
SELECT * FROM users WHERE age = 33 AND name = 'Leo';
SELECT * FROM users WHERE age = 33 OR name = 'Leo';
-- NOT을 사용한 부정 조건
SELECT * FROM users WHERE NOT age = 33;
-- BETWEEN을 사용한 범위 지정
SELECT * FROM users WHERE age BETWEEN 20 AND 25;
(6) 특정 개수 제한 - LIMIT
-- 상위 5개의 데이터 조회
SELECT * FROM users LIMIT 5;
-- 10번째부터 5개의 데이터 조회 (페이징)
SELECT * FROM users LIMIT 10, 5;
(7) 결과 그룹핑 - GROUP BY
-- 나이별로 그룹화하여 그룹별 데이터 개수 조회
SELECT age, COUNT(*) as user_count FROM users GROUP BY age;
(8) 특정 조건에 따라 값 변환 - CASE WHEN
-- 나이가 30 이상인 경우 '성인', 미만인 경우 '미성년자'로 변환하여 조회
SELECT
name,
age,
CASE WHEN age >= 30 THEN '성인' ELSE '미성년자' END AS age_group
FROM users;
(9) 여러 테이블 조인 - JOIN
-- users 테이블과 orders 테이블을 user_id를 기준으로 조인
SELECT users.name, users.age, orders.order_id
FROM users
JOIN orders ON users.user_id = orders.user_id;
(10) 결과 내림차순으로 순위 부여 - ROW_NUMBER()
-- 나이에 따라 내림차순으로 순위 부여하여 조회
SELECT
name,
age,
ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
FROM users;
데이터 업데이트 - UPDATE SET
기본적인 UPDATE 쿼리
UPDATE 테이블명
SET 컬럼1 = 값1, 컬럼2 = 값2, ...
WHERE 조건;
테이블명: 업데이트할 테이블의 이름
컬럼 = 값: 업데이트할 컬럼과 새로운 값을 지정
WHERE 조건: 어떤 레코드를 업데이트할지 결정하는 조건
UPDATE
쿼리-- users 테이블에서 id가 1인 레코드의 이름을 'John'으로 수정
UPDATE users
SET name = 'John'
WHERE id = 1;
여러 레코드 동시에 업데이트
-- age가 25 이상인 모든 레코드의 salary를 50000으로 수정
UPDATE users
SET username = 'senior'
WHERE age >= 60;
SET SQL_SAFE_UPDATES = 0;
(세이프 모드 비활성화)업데이트된 레코드 수 확인
-- 업데이트된 레코드 수 반환
SELECT ROW_COUNT();
CASE
문을 사용한 업데이트-- user의 age가 60 이상인 경우 'senior'로 username 설정,
-- 그 외의 경우 username은 'young'로 설정
UPDATE users
SET username = CASE
WHEN age >= 60 THEN 'senior'
ELSE 'young'
END;
LIMIT
을 사용한 일부 레코드만 업데이트-- age가 30인 레코드 중에서 가장 나이가 어린 5명의 salary를 60000으로 수정
UPDATE users
SET username = 'top5_young_people'
WHERE age = 30
LIMIT 5;
SUBQUERY
를 사용한 업데이트-- 다른 서브쿼리 결과에 따라 업데이트
UPDATE products
SET price = price * 1.1
WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics');
REGEXP
를 사용한 업데이트-- 정규 표현식을 활용하여 업데이트
UPDATE users
SET email = CONCAT(email, '_new')
WHERE email REGEXP '@example\.com$';
CASE
문을 사용한 다양한 조건에 따른 업데이트-- 다양한 조건에 따라 다른 업데이트 수행
UPDATE products
SET price = CASE
WHEN stock < 10 THEN price * 1.1
WHEN stock >= 10 AND stock < 50 THEN price * 1.05
ELSE price
END;
데이터 제거 - DELETE FROM
기본적인 삭제
-- 특정 테이블에서 모든 행 삭제
DELETE FROM users;
조건을 사용한 삭제
-- 특정 조건을 만족하는 행 삭제
DELETE FROM users WHERE age < 18;
LIMIT
을 사용한 삭제-- 특정 개수 이상의 행을 삭제하지 않도록 제한
DELETE FROM orders WHERE status = 'canceled' LIMIT 100;
JOIN
을 사용한 삭제-- 다른 테이블과 조인하여 삭제
DELETE e FROM employees AS e
JOIN departments AS d ON e.department_id = d.id
WHERE d.name = 'Marketing';
USING
을 사용한 삭제-- 다른 테이블과 조인하여 삭제 (USING 구문 활용)
DELETE FROM employees
USING employees, departments
WHERE employees.department_id = departments.id AND departments.name = 'HR';
RETURNING
을 사용한 삭제 및 반환-- 삭제한 행 반환 (PostgreSQL에서 사용 가능)
DELETE FROM users WHERE age > 65 RETURNING *;
SQL (DML) 심화
테이블 생성
users 테이블
CREATE TABLE users (
user_id INTEGER PRIMARY KEY AUTO_INCREMENT,
username TEXT NOT NULL,
email TEXT NOT NULL
);
orders 테이블
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
product_name VARCHAR(255),
quantity INT,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
파이썬으로 데이터 랜덤 생성(generate)
설치가 필요한 패키지
pip install mysql-connector-python faker
app.py
import mysql.connector
from faker import Faker
import random # 파이썬 기본 모듈
# (1) MYSQL 연결 설정
db_connection = mysql.connector.connect(
host='localhost',
user='root',
password='oz-password',
database='testdatabase'
)
# (2) MYSQL 연결
cursor = db_connection.cursor()
faker = Faker()
# 100명의 users 더미 데이터 생성
for _ in range(100):
username = faker.user_name()
email = faker.email()
sql = "INSERT INTO users(username, email) VALUES(%s, %s)"
values = (username, email)
cursor.execute(sql, values)
# user_id 불러오기
cursor.execute("SELECT user_id FROM users")
valid_user_id = [row[0] for row in cursor.fetchall()]
# 100개의 주문 더미 데이터 생성
for _ in range(100):
user_id = random.choice(valid_user_id)
product_name = faker.word()
quantity = random.randint(1, 10)
try:
sql = "INSERT INTO orders(user_id, product_name, quantity) VALUES(%s, %s, %s)"
values = (user_id, product_name, quantity)
cursor.execute(sql, values)
except:
print("오류 발생")
pass
db_connection.commit()
cursor.close()
db_connection.close()
데이터 조인 - INNER, LEFT, RIGHT, FULL JOIN
두 개 이상의 테이블을 연결하여 하나의 테이블처럼 출력하고 싶을 때 사용
INNER JOIN (내부 조인):
테이블 A를 기준으로 테이블 B를 합하여 생성
(기본구조)
SELECT * FROM TABLE A
LEFT JOIN TABLE B
ON A.key = B.key
모든 사용자와 그들이 만든 주문을 조회합니다. 사용자와 주문 테이블 간의
user_id
를 기준으로 매칭된 행을 반환합니다.SELECT * FROM users
INNER JOIN orders ON users.user_id = orders.user_id;
LEFT JOIN (왼쪽 조인):
사용자 테이블의 모든 행을 포함하고, 주문 테이블과 매칭되는 경우 해당 주문 정보를 포함합니다.
SELECT * FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;
3. RIGHT JOIN (오른쪽 조인):
주문 테이블의 모든 행을 포함하고, 사용자 테이블과 매칭되는 경우 해당 사용자 정보를 포함합니다.
SELECT * FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;
FULL JOIN ():
두 테이블의 모든 행을 포함하며, 매칭되지 않는 경우
NULL
로 채워집니다. MySQL에서는 FULL JOIN을 기본적으로 지원하지 않으므로 UNION
을 사용하여 구현합니다.SELECT * FROM users
LEFT JOIN orders ON users.user_id = orders.user_id
UNION
SELECT *
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;
테이블의 데이터만 삭제 - TRUNCATE
TRUNCATE TABLE users;
