[WEB]DAY28_DDL, DML 예제 및 실습

 

/*
 * FLOWER 테이블 생성하기
 * 컬럼명 : ID, NAME, AGE, COLOR
 * ID는 PK로 설정한다.
 * FLOWER에서 FLOWER2로 테이블명 변경
 * FLOWER2에서 PRICE컬럼 추가
 * INSERT로 값 넣기(2개의 정보 넣기)
 * FLOWER2테이블 값 삭제(TRUNCATE)
 * FLOWER2테이블 전체 삭제(DROP)
 */
CREATE TABLE FLOWER(
	ID VARCHAR2(10),
	NAME VARCHAR2(20),
	AGE NUMBER(2),
	COLOR VARCHAR2(10),
	CONSTRAINT FLOWER_PK PRIMARY KEY (ID)
);

SELECT * FROM FLOWER;

ALTER TABLE FLOWER RENAME TO FLOWER2;

SELECT * FROM FLOWER2;

ALTER TABLE FLOWER2 ADD PRICE NUMBER(8);

SELECT * FROM FLOWER2;

INSERT INTO FLOWER2
(ID, NAME, AGE, COLOR, PRICE)
VALUES('20200001', '장미', 1, '빨간색', 3500);

INSERT INTO FLOWER2
(ID, NAME, AGE, COLOR, PRICE)
VALUES('20200002', '해바라기', 2, '노란색', 5500);

SELECT * FROM FLOWER2;

TRUNCATE TABLE FLOWER2;

DROP TABLE FLOWER2;
------------------------------------------------------------------
--시퀀스(Sequence)
--유일한 값을 생성해주는 오라클 객체(자동 즐가 컬럼)

CREATE SEQUENCE USER_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 1000;

--DROP SEQUENCE USER_SEQ
CREATE TABLE "USER"(
	USER_NUMBER NUMBER NOT NULL,
	ID VARCHAR2(20),
	PW VARCHAR2(20),
	NAME VARCHAR2(20),
	AGE NUMBER,
	PHONE_NUMBER VARCHAR2(11),
	CONSTRAINT USER_PK PRIMARY KEY (ID)
);

SELECT * FROM "USER";

INSERT INTO "USER"
(USER_NUMBER, ID, PW, NAME, AGE, PHONE_NUMBER)
VALUES(USER_SEQ.NEXTVAL, 'hds1234', '1234', '한동석', 20, '01043825111');

SELECT * FROM "USER";

---

PK 
- CONSTRAINT 제약조건이름 PRIMARY KEY(컬럼명) 
- ALTER TABLE 테이블명 ADD CONSTRINT 제약조건이름 PRIMARY KEY(컬럼명) 

 

FK 
- CONSTARAINT 제약조건이름 FOREIGN KEY(컬럼명) 
  REFERENCES 부모테이블명(부모PK컬럼명)  

 
- ALTER TABLE 테이블명 ADD 
  CONSTARAINT 제약조건이름 FOREIGN KEY(컬럼명)  
  REFERENCES 부모테이블명(부모PK컬럼명) 
   

값 추가 
부모테이블 먼저 그 다음 자식테이블 
  
값 삭제 
자식테이블 먼저 그 다음 부모테이블 
  

테이블끼리 관계를 맺으면 ERD에서 확인해볼 수 있다.   
 * ● : 부모 
 * ◇ : 자식 

---

DML 

 - SELECT : 검색 
     SELECT 컬럼명 FROM 테이블명 
     WHERE 조건식; 

- INSERT : 추가 
   INSERT INTO 테이블명 
   (컬럼명, 컬럼명,...) 
   VALUES(값, 값,...) 

- UPDATE : 수정 
   UPDATE 테이블명 
   SET 기존컬럼명 = 새로운 값 
   WHERE 조건식; 

- DELETE : 삭제 
   DELETE FROM 테이블명 
   WHERE 조건식;

 

 

조건식 : 참 또는 거짓, 둘 중 하나가 나오는 식 

WHERE 조건절 
   >, < : 초과, 미만 
   >=, <= : 이상, 이하 
   = : 같다 
   <>, !=, ^= : 같지 않다 

   AND : 둘 다 참이면 참 
   OR : 둘 중 하나라도 참이면 참 

 

 

/*
 * 주인 / 애완견
 * 
 * OWNER			→			PET
 * ID(PK) VC2(20)			DOG_ID(PK) VC2(20)
 * --------------------------------------------------
 * NAME VC2(20)			NAME VC2(20)
 * AGE NUMBER(3)			OWNER_ID(FK) VC2(20)
 * ADDRESS VC2(30)		AGE NUMBER(2)
 * GENDER CHAR(1)		FEED VC2(20)
 * 									DISEASE VC2(30)
 */ 
DROP TABLE PET;

CREATE TABLE PET(
	DOG_ID VARCHAR2(20),
	NAME VARCHAR2(20),
	OWNER_ID VARCHAR2(20),
	AGE NUMBER(2),
	FEED VARCHAR2(20),
	DISEASE VARCHAR2(30),
	CONSTRAINT PET_PK PRIMARY KEY(DOG_ID),
	CONSTRAINT PET_FK FOREIGN KEY(OWNER_ID) 
	REFERENCES OWNER(ID)
);

SELECT * FROM PET;


CREATE TABLE OWNER(
	ID VARCHAR2(20),
	NAME VARCHAR2(20),
	AGE NUMBER(3),
	ADDRESS VARCHAR2(30),
	GENDER CHAR(1),
	CONSTRAINT OWNER_PK PRIMARY KEY(ID)
);
SELECT * FROM OWNER;

INSERT INTO OWNER
(ID, NAME, AGE, ADDRESS, GENDER)
VALUES('001204001', '한동석', 20, '서울시 관악구 봉천동', 'M');

SELECT * FROM OWNER;

INSERT INTO PET
(DOG_ID, NAME, OWNER_ID, AGE, FEED, DISEASE)
VALUES('190202001', '바둑이', '001204001', 2, '사료', '기침가래');

SELECT * FROM PET;

INSERT INTO PET
(DOG_ID, NAME, OWNER_ID, AGE, FEED, DISEASE)
VALUES('190202002', '점박이', '001204001', 2, '사료', '설사');

SELECT * FROM PET;

DROP TABLE PET;

DELETE FROM OWNER
WHERE ID='001204001';

SELECT * FROM OWNER;

DROP TABLE OWNER;

SELECT * FROM EMPLOYEES;
----------------------------------------------------------------
--꽃과 화분
CREATE TABLE FLOWER(
	NAME VARCHAR2(20),
	COLOR VARCHAR2(10),
	PRICE NUMBER(6),
	CONSTRAINT FLOWER_PK PRIMARY KEY (NAME)
);


CREATE TABLE POT(
	ID VARCHAR2(10),
	COLOR VARCHAR2(20),
	SHAPE VARCHAR2(20),
	NAME VARCHAR2(20),
	CONSTRAINT POT_PK PRIMARY KEY(ID),
	CONSTRAINT POT_FK FOREIGN KEY(NAME)
	REFERENCES FLOWER(NAME)
);

SELECT * FROM FLOWER;
SELECT * FROM POT;

INSERT INTO FLOWER
(NAME, COLOR, PRICE)
VALUES ('장미', '빨간색', 5000);

INSERT INTO FLOWER
(NAME, COLOR, PRICE)
VALUES ('해바라기', '노란색', 7000);

INSERT INTO FLOWER
(NAME, COLOR, PRICE)
VALUES ('무궁화', '분홍색', 10000);


SELECT * FROM FLOWER;

INSERT INTO POT
(ID, COLOR, SHAPE, NAME)
VALUES('200402001', '검은색', '정육면체', '무궁화');

INSERT INTO POT
(ID, COLOR, SHAPE, NAME)
VALUES('200402002', '하얀색', '원형', '무궁화');

SELECT * FROM POT;

UPDATE POT
SET NAME = '해바라기'
WHERE NAME = '무궁화';

DELETE FROM POT
WHERE NAME != '장미';

SELECT * FROM POT;

TRUNCATE TABLE POT;

DROP TABLE POT;

DROP TABLE FLOWER;

---

----------------------------------------------------------
--제약조건 심화
CREATE TABLE PERSON(
	ID VARCHAR2(20),
	NAME VARCHAR2(20),
	AGE NUMBER(3),
	GENDER CHAR(1) DEFAULT 'M' NOT NULL CONSTRAINT CHECK_CHAR
	CHECK(GENDER = 'M' OR GENDER = 'W'),
	CONSTRAINT PERSON_PK PRIMARY KEY (ID)
);

INSERT INTO PERSON
(ID, NAME, AGE)
VALUES('001', '한동석', 20);

SELECT * FROM PERSON;

INSERT INTO PERSON
(ID, NAME, AGE, GENDER)
VALUES('002', '하니', 20, 'W');

SELECT * FROM PERSON;
-----------------------------------------------------------
--실습
SELECT * FROM PLAYER;

--PLAYER 테이블에서 TEAM_ID가 'K01'인 선수 검색
SELECT * FROM PLAYER
WHERE TEAM_ID = 'K01';
--PLAYER 테이블에서 TEAM_ID가 'K01'이 아닌 선수 검색
SELECT * FROM PLAYER
WHERE TEAM_ID <> 'K01';
--PLAYER 테이블에서 WEIGHT가 70이상이고 80이하인 선수 검색
SELECT * FROM PLAYER
WHERE WEIGHT >= 70 AND WEIGHT <= 80;

SELECT * FROM PLAYER
WHERE WEIGHT BETWEEN 70 AND 80;
--PLAYER테이블에서 TEAM_ID가 'K03'이고 HEIGHT가 180미만인 선수 검색
SELECT * FROM PLAYER
WHERE TEAM_ID = 'K03' AND HEIGHT < 180;

--PLAYER테이블에서 TEAM_ID가 'K06'이고 NICKNAME이 '제리'인 선수 검색
SELECT * FROM PLAYER
WHERE TEAM_ID = 'K06' AND NICKNAME = '제리';
--STADIUM테이블에서 SEAT_COUNT가 30000초과이고 41000이하인 경기장 검색
SELECT * FROM STADIUM
WHERE SEAT_COUNT > 30000 AND SEAT_COUNT <= 41000;
--PLAYER테이블에서 TEAM_ID가 'K02'이거나 'K07'이고 포지션은 'MF'인 선수 검색
--WHERE 조건절에서 OR보다 AND가 우선순위가 높다.
--OR를 먼저하기 위해서는 괄호로 묶어주어야 한다.
SELECT * FROM PLAYER
WHERE TEAM_ID = 'K02' OR TEAM_ID = 'K07' AND "POSITION" = 'MF';

SELECT * FROM PLAYER
WHERE (TEAM_ID = 'K02' OR TEAM_ID = 'K07') AND "POSITION" = 'MF';

SELECT * FROM PLAYER
WHERE TEAM_ID IN('K02', 'K07') AND "POSITION" = 'MF'
--ORDER BY TEAM_ID DESC;
--ORDER BY TEAM_ID;
ORDER BY 3;

---

DB인코딩 글자 깨질 때 해결방법
https://sysinfo.tistory.com/16