MySQL 활용 예제

MySQL 활용 예제

https://github.com/datacharmer/test_db   에 들어가서 파일을 받고 푼다음에

MySQL >> FiLE >> RUN SQL Scripter 압축을 푼 파일로 들러가서 employeess선택해서 불러온다

https://github.com/nomemory/hr-schema-mysql

여기서도 hr 파일을 불러온다

Name : employess ,hr

Set  : utf-8

좌측에 추가 된 것을 확인 할 수 있다 

메뉴창에서 Database >> Reverse Engineer > hr 을 클릭 하면

이런 그림이 나온다 이 그림은 database 가 어떻게 연결 되어있는지 를 보여준다

MySQL 활용 예제

아래는 연습 해볼 것 !!

-- SQL 주석

-- 기본적인 데이터베이스의 확인

SHOW DATABASES; -- 전체 데이터베이스 목록

SELECT USER(); -- 현재 데이터베이스 사용자

SELECT DATABASE(); -- 현재 데이터베이스

USE sakila; -- sakila DB를 사용

SELECT DATABASE();

SHOW TABLES; -- 현재 데이터베이스 내의 테이블 목록

DESC actor; -- actor 테이블의 구조 확인

-- 데이터베이스 사용자 생성

CREATE USER 'dev'@'localhost' IDENTIFIED BY 'bittest';

-- 사용자 암호 변경

ALTER USER 'dev'@'localhost' IDENTIFIED BY 'bitr39';

-- 사용자 삭제

DROP USER 'dev'@'localhost';

-- 사용자 생성

CREATE USER 'dev'@'localhost' IDENTIFIED BY 'bitr39';

-- dev 사용자에게 employess 데이터베이스의 모든 권한을 부여

GRANT all privileges ON employees.* TO 'dev'@'localhost';

-- dev 사용자에게 hr 데이터베이스의 모든 권한을 부여

GRANT all privileges ON hr.* TO 'dev'@'localhost';

-- DML : SELECT 문법

USE hr; -- hr DB 사용

-- 기본적인 SELECT

-- SELECT ~ FROM

SELECT * FROM employees; -- employees의 모든 레코드, 모든 컬럼

SELECT * FROM departments;

-- 모든 컬럼은 *

SELECT first_name,

    last_name,

    salary,

    hire_date

FROM employees;

-- 산술 연산 가능

-- 단순 산술 연산

SELECT 10 * 10 * 3.145159 FROM dual; -- dual은 MYSQL 시스템으로부터 값을 받는다.

-- 특정 컬럼을 산술 연산

SELECT

    frist_name,

    salary,

    salary /12

FROM employees;

-- NULL

-- 빈 값, 산술연산에 포함되면 결과는 항상 NULL

SELECT 10 * NULL FROM dual;

DESC employees;

SELECT commission_pct FROM employees;

-- salary + salary * commission_pct

SELECT first_name,

    salary,

    salary + salary * commission_pct

FROM employees;

-- IFNULL(값, NULL일 경우의 값)

SELECT first_name,

    salary,

    salary + salary * IFNULL(commission_pct, 0) "total salary"

FROM employees;

-- 문자열의 연결: concat 함수

SELECT     CONCAT(first_name + " " + last_name) as "Full Name"

FROM employees;

-- DISTINCT : 중복 형의 제거

SELECT * FROM employess; -- 107

SELECT DISTINCT last_name FROM employees; -- last_name 중복 제거: 102

SELECT department_id FROM employess;

SELECT DISTINCT department_id FROM employees;

-- SELECT ~ FROM ~ WHERE

-- WHERE 절에 추출하고자 하는 레코드의 조건을 부여

-- 단순 비교 연산자

-- 산술 비교 연산자

-- salary가 15000 이상인 사원의 목록

SELECT first_name, salary

FROM employees

WHERE salary >= 15000;

-- 입사일이 1991-01-01 이후 사원들의 목록

SELECT     first_name, salary

FROM employees

WHERE hire_date >= '1991-01-01';

-- 논리 조합 : AND, OR, NOT

-- 급여자 14000 이하이거나 17000이상인 사원의 목록

SELECT first_name, salary

FROM employees

WHERE salary <= 14000 OR salary >= 17000;

-- 급여가 14000 이상이고 17000 이하인 사원의 목록

SELECT first_name, salary

FROM employees

WHERE salary >= 14000 and salary <= 17000;

-- BETWEEN : 구간 정보

-- 위의 쿼리는 다음과 같이 사용

SELECT first_name, salary

FROM employees

WHERE salary BETWEEN 14000 AND 17000;

-- NULL 체크 조건 : IS NULL, IS NOT NULL

-- 커미션을 받지 않는 사원(commission_pct IS NULL)

SELECT first_name, commission_pct

FROM employees

WHERE commission_pct IS NULL;

-- 커미션을 받는 사원(commission_pct IS NOT NULL)

SELECT first_name, commission_pct

FROM employees

WHERE commission_pct IS NOT NULL;

-- 부서 id가 10 , 50인 사원의 목록

SELECT first_name, department_id

FROM employees

WHERE department_id = 10 or

    department_id = 50;

-- 위 쿼리는 IN 연산자로 변경

SELECT first_name, department_id

FROM employees

WHERE department_id IN(10, 50);

-- 부서 ID가 10, 50이 아닌 다른 부서의 사원

SELECT first_name, department_id

FROM employees

WHERE department_id != 10 AND

    department_id !=50;

-- 위 쿼리는 NOT IN

SELECT first_name, department_id

FROM employees

WHERE department_id NOT IN(10, 50);

-- LIKE 검색 : 문자열의 일부를 wildcard를 이용하여 매칭

-- %(여러개의 임의의 문자), _(한개의 임의의 문자)

-- first_name이 William인 사람

SELECT first_name, last_name FROM employees

WHERE first_name = "William";

-- first_name에 am이 포함된 사람

SELECT first_name FROM employees

WHERE first_name LIKE "%am%";

-- first_name에 첫 글자는 모르겠고 두번째 세번째 글자가 am인 사원

SELECT first_name FROM employees

WHERE first_name LIKE "_am%";

-- 레코드의 정렬 : ORDER BY

-- ASC(기본값: 오름차순), DESC(내림차순 정렬)

-- 사원의 기본 정보를 출력, hire_date(입사일) 빠른 사람부터 -> 늦은 사람 (ASC)

SELECT  first_name, hire_date

FROM employees

ORDER BY hire_date ASC; -- ASC가 기본 정렬 방식 -> 생략 가능

-- 사원의 기본 정보를 출력 급여가 15000이상, 급여가 많은 사람부터 -> 적은 사람 (DESC)

-- SELECT ~ FROM ~ WHERE ~ ORDER BY 순

SELECT first_name, hire_date

FROM employees

WHERE salary >= 15000

ORDER BY salary DESC, hire_date;

-- MYSQL Tip : ORDER BY rand()

SELECT * FROM employees

ORDER BY RAND();

-- 단일행 함수 : 개별 행에 적용되는 함수

-- 문자열 단일행 함수

SELECT first_name,

    LOWER(first_name), -- 소문자로 변환

    UPPER(first_name), -- 대문자로 변환

    LPAD(first_name, 10, '*'), -- 10자리 확보, 좌측 빈 공간에 * 문자를 채움

    RPAD(first_name, 10, '?')

FROM employees;

SELECT "    MYSQL    ",

    "****MYSQL****"

FROM dual;

SELECT LTRIM("    MYSQL    "), -- 좌측의 빈 공간 제거

    RTRIM("    MYSQL    "), -- 우측의 빈 공간 제거

    TRIM(BOTH "*" FROM "****MYSQL****"), -- 양쪽의 *을 제거

    TRIM(LEADING "*" FROM "****MYSQL****"), -- 왼쪽의 *을 제거

    TRIM(TRAILING "*" FROM "****MYSQL****"), -- 우측의 *을 제거

    LENGTH("MYSQL") -- 길이 측정

FROM dual;

-- SUBSTRING, SUBSTR -> 문자열 일부 추출

SELECT first_name, hire_date, SUBSTR(hire_date, 1, 4)

FROM employees

WHERE SUBSTR(hire_date, 1, 4) = "1993";

-- 치환 : REPLACE

SELECT REPLACE("The Heart Queen", "Heart", "Spade") From dual;

TRUNCATE(3.14159, 3) -- 버림 소수점 3 자리까지 표기

FROM dual;

-- Date 형 단일행 함수

-- 현재 날짜 : CURDATE(), CURRENT_DATE

SELECT CURDATE(), CURRENT_DATE FROM dual;

-- 현재 시간 : CURTIME(), CURRENT_TIME FROM dual;

SELECT CURTIME(), CURRENT_TIME FROM dual;

-- 현재 날짜와 시간 : now(), SYSDATE(), CURRENT_TIMESTAMP()

SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP() FROM dual;

-- 문자열 포매팅 : DATE_FROMAT

-- 현재 날짜를 %Y/%m/%d

SELECT DATE_FORMAT(CURDATE(), "%Y/%m/%d") FROM dual;

SELECT first_name, hire_date, DATE_FORMAT(hire_date, "%Y/%m/%d %H:%i:%s")

FROM employees;

-- 날짜 계산 함수

-- employees 테이블에서 입사일로부터 1개월 지난 정보를 함께 출력

SELECT first_name, hire_date, DATE_ADD(hire_date, INTERVAL 1 MONTH) FROM employees;

-- 일주일 후

SELECT now(), DATE_ADD(now(), INTERVAL 1 WEEK) as future FROM dual;

-- 일주일 전

SELECT NOW(), DATE_SUB(now(), INTERVAL 1 WEEK) as past FROM dual;