[MySQL] MySQL 문법 요약 - 섹션 2. MySQL 수정,삭제,관리

1. 데이터 베이스 및 테이블 생성
--1) 데이터베이스 만들기 // create database 이름
-- 생성된 데이터베이스는 use문을 이용하여 선택해야 한다.
-- utf-8 인코딩 타입의 한글을 저장하려면 반드시 다음과 같이 캐릭터셋에 언어타입을 지정해준다.
-- create database 이름
-- character set = 'utf8'
-- collate = 'utf8_general_ci'; 

-- test_db 라는 데이터베이스를 생성하고 한글을 저장할수 있게 만드시오.
create database test_db
character set = 'utf8'
collate = 'utf8_general_ci';

use test_db; -- 이렇게 선택해도 되고 더블클릭해도 됨

-- 2) 테이블 만들기
-- 데이터베이스를 선택한 후, create table 명령문
--  create table 이름(
-- 컬럼이름 자료형 제약조건,
-- 컬럼이름 자료형 제약조건
-- ); 
-- 자료형
-- char(n) : char는 10개 공간이 미리 배정되어 고정길이 문자열. 이름이나 우편번호 작성시 많이 사용한다. 속도가 빠르다.
-- varchar(n) : varchar는 1개 공간이 쓸때마다 늘어나는 형태로 가변길이 문자열. 게시판이나 댓글 작성시 많이 사용한다. 
-- int(n), bigint(n) : 정수형 타입
-- float(n,m), double(n,m) : 부동소수점 타입
-- date(날짜), time(시간), datetime(날짜와시간)

create table test_table1(
data1 int(10),
data2 varchar(10),
data3 float(10,2)
);

desc test_table1; -- test_table1의 컬럼들을 조회함

select * from test_table1; -- 지금까지는 아무것도 저장하지 않았기 때문에 빈 값이 출력됨. 

-- 3) 서브쿼리를 이용한 테이블 생성하기
-- select문을 통해 가져온 결과를 이용해 테이블을 생성할때 사용한다.(데이터 복제)
--  create table 테이블명
-- as
-- select문 

-- departments 테이블에 있는 결과를 그대로 복제하여 dept1 테이블을 만드시오.
create table dept1  
as
select * from departments;

-- departments 테이블에 있는 데이터는 복제 하지 않고, 구조만 복제하여 dept2 테이블을 만드시오.
create table dept2
as
select * from departments where 1 = 0; -- 이렇게 항상 거짓인 조건을 넣어주면 구조만 가져오게 됨.

-- departments 테이블에 있는 결과중 dept_no만 복제하여 dept3 테이블을 만드시오.
create table dept3 
as
select dept_no from departments;

 

2. 데이터 저장, 수정, 삭제 (insert, update, delete)
-- 1) 데이터 저장하기 (로우 단위로 저장됨)
-- insert into 테이블명 (컬럼명) values (값)  <- 모든 컬럼을 저장하고 싶을때는 컬럼명 생략
-- 컬럼에 저장될 값을 지정하지 않으면 null이 저장된다.

-- 테이블 test_table1에 값 100, '문자열1', 11.11 를 차례로 저장하시오.
insert into test_table1 (data1, data2, data3) values(100, '문자열1', 11.11);
select * from test_table1;

-- 컬럼순서가 순서대로 되지않아도 됨. 아래와 같이 values와 짝만 잘 이루면 된다.
insert into test_table1 (data2, data3, data1) values('문자열2', 22.22, 200); 
select * from test_table1;

-- 컬럼명 생략할때는 values의 순서 잘 지켜줘서 써주기.
insert into test_table1 values(300, '문자열3', 33.33);
select * from test_table1;

-- test_table1을 구조만 복제하여 test_table2를 만든후, 서브쿼리를 이용하여 test_table1을 test_table2에 통째로 넣으시오.
create table test_table2
as
select * from test_table1 where 1=0;

insert into test_table2
select data1, data2, data3 from test_table1; -- <- 이 서브쿼리를 test_table2에 통쨰로 insert한다. (이때 values는 안써줘도 되나보네,,)

-- 2) 데이터 수정하기
-- update 테이블명 set 컬럼명=값, 컬럼명=값 where 조건식

-- test_table1테이블의 data2컬럼을 모두 '새로운문자열'로 수정하고, data3컬럼을 모두 '66.66'으로 수정하시오.(모두 수정)
update test_table1 set data2='새로운문자열', data3=66.66;

-- test_table2테이블의 <data1컬럼의 데이터가 100인것만> data2컬럼을 '새로운문자열'로 수정하고, data3컬럼을 '66.66'으로 수정하시오.(일부 수정)
update test_table2 set data2='새로운문자열', data3=66.66
where data1=100;   <-- 이 where절만 추가된것

 

-- 3) 데이터 삭제하기
-- delete from 테이블명 where 조건식

-- test_table1의 모든 데이터를 삭제하시오.(모두 삭제)
delete from test_table1;  -- test_table1의 데이터 삭제

-- test_table2의 data1가 100인 데이터만 삭제하시오.(일부 삭제)
delete from test_table2 where data1=100;  -- test_table2에서 data1=100인 데이터만 삭제


3. 트랜젝션 관리 : 데이터베이스에서 데이터 처리의 한 단위 (rollback, savepoint, truncate 기능사용)
-- 대부분의 데이터베이스는 데이터를 저장, 수정, 삭제하는 작업을 바로 하드디스크에 저장된 데이터에 반영하지 않는다.(실수를 수정하기위한 안전장치)
-- 커밋을 하면 그때 물리적인 하드디스크에 반영한다.
-- 개발자가 입력하는 명령문들의 시작부터 ~ 커밋까지를 하나의 트랜젝션이라고 부른다.

-- 1) RollBack : 데이터의 저장,삭제,수정 등의 작업을 한 후, 원래대로 되돌리는 작업
-- 커밋을 한 이후에는 RollBack작업을 해도 되돌릴 수 없다.
-- Workbench 같은 프로그램에서는 자동으로 커밋작업이 발생하므로 Rollback을 해도 원래대로 되돌릴 수 없다. 
-- Rollback을 사용하기 위해 임의로 설정을 바꿔보자. Edit - preference - SQL execution - auto commit 체크박스 해제

-- test_table2를 삭제한 후, 복구하시오.
select * from test_table2;
delete from test_table2; -- 데이터 삭제
select * from test_table2; -- 데이터 삭제된 것 확인
rollback; -- 복구
select * from test_table2; -- 삭제되었던 데이터가 정상적으로 복구됨


-- 커밋을 한 후엔 롤백을 해도 복구되지 않는다.
select * from test_table2;
delete from test_table2; -- 데이터 삭제
commit; -- 커밋
select * from test_table2; -- 데이터 삭제된 것 확인
rollback; -- 복구
select * from test_table2; -- 복구 되지 않는것을 확인


-- 2) SavePoint : rollback시 지정된 위치로 복원
insert into test_table2 (data1, data2, data3) values (100, '문자열1', 11.11);
insert into test_table2 (data1, data2, data3) values (200, '문자열2', 22.22);
insert into test_table2 (data1, data2, data3) values (300, '문자열3', 33.33);

commit;
select * from test_table2;

update test_table2 set data2='새로운문자열', data3=44.44 where data1=100;
savepoint aa;  -- savepoint 지정
delete from test_table2 where data1=100;
select * from test_table2;
rollback to aa;  -- 위에서 지정한 savepoint로 돌아가서 복구함
select * from test_table2;


-- 3. truncate : 지정된 테이블의 모든 로우를 삭제한다. 
-- delete문은 데이터베이스에 바로 반영하지 않아 rollback이 가능하지만 truncate는 데이터베이스에 바로 반영하여 rollback이 불가능하다.

commit;

select * from test_table2;
-- delete 로 삭제하면 롤백 되는것을 확인
delete from test_table2; 
select * from test_table2;
rollback;
select * from test_table2;

-- truncate 로 삭제하면 롤백 되지 않는 것을 확인
truncate test_table2;
select * from test_table2;
rollback;
select * from test_table2;

//

4. 테이블 변경하기 (rename, alter, drop)
-- 1. 테이블명 변경 : rename table 기존테이블명 to 새로운테이블명
-- 2. 컬럼의 데이터타입 변경 : alter table 테이블명 modify 컬럼명 변경할타입
-- 3. 컬럼의 이름, 데이터타입 변경 : alter table 테이블명 change 기존컬럼명 새로운컬럼명 변경할타입 
-- (컬럼명만 바꾸더라도 타입도 필수로 써줘야함. 타입을 변경하지 않을거면 기존의 타입을 그대로 써주면 됨.)
-- 4. 컬럼 추가 : alter table 테이블명 add 추가할컬럼 추가할컬럼의데이터타입
-- 5. 컬럼 삭제 : alter table 테이블명 drop 삭제할컬럼
-- 6. 테이블 삭제 : drop table 테이블명

-- 테이블test_table1을 test_table3으로 변경하시오.
show tables; -- 현재 데이터베이스에 저장된 모든 테이블을 불러온다.(test_table1, test_table2)

rename table test_table1 to test_table3; -- test_table1을 test_table3으로 변경
show tables; -- test_table2, test_table3 으로 변경된 것 확인

-- test_table3의 data1컬럼의 데이터 타입을 int에서 int(100)으로 변경하시오.
desc test_table3;
alter table test_table3 modify data1 int(100);
desc test_table3;

-- test_table3의 컬럼 data1을 data10으로 변경하고, 데이터타입을 int(200)으로 변경하시오.
alter table test_table3 change data1 data10 int(200);
desc test_table3;

-- test_table3에 컬럼 data4를 추가하고, 데이터타입을 int(20)으로 설정하시오.
alter table test_table3 add data4 int(20);
desc test_table3;

-- test_table3에 컬럼 data4를 삭제하시오.
alter table test_table3 drop data4;
desc test_table3;

-- 테이블 test_table3을 삭제하시오.
show tables;
drop table test_table3;
show tables;

 

5. 제약조건 : 컬럼에 저장될 데이터의 조건을 설정(데이터의 무결성을 보장하므로 매우 중요)
-- 1. primary key : 중복불가, null불가. 기본키라고도 부름.
-- 2. foreign key : 특정 테이블의 primary key컬럼에 저장되어있는 값만 저장할 수 있도록 한다. 참조키, 외래키라고도 부름. null값 허용.
-- 3. not null : null불가. 쿼리문을 통해 반드시 값이 지정되어야 함.
-- 4. unique : 중복불가, null허용.
-- 5. check : 값의 범위나 종류를 지정하여 조건에 맞는 값만 저장할 수 있도록 함. check제약조건은 mysql에서 지원하지 않음.(작성은 가능하나 무시함)
-- 6. default : null이 들어올 경우 기본 설정 값을 지정. default를 설정할 경우 컬럼에 null을 저장할 수 없음.


-- test_table10테이블을 만들고, not null제약조건이 있는 data1의 컬럼을 만드시오.
create table test_table10(
data1 int not null
);

insert into test_table10 (data1) value (1);
insert into test_table10 (data1) value (2);
insert into test_table10 (data1) value (3);

select * from test_table10; -- 데이터 1,2,3이 문제없이 삽입됨을 확인.

insert into test_table10 (data1) value (1); -- 중복되어 1이 삽입됨.
select * from test_table10;

insert into test_table10 (data1) value (null); -- Column 'data1' cannot be null 에러 뜸. not null이란 제약조건을 넣어줬기 때문에.
select * from test_table10; 


-- test_table20테이블을 만들고, data1은 primary key조건을 가지고, data2는 not null제약조건을 설정하시오.
create table test_table20(
data1 int,
data2 int not null,
constraint pk1 primary key(data1)  -- 여기서 pk1은 마음대로 이름붙인것.
);

insert into test_table20 (data1, data2) values (10, 100);
insert into test_table20 (data1, data2) values (20, 200);
insert into test_table20 (data1, data2) values (30, 300);

select * from test_table20; 

insert into test_table20 (data1, data2) values (10, 100); -- 에러(중복된 값을 허용하지 않아서)
insert into test_table20 (data1, data2) values (null, 100); -- 에러 (null값을 허용하지 않아서)
insert into test_table20 (data2) values (100); -- 에러 (primary key는 null이 아닌 기본값을 모두 세팅해줘야 해서)


-- test_table30테이블을 만들고, data1은 primary key조건을 가지고, data2는 test_table20의 data1컬럼을 참조하도록 설정하시오.
create table test_table30(
data1 int,
data2 int,
constraint pk1 primary key(data1),
constraint pk2 foreign key(data2) references test_table20(data1) -- data2에는 test_table20의 data1의 값만 넣을수 있다는 뜻.(10,20,30만)
);

select * from test_table20; 
insert into test_table30 (data1, data2) values (1, 10);
insert into test_table30 (data1, data2) values (2, 20);
insert into test_table30 (data1, data2) values (3, 30);

select * from test_table30; 
insert into test_table30 (data1, data2) values (4, 40); -- test_table20의 data1에는 40이 없는데 넣어주니까 에러!
insert into test_table30 (data1, data2) values (5, null);  -- foreign key 여서 null 값 허용
insert into test_table30 (data1, data2) values (6);  -- 이렇게 두번째 값을 생략해도 null 이 들어간다. (난 왜 에러나지??)
select * from test_table30; 

-- test_table40테이블을 만들고, data1, data2는 unique조건을 가지고, data2 not null로 설정하시오.
create table test_table40(
data1 int,
data2 int not null,
constraint uk1 unique(data1),
constraint uk2 unique(data2)
);

insert into test_table40 (data1, data2) values (1, 10);
insert into test_table40 (data1, data2) values (2, 20);
select * from test_table40; 

insert into test_table40 (data1, data2) values (1, 30); -- 중복이라서 에러
insert into test_table40 (data1, data2) values (3, 10); -- 중복이라서 에러
insert into test_table40 (data1, data2) values (null, 40); -- data1은 not null이 아니니까 가능
insert into test_table40 (data1, data2) values (null, 50); -- 원래 unique는 중복불가 null값 허용이니까, data2처럼 일부러 not null 쓰지 않는 경우는 다 null값 허용한다.
select * from test_table40; 

-- test_table50테이블을 만들고, data1는 10초과, data2는 10,20,30만 입력되게하는 제약조건을 가지게하고, 모두 not null로 설정하시오.
create table test_table50(
data1 int not null,
data2 int not null,
constraint chk1 check(data1 > 10),
constraint chk2 check(data2 in(10,20,30))
);

insert into test_table50 (data1, data2) values (20, 30);
select * from test_table50; 

insert into test_table50 (data1, data2) values (1, 100); -- 위 제약조건에 위배되지만 mysql은 무시하기 때문에 그대로 저장한다.(왜 난 저장안되지?)
select * from test_table50; 


-- test_table60테이블을 만들고, data1는 디폴트 1, data2는 디폴트 10으로 입력되게하는 제약조건을 가지게 설정하시오.
create table test_table60(
data1 int not null default 1,
data2 int not null default 10
);

insert into test_table60 (data1, data2) values (100, 200); -- 기본데이터는 정상 입력됨
select * from test_table60; 

insert into test_table60 (data1, data2) values (null, null); -- 디폴트 제약조건은 이렇게 둘다 null값을 대놓고 넣어주면 에러난다.
select * from test_table60; 

insert into test_table60 (data1) values (1000); -- 디폴트 제약조건은 이렇게 컬럼하나를 생략할때만 null값을 넣어준다!!!
insert into test_table60 (data2) values (2000);
select * from test_table60; 


6. 시퀀스, limit

-- 1) 시퀀스 : 로우를 추가할때 자동으로 증가하는 값이 저장되는것. (auto_increment 키워드사용. 데이터베이스마다 사용법다름)
-- 데이터를 insert할때 auto_increment를 설정한 컬럼은 제외한다.(보통 primary key에 많이 사용)

-- test_table100테이블 만들고, data1은 자동으로 숫자가 증가되도록 설정하고, data2,3은 not null로 설정하시오.
create table test_table100(
data1 int auto_increment,   -- data1은 자동으로 1,2,3,4... 추가된다.
data2 int not null,
data3 int not null,
constraint pk1 primary key(data1)
);

insert into test_table100 (data2, data3) values (100, 200); -- auto_increment가 설정된 data1은 생략가능
insert into test_table100 (data2, data3) values (101, 201);
insert into test_table100 (data2, data3) values (102, 202);
insert into test_table100 (data2, data3) values (103, 203);
insert into test_table100 (data2, data3) values (104, 204);

select * from test_table100;

-- 2. limit : select해서 가져온 로우에서 원하는 범위의 로우만 가지고 온다. (게시판 등 페이징 기법시 사용. 데이터베이스마다 사용법다름)
-- select문 limit 시작인덱스, 개수

use employees;

select * from employees order by emp_no;
select * from employees order by emp_no limit 0, 10; -- 1번부터 10개 데이터를 가져옴 (1번~10번)
select * from employees order by emp_no limit 10, 10;  -- 11번부터 10개 데이터를 가져옴 (11번~20번)

7.  뷰 

-- 가상의 테이블(조인이나 서브쿼리를 사용해 만든 '복잡한 쿼리'를 간단히 '뷰'로 지정해 놓으면 편하게 사용가능)
-- 뷰는 select문을 통해 얻어진 '결과'를 가지고 있는게 아니라 select문 자체를 가지고 있어서 뷰를 select하면 이전에 사용한 쿼리문이 실행되어 결과를 가져오는것.
-- 뷰 생성 : create view 뷰이름 as select쿼리문
-- 뷰 삭제 : drop view 뷰이름

create table test_table1000(
data1 int,
data2 int not null,
constraint pk1 primary key (data1)
);

create table test_table2000(
data1 int not null,
data2 int not null,
constraint fk1 foreign key (data1) references test_table1000(data1)
);
desc test_table2000;
alter table test_table2000 change data2 data3 int; -- 내가 잘못써서 컬럼명 수정(data2 -> data3)

insert into test_table1000(data1, data2) values(1, 10);
insert into test_table1000(data1, data2) values(2, 20);
insert into test_table1000(data1, data2) values(3, 30);

select * from test_table1000;

insert into test_table2000(data1, data3) values(1, 100);
insert into test_table2000(data1, data3) values(2, 200);
insert into test_table2000(data1, data3) values(3, 300);

select * from test_table2000;


-- test_table1000과 test_table2000을 조인하는 test_view1을 만들어보자.
-- 먼저, test_table1000과 test_table2000을 조인해보자.
select a1.data1, a1.data2, a2.data3
from test_table1000 a1, test_table2000 a2
where a1.data1 = a2.data1;  -- 공통분모는 data1이니까 그걸 조건문에 넣기.

-- 이제 view를 만들어보자. (위에 조인한 쿼리를 밑에 3줄 그대로 복사해서 넣기)
create view test_view1
as
select a1.data1, a1.data2, a2.data3
from test_table1000 a1, test_table2000 a2
where a1.data1 = a2.data1; 

-- 만든 뷰 조회하기
select * from test_view1;

-- 뷰는 결과값만 가져오는게 아니라 쿼리 자체를 가지고 있어서 이렇게 데이터를 맘대로 추가해도 반영이 된다.
insert into test_table1000(data1, data2) values(4, 40);
insert into test_table2000(data1, data3) values(4, 400);

-- 추가한 데이터 반영한 뷰 조회하기
select * from test_view1;

-- 뷰 삭제하기
drop view test_view1;

select * from test_view1;



[출처] 인프런-  윤재성의 처음 시작하는 MySQL DataBase