1일차 (#임포트# 기본문법)

#04월06일 (수)
1.  SQL 이해 및 개념
2. SQL사용법
3. 명령문 구조
4. 실습 및 구조
 -3개 테이블 추가 구축
 -Query문 실습에 따른 명령문 익히기 

---

#SQL (Structured Query Language)
▶칼럼과 로우로 구성된 데이터 구조

▶데이터 구조
DQL

TML

DML(Data Manipulation language)_조작어
select, insert, delete, update, 
DDL(Data definition language)_정의어
create, drop, alter
DCL(Data Controll Laguage)_제어어
rollback, commit,


#테이블 임포트
--테이블 폴더 우클릭-> 데이터 임포트->데이터파일 임포트(찾아보기)->다음->열정의에서 칼럼 한번씩 클릭해서 읽혀줄것-> 다음 완료

#기본 문법
/*테이블 전체 선택*/
SELECT * FROM EMP;

모든 테이블 확인
FROM USER_TABLES;

/*테이블 만들기*/
CREATE TABLE SUBJECT(
    SUB_NO      CHAR(3),
    SUB_NAME    VARCHAR2(30),
    SUB_PROF    VARCHAR2(12),
    SUB_GRADE   NUMBER(20),
    SUB_DEPT    VARCHAR2(20),
    CONSTRAINT p_sub_no PRIMARY KEY(SUB_NO));

/*테이블 제거*/
DROP TABLE ENROLL;

/*데이터 삽입*/
INSERT INTO STUDENT VALUES(20153075,'옥한빛','기계',1,'C','M',177,80);

/*테이블명 변경*/
SELECT * FROM ENROL;
ALTER TABLE ENROLL RENAME TO ENROL;

/*테이블 제거*/
DROP TABLE STUDENT;

/*테이블 조건으로 확인*/
SELECT * FROM STUDENT WHERE STU_CLASS='C';

--테이블의 구조를 나타내는 명령어
DESC STUDENT;   --학생 테이블의 구조를 보여줌(DESCRIBE)

--특정열(컬럼)의 내용 검색 명령어
--학생테이블에서 학번과 학생이름을 출력하라
SELECT STU_NO, STU_NAME FROM STUDENT;

--학생테이블에서 학생들의 이름과 몸무게,키를 출력하라
SELECT STU_NAME, STU_WEIGHT, STU_HEIGHT FROM STUDENT;

--칼럼의 중복행 제거 명령어(DISTINCT 특별한) 
SELECT DISTINCT STU_DEPT FROM STUDENT;

--수식을 포함한 검색문
SELECT STU_NO, SUB_NO, ENR_GRADE, ENR_GRADE+10 FROM ENROL;

--연결연산자
SELECT STU_DEPT || '과 ' || STU_NAME ||'입니다' AS 학과성명 FROM STUDENT;

--조건 검색
--학생테이블에서 기계과 학생들의 학생이름과 학번을 검색
SELECT STU_NAME, STU_NO FROM STUDENT WHERE STU_DEPT='기계';

--학생테이블에서 기계이면서 학년이 2학년인 학생들의 학생이름과 학번을 검색
SELECT STU_NAME, STU_NO
FROM STUDENT 
WHERE STU_DEPT='기계' AND STU_GRADE=2;

--조건절에서 범위 조건을 가지고 검색할 수 있음
--학생테이블에서 몸무게가 60~70키로인 학생들을 검색
SELECT * 
FROM STUDENT
WHERE STU_WEIGHT BETWEEN 60 AND 70;

--학생테이블에서 김씨성을 가진 데이터를 검색(와일드카드)
SELECT *
FROM STUDENT 
WHERE STU_NAME LIKE '김%';

---

데이터 임포트

1. 데이터베이스 안에 프로젝트 폴더 우클릭 IMPORT

2.찾아보기를 통해서 데이터 파이르 선택

3. 다음

\

4.다음

5. 칼럼 명들을 한번씩 다 클릭해 줌으로써 읽혀줘야 정상적으로 임포트됨

6.완료

---

1일차 수업내용

 

오름차순(Acending)

0

1

2

 

내림차순(Decending)

10

9

8

 

/*1일차*/
--select TABLE_NAME
FROM USER_TABLES;

/*테이블 전체 선택*/
SELECT * FROM EMP;
SELECT * FROM SALGRADE;
SELECT * FROM DEPT;

/* Ctrl+/ = 주석*/

/*테이블 만들기*/
CREATE TABLE SUBJECT(
    SUB_NO      CHAR(3),
    SUB_NAME    VARCHAR2(30),
    SUB_PROF    VARCHAR2(12),
    SUB_GRADE   NUMBER(20),
    SUB_DEPT    VARCHAR2(20),
    CONSTRAINT p_sub_no PRIMARY KEY(SUB_NO));

CREATE TABLE STUDENT(
    STU_NO      CHAR(9),
    STU_NAME    VARCHAR2(12),
    STU_DEPT    VARCHAR2(20),
    STU_GRADE   NUMBER(1),
    STU_CLASS   CHAR(1),
    STU_GENDER  CHAR(1),
    STU_HEIGHT  NUMBER(5,2),
    STU_WEIGHT  NUMBER(5,2),
    CONSTRAINT p_stu_no PRIMARY KEY(STU_NO));
    
 SELECT*FROM SUBJECT;
        
CREATE TABLE ENROL(
    SUB_NO      CHAR(3),
    STU_NO      CHAR(9),
    ENR_GRADE   NUMBER(3),
    CONSTRAINT p_course PRIMARY KEY(sub_no,stu_no));
    
/*테이블 제거*/
DROP TABLE ENROLL;
    
/*데이터 삽입*/
INSERT INTO STUDENT VALUES(20153075,'옥한빛','기계',1,'C','M',177,80);
INSERT INTO STUDENT VALUES(20153088,'이태연','기계',1,'C','F',162,50);
INSERT INTO STUDENT VALUES(20143054,'유가인','기계',2,'C','F',154,47);
INSERT INTO STUDENT VALUES(20152088,'조민우','전기전자',1,'C','M',188,90);
INSERT INTO STUDENT VALUES(20142021,'심수정','전기전자',2,'A','F',168,45);
INSERT INTO STUDENT VALUES(20132003,'박희철','전기전자',3,'B','M',NULL,63);
INSERT INTO STUDENT VALUES(20151062,'김인중','컴퓨터정보',1,'B','M',166,67);
INSERT INTO STUDENT VALUES(20141007,'진현무','컴퓨터정보',2,'A','M',174,864);
INSERT INTO STUDENT VALUES(20131001,'김종헌','컴퓨터정보',3,'C','M',NULL,72);
INSERT INTO STUDENT VALUES(20131025,'옥성우','컴퓨터정보',3,'A','F',172,63);

INSERT INTO SUBJECT VALUES('111','데이터베이스','이재영',2,'컴퓨터정보');
INSERT INTO SUBJECT VALUES('110','자동제어','정순정',2,'전기전자');
INSERT INTO SUBJECT VALUES('109','자동화설계','박민영',3,'기계');
INSERT INTO SUBJECT VALUES('101','컴퓨터개론','강종영',3,'컴퓨터정보');
INSERT INTO SUBJECT VALUES('102','기계공작법','김태영',1,'기계');
INSERT INTO SUBJECT VALUES('103','기초전자실험','김유석',1,'전기전자');
INSERT INTO SUBJECT VALUES('104','시스템분석설계','강석현',3,'컴퓨터정보');
INSERT INTO SUBJECT VALUES('105','기계요소설계','김명성',1,'기계');
INSERT INTO SUBJECT VALUES('106','전자회로실험','최영민',3,'전기전자');
INSERT INTO SUBJECT VALUES('107','CAD응용실습','구봉규',2,'기계');
INSERT INTO SUBJECT VALUES('108','소프트웨어공학','권민성',1,'컴퓨터정보');

INSERT INTO ENROL VALUES('101','20131001',80);
INSERT INTO ENROL VALUES('104','20131001',56);
INSERT INTO ENROL VALUES('106','20132003',72);
INSERT INTO ENROL VALUES('103','20152088',45);
INSERT INTO ENROL VALUES('101','20131025',65);
INSERT INTO ENROL VALUES('104','20131025',65);
INSERT INTO ENROL VALUES('108','20151062',81);
INSERT INTO ENROL VALUES('107','20143054',41);
INSERT INTO ENROL VALUES('102','20153075',66);
INSERT INTO ENROL VALUES('105','20153075',56);
INSERT INTO ENROL VALUES('102','20153088',61);
INSERT INTO ENROL VALUES('105','20153088',78);
       
/*테이블명 변경*/
SELECT * FROM ENROL;
ALTER TABLE ENROLL RENAME TO ENROL;

/*테이블 제거*/
DROP TABLE STUDENT;
DROP TABLE SUBJECT;
DROP TABLE ENROL;

/*테이블 조건으로 확인*/
SELECT * FROM STUDENT WHERE STU_CLASS='C';

/*테이블 확인*/
SELECT * FROM STUDENT;
SELECT * FROM SUBJECT;
SELECT * FROM ENROL;

/*테이블 임포트*/
--테이블 폴더 우클릭-> 데이터 임포트->데이터파일 임포트(찾아보기)->다음->열정의에서 칼럼 한번씩 클릭해서 읽혀줄것-> 다음 완료

--테이블의 구조를 나타내는 명령어
DESC STUDENT;   --학생 테이블의 구조를 보여줌(DESCRIBE)
DESC SUBJECT;   
DESC ENROL;

--특정열(컬럼)의 내용 검색 명령어
--학생테이블에서 학번과 학생이름을 출력하라
SELECT STU_NO, STU_NAME FROM STUDENT;
--학생테이블에서 학생들의 이름과 몸무게,키를 출력하라
SELECT STU_NAME, STU_WEIGHT, STU_HEIGHT FROM STUDENT;

SELECT * FROM STUDENT;
--칼럼의 중복행 제거 명령어(DISTINCT 특별한) 
SELECT DISTINCT STU_DEPT FROM STUDENT;

--수식을 포함한 검색문
SELECT STU_NO, SUB_NO, ENR_GRADE, ENR_GRADE+10 FROM ENROL;

--칼럼이름을 가지고 별칭(ALIAS) 부여하기(AS 생략가능)
SELECT STU_NO AS ID, STU_NAME AS NAME FROM STUDENT;

--연결연산자
SELECT STU_DEPT || '과 ' || STU_NAME ||'입니다' AS 학과성명 FROM STUDENT;

--조건 검색
--학생테이블에서 기계과 학생들의 학생이름과 학번을 검색
SELECT STU_NAME, STU_NO FROM STUDENT WHERE STU_DEPT='기계';

--학생테이블에서 기계이면서 학년이 2학년인 학생들의 학생이름과 학번을 검색
SELECT STU_NAME, STU_NO
FROM STUDENT 
WHERE STU_DEPT='기계' AND STU_GRADE=2;

--조건절에서 범위 조건을 가지고 검색할 수 있음
--학생테이블에서 몸무게가 60~70키로인 학생들을 검색
SELECT * 
FROM STUDENT
WHERE STU_WEIGHT BETWEEN 60 AND 70;

--학생테이블에서 김씨성을 가진 데이터를 검색(와일드카드)
SELECT *
FROM STUDENT 
WHERE STU_NAME LIKE '김%';

--끝자리가 '중'인 사람만 출력 (언더바 2개)
SELECT STU_NO, STU_NAME, STU_DEPT
FROM STUDENT
WHERE  STU_NAME LIKE '__중';

--가운데 자리가 '수'인 사람만 출력 (언더바 2개)
SELECT STU_NO, STU_NAME, STU_DEPT
FROM STUDENT
WHERE  STU_NAME LIKE '_수_';

--학생테이블에서 2015학번 학생들을 검색하라
SELECT *
FROM STUDENT
WHERE STU_NO LIKE '2015%';

--NULL값의 존재유무에 따라 출력시키는 방법
SELECT *
FROM STUDENT
WHERE STU_HEIGHT IS NULL;
/*WHERE STU_HEIGHT IS NOT NULL;*/

--IN의 기능
--전기 전자과와 기계과의 학생들의 학번과 이름을 검색
SELECT STU_NO, STU_NAME, STU_DEPT
FROM STUDENT
WHERE STU_DEPT IN('전기전자','기계');

--SELECT ~ FROM ~WHERE ~ORDER BY
--학생들의 정보를 학번기준으로 정렬 해서 검색(오름차순 ASC 내림차순DESC)
SELECT STU_NO, STU_NAME
FROM STUDENT
ORDER BY STU_NO DESC;

SELECT STU_NO, STU_NAME
FROM STUDENT
ORDER BY 1 DESC;    --1은 select문의 첫번쨰 칼럼을 기준으로 정렬

--별칭으로 순서화 시키는 명령어
--학생테이블에서 학번과 학생이름그리고 
--몸무게를 5만큼 감소시킨열을 TARGET으로 별칭 이를 기준하여 정렬
SELECT STU_NO, STU_NAME, STU_WEIGHT-5 TARGET
FROM STUDENT
ORDER BY 2 DESC;

SELECT STU_NO, STU_NAME, STU_WEIGHT-5 TARGET
FROM STUDENT
ORDER BY STU_WEIGHT-5;

SELECT STU_NO, STU_DEPT, STU_NAME, STU_WEIGHT-5 TARGET
FROM STUDENT
ORDER BY STU_DEPT, TARGET;  --앞칼럼(STU_DEPT) 먼저 정렬하고 그다음 칼럼(TARGET)이 정렬


--함수에 대한 이야기
--1.단일행함수(숫자함수)
SELECT ROUND(345.678), ROUND(345.678,0), ROUND(345.678,1),
ROUND(345.678,-1)
FROM DUAL;

SELECT TRUNC(345.678), TRUNC(345.678,0),TRUNC(345.678,1)Trunc(345.678,-1)
FROM DUAL;


SELECT CEIL(3.00001), FLOOR(-3.00001)
FROM DUAL;
/*MOD -> 나머지 구하기*/
SELECT MOD(10,3), ABS(-100) 
FROM DUAL;

--2. 문자함수 단일행함수
SELECT LOWER('KOREA'),UPPER('abcdefg'), INITCAP('ename'),
SUBSTR('ABCDEFG',2,3) /*부분추출 2부터 3개,*/,LENGTH('ABCDEFG'),
INSTR('abcdefgabcdefgabc','a',2,2) /*a가 2번째 나왔을때 위치*/,
LPAD('AAA',10,'#'), /*AAA포함 10칸의 공간에 왼쪽으로 빈공간에 #로 채워라*/
RPAD('BBB',10,'%')
FROM DUAL;

--3. 날자함수 단일행 함tn
SELECT SYSDATE FROM DUAL;
SELECT SYSDATE+1 FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'수')FROM DUAL; /*현재 날자에서 가장 가까운 요일의 날자를 알려줌->NEXT*/
SELECT * FROM EMP;
SELECT ENAME, MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP;
--4. 변환함수의 단일행 함수
--TO_NUMBER(), TO_DATE(), TO_CHAR()
SELECT * FROM EMP;
SELECT EMPNO, ENAME,HIREDATE, TO_CHAR(HIREDATE,'YYYY-MM-DD-DAY:HH24')AS 입사년월 /*날자형식 변환*/
FROM EMP;

SELECT TO_CHAR(TO_NUMBER(1234.5678),'9999.9') 
FROM DUAL;

SELECT EMPNO, ENAME
FROM EMP
WHERE HIREDATE = TO_DATE('1980-12-17','YY-MM-DD');

--5. 일반함수인 단일행함수
--NVL(인수1,인수2)
SELECT NVL(STU_HEIGHT,0)
FROM STUDENT;   /*NULL값을 0으로 채움*/

--NVL2(인수1,인수2,인수3)
--만약에 인수1이 NULL이면 인수 3으로 반환 NULL이 아니면 인수2의 값으로 반환
SELECT * FROM EMP;
SELECT ENAME, NVL2(COMM,SAL+COMM,SAL) SALARRY
FROM EMP;

--NULLIF?
--NULLIF(인수1,인수2):인수1=인수2, NULL
SELECT NVL(NULLIF('A','A'),'널값')
FROM DUAL;
--COALESC(인수1,인수2,...인수N)
SELECT COALESCE(NULL,NULL,NULL,10,100,NULL) /*많은 NULL값들 사이에서 NULL이아닌 첫번쨰 값*/
FROM DUAL;

--CASE/DECODE?
--사원직무별 급여인상액 ("SALESMAN' 10%, 'MANAGE' 20%, 'CLERK' 15% 이외 사무직은
--인상액이 없을 떄 계산하는 출력 질의 문은?
SELECT EMPNO, ENAME, SAL,
CASE JOB WHEN 'SALESMAN' THEN SAL*1.1
         WHEN 'CLERK' THEN SAL*1.15
         WHEN 'MANAGE' THEN SAL*1.2
         ELSE SAL
         END AS 급여인상
FROM EMP;    

SELECT EMPNO, ENAME, JOB, SAL,
DECODE(JOB, 'SALESMAN',SAL*1.1,
            'CLERK',SAL*1.15,
            'MANAGER',SAL*1.2)
            AS 인상된급여
FROM EMP;

--그룹함수
--정리를 시켜주는 함수 : AVG, COUNT(*), COUNT(),SUM(),MAX(),MIN(),MIN(),STDDEV()
--VARIANCE()
SELECT MAX(ENR_GRADE),MIN(ENR_GRADE)
FROM ENROL;
/*최대값 최소값*/
SELECT MAX(STU_WEIGHT),MIN(STU_HEIGHT)
FROM STUDENT;

/*전체 행 수, NULL값을 제외한 숫자*/
SELECT COUNT(*), COUNT(STU_HEIGHT)
FROM STUDENT;

--학과별로 그룹화하여 학과별 평균 체중을 구하여라
SELECT STU_DEPT, ROUND(AVG(STU_WEIGHT))
FROM STUDENT
GROUP BY STU_DEPT;

--체중이 50KG 이상 학생들의 학과별 인원수를 구하라
SELECT STU_DEPT, COUNT(*)
FROM STUDENT
WHERE STU_WEIGHT>=50
GROUP BY STU_DEPT;


--다중열 그룹바이절
SELECT STU_DEPT, STU_GRADE, COUNT(*)
FROM STUDENT
GROUP BY STU_DEPT, STU_GRADE;

--HAVING절 : 그룹바이에서 조건이 사용되는 경우에 사용
--기계과 학생들중 학년별 평균 신장이 160 이상인 학년과 평균신장 구하라
SELECT STU_GRADE, AVG(STU_HEIGHT)
FROM STUDENT
WHERE STU_DEPT='기계'
GROUP BY STU_GRADE 
HAVING AVG(STU_HEIGHT)>=130;

SELECT STU_DEPT, MAX(STU_HEIGHT)
FROM STUDENT
GROUP BY STU_DEPT HAVING MAX(STU_HEIGHT)>=175;