-- 9월 16일 실습
-- 실습용 데이터 준비
CREATE TABLE jumsu (
    seq SERIAL,
    name VARCHAR(8) NOT NULL,
    java TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    mysql TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    html TINYINT(3) UNSIGNED,
    PRIMARY KEY (seq)
    ) ENGINE = MyISAM;

INSERT INTO jumsu (name, java, mysql, html)
VALUES ('변악도', '100', '100', '100');
INSERT INTO jumsu (name, java, mysql)
VALUES ('성춘향', '60', '85');
INSERT INTO jumsu (name, java, mysql)
VALUES ('홍길동', '80', '80');
INSERT INTO jumsu (name, java, mysql)
VALUES ('이몽룡', '60', '75');

-- 계산식으로 컬럼을 지정
SELECT seq AS `번호`, name AS `이름`, java AS `자바`,
    mysql AS `MySQL`, java + mysql AS `합계`
FROM jumsu
ORDER BY `합계` DESC;

-- 비교 연산자 실습
SELECT '7' > '8';
SELECT 7 = 8;
SELECT 7 <> 8;
SELECT 7 <=> NULL;
SELECT NULL <=> NULL;

-- NULL과의 비교
SELECT * FROM jumsu WHERE html IS NOT NULL;

-- 자바 점수가 60점이상 80점 이하인 행(row) 출력
SELECT * FROM jumsu WHERE java >= 60 AND java <= 80;
SELECT * FROM jumsu WHERE java BETWEEN 60 AND 80;

-- 자바 점수가 60점미만 80점 초과인 행(row) 출력
SELECT * FROM jumsu WHERE java < 60 OR java > 80;
SELECT * FROM jumsu WHERE java NOT BETWEEN 60 AND 80;

-- 홍길동과 성춘향의 점수
SELECT * FROM jumsu WHERE name = '홍길동' OR name = '성춘향';
SELECT * FROM jumsu WHERE name IN ('홍길동', '성춘향');

-- 홍길동과 성춘향을 제외한 사람들의 점수
SELECT * FROM jumsu WHERE name <> '홍길동' AND name <> '성춘향';
SELECT * FROM jumsu WHERE name NOT IN ('홍길동', '성춘향');

-- 우편번호 테이블에서 서울, 인천, 경기 지역의 구군은 총 몇 개?
SELECT COUNT(DISTINCT gugun) FROM ziptable
WHERE sido IN ('서울', '인천', '경기');

-- 홍씨 성을 가진 사람의 자바 점수
SELECT name, java FROM jumsu WHERE name LIKE '홍%';

-- 우편번호 테이블에서 '하'로 시작하는 구군은 몇 개? (중복 무시)
SELECT COUNT(*) FROM ziptable WHERE gugun LIKE '하%';

-- 우편번호 테이블에서 구군의 이름이 3글자인 곳은 몇 개?(중복 무시)
SELECT COUNT(*) FROM ziptable WHERE gugun LIKE '___'; -- "_" 3개 사용

SELECT COUNT(DISTINCT sido) FROM ziptable;
SELECT COUNT(*) FROM ziptable;

-- 우편번호 테이블에서 각 시도별 구군별 동의 개수? (중복 무시)
SELECT sido, gugun, COUNT(*) FROM ziptable GROUP BY sido, gugun;

-- 우편번호 테이블에서 서울, 인천, 경기의  구군별 동의 개수? (중복 무시)
SELECT sido, gugun, COUNT(*) FROM ziptable
WHERE sido IN ('서울', '인천', '경기')
GROUP BY sido, gugun;

-- 우편번호 테이블에서 서울, 인천, 경기의  구군별 동의 개 수를 내림차순 출력? (중복 무시)
SELECT sido, gugun, COUNT(*) FROM ziptable
WHERE sido IN ('서울', '인천', '경기')
GROUP BY sido, gugun
ORDER BY COUNT(*) DESC;

-- 자바와 MySQL 과목의 평균 점수
SELECT AVG((java + mysql) / 2) AS `자바와 MySQL 합계 평균` FROM jumsu;
SELECT java, mysql, java + mysql AS `sum`, (java + mysql) / 2 AS `avg` FROM jumsu; 
SELECT name, java, mysql, (java + mysql) / 2 FROM jumsu;

-- GROUP_CONCAT() 함수 사용 예
CREATE TABLE score (
    seq SERIAL,
    name VARCHAR(8),
    test_score TINYINT UNSIGNED,
    PRIMARY KEY (seq)
    ) ENGINE = MyISAM;

INSERT INTO score (name, test_score) VALUES ('홍길동', '95');
INSERT INTO score (name, test_score) VALUES ('김제동', '95');
INSERT INTO score (name) VALUES ('홍길동');
INSERT INTO score (name, test_score) VALUES ('홍길동', '100');
INSERT INTO score (name, test_score) VALUES ('김제동', '90');
INSERT INTO score (name) VALUES ('홍길동');

SELECT name, GROUP_CONCAT(test_score)
FROM score
GROUP BY 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;

그룹 함수

  
[게시판 분석모델 클래스다이어그램]

-- 게시판 테이블 생성
CREATE TABLE board_data (
    no SERIAL,
    title VARCHAR(100) NOT NULL,
    article VARCHAR(800) NOT NULL,
    writer VARCHAR(50) NOT NULL DEFAULT '아무개',
    wdate TIMESTAMP,
    hitcnt SMALLINT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (no)
    );
ALTER TABLE board_data ENGINE = MyISAM;
ALTER TABLE board_data COLLATE euckr_korean_ci;

-- list()
-- 페이징 계산 필요 offset = (pageNum - 1) * rows
SELECT no, title, article, writer, wdate, hitcnt
FROM board_data
ORDER BY no DESC
LIMIT 0, 10;

-- write()
INSERT INTO board_data (title, article, writer)
VALUES ("제목1", "안녕하세요.", "홍길동");

-- delete()
DELETE FROM board_data
WHERE no = ?;

-- edit()
UPDATE board_data
SET title = "제목1", article = "안녕하세요", writer = "홍길동", hitcnt = hitcnt + 1;

-- view()
SELECT no, title, article, writer, wdate, hitcnt
FROM board_data
WHERE no = ?;

-- search()
SELECT no, title, article, writer, wdate, hitcnt
FROM board_data
WHERE ? LIKE ?;

테이블의 생성과 변경

테이블 만들기

(형식)

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 TABLES;
-- 테이블 구조 확인
DESCRIBE ziptable;
-- 데이터베이스 생성
CREATE DATABASE example2
COLLATE = 'utf8_general_ci';
-- 생성된 데이터베이스 선택
USE example2
-- 테이블 생성
CREATE TABLE test1 (
    enumval ENUM ('봄', '여름', '가을', '겨울'),
    setval SET ('봄', '여름', '가을', '겨울'),
    charval VARCHAR(500)
    );
-- 데이터 추가
INSERT INTO test1 (enumval) VALUES ('봄');
INSERT INTO test1 (setval) VALUES ('봄,겨울');
INSERT INTO test1 (charval) VALUES ('자바(JSP) 프로그래밍 2기');
-- 데이터 추출
SELECT *
FROM test1;
-- 테이블 저장 엔진 MyISAM 설정 (.frm, .MYD, .MYI 파일 생성)
CREATE TABLE notran (
    num SERIAL,
    name VARCHAR(8)
    ) ENGINE=MyISAM;
-- 데이터 추가
INSERT INTO notran (name) VALUES ('홍길동');
-- 컬럼 제약 추가하여 테이블 생성
CREATE TABLE tab_addr (
    no BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    COMMENT '일련번호',
    name VARCHAR(8) NOT NULL COMMENT '이름',
    gender ENUM ('남', '여') NOT NULL DEFAULT '남',
    `position` ENUM ('사원', '대리', '과장', '부장', '사장'),
    hobby SET ('독서', '바둑', '승마', '목욕', '밀리터리', '등산', '낚시'),
    telno VARCHAR(13) COMMENT '전화번호 000-0000-0000' NOT NULL
    ) ENGINE=MyISAM;
-- 데이터 추가
INSERT INTO tab_addr (name, `position`, hobby, telno)
VALUES ('홍길동', '사원', '독서,목욕', '010-1234-1234');
-- 데이터 확인
SELECT no, name, gender, `position`, hobby, telno
FROM tab_addr;