(3주차 15일) Ubuntu, MariaDB

MariaDB

> Ubuntu에 MariaDB 연결

• netstat - tnl; -> tcp 프로토콜의 listening 상태에 있는 프로토콜을 호출
  netstat -tnl | grep 22; -> 22번 포트에서 잘 돌아가는지 확인
  systemctl status ssh; -> ssh 서비스의 상태 확인
  systemctl; -> 서비스 관리
  
  1. mariaDB 서버 설정: sudo apt install mariadb-server 
  2. mariadb -- version
     systemctl status mariadb
     netstat -tnl | grep 3306
  3. 보안환경 설치: sudo mariadb-secure-installation
  4. mariaDB에 연결: sudo mariadb -u root -p
• MariaDB 연결 확인
  1. mariaDB 버전 확인: select version();
  2. mariaDB 내 DB 목록 확인: show databases; 

 

> Linux 환경에서 쿼리 활용

• testDB 생성 및 접근
  
  1. testDB 생성: create database testDB;
  2. DB 접근: use testDB;

• 사용자 생성 및 권한 설정
  
  1. create user `beyond`@`%` identified by 'beyond';
  2. select host, user, password from mysql.user;
  3. 권한 부여: grant all privileges on testDB.* to `beyond`@`%`;
  4. 권한 확인: show grants for `beyond`@`%`;
  5. 권한 적용: flush privileges;

Bye~🖐️

• DB 접근 IP 주소 변경
  
  1. 설정 파일 접근: sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
  2. 모든 호스트에서 접근: bind-address = 0.0.0.0
  3. sudo systemctl restart mariadb; -> 설정 파일에 변경사항 적용 후 재실행

• Ubuntu - HeidiSQL(외부)에서 DB 접근
  
  1. Oracle Vm VirtualBox -> 포트 포워딩 규칙 설정
  2. MariaDB 세션 설정

포트 포워딩 규칙 설정(MariaDB 포트 확인)

HeidiSQL 세션 설정

 

> 테이블 조인 실습

/*
* 실습 문제 1 - 테이블 조인
*/
-- department 테이블에서 location_id 열에 FOREIGN KEY 제약 조건 추가
ALTER TABLE department ADD CONSTRAINT FOREIGN KEY(location_id)
REFERENCES location(local_code);

-- location 테이블에서 national_code 열에 FOREIGN KEY 제약 조건 추가
ALTER TABLE location ADD CONSTRAINT FOREIGN KEY(national_code)
REFERENCES national(national_code);

-- 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명을 조회
SELECT a.emp_id, emp_name, b.dept_title, c.local_name
FROM employee a LEFT JOIN  department b ON a.dept_code = b.dept_id
			LEFT JOIN location c ON b.location_id = c.local_code;

-- 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명, 국가명 조회
SELECT a.emp_id, emp_name, b.dept_title, c.local_name, d.national_name
FROM employee a LEFT JOIN  department b ON a.dept_code = b.dept_id
			LEFT JOIN location c ON b.location_id = c.local_code
			LEFT JOIN national d ON c.national_code = d.national_code;

-- 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명, 국가명, 급여 등급 조회
SELECT a.emp_id, emp_name, b.dept_title, c.local_name, d.national_name, e.sal_level
FROM employee a LEFT JOIN  department b ON a.dept_code = b.dept_id
			LEFT JOIN location c ON b.location_id = c.local_code
			LEFT JOIN national d ON c.national_code = d.national_code
			LEFT JOIN sal_grade e ON a.salary BETWEEN e.min_sal AND e.max_sal;

-- 부서가 있는 직원들의 직원명, 직급명, 부서명, 근무 지역을 조회하시오.
SELECT a.emp_name, f.job_name, b.dept_title, c.local_name
FROM employee a LEFT JOIN  department b ON a.dept_code = b.dept_id
			LEFT JOIN location c ON b.location_id = c.local_code
			LEFT JOIN national d ON c.national_code = d.national_code
			LEFT JOIN sal_grade e ON a.salary BETWEEN e.min_sal AND e.max_sal
			LEFT JOIN job f ON a.job_code = f.job_code
WHERE a.dept_code IS NOT NULL;

-- 직급이 대리이면서 ASIA 지역에서 근무하는 직원들의 사번, 직원명, 직급명, 부서명, 근무지역, 급여를 조회하세요.
SELECT a.emp_id, a.emp_name, f.job_name, b.dept_title, c.local_name, a.salary
FROM employee a LEFT JOIN  department b ON a.dept_code = b.dept_id
			LEFT JOIN location c ON b.location_id = c.local_code
			LEFT JOIN national d ON c.national_code = d.national_code
			LEFT JOIN sal_grade e ON a.salary BETWEEN e.min_sal AND e.max_sal
			LEFT JOIN job f ON a.job_code = f.job_code
WHERE f.job_name = '대리' AND c.local_name LIKE 'ASIA%';

-- 보너스를 받는 직원들의 직원명, 보너스, 연봉, 부서명, 근무지역을 조회하세요.
-- 단, 부서 코드가 없는 사원도 출력될 수 있게 OUTER JOIN 사용
SELECT a.emp_name, a.bonus, a.salary * 12 AS salary, b.dept_title, c.local_name
FROM employee a LEFT JOIN  department b ON a.dept_code = b.dept_id
			LEFT JOIN location c ON b.location_id = c.local_code
			LEFT JOIN national d ON c.national_code = d.national_code
			LEFT JOIN sal_grade e ON a.salary BETWEEN e.min_sal AND e.max_sal
			LEFT JOIN job f ON a.job_code = f.job_code
WHERE a.bonus IS NOT NULL;

-- 한국과 일본에서 근무하는 직원들의 직원명, 부서명, 근무지역, 근무 국가를 조회하세요.
SELECT a.emp_name, b.dept_title, c.local_name, d.national_name
FROM employee a LEFT JOIN  department b ON a.dept_code = b.dept_id
			LEFT JOIN location c ON b.location_id = c.local_code
			LEFT JOIN national d ON c.national_code = d.national_code
			LEFT JOIN sal_grade e ON a.salary BETWEEN e.min_sal AND e.max_sal
			LEFT JOIN job f ON a.job_code = f.job_code
WHERE d.national_name IN ('한국', '일본');

 

> 테이블 정의서 실습

Ubuntu에서 MariaDB에 university 데이터베이스 생성하고, 권한 부여

권한 적용

/*
* 실습 문제 2 - 테이블 정의서
*/
--  DDL for Table tb_department
CREATE TABLE tb_department (	
   department_no VARCHAR(10) PRIMARY KEY NOT NULL, 
	department_name VARCHAR(20) NOT NULL, 
	category VARCHAR(20), 
	open_yn CHAR(1), 
	capacity INT
);

--  DDL for Table tb_student
CREATE TABLE tb_student (	
   student_no VARCHAR(10) PRIMARY KEY NOT NULL, 
	department_no VARCHAR(10) NOT NULL REFERENCES tb_department(department_no), 
	student_name VARCHAR(30) NOT NULL, 
	student_ssn VARCHAR(14), 
	student_address VARCHAR(100), 
	entrance_date DATE, 
	absence_yn CHAR(1), 
	coach_professor_no VARCHAR(10)
);

--  DDL for Table tb_class
CREATE TABLE tb_class (	
   class_no VARCHAR(10) PRIMARY KEY NOT NULL, 
	department_no VARCHAR(10) NOT NULL REFERENCES tb_department(department_no), 
	preattending_class_no VARCHAR(10), 
	class_name VARCHAR(30) NOT NULL, 
	class_type VARCHAR(10)
);

--  DDL for Table tb_professor
CREATE TABLE tb_professor (	
   professor_no VARCHAR(10) PRIMARY KEY NOT NULL, 
	professor_name VARCHAR(30) NOT NULL , 
	professor_ssn VARCHAR(14), 
	professor_address VARCHAR(100), 
	department_no VARCHAR(10) REFERENCES tb_department(department_no)
);

--  DDL for Table tb_class_professor
CREATE TABLE tb_class_professor (	
   class_no VARCHAR(10) NOT NULL REFERENCES tb_class(class_no), 
	professor_no VARCHAR(10) NOT NULL REFERENCES tb_department(department_no)
);

-- 한번에 테이블의 기본키, 외래키 지정 가능
CREATE TABLE tb_class_professor (
	class_no VARCHAR(10),
	professor_no VARCHAR(10),
	PRIMARY KEY (class_no, professor_no),
	FOREIGN KEY (class_no) REFERENCE tb_class(class_no),
	FOREIGN KEY (professor_no)  REFERENCES tb_department(department_no)
);

--  DDL for Table tb_grade
CREATE TABLE tb_grade (	
   tern_no VARCHAR(10) NOT NULL, 
	class_no VARCHAR(10) NOT NULL REFERENCES tb_class(class_no),  
	student_no VARCHAR(10) NOT NULL REFERENCES tb_student(student_no), 
	`point` DECIMAL(3, 2)
);

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

-- 제약조건 추가
-- PK 결합하여 추가(tb_class_professor, tb_grade)
ALTER TABLE tb_class_professor ADD CONSTRAINT PRIMARY KEY (class_no, professor_no);
ALTER TABLE tb_grade ADD CONSTRAINT PRIMARY KEY (tern_no, class_no, student_no);

-- FOREIGN KEY 제약 조건 추가
-- tb_student
ALTER TABLE tb_student ADD CONSTRAINT FOREIGN KEY(coach_professor_no)
REFERENCES tb_professor(professor_no);

-- tb_class
ALTER TABLE tb_class ADD CONSTRAINT FOREIGN KEY(preattending_class_no)
REFERENCES tb_class(class_no);