SQL(structured query language): 데이터베이스 조작에 필요한 언어
데이터베이스의 종류
일반적인 용도로는
- Relational Database
- 정확도와 일관성이 중요하다면 relational db가 일반적
- Document Database
- 입, 출력이 매우 잦으면 document db가 일반적
1. 테이블 만들기 & 데이터 타입
database라는걸 만들고 그 안에 여러개의 table을 보관한다.
database는 일종의 '폴더'이고, tabled은 '파일'이다.
table 안에 원하는 데이터 엑셀처럼 작성하면 된다.
1-1.
터미널 창에
cd /usr/local/mysql/bin./mysql -uroot -p명령어를 입력하고, MySQL 서버를 활성화 시키자.
1-2.
Database 생성하기!
1-3.
table 생성하기!
- table에 어떤 column이 들어갈지 미리 작성해야함
- coulumn은 세로 줄을 의미
1-4. 데이터 타입의 종류
문자
- 일반적으로 varchar(100) 사용, 괄호 안 숫자는 '지정할 최대 글자수'
숫자
- 일반적으로 int를 사용하고, 오차없이 소수점을 저장하고 싶으면 decimal을 사용하면 된다.
날짜, 시간
2. 데이터 출력하고 정렬하는 법
SQL(structured query language): 데이터베이스 조작에 필요한 언어
- 데이터 삽입
- 데이터 출력
- 데이터 삭제
- 데이터 수정
- 조건문, 반복문, 함수, 변수 등 프로그래밍 기능
2-1. 데이터를 출력하려면 SQL언어를 이용해서 코드 작성
- selelct * from 테이블명
- '모두 실행 버튼' 클릭
- sql문법은 빨간색으로 표기됨
- select 컬럼명 from 테이블명 order by 컬럼명 asc / desc
- asc 오름차순, desc 내림차순
3. WHERE로 데이터 필터링하기
csv파일 테이블에 불러오기, 참고사항
파일 내에 한글이 있으면, 인코딩에 euc-kr을 입력해야함
where문법을 언제 써야할까?
- 원하는 행에 필터를 주고 싶을 때
SELECT 컬럼명 FROM 테이블명 WHERE 조건식SELECT * FROM product WHERE 카테고리 = '가구'
SELECT * FROM product WHERE 가격 BETWEEN 5000 AND 8000 - 가격이 5000이상 8000이하를 필터링 하고 싶을 때
4. WHERE 뒤에 조건식을 여러개 쓰려면
SELECT * FROM product
WHERE 카테고리 = '가구' AND 가격 = 5000; - enter키를 자유롭게 사용가능, 조건식 마지막엔 세미콜론을 꼭 붙여주자
- AND, OR, NOT을 붙일 수 있다.
SELECT * FROM product
WHERE NOT 카테고리 = '가구';- 카테고리에 가구만 제외하고 출력해달라는 뜻
SELECT * FROM product
WHERE (카테고리 = '가구' OR 카테고리 = '옷') AND 가격 = 5000;- 괄호 안에 것들을 우선적으로 계산
- 카테고리가 '가구' 거나 '옷'인 것들 중, 가격이 5000원인 상품 출력
SELECT * FROM product
WHERE 카테고리 IN ('신발', '가전, '식품');- 컬럼명 안에 value들을 전부 출력해줌
결론
-
SELECT FROM 뒤에 WHERE 조건식을 붙여서 필터링할 수 있다.
-
조건식란엔 > , < , = , != , >= , <= 전부 이용 가능하다.
-
조건식 여러 개가 필요하면 AND, OR 이런 걸로 이어붙일 수 있다.
-
괄호로 AND, OR 사용한 부분을 묶을 수도 있다.
-
OR 조건식 여러 개 필요하면 IN() 사용해도 될 때가 있다.
5. LIKE, %, _ 연산자로 간단하게 검색가능
SELECT * FROM product
WHERE 상품명 LIKE '%소파%' ;- % 기호는 아무글자(와일드카드)라는 뜻
- '나무'라는 이름을 가진 상품명 전부 출력
주의사항
CHAR(6) 데이터타입의 경우 총 6자를 저장할 수 있는데
4자만 입력해도 나머지 뒷부분 2자를 공백으로 꽉 채워줍니다.
예를 들어서 '가죽소파'를 저장해도 '가죽소파_ _' 이렇게 저장됩니다.
그래서 CHAR 컬럼은 % 써서 검색시 의도와 다르게 동작할 수 있습니다.
'%소파' 이렇게 검색해도 '가죽소파_ _' 이건 못찾음
- 찾으려면 '%소파%' 해야 될듯
- 위와 같은 이유로 일반적으로는 VARCHAR 컬럼에 주로 씀
SELECT * FROM newtable WHERE 상품명 LIKE '%소파%' OR 상품명 LIKE '%chair%' ;- 상품명 컬럼 안에 '소파'가 붙은 문자를 전부 찾고,
상품명 컬럼 안에 'chair'이 붙은 문자도 전부 찾아라
SELECT * FROM newtable WHERE 상품명 LIKE '%소파%' AND NOT 상품명 LIKE '%나무%' - 상품명 컬럼 안에 '소파'가 붙은 문자를 전부 찾고,
상품명 컬럼 안에 '나무'는 미포함
6. MIN, MAX, AVG, SUM 집계함수로 통계내기
카드 테이블 
SELECT MAX(사용금액) FROM card // 최댓값
SELECT MIN(사용금액) FROM card // 최솟값
SELECT MAX(사용금액), MIN(사용금액) FROM card - select 뒤에 컬럼 두 개도 쓸 수 있음
SELECT AVG(연체횟수) FROM card // 평균값SELECT SUM(사용금액) FROM card // 전체합SELECT COUNT(사용금액) FROM card - 몇 개의 행이 있는지 카운팅 할 수 있다.
SELECT MAX(사용금액) AS 최대사용금액 FROM card 
- 컬럼명 뒤에 AS 원하는단어 사용하면, 컬럼명을 원하는 단어로 바꿔줌
SELECT DISTINCT 연체횟수 FROM card - 연체횟수의 중복 된 값을 distinct가 제거해줌
SELECT * FROM card ORDER BY 사용금액 DESC LIMIT 1; - 정렬을 사용해서 최댓값을 구할 수도 있다.
- limit 1는 맨 위의 하나의 요소만 출력
문제
Q3. 연체횟수가 1회 이하인 사람은 몇명일까요?
님이 직접 하나하나 세지 말고 컴퓨터보고 출력해달라고 해봅시다.
SELECT COUNT(연체횟수) FROM card
WHERE 연체횟수 <= 1;7. 컬럼 출력시 사칙연산 넣기 & 문자다루는 함수
SELECT CONCAT(고객명, ' is ', 사용금액) FROM card -
컬럼명, 컬럼명만 해도 붙여줌
-
CONCAT() 안에 여러 문자들을 집어넣을 수 있다.
-
컬럼이름말고 직접 문자 입력해도 합쳐준다.
-
숫자 입력해도 문자처럼 합쳐준다.
SELECT TRIM(컬럼명) FROM 어쩌구 // 문자 데이터의 좌우 공백 제거
SELECT REPLACE(컬럼명, ' ', '') FROM 테이블명 // 응용SELECT REPLACE (고객등급, '패', '훼') FROM card
// 고객등급 컬럼에 '패' 라는 글자를 '훼' 로 변경
SELECT REPLACE('서울에사는 서울맨', '서울', '경기')
// REPLACE(바꿀문자, 이걸찾아서, 이걸로바꾸셈) - 원하는 단어를 다른 단어로 전부 바꿀 수 있다.
SELECT SUBSTR('abcdef', 3, 2)
// SUBSTR(문자, 몇번째부터, 몇자)- 원하는 문자만 뽑아낼 수 있다.
- 3번째 부터 2개의 글자를 가져옴
- 'cd'만 출력
SELECT INSERT('test@naver.com', 1, 4, 'hello')
// INSERT(바꿀문자, 몇번째부터, 몇자를, 이걸로바꾸셈)- 문자의 일부를 다른 단어로 교체할 수 있다.
- hello@naver.com 출력
8. 숫자 조작하는 SQL 함수들
SELECT GREATEST();
SELECT LEAST();
SELECT GREATEST(결제횟수, 연체횟수) FROM card // 예시
SELECT GREATEST(3, 12, 34, 8, 25);
// 하나의 행이나 숫자배열 안에서 최대, 최소를 뽑아줌
SELECT FLOOR(10.1); // 소수 부분 내림해서 정수로 변환
SELECT FLOOR(10.9);
SELECT CEIL(10.1); // 소수 부분 올림해서 정수로 변환
SELECT CEIL(10.9);
SELECT ROUND(10.777, 2); // 반올림
SELECT TRUNCATE(10.777, 2); // 반내림
SELECT POWER(4, 2); // 거듭제곱
SELECT ABS(-100); // 절댓값 출력
9. SELECT 안에 SELECT 또 쓸 수 있음 (서브쿼리)
※ 쿼리문은 sql 한 문장을 뜻함
사용금액의 평균 이상의 값들만 출력하고 싶을 때
SELECT * FROM card
WHERE 사용금액 > (SELECT AVG(사용금액) FROM card);- 소괄호에 있는 쿼리먼저 실행하고 결과를 뱉고 다음 쿼리를 실행
- '문자'나 '숫자' 들어갈 곳에 서브쿼리를 대신 넣을 수 있다.
- 그래서 1개의 문자나 숫자를 뱉는 SELECT문만 서브쿼리로 넣을 수 있음
(여러개의 행을 뱉는 SELECT는 서브쿼리 역할을 할 수 없습니다) - 서브쿼리 넣을 때 ( ) 소괄호 까먹으면 안됨!!!!!
SELECT 사용금액 FROM card
WHERE 고객명 IN ('Pristine', 'George', 'Amy')
SELECT 사용금액 FROM card
WHERE 고객명 IN (SELECT 이름 FROM blacklist) - 서브쿼리를 이용하여 다른 테이블 컬럼도 추출 가능
결론
- 여러 SELECT 문장을 서브쿼리 형태로 하나로 합칠 수 있습니다.
문제
Q1.
"고객등급이 패밀리인 사람들의 평균 연체횟수"보다 연체횟수가 높은 사람은 몇명일까요?
SELECT COUNT(*) FROM card
WHERE 연체횟수 >
(SELECT AVG(연체횟수) FROM card WHERE 고객등급 = '패밀리');
// 내가 짠 코드, 순차적으로 필터링함
SELECT 고객명, 사용금액, 사용금액 - (SELECT AVG(사용금액) FROM card) FROM card;
// 내가 짠 코드
SELECT 고객명, 사용금액, 사용금액 - (SELECT AVG(사용금액) FROM card) AS DIFF FROM card;
// 선생님이 짠 코드
10. 그룹지어 통계낼 땐 GROUP BY
10-1. SQL Error [1055][42000]: 에러 해결하기!!!
https://bcdragonfly.tistory.com/8
나의 해결법
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); // 추가
SELECT * FROM card GROUP BY 고객등급
- 다행히 출력이 잘 된 모습
10-2.
SELECT * FROM card
GROUP BY 고객등급;
SELECT 고객등급 FROM card
GROUP BY 고객등급;
-
card 테이블 안에 모든 컴럼의 '고객등급' 컴럼을 그룹화
-
card 테이블 안에 모든 '고객등급' 컬럼의 '고객등급' 컴럼을 그룹화
// 응용
SELECT 고객등급, COUNT(고객명) FROM card
GROUP BY 고객등급;
SELECT 고객등급, AVG(사용금액) FROM card
GROUP BY 고객등급 SELECT 고객등급, COUNT(고객명) FROM card
GROUP BY 고객등급 HAVING 고객등급 = 'vip';- group화한 고객등급 컬럼을
Having 조건문으로 vip 등급만 필터링 가능
HAVING vs WHERE
HAVING은 용도가 WHERE과 비슷하다. 둘 다 조건식 입력하는 문법
- HAVING은 GROUP BY 결과를 필터링하고 싶을 때 쓴다.
그래서 GROUP BY 뒤에만 붙일 수 있다.
- WHERE는 테이블 전체 데이터 출력시 필터링하고 싶을 때 쓰면 된다.
그래서 SELECT FROM 뒤에만 붙일 수 있다.
SELECT 고객등급, COUNT(고객명) FROM card WHERE 연체횟수 = 0
GROUP BY 고객등급 HAVING 고객등급 = 'vip'; - 카드 테이블 안에 모든 고객등급, 고객명 count를 출력하는데
- 연체횟수가 0인 것들만 필터링 하고
- 고객등급을 group화 하고
- 고객등급이 vip인 것들만 필터링 하여라
Q1. 위 사진처럼 card 테이블에서 연체횟수마다 몇명이 있는지 출력해봅시다.
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT 고객명, 연체횟수 ,COUNT(연체횟수) AS 연체카운트 FROM card
GROUP BY 연체횟수
ORDER BY 연체횟수;
Q2. Q1에서 출력한 결과가 너무 길어서 몇명이냐면 컬럼의 값이 1명인 행은 안보이게 필터링해봅시다.
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT 고객명, 연체횟수 ,COUNT(연체횟수) AS 연체카운트 FROM card
GROUP BY 연체횟수 HAVING NOT 연체카운트 = 1
ORDER BY 연체횟수;11. 중요한 IF / CASE 문법
11-1. IF 문법
SELECT IF(1 + 2 = 3, '맞음', '틀림');- IF(조건식, 조건식맞으면이거, 조건식틀리면이거);
- IF는 양자택일
SELECT 고객명, 사용금액, IF(사용금액>200000, "우수", "거지") FROM card; 출력
11-2. CASE 문법
CASE
WHEN 조건식1 THEN 남길값1
WHEN 조건식2 THEN 남길값2
WHEN 조건식3 THEN 남길값3
END - 조건식에 맞을 경우
- 남길값을 그 자리에 남겨주고
- CASE 문법을 종료
Q.
사용금액이 20만원 이상이면 '우수'
사용금액이 10~20만원이면 '준수'
사용금액이 10만원 미만이면 '그지'
SELECT 고객명, 사용금액,
CASE
WHEN 사용금액 >= 200000 THEN '우수'
WHEN 사용금액 >= 100000 AND 사용금액 < 200000 THEN '준수'
WHEN 사용금액 < 100000 THEN '그지'
END
FROM card;
// 이렇게 줄일 수 있음
SELECT 고객명, 사용금액,
CASE
WHEN 사용금액 >= 200000 THEN '우수'
WHEN 사용금액 >= 100000 THEN '준수'
ELSE '그지'
END
FROM card; Q.
등급이 vip인 사람들은 3점, 로열이면 2점, 패밀리면 1점으로 계산해서 모든 고객의 점수를 다 더하면 몇점일까요?
SELECT SUM(3) FROM card // 모든 행에 3을 더해줌
SELECT SUM(
CASE
WHEN 고객등급 = 'vip' THEN 3
WHEN 고객등급 = '로열' THEN 2
ELSE 1
END
) AS 등급별_합계
FROM card;Q1.
card 테이블에서 장부를 조작하려고 합니다.
사용금액 30만원 이상은 50% 증액, 30만원 미만은 10% 증액해서
사용금액의 총 합계를 출력해봅시다. 답은 5147550로 나오면 정답입니다.
// 내가 짠 코드
SELECT 고객명, SUM(
CASE
WHEN 사용금액 >= 300000 THEN 사용금액 * 1.5
ELSE 사용금액 * 1.1
END
) AS 합계 , 고객등급 FROM card;
// 선생님이 짠 코드
SELECT SUM(
IF( 사용금액 >= 300000, 사용금액 * 1.5, 사용금액 * 1.1 )
) FROM mart.card Q2.
고객등급을 재설정하려고 합니다.
사용금액이 30만원 이상은 'vip'
20만원 이상 30만원 미만은 '로열'
그 외엔 '패밀리'로 다시 설정하려고 합니다.
고객등급이 변동될 이름들만 출력해봅시다.
1.
SELECT 고객명, 사용금액, 고객등급,
CASE
WHEN 사용금액 >= 300000 THEN 'vip'
WHEN 사용금액 >= 200000 THEN '로열'
ELSE '패밀리'
END
FROM card
2.
SELECT 고객명, 사용금액, 고객등급
FROM card
WHERE 고객등급 != CASE
WHEN 사용금액 >= 300000 THEN 'vip'
WHEN 사용금액 >= 200000 THEN '로열'
ELSE '패밀리'
END
// 순차적으로 코딩
- 고객등급 != 바뀔등급 이렇게 필터링
(고객등급과 바뀔등급 서로의 값이 다른 것들을 출력하겠다.)
안녕하세요~!