DSA DB 정리
‘DSA MySQL데이터베이스 정리’
-- 주석
-- 사용하고자 하는 데이터베이스()
use hr;
-- 쿼리 실행은 ctrl + enter (mac: cmd + enter)
-- employees 테이블 구성 살펴보기
-- desc 테이블명;
desc employees;
-- departments 테이블 구성
desc departments;
-- 테이블 전체 데이터 조회
-- select * from 테이블명;
-- query문은 대소문자를 구분하지 않음(데이터는 다른 db에서 구분함)
select * from employees;
-- 쿼리는 대소문자 구별x, 프리포멧
-- jobs 테이블 전체 데이터 조회
select * from jobs;
-- 테이블 부분 컬럼 출력하기
-- 사원번호 (employee_id), 이름(first_name), 급여(salary)
select
employee_id,
first_name,
salary
-- 순서대로 나옴
from
employees;
-- 성(last_name), 고용날짜(hire_date), 상사아이디(manager_id)
select
last_name,
hire_date,
manager_id
from
employees;
-- distinct로 중복 제거하기
-- 부서번호
select department_id
from employees;
select DISTINCT
department_id
from
employees;
-- 열(컬럼)이 두개 이상
select distinct
employee_id,
department_id
from
employees;
-- 사원번호, 이름, 급여
select employee_id, first_name, salary
from employees;
/*
여러줄 주석
여러줄 주석2
*/
-- 어떤 데이터베이스가 있는지
show databases;
-- 테이블의 상태 조회(현재 use로 사용하고 있는 데이터베이스)
show table status;
-- 테이블 하나의 구조
describe departments;
desc departments;
select * from departments;
-- 별칭 (Alias) 설정하기 : 띄어쓰기, as '별칭', as 별칭<이방법은 별칭에 공백 있어선 안됨>
-- as를 붙여주는게 좋음(명시적 기능이 있기 때문에 가독성이 좋음)
-- 모든 사원의 이름, 한달급여, 연봉
-- 컬럼에 연산 가능
-- 컬럼은 사실 테이블명.컬럼명으로 테이블명이 생략되어 있지만 테이블이 하나일땐 명시하지 않아도 동작
select first_name 이름, salary as '한달 급여', salary * 12 as 연봉
from employees e;
-- 쿼리는 대소문자 구별x
-- 들어있는 데이터 기본적으로 대소문자 구별 0 user User USER
-- 이름이 Steven인 사람만 추출하기
select *
from employees
where first_name = 'Steven';
-- where 조건절 : 해당 조건식이 true인 데이터만 추출, 들어있는 데이터를 컨트롤
select *
from employees
where first_name = 'sTEven';
-- mysql은 기본값이 데이터도 대소문자 구분하지 않음 (단점)
-- ex) user uSer
-- 부서번호(department_id)가 30인 데이터만 추출
select *
from employees
where department_id = 30;
select *
from employees
where department_id = '30'; -- 데이터타입이 decimal이지만 자동형변환(Mysql 한정)
-- 이 방법은 하지 않는게 좋음. DB는 Strict하게 관리해야함
-- 부서번호가 50번인 데이터만 추출
select *
from employees
where department_id = 50;
-- 부서번호가 30번이고 직책(job_id)이 PU_MAN인 사원 추출
select *
from employees
where department_id = 30 and job_id = 'PU_MAN'; -- 둘다 true일때만 추출
-- 부서번호가 30번이거나 직책이 AC_ACCOUNT인 사원 추출
select *
from employees
where department_id = 30 or job_id = 'AC_ACCOUNT'; -- 둘다 false인 상황만 제외하고 전부 추출
-- 부서번호가 50번이고 직책이 ST_MAN인 사원들 추출
select *
from employees
where department_id = 50 and job_id = 'ST_MAN';
-- 부서번호가 80번이거나 직책이 ST_MAN인 사원들 추출
select *
from employees
where department_id = 80 or job_id = 'ST_MAN';
-- 날짜 데이터도 대소비교가 가능
-- 날짜는 미래일수록 크다
-- 고용 날짜가 2008년 이후인 사원들 추출
select *
from employees
where hire_date >= '2008/01/01';
-- 연봉(salary * 12)이 100000 이상인 사원들 추출
select *
from employees
where salary * 12 >= 100000;
-- 월 급여가 15000 이상인 사람들
select *
from employees
where salary >= 15000;
-- 문자 대소비교
-- 사전상 뒤에 나올수록 크다
-- 이름이 U랑 같거나 뒤에 나오는 이름을 가진 사원들 추출
select *
from employees
where first_name >= 'U';
-- 이름이 U랑 같거나 앞에 나오는 이름을 가진 사원들 추출
select *
from employees
where first_name <= 'U';
-- 성이 G보다 앞에 나온는 이름을 가진 사원들 추출
select *
from employees
where last_name < 'G';
-- 등가비교연산자
-- 같다 : =
-- 다르다 : !=, <>
-- 부서번호가 30번이고 급여가 3100달러가 아닌 사원
select *
from employees
where department_id = 30 and salary <> 3100;
-- 급여가 5000 ~ 10000 받고 있는 사원들 추출
select *
from employees
where salary >= 5000 and salary <= 10000;
-- A ~ B 사이
-- 컬럼명 between A and B
-- A B 순서 바뀌면 안됨
select *
from employees
where salary between 5000 and 10000;
-- 급여가 3000 ~ 4800 사이인 사원들 추출
SELECT *
FROM employees
WHERE salary BETWEEN 3000 AND 4800;
-- 직책이 PU_CLERK 또는 SH_CLERK 또는 ST_CLERK 인 사원들 추출
select *
from employees
where job_id = 'PU_CLERK' or job_id = 'SH_CLERK' or job_id = "ST_CLERK";
-- IN(a, b, ...) 사용
select *
from employees
where job_id in ('PU_CLERK', 'SH_CLERK', 'ST_CLERK');
select *
from employees
where employee_id = 123;
-- 100 or 101 or 102번 사원번호를 가지는 상사를 둔 사람들
-- 100, 101, 102번의 부하직원들
select
*
from
employees
where
manager_id in (100, 101, 102);
-- ctrl + shift + f : 선택된 쿼리문 자동 정리
-- option(alt) + 위아래 방향키 : 이전 다음 쿼리로 이동 및 쿼리 일괄 선택
-- ctrl + shift + / : 블럭주석
-- ctrl + shift + 위아래 방향키 : 위아래로 행 이동
-- 논리부정연산자(NOT) : 주로 BETWEEN, IN, IS NULL, LIKE와 주로 같이 사용
-- 급여가 3000이 아닌 사원들 추출
select *
from employees
where not salary = 3000;
-- 직책이 PU_CLERK, SH_CLERK, ST_CLERK이 아닌 사원들
select *
from employees
where job_id != 'PU_CLERK' and job_id <> 'SH_CLERK' and not job_id = 'ST_CLERK';
select *
from employees
where job_id not in ('PU_CLERK', 'SH_CLERK', 'ST_CLERK');
-- 급여가 5000 ~ 8000 사이인 사원들
select *
from employees
where salary between 5000 and 8000;
-- 급여가 5000 ~ 8000 사이가 아닌 사원들
select *
from employees
where salary not between 5000 and 8000;
-- LIKE 연산자
-- 이름이 S로 시작하는 사원들 추출
select *
from employees
where first_name like 'S%'; -- %의 의미는 뒤의 글자개수나 형태 상관X
-- 성이 T로 시작하는 사원들 추출
select *
from employees
where last_name like "T%";
-- 이름이 n으로 끝나는 사원들 추출
select *
from employees
where first_name like "%n";
-- 성이 n으로 끝나는 사원들 추출
select *
from employees
where last_name like "%n";
-- 이름의 두번째 글자가 l인 사원들 추출
select *
from employees
where first_name like '_l%'; -- '_' : 글자 하나를 의미, 뭐든 상관없음
-- 이름의 세번째 글자가 e인 사원들 추출
select *
from employees
where first_name like '__e%';
-- 이름에 le가 포함된 사원들
select *
from employees
where first_name like '%le%';
-- 이름에 le가 포함되지 않는 사원들
select *
from employees
where first_name not like '%le%';
-- IS NULL : 데이터가 없는 ROW(행) 추출
-- 커미션이 없는 사원들
select *
from employees
where commission_pct is null; -- null은 = 로 추출불가
-- 커미션이 있는 사원들
select *
from employees
where commission_pct is not null; -- is는 not을 뒤에 붙여줘야 한다
-- 부서번호가 없는 사원들(인턴)
select *
from employees
where department_id is null;
-- UNION : 쿼리 연결해서 하나의 결과, union은 개수 제한X
-- 부서번호가 20번인 사원들
select *
from employees
where department_id = 20;
-- 부서번호가 30번인 사원들
select *
from employees
where department_id = 30;
-- 부서번호가 20, 30번인 사원들
select *
from employees
where department_id in (20, 30);
select * from employees where department_id = 20
union
select * from employees where department_id = 30
union
select * from employees where department_id = 40; -- union 쓸때 세미콜론 위치 주의
-- upper : 대문자로 변환
-- lower : 소문자로 변환
select first_name, upper(first_name) as 대문자, lower(first_name) as 소문자
from employees;
-- Email을 소문자로 변환해서 추출
select email, lower(email)
from employees;
-- MySQL은 기본적으로 데이터의 대소문자를 구별하지 않는다.
-- 그러나 테이블을 만들 때 컬럼에 COLLATE를 추가하면 대소문자를 구분함.
-- length(): 문자열 길이(byte 수)를 구하는 함수
SELECT first_name, length(first_name)
FROM employees;
-- 이름이 9글자 이상인 사원들
select *
from employees
where length(first_name) >= 9;
-- 한글 확인
-- 영문은 1byte, 한글은 3byte
-- 가상테이블: from절 생략, 주로 함수 테스트용.
select length('안녕하세요');
-- 문자열 일부를 추출(substr, substring)
-- substr(str, start, length)
-- substr(추출할 문자열, 시작위치(1부터 셈), 몇 글자 추출할껀지<생략가능>)
-- substring도 동일하게 사용가능 (substr()과 동일)
-- job_id에 언더스코어(_) 이후의 문자열 추출하기
select job_id, substr(job_id, 4)
from employees;
-- job_id에 언더스코어(_) 이전의 두글자 추출하기
select job_id, substr(job_id, 1, 2)
from employees;
-- 고용 날짜에서 년도만 추출
select hire_date, substr(hire_date, 1, 4)
from employees;
-- 문자열 안에서 특정 문자 위치를 찾는 instr
-- instr(str1, str2)
-- instr(검색할 문자열(대상), 찾고자 하는 문자열)
-- index를 알려줌 (1부터 셈)
-- 찾고자 하는 문자열이 여러개 있을 경우 찾은 첫번째 위치를 말함
select 'Hello, MySQL!', instr('Hello, MySQL!', ' ');
-- 이름에 z가 들어있는 사원들 조회
select *
from employees
where instr(first_name, 'z') != 0;
-- 문자열 대체하기
-- replace(original, search, replacement)
-- replace(원본, 찾는 문자열, 대체할 문자열)
select '010/1234/5678', replace('010/1234/5678', '/', '-');
-- 전화번호 '.'를 '-'으로 대체
SELECT phone_number, replace(phone_number, '.', '-') as '전화 번호'
FROM employees;
-- concat: 문자열 연결, ("Hello" + "World") "HelloWorld"
-- concat(연결할 문자열1, 연결할 문자열2, 연결할 문자열3, ....)
-- 사원 번호: 사원 이름 (ex: 100 : Steven)
select
employee_id as "사원번호",
first_name as "이름",
concat(employee_id, ":", first_name) as '사원 정보'
from employees;
-- 풀네임 출력하기
select
first_name as "이름",
last_name as "성",
concat(first_name, ' ', last_name) as 풀네임
from employees;
-- trim() : 양 끝의 공백을 제거
select
' my sql ', trim(' my sql ');
-- 반올림 : round
select
1234.5678,
round(1234.5678) as round, -- 소수 첫번재 자리에서 반올림
round(1234.5678, 1) as round_1, -- 소수 두번째 자리에서 반올림
round(1234.5678, 2) as round_2,-- 소수 세번째 자리에서 반올림
round(1234.5678, -1) as round_minum1, -- 정수 첫번재 자리에서 반올림
round(1234.5678, -2) as round_minus2;-- 정수 두번째 자리에서 반올림
-- ceil : 가장 가까운 큰 정수 반환
-- floor : 가장 가까운 작은 정수 반환
select
ceil(3.14),
floor(3.14),
ceil(-3.14),
floor(-3.14);
-- 나머지 구하기 mod(n1, n2) n1 % n2
select
mod(15, 6), -- 15 % 6
mod(10, 2),
mod(11, 2),
15 % 6;
-- 급여를 3으로 나눈 나머지 as 나머지
-- 급여, 나머지
select salary, mod(salary, 3) as 나머지
from employees;
-- 현재 날짜 시간 구하기
select sysdate(); -- 시스템에서 날짜, 시간
select current_date(); -- 날짜
select curdate();
-- 어제 날짜 구하기
select curdate() - 1 as 어제날짜; -- 실패
-- interval 1 day : 일
-- interval 1 month : 월
-- interval 1 year : 연도
select curdate() - interval 1 day as 어제날짜;
-- 내일 날짜 구하기
select curdate() + interval 1 day as 내일날짜;
-- 입사 일주일 뒤의 날짜
select hire_date, hire_date + interval 7 day as '입사 일주일 뒤'
from employees;
-- 미래의 날짜(후의 날짜) - date_add(), adddate()
-- date_add(기준, interval)
-- 오늘부터 3개월 후의 날짜
select date_add(curdate(), interval 3 month) as '3개월 후';
select adddate(curdate(), interval 3 month) as '3개월 후';
-- 입사가 20주년이 되는 날 (이름, 입사일, 입사 20주년)
select
first_name as 이름,
hire_date as 입사일,
date_add(hire_date, interval 20 year) as '입사 20주년'
from employees;
-- 입사한지 20년이 지난 사람들(이름, 입사일, 입사 20주년) : 날짜는 미래일수록 크다
select
first_name as 이름,
hire_date as 입사일,
date_add(hire_date, interval 20 year) as '입사 20주년'
from
employees
where
curdate() > date_add(hire_date, interval 20 year);
-- 두 날짜 또는 시간의 차이를 시:분:초 형태로 반환
select timediff(sysdate(), '2024-11-18 12:50:00');
-- 오늘 집에 가기까지 몇시간 남았나
select timediff('2024-11-18 16:50:00', sysdate());
-- 두 날짜 간의 일 수 차이
-- datediff(비교할 날짜1, 비교할 날짜2)
-- 입사하고 며칠 지났나?
-- 이름, 입사일, 입사하고 며칠 지났나?
select
first_name as 이름,
hire_date as 입사일,
datediff(curdate(), hire_date) as '입사하고 며칠 지났나?'
from employees;
-- 자동형변환
-- 문자형이지만 숫자로 이루어져 있으므로 허용.
select
employee_id, employee_id + '500'
from
employees;
-- 문자형이 자로 이루어져 있지 않더라도 해당 문자 앞에 숫자까지는 숫자로 인정.
select
employee_id, employee_id + '5a00' -- 5까지는 숫자로 인정
from
employees;
-- 문자가 도저히 숫자로 변환 불가한 상황은 자동형변환 불가(에러는 아님)
select
employee_id, employee_id + '오백' -- 5까지는 숫자로 인정
from
employees;
-- 숫자 또는 날짜데이터 -> 문자로 변환
-- 500 -> '500'
select cast(500 as char);
select convert(500, char);
-- 오늘날짜 -> 문자
select sysdate();
select cast(sysdate() as char);
select convert(sysdate(), char);
-- 날짜 -> 문자로 변환할 때 형식지정
select date_format(sysdate(), '%Y년 %m월 %d일 %H시 %i분 %s초');
-- 문자 -> 숫자로 변환
-- 문자가 섞이면 문자가 등장하기 전까지만 숫자로 변환
select cast('500' as signed);
select cast('5a00' as signed);
select convert('500', signed);
-- 문자 -> 날짜로 변환
select '20241118', cast('241118' as date);
select cast('2024-11-18' as date);
select cast('2024/11/18' as date);
select convert('2024-11-18', date);
select convert('2024/11/18', date);
-- 문자 -> 날짜로 변환할 때 형식을 지정
-- 형식을 변환하고자 하는 형식과 맞춰줘야 함. 안맞추면 NULL
select str_to_date('2024 11 18', '%Y %m %d');
select str_to_date('2024-11-18', '%Y-%m-%d');
select str_to_date('2024/11/18', '%Y/%m/%d');
-- 요일
-- ko_KR(한국어_대한민국), en_US(영어_미국), ja_JP(일본어_일본)
set lc_time_names = 'en_US'; -- 시스템 설정 변경
select dayname(now());
select now();
select sysdate();
-- 2007년 이후 입사한 사원(hire_date(date)는 기본적으로 날짜데이터와 비교)
select *
from employees
where hire_date >= '070101'; -- 자동형변환 문자 -> 날짜
-- 커미션을 포함한 연봉구하기
-- 출력 컬럼: 사원번호, 풀네임, 급여, 커미션퍼센트, 연봉((급여 * 12) + (급여 * 12 * 커미션퍼센트))
select
employee_id as 사원번호,
concat(first_name, ' ', last_name) as 풀네임,
salary as 급여,
commission_pct as 커미션퍼센트,
(salary * 12) + (salary * 12 * commission_pct) as 연봉
from employees;
-- null은 연산에 들어가게 되면 전체 결과가 무조건 null로 반환
-- select 10 / null + 1
-- coalesce(대상 컬럼, null일 경우 줄 값)
-- 데이터가 있는 경우는 그 값 그대로
select
employee_id as 사원번호,
concat(first_name, ' ', last_name) as 풀네임,
salary as 급여,
commission_pct as 커미션퍼센트,
(salary * 12) + (salary * 12 * coalesce(commission_pct, 0)) as 연봉
from employees;
-- 조건이 여러개일 때
-- case 대상 when 조건 then 결과...else 나머지 end
-- 부서번호가 10이면 '열' 20이면 '스물', 30이면 '서른' 나머지는 '숫자'
select
department_id,
case department_id
when 10 then '열'
when 20 then '스물'
when 30 then '서른'
else '숫자'
end as 케이스연습
from employees;
-- 직책 ID가 MGR일 경우 급여 10프로 인상, CLERK일 경우 5프로 인상, PRES는 그대로 나머지는 3프로 인상
-- 사원번호, 이름, 직책ID, 인상전 급여, 인상후 급여
select
employee_id as 사원번호,
first_name as 이름,
job_id as 직책ID,
salary as '인상전 급여',
case substr(job_id, 4)
when 'MGR' then salary * 1.1
when 'CLERK' then salary * 1.05
when 'PRES' then salary
else salary * 1.03
end as '인상후 급여'
from employees;
select job_id, substr(job_id, 4)
from employees;
-- 정렬과 역정렬
-- order by : 숫자, 문자, 날짜
-- order by절은 항상 쿼리 마지막에
-- 오름차순(작은 -> 큰) : order by 칼럼명1, 칼럼명2 [asc] 혹은 생략
-- 내림차순(큰 -> 작은) : order by 칼럼명 desc
-- 부서번호가 30 or 50번인 사원들을 부서번호 오름차순으로 정렬
select *
from employees
where department_id in (30, 50)
order by department_id desc, employee_id asc;
-- 먼저 컬럼명1 기준으로 정렬한 후에 컬럼명2 기준으로 정렬
-- 입사 최근순으로 정렬
select *
from employees
order by hire_date desc;
-- case를 조건식만으로 사용하기(자바의 IF문처럼)
-- 사원번호, 이름, 급여, 커미션여부 (없다면: 커미션없음, 있다면: 해당값)
select
employee_id,
first_name,
salary,
case
when commission_pct is null then '커미션 없음'
else commission_pct
end as '커미션 여부'
from employees;
-- 급여가 15000 이상이면 '많음' 10000 이상이면 '보통' 나머지는 '적음'
-- 급여, 급여수준
select
salary as 급여,
case
when salary >= 15000 then '많음'
when salary >= 10000 then '보통' -- 10000 ~ 14999
else '적음'
end as 급여수준
from employees;
-- 함수
-- 단일행 함수: length(), upper(), lower(), substr() 등등
-- 단일행 함수: 함수 실행 결과가 데이터 하나하나로 추출됨
select
first_name,
length(first_name)
from employees;
-- 다중행 함수: 함수 실행 결과가 하나의 결과로 추출됨
-- 모든 사원의 급여 합계 (데이터 하나)
-- 합계를 구하는 sum()
select
sum(salary)
from employees;
-- 다중행 함수와 일반컬럼 같이 조회하려고 하면 에러
-- select
-- first_name,
-- sum(salary)
-- from employees;
-- 급여 합계 구하기
select
sum(distinct salary), -- 중복제거
sum(all salary), -- 중복제거 X
sum(salary) -- all을 쓴것과 같은 결과
from employees;
-- 모든 사원수 구하기 (해당 테이블의 전체 데이터가 몇개인지?)
-- count()
select
count(*)
from employees;
-- 부서번호가 30번인 사원 수 구하기
select
count(*)
from employees
where department_id = 30;
-- 커미션이 있는 사원의 수
select
count(*)
from employees
where commission_pct is not null;
-- count(컬럼명) : NULL인 데이터는 제외하고 카운트
select
count(commission_pct)
from employees;
-- 부서 배정을 받은 사원의 수(department_id가 있는 사원의 수)
select
count(department_id)
from employees;
-- order by
-- 별칭으로 정렬가능. 단, 공백이 포함된 별칭으로는 정렬 불가능
-- where절 : 별칭 사용 불가능
select
first_name as 이름,
salary as 급여
from employees
order by 급여 desc;
-- max(칼럼명) : 해당 칼럼에서 가증 큰 데이터를 반환. 최댓값
-- 사원들 중에서 가장 급여를 많이 받고 있는 사원의 급여
select
max(salary)
from employees;
-- 50번 부서에서 가장 많이 받는 사원의 급여
select
max(salary)
from employees
where department_id = 50;
-- min(컬럼명) : 해당 컬럼에서 가장 적은 데이터를 반환. 최솟값
-- 모든 사원들 중에서 가장 적게 받고 있는 사원의 급여
select
min(salary)
from employees;
-- 30번 부서에서 가장 적게 받고 있는 사원의 급여
select
min(salary)
from employees
where department_id = 30;
-- 가장 최근에 입사(가장 늦게 입사)한 사원의 입사일
-- 날짜 데이터는 현재에 가까울 수록 즉 미래의 날짜가 더 크다
select max(hire_date)
from employees;
-- 가장 빨리 입사(최초 입사)한 사원의 입사일
select min(hire_date)
from employees;
-- 모든 사람의 급여 평균
-- avg(컬럼명) : 해당 컬럼에 들어 있는 데이터의 평균값을 반환
select avg(salary)
from employees;
-- 모든 사원들의 급여 평균(정수) - 소수 첫번째자리에서 반올림
select
round(avg(salary)) as 급여평균
from employees;
-- 50번 부서의 평균 급여(정수)
select
round(avg(salary)) as 급여평균
from employees
where department_id = 50;
-- 부서별로 평균 급여
select
department_id as 부서번호,
round(avg(salary)) as 평균급여
from
employees
group by
department_id;
-- group by 칼럼명 : 칼럼명 별로 그룹화. 다중행 함수를 그룹별로 각각 실행해서 추출해라
-- group by 칼럼명1, 칼럼명2 : 칼럼명 1로 그룸화, 칼럼명2로 그룹화\
-- 부서별, 직책별 평균 급여
select
department_id,
job_id,
round(avg(salary))
from employees
group by department_id , job_id
order by
department_id;
-- 부서별로 평균급여 정렬(부서별). 단, 평균급여가 5000이 넘는 경우만 추출
select
department_id as 부서번호,
-- first_name, -- group by 절에 없는 일반칼럼은 select절에 올 수 없다.
round(avg(salary)) as 평균급여
from employees
group by department_id
having round(avg(salary)) > 5000
-- where : 조건절. 그룹화 전의 조건. false로 제외되면 그룹화 자격 상실
-- 별칭 사용 X
-- having : 조건절. 단독으로 사용불가. group by와 함께.
-- 별칭 사용 O
-- 그룹화의 예
-- 여자그룹 2개
-- 남자연습생 20명, 여자연습생 20명
-- where 남자가 아닌 조건 (그룹화 전에)
-- having 한국국적 (그룹화 후에)
-- 각 부서의 직책별 평균급여추출
-- 부서, 직책, 평균급여
-- 정렬(부서별, 직책별)
-- 조건1. 급여가 5000 미만인 사원은 그룹화 대상에서 제외(급여가 5000 이상인 사원들이 대상) - where
-- 부서에 배정되지 않은 사원 제외
-- 조건2. 그룹화된 부서별 직책 중 평균급여가 10000이상인 경우만 추출 - having
select
department_id as 부서번호,
job_id as 직책,
round(avg(salary)) as 평균급여
from employees
where salary >= 5000 and department_id is not null
group by department_id, job_id
having 평균급여 > 10000
order by department_id, job_id;
-- 다중행 함수들끼리는 select절에 쓸 수 있다.
select
max(salary),
min(salary)
from employees;
-- 각 부서의 직책별 사원수, 가장 높은 급여(액수), 가장 낮은 급여(액수), 급여합, 급여평균(정수)
select
department_id as 부서,
job_id as 직책,
count(*) as 사원수,
max(salary) as '가장 높은 급여',
min(salary) as '가장 낮은 급여',
sum(salary) as 급여합계,
round(avg(salary)) as 급여평균
from employees
group by department_id , job_id
order by department_id;
-- 소계와 총계 : rollup => 각 그룹별 결과 그리고 전체에 대한 결과 출력
select
department_id as 부서,
job_id as 직책,
count(*) as 사원수,
max(salary) as '가장 높은 급여',
min(salary) as '가장 낮은 급여',
sum(salary) as 급여합계,
round(avg(salary)) as 급여평균
from employees
group by department_id , job_id with rollup;
-- 조인(JOIN)
-- 사원테이블(employees)과 부서테이블(departments) 함께 보고싶다
select * from employees;
select * from departments;
select count(*) from departments;
-- 엉망, 데카르트의 곱(ex: 사원(107), 부서(27), 107X27)
select *
from employees e, departments d;
-- 조인 조건식
-- employees의 테이블이 가지고 있는 칼럼 department_id는
-- departments 테이블의 PK
-- 조인을 하려면 다른 테이블의 PK를 컬럼으로 가지고 있어야한다. => Foreign Key(외래키)
-- employees 테이블의 department_id는 FK
-- departments 테이블의 department_id는 PK
select *
from employees e, departments d
where e.department_id = d.department_id ; -- 조인 조건식
-- 사원번호, 이름, 급여, 부서번호, 부서명
select
e.employee_id,
e.first_name ,
e.salary ,
d.department_id , -- e든 d든 상관없음
d.department_name
from
employees e ,departments d -- 테이블에 별칭 줄때는 as 안써도 됨
where
e.department_id = d.department_id
order by
e.employee_id;
select * from locations;
desc employees ;
desc locations ; -- 같은 이름의 칼럼이 없다면 연관관계가 X, 조인의 대상이 X.
desc departments; -- location_id를 둘 다 가지고 있으니까 연관관계 O. 조인의 대상이 O
-- 급여가 8000 이상인 사원들의 사원번호, 이름, 급여, 부서번호, 부서명
select
e.employee_id ,
e.first_name ,
e.salary ,
e.department_id ,
d.department_name
from
employees e , departments d
where
e.department_id = d.department_id
and
e.salary >= 8000;
-- 자체조인(테이블 하나로 조인)
-- 사원번호, 이름, 상사의 사원번호(manager_id), 상사의 이름
select
e1.employee_id as 사원번호,
e1.first_name as 이름,
e1.manager_id as "상사의 사원번호",
e2.first_name as "상사의 이름"
from
employees e1, employees e2
where
e1.manager_id = e2.employee_id ;
-- join 키워드 사용
-- join 조인할 테이블명
-- on 조인조건식
select
e1.employee_id as 사원번호,
e1.first_name as 이름,
e1.manager_id as "상사의 사원번호",
e2.first_name as "상사의 이름"
from employees e1
join employees e2
on e1.manager_id = e2.employee_id ;
-- steven을 등장 -> 왼쪽 외부 조인
-- 사원번호, 이름, 상사의 사원번호, 상사의 이름
select
e1.employee_id as 사원번호,
e1.first_name as 사원이름,
coalesce(e1.manager_id, '없음') as "상사의 사원번호",
coalesce(e2.first_name, '내가 보스') as "상사의 이름"
from
employees e1
left join employees e2
on e1.manager_id = e2.employee_id ;
-- 킴벌리 등장
-- 사원번호, 이름, 부서번호, 부서명
select
e.employee_id ,
e.first_name ,
e.department_id ,
d.department_name
from
employees e
left join departments d
on e.department_id = d.department_id
order by e.employee_id;
-- 사원번호, 이름, 급여, 부서번호, 부서명, 부서의 주소, 부서의 우편번호, 부서의 소속도시
select
e.employee_id as 사원번호,
e.first_name as 이름,
e.salary as 급여,
e.department_id as 부서번호,
d.department_name as 부서명,
l.street_address as '부서의 주소',
l.postal_code as '부서의 우편번호',
l.state_province as '부서의 소속도시'
from
employees e, departments d, locations l
where
e.department_id = d.department_id -- (FK와 PK 연결. 다대일의 관계)
and
d.location_id = l.location_id -- (FK와 PK 연결. 다대일의 관계)
-- 다대일의 관계 : PK에서는 유일하지만 FK에서는 중복된 값 가질 수 있음
order by
e.employee_id ;
-- join 키워드 사용
-- 조인 순서를 바꾸지 않도록 -> 바꾸면 에러
select
e.employee_id as 사원번호,
e.first_name as 이름,
e.salary as 급여,
e.department_id as 부서번호,
d.department_name as 부서명,
l.street_address as '부서의 주소',
l.postal_code as '부서의 우편번호',
l.state_province as '부서의 소속도시'
from
employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id -- join은 순서대로 진행되기 때문에 순서가 바뀌면 안됨
-- employees랑 locations는 공통칼럼이 없기 때문에 에러남
order by
e.employee_id ;
-- 서브쿼리(쿼리 안의 쿼리)
-- Nancy보다 더 많이 받고 있는 사원들 조회
-- 사원번호, 이름, 급여
select
employee_id,
first_name,
salary
from
employees
where
salary > (select salary
from employees
where first_name = 'Nancy');
-- 부서번호가 50번인 사원들의 최대급여 보다 더 많은 급여를 받는 사원들
-- 사원번호, 이름, 급여
select
employee_id ,
first_name ,
salary
from employees
where salary > (select max(salary)
from employees
where department_id = 50);
-- 서브쿼리에서 결과가 두개 이상일때
-- Nancy와 Daniel의 급여
select salary
from employees
where first_name in('Nancy', 'Daniel');
-- any : 서브쿼리의 결과가 둘 이상일 때 (OR)
select
first_name,
salary
from employees
where salary > any (select
salary
from employees
where first_name in ('Nancy', 'Daniel'));
-- 낸시나 다니엘 둘중 한명의 급여보다 크다면
-- all : 서브쿼리의 결과가 둘 이상일 때 (AND)
select
first_name,
salary
from employees
where salary > all (select
salary
from employees
where first_name in ('Nancy', 'Daniel'));
-- 낸시나 다니엘 둘중 한명의 급여보다 크다면
-- 급여를 많이 받는 순으로 정렬
-- ORDER BY 컬럼명
-- ASC : 오름차순 (작 -> 큰) (생략가능)
-- DESC : 내림차순 (큰 -> 작) (생략불가)
select * from employees
order by salary desc;
-- 중복제거
select
distinct -- 중복제거
salary
from
employees;
-- 급여 많이 받고 있는 사원들 TOP5
select * from employees
order by salary desc
limit 5; -- 추출된 결과에서 5개만
-- LIMIT 시작, 개수(시작은 생략가능: 첫데이터부터)
-- LIMIT 개수 OFFSET 시작
-- 부서별로 급여의 합계
-- 부서번호, 합계
select
department_id as 부서번호,
sum(salary) as '급여의 합계'
from employees
group by department_id ;
-- 부서별로 급여의 합계, 급여합계가 100000달러 이상인 부서만 추출
select
department_id as 부서번호,
sum(salary) as 급여합계
from employees
group by department_id
having 급여합계 >= 100000;
-- 테이블 복사
-- departments 테이블 복사해서 연습용 테이블
drop table if exists departments_temp; -- 테이블이 존재하면 삭제
create table departments_temp
(select * from departments);
select * from departments_temp;
describe departments;
desc departments_temp;
-- DML(데이터 조작어) : SELECT(조회), INSERT(삽입), UPDATE(수정), DELETE(삭제)
-- 테이블에 데이터 넣기
-- INSERT INTO 테이블명(컬럼명1,컬럼명2, ...)
-- VALUES(컬럼명1에 들어갈 값, 컬럼명2에 들어갈 값 ...)
insert into departments_temp (department_id, department_name, manager_id, location_id)
values(280, 'Database', 100, 1700);
select
*
from
departments_temp;
-- null: 없다. 아직 정해지지 않았다
insert into departments_temp (department_id, department_name, manager_id, location_id)
values(290, 'Database2', 100, null);
-- 컬럼 생략
insert into departments_temp (department_id, department_name, manager_id)
values(290, 'Database3', 100);
-- 부서번호 310, 부서명 Database4, 상사 ID, 위치 ID는 NULL
insert into departments_temp (department_id, department_name)
values(310, 'Database4', null, null);
-- NOT NULL 제약조건(속성)이 들어가 있는 컬럼에는 null 데이터를 넣을 수 없다.
insert into departments_temp (department_id, department_name)
values(null, null);
-- 모든 컬럼값을 넣을거라면 테이블명 뒤에 컬럼 나열 생략 가능
insert into departments_temp
values(320, 'Database5', 100, 1700);
select * from departments_temp;
-- 롤백 : 되돌리기, 커밋 : 확정
-- 윈도우 -> 설정 -> 연결 -> 연결 유형 -> auto commit by default 체크해제
-- database -> 트랜잭션 모드 -> manual commit(수동)
-- 데이터 수정
-- UPDATE 테이블명
-- SET 컬럼명1 = 수정할 값1, 컬럼명2 = 수정할 값2
-- WHERE 조건식으로 특정
-- 310번 부서에 상사 ID는 101, 위치ID는 1800
update departments_temp
set manager_id = 101, location_id = 1800
where department_id = 310;
select * from departments_temp;
-- 마지막 commit한 시점으로 돌아가기
rollback;
-- 데이터 변경 확정
commit;
-- 트랜젝션 발생(데이터를 변경) : INSERT, UPDATE, DELETE
-- ROLLBACK과 COMMIT의 대상
-- TCL(ROLLBACK, COMMIT)
-- SELECT는 데이터를 변경시키지 않으므로 트랜잭션을 발생시키지 않는다.
-- 데이터 삭제
-- DELETE FROM 테이블명
-- WHERE 조건식;
select * from departments_temp;
-- departments_temp에 있는 320번 부서를 삭제
delete from departments_temp
where department_id = 320;
-- 데이터 타입
/*
* 1. 정수형 타입
* - TINYINT : -128 ~ 127
* - SMALLINT : -32,768 ~ 32,767
* - MEDIUMINT : -8,388,608 ~ 8,388,607
* - INT or INTEGER : -2^31 ~ 2^31 - 1
* - BIGINT : -2^63 ~ 2^63 - 1
*
* 2. 부동 소수점(실수)
* - FLOAT
* - DOUBLE
*
* 3. 문자형
* - CHAR(n) : 고정길이 최대바이트수 n
* - VARCHAR(n) : 가변길이 최대바이트수 n
* - TEXT : 매우 긴 문자열
*
* 4. 날짜 및 시간형
* - DATE : 날짜 (YYYY-MM-DD)
* - TIME : 시간 (HH:MM:SS)
* - DATETIME or TIMESTAMP : 날짜와 시간
* - YEAR : 연도
*
* 5. 진위형
* - BOOL or BOOLEAN : True, False
*
* 6. 숫자타입
* - DECIMAL(n) : 전체자릿수 n ex) DECIMAL(6) : 숫자 6자리
* - DECIMAL(n, n2) : 전체자릿수 n, 소수점은 n2자리 만큼만 허용
* ex) DECIMAL(6, 2) : 1234.56(O), 123.456(X), 12345.67(X)
* - DECIMAL OR NUMERIC
*/
-- 테이블 만들기
desc employees ;
-- employee_id decimal(6,0)
-- first_name varchar(20)
-- last_name varchar(25)
-- email varchar(25)
-- phone_number varchar(20)
-- hire_date date
-- job_id varchar(10)
-- salary decimal(8, 2)
-- commission_pct decimal(2, 2)
-- manager_id decimal(6, 0)
-- department_id decimal(4, 0)
-- CREATE TABLE 내가 만들 테이블명(
-- 컬럼명1 데이터타입,
-- 컬럼명2 데이터타임,
-- ...
-- );
create table employees_temp(
employees_id decimal(6,0),
first_name varchar(20),
last_name varchar(25),
email varchar(25),
phone_number varchar(20),
hire_date date,
job_id varchar(10),
salary decimal(8,2),
commission_pct decimal(2,2),
manager_id decimal(6,0),
department_id decimal(4,0)
);
desc employees_temp ;
select * from employees_temp;
-- 테이블을 변경하는 명령어 ALTER
select * from departments_temp;
-- 테이블에 새로운 컬럼 추가
-- ALTER TABLE 테이블명
-- ADD 추가할컬럼명 데이터타입
alter table departments_temp
add hp varchar(20);
desc departments_temp ;
select * from departments_temp ;
-- 데이터 1개 넣기
-- 320, 'DB', 100, 1700, '123-456'
insert into departments_temp
values(320, 'DB', 100, 1700, '123-456');
-- 컬럼 이름 변경
-- ALTER TABLE 테이블명
-- RENAME COLUMN 현재컬럼명(바꿀대상) to 바꿀컬럼명
alter table departments_temp
rename column hp to tel;
desc departments_temp;
select * from departments_temp;
-- 컬럼의 자료형(데이터타입) 변경
-- ALTER TABLE 테이블명
-- MODIFY 컬럼명 바꿀데이터타입
-- 주의) 기존에 들어가 있는 데이터에 반하는 데이터타입은 변경되지 않음
-- ex) 123-456이 들어있는 칼럼을 숫자타입으로 변경 X
alter table departments_temp
modify tel int;
alter table departments_temp
modify tel varchar(30);
desc departments_temp ;
-- 컬럼 삭제
-- 해당 칼럼의 데이터는 당연히 삭제되므로 주의
-- ALTER TABLE 테이블명
-- DROP COLUMN 삭제할컬럼명
-- ALTER는 ROLLBACK의 대상이 아님
alter table departments_temp
drop column tel;
select * from departments_temp;
-- 테이블 이름을 변경
-- RENAME TABLE 기존테이블명 to 바꿀테이블명
rename table departments_temp to departments_rename;
desc departments_rename ;
select * from departments_rename;
-- 다시 원래대로
rename table departments_rename to departments_temp;
desc departments_temp ;
select * from departments_temp ;
-- 테이블의 전체 데이터 삭제
-- 1) DELETE FROM 테이블명; (ROLLBACK 가능)
delete from departments_temp;
rollback;
select * from departments_temp;
-- 2) TRUNCATE TABLE 테이블명; (rollback 불가능) -> 사용에 주의
truncate table departments_temp;
-- 테이블 삭제
-- DROP TABLE 테이블명; => 테이블이 없으면 에러
-- DROP TABLE IF EXISTS 테이블명; => 테이블이 없어도 에러X
drop table if exists departments_temp;
desc departments_temp;
select * from departments_temp;
-- 기본키(PK: Primary Key) 설정
-- PK: UNIQUE(중복 불가), NOT NULL(null을 허용하지 않겠다)
create table table_1(
id int primary key,
password varchar(20)
);
desc table_1;
select * from table_1;
insert into table_1
values(3, null);
-- auto_increment : 데이터 타입을 int or bigint
-- 중복되면 안되는 PRIMARY KEY와 같이 쓰임
drop table if exists table_1;
create table table_1(
id int auto_increment primary key,
password varchar(20)
);
desc table_1;
-- auto_increment 사용방법 : 생략하면 됨
insert into table_1(password)
value('abcd');
select * from table_1 ;
-- 1씩 증가되서 들어가는데 중간에 삭제하거나 하더라도 그 다음 숫자가 들어간다.
-- ex) 1 ~ 16 : 13번 삭제. 그 이후에 등록되는 번호는 13번이 아니라 17번
-- 제약 조건
-- NOT NULL : 해당 컬럼에 null이 들어오지 못하도록
drop table if exists table_nn;
create table table_nn(
id varchar(20) not null,
password varchar(40) not null,
tel varchar(30)
);
desc table_nn;
insert into table_nn
values(1, 1, null);
select * from table_nn;
insert into table_nn
values(null, null, null);
update table_nn
set id = null,
where id = '1'; -- 수정쿼리로도 null을 집어넣을 수 없다.
-- 이미 만들어진 테이블에 제약조건 추가
-- ALTER TABLE 테이블명
-- MODIFY 바꿀컬럼명 바꿀데이터타입 추가할제약조건
-- NOT NULL 제약조건을 추가할 거라면 기존의 데이터에 NULL이 있으면 안된다.
alter table table_nn
modify tel int not null; -- null이 들어가 있어서 NOT NULL로 변경이 안됨
truncate table table_nn; -- 데이터를 날려버리고 변경이 가능
desc table_nn;
-- 이미 만들어진 테이블에 제약조건 삭제
alter table table_nn
modify tel int;
desc table_nn;
-- 제약조건명(CONSTRAINT_NAME) 확인하는 방법(모든 데이터베이스에 모든 제약조건들)
select
*
from
information_schema.table_constraints
where
TABLE_SCHEMA = 'hr' -- 확인할 데이터베이스 이름
and TABLE_NAME = 'table_1'; -- 확인할 테이블 이름
-- UNIQUE : 중복데이터 X
-- 제약조건명을 주면서 제약조건을 걸고 싶을 때
-- CONSTRAINT 제약조건명 UNIQUE(제약조건을 줄 컬럼명)
-- 제약조건명 네이밍 방법 : 제약조건이름_테이블명_제약조건받을컬럼명
create table table_unq(
id varchar(20),
password varchar(30) not null,
tel varchar(40),
constraint unq_tblunq_id unique(id)
);
desc table_unq;
-- UNIQUE 중복 허용 X
insert into table_unq
values('123', '123', '123-456');
-- null은 중복으로 판단하지 않기 때문에 문제 발생 -- UNIQUE는 단독으로 주지 않는다.
insert into table_unq
values(null, '123', '123-456');
select * from table_unq;
-- 이미 만들어진 테이블에 UNIQUE 제약조건을 추가
-- 제약조건명을 주면서 추가
-- ALTER TABLE 테이블명
-- ADD CONSTRAINT 제약조건명 UNIQUE(제약조건을 걸 컬럼명)
truncate table table_unq;
alter table table_unq
add constraint unq_tblunq_tel unique(tel);
desc table_unq;
-- 제약조건을 주는 방법
-- 1) 테이블을 만들면서 바로 주는 방법
-- 2) 테이블을 만들고 나서 뒤에 수정으로 주는 방법
-- unique 제약조건을 삭제(제약조건명이 필요한 이유)
-- ALTER TABLE 테이블명
-- DROP INDEX 제약조건명;
alter table table_unq
drop index unq_tblunq_tel;
desc table_unq ;
-- 제약조건명 변경 (삭제 후 다시 추가)
-- PRIMARY KEY (UNIQUE, NOT NULL을 가짐)
-- PRIMARY KEY는 테이블당 하나이므로 따로 제약조건명을 붙여서 관리할 필요 X
create table table_pk(
id varchar(20) primary key,
password varchar(20) not null,
tel varchar(40) unique
);
desc table_pk;
-- 중복값 허용 안함(UNIQUE 속성을 가지기 때문에)
insert into table_pk
values('123', '123', '123-456');
-- null을 허용 안함(NOT NULL 속성을 가지기 때문에)
insert into table_pk
values(null, '123', '123-456');
select * from table_pk;
-- PK 삭제
-- ALTER TABLE 테이블명
-- DROP PRIMARY KEY
alter table table_pk
drop primary key; -- NOT NULL 속성은 살아 있음
desc table_pk;
-- PK 다시 지정
-- 테이블을 일단 만든 후에 나중에 PK 지정
-- ALTER TABLE 테이블명
-- ADD PRIMARY KEY(컬럼명)
truncate table table_pk;
alter table table_pk
add primary key(id);
-- 참조키(외래키, FK, Foreign key)
-- 사원테이블
-- department_id : FK (다른 테이블의 PK를 가지고 있는 형태)
desc employees ; -- MUL : 참조키
-- 부서테이블
-- department_id : PK
-- 조인
-- 사원번호, 이름, 급여, 부서번호, 부서명
select
e.employee_id ,
e.first_name ,
e.salary ,
e.department_id ,
d.department_name
from employees e
join departments d
on e.department_id = d.department_id ;
-- employees와 departments 테이블 사본
-- 사본을 만들면 컬럼명과 컬럼의 데이터 타입, NOT NULL 속성은 가져오지만
-- 그 외 제약조건은 가져오지 않음 (ex: PK, FK, UNIQUE)
drop table if exists employees_temp;
create table employees_temp
(select * from employees);
select * from employees_temp;
desc employees_temp;
drop table if exists departments_temp;
create table departments_temp
(select * from departments);
select * from departments_temp;
desc departments_temp;
-- 참조키로 연결하기
-- 먼저 연결하고자 하는 테이블의 PK 지정해주기
alter table departments_temp
add primary key(department_id);
-- 참조키 연결
-- ALTER TABLE 테이블명
-- ADD CONSTRAINT 제약조건명
-- FOREIGN KEY(참조키 설정할 컬럼명) REFERENCES 참조할테이블명(참조할 컬럼명: 일반적으로 그 테이블의 PK)
alter table employees_temp
add constraint fk_emptemp_dptid
foreign key(department_id) references departments_temp(department_id);
-- 참조키 제약조건
-- departments_temp 테이블에 있는 부서번호(10 - 270)가 아닌 사원은 employees_temp에 등록, 변경 불가
-- 존재하지 않는 부서번호를 입력하는 것을 방지
-- 테이블끼리의 연관관계 (사원테이블과 부서테이블의 관계는 다대일의 관계)
-- 다: FK, 일: PK
select * from departments_temp;
-- 등록할때도 있는 부서번호로
insert into employees_temp
values(501, '길동', '홍', 'aaa@aaa', '111-111', '2024-11-21', 'CLERK', 5000, null, 100, 270);
-- 수정쿼리를 이용할때도 없는 부서는 안됨
update employees_temp
set department_id = 33
where employees_id = 501;
select * from employees_temp
order by employee_id desc;
-- 부서에 소속된 사원이 있다면 부서 삭제 불가
delete from departments_temp
where department_id = 250; -- 소속 사원이 없는 경우
select * from departments_temp;
-- 소속된 사원이 있는 경우(department_id를 가지고 있는 데이터가 employees_temp에 있는 경우)
delete from departments_temp
where department_id = 270;
-- 부모데이터(글, 부서(PK))가 삭제되면 자식데이터(댓글, 사원)도 삭제되도록 허용 하고 싶을 때
-- on delete cascade
create table board(
id int auto_increment,
title varchar(100) not null,
content varchar(500) not null,
constraint pk_board_id primary key(id)
);
insert into board(title, content)
values('제목3', '내용3');
select * from board;
-- 댓글 테이블
create table reply(
id int auto_increment,
content varchar(300) not null,
board_id int,
constraint pk_reply_id primary key(id),
constraint fk_reply_bid foreign key(board_id) references board(id) on delete cascade
);
insert into reply(content, board_id)
values('리플1-1', 1);
-- 부모데이터(글) 삭제
select * from board;
select * from reply;
delete from board
where id = 1;
-- 부모데이터를 참조하고 있는 상황에서 부모데이터가 삭제될 경우 자식데이터도 같이 삭제되도록 지정하는 옵션
-- on delete cascade
-- 참조키 삭제
-- ALTER TABLE 테이블명
-- DROP FOREIGN KEY 삭제할 제약조건명;
alter table employees_temp
drop foreign key fk_emptemp_dptid;
-- 참조키 제약조건명 변경은 삭제 후 다시 만들기
-- 참조키 위배된 데이터가 있는 경우 참조키 설정 불가.
-- 제약조건 : NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
-- CHECK 제약 조건
create table table_chk(
name varchar(20),
age int,
gender char(1),
constraint chk_tblchk_gender check(gender in('M', 'F')) -- M과 F만 들어올수 있게
); -- MySQL은 대소문자 구분X
desc table_chk;
select * from table_chk;
-- 이미 만들어진 테이블의 컬럼에 check 제약 조건 추가
alter table table_chk
add constraint chk_tblchk_gender check(gender in('M', 'F'));
insert into table_chk -- 다 추가하는거면 컬럼명 생략가능 (순서 지켜야함)
values('홍길동', 30, 'A'); -- CHEKC 제약조건에 의해 추가 불가능
insert into table_chk
values('홍길동', 30, 'M');
update table_chk
set gender = 'A'; -- CHECK 제약조건에 의해 수정쿼리로도 수정 불가능
-- check 제약 조건 삭제
-- ALTER TABLE 테이블명
-- DROP CHECK 제약조건명;
alter table table_chk
drop check chk_tblchk_gender;
-- DEFAULT
-- 값이 들어오지 않거나 정해지지 않은 경우 DEFAULT로 지정한 기본값으로 등록, 수정
create table table_df(
id int auto_increment primary key,
name varchar(20) default '홍길동', -- name에 아무것도 입력하지 않으면 홍길동이 들어감
-- create_date date default (current_date) -- 날짜만 필요하다면
create_date datetime default current_timestamp -- 날짜와 시간
);
desc table_df;
insert into table_df
values(); -- 아무것도 입력하지 않아도 default랑 auto_increment로 들어가서 가능.
select * from table_df;
delete from table_df
where id = 5;
-- 가장 최근에 증가시킨 auto_increment의 값이 얼마인가
-- 5가 추출됐다면 그 다음에 insert는 6으로 들어가겠구나 예상
select last_insert_id(); -- auto_increment가 여러 테이블에 걸려있다면 가장 최근에 증가한 id값
-- 초기값을 1000부터 하고 싶다
-- ALTER TABLE 테이블명
-- AUTO_INCREMETN = 초기값
alter table table_df
auto_increment = 1000;
-- 증가값을 변경하고 싶다.(기본값은 1씩 증가)
-- 주의: 서버 기준이 되기 때문에 모든 auto_increment 속성을 가지는 컬럼들이 동시에 변경 (위험)
-- set @@AUTO_INCREMENT_INCREMENT = 증가값;
set @@auto_increment_increment = 1;
-- 한 쿼리로 데이터 여러개 넣기.
-- values 뒤에 붙는 값이 들어가는 ()는 ,(콤마)를 구분자로 나열할 수 있다.
create table table_2(
id varchar(20),
name varchar(30)
);
insert into table_2 values('aaa', 'aaa'),('bbb', 'bbb'),('ccc', 'ccc');
select * from table_2;
-- 테이블의 구조만 복사
-- 테이블의 데이터는 제외하고 컬럼명, 데이터타입, NOT NULL, DEFAULT 속성만 가져와서 테이블 만들고 싶다?
-- CREATE TABLE 테이블명
-- (SELECT 조회 where 1 <> 1)
-- 1 <> 1 라는 조건 : 1은 1과 같지 않다는 무조건 false이기 때문에 어떤 데이터도 추출되지 않고
-- 해당 테이블의 구조만 가져올 수 있다.
-- 1 <> 1을 안쓰면 데이터까지 복사 가능
create table table_df2(
select * from table_df where 1 <> 1
);
desc table_df2;
select * from table_df2;
select * from table_df;
-- INSERT_INTO와 SELECT를 이용하여 조회된 데이터를 바로 넣을 수 있다.
-- INSERT_INTO 데이터를 넣을 테이블명
-- SELECT로 조회
-- 주의: 데이터를 넣을 테이블과 조회한 테이블의 컬럼의 개수와 데이터타입이 맞아야 한다.
insert into table_df2
select * from table_df;
-- INSERT 오류 발생시 대응 방법
create table table_in(
id varchar(20) primary key,
name varchar(30)
);
-- id가 aaa 이름은 홍길동
insert into table_in values('aaa', '홍길동');
commit;
select * from table_in;
-- 한꺼번에 3개 실행 (블록 씌우고 alt + x)
insert into table_in values('aaa', '홍길동'); -- primary key 위배
insert into table_in values('bbb', '손오공'); -- 위의 쿼리때문에 아래 정상적인 쿼리들도 실행되지 않음
insert into table_in values('ccc', '사오정');
select * from table_in;
-- 첫번재 방법
-- 만약 오류가 난다면 그냥 건너뛰고 나머지는 실행해라
-- INSERT INTO 사이에 IGNORE 추가
-- INSERT IGNORE INTO 테이블명 VALUES(...);
insert ignore into table_in values('aaa', '홍길동'); -- 위배되는것은 무시하고 뒤의 쿼리들 실행
insert ignore into table_in values('bbb', '손오공');
insert ignore into table_in values('ccc', '사오정');
-- 두번째 방법
-- 만약 오류가 난다면 다른걸로 수정해 달라
-- PK 존재 : UPDATE, PK 존재X : INSERT
-- INSERT 구문 후에 이어서
-- ON DUPLICATE KEY UPDATE 컬럼명 = 값, 컬럼명2, = 값2, ....;
insert into table_in values('aaa', '홍길동')
on duplicate key update name = '저팔계'; -- PK가 존재하므로 update 실행
select * from table_in;
insert into table_in values('bbb', '손오공')
on duplicate key update name = '손오공'; -- PK가 존재하지 않으므로 insert 실행
-- WITH
-- 부서별 최고 급여 액수의 평균을 추출
select
department_id as 부서,
max(salary) as 최고급여액
from employees
where department_id is not null
group by department_id;
-- 위의 쿼리 결과값이 그냥 aa라는 테이블이였다면?
select
round(avg(최고급여액))
from aa;
-- 이럴 때 WITH절 사용.
-- 쿼리를 날려서 나온 결과가 존재하고 있는 테이블로 생각하고 쿼리를 만들 수 있다.
-- WITH CTE_테이블명(임의로) (컬럼명 혹은 별칭 나열)
-- AS (SELECT 조회 결과)
-- SELECT ... FROM CTE_테이블명;
with aa(부서, 최고급여액)
as (
select
department_id as 부서,
max(salary) as 최고급여액
from employees
where department_id is not null
group by department_id
)
select round(avg(최고급여액)) from aa;
-- 부서별 최저급여를 다 더한값 추출
with bb(부서, 몇명, 가장적은급여) -- 별칭을 여기서 줄수도 있음
as (
select
department_id,
count(*),
min(salary)
from
employees
group by department_id
)
select sum(가장적은급여) from bb;
-- IF(조건식, 참일때 값, 거짓일때 값)
select if(2 < 1, '맞다', '아니다');
-- 급여가 8000이상이면 많다 아니면 적다
-- 이름, 급여, 결과(result)
select
first_name,
salary,
if(salary >= 8000, '많다', '적다') as result
from employees;
-- IFNULL : COALESCE의 사용방법 동일
-- 커미션이 없으면 NULL 대신 0으로 (null은 어떤 연산을 하더라도 다 null로 반환하는것을 주의)
-- IFNULL(null이 들어있는 컬럼명, null이면 줄 값)
select
first_name,
commission_pct,
ifnull(commission_pct, 0) as 결과
from employees;
-- VIEW : 일반적인 사용자 입장에서는 TABLE과 동일한 개체
-- 왜 만드나? : 테이블의 특정칼럼들을 추출하거나 조건을 붙여서 결과를 확인할 때
-- 해당 결과를 자주 추출해야 하는 경우, 매번 쿼리를 치지 않고 VIEW를 활용해서
-- 마치 하나의 테이블처럼 사용하고자 할때
-- CREATE VIEW 뷰이름(임의로)
-- AS (자주 추출해야 하는 SELECT절);
-- 사원번호, 이름, 급여, 부서번호, 부서명, 부서의 주소, 부서의 우편번호
-- 정렬은 사원번호순으로 (오름차순)
select
e.employee_id as 사원번호,
e.first_name as 이름,
e.salary as 급여,
e.department_id as 부서번호 ,
d.department_name as 부서명,
l.street_address as '부서의 주소',
l.postal_code as '부서의 우편번호'
from employees e
left join departments d
on e.department_id = d.department_id
left join locations l
on d.location_id = l.location_id
order by e.employee_id asc;
create view employees_information
as (select
e.employee_id as 사원번호,
e.first_name as 이름,
e.salary as 급여,
e.department_id as 부서번호 ,
d.department_name as 부서명,
l.street_address as '부서의 주소',
l.postal_code as '부서의 우편번호'
from employees e
left join departments d
on e.department_id = d.department_id
left join locations l
on d.location_id = l.location_id
order by e.employee_id asc
);
select * from employees_information;
-- 뷰 삭제
drop view if exists employees_information;
-- 연습 문제
-- 매니저가 같은 사원들별로 최저급여을 추출
-- 1. 매니저가 없을 경우 제외 -- 그룹화 전에 조건 where
-- 2. 최저급여가 6000 미만인 경우 제외 -- 그룹화 후에 조건 having
-- 3. 급여 높은 순으로 정렬
select
manager_id as 매니저ID,
min(salary) as 최저급여
from
employees
where manager_id is not null -- 1번 조건 해결
group by manager_id
having 최저급여 >= 6000
order by 최저급여 desc;
