※ 주의사항 ※
본 블로그는 수업 내용을 바탕으로 제가 이해한 부분을 정리한 블로그입니다.
본 내용을 참고로만 보시고, 틀린 부분이 있다면 지적 부탁드립니다!
감사합니다😁
안녕하세요!!
오늘은 지난번에 구축한 데이터베이스 작업 환경으로
간단하게 DB 및 테이블을 생성하고 데이터를 입력해보았습니다.
오늘은 아래와 같은 내용을 확인해보겠습니다.
데이터베이스(DB) 생성 --> CREATE DATABASE
테이블(TABLE) 생성 --> CREATE TABLE
데이터 입력 --> INSERT INTO
데이터 활용 --> SELECT * FROM
#########################
※ DB 프로그램별 Client 프로그램 이름 정리
① MySQL - MySQL Workbench
② SQL SEVER - SQL Server MANAGEMENT SERVICE (SSMS)
③ MariaDB - heidisql(ORACLE 빼고 다른 DB도 가능)
④ Oracle - sqldeveloper
#########################
간단한 데이터베이스 및 테이블 구조를 가지고 4개의 DBMS를 활용해서 DB를 만들어보겠습니다. (즉, 같은 테이블 구조를 4개의 DB Client 프로그램을 가지고 만드는 시간입니다.) 아무래도 표준 SQL을 바탕으로 본인들의 색을 입혔기때문에 대부분 비슷한 유형을 가지고 있습니다. 4개의 DB를 직접 확인해보고 차이점을 파악하며 공부를 해보겠습니다.
아무래도 취업할 회사에서 어떠한 DB를 사용할지 알수 없기 때문에 표준 SQL을 기본 바탕으로 수업이 진행될 예정입니다.
1. 데이터베이스 및 테이블 구조
데이터베이스의 및 테이블의 구조에 대해 알아보고, 아래와 똑같은 형태의 DB를 4개의 DBMS를 통해 만드는 실습을 진행해보겠습니다. 시작하기에 앞서 DB의 기본 구조에대해 알아보고 알아둬야할 용어를 확인해보겠습니다.
행과 열로 이루어진 테이블은 우리에겐 이미 익숙한 형태입니다. (엑셀 프로그램을 통해 많이 접해왔습니다!)
그래서 생소한 용어만 설명을 하자면
① DBMS : DBMS는 Data Base Management System(Software)의 약자로 흔히 우리가 설치한 DB프로그램을 지칭합니다.
② 데이터베이스(DB) : 데이터베이스는 데이터들의 집합으로 흔히 원통모양으로 표시를 합니다. 유의해야할 사항은 DB프로그램 마다 데이터베이스가 지칭하는게 다르다는 점입니다. 오라클의 경우는 데이터들의 집합(저장소)을 스키마 라고 지칭하기도 합니다. (오라클에서 스키마의 집합소는 데이터베이스!)
③ 테이블 열 : 테이블의 세로로 구성된 항목을 열이라 한다. 각 열마다는 구분하기 위한 이름이 있으며, 열마다는 고유의 형식이 있다.(문자형은 CHAR, 숫자는 INT, 날짜는 DATE 등등)
④ 테이블 행 : 실질적인 데이터를 뜻한다. 즉 행의 갯수가 데이터의 갯수를 지칭한다.
⑤ 기본 키 (Primary Key) : 각 행을 구분하는 유일한 열을 지칭한다. 기본 키는 중복되어서는 안되며 비어있어서도 안된다. (Not NULL)
이정도의 용어와 구조만 알아둔채 바로 DBMS를 활용해서 위와같은 형태의 DB를 생성해보겠습니다. 각 프로그램마다 다른점은 별도로 명시해 두겠습니다.
2. My SQL
DB생성부터 데이터 입력 등 모든 과정은 마우스를 사용하지 않고 SQL문으로만 진행을 하겠습니다.
(모든 툴마다 GUI 환경이 다르기 때문에 마우스로 DB를 다루는것은 의미가 없다는 교수님의 말씀!!)
MySQL로 처음 DB를 생성하는 것이기 때문에 한문장 한문장 자세히 집고 넘어가겠습니다.
다음 Maria DB부터는 쿼리문 전체를 올리고 다른점(차이점)만 집고 넘어가는식으로 글을 작성하겠습니다.
2-1. 데이터 베이스 생성
위의 사진을 보면 쇼핑몰 이란 데이터베이스 안에 두개의 테이블이 들어가 있습니다. 고로 먼저 "쇼핑몰"이란 데이터베이스를 먼저 생성해보겠습니다.
CREATE DATABASE shop_db
SQL문에서는 소문자와 대문자를 구분하지 않는다고 합니다. 즉 작성자가 대문자로 작성을 해도 프로그램에서는 소문자로 인식을 합니다. 하지만 '예약어'와 '사용자 정의어'를 구분짓기위해 예약어는 대문자, 사용자 정의어는 소문자로 작성하겠습니다.
shop이란 데이터베이스를 만드는데 단순히 shop이라고만 하면 나중에 데이터베이스인지 테이블인지 값인지 햇갈릴 수 있기 때문에 shop_db로 뒤에 언더바를 이용해 네이밍을 해주겠습니다.
쿼리문이 정상적으로 실행이 되면 다음과 같은 문구가 출력이 됩니다.
※ 주석은 하이픈("--")을 두개 친 후 한번 띄고 입력해주면 됩니다.
2-2. 테이블 생성
(1) 테이블 생성 전 데이터베이스 선택
USE shop_db;모든 테이블을 생성하기 전에 해당 테이블이 생성될 데이터베이스를 선택해야합니다.
그와 관련된 명령어는 'USE' 이며 USE는 데이터베이스가 바뀌거나 할때 다시 또 사용해주어야합니다.(아마도..)
(2) 테이블 생성
CREATE TABLE member_tbl(
mem_id CHAR(7) PRIMARY KEY,
mem_name CHAR(5),
mem_age TINYINT(2) UNSIGNED
);
CREATE TABLE product_tbl(
prd_name CHAR(10) PRIMARY KEY,
prd_price INT,
prd_date DATE,
prd_company CHAR(10),
prd_num SMALLINT UNSIGNED
);테이블을 생성할때는 테이블명과 괄호 안에 테이블 '열 이름' 과 '데이터 타입'을 입력해주어야 합니다.
여기서 주의해야할 점은 데이터 타입의 선언 입니다. DB의 경우 데이터가 많아지면 나중에 용량이 부족해질 수 있기 때문에 적절한 데이터 타입을 골라주는게 무엇보다 중요합니다. (모든 DB는 RAM으로 로딩 된 후에 작업되기 때문에 나중에 DB 처리가 늦어질 수 있다.)
문자열 데이터 타입은 'CHAR'로 선언해주고 괄호 안에 최대 글자수를 지정해 주어야 합니다.
정수형 데이터 타입은 'INT'이지만 바이트에 따라 세부적으로 나뉩니다.
1BYTE INT → TINYINT
2BYTE INT → SMALLINT
4BYTE INT → INT
여기서 마이너스가 필요 없을때는 뒤에 UNSIGNED를 붙혀주면 음수가 없이 양수로 나옵니다.
날짜형 데이터 타입은 'DATE'로 선언을 합니다.
'DROP' 명령어를 사용해서 테이블 및 데이터베이스를 지울수가 있습니다.
하지만 DROP 명령어는 아주 신중하게 사용 해야 합니다.
데이터베이스는 한 회사의 중요한 자산임을 명심하셔야 합니다.
2-3. 데이터 입력 / 수정 / 삭제
INSERT INTO member_tbl(mem_id, mem_name, mem_age)
VALUES('YOU', '유영하', 30);
INSERT INTO member_tbl(mem_id, mem_name, mem_age)
VALUES('LEE', '이선빈', 28);
INSERT INTO member_tbl(mem_id, mem_name, mem_age)
VALUES('SON', '슈퍼손', 30);
INSERT INTO product_tbl(prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES('컴퓨터', 10, '2021-01-01', '삼성', 17);
INSERT INTO product_tbl(prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES('세탁기', 20, '2022-09-01', 'LG', 3);
INSERT INTO product_tbl(prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES('냉장고', 5, '2023-02-01', '대우', 23);데이터를 입력할땐 위와 같은 쿼리문의 형태로 입력을 해주면 됩니다. 유의해야할 점은 정수형은 그냥, 문자형은 작은 따옴표 안에 데이터를 넣어주셔야 합니다.
추가로, 다른 DB의 경우 한글을 입력할때는 작은 따옴표 안에 N을 붙혀주어야 한글이 안깨져서 저장이 됩니다. (선언을 할때도 마찬가지로 CHAR가 아닌 NCHAR로 해야합니다. )
ex) INSERT INTO member_tbl(mem_id, mem_name, mem_age) VALUES('YOU', N'유영하', 30);
하지만 My SQL은 N을 안붙혀도 한글이 깨지지 않으니 위와 같은 개념은 생략하셔도 됩니다.
2-4. 데이터 활용
SELECT * FROM member_tbl;
SELECT * FROM product_tbl;SELECT 문을 활용해서 테이블을 출력할 수 있습니다.
참고로 SELECT에서 *(별)은 모든 열을 뜻합니다.
따라서 [SELECT * FROM member_tbl;]은 "member_tbl 테이블의 모든열을 선택합니다"로 생각하면 됩니다.
2-5. 결과 확인
위와 같은 SELECT문을 사용해서 실행을 하면 두개의 테이블을 확인할 수 있습니다.
참고로 PRIMARY KEY를 설정하면 해당 열의 오름차순대로 데이터가 나열됩니다.
지금같은 경우는 기초 단계로 2번의 SELECT 문을 사용해서 2개의 TABLE을 각각 열었지만 다음에는 JOIN 이란 명령어를 사용해서 2개의 테이블을 1개로 합칠 수 도 있다고 합니다. 이는 나중에 배워보도록 하겠습니다.
3. Maria DB
✔ Maria DB 현재 커서 쿼리 실행 단축키 : Ctrl + Shift + F9 → 알아두면 매우 유용한 단축키
3-1. 쿼리문
CREATE DATABASE shop_db;
USE shop_db;
CREATE TABLE member_tbl (
mem_id CHAR(7) PRIMARY KEY,
mem_name NCHAR(5),
mem_age TINYINT UNSIGNED
);
CREATE TABLE product_tbl (
prd_name NCHAR(5) PRIMARY KEY,
prd_price INT UNSIGNED,
prd_date DATE,
prd_company NCHAR(7),
prd_num SMALLINT UNSIGNED
);
INSERT INTO member_tbl (mem_id, mem_name, mem_age)
VALUES('YOU',N'유영하',30);
INSERT INTO member_tbl (mem_id, mem_name, mem_age)
VALUES('SON',N'슈퍼손',30);
INSERT INTO member_tbl (mem_id, mem_name, mem_age)
VALUES('ALLA',N'알라딘',99);
INSERT INTO product_tbl (prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES(N'컴퓨터',10,'2021-01-01',N'삼성',17);
INSERT INTO product_tbl (prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES(N'세탁기',20,'2022-09-01','LG',3);
INSERT INTO product_tbl (prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES(N'냉장고',5,'2023-02-01',N'대우',17);
SELECT * FROM member_tbl;
SELECT * FROM product_tbl;Maria DB는 글을 작성하기도 미안할정도로 My SQL과 90% 이상 유사하다.
여기에는 CHAR 앞에 N을 작성하였지만 TEST 결과 N을 붙히지 않아도 한글이기 깨지지 않음을 확인하였다.
고로 지금까지의 쿼리문은 My SQL과 100% 똑같이해도 정상 동작한다.
3-2. 결과 확인
결과 확인창은 Maria DB가 조금더 아기자기하고 귀여운 맛이 있다.
4. SQL Server DB
✔ CTRL + K + C : 주석추가
✔ CTRL + K + U : 주석해제
✔ CTRL + E / F5 : 선택한 쿼리문 실행
4-1. 쿼리문
CREATE DATABASE shop_db;
GO
USE shop_db;
GO
CREATE TABLE member_tbl(
mem_id CHAR(7) PRIMARY KEY,
mem_name NCHAR(5),
-- UNSIGNED가 안되기 때문에 SMALLINT로!! 127세를 넘기는 사람이 있을수도..?
mem_age SMALLINT
);
GO
CREATE TABLE product_tbl(
prd_name NCHAR(7) PRIMARY KEY,
prd_price INT,
prd_date DATE,
prd_company NCHAR(7),
prd_num SMALLINT
);
GO
INSERT INTO member_tbl (mem_id, mem_name, mem_age)
VALUES ('YOU',N'유영하',30);
INSERT INTO member_tbl (mem_id, mem_name, mem_age)
VALUES ('SON',N'슈퍼흥민',30);
INSERT INTO member_tbl (mem_id, mem_name, mem_age)
VALUES ('DAN',N'지네딘지단',49);
-- NCHAR, 즉 한글 입력 테스트
INSERT INTO member_tbl (mem_id, mem_name, mem_age)
VALUES ('TES','테스트',123);
GO
INSERT INTO product_tbl (prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES (N'컴퓨터',10, '2021-01-01',N'삼성',17);
INSERT INTO product_tbl (prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES (N'세탁기',20, '2022-09-01','LG',3);
INSERT INTO product_tbl (prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES (N'냉장고',5, '2023-02-01',N'대우',22);
GO
SELECT * FROM member_tbl;
GO
SELECT * FROM product_tbl;SQL Server는 My SQL하고 다른게 몇가지 보입니다.
(1) NCHAR : 즉 한글을 사용하기 위해선 '반드시' 데이터타입을 결정할때 CHAR 앞에 N을 붙혀주어야 합니다. My SQL이나 Maria DB에는 안넣어도 한글사용이 가능했지만 SQL Server는 안됩니다.
위의 캡처본과 같이 데이터타입에는 N을 넣었지만 입력할때 N을 안넣은 한글은 "???"를 출력합니다.
따라서 DB관계없이 모든 한글을 입력할때는 N을 넣는 습관을 기르는게 좋을 것 같습니다.
(2) UNSIGNED : 1바이트 타입의 정수형 크기는 -128 ~ 127 입니다. 그리고 음수를 사용하기 싫다면 UNSIGNED를 붙혀서 0 ~ 255까지 사용을 할 수 있었습니다. 하지만 SQL Server는 UNSIGNED사용이 불가능합니다. 따라서 127보다 더 큰 값의 데이터를 사용하고 싶다면 TINYINT 보다 한단계 큰 SMALLINT를 사용해야 합니다.
(3) GO : 사실 GO 명령어는 SQL Server만의 특징은 아닙니다. My SQL에도 있다고 들었습니다.(아마도...) 하지만 이번 수업할때 SQL Server에만 GO를 사용했기 때문에 차이점으로 넣었습니다. GO를 사용하는 이유는 “GO를 쓰면 GO 이전의 문장을 완전하게 종료해주며 GO 이후에 나오는 문장은 새로운 시작으로 취급한다. 대부분 하나의 문장만 수행하는 경우에는 GO를 사용하지 않아도 된다.” 라고 [이것이 SQL Server다]라는 책의 91페이지에 명시되어 있습니다.
4-2. 결과 확인
INSERT할때 한글 앞에 N을 붙히지 않아서 "???"가 출력된것을 확인할 수 있습니다.
5. Oracle DB
대망의 Oracle DB..
오라클 DB는 사실 맨앞의 "1. 데이터 베이스 및 테이블 구조"의 그림조차 약간 상이합니다.
그림은 없으니 간략히 설명하자면
오라클의 데이터베이스 용어의 개념이 다른 DBMS의 제품보다 더 포괄적인 의미로 사용됩니다.
다른 DBMS(My SQL, SQL Server, Maria DB 등)에서는 여러개 테이블의 묶음을 데이터베이스라 지칭하였지만 오라클에서는 테이블의 묶음을 '스키마'라고 부르며 스키마의 묶음을 '데이터베이스' 라고 지칭합니다.
또한 다른 DBMS에서는 사용자를 생성하면 그 사용자가 모든 데이터베이스를 돌아다니면서 DB에 접근할 수 있었지만 오라클은 사용자의 이름과 동일하게 스키마를 취급한다. 따라서 사용자를 생성하면 사용자별 고유의 공간(스키마)가 생긴다. 즉, 우리가 여태까지 [CREATE DATABASE shop_db;] 라는 명령어를 통해 데이터베이스를 생성했다면 오라클에서는 shop_db라는 사용자를 생성해서 그안에 테이블을 작성해 주어야 한다. --> shop_db 스키마 생성
5-1. 쿼리문 (스키마 생성 / 사용자 추가)
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
CREATE USER shop_db IDENTIFIED BY "1234"
DEFAULT TABLESPACE "USERS";
ALTER USER shop_db QUOTA 10M ON USERS;
GRANT "CONNECT", "RESOURCE" TO shop_db;관리자(system) 계정에서 shop_db라는 사용자(스키마)를 생성합니다.
맨위의 코드는 [ALTER ~ ] 일정 버전 위에서부터는 무조건 입력해주어야 합니다.
QUOTA라는 예약어는 할당량을 뜻하며 shop_db 사용자에 10M만 사용할 수 있도록 배정한다는 뜻입니다.
마지막 GRANT 라인은 사용자에게 접속을 허용하고 자원 할당을 허용한다는 권한 부여 관련 문장입니다.
사용자를 생성하였으면 위와 같이 사용자를 추가(등록)합니다.
5-2. 쿼리문 (테이블 생성 및 데이터 입력)
Oracle은 다른 DBMS와는 살짝 다른개념이라고 앞서 설명을 하였습니다.
"5-1. 쿼리문(스키마 생성 / 사용자 추가)" 과정을 통해 우리는 데이터베이스를 생성을 하였습니다.
이제는 해당 스키마로 로그인을 해서 테이블 생성부터 시작하면 됩니다.
(해당 스키마(데이터베이스)에 로그인을 하는 개념이기 때문에 USE는 생략 가능)
CREATE TABLE member_tbl(
mem_id CHAR(7) PRIMARY KEY,
mem_name NCHAR(5),
mem_age SMALLINT
);
CREATE TABLE product_tbl(
prd_name NCHAR(7) PRIMARY KEY,
prd_price INT,
prd_date DATE,
prd_company NCHAR(7),
prd_num SMALLINT
);
INSERT INTO member_tbl (mem_id, mem_name, mem_age)
VALUES ('YOU',N'유영하',30);
INSERT INTO member_tbl (mem_id, mem_name, mem_age)
VALUES ('SON',N'슈퍼흥민',30);
INSERT INTO member_tbl (mem_id, mem_name, mem_age)
VALUES ('DAN',N'지네딘지단',49);
INSERT INTO product_tbl (prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES (N'컴퓨터',10, '2021-01-01',N'삼성',17);
INSERT INTO product_tbl (prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES (N'세탁기',20, '2022-09-01','LG',3);
INSERT INTO product_tbl (prd_name, prd_price, prd_date, prd_company, prd_num)
VALUES (N'냉장고',5, '2023-02-01',N'대우',22);
SELECT * FROM member_tbl;
SELECT * FROM product_tbl;코드 자체는 SQL Server와 동일한 형태를 띄고 있습니다.
5-3. 결과 확인
너무 중복되는 내용이라 글을 올릴때 좀 그랬지만 이번 글에서의 핵심은
"데이터베이스를 생성하고 테이블을 생성하는 기본 과정은 4개의 툴 모두 비슷하다!!!" 인것 같습니다.
추후 어디로 취업할 지 모르는 상황이기 때문에 표준 SQL을 기본 바탕으로 공부를 진행해보겠습니다.
댓글