MySQL(7) - DML(INSERT, UPDATE, DELETE), 제약 조건

<목차>

1) DML(INSERT, UPDATE, DELETE)

   1-1) 개념

   1-2) 예제

2) 제약 조건

   2-1) 개념

   2-2) 예제

 

 

 

 

 

1) DML(INSERT, UPDATE, DELETE)

1-1) 개념

 

 

 

 

1-2) 예제

/*
	DML
	- SELECT, INSERT, UPDATE, DELETE
*/

-- DML 연습용 테이블
drop table testTable1;
create table testTable1(
	id         int,
	userName   varchar(10),
	age        int
);

select * from testTable1;

 

/*
	INSERT 문 사용
*/

-- 원하는 데이터만 삽입할 경우
insert into testtable1(id, userName) values(1, '홍길동');

 

 

 

-- 데이터의 순서를 변경해 삽입할 경우
insert into testtable1(age, userName, id)
values(33, '이순신', 2);

 

 

 

-- 모든 데이터를 삽입할 경우
insert testtable1 values(3, '강감찬', 55);

 

 

 

-- 서브쿼리를 사용하여 삽입할 경우
insert testtable1(
	select 4, '곽재우', 60 from dual
);

 

 

 

-- AUTO_INCREMENT
-- AUTO_INCREMENT로 이용할 컬럼은 INT형 자료형을 가지고 있어야 한다.
-- 중복이 없는 값이기 때문에 주로 기본키(PRIMARY KEY or UNIQUE)로 이용한다.
-- 기본키가 아니라면 NULL이 들어가는 것을 방지하기 위해
-- NOT NULL 조건을 추가해야 한다.
-- (MySQL에서 가지는 기본키 생성전략)

 

drop table testtable2;
create table testtable2(
	id         int           auto_increment     primary key,
	userName   varchar(10),
	age        int
);
select * from testtable2;

 

insert testtable2 values(null, '홍길동', 11);
insert testtable2 values(null, '이순신', 12);
insert testtable2 values(null, '강감찬', 13);

 

 

 

-- 현재 증가된 숫자 확인
select last_insert_id();

 

 

 

-- 시작값 변경
alter table testtable2 auto_increment = 100;
insert testtable2 values(null, '곽재우', 14);

 

 

 

-- 증가값 변경
-- "@@"는 시스템 변수, 시스템 변수의 세션값이나 전역값을 얻을 수 있다.
set @@auto_increment_increment = 3;
insert testtable2 values(null, '도우너', 15);
insert testtable2 values(null, '마이콜', 16);

 

 

 

-- 한꺼번에 insert
insert testtable2 values
	(null, '둘리', 20), (null, '또치', 30), (null, '고길동', 40);

 

 

 

-- 대량의 샘플 데이터 생성
drop table testtable3;
create table testtable3(
	id          int,
	userName    varchar(10),
	age         int
);
select * from testtable3;
insert testtable3
	select id, userName, age from testtable2;

 

 

 

/*
	UPDATE 문 사용
*/

select * from testtable3;
-- where 절로 조건부 변경
update testtable3
set userName = '하얀 둘리', age = 99
where id = 109;

 

 

 

-- TCL
commit;
rollback;

 

-- 현재 autocommit 설정 확인
-- 0: 사용 안함, 1: 사용 중
select @@autocommit;
-- autocommit 해제
set autocommit = 0;

 

-- 전체 데이터 변경
update testtable3
set userName = '전부 변경', age = 10;

※ 여기서 위의 코드를 실행하기 전에 "commit" 명령을 먼저 실행하여 아래 "전부 변경"과 "10"으로 컬럼 내용이

바뀌기 전에 원래 데이터 내용을 영구적으로 저장해놓고, 해당 transaction을 종료시킴! 

 

이후 "rollback" 명령을 실행하여 보류 중인 모든 데이터 변경사항을 폐기한 뒤 현재 트랜잭션을 종료하고,

직전 commit 직후의 단계로 회귀시킴(= 되돌아가기)

 

 

/*
	DELETE 문 사용
*/
select * from testtable3;
-- where 절로 조건부 삭제
delete from testtable3 where id = 112;

 

 

 

-- 전체 삭제
delete from testtable3;

 

 

 

-- LIMIT
-- 출력하는 개수를 제한
select 
	employee_id, last_name, salary
from employees
order by salary desc
limit 10;

 

 

 

select 
	employee_id, last_name, salary
from employees
order by salary desc
limit 3, 5;  -- 시작 index(index가 0부터 카운트됨!), 개수

 

 

 

-- LIMIT을 이용한 삭제
select * from testtable3 order by age desc;

 

 

 

insert testtable3 values
	(null, '또치', 50), (null, '감자', 60), (null, '고양이', 70);

 

 

delete from testtable3 order by age desc limit 3;

 

 

 

 

2) 제약 조건

2-1) 개념

 

 

2-2) 예제

/*
	제약조건(constraint)
	데이터의 무결성을 지키기 위해, 데이터를 입력받을 때 실행되는 검사 규칙을 의미
	
	CHECK
		컬럼에 들어갈 수 있는 값을 제한
		부호, 숫자 비교, 문자 비교 등을 비교할 수 있다.
		입력되는 값이 CHECK 조건과 맞지 않으면 에러가 발생
	
	NOT NULL
		- NULL 비허용
		- 중복값 허용
	
	UNIQUE
		- NULL 허용
		- 중복값 비허용
	
	DEFAULT
		- 기본값 추가
	
	PRIMARY KEY
		- UNIQUE + NOT NULL
		- NULL 비허용
		- 중복값 비허용
		- 테이블당 하나
	
	FOREIGN KEY
		- FOREIGN KEY 제약조건을 설정한 컬럼은 외래키라고 부르며,
		  한 테이블을 다른 테이블과 연결해주는 역할
		- 외래키가 포함된 테이블을 자식 테이블이라고 하고,
		  외래키 값을 제공하는 테이블을 부모 테이블이라고 한다.
*/

 

-- 제약 조건
create table student(
	-- 컬럼명           -- 자료형         -- 제약조건
	student_id       varchar(30)    primary key,
	student_name     varchar(30)    unique,
	student_address  varchar(100)   not null,
	student_gender   char(6)        check(student_gender in ('남성', '여성')),
	student_phone    varchar(30),
	reg_date         date           default(current_date),
	student_class    char(2)        check(student_class in ('A', 'B', 'C'))
);

drop table student;

select * from student;

 

-- INSERT
-- 전체 데이터 삽입
insert into student values(
	20241001, '마이콜', '광주광역시', '남성', '01011112222', current_date(), 'B'
);

 

 

 

-- 부분 데이터 삽입(default 확인)
insert into student(
	student_id, student_name, student_address, student_gender, student_phone, student_class)
values(
	20241002, '또치', '부산광역시', '남성', '01022223333', 'B'
);

 

 

 

-- 부분 데이터 삽입(unique 확인)
insert into student values(  -- 또치 = 중복 에러
	20241003, '또치2', '서울특별시', '여성', '01033334444', current_date(), 'C'
);

 

 

 

-- 부분 데이터 삽입(check 확인)
insert into student values(  -- 성별에 중성 쓰면 check 에러
	20241004, '둘리', '삼성역', '남성', '01044445555', current_date(), 'A'
);

 

 

 

-- 부분 데이터 삽입(not null 확인)
insert into student values(  -- 주소에 null 쓰면 에러
	20241005, '홍길동', '코엑스', '남성', '01055556666', current_date(), 'A'
);

 

 

 

/*
	외래키(FOREIGN KEY)
*/
create table score(
	score_id        varchar(30),     -- references student(student_id),  --> 이렇게 해당 컬럼에 바로 쓸 수 있으며, 이렇게 쓰면 아래 foreign key 키워드 부분을 쓸 필요 없음!
	score_java      int,
	score_db        int,
	score_web       int,
	score_date      date             default(current_date),
	foreign key(score_id)
	references student(student_id)
	on delete cascade
	-- CASCADE : 참조되는 테이블에서 데이터를 삭제하면, 참조하는 테이블에서도 삭제를 같이 진행
);

drop table score;

select * from score;

 

 

 

insert into score values(  -- student_id가 존재하므로 사용
	20241001, 90, 75, 30, current_date()
);

 

 

 

-- 에러를 발생시키는 INSERT 문이다!
insert into score values(  -- student_id가 존재하지 않으면 사용할 수 없음
	20243333, 80, 95, 45, current_date()
);

 

 

select
	student_id, student_name,
	score_java, score_db, score_web
from
	student join score
	on student_id = score_id;

 

 

 

delete from student where student_id = 20241001;

 

select * from student;

 

 

 

select * from score;