sql/localhost_ODOUNG_220303.sql at master · rbdnd1019/sql · GitHub

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
--public Long bno;
--public String title;
--public String content;
--public int hitcount;
--public long regDate;
--
--public String writer;
DROP TABLE BOARD;
CREATE TABLE TBL_BOARD (
    BNO NUMBER,
    TITLE VARCHAR(300),
    CONTENT CLOB,
    HITCOUNT NUMBER DEFAULT 0,
    REGDATE DATE DEFAULT SYSDATE,
    WRITER VARCHAR2(100)
);
-- SEQUENCE
CREATE SEQUENCE SEQ_BOARD;

-- 글 등록
INSERT INTO TBL_BOARD(BNO, TITLE, CONTENT, WRITER, REGDATE)
VALUES (SEQ_BOARD.NEXTVAL, '글 제목', '글 내용', '작성자', SYSDATE - 3);


-- 목록 조회
SELECT BNO, TITLE, HITCOUNT,
CASE
    WHEN SYSDATE - REGDATE > 1 THEN TO_CHAR(REGDATE, 'YY/MM/DD')
    ELSE TO_CHAR(REGDATE, 'HH24:MI:SS')
END REGDATE,
WRITER FROM TBL_BOARD ORDER BY 1 DESC;


-- 상세 조회
SELECT BNO, TITLE, CONTENT, HITCOUNT, REGDATE, WRITER FROM TBL_BOARD WHERE BNO = 1;

-- 글 수정
UPDATE TBL_BOARD SET
TITLE = '수정된 글 제목',
CONTENT = '수정된 글 내용',
REGDATE = SYSDATE
WHERE BNO = 3;

-- 글 삭제
DELETE TBL_BOARD
WHERE BNO = 2;

-----
DROP TABLE TBL_MEMBER;
CREATE TABLE TBL_MEMBER (
    ID VARCHAR2(100),
    PW VARCHAR2(100),
    NAME VARCHAR2(100)
);

INSERT INTO TBL_MEMBER VALUES('javaman', '1234', '자바맨');
INSERT INTO TBL_MEMBER VALUES('babamba', '1234', '바밤바');
INSERT INTO TBL_MEMBER VALUES('odoung', '1234', '오둥이');

SELECT * FROM tbl_member;

SELECT * FROM TBL_MEMBER
WHERE ID = 'babamba' AND PW = '1234';

INSERT INTO TBL_MEMBER VALUES('amanna', '1234', '아맛나');

DESC TBL_MEMBER;

SELECT * FROM TBL_MEMBER
WHERE ID = 'odoung' AND PW = '1234';

SELECT * FROM TBL_MEMBER
WHERE ID = 'javaman' AND PW = '5678';

commit;


-- 회원정보 수정
UPDATE TBL_MEMBER SET
PW = 5678,
NAME = '자바공주'
WHERE ID = 'javaman';

-- 회원 탈퇴
DELETE TBL_MEMBER
WHERE ID = 'babamba';

DELETE TBL_MEMBER
WHERE ID = '123';

SELECT * FROM TBL_MEMBER;

-- 댓글
CREATE TABLE TBL_REPLY (
    RNO NUMBER,
    CONTENT VARCHAR2(4000),
    REGDATE DATE DEFAULT SYSDATE,
    BNO NUMBER,
    WRITER VARCHAR2(100)
);
CREATE SEQUENCE SEQ_REPLY;

INSERT INTO TBL_REPLY (RNO, CONTENT, BNO, WRITER) VALUES (SEQ_REPLY.NEXTVAL, '댓글 내용1', 1, '작성자');
INSERT INTO TBL_REPLY (RNO, CONTENT, BNO, WRITER) VALUES (SEQ_REPLY.NEXTVAL, '댓글 내용2', 1, '작성자');
INSERT INTO TBL_REPLY (RNO, CONTENT, BNO, WRITER) VALUES (SEQ_REPLY.NEXTVAL, '댓글 내용3', 1, '작성자');
INSERT INTO TBL_REPLY (RNO, CONTENT, BNO, WRITER) VALUES (SEQ_REPLY.NEXTVAL, '댓글 내용4', 1, '작성자');
INSERT INTO TBL_REPLY (RNO, CONTENT, BNO, WRITER) VALUES (SEQ_REPLY.NEXTVAL, '댓글 내용5', 1, '작성자');

INSERT INTO TBL_REPLY (RNO, CONTENT, BNO, WRITER) VALUES (SEQ_REPLY.NEXTVAL, '댓글 내용3', 3, '작성자');
INSERT INTO TBL_REPLY (RNO, CONTENT, BNO, WRITER) VALUES (SEQ_REPLY.NEXTVAL, '댓글 내용4', 3, '작성자');
INSERT INTO TBL_REPLY (RNO, CONTENT, BNO, WRITER) VALUES (SEQ_REPLY.NEXTVAL, '댓글 내용5', 3, '작성자');

SELECT * FROM TBL_REPLY WHERE BNO = 3;

DELETE FROM TBL_REPLY WHERE RNO = 3;

SELECT * FROM TBL_BOARD ORDER BY 1 DESC;

SELECT * FROM TBL_REPLY;

SELECT * FROM TBL_BOARD WHERE BNO = 1;
UPDATE TBL_BOARD SET
HITCOUNT = HITCOUNT + 1
WHERE BNO = 1;