DBMS & SQL

23 Apr 2021

Reading time ~3 minutes

데이터베이스와 데이터베이스 관리 시스템

데이터베이스 : 도서관의 책들
데이터베이스 관리시스템 : 도서관 관리자

데이터베이스 기본 개념

데이터의 집합 (a Set of Data)
여러 응용 시스템(프로그램)들의 통합된 정보들을 저장하여 운영할 수 있는 공용(share) 데이터의 집합
효율적으로 저장, 검색, 갱신할 수 있도록 데이터 집합들끼리 연관시키고 조직화되어야 한다.

DBMS

데이터베이스를 관리하는 소프트웨어
여러 응용 소프트웨어(프로그램) 또는 시스템이 동시에 데이터베이스에 접근하여 사용할 수 있게 한다
필수 3기능
1. 정의기능 : 데이터 베이스의 논리적, 물리적 구조를 정의
2. 조작기능 : 데이터를 검색, 삭제, 갱신, 삽입, 삭제하는 기능
3. 제어기능 : 데이터베이스의 내용 정확성과 안전성을 유지하도록 제어하는 기능
Oracle, SQL Server, MySQL, DB2 등의 상용 또는 공개 DBMS가 있다.

SQL(Structured Query Language)

SQL은 데이터를 보다 쉽게 검색하고 추가, 삭제, 수정 같은 조작을 할 수 있도록 고안된 컴퓨터 언어
관계형 데이터베이스에서 데이터를 조작하고 쿼리하는 표준 수단
DML (Data Manipulation Language): 데이터를 조작하기 위해 사용 INSERT, UPDATE, DELETE, SELECT 등이 여기에 해당
DDL (Data Definition Language): 데이터베이스의 스키마를 정의하거나 조작하기 위해 사용 CREATE, DROP, ALTER 등이 여기에 해당
DCL (Data Control Language) : 데이터를 제어하는 언어 권한을 관리하고, 테이터의 보안, 무결성 등을 정의 GRANT, REVOKE 등이 여기에 해당

MySQL DBMS 권한부여

create user 'connectuser'@'%' identified by 'connect123!@#';
grant all privileges on connectdb.* to 'connectuser'@'%';

create user 'connectuser'@'localhost' identified by 'connect123!@#';
grant all privileges on connectdb.* to 'connectuser'@'localhost';

flush privileges;

Database에 권하 부여한 사람이 접속하기

mysql -h127.0.0.1 -uconnectuser -p connectdb

table의 구성요소

table 조회 SQL 명령

desc mytable(테이블이름);

컬럼에 Alias(별칭)부여하기

select empno as 사번, name as 이름, job as 직업 from employee;

컬럼의 합성(Concatenation)

문자열 결합함수 concat 사용

SELECT concat( empno, '-', deptno) AS '사번-부서번호' 
FROM employee;

중복행의 제거

distinct를 써서 같은 record 중복 제거
```
select distinct deptno from employee;
```

정렬하기

예제 : employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력, 단, 이름을 기준으로 오름차순 정렬합니다.

select empno, name, job from employee order by name;

select empno as 사번, name as 이름, job as 직업 from employee order by 이름;

Where절

예제 : employee 테이블에서 고용일(hiredate)이 1981년 이전의 사원이름과 고용일을 출력
```
select name, hiredate from employee where hiredate < '1981-01-01';
```
예제 : employee 테이블에서 부서번호가 30인 사원이름과 부서번호를 출력하시오.
```
select name, deptno from employee where deptno = 30;
```
예제 : employee 테이블에서 부서번호가 10또는 30인 사원이름과 부서번호를 출력
```
select name, deptno from employee where deptno in (10, 30);
```

Like 키워드

와일드 카드를 사용하여 특정 문자를 포함한 값에 대한 조건을 처리
% 는 0에서부터 여러 개의 문자열을 나타냄
_ 는 단 하나의 문자를 나타내는 와일드 카드
예제 : employee 테이블에서 이름에 ‘A’로 시작하는 사원의 이름(name)과 직업(job)을 출력
```
select name, job from employee where name like 'A%';
```
예제 : employee 테이블에서 이름에 ‘A’가 포함된 사원의 이름(name)과 직업(job)을 출력

select name, job from employee where name like '%A%';

예제 : employee 테이블에서 이름에 두번째로 ‘A’가 포함된 사원의 이름(name)과 직업(job)을 출력

select name, job from employee where name like '_A%';

함수의 사용

SELECT UPPER('SEoul'), UCASE('seOUL');

+—————–+—————–+ | UPPER(‘SEoul’) | UCASE(‘seOUL’) | +—————–+—————–+ | SEOUL | SEOUL | +—————–+—————–+

SELECT SUBSTRING('Happy Day',3,2);

+—————–+—————–+ | SUBSTRING(‘Happy Day’,3,2) | +—————–+—————–+ | pp | +—————–+—————–+

hi를 출력할건데 총 5자리로 출력하고 싶다 => 만약 5자리가 안되면 왼쪽으로 ?를 채운다
```
SELECT LPAD('hi',5,'?'),LPAD('joe',7,'*');
```
+——————+——————-+ | LPAD(‘hi’,5,’?’) | LPAD(‘joe’,7,’*’) | +——————+——————-+ | ???hi | **joe | +——————+——————-+

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의 차이 개월을 반환합니다.