MySQL 이해와 용어 정리

MySQL이란?

전세계적으로 가장 널리 사용되고 있는 오픈 소스 데이터베이스이며, 표준 데이터베이스 질의 언어 SQL을 사용하는 개방 소스의 관계형 데이터베이스 관리 시스템(RDBMS)이다. 매우 빠르고, 유연하며 사용하기 쉬원 특징이 있다.

데이터 베이스란 여러 응용 시스템이 공유할 수 있도록 통합, 저장된 운영 데이터의 집합을 의미한다. 데이터베이스는 질의에 대한 실시간 처리 및 응답을 처리할 수 있도록 실시간 접근과 삽입, 삭제 갱신을 통해서 현재의 데이터를 동적으로 유지할 수 있는 계속적인 변화를 제공하며, 여러 사용자가 동시에 공용할 수 있는 동시 공유와 위치나 주소가 아닌 내용 즉, 값에 따라 참조할 수 있는 내용에 의한 참조의 특징을 지나고 있다.

MySQL 데이터베이스의 특징

MySQL이 가지고 있는 특징을 살펴보면, 무료이면서도 처리되는 속도 또한 상당히 빠르고 용이하며, 대용량의 데이터를 처리 할 수 있는 장점과 보안에도 뛰어난 특성을 지니고 있습니다.

DB 다루기 구문

-- 데이터베이스 보기
show databases;
 
-- 만약 sqlDB가 존재하면 우선 지운다.
drop database if exists sqlDB; 
 
-- 데이터베이스 생성
create database sqlDB; 
 
-- 데이터베이스 선택
use sqlDB;

테이블 생성

CREATE TABLE db명.테이블명 (
  컬럼명1 INT PRIMARY KEY AUTO_INCREMENT, -- 기본키 숫자 자동 증가 설정
  컬럼명2 CHAR(15) NOT NULL,
  컬럼명3 INT,
 
  PRIMARY KEY(컬럼명1),
  FOREIGN KEY(컬럼명2) REFERENCES 테이블명(컬럼명) -- 자기자신 외래키 참조
  FOREIGN KEY(컬럼명3) REFERENCES 다른테이블명(컬럼명a) -- 다른테이블 외래키 참조
);

-- 테이블이름을 띄어쓰기 하고싶으면 ``를 반드시 넣어야 한다.
create table `my testTBL`(id InT);

테이블 제약 조건

• NOT NULL : 해당 필드는 NULL 값을 저장할 수 없게 됩니다.
• UNIQUE : 해당 필드는 서로 다른 값을 가져야만 합니다.
• PRIMARY KEY : 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됩니다.
• FOREIGN KEY : 하나의 테이블을 다른 테이블에 의존하게 만듭니다.
• DEFAULT : 해당 필드의 기본값을 설정합니다.
• AUTO_INCREMENT : 해당 필드의 값을 1부터 시작하여 새로운 레코드가 추가될 때마다 1씩 증가된 값을 저장합니다.

create table userTbl
(	
    userID char(8) primary key,
    name varchar(10) not null unique, -- 만일 기본기지정을 안했으면 not null 유니크가 기본키가 된다.
    birthYear int not null,
    addr char(2) not null,
    mobile char(3),
    mdate date
);

create table buyTbl
(	
    num int auto_increment not null primary key, -- 자동으로 숫자순서대로 증가
    userID char(8) not null,
    price int not null default 80, -- 값이 입력되지 않을 때 기본값
    
    constraint 외래키이름 foreign key (userID) references userTbl(userID) on update cascade 
    -- 외래키 지정. constraint쓰면 외래키이름 지정, 안쓰면 필드값 자체가 이름이 됨.
    -- 만일 부모키가 변경할 경우 같이 외래키값도 변경
);

테이블 조회

show tables
 
show table STATUS -- 더 자세히

• Name : 테이블 이름
• Type : 테이블 타입
• Row_format : 열 저장 형태 (Fixed, Dynamic, Compressed )
• Rows: 열의 수
• Avg_row_length : 열의 평균 길이
• Data_length : 데이터 파일의 길이
• Max_data_length : 데이터파일의 최대 길이
• Index_length : 인덱스 파일의 길이
• Data_free : 사용되지않는 bytes 에 할당된 수
• Auto_increment : 다음 자동증가 변수
• Create_time : 테이블이 생성된 시간
• Update_time : 데이터파일의 마지막 UPDATE 시간
• Check_time : 테이블의 마지막 체크시간
• Create_options : 테이블 생성시의 기타옵션
• Comment : 테이블 생성시의 명령어

테이블 구성도

show columns from 테이블명
describe 테이블명
desc 테이블명

테이블 데이터 조회

SELECT 컬럼명, 집계함수 as 별명   
FROM 테이블명                     
WHERE 테이블 조건              
GROUP BY 컬럼명                  
HAVING 그룹 조건                  
ORDER BY 컬럼명                   

• FROM : SQL은 구문이 들어오면 테이블을 가장 먼저 확인합니다.
• WHERE : 테이블 명을 확인했으니
• GROUP BY : 조건에 맞는 데이터가 추출되었으니, 공통적인 데이터들끼리 묶어 그룹을 만들어줍니다.
• HAVING : 공통적인 데이터들이 묶여진 그룹 중, 주어진 조건에 맞는 그룹들을 추출해줍니다.
• SELECT : 최종적으로 추출된 데이터들을 조회합니다.
• ORDER BY : 추출된 데이터들을 정렬해줍니다.

select 필드1, 필드2, sum(필드명) as 별명
from 테이블명
where 필드명1=값 (조건)
group by 필드명1
having 별명 > 100 (그룹 조건)
order by 필드명2 desc (정렬)

• select : 필드(열) 들과 집계함수 결과값을 선택하고 as 로 별명지정 가능.
• from : 어느 테이블에서
• where : 어느 조건을 만족하는 것만 (select 문에 집계함수가 있든없든 무조건 where조건부터 맞추고 집계)
• group : 그룹핑
• having : 집계용 조건
• order : 정렬

테이블 구조 수정

필드 추가

ALTER TABLE 테이블이름 ADD 필드이름 필드타입
 
-- 컬럼을 추가하는데 어느 필드 after 이후에 추가하는지 위치를 지정해 줄 수 있다.
ALTER TABLE 테이블이름 ADD 필드이름 필드타입 AFTER 기존필드명

필드 제거 ( drop )

ALTER TABLE 테이블이름 DROP 필드이름

필드 수정 ( change )

ALTER TABLE 테이블이름 change 필드명 새필드명 새필드타입

필드 타입만 수정 (modify)

ALTER TABLE 테이블이름 modify 필드명 새필드타입

키 추가(add ...key)

ALTER TABLE 테이블이름 add constraint 기본키명 primary key (필드값)
 
ALTER table 테이블이름 add FOREIGN KEY(columnName) REFERENCES 참조테이블(참조컬럼);

키 제거(drop ...key)

ALTER TABLE 테이블이름 drop foreign key 외래키명

테이블 이름 변경

ALTER TABLE table_name1 RENAME table_name2;

테이블 데이터 수정 (업데이트)

UPDATE 테이블이름
SET 필드이름1=데이터값1, 필드이름2=데이터값2, ...
WHERE 필드이름=데이터값; -- 조건식을 안쓰면 테이블 전체 레코드가 싹 바뀜 !!!

테이블 삽입

-- 필드 몇개만 정하여 넣을 때
INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...)
VALUES (데이터값1, 데이터값2, 데이터값3, ...)
 
-- 필드 전체를 넣을때 (필드명 생략 가능)
INSERT INTO 테이블이름
VALUES (데이터값1, 데이터값2, 데이터값3, ...)

테이블 삭제

drop table 테이블명
 
DROP DATABASE IF EXISTS Hotel; -- 에러 방지를 위해 if문 추가
DROP TABLE IF EXISTS Reservation;

휴지통

DELETE FROM 테이블이름
WHERE 필드이름=데이터값

• 트랜잭션 로그를 기록해서 속도가 느림.
• 지운거 복구가 가능합니다.
• 테이블 자체 용량은 안줄어듭니다. 휴지통 개념
• 만약 WHERE 절을 생략하면, 해당 테이블에 저장된 모든 데이터가 삭제됩니다.

영구 삭제

truncate table 테이블명

레코드 정렬

• 오름차순 : ASC
• 내림차순 : DESC

-- 기본 오름차순
SELECT [column_names] FROM [table_name] ORDER BY [column_name];
 
-- 각각 컬럼을 따로따로씩 정렬 설정이 가능하다
SELECT [column_names] FROM [table_name] ORDER BY [column_name] DESC, [column_name2] ASC ;

테이블 별칭

SELECT col1 from Eployee as E
--> 테이블 별칭 E.col1
 
SELECT col1 AS '성명', col2 AS '국어점수' FROM grade;
--> 필드명을 보기좋게 한글로 별명

중복 비허용

• 해당 컬럼의 중복된 값을 합쳐서 보여줌.
• 어떤 데이터들이 있는지 확인 할 때

select distinct 컬럼명 from 테이블명

널값 검색

-- = null 이 아닌 is null
select name
from usertbl
where mobile1 is null;

참고 자료

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EA%B8%B0%EB%B3%B8-SQL%EB%AC%B8-%EC%A0%95%EB%A6%AC-%ED%85%8C%EC%9D%B4%EB%B8%94-%EC%A1%B0%ED%9A%8C-%EC%83%9D%EC%84%B1-%EC%88%98%EC%A0%95-%EC%82%AD%EC%A0%9C