2021-0908-01)sql명령
-SELECT:검색
DDL(DATA DERINITION KANGUAGE) : CREATE, ALTER, DROP
DCL(DATA CONTROL LANGUAGE) : COMMIT, ROLLBACK, SAVEPOINT,REVOKE,GRANT
DML(DATA MAINPULATION LANGUAGE): INSERT, UPDATE, DELETE, MERGE
1. CREATE
-오라클 객체를 생성할 때 사용
-테이블 생성시 CREATE TABLE 명령 사용
(사용형식)
CREATE TABLE 테이블명(
컬럼명1 데이터타입[(크기)][NO NULL] [DEFAULT 값][,]
컬럼명2 데이터타입[(크기)][NO NULL] [DEFAULT 값][,]
:
컬럼명N 데이터타입[(크기)][NO NULL] [DEFAULT 값]
[CONSTRAINT 기본키설정명 PRIMARY KEY(컬럼명[,컬럼명,...]([,]CONSTRAINT 외래키설정명 FOREIGN KEY(컬럼명[,컬럼명,...]([,]
REFERENCES 테이블명(컬럼명[,컬럼명,...]([,]CONSTRAINT 외래키설정명 FOREIGN KEY(컬럼명[,컬럼명,...]([,]
REFERENCES 테이블명(컬럼명[,컬럼명,...]([,]
:
[CONSTRAINT 외래키설정명 FOREIGN KEY(컬럼명[,컬럼명,...]([,]
REFERRENCES 테이블명(컬럼명[,컬럼명,...]([,]
사용예) 다음 자료를 참고하여 사원테이블을 생성하시오
테이블명: EMPLOYEE
컬럼명 데이터타입 크기 NULLABLE PK/FK EMP_ID CHAR 5 N.N PK
EMP_NAME VARCHAR2 30 N.N
EMP_ADDR VARCHAR2 100
DEPT_NAME VARCHAR2 CREATE TABLE EMPLOYEE(
EMP_ID CHAR(5)NOT NULL,
EMP_NAME VARCHAR2(30)NOT NULL,
EMP_ADDR VARCHAR2(100),
DEPT_NAME VARCHAR2(50),
CONSTRAINT pk_employee PRIMARY KEY(EMP_ID));
2021-0909-01)사용예) 사업장 테이블을 생성하시오
테이블명: CONST_SITE(사업장)
----------------------------------------------------------
컬럼명 데이터타입 크기 NULLABELE PK/FK
----------------------------------------------------------
CONST_ID CHAR 8 N.N PK
CONST_NAME VARCHAR2 50 N.N
CONST_ADDR VARCHAR2 100
CONST_TER_NUM VARCHAR2 20
REMARKS VARCHAR2 200
-----------------------------------------------------------
CREATE TABLE CONST_SITE(
CONST_ID CHAR(8), --기본키는 NOT NULL 안써두댐
CONST_NAME VARCHAR2(50) NOT NULL,
CONST_ADDR VARCHAR2(100), --컬럼명 공백 조심
CONST_TER_NUM VARCHAR2(20),
REMARKS VARCHAR2(200),
CONSTRAINT pk_const_site PRIMARY KEY(CONST_ID) --테이블 끝났으면 ,안해두댐
); 사용예) 근무 테이블을 생성하시오
테이블명:TBL_WORRKS(근무)
----------------------------------------------------------
컬럼명 데이터타입 크기 NULLABELE PK/FK
----------------------------------------------------------
EMP_ID CHAR 5 PK&FK
--다른 테이블 거를 기본키와 외래키로 지정함
CONST_ID CHAR 8 PK&FK
--다른 테이블 거를 기본키와 외래키로 지정함
SDATE DATE Default SYSDATE
-- SDATE 는 투입일자 DATE타입은 길이를 가지지 않는다/ Default SYSDATE는 시스템에서 제공하는 날짜와 시간
EDATE DATE
----------------------------------------------------------
CREATE TABLE TBL_WORRKS(
EMP_ID CHAR(5),
CONST_ID CHAR(8),
SDATE DATE Default SYSDATE,
EDATE DATE,
CONSTRAINT pk_tbl_worrks PRIMARY KEY(EMP_ID,CONST_ID),
CONSTRAINT fk_works_emp FOREIGN KEY(EMP_ID)--부모테이블 이름
REFERENCES EMPLOYEE(EMP_ID),
CONSTRAINT fk_works_site FOREIGN KEY(CONST_ID)
REFERENCES CONST_SITE(CONST_ID)
);
2. INSERT문
- 생성된 테이블에 새로운 자료를 삽입
(사용형식)
INSERT INTO 테이블명[(컬럼명1,컬럼명2,...)]
VALUES(값1,값2,...);
.'(컬럼명1,컬럼명2,...)': 생략하면 테이블 생성시 기술한 순서와 모든 컬럼에 배정될 값(데이터)을 VALUES절에 기술해야한다
.'(컬럼명1,컬럼명2,...)': 필요한 컬럼에만 값을 배정하기 위해 사용되며 컬럼 제약사항이 NOT NULL인 걸럼은 생략할 수 없음 사용예) 사원 테이블(EMPLOYEE)에 다음 자료를 입력하시옹
[자료]
---------------------------------------------------------
사원번호 사원명 주소 부서명
---------------------------------------------------------
18021 홍길동 대전시 중구 중앙로 76 설계부
20002 홍길순 토목
17113 강감찬 총무부
19007 이성계
--------------------------------------------------------
INSERT INTO EMPLOYEE --공백은 구분자로 쓰임
VALUES('18021','홍길동','대전시 중구 중앙로 76','설계부');
INSERT INTO EMPLOYEE --값이 없을때는 ''
VALUES('20002','홍길순','','토목');
INSERT INTO EMPLOYEE(EMP_ID,EMP_NAME,DEPT_NAME) --이것만 하겠다는 의미(컬럼명 작성)
VALUES('17113','강감찬','총무부'); --주소값은 적지 않아도됨
INSERT INTO EMPLOYEE(EMP_ID,EMP_NAME)
VALUES('19007','이성계',NULL,NULL);
SELECT * FROM EMPLOYEE;
사용예) 다음 자료를 사업장테이블(CONST_SITE)에 저장하시오
---------------------------------------------------------------------
사업장번호 사업장명 주소 전화번호 비고
---------------------------------------------------------------------
A1010120 도로신설공사 충남 예산군 예산읍 132-5678 공사중
C2103002 새여울초등학교 대전시 대덕구 867-0987 공사완료
--------------------------------------------------------------------
INSERT INTO CONST_SITE
VALUES('A1010120','도로신설공사','충남 예산군 예산읍','132-5678','공사중');
INSERT INTO CONST_SITE
VALUES('C2103002','새여울초등학교','대전시 대덕구','867-0987','공사완료');
SELECT * FROM CONST_SITE;
사용예) 다음 자료를 근무테이블에 저장하시오
1) 홍길동사원이 '도로신설동사' 공사장에 오늘 일자로 배속됨
INSERT INTO TBL_WORRKS(EMP_ID,CONST_ID)
VALUES('18021','A1010120');
INSERT INTO TBL_WORRKS(EMP_ID,CONST_ID)
VALUES('20002','A1010120');
SELECT * FROM TBL_WORRKS;
2) 강감찬직원이 '새여울초등학교'공사장에 '2020년 2월 1일에 배속되어 2020년 12월 30일까지 근무했음;
INSERT INTO TBL_WORRKS
VALUES('17113','C2103002',TO_DATE('20200201'),TO_DATE('20201230'));
SELECT * FROM TBL_WORRKS;
**'도로신설공사' 공사장에서 근무하는 모든 사원을 조회하시오
SELECT B.EMP_IS AS 사원번호,
B.EMP_NAME AS 사원명,
B.EMP_NAME AS 부서명,
A.SADTE AS 배속일
FROM TBL_WORRKS A, EMPLOYEE B, CONST_SITE C
WHERE .,EMP_ID=B.EMP_ID
AND A.CONST_ID=C.CONST_ID
AND A.CONST_NAME='도로신설공사'
ORDER BY 1;
데이터 타입
- 오라클에 사용되는 데이터 타입은 문자열, 숫자, 날짜, 이진데이터 타입으로 구분1. 문자열 데이터 타입
. 오라클은 문자타입은 존재하지 않음
. 모든 문자열은 ' '안에 기술 되어야 함
. ' '에 기술되는 문자열은 대소문자 구별 됨
. 문자열 데이터 타입은 CHAR, VARCHAR, VARCHAR2,NVARCHAR,LONG,CLOB,NCLOB 등이 있음
CHAR는 고정길이라 지정해 놓으면 남더라도 빈공간으로 채워짐
가변길이는 나머지 모두이다. 1) CHAR
- 고정길이 데이터를 저장
- 최대 2000 byte 저장가능
- 기억공간이 남으면 오른쪽에 남는 공간에 공백이 채워짐
-기본키 설정이나 확정된 길이를 나타내는 컬럼(주민번호 등)
(사용형식)
컬럼명 CHAR(크기[BYTE|CHAR])-> 생략하면 BYTE
.'크기[BYTE|CHAR]' : '크기'가 byte 인지 글자의 갯수인지 설정, 기본은 BYTE --1~2000까지 사용가능
.한글 한글자는 3 BYTE(최대 666글자 저장)
사용예)
CREATE TABLE TEMPO1(
COL1 CHAR(50),
COL2 CHAR(50 BYTE),
COL3 CHAR(50 CHAR);
INSERT INTO TEMP01
VALUES('IL POSTINO','IL POSTINO','일 포스티노');
--60글자에 6글자를 사용했으면 남은 글자는 44글자 이다. 이것은 미국이 만들어서 영어 기준이다.
--그래서 영어는 44바이트가 필요함 하지만 한글은 글자수로 6글자는 5글자 * 3 = 15 + 1BYTE + 16 이며 16 + 44
SELECT * FROM TEMP01;연산자
- 사칙연산자(+,-,/,*) --%는 연산자로 사용하지 않는다.(와일드 카드) MOD로 나머지 연산 사용
- 관계연산자(>,<,>=,<=,=,!=(<>))
- 논리연산자(not,and,or)
- 기타연산자(IN, ANY, SOME, ALL, EXISTS, LIKE, BETWEEN)
--EXISTS 반드시 서브 쿼리가 따라와야함
--LIKE 패턴을 가진 것을 비교낼때 사용(문자열에만 사용/ 날짜 처리 안함)
--BETWEEN 범위지정 후 비교(날짜, 문자, 숫자 다 사용 가능) + AND 가 붙으면 직관적으로 조건식 구성 가능 - 문자열 결합 연산자: '||'
--덧셈연산자와 같은 것
--Where절에 관계, 논리, 기타 연산자가 사용됨(참과 거짓으로 결과값이 나옴)
--오라클은 숫자 우선이라 문자열이 숫자로 바뀜(하지만 허용하지 않는 문자열 사용시 오류)
--여러 결과와 왼쪽결과를 비교할때 관계나 논리는 못쓰고 다중행연산자를 사용
- 사칙연산자, 관계연산자, 논리연산자
-
WHERE 절의 조건문 구성에 사용
BUYER는 거래처
CSRT 매출(_MEMBER구매 회원/_NO 카트의 고유 번호(날짜 8자리:연도 4자리-월2자리-일2자리)/_PROD 구매 상품 코드/_QTY 구매 상품 수량)
-- 다음 사람에게 부여하려면 +만 붙여도됨LPROD: 상품 분류가 저장된 테이블(_ID 순번이며 시퀀스(차례로 부여되는 것: 오라클 객체)를 사용(기본키 아님)/_GU 코드 값 /_NM 분류 코드 명 )
--가장 상위 테이블MEMBER (*_ID 기본키/_PASS/_NAME/_REGNO1 주민등록 번호 앞부분/_REGNO2 주민등록 번호 뒷부분/_BIR 생년월일/_ZIP 우편번호 코드/_ADD1 기본주소/_ADD2 상세 주소
/_HOMETEL집전화/_COMTEL/_HP핸드폰/_MAIL메일/_JOB직업/_LIKE 취미(원자값이여야 하는데 쪼개질 수 있어서 사용하면 안됨)/_MEMORIAL 기념일종류/_MEMORIADAY기념일
/_MILDAGE 물건 구매시 부가되는 마일리지 /_DELETEPROD 상품 정보가 저장된 테이블(_ID 상품 번호/_NAME/_LGU 분류 코드(분류코드를 갖다 쓴것)/_BUYER 거래처 코드(잘못 사용한것)/_COST매입 단가/_PRICE 매출 단가
/_SALE할인 판매 단가/_OUTLINE대략적인 설명/_DETALE자세한 설명/_IMG 상품 이미지/_TOTALSTOCK 진짜 재고량/_INSDATE/_PROPERSTOCK 각 품목당 팔리는 갯수
/_SIZE 크기/_COLOR 색상/_DELIVERY 배송/_UNIT/_QTYN/_QTYSALE/_MILEAGE 하나의 상품이 팔렸을때 부여되는 마일리지
사용예) 회원테이블에서 (조건: 마일리지가 3000이상)인 회원들을 조회하시오
조회할 컬럼은 회원번호, 회원명, 주소, 나이, 마일리지이다.
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_ADD1 || ' ' || MEM_ADD2 AS"주 소",
EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR) AS 나이,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE MEM_MILEAGE >= 3000;
-- WHERE절이 참일 때만 SELECT절 수행
-- 키워드가 없다는 에러가 날 경우FROM 절 위에 별칭을 찾아봐야함 (콤마)
사용예) 상품테이블에서 매입가가 10만원 이상인 상품을 조회하시오
Alias는 상품코드, 상품명, 매입가, 거래처명
SELECT A.PROD_ID AS 상품코드,
A.PROD_NAME AS 상품명,
A.PROD_COST AS 매입가,
B.BUYER_NAME AS 거래처명
FROM PROD A, BUYER B --PROD를 A로 BUYER를 B로 별칭함
WHERE A.PROD_COST >= 100000
AND A.PROD_BUYER = B.BUYER_ID; --테이블 조인
사용예) 사원테이블에서 급여가 5000이상 10000이하인 사원을 조회하시오
Alias는 사원번호, 사원명, 입사일, 부서코드, 급여
--HR 계정에 있는 것을 가져다 쓸때 HR.테이블명으로 작성해야 한다.
SELECT EMPLOYEE_ID AS 사원번호, --1
FIRST_NAME || ' ' || LAST_NAME AS 사원명, --2
HIRE_DATE AS 입사일, --3
DEPARTMENT_ID AS 부서코드, --4
SALARY AS 급여 --5
FROM HR.EMPLOYEES
WHERE SALARY >= 5000 AND SALARY <= 10000; -- WHERE SALARY BETWEEN 5000 AND 10000;
-- ORDER BY 4, 5(SALATY DESC);
-- 번호는 줄 수 / 내림차순으로 정렬
사용예) 2005년 1월 매입정보 중 매입수량이 20개이상인 상품만 조회하시오
Alias는 일자, 상품코드, 매입수량, 매입단가, 금액
SELECT BUY_DATE AS 일자,
BUY_PROD AS 상품코드,
BUY_QTY AS 매입수량,
BUY_COST AS 매입단가,
BUY_QTY * BUY_COST AS 금액
FROM BUYPROD
-- WHERE EXTRACT(YEAR FROM BUY_DATE) = 2005 AND EXTRACT(MONTH FROM BUY_DATE)
= 01 AND BUY_QTY >=20;
WHERE BUY_DATE >= TO_DATE('20050101') AND
BUY_DATE <= TO_DATE('20050131') AND
BUY_QTY >=20
ORDER BY 1;
사용예) 회원테이블에서 직업이 주부이면서 마일리지가 3000이상인 회원을 조회하시오 -- 이거나: OR
Alias는 회원 번호, 회원명, 직업, 마일리지
SELECT MEM_ID AS 회원번호, --회원 번호 라고 쓰면 오류남
MEM_NAME AS 회원명,
MEM_JOB AS 직업,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE MEM_JOB = '주부' AND MEM_MILEAGE >= 3000; --주부는 문자열 임 2. 기타연산자(IN.ANY,SOME,EXISTS,ALL,BETWEEN,LIKE)
- IN 연산자
- 하나의 컬럼과 다수개의 데이터를 비교하여 그 중 어느 하나라도 일치하면 전체 결과가 참(TRUE)이 됨
- =ANY, =SOME으로 치환될 수 있음 (반드시 등호, 부등호가 와야함 IN은 필요없음)
- OR 연산자로 치환 가능
(사용형식)
expr IN(값1[,값2,...])
. 연속적이지 않은 값들을 비교하는 경우 주로 사용
사용예0 사원테이블에서 부서번호 10-30번부서의 사원정보를 조회하시오
Alias는 사원번호, 사원명, 부서번호, 직무코드
=> 이 경우 IN 연산자 보다 BETWEEN 또는 AND 연산자보다 효율적이다.
SELECT EMPLOYEE_ID AS 사원번호,
FIRST_NAME ||' '||LAST_NAME AS 사원명,
DEPARTMENT_ID AS 부서번호,
JOB_ID AS 직무코드
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID >= 10 AND DEPARTMENT_ID <= 30
ORDER BY 3;
-- WHERE DEPARTMENT_ID-BETWEEN 10 AND 30 ORDER BY 3;
-- WHERE DEPARTMENT_ID IN(10,20,30) ORDER BY 3;
-- WHERE DEPARTMENT_ID =1 OR DEPARTMENT_ID =20 OR DEPARTMENT_ID =30 ORDER BY 3;
사용예) 사원 테이블에서 부서번호 30,70,90,100번 부서의 사원정보를 조회하시오
Alias는 사원번호, 사원명, 부서번호, 직무코드 => 이 경우 IN 연산자가 다른 연산자보다 효율적이다.
SELECT EMPLOYEE_ID AS 사원번호,
FIRST_NAME ||' '||LAST_NAME AS 사원명,
DEPARTMENT_ID AS 부서번호,
JOB_ID AS 직무코드
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID =30 OR DEPARTMENT_ID =70 OR DEPARTMENT_ID =90 OR DEPARTMENT_ID =100
ORDER BY 3;
-- =SOME, =ANY(비교대상이 연속적이고 규칙적이지 않을때 사용) WHERE DEPARTMENT_ID =ANY(30,70,90,100)
사용예)사원의 수가 5명 이상인 부서에 속한 사원들을 조회하시오
Alias는 사원번호, 사원명, 부서코드
SELECT EMPLOYEE_ID AS 사원번호,
FIRST_NAME||' '||LAST_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(SELECT A.DID
FROM(SELECT DEPARTMENT_ID AS DID,
COUNT(*) AS CNT
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>=5 A)
ORDER BY 3;
신입 개발자 입니다!!!