[SQL]MySQL_제약조건 있는 테이블 활용 및 ROLLBACK 실습

 

---

 

 

데이터 분석을 위한 MySQL

 

✔️ CRUD

✔️ 제약조건이 있는 테이블 활용

✔️ 트랜젝션과  Rollback

 

 

---

 

 

✔️ CRUD

 

 

CRUD 작업과 SQL 연관

 

 

 

데이터베이스 생성 및 제거

-- DB 생성
create database 데이터베이스명;

-- DB 제거
drop database 데이터베이스명;

워크벤치 내에서 데이터베이스 생성 가능

 

 

 

테이블 생성 및 제거

-- 테이블 생성
create table 테이블명(컬럼명1(데이터타입), 컬럼2(데이터타입),...)

-- 테이블 제거
drop table 테이블명;

market_db 및 테이블 생성 실습 수행

-- market_db 생성
create database market_db;
use market_db; -- DB 사용

-- hongonh1 테이블 생성
-- toy_id(int), toy_name(char(4)), age(int)
create table hongong1 (toy_id int, toy_name char(4), age int);

-- 테이블 생성 확인
show tables;

-- 테이블 조회
-- 원하는 형식으로 만들어졌는지 확인
desc hongong1;

 

 

생성한 테이블에 데이터 입력

-- 데이터 입력
insert into 테이블명(컬럼1, 컬럼2, ...) values (컬럼1의 값, 컬럼2의 값,...)

-- 데이터 입력 수행
insert into hongong1 values (1, '우디', 25);

-- 입력되지 않은 컬럼의 값은 NULL 입력됨
insert into hongong1(toy_id, toy_name) values (2, '버즈');

-- 컬럼에 맞춰 데이터를 입력한다면 정확히 값이 입력됨
insert into hongong1(toy_name, age, toy_id) values('제시', 20, 3);

-- 테이블 컬럼에 맞게 데이터를 입력하면 그대로 입력됨
insert into hongong1 values(4, '포테이토', 40);

 

 

---

 

 

✔️ 제약조건이 있는 테이블 활용

 

 

제약조건

워크벤치 내의 테이블 생성 시 제약 조건 설정

 

NOT NULL : NULL 값 방지

UNIQUE : 중복 값 방지

PRIMARY KEY : 기본키( NULL 불가)

FOREIGN KEY : 참조키( 무결성 유지 )

DEFAULT : 기본값 설정

AUTO_INCREMENT : 자동 증가 값 생성

 

 

 

데이터 타입

정수형(부호 있음/부호 없음)

TINYINT : 아주 작은 정수 저장

INT : 일반적인 크기의 정수 저장

BIGINT : 매우 큰 정수 저장

 

 실수형(길이, 소수점 이하)

FLOAT : 데이터 크기가 크고, 대략적인 값(소수)

DOUBLE : 아주 정확한 숫자 필요한 경우 소수점 이하까지 정확한 값(소수)

DECIMAL : 고정 소수점 숫자

 

문자열

CHAR : 고정 길이 문자열, 지정한 길이만큼 항상 공간 예약

VARCHAR : 가변 길이 문자열, 실제 데이터 길이만큼만 공간 사용

TEXT : 가변 길이 텍스트, 중간 크기 문자열 저장

MEDIUMTEXT : TEXT보다 더 큰 텍스트 저장

LONGTEXT : 매우 큰 텍스트 저장

 

 

PK, auto increment 기능을 추가한 테이블 생성

-- 조건이 있는 테이블 생성
create table hongong2 (
	toy_id int auto_increment primary key, 
	toy_name char(4), 
	age int
);

-- 생성된 테이블 확인
desc hongong2;

-- AUTO INCREMENT가 지정된 테이블에 데이터 입력
-- AUTO INCREMENT 지정된 컬럼에 데이터 입력 시 NULL 입력

insert into hongong2 values(null, '보핍', 25);
insert into hongong2 values(null, '슬링키', 22);
insert into hongong2 values(null, '렉스', 21);

-- 데이터 조회
select * from hongong2;

 

 

 

 ALTER를 통한 테이블 수정

-- 컬럼 추가(기본 형식)
ALTER TABLE 테이블명 ADD COLUMN 컬럼명, 자료형, 속성(NOT NULL, UNIQUE);


-- hongong2 테이블에 country 컬럼 추가 -> 전에 입력된 값들은 NULL값이 들어감
alter table hongong2 add column country varchar(100);

-- 기존 테이블에 있는 자료 UPDATE + where과 함께 사용 
-- UPDATE 테이블명 SET 컬럼명 = 업데이트할 값 WHERE toy_id = 1;

update hongong2 set country = '미국' where toy_id = 1;
update hongong2 set age = 30 where toy_id = 1;

 

 

테이블의 데이터 삭제

TRUNCATE : 테이블의 내용은 전부 지우고 테이블의 구조는 남기고 싶은 경우 

-- 기본 형식
TRUNCATE TABLE 테이블명;

-- hongong2 테이블 제거
truncate table hongong2;

 

 

DELETE : 전체 또는 특정 조건의 데이터 삭제(Roll Back 가능)

-- 기본 형식
DELETE FROM 테이블명 WHERE 조건;

-- 실습 수행
-- PK가 따로 지정되어 있지 않기 때문에 오류 발생
delete from hongong1 where toy_id = 2;

-- idx 컬럼추가 primary key로 지정 auto_increment
alter table hongong1 add column idx int auto_increment Primary key;

-- idx 추가되었는지 테이블 확인
select * from hongong1;

-- 데이터 삭제 수행
delete from hongong1 where idx = 2;

-- 데이터 추가 및 삭제
insert into hongong1 values(7, '렉스', 30, null);
delete from hongong1 where idx = 4;

-- 테이블 확인
select * from hongong1;

 

 

제약조건이 있는 테이블 실습

 

member 테이블 생성 실습

-- member 테이블 생성
drop table if exists member;
create table member (
	id int auto_increment primary key,
    member_id varchar(5) not null,
    name varchar(20) not null,
    address varchar(225)
);

-- member 테이블 데이터 입력 및 확인
desc member;


-- 데이터 입력
insert into member values(null,'tess','나훈아','경기 부천시 중동');
insert into member values(null,'hero','임영웅','서울 은평구 증산동');
insert into member values(null,'iyou','아이유','인천 남구 주안동');
insert into member values(null,'jyp','박진영','경기 고양시 장항동');

-- 테이블의 데이터 확인
select * from member;

 

product 테이블 생성 실습

-- product 테이블 생성
drop table if exists product;
create table product (
	prod_name varchar(20) not null,
	price int,
    date_of_prod date not null,
    manuf varchar(50),
    balance int
);

-- product 테이블 확인
desc product;


-- 데이터 입력
insert into product values('바나나',1500,'2024-07-01','델몬트',17);
insert into product values('카스',2500,'2023-12-12','OB',3);
insert into product values('삼각김밥',1000,'2025-02-26','CJ',22);

-- product 테이블에 prod_id 컬럼 추가 + auto_increment, primary key 적용
alter table product add column prod_id int auto_increment primary key;

-- product 테이블 확인
select * from product;

 

 

---

 

 

✔️ 트랜젝션과  Rollback

 

 

트랜젝션 :데이터베이스에서 여러 SQL 명령을 하나의 작업 단위로 묶는 것

Rollback : 트랜잭션 시작 이후의 모든 변경을 취소하고 원래 상태로 복구

-- 데이터베이스 생성
create database mywork;
use mywork;

-- 테이블 생성
create table emp_test (
	emp_no int not null,
    emp_name varchar(30) not null,
    hire_date date null,
    salary int null,
    primary key(emp_no)
);

-- emp_test 테이블 확인
desc emp_test;

-- 데이터 입력
insert into emp_test(emp_no, emp_name, hire_date, salary) 
values (1005, '쿼리', '2021-03-01',4000),
(1006, '호킹', '2021-03-05',5000), 
(1007, '페러데이', '2021-04-01',2200), 
(1008, '맥스웰', '2021-04-04',3300),
(1009, '플랑크', '2021-04-05',4400);

-- 테이블의 데이터 확인
select * from emp_test;

 

 

Rollback 실습

-- 테이블 생성
create table emp_tran1 as select * from emp_test;

-- 테이블의 데이터 확인
select * from emp_tran1;

-- Primart Key 설정이 되어있지 않음
desc emp_tran1; 

 -- Primary Key 설정
alter table emp_tran1 add constraint primary key(emp_no);
desc emp_tran1;


-- rollback 수행

-- 오토커밋 옵션 활성화 확인
-- edit -> Perfences -> SQL editor -> SQL Execution -> Auto commit
-- AUTOCOMMIT 비활성화
set autocommit = 0;

-- 트랜잭션 시작
start transaction;

-- 호킹의 SALARY를 10000으로 변경
update emp_test set salary = 10000 where emp_no = 1006;

-- 변경된 데이터 확인
SELECT * FROM emp_test WHERE emp_no = 1006;

-- 변경 취소 (원래 상태로 복구)
ROLLBACK;

-- ROLLBACK 후 데이터 확인
SELECT * FROM emp_test WHERE emp_no = 1006;

-- AUTOCOMMIT 원래 상태로 복구
SET AUTOCOMMIT = 1;

 

 

---

 

 

데이터 분석을 위한 SQL 입문_2

CREATE, INSERT, UPDATE, DELETE, SELECT 활용과 

제약 조건이 있는 테이블의 생성 및 데이터 입력 활용에 대해 알아보았습니다.

자세한 내용은 혼자 공부하는 SQL 블로그 글도 참고해주시기 바랍니다.

 

'혼공시리즈/혼공S_혼자 공부하는 SQL' 카테고리의 글 목록

(위 내용은 혼자 공부하는 SQL 정리본으로 MySQL에 관한 다양한 내용을 정리해놓았습니다.)