23.3.28 : Database 기초

이준영·2023년 3월 28일

데이터 베이스

데이터의 집합
여러 명의 사용자, 응용 프로그램이 공유
동시 접근 가능 ( + 원격)

파일의 부족한 점을 보충하기 위함 (파일의 확장 버전)
과거에 수기로 기록함 -----지속적인 프로그램화(ERP(전사적 자원 관리))----> 후에 대량의 데이터를 보다 효율적으로 관리, 운영하기 위해 DBMS(DataBase Management System) 시스템 발전

SQL - Structured Query Language

구조화된 질의 언어
dbms에 데이터 구축하고 관리, 활용하기 위해 사용되는 언어
다른 시스템으로 이식성이 좋다

F는 파일
DBMS부분이 서버(24시간 가동) - 연결 부분이 네트워크(네트워크 통해 SQL 명령어 주는 것) - 사용자나 응용프로그램 부분이 클라이언트(제작하거나 제공하거나 구매하는 행위 가능)

저장용량

대용량 (은행권이나 공공회사에서 많이 사용)
DB2 - IBM에서 제작
Oracle - Oracle에서 제작
범용
MySQL -Oracle에서 제작 (유료)
Mariadb(MYSQL의 무료버전, 90% 유사) - Mariadb에서 제작
SQLServer - MS에서제작
소형
Access - MS에서 제작
SQLite - SQLite에서 제작 (android에 주로 사용)

데이터베이스 연관된 개발자

시스템 관리자(SA)
설치
외부 접근 보안
데이터 베이스 관리자(DBA)
모니터링 - 튜닝
백업, 복원(restore)
내부 접근 보안

자격증(SQLD/SQLP) + oracle(OCP(초보자도 따기 쉬움), OCM)

개발자
데이터베이스와 프로그램 연동 => SQL

SQL 종류

ANSI(표준 SQL) + 확장 SQL

DML(Data Manipulation Langauage) : 데이터 조작 (데이터 운영)
select(DQL) - 가장 중요( 이것을 위해 데이터베이스 구축했다고 봐도 됨)
insert
update
delete
DDL(Data Definition Langauage) : 데이터 정의
데이터를 기록할 장소에 대한 정의
데이터베이스 / 테이블
컬럼에 대한 설정 / constraint 설정, view, index

create
alter
drop
rename
truncate

DCL(Data Control Langauage) : 접근 권한
grant
revoke
TCL(Transaction Control Langauage) : 트랜잭션 (마리아db에서 잘 안 씀)
commit
rollback
savepoint

Mariadb 사용법

위 명령어 치고 비밀번호 입력하고 연결

-u 사용자
root 사용자 아이디
-p 암호 입력 명령어

다른 아이피로도 접속 가능

데이터베이스 - 디렉토리
(디렉토리 안에) 테이블(실제 데이터 저장되는 곳) : 행 / 열(=excel 확장판)

show databases : 데이터 베이스 목록 보기

use 데이터베이스명 : 목록 중 데이터베이스명 선택하기

show tables - 테이블 목록

describe 테이블명 - 구조 보여줌(컬럼 정의)

보통 많이써서 desc 유저명 으로 줄여 쓴다.

인사(사원) 관리 프로그램 ( mariadb 프롬포트에서 해보기)

오늘 모든 예제들은 이 파일을 이용하여 출력한다

-- 데이터베이스 생성

create database sample;
use sample;

-- 테이블 생성

create table dept ( 
	deptno      int(2) primary key,
	dname       varchar(14),
	loc         varchar(13)
);

create table emp ( 
	empno       int(4) primary key,
	ename       varchar(10),
	job         varchar(9),
	mgr         int(4),
	hiredate    date,
	sal         numeric(7,2),
	comm        numeric(7,2),
	deptno      int(2)
);

create table salgrade (
	grade       int(1),
	losal       numeric(7,2),
	hisal       numeric(7,2)
);

-- 데이터 입력

insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'SALES', 'CHICAGO');
insert into dept values(40, 'OPERATIONS', 'BOSTON');

insert into emp values( 7839, 'KING', 'PRESIDENT', null, STR_TO_DATE ('17-11-2011','%d-%m-%Y'), 5000, null, 10);
insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('1-5-2011','%d-%m-%Y'), 2850, null, 30);
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('9-6-2011','%d-%m-%Y'), 2450, null, 10);
insert into emp values( 7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('2-4-2011','%d-%m-%Y'), 2975, null, 20);
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, STR_TO_DATE('13-7-2017','%d-%m-%Y') , 3000, null, 20);
insert into emp values( 7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('3-12-2011','%d-%m-%Y'), 3000, null, 20);
insert into emp values( 7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-2010','%d-%m-%Y'), 800, null, 20);
insert into emp values( 7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-2-2011','%d-%m-%Y'), 1600, 300, 30);
insert into emp values( 7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-2-2011','%d-%m-%Y'), 1250, 500, 30);
insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-2011','%d-%m-%Y'), 1250, 1400, 30);
insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('8-9-2011','%d-%m-%Y'), 1500, 0, 30);
insert into emp values( 7876, 'ADAMS', 'CLERK', 7788, STR_TO_DATE('13-7-2017', '%d-%m-%Y'), 1100, null, 20);
insert into emp values( 7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('3-12-2011','%d-%m-%Y'), 950, null, 30);
insert into emp values( 7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-1-2012','%d-%m-%Y'), 1300, null, 10);

insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);

--

create index idx_emp on emp ( deptno );
alter table emp add constraint fk_emp_dept foreign key ( deptno ) references dept( deptno ) on delete no action on update no action;

--

인사(사원)관리

dept	- 부서정보
	deptno	- 부서번호
	dname	- 부서이름
	loc	- 부서위치
emp	- 사원정보
	empno	- 사원번호
	ename	- 사원이름
	job	- 직책
	mgr	- 관리자 사원번호
	hiredate	- 입사일자
	sal	- 급여(월급)
	comm	- 수당
	deptno	- 부서번호
salgrade	- 호봉정보
	grade	- 호봉
	losal	- 급여하한
	hisal	- 급여상한

select

(대소문자 구별 x)
select 컬럼절
from 테이블명;

select * from dept;

(dept(테이블명)에서 전체 컬럼을 다 보여줘) 라는 뜻

위 코드의 dept의 내용

필요한 부분만 발췌하여 보기(*이 아닌 직접 컬럼명 써주기)

원본은 바뀌지 않으나, 순서는 상관없이 치는 순으로 나옴

컬럼의 이름 변경하기(컬럼에 대한 별칭(alias))

줄여서 as로 사용 (select 원래명 as 바꿀명 from 유저명)

한글로도 가능(문자에 공백을 추가하면 에러, 공백 구분 못함)
공백 추가 하려면 ''추가하기

as를 생략시켜도 된다.

연산자

산술연산자 : + - * / %

산술연산에서 null과 연산이 되면 값이 null로 나옴

컬럼에 연산을 하게 되면 전체에 적용된다(집합개념), 컬럼명도 같이 변경도 가능

null로 연산해서 결과 값이 null인 모습, comm은 보너스

비교연산자

true면 1 false면 0으로 나옴

!= 연산자는 <>로도 쓰임

논리연산자

논리연산도 가능( 기존이랑 동일)

특정 내용만 보기 : where절 (조건절)

select 컬럼이름 ...
from 테이블명
where 행제한 조건
컬럼명 비교 / 논리 연산 값

전체에서 deptno = 10 인 것(아닌 것) 가져오기

empno,ename, sal 에서 sal <= 1000 인 것 가져오기

문자열 비교

문자열을 비교 할 때는 구별해주기 위해 반드시 컬럼명에다 ' '추가 (대소문자 안 가림)

문자에서의 > <는 아스키 코드 값 비교이다

테이블 안에서 k보다 큰 아스키 코드의 값들이 출력됨

날짜로도 비교가 가능하다. <, > 를 통해서도 출력 가능

논리 연산자를 이용하여 where 쓰기

급여가 1000이상 2000이하인 사원들 출력하기 (AND 연산자 이용)

(범위형)
입사년도 2011인 사원에 대한 사원번호, 이름, 급여, 입사일자 출력
그냥 2011만 비교하는 것이 아닌 2011-1-1 ~ 2011-12-31 까지를 논리연산으로 비교

(목록형)
직책이 clerk이거나 salesman인 사원에 대한 사원번호, 이름, 직책 출력(OR 연산자 이용하기)

범위형

시작과 끝 = between을 사용하여 가공할 수 있다.

목록형

in 사용

null 조회하기

일반적으로는 null이 조회가 안된다.
is를 사용하면 null 출력된다 (is not 은 null이 아닌 것)

is not

like : 유사검색

1. k로 시작하는 단어 (~%)
2. g로 끝나는 단어 (%~)
3. 중간 어디에 i가 있는 단어 (%~%)

아까 했던 날짜(고용일) like로 출력하기

02월 입사한 사원 뽑기 ( %~% 쓰면 02년이나 02일도 나오니까 - - 표시해서 월이라는 것을 정확하게 표시)

_ 표시 : 한글자

언더바를 이용하여 단어의 위치를 조정? 가능하다.
ex> where ename like '_k%'

distinct : 중복 배제하여 목록화 보여줌

일반적인 사용

distinct를 사용할 때 컬럼값을 여러개 넣으면 해당 컬럼 쌍이 중복인 값만 제거된다.
사진처럼 (30,MANAGER) 와 (20,MANAGER) 은 다른 값으로 본다.

limit : 상위 몇 개 데이터만 출력하는 방법 (중요)

mariadb 전용구문

1.limit 3 -> 0행부터 3개
2. limit 1,3 -> 1행부터 3개

정렬기능

select
from
where
order by 컬럼명(오름차순(asc) - 생략가능 / 내림차순(desc))

오름차순 정렬(생략가능)

내림차순 정렬

여러가지 컬럼으로 정렬하기(여기서는 각각 동일한 deptno에서 또 오름차순으로 아스키 코드 정리가 됨)

정렬할 때 뒤에 인덱스를 넣어도 됨 ( deptno : 1 기준)

이름 바꾸고 넣고 정렬도 가능!!

https://mariadb.com/kb/en/documentation/

여기서 다양한 sql 기능들 볼 수 있음 (자바 api 느낌)

select version(); -> 버전 확인
select database(); -> 사용하고 있는 datebase 확인
use 데이터베이스명 -> 데이터베이스명 사용하겠다.
select user; -> 사용하고 있는 사용자 확인
status; -> 상태값 확인

Numeric : 뉴메릭(수학함수)

select abs(123), abs(-123); -> 절대값
select ceil(1.1) / floor(1.1) / round(1.1) -> 올림 / 내림 / 반올림
select pow(2, 2) -> 제곱(2의 2승)
select 5 % 2, mod(5,2)   --> 둘다 나머지 연산

select truncate(999.999, n) 절삭