부스트코스 강의를 듣고 정리한 내용.
DBMS
DB와 DBMS
어린이가 가지고 있는 책(정보)들을 DB라 한다면, 그 책을 관리해주는 엄마는 DBMS라 할 수 있다.
DB의 기본 개념 (정의)
- 데이터의 집합 (a set of data)
- 여러 응용 시스템(프로그램)들의 통합된 정보들을 저장하여 운영할 수 있는 공용(share) 데이터의 집합
- 효율적으로 저장, 검색, 갱신할 수 있도록 데이터 집합들끼리 연관시키고 조직화되어야 한다.
DB의 특성
- 실시간 접근성 (Real-time Accessibility) 사용자의 요구를 즉시 처리할 수 있다.
- 계속적인 변화 (Continuous Evolution) 정확한 값을 유지하려고 삽입·삭제·수정 작업 등을 이용해 데이터를 지속적으로 갱신할 수 있다.
- 동시 공유성(Concurrent Sharing) 사용자마다 서로 다른 목적으로 사용하므로 동시에 여러 사람이 동일한 데이터에 접근하고 이용할 수 있다.
- 내용 참조(Content Reference) 저장한 데이터 레코드의 위치나 주소가 아닌 사용자가 요구하는 데이터의 내용, 즉 데이터 값에 따라 참조할 수 있어야 한다.
Database Management System (DBMS)
- DB를 관리하는 SW
- 여러 응용 SW(프로그램) 또는 시스템이 동시에 DB에 접근하여 사용할 수 있게 함
- 필수 3 기능
- 정의 기능: DB의 논리적, 물리적 구조를 정의
- 조작 기능: 데이터를 검색, 삭제, 갱신, 삽입, 삭제
- 제어 기능: 데이터베이스의 내용 정확성과 안전성을 유지하도록 제어
-
- Oracle, SQL Server, MySQL, DB2 등의 상용 또는 공개 DBMS가 있다.
DBMS의 장단점
- 장점
- 데이터 중복이 최소화
- 데이터의 일관성 및 무결성 유지
- 데이터 보안 보장
- 단점
- 비싼 운영비
- 백업 및 복구에 대한 관리 복잡
- 부분적 DB 손실이 전체 시스템을 정지
Structured Query Language (SQL)
- 데이터를 보다 쉽게 검색하고 추가, 삭제, 수정 같은 조작을 할 수 있도록 고안된 언어
- RDB에서 데이터를 조작하고 쿼리하는 표준 수단
- DML (Data Manipulation Language): 데이터를 조작하기 위해 사용. INSERT, UPDATE, DELETE, SELECT 등이 여기에 해당된다.
- DDL (Data Definition Language): DB의 스키마를 정의하거나 조작하기 위해 사용. CREATE, DROP, ALTER 등이 여기에 해당된다.
- DCL (Data Control Language): 데이터를 제어. 권한을 관리하고, 테이터의 보안, 무결성 등을 정의한다. GRANT, REVOKE 등이 여기에 해당된다.
예제
DB 생성하기
- 콘솔로 MySQL 관리자 계정인 root로 DBMS에 접속
mysql -uroot -p - DB 생성
mysql> CREATE databae DB이름;
DB 사용자 생성과 권한 주기
DB를 생성했다면 해당 DB를 사용하는 계정을 생성하고 해당 계정이 DB를 이용할 수 있는 권한을 줘야 한다.
GRANT ALL PRIVILEGES ON db이름.* to 계정이름@'%' IDENTIFIED BY ＇암호’;
GRANT ALL PRIVILEGES ON db이름.* to 계정이름@'localhost' IDENTIFIED BY '암호';
FLUSH PRIVILEGES;
DB 이름 뒤의 *는 모든 한을, @'%'는 어떤 클라이언트에서든 접근 가능하다는 의미이다. flush privileges 명령을 실행해줘야 DBMS에 적용이 되지 잊지 말자.
그런데 MySQL 버전 8.0 이상부터는
GRANT 명령어를 사용해 implicit하게 사용자를 생성할 수 없다. 그래서 CREATE USER를 사용해 사용자를 따로 생성한 다음 GRANT 명령어로 권한을 줘야 한다.
CREATE USER 'root' @'%' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
그래서 이렇게 작성해줘야 한다.
CREATE USER 'connectuser'@'%' identified by 'connect123!@#';
GRANT ALL PRIVILEGES ON connectdb.* to 'connectuser'@'%';
FLUSH PRIVILEGES;
생성한 DB에 접속하기
mysql -h호스트명 -uDB계정명 -p DB이름
MySQL 연결 끊기
QUIT 또는 EXIT 입력
QUIT
exit
버전 확인 & 현재 날짜 구하기
SELECT VERSION(), CURRENT_DATE;
Miscellaneous
- 키워드는 대소문자 구분 안 함
- 쿼리를 이용해 계산식 결과 구할 수 있음
- 여러 문장을 한 줄에 연속으로 붙여 실행 가능. 이때 세미콜론은 붙여줘야 함
- 하나의 SQL은 여러 줄로 입력 가능
- SQL을 입력하는 도중에
\c를 누르면 취소 가능
Table
테이블이란 데이터를 저장하는 공간을 의미한다. 엑셀과 비슷하지만 DB를 생성해도 테이블이 자동으로 만들어지는 것은 아니다.
테이블의 구성 요소
- 테이블 : RDBMS의 기본적 저장구조 한 개 이상의 column과 0개 이상의 row로 구성됨
- 열(Column) : 테이블 상에서의 단일 종류의 데이터를 나타냄. 특정 데이터 타입 및 크기를 가진다.
- 행(Row) : Column들의 값의 조합. 레코드라고 불림. 기본키(PK)에 의해 구분되는데, PK는 중복을 허용하지 않으며 없어서는 안 됨.
- Field : Row와 Column의 교차점. Field는 데이터를 포함할 수 있고 없을 때는 NULL 값을 가진다.
현재 DB에 존재하는 테이블 목록 확인
DB 선택 후 DB의 전체 테이블 목록 출력
SHOW TABLES;
“empty set” 은 데이터베이스에 어떤 테이블도 아직 생성되지 않았다는 것을 의미한다.
Data Manipulation Language (DML)
모두 동사로 시작.
- 데이터를 조작하기 위해 사용.
- INSERT, UPDATE, DELETE, SELECT 등
SELECT
SELECT(DISTINCT) 칼럼명(ALIAS)
- SELECT: 검색하고자 하는 데이터(칼럼) 나열
- DISTINCT: 중복행 제거
- ALIAS: 나타날 컬럼에 대한 이름 부여
- FROM: 선택한 컬럼이 있는 테이블 명시
전체 데이터 검색
SELECT 뒤에 *
SELECT * FROM DEPARTMENT;
특정 컬럼 검색
SELECT 뒤의 컬럼을 .로 구분해 나열
SELECT empno, name, job FROM employee;
컬럼에 alias 부여
컬럼에 별칭을 부여하여 나타내는 컬럼의 HEADING을 변경
SELECT empno AS 사번, name AS 이름, job AS 직업 FROM employee;
컬럼 concatenation
문자열 결합 함수 concat 사용
SELECT concat( empno, '-', deptno) AS '사번-부서번호' FROM employee;
중복행 제거
중복되는 행이 출력되는 경우, DISTINCT 키워드로 중복행 제거
SELECT DISTINCT deptno FROM employee;
정렬
SELECT(DISTINCT) 칼럼명(ALIAS)
FROM 테이블명
ORDER BY 칼럼이나표현식(ASC 또는 DESC);
- ASC: 오름차순 정렬, 기본값
- DESC: 내림차순
SELECT empno, name, job FROM employee ORDER BY name;
SELECT empno AS 사번, name AS 이름, job AS 직업 FROM employee ORDER BY 이름;
SELECT empno, name, job FROM employee ORDER BY name DESC;
특정 행 검색 - WHERE
조건식은 컬럼 이름이나 표현식의 상수, 연산자로 구성된다.
SELECT(DISTINCT) 칼럼명(ALIAS)
FROM 테이블명
WHERE 조건식
ORDER BY 칼럼이나표현식(ASC 또는 DESC);
- 산술 비교 연산자
SELECT name, hiredate FROM employee WHERE hiredate < '1981-01-01'; - 논리 연산자
SELECT name, deptno FROM employee WHERE deptno = 30; - IN 키워드
SELECT name, deptno FROM employee WHERE deptno in (10, 30); - LIKE 키워드
- 와일드 카드를 사용해 특정 문자를 포함한 값에 대한 조건 처리
%는 0에서부터 여러 개의 문자열을 나타냄_는 단 하나의 문자를 나타내는 와일드 카드SELECT name, job FROM employee WHERE name LIKE '%A%';기타 함수
- UCASE, UPPER
SELECT UPPER('SEoul'), UCASE('seOUL'); - LCASE, LOWER
SELECT LOWER('SEoul'), LCASE('seOUL'); - SUBSTRING
SELECT SUBSTRING('Happy Day',3,2); - LPAD, RPAD
SELECT LPAD('hi',5,'?'),LPAD('joe',7,'*'); - TRIM, LTRIM, RTRIM
SELECT LTRIM(' hello '), RTRIM(' hello '); SELECT TRIM(' hi '),TRIM(BOTH 'x' FROM 'xxxhixxx'); - ABS(x)
SELECT ABS(2), ABS(-2); - MOD(n, m),
%: n을 m으로 나눈 나머지 값을 출력SELECT MOD(234,10), 253 % 7, MOD(29,9); - FLOOR(x) : x보다 크지 않은 가장 큰 정수 리턴. BIGINT로 자동 변환됨
- CEILING(x) : x보다 작지 않은 가장 작은 정수리턴
- ROUND(x) : x에 가장 근접한 정수 리턴
- POW(x,y) POWER(x,y) : x의 y 제곱 리턴
- GREATEST(x,y,…) : 가장 큰 값 리턴
- LEAST(x,y,…) : 가장 작은 값 리턴
- CURDATE(),CURRENT_DATE : 오늘 날짜를 YYYY-MM-DD나 YYYYMMDD 형식으로 리턴
- CURTIME(), CURRENT_TIME : 현재 시각을 HH:MM:SS나 HHMMSS 형식으로 리턴
- NOW(), SYSDATE() , CURRENT_TIMESTAMP : 오늘 현시각을 YYYY-MM-DD HH:MM:SS나 YYYYMMDDHHMMSS 형식으로 리턴
- DATE_FORMAT(date,format) : 입력된 date를 format 형식으로 리턴
- PERIOD_DIFF(p1,p2) : YYMM이나 YYYYMM으로 표기되는 p1과 p2의 차이 개월 리턴
CAST 형 변환
CAST 함수는 type을 변경(지정)하는데 유용하다.
- CAST 함수의 사용법:
- CAST( expression AS type)
- COVNERT(expression, type)
- MySQL 타입: BINARY, CHAR, DATE, DATETIME, SIGNED(INTEGER), TIME, UNSIGNED (INTEGER)
SELECT CAST(NOW() AS DATE);
SELECT CAST(1-2 AS UNSIGNED);`
그룹함수
SELECT AVG(salary) , SUM(salary)
FROM employee
WHERE deptno = 30;
GROUPBY
SELECT deptno, AVG(salary) , SUM(salary)
FROM employee
GROUP BY deptno;
INSERT
INSERT INTO 테이블명(필드1, 필드2, 필드3, 필드4, … )
VALUES ( 필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, … )
- 필드명을 지정해주는 방식은 디폴트 값이 세팅되는 필드는 생력할 수 있다.
- 필드명을 지정해주는 방식은 추 후, 필드가 추가/변경/수정 되는 변경에 유연하게 대처 가능
- 필드명을 생략했을 경우에는 모든 필드 값을 반드시 입력해야 함.
UPDATE
UPDATE 테이블명
SET 필드1=필드1의값, 필드2=필드2의값, 필드3=필드3의값, …
WHERE 조건식
- 조건식을 통해 특정 row만 변경
- 조건식을 주지 않으면 전체 row가 영향을 받음
DELETE
DELETE FROM ROLE WHERE role_id = 200;
조건식을 주지 않으면 모든 데이터가 삭제되니 주의할 것
Data Definition Language (DDL)
MySQL 데이터 타입
- TINYINT(M)
- 부호 있는 수: -128부터 127까지
- 부호 없는 수: 0부터 255까지
- 1바이트
- SMALLINT(M)
- 부호 있는 수: -32768부터 32767까지
- 부호 없는 수: 0부터 65535까지
- 2바이트
- MEDIUMINT(M)
- 부호 있는 수: -8388608부터 8388607까지
- 부호 없는 수: 0부터 16777215
- 3바이트
- INT(M) OR INTEGER(M)
- 부호 있는 수: -2147483648부터 2147483647까지
- 부호 없는 수: 0부터 4294967295까지
- BIGINT(M)
- 부호 있는 수: -9223372036854775808부터 9223372036854775807까지
- 부호 없는 수: 0부터 18446744073709551615까지
- 8바이트
- FLOAT(M, D): 부동소수점을 나타냄. 언제나 부호 있는 수. 4바이트
- DOUBLE(M, D): 2배 정밀도를 가진 부동 소수점
- DATE
- 날짜를 표현하는 타입
- 2021-08-31
- 3바이트
- DATETIME
- 날짜와 시간을 같이 나타내는 타입
- 2021-08-31 00:00:00
- 8바이트
- TIMESTAMP
- 1970-01-01 00:00:00부터 2037년까지 나타낼 수 있음
- 4바이트
- TIME
- 시간을 나타냄
- -839:59:59부터 839:59:59까지 나타낼 수 있음
- YEAR
- 년도를 나타냄
- 1901년부터 2155년, 0000년 나타낼 수 있음
- CHAR(M)
- 고정 길이를 갖는 문자열 저장
- M은 1부터 255까지
- VARCHAR(M): 가변 길이 문자열 저장
- TINYBLOB, TINYTEXT: 255개의 문자 저장
- BLOB, TEXT: 63535개의 문자 저장
- MEDIUMBLOB, MEDIUMTEXT: 16777215개의 문자 저장
- LONGBLOB, LONGTEXT: 4기가개의 문자 저장
테이블 생성
CREATE TABLE 테이블명(
필드명1 타입 [NULL | NOT NULL][DEFAULT ][AUTO_INCREMENT],
필드명2 타입 [NULL | NOT NULL][DEFAULT ][AUTO_INCREMENT],
필드명3 타입 [NULL | NOT NULL][DEFAULT ][AUTO_INCREMENT],
...........
PRIMARY KEY(필드명)
);
- 데이터 형 외에도 속성값의 빈 값 허용 여부는 NULL 또는 NOT NULL로 설정
- DEFAULT 키워드와 함께 입력하지 않았을 때의 초기값 지정
- 입력하지 않고 자동으로 1씩 증가하는 번호를 위한 AUTO_INCREMENT
테이블 수정 (컬럼 추가/삭제/수정)
ALTER TABLE 테이블명
ADD 필드명 타입 [NULL | NOT NULL][DEFAULT ][AUTO_INCREMENT];
ALTER TABLE 테이블명
DROP 필드명;
ALTER TABLE 테이블명
CHANGE 필드명 새필드명 타입 [NULL | NOT NULL][DEFAULT ][AUTO_INCREMENT];
테이블 이름 변경
alter table 테이블명 rename 변경이름
테이블 삭제
DROP TABLE 테이블이름;