SQL 02. DML 기본 명령어

1. SELECT : 데이터의 조회

SELECT의 구문 형식

SELECT select_expr

   [FROM table_references]

   [WHERE where_condition]

   [GROUP BY {col_name | expr | position}] 

   [HAVING where_condition]

   [ORDER BY {col_name | expr | position}]

 

WHERE : 특정한 조건의 데이터만 조회

# select 칼럼명 from 테이블_이름 where 조건식 ; 

# as : 열 이름의 별칭을 지정할 때 사용 ex. select name as 이름, gender 성별, hire_date '회사 입사일'

  * 중간에 공백 있으면 꼭 홀따움표('')로 별칭 감싸주기

# 조건 연산자 (=,<,>,<=,>=,<>,!= 등)dhk 관계 연산자 (not, and, or 등) 사용 가능

# where 칼럼명 between 180 and ~ 185 ;  ( = where 칼럼명 >= 180 and 칼럼명 <= 185 ) ;

# where 칼럼명 in ('x', 'y', 'z') ;  ( = where 칼럼명='x' or 칼럼명='y' or 칼럼명='z') ;

# where 칼럼명 like '김%' ;  - 문자열 내용 : %는 무엇이든 몇글자든 허용, _는 한 글자와 매치

 

ANY/ALL/SOME 그리고 서브쿼리(SubQuery, 하위쿼리)

# 서브쿼리 : 쿼리 안에 있는 쿼리로, select문으로 형성됨

# any (서브쿼리) : 서브쿼리의 여러 개의 결과 중 한 가지만 만족해도 됨 = some

# all (서브쿼리) : 서브쿼리의 여러 결과를 만족시켜야 함

# '= any (서브쿼리)' 는 = 'in (서브쿼리)' 와 동일

 

ORDER BY : 원하는 순서대로 정렬하여 출력

# select 칼럼1, 칼럼2, ... from 테이블_이름 order by 칼럼1 (asc) ;  - 기본적으로 오름차순

# select 칼럼1, 칼럼2, ... from 테이블_이름 order by 칼럼1  desc ; - 내림차순

# select 칼럼1, 칼럼2, ... from 테이블_이름 order by 칼럼1 desc, 칼럼2 asc ; - 여러 개로 정렬 가능 

# order by절은 select, from, where, group by, having 중에서 제일 뒤에 와야 한다.

 

DISTINCT : 중복된 것은 하나만 남김

# select distinct 칼럼 from 테이블_이름 ; - 중복된 것은 1개씩만 보여주면서 출력됨

 

LIMIT : 출력 개수 제한

# select 칼럼1 from 테이블_이름 order by 칼럼2 desc limit 10 ;

   - 칼럼2를 기준으로 내림차순으로 나타나는 칼럼1 데이터를 10개만 출력

# limit 시작, 개수   =   limit 개수 offset 시작

# select 칼럼1 from 테이블_이름 order by 칼럼2 desc limit 0, 10 ;  - 위와 똑같이 10개 출력

 

CREATE TABLE ... SELECT : 테이블 복사

# create table 새로운_테이블 (select 복사할칼럼 from 기존_테이블) ;

# create table newone (select * from oldone) ; - oldone 테이블을 newone 테이블에 그대로 복사

  * 기존에 지정된 Primary Key 및 Foreign Key 등의 제약 조건은 복사되지 않는다.

# 여기서 잠깐, Primary Key 추가 방법 :

ex.

create table memberTbl (select userID, name, addr from userTbl limit 3) ; -- 3건만 가져옴

alter table memberTbl

    add constraint pk_memberTbl primary key (userID) ; -- PK 지정

 

GROUP BY 및 HAVING 그리고 집계 함수

# select 칼럼1, sum(칼럼2) from 테이블_이름 group by 칼럼1 ;

# 별칭 사용 : select 칼럼1 as '아이디', sum(칼럼2) as '총합' from 테이블_이름 group by 칼럼1 ;

 

# 집계함수

 - avg( ), min( ), max( ) : 평균, 최소값, 최대값

 - count( ), count(distinct) : 행의 개수, 행의 개수(중복은 1개만 인정)

 - stdev( ), var_samp( ) : 표준편차, 분산

ex. 가장 큰 키와 가장 작은 키의 회원 이름과 키를 출력하는 쿼리 (서브쿼리와 조합하는 것이 수월)

select name, height from userTable

    where height = (select max(height) from userTable)

          or height = (select min(height) from userTable) ; 

 

# where절에는 집계 함수를 사용할 수 없다 => having절 사용!

# having절은 where절과 비슷하게 조건을 제한하는 것이지만, 집계 함수에 대해서 조건을 제한한다.

# having절은 꼭 group by절 다음에 나와야한다!

ex. 총 구매액이 1,000 이상인 사용자에게만 사은품을 증정하고 싶다면?

select userID as '사용자', sum(price * amount) as '총구매액' from buyTable

   group by userID

   having sum(price * amount) > 1000 

   ( order by sum(price * amount ) ) ;

 

WITH ROLLUP : 총합 또는 중간합계가 필요할 때 group by절과 함께 쓰임

# select 칼럼1, 칼럼2, sum(칼럼3*칼럼4) from 테이블_이름

      group by 칼럼2, 칼럼1

      with rollup ; 

 

2. INSERT : 데이터의 삽입

# insert into 테이블_이름(칼럼1, 칼럼2, ... ) values (값1, 값2, ...) ;

# 테이블_이름 뒤에 오는 칼럼은 생략이 가능하지만, 생략할 경우에 value 다음에 오는 값들의 순서와 개수가 테이블에 정의된 열 순서 및 개수가 동일해야 함

# AUTO_INCREMENT : 자동으로 1부터 증가하는 값 입력 ( CREATE TABLE 할 때 칼럼 속성에 지정 )

# auto_increment 지정 시엔 꼭 primary key 또는 unique로 지정해줘야 하고, 데이터형은 숫자형만 사용 가능

# 테이블의 속성이 auto_increment로 지정되어 있다면 insert에서는 해당 칼럼(열)이 없다고 생각하고 입력하면 됨

ex. 

create table testTable

   ( id int auto_increment primary key,

   userName char(3),

   age int ) ;

insert into testTable values (null, '조이', 23) ; - id는 제외하고 이름과 나이만 입력

# SELECT LAST_INSERT_ID() ; => 마지막에 id에 입력된 값을 보여줌

# 이후에 입력값을 100부터 입력되도록 변경하고 싶다면

# alter table testTable auto_increment=100 ;

# 증가값을 지정하려변 서버 변수인 @@auto_increment_increment 변수를 변경

ex. 초깃값을 1000으로 하고 증가값은 3으로 변경

alter table testTable auto_increment = 1000 ;

set @@auto_increment_increment = 3;

 

INSERT INTO ... SELECT : 대량의 샘플 데이터 생성

# insert into 테이블_이름 (칼럼1, 칼럼2, ... ) select문 ;

# select문의 결과 칼럼수는 insert 할 테이블의 칼럼수와 일치해야 함

# create table ... select 구문도 있음

# create table 새로운테이블 (select 칼럼1, 칼럼2, ... from 기존_테이블) ; 

# select * into 새로운테이블 from 기존_테이블 ; 

 

# INSERT IGNORE문 : PK 중복이더라도 오류를 발생시키지 않고 무시하고 넘어간다 (다음 데이터 입력되도록)

# insert ignore into 테이블_이름 values (값1, 값2, ... ) ;

 

# ON DUPLICATE KEY UPDATE : 기본 키 중복 시 데이터 새 값으로 수정

# insert into 테이블_이름 values(값1, 값2, 값3)

     on duplicate key update 칼럼2=값2, 칼럼3=값3 ; 

 

 

3. UPDATE : 데이터의 수정

# update 테이블_이름 set 칼럼1=값1, 칼럼2=값2 ... where 조건 ;

# 매우 주의! where절을 생략할 경우 테이블의 전체 행이 변경된다.

# where절 없이 입력하여 특정 칼럼이 모두 특정 값으로 변경된 경우 복구 불가능하거나 매우 복잡한 절차가 필요

# 그러나 가끔 전체 테이블 내용을 변경하고 싶은 경우 where절 생략

ex. 구매 테이블에서 현재의 단가가 모두 1.5배 인상

update buyTable set price = price * 1.5 ;

 

 

4. DELETE FROM : 데이터의 삭제

# delete from 테이블_이름 where 조건 ;

# where절이 생략되면 전체 데이터를 삭제한다.

 

대용량의 테이블 삭제 : DELETE / DROP / TRUNCATE

# delete from bigTable1 ;

# drop table bigTable2 ; 

# truncate table bigTable3 ;

# 위 세개중에 delete만 시간이 오래 걸림 - DML문으로, 트랜잭션 로그를 기록하기 때문

# drop과 DDL문으로 트랜잭션 발생시키지 않고 바로 테이블을 삭제

# truncate문의 효과는 delete와 동일하지만 트랜잭션 로그를 기록하지 않아서 속도가 무척 빠름

# 즉 대용량 테이블 삭제 시, 테이블 자체가 필요없으면 drop, 테이블 구조는 남겨놓고 싶다면 truncate로 삭제