[MySQL] University DB 예제

CREATE DATABASE `university`;

SHOW databases;

USE `university`;

#DROP TABLE `university`.`student`;
CREATE TABLE `university`.`student`(           # ` ` 은 해도 되고 안해도 되는 것 같다. 속성도.
  `student_id` int unsigned NOT NULL ,
  `name` varchar(20) NOT NULL ,
  `address` varchar(100) NULL ,
  `birth_date` char(8) NULL ,
  `major_id` smallint NULL ,
  `enterance_year` smallint NULL ,
  `leave_yn` bit NULL default 0;
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB;

INSERT INTO `university`.`student`
(student_id, name, address, birth_date, major_id, enterance_year, leave_yn)
VALUES
(2016001, '정소정', '서울시 서초구 방배동 911-1', '19971101', 111, 2016, 0),
(2017053,'성민경','경상북도 대구 수성구 삼익 1-1','19980412',111,2017,1),
(2018111,'노규희','충청남도','19991204',130,2018,1),
(2017105,'박성희','강원도','19980908',130,2017,0);

select * from `university`.`student`;

select * from `university`.`student`
where student_id = 2016001;

select student_id, name from `university`.`student`
where major_id = 111;

UPDATE `university`.`student`
SET
address = '부산특별시 해운대 비치333-3',
leave_yn = 1
WHERE student_id = 2016001;

select * from university.student
where student_id = 2016001;

DELETE FROM university.student
WHERE student_id = 2016001;

select * from university.student 
where student_id = 2016001;

select * from university.student;

CREATE TABLE `university`.`class` (
  `class_id` int unsigned NOT NULL ,
  `name` varchar(20) NOT NULL , 
  `professor_id` int NOT NULL , 
  `major_id` smallint NULL ,
  `duration` smallint NULL ,
  `type` tinyint NULL ,
  `start_time` char(2) NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (class_id)
  ) ENGINE = InnoDB;
  
SHOW TABLES;

SHOW CREATE TABLE `university`.`class`;

select * from `university`.`class`;

DESC `university`.`class`;

INSERT INTO `university`.`class`
(class_id, name, professor_id, major_id, duration, type, start_time)
VALUES
(10000, '데이터베이스', 10001, 111, 3, 1, '13'),
(20001, '물리기초1', 20011, 111, 1, 2, '09'),
(30002, '사회학개요', 30001, 120, 2, 3, '15'),
(50003, '통계학1', 50001, 130, 3, 4, '10');

CREATE TABLE `university`.`registerClass` (
  student_id int unsigned NOT NULL ,
  class_id int unsigned NOT NULL ,
  last_update timestamp NOT NULL DEFAULT CURRNET_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (student_id, class_id) ,
  FOREIGN KEY (student_id) REFERENCES `university`.`student` (student_id) ,
  FOREIGN KEY (class_id) REFERENCES `university`.`class` (class_id)
  ) ENGINE=InnoDB;
  
INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2018111, 10000), (2018111, 5003), (2018111, 30002);

INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2017105, 20001), (2017105, 30002), (2017105, 50003);

SELECT * FROM `university`.`registerClass`;

INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2017053, 10000), (2017053, 50003);

  #INSERT INTO `university`.`registerClass`
  #(student_id, class_id)
  #VALUES
  #(2017053, 45003);

SELECT S.NAME, C.NAME, R.last_update
FROM student S join registerClass R on S.student_id = R.student_id
join class C on R.class_id = C.class_id
where S.student_id = 2017053;

SELECT S.NAME, C.NAME, R.last_update
FROM student S join registerClass R on S.student_id = R.student_id
join class C on R.class_id = C.class_id
where S.student_id = 2018111;

SELECT S.NAME, C.NAME, R.last_update
FROM student S join registerClass R on S.student_id = R.student_id
join class C on R.class_id = C.class_id
where S.student_id = 2017105;

 

 

CREATE DATABASE `university`;

`university`라는 데이터베이스를 생성한다.

 

SHOW databases;

현재 생성되어있는 데이터베이스들을 보여준다.

 

 

USE `university`;

`university` 데이터베이스를 이용할 것임을 선언한다.

 

#DROP TABLE `university`.`student`;

DROP TABLE은 이미 존재하는 이 테이블을 삭제하겠다는 의미이다.

 

CREATE TABLE `university`.`student`(          
  `student_id` int unsigned NOT NULL ,
  `name` varchar(20) NOT NULL ,
  `address` varchar(100) NULL ,
  `birth_date` char(8) NULL ,
  `major_id` smallint NULL ,
  `enterance_year` smallint NULL ,
  `leave_yn` bit NULL default 0;
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB;

 

university DB 안에 'student'라는 테이블을 생성한 것이다.

 

student_id, name, address, birth_date, major_id, enterance_year, leave_yn, last_update가 속성이다.

속성 이름 뒤에 도메인을 적어준다. 도메인은 속성의 특성에 맞는 자료형이다.

 

unsigned는 부호가 없다는 뜻이다. 예를 들어 tinyint의 경우, (signed) tinyint의 범위는 -128~127이며, 

unsigned tinyint의 범위는 0~255이다.

 

NULL, NOT NULL은 NULL값이 될 수 있냐 없냐를 정해주는 것이다. 예제의 경우 학번과 이름은 NULL값이 될 수 없도록 했다.

 

varchar( )의 괄호 속 숫자는 최대로 들어갈 수 있는 byte 값이다.

 

`leave_yn`는 휴학여부 속성으로 0과 1로 구분되고, 투플값이 없을 경우 default 값은 0이 들어가도록 했다.

 

`last_update`는 업데이트 시각으로, 직접 적는 것이 아니라 CURRNET_TIMESTAMP를 이용하여 자동으로 저장된다.

이 테이블의 기본키는 `student_id`로, 학번만 가지고도 투플들을 구분할 수 있다는 뜻이다.

 

MySQL의 스토리지 엔진으로 주로 MyISAM과 InnoDB가 사용된다.

스토리지 엔진은 데이터베이스 엔진이라고도 불리며, RDBMS가 데이터베이스에 대해 데이터를 삽입, 추출, 업데이트, 삭제하는 데 사용하는 기본 소프트웨어 컴포넌트이다. 

MyISAM은 항상 테이블에 ROW COUNT를 가지고 있기 때문에 SELECT 명령 시 속도가 빠르고, '풀텍스트 인덱스'를 지원하는데, 이는 자연 언어를 이용해 검색할 수 있는 특별한 인덱스로 모든 데이터 문자열의 단어를 저장한다는 것이다. 이때문에 Read only 기능이 많은 서비스일수록 효율적으로 사용할 수 있는 엔진이다. 단점으로는, row level locking을 지원하지 못해서 select, insert, update, delete 명령 시 해당 테이블 전체에 locking이 걸린다는 것이다. 그래서 갱신이 많이 필요한 경우에는 유용하지 못하다.

InnoDB는 MyISAM과 대조적인 엔진으로, 우선 row level locking이 지원된다는 장점이 있고, 트랜잭션 처리가 필요한 대용량 데이터를 다룰 때 효율적이다. 데이터의 변화가 많은 서비스에 적합하다. 또한, 유일하게 외래키를 지원하는 표준적인 스토리지 엔진이다.단점으로는, 풀텍스트 인덱스를 지원하지 않는다는 것이 있다.

 

주로 InnoDB를 많이 사용한다고 하고, 이번 예제에서도 InnoDB를 사용하였다. 

 

INSERT INTO `university`.`student`
(student_id, name, address, birth_date, major_id, enterance_year, leave_yn)
VALUES
(2016001, '정소정', '서울시 서초구 방배동 911-1', '19971101', 111, 2016, 0),
(2017053,'성민경','경상북도 대구 수성구 삼익 1-1','19980412',111,2017,1),
(2018111,'노규희','충청남도','19991204',130,2018,1),
(2017105,'박성희','강원도','19980908',130,2017,0);

 

student 테이블에 투플들을 삽입하는 코드이다. 

INSERT INTO로 속성을 한 번 나열하고, VALUES를 통해 각각의 개체들을 추가한다.

 

select * from `university`.`student`;

select from은 테이블에서 정보를 가져온다는 뜻이고, *은 테이블에 있는 모든 데이터를 가져온다는 뜻이다.

따라서 이 코드를 실행하면 university.student에 있는 4개의 투플들을 보여준다.

 

select * from `university`.`student`
where student_id = 2016001;

where은 조건의 개념으로, 이 경우에는 학번이 2016001인 투플의 모든 데이터를 가져온다는 뜻이 된다.

 

select student_id, name from `university`.`student`
where major_id = 111;

이 경우에는 selcet student_id, name 이므로, 전공번호가 111인 투플의 학번과 이름을 가져온다.

 

UPDATE `university`.`student`
SET
address = '부산특별시 해운대 비치333-3',
leave_yn = 1
WHERE student_id = 2016001;

 

update는 말 그대로 테이블을 새로운 정보로 업데이트한다는 뜻이다.

이 경우, 학번이 2016001인 투플의 주소와 휴학여부 정보를 변경하게 된다.

 

select * from university.student
where student_id = 2016001;

DELETE FROM university.student
WHERE student_id = 2016001;

select * from university.student 
where student_id = 2016001;

select * from university.student;

 

select문은 마찬가지로 정보를 가져오는 코드이고,

DELETE문은 university DB의 student 테이블에서 student_id가 2016001인 투플을 삭제하는 코드이다.

CREATE TABLE `university`.`class` (
  `class_id` int unsigned NOT NULL ,
  `name` varchar(20) NOT NULL , 
  `professor_id` int NOT NULL , 
  `major_id` smallint NULL ,
  `duration` smallint NULL ,
  `type` tinyint NULL ,
  `start_time` char(2) NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (class_id)
  ) ENGINE = InnoDB;

 

두번째로 universityDB 안에 'class'라는 테이블을 생성한 것이다.

속성으로는 class_id, name, professor_id, major_id, duratin, type, start_time, last_update가 있고 각각의 도메인이 달려있다.

투플을 구분할 수 있는 기본키는 class_id이다.

 

SHOW CREATE TABLE `university`.`class`;

class 테이블에 대한 정보를 알려준다.

 

select * from `university`.`class`;

DESC `university`.`class`;

테이블의 속성과 도메인, NULL 여부 등의 모든 정보를 알려주는 DESC문이다.

 

INSERT INTO `university`.`class`
(class_id, name, professor_id, major_id, duration, type, start_time)
VALUES
(10000, '데이터베이스', 10001, 111, 3, 1, '13'),
(20001, '물리기초1', 20011, 111, 1, 2, '09'),
(30002, '사회학개요', 30001, 120, 2, 3, '15'),
(50003, '통계학1', 50001, 130, 3, 4, '10');

 

class테이블에 4개의 투플을 생성하는 코드이다. 위의 INSERT구문과 같다.

 

CREATE TABLE `university`.`registerClass` (
  `student_id` int unsigned NOT NULL ,
  `class_id` int unsigned NOT NULL ,
  `last_update` timestamp NOT NULL DEFAULT CURRNET_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (student_id, class_id) ,
  FOREIGN KEY (student_id) REFERENCES `university`.`student` (student_id) ,
  FOREIGN KEY (class_id) REFERENCES `university`.`class` (class_id)
  ) ENGINE=InnoDB;

 

세번째로 registerClass 테이블을 생성하는 코드이다. 

속성으로는 student_id, class_id, last_update가 있고, 기본키는 student_id와 class_id이다.

이 테이블에서는 외래키를 사용하였는데, 외래키는 다른 테이블의 속성을 참조하는 속성이다.

student_id는 student 테이블의 student_id를 참조하였고,

class_id는 class 테이블의 class_id를 참조하였다. 그곳에서 값을 가져올 수 있다고 생각하면 될 것 같다.

 

INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2018111, 10000), (2018111, 5003), (2018111, 30002);

INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2017105, 20001), (2017105, 30002), (2017105, 50003);

SELECT * FROM `university`.`registerClass`;

INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2017053, 10000), (2017053, 50003);

 

resigterClass 테이블에 투플들을 생성하는 코드들이다.

코드를 보면 student_id와 class_id 투플값이 모두 student테이블의 student_id와 class테이블의 class_id에 있는 값들인 것을 확인할 수 있다. 외래키를 사용했기 때문이다.

 

  #INSERT INTO `university`.`registerClass`
  #(student_id, class_id)
  #VALUES
  #(2017053, 45003);

이처럼 만약 참조되는 테이블에 없는 값을 value로 입력하면 오류가 뜬다.

 

SELECT S.NAME, C.NAME, R.last_update                        #조회할 것 -> 결합할 것
FROM student S join registerClass R on S.student_id = R.student_id         #조건
join class C on R.class_id = C.class_id
where S.student_id = 2017053;

SELECT S.NAME, C.NAME, R.last_update
FROM student S join registerClass R on S.student_id = R.student_id
join class C on R.class_id = C.class_id
where S.student_id = 2018111;

SELECT S.NAME, C.NAME, R.last_update
FROM student S join registerClass R on S.student_id = R.student_id
join class C on R.class_id = C.class_id
where S.student_id = 2017105;

 

join은 릴레이션 간의 조합을 검색하는 키워드이다.

 

예를 들어, 첫번째 예시는 학번이 2017053인 학생의 이름(from S)과 수강한 과목(C), 수강신청시간(R)을 조회하는 코드이다.

우선 join 다음에 나오는 on은 두 테이블이 결합할 조건을 의미한다.

이 예제에서는 student테이블의 student_id와 registerClass테이블의 student_id가 같을 경우, 

student테이블의 name과 class테이블의 name과 registerClass테이블의 last_update를 결합한다는 의미이다.

결과는 다음과 같이 나온다.

 

 

두번째, 세번째 예제도 같은 코드이고 학번만 바꾼 예제이다.