데이터베이스 예제를 보면서 이해하기 기본편

Posted a year Updated a year

By 베짱이가 되고싶은 개미。

데이타베이스 생성하기

nodejs라는 데이타 베이스 생성하고 use nodejs; 명령어로 생성한 데이타베이스 선택하는 예제

mysql> CREATE SCHEMA ‘nodejs’ DEFAULT CHARACTER SET utf8;
Query Ok, 1 row affected (0,01 sec)
mysql> use nodejs;
Database changed

CREATE SCHEMA 'nodejs' DEFAULT CHARACTER SET utf8; 명령어는 MySQL 데이터베이스에서 새로운 스키마(데이터베이스)를 생성하는 SQL 명령어이다.

CREATE SCHEMA 'nodejs';

위 명령어는 새로운 스키마(또는 데이터베이스)인 'nodejs'를 생성한다.

DEFAULT CHARACTER SET utf8;

스키마에서 기본으로 사용할 문자 세트를 UTF-8로 설정한다. UTF-8은 다양한 언어와 문자를 지원하는 유니코드 인코딩 방식이다. 즉, 이 명령어는 'nodejs'라는 이름의 데이터베이스를 생성하고, 그 데이터베이스 내에서 모든 테이블이 UTF-8 문자 세트를 기본으로 사용하도록 설정한다.

MySQL 프롬프트에서 댓글테이블 생성

mysql> CREATE TABLE nodejs.comments(
-> id INT NOT NULL AUTO_INCREMENT,
-> commenter INT NOT NULL, //댓글단사람 아이디 
-> comment VARCHAR(100) NOT NULL, //100글짜까지 커멘트 
-> created_at DATETIME NOT NULL DEFAULT now(), 생성일 날짜시간 유저가 값을 입력하지 않을경우에 현재날짜시간을 입력해줌
-> PRIMARY KEY(id)
-> INDEX commenter_idx (commenter ASC),
-> CONSTRAINT commenter
-> FOREIGN KEY (commenter)
-> REFERENCES nodejs.users (id)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE
-> COMMENT =“댓글”
-> DEFAULT CHARSET=utf8mb4
-> ENGIME=InnoDB;
)

INT : 정수 자료형
VARCHAR : 문자열 자료형, 가변길이(CHAR은 고정길이)
TEXT : 긴 문자열은 TEXT로 별도저장(소설, 일기 등)
DATETIME : 날짜 자료형 저장
TINYINT : -128에서 127까지 저장할수있다. 1 또는 0만 저장해 불값으로 사용 가능
NOT NULL : 빈값은 허용하지 않음
AUTO_INCREMENT : 숫자 자료형인 경우 다음 로우가 저장될때 자동으로 1증가
UNSIGNED : 0과 양수만 허용
ZEROFILL : 숫자의 자리수가 고정된 경우 빈자리에 0을 넣음
DEFAULT now() : 날짜 컬럼의 기본값을 현지 시간으로

column 세부 옵션 설명

INDEX commenter_idx (commenter ASC)

INDEX는 데이터베이스에서 특정 열의 값을 기반으로 빠르게 데이터를 검색할 수 있도록 인덱스를 생성하는 명령어로 댓글검색시 자주 사용하는 commenter에 INDEX를 걸어주고 코맨터 컬럼 을 오름차순으로 인덱싱 하겠다는 뜻이다.

CONSTRAINT commenter

CONSTRAINT는 특정 제약 조건에 이름을 부여하는 명령어로 commenter에 특정 제약조건을 부여 하겠다라는 명령어이다.

FOREIGN KEY (commenter)

FOREIGN KEY는 테이블 간의 관계를 정의하는 명령어이다. * (commenter)는 이 외래 키가 참조할 테이블의 열(column)을 나타내며 이 경우, commenter 열이 외래 키 역할을 한다.

REFERENCES nodejs.users (id)

REFERENCES는 외래 키가 참조할 테이블과 열을 지정한다. nodejs.users는 참조할 대상 테이블을 나타내며, id는 그 테이블에서 참조할 열 이다. 따라서, commenter 열은 nodejs 스키마(테이블)의 users 테이블에 있는 id 열을 참조하게 되며

이 SQL 구문은 외래 키(Foreign Key)를 정의하여 테이블 간의 참조 무결성을 설정하는 명령문이다

ON DELETE CASCADE

ON DELETE CASCADE는 참조된 레코드가 삭제될 때 이 외래 키를 가진 레코드도 자동으로 삭제되도록 설정한다. 예를 들어, users 테이블에서 특정 id를 가진 사용자가 삭제되면, 이 id를 참조하는 모든 commenter 레코드도 자동으로 삭제된다.

ON UPDATE CASCADE

ON UPDATE CASCADE는 참조된 레코드의 기본 키(즉, id)가 업데이트될 때, 이 외래 키를 가진 레코드의 값도 자동으로 업데이트되도록 설정한다. 예를 들어, users 테이블의 특정 id 값이 변경되면, 이 id를 참조하는 모든 commenter 열의 값도 자동으로 업데이트된다.

DEFAULT CHARSET=utf8mb4

DEFAULT CHARSET는 테이블 또는 데이터베이스에서 문자열 열(column)에 사용할 기본 문자 집합(문자열 데이터의 인코딩 방식)을 지정한다. utf8mb4는 MySQL에서 제공하는 문자 집합 중 하나로 유니코드의 모든 문자를 표현할 수 있으며, 특히 이모지(emoji)나 특수 문자를 제대로 저장할 수 있다.

UNIQUE INDEX name_QNIQUE (name ASC)

이 명령문은 데이터베이스의 특정 테이블에 대해 고유 인덱스를 생성한다. INDEX는 데이터베이스에서 특정 열의 값을 기반으로 빠르게 데이터를 검색할 수 있도록 인덱스를 생성하는 명령어이다.

* UNIQUE INDEX 는 인덱스가 고유해야 함을 의미하며 이 인덱스를 적용한 열(column)에는 중복된 값이 있을 수 없다.
* name_QNIQUE는 생성할 인덱스의 이름이며 인덱스 이름은 데이터베이스 내에서 고유해야 하며, 쿼리에서 사용하기 위한 식별자로 사용된다.
* (name ASC)는 name은 인덱스를 생성할 열(column)의 이름을 나타내며 ASC는 오름차순(ascending)으로 인덱스를 생성하겠다는 의미이다. 이 인덱스를 사용하여 데이터를 검색할 때 오름차순으로 정렬된 결과를 빠르게 얻을 수 있다.

예로 users라는 테이블이 있고, 이 테이블에서 name 열에 대해 고유한 인덱스를 생성하고 싶다면 다음과 같은 쿼리를 사용할 수 있다.

CREATE UNIQUE INDEX name_QNIQUE ON users(name ASC);

[테이블] - CREATE 생성

INSERT INTO 테이블 (컬럼명들) VALUES (값들)


mysql> INSERT INTO nodejs.users (name, age, married, comment) VALUES ('zero', 24, 0, '자기소개 커멘트');
Query OK, 1 row affected (0.01 sec)

위 SQL 쿼리는 nodejs 데이터베이스의 users 테이블에 이름이 zero이고, 나이가 24세이며, 미혼이고, "자기소개 커멘트"라는 코멘트를 가진 새로운 사용자를 추가하는 명령이다. id와 create_at은 자동으로 생성된다.

mysql> INSERT INTO nodejs.comments (commenter, comment) VALUES(1, ‘안녕하세요. commenter1의 댓글입니다. )

위 SQL 쿼리는 nodejs.comments 테이블에 사용자 ID가 1인 사용자가 "안녕하세요. commenter1의 댓글입니다."라는 댓글을 추가하는 명령이다.

[테이블] - READ 조회

SELECT 컬럼 FROM 테이블명

mysql> SELECT * FROM nodejs.users;

SELECT는 데이터베이스에서 데이터를 조회하는 데 사용된다. * 는 테이블의 모든 열(columns)을 조회하겠다는 뜻으로 모든 컬럼을 선택한다는 의미이다.

mysql> SELECT name, married FROM nodejs.users;

위 SQL 쿼리는 nodejs라는 데이터베이스 안에 있는 users라는 테이블에서 name과 married라는 두 개의 컬럼(열)의 데이터를 선택(select)하여 조회하는 명령이다.

WHERE 조건문과 AND

여러가지 조건을 동시에 만족하는 것을 찾아준다.

mysql> SELECT name, age, FROM nodejs.users WHERE married = 1 AND age > 30;

쿼리의 내용은 married 필드가 1(즉, 결혼한 상태)이고 age 필드가 30세 이상인 사용자들의 name(이름)과 age(나이)를 조회한다.

WHERE 조건문과 OR

OR로 여러가지 조건 중 하나 이상을 만족하는 것을 찾아준다.

mysql> SELECT id, name FROM nodejs.users WHERE married = 0 OR age > 30;

쿼리의 내용은 결혼하지 않은 사용자(married가 0인 사용자) 또는 나이가 30세를 초과하는 사용자(age > 30)의 id와 name을 가져온다.

ORDER BY로 특정 컬럼을 순서대로 정렬 한값 가져오기

DESC는 내림차순
ASC는 오름차순

mysql> SELECT id, name FROM nodejs.users ORDER BY age DESC;

nodejs 데이터베이스의 users 테이블에서 사용자들의 id와 name을 조회하고, age(나이) 값을 기준으로 내림차순(나이가 많은 순서대로)으로 정렬하는 명령이다.

LIMIT으로 조회할 개수 제한

mysql> SELECT id, name FROM nodejs.users ORDER BY age DESC LIMIT 1;

ORDER BY age DESC

age(나이) 필드를 기준으로 내림차순(나이가 많은 순서)으로 정렬한다.

LIMIT 1

결과 집합에서 가장 첫 번째 행만 가져온다. 즉, 정렬된 결과 중 첫 번째 사용자(나이가 가장 많은 사용자) 하나만 선택한다.

OFFSET으로 앞의 로우들 스킵하기

OFFSET 2면 세번째 것부터 찾음

mysql> SELECT id, name FROM nodejs.users ORDER BY age DESC LIMIT 1 OFFSET 1;

ORDER BY age DESC

* age(나이) 필드를 기준으로 내림차순(나이가 많은 순서)으로 결과를 정렬해준다.

LIMIT 1

* 결과에서 하나의 행만 선택 하지만 이 행은 기본적으로 정렬된 결과의 첫 번째가 아닌, OFFSET 값이 적용된 다음 행이다.

OFFSET 1

* 정렬된 결과 집합에서 첫 번째 행을 건너뛰고 두 번째 행부터 시작한다. 이 경우, 내림차순으로 정렬된 사용자 목록에서 두 번째로 나이가 많은 사용자가 선택된다.

[테이블] - UPDATE 수정

UPDATE 테이블명 SET 칼럼=새값 WHERE 조건

mysql> UPDATE nodejs.users SET comment = ‘여기 comment의 내용을 변경합니다.’ WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

nodejs.users 테이블에서 id 값이 2인 행을 찾아서 해당 행의 comment 컬럼의 값을 '바꿀 내용'으로 업데이트한다.

[테이블] - DELETE 삭제

DELETE FROM 테이블명 WHERE 조건

mysql> DELETE FROM nodejs.users WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

nodejs.users 테이블에서 id 값이 2인 행을 찾아 해당 행을 테이블에서 삭제한다.

위 내용은 https://www.inflearn.com/course/lecture?courseSlug=%EB%85%B8%EB%93%9C-js-%EA%B5%90%EA%B3%BC%EC%84%9C&unitId=143595&tab=curriculum 를 기반으로 작성 되었습니다.