[SQL] 테이블 기본 생성과 조작

테이블 간단 설명 이미지

--20220721 create table : 테이블생성
CREATE TABLE GROUP_PORTFOLIO (
--컬럼이름 / 컬럼타입 / 추가옵션
GROUP_NUMBER	NUMBER(2) DEFAULT 0,
NAME			VARCHAR(20) NOT NULL,
PORTFOLIO_NAME	VARCHAR(30)
);

--DROP TABLE
DROP TABLE GROUP_PORTFOLIO;
--ALTER TABLE
ALTER TABLE GROUP_PORTFOLIO MODIFITY CONSTRAINT NAME PRIMA
--COMMIT
COMMIT
ROLLBACK;

--SELECT DATA : 테이블을 찾아서 조회
--SELECT * : 조회하고싶은 컬럼 (>>몽땅 조회) / FROM / 테이블이름;
SELECT * FROM GROUP_PORTFOLIO;

--INSERT DATA : 테이블에 컬럼 맞춰서 데이터 넣기
--INSERT INTO / 테이블 이름 / VALUES / (컬럼순서에따라 데이터 차례대로 넣기)
INSERT INTO GROUP_PORTFOLIO VALUES (0, '김준석', '슬라임게임');
INSERT INTO GROUP_PORTFOLIO VALUES (2, '김병진', '');
INSERT INTO GROUP_PORTFOLIO VALUES (2, '지성진', '');
INSERT INTO GROUP_PORTFOLIO VALUES (2, '이영민', '');
INSERT INTO GROUP_PORTFOLIO VALUES (2, '이승현', '');
INSERT INTO GROUP_PORTFOLIO VALUES (2, '김명훈', '');

---

고객아이디의 밑줄은 기본키

기본 테이블생성 설명예제1

--CREATE TABLE // 테이블 이름 //
--작성 방법 : 컬럼 이름 / 데이터 타입

CREATE TABLE 고객 (
	고객아이디		VARCHAR2(20),
	고객이름		VARCHAR2(12),
	나이			NUMBER(3),
	등급			VARCHAR2(10),
	직업			VARCHAR2(20),
	적립금		NUMBER(10),
   --중복입력불가능
	CONSTRAINT 고객_PK PRIMARY KEY (고객아이디)
   --암기 : CONSTARANT 고객테이블_PK PRIMARY KEY(고객아이디)
);

--INSER INTO // 테이블이름 // VALUES // ('', 20);
--반드시 컬럼의 생성 순서대로 작성해야한다

INSERT INTO 고객 VALUES (
'apple', '정소화', 20, 'gold', '학생', 1000
);
INSERT INTO 고객 VALUES (
'banana', '김선우', 25, 'vip', '간호사', 2500
);
INSERT INTO 고객 VALUES (
'carrot', '고명석', 28, 'gold', '교사', 4500
);
INSERT INTO 고객 VALUES (
'orange', '김용욱', 22, 'silver', '학생', 0
);
INSERT INTO 고객 VALUES (
'melon', '성원용', 35, 'gold', '회사원', 5000
);
INSERT INTO 고객 VALUES (
'peach', '오형준', NULL, 'silver', '의사', 300
);
INSERT INTO 고객 VALUES (
'pear', '채광주', 31, 'silver', '회사원', 500
);

--SELECT // 컬럼 이름, ...(* : 모든 컬럼 조회) // FROM // 테이블이름 )
SELECT * FROM 고객 WHERE 고객아이디 like '%o%'OR 고객아이디 LIKE '2';

DROP TABLE 고객;

NUMBER : 숫자 (정수, 소수...)
NUMBER(3) : 100의 자리수까지 입력가능
NUMBER(4,3) : 1000의 자리수(정수)까지 입력가능 ,소수점 3의 자리까지 입력가능

CHAR : 고정 DB문자열
CHAR(10) : 'ABC' > 3 자리까지 들어가고 나머지 7자리를 공백으로 채운다.

VARCHAR : 가변길이 문자열 (1~2000byte)
VARCHAR2 : 가변길이 문자열 (1~4000byte)
영어는 1byte, 한글 3byte
DATE : 날짜

SELECT // 컬럼 이름, ...(* : 모든 컬럼 조회) // FROM // 테이블이름
SELECT * FROM 고객테이블; //전체검색
WHERE절 : 검색범위를 지정해주는 구문
SELECT * FROM 고객테이블 WHERE 고객이름 = "정소화";
WHERE 고객아이디 LIKE(비슷한거 찾기) '정%' (맨앞줄에 정이라는 문자가 있는 데이터 찾기)
WHERE 고객아이디 LIKE(비슷한거 찾기) '%화%' (어느자리에 상관없이 '화'라는 문자가있는 데이터 찾기)

---

기본 테이블생성 예제2

CREATE  TABLE 제품 (
	제품번호	VARCHAR2(3),
	제품명	VARCHAR2(20),
	재고량	NUMBER(10),
	단가		NUMBER(10),
	제조업체	VARCHAR2(12),
	CONSTRAINT 제품_PK PRIMARY KEY (제품번호)
);

INSERT INTO 제품 VALUES (
	'p01', '그냥만두', 5000, 4500, '대한식품'
);
INSERT INTO 제품 VALUES (
	'p02', '매운쫄면', 2500, 5500, '민국푸드'
);
INSERT INTO 제품 VALUES (
	'p03', '쿵떡파이', 3600, 2600, '한빛제과'
);
INSERT INTO 제품 VALUES (
	'p04', '맛난초콜릿', 1250, 2500, '한빛제과'
);
INSERT INTO 제품 VALUES (
	'p05', '얼큰라면', 2200, 1200, '대한식품'
);
INSERT INTO 제품 VALUES (
	'p06', '통통우동', 1000, 1550, '민국푸드'
);
INSERT INTO 제품 VALUES (
	'p07', '달콤비스킷', 1650, 1500, '한빛제과'
);


SELECT * FROM 제품;

DROP TABLE 제품;

기본 테이블생성 예제3

CREATE TABLE 주문 (
	주문번호 	VARCHAR2(3),
	주문고객 	VARCHAR2(20),
	주문제품 	VARCHAR2(3),
	수량 	NUMBER(10),
	배송지 	VARCHAR2(100),
	주문일자 	DATE,
CONSTRAINT 주문_PK PRIMARY KEY (주문번호)
);

INSERT INTO 주문 VALUES (
'o01', 'apple', 'p03', 10, '서울시 마포구', to_date('2022/01/01', 'YYYY/MM/DD')
);
INSERT INTO 주문 VALUES (
'o02', 'melon', 'p01', 5, '인천시 계양구', '2022-01-10'
);
INSERT INTO 주문 VALUES (
'o03', 'banana', 'p06', 45, '경기도 부천시', '2022-01-11'
);
INSERT INTO 주문 VALUES (
'o04', 'carrot', 'p02', 8, '부산시 금정구', '2022-02-01'
);
INSERT INTO 주문 VALUES (
'o05', 'melon', 'p06', 36, '경기도 용인시', '2022-02-20'
);
INSERT INTO 주문 VALUES (
'o06', 'banana', 'p01', 19, '충청북도 보은군', '2022-03-02'
);
INSERT INTO 주문 VALUES (
'o07', 'apple', 'p03', 22, '서울시 영등포구', '2022-03-15'
);
INSERT INTO 주문 VALUES (
'o08', 'pear', 'p02', 50, '강원도 춘천시', '2022-04-10'
);
INSERT INTO 주문 VALUES (
'o09', 'banana', 'p04', 15, '전라남도 목포시', '2022-04-11'
);
INSERT INTO 주문 VALUES (
'o10', 'carrot', 'p03', 20, '경기도 안양시', '2022-05-22'
);

DROP TABLE 주문;
SELECT * FROM 주문;

---

기본 테이블생성 문제 1

CREATE TABLE 조별포트폴리오목록 (
	그룹				NUMBER(3),
	조장이름			VARCHAR2(12),
	포트폴리오제목		VARCHAR2(30),
	포트폴리오작업내용	VARCHAR2(4000),
	CONSTRAINT 조별포트폴리오목록_PK PRIMARY KEY (그룹)
);
CREATE TABLE 조원구성 (
	그룹				NUMBER(3),
	조원이름			VARCHAR2(12),
	직책				VARCHAR2(30),
	포트폴리오담당		VARCHAR2(30),
	기타사항 			VARCHAR2(60)
);
CREATE TABLE 업무내용 (
	그룹				NUMBER(3),
	조원이름			VARCHAR2(12),
	업무내용			VARCHAR2(30),
	업데이트날짜		DATE,
	기타사항 			VARCHAR2(60)
);


INSERT INTO 조별포트폴리오목록 VALUES (
	1, '조인국', '카드게임', '하스스톤을 개발하였습니다 블리자드로 가야겠네요'
);
INSERT INTO 조별포트폴리오목록 VALUES (
	2, '이승현', '슬라임게임', '메이플스토리를 개발하였습니다 넥슨으로 가야겠네요'
);
INSERT INTO 조별포트폴리오목록 VALUES (
	3, '홍영재', '미니홈피', '엄청 큰 도토리를 개발하였습니다 테슬라로 가야겠네요'
);
INSERT INTO 조별포트폴리오목록 VALUES (
	4, '임수빈', '검색사이트', '검색사이트를 개발하였습니다 구글로 가야겠네요'
);


INSERT INTO 조원구성 VALUES (
	4, '피카츄', '프론트엔드', '귀여움담당', '이힛번개발싸이히히힛'
);
INSERT INTO 조원구성 VALUES (
	4, '라이츄', '백엔드', '깜찍함담당', '하핫라이츄입니당안녕'
);
INSERT INTO 조원구성 VALUES (
	4, '파이리', '백엔드', '카리스마담당', '불장난하자불꽃발싸'
);
INSERT INTO 조원구성 VALUES (
	4, '꼬부기', 'PM', '달콤함담당', '히힛침발싸켘켘켘'
);
INSERT INTO 조원구성 VALUES (
	4, '버터풀', 'PM', '존예담당', '나방아닙니다나빕니다'
);


INSERT INTO 업무내용 VALUES (
	4, '피카츄', '전기연결하기', to_date(SYSDATE), '전기료가안나가요'
);
INSERT INTO 업무내용 VALUES (
	4, '라이츄', '커피타기', to_date(SYSDATE), '어제야근하고가셨어요'
);
INSERT INTO 업무내용 VALUES (
	4, '파이리', '점심만들기', to_date(SYSDATE), '고기없으면퇴사합니다'
);
INSERT INTO 업무내용 VALUES (
	4, '꼬부기', '화장실청소하기', to_date(SYSDATE), '냄새가지독하군요'
);
INSERT INTO 업무내용 VALUES (
	4, '버터풀', '출장다녀오기', to_date(SYSDATE), '나방아닙니다나빕니다'
);


SELECT * FROM 조별포트폴리오목록;
SELECT * FROM 조원구성;
SELECT * FROM 업무내용;

DROP TABLE 조별포트폴리오목록;
DROP TABLE 조원구성;
DROP TABLE 업무내용;

테이블생성 문제 2 - 외래키

기본키 : 최소성과 유일성 만족, NOT NULL
대체키 : 기본키 외에 후보키 (언제나 기본키를 대체할수있는 컬럼의 값)
외래키 : 다른 테이블에서 참조할 수 있는 키

--/////////////////문제1
CREATE TABLE 고객테이블(
	고객아이디 	VARCHAR(20) NOT NULL,
	고객이름 	VARCHAR(10) NOT NULL,
	나이		NUMBER(3),
	등급		VARCHAR(10) NOT NULL,
	직업		VARCHAR(20),
	적립금	NUMBER(5) DEFAULT 0,
	
	CONSTRAINT 고객테이블_PK PRIMARY KEY (고객아이디)
);

INSERT INTO 고객테이블 VALUES(
	'kim001', '김준석', '34', 'stone', '강사', DEFAULT
);
INSERT INTO 고객테이블 VALUES(
	'yoon001', '윤준석', '25', 'silver', '교사', '500'
);
INSERT INTO 고객테이블 VALUES(
	'park001', '박준석', '20', 'gold', '무직', '1000'
);
INSERT INTO 고객테이블 VALUES(
	'sin001', '신준석', '16', 'diamond', '학생', '10000'
);

SELECT * FROM 고객테이블;

DROP TABLE 고객테이블;

--/////////////////문제2
CREATE TABLE 제품테이블(
제품번호 	CHAR(3)  NOT NULL,
제품명	VARCHAR(20),
재고량 	NUMBER(5) DEFAULT 0,
단가		NUMBER(5),
제조업체	VARCHAR(20),
CONSTRAINT 제품테이블_PK PRIMARY KEY (제품번호),
CHECK (재고량 >= 0 AND 재고량 <= 10000)
--제약조건 : 컬럼에 들어가는 데이터의 범위 또는 조건을 제한함 
--TRUE/FALSE
);

INSERT INTO 제품테이블 VALUES(
	'p01', '이쑤시개', 5000, 100, 'korea'
);
INSERT INTO 제품테이블 VALUES(
	'p02', '젓가락', 10000, 200, 'china'
);
INSERT INTO 제품테이블 VALUES(
	'p03', '숟가락', 0, 500, 'japan'
);
INSERT INTO 제품테이블 VALUES(
	'p04', '이어폰', 500, 10000, 'samsung'
);

SELECT * FROM 제품테이블;

DROP TABLE 제품테이블;

--/////////////////문제3

CREATE TABLE 주문테이블(
	주문번호 	CHAR(3) NOT NULL,
	주문고객 	VARCHAR(20),
	주문제품 	CHAR(3),
	수량 		INT,
	배송지 	VARCHAR(30),
	주문일자 	DATE,
	CONSTRAINT 주문테이블 PRIMARY KEY (주문번호),
	FOREIGN KEY(주문고객) REFERENCES 고객(고객아이디),
	FOREIGN KEY(주문제품) REFERENCES 제품(제품번호)
);

INSERT INTO 주문테이블 VALUES(
	'o01', 'kim001', 'p01', 50, '서울시한남동', to_date(SYSDATE)
); 
INSERT INTO 주문테이블 VALUES(
	'o02', 'sin001', 'p02', 100, '전주시효자동', to_date(SYSDATE)
); 
INSERT INTO 주문테이블 VALUES(
	'o04', 'park001', 'p03', 20, '천안시서북구', to_date(SYSDATE)
); 
INSERT INTO 주문테이블 VALUES(
	'o05', 'yoon001', 'p04', 10, '천안시동남구', to_date(SYSDATE)
); 

SELECT * FROM 주문테이블;

DROP TABLE 주문테이블;

--/////////////////문제4
CREATE TABLE 배송업체테이블 (
업체번호  	CHAR(3) NOT NULL,
업체명   	VARCHAR(20),
주소		VARCHAR(100),
전화번호 	VARCHAR(20),
PRIMARY KEY (업체번호)
);

INSERT INTO 배송업체테이블 VALUES(
	'c01', '배송기업1', '뫄뫄동뫄뫄빌딩', '88886666'
);
INSERT INTO 배송업체테이블 VALUES(
	'c02', '배송기업2', '과과동과과빌딩', '77776666'
);

SELECT * FROM 배송업체테이블;

DROP TABLE 배송업체테이블;

ALTER TABLE문 / 추가했던 속성 삭제 / 제약조건 추가 / 추가했던 제약조건 삭제

--테이블변경 ALTER TABLE문
ALTER TABLE 고객테이블 ADD 가입날짜 DATE;

------------------------------------

--추가한 고객테이블의 가입날짜 속성을 삭제해보자
--기존속성삭제
ALTER TABLE 고객테이블 DROP COLUMN 가입날짜;

------------------------------------

--새로운 제약조건 추가
ALTER TABLE 고객테이블 ADD CONSTRAINT CHK_AGE CHECK(나이>=10);

------------------------------------

--추가했던 제약조건 삭제
ALTER TABLE 고객테이블 DROP CONSTRAINT CHK_AGE;

---

암호화 하는 방법 - 복호화 하는 방법