실행
MySQL 서버 실행 후 MySQL Command Line Client 실행하고 비밀번호를 입력한다.
데이터베이스
한 데이터베이스에는 여러 테이블이 존재할 수 있다.
데이터베이스 생성
create database 데이터베이스이름 default character set utf8;한글을 출력할 수 있는 UTF8 형식으로 새로운 데이터베이스를 만든다.
데이터베이스 선택
use 데이터베이스이름;사용할 데이터베이스를 선택한다.
데이터베이스 목록
show databases;현재 MySQL 서버에 존재하는 모든 데이터베이스 목록을 출력한다.
select database();현재 선택된 데이터베이스 이름을 출력한다.
테이블
테이블(table)은 여러 레코드(record)의 묶음이고, 한 레코드는 여러 개의 필드(field)로 이뤄져 있다. 테이블의 행을 레코드라고 하고, 열을 컬럼(column) 또는 attribute라고 부른다. 그리고 테이블의 행과 열이 만나는 칸을 필드라고 한다.
레코드는 디스크 블록에 저장되고, 한 디스크 블록엔 여러 개의 레코드가 저장된다. 찾고자 하는 레코드가 동일한 디스크 블록에 모여 있을수록 디스크 블록 입출력 횟수가 낮아져 SQL문 처리 속도가 빨라진다.
테이블 생성
create table 테이블이름(
컬럼이름 자료형 제약조건,
... ,
primary key (컬럼이름, ...),
foreign key (컬럼이름, ...) references 테이블이름 (컬럼이름),
index 인덱스이름 (컬럼이름, ...)
);테이블 레코드 확인
select * from 테이블이름;해당 테이블의 모든 행(레코드)과 열을 출력한다.
테이블 삭제
drop table 테이블이름;테이블 목록
show tables;현재 데이터베이스에 생성된 모든 테이블 목록을 확인할 수 있다.
레코드
레코드 삽입
insert into 테이블이름 values (필드값, ...);기본적으로 테이블에 새로운 레코드를 삽입하는 SQL문이다.
insert into 테이블이름 (컬럼이름, ...) values (필드값, ...);컬럼을 지정해 특정 컬럼에만 값을 넣고 나머지 컬럼은 default 값 또는 null로 초기화한 후 레코드를 삽입할 수 있다.
레코드 수정
update 테이블이름
set 컬럼이름 = 변경할값, ...
where 조건;where 절의 조건에 해당하는 레코드를 찾고, 그 레코드의 컬럼을 변경할 값으로 변경한다.
레코드 삭제
delete from 테이블이름 where 조건;where 절의 조건에 해당하는 레코드를 찾아서 삭제한다. where 절이 없으면 테이블에 존재하는 모든 레코드를 삭제한다.
인덱스
인덱스는 일종의 전화번호부다. 검색 속도 등을 높이기 위해서 사용된다. InnoDB(MySQL)에서 인덱스는 클러스터 인덱스(Clustered index)와 비클러스터 인덱스(Nonclustered index)가 존재한다.
InnoDB에서 클러스터 인덱스는 기본키 인덱스의 일종이다. 기존 인덱스가 B+tree 기반으로 테이블과 따로 저장되는 것과 다르게 클러스터 인덱스는 레코드의 순서를 물리적으로 정렬해서 저장한 테이블이라고 할 수 있다.
만약 테이블 생성 시 기본키를 정의한다면 기본키를 기준으로 클러스터 인덱스를 생성한다. 기본키를 정의하지 않으면 MySQL은 내부적으로 NOT NULL이면서 UNIQUE한 첫번째 인덱스의 첫번째 컬럼(column)을 기본키로 사용한다. 클러스터 인덱스도 없으면 내부적으로 사용하는 테이블의 rowid를 기준으로 클러스터 인덱스를 생성한다. 결과적으로 한 테이블 당 한 클러스터 테이블을 가진다. 클러스터 인덱스를 사용하면 레코드가 인덱스와 동일한 페이지에 존재하기 때문에 디스크 블록 입출력 수를 줄일 수 있다.
모든 InnoDB 테이블은 레코드가 저장되어 있는 곳에 클러스터 인덱스 (clustered index)라고 불리는 인덱스를 가지고 있다. 기본키와 관련된 쿼리(query)나 정렬을 빠르게 수행하기 위해 InnoDB는 기본키 값을 기준으로 테이블을 물리적으로 정렬한 후 디스크에 저장한다. 그래서 쿼리에 자주 사용되는 컬럼을 기본키로 설정하는 것이 중요하다. 그리고 기본키 수정 작업은 테이블 전체를 다시 정렬해야 하기 때문에 변하지 않는 컬럼을 기본키로 설정하는 것이 좋다.
인덱스 생성
create index 인덱스이름 on 테이블이름 (컬럼이름, ...);
alter table 테이블이름 add index 인덱스이름 (컬럼이름, ...);인덱스 목록
show index from 테이블이름;인덱스 삭제
drop index 인덱스이름 on 테이블이름;
alter table 테이블이름 drop index 인덱스이름;외부 파일 입출력
외부 파일 🠖 테이블
load data
infile 파일경로
into table 테이블명
fields terminated by '\t'
lines terminated by '\n'
ignore 1 lines;
load data
infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data.txt'
into table test
fields terminated by '\t'
lines terminated by '\n'
ignore 1 lines;load data ... SQL문으로 외부 데이터 파일을 불러올 수 있다. 외부 텍스트 파일인 data.txt에 있는 레코드를 test 테이블로 불러오는 SQL문이다. 그리고 데이터 파일의 레코드는 enter로 구분하고, 필드는 tab으로 구분한다고 명시했다.
주의할 점이 운영체제에 따라 레코드 구분자를 잘못 설정하면 나중에 데이터 쿼리에서 오류가 발생할 수 있다. 윈도우 기준 외부 파일을 메모장으로 편집하고 각 레코드를 엔터로 구분했다면 \n 대신 \r\n을 사용해야 외부 데이터를 테이블에 제대로 불러올 수 있다.
데이터 파일의 절대 경로는 \를 사용하는 대신에, \\ 또는 /로 하위 폴더를 구분해야 한다.
테이블 🠖 외부 파일
select * from test
into outfile 파일경로
fields terminated by '\t'
lines terminated by '\n';
select * from test
into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/out3.txt'
fields terminated by '\t'
lines terminated by '\n';select ... into outfile 구문으로 테이블 데이터를 파일에 저장할 수 있다. 위 SQL문은 test 테이블에 있는 모든 레코드와 컬럼을 저 경로에 있는 파일에 저장하는 문장이다. 필드 구분은 \t으로, 레코드 구분은 \n로 구분한다.
--secure-file-prev 오류
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
파일 경로를 아무 곳으로 설정하면 위와 같은 보안 오류가 발생한다.
select @@GLOBAL.secure_file_priv;@@GLOBAL.secure_file_priv |
|---|
C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
위 SQL문으로 허용된 폴더 경로를 확인한다. 거기에 데이터 파일을 위치시키면 오류 없이 불러올 수 있다. 기본값은 위와 같다.
