Sam Baek, The Dev's Corner

📃 SQL 데이터베이스 다루기

19 Jul 2023

SQL 기본문

CRUD와 관련해 노드를 공부하는 요즘,
SQL을 작성해야할 때가 잦다.
그래서 오늘은 SQL 문법을 정리하고,
해당 문법을 활용하는 것에 대해 정리하고자 한다.

CRUD는 차례로 Create, Read , Update, Delete를 의미한다.
언어를 작성하면 꼭 ;으로 마무리 해준다.

언어 (SQL)
Keword	내용
show databases	데이터베이스 목록을 보여라
create database databaseName	데이터베이스를 만든다
use databaseName	데이터베이스네임의 데이터베이스를 사용한다
drop databaseName	데이터베이스네임의 데이터베이스를 삭제한다
show tables	데이터베이스 안에 있는 테이블을 모두 보여라
create table tableName(fieldName Type length(NOT NULL) PRIMARY KEY AUTO_INCREMENT…) ENGINE InnoDB DEFAULT CHARSET=utf8mb4;	세부사항, 속성 지정 테이블 생성
desc tableName	테이블의 구조를 보여라
select * from tableName	테이블 안의 모든 목록을 보여라
select columnName from tableName	테이블 안의 columnName을 보여라
select * from tableName order by columnName DESC	테이블 안의 모든 목록 중에서 컬럼네임을 오름차순으로 보여라
select * from tableName order by columnName ASC	테이블 안의 모든 목록 중에서 컬럼네임을 내림차순으로 보여라
alter table `tableName` add column `fieldName` type length…	테이블 안에 필드를 추가하라
alter table `tableName` change column `fieldName` `fieldName`(수정할필드내용) type length…	테이블 안에 필드를 변경하라
update tableNmae set fieldName	테이블 안에 필드를 추가하라
delete from tableName where(조건)	조건 안에 포함된 것들을 테이블에서 삭제하라
insert into tableName(column1, column2,…) values(value1, value2, …), ( ), ( ), ( )	테이블 컬럼에 값 추가하라

asc: 작은것부터 큰 것으로
desc : 큰 것부터 작은 것으로

명령어 정리

위를 보고 이해하고자해도 난잡할 때가 있다.
세부를 추가해보자

명령어	내용
order by columName desc(asc)	desc는 오름차순, asc는 내림차순이다.
select distinct	중복 없이 나열할 때 사용한다.
where	필터링할 때 사용한다.
from	어느 테이블에서 갖다 쓸 것인지
like	와일드카드, 정규표현식 참조, 보통 ‘%[]%’ 방식 사용,
between a and b	a와 b사이의 범위
where columnName in (value1, value2,…)	여러 조건 값 지정
where locate(value, columnName) > 0	columnName에 value가 있는지 조건
where instr(columnName, value) > 0	columnName에 value가 있는지 조건
limit number	limit은 출력할 행의 수를 제한하는 명령어
count(columnName or *)	수를 센다.
AS	AS는 별칭을 의미한다.
max	max는 주로 하나의 값만 반환 where에서 사용할 때는 비교연산자를 사용한다.
min	min은 주로 하나의 값만 반환 where에서 사용할 때는 비교연산자를 사용한다.
sum	sum은 주로 하나의 값만 반환 where에서 사용할 때는 비교연산자를 사용한다.
avg	avg는 주로 하나의 값만 반환 where에서 사용할 때는 비교연산자를 사용한다.
group by	group by는 그룹을 지정한다.
having	having은 그룹에 조건을 지정한다. count(name)>1
offset	offset은 출력할 행의 시작점을 지정한다.
and	and는 여러개의 조건을 지정할 때 사용한다.
or	or는 여러개의 조건을 지정할 때 사용한다.
not	not은 조건을 반대로 지정할 때 사용한다.
hour	hour은 시간을 추출할 때 사용한다.
minute	minute은 분을 추출할 때 사용한다.
second	second는 초를 추출할 때 사용한다.
date	date는 날짜를 추출할 때 사용한다.
year	year은 년도를 추출할 때 사용한다.
month	month는 월을 추출할 때 사용한다.
day	day는 일을 추출할 때 사용한다.
week	week는 주를 추출할 때 사용한다.
dayofweek	dayofweek는 요일을 추출할 때 사용한다.
dayofyear	dayofyear는 년 중 몇 번째 날인지 추출할 때 사용한다.
last_day	last_day는 해당 월의 마지막 날짜를 추출할 때 사용한다.
quarter	quarter는 해당 년도의 몇 분기인지 추출할 때 사용한다.
to_days	to_days는 날짜를 추출할 때 사용한다.
from_days	from_days는 날짜를 추출할 때 사용한다.
date_format	date_format은 날짜를 지정된 형식으로 출력할 때 사용한다.
time_format	time_format은 시간을 지정된 형식으로 출력할 때 사용한다.
timestampdiff	timestampdiff는 두 날짜의 차이를 구할 때 사용한다.
timestampadd	timestampadd는 날짜에 일정 기간을 더할 때 사용한다.
timediff	timediff는 두 시간의 차이를 구할 때 사용한다.
date_add	date_add는 날짜에 일정 기간을 더할 때 사용한다.
date_sub	date_sub는 날짜에 일정 기간을 뺄 때 사용한다.
addtime	addtime은 시간에 일정 기간을 더할 때 사용한다.
subtime	subtime은 시간에 일정 기간을 뺄 때 사용한다.
with recursive	with recursive 재귀적으로 테이블을 만들 때 사용한다.
left join	left join은 왼쪽 테이블을 기준으로 오른쪽 테이블을 합칠 때 사용한다.
right join	right join은 오른쪽 테이블을 기준으로 왼쪽 테이블을 합칠 때 사용한다.
full outer join	full outer join은 왼쪽 테이블과 오른쪽 테이블을 합칠 때 사용한다.
cross join	cross join은 왼쪽 테이블과 오른쪽 테이블을 모두 합칠 때 사용한다.
natural join	natural join은 왼쪽 테이블과 오른쪽 테이블을 모두 합칠 때 사용한다.
inner join	inner join은 왼쪽 테이블과 오른쪽 테이블을 모두 합칠 때 사용한다.
on extract	on extract는 조건을 지정할 때 사용한다.
case when then else end	if문과 비슷한 역할을 한다. when절에는 조건을, then절에는 조건이 참일 때 실행할 쿼리를, else절에는 조건이 거짓일 때 실행할 쿼리를 작성한다. end 절은 case문의 끝을 의미한다.
inner join	두 테이블의 교집합을 반환합니다.
inner join on	on 절에는 교집합을 구할 기준이 되는 컬럼을 지정한다. where의 역할

left join은 왼쪽 테이블의 모든 데이터를 가져오고 오른쪽 테이블의 데이터는 일치하는 데이터만 가져온다.

SQL 작성 예시

public int signup(UserDTO userDTO) {
        String SQL = "INSERT INTO kurly_table(user_id, user_pw, user_name, user_email, user_hp, user_addr, user_gender, user_birth) VALUES(?,?,?,?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(SQL);
            ps.setString(1, userDTO.getUser_id());
            ps.setString(2, userDTO.getUser_pw());
            ps.setString(3, userDTO.getUser_name());
            ps.setString(4, userDTO.getUser_email());
            ps.setString(5, userDTO.getUser_hp());
            ps.setString(6, userDTO.getUser_addr());
            ps.setString(7, userDTO.getUser_gender());
            ps.setString(8, userDTO.getUser_birth());
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

public int signin(String user_id, String user_pw) {
        String SQL = "SELECT user_pw FROM kurly_table WHERE user_id=?";
        try {
            ps = conn.prepareStatement(SQL);
            ps.setString(1, user_id);
            rs = ps.executeQuery();

            if(rs.next()){
                if(rs.getString(1).equals(user_pw)){
                    return 1;
                }
                else{
                    return 0;
                }
            }
            else{
                return -1;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -2;
    }

public int update(UserDTO userDTO) {
        String SQL = "UPDATE kurly_table SET user_pw=?, user_name=?, user_email=?, user_hp=?, user_addr=?, user_gender=?, user_birth=? WHERE user_id=?";
        try {
            ps = conn.prepareStatement(SQL);
            ps.setString(1, userDTO.getUser_pw());
            ps.setString(2, userDTO.getUser_name());
            ps.setString(3, userDTO.getUser_email());
            ps.setString(4, userDTO.getUser_hp());
            ps.setString(5, userDTO.getUser_addr());
            ps.setString(6, userDTO.getUser_gender());
            ps.setString(7, userDTO.getUser_birth());
            ps.setString(8, userDTO.getUser_id());
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

public int delete(UserDTO userDTO) {
    String SQL = "DELETE FROM kurly_table WHERE user_id=? AND user_pw=?";
    try {
        ps = conn.prepareStatement(SQL);
        ps.setString(1, userDTO.getUser_id());
        ps.setString(2, userDTO.getUser_pw());
        return ps.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return -1;
}