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를 결합한다는 의미이다.
결과는 다음과 같이 나온다.
두번째, 세번째 예제도 같은 코드이고 학번만 바꾼 예제이다.
'Database > SQL' 카테고리의 다른 글
| [MySQL] SQL 옵티마이저 (0) | 2021.01.18 |
|---|---|
| [MySQL] 데이터 제어어 : DCL (0) | 2021.01.18 |
| [MySQL] Titanic 예제 (0) | 2021.01.11 |
| [MySQL] MySQL 설치하기 (0) | 2021.01.10 |
| [MySQL] 데이터베이스 개념 (0) | 2021.01.10 |
