추억의 강좌

내소개

추억의 강좌

쌀맛나는세상 2013. 4. 25. 17:30

예전 인포메일과 .. 등등 메일러가 유행했을때 썻던 강좌인가 보다. 인터넷 검색으로 나왔음.

ASP강좌:http://i.kebi.com/~woopo1/ASP/

비베강좌:http://i.kebi.com/~woopo1/vb/(업데이트중)

JSP강좌:http://www.4offline.org (같이 공부하는 사이트...^^)

WAP강좌:http://i.kebi.com/~woopo1/wap/(거의 완료)

DB강좌:http://i.kebi.com/~woopo1/database/(준비중)

network강좌:http://i.kebi.com/~woopo1/network/(준비중) 에 계속 강좌를 올려 놓기로 하겠습니다.

SQL문 총정리

1. 데이터베이스 데이터베이스란 어느 한 조직의 여러 응용 시스템들이 공유할 수 있도록

통합, 저장한 운영DATA의 집합이라 보면 됩니다.

2. 관계형 데이터베이스 관계형 데이터베이스는 1970년 Dr.E.F.Codd 에 의해 정립되었다 관계형 데이터베이스는 관계대수학의

개념으로 체계화 되어있다.

따라서 관계모델 아래에서 데이터는 테이블 구조로 구성되고 테이블 구조는 열이나 필드라 불리는 각각의 데이터 요소로 구성된다.

필드 그룹의 단일 세트(SET)는 열이나 필드로 구성되고 필드그룹의 단일 세트는 레코드 또는 행이라 말한다.

우리가 많이 들어본RDBMS(Relational Database Management Systems)란 이러한 관계형 데이터베이스를 관리하는 시스템을 말하며

ORACLE, SQL Server, DB2… 등 많은 회사들의 제품이 있으며 개발하기 용이하고 사용하기 편리하여 널리 사용 되고 있습니다.

3. 관계형 데이터베이스의 기능 - 저장된 정보를 관리한다 -

데이터에 대한 접근을 제어한다 - 데이터를 입력, 조회, 수정, 삭제 할 수 있는 기능을 제공한다

4. 관계형 데이터베이스의 장점

- 중복성(Redundancy)이 감소

- 불일치(Inconsistency)의 최소화

- 데이터의 공유(Shared)

- 표준화(Standard) 가 가능

- 보안(Security) 유지

- 무결성(Integrity) 유지

- 요구사항의 충돌을 해결

1. 데이터베이스 용어( Database Terminology )

- 테이블(Table ) RDBMS의 기본적인 데이터 저장구조이며 행과 열로 이루어 지며 테이블은 이름으로 구분됩니다. Ex) 급여(Pay) 테이블

- 행(Row) 테이블내의 열(Column)의 값들의 조합이 행이며 보통 "Record"라고도 한다 Ex) 사번+급여 - 열(Column) 테이블 내의 특정 데이터를 말한다. 데이터의 이름, 타입 과 길이를 지정할수 있다. Ex) 사번 char (10) - 필드(field) 데이터가 들어 있는 곳.

- 주키(Primary key) 테이블 내에 행을 다른 행과 유일하게 인식할 수 있게 해주는 열 또는 열들의 값을 말하며 테이블에는 반드시 주키가 있어야 한다.

- 외부키(Foreign Key ) 다른 테이블의 주키를 참조하거나 같은 테이블의 주키를 참조하는 열 또는 열들이다.

- 인덱스(Index) 테이블내의 데이터의 조회 속도를 향상시키기 위해 열을 일정한 순서로 정렬하여 실데이타가 있는 주소를 연결하는 구조를 인덱스라 한다.

- 시컨스(Sequence) 사번과 같이 순차 증가또는 감소하는 키값을 생성해준다

- 뷰(View) 가상으로 같은 테이블 또는 다른 테이블들의 데이터를 사용자에 따라 보여준다

- 동의어(Synonym) 테이블을 다른 이름으로 참조하여 사용할 수 있습니다.

1. SQL(Structured Query Language ) 이란 SQL 은 관계형 데이터베이스에서 데이터를 조작하고 검색할 때 사용되는 언어로 ANSI standard를 따르며 대부분의 RDBMS에서 사용할 수 있다.

각 RDBMS별로 각자 편의에 맞추어 추가된 기능들을 사용하기도 한다.

2. SQL의 기능

- 데이터베이스 구조의 수정

- 시스템 보안의 변경

- 데이터베이스 정보 조회

- 데이터베이스 내용 갱신

- 데이터베이스 사용자 권한 추가

3. SQL 주요 Commands

- Data 조회 SELECT

- Data Manipulation (DML) INSERT, UPDATE, DELETE

- Data Definition 제어 CREATE, ALTER, DROP

- Transaction 제어 COMMIT, ROLLBACK

- 보안(Security) GRANT, REVOKE

4. SQL 작성법

- SQL 문은 대소 문자를 구분하지 않는다. 그러나 비교하는 값의 대소문자는 구분한다

- 여러 줄을 작성 하거나 한줄로 작성하여도 무방하다

- SQL 문의 가독성(READBILITY)이 좋도록 작성하는 것이 좋다

- SQL 명령문을 생략하거나, 약어로 작성 사용할 수 없다

- SQL 명령문의 마지막에 세미콜론(;) 이나 슬래쉬(/)를 쓴다

1. SELECT 문 데이터를 조회 할 때 사용 한다.

사용법은 다음과 같다.

SELECT [DISTINCT] {*,행이름 [ alias],… } FROM TABLE명 [WHERE 조건 ] [ORDER BY { 컬럼 } [ASC| DESC] SELECT 다음에 조회할 컬럼의 이름을 쓴다.

DISTINCT 는 중복된 컬럼을 제거 해준다.

FROM 뒤에는 해당되는 테이블을 쓴다.

WHERE 절에는 조회하고자 하는 조건을 작성한다.

ORDER BY 조건으로 선택된 ROW를 정렬(SORT)한다.

DEPT 라는 TABLE의 전체를 조건 없이 조회 한다.

SQL> SELECT * FROM DEPT ;

DEPTNO DNAME LOC

--------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

전체 컬럼을 조회할 때는 * 을 사용할 수 있다. FROM 다음에는 조회하고자 하는 테이블의 이름을 쓴다. SQL문은 구문의 끝에 세미콜론(;)이 SQL 문이 끝났음을 나타낸다. 항상 SQL문의 끝에 세미콜론을 넣어 준다. 참고로 SQL 명령어는 대소문자 구분이 없다.

- 부서번호(DEPTNO) 만 조회해서 보고싶은 경우 SELECT 문 뒤에 조회할 열 이름을 써준다

SQL> SELECT DEPTNO FROM DEPT; DEPTNO --------- 10 20 30 40 1. ORDER BY 구문을 이용한 정렬 - 부서번호 내림차순으로 전체 테이블을 조회하고 싶은 경우 ORDER BY DEPTNO DESC 를 추가 한다. SQL> SELECT * 2 FROM DEPT 3 ORDER BY DEPTNO DESC; DEPTNO DNAME LOC - -------- -------------- ------------- 40 OPERATIONS BOSTON 30 SALES CHICAGO 20 RESEARCH DALLAS 10 ACCOUNTING NEW YORK 결과는 위와 같이 부서 번호가 내림차순으로 정렬되어 나온다. 아무 조건없이 Order by,를 주면 오름차순으로 정렬된다 2. 조회 컬럼 이름의 변경 부서번호를 다른 이름으로 이해하기 편하게 조회하고자 할 때 다음과 같이 SQL을 작성 실행하면 된다. SQL> SELECT DEPTNO 부서번호, DNAME 부서이름 FROM DEPT; 부서번호 부서이름 --------- -------------- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS 조회하고자 하는 컬럼 옆에 콤마(, ) 두지 않고 다른이름을 써주면 된다. 1. 산식(Arithmetic Expression‍)의 사용 데이터를 조회 하면서 계산을 하고자 할 때 데이터 타입이 숫자 나 날짜인 경우 산식을 사용하여 조회할 수 있다. 덧셈(+), 뺄셈(-), 곱셈(*), 나눗셈(/) 과 같이 사칙연산을 모두 사용할 수 있다. Salgrad 테이블에는 급여등급 , 등급내 최저 월급, 최고 월급 으로 구성되어 있다. 이 급여 등급 테이블에서 각 등급에서 받을 수 있는 최고 월급 과 최저 월급의 차이 금액을 구해 본다. SQL> select grade, hisal - losal from salgrade; GRADE HISAL-LOSAL --------- ----------- 1 500 2 199 3 599 4 999 5 6998 위의 조회된 결과를 보기 좋게 하려면 아래와 같이 하면 된다. SQL> select grade 등급, hisal - losal 급여차이 from salgrade; 등급 급여차이 --------- --------- 1 500 2 199 3 599 4 999 5 6998 1. 둘 이상의 컬럼을 연결 시 둘 이상의 컬럼을 연결하여 데이터를 조회하고자 하는 경우 " ||"을 사용하여 연결 할 수 있다. SQL> select dname || loc from dept; DNAME||LOC --------------------------- ACCOUNTINGNEW YORK RESEARCHDALLAS SALESCHICAGO OPERATIONSBOSTON 2. 연결 된 두 컬럼 사이에 문장을 넣을시 다음과 같이 하면 된다. SQL> select dname||' in ' || loc from dept; DNAME||'IN'||LOC ------------------------------- ACCOUNTING in NEW YORK RESEARCH in DALLAS SALES in CHICAGO OPERATIONS in BOSTON 위의 쿼리를 좀더 보기 좋게 바꾸면 AS 문을 써서 아래와 같이 할 수 있다 SQL> select dname|| ' in ' || loc AS " 부서위치 " from dept; 부서위치 ------------------------------- ACCOUNTING in NEW YORK RESEARCH in DALLAS SALES in CHICAGO OPERATIONS in BOSTON 1. Where 절 사용하기 Where절에 조건문, 논리연산자 과 SQL operators를 넣어서 테이블에서 선택하는 데이터를 선별 할 수 있다. 비교연산자 : = , > , < ,>=, <= 논리연산자 : AND, OR,NOT SQL OPERATORS: BETWEEN….AND… 두 값 사이의 값 IN ( 'A', 'B' ) 괄호안의 값과 매치되는 값 LIKE 문자가 일치되는 경우 IS NULL 데이터가 NULL VALUE를 갖는 경우 - 비교연산자 사용 비교연산자를 사용하여 부서테이블에서 부서번호가 10 인경우 부서 이름을 조회해 본다. 참고로 문자열을 비교시에는 ' '을 사용한다. 이 테이블의 부서번호는 문자열이 아니다. SQL> SELECT DNAME 2 FROM DEPT 3 WHERE DEPTNO = 10; DNAME -------------- ACCOUNTING 참고> ORACLE 은 DESC 함수를 이용하여 데이터 TYPE을 알 수 있다. SQL> DESC DEPT; 이름 Null? 유형 ------------------------------- -------- ---- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) - BETWEEN 사용법 부서 번호 10 부터 30 번까지 조회하려면 아래와 같이 한다. SQL> SELECT * FROM DEPT 2 WHERE DEPTNO BETWEEN 10 AND 30 ; DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO - IN 사용법 부서번호가 10 , 20 인 경우 조회시 아래와 같다. SQL> SELECT * FROM DEPT 2 WHERE DEPTNO IN ( 10 , 20 ); DEPTNO DNAME LOC --------- -------------- ------------- 20 RESEARCH DALLAS 10 ACCOUNTING NEW YORK 참고> 위의 조회를 조건문 으로 구해 보면 아래와 같다. SQL> SELECT * FROM DEPT 2 WHERE DEPTNO = 10 OR DEPTNO = 20; DEPTNO DNAME LOC --------- -------------- ------------- 20 RESEARCH DALLAS 10 ACCOUNTING NEW YORK SQL> SELECT * FROM DEPT 2 WHERE DEPTNO >= 10 AND DEPTNO <= 20; DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS - LIKE 사용법 일치 하는 패턴의 문자열을 가진 데이터를 조회한다. SQL> SELECT * FROM DEPT 2 WHERE LOC LIKE 'DALLAS'; DEPTNO DNAME LOC --------- -------------- ------------- 20 RESEARCH DALLAS LOC 지역이 "D"로 시작되는 경우 '%'을 사용하면 편리하다 SQL> SELECT * FROM DEPT 2 WHERE LOC LIKE 'D%' ; DEPTNO DNAME LOC -------- -------------- ------------- 20 RESEARCH DALLAS LOC 값안에 'A' 자가 들어 가는 경우를 조회하는 경우 SQL> SELECT * FROM DEPT 2 WHERE LOC LIKE '%A%' ; DEPTNO DNAME LOC --------- -------------- ------------- 20 RESEARCH DALLAS 30 SALES CHICAGO ` 1. SQL FUNCTION SQL에는 사용하기 편리하게 여러 함수를 제공한다. 함수에 대해 간략히 알아 본다. - COUNT 함수 COUNT(*) 는 SQL이 수행되어 보여주는 행의 수를 리턴 한다. 먼저 DEPT 테이블을 전체 조회하여 보면 4개의 행이 나온다 SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 위의 SQL문에 COUNT 함수를 사용하면 4를 리턴 한다 SQL> SELECT COUNT(*) FROM DEPT; COUNT(*) --------- 4 따라서 카운트 함수를 사용하여 부서가 4개 있다는 것을 알 수 있다. - LOWER() 소문자로 보여준다 - UPPER() 대문자로 보여준다 - INITCAP() 앞 이니셜은 대문자로 나머지는 소문자로 보여준다. 위의 세 함수는 영문 데이터를 조회 시 변경 하여 볼 수 있도록 해준다. 위의 함수를 사용하여 데이터를 조회해 보도록 하자. SQL> SELECT LOWER(DNAME) FROM DEPT; LOWER(DNAME) -------------- accounting research sales operations SQL> SELECT UPPER(DNAME) FROM DEPT; UPPER(DNAME) -------------- ACCOUNTING RESEARCH SALES OPERATIONS SQL> SELECT INITCAP(LOC) 2 FROM DEPT; INITCAP(LOC) ------------- New York Dallas Chicago Boston - AVG() 표현식 전체나 각각의 평균값 - MAX() 표현식에서 제일 큰 값 - MIN() 표현식에서 가장 작은 값 - SUM() 표현식에서 나온 값의 합 - ROUND() 반올림 처리 함수 \ EMP 라는 테이블에서 위의 함수를 사용해 보도록 하자. EMP TABLE에서 받는 급여(SAL) 의 평균을 AVG() 함수를 써서 간단히 구할 수 있다. SQL> SELECT AVG(SAL) 2 FROM EMP; AVG(SAL) --------- 2073.2143 평균급여의 값을 반올림 처리 하려면 아래와 같이 ROUND()를 써주면 된다. SQL> SELECT ROUND(AVG(SAL)) 2 FROM EMP; ROUND(AVG(SAL)) --------------- 2073 MAX() 와 MIN() 함수를 이용하여 최고 급여와 최저 급여를 구하고 그 차이를 계산해 보도록 하자. SQL> SELECT MAX(SAL), MIN(SAL), MAX(SAL)-MIN(SAL) 2 FROM EMP; MAX(SAL) MIN(SAL) MAX(SAL)-MIN(SAL) --------- --------- ----------------- 5000 800 4200 - GROUP BY 위의 사원(EMP) 테이블에서 업무별로 최고 급여가 얼마인지 알고 싶을 때 GROUP BY 함수를 쓰면 쉽게 구할 수 있다. SQL> SELECT JOB, MAX(SAL) 2 FROM EMP 3 GROUP BY JOB; JOB MAX(SAL) --------- --------- ANALYST 3000 CLERK 1300 MANAGER 2975 PRESIDENT 5000 SALESMAN 1600 - HAVING GROUP BY 를 사용한 조회 문에 어떤 조건을 주고 싶을 때 HAVING을 이용하면 된다. 업무별로 받는 최고 금액이 2000 이상이 경우만 조회 해 보려 할 때 는 다음과 같이 한다. SQL> SELECT JOB,MAX(SAL) 2 FROM EMP 3 GROUP BY JOB 4 HAVING MAX(SAL) > 2000 ; JOB MAX(SAL) --------- - -------- ANALYST 3000 MANAGER 2975 PRESIDENT 5000 1. INSERT 문 테이블에 새로운 데이터를 INSERT 명령으로 추가 할 수 있다. 즉 새로운 열을 삽입할 수 있다. 먼저 CREATE 문을 사용하여 간단한 TEST TABLE 을 작성해 본다. SQL> CREATE TABLE TEST 2 ( ID VARCHAR2(10) NOT NULL , 3 NAME VARCHAR2(20), 4 AGE NUMBER NOT NULL ); CREATE TABLE 명령어를 이용하여 간단한 TABLE에 데이터를 삽입 하기로 하자. INSERT ( INTO ) TABLE명 ( 컬럼이름, 컬럼이름,…) VALUES ( 값1, 값2… ) SQL> INSERT INTO TEST 2 VALUES ( 'KING', '홍길동' , '29'); 삽입된 데이터를 조회 해보았다. SQL> SELECT * FROM TEST; ID NAME AGE ---------- -------------------- --------- KING 홍길동 29 테이블에 특정 값만 INSERT 할 경우는 아래와 같다. SQL> INSERT INTO TEST ( ID, AGE) 2 VALUES ( 'QUEEN', 27); 테이블 명 옆에 괄호를 주고 입력할 대상 필드 명을 나열 한다. 그리고 VALUE 에 해당 값들을 써준다. 조회 해보면 확인 할 수 있다. SQL> SELECT * FROM TEST WHERE ID = 'QUEEN'; ID NAME AGE ---------- -------------------- --------- QUEEN 27 1. UPDATE 문 테이블에 들어 있는 데이터를 변경 할 때 사용하는 명령어 이다. 사용법은 아래와 같다. UPDATE 테이블 명 SET COLUMN 이름 = 변경될 값 WHERE ( 조건 ) ; TEST TABLE 에 ID 가 QUEEN 인 RECORD의 NAME 값이 현재 SPACE 인데 NAME 필드의 값을 변경해 보자. SQL> UPDATE TEST 2 SET NAME = '김희선' 3 WHERE ID = 'QUEEN'; 조회하여 확인 해보자. SQL> SELECT ID, NAME,AGE 2 FROM TEST 3 WHERE ID = 'QUEEN'; ID NAME AGE ---------- -------------------- --------- QUEEN 김희선 27 이번에는 여러 열의 값을 한번에 변경 해본다. WHERE 절에 조건을 주지 않으면 해당 TABLE 의 전체 열의 행들이 변경되므로 주의 해야 한다. 여기서는 조건을 주지 않고 AGE를 모두 18 살로 변경해 보자. SQL> UPDATE TEST 2 SET AGE = 18 ; TEST TABLE 안의 AGE 값만 조회 해보자, 모두 변경되었을 것이다. SQL> SELECT AGE 2 FROM TEST; AGE --------- 18 18 1. DELETE 문 테이블에 들어 있는 데이터를 삭제 할 때 사용한다. DELETE 테이블 명 ( WHERE 조건 ) 위와 같이 사용하며 WHERE 절에 조건을 명시하지 않으면 테이블에 있는 전체 데이터가 삭제되므로 주의 해야 한다. TEST TABLE에서 ID가 KING 인 RECORD를 삭제 해 보도록 하자. SQL> DELETE TEST 2 WHERE ID = 'KING'; 데이터가 정확히 잘 못 삭제 된 경우 ROLLBACK 명령어를 사용하여 데이터를 복구할 수 있다. SQL> ROLLBACK; 롤백이 완료되었습니다. SQL> SELECT * 2 FROM TEST 3 WHERE ID = 'KING'; ID NAME AGE ---------- -------------------- --------- KING 홍길동 29 정확히 삭제 되었으면 COMMIT 명령어로 확정 한다. SQL> COMMIT; 커밋이 완료되었습니다. 이제 삭제된 데이터는 복구 되지 않는다. SQL 명령어는 SQL 버퍼에서 실행된다 이때 실행된 명령에 의해 값이 변경되었을 경우 실제 데이터베이스의 값이 변경된 것이 아니라 버퍼에 있는 값만 변경된 것 이다. 이 변경된 값을 실제 데이터베이스와 동일하게 맞추어 주는 작업이 COMMIT 이다. 즉 우리가 파일을 수정하면 저장할 것 인지 아닌지를 결정하는 것과 동일한 역할을 한다. 위의 COMMIT 과 ROLLBACK은 데이터 DELETE, INSERT 과 UPDATE 명령어에 모두 사용할 수 있다. 그러나 환경설정을 해 놓은 것에 따라 자동 COMMIT이 이루어져서 데이터의 복구가 되지 않을 수도 있으므로 주의한다. 참고로 TABLE을 완전히 삭제 하는 명령어로 TRUNCATE 가 있다. 하지만 이 명령어를 사용하면 삭제된 테이블이 복구 되지 않으므로 함부로 사용하지 않는다. TRUNCATE TABLE 명;