MariaDB로 SQL 다뤄보기
터미널 접속시 항상 해야 하는일
cd / → 루트 디렉토리로 들어가야 유저 명령을 줄 수 있다.
cd var → var 디렉토리로 들어가기
그 다음에 mysql -u user1 -p → 기존 계정으로 접속하기
※ "Access denied for user 'user1'@'localhost'" 뜰때 해결방안
1) 해당 디렉토리 들어갔는지 확인 (나의 경우 루트 디렉토리에서는 로그인이 안되고 var 디렉토리 가야 로그인이 되는경우가 있었다. 하지만 루트 디렉토리에서도 될때 있다는게 함정. (뭐지..))
2) ALTER USER 'user1'@'localhost' IDENTIFIED BY '새로운_비밀번호';
FLUSH PRIVILEGES; - 루트계정에서 비밀번호 변경해서 들어가자.
3) GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY '1234';
FLUSH PRIVILEGES; - 루트계정에서 user1 에게 권한을 다시 줘보자.
4) DROP USER 'user1'@'localhost'; - 유저를 삭제하고 다시 생성하자...
테이블 생성 (CREATE TABLE)
CREATE TABLE < 테이블명 > (
id INT AUTO_INCREMENT PRIMARY KEY, # 자동 증가하는 기본 키
name VARCHAR(100) NOT NULL, # 문자열에는 VARCHAR(가변) 또는 CHAR(고정) - 괄호는 문자열의 길이
position VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL, # 숫자열에는 INT 혹은 DECIMAL(전체자릿수, 소수점이하 자릿수)
hire_date DATE DEFAULT (CURRENT_DATE) # 입사일 (기본값: 현재 날짜. 시스템 날짜 불러오기)
); # 마지막에 세미콜론 필수 / 마지막 컬럼에는 콤마가 없어야 한다! (hire_date)
데이터 삽입 (INSERT INTO) - 이미 있는 데이터 테이블에 행 추가해도 INSERT 사용
INSERT INTO < 테이블명 > (name, position, salary) # 컬럼을 나열 ( id는 자동증가 기본키이기 때문에 빼고! )
VALUES
('김철수', '디자이너', 4500000), # 문자열에는 반드시 ' '로 감싸준다.
('이영희', '마케팅', 4800000), # 참고로 날짜 및 시간 데이터도 반드시 ' '로 감싸준다!
('박민수', '개발자', 5200000),
('최수영', 'HR 매니저', 4700000); # 마지막에 세미콜론 필수
데이터 조회(SELECT)
SELECT * FROM < 테이블명 >; # 모든 데이터 조회
SELECT * FROM < 테이블명 > WHERE position = '개발자'; # 특정조건으로 데이터 조회
데이터 수정(UPDATE)
UPDATE employees
SET position = '시니어 개발자', salary = 6000000 # 여러 개 컬럼 동시에 수정 가능
WHERE name = '박민수';
특정 조건을 만족하는 모든 행 업데이트
UPDATE employees
SET salary = salary * 1.1 # 급여를 10% 인상
WHERE position = '디자이너';
데이터 삭제 (DELETE)
특정 행 삭제
DELETE FROM employees
WHERE name = '김철수';
특정 조건을 만족하는 여러 행 삭제
DELETE FROM employees
WHERE position = '마케팅';
테이블 전체 데이터 삭제
TRUNCATE TABLE employees; # 모든 데이터는 삭제하지만 테이블 구조는 유지된다.※ desc (테이블명); - 해당 테이블의 구조를 확인하는 SQL 명령어
테이블 삭제
DROP TABLE employees; # 테이블 자체가 삭제됨.
테이블 열이 길어서 보기 불편할때 결과를 세로로 출력하는 법
SELECT * FROM employees \G
응용) 직원수가 가장 많은 부서 찾기
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC
LIMIT 1; # 행 첫번째만 출력하기. DESC로 내림차순에 정렬했으니 첫번째 값은 가장 큰 값이다.
COUNT는 갯수세기, (*)는 행(row)전체.
AS는 별칭으로 계산값을 employee_count 라는 임시변수에 담았다. 별칭은 쿼리가 종료되면 사라짐.
DESC는 내림차순 ( 오름차순은 ASC)
이 쿼리는
1) employees 테이블에서 department 와 employee_count를 가져온다.
1-1) employee_count는 임시변수로 부서별로 직원 수를 센 데이터를 담는다. (COUNT(*))
2) 부서별로 그룹화한다. ( GROUP BY department )
3) 직원 수 기준으로 내림차순 정렬한다. ( ORDER BY employee_count DESC )
4) 맨 위 첫번째 데이터만 가져온다. ( 가장 직원 수가 많은 부서 한 개만 가져온다.)
- 결과 : 직원 수가 가장 많은 부서의 이름과 직원수를 출력하게 된다.
응용) 이메일 주소 도메인이 'example.com'인 직원 찾기
SELECT * FROM employees WHERE email LIKE '%@example.com';
LIKE는 특정 문장 내에 특정 단어 찾을 때
%는 그것 앞에 어떠한 내용이 있다는 뜻. 즉 %앞에 어떤 문자가 오든 상관없이 ' @example.com'으로 끝나는 이메일 주소만 검색이 가능. 다양한 위치에서 %를 사용해 특정 패턴을 검색할 수 있음. (앞, 뒤, 앞뒤 모두)
응용) 각 부서별 평균 급여 확인 - 집계함수
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;
집계함수의 예 : AVG(평균), SUM(합계), COUNT(개수 세기), MAX(최대값 찾기), MIN(최소값 찾기)
응용) 가장 높은 전환율(Conversion Rate)을 보인 캠페인 조회
전환율(%) = (전환 수 / 클릭 수) * 100
SELECT campaign_name, channel, (conversions / clicks) * 100 AS conversion_rate
FROM marketing_campaigns
ORDER BY conversion_rate DESC
LIMIT 1;
이 쿼리는
marketing_campaigns 테이블에서
1) campaign_name, channel, conversion_rate를 가져온다.
1-1) conversion_rate는 임시변수로 켐페인별 전환율을 계산한 값을 담는다. ( (conversions / clicks) * 100)
2) 전환율이 높은 순서로 정렬한다. (ORDER BY conversion_rate DESC)
3) 전환율이 가장 높은 캠페인 하나만 가져온다. ( LIMIT 1;)
- 결과 : 전환율이 가장 높은 마케팅 캠페인의 이름, 채널, 전환율을 출력한다.
응용) 특정 채널(예: Google Ads)에서 진행된 캠페인의 총 전환 수 및 평균 전환율
SELECT channel, SUM(conversions) AS total_conversions, AVG((conversions / clicks) * 100)
AS avg_conversion_rate
FROM marketing_campaigns
WHERE channel = 'Google Ads'
GROUP BY channel;
이 쿼리는
marketing_campaigns 테이블에서
1) Google Ads 캠페인만 선택 (WHERE channel = 'Google Ads')
2) 그 중에서 channel, total_conversions, avg_conversion_rate를 가져온다.
2-1) 총 전환 수 계산 (SUM(conversions)) ----- total_conversions라는 임시변수에 담음.
2-2) 평균 전환율 계산 (AVG((conversions / clicks) * 100)) ----- avg_conversion_rate라는 임시변수에 담음.
3) Google Ads 채널 단위로 그룹화 (GROUP BY channel)
- 결과 : Google Ads 채널에서의 총 전환 수와 평균 전환율을 출력한다.
응용) 특정 기간(예: 6개월) 동안 캠페인 성과 조회
SELECT * FROM email_campaigns1
WHERE send_date >= DATE_SUB('2024-08-01', INTERVAL 6 MONTH);
이 쿼리는
email_campaigns1 테이블에서
2024년 8월 1일 기준으로 최근 6개월(2024년 2월 1일 이후)에 발송된 이메일 캠페인을 찾음.
send_date가 2024년 2월 1일 이후인 데이터만 조회한다.
- 결과 : 2024년 2월 1일 이후에 발송된 이메일 캠페인만 출력한다.
분석, 최적화를 하는 이유
분석은 데이터가 어떻게 생겼는지 보는것 = 검토
최적화는 비율분석에 해당하는것. = 조정
먼저 분석이 되어야 최적화가 가능하다.
예시)
분석 - 사용자 리텐션 분석, 마케팅 채널별 ROI 비교, A/B테스트 결과 비교, 퍼널 분석, 구독모델분석
최적화 - 전환율 최적화, 고객생애가치(LTV)예측, 캠페인 자동 최적화, 추천 시스템 최적화
기억하기
SQL에서는 따로 뒤로가기(ctrl+z), delete 기능이 없다 ^^... 그냥 ctrl+c를 누르자...
테이블 동시저장, 동시수행은 불가능..
오늘 회고
강의 시작시간에 강사님이 해주신 조언이 너무 와닿는 하루였다. 공부를 할때 하나씩 꼼꼼히 보기보다 많이 해보는 것이 중요하다는 것..모르는 부분은 넘어가더라도 여러번 해보기..너무 하나하나 다 꼼꼼히 따지면서 배우면 많이 어려워진다고 하셨는데 완전 내얘기였다. ^_ㅠ 외우기 방식은 지양하고 몰라도 일단 많이 해보는걸로.. 오늘도 수업시간에 코드및 명령어에 주석을 달아보았는데 코드가 어떻게 작동되는지 이해할 수 있어서 개인적으로는 정말 도움이 되는 시간이었다.