풀스택 sql - 20240809

SQL문 연습

# a5 데이터베이스 삭제/생성/선택
DROP DATABASE IF EXISTS a5;
CREATE DATABASE a5;
USE a5;

# 부서(dept) 테이블 생성 및 홍보부서 기획부서 추가
CREATE TABLE dept (
	id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name varchar(50),
	regDate datetime
);
INSERT INTO dept SET name = "홍보", regDate = now();
INSERT INTO dept SET name = "기획", regDate = now();

# 사원(emp) 테이블 생성 및 홍길동사원(홍보부서), 홍길순사원(홍보부서), 임꺽정사원(기획부서) 추가
CREATE TABLE emp (
	name varchar(100) NOT null,
	deptName varchar(100) NOT NULL
);
INSERT INTO emp SET name = "홍길동", deptName = "홍보";
INSERT INTO emp SET name = "홍길순", deptName = "홍보";
INSERT INTO emp SET name = "임꺽정", deptName = "기획";

# 홍보를 마케팅으로 변경
UPDATE dept SET name = "홍보" WHERE name = "마케팅";
UPDATE emp SET deptName = "홍보" WHERE deptName = "마케팅";
SELECT * FROM emp;

# 마케팅을 홍보로 변경
UPDATE dept SET name = "마케팅" WHERE name = "홍보";
UPDATE emp SET deptName = "마케팅" WHERE deptName = "홍보";

## 구조를 변경하기로 결정(사원 테이블에서, 이제는 부서를 이름이 아닌 번호로 기억)
ALTER TABLE emp ADD COLUMN deptId int UNSIGNED NOT NULL;
SELECT * FROM emp;
UPDATE emp SET deptId = 1 WHERE deptName = "마케팅";
UPDATE emp SET deptId = 2 WHERE deptName = "기획";
ALTER TABLE emp DROP COLUMN deptName;

# 사장님께 드릴 인명록을 생성
SELECT * FROM emp;
SELECT * FROM dept;
# 사장님께 드릴 인명록을 생성(v5, 테이블 AS 적용)
SELECT 
emp.name AS '사원이름', 
dept.name AS '부서명' 
FROM emp 
INNER JOIN dept 
ON emp.deptId = dept.id;

# 추가내용↓

# 기획부서에 김영희가 배속되었다.
## 먼저 기획부서의 번호가 몇번인지 확인
SELECT * FROM dept;
INSERT INTO emp SET name = "김영희", deptId = 2;

# 신설 IT부서에 김철수가 배속되었다.
## IT 부서의 번호가 3번임을 확인
INSERT INTO dept SET name = "IT", regDate = now();
SELECT * FROM dept;
INSERT INTO emp SET name = "김철수", deptId = 3;
SELECT 
emp.name AS '사원이름', 
dept.name AS '부서명' 
FROM emp 
INNER JOIN dept 
ON emp.deptId = dept.id;

#테스트
DROP TABLE IF EXISTS article;
CREATE TABLE article (
	id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	title varchar(100) NOT NULL,
	content text NOT null,
	hit int NOT null,
	userId int NOT NULL,
	regDate datetime NOT null
);
INSERT INTO article SET title = "우린언제", content = "가을야구하나", hit = 10, userId = 1, regDate = now();
INSERT INTO article SET title = "30분", content = "무료이용가능", hit = 11, userId = 2, regDate = now();
INSERT INTO article SET title = "코딩", content = "재밌다ㅋㅋ", hit = 12, userId = 4, regDate = now();
INSERT INTO article SET title = "엄준식은", content = "살아있다", hit = 15, userId = 3, regDate = now();
INSERT INTO article SET title = "우왕", content = "ㅋ굳ㅋ", hit = 20, userId = 5, regDate = now();
SELECT * FROM article;
DROP TABLE IF EXISTS user;
CREATE TABLE user (
	id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name varchar(100) NOT NULL,
	nickname varchar(100) NOT NULL,
	regDate datetime NOT null
);
INSERT INTO USER SET name = "user1", nickname = "독수리", regdate = now();
INSERT INTO USER SET name = "user2", nickname = "타슈맨", regdate = now();
INSERT INTO USER SET name = "user3", nickname = "엄준식", regdate = now();
INSERT INTO USER SET name = "user4", nickname = "코딩조아", regdate = now();
INSERT INTO USER SET name = "user5", nickname = "우왕굳", regdate = now();
SELECT * FROM USER;

#목표1
SELECT 
article.title AS '제목', 
article.content AS '내용', 
USER.nickname AS '작성자(닉네임)' 
FROM article INNER JOIN USER 
ON article.userId = USER.id;

#목표2
SELECT 
article.title AS '제목', 
article.content AS '내용', 
USER.nickname AS '작성자(닉네임)', 
article.hit AS '조회수'FROM article 
INNER JOIN USER 
ON article.userId = USER.id 
ORDER BY hit desc;