MySQL 코드 관련(INNER JOIN / 사원, 부서DB)

*SQL 코드 관련 1(INNER JOIN / 사원, 부서DB)

1. 데이터 베이스 삭제 / 생성 / 선택

# a5 데이터베이스 삭제/생성/선택
삭제 : drop database if exists a5;

생성 : create database a5;

선택 : use a5;

 

2. 테이블(부서 dept) 생성 / 테이블 내 값 추가 / 확인

# 부서(dept) 테이블 생성 및 홍보부서 기획부서 추가
1. 부서 테이블 생성
create table dept (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    `name` CHAR(100) NOT NULL UNIQUE
);

2. 부서 테이블에 홍보 부서 추가
insert into dept
SET regDate = NOW(),
`name` = '홍보';

3. 부서 테이블에 기획 부서 추가
INSERT INTO dept
SET regDate = NOW(),
`name` = '기획';

4. 추가 되었는지 부서 테이블 확인
select *
from dept;

 

2-1. 테이블(사원 emp) 테이블 생성 / 테이블 내 값 추가 / 확인

# 사원(emp) 테이블 생성 및 홍길동사원(홍보부서), 홍길순사원(홍보부서), 임꺽정사원(기획부서) 추가
1. 사원 테이블 생성
create table emp (
    id int(10) unsigned not null auto_increment,
    primary key(id),
    regDate datetime not null,
    `name` char(100) not null,
    deptName char(100) not null
);

2. 각 사원 부서 설정
insert into emp
set regDate = NOW(),
`name` = '홍길동',
deptName = '홍보';

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길순',
deptName = '홍보';

INSERT INTO emp
SET regDate = NOW(),
`name` = '임꺽정',
deptName = '기획';

 

3. 테이블 내 부서 이름 변경

# 홍보를 마케팅으로 변경
select *
from dept;

update dept
set `name` = '마케팅'
where `name` = '홍보';

select *
from emp;

update emp
set deptName = '마케팅'
where deptName = '홍보';

SELECT *
FROM emp;


# 마케팅을 홍보로 변경
UPDATE dept
SET `name` = '홍보'
WHERE `name` = '마케팅';

SELECT *
FROM dept;

UPDATE emp
SET deptName = '홍보'
WHERE deptName = '마케팅';

SELECT *
FROM emp;

 

3-1. 테이블 내 부서 이름 변경 ( 구조 변경 )

# 홍보를 마케팅으로 변경
# 구조를 변경하기로 결정(사원 테이블에서, 이제는 부서를 이름이 아닌 번호로 기억)
ALTER TABLE emp add COLUMN deptId int(10) unsigned not null;

select *
from emp;

update emp
set deptId = 1
where deptName = '홍보';

UPDATE emp
SET deptId = 2
WHERE deptName = '기획';

SELECT *
FROM emp;

alter table emp drop column deptName;

select *
from dept;

UPDATE dept
SET `name` = '마케팅'
WHERE `name` = '홍보';

select *
from dept;

select *
from emp;

 

4. INNER JOIN 관련

- INNER JOIN은 업무에서 주로 사용.

- WHERE 조건문이 없다면 조인될 조건에 따라 두 테이블이 모두 결합(Join) 된다.

* INNER JOIN 사용방법
SELECT <열 목록>
FROM <첫 번째 테이블>
    INNER JOIN <두 번째 테이블>
    ON <조인될 조건>
[WHERE 검색조건]

# 사장님께 드릴 인명록을 생성
select *
from emp;

# 사장님께서 부서번호가 아니라 부서명을 알고 싶어하신다.
# 그래서 dept 테이블 조회법을 알려드리고 혼이 났다.
SELECT *
FROM dept
where id = 1;

# 사장님께 드릴 인명록을 생성(v2, 부서명 포함, ON 없이)
# 이상한 데이터가 생성되어서 혼남
SELECT emp.*, dept.name AS `부서명`
FROM emp
inner join dept;

# 사장님께 드릴 인명록을 생성(v3, 부서명 포함, 올바른 조인 룰(ON) 적용)
# 보고용으로 좀 더 편하게 보여지도록 고쳐야 한다고 지적받음
SELECT emp.*, dept.id, dept.name AS `부서명`
FROM emp
inner join dept
ON emp.deptId = dept.id;

# 사장님께 드릴 인명록을 생성(v4, 사장님께서 보시기에 편한 칼럼명(AS))
SELECT emp.id AS `사원번호`,
emp.name AS `사원명`,
DATE(emp.regDate) AS `입사일`,
dept.name AS `부서명`
FROM emp
INNER JOIN dept
ON emp.deptId = dept.id
order by `부서명`, `사원번호`;
*/

 

 

*SQL 코드 관련 2

1. 데이터 베이스 삭제 / 생성 / 선택

# a6 DB 삭제/생성/선택 
DROP DATABASE IF EXISTS a6; 
CREATE DATABASE a6; 
USE a6; 

 

2. 테이블(부서 dept) 생성 / 테이블 내 값 추가 / 확인

# 부서(홍보, 기획) 
CREATE TABLE dept ( 
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    regDate DATETIME NOT NULL, 
    `name` CHAR(100) NOT NULL 
); 

INSERT INTO dept 
SET regDate = NOW(), 
`name` = '홍보'; 

INSERT INTO dept 
SET regDate = NOW(), 
`name` = '기획'; 

3. 테이블(부서 dept) 생성 / 테이블 내 값 추가 / 확인

# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원) 
CREATE TABLE emp ( 
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    regDate DATETIME NOT NULL, 
    `name` CHAR(100) NOT NULL, 
    deptId INT(10) UNSIGNED NOT NULL, 
    salary INT(10) UNSIGNED NOT NULL 
); 

INSERT INTO emp 
SET regDate = NOW(), 
`name` = '홍길동', 
deptId = 1, 
salary = 5000; 

INSERT INTO emp 
SET regDate = NOW(), 
`name` = '홍길순', 
deptId = 1, 
salary = 6000; 

INSERT INTO emp 
SET regDate = NOW(), 
`name` = '임꺽정', 
deptId = 2, 
salary = 4000; 

4. 특정 값의 수 / 큰 값 / 작은 값 출력 ( COUNT / MAX / MIN )

- COUNT : 입력된 값(특정 테이블 등)에 대한 수 출력

- MAX(값) : 입력된 값에 대한, 가장 큰 값 출력

- MIN(값) : 입력된 값에 대한, 가장 작은 값 출력

# 사원 수 출력 
SELECT COUNT(*) 
FROM emp; 

# 가장 큰 사원 번호 출력 
SELECT MAX(id) 
FROM emp; 

# 가장 고액 연봉 
SELECT MAX(salary) 
FROM emp; 

# 가장 저액 연봉 
SELECT MIN(salary) 
FROM emp; 

 

5. 특정 값의 합계 /  평균 및 MAX, MIN 응용 (SUM, AVG)

# 회사에서 1년 고정 지출(인건비) 
SELECT SUM(salary) 
FROM emp; 

# 부서별, 1년 고정 지출(인건비) 
SELECT deptId, SUM(salary) 
FROM emp 
GROUP BY deptId; 

# 부서별, 최고연봉 
SELECT deptId, MAX(salary) 
FROM emp 
GROUP BY deptId; 

# 부서별, 최저연봉 
SELECT deptId, MIN(salary) 
FROM emp 
GROUP BY deptId; 

# 부서별, 평균연봉 
SELECT deptId, AVG(salary) 
FROM emp 
GROUP BY deptId; 

6. INNER JOIN , GROUP 등 응용

# 부서별, 부서명, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수 
## V1(조인 안한 버전) 
SELECT E.deptId AS 부서번호, 
GROUP_CONCAT(E.name) AS 사원리스트, 
TRUNCATE(AVG(E.salary), 0) AS 평균연봉, 
MAX(E.salary) AS 최고연봉, 
MIN(E.salary) AS 최소연봉, 
COUNT(*) AS 사원수 
FROM emp AS E 
GROUP BY E.deptId 

## V2(조인해서 부서명까지 나오는 버전) 
SELECT D.name AS 부서, 
GROUP_CONCAT(E.name) AS 사원리스트, 
TRUNCATE(AVG(E.salary), 0) AS 평균연봉, 
MAX(E.salary) AS 최고연봉, 
MIN(E.salary) AS 최소연봉, 
COUNT(*) AS 사원수 
FROM emp AS E 
INNER JOIN dept AS D 
ON E.deptId = D.id 
GROUP BY E.deptId