SQL (DDL,DML,DCL)

DDL

- 데이터베이스와 테이블을 정의하는 언어
- CREATE: 데이터베이스와 테이블을 생성하는 명령어
- ALTER: 테이블을 수정하는 명령어
- DROP: 데이터베이스와 테이블을 삭제하는 명령어

 

---

- 데이터베이스 및 테이블 생성 (CREATE)

 

Workbench를 설치 후 데이터베이스가 잘 설치되는지 확인하기 위해 다음과 같이 실행해봤다.

 

create database test;


-- 데이터베이스 삭제
drop database test;


-- 데이터베이스 삭제시 조건 주기
drop database if exists test;

 

잘 실행이 된다.

 

마지막에 조건을 주지 않으면 모든 데이터베이스가 삭제될 수 있다.

 

그래서 삭제하는 명령어를 쓸 때는 조건을 주는 것이 좋다.

 

이 후 테스트가 아닌 실제로 들어가보겠다.

 

이번 과정은 명령어를 주의 깊게 보면서 익히는 것이 좋다.

 

-- 데이터베이스 생성
create database shop;

-- 데이터베이스 리스트 출력하기
show databases;


-- 데이터베이스를 선택하려면 아래의 USE를 사용해야한다.
use shop;


-- 현재 어떤 데이터베이스를 선택했는지 확인한다.
select database();

 

여기까지 우리는 데이터베이스를 생성하고 선택하였다.

 

이 후 테이블를 생성해볼 예정이다.

 

-- 테이블 생성하기
create table tb_user(
user_id int
);

-- 테이블 리스트 보기
show tables;

-- 테이블 삭제하기
drop table tb_user;
drop table if exists tb_user;

create table tb_user(
user_id int,
    user_name varchar(10), # 가변길이, 입력한 크기만큼 공간이 잡힌다.
    phone char(13) # 고정길이, 가변길이보다 속도가 빠르다.
);

-- 테이블 구조 확인하기
desc tb_user;

 

위와 같이 처음 create table을 사용하여 테이블을 만들었다.

 

테이블을 만들 때는 데이터베이스와 달리 (" ")를 사용하고, 그 뒤에 제약조건을 하나 붙여줘야한다.

 

나머지는 손으로 코딩해보면서 넘어가면 이해하기 쉽다.

 

---

조건 걸기

 

- 제약조건: 어떠한 제약조건이 만족할 시 데이터 삽입!

- 기본키(Primary key): 중복값 X, Null X

- auto_increment: 새 데이터 저장 시 고유번호가 자동생성되서 들어간다.

 

위의 조건들을 다음과 같이 걸어보겠다.

 

create table tb_product(
product_id int primary key auto_increment,	# 기본키
    product_name varchar(20) unique not null, # 중복 불가
product_price int
);
desc tb_product;

select * from tb_product;	# 테이블 조회

 

product_id 하나를 예시로 보면 조건이 3가지이다.

 

처음은 int형이여야하고, 기본키이면서 고유번호가 자동생성되는 것이다.

 

---

테이블 수정하기 (ALTER)

테이블을 변경하는 것은 alter로 각각의 역할에 따라 add, change, modify 등이 있다.

 

-- 컬럼 추가하기
alter table tb_user add user_addr varchar(255);
desc tb_user;

-- 컬럼명 수정하기
alter table tb_user change phone user_phone varchar(50);
desc tb_user;

-- 컬럼 타입 수정하기
alter table tb_user modify column user_phone varchar(13);
desc tb_user;

-- 컬럼에 대하여 제약조건 및 속성 추가하기
alter table tb_user modify column user_id int primary key auto_increment;
desc tb_user;

-- 컬럼 추가
alter table tb_user add user_age int;

-- 컬럼 삭제하기
alter table tb_user drop user_age;
desc tb_user;

-- not null로 변경
alter table tb_user modify column user_name varchar(10) not null;
alter table tb_user modify column user_phone varchar(13) not null;
alter table tb_user modify column user_addr varchar(255) not null;
desc tb_user;

 

위 코드를 복붙하지말고 워크벤치에 직접해보면 이해하기 쉽다.

foreign key(외래키, 외부키, 참조키...)

- pk를 보유하는 테이블이 부모, 참조하는 테이블을 자식

 

create table tb_order(
order_id int primary key auto_increment,
    user_id int, # 부모의 기본키의 테이터 타입으로.. 
    product_id int, # 부모의 기본키의 테이터 타입으로.. 
    order_dt datetime default current_timestamp, # 값이 null이면 현재 시간이 들어간다.
    foreign key(user_id) references tb_user(user_id),
    foreign key(product_id) references tb_product(product_id)
);
desc tb_order;

여기서 외래키는 user_id와 product_id이다.

 

둘은 각자 다른 테이블에서 기본키이자 order테이블에서는 외래키이다.

 

 

 

쉽게 말해 이러한 구조로 우리는 테이블들을 설계한 것이다!

 

---

 DML

- 테이블의 데이터를 조작하는 명령어

- insert: 테이블에 데이터를 삽입하는 명령어

- update: 테이블에 데이터를 수정하는 명령어
- delete: 테이블에 데이터를 삭제하는 명령어
- select: 테이블에 데이터를 조회하는 명령어

 

---

데이터 삽입하기 (INSERT)

기존에 만들었던 shop데이터베이스를 사용한다.

 

use shop;
select database();

-- 하나씩 삽입하기
desc tb_user;
insert into tb_user(user_name, user_phone, user_addr)
values("손용석","010-1234-1324","서울시 금천구");
select * from tb_user;

-- user_name의 varchar 길이가 10으로 설정되어 있어서 에러 발생!
insert into tb_user(user_name,user_phone,user_addr)
values("111111111111","010-1544-1324","서울시 강남구");    # 조건인 11개 이상이라 에러 발생!
desc tb_user;

-- not null 조건을 갖고 있는 컬럼에 데이터를 안 넣을 경우 에러 발생
insert into tb_user(user_name)
values("손흥민");

-- 여러 데이터 삽입하기
insert into tb_user(user_name,user_phone,user_addr)
values("기석","010-9876-5432","서울시 관악구"),
      ("철수","010-9517-5395","서울시 서초구"),
      ("만수","010-5617-5214","서울시 종로구"),
      ("훈수","010-7684-5455","서울시 중구");
select * from tb_user;

insert into tb_product(product_name,product_price)
values("에어컨","1200000"),
      ("스마트tv","2000000"),
      ("컴퓨터","1000000"),
      ("모니터","200000");
select * from tb_product;

-- unique 제약이 있는 컬럼에 중복 데이터를 넣을 경우 에러발생
insert into tb_product(product_name,product_price)
values("에어컨","0000")

desc tb_order;
select * from tb_user;


-- 부모에 없는 값을  넣을 경우 외래키에 넣을 경우 안들어간다.
insert into tb_order(user_id,product_id)
values(1,3);
select * from tb_order; # *: 모든 것을 조회한다. tb_order로 부터

 

이번에 데이터를 넣어보라고 강사님이 시간을 주셨다.

 

하지만 나는 여러 데이터를 넣는 법을 몰라 하나씩 데이터를 넣는 멍청한 짓을 하였다...

 

강사님이 여러 데이터를 넣는 법을 알려주셨을 때 너무나도 간단해서 어이가 없었다...

 

그리고 제약이 실제로도 적용되는지와 외래키가 잘 적용되었는지를 알아보았다.

 

---

데이터 수정하기 (UPDATE)

select * from tb_product;
update tb_product
set product_name = "삼성에어컨"
where product_id = 1; # 조건을 안 걸면 전부 다 바뀜
select * from tb_product;

 

데이터를 수정하는 방법은 update 다음에 테이블을 넣고 set을 활용하여 데이터를 수정해주면 된다!

 

그러나 여기서 주의해야할 점이 있는데 where 다음 조건을 안 걸어주면 전부 다 바뀌게 된다.

 

하지만 Workbench가 계속 업데이트가 되면서 조건을 안 걸어주면 알아서 에러가 발생하게 된다.

 

---

데이터 삭제하기 (DELETE)

 

select * from tb_user;
delete from tb_user
where user_id = 5;
select * from tb_user;

 

이것도 동일하게 where을 이용하여 조건을 걸어주어야한다. 그 이유는 update와 동일하다.

 

select * from tb_order;
delete from tb_order
where user_id = 1;
# 자식 테이블 삭제

delete from tb_user
where user_id = 1;
select * from tb_user;

 

다음과 같이 tb_order의 데이터를 삭제하려하면 삭제가 안된다.

 

이유는 2가지로 자식 테이블에서 참조하는 값이 있는 부모키의 값은 삭제가 안되고,

 

자식 테이블에 참조하는 값을 먼저 삭제하고 해당 부모키를 삭제해야한다.

 

select은 별도의 설명이 필요없어도 될거 같아서 안넣었다.

---

DCL

- grant: 사용자 계정에 대한 권한을 주는 명령어
- revoke: 사용자 계정에 대한 권한을 회수하는 명령어
- commit: insert,update,delete에 대한 데이터베이스에 실제 반영
- rollback: insert,update,delete에 대한 데이터베이스에 대한 복구

 

DCL 같은 경우는 DBA가 목표가 아니라면 그렇게까지 쓸 일이 없다고 하셨다.

 

그래서 내용도 가볍게 이해하며 넘어갔다!

 

use mysql;
select database();
-- mysql에 계정 생성하기

# 예시 => create user 'ID'@'IP' identified by '비밀번호';
create user 'son'@'%' identified by '153246son@';
select * from user;

-- 권한 부여하기
grant select,insert,delete,update on *.* to 'son'@'%'; # 모든 DB와 모든 table에 접근한다.
select * from user;

-- 권한 해제하기
revoke select,insert,delete,update on *.* from 'son'@'%';
select * from user;

-- 계정 삭제
drop user 'son'@'%';
select * from user;

 

다음과 같이 계정을 만들어 권한을 부여할 수 있다. 반대로 권한 해제와 계정 삭제도 가능하다!

 

select @@AUTOCOMMIT; # 오토 커밋모드라 삭제 후 자동으로 보정됨!
set AUTOCOMMIT = 0;
select @@AUTOCOMMIT;

use shop;
select * from tb_user;
delete from tb_user
where user_id = 2; # 데이터 전체가 삭제되니 워크 벤치에서 경고함!
select * from tb_user;
rollback;
select * from tb_user;

delete from tb_user
where user_id = 2;
commit;
rollback;
select * from tb_user; # 커밋을 하면 롤백이 안된다.

 

우리가 롤백과 커밋을 사용하기 위해 워크벤치에 자동으로 보정되어있는 오토커밋모드를 해제하였다.

 

이 후 직접 워크벤치에서 해보면서 롤백과 커밋에 대해 알게 되었다.

---

강사님이 데이터 사이언티스는 데이터베이스를 조회만 잘하기만 하면 된다고 말씀하였다.

 

그래서 강의도 조회를 중점으로 나갔다.

 

내일은 조회를 엄청한다고 말씀해주셨다.

 

내가 조회한 데이터에 어떠한 정보를 얻을 수 있을지 너무 기대가 된다.

 

내일도 열심히 하자!