DB.txt
1.66 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
<회원 정보>
CREATE TABLE tunnel.users(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
pw VARCHAR(30) NOT NULL,
personality CHAR(4) NOT NULL,
status TINYINT NOT NULL,
PRIMARY KEY(id),
UNIQUE INDEX name_UNIQUE (name ASC)
)
COMMENT = 'user information'
DEFAULT CHARACTER SET = utf8
ENGINE = InnoDB;
<사용자 게시물>
CREATE TABLE tunnel.posts(
id INT NOT NULL AUTO_INCREMENT,
userid VARCHAR(30) NOT NULL,
post TEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT now(),
status TINYINT NOT NULL,
PRIMARY KEY(id),
INDEX userid_idx (userid ASC),
CONSTRAINT commenter FOREIGN KEY (userid) REFERENCES tunnel.users (name)
ON DELETE CASCADE
ON UPDATE CASCADE)
COMMENT = 'post information'
DEFAULT CHARACTER SET = utf8
ENGINE = InnoDB;
<후 처리>
ALTER TABLE tunnel.posts MODIFY post TEXT CHARACTER SET utf8mb4;
:게시물에 이모지 사용가능하게 칼럼 수정.
<댓글>
CREATE TABLE tunnel.comments(
id INT NOT NULL AUTO_INCREMENT,
postid INT NOT NULL,
userid VARCHAR(30) NOT NULL,
created_at DATETIME NOT NULL DEFAULT now(),
comment TEXT NOT NULL,
PRIMARY KEY(id),
INDEX postid_idx (postid ASC),
CONSTRAINT post_id FOREIGN KEY (postid) REFERENCES tunnel.posts (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT user_id FOREIGN KEY (userid) REFERENCES tunnel.users (name)
ON UPDATE CASCADE
)
COMMENT = 'post information'
DEFAULT CHARACTER SET = utf8
ENGINE = InnoDB;
<후 처리>
ALTER TABLE tunnel.comments MODIFY comment TEXT CHARACTER SET utf8mb4;
: 댓글에 이모지 사용가능하게 칼럼 수정.
<명령어>
show databases;
use tunnel;
show tables;
DESC tunnel.users ; #유저테이블 자세히 보기
DROP TABLE tunnel.users; #유저테이블 지우기