06월 20일 목 | SW활용 04 - SQL 데이터타입과 테이블설계

4. SQL 데이터타입과 테이블설계

SQL 데이터타입

Oracle DB Server 서비스 확인

1. 시작

서비스 -> OracleServerXE 실행되고 있나 확인

2. Oracle 자체

시작 -> 모든 프로그램 -> Oracle Database 11g

예) www.soldesk.com -> 도메인

 

Oracle 명령어 입력

1. SQLPLUS

시작->cmd->sqlplus ip/pw

2. Run Command Line

SQL>connect >id,pw > connected.

3. SQL Developer

별도의 프로그램, 회원가입 후 설치한 다음 사용

4. 웹브라우저

시작 -> Oracle DB 11g XE -> Get Started

127.0.0.1:8080/apex

이런 식으로 나온다

http://127.0.0.1:8080/apex

http://localhost:8080/apex

*자기 자신의 PC를 지칭하는 IP와 도메인

127.0.0.1 / localhost

내 서버구축 후 이용할 수 있다.

http://127.0.0.1

http://localhost

를 통해 할 수 있다.

 

 

SQL 명령어 입력

 

DDL (CREATE, DROP, ALTER)

CREATE USER

CREATE TABLE

CREATE SEQUENCE

 

DROP USER

DROP TABLE

 

ALTER USER

ALTER TABLE 

//자주 ALTER를 사용한다면 무언가 문제가 있으니 시스템 설계를 의심해야한다.

참고) https://seaweedisland.tistory.com/38?category=839313

 

 

1) SUNGJUK 테이블 생성

어제 만들어둔 동명의 테이블이 있어서 DROP으로 삭제하고 새로 만들어 주었다.

CREATE TABLE SUNGJUK(UNAME VARCHAR(10) NOT NULL --빈값 허용X

,KOR INT NOT NULL

,ENG INT NOT NULL

,MAT INT NOT NULL

,AVG INT);

 

2) 테이블 구조 수정

ALTER TABLE --테이블 수정하기

ALTER TABLE 테이블명 RENAME COLUMN 원래컬럼명 TO 변경할컬럼명;

ALTER TABLE SUNGJUK RENAME COLUMN KOR TO KOREA;

DESC SUNGJUK; --바뀐내역 순서대로 확인하기

ALTER TABLE SUNGJUK DROP(KOREA); --컬럼하나 삭제하기

ALTER TABLE SUNGJUK ADD (컬럼명 데이터타입)ㅇ

ALTER TABLE SUNGJUK ADD (TOTAL INT);

 

 

연습문제 1) SUNGJUK 테이블을 아래와 같이 수정하시오.

1) UNAME 칼럼의 글자수를 50개로 수정

2) UNAME 칼럼을 NULL 조건으로 수정

 

ALTER TABLE SUNGJUK MODIFY(UNAME VARCHAR(50));

 

ALTER TABLE SUNGJUK MODIFY(UNAME NULL);

 

COMMIT

명령 완료

 

ROLLBACK

명령 취소

 

--sqlplus는 자동커밋된다. 

 

 

DML (INSERT, UPDATE, DELETE, SELECT)

※UNAME칼럼 20칸으로 변경

[SUNGJUK테이블 샘플데이터]

 

INSERT INTO SUNGJUK(UNAME,KOR,ENG,MAT)

VALUES('지성',50,60,30);

 

INSERT INTO SUNGJUK(UNAME,KOR,ENG,MAT)

VALUES('이해찬',30,30,40);

 

INSERT INTO SUNGJUK(UNAME,KOR,ENG,MAT)

VALUES('이보영',90,80,80);

 

INSERT INTO SUNGJUK(UNAME,KOR,ENG,MAT)

VALUES('이종석',70,40,20);

 

INSERT INTO SUNGJUK(UNAME,KOR,ENG,MAT)

VALUES('이다희',50,60,90);

 

INSERT INTO SUNGJUK(UNAME,KOR,ENG,MAT)

VALUES('윤상현',90,45,30);

 

INSERT INTO SUNGJUK(UNAME,KOR,ENG,MAT)

VALUES('장준혁',90,100,98);

 

INSERT INTO SUNGJUK(UNAME,KOR,ENG,MAT)

VALUES('이순재',85,90,88);

 

목록조회 

SELECT * FROM SUNGJUK;

 

SELECT KOR,ENG,MAT FROM SUNGJUK;

 

SELECT UNAME, AVG FROM SUNGJUK;

 

물리적 스키마(테이블) - CREATE, INSERT 등을 직접 입력한 값들

논리적 스키마(테이블) - 조인, 뷰등을 통해서 만들어진 값들

 

--AS (생략가능)

특정 칼럼명, 테이블명을 일시적으로 리네임

SELECT KOR AS KOREA

,ENG ENGLISH

,MAT MATH

FROM SUNGJUK; 

 

--COUNT() 함수 

SELECT COUNT(UNAME) FROM SUNGJUK;

이렇게 논리적으로 나온 결과 (실제 값을 직접 입려한게 아닌 결과)를 논리적 테이블이라고 한다.

 

SELECT COUNT(UNAME) AS CNT FROM SUNGJUK;

SELECT COUNT(UNAME) CNT FROM SUNGJUK;

 

SELECT COUNT(KOR) AS KOREA FROM SUNGJUK;

SELECT COUNT(AVG) AS AVERAGE FROM SUNGJUK;

아직 평균을 연산하지 않았기 때문에 값이 NULL인데 그것을 세지 않고 0을 출력하였다.

NULL은 COUNT의 적용대상이 아니다.

 

--전체 레코드(행) 갯수

SELECT COUNT(*) FROM SUNGJUK; --전체 행의 갯수

SELECT COUNT(*) AS CNT FROM SUNGJUK;

 

--집계함수

SELECT SUM(KOR) FROM SUNGJUK; --국어점수를 전부 합산하였다.

SELECT SUM(KOR),MAX(KOR),MIN(KOR) FROM SUNGJUK; 

DB에서 웬만한 걸 다 해놓은 다음 자바로 넘겨야한다.

안그러면 너무 복잡해짐

 

SELECT SUM(KOR) AS HAP

,MAX(KOR) AS MAXIMUM

,MIN(KOR) AS MINIMUM

,AVG(KOR) AS AVERAGE

FROM SUNGJUK; 

 

연습문제 2) 국,영,수 각 과목의 평균점수를 조회하시오.

SELECT AVG(KOR)

,AVG(ENG)

,AVG(MAT)

FROM SUNGJUK;

 

정렬 (SORT)

순서대로 재배치하는 것이다. 

오름차순 ASCENDING ASC     --기본값

내림차순 DESCENDING DESC  --

형식) ORDER BY 칼럼1, 칼럼2, 칼럼3~~~  조건대로 거르기

 

칼럼1을 기준으로 내림차순 정렬하고

칼럼1값이 동일하다면 칼럼2를 기준으로 오름차순 정렬하라.

칼럼2값이 동일하다면 칼럼3을 기준으로 기본값인 오름차순 정렬하라.

ORDER BY 칼럼1 DESC,

              칼럼2 ASC,

              칼럼3, ~~

 

SELECT UNAME FROM SUNGJUK ORDER BY UNAME;  

SELECT UNAME FROM SUNGJUK ORDER BY UNAME ASC;  --ASC생략가능 오름차순은 기본값

SELECT UNAME FROM SUNGJUK ORDER BY UNAME DESC;  --내림차순

 

SELECT UNAME,KOR FROM SUNGJUK ORDER BY UNAME,KOR ASC;  --ASC생략가능 오름차순은 기본값

SELECT UNAME,KOR FROM SUNGJUK ORDER BY UNAME,KOR DESC;  --내림차순

 

1차정렬: SELECT UNAME,KOR FROM SUNGJUK ORDER BY UNAME,KOR DESC;  --내림차순

2차정렬: SELECT KOR, UNAME FROM SUNGJUK ORDER BY KOR, UNAME;

 

3차정렬: SELECT KOR,ENG,MAT,UNAME FROM SUNGJUK ORDER BY KOR,ENG,MAT;

 

 

--칼럼명이 생략되면 values()값은 생성순으로 입력

그러나 생략은..추천하지 않음.

 

INSERT INTO SUNGJUK

VALUES('손흥민',90,65,55,68);

 

--4차정렬

SELECT KOR,ENG,MAT,UNAME FROM SUNGJUK ORDER BY KOR,ENG,MAT,UNAME;

 

조건절

SQL의 조건절은 WHERE,HAVING,ON 등 이 있다.

 

WHERE 조건절

조건에 만족하는 레코드만 대상이다.

특정한 레코드를 수정, 삭제, 조회하기 위해서 사용한다.

-산술연산자: + = * /

-비교연산자: > < >= <=   같지않다!=, 같다= (대입과 비교 둘다 가능)

-논리연산자: &&, ||

 

DUAL 테이블

결과값을 일시적으로 출력할 때 유용한 임시 테이블

SELECT 5+3 FROM DUAL;

SELECT 5-3 FROM DUAL;

SELECT 5*3 FROM DUAL;

SELECT 5/3 FROM DUAL;

그럼 나머지는 어떻게 구할까?

SELECT MOD(5,3) FROM DUAL; --이렇게하면 된다.

 

HAVING 조건절

GROUP BY라는 명령어와 같이 쓰인다.

 

ON 조건절

 

 

WHERE 조건절

산술연산자

 

국어점수 90이상 조회

SELECT KOR,UNAME FROM SUNGJUK WHERE KOR>=90;

커서가 순서대로 조건에 맞는 것을 탐색 후 출력.

수학점수 50미만 조회

SELECT MAT,UNAME FROM SUNGJUK WHERE MAT<50;

이름이 '이보영' 조회

SELECT UNAME FROM SUNGJUK WHERE UNAME='이보영';

성이 '이'씨인 사람 조회

SELECT UNAME FROM SUNGJUK WHERE UNAME LIKE '이%';

이름이 '이종석'인 행의 평균을 구하시오

UPDATE SUNGJUK SET AVG=(KOR+ENG+MAT)/3 WHERE UNAME='이종석';

이전에 만들었던 평균의 셀은 비어있었다. UPDATE로 값을 추가했다.

SELECT UNAME,AVG FROM SUNGJUK WHERE UNAME ='이종석';

평균값이 들어가 있는 갯수 계산

SELECT AVG,UNAME FROM SUNGJUK;

SELECT COUNT(AVG) FROM SUNGJUK;

비어있는 AVG칼럼의 갯수를 구하시오.

SELECT UNAME,AVG FROM SUNGJUK WHERE AVG=NULL;

이렇게 하면 될 것 같은데 안된다.

NULL값을 조회할때는 =가 아니라 IS 연산자를 써야하기 때문이다.

SELECT UNAME,AVG FROM SUNGJUK WHERE AVG IS NULL;

IS연산자를 사용하였더니 정상적인 결과가 도출하였다. 

 

AVG만 검색하면 0이 나온다. NULL을 세지 않기 때문에

전체 행을 검색했을 때

SELECT COUNT(*) FROM SUNGJUK WHERE AVG IS NULL;

결과는 7로 맞는 갯수가 나온다.

 

비어있지 않은 AVG칼럼의 갯수를 구하시오.

SELECT COUNT(*) FROM SUNGJUK WHERE AVG IS NOT NULL;

논리연산자

국영수가 50 60 90인 행의 평균을 구하시오

UPDATE SUNGJUK SET AVG=(KOR+ENG+MAT)/3 WHERE KOR=50 AND ENG=60 AND MAT=90;

SELECT UNAME,AVG FROM SUNGJUK WHERE KOR=50 AND ENG=60 AND MAT=90;

 

국영수 모두 50미만의 행의 평균을 구하시오

UPDATE SUNGJUK SET AVG=(KOR+ENG+MAT)/3 WHERE KOR<50 AND ENG<50 AND MAT<50;

SELECT UNAME,AVG FROM SUNGJUK WHERE KOR<50 AND ENG<50 AND MAT<50;

 

비어있는 평균값을 모두 채우시오

UPDATE SUNGJUK SET AVG=(KOR+ENG+MAT)/3 WHERE AVG IS NULL;

SELECT UNAME,AVG FROM SUNGJUK

 

평균 60~69점 사이의 평균, 이름 조회

SELECT UNAME,AVG FROM SUNGJUK WHERE AVG>=60 AND AVG<=69;

SELECT COUNT(*) FROM SUNGJUK WHERE AVG>=60 AND AVG<=69;

또는

SELECT UNAME,AVG FROM SUNGJUK WHERE AVG BETWEEN 60 AND 69;

SELECT COUNT(*) FROM SUNGJUK WHERE AVG BETWEEN 60 AND 69; --BETWEEN A AND B 사잇값 구할 때 

국영수 중에서 한 과목이라도 40점 미만인 사람 조회

SELECT UNAME,KOR,ENG,MAT FROM SUNGJUK WHERE KOR<40 OR ENG<40 OR MAT<40;

이름 '윤상현','지성','손예진' 조회

SELECT UNAME,AVG FROM SUNGJUK WHERE UNAME='윤상현' OR UNAME='손예진';

또는

--IN 연산자 (일치하는 값) 찾기

SELECT UNAME,AVG FROM SUNGJUK WHERE UNAME IN('윤상현','지성','손예진');

숫자도 적용할 수 있다.

SELECT UNAME,KOR,AVG FROM SUNGJUK WHERE KOR IN(90);

국어점수가 90점인 사람들만 조회되었다.

 

문자데이터 비슷한 것을 찾기 

--LIKE연산자 : 문자열에서 비슷한 유형을 찾을 때 사용한다.

만능문자 % ?

 

'전'씨 성을 조회하시오.

SELECT UNAME,AVG FROM SUNGJUK WHERE UNAME LIKE '전%';

'현'으로 끝나는 이름을 조회하시오.

SELECT UNAME,AVG FROM SUNGJUK WHERE UNAME LIKE '%현';

'준'자가 들어가는 이름을 조회하시오

SELECT UNAME,AVG FROM SUNGJUK WHERE UNAME LIKE '%준%';

두 글자 중에서 '찬'으로 끝나는 문자열 조회

SELECT UNAME,AVG FROM SUNGJUK WHERE UNAME LIKE '_찬';

세 글자 중에서 '찬'으로 끝나는 문자열 조회

SELECT UNAME,AVG FROM SUNGJUK WHERE UNAME LIKE '__찬';

세 글자 중 가운데 글자가 '지'인 문자열 조회

SELECT UNAME FROM SUNGJUK WHERE UNAME LIKE '_지_';

 

연습문제 3) 자유게시판 검색시 제목 + 내용을 선택하고 검색어 HAPPY를 입력했을 때 레코드를 조회하시오.

SELECT SUBJECT,CONTENT WHERE SUBJECT,CONTENT IN ('happy');

SELECT SUBJECT,CONTENT WHERE LIKE ('happy'); 5글자인 것들 중

SELECT SUBJECT,CONTENT WHERE SUBJECT LIKE ('%happy%') OR CONTENT LIKE ('%happy%');

 

---

트랜잭션

 - 데이터 파일의 내용에 영향을 미치는 모든 거래
 - INSERT, UPDATE, DELETE 쿼리가 사용되는 경우 Transaction 상태가 됩니다.
 - 데이터 변형되면 상황에 따라 복구되어야 하는 상태가 필요한 경우 명령어를 이용하여 최초 상태로 데이터를 돌릴 수 있습니다.
 - COMMIT WORK(COMMIT): 변경된 데이터 확인후 데이터 영역에 적용
 - ROLLBACK WORK(ROLLBACK): 변경된 데이터를 취소하고 원래대로 복구합니다.