DB&SQL

데이터베이스

• 여러 사람이 공유하고 사용할 목적으로 통합 관리되는 정보의 집합
• 논리적으로 연관된 하나 이상의 자료의 모음으로 그 내용을 고도로 구조화 함으로써 검색과 갱신의 효율화를 꾀한 것
• 몇 개의 자료 파일을 조직적으로 통합하여 자료 항목의 중복을 없애고 자료를 구조화하여 기억시켜 놓은 자료의 집합체
• 통합된 데이터
  각자 사용하던 데이터를 모아서 중복을 최소화하고 데이터 불일치를 제거
• 저장된 데이터
  문서 형태로 보관되는 것이 아니라 저장장치(디스크, 테이프 등 컴퓨터 저장장치)에 저장됨
• 운영데이터
  조직의 목적을 위해서 사용되는 데이터를 의미
• 공용데이터
  여러 사람이 각각 다른 목적의 업무를 위해서 공통으로 사용되는 데이터를 의미

DBMS(Database Management System)

데이터베이스 관리 프로그램

• 데이터베이스 조작 인터페이스(추가, 수정, 삭제, 조회) 제공->무결성 보장
• 효율적인 데이터 관리 기능 제공
• 데이터베이스 구축 기능 제공
• 데이터 복구, 사용자 권한부여, 유지보수 기능제공

관계형 데이터 베이스

• 테이블 기반의 Database
• 테이블
  • 실제 데이터가 저장되는 곳
  • 행과 열의 2차원 구조를 가진 데이터 저장 장소
• 데이터를 테이블 단위로 관리
  • 테이블 간의 관계를 이용하여 필요한 데이터 검색가능

SQL(Structed Query Language)

• 관계형 데이터 베이스에서 데이터 조작과 데이터 정의를 위해 사용하는 언어
  • 데이터 조회
  • 데이터 삽입, 삭제, 수정
  • DB Object 생성 및 변경, 삭제
  • DB 사용자 생성 및 삭제, 권한 제어
• 표준 SQL은 모든 DBMS에서 사용 가능

SQL 특징

• 배우고 사용하기 쉽다
• 대소문자 구별X(데이터의 대소문자는 구분)->SELECT이나 select는 같다. 하지만 처음은 대문자로 쓰는 걸 권장
• 절자척인 언어가 아니라 선언적 언어이다.
• DBMS에 종속적이지 않다.

DML(Data Manipulation Language)

데이터 조작 언어

• 데이터베이스에서 데이터를 조작하거나 조회할 때 사용
• 테이블의 레코드(행)를 CRUD(Create, Read, Update, Delete)

-- 모든 컬럼 입력
INSERT INTO ssafy_user
VALUES (1, "GODQhr", "양명균", "1234", "godqhr@gmail.com", NOW());
-- 생성 시 작성한 모든 컬럼에 입력 값이 주어지면 컬럼 이름 생략 가능

-- 원하는 컬럼만 입력
INSERT INTO ssafy_User (user_id, user_nAme, user_password)
VALUES("kimssafy", "김싸피", "1q2w3e4r!@");
-- 컬럼이름과 입력 값의 순서가 일치하도록 작성(NULL, DEFAULT, AUTO INCREMENT 설정 필드 생략 가능)

-- 여러행 입력
INSERT INTO Ssafy_usEr (user_id, user_name, usEr_PASSWOrd)
VALUES  ("leessafy", "이싸피", "0000"),
		("parkssafy", "박싸피", "1111"),
        ("5Ssafy", "오싸피", "2222");
        
-- 데이터 수정 조건x(safe modE 해제) Edit -> prefereNCes -> SQLEditor
UPDATE ssafy_user
SET user_name = 'anonyMous';
-- 기존 레코드를 수정한다.
-- WHERE 절을 이용해 하나의 레코드 또는 다수의 레코드를 한 번에 수정할 수 있다.

-- usEr_num가 3번인 학생 비밀번호 1234로 수정
UPDATE ssafy_user
SET user_password = '1234'
WHERE user_num = 3;


-- 삭제
-- user_num가 4인 학생 삭제
DELETE FROM ssafy_user
WHERE user_num = 4;
-- 기존 레코드를 삭제한다.
-- WHERE 절을 이용해 하나의 레코드 또는 다수의 레코드를 한 번에 삭제할 수 있다.

SELECT * FROM ssafy_user;

-- SSAFY_CAMPUS 데이터 입력
DROP DATABASE IF EXISTS `SSAFY_CAMPUS`;

CREATE DATABASE `SSAFY_CAMPUS` DEFAULT CHARACTER SET utf8mb4;

USE SSAFY_CAMPUS;

CREATE TABLE IF NOT EXISTS `BONUS` ( 
`ENAME` varchar(10) DEFAULT NULL,
`JOB` varchar(9) DEFAULT NULL,
`SAL` double DEFAULT NULL,
`COMM` double DEFAULT NULL);

DROP TABLE IF EXISTS dept;

CREATE TABLE IF NOT EXISTS `DEPT` ( 
`DEPTNO` int(11) NOT NULL,
`DNAME` varchar(14) DEFAULT NULL,
`LOC` varchar(13) DEFAULT NULL,
PRIMARY KEY (`DEPTNO`));

INSERT INTO `DEPT` (`DEPTNO`,`DNAME`,`LOC`)
VALUES(10, 'ACCOUNTING', 'NEW YORK'),
	  (20, 'RESEARCH', 'DALLAS'),
      (30, 'SALES', 'CHICAGO'),
      (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE IF NOT EXISTS `EMP` ( 
`EMPNO` int(11) NOT NULL,
`ENAME` varchar(10) DEFAULT NULL,
`JOB` varchar(9) DEFAULT NULL,
`MGR` int(11) DEFAULT NULL,
`HIREDATE` datetime DEFAULT NULL,
`SAL` double DEFAULT NULL,
`COMM` double DEFAULT NULL,
`DEPTNO` int(11) DEFAULT NULL,
PRIMARY KEY (`EMPNO`),
KEY `PK_EMP` (`DEPTNO`));

INSERT INTO	`EMP` ( `EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`)
VALUES 
       (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000, NULL, 10),
       (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450, NULL, 10),
       (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300, NULL, 10),
       (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975, NULL, 20),
       (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19 00:00:00', 3000, NULL, 20),
       (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00', 1100, NULL, 20),
       (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000, NULL, 20),
       (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800, NULL, 20),
       (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850, NULL, 30),
       (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600, 300, 30),
       (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250, 500, 30),
	   (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250, 1400, 30),
       (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500, 0, 30),
       (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950, NULL, 30);
       
       

CREATE TABLE IF NOT EXISTS `SALGRADE` ( 
`GRADE` double DEFAULT NULL,
`LOSAL` double DEFAULT NULL,
`HISAL` double DEFAULT NULL);

INSERT INTO `SALGRADE` (`GRADE`, `LOSAL`, `HISAL`)
VALUES (1, 700, 1200),
       (2, 1201, 1400),
	   (3, 1401, 2000),
       (4, 2001, 3000),
       (5, 3001, 9999);

ALTER TABLE `EMP` ADD CONSTRAINT `PK_EMP` FOREIGN KEY (`DEPTNO`) REFERENCES `DEPT` (`DEPTNO`) ON
DELETE SET NULL ON UPDATE CASCADE;
==================================================================
-- 모든 사원 정보 검색
SELECT * 
FROM emp;
-- emp라는 테이블에서 모든 것을 가져와서 조회
-- 테이블에서 레코드를 조회하기 위해 사용
-- 조회 시 컬럼 이름이나 표현식을 조회할 수 있고 별칭(AS) 사용이 가능하다.
-- *는 모든 속성을 조회한다.
-- WHERE 조건식을 이용하여 원하는 레코드를 조회할 수 있다.

-- 사원이 근무하는 부서번호
SELECT deptno
FROM emp;
-- emp라는 테이블에서 부서 번호만을 가져와서 조회


-- 사원이 근무하는 부서번호 (중복제거)
SELECT DISTINCT deptno AS "부서번호"
FROM emp;
-- DISTINCT를 통해 중복을 제거해줄 수 있고 AS를 써서 원래의 collmn명인 deptno에서 ""안에 있는 값으로
-- 바꿔줄 수 있다.
-- 만약 부서번호를 쓰는 사람이 없다면 해당하는 부서번호는 출력되지 않는다.

-- 사원의 이름, 부서번호, 업무 조회
SELECT ename, deptno, job
FROM emp;

-- 사원의 이름, 사번, 급여*12 (연봉), 업무 조회
SELECT ename 이름, empno "사번", sal*12 AS 연봉, job AS "업 무"  
FROM emp;
-- 사칙연산 사용 가능
-- collmn값 뒤에 AS를 쓰지 않고 바로 별칭을 입력해도 된다.

-- 사원의 이름, 사번, 커미션, 급여, 커미션 포함 급여 조회
SELECT ename 이름, empno AS "사번", comm 커미션, 
	   sal AS 급여, sal + comm AS "커미션 포함급여", 
       sal + IFNULL(comm, 0) AS "커미션 포함급여2"
FROM emp;
-- NULL값은 계산 불가       
-- IFNULL(a,b) : a값이 null이면 b값으로 대체, a값이 null이 아니라면 그냥 a값 리턴


-- CASE FUNCTION 써보자
SELECT empno, ename, sal,
	CASE WHEN sal >= 5000 THEN "고액연봉"
		 WHEN sal >= 2000 THEN "평균연봉"
         ELSE "저액연봉"
	END AS "연봉등급"
FROM emp;
-- CASE문은 조건을 통과하고 첫 번째 조건이 충족될 때 값을 반환한다.
-- 조건이 충족되지 않으면 ELSE절의 값을 반환한다.

-- 부서 번호가 30인 사원중 급여가 1500 이상인 사원의 이름, 급여, 부서번호 조회
SELECT ename, sal, deptno
FROM emp
WHERE deptno = 30 AND sal >= 1500;
-- WHERE절은 조건에 맞는 레코드를 조회하기 위해서 사용한다.

-- 부서번호가 20 또는 30인 부서에서 근무하는 사원의 사번, 이름, 부서번호 조회
SELECT empno, ename, deptno
  FROM emp
 WHERE deptno = 30
    OR deptno = 20;

-- 부서번호가 20,30이 아닌 부서에서 근무하는 사원의 사번, 이름, 부서번호 조회
SELECT empno, ename, deptno
FROM emp
WHERE deptno != 30 AND deptno <> 20;

SELECT empno, ename, deptno
FROM emp
WHERE NOT (deptno = 30 OR deptno = 20);

-- 업무가 MANAGER, ANALYST, PRESIDENT 인 사원의 이름, 사번, 업무조회
SELECT ename, empno, job
FROM emp
WHERE job IN ('MANAGER', 'ANALYST', 'PRESIDENT');
-- IN : 피연산자가 여러 표현 중 하나라도 같다면 TRUE

-- 부서번호가 10, 20이 아닌 사원의 사번, 이름, 부서번호 조회
SELECT empno, ename, deptno
FROM emp
WHERE deptno NOT IN (10, 20);

-- 급여가 2000이상 3000이하 인 사원의 사번, 이름, 급여조회
SELECT empno, ename, sal
FROM emp
WHERE sal BETWEEN 2000 AND 3000; 
-- WHERE sal >= 2000 AND sal <=3000;
-- BETWEEN : 값이 주어진 범위의 범위 안에 있으면 조회
-- 값은 숫자나, 문자, 날짜가 될 수 있다.

-- 입사일이 1981년인 직원의 사번, 이름, 입사일 조회
SELECT empno, ename, hiredate
FROM emp
WHERE hiredate BETWEEN '1981-01-01' AND '1981-12-31';

-- 커미션인 NULL 인 사원의 사번, 이름, 커미션 조회
SELECT empno, ename, comm
FROM emp
-- WHERE comm = NULL;
-- 이와 같이 =을 쓰면 안됨.
WHERE comm IS NULL;
-- NULL 비교 : IS NULL, IS NOT NULL
-- 값이 NULL인지 NULL이 아닌지 검사하기 위해서 사용한다.

-- 커미션 NULL이 아닌 사원의 사번, 이름, 업무, 커미션 조회
SELECT empno, ename, comm
FROM emp
WHERE comm IS NOT NULL;

-- 이름이 M으로 시작하는 사원의 사번, 이름 조회
SELECT empno, ename
FROM emp
WHERE ename LIKE 'M%';
-- LIKE : WHERE 절에서 칼럼의 값이 특정 패턴을 가지는 검사하기 위해 사용
-- 와일드 카드(%,_)를 이용해 패턴을 표현한다.
-- % : 0개 이상의 문자를 의미
-- _ : 문자 하나를 의미

-- 이름에 E가 포함된 사원의 사번 이름 조회
SELECT empno, ename
FROM emp
WHERE ename LIKE '%E%';

-- 이름의 세번째 알파벳이 'A'인 사원의 사번, 이름 조회
SELECT empno, ename
FROM emp
WHERE ename LIKE '__A%';

-- 모든 직원의 모든 정보를 이름을 기준으로 내림차순 정룔
SELECT *
FROM emp
ORDER BY ename DESC;
-- 조회 결과를 오름차순(ASC) 또는 내림차순(DESC)으로 정렬할 때 사용한다.(default : ASC)
-- 정렬 기준(칼럼)을 지정할 수 있다.

-- 모든 사원의 사번 이름, 급여를 조회 (급여 내림차순)
SELECT empno, ename, sal
FROM emp
ORDER BY sal DESC;

-- 20, 30번 부서에 근무하는 사원의 사번, 이름, 부서번호, 급여 조회 (부서별 오름차순, 급여순 내림차순)
SELECT empno, ename, deptno, sal
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, sal DESC;

데이터 정의 언어

• 데이터 베이스 객체(tabe, view, user, index 등)의 구조를 정의

-- 기본값 확인
SHOW VARIABLES LIKE 'c%';
-- 사용하는 CHARSET 확인
SHOW CHARACTER SET;

-- 데이터 베이스 생성
CREATE DATABASE ssafy;
-- CREATE DATABASE 명령문은 새 데이터 베이스를 생성하는데 사용된다.
-- 데이터 베이스는 여러 테이블을 포함하고 있다.
-- 데이터 베이스 생성 시 관리자 권한으로 생성해야 한다.
-- 데이터 베이스 생성 후, 다음 명령어를 이용해서 데이터 베이스의 목록을 확인할 수 있다.
  
-- 데이버 베이스 목록 조회 
SHOW DATABASES;

-- 데이터 베이스 수정
ALTER DATABASE ssafy
DEFAULT CHARACTER SET utf8mb4 collate utf8mb4_general_ci;
-- utf8mb3 : 다국어 처리
-- utf8mb4 : 이모지 처리

-- 데이터 베이스 삭제
DROP DATABASE ssafy;
-- 데이터베이스의 모든 테이블을 삭제하고 데이터베이스를 삭제
DROP DATABASE IF EXISTS ssafy;
-- 데이터베이스가 없을 시 나올 수 있는 에러를 방지
-- 삭제 시, DROP DATABASE 권한 필요
-- DROP SCHEMA는 DROP DATABASE와 동의어

-- 데이터 베이스 사용
USE ssafy;

테이블 생성하기

-- ssafy 데이터 베이스 생성 및 사용
CREATE DATABASE IF NOT EXISTS SSAFY;
USE SSAfY;

-- 테이블 생성
-- 테이블 스키마 : 스키마란 테이블에 저장될 데이터의 구조와 형식(예:교육생의 정보를 저장하기 위한 테이블)
CREATE TABLE ssafy_user (
    usER_NUM INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    USER_ID VARCHAR(20) NOT NULL,
    user_nAME VARCHAR(20) NOT NULL,
    user_pASSWORD VARCHAR(20) NOT NULL,
    user_eMAIL VARCHAR(30),
    signup_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 테이블 정보 확인
DESC SSAFY_USEr;
-- DESCRIBE 또는 DESC 명령어를 이용하여 생성된 테이블 스키마 확인

 

DCL(Data Control Language)

데이터 제어 언어

• Database, Table 접근 권한이나 CRUD권한 정의
• 특정 사용자에게 테이블의 검색권한 부여 금지

 

TCL(Transaction Control Language)

트랙잭션 제어 언어

• 트랜잭션 단위로 실행한 명령문을 적용(Commint)하거나 취소(Rollback)

ySQL Functions(내장 함수)

-- 2의 3제곱
SELECT POW(2, 3) AS "2^3"
FROM dual;

-- 8 나누기 3의 나머지
SELECT MOD(8, 3) AS "8을 3으로 나눈 나머지";

-- 최대값, 최솟값
SELECT greatest(8,17,86,17,100,77,999,2,13,31,97), least(8,17,86,17,100,77,999,2,13,31,97);

-- 반올림
SELECT round(1526.159), round(1526.159, 0), round(1526.159, 1), round(1526.159,2), round(1526.159, 3);

-- 아스키 코드값 얻기
SELECT ascii('0'), ascii('A'), ascii('a'); 

-- concat 메서드를 써보자.
SELECT CONCAT('PRESIDENT의 이름은 ', ename, ' 입니다.') AS 소개
FROM emp
WHERE job = 'PRESIDENT'; 


-- 이름의 길이가 5인 직원의 이름을 조회
SELECT ename
FROM emp
WHERE length(ename) = 5;

-- 김싸피
SELECT length('김싸피'), char_length('김싸피');

SELECT length('🍕'), char_length('🍕');

-- 문자열 변경
SELECT replace('Hello abc abc', 'abc', 'ssafy');

-- 문자열 인덱스
SELECT instr('Hello SSAFY', 'SSAFY');

-- 모든 직원의 이름 3자리조회
SELECT substr(ename, 1, 3)
FROM emp;

-- LPAD RPAD
SELECT LPAD('SSAFY',10,'*'), RPAD('SSAFY',10,'*');

-- REVERSE
SELECT REVERSE('HELLO SSAFY!');
SELECT REVERSE('우영우역삼역기러기인도인토마토별똥별스위스');

-- 2초 더하기
SELECT ADDTIME("2022-02-13 17:29:21", "00:02:00");

-- 날짜차이
SELECT datediff("2008-02-18", "2006-02-21");

-- 오늘은?
SELECT now(), day(now()), month(now()), year(now()), yearweek(now());

-- 모든 사원에 대하여 사원수, 급여총액, 평균급여, 최고급여, 최저급여 조회
SELECT COUNT(*) 사원수, SUM(sal), AVG(sal), MAX(sal), MIN(sal)
-- 모든 사원의 수, sal의 전체 합계, sal의 평균, sal의 최고값, sal의 최소값
FROM emp;

-- 모든 사원에 대하여 부서, 사원수, 급여총액, 평균급여, 최고급여, 최저급여를 부서별로 조회하고, 소수점 둘쨰자리 반올림
SELECT deptno 부서, COUNT(*) 사원수, SUM(sal) 급여총액, ROUND(AVG(sal), 2) 평균급여, MAX(sal) 최고급여, MIN(sal) 최저급여
FROM emp
GROUP BY deptno;
-- 일단 부서로 한 번 묶고 나서 조회

-- 모든 사원에 대하여 부서, 업무, 사원수, 급여총액, 평균급여, 최고급여, 최저급여를 부서별, 직급별로 조회
SELECT deptno 부서, job 업무, COUNT(*) 사원수, SUM(sal) 급여총액, ROUND(AVG(sal), 2) 평균급여, MAX(sal) 최고급여, MIN(sal) 최저급여
FROM emp
GROUP BY deptno, job
ORDER BY deptno;


-- 모든 사원에 대하여 이름, 부서, 업무, 사원수, 급여총액, 평균급여, 최고급여, 최저급여를 부서별, 직급별로 조회
SELECT ename 이름, deptno 부서,job 업무, COUNT(*) 사원수, SUM(sal) 급여총액, 
       ROUND(AVG(sal),2) 평균급여, MAX(sal) 최고급여, MIN(sal) 최저급여
FROM emp
GROUP BY deptno,job;


-- 급여(커미션포함) 평균이 2000이상인 부서번호, 부서별 사원수, 평균급여(커미션포함) 조회 
SELECT deptno, COUNT(*) 사원수, ROUND(AVG(sal+IFNULL(comm, 0)),2) AS "평균급여(커미션포함)"
FROM emp
GROUP BY deptno
HAVING AVG(sal + IFNULL(comm, 0)) >= 2000;
-- 집계 함수의 결과 내에서 조건문에 맞는 레코드를 조회

Transaction

-- 오토커밋 나 안할래 
set autocommit = 0;

-- 트랜잭션 : 커밋하거나 롤백할 수 있는 가장 작은 작업 단위
USE ssafy;
CREATE TABLE test_table(val VARCHAR(20));

-- 커밋(COMMIT) : 트랜잭션을 종료하여 변경사항에 대해서 영구적으로 저장하는 SQL
-- 롤백(ROLLBACK) : 트랜잭션에 의해 수행된 모든 변경사항을 실행 취소하는 SQL

-- 롤백
START TRANSACTION;
INSERT INTO test_table VALUES ('A');
INSERT INTO test_table VALUES ('B');
INSERT INTO test_table VALUES ('C');
INSERT INTO test_table VALUES ('D');
ROLLBACK;
SELECT * FROM test_table;

-- 커밋
START TRANSACTION;
INSERT INTO test_table VALUES ('S');
INSERT INTO test_table VALUES ('S');
INSERT INTO test_table VALUES ('A');
INSERT INTO test_table VALUES ('F');
INSERT INTO test_table VALUES ('Y');
COMMIT;
SELECT * FROM test_table;

문법 순서 : SELECT->FROM->WHERE->GROUP BY->ORDER BY

실행 순서 : FROM->ON->JOIN->WHERE->GROUP BY->HAVING->DISTINCT->ORDER BY