데이터베이스와 테이블 생성, 삭제 / 제약조건

Execution plan

실행 계획이란, 말 그대로 SQL 문으로 요청한 데이터를 어떻게 불러올 것인지에 관한 계획, 즉 경로를 의미.

지름길을 사용해 데이터를 빠르게 찾아낼 것인지, 지름길이 있어도 멀리 돌아가서 찾을 것인지 미리 확인할 수 있다.

어떻게 짜는지에따라 속도가 달라진다.(데이터가 10만건정도는 돼야 튜닝했을때 속도라 달라짐)

실행 계획을 확인하는 키워드로는 EXPLAIN, DESCRIBE, DESC 가 있는데, 해당 결과는 모두 같다.

참고 : https://gngsn.tistory.com/161

query optimizer

Execution plan : 을 세움
query tunning : Execution plan을 만드는 것

데이터를 만들어서 넣기(DML)
DML
insert
update
delete

데이터를 저장할 박스를 생성(DDL)
DDL
데이터 베이스

• create
• alter

테이블

• 문자형
  char, varchar
  char - 고정(검색의 속도 좋음)
  varchar - 가변(공간의 신축성 좋음)
  tinytext / text / mediumtext / longtext <- 많은 양을 관리할 수있음

• 숫자형
  정수 int
  실수 decimal / double

• 날짜형
  datetime / date

• 이진 데이터
  생각 x
  
  
  

새로운 데이터베이스 만들기 - create database 데이터베이스이름; / 지우기

같은것을 또 만들면 에러가 생긴다

있으면 만들지말고 없으면 만들기 - if not exists 데이터베이스명
create database if not exists 데이터베이스명

만들어진 데이터베이스 지우기 - drop
drop database 데이터베이스명

데이터베이스 존재하면 지우기
drop database if exists 데이터베이스명

테이블 만들기

테이블에 데이터를 만들 컬럼을 규정하는 것 (컬럼이름, 데이터 종류, 데이터 길이, 옵션으로 이루어짐) <- 이것들로 테이블을 만들어 준다.

이름은 마음대로 만들지만 데이터타입은 종류를 알아야한다
https://mariadb.com/kb/en/data-types/ <- 사이트 참고

테이블만들기 선언

미리 규정을 해줘야한다.

테이블명 tbl1
col1 / 컬럼만들고
varchar(2)/ varchar로 2자 만들거야

desc dept; 테이블을 똑같이 한번 만들어보기

테이블 존재하지않을때만 만들게 하기
if not exists

테이블 지우기 drop / 존재하면 지워라도 가능 drop ... if exists ...

테이블 복제해서 만들기 as
emp를 select해서 emp2를 만들어 / emp2 테이블을 만들고 emp 테이블을 다 emp2로 복제함

복제 영역을 지정해줄수있다
테이블에 where deptno = 10인 일부분
emp10테이블을 만들고 emp테이블의 deptno(사원번호)가 10인 요소를 emp10으로 복사

일부 컬럼 복제
emp11테이블을 만들고 emp의 사원번호가 10인 empno, ename, job을 emp11로 복사

가공된 컬럼 넣기

가공된 컬럼의 이름값(sal*12+ifnull(comm,0)이 따라가서 alias로 걸어줘서 이름 annsal로 지정

문제

테이블명 tbl1
col1 varchar(2)

emp_dept 생성
empno, ename, deptno, dname, loc

데이터 없이 껍데기만 복사해오기
where 절로 부정조건을 만들어준다. 꼭 1!= 1 일 필요는 없다 부정조건만 만들어주면 됨

alter 수정 / 삭제 / 추가 등..

테이블에서는 컬럼을 수정하는것이 가장 먼저
alter를 통해 테이블 규정을 수정 할 수 있음
컬럼 / 컬럼 수정 / 컬럼 삭제

절대경로로 다른경로 접근

데이터베이스에서 다른 데이터베이스 데이터에 접근 가능
접근할 데이터베이스의 '데이터베이스명.테이블명' 으로 접근

컬럼 더하기 (추가) 원하는 위치에는 안되고 마지막 위치에 된다.

modify : 한번 만들어진 컬럼의 내용 수정 (사이즈)
사이즈 조절할때 주의사항
데이터의 사이즈를 늘리는것은 되지만 줄이는것은 자유롭지않다.

emp_alter2를 만들어서 job 추가 후 emp 출력
두번째는 varchar(20)으로 늘림

줄이려고 한다면?
데이터에 대한 길이가 있기때문에 함부로 할 수 없음.

컬럼의 이름을 바꾸기 rename

지우기

drop은 조심 다 없애버림

테이블명 변경

구문이 두 개다

테이블 상태정보를 sql 구문으로

이상한것들 제거 mariadb의 기능 \G

데이터베이스 테이블의 정보 (어떻게 구성돼있고 내용은 어쩌고 ... 의 얘기)
이런것들 통해서 탐색기 / 응용프로그램등 등을 만들 수 있다. (고수들이 함)

하이디
mariadb 툴도 만들 수 있다 (고수들이 함)

데이터 입력 - insert

insert into 테이블명 valuse(값1, 값2, ...) / into는 예약어 values 값은 순서대로
insert into 테이블명(컬럼명1, 컬럼명2 ...) valuse(값1, 값2, ...) / 컬럼명과 값은 같은 위치끼리 매칭 (컬럼명1과 값1 매칭)

1. 값의 순서는 컬럼의 순서와 동일해야한다.
2. 자료형의 크기 확인
3. 문자열 데이터는 반드시 ''(단일따옴표)로 표시

데이터 입력 / 실수로 두번 넣음

입력 데이터 길이가 다르면 에러

데이터입력 또 다른 방법
insert into 테이블명(컬럼명1, 컬럼명2 ...) valuse(값1, 값2, ...) / 컬럼명과 값은 같은 위치끼리 매칭 (컬럼명1과 값1 매칭)

데이터를 입력의 필수요소
null(안넣어도 됨) / not null( 반드시 넣어야함 )
no - 넣어야함, yes 안넣어도 됨

null을 허용하는 결과

필수 입력항목을 적용시킬거야

데이터를 입력하는데 뒤에 null 을 넣을 수 있음
문자열로 하면 안됨 'null'
문자열로하면 문자열 null이 들어감

두번째도 null을 줄 수있다. 근데 첫번째는 줄수없음

null이 들어간 것의 작용 ( 데이터 입력의 제한 )
deptno만 두고 null로 하겠다.
둘중에 하나 선택해서 사용하면 됨.

디폴트

null을 넣으면?

default 넣으면

데이터를 한번에 많이 넣기

다른테이블을 기반으로 해서 넣기
빈 껍데기 테이블을 만들고 -> sample의 dept테이블에 부서가 30인 요소들 넣음

데이터 수정 - update

update 테이블명 set 컬럼명 = 값 - 컬럼 내용 전체 변경
update 테이블명 set 컬럼명 = 값 where 조건 - 조건에 해당부분만 변경

loc를 다 부산으로 변경

deptno 가 30인 내용만 대전으로 변경

emp 테이블을 복사하시고..
직책이 사원(clerk)이고 부서번호가 20번인 사원에 대한 커미션을 1000.0으로 변경

데이터 삭제

delete from 테이블명 - 테이블 전체의 내용이 삭제됨
delete from 테이블명 where 조건 - 특정 행 삭제

30번 부서 지우기

전체데이터 지우기

주의사항
행을 지우는것은 delete
컬럼데이터(열)을 지우는것은 update
헷갈리지말것 !

update로 job을 null로 다 지움.

문제

1. 테이블 생성
   

2. 전화번호(PHONE)의 컬럼을 varchar(10)타입으로 추가시오
   

3. 이메일(email) 컬럼을 varchar(20)타입으로 변경하시오
   

4. 주소(address) 컬럼을 삭제하시오
   

5. iinsert 구문 생성

DML

select
insert
update
delete

DDL

create database
drop database
create table
alter table
drop table

테이블에 입력할 데이터 제한 - 제약조건 (constraint)

1. 필수 입력 - not null
2. 중복 방지 - unique
3. 필수 + 중복 - primary key (테이블당 1 - 고유번호에)
4. 참조 - foreign key

값에 대한 검사 - check (mariadb에는 없음)

기술하는 방식 2개 (좋아하는 방식으로 사용하면 됨)

• 테이블단위 제약조건
  테이블 선언
  선언이 끝나고 제약조건을 준다

• 컬럼단위 제약조건
  컬럼 선언 뒤에 연결해서 제약조건을 준다

제약조건을 확인하는 방법

(간단) null과 key

내부적으로 적용시켜놓은 테이블
데이터가 아닌 constraint 내용을 규정한 테이블

informatino_schema

1. not null 제약조건을 가진 dept_n1

create table dept_n1 (
deptno int(2) not null,
dname varchar(14),
loc varchar(13)
);

2. unique 중복데이터 배제

create table dept_u1 (
deptno int(2) unique,
dname varchar(14),
loc varchar(13)
);

10이 이미 있어서 에러가 난다

null은 중복을 허용한다.

테이블 설정이 다 끝난거라서 컬럼명을 넣어줘야한다. (constraint unique(deptno))
create table dept_u2 (
deptno int(2),
dname varchar(14),
loc varchar(13),
constraint unique(deptno)
);

컬럼명 바꾸기
create table dept_u3 (
deptno int(2),
dname varchar(14),
loc varchar(13),
-- constraint unique(deptno)
constraint dept_u3_deptno_uk unique(deptno)
);

3. primary key

create table dept_p1 (
deptno int(2) primary key,
dname varchar(14),
loc varchar(13)
);
프라이머리가 되면 null이 no(데이터를 넣어줘야함) key가 primary(중복 허용 안함)

확인

테이블 단위 제약조건 주기
create table dept_p2 (
deptno int(2),
dname varchar(14),
loc varchar(13),
constraint primary key(deptno)
);

자동증가
create table dept_a2 (
deptno int(2) unsigned primary key auto_increment,
dname varchar(14),
loc varchar(13)
);
0 넣으면 1로 된다

또 0 넣으면 1증가된 값이 된다

수동으로 10 넣음

또 10 넣으면 중복이라 에러가 난다

다시 0으로 넣으면 마지막에 넣었던 10 에서 1증가된값이 출력된다.