SQL(Structured Query Language)

Posted on: 2017년 12월 25일 2017년 12월 25일
Categories: 데이터베이스

DB 설치 : 오토셋 설치( http://autoset.net/xe/ )
실습용 파일
1. 수업용 소스 파일
2. 엑셀 파일

DDL(Data Definition Language)

정의 : 스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 변경 또는 제거할 때 사용
유형
1. CREATE
2. ALTER
3. DROP

CREATE TABLE

CREATE TABLE 학생(
	학번 CHAR(9),
	이름 VARCHAR(10) NOT NULL,
	전공 VARCHAR(5),
	성별 CHAR(1),
	생년월일 DATE,
	PRIMARY KEY(학번),
	FOREIGN KEY(전공) REFERENCES 학과(학과코드)
		ON DELETE SET NULL
		ON UPDATE CASCADE
);

CREATE TABLE 학생(

학번 CHAR(9),

이름 VARCHAR(10) NOT NULL,

전공 VARCHAR(5),

성별 CHAR(1),

생년월일 DATE,

PRIMARY KEY(학번),

FOREIGN KEY(전공) REFERENCES 학과(학과코드)

ON DELETE SET NULL

ON UPDATE CASCADE

);

ALTER TABLE

ALTER TABLE 학생 ADD 나이 INT; ALTER TABLE 학생 DROP COLUMN 생년월일; ALTER TABLE 학생 CHANGE 학번 학번 CHAR( 9 );

1
2
3

ALTER TABLE 학생 ADD 나이 INT;
ALTER TABLE 학생 DROP COLUMN 생년월일;
ALTER TABLE 학생 CHANGE 학번 학번 CHAR( 9 );
DROP TABLE

DROP TABLE 학생;

1

DROP TABLE 학생;
CREATE VIEW

CREATE VIEW 남학생 AS SELECT * FROM `학생` WHERE 성별='남'

1
2

CREATE VIEW 남학생 AS
SELECT * FROM `학생` WHERE 성별='남'
1. 뷰(VIEW) : 하나 이상의 기본 테이블로 부터 유도되는 이름을 갖는 가상테이블
2. 학생 테이블에서 성별이 ‘남’인 학생들을 ‘남학생’이란 이름의 뷰로 생성
DROP VIEW

DROP VIEW 남학생;

1

DROP VIEW 남학생;
CREATE INDEX

CREATE INDEX 이름_IDX ON 학생(이름 DESC);

1
2

CREATE INDEX 이름_IDX
ON 학생(이름 DESC);
1. 인덱스(INDEX) : 검색을 빠르게 하기 위해 만든 보조적인 데이터 구조
2. MySql의 information_schema DB의 INNODB_SYS_INDEXES 테이블에 추가된다.
CREATE TRIGGER

CREATE TRIGGER `학년정보_tri` BEFORE INSERT ON `학생` FOR EACH ROW IF NEW.학년 = '' THEN SET NEW.학년='신입생'; END IF

1
2
3
4
5
6

CREATE TRIGGER `학년정보_tri`
BEFORE INSERT ON `학생`
FOR EACH ROW
IF NEW.학년 = '' THEN
SET NEW.학년='신입생';
END IF
1. 트리거(TRIGGER) : 데이터베이스 시스템에서 데이터의 입력, 갱신, 삭제 등의 이벤트가 발생할 때 마다 자동적으로 수행되는 사용자 정의 프로시저
2. 새로운 레코드가 삽입되기 전 학년에 값이 없다면 학년을 ‘신입생’으로 바꾸어 입력

DML(Data Manipulation Language)
1. 정의 : 데이터베이스 사용자가 질의어를 통해 저장된 데이터를 실질적으로 관리하는데 사용
2. 유형
  1. INSERT
  2. DELETE
  3. UPDATE
3. INSERT 문
  
  INSERT INTO 학생 (이름, 학번, 전공, 생년월일) VALUES ('홍길동','201712345','컴퓨터','2017/12/23');
  
  1
  2
  3
  4
  
  INSERT INTO 학생
  (이름, 학번, 전공, 생년월일)
  VALUES
  ('홍길동','201712345','컴퓨터','2017/12/23');
4. DELETE 문
  
  DELETE FROM 학생 WHERE 학번='201712345';
  
  1
  2
  
  DELETE FROM 학생
  WHERE 학번='201712345';
5. UPDATE 문
  
  UPDATE 학생 SET 전공='CS' WHERE 학번='201712345';
  
  1
  2
  3
  
  UPDATE 학생
  SET 전공='CS'
  WHERE 학번='201712345';

SELECT 문

일반형식

SELECT 이름, 학번, 전공, 생년월일 FROM 테이블 WHERE 이름 LIKE '김*' ORDER BY 전공 ASC, 이름 ASC [GROUP BY 전공 HAVING COUNT(*) > 2];

1
2
3
4
5
6

SELECT 이름, 학번, 전공, 생년월일
FROM 테이블
WHERE 이름 LIKE '김*'
ORDER BY 전공 ASC, 이름 ASC
[GROUP BY 전공
HAVING COUNT(*) > 2];
그룹 검색
1. 예제1
  
  SELECT 부서, AVG(기본급) AS 평균 FROM `사원` GROUP BY 부서;
  
  1
  2
  3
  
  SELECT 부서, AVG(기본급) AS 평균
  FROM `사원`
  GROUP BY 부서;
2. 예제2
  
  SELECT 부서, COUNT(*) AS 사원수 FROM `사원` WHERE 기본급 >= 100 GROUP BY 부서 HAVING COUNT(*) >=2;
  
  1
  2
  3
  4
  5
  
  SELECT 부서, COUNT(*) AS 사원수
  FROM `사원`
  WHERE 기본급 >= 100
  GROUP BY 부서
  HAVING COUNT(*) >=2;
하위 질의

SELECT 이름, 주소 FROM 사원 WHERE 이름 IN (SELECT 이름 FROM 여가활동 WHERE 취미='나이트댄스');

1
2
3

SELECT 이름, 주소
FROM 사원
WHERE 이름 IN (SELECT 이름 FROM 여가활동 WHERE 취미='나이트댄스');

복수 테이블 검색

SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
FROM 사원, 여가활동
WHERE 여가활동.경력 >= 10 AND 사원.이름=여가활동.이름

SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력

FROM 사원, 여가활동

WHERE 여가활동.경력 >= 10 AND 사원.이름=여가활동.이름

양쪽 테이블에 이름이 같은 레코드만 검색됨
WHERE를 이용한 INNER JOIN의 한 방법

통합(UNION) 검색

SELECT * FROM 임원 UNION SELECT * FROM 직원;

1
2
3

SELECT *
FROM 임원
UNION SELECT * FROM 직원;
연산자
1. 비교 연산자 : = <> > < >= <=
2. 논리 연산자 : AND OR NOT
3. LIKE 연산자 : * % ? _ #
4. 연산자 우선 순위 : 산술 연산자 > 관계 연산자 > 논리 연산자
그룹 함수
1. COUNT(속성명) :
2. MAX(속성명) :
3. MIN(속성명) :
4. SUM(속성명) :
5. AVG(속성명) :

JOIN
1. INNER JOIN : 2개의 테이블에 대해 연관된 튜플을 결합하여 조건에 맞는 것만 반환
  1. WHERE 이용
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원, 여가활동 WHERE 사원.이름=여가활동.이름
    
    1
    2
    3
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
    FROM 사원, 여가활동
    WHERE 사원.이름=여가활동.이름
  2. NATURAL JOIN 이용
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원 NATURAL JOIN 여가활동;
    
    1
    2
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
    FROM 사원 NATURAL JOIN 여가활동;
  3. JOIN ~ USING 이용
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원 JOIN 여가활동 USING(이름);
    
    1
    2
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
    FROM 사원 JOIN 여가활동 USING(이름);
2. OUTER JOIN : 조건에 맞지 않는 것도 반환
  1. LEFT OUTER JOIN
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원 LEFT OUTER JOIN 여가활동 ON 사원.이름 = 여가활동.이름
    
    1
    2
    3
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
    FROM 사원 LEFT OUTER JOIN 여가활동
    ON 사원.이름 = 여가활동.이름
  2. RIGHT OUTER JOIN
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원 RIGHT OUTER JOIN 여가활동 ON 사원.이름 = 여가활동.이름
    
    1
    2
    3
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
    FROM 사원 RIGHT OUTER JOIN 여가활동
    ON 사원.이름 = 여가활동.이름
  3. FULL OUTER JOIN : MySql은 지원하지 않음
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원 FULL OUTER JOIN 여가활동 ON 사원.이름 = 여가활동.이름
    
    1
    2
    3
    
    SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
    FROM 사원 FULL OUTER JOIN 여가활동
    ON 사원.이름 = 여가활동.이름
3. SELF JOIN : 같은 테이블을 연결
  1. WHERE 이용
    
    SELECT A.학번, A.이름, B.이름 AS 선배 FROM 선후배 A, 선후배 B WHERE A.선배 = B.학번;
    
    1
    2
    3
    
    SELECT A.학번, A.이름, B.이름 AS 선배
    FROM 선후배 A, 선후배 B
    WHERE A.선배 = B.학번;
  2. JOIN 이용
    
    SELECT A.학번, A.이름, B.이름 AS 선배 FROM 선후배 A JOIN 선후배 B ON A.선배 = B.학번;
    
    1
    2
    3
    
    SELECT A.학번, A.이름, B.이름 AS 선배
    FROM 선후배 A JOIN 선후배 B
    ON A.선배 = B.학번;
DCL(Data Control Language)
1. 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용하는 언어
2. 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용
3. 유형
  1. COMMIT : 조작 작업을 영구적으로 반영하여 완료
  2. ROLLBACK : 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복귀
  3. GRANT : 사용자에게 사원 권한 부여
  4. REVOKE : 사용자에게 사원 권한 취소

실습 : [사원], [여가활동] 테이블을 만들어 아래 표의 값을 저장하시오.

[사원] 테이블

이름	부서	생일	주소	기본급
홍길동	기획	04/04/61	효자동	120
임꺽정	인터넷	10/09/69	석사동	80
황진이	편집	07/21/75	후평동	100
김선달	편집	10/22/73	효자동	90
성춘향	기획	02/20/64	후평동	100
장길산	편집	03/11/67	고잔동	120
일지매	기획	04/29/78	원곡동	110
강호동	인터넷	12/11/80		90

[여가활동] 테이블

이름 취미 경력

김설달 당구 10

성춘향 나이트댄스 5

일지매 택견 15

임꺽정 씨름 8

whan0623

517

이름	취미	경력
김설달	당구	10
성춘향	나이트댄스	5
일지매	택견	15
임꺽정	씨름	8