9/5 Node.js 숙련 1주차 (1)

성준호·2024년 9월 5일

1. SQL 살펴보기

1) SQL의 종류

DDL (Data Definition Language)

DML (Data Manipulation Language)

DCL (Data Control Language)

TCL (Transaction Control Language)

2) DDL

데이터를 정의할 때 사용하는 언어. 테이블이나 데이터베이스를 생성, 수정, 삭제할 때 사용

CREAT
- DATABASE, TABLE, VIEW, INDEX 등을 생성

CREATE DATABASE 데이터베이스명;
CREATE TABLE 테이블명
{
 컬럼명 컬럼속성
}

DROP
- DATABASE, TABLE, VIEW, INDEX 등을 삭제

DROP DATABASE 데이터베이스명;
DROP TABLE 테이블명;

ALTER
- DATABASE, TABLE 등의 속성을 변경

ALTER DATABASE 데이터베이스명 변경조건;
ALTER TABLE 테이블명 ADD 변경조건; -- 테이블에 새로운 컬럼을 추가합니다.
ALTER TABLE 테이블명 DROP 변경조건; -- 테이블에서 컬럼을 삭제합니다.
ALTER TABLE 테이블명 MODIFY 변경조건; -- 테이블의 컬럼을 수정합니다.
ALTER TABLE 테이블명 RENAME 변경조건; -- 테이블의 이름을 변경합니다.
...

3) DML

데이터베이스에서 데이터를 조작할 때 사용. 데이터의 저장, 삭제, 수정, 조회

SELECT
- 일반적으로 TABLE에서 원하는 데이터들을 조회할 때 사용

SELECT 컬럼목록 FROM 테이블명 [WHERE 조건];

INSERT
- TABLE에 새로운 데이터들을 삽입

INSERT INTO 테이블명 (컬럼목록) VALUES (값목록);

DELETE
- TABLE에서 특정한 조건에 맞는 데이터들을 삭제
- WHERE 조건이 없다면, 모든 데이터가 삭제된다.

DELETE FROM 테이블명 [WHERE 조건];

UPDATE
- TABLE에서 특정한 조건에 맞는 데이터들을 수정
- WHERE 조건이 없다면, 모든 데이터가 수정된다.

UPDATE 테이블명 SET 컬럼 = 값 [WHERE 조건];

4) DCL

데이터베이스에 대한 권한과 관련된 문법으로 특정 유저가 DB에 접근할 수 있는 권한을 설정

GRANT
- 데이터베이스의 특정한 유저에게 사용 권한을 부여
- 권한에는 SELECT, INSERT, UPDATE, DELETE 등 다양한 종류가 있다.

GRANT [권한] ON 객체명 TO 사용자;

REVOKE
- 데이터베이스의 특정한 유저에게 사용 권한을 취소

REVOKE [권한] ON 객체명 FROM 사용자;

5) TCL

데이터베이스 내의 트랜잭션을 관리하는 문법. 트랜젝션의 시작과 종료, 롤백을 수행

COMMIT
- 데이터베이스의 작업이 정상적으로 완료되었음을 관리자에게 알려준다.

START TRANSACTION;
...
COMMIT;

ROLLBACK
- 데이터베이스의 작업이 비정상적으로 완료되었음을 관리자에게 알려준다.
- 작업 중 오류가 발생한 경우 이를 취소하고, 이전 상태로 되돌린다.

START TRANSACTION;
...
ROLLBACK;

2. SQL

1) 데이터베이스 생성

CREATE DATABASE NodeJS;

2) 테이블 생성

CREATE TABLE IF NOT EXISTS courses (
    id bigint(5) NOT NULL AUTO_INCREMENT, 
    title varchar(255) NOT NULL,
    tutor varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

3) 데이터 삽입

INSERT INTO courses (title, tutor) VALUES
    ('Spring 기초반', '최원빈'),
    ('Spring 숙련반', '최원빈'),
    ('React 기초반', '김예지'),
    ('React 숙련반', '김예지'),
    ('Node.js 기초반', '이용우'),
    ('Node.js 숙련반', '이용우'),
    ('웹개발 종합반', '이범규'),
    ('웹개발 종합반 플러스', '이범규');

4) 데이터 조회

SELECT * FROM courses;

5) 데이터 조건 조회

SELECT * FROM courses WHERE tutor='이용우';

6) 연습 퀴즈

name, age 라는 열(Column)을 가진 users 라는 테이블을 만들고,
나의 이름과 나이 데이터를 삽입해보세요.
그 다음에 users 테이블을 조회해보세요.

// 테이블 생성
CREATE TABLE IF NOT EXISTS users (
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    age int(11) NOT NULL,
    PRIMARY KEY (id)
);

// 데이터 삽입
INSERT INTO users (name, age) VALUES
    ('이용우', 29);

// 데이터 조회
SELECT * FROM users;

1. SQL 제약조건

1) 제약조건이란?

제약 조건은 각 컬럼들간의 제한사항을 관리하고, 조건을 위반하는 데이터를 방지하여 데이터베이스의 무결성을 보장하는 규칙이다.

2) 제약조건의 종류

고유 제약 조건 Unique
- 테이블에 소속된 특정 컬럼이 중복된 키를 가질 수 없는 조건
- 사용자 아이디, 이메일과 같은 고유한 정보를 저장할 때 사용
NULL 제약 조건 Null
- 특정 컬럼이 아무런 값을 입력받지 않도록 설정하거나, 무조건 값을 입력 받도록 설정하는 조건
- 데이터가 없다면 NULL을 저장하여, 데이터가 존재하지 않다는 것을 표현
기본키 제약 조건 Primary Key
- 테이블 내에서 각 행을 고유하게 식별할 수 있도록 보장하는 조건
외래키 제약 조건 Foreign Key
- 테이블 간의 관계를 설정하는 조건
- 한 테이블의 컬럼이 다른 테이블의 특정 행을 참조하도록 설정하는 조건

3) 고유 제약 조건

특정 컬럼에서 중복된 값이 허용되지 않도록 한다.

UNIQUE 제약 조건 요구사항!

사용자(Users) 테이블
1. userId 컬럼을 가지고, 기본키로 설정합니다.
2. 이름(name) 컬럼을 가집니다.

이외 요구사항
1. 사용자 테이블의 이름 컬럼은 NULL을 허용하지 않습니다.
2. 사용자 테이블의 이름 컬럼은 중복된 값을 허용하지 않습니다.

CREATE TABLE Users
(
    userId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255) NOT NULL UNIQUE
);

중복된 데이터를 넣었을 때 `ER_DUP_ENTRY` 에러메시지가 출력된다.

INSERT INTO Users (name) VALUES ('이용우');
INSERT INTO Users (name) VALUES ('이용우');

Error: ER_DUP_ENTRY: Duplicate entry '이용우' for key 'Users.name'

이때 기본 키를 적용한 컬럼은 고유 제약 조건이 자동으로 적용된다

4) NULL 제약 조건

특정 컬럼에서 NULL 값을 허용하지 않아야 할 때는 NOT NULL 제약 조건을 추가하여 해당 컬럼에 NULL 값이 입력되는 것을 방지한다.

NULL 제약 조건 요구사항!

사용자(Users) 테이블
1. userId 컬럼을 가지고, 기본키로 설정합니다.
2. 이름(name) 컬럼을 가집니다.

이외 요구사항
1. 사용자 테이블의 이름 컬럼은 NULL 값을 허용하지 않습니다.

CREATE TABLE Users
(
    userId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255) NOT NULL
);

5) 기본 키 제약 조건

테이블에 있는 데이터를 고유하게 구분할 수 있는 정보를 나타내기 위해 사용한다.

기본 키 제약 조건 요구사항!

사용자(Users) 테이블
1. userId 컬럼을 가지고, 기본키로 설정합니다.
2. 이름(name) 컬럼을 가집니다.

CREATE TABLE Users
(
    userId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255)
);

AUTO_INCREMENT란?
데이터가 입력될 때마다 숫자를 1씩 증가시켜 기본 키의 고유한 값을 유지시켜준다.

6) 외래 키 제약 조건

테이블 간의 연관 관계를 표현할 때 사용한다.
기본적으로 외래 키 제약 조건은 CREATE TABLE 문을 사용하여 테이블을 생성함과 동시에 정의한다.

외래키 제약 조건 요구사항!

정원(Garden) 테이블
1. 이름(name) 컬럼을 가집니다.
2. 주소(address) 컬럼을 가집니다.

정원 식물(GardenPlants) 테이블
1. 식물 이름(name) 컬럼을 가집니다.

이외 요구사항
1. 정원 식물(GardenPlants)은 특정 정원(Garden)에 소속되어 있습니다.
2. 하나의 정원은 여러개의 정원 식물을 가질 수 있습니다.

CREATE TABLE Garden
(
    gardenId INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(255) NOT NULL,
    address  VARCHAR(255) NOT NULL
);

CREATE TABLE GardenPlants
(
    gardenPlantsId INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    gardenId       INT(11)      NOT NULL,
    name           VARCHAR(255) NOT NULL,
    FOREIGN KEY (gardenId) REFERENCES Garden (gardenId)
);

gardenId는 GardenPlants 테이블에서 Garden 테이블을 참조하는 외래키이다.

Garden 테이블

gardenId	name	address
1	Rose Garden	123 Flower, City
2	Tropical Paradise	456 Fucking, Beach
3	Herb Haven	789 Herb Street, Town

GardenPlants 테이블

gardenPlantsId	gardenId	name
1	1	Red Rose
2	1	Yellow Rose
3	2	Coconut Palm
4	2	Banana Tree
5	3	Basil
6	3	Sausage Tree

7) 외래 키 제약 조건 심화

외래 키의 경우 다른 테이블과 관계를 맺고 있는 참조 데이터가 삭제 또는 수정될 때 어떤 행위를 할지 설정할 수 있다.

연계 참조 무결성 제약 조건의 종류

CASCADE
- 참조하고 있는 개체가 변경/삭제 될 경우 함께 변경/삭제된다.

FOREIGN KEY (userId) REFERENCES Users(userId)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

NO ACTION
- 참조하고 있는 개체가 변경/삭제 될 경우 아무런 행위를 하지 않고 에러가 발생한다.

FOREIGN KEY (userId) REFERENCES Users(userId)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

SET NULL
- 참조하고 있는 개체가 변경/삭제 될 경우 현재 데이터를 NULL로 변경한다.

FOREIGN KEY (userId) REFERENCES Users(userId)
    ON DELETE SET NULL
    ON UPDATE SET NULL;

SET DEFAULT
- 참조하고 있는 개체가 변경/삭제 될 경우 현재 데이터를 기본 값으로 변경한다.

FOREIGN KEY (userId) REFERENCES Users(userId)
    ON DELETE SET DEFAULT
    ON UPDATE SET DEFAULT;

8) SELECT JOIN 연산자

JOIN 연산자는 두 테이블 사이의 공통된 데이터를 기준으로 테이블을 연결하여 하나의 테이블처럼 조회할 수 있게 해주는 연산자이다.

JOIN 연산자는 SQL의 제약 조건은 아니다. 여러 테이블 간의 외래 키로 설정된 컬럼들을 연결하여 조회하는 SELECT 연산자의 활용법 중 하나이다.

사용자, 게시글 생성 및 삽입 SQL

CREATE TABLE Users
(
    userId   int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email    varchar(255) NOT NULL,
    password varchar(255) NOT NULL
);

CREATE TABLE Posts
(
    postId  int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId  int(11)      NOT NULL,
    title   varchar(255) NOT NULL,
    content varchar(255) NOT NULL,
    FOREIGN KEY (userId) REFERENCES Users (userId)
);


INSERT INTO Users (userId, email, password)
VALUES (1, 'AAAA', '1234'),
       (2, 'BBBB', '1234');

INSERT INTO Posts (userId, title, content)
VALUES (1, 'AAAA Title1', 'content'),
       (1, 'AAAA Title2', 'content'),
       (2, 'BBBB Title1', 'content'),
       (2, 'BBBB Title2', 'content');

사용자, 게시글 테이블 JOIN SQL

SELECT p.postId, p.title, p.content, u.email
FROM Posts as p
JOIN Users as u
	ON p.userId = u.userId;

FROM ... as, JOIN ... as
별칭은 여러개의 테이블을 참조할 때, 특정 테이블을 간단하게 나타내기 위해 사용한다.
JOIN ... ON
두 개 이상의 테이블을 결합할 때, 어떤 조건으로 테이블을 결합할 것인지를 정의한다.

9) 연습 퀴즈

1:N 테이블 요구사항

출판사(Publisher) 테이블
1. publisherId 컬럼을 가집니다. 기본 키 조건을 설정합니다.
2. 출판사 명(publisherName) 컬럼을 가집니다. 문자열 타입을 가집니다.
3. 출판사 주소(publisherAddress) 컬럼을 가집니다. 문자열 타입을 가집니다.

책(Books) 테이블
1. bookId 컬럼을 가집니다. 기본 키 조건을 설정합니다.
2. 책 명(bookName) 컬럼을 가집니다. 문자열 타입을 가집니다.

이외 요구사항

출판사는 여러개의 책을 가질 수 있습니다.

출판사 명, 출판사 주소, 책 명은 NULL 값을 가질 수 없습니다.

CREATE TABLE Publisher
(
    publisherId      int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    publisherName    varchar(255) NOT NULL,
    publisherAddress varchar(255) NOT NULL
);

CREATE TABLE Books
(
    bookId      int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    publisherId int(11)      NOT NULL,
    bookName    varchar(255) NOT NULL,
    FOREIGN KEY (publisherId) REFERENCES Publisher (publisherId)
);

1:1 테이블 요구사항

별(Star) 테이블
1. starId 컬럼을 가집니다. 기본 키 조건을 설정합니다.

별 세부정보(StarInfo) 테이블
1. starInfoId 컬럼을 가집니다. 기본 키 조건을 설정합니다.
2. 이름(name) 컬럼을 가집니다. 문자열 타입을 가집니다.
3. 밝기(magnitude) 컬럼을 가집니다. 문자열 타입을 가집니다.
4. 거리(distance) 컬럼을 가집니다. 문자열 타입을 가집니다.

이외 요구사항

별은 1개의 별 세부정보를 가질 수 있습니다.

이름, 밝기, 거리 컬럼은 NULL 값을 가질 수 없습니다.

CREATE TABLE Star
(
    starId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE StarInfo
(
    starInfoId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    starId     int(11)      NOT NULL UNIQUE,
    name       varchar(255) NOT NULL,
    magnitude  varchar(255) NOT NULL,
    distance   varchar(255) NOT NULL,
    FOREIGN KEY (starId) REFERENCES Star(starId)
);

N:M 테이블 요구사항

아이돌(Idol) 테이블
1. 이름(name) 컬럼을 가집니다.
2. 나이(age) 컬럼을 가집니다.
3. 성별(gender) 컬럼을 가집니다.

소속사(production) 테이블\
1. 회사명(productionName) 컬럼을 가집니다.
2. 주소(address) 컬럼을 가집니다.

소속사 멤버(ProductionMember) 테이블

아이돌과 소속사를 연결해줍니다.

아이돌의 소속사 데뷔 날짜(debutDate) 컬럼을 가집니다.

이외 요구사항

아이돌은 소속사를 가지지 않거나, 여러개를 가질 수 있습니다.

아이돌의 이름은 중복될 수 없습니다.

소속사는 아이돌을 가지지 않거나, 여러명을 소속시킬 수 있습니다.

소속사의 회사명은 중복될 수 없습니다.

소속사의 주소는 중복될 수 없습니다.

CREATE TABLE Idol
(
    idolId int(11)        NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255)   NOT NULL UNIQUE,
    age    int(11)        NULL,
    gender enum ('M','F') NOT NULL
);

CREATE TABLE Production
(
    productionId   int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    productionName varchar(255) NOT NULL UNIQUE,
    address        varchar(255) NOT NULL UNIQUE
);

CREATE TABLE ProductionMember
(
    productionMemberId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    productionId       int(11) NOT NULL,
    idolId             int(11) NOT NULL,
    debutDate          date    NOT NULL,
    FOREIGN KEY (productionId) REFERENCES Production (productionId),
    FOREIGN KEY (idolId) REFERENCES Idol (idolId)
);

성준호

안녕하세요

이전 포스트

9/4 Node.js 입문 2주차 (2)

다음 포스트

9/5 Node.js 숙련 1주차 (1)

1. SQL 살펴보기

1) SQL의 종류

2) DDL

3) DML

4) DCL

5) TCL

2. SQL

1) 데이터베이스 생성

2) 테이블 생성

3) 데이터 삽입

4) 데이터 조회

5) 데이터 조건 조회

6) 연습 퀴즈

1. SQL 제약조건

1) 제약조건이란?

2) 제약조건의 종류

3) 고유 제약 조건

중복된 데이터를 넣었을 때 `ER_DUP_ENTRY` 에러메시지가 출력된다.

4) NULL 제약 조건

5) 기본 키 제약 조건

6) 외래 키 제약 조건

Garden 테이블

GardenPlants 테이블

7) 외래 키 제약 조건 심화

연계 참조 무결성 제약 조건의 종류

8) SELECT JOIN 연산자

사용자, 게시글 생성 및 삽입 SQL

사용자, 게시글 테이블 JOIN SQL

9) 연습 퀴즈

9/4 Node.js 입문 2주차 (2)

9/6 Node.js 숙련 1주차 (2)

0개의 댓글

9/5 Node.js 숙련 1주차 (1)

1. SQL 살펴보기

1) SQL의 종류

2) DDL

3) DML

4) DCL

5) TCL

2. SQL

1) 데이터베이스 생성

2) 테이블 생성

3) 데이터 삽입

4) 데이터 조회

5) 데이터 조건 조회

6) 연습 퀴즈

1. SQL 제약조건

1) 제약조건이란?

2) 제약조건의 종류

3) 고유 제약 조건

중복된 데이터를 넣었을 때 ER_DUP_ENTRY 에러메시지가 출력된다.

4) NULL 제약 조건

5) 기본 키 제약 조건

6) 외래 키 제약 조건

Garden 테이블

GardenPlants 테이블

7) 외래 키 제약 조건 심화

연계 참조 무결성 제약 조건의 종류

8) SELECT JOIN 연산자

사용자, 게시글 생성 및 삽입 SQL

사용자, 게시글 테이블 JOIN SQL

9) 연습 퀴즈

9/4 Node.js 입문 2주차 (2)

9/6 Node.js 숙련 1주차 (2)

0개의 댓글

중복된 데이터를 넣었을 때 `ER_DUP_ENTRY` 에러메시지가 출력된다.