[MySQL] MySQL 기본 문법

-- 데이터베이스 조회하기. show database; 로그인한 계정으로 사용가능한 데이터베이스 목록이 조회됨
show databases;

-- 데이터베이스 사용하기. use 데이터베이스명; 그냥 더블클릭 해도됨.
use employees;

-- 선택된 데이터베이스 내의 테이블을 조회함. show tables;
show tables;

-- 선택된 테이블 내의 컬럼들을 조회함. desc 테이블명;
desc departments;

desc dept_emp;

desc dept_manager;

desc employees;

desc salaries;

desc titles;

//

-- select 문 기본

-- 사원의 정보를 모두 가져온다.
select *
from employees;

-- 부서 정보를 모두 가져온다.
select *
from departments;

-- 매니저의 모든 정보를 가져온다.
select *
from dept_manager;

-- 사원들의 모든 직함 정보를 가져온다.
select *
from titles;

-- 사원의 사원번호, 이름을 가져온다.
select emp_no, first_name, last_name
from employees;

-- 사원의 사원번호, 생년월일, 성별을 가져온다.
select emp_no, birth_date, gender
from employees;

-- 부서의 부서번호, 부서이름을 가져온다.
select dept_no, dept_name
from departments;

-- 아래와 같게됨. 쌤은 *는 추천하지 않음. 나중에 유지보수할때 힘들수 있다.
-- 귀찮아도 컬럼 이름을 전부 작성해 주도록 하자.
select *
from departments;

-- 각 사원의 사원번호, 급여액을 가져온다.
select emp_no, salary
from salaries;

-- 각 사원의 사원번호, 직함 이름을 가져온다.
select emp_no, title
from titles;

//

-- 산술 연산자

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;

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

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

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

-- 각 사원의 직함을 중복되지 않게 가져온다.
select distinct title
from titles;

//

-- 조건절 사용하기 (select 문에서 where 키워드를 사용하면 조건을 사용할수 있다.)

-- d005부서 매니저의 사원의 사원번호, 부서번호를 가져온다.
select emp_no, dept_no
from dept_manager
where dept_no = 'd005';

-- 부서가 d003이 아닌 매니저 사원들의 사원번호와 부서번호를 가져온다.
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';

//

-- 논리연산자 (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');

//

-- 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 '_____';

//

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

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

-- 사원의 번호와 급여를 가져온다. 급여를 기준으로 내림차순 정렬한다.
select emp_no, salary
from salaries
order by salary desc; 

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

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


//

-- 숫자 함수
-- 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);

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

-- 반올림
select round(10.1), round(10.4), round(10.5), round(10.8);
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);

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

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

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

-- 사원들의 사원번호와 급여를 가져온다. 급여는 10% 인상된 급여를 가져오며 소수점 이하는 올린값, 버린값, 반올림한값을 모두 가져온다.
select emp_no, salary * 1.1, ceil(salary * 1.1), floor(salary * 1.1), round(salary * 1.1, 0)
from salaries;



//

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

select concat('aaa', 'bbb', 'ccc');
select insert('aaaaa', 2, 2, 'bbb');
select insert('aaaaa', 2, 0, 'bbb');
select replace('aabbcc', 'bb', 'ff');
select instr('abcdefg', 'cde');

-- 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(문자열) : 문자열을 반대로 가져온다.alter

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


//

-- 날짜 함수
-- 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());   -- 왜 5가 나오지? 9월인데..
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초');

//

-- 그룹 함수
-- 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';
  
-- 현재 받고 있는 급여의 평균을 구한다.
  select avg(salary)
  from salaries
  where to_date = '9999-01-01';
  
-- 현재 받고 있는 급여의 최고액을 구한다.
  select max(salary)
  from salaries
  where to_date = '9999-01-01';
  
-- 현재 받고 있는 급여의 최저액을 구한다.
  select min(salary)
  from salaries
  where to_date = '9999-01-01';
  
  //
  
-- 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;

-- 급여 수령 시작일별 급여 평균을 구한다.
select from_date, avg(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;

//

-- join (여러테이블에 있는것을 조합하여 가져옴. 다대다 형태로 데이터를 가져오기 때문에 꼭 조건문을 넣어줘야함)

-- 사원들의 사원번호(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테이블), 현재 받고있는 급여액(salaries테이블)를 가져온다.
select a1.emp_no, a1.first_name, a2.salary
from employees a1, salaries a2
where a1.emp_no = a2.emp_no and a2.to_date = '9999-01-01'; -- 여기서도 두 테이블 간의 공통분모가 사원번호

-- 각 사원들의 사원번호(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;

//

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

-- '현재 받는 급여의 평균'보다 많이 받는 사원들의 사원번호, 급여액을 가져온다.
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');

//

-- 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');

//

-- 1. 데이터 베이스 만들기 
-- create database 이름
-- 생성된 데이터베이스는 use문을 이용하여 선택해야 한다.
-- utf-8 인코딩 타입의 한글을 저장하려면 반드시 다음과 같이 언어타입을 지정해준다.
-- /* create database 이름
-- character set = 'utf8'
-- collate = 'utf8_general_ci'; */

-- test_db 라는 데이터베이스를 생성하고 한글을 저장할수 있게 만드시오.
create database test_db
character set = 'utf8'
collate = 'utf8_general_ci';

use test_db; -- 이렇게 선택해도 되고 더블클릭해도 됨

-- 2. 테이블 만들기
-- 데이터베이스를 선택한 후, create table 명령문
-- /* create table 이름(
-- 컬럼이름 자료형 제약조건,
-- 컬럼이름 자료형 제약조건
-- ); */
-- 자료형
-- char(n) : char는 10개 공간이 미리 배정되어 고정길이 문자열. 이름이나 우편번호 작성시 많이 사용한다. 속도가 빠르다.
-- varchar(n) : varchar는 1개 공간이 쓸때마다 늘어나는 형태로 가변길이 문자열. 게시판이나 댓글 작성시 많이 사용한다. 
-- int(n), bigint(n) : 정수형 타입
-- float(n,m), double(n,m) : 부동소수점 타입
-- date(날짜), time(시간), datetime(날짜와시간)

create table test_table1(
data1 int(10),
data2 varchar(10),
data3 float(10,2)
);

desc test_table1; -- test_table1의 컬럼들을 조회함

select * from test_table1; -- 지금까지는 아무것도 저장하지 않았기 때문에 빈 값이 출력됨. 

-- 3. 서브쿼리를 이용한 테이블 생성하기
-- select문을 통해 가져온 결과를 이용해 테이블을 생성할때 사용한다.(데이터 복제)
-- /* create table 테이블명
-- as
-- select문 */

use employees;

-- departments 테이블에 있는 결과를 그대로 복제하여 dept1 테이블을 만드시오.
select * from departments;

create table dept1  -- departments의 테이블을 가져와 dept1의 테이블로 복제한다.
as
select * from departments;

desc dept1; -- dept1의 컬럼 확인
select * from dept1; -- dept1에 데이터가 잘 들어와 있는지 확인. 잘 들어와있음.

-- departments 테이블에 있는 데이터는 복제 하지 않고, 구조만 복제하여 dept2 테이블을 만드시오.
create table dept2
as
select * from departments where 1 = 0; -- 이렇게 항상 거짓인 조건을 넣어주면 구조만 가져오게 됨.

desc dept2;
select * from dept2; -- 데이터는 복제되지 않고 구조(dept_no, dept_name)만 들어와 있음.

-- departments 테이블에 있는 결과중 dept_no만 복제하여 dept3 테이블을 만드시오.
create table dept3 
as
select dept_no from departments;

select * from dept3; -- dept_no데이터만 들어와 있음.

//

-- 데이터 저장, 수정, 삭제 (insert, update, delete)
-- 1. 데이터 저장하기 (로우 단위로 저장됨)
-- insert into 테이블명 (컬럼명) values (값)  <- 모든 컬럼을 저장하고 싶을때는 컬럼명 생략
-- 컬럼에 저장될 값을 지정하지 않으면 null이 저장된다.

-- 테이블 test_table1에 값 100, '문자열1', 11.11 를 차례로 저장하시오.
desc test_table1;

insert into test_table1 (data1, data2, data3) values(100, '문자열1', 11.11);
select * from test_table1;

-- 컬럼순서가 순서대로 되지않아도 됨. 아래와 같이 values와 짝만 잘 이루면 된다.
insert into test_table1 (data2, data3, data1) values('문자열2', 22.22, 200); 
select * from test_table1;

-- 컬럼명 생략할때는 values의 순서 잘 지켜줘서 써주기.
insert into test_table1 values(300, '문자열3', 33.33);
select * from test_table1;

-- test_table1을 구조만 복제하여 test_table2를 만든후, 서브쿼리를 이용하여 test_table1을 test_table2에 통째로 넣으시오.
create table test_table2
as
select * from test_table1 where 1=0;

select * from test_table2;

insert into test_table2
select data1, data2, data3 from test_table1; -- <- 이 서브쿼리를 test_table2에 통쨰로 insert한다. (이때 values는 안써줘도 되나보네,,)

select * from test_table2;


-- 2. 데이터 수정하기
-- update 테이블명 set 컬럼명=값, 컬럼명=값 where 조건식

-- test_table1테이블의 data2컬럼을 모두 '새로운문자열'로 수정하고, data3컬럼을 모두 '66.66'으로 수정하시오.(모두 수정)
select * from test_table1;

update test_table1 set data2='새로운문자열', data3=66.66;

select * from test_table1;

-- test_table2테이블의 <data1컬럼의 데이터가 100인것만> data2컬럼을 '새로운문자열'로 수정하고, data3컬럼을 '66.66'으로 수정하시오.(일부 수정)
select * from test_table2;

update test_table2 set data2='새로운문자열', data3=66.66
where data1=100;

select * from test_table2;


-- 3. 데이터 삭제하기
-- delete from 테이블명 where 조건식

-- test_table1의 모든 데이터를 삭제하시오.(모두 삭제)
select * from test_table1; -- 삭제되기 전 데이터 확인

delete from test_table1;  -- test_table1의 데이터 삭제

select * from test_table1; -- 삭제된 것 확인

-- test_table2의 data1가 100인 데이터만 삭제하시오.(일부 삭제)
select * from test_table2; -- 삭제되기 전 데이터 확인

delete from test_table2 where data1=100;  -- test_table2에서 data1=100인 데이터만 삭제

select * from test_table2; -- 삭제된 것 확인

//

-- 트랜젝션 : 데이터베이스에서 데이터 처리의 한 단위 (rollback, savepoint, truncate 기능사용)
-- 대부분의 데이터베이스는 데이터를 저장, 수정, 삭제하는 작업을 바로 하드디스크에 저장된 데이터에 반영하지 않는다.(실수를 수정하기위한 안전장치)
-- 커밋을 하면 그때 물리적인 하드디스크에 반영한다.
-- 개발자가 입력하는 명령문들의 시작부터 커밋까지를 하나의 트랜젝션이라고 부른다.

-- 1. RollBack : 데이터의 저장,삭제,수정 등의 작업을 한 후, 원래대로 되돌리는 작업
-- 커밋을 한 이후에는 RollBack작업을 해도 되돌릴 수 없다.
-- Workbench 같은 프로그램에서는 자동으로 커밋작업이 발생하므로 Rollback을 해도 원래대로 되돌릴 수 없다. 
-- Rollback을 사용하기 위해 임의로 설정을 바꿔보자. Edit - preference - SQL execution - auto commit 체크박스 해제

-- test_table2를 삭제한 후, 복구하시오.
select * from test_table2;

delete from test_table2; -- 데이터 삭제

select * from test_table2; -- 데이터 삭제된 것 확인

rollback; -- 복구

select * from test_table2; -- 삭제되었던 데이터가 정상적으로 복구됨


-- 커밋을 한 후엔 롤백을 해도 복구되지 않는다.
select * from test_table2;

delete from test_table2; -- 데이터 삭제
commit; -- 커밋
select * from test_table2; -- 데이터 삭제된 것 확인

rollback; -- 복구

select * from test_table2; -- 복구 되지 않는것을 확인


-- 2. SavePoint : rollback시 지정된 위치로 복원

insert into test_table2 (data1, data2, data3) values (100, '문자열1', 11.11);
insert into test_table2 (data1, data2, data3) values (200, '문자열2', 22.22);
insert into test_table2 (data1, data2, data3) values (300, '문자열3', 33.33);

commit;
select * from test_table2;

update test_table2 set data2='새로운문자열', data3=44.44 where data1=100;
savepoint aa;  -- savepoint 지정
delete from test_table2 where data1=100;
select * from test_table2;
rollback to aa;  -- 위에서 지정한 savepoint로 돌아가서 복구함
select * from test_table2;


-- 3. truncate : 지정된 테이블의 모든 로우를 삭제한다. 
-- delete문은 데이터베이스에 바로 반영하지 않아 rollback이 가능하지만 truncate는 데이터베이스에 바로 반영하여 rollback이 불가능하다.

commit;

select * from test_table2;
-- delete 로 삭제하면 롤백 되는것을 확인
delete from test_table2; 
select * from test_table2;
rollback;
select * from test_table2;

-- truncate 로 삭제하면 롤백 되지 않는 것을 확인
truncate test_table2;
select * from test_table2;
rollback;
select * from test_table2;

//

-- 테이블 변경하기 (rename, alter, drop)
-- 1. 테이블명 변경 : rename table 기존테이블명 to 새로운테이블명
-- 2. 컬럼의 데이터타입 변경 : alter table 테이블명 modify 컬럼명 변경할타입
-- 3. 컬럼의 이름, 데이터타입 변경 : alter table 테이블명 change 기존컬럼명 새로운컬럼명 변경할타입 
-- (컬럼명만 바꾸더라도 타입도 필수로 써줘야함. 타입을 변경하지 않을거면 기존의 타입을 그대로 써주면 됨.)
-- 4. 컬럼 추가 : alter table 테이블명 add 추가할컬럼 추가할컬럼의데이터타입
-- 5. 컬럼 삭제 : alter table 테이블명 drop 삭제할컬럼
-- 6. 테이블 삭제 : drop table 테이블명

-- 테이블test_table1을 test_table3으로 변경하시오.
show tables; -- 현재 데이터베이스에 저장된 모든 테이블을 불러온다.(test_table1, test_table2)

rename table test_table1 to test_table3; -- test_table1을 test_table3으로 변경
show tables; -- test_table2, test_table3 으로 변경된 것 확인

-- test_table3의 data1컬럼의 데이터 타입을 int에서 int(100)으로 변경하시오.
desc test_table3;
alter table test_table3 modify data1 int(100);
desc test_table3;

-- test_table3의 컬럼 data1을 data10으로 변경하고, 데이터타입을 int(200)으로 변경하시오.
alter table test_table3 change data1 data10 int(200);
desc test_table3;

-- test_table3에 컬럼 data4를 추가하고, 데이터타입을 int(20)으로 설정하시오.
alter table test_table3 add data4 int(20);
desc test_table3;

-- test_table3에 컬럼 data4를 삭제하시오.
alter table test_table3 drop data4;
desc test_table3;

-- 테이블 test_table3을 삭제하시오.
show tables;
drop table test_table3;
show tables;

//

-- 제약조건 : 컬럼에 저장될 데이터의 조건을 설정(데이터의 무결성을 보장하므로 매우 중요)
-- 1. primary key : 중복불가, null불가. 기본키라고도 부름.
-- 2. foreign key : 특정 테이블의 primary key컬럼에 저장되어있는 값만 저장할 수 있도록 한다. 참조키, 외래키라고도 부름. null값 허용.
-- 3. not null : null불가. 쿼리문을 통해 반드시 값이 지정되어야 함.
-- 4. unique : 중복불가, null허용.
-- 5. check : 값의 범위나 종류를 지정하여 조건에 맞는 값만 저장할 수 있도록 함. check제약조건은 mysql에서 지원하지 않음.(작성은 가능하나 무시함)
-- 6. default : null이 들어올 경우 기본 설정 값을 지정. default를 설정할 경우 컬럼에 null을 저장할 수 없음.


-- test_table10테이블을 만들고, not null제약조건이 있는 data1의 컬럼을 만드시오.
create table test_table10(
data1 int not null
);

insert into test_table10 (data1) value (1);
insert into test_table10 (data1) value (2);
insert into test_table10 (data1) value (3);

select * from test_table10; -- 데이터 1,2,3이 문제없이 삽입됨을 확인.

insert into test_table10 (data1) value (1); -- 중복되어 1이 삽입됨.
select * from test_table10;

insert into test_table10 (data1) value (null); -- Column 'data1' cannot be null 에러 뜸. not null이란 제약조건을 넣어줬기 때문에.
select * from test_table10; 


-- test_table20테이블을 만들고, data1은 primary key조건을 가지고, data2는 not null제약조건을 설정하시오.
create table test_table20(
data1 int,
data2 int not null,
constraint pk1 primary key(data1)  -- 여기서 pk1은 마음대로 이름붙인것.
);

insert into test_table20 (data1, data2) values (10, 100);
insert into test_table20 (data1, data2) values (20, 200);
insert into test_table20 (data1, data2) values (30, 300);

select * from test_table20; 

insert into test_table20 (data1, data2) values (10, 100); -- 에러(중복된 값을 허용하지 않아서)
insert into test_table20 (data1, data2) values (null, 100); -- 에러 (null값을 허용하지 않아서)
insert into test_table20 (data2) values (100); -- 에러 (primary key는 null이 아닌 기본값을 모두 세팅해줘야 해서)


-- test_table30테이블을 만들고, data1은 primary key조건을 가지고, data2는 test_table20의 data1컬럼을 참조하도록 설정하시오.
create table test_table30(
data1 int,
data2 int,
constraint pk1 primary key(data1),
constraint pk2 foreign key(data2) references test_table20(data1) -- data2에는 test_table20의 data1의 값만 넣을수 있다는 뜻.(10,20,30만)
);

select * from test_table20; 
insert into test_table30 (data1, data2) values (1, 10);
insert into test_table30 (data1, data2) values (2, 20);
insert into test_table30 (data1, data2) values (3, 30);

select * from test_table30; 
insert into test_table30 (data1, data2) values (4, 40); -- test_table20의 data1에는 40이 없는데 넣어주니까 에러!
insert into test_table30 (data1, data2) values (5, null);  -- foreign key 여서 null 값 허용
insert into test_table30 (data1, data2) values (6);  -- 이렇게 두번째 값을 생략해도 null 이 들어간다. (난 왜 에러나지??)
select * from test_table30; 

-- test_table40테이블을 만들고, data1, data2는 unique조건을 가지고, data2 not null로 설정하시오.
create table test_table40(
data1 int,
data2 int not null,
constraint uk1 unique(data1),
constraint uk2 unique(data2)
);

insert into test_table40 (data1, data2) values (1, 10);
insert into test_table40 (data1, data2) values (2, 20);
select * from test_table40; 

insert into test_table40 (data1, data2) values (1, 30); -- 중복이라서 에러
insert into test_table40 (data1, data2) values (3, 10); -- 중복이라서 에러
insert into test_table40 (data1, data2) values (null, 40); -- data1은 not null이 아니니까 가능
insert into test_table40 (data1, data2) values (null, 50); -- 원래 unique는 중복불가 null값 허용이니까, data2처럼 일부러 not null 쓰지 않는 경우는 다 null값 허용한다.
select * from test_table40; 

-- test_table50테이블을 만들고, data1는 10초과, data2는 10,20,30만 입력되게하는 제약조건을 가지게하고, 모두 not null로 설정하시오.
create table test_table50(
data1 int not null,
data2 int not null,
constraint chk1 check(data1 > 10),
constraint chk2 check(data2 in(10,20,30))
);

insert into test_table50 (data1, data2) values (20, 30);
select * from test_table50; 

insert into test_table50 (data1, data2) values (1, 100); -- 위 제약조건에 위배되지만 mysql은 무시하기 때문에 그대로 저장한다.(왜 난 저장안되지?)
select * from test_table50; 


-- test_table60테이블을 만들고, data1는 디폴트 1, data2는 디폴트 10으로 입력되게하는 제약조건을 가지게 설정하시오.
create table test_table60(
data1 int not null default 1,
data2 int not null default 10
);

insert into test_table60 (data1, data2) values (100, 200); -- 기본데이터는 정상 입력됨
select * from test_table60; 

insert into test_table60 (data1, data2) values (null, null); -- 디폴트 제약조건은 이렇게 둘다 null값을 대놓고 넣어주면 에러난다.
select * from test_table60; 

insert into test_table60 (data1) values (1000); -- 디폴트 제약조건은 이렇게 컬럼하나를 생략할때만 null값을 넣어준다!!!
insert into test_table60 (data2) values (2000);
select * from test_table60; 


//

-- 1. 시퀀스 : 로우를 추가할때 자동으로 증가하는 값이 저장되는것. (auto_increment 키워드사용. 데이터베이스마다 사용법다름)
-- 데이터를 insert할때 auto_increment를 설정한 컬럼은 제외한다.(보통 primary key에 많이 사용)


-- test_table100테이블 만들고, data1은 자동으로 숫자가 증가되도록 설정하고, data2,3은 not null로 설정하시오.
create table test_table100(
data1 int auto_increment,   -- data1은 자동으로 1,2,3,4... 추가된다.
data2 int not null,
data3 int not null,
constraint pk1 primary key(data1)
);

insert into test_table100 (data2, data3) values (100, 200); -- auto_increment가 설정된 data1은 생략가능
insert into test_table100 (data2, data3) values (101, 201);
insert into test_table100 (data2, data3) values (102, 202);
insert into test_table100 (data2, data3) values (103, 203);
insert into test_table100 (data2, data3) values (104, 204);

select * from test_table100;

-- 2. limit : select해서 가져온 로우에서 원하는 범위의 로우만 가지고 온다. (게시판 등 페이징 기법시 사용. 데이터베이스마다 사용법다름)
-- select문 limit 시작인덱스, 개수

use employees;

select * from employees order by emp_no;
select * from employees order by emp_no limit 0, 10; -- 1번부터 10개 데이터를 가져옴 (1번~10번)
select * from employees order by emp_no limit 10, 10;  -- 11번부터 10개 데이터를 가져옴 (11번~20번)

//

-- 뷰 : 가상의 테이블(조인이나 서브쿼리를 사용해 만든 복잡한 쿼리를 간단히 뷰로 지정해 놓으면 편하게 사용가능)
-- 뷰는 select문을 통해 얻어진 '결과'를 가지고 있는게 아니라 select문 자체를 가지고 있어서 뷰를 select하면 이전에 사용한 쿼리문이 실행되어 결과를 가져오는것.
-- 뷰 생성 : create view 뷰이름 as select쿼리문
-- 뷰 삭제 : drop view 뷰이름

create table test_table1000(
data1 int,
data2 int not null,
constraint pk1 primary key (data1)
);

create table test_table2000(
data1 int not null,
data2 int not null,
constraint fk1 foreign key (data1) references test_table1000(data1)
);
desc test_table2000;
alter table test_table2000 change data2 data3 int; -- 내가 잘못써서 컬럼명 수정(data2 -> data3)

insert into test_table1000(data1, data2) values(1, 10);
insert into test_table1000(data1, data2) values(2, 20);
insert into test_table1000(data1, data2) values(3, 30);

select * from test_table1000;

insert into test_table2000(data1, data3) values(1, 100);
insert into test_table2000(data1, data3) values(2, 200);
insert into test_table2000(data1, data3) values(3, 300);

select * from test_table2000;


-- test_table1000과 test_table2000을 조인하는 test_view1을 만들어보자.
-- 먼저, test_table1000과 test_table2000을 조인해보자.
select a1.data1, a1.data2, a2.data3
from test_table1000 a1, test_table2000 a2
where a1.data1 = a2.data1;  -- 공통분모는 data1이니까 그걸 조건문에 넣기.

-- 이제 view를 만들어보자. (위에 조인한 쿼리를 밑에 3줄 그대로 복사해서 넣기)
create view test_view1
as
select a1.data1, a1.data2, a2.data3
from test_table1000 a1, test_table2000 a2
where a1.data1 = a2.data1; 

-- 만든 뷰 조회하기
select * from test_view1;

-- 뷰는 결과값만 가져오는게 아니라 쿼리 자체를 가지고 있어서 이렇게 데이터를 맘대로 추가해도 반영이 된다.
insert into test_table1000(data1, data2) values(4, 40);
insert into test_table2000(data1, data3) values(4, 400);

-- 추가한 데이터 반영한 뷰 조회하기
select * from test_view1;

-- 뷰 삭제하기
drop view test_view1;

select * from test_view1;










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