테이블 PK 자동으로 부여하기 (SELECT NVL(MAX(PK), 100) FROM BOARD) + 1

 

< 전체 코드 >

CREATE TABLE BOARD(
	BID INT PRIMARY KEY,
	TITLE VARCHAR(50) NOT NULL,
	CONTENT VARCHAR(100) NOT NULL,
	MID VARCHAR(30) NOT NULL,
	BCOUNT INT DEFAULT 0
);

DROP TABLE BOARD;

INSERT INTO BOARD(BID, TITLE, CONTENT, MID) VALUES((SELECT NVL(MAX(BID), 100) FROM BOARD)+1, '제목', '내용', 'admin');
INSERT INTO BOARD(BID, TITLE, CONTENT, MID) VALUES((SELECT NVL(MAX(BID), 100) FROM BOARD)+1,  '제목', '내용', 'test');
INSERT INTO BOARD(BID, TITLE, CONTENT, MID) VALUES((SELECT NVL(MAX(BID), 100) FROM BOARD)+1,  '제제제', '내용', 'test');
INSERT INTO BOARD VALUES((SELECT NVL(MAX(BID), 100) FROM BOARD)+1,  '공지', '내용', 'admin', 100);

SELECT * FROM BOARD;
SELECT * FROM BOARD ORDER BY BID DESC;
SELECT * FROM BOARD WHERE TITLE LIKE '%제%' ORDER BY BID DESC;
SELECT * FROM BOARD WHERE MID = 'admin' ORDER BY BID DESC;
SELECT * FROM BOARD ORDER BY BCOUNT DESC, BID DESC;
SELECT * FROM BOADR WHERE BID = 101;

DELETE FROM BOARD WHERE BID = 103;

UPDATE BOARD SET BCOUNT = BCOUNT+1 WHERE BID = 101;
UPDATE BOARD SET TITLE = '변경' WHERE BID = 101;
UPDATE BOARD SET CONTENT = '변경' WHERE BID = 101;

 

 

 

< INSERT 코드 분석 >

INSERT INTO BOARD (BID, TITLE, CONTENT, MID)
VALUES ((SELECT NVL(MAX(BID), 100) FROM BOARD)+1, '제제제', '내용', 'test');

 

INSERT INTO BOARD (...) 

▶ BOARD 테이블에 데이터를 새로 추가하겠다

 

(BID, TITLE, CONTENT, MID)

▶ 데이터를 넣을 컬럼 이름을 지정 (순서 중요!)

 

VALUES (...)

▶ 컬럼에 넣을 값을 순서대로 작성

 

(SELECT NVL(MAX(BID), 100) FROM BOARD) + 1

▶ 새 글의 번호(BID) 를 자동으로 계산

     번호(BID)를 자동 증가처럼 처리

     현재 BOARD 테이블에 저장된 가장 큰 게시글 번호를 가져옴
     테이블이 비어 있을 때(즉, MAX(BID)가 NULL일 때),
     대신 100을 사용함
      그 다음 번호를 사용하겠다