DB에 들어가는 거 #1
Description
CREATE TABLE t_board_fav(
iboard INT UNSIGNED,
iuser INT UNSIGNED,
regdt DATETIME DEFAULT NOW(),
PRIMARY KEY(iboard,iuser),
FOREIGN KEY(iboard) REFERENCES t_board(iboard),
FOREIGN KEY(iuser) REFERENCES t_user(iuser)
);
SELECT*FROM t_board_fav;
SELECT B.unm, A.iboard, A.title, A.ctnt, A.iuser, A.regdt
,if(C.iboard IS NULL, 0, 1) AS isFav
FROM t_board A
INNER JOIN t_user B
ON A.iuser = B.iuser
LEFT JOIN t_board_fav C
ON A.iboard = C.iboard
AND C.iuser = ?
WHERE A.iboard = ?;
CREATE TABLE t_board_cmt(
icmt INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
cmt VARCHAR(400) NOT NULL,
iuser INT UNSIGNED,
regdt DATE DEFAULT NOW(),
iboard INT UNSIGNED,
FOREIGN KEY(iboard) REFERENCES t_board(iboard) ON DELETE CASCADE,
FOREIGN KEY(iuser) REFERENCES t_user(iuser) ON DELETE CASCADE
);
SELECT A.icmt, A.cmt, A.regdt, B.unm, B.iuser
FROM t_board_cmt A
INNER JOIN t_user B
ON A.iuser = B.iuser
WHERE iboard = 27;
SELECT*FROM t_board_cmt;
DELETE FROM t_board_cmt
WHERE icmt = 4 AND iuser = 5;
/and를 쓰면 두 조건 다 충족해야 실행됨/
DROP TABLE t_board_cmt;
ALTER TABLE t_board ADD CONSTRAINT t_board_cmt FOREIGN KEY (iboard) REFERENCES t_board(iboard) ON DELETE CASCADE;
CREATE TABLE t_user(
iuser INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uid VARCHAR(20) NOT NULL UNIQUE,
upw VARCHAR(100) NOT NULL,
unm VARCHAR(5) NOT NULL,
gender INT(1) unsigned CHECK(gender IN (0,1)),
regdt DATETIME DEFAULT NOW()
);
SELECT*FROM t_user;
DROP TABLE t_user;
SELECT*FROM t_board
ORDER BY iboard DESC
LIMIT 10, 10;
SELECT ceil(COUNT(*) / 3) FROM t_board;
SELECT CEIL(10/3) FROM t_board
WHERE title LIKE '%?%';