[DB] SQL 연습 (윈도우 MySQL Workbench)

sql 연습을 위한 sample 데이터 준비

1. 리눅스 컴퓨터에서 파일 받아온 후 ls로 파일이 잘 받아와졌는지 확인

wget [SQL 파일 링크]

 

2. sql 파일에 있는 것들을 한 번에 실행

mariadb -u root -p <  [sql 파일]
패스워드 입력

 

 

3. 테이블 있나 확인 후 권한 부여

mariadb -u root -p
show databases;

GRANT ALL PRIVILEGES ON [DB 이름].* TO '[사용자 이름]'@'%';
FLUSH PRIVILEGE;

실습에서는 classicmodels 사용

 

4. 윈도우 MySQL Workbench에서 들어온 거 확인

classicmodels 디비와 테이블들이 잘 들어와있는 것을 확인할 수 있다.

 

---

 

MySQL Workbench - SQL 실습

실습하기 전 어떤 데이터베이스를 쓸 것인지 선택해야 한다.

 

여기서 내가 쓰고 싶은 데이터베이스를 더블클릭하면 선택된다.

Schema에 들어온 거 확인 후 작업해야 한다.

 

 

빨간 박스 - sql 전체 실행

초록 박스 - 한 줄만 실행

---

DDL

테이블 생성 (CREATE TABLE)

CREATE TABLE my_table(
   name VARCHAR(10),
   age  INT,
   created_at DATETIME,
   contents VARCHAR(200)
);


# 회원 테이블     user(idx 숫자 , name (20)글자, id (20)글자, password (256)글자) 
CREATE TABLE user(
   idx       INT PRIMARY KEY AUTO_INCREMENT,
   name       VARCHAR(20),
   ID         VARCHAR(20),
   password    VARCHAR(256),
   profile_img VARCHAR(256) DEFAULT '/images/default.png'
);


# 게시글 테이블    user(idx 숫자, title 글자(100), contents 글자(500), writer user를 참조) 
CREATE TABLE board(
   idx       INT PRIMARY KEY AUTO_INCREMENT,
   title       VARCHAR(100),
   contents      VARCHAR(500),
   writer       INT,
   FOREIGN KEY (writer) REFERENCES user(idx)   # 외래키 설정
);


# 좋아요 테이블		likes(user(user 테이블 참조), board(board 테이블 참조))
CREATE TABLE likes (
    user 		INT,
    FOREIGN KEY (user) REFERENCES user(idx),
    board 		INT,
    FOREIGN KEY (board) REFERENCES board(idx)	
);

 

타입

• 숫자는 INT, 글자는 VARCHAT(글자수)로 나타냄

 

PRIMARY KEY, AUTO_INCREMENT

• primary 키 지정, 키 자동 설정 

 

DEFAULT '/images/default.png'

• 프로필 사진처럼 지정 안 할 때 디폴트 값으로 넣기

 

FOREIGN KEY (writer) REFERENCES user(idx)

FOREIGN KEY (자식 테이블의 열 이름) REFERENCE [부모 테이블 이름](부모 테이블의 열 이름)

• writer는 user 테이블의 idx를 참조한다.

---

 

테이블 삭제 - DROP

DROP TABLE user;

 

테이블 삭제 명령어

 

---

테이블 수정 - ALTER 

ALTER TABLE user ADD birth DATE;	# 속성 추가
ALTER TABLE user RENAME COLUMN birth TO yyyymmdd;	# 속성 이름 변경
ALTER TABLE user MODIFY yyyymmdd INT;	# 속성 타입 변경
ALTER TABLE user DROP yyyymmdd;	# 속성 삭제

 

• ADD [속성 이름] [속성 타입] : 속성 추가
• RENAME COLUUMN [원래 속성 이름] TO [바꿀 속성 이름] : 속성 이름 변경
• MODIFY [속성 이름] [속성 타입] : 속성 타입 변경
• DROP [속성 이름] : 속성 삭제

 

---

DML

데이터 삽입 - INSERT

INSERT INTO [테이블 이름] (속성1, 속성 2, 속성 3,,,) VALUES (값 1, 값 2, 값 3,,,);

INSERT INTO user (name, id, password) VALUES ('test01','test01','qwer1234');

INSERT INTO user (idx, name, id, password, profile_img) VALUES (2, 'test02','test02','qwer1234', 'abcd.jpg');

INSERT INTO user VALUES (3, 'test03','test03','qwer1234', 'abcd.jpg');

INSERT INTO user (name, id, password, profile_img) VALUES ('test04','test04','qwer1234', 'abcd.jpg');

INSERT INTO user (name, id, password) VALUES ('test05','test05','qwer1234'), ('test06','test06','qwer1234');

 

 

 

데이터 조회 - SELECT

 

SELECT [찾을 속성] FROM [테이블 이름];

SELECT * FROM user;

 

 

데이터 수정 - UPDATE

UPDATE [테이블 이름] SET [속성]=[바꿀 값] WHERE [삭제할 튜플 조건];

# idx가 1번인 사용자의 profile_img를 '/images/abc.png' 바꾸겠다.
UPDATE user SET profile_img='/images/abc.png' WHERE idx=1;

 

데이터 삭제 - DELETE

DELETE FROM [테이블 이름] WHERE [삭제할 튜플 조건];

DELETE FROM user WHERE idx=3;

 

 

💡 취약한 쿼리

입력값 중에 ' 나 # 등 쿼리와 연관된 특수문자들이 들어있으면 주석 처리가 될 수도 있음.

# 로그인

# ID : test01';#
# PW : ********
# SQL 인젝션

# 취약한 쿼리
SELECT * FROM user WHERE id='test01';#' AND password='qwer1234';

# ID먼저 확인
SELECT * FROM user WHERE id='test01';​

뒤에 AND password='qwer1234' 부분이 주석처리가 되기 때문에 
비밀번호를 입력하지 않아도 select 문으로 유저의 데이터가 나온다.
이렇게 되면 비밀번호를 입력하지 않아도 로그인이 되는 상황이 발생할 수도 있음!! 

그래서 보통 특수문자를 제한을 두거나, 비밀번호 입력 횟수를 제한한다.
ex) 특수문자 (_), (-)만 사용, 비밀번호 5회 입력 시 잠금 등