(1주차 4일) MariaDB, SQL 기본

MariaDB

> DBMS(Database Management System)

• 데이터베이스를 관리해 주는 시스템 또는 소프트웨어를 말한다.
• 클라이언트: 인터넷을 통해 서버에 요청할 수 있는 장치
  서버: 클라이언트의 요청을 처리하는 소프트웨어

> MariaDB 실습 환경 구축

• 설치 및 PATH 환경 변수 설정(설치 경로 등록)
  
  1. 시스템 환경 변수 편집 - 고급 - 환경 변수
  2. 시스템 변수 - Path - 경로 추가(MariaDB 설치 경로 - bin)
  3. MariaDB 버전 확인

mariadb --version
mariadb -V

 

• 루트 계정 로그인 및 데이터베이스 불러오기

mariadb -u root -p

MariaDB [(none)]> source employees.sql;
MariaDB [(employees)]> show databases;
MariaDB [employees]> show tables;

 

> HeidiSQL: MariaDB 사용 시 기본으로 제공되는 GUI 환경

 

> 사용자 생성

-- 사용자 생성(주소 지정 필요)
CREATE USER `beyond`@`%` IDENTIFIED BY 'beyond';

-- 사용자 확인(mysql.user 뷰 조회)
SELECT Host, User, Password FROM mysql.user;

-- beyond 사용자에 employees 데이터베이스에 대해 모든 권한을 부여
GRANT ALL PRIVILEGES ON employees.* TO `beyond`@`%`;

-- beyond 사용자에 testDB 데이터베이스에 대해 모든 권한을 부여
GRANT ALL PRIVILEGES ON testDB.* TO `beyond`@`%`;

-- 권한 확인
SHOW GRANTS FOR `beyond`@`%`;

-- 권한 적용
FLUSH PRIVILEGES;

• '%': MariaDB의 경우, 사용자 생성 시 주소 지정이 필요하며(ip 등), '%' 설정을 통해 모든 주소에서 접속 가능하도록 함
  * 127.0.0.1: 자기 자신의 pc(localhost)

• 테이블 혹은 뷰를 조회하는 경우, 현재 위치한 데이터베이스를 기준으로 조회하므로 타 데이터베이스를 조회하는 경우 테이블 혹은 뷰 이름 앞에 해당 데이터베이스를 표기해야 함. e.g. mysql.user; 

> SQL 기본 (1)

-- usertbl의 모든 데이터 조회
SELECT * FROM usertbl;

-- employees 테이블의 모든 데이터 조회
SELECT * FROM employees.employees;

/*
* SELECT
* 데이터 추출, 조회
*/
-- usertbl 테이블에서 데이터 조회
SELECT userID, NAME, addr FROM usertbl;
SELECT userID AS '아이디', NAME AS 이름, addr 주소 FROM usertbl; 

-- DISTINCT는 SELECT절에 한 번만 기술할 수 있다.
SELECT DISTINCT addr FROM usertbl; 
SELECT DISTINCT addr, mobile1 FROM usertbl;

SELECT * FROM usertbl ORDER BY birthYear LIMIT 5;

-- #산술연산
-- employee 테이블에서 연봉 데이터 추가(급여*12)
SELECT emp_name, salary, salary*12
FROM employee;

-- #IFNULL
-- 산술연산 중 NULL값이 존재하는 경우, NULL 결과값 반환
-- 보너스가 포함된 연봉((급여+(급여*보너스))*12)
SELECT emp_name, salary, salary*12, bonus,
IFNULL(bonus, 0),
(salary+(salary*bonus))*12,
(salary+(salary*IFNULL(bonus, 0)))*12
FROM employee;

/*
* WHERE
* 조건 설정. 연산자 사용 가능
*/
SELECT userID, NAME, height FROM usertbl
-- WHERE userID != 'KBS';
WHERE addr = '서울';

-- #IS NULL: null값은 비교연산자로 비교 불가능
SELECT * FROM usertbl
WHERE mobile1 IS NULL;

SELECT * FROM usertbl
WHERE height >= 174;

SELECT emp_name, salary, salary*12 FROM employee
WHERE salary*12 >= 50000000
ORDER BY salary;

-- #날짜 데이터 활용
SELECT * FROM usertbl
WHERE mDate = '2010-10-10';

SELECT * FROM usertbl
WHERE YEAR(mDate) <= 2010;

-- #AND, BETWEEN AND
SELECT * FROM usertbl
WHERE height >= 170 && height <= 182;

SELECT * FROM usertbl
WHERE height BETWEEN 170 AND 182
ORDER BY height;

SELECT * FROM usertbl
WHERE mDate >= '2008-01-01' AND mDate <= '2010-12-31';

-- #NOT
-- height이 170 이상 182 이하가 아닌 회원의 데이터 조회
SELECT * FROM usertbl
-- WHERE height NOT BETWEEN 170 AND 182
WHERE NOT height BETWEEN 170 AND 182
ORDER BY height;

-- #OR
SELECT emp_name, dept_code, salary FROM employee
WHERE dept_code = 'D5' OR salary >= 5000000;

-- #IN, LIKE
SELECT NAME, addr FROM usertbl
WHERE addr IN ('경북', '경남', '전남');
-- WHERE addr NOT IN ('경북', '경남', '전남');

SELECT * FROM usertbl
WHERE NAME LIKE '김%';
-- WHERE NOT NAME LIKE '김%';

SELECT * FROM usertbl
WHERE NAME LIKE '김_수';

-- #이스케이프 문자 '\'를 통해 '_'를 일반 문자로 인식
-- employee 테이블에서 이메일 중 _ 앞 글자가 3자리인 주소를 가진 데이터 조회
SELECT emp_id, emp_name, email FROM employee
WHERE email LIKE '___\_%';

-- 이스케이프 문자 설정을 변경할 수 있다.
SELECT emp_id, emp_name, email FROM employee
WHERE email LIKE '___$_%' ESCAPE '$';

/*
* ORDER BY: 조회된 데이터 정렬
* GROUP BY: 여러 값을 그룹으로 묶어 처리
*/
-- #ORDER BY
SELECT * FROM usertbl
ORDER BY addr ASC, mDate DESC;

 

> 연습 문제 (1)

-- EMPLOYEE 테이블에서 급여가 350만원 이상 600만원 이하를 받는 직원의
-- 사번, 직원명, 부서 코드, 급여 조회 (BETWEEN AND)
SELECT emp_id, emp_name, dept_code, salary FROM employee
WHERE salary BETWEEN 3500000 AND 6000000;

-- EMPLOYEE 테이블에서 입사일이 '1990-01-01' ~ '2001-01-01'인 사원의 모든 컬럼 조회
SELECT * FROM employee
WHERE hire_date BETWEEN '1990-01-01' AND '2001-01-01';

-- EMPLOYEE 테이블에서 전화번호 처음 3자리가 010이 아닌 사원의 이름, 전화번호 조회
SELECT emp_name, phone FROM employee
WHERE phone NOT LIKE '010%' OR phone IS NULL;

-- EMPLOYEE 테이블에서 이름 중에 '하'가 포함된 사원의 직원명, 주민번호, 부서 코드 조회
SELECT emp_name, emp_no, dept_code FROM employee
WHERE emp_name LIKE '%하%';

-- usertbl 테이블에서 가입일이 가장 늦은 회원 3명의 모든 데이터 조회
SELECT * FROM usertbl
ORDER BY mDate DESC LIMIT 3;

-- usertbl 테이블에서 아이디, 이름, 가입일 조회(별칭 포함)하고 name으로 내림차순 정렬
SELECT userID AS '아이디', NAME AS '이름', mDate AS '가입일'
FROM usertbl
ORDER BY NAME DESC;