데이터베이스 실습2

0. 작업 환경

• 운영 체제 : 우분투 16.05
• DBMS : MySQL

1. My SQL 설치

MySQL 을 설치하는 방법을 소개한다. 나는 우분투에 MySQL을 설치할 것이다.

• ctrl + alt + t 로 터미널창을 열고 다음의 명령어를 입력한다.

sudo apt-get update
sudo apt-get install mysql-server
mysql_secure_installation

• 중간에 password 를 설정해주는 부분이 있다. 비밀번호를 복잡하게 하는 것은 상식이다.
• mysql_secure_installation 은 추가적인 보안설정을 하는 명령어이므로 지금은 생략해도 된다.

2. MySQL의 구조

• 정보는 표에 저장된다. 표가 늘어날 것이다. 그러면 많아진 표를 정리정돈할 필요성이 생긴다.

• 파일의 폴더같은 것을 MySQL에서는 스키마라고 한다.

• 이 스키마들이 많아지고 이 스키마들의 집합을 저장한 곳이 데이터베이스 서버이다.

3. MySQL 서버 접속

장점

• 보안이 좋다. 자체적인 보안체계를 가지고 있기때문에 보안이 좋다.
• 사용자에 따라 차등적인 권한을 부여할 수 있다.

서버 접속 완료 장면

4. 스키마(Schema)의 사용

• 스키마 생성 명령

  CREATE DATABASE firstdb;
  

• 스키마 삭제 명령

  DROP DATABASE firstdb;
  

  기타 명령어들은 검색하면 쉽게 찾아볼 수 있다.

5. SQL과 테이블 구조

• SQL : Structured Query Language (구조화된 질의 언어)

• 쉬우면서도 중요한 언어

• 표

행 : row , record

열 : column

6. 테이블의 생성

(MySQL cheat sheet)

• 테이블을 생성하고 싶은 스키마로 진입하여 테이블을 생성
• 정리

AUTO_INCREMENT : 자동으로 1씩 증가하게 해준다.

기타 어떤 자료형을 쓸 지 등은 cheat sheet 을 참고할 것.

• STUDENT TABLE 을 생성(CREATE) 하고 ALTER TABLE로 기본키를 정의한 상태

• 외래키의 제약 조건을 추가 할 수 없다는 에러가 뜸.

고려할 수 있는 원인

1. 제한 사항이 참조하는 테이블 혹은 인덱스가 아직 조냊하지 않는 경우
2. 제한 사항 참조 내 테이블 혹은 인덱스에 대해 따옴표를 잘못 사용하는 경우
3. 제한 사항 내 로컬 키, 외래 테이블 혹은 컬럼에 오타가 있는 경우
4. 제한 사항이 참조하는 컬럼이 외래 컬럼과 동일한 타입 혹은 길이를 갖지 않는 경우
5. 외래 객체가 어떤 종류의 키도 아닌 경우
6. 참조된 컬림이 가장 왼쪽에 있지 않을 때, 외래키가 멀티 컬럼의 PK 혹은 UK인경우

등

• MySQL 에서는 같은 column 이름을 가지고 있어도 명세를 해주어야만 한다.

7. CRUD

Create Read Update Delete

• create 와 read 가 특히 중요함

8. INSERT 구문

INSERT INTO 테이블명 VALUES(‘값’,’값’,….);

INSERT INTO (속성1, 속성2) VALUES(‘값’,’값’);

• 한국어 설정은 my.cnf 변경 후

ALTER TABLE (테이블명) convert to charset utf8;
ALTER TABLE 테이블명 COVERT TO CHRSET UTF8;

• 여러 데이터 동시에 insert 하기

INSERT INTO 테이블 이름 (속성1, 속성2)
VALUES
('값1','값2'),
('값3','값4');

• STUDENT TABLE

• ENROL TABLE

9. SELECT 구문

• 기본 구조

SELECT 열_리스트

FROM 테이블_리스트

WHERE 조건

;

• 예제 (교재 139쪽)

mysql> SELECT Sname, Sno
    -> FROM STUDENT
    -> WHERE Dept = '컴퓨터';
mysql> SELECT Sname, Sno
    -> FROM STUDENT
    -> WHERE Dept = '컴퓨터';

1. 검색 결과에 중복 레코드의 제거

1. 테이블의 열 전체를 검색하는 경우 (INSERT 구문의 각 테이블 볼 때 사용)
2. 조건 검색

학생 테이블에서 학과가 ‘컴퓨터’이고 학년이 4인 학생의 학번과 이름을 검색하라

1. 순서를 명세하는 검색

등록 테이블에서 중간 성적이 90점 이상인 학생의 학번과 과목 번호를 검색하되 학번에 대해서는 내림차순으로, 또 같은 학번에 대해서는 과목 번호의 오름차순으로 검색하라.

1. 산술식, 문자 스트링, 새로운 열 이름이 명세된 검색

등록 테이블에서 과목 번호가 ‘C312’ 인 중간 성적에 3점을 더한 점수를 ‘힉번’, ‘중간성적=’이란 텍스트 내용을 ‘시험’, 그리고 ‘점수’라는 열 이름으로 검색하라.

1. 복수 테이블로부터의 검색

: 관계 데이터베이스 시스템이 다른 유형의 데이타베이스 시스템에 비해 강력한 점은 둘 이상의 테이블을 조인시킬 수 있다는 것이다.

과목 번호 ‘C413’에 등록한 학생의 이름, 학과, 성적을 검색하라 (조인 프리디킷 사용)

SQL에서는 이 조인이 필요한 테이블을 미리 사용자가 FROM ㅈ러에서 직접 다음과 같은 세 가지 형식으로 명세할 수 있도록 지원하고 있다.

1. 테이블1 JOIN 테이블2 ON 조건식

1. 테이블1 JOIN 테이블2 USING(열_리스트)

1. 테이블1 NATURAL JOIN 테이블2

1. 자기 자신의 테이블에 조인하는 검색

같은 학과 학생들의 학번을 쌍으로 검색하라. 단, 첫 번째 학번은 두 번째 학번보다 작게 하라.

1. 집계 함수를 이용한 검색

학생 테이블에 학생 수가 얼마인가를 검색하라.

학번이 300인 학생이 등록한 과목은 몇 개인가?

과목 ‘C413’에 대한 중간 성적의 평균은 얼마인가?

1. GROUP BY를 이용한 검색

과목별 기말 성적의 평균을 검색하라

GROUP BY 는 논리적으로 FROM 절에 있는 테이블을 GROUP BY 절에 명세된 열의 값에 따라 그룹으로 분할한다.

1. HAVING 을 사용한 검색

3명 이상 등록한 과목의 기말 평균 성적을 검색하라

GROUP BY 절이 생략되면 테이블 전체를 하나의 그룹으로 취급한다.

1. 부속 질의문(Subquery)을 사용한 검색

과목 번호 ‘C413’을 등록한 학생 이름을 검색하라.

과목 번호 ‘C413’에 등록하지 않은 학생의 이름을 검색하라.

학생 ‘정기태’와 같은 학과에 속하는 학생의 이름과 학과를 검색하라.

등록 테이블에서 학번이 500인 학생의 모든 기말 성적보다 좋은 학기말 성적을 받은 학생의 학번과 과목 번호를 검색하라

1. LIKE 를 사용하는 검색

과목 번호가 C 로 시작하는 과목의 과목 번호와 과목이름을 검색하라.

1. NULL을 사용한 검색

학과가 NULL인 학생의 학번과 이름을 검색하라.

14 EXISTS를 사용하는검색

과목 ‘C413’에 등록한 학생의 이름을 검색하라.

등록하지 않은 학생을 검색할 때는 ‘NOT EXISTS’를 사용하녀 된다.

1. UNION이 관련된 검색

3학년이거나 과목 ‘C324’에 등록한 학생의 학번을 검색하라.

일반 집합론의 합집합과 같다. 따라서 중복되는 튜플은 제거한다.

10. UPDATE 구문

UPDATE 테이블

SET {열_이름=산술식}’+

[WHERE 조건];

WHERE 절이 명세되면 조건을 만족하는 모든 레코드들이 SET절에 지시된대로 변경된다.

1. 하나의 레코드 변경

학번이 300인 학생의 학년을 2로 변경하라.

‘정기태’ 학년이 1에서 2로 변한 것을 확인할 수 있다.

1. 복수의 레코드 변경

‘컴퓨터’과 과목의 학점을 1학점씩 증가시켜라.

1. 부속 질의문을 이용한 변경

‘컴퓨터’과 학생의 기말 성적을 5점씩 가산하라.

모든 4학년 학생의 학과를 ‘데이타베이스’과목을 개설한 학과로 갱신하라.

11. INSERT 구문

기존 테이블에 행을 삽입할 경우에는 다음과 같은 형식의 INSERT문을 사용한다.

INSERT

INTO 테이블[(열_이름_리스트)]

VALUES (열_값_리스트);

또는

INSERT

INTO 테이블[(열_이름_리스트)]

SELECT문;

1. 레코드의 직접 삽입

학번: 600, 이름 : ‘박상철’, 학년 : 1, 학과 : ‘컴퓨터’인 학생을 삽입하라.

1. 부속 질의문(Subquery)를 이용한 레코드 삽입

학생 테이블에서 ‘컴퓨터’과 학생의 학번,이름,학년을 검색하여 테이블 컴퓨터에 삽입하라

INSERT
INTO COMPUTER(Sno,Sname,Year)
SELECT Sno, Sname,Year
FROM STUDENT
WHERE Dept='컴퓨터';

12. DELETE 구문

SQL 테이블에서의 삭제는 튜플을 대상으로 함.

DELETE

FROM 테이블

[WHERE 조건];

1. 하나의 레코드 삭제

학번이 100인 학생을 삭제하라.

1. 복수의 레코드 삭제

등록 테이블의 모든 행을 삭제하라

13. SQL 뷰

뷰는 기본적으로 다른 테이블로부터 유도된 이름을 가진 가상 테이블을 말한다.

13-1. 뷰의 생성

CREATE VIEW 문

CREATE VIEW 뷰_이름 [(열_이름_리스트)] AS SELECT문

[WITH CHECK OPTION];

• CSTUDENT 뷰의 생성

• 뷰 정의문에서 열 이름이 명세되지 않으면 AS SELECT 문에 나오는 열의 이름을 그대로 상속받는다. 열의 이름을 받으시 명세하여야 하는 경우도 있다.

다음과 같이 정의할수도 있다.

CREATE VIEW DEPTSIZE
	AS SELECT Dept, COUNT(*) AS Size
		FROM STUDENT
		GROUP BY Dept;

• 뷰는 다음과 같이 조인해서 정의할 수도 있다.

STUDENT 테이블의 학생 이름, 학과, ENROL 테이블의 기말성적(기말성적은 90점 이상)을 속성으로 하는 HONOR 뷰를 생성하라.

• 정의된 뷰를 사용하여 다른 뷰를 정의할 수도 있다.

13-2. 뷰의 제거

DROP VIEW 뷰_이름 {RESTRICT

CASCADE};

• DEPTSIZE는 다른 곳에서 참조되고 있지 않는 한 데이타베이스에서 제거되어 없어진다.

• HONOR 뿐만 아니라 이 뷰를 기반으로 정의된 뷰 COMHONOR도 자동으로 제거된다. (아래와 같이 검색하려고 했으나 오류가 난다.)

13-3. 뷰의 조작 연산

SELECT문을 사용할 수 있다. 하지만 INSERT, DELETE, UPDATE문을 뷰에 사용하는데에는 제한이 많다.

• 뷰의 변경이 허용되지 않는 경우

1. 뷰의 열이 상수나 산술 연산자 또는 함수가 사용된 산술식으로 만들어졌을 때
2. 집계함수(COUNT,SUM,AVG,MAX,MIN)가 관련되어 정의되었을 때
3. DISTINCT, GROUP BY, HAVING이 사용되어 정의되었을 때
4. 두 개 이상의 테이블이 관련되어 정의되었을 때
5. 변경할 수 없는 뷰를 기초로 정의되었을 때

Tweet Share