[MySQL] MySQL 문법 요약 - 섹션 1. MySQL select 쿼리문

1. 기본 정보 조회

-- 데이터베이스 조회하기. show database; 로그인한 계정으로 사용가능한 데이터베이스 목록이 조회됨
-- 데이터베이스 사용하기. use 데이터베이스명; 그냥 더블클릭 해도됨. use employees;
-- 선택된 데이터베이스 내의 테이블을 조회함. show tables;
-- 선택된 테이블 내의 컬럼들을 조회함. desc 테이블명;  desc departments;

2. select 문 기본 및 연산자
-- 사원의 정보를 모두 가져온다.
select *
from employees;

-- 산술 연산자
select 20 + 10; / select 20 - 10; / select 20 * 10; / select 20 / 10;

-- 데이터에 산술연산하기
select salary, salary + 1000, salary - 1000, salary * 1000, salary / 1000
from salaries;

-- 각 사원의 사원번호, 급여액, 10%인상된 급여액을 가져온다.
select emp_no, salary, salary * 1.1
from salaries;

-- 문자열 컬럼 연산 (문자열을 0으로 취급하여 연산한다)
select first_name + 100
from employees;

-- 근무 부서 테이블에서 부서 번호를 중복되지 않게 가져온다. distinct 사용
select distinct dept_no
from dept_emp;

 

3. 조건절  (select 문에서 where 키워드를 사용하면 조건을 사용할수 있다.)
-- d005부서 매니저의 사원의 사원번호, 부서번호를 가져온다.
select emp_no, dept_no
from dept_manager
where dept_no = 'd005';

-- 부서가 d003이 아닌 매니저 사원들의 사원번호와 부서번호를 가져온다. (not = <>)
select emp_no, dept_no
from dept_manager
where dept_no <> 'd003';

-- 급여액이 150000 이상인 사원의 사원번호, 급여액을 가져온다.
select emp_no, salary
from salaries
where salary >= 150000;

-- 1986년 이후에 입사한 사원들의 사원번호, 입사일, 성, 이름을 가져온다.(날짜는 문자열처럼 ' '안에 써주기)
select emp_no, hire_date, gender, first_name, last_name
from employees
where hire_date >= '1986-01-01';

 

4. 논리연산자 (and, or, <>, between, in 사용)
-- d001 부서의 매니저 중에 1990년 이후부터 매니저인 사원의 사원번호, 부서번호, 매니저 시작날짜를 가져온다.
select emp_no, dept_no, from_date
from dept_manager
where dept_no = 'd001' and from_date >= '1990-01-01';

-- 1990년 이후에 입사한 남자사원의 사원번호, 성별, 입사일을 가져온다.
select emp_no, gender, hire_date
from employees
where hire_date >= '1990-01-01' and gender = 'M';

-- d001 부서와 d002부서 매니저의 사원번호, 부서번호를 가져온다.(or사용)
select emp_no, dept_no
from dept_manager
where dept_no = 'd001' or dept_no = 'd002';

-- 부서번호가 d003이 아닌 매니저의 사원번호와 부서번호를 가져온다.(<>사용)
select emp_no, dept_no
from dept_manager
where dept_no <> 'd003';   -- where not dept_no = 'd003'; 이렇게 해도 같은결과.

-- 급여가 60000이상 70000미만인 사원들의 사원번호, 급여를 가져온다.(between사용. -> and대신 사용)
select emp_no, salary
from salaries
where salary between 60000 and 69999; -- 미만을 나타내 주려면 이렇게 해줘야함.

-- d001부서와 d002부서 매니저의 사원번호, 부서명을 가져온다.(in 사용. -> or대신 사용)
select emp_no, dept_no
from dept_manager
where dept_no in('d001','d002');

 

5. like
-- like : 조건식을 만들어 줄때 문자열과 비교시 사용. 원래 = 를 썼는데, 이보다 더 확장해서 조건을 만들어 줌. (와일드카드 개념)
-- _ : 글자 하나를 의미

-- % : 글자 수와 상관없이 모든 글자를 의미

-- 이름이 A로 시작하는 사원의 사원번호, 이름을 가져온다.
select emp_no, first_name
from employees
where first_name like 'A%';

-- 이름의 두번째 글자가 i 인 사원의 사원번호, 이름을 가져온다.
select emp_no, first_name
from employees
where first_name like '_i%';

-- 이름에 o가 포함되어 있는 사원의 사원번호, 이름을 가져온다. 단, 마지막 글자가 o가 아닌 사원만 가져온다.
select emp_no, first_name
from employees
where first_name like '%o%' and not first_name like '%o';

-- 이름이 5글자인 사원의 사원번호, 이름을 가져온다.
select emp_no, first_name
from employees
where first_name like '_____';

 

6.  정렬

(오름차순, 내림차순 /  정렬기준은 숫자, 문자열, 날짜 등 모든 컬럼이 가능하다.)
-- 오름차순 : Order by 컬럼명 asc (asc는 생략가능) 
-- 내림차순 : Order by 컬럼명 desc 

-- 사원의 번호와 급여를 가져온다. 급여를 기준으로 오름차순 정렬한다.(where절은 현재 없으니까 지워주기)
select emp_no, salary
from salaries
order by salary asc;     -- asc생략가능!!

-- 사원의 번호와 이름을 가져온다. 이름을 기준으로 오름차순 정렬한다.
select emp_no, first_name
from employees
order by first_name asc;


7. 숫자 함수
-- ABS(숫자) : 절대값을 구한다.
-- CEIL(숫자) : 값보다 큰 정수 중 가장 작은 정수. 소수점 이하 올림.
-- FLOOR(숫자) : 값보다 작은 정수 중 가장 큰 정수. 소수점 이하 버림.
-- ROUND(숫자, 자릿수) : 자릿수를 기준으로 반올림 한다.
-- TRUNCATE(숫자, 자릿수) : 자릿수를 기준으로 버림 한다.
-- POW(X,Y) or POWER(X,Y) : X의 Y승
-- MODE(분자, 분모) : 분자를 분모로 나눈 나머지를 구한다.
-- GREATEST(숫자1, 숫자2, 숫자3) : 주어진 숫자 중에 가장 큰 값을 반환한다.
-- LEAST(숫자1, 숫자2, 숫자3) : 주어진 숫자 중에 가장 작은 값을 반환한다.

-- 절대값
select abs(100), abs(-100);

-- 소수점 이하 올림
select ceil(10.1), ceil(10.4), ceil(10.5), ceil(10.8);   //11

-- 소수점 이하 내림
select floor(10.1), floor(10.4), floor(10.5), floor(10.8);  //10

-- 반올림
select round(10.1), round(10.4)  //10  , round(10.5), round(10.8);  //11
select round(166.555, 0), round(166.555, 1), round(166.555, -1);
-- 0이면 소수점 첫번째에서 반올림, 1이면 소수점 두번째에서 반올림, -1이면 정수 첫번째자리에서 반올림

-- 버림
select truncate(166.555, 0), truncate(166.555, 1), truncate(166.555, -1);

-- x의 y승
select pow(10,2);  //100

-- 나머지 구하기
select mod(10,3);  //1

-- 최대 숫자 구하기
select greatest(10, 4, 20, 1);

-- 최소 숫자 구하기
select least(10, 4, 20, 1);


8. 문자열 함수 (문자열에 대한 작업을 하는 함수)
-- CONCAT(문자열1, 문자열2, 문자열3...) : 문자열을 합친다.
-- INSERT(문자열, 시작위치, 길이, 새로운문자열) : 문자열의 시작위치부터 길이 만큼의 문자열을 제거하고, 그자리에 새로운 문자열을 삽입한다.
-- REPLACE(문자열, 기존문자열, 새로운문자열) : 문자열에서 기존문자열을 찾아 제거하고, 그 자리에 새로운 문자열을 삽입한다.
-- INSTR(문자열1, 문자열2) : 문자열1에서 문자열2를 찾아 위치를 반환한다. 위치는 1부터 시작하며 문자열2를 찾지 못하면 0을 반환한다.

select concat('aaa', 'bbb', 'ccc');   //aaabbbccc
select insert('aaaaa', 2, 2, 'bbb');   //abbbaa
select insert('aaaaa', 2, 0, 'bbb');   //abbbaaaa
select replace('aabbcc', 'bb', 'ff');  //aaffcc
select instr('abcdefg', 'cde');  //3

-- LEFT(문자열, 개수) : 문자열의 좌측부터 개수만큼 가져온다.
-- RIGHT(문자열, 개수) : 문자열의 우측부터 개수만큼 가져온다.
-- MID(문자열, 시작위치, 개수) : 문자열의 시작위치에서 개수만큼 가져온다.
-- SUBSTRING(문자열, 시작위치, 개수) : 문자열의 시작위치에서 개수만큼 가져온다.
-- LTRIM(문자열) : 문자열의 좌측 공백을 제거한다.
-- RTRIM(문자열) : 문자열의 우측 공백을 제거한다.
-- TRIM(문자열) : 문자열의 좌우측 공백을 제거한다.

select left('abcdefg', 3);
select right('abcdefg', 3);
select mid('abcdefg', 3, 3);
select substring('abcdefg', 3, 3);
select concat('[', '        abc        ',']');
select concat('[', ltrim('        abc        '),']');
select concat('[', rtrim('        abc        '),']');
select concat('[', trim('        abc        '),']');

-- LCASE(문자열), LOWER(문자열) : 문자열을 모두 소문자로 변경한다.
-- UCASE(문자열), UPPER(문자열) : 문자열을 모두 대문자로 변경한다.
-- REVERSE(문자열) : 문자열을 반대로 가져온다.

select lcase('abCDef');
select lower('abCDef');
select ucase('abCDef');
select upper('abCDef');
select reverse('abcdef');

-- 사원의 이름을 가져온다. 성과 이름(first_name 과 last_name)을 하나의 문자열(소문자)로 가져온다.
select lower(concat(first_name, ' ',last_name))
from employees


9. 날짜 함수
-- now(), sysdate(), current_timestamp() : 현재 날짜와 시간을 반환한다.
-- curdate(), current_date() : 현재 날짜를 반환.
-- curtime(), current_time() : 현재 시간을 반환.
-- date_add(날짜, INTERVAL 기준값) : 날짜에서 기준값 만큼 더한다.
-- (기준값은 이중 하나로 -> YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)
-- date_sub(날짜, INTERVAL 기준값) : 날짜에서 기준값 만큼 뺀다.
-- (기준값은 이중 하나로 -> YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)

-- 현재 날짜와 시간(세가지 모두 동일)
select now();
select sysdate();
select current_timestamp();

-- 현재 날짜만 반환
select curdate();
select current_date();

-- 현재 시간만 반환
select curtime();
select current_time();

-- 날짜 더하기
select now(), date_add(now(), interval 100 day);

-- 직원들의 입사일로부터 100일 뒤를 구하시오.
select hire_date, date_add(hire_date, interval 100 day)
from employees;

-- 날짜 빼기
select now(), date_sub(now(), interval 100 day);

-- YEAR(날짜) : 날짜의 연도를 가져온다.
-- MONTH(날짜) : 날짜의 월을 가져온다.
-- MONTHNAME(날짜) : 날짜의 월을 영어로 가져온다.
-- DAYNAME(날짜) : 날짜의 요일을 영어로 가져온다.
-- DAYOFMONTH(날짜) : 날짜의 월별 일자를 가져온다.
-- DAYOFWEEK(날짜) : 날짜의 주별 일자를 가져온다. (일요일-1, 월요일-2, 화요일-3..)
-- WEEKDAY(날짜) : 날짜의 주별 일자를 가져온다. (월요일-0, 화요일-1, 수요일-2..)
-- DAYOFYEAR(날짜) : 일년을 기준으로 한 날짜까지의 날 수(올해의 몇번째 날인지?)
-- WEEK(날짜) : 일년 중 몇번째 주(올해의 몇번째 주 인지?)
-- FROM_DAYS(날 수) : 00년 00월 00일 부터 날 수 만큼 지난 날짜
-- TO_DAYS(날짜) : 00년 00월 00일 부터 날짜까지의 일 수

select now(), year(now());
select now(), month(now());
select now(), monthname(now());
select now(), dayname(now());
select now(), dayofmonth(now());  
select now(), dayofweek(now());
select now(), weekday(now());
select now(), dayofyear(now());
select now(), week(now());
select from_days(1000);   -- 0002-09-27 (000000부터 1000일이 지난 날짜)
select to_days(now()); -- 738768 (000000부터 현재까지의 날수)

-- DATE_FORMAT(날짜, 형식) : 날짜를 형식에 맞게 만들어 반환한다.
-- 년도 : %Y(4자리), %y(2자리)
-- 월 : %M(긴 월 영어이름), %m(숫자의 월, 01, 02, 03...), %b(짧은 월이름), %c(숫자의 월, 1,2,3...)
-- 요일 : %W(긴 요일), %a(짧은 요일), %w(0-일요일, 1-월요일..)
-- 일 : %D(1th, 2th, 3th..), %d(01,02,03,04..), %e(1,2,3..), %j(일년중 날짜. 001,002,003..)
-- 시 : %I(12시간제, 1,2,3..), %k(24시간제, 0,1,2,3..), %h(12시간제, 01,02,03..), %H(24시간제, 00,01,02..), %l(12시간제,01,02,03..)

-- 포맷
select now(), date_format(now(), '%Y년 %m월 %d일 %H시 %i분 %s초');

 

10. 그룹 함수
-- COUNT(컬럼명) : 가져온 로우의 개수를 반환한다.
-- SUM(컬럼명) : 가져온 로우의 해당 컬럼에 저장된 값의 총합을 구한다.
-- AVG(컬럼명) : 가져온 로우의 해당 컬럼에 저장된 값의 평균을 구한다.
-- MAX(컬럼명) : 가져온 로우의 해당 컬럼에 저장된 값 중 최대값을 구한다.
-- MIN(컬럼명) : 가져온 로우의 해당 컬럼에 저장된 값 중 최소값을 구한다.

-- 사원의 수를 구한다.
select count(*) from employees; -- employees의 모든 컬럼의 개수.

-- 남자 사원의 수를 구한다.
select count(*) 
from employees
where gender = 'M';

-- d005 부서에 근무하고 있는 사원들의 수를 가져온다. to_date가 9999년 01월 01일인 사원이 현재 근무하고 있는 사원이다.
  select count(*)
  from dept_emp
  where dept_no = 'd005' and to_date = '9999-01-01';
  
-- 현재 받고 있는 급여의 총합을 구한다.
  select sum(salary)
  from salaries
  where to_date = '9999-01-01';
  
  
11. Group by , having

-- Group by : select문을 통해 가져온 모든 로우를 개발자가 정한 기준에 따라 그룹으로 나눌수 있다.
-- 사원의 수를 성별로 구분하여 가져온다.
select gender, count(*)
from employees
group by gender;

-- 각 부서에 현재 근무하고 있는 사원들의 수를 가져온다.
select dept_no, count(*)
from dept_emp
where to_date = '9999-01-01'
group by dept_no;

-- 각 부서별 과거의 매니저 수를 구한다.
select dept_no, count(*)
from dept_manager
where to_date <> '9999-01-01'
group by dept_no;

-- 급여 수령 시작일별 급여 총합을 구한다.
select from_date, sum(salary)
from salaries
group by from_date;

-- having : Group by 절을 이용하여 개발자가 정한 기준으로 그룹을 나눈후, having절로 만든 조건에 맞는 그룹의 데이터만 가져올수 있다.
-- 10만명 이상이 사용하고 있는 직함의 이름과 지원의 수를 가지고 온다.
select title, count(*)
from titles
group by title
having count(*) >= 100000;

-- 5만명 이상이 근무하고 있는 부서의 부서 번호와 부서 소속 사원의 수를 가져온다.
select dept_no, count(*)
from dept_emp
group by dept_no
having count(*) >= 50000;

 

12. join

(여러테이블에 있는것을 조합하여 가져옴. 다대다 형태로 데이터를 가져오기 때문에 꼭 조건문(where절)을 넣어줘야함)
-- 사원들의 사원번호(dept_emp테이블), 부서번호(dept_emp테이블), 부서명(departments테이블)을 가져온다. 사원 번호를 기준으로 오름차순 정렬
select dept_emp.emp_no, dept_emp.dept_no, departments.dept_name
from departments, dept_emp
order by emp_no asc;

-- 위의 테이블명이 너무 길다고 생각되면, 내 마음대로 테이블명을 만들어도 됨.
select a2.emp_no, a2.dept_no, a1.dept_no, a1.dept_name
from departments a1, dept_emp a2   -- <- 이렇게!!
where a1.dept_no = a2.dept_no  <-- join 문은 다대다의 형태로 데이터를 가지고 오기때문에, 이렇게 꼭 조건문을 달아줘야 원하는 정보를 정확히 얻을수 있다!! 
-- 공통분모를 찾으면 된다. 여기서는 부서번호(dept_no)가 공통이기 때문에 where문에서 사원번호가 같을경우만 추출하도록 조건을 줄수가 있었던 것이다.
order by a2.emp_no; 

-- 각 사원들의 사원번호(employees테이블), first_name(employees테이블), 근무부서번호(dept_emp테이블)를 가져온다.
select a1.emp_no, a1.first_name, a2.dept_no, a2.emp_no -- 여기서 a2.emp_no는 굳이 안써줘도 된다. 여기서는 공통분모를 확인하기위해 써준것.
from employees a1, dept_emp a2  
where a1.emp_no = a2.emp_no; -- 여기서는 두 테이블 간의 공통분모가 사원번호기때문에 사원번호가 같은 경우에 추출하도록 조건을 걸어줄수가 있다.

-- 각 사원들의 사원번호(employees테이블), first_name(employees테이블), 근무부서이름(departments테이블)을 가져온다. (공통분모가 없는경우 - 중간다리테이블 이용)
select a1.emp_no, a1.first_name, a2.dept_name
from employees a1, departments a2, dept_emp a3 -- 여기서는 중간다리테이블을 dept_emp를 사용함
where a1.emp_no = a3.emp_no and a2.dept_no = a3.dept_no;  <-- 중간다리테이블이 있어서 조건절이 2개 필요하겠지?

 

13. 서브쿼리 : 쿼리문 안에 쿼리문이 있는것.
-- 조건문 등을 만들때 값을 직접 지정하지 못하고 쿼리문을 통해 구해와야 할 경우, 서브쿼리를 통해 값을 구해와 조건문을 완성할 수 있다.

-- '현재 받는 급여의 평균'보다 많이 받는 사원들의 사원번호, 급여액을 가져온다.
select emp_no, salary
from salaries
where salary > (select avg(salary) from salaries where to_date = '9999-01-01'); -- 이렇게 쿼리 안에 또 쿼리를 넣는게 서브 쿼리!

-- 'd001부서에 근무하고 있는 사원들'의 사원번호와 first_name을 가져온다.★(join문으로 해결가능하지만, 여기서는 서브쿼리로 해보자) --> in사용
select emp_no, first_name
from employees
where emp_no in(select emp_no from dept_emp where dept_no = 'd001');

-- 1960년 이후에 태어난 사원들이 근무하고 있는 사원들의 사원번호, 근무부서번호를 가져온다.★ -> 얘도 in사용. from이 같지 않아도된다. 서브쿼리는 개별적으로 독립적인 쿼리야!
select emp_no, dept_no
from dept_emp
where emp_no in (select emp_no from employees where birth_date >= '1960-01-01');

14.  set 

-- 두 select문을 통해 얻어온 결과를 집합연산을 통해 하나의 결과로 만드는 것.(합집합, 교집합, 차집합 등)
-- 집합연산을 하기 위해서는 두 select문을 통해 가져오는 컬럼이 같아야 한다.
-- 1. 합집합 : UNION(중복되는 데이터를 하나만 가져옴)/ UNION ALL(중복되는 데이터도 모두 가져옴)

-- 같은 사람(사원번호 10005)이 Staff 였다가 승진을 해서 Senior Staff 가 되면, 그 사람이 Staff그룹과 Senior Staff그룹에 중복으로 있게된다.
--  union 과 union all을 이용해서 합집합으로 연산해보자.
select emp_no from titles where title = 'Senior Staff';
select emp_no from titles where title = 'Staff'; 

select emp_no from titles where title = 'Senior Staff'
union  -- emp_no : 10005가 1명 나온다. (중복제거)
select emp_no from titles where title = 'Staff'; 

select emp_no from titles where title = 'Senior Staff'
union all   -- emp_no : 10005가 2명 나온다. (중복허용)
select emp_no from titles where title = 'Staff'; 

-- 2. 교집합 : 두 select문의 결과 중 중복되는 부분만 가져온다. MySQL에는 교집합 연산자가 제공되지 않아 join문을 사용한다.

-- Staff그룹과 Senior Staff그룹의 교집합을 만들어 사원번호를 구하시오.
select a1.emp_no
from titles a1, titles a2   -- (이렇게 같은 테이블을 join 해주기!!)
where a1.emp_no = a2.emp_no and a1.title = 'Senior Staff' and a2.title = 'Staff'; -- 사원번호도 같고, 직함도 같아야 하니까.

-- 3. 차집합 : 두 select문의 결과 중 중복되는 부분을 제거하고 첫번째 select문 결과만 가져온다. MySQL에는 차집합 연산자가 제공되지 않아 서브쿼리문을 사용한다.

-- Staff그룹에서 Senior Staff그룹을 뺀 결과의 사원번호를 구하시오.
select emp_no
from titles
where title = 'Staff' and emp_no not in(select emp_no from titles where title = 'Senior Staff');

 

 

[출처] 인프런-  윤재성의 처음 시작하는 MySQL DataBase