SQL(1)-DBMS 개념과 기본 SQL 구문

오늘은 데이터베이스에 대해서 공부하겠습니다 :)

데이터베이스란? 한마디로 정의하자면 '데이터의 집합'이라고 할 수 있습니다.

 

DBMS란?

앞서 말했듯이 데이터베이스를 '데이터의집합'이라고 정의한다면, 이런 데이터베이스를 관리하고 운영하는 소프트웨어는 DBMS라고 합니다. 데이터베이스는 여러 명의 사용자나 응용 프로그램과 공유하고 동시에 접근이 가능해야 합니다. 

 

1.DBMS 종류

데이터 베이스를 사용하기 위해서 대표적으로 MYSQL,Oracle,SQL server,MariaDB등이 있습니다.소프트웨어 각각의 사용 방법과 특징이 다르지만 특정 목적을 위해서는 어떤 것을 사용해도 무방합니다.

 

2.DBMS 분류

DBMS의 유형은 계층형, 망형,관계형, 객체지향형, 객체관게형으로 분류됩니다. 현재 많이 사용되는 DBMS은 관계형 DBMS가 가장 많은 부분을 차지하며, 우리가 사용할 MySQL도 관계형 DBMS에 포함됩니다.

 

1) 계층형 DBMS

계층형 DBMS은 처음으로 등장한 DBMS의 개념으로 은행계열에서 많이 씁니다. 계층 DBMS은 트리 형태를 가집니다. 계층형 DBMS 의 문제는 처음 구성을 완료한 후 에 이를 변경하기가 상당히 까다롭다는 것입니다.

 

2)망형 DBMS

망형 DBMS은 계층형 DBMS 문제점을 개선하기 나온 것으로 하위에 있는 구성원끼리도 연결된 유연한 구조입니다. 하지만 망형 DBMS을 활용하려면 프로그래머가 모든 구조를 이해해햐아만 프로그램 작성이 가능하다는 단점이 존재합니다.

 

3)관계형 DBMS

관계형 DBMS의 또다른 이름은 RDBMS입니다. 우리가 사용할 MySQl뿐만 아니라 거의 대부분의 DBMS가 RDBMS형태로 사용됩니다. 이는 데이터베이스는 테이블 이라는 최소 단위로 구성되며 테이블은 행과 열 로 이루어져 있습니다.

테이블은 열과 행으로 이루어진 2차원 구조를 갖습니다. 2차원 구조 말고 격자구조(sqld시험에서 낚시용으로 많이 써진답니다) 등으로 불리기도 합니다. 행은 각각의 정보로 이루어져있고, 열은 column을 가지고 있습니다.

 

4)DBMS에서 사용되는 언어:SQL

 

SQL은 관계형 데이터베이스에서 사용되는 언어입니다.국제표준화기구에서 SQL에 대한 표준을 정해서 발표하는것을 표준 SQL입니다.그런데 문제는 SQL을 사용하는 DBMS를 만드는 회사가 여러 곳 이기 때문에 표준 SQL이 각 회사 제품의 특성을 모두 포용하지 못한다는 점입니다. 그래서 DBMS 을 만드는 회사에서는 되도록 표준 SQL을 준수하되, 각 제품의 특성을 반영한 SQL을 사용합니다.

 

---

데이터 베이스 모델링

 

 데이터베이스 모델링은 테이블의 구조를 미리 설계하는 개념입니다. 우리가 살고 있는 세상에서 사용되는 작업이나 사물들을 DBMS의 데이터베이스 객체로 옮기기 위한 과정입니다.또는 현실에서 쓰이는 것을 테이블로 변경하기 위한 작업입니다. 데이터 베이스 모델링이 완료되면 절차에 따라 데이터 베이스를 구축할 수 있습니다. 아래 그림은 데이터베이스의 구축 절차입니다.

 

 

프로젝트를 진행하기 위해서는  폭포수 모델(waterfall model)을 사용하며 데이터베이스 모델링은 폭포수 모델의 업무 분석과 시스템 설계 단계에 해당합니다. 프로젝트란 현실 세계에서 일어나는 업무를 컴퓨터 시스템으로 옮겨놓는 과정입니다. 더 쉽게 대규모 소프트웨어를 작성하기 위한 전체 과정이라고 이야기할 수 있습니다.

위의 그림은 소프트웨어 개발 단계를 폭포수 모델로 표현하는 것입니다. 폭포수 모델은 각 단계가 구분되어 프로젝트의 진행 단계가 명확하다는 장점이 있습니다. 하지만 모델의 가장 큰 단점은 폭포에서 내려가기는 쉬워도 다시 올라가기는 힘든 것처럼 문제가 발생할 경우 다시 앞 단계로 돌아가기가 어렵다는 것입니다. 그래도 각 단계가 명확하기 때문에 많이 사용되고 있습니다.

---

 

전체 데이터베이스 구성도

 

 

위의 그림은 전체 데이터베이스 구성도를 나타낸 그림입니다.하나하나씩 잘 살펴보죠.

데이터:하나하나의 단편적인 정보 테이블:회원이나 제품의 데이터를 입력하기 위해 표 형태로 표현한 것 데이터베이스:테이블이 저장되는 저장소 .데이터베이스는 서로 이름이 달라야함. DBMS:데이터베이스 관리시스템 또는 소프트웨어 열(column):테이블의 세로.column 열 이름: 각 열을 구분하기 위한 이름 데이터 형식: 열에 저장될 데이터의 형식 행(row,record): 실질적인 진짜 데이터.  기본키(PK):기본키 열은 각 행을 구분하는 유일한 열

위의 내용들은 데이터베이스에 매우 자주 나오는 단어들이므로 꼭 기억하고있어야합니다!

---

데이터베이스 개체

테이블은 위에서도 말했듯이 데이터 베이스의 핵심개체입니다. 하지만, 데이터 베이스에서는 테이블 외에, 인덱스, 뷰, 스토어드 프로시저, 트리거, 함수 ,커서등의 개체도 필요합니다. 모든 데이터 베이스 개체는 독립적으로 존재하는 것이 아니라 테이블과 상호 연관이 있습니다. 각각의 개체를 하나씩 보겠습니다.

1)인덱스

인덱스는 책의 제일 뒤에 수록되는 '찾아보기'와 비슷한 개념으로, 데이터를 조회할 때 결과가 나오는 속도를 획기적으로 빠르게 해줍니다.데이터를 조회할 때 테이블에 데이터가 적다면 결과가 금방 나오지만 데이터가 많아질수록 결과가 나오는 시간이 많이 소요니다. 인덱스는 이런 겨우 결과가 나오는 시간을 대폭 줄여줍니다. 인덱스에서 기억해야될 점은 인덱스 생성 여부에 따라 결과가 달라지는 것은 아니라는 것입니다. 즉 책의 내용을 찾을 때 찾아보기가 있으면 시간을 단축하는 효과는 있지만, 책의 찾아보기가 없어도 책의 첫 페이지부터 찾아야 하기 때문에 시간이 오래 걸릴 뿐 어차피 동일하게 찾을 수는 있습니다.

 

2)뷰

뷰는 테이블의 일부를 제한적으로 표현할때 주로 사용합니다. 뷰를 활용하면 보안도 강화하고, SQL문도 간단하게 사용할 수 있습니다.일반 사용자의 입장에서는 테이블과 뷰를 구분할 수 었습니다. 일반 사용자는 테이블과 동일하게 뷰를 취급하면 됩니다.다만 뷰는 실제 데이터를 가지고 있지 않으며, 진짜 테이블에 링크된 개념이라고 생각하면 됩니다.

 

3)스토어도 프로시저

스토어도 프로시저는 프로시저를 통해  SQL안에서도 일반 프로그래밍 언어처럼 코딩을 할 수 있습니다.비록 일반 프로그래밍 보다는 좀 불편하지만, 프로그래밍 로직처럼 여러개의 SQL문을 하나로 묶어서 편리하게 사용할 수 있습니다.

 

4) 트리거

트리거는 잘못된 데이터가 들어가는 것을 미연에 방지하는 기능입니다. 트리거는 특정 테이블에 insert, delete,update 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램입니다. 즉, 사용자가 직접 호출 하는 것 이 아니라, 데이터베이스에서 자동적으로 호출하는 것이 가장 큰 특징입니다.

 

5)함수 

프로시저와 사용 용도가 거의 비슷하나, out 매개변수를 사용하지 않아도 실행 값을 되돌려 받을 수 있습니다.(Return값)

 

 

6)커서

커서는 select 문 처리결과를 담고 있는 메모리 공간에 대한 포인터 입니다. 커서 사용시 여러 row 로 나타난 처리 결과에 순차적으로 접근이 가능합니다.

---

SQL 기본 문법

1.USE 문 - 스키마(데이터베이스)선택

select문을 실행하려면 먼저 사용할 데이터베이스를 지정해야합니다.현재 사용하는 데이터 베이스를 지정 또는 변경하는 형식은 다음과 같습니다.

-- 데이터베이스 지정
USE market_db;

 

2.SELECT문-조회할 데이터 지정

select문은 구축이 완료된 테이블에서 데이터를 추출하는 기능을 합니다.

-- select 컬럼명 from '테이블명'
SELECT member_id, name FROM member;

-- *은 모든 컬럼을 조회함
SELECT * FROM member;

 

기본적으로 테이블 이름은 스키마이름.테이블명으로 표현해야하지만, USE문으로 스키마를 지정해놨다면 테이블명만 명시해도 됩니다.

 

3.WHERE문 -특정조건만 조회하기

where문은 필요한 것들만 골라서 결과를 보는 효과를 갖습니다. Where절을 사용해 특정 조건에 해당하는 데이터를 조회할 수 있고, 관계 연산자/논리 연산자 사용가능합니다.

-- member 테이블에서 mem_number 컬럼 값이 5이상인 데이터 조회
SELECT * FROM member WHERE mem_number >= 5;

 

4.BETWEEN문 -범위 표현식

-- 위 2 3번구문은 같은 의미
select mem_name,height from member where height>=163 and height<=165;
select mem_name,height from member where height between 163 and 165;

 

between 연산자를 활용해 특정 범위를 지정해 이에 해당하는 데이터를 조회할 수 있습니다.

 

5.IN()문

height와 같이 숫자로 구성된 데이터는 크다/작다의 범위를 지정할 수 있으므로 between문을 사용하지만, 주소와 같은 데이터는 문자로 표현되기 때문에 어느 범위에 들어 있다고 표현할 수 없습니다. IN문을 활용하면 조금 더 간결하게 작성이 가능합니다.

-- addr 컬럼값이 경기, 전남, 경남인 데이터 조회
SELECT * FROM member WHERE addr IN('경기', '전남', '경남');

SELECT * FROM member WHERE addr = '경기' OR addr = '전남' 0R addr = '경남';

 

 

6.LIKE문

문자열의 일부 글자를 검색하려면 LIKE문을 활용해야합니다. 한 글자만 매치할 경우는 언더바(_)를 활용하고 몇글자든 상관없다면 %을 활용합니다.

-- mem_name 컬럼 값이 '블'로 시작하는 4글자 글자 데이터 조회
SELECT * FROM member WHERE mem_name LIKE '블___';

-- mem_name 컬럼 값이 '블'로 시작하는 모든 데이터 조회
SELECT * FROM member WHERE mem_name LIKE '블%';

-- mem_name 컬럼 값에 '블'이 들어가는 모든 데이터 조회
SELECT * FROM member WHERE mem_name LIKE '%블%';

 

7.서브쿼리

SELECT문 안에 또 다른 SELECT가 들어갈 수 있습니다.이것을 서브쿼리라고 부릅니다. 

SELECT mem_name, height	FROM member	WHERE height > (select height from member where mem_name LIKE '에이핑크');

 

8.ORDER BY절

ORDER BY 절을 활용하면 조회된 데이터를 정렬할 수 있습니다.ORDER BY절은 값이나 개수에 대해서는 영향을 미치지 않지만, 결과가 출력되는 순서를 조절합니다. ORDER BY절은 WHERE 절과 함께 사용이 가능하나, WHERE절 다음에 ORDER BY절이 등장해야합니다.

SELECT 	mem_id,mem_name,debut_date,height FROM member WHERE height>=164 ORDER BY height DESC;

 

 

9.LIMIT

LIMIT은 출력하는 개수를 제한합니다. LIMIT 형식은 LIMIT 시작, 개수입니다. 

SELECT * FROM member
	LIMIT 3;    		-- 상위 3건만 조회

SELECT * FROM member
	LIMIT 3, 2; 		-- 3번째 데이터부터 2건만 조회
	LIMIT 2 OFFSET 3; 	-- 위와 동일

 

10.DISTINCT

DISTINCT는 조회된 결과에서 중복된 데이터를 1개만 남깁니다. 

-- addr 의 모든 컬럼 값을 중복을 제거하여 조회
SELECT DISTINCT addr FROM member;

 

11.GROUP BY절

GROUP BY절은 말 그대로 그룹으로 묶어주는 역할을 합니다. 집계함수를 사용해서 좀 더 간단하게 표현이 가능합니다.

함수명	설명
SUM()	합계를 구합니다.
AVG()	평균을 구합니다
MIN()	최소값을 구합니다
MAX()	최대값을 구합니다
COUNT()	행의 개수를 셉니다
COUNT(DISTINCT)	행의 개수를 셉니다

 

SELECT 열_이름 집계함수() FROM 테이블_이름 GROUP BY 열_이름;

 

12.HAVING절

WHERE 대신에 사용되는 것으로, HAVING은 WHERE와 같은 개념으로 조건을 제한하는 것이지만, 집계 함수에 대해서 조건을 제한하는 것이라고 생각하면됩니다. HAVING절은 꼭 GOUP BY절 다음에 나와야합니다.

-- mem_id 를 기준으로 그룹화
-- 그룹화된 데이터를 기준으로 amount*price 합계가 1000 이상인 그룹만 남김
-- 조건에 걸러진 그룹에서 amount*price 의 합계를 조회
SELECT SUM(amount*price) AS "총 금액"
	FROM buy
    GROUP BY mem_id
    HAVING SUM(amount*price) >= 1000;

 

---

데이터 변경을 위한 SQL문

앞서 배운 SELECT 는 이미 만들어 놓은 테이블에서 데이터를 추출하는 구문이고, 앞으로 나올 내용은 INSERT,UPATE,DELETE 문을 통해 행 데이터를 구축하는 방법입니다.

 

1.INSERT

테이블에 행 데이터를 입력하는 기본적인 SQL문은 INSERT입니다.

INSERT INTO 테이블[(열1,열2,...)] VALUEE(값1,값2,...)

 INSERT문 에서는 테이블 이름 다음에 나오는 열은 생략이 가능합니다. 열 이름을 생략할 경우에 VALUES 다음에 나오는 값들의 순서 및 개수는 테이블을 정의할 때 열 순서 및 개수와 동일해야합니다.

 

INSERT 하면서 배운것중 AUTO_INCREMENT라는  것이 있습니다. AUTO_INCREMENT는 자동으로 증가하는 것으로써, 열을 정의할때 1부터 증가하는 값을 입력해줍니다.INSERT에서는 해당 열이 없다고 생각하고 입력하며 됩니다. 단, 주의 할 점은 AUTO_INCREMENT로 지정하는 열은 꼭 PRIMARY KEY로 지정해야합니다.

CREATE TABLE hongong2(
	toy_id INT AUTO_INCREMENT PRIMARY KEY,
    toy_name CHAR(4),
    age INT);
    
    
INSERT INTO hongong2 VALUES(NULL.'보핍',25); 
INSERT INTO hongong2 VALUES(NULL.'슬링키',22);    
INSERT INTO hongong2 VALUES(NULL.'렉스',21);    
-- toy_id는 순차적으로 증가하게됨

아래와 같이 null값으로 지정해놓으면 알아서 증가하게 됩니다. 만약 100부터 시작하면서 3씩 증가하게 하려면 아래와 같은 코드를 사용하면 됩니다.

CREATE TABLE hongong3(
	toy_id INT AUTO_INCREMENT PRIMARY KEY,
    toy_name CHAR(4),
    age INT);
    
ALTER TABLE hongong3 AUT0_INCREMENT=100;
SET @@auto_increment_increment=3;

@@는 시스템 변수로써, MYSQL에서 자체적으로 가지고 있는 설정값이 저장된 변수를 말합니다.

 

또, 많은 양의 데이터를 지금까지 했던 방식으로 직접 타이핑해서 입력하려면 오랜 시간이 걸릴 것입니다. 다른 테이블에 만약 데이터가 입력되어 있다면 INSERT INTO~ SELECT 구문을 사용해 해당 테이블의 데이터를 가져와서 한번에 입력할 수 있습니다.주의할 점은 SELECT 문의 열의 개수는 INSERT할 테이블의 열 개수와 같아야합니다. 즉 SELECT의 열이 3개라면 INSERT 될 테이블의 열도 3개여야합니다.

INSERT INTO 테이블_이름(열_이름1,열_이름2,...)
	SELECT 문;

 

2.UPDATE

데이터를 수정해야한다면 UPDATE를 사용해서 내용을 수정합니다.

-- UPDATE 기본문법
UPDATE 테이블_이름
	SET 열1=값1,열2-값2,...
    WHERE 조건;

또, UPDATE 문에서 WHERE절은 문법상 생략이 가능하지만, WHERE 절을 생략한다면 테이블의 모든 행의 값이 변경됩니다.일반적으로 전체 행의 값을 변경하는 경우는 별로 없으므로 주의해야 합니다.

 

3.DELETE

테이블의 행 데이터를 삭제해야하는 경우도 발생합니다.DELETE문은 행 단위로 삭제하며, 형식은 다음과 같습니다.

DELETE FROM 테이블 이름 WHERE 조건;

만약 몇억건의 데이터가 있는 대용량의 테이블을 삭제시키는 방법은 뭐가 있을까요??

DELETE FROM 테이블이름;
DROP TABLE 테이블이름;
TRUNCATE TABLE 테이블이름;
-- 대용량 테이블을 한번에 삭제시키는 법

DELETE문 은 삭제가 오래 걸립니다. DROP문은 테이블 자체를 삭제시키고, TRUNCATE문은 DELETE와 동일한 효과를 내지만 속도가 무척 빠릅니다. 하지만 DROP 문은 테이블이 아예 없어지지만, DELETE와 TRUNCATE문은 빈 테이블을 남깁니다. DELETE와 TRUNCATE문의 차이는 WHERE 문을 사용할 수 있냐에 대한 차이인데, TRUNCATE문은 WHERE문을 사용할 수 없어서 TRUNCATE는 조건 없이 전체 행을 삭제 할때만 사용됩니다. 

 

---

지금까지 SQL의 기본에 대해서 공부했습니다. SQL문을 기본문 자체가 영어만 이해한다면 생각보다 쉽게 이해할 수 있었습니다!! 모르는거나 혹시 제가 틀린 부분이 있으면 댓글 남겨주시면 감사하겠습니다.:)