- 데이터베이스 : row & coulmn으로 이뤄진 table
DBMS
: 데이터베이스 관리시스템SQL
: DBMS에 명령을 내리기 위해 사용하는 언어 (My SQL 사용예정)- My SQL : 페이스북, 유튜브 등을 비롯한 유명한 서비스에서도 활발히 사용되고 있는 DBMS (오픈소스)
- DBMS는 주요 구성요소 2종류의 프로그램 有 : client를 통해 server에 접속해 원하는 명령을 내린다.
client(클라이언트)
: 사용자가 server에 접속해서 원하는 데이터베이스 관련 작업을 할 수 있도록, SQL을 입력할 수 있는 화면 등을 제공하는 프로그램server(서버)
: client로부터 SQL 문 등을 전달받아 데이터베이스 관련 작업을 직접 처리하는 프로그램
- MySQL에서 서버 프로그램 이름 = 클라이언트 프로그램 이름 = ‘mysqld’ → 보통 CLI 환경에서 사용하지만 GUI환경에서도 가능.
- Oracle이 공식적으로 제공하는 GUI환경 : MySQL Workbench
- sys : MySQL 서버의 성능 관련 정보들을 갖고있는 데이터베이스
- 데이터 베이스를 만들어 주는 구문
create database copang_main
primary key(기본키)
: 테이블에서 하나의 ROW를 고유하게 식별할 수 있도록 해주는 columnNatural Key
: 개체가 갖고 있는속성을 나타내는 컬럼이 Primary Key가 됐을 때
Surrogate Key
: 개체의 실제 속성은 아니지만Primary Key로 쓰기 위해 추가한 컬럼
(주로 1부터 순차적으로 증가하는 숫자)
ㄴ Auto Increment(AL) = 이전보다 1이 더 큰 정수를 자동으로 넣어주는 기능
NOT NULL
= NULL이 아니다. (NULL=값이 존재하지 않는 상태) = primary key는NN
이어야 한다.
SELECT * FROM copang_main.member; # "*" 는 asterisk로 all coulumn
SELECT email, age, address FROM copang_main.member; # "FROM"은 ~로 부터
SELECT * FROM copang_main.member where EMAIL = '[email protected]';
SELECT * FROM copang_main.member where age >= 27;
SELECT * FROM copang_main.member where age BETWEEN 30 AND 39; # 30, 39 포함 그 사이 숫자
SELECT * FROM copang_main.member where age NOT BETWEEN 30 AND 39;
SELECT * FROM copang_main.member where sign_up_day > '2019-01-01';
SELECT * FROM copang_main.member where address LIKE '서울%'; # LIKE 처럼
SELECT * FROM copang_main.member where address LIKE '%고양%';
SELECT * FROM copang_main.member where gender != 'm'; # != 같지 않음
SELECT * FROM copang_main.member where gender <> 'm'; # <> 같지 않음
SELECT * FROM copang_main.member where age in (20, 30); # IN 이 중에 있는
SELECT * FROM copang_main.member where email LIKE 'c_____@%' # LIKE 뒤의 언더바 하나=문자 하나
SELECT * FROM copang_main.member where year(birthday) = '1992'; # 1992년에 태어난 회원만 조회
SELECT * FROM copang_main.member WHERE MONTH(sign_up_day) IN (6,7,8); # 여름(6, 7, 8월)에 가입한 회원들만 조회하기
SELECT * FROM copang_main.member WHERE DAYOFMONTH(sign_up_day) BETWEEN 15 AND 31; # 각 달의 후반부(15일~31일)에 가입했던 회원들만 조회하기
SELECT email, sign_up_day, DATEDIFF(sign_up_day, '2019-01-01') FROM copang_main.member; # 2019년 1월 1일~sign_up_day 차이 일 수
SELECT email, sign_up_day, CURDATE(), DATEDIFF(sign_up_day, CURDATE()) FROM copang_main.member; # 오늘 날짜 기준
SELECT email, sign_up_day, DATEDIFF(sign_up_day, birthday) /365 FROM copang_main.member; # 몇살이었을 때 가입?
SELECT email, sign_up_day, date_add(sign_up_day, INTERVAL 300 DAY) FROM copang_main.member; # 가입일(sign_up_day) 기준으로 300일 이후의 날짜
SELECT email, sign_up_day, DATE_SUB(sign_up_day, INTERVAL 250 DAY) FROM copang_main.member; # 가입일(sign_up_day) 기준 250일 이전의 날짜
SELECT email, sign_up_day, from_unixtime(UNIX_TIMESTAMP(sign_up_day)) FROM copang_main.member; # DATE 타입의 값을 Unix Timestamp로 바꿔줌
SELECT * FROM copang_main.member
WHERE gender = 'm' AND address LIKE '서울%' AND age BETWEEN 25 and 29;
SELECT * FROM copang_main.member
WHERE (gender = 'm' AND height >= 180) OR (gender = 'f' AND height >= 170)
- OR를 사용할 때의 주의사항 - WHERE id = 1 OR id = 2 (O)
- AND와 OR 간의 우선순위 : AND가 우선 순위 이므로, 괄호를 꼭 쳐주자
- 이스케이핑(escaping) 문제 : \ 후 기재
- 대소문자 구분 문제 : BINARY '%g%' 소문자만 조회
SELECT * FROM sales ORDER BY YEAR(sign_up_day) DESC, email ASC; # 이름을 먼저 쓴 컬럼을 우선으로 해서 정렬이 차례대로 수행
SELECT * FROM sales ORDER BY CAST(registration_num AS signed); # 일시적으로 다른 데이터 타입으로 변경할 수 있게 해주는 함수
SELECT * FROM db.search_result ~ ORDER BY registration_date DESC LIMIT 0, 10
SELECT COUNT(email) FROM copang_main.member; # 갯수
SELECT MAX(height) FROM copang_main.member; # 최대 값
SELECT MIN(weight) FROM copang_main.member; # 최소 값
SELECT AVG(weight) FROM copang_main.member; # 평균 값
SELECT SUM(weight) FROM copang_main.member; # 합계
SELECT STD(weight) FROM copang_main.member; # 표준편차
SELECT ABS(weight) FROM copang_main.member; # 절대값
SELECT SQRT(weight) FROM copang_main.member; # 제곱근
SELECT CEIL(weight) FROM copang_main.member; # 올림
SELECT FLOOR(weight) FROM copang_main.member; # 내림
SELECT ROUND(weight) FROM copang_main.member; # 반올림
SELECT * FROM copang_main.member WHERE address IS NULL; # NULL 값 조회
SELECT * FROM copang_main.member WHERE address IS not NULL;# NULL 아닌 값 조회
WHERE height IS NULL OR weight IS NULL OR address IS NULL;
SELECT COALESCE(height, '####'), COALESCE(weight, '---'), COALESCE(address, '@@@')
FROM copang_main.member; # COALESCE 는 결합한다는 뜻으로, NULL인 값을 채워넣어준다.
SELECT AVG(age) FROM copang_main.member WHERE age between 5 and 100;
SELECT * FROM copang_main.member WHERE address NOT LIKE '%호';
SELECT * FROM copang_main.member WHERE address LIKE '%호';
SELECT
email,
CONCAT(height, 'cm', ',', weight, 'kg') AS '키와 몸무게',
weight / ((height/100) * (height/100)) AS BMI,
(CASE
WHEN weight IS NULL OR height IS NULL THEN '비만 여부 알 수 없음'
WHEN weight/((height/100) * (height/100)) >= 25 THEN '과체중 또는 비만'
WHEN weight/((height/100) * (height/100)) >= 18.5
AND weight/((height/100) * (height/100)) < 25 THEN '정상'
ELSE '저체중'
END) AS obesity_check
FROM copang_main.member;
SELECT COALESCE(height, 'N/A') FROM copang_main.member;
SELECT COALESCE(height, weight * 2.3, 'N/A') FROM copang_main.member; # NULL이면 weight *2.3, 그래도 NULL이면 'N/A'
SELECT IFNULL(height, 'N/A') FROM copang_main.member; IF NULL이면, 'N/A'로 표시
SELECT IF(height IS NOT NULL, height, 'N/A') FROM copang_main.member; # height가 not null이면, height 반환 아니면 'N/A'
SELECT CASE WHEN height IS NOT NULL THEN height ELSE 'N/A' END FROM copang_main.member;
SELECT DISTINCT(gender) FROM copang_main.member;
SELECT DISTINCT(SUBSTRING(address, 1, 2)) FROM copang_main.member; # 첫번째 시작 2개만큼
SELECT *, length(address) FROM copang_main.member;
SELECT email, upper(email), lower(email) FROM copang_main.member;
SELECT email, LPAD(age, 10, '0'), RPAD(age, 10, '!') FROM copang_main.member;
SELECT SUBSTRING(address, 1,2) AS region, gender, COUNT(*)
FROM copang_main.member
GROUP BY substring(address, 1, 2), gender
WITH ROLLUP HAVING region IS NOT NULL
ORDER BY region ASC, gender DESC;
SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT
- FROM : 어느 테이블을 대상으로 할 것인지를 먼저 결정합니다.
- WHERE : 해당 테이블에서 특정 조건(들)을 만족하는 row들만 선별합니다.
- GROUP BY : row들을 그루핑 기준대로 그루핑합니다. 하나의 그룹은 하나의 row로 표현됩니다.
- HAVING : 그루핑 작업 후 생성된 여러 그룹들 중에서, 특정 조건(들)을 만족하는 그룹들만 선별합니다.
- SELECT : 모든 컬럼 또는 특정 컬럼들을 조회합니다. SELECT 절에서 컬럼 이름에 alias를 붙인 게 있다면, 이 이후 단계(ORDER BY, LIMIT)부터는 해당 alias를 사용할 수 있습니다.
- ORDER BY : 각 row를 특정 기준에 따라서 정렬합니다.
- LIMIT : 이전 단계까지 조회된 row들 중 일부 row들만을 추립니다.
Foreign Key
: '다른 테이블의 특정 row를 식별할 수 있게 해주는 컬럼'
ㄴ stock table 에서 item의 item_id 를 referenced Table로 지정해 준다.
SELECT i.id,
i.name,
s.item_id,
s.inventory_count
# FROM item AS i RIGHT OUTER JOIN stock # 오른쪽 기준 조회
# FROM item LEFT OUTER JOIN stock # 왼쪽 기준 조회
FROM item AS i INNER JOIN stock AS s # 둘다 있는 것 조회
ON i.id = s.item_id;
SELECT * FROM member_A
INTERSECT # A ∩ B (INTERSECT 연산자 사용)
MINUS # A - B (MINUS 연산자 또는 EXCEPT 연산자 사용)
UNION # A U B (UNION 연산자 사용) OR UNION ALL (열이 생략되는 경우도 있기 때문)
SELECT * FROM member_B
ON old.id = new.id 와 USING(id)의 의미는 같다.
SELECT
YEAR(i.registration_date) as '등록 연도',
COUNT(*) as '리뷰 개수',
AVG(r.star) as '별점 평균값'
FROM
review as r
INNER JOIN item as i
ON r.item_id = i.id
INNER JOIN member as m
ON r.mem_id = m.id
WHERE i.gender = 'u'
GROUP BY YEAR(i.registration_date)
HAVING COUNT(*) >= 10
ORDER BY AVG(r.star) DESC;
- NATURAL JOIN - 같은 이름의 컬럼을 찾아서 자동으로 그것들을 조인 조건을 설정하고, INNER JOIN을 해주는 조인
- CROSS JOIN - 두 테이블의 Cartesian Product를 구하는 조인
- SELF JOIN - 테이블이 자기 자신과 조인을 하는 경우. 같은 테이블을 마치 별도의 테이블인 것처럼 간주하고 진행된다는 점
- FULL OUTER JOIN - LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과를 합치는 조인
- Non-Equi JOIN - 동등 조건이 아닌 다른 종류의 조건
SELECT i.id, i.name, AVG(star) AS avg_star
FROM
item as i LEFT OUTER JOIN review as r
ON r.item_id = i.id
GROUP BY i.id, i.name
HAVING avg_star < (SELECT AVG(star) FROM review) # 서브쿼리
ORDER BY avg_star DESC;
SELECT * FROM item
WHERE id IN
(
SELECT item_id
FROM review
GROUP BY item_id HAVING COUNT(*) >=3 # WHERE 절에 있는 서브쿼리
);
- ANY=SOME의 의미 - 하나라도 조건을 만족하는 경우가 있으면 True를 리턴한다
- ALL의 의미 - 모든 경우에 대해서 해당 조건이 성립해야 True를 리턴
SELECT AVG(review_count)
FROM
(SELECT SUBSTRING(address, 1, 2) AS region,
COUNT(*) AS review_count
FROM review AS r LEFT OUTER JOIN member AS m
ON r.mem_id = m.id
GROUP BY SUBSTRING(address, 1, 2)
HAVING region IS NOT NULL
AND region != '안드') AS review_count_summary;
- 비상관(Non_Correlated) 서브쿼리 : outer query와 상관 관계가 없는 서브쿼리
- 상관(Correlated) 서브쿼리 : outer query와 상관 관계가 있는 서브쿼리 (단독으로 실행 불가)
SELECT *,
(SELECT MIN(height) # 해당 row(의/들의) height 컬럼의 최솟값
FROM member AS m2 WHERE birthday IS NOT NULL AND height IS NOT NULL # birthday & height 컬럼 둘다 값이 있는 회원들만
AND YEAR(m1.birthday) = YEAR(m2.birthday)) # 같은 YEAR(birthday) ('생일연도') 값을 가진 row(를/들을)
AS min_height_in_the_year
FROM member AS m1
ORDER BY min_height_in_the_year ASC;
SELECT MAX(copang_report.price) AS max_price,
AVG(copang_report.star) AS avg_star,
COUNT(DISTINCT(copang_report.email)) AS distinct_email_count
FROM (SELECT price, star, email
FROM item AS i INNER JOIN review AS r ON r.item_id = i.id
INNER JOIN member AS m ON r.mem_id = m.id)
AS copang_report;
SELECT email, BMI,
(CASE
WHEN weight IS NULL OR height IS NULL THEN '비만 여부 알 수 없음'
WHEN BMI >= 25 THEN '과체중 또는 비만'
WHEN BMI >= 18.5
AND BMI < 25 THEN '정상'
ELSE '저체중'
END) AS obesity_check
FROM (SELECT *, weight/((height/100)*(height/100)) AS BMI FROM member) AS subquery_for_BMI
ORDER BY obesity_check ASC;
CREATE VIEW three_tables_joined AS # 뷰 생성
SELECT i.id, i.name, AVG(star) AS avg_star, COUNT(*) AS count_star
FROM item AS i LEFT OUTER JOIN review AS r on r.item_id = i.id
LEFT OUTER JOIN member AS m ON r.mem_id = m.id
WHERE m.gender = 'f'
GROUP BY i.id, i.name
HAVING COUNT(*) >= 2
ORDER BY AVG(star) DESC, COUNT(*) DESC;
SELECT * FROM copang_main.three_tables_joined
WHERE avg_star = (
SELECT MAX(avg_star) FROM copang_main.three_tables_joined
) AND count_star = (
SELECT MAX(count_star) FROM copang_main.three_tables_joined
);
CREATE VIEW emp_view2 AS SELECT id, name, age, department FROM employee WHERE department != 'secret'; # 부서는 미조회 되게
CREATE VIEW v_emp AS SELECT id, name, age, department, phone_num, hire_date FROM employee
SELECT * FROM v_emp;
- 존재하는 데이터 베이스 확인
SHOW DATABASES;
- 한 데이터베이스 안의 테이블(뷰도 포함)들 파악
SHOW FULL TABLES IN copang_main;
- 한 테이블의 컬럼 구조 파악
DESCRIBE item;
- Foreign Key(외래키) 파악 : 해당 회사의 데이터베이스를 설계한 분의 설명 or 본인이 직접 데이터의 관계 및 흐름을 파악