[MySQL] DB 생성 및 운영 하기

by

– DataBase 생성

shopdb 데이터베이스 생성
CREATE SCHEMA shopdb;
USE shopdb;

– members 테이블 생성

members 테이블 생성
CREATE TABLE members (
    member_id int primary key,
    member_name varchar(8) not null,
    birth date not null,
    job varchar(20),
    phone varchar(20) unique,
    address varchar(80)
);
DESC members;
products 테이블 생성
CREATE TABLE products(
    prod_id int primary key,
    prod_name varchar(20) not null,
    price int check (price > 0),
    make_date date,
    company varchar(10) not null
);
DESC products;
orders 테이블 생성
CREATE TABLE orders(
    order_num int,
    member_id int,
    prod_id int,
    order_date datetime default now(),
    PRIMARY KEY(order_num),
    FOREIGN KEY(member_id) references members(member_id),
    FOREIGN KEY(prod_id) references products(prod_id)
);
DESC orders;

– 데이터 삽입 / 수정 / 삭제

1. 데이터 삽입

members 테이블에 데이터 삽입
DESC members;

INSERT INTO members
VALUE (100, '홍길동', '2024-05-28', '학생', '010-1111-1111', '부산 부산진구 부전동');
생략된 컬럼에는 자동으로 null 값이 삽입됨
INSERT INTO members (member_id, member_name, birth, phone)
VALUES (101, '김민수', '1990-03-05', '010-2222-2222');

INSERT INTO members (member_id, member_name, phone, address, birth)
VALUES (102, '최아영', '010-3333-3333', '서울 강남구 선릉로', '2025-01-10');

INSERT INTO members (member_id, member_name, birth, job, phone)
VALUES (103, '홍길동', '1998-05-10', '회사원', '010-4444-4444');

INSERT INTO members (member_id, member_name, birth, job)
VALUES (104, '강주영', '1998-10-10', '대학생');

INSERT INTO members (member_id, member_name, phone, birth, address, job)
VALUES (105, '고승현', '010-5555-5555', '1995-01-10', '경기도 부천시 원미구', '트레이너');

INSERT INTO members (member_id, member_name, birth, job, phone)
VALUES (106, '정유빈', '1970-02-04', '회사원', '010-6666-6666');

INSERT INTO members (member_id, member_name, birth, phone)
VALUES (107, '이영수', '1988-12-06', '010-7777-7777');

INSERT INTO members (member_id, member_name, birth, phone, address)
VALUES (108, '김철수', '1999-01-15', '010-8888-8888', '부산 해운대구 센텀로');

INSERT INTO members (member_id, member_name, birth, job, phone, address)
VALUES (109, '최승현', '1995-04-22', '간호사', '010-9999-9999', '서울 강북고 수유동');

INSERT INTO members (member_id, member_name, birth, job, phone, address)
VALUES (110, '한주연', '2001-08-24', '승무원', '010-1010-1010', '대구 수성구 수성로');

SELECT * FROM members;
products 테이블에 데이터 삽입
DESC products;

INSERT INTO products VALUES
(10, '냉장고', 500, null, '삼성');

INSERT INTO products VALUES
(20, '컴퓨터', 150, '2022-01-13', '애플'),
(30, '세탁기', 250, '2020-03-10', 'LG'),
(40, 'TV', 200, '2021-09-30', 'LG'),
(50, '전자렌지', 50, '2019-06-20', '삼성'),
(60, '건조기', 300, '2021-07-09', 'LG');

SELECT * FROM products;
orders 테이블에 데이터 삽입
DESC orders;

INSERT INTO orders
VALUES (1, 101, 20, '2022-02-01');

INSERT INTO orders
VALUES (2, 107, 40, '2022-02-05 17:51');

INSERT INTO orders
VALUES (3, 106, 50, now());

-- default 값이 선언된 컬럼은 데이터 삽입 시 생략하면 자동으로 default 값 삽입됨.
INSERT INTO orders (order_num, member_id, prod_id)
VALUES (4, 104, 10);

-- default 값 삽입하기
INSERT INTO orders
VALUES (5, 108, 50, default);

INSERT INTO orders
VALUES (6, 103, 30, default);

INSERT INTO orders
VALUES (7, 105, 60, default);

INSERT INTO orders
VALUES (8, 110, 40, '2021-12-30 10:30:45'),
(9, 107, 30, default),
(10, 101, 60, now());

SELECT * FROM orders;
stu20 테이블에 데이터 삽입
DESC stu20;

INSERT INTO stu20
VALUES (null, '김온달', 28);

INSERT INTO stu20
VALUES (null, '이평강', 25);

-- 시작 값 변경하기
ALTER TABLE stu20 AUTO_INCREMENT = 100;

INSERT INTO stu20
VALUES (null, '최찬미', 29);

INSERT INTO stu20
VALUES (null, '김동희', 31);

DELETE FROM stu20 WHERE stu_id = 102;

-- 증가값 변경하기
SET @auto_increment_increment = 5;

INSERT INTO stu20
VALUES (null, '박혜경', 22);

INSERT INTO stu20
VALUES (null, '문진원', 27);

SELECT * FROM stu20;

2. 데이터 수정 (update)

products 테이블의 모든 상품의 가격을 50씩 인상
UPDATE products
SET price = price * 50;
products 테이블에서 TV 제품 가격을 30 인상
UPDATE products
SET price = price + 30
WHERE prod_name = 'TV';

SELECT * FROM products;
members 테이블 105번 회원 전화번호를 010-5050-5050 으로 변경
UPDATE members
SET phone = '010-5050-5050'
WHERE member_id = 105;
members 테이블의 102번 회원 직업을 ‘개발자’ 로 변경
UPDATE members
SET job = '개발자'
WHERE member_id = 102;
members 테이블의 104번 회원 직업을 null 로 변경
UPDATE members
SET job = null
WHERE member_id = 104;

SELECT * FROM members;

3. 데이터 삭제 (delete)

stu20 테이블에서 나이가 25세 이하인 학생을 삭제
DELETE FROM stu20
WHERE age <= 25;
stu20 테이블의 모든 학생을 삭제
DELETE FROM stu20;

SELECT * FROM stu20;

4. 데이터 조회

테이블로부터 모든 컬럼, 모든 행 출력
SELECT * FROM members;
SELECT * FROM products;
SELECT * FROM orders;
테이블로부터 특정 컬럼 출력
SELECT member_id, member_name, phone, address
FROM members;

SELECT company, prod_name, price
FROM products;
테이블로부터 특정 행 출력
members 테이블에서 member_id가 105번인 회원 정보 출력
SELECT *
FROM members
WHERE member_id = 105;
members 테이블에서 이름이 ‘홍길동’ 인 회원 정보 출력
SELECT *
FROM members
WHERE member_name = '홍길동';
members 테이블에서 직업이 ‘회사원’ 인 회원 정보 출력
SELECT *
FROM members
WHERE job = '회사원';
members 테이블에서 직업이 ‘회사원’ 이 아닌 회원 정보 출력
SELECT *
FROM members
WHERE job != '회사원';
!=, <> 둘다 같지 않다는 의미
products 테이블에서 가격이 300 이상인 제품의 이름과 가격을 출력
SELECT prod_name, price
FROM products
WHERE price >= 300;
members 테이블에서 생년월일이 1990년 이전인 회원의 이름, 생년월일, 전화번호, 주소를 출력
SELECT member_name, birth, phone, address
FROM members
WHERE birth < '1990-01-01';
members 테이블에서 생년월일이 1990년도 인 회원 만 출력
SELECT member_name, birth, phone, address
FROM members
WHERE birth BETWEEN '1990-01-01' AND '1990-12-31';

– 데이터 조회 (계속)

products 테이블에서 ‘LG’ 제품 중 가격이 300 이하인 제품만 출력
select *
from products
where company = 'LG' and price <= 300;
products 테이블에서 ‘삼성’ 과 ‘LG’ 제품만 출력
select *
from products
where company = 'LG' or company = '삼성';
where 절에 and 와 or 을 함께 사용하면 우선순위가 and 가 높다!!
예제1: and 가 먼저 실행되고 or 가 실행됨
select *
from products
where company = 'LG' or company = '삼성' and price <= 300;
예제2: or 가 먼저 실행되고 and 가 실행됨
select *
from products
where (company = 'LG' or company = '삼성') and price <= 300;
정렬하기
오름차순 (가격기준)
select * from products
order by price;
내림차순 (가격기준)
select * from products
order by price desc;
오름차순 (날짜기준 -> 예전 날짜부터 현재날짜 순서로)
select * from products
order by make_date;
내림차순 (날짜기준 -> 현재 날짜부터 예전 날짜 순서로)
select * from products
order by make_date desc;
오름차순 (이름기준)
select * from members
order by member_name;
내림차순 (이름기준 -> 같은 이름일때 생년월일 기준으로)
select * from members
order by member_name desc, birth;
내림차순 (회사명 기준, 가격 기준)
select * from products
order by company desc, price desc;

Leave a Comment