SQL , 오라클 , 정의어 DDL, DML, DCL 개념 정리

SQL(Structured Query Language)

데이터 베이스에 저장된 데이터를 조회, 입력, 수정, 삭제하는 등의 조작

또한 테이블을 비롯한 자양한 댇체(시퀀스, 인덱스 등) 생성 및 제어하는 역할

 

SQL의 종류

정의어(DDL)

CREATE : 스키마, 도메인, 테이블, 뷰, 인덱스를 정의, 생성

ALTER : 테이블에대한 정의를 변경, 수정, 조작
DROP : 스키마, 도메인, 테이블, 뷰, 인덱스 제거

CREATE TABLE 테이블명(

속성명 테이터_타입[NOT NULL,

PRIMARY KEY(기본키_속성명, ..),
UNQUE(대체키_속성명 ..,
FOREIGN KEY(외래키_속성명, ..) REFERENCES 참조테이블(기본키_속성명),
CHECK(조건식),

);
- 기본 테이블에 포함될 모든 속성에 대하여 속성명, 속성의 테이터 타입, NOT NULL을 지정한다.
- PRIMARY KEY : 기본키를 구성하는 속성 또는 속성의 집합을 지정한다.
- UNIQUE : 대체키로 사용할 속성 또는 속성의 집합을 지정한다.
- FOREIGN KEY ~ REFERENCES ~ : 외래키 속성과 참조 테이블에 관한 정보를 지정한다. 외래키가 지정되면 참조 무결성의 CASCADE법칙이 적용된다.
- CHECK : 속성값에 대한 제약 사항을 지정한다.

 

ALTER TABLE 테이블명 ADD 속성명 테이타_타입[DEFAULT '기본값];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값];
ALTER TABLE 테이블명 DROP 속성명 [CASCADE];

DROP SCHEMA 스키마명[CASCADE | RESTRICT];
DROP DOMAIN 도메인명[CASCADE | RESTRICT];
DROP TABLE 테이블명[CASCADE | RESTRICT];
DROP VIEW 뷰명[CASCADE | RESTRICT];
DROP INDEX 인덱스명;
- CASCADE : 제거할 개체를 참조하는 다른 모든 개체를 함께 제거한다. 즉, 주테이블의 테이터 제거시, 각각의 외래키와 관계를 맺고 있는 모든 데이터를 함께 제거하는 참조 무결성 제약 조건을 설정하기 위해 사용된다.
- RESTRICT : 다른 개체가 제거할 개체를 잠조중일 경우 제거가 취소된다.
예) <학생> 테이블을 제거하는 SQL문을 작성하시오. 단 <학생> 테이블을 참조하는 모든 데이터도 함께 제거한다.
DROP TABLE 학생 CASCADE;

 

 

 

조작어(DML)

 

SELECT [PREDICATE][테이블명,]속성명[AS 별칭][,테이블명.]속성명, ..]
FROM 테이블명[, 테이블명, ..]
[WHERE 조건]
[GROUP BY 속성명[,속성명, ..]]
[HAVING 조건]
[ORDER BY 속성명[ASC | DESC][, 속성명[ASC | DESC],...]];
=SELECT 절=
->PREDICATE : 검색할 튜플을 제한할 목적으로 사용되는 조건
    ->ALL : 모든 튜플들을 검색할 때 사용된다.(기본값)
    ->DISTINCT : 중복된 튜플을 제거할 때 사용된다.
    ->DISTINCTROW : 중복된 튜플을 제거하지만, 선택된 속성의 값이 아닌 튜플 전체값을 대상으로 할때사용된다.
-> 속성명 : 검색하여 불러올 속성 또는 수식으로서, 두 개 이상의 테이블을 대상으로 검색할 때는 '테이블명,속성명'으로 사용된다.
-> AS : 속성 및 연산의 이름을 다른 제목으로 표시하기 위해 사용된다.
- GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 사용한다. 일반적으로 GROUP BY절은 그룹함수와 함께 사용한다.
- HAVING절 : 그룹에 대한 조건을 기술한다.
- ORDER BY절 : 특정 속성을 기준으로 정렬하여 검색할 때 사용한다.
    ->속성명 : 정렬의 기준이 되는 속성명을 기술한다.
    ->[ASC | DESC] : ASC는 오름차순, DESC는 내림차순

 

 

- 기본검색 -
예1) <사원>테이블의 모든 튜블 검색
- SELECT * FROM 사원;

예2) <사원>테이블에서 부서만 검색하되, 같은 부서는 한번만 검색
- SELECT DSTINCT 부서 FROM 사원;

예3) <사원>테이블에서 이름,문자열 "월급은",기본급에 10을 더한 값을 검색
- SELECT 이름,"월급은",기본급+10 FROM 사원;

- 조건 지정 검색 -
예4) <사원>테이블에서 부서가 '기획'인 튜플검색
- SELECT * FROM 사원 WHERE 부서='기획';

예5) <사원>테이블에서 부서는 '기획'이고, 기본급이 110보다 큰 튜플을 검색
- SELECT * FROM 사원 WHERE 부서='기획' AND 기본급 > 110;

예6) <사원>테이블에서 부서가 '기획'이거나 '인터넷'인 튜플을 검색
- SELECT * FROM 사원 WHERE 부서='기획' OR 부서='인터넷';
- SELECT * FROM 사원 WHERE 부서 IN('기획','인터넷');

예7) <사원>테이블에서 이름의 첫 번재 글자가 '김'인 모든 튜플을 검색
- SELECT * FROM 사원 WHERE 이름='김%';

예8) <사원>테이블에서 기본급이 85에서 95사이인 튜플을 검색
- SELECT * FROM 사원  WHERE 기본급 BETWEEN 85 AND 95;

예9) <사원>테이블에서 주소가 NULL인 튜플을 검색
- SELECT * FROM 사원 WHERE 주소 IS NULL;

- 정 렬 -
예10) <사원>테이블에서 부서가 '기획'인 튜플을 검색하되, 이름을 기준으로 오름차순 정렬
- SELECT * FROM 사원 WHERE 부서='기획' ORDER BY 이름;

예11) <사원>테이블의 모든 튜플들을 검색하되, 부서를 기준으로 오름차순 정렬하고 같은 부서에 대해서는 이름기준으로 내림차순 정렬
- SELECT * FROM 사원 ORDER BY 부서 ASC, 이름 DESC;

- 그룹 검색 -
예12) <사원>테이블에서 부서별로 기본급의 평균을 구하여 부서를 기준으로 오름차순 정렬
- SELECT 부서,AVG(기본급) AS 평균 FROM 사원 GROUP BY 부서 ASC;

예13) <사원>테이블에서 기본급이 100이상인 사원이 2명 이상인 부서와 그 부서의 전체 사원수 검색
- SELECT 부서,COUNT(*) AS 사원수 FROM 사원
    WHERE 기본급 >= 100 GROUP BY 부서 HAVING COUNT(*) >=2;

- 하위 질의 -
예14) <여가활동>테이블에서 취미가 '나이트댄스'인 사원에 대해 <사원>테이블에서 해당 사원의 이름과 주소를 검색
- SELECT 이름,주소 FROM 사원
    WHERE 이름 IN (SELECT 이름 FROM 여가활동 WHERE 취미='나이트댄스');

예15) 여가활동을 하지 않는 사원들을 검색
- SELECT * FROM 사원 WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);

- 복수 테이블 검색 -
예16)경력이 10년 이상인 사원의 이름,부서,취미,경력을 검색
- SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
    FROM 사원, 여가활동
    WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;

- 통합 질의 -
예17) 사원들의 명단이 <임원>테이블과 <직원>테이블에 저장되어 있다. 두 테이블을 통합하는 SQL문을 작성. 단, 같은 레코드가 중복되어 나오지 않게 한다.
- SELECT * FROM 임원 UNION SELECT * FROM 직원;

 

* INSERT 문 *
INSERT 문은 테이블에 새로운 튜플을 삽입할 때 사용하는 명령문이다.
= 표기 형식 =
INSERT INTO 테이블명[(속성명1,속성명2, ..)]
VALUES (데이터1, 데이터2, ..);

- 대응하는 속성과 데이터는 개수와 데이터 타입이 일치해야 한다.
- 테이블의 모든 속성에 대해 입력할 때는 속성명을 생략할 수 있다. 단, 이때에는 CREATE TABLE문에서 기술된 속성 순으로 속성값들을 지정해야 한다.
- SELECT문을 이용하여 다른 테이블의 검색 결과를 삽입할 수 있다.
예) <사원> 테이블에 이름이 '이순신'이고,부서가 'DB;인 사원을 삽입하는 SQL 문 작성
INSERT INTO 사원(이름, 부서) VALUES('이순신','DB');
예) <사원> 테이블에 ('장보고','기획',#07/11/20#,'우암동',90) 을 삽입하는 SQL문 작성.
INSERT INTO 사원 VALUES('장보고','기획',#07/11/20#,'우암동',90);
예) <사원> 테이블에 있는 편집 부서의 모든 튜플을 편집부원(이름,생일,주소,기본급) 테이블에 삽입하는 문 작성.
INSERT INTO 편집부원(이름,생일,주소,기본급)
SELECT 이름,생일,주소,기본급 FROM 사원 WHERE 부서 = '편집';

* DELETE 문 *
DELETE 문은 테이블에 있는 튜플들 중에서 특정 튜플을 삭제할 때 사용하는 명령문이다.
= 표기 형식 =
DELETE FROM 테이블명 WHERE 조건;
- 모든 튜플을 삭제할 때는 WHERE절을 생략한다.
예) <사원> 테이블에서 이름이 '임꺽정'인 튜플을 삭제하는 SQL문
DELETE FROM 사원 WHERE 이름='임꺽정';
예) <사원> 테이블의 모든 튜플을 삭제하는 SQL 문.
DELETE FROM 사원;

* UPDATE 문 *
UPDATE문은 테이블에 있는 튜플들 중에서 특정 튜플의 내용을 갱신할 때 사용하는 명령문이다.
= 표기 형식 =
UPDATE 테이블명 SET 속성명 = 데이터[, 속성명 = 데이터, ..] WHERE 조건;
예) <사원> 테이블에서 홍길동의 주소를 '이월면' 으로 갱신하는 SQL문
UPDATE 사원 SET 주소='이월면' WHERE 이름='홍길동';
예) <사원> 테이블에서 황진이의 부서를 '기획'으로 변경하고 기본급을 50,000원 인상하는 SQL문.
UPDATE 사원 SET 부서='기획', 기본급=기본급+50,000 WHERE 이름='황진이'

조인

 

* JOIN의 개념 *
- JOIN은 두 개의 릴레이션에 대해 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환한다.
- JOIN은 크게 INNER JOIN과 OUTER JOIN으로 구분된다.
- JOIN은 일반적으로 FROM절에 기술하지만, 릴레이션이 사용되는 어느 곳에서나 사용할 수 있다.

* INNER JOIN *
- INNER JOIN은 일반적으로 EQUI JOIN과 NON EQUI JOIN으로 구분된다.
- EQUI JOIN은 JOIN 대상 테이블에서 공콩 속성을 기준으로 '='(equal)비교를 통해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN방법이다.
- EQUI JOIN에서 JOIN조건이 '='일 때 동일한 속성이 두 번 나타나게 되는데, 이 중 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL JOIN이라 한다.
- EQUI JOIN에서 연결 고리가 되는 공통 속성을 JOIN속성이라고 한다.

= WHERE 절을 이용한 EQUI JOIN 표기 형식 =
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ..
FROM 테이블명1, 테이블명2, ..
WHERE 테이블명1.속성명 = 테이블명2.속성명;

= NATURAL JOIN을 이용한 EQUI JOIN의 표기 형식 =
SELECT [테이블명1.], [테이블명2.]속성명, ..
FROM 테이블명1 NATURAL JOIN 테이블명2;

= JOIN ~ USING 절을 이용한 EQUI JOIN의 표기 형식 =
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ..
FROM 테이블명1 JOIN 테이블명2 USING(속성명);

예) <학생> 테이블과 <학과> 테이블에서 학과코드 값이 같은 튜플을 JOIN하여 학번, 이름, 학과코드, 학과명을 출력하는 SQL문

- WHERE절 이용 -
SELECT 학번,이름,학과코드,학과명
FROM 학생,학과
WHERE 학생.학과코드 = 학과.학과코드;

- NATURAL JOIN -
SELECT 학번,이름,학과코드,학과명
FROM 학생 NATURAL JOIN 학과;

- JOIN ~ USING -
SELECT 학번,이름,학과코드,학과명
FROM 학생 JOIN 학과 USING(학과코드);

* OUTER JOIN *
OUTER JOIN은 릴레이션에서 JOIN조건에 만족하지 않더라도 결과로 출력하기 위한 JOIN방법으로, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있다.
- LEFT OUTER JOIN -
    -INNER JOIN의 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.
= 표기 형식 =
<1>
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ..
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
<2>
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ..
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명(+);

- RIGHT OUTER JOIN -
    - INNER JOIN의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우축 항의 릴레이션에 있는 튜플들에 JULL값을 붙여서 INNER JOIN의 결과에 추가한다.
= 표기 형식 =
<1>
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ..
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
<2>
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ..
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명(+) = 테이블명2.속성명;

- FULL OUTER JOIN -
    - LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합쳐 놓은 것이다.
    - INNER JOIN의 결과를 구한 후, 좌측 항의 릴레이션의 튜플들에 대해서 우측 항의 릴레이션의 어떤 튜플과도 맞지 않는 튜플들에 NULL값을 붙여서 INNER JOIN의 결과에 추가한다. 그리고 유사하게 우측항의 릴레이션의 튜플들에 대해 좌측 항의 릴레이션의 어떤 퓨플과도 맞지 않는 튜플들에 NULL값을 붙여서 INNER JOIN의 결과에 추가한다.
= 표기 형식 =
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ..
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

예) <학생> 테이블과 <학과>테이블에서 학과코드 값이 같은 튜플을 JOIN하여 학번,이름,학과코드,학과명을 출력하는 SQL문 (학과코드가 입력되지 않은 학생도 출력)
<1>
SELECT 학번,이름,학과코드,학과명
FROM 학생 LEFT OUTER JOIN 학과 ON 학생.학과코드 = 학과.학과코드;
<2>
SELECT 학번,이름,학과코드,학과명
FROM 학생, 학과 WHERE 학생.학과코드 = 학과.학과코드(+);

* SELF JOIN *
SELF JOIN은 같은 테이블에서 두 개의 속성을 연결하여 EQUI JOIN을 하는 JOIN방법이다.
= 표기 형식 =
<1>
SELECT [별칭1.]속성명, [별칭1.]속성명, ..
FROM 테이블명1 [AS] 별칭1 JOIN 테이블명1 [AS] 별칭2
ON 별칭1.속성명 = 별칭2.속성명;
<2>
SELECT [별칭1.]속성명, [별칭1.]속성명, ..
FROM 테이블명1 [AS] 별칭1,  테이블명1 [AS] 별칭2
WHERE 별칭1.속성명 = 별칭2.속성명;

예) 학생 테이블을 SELF JOIN하여 선배가 있는 학생과 선배의 이름을 구하는 SQL문
<1>
SELECT A.학번, A.이름, B.이름  AS 선배
FROM 학생 A JOIN  학생 B ON A.선배=B.학번;
<2>
SELECT A.학번, A.이름, B.이름  AS 선배
FROM 학생 A ,학생 B WHERE A.선배=B.학번;

제어어(DCL)

 

?

 

MySQL에는 locate()와 position()라는 함수가 존재한다.
함수만 해석한다면 위치를 반환하라 정도? 맞는말이다 위치를 반화하는것이다 하지만 비교하는 문자의 값이 비교할 데이터와 일치해야만 위치를 반환한다.
locate()함수와 positiion()함수는 같은 기능을한다.

* locate();함수사용법 *
- select locate('ayoe','qqkyoeqq');
문자'a'이후의 값'yoe'는 일치하지만 첫문자'a'와'k'가 일치하지 않기 때문에 결과값은 '0'이된다.
- select locate('kyoe','qqkyoeqq');
위와 같이 sql문을 작성하면 반환되는 값은 3이 된다. 'kyoe'라는문자가 'qqkyoeqq'에 속해있기때문에 그결과는 참이되고 문자'kyoe'의 위치를 반환하게 되는것이다.

* position();함수 사용법 *
- select position('kyoe' IN 'qqkyoeqq');
결과값은 locate()함수와 동일하다.