[TIL] 20.12.23 MariaDB 데이터 형식, 내장 함수, 조인, 제약조건

[20.12.23] MariaDB 데이터 형식, 내장 함수, 조인, 제약조건

지난 시간 복습과 함께 ODB 등장, RDB와 NoSQL의 차이점에 대해서 배웠다.

(ODB나 RDB와 NoSQL 차이점은 추후 따로 게시글로 정리해야겠다.)

 

* MariaDB 데이터 형식

- 다양한 데이터 형식이 있으며 당연히 형식마다 차지하는 byte수가 달라진다.

- 워-낙에 많기 때문에 일일이 타이핑으로 정리하긴 힘들고 표를 그대로 옮긴다.

- 자주 쓰이는 건 쓰다 보면 알아서 외워질 테니 헷갈릴 때 찾아보자.

숫자 데이터 형식

문자 데이터 형식

날짜/시간 데이터 형식

 

* MariaDB 내장 함수

1) CASE ~ WHEN ~ THEN ~ ELSE ~ END

> CASE는 내장 함수는 아니고 연산자로 분류된다.

> 다중 분기에서 사용된다.

# (1)
SELECT case 10 when 1 then '일'
               when 5 then '오'
               when 10 then '십'
               ELSE '모름'
       END AS case연습;
# (2)
SELECT userID,
        case mobile1 when '010' then 'A'
                     when '011' then 'B'
                     when '018' then 'C'
                     ELSE 'D'
        END AS '모바일종류'
FROM usertbl;

> 1번 예시처럼 바로 값을 넣어서 사용할 수도 있고 2번 예시처럼 검색할 항목으로 넣을 수도 있다.

> case로 선언하고 비교할 값을 넣어준다.

> when 뒤에서 비교할 값의 경우에 따라 then으로 출력을 달리한다.

> 프로그래밍 언어에서 switch-case를 생각하거나 중첩 if 문을 생각하면 편하다.

 

2) 문자열 함수

> 여러 가지가 있다. 예시를 보면서 알아보자.

-- 문자 함수
SELECT UPPER('aa');	# 소문자->대문자
SELECT LOWER('AA');	# 대문자->소문자
-- LPAD : 문자열을 n글자로 채우되 왼쪽은 '*'로 채우라.
-- RPAD : LPAD와 같지만 오른쪽부터 '*'채움
SELECT LPAD('AA',10,'*');	# 출력 : ********AA
SELECT RPAD(userid, 5, '*') 사용자 FROM usertbl; # 출력 : ABC**
-- LTRIM/RTRIM : 문자열의 왼쪽/오른쪽 공백 제거 
-- TRIM : 문자열의 앞뒤 공백 모두 제거
SELECT TRIM('    이    ') 연습1, RTRIM('   이   ') 연습2;
-- TRIM ('방향' '자를 문자열' FROM '문자열') : 특정 방향으로 문자열에서 자를 문자열을 제거
-- 방향으로는 BOTH(양쪽), LEADING(앞), TRAILING(뒤)로 표시.
-- SUBSTRING(문자열, 시작위치, 길이) or SUBSTRING(문자열 FROM 시작위치 FOR 길이)
-- => 시작위치부터 길이만큼 문자를 반환, 길이 생략시 끝까지 반환
SELECT SUBSTRING('대한민국만세', 3, 2);
-- 출력 : 민국

 

3) 날짜/시간 함수

> 예시를 다루지는 않았기 때문에 적어놓기만 하고 나중에 실습해보도록 하자.

> ADDDATE(날짜, 차이) / SUBDATE(날짜, 차이) : 날짜 기준으로 차이만큼 더하거나 뺀 날짜 반환

> ADDTIME(날짜/시간, 시간) / SUBTIME(날짜/시간, 시간) : 날짜/시간 기준으로 시간을 더하거나 뺀 결과 반환

> CURDATE( ), CURTIME( ), NOW( ), SYSDATE( )

=> CURDATE( ) : 현재 연-월-일

=> CURTIME( ) : 현재 시:분:초

=> NOW( ), SYSDATE( ) : 현재 연-월-일 시:분:초

> YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)

=> 날짜 또는 시간에서 연, 월, 일, 시, 분, 초, 밀리초

> DATEDIFF(날짜1, 날짜2) : (날짜1-날짜2)의 일 수 반환

 

* 조인(Join)

- 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만드는 것

- INNER, OUTER, CROSS, SELF JOIN이 있다.

 

1) INNER JOIN

> 가장 많이 사용되는 조인, 일반적으로 조인이라고 한다면 INNER JOIN을 나타냄

SELECT *
FROM usertbl JOIN buytbl -- 두 테이블을 조인한다.
ON usertbl.userID = buytbl.userID; -- 조인 조건은 userID가 동일하게.

> JOIN을 쓰거나 INNER JOIN을 쓰거나 둘 다 동일하게 작동한다.

 

2) OUTER JOIN

> 조인 조건에 만족되지 않는 행도 포함시킴

> LEFT OUTER JOIN : '왼쪽 테이블은 모두 출력되어야 한다.'로 이해

> RIGHT OUTER JOIN : '오른쪽 테이블은 모두 출력되어야 한다.'로 이해

LEFT OUTER JOIN 결과

RIGHT OUTER JOIN 결과

> 위 두 예시를 보면 알겠지만 두 테이블을 조인하면서 LEFT, RIGHT에 따라 어느 테이블을 기준으로 조인할지가 결정된다. 조인할 방향이 결정되면 그 방향에 있는 테이블의 데이터는 모두 표시되고 나머지 테이블의 데이터도 연결해서 표시한다. 다만, 갖고 있지 않은 데이터라도 NULL로 채운다.

 

3) CROSS JOIN(상호 조인)

> 한 쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능

> CROSS JOIN 결과 수 = A테이블 행 개수 * B테이블 행 개수

> 위의 내용만 설명하고 넘아가셨다. 자료에 의하면 테스트로 사용할 많은 용량의 데이터 생성 시 사용한다고 한다.

 

4) SELF JOIN(자체 조인)

> 자기 자신과 조인한다는 의미

> 같은 테이블 2개를 조인한다고 생각하면 된다.

> 하지만 쉽게 예시가 생각나지는 않는데, 강사님 설명에 의하면 다음의 예시와 같다.

SELF JOIN 예시

> 사원 테이블이 하나 있고 이를 각각 E, M으로 별칭을 취급하여 조인한다고 가정한다.

> 쿼리의 내용은 '사원의 상관의 구내번호'를 찾기 위한 쿼리다.

> 조회되는 내용은 사원 이름, 사원의 상관 이름, 그 상관의 구내번호이다.

> 단순 조회로는 뽑아낼 수 없는 내용을 뽑기 위해서 쓰는 듯하다. (서브 쿼리 잘 쓰면 될지도...?)

 

* 테이블 생성

- 하이디 SQL에서 GUI 형식으로 생성할 수 있고 쿼리로 생성할 수 있다.

 

1) 하이디 SQL에서 테이블 생성

> 데이터베이스에서 우클릭하여 [새로 생성] - [테이블]을 선택한다.

 

> 테이블의 이름을 정의하고(여기선 newtbl) 열을 추가한다.

> 원하는 행을 우클릭하여 [새 인덱스 생성] - [PRIMARY]를 통해 기본키를 설정할 수 있다.

 

> 기본키 설정을 완료했으면 저장한다.

 

> 이제 생성된 테이블을 열어서 [데이터] 탭에서 빈 창을 우클릭하여 [행 삽입]을 눌러준다.

 

> 행을 삽입하여 아까 지정해준 열의 형식에 맞게 데이터를 입력해준다.

> 다시 우클릭하여 행을 삽입할 수 있다.

 

2) SQL로 테이블 생성

> 일반 사용자는 GUI의 윈도우 OS가 편하고 개발자에겐 CLI Linux가 편하듯이 아마 이 방식을 선호할 듯하다.

> 먼저 앞선 예시와 동일한 결과를 도출하기 위해 앞서 만든 'newtbl' 테이블을 삭제해준다.

> 마우스로 우클릭해서 삭제해도 되고, 쿼리를 사용해도 된다.

# newtbl 생성
CREATE TABLE newtbl(
    data_id int primary key,
    name varchar(20) not null,
    store_date date
);

> 앞서 이거 저거 클릭하고 했던 과정 중 테이블 생성이 끝났다.

> 다만 앞에서 언급하지 않은 'not null' 이 등장했는데, 밑에서 추가로 설명하겠다.

> 이제 데이터를 넣어야 할 차례이다.

# (1)
INSERT INTO newtbl VALUES (1, '홍길동', '2021-01-18');
# (2)
INSERT INTO newtbl (data_id, name) VALUES (2, '홍길동');

> 두 개 모두 정상적으로 작동하는 쿼리인데, 약간의 차이가 있다.

> 1번 예시는 테이블의 모든 컬럼에 데이터를 지정해줄 때 사용한다.

> 2번 예시는 특정 컬럼에 데이터를 지정해줄 때 사용한다.

> 2번 예시에서 나머지 컬럼은 테이블 생성 시에 설정해준 제약조건에 따라서 null이나 자동 기본값 등이 들어간다.

 

* 제약 조건(Constraint)

- 데이터 무결성을 지키기 위해 제한된 조건

- 특정 데이터 입력 시 조건을 만족해야만 입력되도로 제약

 

1) 기본 키(Primary key) 제약 조건

> 테이블의 존재하는 여러 행을 구분할 수 있는 식별자를 '기본키'라고 한다.

> 기본키는 중복되거나 NULL값이 올 수 없다.

> 하나 이상의 열을 이용하여 기본키를 설정할 수 있다.

# (1)
CREATE TABLE userTBL(
    userID CHAR(8) PRIMARY KEY,
    NAME VARCHAR(10) NOT NULL,
    birthYear INT NOT NULL,
    email CHAR(30) NULL
);
# (2)
CREATE TABLE userTBL(
    userID CHAR(8) NOT NULL,
    NAME VARCHAR(10) NOT NULL,
    birthYear INT NOT NULL,
    email CHAR(30) NULL,
    CONSTRAINT PK_userTBL_userID
    PRIMARY KEY (userID)
);

> 1, 2번 예시 둘 다 동일하게 작동한다.

> 2번 예시의 경우 'CONSTRAINT ~'는 걸어주는 제약 조건에 이름을 붙이는 것이다.

> 제약 조건의 이름은 제약 조건을 지울 때 사용한다.

 

2) 외래 키(Foreign key) 제약 조건

> 다른 테이블의 열을 키로 설정하는 것이다.

> 외래키 테이블이 참조하는 기준 테이블의 열은 기본키이거나 Unique 제약 조건이 설정돼야 한다.

> 외래키의 옵션 중에 'ON DELETE CASCADE', 'ON UPDATE CASCADE'가 있다.

> 각 옵션은 기준 테이블 데이터 수정/삭제 시 외래키 테이블도 자동으로 적용되게 설정하는 것이다.

# 기준 테이블 생성
CREATE TABLE userTBL(
    userID CHAR(8) PRIMARY KEY,
    NAME VARCHAR(10) NOT NULL,
    birthYear INT NOT NULL,
    email CHAR(30) NULL
);
# (1)
CREATE TABLE tmpTBL(
    num INT AUTO_INCREMENT PRIMARY KEY,
    userID CHAR(8) NOT NULL.
    FOREIGN KEY(userID) REFERENCES userTBL(userID)
);
# (2)
CREATE TABLE tmpTBL(
    num INT AUTO_INCREMENT PRIMARY KEY,
    userID CHAR(8) NOT NULL.
);
ALTER TABLE tmpTBL
    ADD CONSTRAINT FK_userTBL_tmpTBL
    FORIEGN KEY(userID)
    REFERENCES userTBL(userID);

> 1번 예시는 테이블 생성 시 기존 테이블의 키를 참조해서 외래키를 설정한다.

> 2번 예시는 테이블을 생성한 뒤에 테이블 수정을 통해서 외래키를 설정한다.

 

※ 복합키

- 컬럼 2개 이상을 묶어서 기본키로 지정하는 것이다.

CREATE TABLE userTBL(
    userID CHAR(8),
    num INT,
    name VARCHAR(10),
    email CHAR(30),
    PRIMARY KEY (userID, num)
);

- userID, num 2개를 이용하여 기본키를 구성했다.

 

 

* 실습문제

> 오늘도 어김없이 캡처로 문제를 대신한다.

1) 조인

# 내 풀이
--문제 3
-- 1)
USE employees;
SELECT * FROM employees;
SELECT * FROM departments;

-- 1
SELECT d.dept_name, e.first_name, e.last_name
FROM departments d join dept_emp de
ON d.dept_no=de.dept_no
JOIN employees e
ON de.emp_no = e.emp_no;

-- 2
SELECT d.dept_name, e.first_name, e.last_name
FROM (SELECT * FROM departments WHERE departments.dept_no='d007') d join dept_emp de
ON d.dept_no = de.dept_no
JOIN employees e
on de.emp_no = e.emp_no;

-- 3
SELECT d.dept_name, e.first_name, e.last_name
FROM (SELECT * FROM departments WHERE departments.dept_no='d007') d join dept_emp de
ON d.dept_no = de.dept_no
JOIN (SELECT * from employees where gender='M') e
on de.emp_no = e.emp_no;

-- 4
SELECT d.dept_name, e.first_name, e.last_name
FROM (SELECT * FROM departments WHERE departments.dept_no='d007') d JOIN dept_emp de
ON d.dept_no = de.dept_no
JOIN (SELECT * from employees where gender='M' AND hire_date LIKE '2000%') e
on de.emp_no = e.emp_no;

-- 5
SELECT d.dept_name, e.first_name, e.last_name
FROM (SELECT * FROM departments WHERE departments.dept_no='d007') d JOIN dept_emp de
ON d.dept_no = de.dept_no
JOIN (SELECT * from employees where gender='M' AND hire_date LIKE '2000%') e
on de.emp_no = e.emp_no
ORDER BY e.first_name;

-- 6
SELECT e.first_name, e.last_name
FROM employees e JOIN titles t
ON e.emp_no = t.emp_no
WHERE t.title='Staff';

-- 7
SELECT AVG(s.salary)
FROM salaries s JOIN titles t
ON s.emp_no=t.emp_no
WHERE t.title = 'Staff';

-- 8
SELECT t.title, AVG(s.salary)
FROM salaries s JOIN titles t
ON s.emp_no=t.emp_no
GROUP BY t.title;

> 강사님의 풀이도 보았는데, 뭔가 내 풀이보다 한 두 가지 정도 더 작성하셨다. 내가 볼 때는 그냥 생략해도 괜찮아 보이는데 명확하게 하기 위해서 그런 건지 모르겠다. 출력을 비교해보면 알 텐데, 지금은 귀찮으니 나중에 하자... 일단은 여기에 올려놓아야겠다.

Use employees ; 

--1.  모든 부서의 이름(departments.dept_name)과  
--그 부서에 근무하는 모든 사원의 이름(employees.first_name 과 employees.last_name )을 
-- 출력하세요.   

SELECT  departments.dept_name, employees.first_name ,employees.last_name 
FROM  employees  join  dept_emp
ON  employees.emp_no = dept_emp.emp_no 
JOIN departments 
ON departments.dept_no = dept_emp.dept_no 
WHERE dept_emp.to_date LIKE '9999%'; 
  
--2.  d007번 부서의 이름(departments.dept_name)과  
--그 부서에 근무하는 모든 사원의 이름(employees.first_name  과 employees.last_name )을 출력하세요.

SELECT  departments.dept_name, employees.first_name ,employees.last_name 
FROM  employees  join  dept_emp
ON  employees.emp_no = dept_emp.emp_no 
JOIN departments 
ON departments.dept_no = dept_emp.dept_no 
WHERE dept_emp.to_date LIKE '9999%'
AND departments.dept_no = 'd007' ; 

--3.  d007번 부서의 이름(departments.dept_name)과 그 부서에 근무하는 모든 사원 중 남자인 사원의 모든 사원의 이름(employees.first_name  과 employees.last_name )을 출력하세요.
SELECT  departments.dept_name, employees.first_name ,employees.last_name 
FROM  employees  join  dept_emp
ON  employees.emp_no = dept_emp.emp_no 
JOIN departments 
ON departments.dept_no = dept_emp.dept_no 
WHERE dept_emp.to_date LIKE '9999%'
AND departments.dept_no = 'd007' 
AND employees.gender = 'M' ;   
 
--4.  d007번 부서의 이름(departments.dept_name)과 그 부서에 근무하는 모든 사원 중 남자이고, 2000년에 입사한 모든 사원의 이름(employees.first_name  과 employees.last_name )을 출력하세요.
SELECT  departments.dept_name, employees.first_name ,employees.last_name 
FROM  employees  join  dept_emp
ON  employees.emp_no = dept_emp.emp_no 
JOIN departments 
ON departments.dept_no = dept_emp.dept_no 
WHERE dept_emp.to_date LIKE '9999%'
AND departments.dept_no = 'd007' 
AND employees.gender = 'M' 
AND employees.hire_date LIKE '2000%' ;   

--5.  d007번 부서의 이름(departments.dept_name)과 그 부서에 근무하는 모든 사원 중 남자이고, 2000년에 입사한모든 사원의 이름(employees.first_name  과 employees.last_name )을 출력하세요. 
--단, 사원이름(employees.first_name) 오름차순으로 정렬하여 출력하세요 .
 
SELECT  departments.dept_name, employees.first_name ,employees.last_name,employees.hire_date  
FROM  employees  join  dept_emp
ON  employees.emp_no = dept_emp.emp_no 
JOIN departments 
ON departments.dept_no = dept_emp.dept_no 
WHERE dept_emp.to_date LIKE '9999%'
AND departments.dept_no = 'd007' 
AND employees.gender = 'M' 
AND employees.hire_date LIKE '2000%' 
ORDER BY employees.first_name  ASC ;   

 
--6,   Staff 업무(titles.title) 로 근무한 적이 있는 모든 사원의 이름(employees.first_name  과 employees.last_name )을 출력하세요. 

SELECT employees.first_name,  employees.last_name
FROM employees  JOIN   titles
ON employees.emp_no = titles.emp_no  
WHERE titles.title = 'Staff' ;  
  
  
-- 7.   Staff 업무(titles.title) 로 근무한 적이 있는 모든 사원의 평균 급여(salaries.salary) 를 출력하세요. 
-- 과거 salary도 포함.    
SELECT AVG( salaries.salary ) 
FROM titles JOIN salaries
ON salaries.emp_no = titles.emp_no
WHERE titles.title = 'Staff'  ;  -- 데이터 대소문자 구분 안함. 

-- 현재  salary만  포함.    
SELECT AVG( salaries.salary ) 
FROM titles JOIN salaries
ON salaries.emp_no = titles.emp_no
WHERE titles.title = 'Staff'  ; 
and salaries.to_date LIKE '9999%'; 

--8.  업무(titles.title) 별로  사원들의 평균 급여(salaries.salary) 를 출력하세요. 
-- 과거 salary도 포함. 
SELECT titles.title, AVG(salaries.salary)
FROM titles JOIN salaries
ON salaries.emp_no = titles.emp_no
GROUP BY titles.title ; 

-- 현재  salary만  포함.
SELECT titles.title, AVG(salaries.salary) AS 평균급여 
FROM titles JOIN salaries
ON salaries.emp_no = titles.emp_no
WHERE salaries.to_date LIKE '9999%'  
GROUP BY titles.title ;

 

※ 이 게시물은 '이것이 MySQL이다' 교재와 엔코아 플레이데이터에서 배운 내용을 토대로 작성되었습니다.