데이터 추가 및 삭제와 변경

INSERT문

(형식)

SELECT INTO 테이블명 (컬럼 목록) VALUES (데이터 목록);

INSET문은 하나의 행(row)을 저장하는 명령입니다. 데이터 추가시 컬럼 목록과 데이터 목록의 짝이 반드시 맞아야 하며 컬럼의 순서는 컬럼 목록에 사용된 컬럼 이름을 따릅니다. 사실 데이터베이스의 테이블은 데이터의 저장순으로 로우(행)가 추가되며 로우의 순서는 의미가 없습니다. 대신 PRIMARY KEY(기본키)로 설정된 컬럼의 경우 자동으로 인덱스가 만들어져서 미리 정렬이 되어 있습니다. 인덱스와 연결된 테이블은 SELECT문의 실행시 기본키를 기준으로 정렬되어 출력됩니다.

AUTO_INCREMENT 컬럼 제약이 주어졌을 경우 추가시 컬럼을 생략하면 자동으로 증가된 값이 추가됩니다. NOT NULL 컬럼 제약이 주어진 컬럼은 무조건 추가를 해야합니다.

(사용예)

CREATE TABLE customers (
    customer_id INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    gender ENUM('남', '여') NOT NULL,
    name VARCHAR(8) NOT NULL,
    email VARCHAR(35) NOT NULL,
    address VARCHAR(255) NOT NULL,
    telephone VARCHAR(14) NOT NULL,
    registdate DATE DEFAULT NULL,
    modifydate TIMESTAMP
);

INSERT INTO customers (gender, name, email, address, telephone, registdate)
VALUES ('남', '홍길동', 'kidong@hong.com', '전라도 장성현 아차곡', '010-1234-1234', '2009-09-21');

INSERT INTO customers
VALUES ('2', '남', '철수', 'cs@chol.com', '서울시', '02-1234-1234', '2009-09-21', '2009-09-21');

INSERT INTO customers
VALUES (3, '여', '영희', 'yh@chol.com', '서울시', '02-4321-4321', '2009-09-20', '2009-09-21'),
    (4, '남', '칠숙', 'hwarang7@chol.com', '서울시', '02-1234-4321', '2009-09-20', '2009-09-21'),
    (5, '여', '덕만', 'queen@chol.com', '서울시', '02-4321-1234', '2009-09-20', '2009-09-21');

DELETE문, TRUNCATE문

(형식)

DELETE FROM 테이블명 WHERE 조건문;

DELETE문의 경우 테이블을 대상으로 삭제 작업을 하기 때문에 WHERE절이 없을 경우 테이블의 모든 데이터를 삭제합니다. 이때 로우 단위로 삭제하기 때문에 시간이 좀 걸리는데 만일 전체 데이터를 지우고자 한다면 TRUNCATE 테이블문을 사용하는 것이 더 효율적입니다.

(사용예)

DELETE FROM customers WHERE customer_id = 4;

UPDATE문

(형식)

UPDATE 테이블명 SET 컬럼1 = 데이터, 컬럼2 = 데이터, ... WHERE 조건문;

특정 컬럼의 값을 변경할 때 사용합니다.

(사용예)

UPDATE costomers SET name = '홍삼정', telephone = '031-0123-1234' WHERE name = '홍길동';

대량 데이터 입력 (Bulk operation)

LOAD DATA INFILE

(사용예)

LOAD DATA LOCAL INFILE 'C:\\zip.txt'
    INTO TABLE ziptable
    CHARACTER SET 'utf8'
    COLUMNS TERMINATED BY '\t'
    LINES TERMINATED '\n'
    (seq, sido, gugun, dong, bunji);

mysqlimport 유틸리티

(사용예)

c:\> mysqlimport -u root -p ziptable zip.txt

MySQL 함수

날짜 함수

NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP()

현재 시간을 리턴합니다.

SYSDATE()

'YYYY-MM-DD HH:MM:SS' 또는 'YYYYMMDDHHMMSS' 형식으로 현재 날짜나 시간을 리턴합니다. 5.0.13 버전이후부터는 SYSDATE()는 실행 시점의 시간을 표시하므로 NOW()와 차이점이 있습니다. 아래 명령을 실행하여 차이점을 확인하세요.

SELECT NOW(), SLEEP(2), NOW();
SELECT SYSDATE(), SLEEP(2), SYSDATE();

CURDATE(), CURRENT_DATE, CURRENT_DATE()

'YYYY-MM-DD' 또는 'YYYYMMDD' 형식으로 현재 날짜를 리턴합니다.

CURTIME(), CURRENT_TIME, CURRENT_TIME()

'HH:MM:SS' 또는 'HHMMSS.uuuuuu' 형식으로 현재 시간을 리턴합니다.

DATE_FORMAT(date,format)

date로 지정된 날짜른 format에 의해 표시합니다.

GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

특정 국가의 형식으로 표시합니다. 메뉴얼을 참고 하세요.

YEAR(date)

'1000'에서 '9999' 사이의 년도를 리턴합니다.

QUARTER(date)

분기를 1 ~4 로 리턴합니다.

MONTH(date)

월을 1 ~ 12 로 리턴합니다.

MONTHNAME(date)

월 이름을 리턴합니다.

WEEK(date[,mode])

요일 순서를 출력합니다. mode가 지정되면 특정 조건에 맞는 값을 리턴합니다.

WEEKDAY(date)

요일 순서를 리턴합니다. (0=Sunday, 1=Monday, ..., 6=Saturday)

WEEKOFYEAR(date)

년중 주의 순서를 리턴합니다. WEEK(date, 3)과 동일한 표현입니다.

YEARWEEK(date), YEARWEEK(date,mode)

해당되는 년도 및 주를 리턴합니다. mode는 WEEK() 함수와 동일한 동작을 합니다.

DAYNAME(date)

요일 이름을 리턴합니다.

DAYOFYEAR(date)

년중 날짜를 리턴합니다.

DAY(date), DAYOFMONTH(date)

월중 날짜를 리턴합니다.

DAYOFWEEK(date)

주중 순서를 리턴합니다. (1=Sunday, 2=Monday, ..., 7=Saturday)

LAST_DAY(date)

해당 월의 마지막 날짜를 리턴합니다.

DATE(expr)

date나 datetime 형식에서 date 부분만 리턴합니다.

HOUR(time)

시간을 리턴합니다.

MINUTE(time)

분을 리턴합니다.

SECOND(time)

초를 리턴합니다.

TIME(expr)

time이나 datetime 형식에서 time 부분만 리턴합니다.

MICROSECOND(expr)

마이크로초를 리턴합니다.

PERIOD_ADD(P,N)

P기간에 N달만큼 더한 결과를 YYYYMM 형태로 리턴합니다.

PERIOD_DIFF(P1,P2)

YYMM 이나 YYYYMM 형식의 P1과 P2 사이가 몇 달인지를 리턴합니다.

DATE_ADD(date,INTERVAL expr unit), ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

date로부터 expr만큼 type 단위로 더한 날짜를 리턴합니다.

DATE_SUB(date,INTERVAL expr unit), SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)

date로부터 expr만큼 unit 단위로 뺀 날짜를 리턴합니다.

DATEDIFF(expr1,expr2)

expr1과 expr2 사이의 일 수를 리턴합니다.

TIMEDIFF(expr1,expr2)

expr1과 expr2 사이의 시간을 time 형식으로 리턴합니다.

ADDTIME(expr1,expr2)

date나 datetime 형식의 expr1에 time 형식의 expr1을 더한 time 형식의 값을 리턴합니다.

SUBTIME(expr1,expr2)

date나 datetime 형식의 expr1에 time 형식의 expr1을 뺀 time 형식의 값을 리턴합니다.

TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

date나 datetime 형식의 시간을 datetime 형식으로 리턴합니다. expr2가 주어지면 해당 시간으로 시간을 지정합니다.

TIMESTAMPADD(unit,interval,datetime_expr)

datetime_expr로부터 interval만큼 unit 단위로 더한 시간를 리턴합니다.

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

datetime_expr1과 datetieme_expr2 사이를 unit 단위로 리턴합니다.

TIME_FORMAT(time,format)

time 형식의 시간을 format 형식으로 리턴합니다. format은 DATE_FORMAT() 함수와 동일합니다.

EXTRACT(unit FROM date)

date로부터 unit 형식으로 값을 리턴합니다.

SELECT EXTRACT(YEAR FROM SYSDATE());
SELECT EXTRACT(MONTH FROM SYSDATE());
SELECT EXTRACT(DAY FROM SYSDATE());

FROM_DAYS(N)

0년부터 계산되 날 수로부터 날짜를 리턴합니다.

TO_DAYS(date)

date를 기준으로 0년부터 계산된 날 수를 리턴합니다. 그레고리력을 따르지 않습니다.

SEC_TO_TIME(seconds)

초를 time 형식으로 리턴합니다.

TIME_TO_SEC(time)

time 형식의 시간을 초로 리턴합니다.

STR_TO_DATE(str,format)

문자열str을 format 형식의 시간으로 리턴합니다. format문자열은 DATE_FORMAT() 과 동일합니다.

MAKEDATE(year,dayofyear)

year와 day-of-year 값으로 date 형식(YYYY-MM-DD)의 날짜를 리턴합니다.

MAKETIME(hour,minute,second)

hour, minute, second 값으로 time 형식(HH:MM:SS)의 시간을 리턴합니다.

CONVERT_TZ(dt,from_tz,to_tz)

시간대 변경 함수.

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

유닉스 타입의 시간을 리턴합니다.

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

유닉스 시간 ('1970-01-01 00:00:00' 기준)을 리턴합니다. date가 주어지면 date의 유닉스 시간을 리턴합니다.

UTC_DATE, UTC_DATE()

UTC 시간을 리턴합니다.

UTC_TIME, UTC_TIME()

UTC 시간을 리턴합니다.

UTC_TIMESTAMP, UTC_TIMESTAMP()

UTC 시간을 리턴합니다.

MySQL 함수

수학 함수

산술 연산자

+, -, *, /

더하기, 빼기, 곱하기, 나누기 사칙연산을 처리합니다.

DIV

정수 나누기 연산을 처리합니다. FLOOR() 함수로 비슷한 처리가 가능합니다.

SELECT 5 DIV 2;
SELECT FLOOR(5 / 2);

%

나머지 연산으로 MOD() 함수와 같은 역할을 합니다.

SELECT 5 % 2;
SELECT MOD(5, 2);

수학 함수

MOD(N, M), N % M, N MOD M

N을 M으로 나눈 나머지를 리턴합니다.

ABC(X)

절대값을 리턴합니다.

SIGN(X)

양수, 음수에 따라 부호를 리턴합니다.

CEIL(X), CEILING(X)

X보다 작지 않은 정수의 최소값을 리턴합니다.

FLOOR(X)

X보다 크지 않은 정수의 최대값을 리턴합니다.

ROUND(X), ROUND(X, D)

반올림 값을 리턴합니다. 두번째 문법은 D자리까지의 반올림 값을 리턴합니다.

TRUNCATE(X, D)

D자리에서 버림한 값을 리턴합니다.

FORMAT(X, D)

문자열 함수의 FORMAT() 참고.

RAND(), RAND(N)

0 ~ 1 사이의 난수를 리턴합니다. N은 seed 값을 의미합니다.

POW(X, Y), POWER(X, Y)

X의 Y 제곱한 값을 리턴합니다.

SQRT(X)

X의 제곱근을 리턴합니다.

CONV(X, from_base, to_base)

form_base진수 X를 to_base진수로 리턴합니다.

OCT(N)

8진수로 리턴합니다.

HEX(X or str)

문자열 함수 HEX() 참고.

EXP(X)

X의 제곱에 대한 자연로그의 근 e 값을 리턴합니다.

LN(X)

X의 자연로그를 리턴합니다.

LOG(X), LOG(B, X)

로그 근을 구합니다.

LOG2(X)

e를 밑으로 하는 로그를 리턴합니다.

LOG10(X)

상용로그를 리턴합니다.

CRC32(expr)

CRC값을 계산하여 32비트의 부호화되지 않은 값을 리턴합니다.

PI()

원주율 값을 리턴합니다. 기본적으로 7자리의 값을 리턴하지만 MySQL은 이배정도 값까지 리턴할 수 있습니다.

SELECT PI(); -- 3.141593
SELECT PI() + 0.000000000000000000; -- 3.14159265358979

RADIANS(X)

X각도의 라디안 값을 리턴합니다.

DEGREE(X)

X 라이안 값을 각도로 리턴합니다.

SIN(X)

라디안 X의 사인 값을 리턴합니다.

COS(X)

라디안 X의 코사인 값을 리턴합니다.

TAN(X)

라디안 X의 탄젠트 값을 리턴합니다.

ASIN(X)

라디안 X의 역 사인 값을 리턴합니다.

ACOS(X)

라디안 X의 역 코사인 값을 리턴합니다.

ATAN(X)

라디안 X의 역 탄젠트 값을 리턴합니다.

COT(X)

라디안 X의 코탄젠트 값을 리턴합니다.

MySQL 함수

문자열 함수

코드변환과 관련된 함수들

ASCII(str)

문자열 중 가장 왼쪽 문자의 ASCII 코드 값을 리턴합니다. NULL의 경우 NULL을 출력합니다.

SELECT ASCII('ABC'); -- 65

ORD(str)

멀티 바이트 문자열의 가장 왼쪽 문자의 코드값을 리턴합니다.

CHAR(N, ... [USING character_name])

숫자 값에 해당하는 문자들을 리턴합니다.

SELECT CHAR(65, 66, 15380608 USING utf8); -- AB가

BIN(N)

숫자 값을 이진수로 리턴합니다. CONV(N, 10, 2)와 같습니다.

HEX(N or str)

숫자 N을 16진수로 리턴합니다. 문자열의 경우 두자리의 16진수 문자열로 리턴합니다.

UNHEX(str)

16진수 문자열을 문자열로 리턴합니다.

문자열 길이를 구하는 함수들

BIT_LENGTH(str)

문자열의 길이를 bit 단위로 출력합니다.

SELECT BIT_LENGTH('가A'); -- 32 UTF-8 인코딩일 경우 한글은 3byte이며 영문은 1byte로 처리합니다.

LENGTH(str), OCTET_LENGTH(str)

byte단위의 길이를 리턴합니다.

CHAR_LENGTH(str)

글자 수를 리턴합니다. 멀티 바이트 문자의 경우 한 글자로 취급합니다. LENGTH()의 경우 바이트 단위로 글자 수를 셉니다.

SELECT CHAR_LENGTH('가나다'); -- 3

SELECT LENGTH('가나다'); -- 9 (UTF-8 인코딩일 경우 한글은 3byte 처리)

공백 처리 함수들

LTRIM(str)

문자열 왼쪽의 공백을 모두 제거하여 리턴합니다.

RTRIM(str)

문자열 오른쪽의 공백을 모두 제거하여 리턴합니다.

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

문자열 str에서 양쪽으로 모든 remstr 문자열을 제거합니다. remstr이 지정되지 않으면 공백을 제거합니다. BOTH는 양쪽, LEADING은 문자열 왼쪽, TRAILING은 문자열 오른쪽의 모든 remstr 문자열을 제거합니다.

SPACE(N)

N개의 공백 문자열을 리턴합니다.

문자열 처리 함수들

CONCAT(str1, str2)

문자열들을 연결하여 리턴합니다.

SELECT CONCAT('ABC', 'DEF'); -- ABCDEF

CONCAT_WS(separator, str1, str2)

문자열들은 구분자로 연결하여 연결합니다.

SELECT CONCAT_WS(',', 'ABC', 'DEF'); -- ABC,DEF

INSERT(str, pos, len, newstr)

문자열 str을 POS 위치부터 len 길이만큼 잘라낸 후 그 자리를 newstr로 대체합니다.

INSTR(str, substr)

str에서 substr이 처음 나타나는 지점의 위치를 리턴합니다. LOCATE()와 인자의 순서가 바뀌었을 뿐 기능은 같습니다.

POSITION(substr IN str)

LOCATE(substr, str)와 동일한 표현입니다.

LOCATE(substr, str), LOCATE(substr, str, pos)

문자열 str에서 substr이 처음 나타나는 지점의 위치를 리턴합니다. 두 번째 문법은 str에서 pos 위치부터 시작해서 substr이 나타나는 지점의 위치를 리턴합니다.

SUBSTR(str, pos), SUBSTR(str FROM pos), SUBSTR(str, pos, len), SUBSTR(str FROM pos FOR len)

SUBSTRING()과 동일한 표현입니다.

SUBSTRING(str, pos), SUBSTRING(str FROM pos), SUBSTRING(str, pos, len), SUBSTRING(str FROM pos FOR len)

문자열 str를 pos 위치부터 끝까지 문자열를 리턴합니다. 두번째 문법은 문자열 str를 pos 위치부터 len 길이만큼 리턴합니다.

SUBSTRING_INDEX(str, delim, count)

문자열 str을 구분자 delim으로 분리하여 count번째 위치만큼 리턴합니다. count가 양수이면 문자열의 왼쪽부터 순서를 세고, 음수이면 오른쪽부터 순서를 셉니다.

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -- www.mysql

LEFT(str, len)

문자열 str에서 왼쪽으로부터 len 길이만큼 리턴합니다.

RIGHT(str, len)

문자열 str을 오른쪽에서 len 길이만큼 리턴합니다.

MID(str, pos, len)

SUBSTRING(str, pos, len)과 동일한 표현입니다.

REPLACE(str, from_str, to_str)

문자열 str에서 from_str을 찾아서 to_str로 대치하여 리턴합니다.

REVERSE(str)

문자열을 뒤집어서 리턴합니다.

UPDATE t SET BLOB col=LOAD_FILE('file.txt') WHERE id=1;

LPAD(str, len, padstr)

문자열 str의 len길이가 될 때까지 왼쪽에 padstr을 반복하여 붙여서 리턴합니다.

RPAD(str, len, padstr)

문자열 str의 len길이가 될 때까지 오른쪽에 padstr을 반복하여 붙여서 리턴합니다.

REPEAT(str, count)

문자열 str을 count만큼 반복시켜서 리턴합니다.

FORMAT(X, D)

숫자 X를 "#,###,###.##"과 같은 형식으로 리턴합니다. D는 표시할 소수점 자리를 나타냅니다.

QUOTE(str)

문자열 str내에 작은 따옴표가 있을 경우 SQL 문장이 오류가 발생할 수 있기 때문에 이를 이스케이프 문자를 사용하여 처리합니다. 예를 들어 INSERT할 데이터 안에 작은 따옴표가 들어있을 경우 QUOTE() 함수로 묶어 주면 정상적으로 추가할 수 있습니다.

LCASE(str), LOWER(str)

문자열을 소문자로 변환하여 리턴합니다.

UCASE(str), UPPER(str)

문자열을 대문자 문자열로 리턴합니다.

LOAD_FILE(file_name)

문자열 파일을 읽어들입니다.

기본적인 질의문 (SELECT)
기본적인 질의문
(형식)
SELECT 컬럼목록 FROM 테이블명
SELECT는 테이블에서 원하는 컬럼을 지정하여 데이터를 불러오는 명령입니다. 참고로 SELECT와 UPDATE는 작업  대상이 컬럼 단위이며, INSERT와 DELETE는 로우 단위가 작업 대상입니다. SELECT, INSERT, UPDATE,  DELETE를 일컬어 데이터 관리어(DML; Data Management Language)라고 합니다.
SELECT문에서 컬럼 목록은 컬럼을 쉼표(,)로 구분하며 컬럼 목록외에도 수식을 이용하여 계산된 결과를 출력할 수 있습니다.
(사용예)
SELECT * FROM board_data -- board_data 테이블의 모든 데이터를 추출
SELECT title FROM board_data -- board_data 테이블에서 title 컬럼의 데이터들만 추출
SELECT hitcnt + 10 FROM board_data
-- hitcnt 컬럼의 데이터에 일괄적으로 10을 더하여 추출, hitcnt 컬럼의 값은 불변
별칭(Alias)
마지막 예에서 컬럼의 이름이 "hitcnt + 1"으로 표시되었을 것입니다. 이런 이름의 컬럼 이름은 JDBC를 이용하여  자바에서 사용하기에 적합하지 않기 때문에 다른 이름이 필요할 것입니다. 이렇게 다른 이름을 컬럼 이름으로 표시하는 기능을 "알리아스"라고 합니다.
(사용예)
SELECT hitcnt + 10 AS result FROM board_data
-- "hitcnt + 10"이라는 컬럼명 대신 "result"가 표시
SELCT * FROM board_data AS brd;
-- "board_data"라는 테이블 이름을 "brd"라는 알리아스로 사용
테이블에도 알리아스를 지정할 수 있습니다. 테이블 알리아스는 조인(JOIN) 연산에서 자주 볼 수 있습니다.
추출결과 정렬하기
(형식)
SELECT 컬럼 목록 FROM 테이블명 ORDER BY 컬럼명 [ASC | DESC]
컬럼명을 기준으로 오름차순(ASC)과 내림차순(DESC)으로 추출을 합니다. 디폴트로 오름차순을 하기 때문에 ASC는 생략합니다. 2개 이상 컬럼을 기준으로 정렬하기를 원할 경우 쉼표(,)로 구분하여 입력합니다.
(사용예)
SELECT * FROM board_data ORDER BY writer ASC, no DESC;
작성자를 기준으로 오름차순하여 출력하고 같은 이름일 경우 번호순으로 내림차순하여 출력합니다.
조건절을 이용한 데이터 추출
WHERE절을 사용하여 컬럼을 통째로 가져오지 않고 일부를 걸러낼 수 있습니다.
(형식)
SELECT 컬럼 목록 FROM 테이블명 WHERE 조건문
비교 연산자와 논리 연산자를 이용하여 조건문을 작성할 수 있습니다.
비교 연산자
값을 비교하여 조건을 만족하면 "1"을 만족하지 않으면 "0"을 출력합니다.
= (같다)
!= 혹은 <> (같지않다)
> (크다)
>= (크거나 같다)
< (작다)
<= (작거나 같다)
<=> (NULL과 비교)
일반적으로 NULL과의 비교는 무조건 NULL이 출력되는데 이 연산을 사용하면 NULL인지를 비교할 수 있다.
(사용예)
SELECT 1 <=> 1, 1 <=> NULL, NULL <=> NULL;
IS NULL (NULL인지 확인)
(사용예)
SELECT seq, title, writer, wdate FROM board_data WHERE writer IS NULL ORER BY no DESC;
writer 컬럼의 값이 NULL인 로우들만 출력합니다.
BETWEEN 최소값 AND 최대값 (최소값에서 최대값 사이의 구간)
IN (값1, 값2, ...) (집합에 포함된 값 중 하나 이상 포함)
LIKE (와일드카드 문자인 "%"와 "_"를 사용하여 비교)
논리 연산자
NOT 혹은 !
AND 혹은 &&
두 결과가 모두 "1"일 경우만 "1"을 출력합니다. 결과 중 NULL이 있으면 NULL을 출력합니다.
OR 혹은 ||
두 결과중 하나 이상이 "1"인 경우 "1"을 출력합니다.
XOR
두 결과가  같지 않을 경우 "1", 같을 경우 "0"을 출력합니다.
그룹으로 묶어서 출력하기
출력된 컬럼 값의 중복을 허용하지 않도록 하기 위해 "DISTINCT" 예약어를 사용하여 출력합니다. 출력 순서를 기준로 첫 번째 컬럼에 대해 한 번만 사용할 수 있습니다.
(사용예)
SELECT DISTINCT sido, gugun FROM ziptable;
GROUP BY절
데이터를 출력하다 보면 각 시도별로 구군이 몇 개씩 있는지를 알고 싶을 경우가 있습니다. 이를 위해 COUNT()와 같은 그룹함수를 제공하며 그룹함수와 함께 그룹별로 결과를 출력할 수 있도록 GROUP BY절을 제공합니다.
(사용예)
SELECT sido, COUNT(gugun)
FROM ziptable
WHERE sido IN ('서울', '인천')
GROUP BY sido;
HAVING절
GROUP BY절에 의해 그룹으로 묶인 결과물 중 그룹 함수 등을 이용한 값에 대한 조건이 필요할 경우 사용합니다.
(사용예) 부서(depid)별 평균 급여(salary) 중 3500이상인 부서
SELECT depid, AVG(salary)
  FROM staff
  GROUP BY depid
HAVING AVG(salary) >= 3500;
그룹 함수
[주요 그룹 함수들]  
함수
설명
AVG()
지정된 컬럼 값들의 평균을 리턴
COUNT(DISTINCT)
중복되지 않는 컬럼 값을 갖는 행의 수를 리턴
COUNT()
컬럼을 지정할 경우는 컬럼의 값이 NULL이 아닌 인수들의 행 수를 리턴하고 "*"를 지정할 경우는 모든 행 수를 리턴
GROUP_CONCAT()
지정된 컬럼에서 NULL이 아닌 값을 쉼표(,)로 연결하여 출력
MAX(DISTINCT)
      MIN(DISTINCT)
      MAX()
      MIN()    
컬럼에서 중복되지 않는 가장 긴 문자열을 리턴, 숫자 값의 경우 최대 최소값 리턴
STD()
컬럼 값들의 표준 편차 리턴
SUM()
컬럼 값들의 합을 리턴

테이블의 생성과 변경

테이블 만들기

(형식)

CREATE CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (
컬럼 정의,
...)
[테이블 옵션 지정]

TEMPORARY를 지정하면 1회용 테이블로 만들어져서 현재 연결이 유지되는 동안만 사용이 가능합니다. 현재 연결을 종료하면 자동으로 삭제됩니다. IF NOT EXISTS 지정은 이미 동일한 이름의 테이블이 존재할 경우 에러없이 문장의 실행을 중지시킵니다. 배치처리를 위해 사용합니다. 테이블 옵션 중 "ENGINE"은 "MyISAM"과 "InnoDB" 등이 사용되며 MyISAM의 경우 트랜젝션을 지원하지 않는대신 빠르고 안정적인 속도를 보장받을 수 있습니다. 반면 InnoDB의 경우 MyISAM에 비해 늦은 속도대신 트랜젝션을 지원합니다.

(사용예)

-- 테이블 생성
CREATE TABLE tab_sample (
seq SERIAL, -- BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 정의의 별칭(alias)
name VARCHAR(8) NOT NULL,
gender ENUM('남', '여') NOT NULL,
age TINYINT(3) UNSIGNED NOT NULL,
habby SET('독서', '낚시', '등산', '바둑', '헬스', '기타') NOT NULL,
etc VARCHAR(50),
PRIMARY KEY (seq) -- 테이블 제약으로 기본키 지정
) ENGINE = MyISAM; -- Storage Engine을 MyISAM으로 지정

-- 데이터 추가
INSERT INTO tab_sample (name, gender, age, habby)
VALUES ('홍길동', '남', '17', '독서,낚시'); -- SET값을 입력할 경우 띄어쓰기하면 안됩니다.

-- 테이터 추출
SELECT * FROM tab_sample;

테이블 변경하기

(형식)

ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...

ALTER TABLE 명령은 테이블의 컬럼의 추가, 변경, 삭제와 컬럼의 타입 변경, 제약 변경, 테이블의 이름 변경 및 컬럼 이름 변경 등 다양한 작업을 할 수 있습니다.

테이블 컬럼 추가

ALTER TABLE tab_sample
ADD COLUMN address VARCHAR(255) NOT NULL;

-- 컬럼을 시작 위치에 추가, "AFTER 컬럼명"을 이용하여 특정 컬럼 앞에 위치 가능
ALTER TABLE tab_sample
ADD COLUMN code VARCHAR(3) NOT NULL FIRST;

테이블 컬럼 변경

-- 컬럼 타입 변경
ALTER TABLE tab_sample
MODIFY COLUMN habby SET('독서', '낚시', '등산', '바둑', '헬스', '조깅', '기타') NOT NULL;

-- 컬럼 이름 변경 및 타입 변경
ALTER TABLE tab_sample
CHANGE COLUMN address addr VARCHAR(255) NOT NULL;

테이블 컬럼 삭제

-- 컬럼 삭제
ALTER TABLE tab_sample
DROP COLUMN etc;

테이블 옵션 변경

-- 저장 엔진 변경
ALTER TABLE tab_sample
ENGINE = InnoDB;

테이블 이름 변경

ALTER TABLE tab_sample
RENAME sample;

테이블 삭제하기

(형식)

DROP TABLE [IF EXISTS] tbl_name;

/* 데이터베이스 확인 */

SHOW DATABASES;

/* 데이터베이스 생성 */

CREATE DATABASE head1ton_db;

/* 데이터베이스 접속 */

USE head1ton_db;

USE mysql;

/* USER 테이블 확인 */

DESC user;

/* 계정 생성 */

insert into user(host,user, password, ssl_cipher, x509_issuer,x509_subject,authentication_string)

values('localhost','head1ton',password('oracle'),'','','','');

/* 계정 확인 */

select host, user, password from user;

/* 권한 테이블 확인 */

select * from db;

desc db;

/* DB와 계정 연결 */

insert into db values ('localhost', 'head1ton_db', 'head1ton', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'n', 'n', 'y', 'y');

select * from mysql.db;

grant all on head1ton_db.* to 'head1ton'@'localhost' identified by 'oracle';

/* 외부 접근 권한 설정 */

grant all on head1ton_db.* to 'head1ton'@'localhost' identified by 'oracle';

GRANT ALL PRIVILEGES ON *.* to head1ton@localhost IDENTIFIED BY 'oracle' WITH GRANT OPTION;

/* 권한 적용 */

flush privileges;

/* 계정 삭제 */

drop user head1ton@localhost;