[22.10.31] 8일차 [데이터베이스] MySQL 데이터 삽입, 수정, 삭제, 조회

products 테이블에 null 값 삽입하기

insert into products
values (10, '냉장고', 500, null, '삼성');

다음 5개행을 products 테이블에 삽입하시오.

insert into products
values (20, '컴퓨터', 150, '2022-01-13', '애플');

insert into products
values (30, '세탁기', 250, '2020-03-10', 'LG');

insert into products
values (40, 'TV', 200, '2021-09-30', 'LG');

insert into products
values (50, '전자렌지', 50, '2019-06-20', '삼성');

insert into products
values (60, '건조기', 300, '2021-07-09', 'LG');

• 테이블 데이터 조회

select *
from product

• 한꺼번에 입력하기

*  콤마 이용
insert into products
values (40, 'TV', 200, '2021-09-30', 'LG'),
(50, '전자렌지', 50, '2019-06-20', '삼성'),
(60, '건조기', 300, '2021-07-09', 'LG');

orders 테이블에 데이터 삽입하기

desc orders;

insert into orders
values (1, 101, 20, '2022-02-01');

select *
from orders;

insert into orders
values (2, 107, 40, '2022-02-05 17:51');

date : 연월일
datetime : 연월일 시분초

* now 함수 사용해서 오늘 날짜 삽입
insert into orders
values (3, 106, 50, now( ));

* default값이 선언된 칼럼에 자동으로 default값 삽입하기
insert into orders(order_num, member_id, prod_id) 
values (4, 103, 10);

* default값이 선언된 칼럼에 수동으로 default값 삽입하기
insert into orders
values (5, 108, 50, default);

foreign key 관계

[members]

member_id	..
100
101
102
103
104
부모컬럼

[orders]

order_num	member_id	prod_id	...
1	101	20
2	107	40
3	106	50
	fk(자식)	fk(자식)

[products]

prod_id	...
10
20
30
40
50
부모컬럼

** foreign key 제약조건이 선언된 컬럼 데이터 삽입1
insert into orders
values (6, 120, 30, default);
=> Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails

· 관계 : orders 테이블의 member_id 컬럼(자식 컬럼, FK 제약조건이 선언된 컬럼) 
 → members 테이블의 member_id 컬럼(부모 컬럼)

· members 테이블에는 120번 멤버는 존재하지 않으므로 주문을 할 수 없다.

수정))
insert into orders
values (6, 103, 30, default);

· FK 제약조건 : 다른 테이블의 특정 컬럼을 참조하는 제약조건

· FK 제약조건이 선언된 컬럼(자식 컬럼)에는 반드시 부모 컬럼의 데이터 값 중 하나만 삽입/수정될 수 있다.

** foreign key 제약조건이 선언된 컬럼 데이터 삽입2
insert into orders
values (7, 105, 80, default);
=> Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails 

· 관계 : orders 테이블의 prod_id 컬럼(자식 컬럼, FK 제약조건이 선언된 컬럼) 
 → products 테이블의 prod_id 컬럼(부모 컬럼)

· 오류 원인 : products 테이블에는 80번 상품이 존재하지 않으므로 해당 상품을 주문할 수 
 없다. 
 
수정))
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());

• orders 데이터 조회 (select * from orders)

AUTO_INCREMENT 속성이 선언된 테이블에 데이터 삽입

• 저번 시간에 auto_increment 속성이 적용된 테이블 stu20 이용
• auto_increment 속성의 기본값은 1부터 시작해서 1씩 증가하는 값 반환됨.

insert into stu20
values (null, '김온달', 28);

insert into stu20
values (null, '이평강', 24);

insert into stu20(stu_name, age)
values ('김철수', 20);

• auto_increment 속성의 시작값(입력값) 변경

alter table stu20 auto_increment=100;

insert into stu20
values(null, '최찬미', 29); -- 100 반환됨

insert into stu20
values (null, '김동희', 31); -- 101 반환됨

• auto_increment 증가값(증가 사이즈) 변경

set @@auto_increment_increment=5; -- 5로 변경

insert into stu20
values (null, '박혜경', 22); -- 106 반환됨

insert into stu20
values (null, '문진원', 27); -- 111 반환

데이터 수정 : UPDATE

update 테이블명
set 컬럼명 = 값
[where 조건문];
 
update 테이블명
set 컬럼명1 = 값1, 컬럼명2 = 값2, ... [where 조건문];

** products 테이블의 모든 상품의 가격을 50씩 인상하시오.

update products
set price = price + 50;

** products 테이블의 TV 제품 가격을 30 인상하시오.

update products
set pricd = price + 30
where prod_name = 'TV';

where절 작성 시 특정 행만 수정됨.

where 좌변(컬럼명) = 우변(리터럴값);
=에 들어갈수 있는 값 : >, >=, <, <=, <>, =

** members 테이블 105번 회원 전화번호를 010-5050-5050으로 변경하시오.

update members
set phone = '010-5050-5050'
where member_id = 105;

** foreign key 제약조건이 선언된 컬럼 데이터 수정

update orders
set member_id = 120
where order_num = 2;--오더번호 2번의 주문자 변경
=> Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails

-- update 작업 시 제약조건 만족해야 함.
-- 120번 회원은 존재하지 않으므로 FK 제약조건에 위배가 됨.

수정)) update orders
 set member_id = 109
 where order_num = 2;

데이터 삭제 : DELETE

delete from 테이블명 
[where 조건문];

where절 작성 시 특정 행 삭제됨.
where절 생략 시 모든 행 삭제됨.

stu20 테이블에서 나이가 25세 이하인 학생을 삭제하시오.

delete from stu20
where age <=25;

• DBMS의 auto-commit(자동저장) 기능 설정 여부
  - auto-commit 활성화된 상태 : DML(insert, update, delete) 작업 시 자동 저장됨.
  작업 취소 안됨.
  - auto-commit 비활성화된 상태 : DML 작업시 자동 저장 안됨.
  DML 작업 후 저장 또는 취소를 추가로 작업해야함.
  commit(저장), rollback(취소) 명령어로 작업 마무리해야함.

• auto-commit 해제하기
  

[Query] - [Auto-commit] 해제된 상태에서
stu20 테이블의 모든 행 삭제하시오.

모든 행 삭제 후 데이터 확인(미리보기, 저장된 상태 아님)

★ 작업 되돌리기

roll back;

auto-commit은 다시 켜놓기..

• MySQL Workbench 사용 시에는 설정 확인하기
  ① [Query] - [Auto-Commit Transactions] 가 체크되어 있는 경우(기본)
  - DML 발생 시 바로 자동 저장되므로 작업 취소를 할 수 없다.
  - 장점 : 따로 저장할 필요가 없으므로 편함.
  - 단점 : 자동 저장되므로 작업 후 되돌릴 수 없음
  ② [Query] - [Auto-Commit Transactions] 가 체크 해제되어 있는 경우
  - DML 작업 후 commit 또는 rollback을 개발자가 결정해야 한다.
  - 장점 : 작업 후 select 구문을 통한 충분한 미리보기 후 저장 또는 취소를 결정할 수 있음. 그러므로 작업을 실수한 경우 되돌릴 수 있음.
  - 단점 : 작업 후 commit 또는 rollback으로 마무리를 반드시 해줘야 함

데이터 조회

select * ---> 컬럼 관련
from 테이블명
[where 조건문.]; ---> 행 관련

select 컬럼1, 컬럼2, 컬럼3, ... from 테이블명 
[where 조건문.];

테이블의 모든 컬럼, 모든 행 조회하기

select *
from members;

select *
from products;

select *
from orders;

테이블의 특정 컬럼 조회하기

select절에 출력하고 싶은 컬럼을 출력하고 싶은 순서대로 나열하면 됨.

// members 테이블의 특정 컬럼 조회
select member_id, member_name, phone
from members;

// products 테이블의 특정 컬럼 조회
select company, prod_name, price
from products;

// orders 테이블의 특정 컬럼 조회
select order_num, order_date
from orders;

select 구문에 산술연산자 활용하기

- select 절에 산술 연산자(+, -, *, %)를
활용한 산술식 작성 가능함.
- 컬럼 alias : 컬럼 제목을 컬럼명이나 산술식이 아닌 원하는 제목으로 출력할 경우 사용

select 컬럼1, 컬럼2 [AS] ‘alias’
from 테이블명
[where 조건문];

** alias 작은 따옴표, 큰 따옴표 모두 허용(Mysql)
다른 DMBS는 큰 따옴표만 허용..

** as는 생략 가능

select prod_id, prod_name, 
price + 50 as "인상 예정 가격"
from products;

select prod_id, prod_name, 
price as "원가", price * 0.9 as "10% 세일 가격"
from products;

테이블로부터 특정 행 출력하기[where절]

- where절을 작성한 경우 조건에 만족되는 행만 반환됨.

[문법] select * | 컬럼명1, 컬럼명2, 컬럼명3 -> 모든 컬럼 또는 특정 컬럼
      from 테이블명;
      where 좌변      =       우변;
          (컬럼명)(비교연산자)(리터럴값)
 --                          -> 숫자, '문자', '날짜'

where절을 활용한 데이터 검색 – 단일행 비교연산자(=, >, >=, <, <=, <>)

// members 테이블에서 member_id가 105번인 회원정보를 출력하시오.

select *
from members
where member_id = 105;

// members 테이블에서 이름이 ‘홍길동’인 회원만 조회
select *
from members
where member_name = '홍길동';

// products 테이블에서 가격이 300 이상인 제품의 이름과 가격 조회
select prod_name, price
 from products
 where price >= 300;
 

// members 테이블에서 생년월일이 1990년 이전인 회원의 이름, 생년월일, 전화번호 조회
select member_name, birth, phone
from members
where birth < '1990-01-01';