언제쯤 검색없이 코딩을 짤까요.

함수

물론 Mysql에서 프로시저만 사용하는 것은 아니다. 함수를 따로 정의해서 사용해도 된다.

SET GLOBAL log_bin_trust_function_creators = 1;

우선 위의 코드를 실행시키고 다음과 같이 함수를 만들어주면 된다.

CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(n1 varchar(20), n2 varchar(20)) 
RETURNS varchar(50) CHARSET utf8
BEGIN
	declare name varchar(50);
    set name = concat_ws(' ', n1, n2);
RETURN name;
END

하지만 mysql에서는 자체 내장된 함수만을 사용하는 것을 선호하기 때문에 프로시저를 이용하는 것을 추천한다.

트리거(Trigger)

insert, update, delete 동작이 실행될떄마다 이 동작 전이나 후에 실행할 코드를 등록하는 방법

create trigger 트리거이름
after/before insert/update/delete
on 테이블이름
[for each row] 

행의 삽입, 수정 후에 트리거가 실행되는 것이 좋다.

delimiter $$
create trigger insert_emp_trig
after insert
on emp1
for each row
begin
set @msg = concat(new.name, '님 새로 추가됨');    #new.name/old.name : 트리거에서 사용하는 값
end$$
delimiter ;
-> emp1에 insert할떄마다 실행

insert into emp1 values(300,'aaa',10000,80);
select @msg;

delimiter $$
create trigger emp1_trig
after insert
on emp1
for each row
begin
insert into emp1_backup(id, cmd, new_sal) values(new.emp_id, 'insert', new.sal);
end$$
delimiter ;

insert into emp1 values(301,'bbb',15000,80);
select @msg;

delimiter $$
create trigger emp1_trig2
after update
on emp1
for each row
begin
insert into emp1_backup(id, cmd, old_sal, new_sal) 
    values(old.emp_id, 'update', old.sal, new.sal);
end$$
delimiter ;

update emp1 set sal=20000 where emp_id=300;

삭제 전에 트리거가 실행되는 것이 좋다.

delimiter $$
create trigger emp1_trig3
before delete
on emp1
for each row
begin
insert into emp1_backup(id, cmd, old_sal, new_sal) 
    values(old.emp_id, 'delete', old.sal);
end$$
delimiter ;

delete from emp1 where emp_id>=145 and emp_id<150;

프로시저(Proceduer)

프로시저는 간단하게 파이썬에서 함수라고 생각하면 된다.

함수 호출
call 함수명()

CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(enum int)
BEGIN
	declare ename varchar(20); #변수선언
    select last_name into ename
    from employees where employee_id = enum;
    select ename;
END

같은 아웃풋을 보이는 프로시저들을 다양한 방법으로 표현해보자.

(if/case/loop/repeat/while)

• IF

CREATE DEFINER=`root`@`localhost` PROCEDURE `p5`(enum int)
BEGIN
	declare dept_id int default 0;
	declare dept_name varchar(20);
	select department_id into dept_id from employees where employee_id=enum;
    if dept_id = 10 then set dept_name = '개발1팀';
	elseif dept_id = 20 then set dept_name = '개발2팀';
    elseif dept_id = 30 then set dept_name = '개발3팀';
    elseif dept_id = 40 then set dept_name = '개발4팀';
    else set dept_name = '디자인팀';
    end if;
    select dept_name;
END

• case

CREATE DEFINER=`root`@`localhost` PROCEDURE `p6`(enum int)
BEGIN
	declare dept_id int default 0;
	declare dept_name varchar(20);
	select department_id into dept_id from employees where employee_id=enum;
    case 
		when dept_id = 10 then set dept_name = '개발1팀';
		when dept_id = 20 then set dept_name = '개발2팀';
		when dept_id = 30 then set dept_name = '개발3팀';
		when dept_id = 40 then set dept_name = '개발4팀';
		else set dept_name = '디자인팀';
    end case;
    select dept_name;
END

• Loop

CREATE DEFINER=`root`@`localhost` PROCEDURE `p7`(x int)
BEGIN
	declare y int default 1;
    l1:loop
		select y;
        set y=y+1;
        if y>x then leave l1;   #y가 x보다 크면 루프를 나가라
        end if;
	end loop;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `p8`(x int)
BEGIN
	declare y int default 1;
    l1:loop
		select y;
        set y=y+1;
        if y <=x then iterate l1;   #반복할 조건
        end if;
	end loop;
END

• 반복문 Repeat

CREATE DEFINER=`root`@`localhost` PROCEDURE `p9`(x int)
BEGIN
	declare y int default 1;
    repeat         #반복문
		select y;
        set y=y+1;
        until y > x end repeat;   #조건 만족시 반복끝
END

• while

CREATE DEFINER=`root`@`localhost` PROCEDURE `p10`(x int)
BEGIN
	declare y int default 1;
    while y <= x do        #반복문
		select y;
        set y=y+1;
	end while;
END

커서(Cusor)

CREATE DEFINER=`root`@`localhost` PROCEDURE `cusor_test`()
BEGIN
	declare enum int;
    declare ename varchar(20);
    declare sal int;
    
    #cursor: 여러줄, 여러컬럼으로 구성된 검색결과. 
    declare done tinyint default 0;
    declare c1 cursor for select employee_id, last_name, salary from employees;
    declare continue handler for not found set done=1;  #반복조건으로 사용
    #cursor에서 한줄씩 fetch. 언제까지? not found(커서에서 더 읽을줄이 없음)까지.
    #open -> fetch(한줄씩 읽음) : 반복 -> close
    
    open c1;
    l1: loop
		fetch from c1 into enum, ename, sal;  #커서 c1에서 한 줄씩 읽음
        if done then leave l1;  #not found이면 루프 빠져나감
        end if;
        select enum, ename, sal;   #변수값 출력
	end loop;
    

END

뷰(View)

가상의 테이블  -> 
1. 복잡한 쿼리를 가상 테이블인 뷰로 만들면 사용이 간단해 진다.
2. 보안(사용자의 레벨에 따라 접근 레벨을 분류할 수 있음)
3. 데이터의 독립성을 유지

create [or replace] view 뷰이름
as 
서브쿼리

create view view_80
as
select employee_id as emp_id, last_name as name, salary
from employees
where department_id = 80;

인덱스(Index)

빠른 검색을 제공하기 위해 지정된 컬럼값을 완전 B트리로 구성
제약조건 primary key, unique을 만들면 자동 생성됨
인덱스 설정할 컬럼: 수정, 삭제가 잘 일어나지 않고 테이블에 많은 수의 행이 있지만 검색 where 잘해서 사용하는 컬럼으로 이 컬럼으로 검색된 행이 몇 줄 안되는 컬럼.

create index 인덱스명 on 테이블명(컬럼명);

Q. 게시판(num, writher, w_date, title, content)과 회원가입 (id, pwd, name, email) 테이블 만들고 서로 참조시키기.

1. 회원가입 테이블 만들기

create table member(
id varchar(20) primary key,  #줄 대푯값
pwd varchar(20) not null,   #null 허용안함
name varchar(20) not null,
email varchar(20) unique   #중복허용안함
);

  1-1. 3명 회원가입

insert into member values('aaa', '111', 'namea', 'aaa@mail.con');
insert into member values('bbb', '222', 'nameb','bbb@mail.con');
insert into member values('ccc', '333', 'namec','ccc@mail.con');

2. 게시판(num, writher, w_date, title, content) 생성

create table board(
num int auto_increment primary key,
writer varchar(20), 
w_date datetime, 
title varchar(50),
content varchar(500), 
constraint foreign key(writer) references member(id)
);

insert into board(writer, w_date, title, content) 
values('aaa', sysdate(), 'title1', 'content1'); 

insert into board(writer, w_date, title, content) 
values('abc', sysdate(), 'title1', 'content');

위의 코드는 에러발생! member(id)에 abc가 없기때문. (근데 번호 생성은 했음.)

insert into board(writer, w_date, title, content) 
values('bbb', sysdate(), 'title2', 'content2'); 

delete from board where writer = 'bbb';
delete from member where id = 'bbb';

-> 삭제시 참조하는 행도 삭제하고 진행해야하기 때문에 create table할 때 on delete cascade를 추가해주면 문제가 없다.

자, drop하고 다시 시작하자.

drop table board;

create table board(
num int auto_increment primary key,
writer varchar(20), 
w_date datetime, 
title varchar(50),
content varchar(500), 
constraint foreign key(writer) references member(id) on delete cascade
);

insert into board(writer, w_date, title, content) 
	values('aaa', sysdate(), 'title1', 'content1');

그럼 이제 id='aaa' 를 삭제하자.

delete from member where id = 'aaa';

만약, on delete cascade 대신 on delete set null을 추가하면 부모 테이블의 행 삭제시 이 줄을 참조하는 모든 줄의 값을 null로 셋팅이 된다.

테이블(Table)

• 테이블 생성
  

  create table 이름(
  컬럼명 타입(크기) [제약조건],
  컬럼명 타입(크기) [제약조건],
  컬럼명 타입(크기) [제약조건]
  );​

• 타입
  정수 : int(interger)
  실수 : float
  문자 : char(크기) -고정크기 / varchar(크기)-가변크기
  대용량 텍스트: longtext(4GB)
  날짜: date(년-월-일) / datetime(년-월-일 시:분:초)
  

  create table test2(
  num int auto_increment primary key,
  name varchar(20) not null,
  addr varchar(50) default '서울',
  w_date datetime default now(),
  msg varchar(200)
  );​

• 테이블 삽입
  

  insert into test2(name, msg) values('aaa', 'hello');
  insert into test2(name, addr, msg) values('bbb', '안양', '안녕하세요');​

• 테이블 수정

     테이블 구조 변경. 컬럼추가, 컬럼삭제, 컬럼의 타입이나 크기를 변경(값이 없을떄)

     -1. 컬럼추가

alter table 테이블명
add (컬럼명 타입(크기))​

alter table test2
add (pwd varchar(10)); ​

    -2. 컬럼변경(타입이나 크기 변경)

alter table 테이블명
modify (컬럼명 새타입(새크기));

alter table test2
modify pwd varchar(20);   #크기를 크게 바꾸는 건 가능하지만 작게는 힘듬.

    -3. 컬럼 삭제

alter table 테이블명
drop column 컬럼명;

alter table test2
drop column pwd;

     -4. 컬럼명 변경

alter table 테이블명
change 원컬럼명 새컬럼명 타입;

alter table test2
change msg hello_msg varchar(200);

   -5. 컬럼 삭제 (한번에 컬럼 하나만 삭제가능)

alter table test2
drop column col1;

    delete VS truncate

select * from new_test2;
delete from new_test2;   #delete는 rollback이 되지만
rollback;

truncate table new_test2;  #전체행 삭제. rollback 안됨.

• 테이블 이름 변경

alter table test2
rename new_test2;

Q1. Zlotkey와 동일한 부서에 속한 모든 사원의 이름과 입사일을 표시하는 질의를 작성하십시오. Zlotkey는 제외.

select e1.last_name, e1.hire_date
from employees e1 join employees e2
on e2.department_id = (select e1.department_id
from employees e1
    where e1.last_name = 'Zlotkey')
and e1.last_name <> e2.last_name;

#sol2
select last_name, hire_date
from employees
where department_id = (select department_id
from employees
    where last_name = 'Ziotkey');
    

Q2. 급여가 평균 급여보다 많은 모든 사원의 사원번호와 이름을 표시하는 질의를 작성하고 결과를 급여에 대해 오름차순으로 정렬하십시오.

select employee_id, last_name, salary
from employees
where salary > (select avg(salary)
from employees)
order by salary;

Q3. 이름에 u가 포함된 사원과 같은 부서에서 일하는 모든 사원의 사원 번호와 이름을 표시하는 질의를 작성하시오.

select employee_id, last_name
from employees
where department_id = (select distinct department_id
from employees 
where last_name like '%u%');

Q4. 부서 위치 ID가 1700인 모든 사원의 이름, 부서 번호 및 업무 ID를 표시하십시오.

select last_name, department_id, job_id
from employees join locations
where location_id = 1700; 

#sol2
select last_name, department_id, job_id
from employees 
where department_id in (select deprtment_id
from departments 
    where location_id = 1700);

 
Q5. King에게 보고하는 모든 사원의 이름과 급여를 표시하십시오.

select last_name, salary
from employees
where manager_id in (select employee_id   #출력하는 값이 단일행이 아니라서 = 만 쓰면 안됌
from employees 
where last_name = 'king'); 

#sol2.
select last_name, salary
from employees
where manager_id = any (select employee_id 
from employees 
where last_name = 'king');

Q6. Executive 부서의 모든 사원에 대한 부서 번호, 이름 및 업무 ID를 표시하십시오.

select e.department_id, e.last_name, e.job_id
from employees e join departments d
where e.department_id = d.department_id
and d.department_name = 'executive';

#sol2
select department_id, last_name, job_id
from employees
where department_id = (select department_id 
from departments 
where department_name = 'Executive');

Q7. 3번 문제의 질의를 수정하여 평균 급여보다 많은 급여를 받고 이름에 u가 포함된 사원과 같은 부서에서 근무하는 모든 사원의 사원번호, 이름 및 급여를 표시하시오.

select employee_id, last_name, salary
from employees
where salary > all(select avg(salary)
from employees)
and department_id in (select department_id
from employees 
where last_name like '%u%');

#if함수 #if(수식, 참일때실행문, 거짓일때실행문)

select if(hire_date like '2005%', last_name, '2005년 입사 아님')

from employees;

#ifnull(컬럼/값, 널일때실행문)

select last_name, commission_pct, 12*salary+12*salary*commission_pct

from employees;

select last_name, commission_pct, 12*salary+12*salary*ifnull(commission_pct, 0)

from employees;

#nullif(exp1, exp2): exp1과 exp2가 같으면 null, 같지 않으면 exp1 반환

select last_name, length(last_name), first_name, length(first_name), 
	nullif(length(last_name), length(first_name))

from employees;

#case문: 비교할 조건이 여러개 일때 사용

case exp when 값1 then 실행문
	when 값2 then 실행문
	when 값3 then 실행문
	else 실행문
end as '컬럼별칭' 

select department_id,
case department_id when 10 then '10번 부서'
	when 20 then '20번 부서' when 30 then '30번 부서'
	else '이외 부서' end as '부서명'
from employees;

Q1. 그룹 함수는 여러 행에 적용되어 그룹 당 하나의 결과를 출력합니다. ( True / False ) T

Q2. 그룹 함수는 계산에 널을 포함합니다. ( True / False ) F

Q3. WHERE 절은 그룹 계산에 행을 포함시키기 전에 행을 제한합니다. ( True / False ) T

Q4. 모든 사원의 급여 최고액, 최저액, 총액 및 평균액을 표시하십시오. 열 레이블을 각각 Maximum, Minimum, Sum 및 Average 로 지정하고 결과를 정수로 반올림한 후 작성한 SQL문을 lab5_4.sql이라는 파일에 저장하십시오.

select max(salary) as Maximum, min(salary) as Minimum, sum(salary) as Sum, 
round(avg(salary), 1) as Average
from employees;

Q5. lab5_4.sql의 질의를 수정하여 각 업무 유형별로 급여 최고액, 최저액, 총액 및 평균액을 표시하십시오. lab5_4.sql을 lab5_5.sql로 다시 저장하고 lab5_5.sql의 명령문을 실행하십시오.

select job_id, max(salary) as Maximum, min(salary) as Minimum, sum(salary) as Sum, 
avg(salary) as Average
from employees
group by job_id;

Q6. 업무가 동일한 사원 수를 표시하는 질의를 작성하십시오.

select job_id, count(job_id) as 'Count(*)'
from employees
group by job_id;

Q7. 관리자는 나열하지 말고 관리자 수를 확인하십시오. 열 레이블은 Number of Managers로 지정하십시오 .힌트 : MANAGER_ID 열을 사용하여 관리자 수를 확인하십시오.

select count(manager_id) as 'Number of Managers'
from employees;

Q8. 최고 급여와 최저 급여의 차액을 표시하는 질의를 작성하고 열 레이블을 DIFFERENCE로 지정하십시오.

select max(salary) - min(salary) as 'DIFFERENCE'
from employees

Q9. 관리자 번호 및 해당 관리자에 속한 사원의 최저 급여를 표시하십시오. 관리자를 알 수 없는 사원 및 최저 급여가 $ 6,000 미만인 그룹은 제외시키고 결과를 급여에 대한 내림차순으로 정렬하십시오.

select manager_id, min(salary) 
from employees    
WHERE manager_id IS NOT NULL      
GROUP BY manager_id      
HAVING MIN(salary) > 6000      
ORDER BY MIN(salary) DESC;

Q10. 각 부서에 대해 부서 이름, 위치, 사원 수, 부서 내 모든 사원의 평균 급여를 표시하는 질의를 작성하고, 열 레이블을 각각 Name, Location, Number of People 및 Salary로 지정하십시오. 평균 급여는 소수점 둘째 자리로 반올림하십시오.

select department_name as 'Name', location_id as 'Location', count(*) as 'Number of People', 
round(avg(salary), 1) as Salary 
from employees join departments
using(department_id)
group by department_name;

Q11. 총 사원 수 및 2005, 2006, 2007, 2008년에 입사한 사원 수를 표시하는 질의를 작성하고 적합한 열 머리글을 작성하십시오.

select count(*) TOTAL,
sum(if(hire_date like '2005%', 1, 0)) '2005%',
sum(if(hire_date like '2006%', 1, 0)) '2006%',
sum(if(hire_date like '2007%', 1, 0)) '2007%',
sum(if(hire_date like '2008%', 1, 0)) '2008%'
from employees;

Q12. 업무를 표시한 다음 해당 업무에 대해 부서 번호별 급여 및 부서 20, 50, 80 및 90의 급여 총액을 각각 표시하는 행렬 질의를 작성하고 각 열에 적합한 머리글을 지정하십시오.

select job_id,
sum(case department_id when 20 then salary end) Dept20,
sum(case department_id when 50 then salary end) Dept50,
sum(case department_id when 80 then salary end) Dept80,
sum(case department_id when 90 then salary end) Dept90,
sum(salary) Total
from employees
group by job_id;

Q1. 모든 사원의 이름, 부서 번호, 부서 이름을 표시하는 질의를 작성하십시오.

select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

#sol2.
select last_name, e.department_id, department_name
from employees e join departments d
on e.department_id = d.department_id;

Q2. 부서 80에 속하는 모든 업무의 고유 목록을 작성하고 출력 결과에 부서의 위치를 포함시키십시오.

select e.job_id, d.location_id
from employees e, departments d
where e.department_id = d.department_id
and d.department_id =80;

#sol2.
select job_id, location_id
from employees join departments
using(department_id)
where department_id = 80;

Q3. 커미션을 받는 사원의 이름, 부서 이름, 위치 ID 및 도시를 표시하는 질의를 작성하십시오.

#city가 있는 테이블이 뭔지 못찾았음.
select e.last_name, e.department_id, d.location_id
from employees e, departments d
where e.department_id = d.department_id
and commission_pct is not null; 

#sol2
select last_name, department_name, location_id, city
from employees natural join departments
natural join locations
where commission_pct is not null;

Q4. 이름에 a(소문자)가 포함된 모든 사원의 이름과 부서 이름을 표시하시오.

select e.last_name, e.department_id
from employees e, departments d
where e.department_id = d.department_id
and last_name like '%a%';

#sol2.
select last_name, department_id
from employees join departments
using(department_id)
where last_name like '%a%'
and not last_name like 'a%';

Q5. Toronto에서 근무하는 모든 사원의 이름, 업무, 부서 번호 및 부서 이름을 표시하는 질의를 작성하십시오.

select last_name, job_id, department_id, department_name 
from employees join departments
using(department_id)
join locations
using(location_id)
where city = 'toronto';

Q6. 사원의 이름 및 사원 번호를 관리자의 이름 및 관리자 번호화 함께 표시하고 각각의 열 레이블을 Employee, Emp#, Manager, Mgr#로 지정하십시오.

select e.last_name as 'Employee', e.employee_id as 'EMP#', 
m.last_name as 'Manager', m.employee_id as 'Mgr#'
from employees e join employees m
on e.manager_id = m.employee_id;

Q7. 6번을 수정하되 King을 포함하여 관리자가 없는 모든 사원을 표시하도록 하고 결과를 사원 번호를 기준으로 정렬하십시오.

select e.last_name as 'Employee', e.employee_id as 'EMP#', 
m.last_name as 'Manager', m.employee_id as 'Mgr#'
from employees e left outer join employees m
on e.manager_id = m.employee_id;

Q8. 지정한 사원의 이름, 부서 번호 및 지정한 사원과 동일한 부서에서 근무하는 모든 사원을 표시하도록 질의를 작성하고 각 열에 적합한 레이블을 지정하십시오.

select e.last_name, e.department_id, c.last_name as colleague
from employees e join employees c
on e.department_id = c.department_id
where e.last_name <> c.last_name;

Q9. JOB_GRADES 테이블의 구조를 표시하고 모든 사원의 이름, 업무, 부서 이름, 급여 및 등급을 표시하는 질의를 작성하십시오.

# Job_grade 테이블
create table job_grades(
gra char(1) primary key,
lowest_sal int ,
highest_sal int
);

insert into job_grades values('A', 1000, 2999);
insert into job_grades values('B', 3000, 5999);
insert into job_grades values('C', 6000, 9999);
insert into job_grades values('D', 10000, 14999);
insert into job_grades values('E', 15000, 24999);
insert into job_grades values('F', 25000, 40000);
desc job_grades;

select * from job_grades;

desc job_grades;
select last_name, job_id, department_name, salary, gra
from employees join departments
using (department_id)
join job_grades
on salary between lowest_sal and highest_sal;

Q10. Davies라는 사원보다 늦게 입사한 사원의 이름과 입사일을 표시하는 질의를 작성하십시오.

select e2.last_name, e2.hire_date
from employees e1 join employees e2
on e1.hire_date < e2.hire_date
where e1.last_name = 'Davies';

Q11. 관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 함께 표시하고 열 레이블을 각각 Employee, Emp Hired, Manager, Mgr Hired

select e.last_name as 'Employee', e.hire_date as 'Emp Hired', 
m.last_name as 'Manager', m.hire_date as 'Mgr Hired'
from employees e join employees m
on e.hire_date < m.hire_date;

조인(Join)

• 등가조인

    : 조인기준 컬럼의 값이 같을 조건으로조인

select 이름, 부서이름

from 사원테이블 natural join 부서테이블

select 이름, 부서이름

from 사원테이블 join 부서테이블

using(부서번호)   -> 조인할 컬럼을 지정

select 이름,부서이름

from 사원테이블 e join 부서테이블 d

on e.부서번호 = d.부서번호

where 부서번호 = 10

• Outer Join(포괄조인)

     조인 조건을 만족하지 않는 행을 같이 보여줌

     Left outer join : 왼쪽 테이블의 만족하지 않는 행을 같이 보여줘라

#left join
select 이름,부서이름

from 사원테이블 e left outer join 부서테이블 d

on e.부서번호 = d.부서번호

     Right outer join : 오른쪽 테이블의 만족하지 않는 행을 같이 보여줘라

#right join

select 이름,부서이름

from 사원테이블 e right outer join 부서테이블 d

on e.부서번호 = d.부서번호

     full join (sql에서는 제공 안해서 합집합으로)

#full outer join
select 이름,부서이름

from 사원테이블 e left outer join 부서테이블 d

on e.부서번호 = d.부서번호

union

select 이름,부서이름

from 사원테이블 e right outer join 부서테이블 d

on e.부서번호 = d.부서번호

• self join

     자기 자신을 조인 -> 테이블 별칭 꼭 사용해야 함

     ex) 사번이 175번인 사람의 매니저 이름이 무엇인가?

select e.이름, m.이름
from 사원테이블 e join 사원테이블 m
on e.manager_id = m.사번
where e.사번= 175

• 비등가조인

     : 등가(=)가 아닌 조건으로 조인

create table job_grades(
gra char(1) primary key,
lowest_sal int ,
highest_sal int
);

insert into job_grades values('A', 1000, 2999);
insert into job_grades values('B', 3000, 5999);
insert into job_grades values('C', 6000, 9999);
insert into job_grades values('D', 10000, 14999);
insert into job_grades values('E', 15000, 24999);
insert into job_grades values('F', 25000, 40000);
desc job_grades;

select * from job_grades;

select employee_id, last_name, salary, gra
from employees e join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;