230308_강의 메모

왕감자·2023년 3월 8일
강의 메모
0

강의 메모

목록 보기
3/6

DB 메모

  • 테이블과 DB 조작: DDL
    데이터(row) 조작: DML
    CRUD: Create, Read, Update, Delete

  • DB 생성: create database
    DB 삭제: drop database

  • 테이블 생성: create table
    테이블 삭제: drop table
    테이블 수정: alter table

  • 데이터 생성: INSERT
    데이터 조회: SELECT
    데이터 수정: UPDATE
    데이터 삭제: DELETE

SQL 쿼리 문제1


# 전체 데이터베이스 리스팅
SHOW DATABASES;

# `mysql` 데이터 베이스 선택
USE mysql;

# 테이블 리스팅
SHOW TABLES;

# 특정 테이블의 구조
DESC user;

# `test` 데이터 베이스 선택
USE test;

# 테이블 리스팅
SHOW TABLES;

# 기존에 a1 데이터베이스가 존재 한다면 삭제
drop database if EXISTS a1;

# 새 데이터베이스(`a1`) 생성
CREATE DATABASE a1;

# 데이터베이스(`a1`) 선택
USE a1;

# 데이터베이스 추가 되었는지 확인
SHOW DATABASES;

# 테이블 확인
SHOW TABLES;

# 게시물 테이블 article(title, body)을 만듭니다.
# VARCHAR(100) => 문자 100개 저장가능
# text => 문자 많이 저장가능
CREATE TABLE article(
title VARCHAR(100),
body TEXT
);

# 잘 추가되었는지 확인, 리스팅과 구조까지 확인
SHOW TABLES;
DESC article;

# 데이터 하나 추가(title = 제목, body = 내용)
INSERT into article 
SET title = '제목',
body = '내용';

# 데이터 조회(title 만)
SELECT title FROM article;

# 데이터 조회(title, body)
SELECT title, body FROM article;

# 데이터 조회(body, title)
SELECT body, title FROM article;

# 데이터 조회(*)
SELECT * FROM article;

# 데이터 또 하나 추가(title = 제목, body = 내용)
INSERT INTO article
SET title = '제목',
body = '내용';

# 데이터 조회(*, 어떤게 2번 게시물인지 알 수 없음)
SELECT * FROM article;

# 테이블 구조 수정(id 칼럼 추가, first)
ALTER TABLE article
ADD COLUMN id INT FIRST;

# 데이터 조회(*, id 칼럼의 값은 NULL)
SELECT * FROM article;

# 기존 데이터에 id값 추가(id = 1, id IS NULL)
UPDATE article
SET id = 1
WHERE id IS null;

# 데이터 조회(*, 둘다 수정되어 버림..)
SELECT * FROM article;

# 기존 데이터 중 1개만 id를 2로 변경(LIMIT 1)1
UPDATE article 
SET id = 2
LIMIT 1;

# 데이터 조회(*)
SELECT * FROM article;

# 데이터 1개 추가(id = 3, title = 제목3, body = 내용3)
INSERT INTO article
SET id = 3,
title = '제목3',
body = '내용3';

# 데이터 조회(*)
SELECT * FROM article;

# 2번 게시물, 데이터 삭제 => DELETE
DELETE FROM article WHERE id = 2;

# 데이터 조회(*)
SELECT * FROM article;

# 날짜 칼럼 추가 => regDate DATETIME
ALTER TABLE article 
ADD regDate DATETIME AFTER id;

# 테이블 구조 확인
DESC article;

# 데이터 조회(*, 날짜 정보가 비어있음)
SELECT * FROM article;

# 1번 게시물의 비어있는 날짜정보 채움(regDate = 2018-08-10 15:00:00)
UPDATE article
SET regDate = '2018-08-10 15:00:00'
WHERE id = 1;

# 데이터 조회(*, 이제 2번 게시물의 날짜 정보만 넣으면 됩니다.)
SELECT * FROM article;

# NOW() 함수 실행해보기
SELECT NOW();

# 3번 게시물의 비어있는 날짜정보 채움(NOW())
UPDATE article
SET regDate = NOW()
WHERE id = 3;

# 데이터 조회(*)
SELECT * FROM article;

결과

SQL 쿼리 문제2

# 기존에 a2 데이터베이스가 존재 한다면 삭제
DROP DATABASE if EXISTS a2;

# 새 데이터베이스(`a2`) 생성
CREATE DATABASE a2

# 새 데이터베이스(`a2`) 선택
USE a2;

# article 테이블 생성(id, regDate, title, body)
CREATE TABLE article(
id INT,
regDate DATETIME,
title VARCHAR(100),
body TEXT)

# article 테이블 조회(*)
SELECT * FROM article;

# article 테이블에 data insert (regDate = NOW(), title = '제목', body = '내용')
INSERT INTO article
SET regDate = NOW(),
title = '제목',
body = '내용';

# article 테이블에 data insert (regDate = NOW(), title = '제목', body = '내용')
INSERT INTO article
SET regDate = NOW(),
title = '제목',
body = '내용'

# article 테이블 조회(*)
SELECT * FROM article

## id가 NULL인 데이터 생성이 가능하네?
# id 데이터는 꼭 필수 이기 때문에 NULL을 허용하지 않게 바꾼다.(alter table, not null)
## 기존의 NULL값 때문에 경고가 뜬다.
## 기존의 NULL값이 0으로 바뀐다.
UPDATE article
SET id = 0
ALTER TABLE article MODIFY id INT NOT NULL;

# article 테이블 조회(*)
SELECT * FROM article;

# 생각해 보니 모든 행(row)의 id 값은 유니크 해야한다.(ADD PRIMARY KEY(id))
## 오류가 난다. 왜냐하면 기존의 데이터 중에서 중복되는게 있기 때문에
ALTER TABLE article ADD PRIMARY KEY(id);

# id가 0인 것 중에서 1개를 id 1로 바꾼다.
UPDATE article
SET id = 1
WHERE id = 0
LIMIT 1

# article 테이블 조회(*)
SELECT * FROM article;

# id가 0인것을 id 2로 바꾼다.
UPDATE article 
SET id = 2
WHERE id = 0

# 생각해 보니 모든 행(row)의 id 값은 유니크 해야한다.(ADD PRIMARY KEY(id))
## 이제 적용이 잘 된다.
ALTER TABLE article ADD PRIMARY KEY(id);

# id 칼럼에 auto_increment 를 건다.
## auto_increment 를 걸기전에 해당 칼럼은 무조건 key 여야 한다.
ALTER TABLE article MODIFY COLUMN id int not null AUTO_INCREMENT

# article 테이블 구조확인(desc)
DESC article

# 나머지 칼럼 모두에도 not null을 적용해주세요.
ALTER TABLE article 
MODIFY COLUMN regDate DATETIME NOT NULL;
ALTER TABLE article 
MODIFY COLUMN title VARCHAR(100) NOT NULL;
ALTER TABLE article 
MODIFY COLUMN body TEXT NOT null NOT NULL;

# id 칼럼에 UNSIGNED 속성을 추가하세요.
ALTER TABLE article MODIFY COLUMN id INT UNSIGNED NOT NULL auto_increment
DESC article

# 작성자(writer) 칼럼을 title 칼럼 다음에 추가해주세요.
ALTER TABLE article ADD COLUMN writer VARCHAR(100) NOT null AFTER title;

# 작성자(writer) 칼럼의 이름을 nickname 으로 변경해주세요.(ALTER TABLE article CHANGE oldName newName TYPE 조건)
ALTER TABLE article CHANGE writer nickname VARCHAR(100) NOT null

# nickname 칼럼의 위치를 body 밑으로 보내주세요.(MODIFY COLUMN nickname)
ALTER TABLE article MODIFY COLUMN nickname VARCHAR(100) not null AFTER body

# hit 조회수 칼럼 추가 한 후 삭제해주세요.
ALTER TABLE article ADD hit INT UNSIGNED NOT NULL;
ALTER TABLE article drop hit;

# hit 조회수 칼럼을 다시 추가
ALTER TABLE article ADD hit INT UNSIGNED NOT NULL 

# 기존의 비어있는 닉네임 채워넣기(무명)
SELECT * FROM article;
update article
SET nickname = '무명'
WHERE nickname = '';

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목3', body = '내용3', nickname = '홍길순', hit = 10)
INSERT INTO article 
SET regDate = NOW(), title = '제목3', body = '내용3', nickname = '홍길순', hit = 10;

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목4', body = '내용4', nickname = '홍길동', hit = 55)
INSERT INTO article 
SET regDate = NOW(), title = '제목4', body = '내용4', nickname = '홍길동', hit = 55;

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목5', body = '내용5', nickname = '홍길동', hit = 10)
INSERT INTO article 
SET regDate = NOW(), title = '제목5', body = '내용5', nickname = '홍길동', hit = 10

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목6', body = '내용6', nickname = '임꺽정', hit = 100)
INSERT INTO article 
SET regDate = NOW(), title = '제목6', body = '내용6', nickname = '임꺽정', hit = 100

# 조회수 가장 많은 게시물 3개 만 보여주세요., 힌트 : ORDER BY, LIMIT
SELECT * FROM article ORDER BY hit DESC LIMIT 3

# 작성자명이 '홍길'로 시작하는 게시물만 보여주세요., 힌트 : LIKE '홍길%'
SELECT * FROM article WHERE nickname LIKE '홍길%'

# 조회수가 10 이상 55 이하 인것만 보여주세요., 힌트 : WHERE 조건1 AND 조건2
SELECT * FROM article WHERE hit BETWEEN 10 AND 55

# 작성자가 '무명'이 아니고 조회수가 50 이하인 것만 보여주세요., 힌트 : !=
SELECT * FROM article WHERE hit <= 50 AND nickname != '무명'

# 작성자가 '무명' 이거나 조회수가 55 이상인 게시물을 보여주세요. 힌트 : OR
SELECT * FROM article WHERE hit >= 55 OR nickname = '무명'


USE a2;
SELECT * FROM article;

결과

SQL 쿼리 문제3

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

# 부서(dept) 테이블 생성 및 홍보부서 기획부서 추가
CREATE TABLE dept(
id INT UNSIGNED NOT NULL PRIMARY KEY auto_increment,
regDate DATETIME NOT NULL,
`name` CHAR(100) NOT NULL UNIQUE)

INSERT INTO dept
SET regDate = NOW(),
`name` = '홍보';

INSERT INTO dept
SET regDate = NOW(),
`name` = '기획';

SELECT * FROM dept;

# 사원(emp) 테이블 생성 및 홍길동사원(홍보부서), 홍길순사원(홍보부서), 임꺽정사원(기획부서) 추가
CREATE TABLE emp(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
`name` CHAR(100) NOT NULL,
deptName CHAR(100) NOT NULL)

INSERT INTO emp 
SET regDate = NOW(),
`name` = '홍길동사원',
deptName = '홍보';

INSERT INTO emp 
SET regDate = NOW(),
`name` = '홍길순사원',
deptName = '홍보';

INSERT INTO emp 
SET regDate = NOW(),
`name` = '임꺽정사원',
deptName = '기획';

SELECT * FROM emp;

# 홍보를 마케팅으로 변경
select *
from dept;

update dept
set `name` = '마케팅'
where `name` = '홍보';

select *
from emp;

update emp
set deptName = '마케팅'
where deptName = '홍보';

SELECT *
FROM emp;

# 마케팅을 홍보로 변경

UPDATE dept
SET `name` = '홍보'
WHERE `name` = '마케팅';

SELECT *
FROM dept;

UPDATE emp
SET deptName = '홍보'
WHERE deptName = '마케팅';

SELECT *
FROM emp;

# 홍보를 마케팅으로 변경
# 구조를 변경하기로 결정(사원 테이블에서, 이제는 부서를 이름이 아닌 번호로 기억)
ALTER TABLE emp add COLUMN deptId int(10) unsigned not null;

select *
from emp;

update emp
set deptId = 1
where deptName = '홍보';

UPDATE emp
SET deptId = 2
WHERE deptName = '기획';

SELECT *
FROM emp;

alter table emp drop column deptName;

select *
from dept;

UPDATE dept
SET `name` = '마케팅'
WHERE `name` = '홍보';

select *
from dept;

select *
from emp;

# 사장님께 드릴 인명록을 생성
select *
from emp;

# 사장님께서 부서번호가 아니라 부서명을 알고 싶어하신다.
# 그래서 dept 테이블 조회법을 알려드리고 혼이 났다.
SELECT *
FROM dept
where id = 1;

# 사장님께 드릴 인명록을 생성(v2, 부서명 포함, ON 없이)
# 이상한 데이터가 생성되어서 혼남
SELECT emp.*, dept.name AS `부서명`
FROM emp
inner join dept;

# 사장님께 드릴 인명록을 생성(v3, 부서명 포함, 올바른 조인 룰(ON) 적용)
# 보고용으로 좀 더 편하게 보여지도록 고쳐야 한다고 지적받음
SELECT emp.*, dept.id, dept.name AS `부서명`
FROM emp
inner join dept
ON emp.deptId = dept.id;

# 사장님께 드릴 인명록을 생성(v4, 사장님께서 보시기에 편한 칼럼명(AS))
SELECT emp.id AS `사원번호`,
emp.name AS `사원명`,
DATE(emp.regDate) AS `입사일`,
dept.name AS `부서명`
FROM emp
INNER JOIN dept
ON emp.deptId = dept.id
ORDER BY `부서명`, `사원명`;

# 사장님께 드릴 인명록을 생성(v5, 테이블 AS 적용)
SELECT E.id AS `사원번호`,
E.name AS `사원명`,
DATE(E.regDate) AS `입사일`,
D.name AS `부서명`
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
ORDER BY `부서명`, `사원명`;

결과

profile
왕감자
감자 심기
이전 포스트

230224_자바 기초2

다음 포스트

230309_강의 메모

0개의 댓글