[SQL] SQL 키 종류와 예제

기본키 :유일성 + NOT NULL
대체키 : 기본키가 되지못한 유일성 + NOT NULL
유일성, 최소성을 갖추면 후보키 / 기본키가 되지 못한 후보키 = 대체키
외래키 : 다른 테이블에서 참조하는 키
슈퍼키 : 유일성은 갖추되, 최소성은 갖추지 못한 것  

*유일성: 그컬럼을 식별할 수 있는 유일한 값 
*최소성: (1개만 있는 것) 컬럼1의 유일한값 + 컬럼2 의 유일한 값을 통해 도출해내는 유일성
사원번호(유일성)+주민등록번호(유일성) = 최소성(공간낭비)
(어차피 사원번호로 튜플(ROW)를 구분할수있는데 왜? 주민등록번호를 넣느냐 > 공간낭비를 하므로 최소성 성립안한다)

DB : 데이터 넣는것, 정리하는 것 (도출하는 것)

학생이면서, 천안시에 시민인 사람을 = 휴먼교육센터의 학번 + 시민번호 (직관적으로 데이터도출)

 

예제1번

--------------------------------------------------------------부서
CREATE TABLE 부서(
부서번호 INT,
부서이름 VARCHAR2(20),
PRIMARY KEY (부서번호)
);

DROP TABLE 부서;

INSERT INTO 부서 VALUES (1, '인사부');
INSERT INTO 부서 VALUES (2, '연구부');
INSERT INTO 부서 VALUES (3, '홍보부');

SELECT * FROM 부서;
--------------------------------------------------------------사원

CREATE TABLE 사원(
사원번호 INT,
사원이름 VARCHAR(20),
소속부서 INT,
PRIMARY KEY (사원번호),
FOREIGN KEY (소속부서) REFERENCES 부서(부서번호)
ON DELETE CASCADE
);
--ON DELETE CASCADE : 어떤 튜플이 삭제될때 외래키로 연결된 튜플또한 같이 삭제된다

DROP TABLE 사원;

COMMENT ON COLUMN 사원.소속부서 IS '테이블예제';
COMMENT ON TABLE 사원 IS '예제테이블';
--코멘트를 쓸 수있다.

INSERT INTO 사원 VALUES (1002, '김용욱', 1);
INSERT INTO 사원 VALUES (1003, '고명석', 2);
INSERT INTO 사원 VALUES (1005, '이준석', 3);
INSERT INTO 사원 VALUES (1008, '김준석', 3);
INSERT INTO 사원 VALUES (1011, NULL, 2); --NULL처리 : 회원가입시 선택사항 데이터를 위한 임시공간
INSERT INTO 사원 VALUES (1012, NULL, 1);

SELECT * FROM 사원 ORDER BY 사원이름 ASC;
--ASC: 오름차순 | DESC: 내림차순
--ORDER BY 정렬 뒤 출력 (성능에 매우 영향이 있다)
--꼭 필요할 때 사용. (WHERE 절을 써서 범위를 축소한 뒤 ORDER BY) 범위는 최소한으로!
--NULL값은 오름차순에서는 맨마지막에 출력되고, 내림차순에서는 맨 먼저 출력됨

SELECT 500*1.5 AS 계산결과 FROM DUAL;
--DUAL : 비어있는 테이블, 간단하게 함수를 이용해 계산 결과값을 확인 할 때 사용하는 테이블

NULL 데이터를 입력하는 시점에서 해당 속성값을 모르거나 미확정일때 사용
NULL 이란 ? 
OBJECT(자바로따지면) = DB에서 모든타입의 원형 (INT,NUMBER,CHAR,VARCHAR,DATE..)
NULL 값이 할당되지 않는 원형의 공간
NULL = '' 
NULL은 모든 타입의 원형이므로 어떤타입이든 연산이 가능

 

예제2번

--자동으로 INDEX 컬럼을 추가하는 옵션을 GOOGLE 검색해서 넣으세요 (Oracle=IDENTITIY, mySQL=AUTO_INCREMENT)
--단, 컬럼이름은 INDEX_AUTO 입니다
--CREATE 문과 INSERT, SELECT문을 만드세요(예제 7-26)

--고객테이블에서 고객이름, 등급,나이를 검색하되 나이를 기준으로 내림차순 정렬해보자

CREATE TABLE 고객AUTO(
INDEX_AUTO INT GENERATED ALWAYS AS IDENTITY, --AUTO 숫자증가
고객이름 		VARCHAR(12) NOT NULL,
등급 		VARCHAR(10) NOT NULL,
나이 		INT,
PRIMARY KEY (INDEX_AUTO)
);

INSERT INTO 고객AUTO(고객이름,등급,나이) VALUES ('오형준', 'silver', NULL);
INSERT INTO 고객AUTO(고객이름,등급,나이) VALUES ('성형원', 'gold', 35);
INSERT INTO 고객AUTO(고객이름,등급,나이) VALUES ('김준석', 'gold', 30);
INSERT INTO 고객AUTO(고객이름,등급,나이) VALUES ('고명석', 'gold', 31);

SELECT * FROM 고객AUTO;

--ORDER BY 를 포함해서 조건절에서는 처음 조건에 먼저 정렬하고, 그 정렬 조건안에서 다음조건 정렬 
SELECT * FROM 고객AUTO ORDER BY 나이 ASC;
--ASC : 오름차순 | DESC : 내림차순

DELETE FROM 고객AUTO WHERE INDEX_AUTO = 2;

 

자동으로 INDEX 컬럼을 추가하는 옵션 GENERATED ALWAYS AS IDENTITY

---

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

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
);

DROP TABLE 고객;
SELECT * FROM 고객;


-----------------SELECT 예제


--예제7-22 
---[LIKE'데이터%' : 데이터로 시작하는 문자열]
SELECT 고객이름, 나이, 등급, 적립금 FROM 고객 WHERE 고객이름 LIKE '김%';
--예제7-23 
--[LIKE'_____' : 5자 길이의 문자열] *고객아이디 5자인 고객검색
SELECT 고객아이디, 고객이름, 등급 FROM 고객 WHERE 고객아이디 LIKE '_____';


--예제7-24 
--[IS NULL : 특정속성값이 널값인지 비교]
SELECT 고객이름 FROM 고객 WHERE 나이 IS NULL;
--예제7-25 
--[IS NOT NULL : 특정속성값이 널값이 아닌지 비교]
SELECT 고객이름 FROM 고객 WHERE 나이 IS NOT NULL;


--예제7-26 
--[ORDER BY : 정렬방식 지정] [ASC:오름차순 | DESC:내림차순]
SELECT 고객이름, 등급, 나이 FROM 고객 ORDER BY 나이 DESC;


--예제7-30 
--[COUNT : 속성값의 개수] 
--*고객테이블에 고객이 몇명등록되어있는지 검색-> "고객수"라는 새이름으로 출력
SELECT COUNT(고객아이디) AS 고객수 FROM 고객;  
SELECT COUNT(나이) AS 고객수 FROM 고객; --NULL인 값은 제외하고 개수 계산
SELECT COUNT(*) AS 고객수 FROM 고객; 


--예제7-35 ★
--[GROUP BY : 특정속성의 값이 같은 튜플을 모아 그룹을 만들고 그룹별로 검색] 
--[GROUP BY는 HAVING 키워드와 함께 그룹에 대한 조건 작성 가능]
-- *적립금 평균이 1000이상인 등급에 대해 ,고객수와 평균 AVG 값구해서 "평균적립금" 이라는 새이름으로 출력
SELECT 등급, COUNT(*) AS 고객수, AVG(적립금) AS 평균적립금 FROM 고객
GROUP BY 등급 HAVING AVG (적립금) >= 1000;

-------------------------------------------------------------------COMMIT
COMMIT;
ROLLBACK;

7-35

--------------------------------------------------------------제품 (30p)
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, '한빛제과'
);

DROP TABLE 제품;
SELECT * FROM 제품;


-----------------SELECT 예제


--예제-7-13
SELECT 제조업체 FROM 제품;
--예제7-14 [ALL: 중복허용]
SELECT ALL 제조업체 FROM 제품;
--예제7-15 [DISTINCT : 중복불허용]
SELECT DISTINCT 제조업체 FROM 제품;

--예제7-16 
--[AS : 속성의 이름 바꾸어 출력가능] 
--*'단가'를 '가격'이라는 새이름으로 출력
SELECT 제품명, 단가 AS 가격 FROM 제품; 
--예제7-17	 
--*단가에 500원 더한값으로 변경 '조정단가'라는 새이름으로 출력
SELECT 제품명, 단가+500 AS "조정단가" FROM 제품; 
--예제7-18 	
--*한빛제과에서 제조한 제품 정보 검색
SELECT 제품명, 재고량, 단가 FROM 제품 WHERE 제조업체 = '한빛제과'; 
--예제7-21    
--*단가가 2000원 이상이면서 3000원이하인 제품 정보검색
SELECT 제품명, 단가, 제조업체 FROM 제품 WHERE 단가>=2000 AND 단가<=3000;


--예제7-28 
--[AVG : 속성값의 평균] *속성 '단가'의 평균값 출력
SELECT AVG(단가) FROM 제품;
--예제7-29 
--[SUM : 속성값의 합계] 
--*한빛제과에서 제조한 제품의 '재고량' SUM 검색 후 "재고량합계"라는 새이름으로 출력
SELECT SUM(재고량) AS "재고량합계" FROM 제품 WHERE 제조업체 = '한빛제과';
--예제7-31 
--[COUNT : 속성값의 개수][DISTINCT : 중복불허용] 
--*중복을 없애고 서로 다른 제조업체의 개수만 계산
SELECT COUNT(DISTINCT 제조업체) AS "제조업체 수" FROM 제품;


--예제7-33 ★ 
--[GROUP BY : 특정속성의 값이 같은 튜플을 모아 그룹을 만들고 그룹별로 검색]
--*제품테이블에서 제조업체별로 제조한 제품의 개수와 제품중 가장비싼 단가 검색,
--*제품의 개수는 제품수라는 이름으로 출력 , 가장비싼 단가는 최고가라는 이름으로 출력
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가 FROM 제품 GROUP BY 제조업체;
--예제7-34 ★ 
--[GROUP BY는 HAVING 키워드와 함께 그룹에 대한 조건 작성 가능]
--*집계함수를 이용한 조건은 WHERE절에는 사용할수없고 HAVING 절에서 작성가능 GROUP BY HAVING
--*제품테이블에서 제품을 3개이상 제조한 제조업체별로 제품의 개수와, 제품중 가장 비싼 단가 검색
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가 FROM 제품 
GROUP BY 제조업체 HAVING COUNT(*) >=3;

7-33

--------------------------------------------------------------주문(31p)
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 주문;


-----------------SELECT 예제


--예제7-19  
--*apple고객이 15이상 주문한 정보 검색
SELECT 주문제품, 수량, 주문일자 FROM 주문 WHERE 주문고객='apple' AND 수량>=15;
--예제7-20  
--*apple고객이 주문했거나 15개 이상 주문된 제품 검색
SELECT 주문제품, 수량, 주문일자, 주문고객 FROM 주문 WHERE 주문고객='apple' OR 수량>=15;


--예제7-27 
--[ASC:오름차순 | DESC:내림차순] 
--*수량이 10개이상인 정보 검색 후 주문제품은 오름차순 그 기준으로 + 수량은 내림차순
SELECT 주문고객,주문제품,수량,주문일자 FROM 주문 WHERE 수량>=10 ORDER BY 주문제품 ASC, 수량 DESC;


--예제7-32★ 
--[GROUP BY : 특정속성의 값이 같은 튜플을 모아 그룹을 만들고 그룹별로 검색]
-- *주문제품 p01은 2개의 튜플이있고,각 '수량'값은 5와 19이다 -> 이것을 그룹으로만들어 "총주문수량"라는 새이름으로 24값을 출력  
-- *그룹을 나누는 기준이되는 '주문제품' 속성 
-- *동일 제품을 주문한 튜플을 모아 그룹으로 만들고 그룹별로 수량의 합계를 계산
SELECT 주문제품, SUM(수량) AS "총주문수량" FROM 주문 GROUP BY 주문제품;
--예제7-36 
-- *주문테이블에서 각주문이 주문한 제품의 총주문수량을 주문제품별로 검색
-- *집계함수나 GROUP BY절에 명시된 속성외의 속성은 SELECT절에 작성불가
SELECT 주문제품,주문고객,SUM(수량) AS 총주문수량 FROM 주문 GROUP BY 주문제품, 주문고객;

 

7-32