SQL DDL (Data Definition Language)
DATABASE
CREATE DATABASE: 데이터베이스 생성
CREATE DATABASE dbname;SHOW DATABASES: 데이터베이스 목록 보기
SHOW DATABASES;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)USE: 데이터베이스 사용
USE dbname;DROP DATABASE: 데이터베이스 삭제
IF EXISTS: 특정 명칭의 데이터베이스가 존재한다면 명령 실행 (특정 명칭의 데이터베이스가 존재하지 않을 때 오류 발생을 방지함)
DROP DATABASE [IF EXISTS] dbname;TABLE
CREATE TABLE: 테이블 생성
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
);SHOW TABLES: 테이블 목록 보기
USE mysql;
SHOW TABLES;+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version |
| replication_group_member_actions |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
38 rows in set (0.00 sec)DESC: 테이블 구조 설명
USE mysql; -- mysql : 데이터베이스명
DESC db; -- db : 테이블명+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.01 sec)ALTER TABLE: 테이블 구조 수정
ADD COLUMN: 테이블에 새로운 컬럼 추가
ALTER TABLE table_name
ADD COLUMN column_name datatype;NOT NULL: NULL 값을 허용하지 않음
ALTER TABLE mytable
ADD COLUMN model_type varchar(10) NOT NULL;MODIFY COLUMN: 테이블 컬럼 타입 변경
ALTER TABLE mytable
MODIFY COLUMN name varchar(20) NOT NULL;DROP COLUMN: 테이블 컬럼 삭제
ALTER TABLE mytable
DROP COLUMN series;SQL DML (Data Manipulation Language)
DML의 주 기능 : CRUD (Create, Read, Update, Delete)
생성 (Create)
INSERT INTO…VALUES: 테이블 전체 컬럼에 대응하는 값 삽입
INSERT INTO mytable VALUES(1, 'i7', '7700', 'Kaby Lake');
/* VALUES 안의 값은 컬럼 순서대로 각 컬럼에 해당하는 데이터형과 제약조건을 만족해야 에러 없이 데이터 값 삽입 가능 */
INSERT INTO mytable (name, model_num, model_type)
VALUES('i7', '7700K', 'Kaby Lake');
/* 특정 컬럼에만 데이터값 삽입 가능 */읽기 (Read)
SELECT...FROM: 특정 테이블의 특정 데이터를 선택해서 불러오기
SELECT * FROM table_name; -- 테이블 전체 컬럼의 데이터 모두 읽기
SELECT name, model_num FROM mytable; -- 테이블 특정 컬럼의 데이터만 읽기COUNT(): 컬럼값의 개수
SUM(): 컬럼값의 합계
AVG(): 컬럼값의 평균
MAX(): 컬럼값의 최대값
MIN(): 컬럼값의 최소값
AS: 컬럼명 변경
SELECT CountryCode,
COUNT(*) AS total
FROM city
GROUP BY CountryCode;JOIN: 두 개 이상의 테이블로부터 필요한 데이터를 연결해 하나의 포괄적인 구조로 결합시키는 연산
INNER JOIN…ON: 조인하는 테이블의ON절의 조건이 일지하는 결과만 출력
/* 전체 베스트상품(ALL 메인 카테고리)에서 판매자별 베스트상품 갯수 출력해보기 */
SELECT i.provider, COUNT(*) -- provider 컬럼이 items 테이블에 유일하기 때문에 생략 가능
FROM items AS i -- AS 생략 가능
INNER JOIN ranking AS r -- AS 생략 가능
ON r.item_code = i.item_code
WHERE r.main_category = "ALL" -- main_category 컬럼이 ranking 테이블에 유일하기 때문에 생략 가능
GROUP BY i.provider -- provider 컬럼이 items 테이블에 유일하기 때문에 생략 가능
ORDER BY COUNT(*) DESC;OUTER JOIN: 조인하는 테이블의ON절의 조건 중 한쪽의 데이터를 모두 가져옴
LEFT OUTER JOIN
RIGHT OUTER JOIN
- EXPLICIT JOIN : 일반적인 JOIN 사용 방법
SELECT *
FROM items
JOIN ranking ON items.item_code = ranking.item_code;- IMPLICIT JOIN :
JOIN선언 없이,로 테이블을 불러오는 방법 (파생 문법)
SELECT *
FROM items, ranking
WHERE items.item_code = ranking.item_code;WHERE: 조건에 맞는 데이터만 검색하기
SELECT * FROM mytable WHERE id < 2; -- 비교
SELECT * FROM mytable WHERE id > 0 OR id < 2; -- 논리 연산자 OR
SELECT * FROM mytable WHERE id = 1 AND name = 'i7'; -- 논리 연산자 AND
SELECT * FROM 테이블명 WHERE 필드명 LIKE '홍%'; -- 홍으로 시작되는 값을 모두 찾을 경우
SELECT * FROM 테이블명 WHERE 필드명 LIKE '%홍%'; -- 홍이 들어간 값을 모두 찾을 경우
SELECT * FROM 테이블명 WHERE 필드명 LIKE '홍__'; -- 홍으로 시작되고 뒤에 2글자가 붙을 경우GROUP BY: 특정 컬럼값을 기반으로 그룹화
SELECT COUNT(*) FROM film WHERE 조건문 GROUP BY rating;
/* 특정 조건에 맞는 데이터 중 rating 값 종류별로, 몇 개의 데이터가 있는지를 확인 */HAVING: 집계함수를 가지고 조건비교를 할 때 사용 (GROUP BY와 함께 사용)
WHERE조건 적용 후GROUP BY로 그룹핑 후HAVING으로 그룹핑된 테이블에서 또 다른 조건 적용
SELECT provider, COUNT(*)
FROM items
WHERE provider != '스마일배송' -- 스마일배송은 제외
GROUP BY provider -- 판매처별로 그룹
HAVING COUNT(*) > 100 -- 베스트상품이 100개 이상 등록된 경우만 검색
ORDER BY COUNT(*) DESC; -- 베스트상품 등록갯수 순으로 검색ORDER BY: 데이터 정렬 기준 설정
ORDER BY column_name DESC|ASC -- 아무것도 안쓰면 오름차순 정렬LIMIT: 결과 중 일부만 데이터 불러오기
SELECT * FROM 테이블명 LIMIT 10; -- 결과의 앞 10개만 불러오기
SELECT * FROM 테이블명 LIMIT 100, 10; -- 결과중 100번째부터, 10개만 가져오기- SQL 조건 순서
SELECT 컬럼 AS 컬럼명
FROM 테이블명
WHERE 조건
GROUP BY 컬럼
ORDER BY
LIMITCTEs (WITH 절)
- CTEs (Common Table Expressions) : 일시적인 결과 세트를 만들어
SELECT,INSERT,UPDATE,DELETE문에서 참조할 수 있게 해줌
- 단일 SQL 쿼리 내에서만 사용 가능
- SQL 쿼리가 종료되면 자동 삭제됨
- 복잡한 쿼리를 쉽게 분해할 수 있음
WITH cte_name AS (
-- SQL 쿼리
)
-- CTE를 사용하는 메인 쿼리
-- 재고에도 있는 모든 영화의 이름 가져오기
WITH FilmInventory AS (
SELECT DISTINCT film_id FROM inventory
)
SELECT f.film_id, f.title
FROM film f
JOIN FilmInventory fi ON f.film_id = fi.film_id;CASE WHEN 절
- SQL 쿼리에 조건 로직 추가 가능
IF … ELSE문과 비슷한 역할
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END갱신 (Update)
UPDATE...SET: 데이터 수정 (보통WHERE조건문과 함께 쓰임)
UPDATE 테이블명
SET 수정하고 싶은 컬럼명 = '수정하고 싶은 값'
WHERE 특정 컬럼 = '값';
/* 다수 컬럼 값 수정 가능 */
UPDATE 테이블명
SET 수정하고 싶은 컬럼명1 = '수정하고 싶은 값',
수정하고 싶은 컬럼명2 = '수정하고 싶은 값',
수정하고 싶은 컬럼명3 = '수정하고 싶은 값'
WHERE 특정 컬럼 < '값';삭제 (Delete)
DELETE FROM: 테이블에 저장된 데이터 삭제 (보통WHERE조건문과 함께 쓰임)
DELETE FROM 테이블명
WHERE 특정 컬럼 = '값';
DELETE FROM 테이블명; -- 테이블에 저장된 모든 데이터 삭제 가능SQL DCL (Database Control Language)
mysql -u root -p
mysql> use mysql;
mysql> create user '만들고싶은ID'@'%' identified by '비밀번호'; # 사용자 추가
mysql> select host, user from user;
mysql> SET PASSWORD FOR '만들고싶은ID'@'%' = '신규비밀번호'; # 사용자 비밀번호 변경
mysql> exit;서브 쿼리 (SubQuery)
- SQL문 안에 포함되어 있는 SQL문
- SQL문 안에서
()괄호로 서브쿼리문 추가 가능
- 테이블과 테이블간의 검색시, 검색 범위(테이블 중 필요한 부분만 먼저 가져오도록)를 좁히는 기능에 주로 사용
- JOIN 대신 사용 가능 (대부분 서브쿼리 대신 JOIN 사용함)
/* 조인 사용 예시 */
SELECT title
FROM items
INNER JOIN ranking ON items.item_code = ranking.item_code
WHERE ranking.sub_category = '여성신발'
/* 서브쿼리 사용 예시 */
SELECT title
FROM items
WHERE item_code IN
(SELECT item_code FROM ranking WHERE sub_category = '여성신발')/* 조인 사용 예시 */
SELECT MAX(items.dis_price)
FROM items
INNER JOIN ranking ON items.item_code = ranking.item_code
WHERE ranking.sub_category = '여성신발'
/* 서브쿼리 사용 예시 */
SELECT MAX(dis_price)
FROM items
WHERE item_code IN
(SELECT item_code FROM ranking WHERE sub_category = '여성신발')- 비교연산자와 서브쿼리 함께 사용 가능
SELECT category_id, COUNT(*) AS film_count FROM film_category
WHERE film_category.category_id >
(SELECT category.category_id FROM category WHERE category.name = 'Comedy')
GROUP BY film_category.category_id- FROM 절에 서브쿼리
SELECT a, b, c
FROM (SELECT * FROM atoz_table)- GROUP BY 절 + HAVING 절의 서브 쿼리
-- 평균 결제 횟수보다 많은 결제를 한 고객
SELECT first_name, last_name
FROM customer
WHERE customer_id IN (
SELECT customer_id
FROM payment
GROUP BY customer_id
HAVING COUNT(*) > (
SELECT AVG(payment_count)
FROM (
SELECT COUNT(*) AS payment_count
FROM payment
GROUP BY customer_id
) AS payment_counts
)
);- 상관 서브쿼리 (Correlated Subquery)
-- 각 고객에 대해 자신이 결제한 평균 금액보다 큰 결제를 한 경우의 결제 정보
SELECT P.customer_id, P.amount, P.payment_date
FROM payment P
WHERE P.amount > ( -- 각 고객에 대해 자신이 결제한 평균 금액보다 큰 결제를 한 경우
SELECT AVG(amount) -- 각 결제에 대해 해당 결제를 한 고객의 평균 결제 금액을 계산
FROM payment
WHERE customer_id = P.customer_id
);인덱스 (Index)
- 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조
- 어떤 데이터를 인덱스로 만드느냐에 따라 방대한 데이터의 경우 성능에 큰 영향을 미칠 수 있음
SHOW INDEX: 인덱스 확인
SHOW INDEX FROM table;ALTER TABLE...ADD: 인덱스 추가
ALTER TABLE...DROP INDEX: 인덱스 삭제
ALTER TABLE userTbl DROP INDEX idx_userTbl_name;CONSTRAINT,UNIQUE: 테이블에 특정 컬럼에 중복값이 나오지 않도록 제약조건을 추가하기
ALTER TABLE userTbl ADD [CONSTRAINT TESTDate] UNIQUE(mDate);CREATE INDEX…ON: 인덱스 추가
CREATE INDEX idx_name ON userTbl (name);SQL 함수
문자열 함수
LENGTH(string): 문자열의 길이를 반환
SELECT title, LENGTH(title) AS title_length
FROM film
LIMIT 10;
SELECT title
FROM film
WHERE LENGTH(title) = 15;UPPER(string)/LOWER(string): 문자열을 대/소문자로 변환
SELECT UPPER(title) AS uppercased_title
FROM film
LIMIT 10;
SELECT LOWER(title) AS lowercased_title
FROM film
LIMIT 10;CONCAT(string1, string2, …): 두개 이상의 문자열을 하나로 연결
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM actor
LIMIT 10;
SELECT UPPER(CONCAT(first_name, ' ', last_name))
FROM actor
WHERE first_name = LOWER('john');SUBSTRING(string, start, length): 문자열에서 부분 문자열 추출
SELECT SUBSTRING(description, 1, 10) AS short_description
FROM film
LIMIT 10;
SELECT title
FROM film
WHERE SUBSTRING(description, 3, 6) = 'Action';GROUP_CONCAT(expression SEPARATOR separator_string): 그룹 내의 여러 행을 하나의 문자열로 결합
-- 각 고객이 대여한 영화 제목들을 쉼표로 구분된 하나의 문자열로 출력
SELECT
c.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ') AS rented_movies
FROM
customer c
JOIN rental r ON c.customer_id = r.customer_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
GROUP BY
c.customer_id, c.first_name, c.last_name;날짜/시간 함수
NOW(): 현재 날짜와 시간 반환
SELECT NOW() AS current_date_time;CURDATE(): 현재 날짜 반환
SELECT CURDATE() AS current_date;CURTIME(): 현재 시간 반환
SELECT CURTIME() AS current_time;DATE_ADD(date, INTERVAL unit): 날짜에 간격 추가
INTERVAL unit에 사용 가능한 단위 :YEAR,MONTH,DAY,HOUR,MINUTE,SECOND
/* rental 테이블에서 각 대여 시작 날짜(rental_date)에 7일을 추가 */
SELECT rental_date, DATE_ADD(rental_date, INTERVAL 7 DAY) AS return_date
FROM rental;
SELECT DATE_ADD(rental_date, INTERVAL 5 DAY)
FROM rental
WHERE rental_date >= '2006-01-01';DATE_SUB(date, INTERVAL unit): 날짜에 간격 제외
/* rental 테이블에서 각 대여 시작 날짜( rental_date )에서 7일을 제외 */
SELECT rental_date, DATE_SUB(rental_date, INTERVAL 7 DAY) AS earlier_date
FROM rental
LIMIT 10;EXTRACT(field FROM source): 날짜에서 특정 부분을 추출
ANSI SQL (표준 SQL)에 있는 함수이기 때문에 하위 버전 SQL과 호환 가능
SELECT EXTRACT(YEAR FROM '2024-10-17'::DATE) AS year; -- 연도 추출
SELECT EXTRACT(MONTH FROM '2024-10-17'::DATE) AS month; -- 월 추출
SELECT EXTRACT(DAY FROM '2024-10-17'::DATE) AS day; -- 일자 추출
SELECT EXTRACT(MINUTE FROM '14:30:00'::TIME) AS minute; -- 시간에서 분 추출
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS current_hour; -- 현재 시간에서 시 추출ANSI SQL (표준 SQL)에서 지원되지 않는 함수
YEAR(date): 날짜에서 연도 추출
MONTH(date): 날짜에서 월 추출
DAY(date): 날짜에서 일자 추출
SELECT YEAR(payment_date) AS payment_year,
MONTH(payment_date) AS payment_month,
DAY(payment_date) AS payment_day
FROM payment;HOUR(time): 시간에서 시 추출
MINUTE(time): 시간에서 분 추출
SECOND(time): 시간에서 초 추출
SELECT HOUR(payment_date) AS payment_hour,
MINUTE(payment_date) AS payment_minute,
SECOND(payment_date) AS payment_second
FROM payment;WEEK(date): 날짜가 한 해의 몇번째 주에 해당하는지를 반환
SELECT WEEK('2024-10-17') AS week_number;DAYOFWEEK(date): 날짜가 그 주의 몇 번째 요일인지 숫자로 반환 (1=일요일, 7=토요일)
SELECT DAYOFWEEK('2024-10-17') AS day_of_week;TIMESTAMPDIFF(unit, start_datetime, end_datetime): 두 날짜/시간 값 사이의 차이를 계산
SELECT TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
FROM rental
LIMIT 5; -- 대여와 반납 사이의 일수 차이
SELECT TIMESTAMPDIFF(HOUR, rental_date, return_date) AS rental_hours
FROM rental
LIMIT 5; -- 대여와 반납 사이의 시간 차이DATE_FORMAT(date, format): 날짜/시간을 특정 형식의 문자열로 변환
- 날짜/시간 형식 지시자
%Y: 연도 4자리 (2024)
%y: 연도 2자리 (24)
%M: 영문 월 이름 (January ~ December)
%m: 월 2자리 (01 ~ 12)
%c: 월 1~2자리 (1 ~ 12)
%D: 일 1~2자리 + 영문 접미사 (1st ~ 31st)
%d: 일 2자리 (01 ~ 31)
%H: 시 2자리 24시간 형식 (00 ~ 23)
%h: 시 2자리 12시간 형식 (01 ~ 12)
%l: 시 1~2자리 (1 ~ 12)
%i: 분 2자리 (00 ~ 59)
%s: 초 2자리 (00~59)
SELECT rental_id,
DATE_FORMAT(rental_date, '%Y-%m-%d') AS formatted_rental_date
FROM rental
LIMIT 5; -- 대여 날짜를 'YYYY-MM-DD' 형식으로 변환숫자 함수
ABS(number): 절대값 반환
CEIL(number): 숫자를 올림하여 가장 가까운 정수로 반환
FLOOR(number): 숫자를 내림하여 가장 가까운 정수로 반환
ROUND(number, decimals): 숫자를 특정 소수점 자리수로 반올림
SQRT(number): 숫자의 제곱근 반환
집합 연산
UNION: 합집합 — 두 결과 집합을 결합 (중복값 제외)
UNION ALL: 합집합 — 두 결과 집합을 결합 (중복값 포함)
INTERSECT: 교집합 — 두 결과 집합 간의 공통된 부분
EXCEPT: 차집합 — 첫 번째 결과 집합에서 두 번째 결과 집합을 제외한 부분
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;
SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;트랜잭션 제어
- 트랜잭션 (transaction)
- 하나 이상의 SQL 문을 포함하는 작업의 논리적 단위
- 그리고 그 단일 논리적 작업 단위로 수행되는 연산의 순서
- ACID — 네 가지 특성 (원자성, 일관성, 고립성, 지속성)
COMMIT: 현재 트랜잭션에서 만든 모든 변경 사항을 저장하는 데 사용됨
ROLLBACK: 현재 트랜잭션에서 만든 일부 또는 모든 변경 사항을 취소함
VIEW
- 뷰 (View)
- 실제 테이블을 기반으로 한 가상 테이블
- 복잡한 쿼리를 단순화하고 데이터의 특정 부분에만 접근을 허용함
CREATE VIEW … AS: 뷰 생성
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;CREATE OR REPLACE VIEW … AS: 뷰 수정
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;DROP VIEW: 뷰 삭제
DROP VIEW view_name;'데이터 분석 부트캠프 > 주간학습일지' 카테고리의 다른 글
| [패스트캠퍼스] 데이터 분석 부트캠프 16기 13주차 - Tableau (2) | 2024.11.15 |
|---|---|
| [패스트캠퍼스] 데이터 분석 부트캠프 16기 12주차 - 생성형 AI 활용 (3) | 2024.11.07 |
| [패스트캠퍼스] 데이터 분석 부트캠프 16기 8주차 - SQL 기본 이론 (3) | 2024.10.11 |
| [패스트캠퍼스] 데이터 분석 부트캠프 16기 5주차 - Python을 활용한 데이터 시각화 (NumPy / Pandas / MatPlotLib / Seaborn) (3) | 2024.09.20 |
| [패스트캠퍼스] 데이터 분석 부트캠프 16기 4주차 - Python의 Selenium을 활용한 데이터 크롤링 (6) | 2024.09.13 |