들어가기 전 !
📌 DDL (조작어)
테이블의 구조나 관계를 생성하는 데 사용 ! create(생성), drop(삭제), alter(변경) 등이 있다.
1. create(생성)
# fullstack/database/p240611/create.sql
[문법] create database DB명;
한 번 데이터베이스를 생성해보자
create database test;
스키마를 보면 데이터가 생성 됐는지 안됐는지 GUI 를 통해 알 수 있다 !
근데 mysql 워크 벤치가 없다면 어떻게 확인할래 . . .
cmd 창에서 확인할 줄도 알아야 되는데 ..!
그럴 때 DB 목록을 보면 된다 ㅎ
# DB 목록
show databases;
📌 작성된 쿼리문 확인
생성한 DB 의 쿼리문을 확인하고 싶을 때
show create database test;
📌 이모지(emoji) 지원
버전 업이 되면서 기본값이 되었기 때문에 지금 내 환경에서는 작성하면 error !
작성된 쿼리문을 보면 character set 이 반영 됐는지 확인할 수가 있다
character set utf8mb4; 버전 업되면서 기본값 됨
test DB 선택!
스키마에 볼드체로 보여짐 !
use test;
테이블 목록도 확인 !
방금 생성해서 암것도 없음
show tables;
2. drop(삭제)
# fullstack/database/p240611/create.sql
# fullstack/database/p240613/drop.sql
[문법] drop(변경)
drop table 테이블명;
drop database DB명;
# t2 테이블 삭제
drop table t2;
# 테이블 목록
show tables;
show tables like 't2';
# temp2 테이블 삭제
# Error Code: 3730. Cannot drop table 'temp2' referenced by a foreign key constraint 'temp6_ibfk_1' on table 'temp6'.
drop table temp2; # 외래키를 의해 참조 되고 있기 때문에 삭제 x
생성할 땐 기본키가 있는 테이블을 먼저 만든다 했는데
삭제할 땐 외래키를 참조 하고 있는 자식 테이블을 먼저 삭제 !
어려운 말로 데이터를 무결성 유지 해주자 (안전하게)
다른 테이블과 연결되어 있는게 외래키인데
관계를 먼저 끊어줘야함 !
외래키를 삭제하던지 자식을 삭제하던지 !
drop 을 한다고 무조건 삭제 할 수 없다 !
3. 테이블 생성 방법 3가지
# fullstack/database/p240611/create.sql
📌 [IF NOT EXISTS]
테이블이 존재하지 않을 때만 생성하겠다 ! (옵션)
📌 [문법 1]
create table [IF NOT EXISTS] 테이블명 (
컬럼명1 자료형;
컬럼명2 자료형(크기);
);
test DB 생성 후 DB 선택
create database test;
use test;
table 조회 : test DB 에 테이블 없음
show tables;
t1 테이블 생성
컬럼이 오고 타입이 와야 함 ! 순서가 바뀌면 error !
create table t1(
co1 int,
co2 varchar(10),
co3 float
);
table 을 조회하면 t1 테이블이 생성된 것을 확인할 수 있음 !
t1 테이블 구조도 확인해보자
# table 조회 : t1 테이블 생성 !
show tables;
# t1 테이블 구조 확인
desc t1;
# t1 테이블 데이터 조회
select * from t1;
📌 [문법 2]
- 테이블 구조 + 데이터 복사
- 서브쿼리 이용한 테이블 생성
- 테이블 복사 -> 키는 복사 x
- as # 생략 가능
create table [IF NOT EXISTS] 테이블명
as
select 컬럼명, 컬럼명
from 테이블명
where 조건;
현재 선택된 DB 확인해보면 test 로 되어 있다
select database();
test DB 에서 departments 테이블 데이터를 조회하기 !
앞에 테이블명을 명시해주면 된다 테이블명은 생략되어있음을 알아야함 !
select * from employees.departments;
employees DB 의 departments 테이블 복사
create table copy_departments
as
select * from employees.departments;
table 목록 확인
show tables;
copy_departments 데이터 조회
select * from copy_departments;
📌 [문법 3]
- 테이블 구조만 복사
- key 복사
create table [IF NOT EXISTS] 복사본_테이블명 like 원본_테이블명
4. 타입 Type
# fullstack/database/p240612/create.sql
📌 Type : float 확인
버전업되면서 float 자리수를 지정 안해줘도 됨 !
# 예전 버전
create table t2(
co1 float(5, 1),
co2 float(5, 2),
co3 float(5, 3),
co4 float(5, 4),
co5 float(5, 5),
co6 float
);
테이블 구조 확인
desc t2
데이터 추가(=로우 추가)
DML insert 는 아직 배우지는 않았지만 그냥 간단하게 !
insert into 테이블명(컬럼명1, 컬럼명2) values(값1, 값2);
insert into 테이블명 values(값1, 값2);
칼럼이 6개니까 값 6개 넣어줌
insert into t2 values(1, 1, 1, 1, 1, 1); Error Code: 1264. Out of range value for column 'co5' at row 1
insert into t2 values(1, 1, 1, 1, 0.1, 1);
t2 테이블 데이터 조회
select * from t2;
co1 를 보면1.0 이면 볼 필요가 있나 ! 그래서 버전업 되면서 바뀐 것 ㅎ
그럼 다시 다른 데이터를 추가하고 데이터 조회
insert into t2 values(1.1, 1.1, 1.1, 1.1, 0.1, 1.1);
select * from t2;
소수점 2번째 자리 추가
insert into t2 values(1.12, 1.12, 1.12, 1.12, 0.12, 1.12);
select * from t2;
소수점 3번째 자리 추가
insert into t2 values(1.123, 1.123, 1.123, 1.123, 0.123, 1.123);
select * from t2;
소수점 4번째 자리 추가
insert into t2 values(1.1234, 1.1234, 1.1234, 1.1234, 0.1234, 1.1234);
select * from t2;
자 여기서 알 수 있는 점 !
칼럼을 지정할 때 소수점까지 지정하면 데이터를 넣을 때 내가 원하는 값이 제한 될 수 있음 !
근데 소수점 5번째 자리는 ?
insert into t2 values(1.12345, 1.12345, 1.12345, 1.12345, 0.12345, 1.12345);
select * from t2;
소수점 4번째 자리까지는 표현되는데 5번째자리가 오면서 반올림하면서 버린 것
반올림 된당
insert into t2 values(1.16, 1.16, 1.16, 1.16, 0.16, 1.16); # 반올림 !
select * from t2;
5. 제약 조건
[제약 조건]
primary key : 중복 허용 x, null 허용 x, not null + unique
foreign key : 중복 허용 o, null 허용 o, 참조값만 허용
not nul : null 허용 x
auto_increment : 자동으로 1씩 증가
unique : 중복 허용 x(유일한 값만 허용), null 허용 o
check : 값의 범위나 종류 지정, 도메인 설정, MySQL 8.0.16 부터 지원
default : null 값일 경우 설정되는 값
📌 컬럼 레벨 방식과 테이블 레벨 방식
https://sowon-dev.github.io/2020/06/07/200608dbi/#google_vignette
📌 PK, NOT NULL, AUTO_INCREMENT
create table temp1 (
one int primary key auto_increment, # 컬럼 레벨 방식
two int not null
);
테이블 목록 확인과 테이블 구조 확인하기
show tables; # 테이블 목록 확인
desc temp1; # 테이블 구조 확인
기본키는 따로 설정 안해도 null 허용 x
데이터 추가, temp 테이블 데이터 조회
# 데이터 추가
insert into temp1(one, two) values(1, 1);
# temp1 테이블 데이터 조회
select * from temp1;
컬럼명 지정해줘도 생략해도 값만 잘 주면 된다
# 데이터 추가
insert into temp1(one, two) values(1, 1); # 컬럼명 지정
insert into temp1 values(2, 20); # 컬럼명 생략
# temp1 테이블 데이터 조회
select * from temp1;
근데
값이 들어오지 않을 때, 마지막 행 기준으로 auto_increment 1씩 증가
# 데이터 추가
insert into temp1(one, two) values(1, 1); # 컬럼명 지정
insert into temp1 values(2, 20); # 컬럼명 생략
# 값이 들어오지 않을 때, 마지막 행 기준으로 auto_increment 1씩 증가
insert into temp1(two) values(30); # 특정 컬럼에만 값 지정
# temp1 테이블 데이터 조회
select * from temp1;
그럼 꼭 순서에 맞게 넣어야되나 ? 다음은 무조건 4?
아님 !
# 데이터 추가
...
insert into temp1 values(100, 100);
# temp1 테이블 데이터 조회
select * from temp1;
근데 앞서 말했듯 auto_increment 는 마지막 행 기준으로 1씩 증가되는거니까
다음 행은 101 로 들어온다
# 데이터 추가
...
insert into temp1 values(100, 100);
insert into temp1(two) values(200);
# temp1 테이블 데이터 조회
select * from temp1;
그럼 다른 거 추가해보자
# 데이터 추가
...
insert into temp1 values(100, 100);
insert into temp1(two) values(200);
insert into temp1(one) values(300); # 추가 # Error Code: 1364. Field 'two' doesn't have a default value
# temp1 테이블 데이터 조회
select * from temp1;
오잉 그럼 에러가 뜬다 왜지 !
two 컬럼에는 not null 을 지정해줬기 때문에 에러 발생 !
Error Code: 1364. Field 'two' doesn't have a default value
그럼 다시 다른 값 추가
# 데이터 추가
...
insert into temp1 values(3, 300); Error Code: 1062. Duplicate entry '3' for key 'temp1.PRIMARY'
# temp1 테이블 데이터 조회
select * from temp1;
근데 또 에러 왜2ㅣ !
지금 3 이라는 key 가 있기 때문에 중복 허용하지 않음
Error Code: 1062. Duplicate entry '3' for key 'temp1.PRIMARY'
에러 다시 정리
# not null : two 컬럼 null 허용 x
# Error Code: 1364. Field 'two' doesn't have a default value
insert into temp1(one) values(300);
# PK 제약 조건에 위해 ! - 중복
# Error Code: 1062. Duplicate entry '3' for key 'temp1.PRIMARY'
insert into temp1 values(3, 300);
📌 PK, UNIQUE
create table temp2 (
one int,
two int unique,
primary key(one) # 테이블 레벨 방식, 기본키 하고자 하는 컬럼명 넣기
);
테이블 목록 확인
show 를 한다는 건 select 문이 실행된다는 거 알아두기
show tables; # 테이블 목록 확인
그래서 내가 보고 싶은 테이블을 볼 수 있음 ! 조건을 걸 수 있다는 뜻
show tables like 'te%'; # 특정 테이블 목록 확인
temp2 구조 확인
desc temp2; # temp2 구조 확인
데이터 추가하고 테이블 데이터 조회하는건 이전 코드들과 동일 !
# 데이터 추가
insert into temp2 value(1, 100);
# temp2 테이블 데이터 조회
select * from temp2;
그럼 다른 것도 추가해보자
# 데이터 추가
insert into temp2 value(1, 100);
# primary key
insert into temp2(two) value(200);
# temp2 테이블 데이터 조회
select * from temp2;
근데 에러가 뜬다 ..!
PK 제약 조건 위배 ! - null 허용 x
Error Code: 1364. Field 'one' doesn't have a default value
그래서 아무 데이터나 들어올 수 없음
제약 조건을 쓰는 이유가 있다 !
# 데이터 추가
insert into temp2 value(1, 100);
# unique
insert into temp2(one) values(2);
# temp2 테이블 데이터 조회
select * from temp2;
테이블은 순서가 없음 !
one 에 꼭 첫 번째 ? 아님 !
1이 꼭 첫 번째 ? 아님 !
row 사이에 순서가 있다? 아님 !
📌 primary key Error !
# primary key ----------------------------------------------
# PK 제약 조건 위배 ! - null 허용 x
# Error Code: 1364. Field 'one' doesn't have a default value
insert into temp2(two) value(200);
# PK 제약 조건 위배 ! - 중복 허용 x
# Error Code: 1062. Duplicate entry '1' for key 'temp2.PRIMARY'
insert into temp2 value(1, 200);
📌 unique Error !
# unique ---------------------------------------------------
insert into temp2(one) values(2); # unique : null 허용
insert into temp2(one) values(4); # unique : null 허용
# Error Code: 1062. Duplicate entry '100' for key 'temp2.two'
# ex) 아이디
insert into temp2 values(3, 100); # unique : 중복 허용 x
📌 PRIMARY KEY : MULTIPLE COLUMN PRIMARY KEY
create table temp3 (
one int,
two char(10),
three float,
four int,
primary key(one, two, three) ★
);
# 테이블 구조
desc temp3;
데이터 추가 및 조회
# 데이터 추가
insert into temp3 values(1, '하나', 1.1, 4);
insert into temp3 values(2, '둘', 2.2, 8);
# temp3 테이블 데이터 조회
select * from temp3;
# 데이터 추가
insert into temp3 values(1, 'first', 1, 1);
다중 컬럼으로 다중 키로 들어왔다면 row 전체가 같은 값이어야 중복
# row 전체가 같지 않아서 중복 x
insert into temp3 values(1, 'first', 1, 1);
insert into temp3 values(1, '하나', 1, 1);
insert into temp3 values(1, '하나', 1.1, 100); # error ! PK 제약조건 위배 : 중복 허용 x
insert into temp3(two, three, four) values('하나', 1, 100); # error ! PK 제약조건 위배 : null 허용 x
# temp3 테이블 데이터 조회
select * from temp3;
📌 FOREIGN KEY
references : 다른 테이블의 기본키 가져옴, 컬럼명은 다르되 타입은 같아야 함
create table temp4 (
id int,
name varchar(50),
foreign key(id) references temp1(one)
);
# 테이블 구조
desc temp4;
# 데이터 추가
insert into temp4 values(1, '홍길동');
insert into temp4 values(10, '박보검'); # 현재 참조 하고 있는 temp1 에 10이 없어서 error !
insert into temp4 values(1, 'hong'); # FK : 중복 허용
insert into temp4(name) values('이미자'); # FK : null 허용
# temp4 테이블 데이터 조회
select * from temp4;
select * from temp1;
📌 CHECK, DEFAULT
- M or F, 한 글자이기 때문에 1 byte 여야되는데 2 byte 로 설정함
- check(조건)
- constraint 제약명 check(조건)
create table temp5 (
gender char(2),
age int default 1,
constraint ck_gender check(gender in ('M', 'F')),
check(age >= 1)
);
# 데이터 추가
insert into temp5 values('M', 25);
insert into temp5 values('F', 26);
insert into temp5 values('male', 33); # Error Code: 1406. Data too long for column 'gender' at row 1
insert into temp5 values('W', 55); # Error Code: 3819. Check constraint 'ck_gender' is violated.
insert into temp5 values('f', 0); # Error Code: 3819. Check constraint 'temp5_chk_1' is violated.
insert into temp5 values('m', null); # default null 들어올 수 있음
insert into temp5(gender) values('m'); # age 컬럼 : null일 경우, 기본값으로 설정
insert into temp5(age) values(33); # gender 컬럼 : null일 경우, 허용 !
# temp5 테이블 데이터 조회
select * from temp5;
📌 AUTO_INCREMENT
아무 컬럼에 설정할 수 없고, 기본키가 설정된 컬럼에 해야함 ! 일반 컬럼에 x
# Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key
create table temp6 (
co1 int,
co2 int auto_increment,
primary key(co1)
);
6. 특정 테이블의 index 정보 조회
# 컬럼 레벨 방식으로 작성해도 테이블 레벨 방식으로 저장이 됨
CREATE TABLE `temp5` (
`gender` char(2) DEFAULT NULL,
`age` int DEFAULT '1',
CONSTRAINT `ck_gender` CHECK ((`gender` in (_utf8mb4'M',_utf8mb4'F'))),
CONSTRAINT `temp5_chk_1` CHECK ((`age` >= 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
show create table temp1;
CREATE TABLE `temp1` (
`one` int NOT NULL AUTO_INCREMENT,
`two` int NOT NULL,
PRIMARY KEY (`one`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
테이블 생성해보기 !
temp6 테이블 생성
<컬럼의 정보>
id : temp2의 one 컬럼을 참조하는 외래키, null 허용 안함
name : varchar, 길이 30, null 허용 안함
age : int, 1 이상, null 허용 안함
모든 컬럼에 null 허용 x
id : temp2 테이블의 one 컬럼 참조
constraint : 제약명 지정하기
create table temp7 (
id int not null,
name varchar(30) not null,
age int not null,
constraint pk_id primary key(id),
constraint fk_id foreign key(id) references temp2(one),
constraint ck_age check(age >= 1)
);
# temp7 테이블 구조 확인
desc temp7;
# 데이터 추가
insert into temp7 values(1, '홍길동', 25);
insert into temp7 values(3, '박보검', 35); # Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`test`.`temp7`, CONSTRAINT `fk_id` FOREIGN KEY (`id`) REFERENCES `temp2` (`one`))
insert into temp7 values(1, '이미자', 55); # Error Code: 1062. Duplicate entry '1' for key 'temp7.PRIMARY'
# temp7 테이블 구조 확인
select * from temp7;
7. [system catalog] temp7 테이블의 제약 조건 확인
show databases;
use information_schema;
show tables;
select * from table_constraints;
test DB의 정보를 볼 수 있음
select *
from table_constraints
where table_schema like 'test';
test DB의 temp7 테이블 정보
select *
from table_constraints
where table_schema like 'test'
and table_name like 'temp7';
8. alter(변경)
# fullstack/database/p240613/alter.sql
[문법] alter(변경)
alter table 테이블명 modify 컬럼명 자료형; # 데이터 DB 구조를 잘못 짤 경우라 잘 안씀
alter table 테이블명 change 기존컬럼명 새컬럼명 자료형
alter table 테이블명 add 컬럼명 자료형;
alter table 테이블명 drop 컬럼명;
alter table 테이블명 rename 새테이블명;
이 외에도 많음
# DB 선택
use test;
# [변경 전] t2 테이블 구조 확인 및 데이터 조회
desc t2;
select * from t2;
# 1
# t2 테이블의 co1 컬럼 자료형을 char(10)로 변경
# [문법] alter table 테이블명 modify 컬럼명 자료형;
alter table t2 modify co1 char(10);
# [변경 후] t2 테이블 구조 확인 및 데이터 조회
desc t2;
select * from t2;
# 2
# t2 테이블의 co2 컬럼명을 num 으로 변경
# [문법] alter table 테이블명 change 기존컬럼명 새컬럼명 자료형
# 자료형 안넣음 Error Code: 1064. You have an error in your SQL syntax;
alter table t2 change co2 num float(5, 2); # 경고문 ! 버전업 되면서 float 자리수 표시 x
# [변경 후] t2 테이블 구조 확인 및 데이터 조회
desc t2;
select * from t2;
컬럼명을 바꾸면서 type 를 변경했는데 데이터도 변경됨
# 3
# t2 테이블의 co3 컬럼명 num3 변경, 타입 : float -> int
# [문법] alter table 테이블명 change 기존컬럼명 새컬럼명 자료형
alter table t2 change co3 num3 int;
# [변경 후] t2 테이블 구조 확인 및 데이터 조회
desc t2;
select * from t2;
# 4
# t2 테이블에 자료형 int 인 co7 컬럼 추가
# [문법] alter table 테이블명 add 컬럼명 자료형;
# 데이터 값은 null 로 들어옴
alter table t2 add co7 int;
# [변경 후] t2 테이블 구조 확인 및 데이터 조회
desc t2;
select * from t2;
# 5
# t2 테이블에 자료형 int 인 co8 컬럼 추가(기본값 0)
# [문법] alter table 테이블명 add 컬럼명 자료형;
# DB 설계에서 꼼꼼하게 했기 때문에 이렇게 추가할 일은 x
alter table t2 add co8 int default 0 not null;
# [변경 후] t2 테이블 구조 확인 및 데이터 조회
desc t2;
select * from t2;
# 6
# t2 테이블에 co4 컬럼 삭제
# [문법] alter table 테이블명 drop 컬럼명;
alter table t2 drop co4;
# [변경 후] t2 테이블 구조 확인 및 데이터 조회
desc t2;
select * from t2;
# 7
# [테이블명 변경] t2 테이블명을 copy_t2 로 변경
# [문법] alter table 테이블명 rename 새테이블명;
alter table t2 rename copy_t2;
# [변경 후] t2 테이블 구조 확인 및 데이터 조회
desc t2; Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from t2' at line 2
desc copy_t2;
select * from t2;
# 8
# [테이블명 변경] copy_t2 를 다시 t2 로 변경
# [문법] rename table 기존_테이블명 to 새_테이블명;
rename table copy_t2 to t2;
# [변경 후] t2 테이블 구조 확인 및 데이터 조회
desc t2; Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from t2' at line 2
desc copy_t2;
select * from t2;
'Others > 데이터베이스' 카테고리의 다른 글
[SQL] TCL (트랜잭션 제어어) 문법 정리 및 예제 (0) | 2024.06.16 |
---|---|
[SQL] DCL (제어어) 문법 정리 및 예제 (0) | 2024.06.16 |
[SQL] DML (조작어) 문법 정리 및 예제 (0) | 2024.06.16 |
006. 관계형 데이터베이스 SQL 및 모델링 - 2편 MySQL (0) | 2024.06.16 |
006. 관계형 데이터베이스 SQL 및 모델링 - 1편 DB, DBMS, RDBMS (1) | 2024.06.16 |