Node.js 숙련주차 3.2 SQL (Structured Query Language)

1. SQL(Structured Query Language)과 SQL의 유형을 알아봅니다.
2. SQL(Structured Query Language)를 사용하여 데이터베이스를 조작해봅니다

SQL을 이용해 MySQL의 DATABASE를 직접 조작해봅시다

01. SQL (Structured Query Language) 살펴보기

1) SQL 이란?

데이터베이스에서 사용되는 생성, 삽입, 조회 명령문을 SQL(Structured Query Language)이라고 합니다.
조건에 맞는 데이터를 활용할 수 있어야 하고, 끊임없이 변하는 내용을 지속적으로 삽입, 수정, 삭제, 조회가 가능해야합니다.
그런상황에서 데이터를 어떻게 관리해야할 지 고민한 결과, 모든 데이터들을 공통적으로 관리할 수 있는 표준 언어인 SQL이 탄생하게 되었습니다.

2) SQL의 종류

DDL (Data Definition Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
TCL (Transaction Control Language)

3) DDL (Data Definition Language)

DDL(Data Definition Language)은 데이터를 정의할 때 사용하는 언어로 테이블이나 데이터베이스를 생성, 수정, 삭제와 같은 행위를 할 때 사용합니다.

CREAT : https://dev.mysql.com/doc/refman/8.0/en/create-database.html

DROP : https://dev.mysql.com/doc/refman/8.0/en/drop-table.html

ALTER : https://dev.mysql.com/doc/refman/8.0/en/alter-database.html

-- CREATE : 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 변경조건; -- 테이블의 이름을 변경합니다.

 

​

4) DML (Data Manipulation Language)

DML(Data Manopulation Language)은 데이터베이스에서 데이터를 조작할 때 사용하는 언어로 데이터의 저장, 삭제, 수정, 조회와 같은 행위를 합니다.

SELECT : https://dev.mysql.com/doc/refman/8.0/en/select.html
일반적으로 TABLE에서 원하는 데이터들을 조회할 때 사용합니다.
DB를 관리하면서 가장 많이 사용하는 명령어 입니다.

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

INSERT  : https://dev.mysql.com/doc/refman/8.0/en/insert.html
TABLE에 새로운 데이터들을 삽입할 때 사용합니다.

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

DELETE  : https://dev.mysql.com/doc/refman/8.0/en/delete.html
TABLE에서 특정한 조건에 맞는 데이터들을 삭제할 때 사용합니다.
WHERE 조건이 없다면, 모든 데이터가 삭제됩니다.

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

UPDATE  :  https://dev.mysql.com/doc/refman/8.0/en/update.html
TABLE에서 특정한 조건에 맞는 데이터들을 수정할 때 사용합니다.
WHERE 조건이 없다면, 모든 데이터가 수정됩니다.

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

 

5) DCL (Data Control Language)

DCL(Data Control Language)은 데이터베이스에 대한 권한과 관련된 문법으로 특정 유저가 DB에 접근할 수 있는 권한을 설정할 때 사용합니다.

GRANT : https://dev.mysql.com/doc/refman/8.0/en/grant.html
데이터베이스의 특정한 유저에게 사용 권한을 부여할 때 사용합니다.
권한에는 SELECT, INSERT, UPDATE, DELETE 등 다양한 종류가 존재합니다.

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

​
REVOKE : https://dev.mysql.com/doc/refman/8.0/en/revoke.html
데이터베이스의 특정한 유저에게 사용 권한을 취소할 때 사용합니다.

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

 

6) TCL (Transaction Control Language)

TCL(Transaction Control Language)은 데이터베이스 내의 트랜잭션을 관리하는 문법으로, 트랜잭션의 시작과 종료, 그리고 롤백을 수행할 때 사용합니다. Transaction은 정상 완료시 반영, 오류 발생 시 롤

COMMIT : https://dev.mysql.com/doc/refman/8.0/en/commit.html
데이터베이스의 작업이 정상적으로 완료되었음을 관리자에게 알려줄 때 사용합니다. 실제 db에 반영합니다.

START TRANSACTION;
...
COMMIT;

​
ROLLBACK : https://dev.mysql.com/doc/refman/8.0/en/commit.html
데이터베이스의 작업이 비정상적으로 완료되었음을 관리자에게 알려줄 때 사용합니다.
작업 중 오류가 발생한 경우 이를 취소하고, 이전 상태로 되돌릴 때 사용합니다.

START TRANSACTION;
...
ROLLBACK;

 

02. SQL (Structured Query Language)

1) VSCode를 이용하여 SQL 사용 전 준비하기

VSCode에서 연결된 MySQL을 우클릭하여 New Query버튼을 누르고 새로운 Query 파일을 생성합니다.

Query 파일에서 원하는 SQL을 사용할 수 있습니다

DESC는 테이블의 속성을 확인하는 명령어입니다!

 

주의!! 생성한 Query 파일은 MySQL을 우클릭한 기준으로 데이터베이스를 바라보고 있습니다. 즉, 자료와 같은 express-database를 우클릭하여 실행한 경우 No database selected 에러가 발생 할 수 있으니 생성한 데이터베이스를 기준으로 쿼리파일을 생성해주세요.

2) 데이터 베이스 기본 개념

데이터베이스는 매우 고도화된 엑셀입니다.

엑셀 파일 하나가 "데이터베이스"라면,
엑셀 시트 하나는 "테이블"이라고 부르고,
엑셀 행 하나는 "데이터"라고 부릅니다.
따라서 지금부터 우리가 할 것은
테이블을 생성하고,
테이블에 데이터를 삽입하고,
삽입된 데이터를 조회해보는 것입니다.

3) 데이터 베이스 생성

이름이 NodeJS 인 엑셀 파일을 하나 만드는 것과 같습니다!
데이터베이스 NodeJS 생성하기

CREATE DATABASE NodeJS;

명령을 실행할 경우 MySQL 확장 플러그인에서 NodeJS 데이터베이스가 추가됩니다

 

파란 드럼통 우클릭 -> New Query

 

커드 입력 후 코드영역에 우클릭 -> Run MySQL Query

 

결과 : Node.js DB가 만들어짐.

4) 테이블 생성

이름이 courses 인 엑셀 시트 하나 만드는 것과 같습니다.
DB를 선택해서 테이블을 만드새요.

 

테이블 courses 생성하기

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) -- 데이터의 고유함을 중명
);

​Node.js에서 우클릭 -> New Query -> MySQL로 쿼리 실행

결과 : courses 테이블 생성

 

 

MySQL 8.0.17 이후로 숫자형 데이터 타입에서 디스플레이 길이 옵션은 더 이상 지원되지 않습니다.
따라서 BIGINT(5) 같은 표현은 무의미하며, 단순히 BIGINT로만 사용하는 것이 좋습니다.
결론
BIGINT(5)는 현대 MySQL에서는 의미 없는 표현이며, 다음처럼 작성하는 것이 적합합니다:

BIGINT NOT NULL AUTO_INCREMENT

VARCHAR의 길이 제한은 실질적으로 의미가 있습니다.
VARCHAR(255)에서 숫자 255는 최대 문자의 길이를 지정합니다.
즉, 최대 255개의 문자를 저장할 수 있습니다.

int(숫자) 는 무의미. int의 데이터 크기는 항상 4bit

-- 문자 길이를 제한하는 의미가 있을만한 값을 적으세요.
CREATE TABLE IF NOT EXISTS courses (
    id bigint NOT NULL AUTO_INCREMENT, 
    title varchar(50) NOT NULL,
    tutor varchar(50) NOT NULL,
    PRIMARY KEY (id)
);

 

 

5) 데이터 삽입

'Node.js' db에서 new query -> 코드입력 ->쿼리 샐행(nysql로)

title, tutor  컬럼을 포함하는 courses 테이블의 행을 2개를 삽입니다.
courses 데이터 삽입

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

6) 데이터 조회

Node.js' db에서 new query -> 코드입력 ->쿼리 샐행(Mysql로)
삽입한 데이터들을 조회해보겠습니다.
courses 조회하기

SELECT * FROM courses;

courses 테이블에 존재하는 모든 데이터를 조회합니다.

7) 데이터 조건 조회

삽입한 데이터에서 tutor 컬럼이 ‘이용우’인 값만 조회합니다.
ourses 조건 조회하기

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

8) 연습퀴즈 - SQL

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

-- 데이터의 개수가 적은 경우 id bigint말고 id int~ 로도 가능
create table if not exists user (
    id bigint(5) not null AUTO_INCREMENT,
    name varchar(255) not null,
    age int not null,
    primary key (id)
)


insert into user (name, age) values
    ('지구인', 30),
    ('화성인', 500),
    ('목성인', 2),
    ('해왕성인', 60),
    ('토성인', 900),
    ('수성인', 5);


select * from user;

​