240527 MariaDB(8) - 리눅스에 MariaDB 설치/NOT EQUAL JOIN 실습/조인 실습 문제

우리는 보통 윈도우를 실행하면 프로그램을 찾아가서 직접 실행한다. 

서비스와 데몬이 뭘까?

 

서비스와 데몬은 실제로 실행시키지 않아도 OS가 부팅 됐을 때 자동으로 백그라운드에서 실행되는 것을 말한다.

자세히 얘기하면 차이점은 존재하지만 둘은 거의 같은 용어로 표현이 된다고 한다.

지금은 그렇게만 알아두자!

 

시스템D(시스템 데몬)은 서비스를 관리한다.

systemctl을 통해서 시스템 D라는 프로세스에 접근해서 관리할 수가 있다.

 

-

 

우리는 패키지 매니저를 통해서 무언가를 다운로드한다.

시작에 앞서서 아래 명령어를 통해서 패키지 매니저가 가지고 있는 패키지를 업데이트해준다.

sudo apt update

 

저장소에 패키지들이 존재하고, PC에서 설치를 요청하면 apt가 저장소에서 가져와서 설치한다.

apt에 가지고 있는 목록들은 자동으로 갱신되지 않는다.

(만약 저장소에 존재하는 패키지가 2.1 버전인데 업데이트하지 않으면 apt가 가지고 있는 목록은 2.0이다.)

 

이러한 개념으로 apt에 존재하는 패키지들을 새로 갱신해 주어 설치를 진행한다.

sudo apt upgrade

 

위와 같은 명령어도 존재하는데, 패키지들의 최신 버전에 따라서 우리가 가지고 있는 패키지들을 업그레이드해주는 명령어이다.

시간이 얼마나 걸릴지 몰라서 이번 실습에서는 하지 않았다.

 

리눅스 환경에서 MariaDB 설치

---

리눅스 환경에서 MariaDB를 사용하기 위해 설치를 진행해 보자!

sudo apt install mariadb-server

 

위 명령어를 통해 mariaDB의 서버를 설치한다.

설치가 완료되면 아래 명령어 실행을 통해 클라이언트를 설치해 준다.

sudo apt install mariadb-client

 

설치가 완료 됐다면 아래 명령어를 통해 버전 정보 확인이 가능하다.

sudo mariadb --version

 

sudo mariadb --version 버전 정보 확인

 

정상적으로 설치가 되었으면 systemctl status mariadb라고 명령어를 입력하면 실행상태인 active라고 뜬다.

systemctl status maridb

 

netstat -tnl | grep 3306  입력해서 LISTEN 상태인지 확인한다.

netstat -tnl ❘ grep 3306

 

-

 

아래 명령어도 실행해 준다.

sudo mariadb-secure-installation

 

이 명령어를 실행할 때 나오는 질문들에 대해서는 각자 필요한 대답을 써주면 된다. 

유닉스 소켓을 통해 인증할 것인가?

 

유닉스 소켓을 통해서 인증할 것이냐?라는 질문이다.

 

루트 계정의 패스워드를 변경할 것인가?

 

루트 계정의 패스워드를 변경할 것이냐?라는 질문이다.

나는 Yes로 답해서 계정 패스워드를 바꾸어 주었다.

 

루트 계정의 원격 접속을 차단할 것인가?

 

루트 계정의 원격 접속을 차단할 것인가?라는 질문이다.

우리는 리눅스를 통해서 mariaDB로 접근해야 하기 때문에 No라고 답하였다.

 

테스트 DB와 접근 정보를 지울 것인가?

 

테스트 DB와 접근 정보 지울 것인가? 에 대한 질문이다.

 

권한 정보를 새로고침 할 것이냐?

권한정보 새로고침 할 것이냐?라는 질문이다.

여기까지 설정하면 All done! 이라며 완료되었다고 뜬다.

 

-

 

설정이 완료되었다면 mariaDB에서 루트 계정으로 로그인이 되는지 확인해 보자!

 

루트 계정 로그인

 

정상적으로 로그인이 되는 것을 확인할 수 있다.

 

아래 명령어들을 통해서 mariaDB의 버전 정보와 데이터베이스 목록을 확인할 수 있다.

select version(); -- 버전 정보 확인
show databases; -- 데이터베이스 목록 확인

또한, 루트 계정 로그인 시에 none이라고 되어있는 것은 데이터베이스가 선택되어 있지 않아서 그렇다.

 

아래와 같이 데이터베이스를 선택할 수 있다.

use (데이터베이스명);

데이터 베이스 선택

 

데이터 베이스를 선택했다면 show tables; 명령어를 통해서 어떠한 테이블이 존재하는지도 확인할 수 있다.

 

- 

 

이제 데이터베이스 새로 만들고, 계정을 새로 만들어 접근 권한을 줘보자!!

 

아래 명령어를 통해 새 데이터베이스를 만들어주었다.

testDB는 여기서 정한 데이터베이스 이름이고 원하는 대로 데이터베이스 명을 정해주면 된다.

create databases testDB;

 

새 데이터베이스 생성

 

show databases; 를 통해서 잘 생성 됐는지 확인도 가능하다.

데이터 베이스 생성 확인

 

데이터베이스 변경

데이터베이스도 변경해 주었다.

 

-

 

이제 사용자 계정을 만들어보자

사용자 계정 생성

identified by 뒤에는 패스워드를 의미한다.

 

사용자가 생성됐는지 확인하려면 아래 명령어를 실행한다.

select host, user, password from mysql.user;

 

사용자 생성 확인

 

우리가 생성한 beyond 계정이 잘 생성된 것을 확인할 수 있다.

패스워드는 외부에 노출되면 안 되기 때문에 암호화되어 보이는 것이다.

 

-

 

아래 명령어를 통해서 생성한 beyond 계정에 testdb에서 사용할 수 있는 모든 권한을 줘보자.

grant all privileges on testDB.* to `beyond`@`%`;

 

권한 부여

 

아래의 명령어를 통해서 권한이 주어졌는지 확인할 수 있다.

show grants for `beyond`@`%`;

권한 부여 확인

 

아래 명령어를 통해서 권한 적용까지 완료하자.

flush privileges;

권한 적용

 

-

 

아래 명령어를 통해서 파일을 하나 열어보자.

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

 

서버 설정 파일인데 기본적으로 로컬 호스트(내 피시)에서만 접근하게 되어있는데,

외부에서도 접근할 수 있도록 설정파일을 수정해 주기 위함이다.

(sudo를 붙이지 않으면 읽기 전용으로만 볼 수 있다. 수정 불가능!!)

 

pw 입력 후 vim 프로젝트에 들어가서 

/bind 입력 후 bind-address 위치로 가서 입력 모드(i)로 들어가서 IP 수정을 해준다.

IP 수정

 

IP를 0.0.0.0으로 바꾸어주고, 이것은 모든 접근을 허용하겠다는 뜻이다.

esc를 통해서 일반모드로 나온 뒤, :wq 해서 저장 후 종료를 한다.

 

-

 

수정이 완료 됐다면 아래 코드를 통해서 서버를 다시 실행해 준다.

(다시 실행하지 않으면 안 돌아갈 수도)

sudo systemctl restart maridb

 

 

이후에는 버츄얼 박스에서 포트 포워딩 설정을 하나 더 해주어야 한다.

버츄얼 박스 포트 포워딩 설정

 

외부에서 MariaDB로 접속할 수 있게 열어주는 것이다.

30027로 오면 가상머신의 22번 포트,

30028로 오면 가상머신의 3306번 포트로 보낸다.

 

이후 Heidisql을 열어서 신규로 세션을 하나 만들어준다.

호스트 IP는 똑같고, 포트를 30028로 바꿔준다.

3306은 윈도우에 설치된 마리아 디비로 연결하는 것이고

30028은 리눅스에 설치된 마리아 디비로 연결하는 것이다.

HeidiSQL 신규 세션 설정

 

설정이 다 되었으면 저장 후 열기 정상적으로 실행이 된다!!

 

 

NON EQUAL JOIN 실습

---

시간이 애매하게 남아서 테이블을 몇 개 더 추가해 주고

NON EQUAL JOIN 실습을 진행하였다.

 

NOT EQUAL JOIN

조인 조건에 등호(=)를 사용하지 않는 조인문을 비등가 조인이라고 한다.

 

-- 조인 조건에 등호(=)를 사용하지 않는 조인문을 비등가 조인이라고 한다.
-- employee 테이블과 sal_grade 테이블을 비등가 조인하여
-- 직원명, 급여, 급여 등급 조회
SELECT e.emp_name,
		 e.salary,
		 s.sal_level
FROM employee e
INNER JOIN sal_grade s ON e.salary BETWEEN s.min_sal AND s.max_sal;

 

NON EQUAL JOIN

 

employee 테이블의 salary 값에 따라서 sal_grade의 범위에 속하는 등급이 함께 출력되는 것을 알 수 있다.

범위에 속하지 않는 사람은 INNER JOIN 시에는 출력되지 않고,

LEFT OUTER JOIN으로 값을 출력했을 때 NULL로 뜬다.

 

조인/테이블 정의서 실습 문제

---

-- 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명을 조회
SELECT e.emp_id,
		 e.emp_name,
		 d.dept_title,
		 l.local_name
FROM department d
INNER JOIN employee e ON d.dept_id = e.dept_code
INNER JOIN location l ON d.location_id = l.local_code
ORDER BY emp_id;

 

SELECT e.emp_id,
		 e.emp_name,
		 d.dept_title,
		 l.local_name,
		 n.national_name
FROM department d
LEFT OUTER JOIN employee e ON d.dept_id = e.dept_code
LEFT OUTER JOIN location l ON d.location_id = l.local_code
LEFT OUTER JOIN NATIONAL n ON l.national_code = n.national_code;

 

 

LEFT OUTER JOIN을 하면 NULL 값으로 존재하는 값들도 다 조회가 된다.

 

테이블 정의서 실습 DDL

---

강사님께서 만들어주신 테이블 정의서를 보고 SQL문으로 테이블을 생성해 보자!

 

-- university 실습

CREATE DATABASE university;
GRANT ALL PRIVILEGES ON university.* TO `beyond`@`%`;
SHOW GRANTS FOR `beyond`@`%`;
FLUSH PRIVILEGES;

-- 학과 테이블 (tb_department)
CREATE TABLE tb_department (
	department_no VARCHAR(10) PRIMARY KEY,
	department_name VARCHAR(20) NOT NULL,
	category VARCHAR(20),
	open_yn CHAR(1),
	capacity INT
);

-- 학생 테이블 (tb_student)
CREATE TABLE tb_student (
	student_no VARCHAR(10) PRIMARY KEY,
	department_no VARCHAR(10) NOT NULL REFERENCES tb_department(department_no),
	student_name VARCHAR(30) NOT NULL,
	student_ssn VARCHAR(14) NULL,
	student_address VARCHAR(100) NULL,
	entrance_date DATE NULL,
	absence_yn CHAR(1) NULL,
	coach_professor_no VARCHAR(10) NULL REFERENCES tb_professor(professor_no)
);

-- 과목 테이블 (tb_class) 
-- 열 이름이 같으면 알아서 찾아감
-- 자기 자신을 부모 테이블로 하기 때문에 PK를 참조한다.
CREATE TABLE tb_class (
	class_no VARCHAR(10) PRIMARY KEY,
	department_no VARCHAR(10) NOT NULL REFERENCES tb_department(department_no),
	preattending_class_no VARCHAR(10) REFERENCES tb_class(class_no),
	class_name VARCHAR(30) NOT NULL,
	class_type VARCHAR(10)
);

-- 교수 테이블 (tb_professor)
CREATE TABLE tb_professor (
	professor_no VARCHAR(10) PRIMARY KEY,
	professor_name VARCHAR(30) NOT NULL,
	professor_ssn VARCHAR(14),
	professor_address VARCHAR(100),
	department_no VARCHAR(10) REFERENCES tb_department(department_no)
);

-- 과목 교수 테이블 (tb_class_professor)
CREATE TABLE tb_class_professor (
	class_no VARCHAR(10),
	professor_no VARCHAR(10),
	PRIMARY KEY (class_no, professor_no),
	FOREIGN KEY (class_no) REFERENCES tb_class(class_no),
	FOREIGN KEY (professor_no) REFERENCES tb_professor(professor_no)
);

CREATE TABLE tb_grade (
	tern_no VARCHAR(10),
	class_no VARCHAR(10),
	student_no VARCHAR(10),
	PRIMARY KEY(tern_no, class_no, student_no),
	FOREIGN KEY (class_no) REFERENCES tb_class(class_no),
	FOREIGN KEY (student_no) REFERENCES tb_student(student_no),
	POINT DECIMAL(3,2)
);