DDL

데이타베이스(스키마) 생성

create database {dbName};

테이블 생성

create table {tableName}({...});

# ex
create table author(id int primary key, name varchar(100), email varchar(30), password varchar(20) );

테이블 수정

# 컬럼 데이터 타입 변경
alter table {tableName} modify column {columnName} {newType};
# ex
ALTER TABLE customers MODIFY COLUMN phone_number VARCHAR(30);

# 컬럼에 제약 조건 추가
ALTER TABLE {tableName} ADD CONSTRAINT {제약조건명} UNIQUE(columnName);
# ex
ALTER TABLE customers ADD CONSTRAINT unique_email UNIQUE(email);

테이블 삭제

drop table {tableName};

# 테이블이 존재하는 경우 삭제
drop table if exists {tableName};

테이블 이름 수정

alter table {preTableName} rename {postTableName};

컬럼 추가

alter table {tableName} add column {columnName} {자료형};

# ex
alter table author add column age int;

컬럼 삭제

alter table {tableName} drop column {columnName};

컬럼 이름 변경

alter table {tableName} change column {preColumnName} {postColumnName} {자료형};

# ex
alter table posts change column content contents varchar(3000);

컬럼 수정

alter table {tableName} modify column {columnName} {자료형};

# ex
alter table posts modify column title varchar(255) not null;

DML

데이터 추가

insert into {tableName}({columnName1}, {columnName2}) values ({columnValue1}, {columnValue2});

# ex
insert into author(id, name, email) values(1, 'gdhong', 'gdhong@gmail.com');

데이터 수정

update {tableName} set {columnName1}={columnValue2}, {columnName2}={columnValue2} where {조건절};

# ex
update author set name = '홍길동' where id = 1;

# set 뒤에 '=' 은 값 대입
# where 뒤에 '=' 은 비교 연산

데이터 삭제

delete from {tableName} where {조건절};

# join이 포함된 delete 문
delete {삭제할 tableName1} from {tableName1} join {tableName2} on ...;

#ex
delete posts from author join posts on author.id = posts.author_id where posts.title = 'post title';

# delete는 데이터를 지울 때 로그를 남김. 추 후 복구 가능.
# truncate는 데이터를 지울 때 로그 안 남김.
# delete와 truncate는 데이터만 삭제, drop은 테이블 구조까지 전체 삭제

조회

# 정렬 (기본: asc(오름차순) 명시: desc(내림차순))
# {columnName1} 이 같은 데이터는 {columnName2} 로 정렬
select * from {tableName1} order by {columnName1}, {columnName2} desc;

# 별칭
# 컬럼 별칭
select {columnName} as {aliasName} from {tableName};
# 테이블 별칭
select * from {tableName} as {aliasName};

# 조건문
# 비교 연산
select * from {tableName} where {columnName} > {number};
select * from {tableName} where {columnName} >= {number};
select * from posts where id > 1;
# null
select * from {tableName} where {columnName} is null;
select * from {tableName} where {columnName} is not null;

제약조건

제약조건 조회

select * from information_schema.key_column_usage where table_name = {tableName};

제약조건 삭제

# 기본키
alter table {tableName} drop primary key;

# 왜래키
alter table {tableName} drop foreign key {제약조건 이름};

# 유니크
alter table {tableName} drop index {제약조건 이름};

# 체크
alter table {tableName} drop check {제약조건 이름};

# 기본값
alter table {tableName} alter column {columnName} drop default;

제약조건 생성

alter table {tableName} add constraint {제약조건 이름} foreign key({columnName}) references {타 테이블 이름}({타 테이블 컬럼명});

# ex
alter table posts add constraint post_author_fk foreign key(author_id) references author(id);

# on delete cascade 를 붙이면, 타 테이블의 데이터가 삭제되면 같이 삭제됨
# 아래 예시 코드로 보면 author에서 id가 1인 로우가 삭제되면 posts에 author_id가 1인 로우도 삭제됨.
alter table posts add constraint post_author_fk foreign key(author_id) references author(id) on delete cascade;

흐름제어문

CASE

select case name when '홍길동' then '홍' when '김철수' then '김' else '기타' end as 'name' from author;

IF

• if(조건, 조건이 true일 때 반환 값, 조건이 false일 때 반환 값)

select id, email, if(name is null, '익명사용자', name) as 'name' from author;

• case와 if문은 where 조건에는 사용하지 않는 게 좋다. or 로 충분히 대체 가능하고, or을 사용하는게 성능이 더 좋다.

서브쿼리

where절 서브쿼리

in, not in 과 함께 주로 사용

# ex
select * from author where id in (select author_id from posts);

select절 서브쿼리

테이블에 있는 컬럼 외에 추가적인 컬럼을 만들 때 사용

# ex
# select절 서브쿼리에서는 select 된 컬럼들을 사용할 수 있음.
# 예시 sql에서 보면 a를 From 으로 가져왔기 때문에 서브쿼리에서는 a의 컬럼을 사용할 수 있음
select a.id, a.email, (select count(*) from posts p where p.author_id = a.id) as count from author a;

from절 서브쿼리

서브쿼리로 조회한 값들을 하나의 테이블로 사용할 수 있음

# ex
select a.name from (select * from author) as a;

기타

인덱스 조회

show index from {tableName}