1 학습목표

2 데이터베이스 제어 SQL

  1. 데이터베이스 리스트 출력

    SHOW DATABASES;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +--------------------+
    ## | Database           |
    ## +--------------------+
    ## | information_schema |
    ## | admin              |
    ## | gradeall           |
    ## | jskim              |
    ## | mysql              |
    ## | performance_schema |
    ## | phpmyadmin         |
    ## | quiz               |
    ## | sys                |
    ## | wikidb             |
    ## +--------------------+
  2. 데이터베이스 생성

    CREATE DATABASE [db_name];
    • 이 문장을 실행하면 MySQL data 디렉토리에 db_name이란 서브디렉토리가 생성된다.
    • 만일 같은 이름의 data base디렉토리가 있으면 에러메시지를 내보낸다.

    • 새로운 database world를 생성

    CREATE DATABASE world;
  3. 데이터베이스 이동

    USE world;
  4. 데이터베이스 삭제

    DROP DATABASE [IF EXISTS] db_name
    + DROP DATABASE
    + IF EXISTS: 삭제하려는 데이터베이스가 없는 경우에 에러메시지를 막아줌
  5. DATABASE 한글처리
    • 문자셋(Character Set): 문자의 인코딩 규칙이 정의된 집합 (ASCII, EUC-KR, UTF-8 등)
    • 콜레이션(Collation): 문자셋으로 인코딩된 문자들간 비교시 사용하는 규칙집합
    • 데이터베이스 생성시 character set과 collation을 지정하는 방법:

      CREATE DATABASE mydb
      DEFAULT CHARACTER SET utf8 
      DEFAULT COLLATE utf8_general_ci;

3 테이블 정의 및 제어 SQL

3.1 테이블 생성

CREATE TABLE [IF NOT EXISTS] 테이블이름
컬럼이름 자료속성 기타속성 정의1;
...;
컬럼속성 정의n;
  • 컬럼속성 (자료의 속성)
    • INTEGER[n], INT[n]: n자리의 정수
    • REAL: 실수값 지정
    • CHAR(n): 고정크기(n) 문자열
    • VARCHAR(n): 가변크기 문자열
    • DATE
    • TIME
  • 컬럼속성:
    • PRIMARY KEY : 기본키로 설정
    • NOT NULL : 반드시 값을 가져야 함을 의미
    • AUTO_INCREMENT : 레코드가 삽입될 때 마다 자동으로 값을 증가
    • DEFAULT default_value: 디폴트값을 지정
  • 테이블 생성시 한글 처리 - 테이블 생성시 character set과 collation을 지정하는 방법:

    CREATE TABLE mytbl(
     테이블 정의문;
     ...
     ...
    ) DEFAULT CHARACTER SET utf8 
      DEFAULT COLLATE utf8_general_ci;
  1. 테이블 생성 예제 1
    • 아래의 컬럼정보를 갖는 고객테이블

      테이블 명 : customer
      컬럼 명   : no: 자동으로 부여되는 일련 번호
              name: 이름
              age: 나이
              phone: 전화번호
              email: 전자우편주소
              address: 주소
    • 테이블 명세서

      테이블명 컬럼명 데이터형식 NULL유무 기본키 외래키 FK테이블명 FK컬럼명
      고객테이블 no INTEGER NOT NULL PK
      (customer) name CHAR(20) NOT NULL
      age TINYINT
      phone VARCHAR(20)
      email VARCHAR(30) NOT NULL
      address VARCHAR(50)
    • SQL

      CREATE TABLE customer(
          no INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
          name CHAR(20) NOT NULL,
          age TINYINT,
          phone VARCHAR(20),
          email VARCHAR(30) NOT NULL,
          address VARCHAR(50)
      );
  2. 예제 데이터-애완동물 관리

    이름 소유자 종류 암수 출생일 사망일
    Fluffy Harold cat f 1993-02-04
    Claws Gwen cat m 1994-03-17
    Buffy Harold dog f 1989-05-13
    Fang Benny dog m 1990-08-27
    Bowser Dianne dog m 1998-08-31 1995-07-29
    Chirpy Gwen bird f 1998-09-11
    Whistler Gwen bird 1997-12-09
    Slim Benny snake m 1996-04-29
      CREATE TABLE jskim.pet(
        name  VARCHAR(20) NOT NULL PRIMARY KEY,
        owner VARCHAR(20),
        species VARCHAR(20),
        sex VARCHAR(1),
        birth DATE,
        death DATE
        );

3.2 테이블 스키마 조회

USE jskim;
DESC pet;
## mysql: [Warning] Using a password on the command line interface can be insecure.
## +---------+-------------+------+-----+---------+-------+
## | Field   | Type        | Null | Key | Default | Extra |
## +---------+-------------+------+-----+---------+-------+
## | name    | varchar(20) | NO   | PRI | NULL    |       |
## | owner   | varchar(20) | YES  |     | NULL    |       |
## | species | varchar(20) | YES  |     | NULL    |       |
## | sex     | varchar(1)  | YES  |     | NULL    |       |
## | birth   | date        | YES  |     | NULL    |       |
## | death   | date        | YES  |     | NULL    |       |
## +---------+-------------+------+-----+---------+-------+

3.3 자료의 검색(조회)

SELECT * FROM jskim.pet;
## mysql: [Warning] Using a password on the command line interface can be insecure.
## +----------+--------+---------+------+------------+------------+
## | name     | owner  | species | sex  | birth      | death      |
## +----------+--------+---------+------+------------+------------+
## | Bowser   | Dianne | dog     | m    | 1998-08-31 | 1995-07-29 |
## | Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
## | Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
## | Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
## | Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
## | Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
## | Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
## | Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
## +----------+--------+---------+------+------------+------------+

3.4 테이블의 삭제 (DELETE/DROP)

  • 데이터만 삭제, 테이블 정보는 그대로 둠

        DELETE FROM [table_name];
  • 모두 삭제

        DROP TABLE table_name;

3.5 테이블에 자료입력

  • 레코드 단위의 입력: INSERT 문 사용
  • 외부 파일에서 입력: LOAD DATA 구문의 이용
  1. 레코드 단위 입력

    INSERT INTO jskim.pet VALUES('Puffball','Diane','hamster','f','1999-03-30',NULL);
    INSERT INTO jskim.pet VALUES('Fluffy','Harold','cat','f','1993-02-04',NULL);
    INSERT INTO jskim.pet VALUES('Claws','Gwen','cat','m','1994-03-17',NULL);
    ...
  2. 외부 파일에서 입력: LOAD DATA

    LOAD DATA LOCAL INFILE 외부파일명 INTO TABLE 테이블이름
      FIELDS TERMINATED BY "," (컬럼간 구분자 표시)
      그외의 옵션들... ;
    • 아래와 같이 입력

      LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet
      FIELDS TERMINATED BY ",";

3.6 실습 1: 뉴스 테이블

  1. 테이블 생성

    CREATE TABLE jskim.news(
    journal VARCHAR(20) NOT NULL,
    title   VARCHAR(200),
    pub_date DATE,
    reporter VARCHAR(20),
    contents text
    );
  2. 외부파일을 이용한 입력

    • 엑셀 데이터 변환
      • 기사내용의 줄나눔(개행) 문자를 공백으로 치환
      • 탭구분 텍스트 파일로 저장
      • Linux에서는 한글 인코딩 UFT-8로 변환
      • 아래의 SQL구문 사용
     LOAD DATA LOCAL INFILE "/var/www/html/lectures/2018-2/DB/news-3.txt" 
      INTO TABLE jskim.news
      FIELDS TERMINATED BY "\t" ;
      /* LINES TERMINATED BY "EOL\n";*/
     SELECT journal FROM jskim.news LIMIT 10;
  3. 테이블 입력 자료의 조회

    SELECT journal FROM jskim.news LIMIT 10;
    SELECT title   FROM jskim.news LIMIT 10;
    SELECT journal, pub_date, reporter FROM jskim.news LIMIT 5;
    SELECT SUBSTRING(contents, 1, 46) as cont FROM jskim.news LIMIT 10;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +---------+
    ## | journal |
    ## +---------+
    ## | 경향    |
    ## | 경향    |
    ## | 경향    |
    ## | 경향    |
    ## | 경향    |
    ## | 경향    |
    ## | 경향    |
    ## | 경향    |
    ## | 경향    |
    ## | 경향    |
    ## +---------+
    ## +---------------------------------------------------------------------------------------------------+
    ## | title                                                                                             |
    ## +---------------------------------------------------------------------------------------------------+
    ## | 최은영 '가정주부로 집에만 있다 나와서 전문성 부족' 변명                                           |
    ## | 대통령 추석 선물이 뭐기에…청·야 의원 ‘신경전’                                                     |
    ## | ‘철이와 미애’ 신철, 라디오 작가와 10월 결혼                                                       |
    ## | 야구해설가 하일성, 사무실서 숨진 채 발견 '사기 혐의 억울해'                                       |
    ## | 송윤아 SNS 통해 심경고백, 대중의 악녀? '해선 안될 일 한 적 없어'                                  |
    ## | 100일 아들 학대해 혼수상태 빠뜨린 20대 아빠 구속                                                  |
    ## | 바나나맛 초코파이, 6개월 만에 1억개 판매                                                          |
    ## | 올림픽 유도 금메달리스트, '30년산 발렌타인' 양주 선물 주고 '견책' 처분                            |
    ## | 일본에 '땅콩회항'이 발생하지 않는 이유                                                            |
    ## | 정용진 '스타필드 하남은 어머니에게서 영감'                                                        |
    ## +---------------------------------------------------------------------------------------------------+
    ## +---------+------------+--------------------+
    ## | journal | pub_date   | reporter           |
    ## +---------+------------+--------------------+
    ## | 경향    | 2016-09-09 | 선명수             |
    ## | 경향    | 2016-09-08 | 조미덥             |
    ## | 경향    | 2016-09-09 | 강주일             |
    ## | 경향    | 2016-09-08 | 온라인뉴스팀       |
    ## | 경향    | 2016-09-08 | 온라인뉴스팀       |
    ## +---------+------------+--------------------+
    ## +-----------------------------------------------------------------------------------------------------------------------+
    ## | cont                                                                                                                  |
    ## +-----------------------------------------------------------------------------------------------------------------------+
    ## | 조선·해운산업 구조조정 청문회 마지막날인 9일은 증인으로 출석한 최은영 전 한진해운                                     |
    ## | 박근혜 대통령의 추석 선물을 두고 청와대와 야당 의원들 간에 신경전이 벌어졌다. 더                                      |
    ## |  혼성듀오 ‘철이와 미애’로 유명한 가수 겸 음반제작자 신철(48)이 방송작가 ㄱ씨                                          |
    ## |  야구해설가 하일성이 숨진 채 발견됐다. 하일성 씨는 8일 오전 7시56분께 서울                                            |
    ## | 배우 송윤아가 자신의 악플과 관련해 솔직한 심경을 밝혔다. 송윤아는 배우 설경구와                                       |
    ## | 생후 100일 된 아들을 학대해 혼수상태에 빠뜨린 20대 친아버지가 구속됐다. 광주                                          |
    ## | 오리온은 ‘초코파이 정(情) 바나나’가 출시 6개월 만에 낱개 기준으로 누적 판매량                                         |
    ## | 올림픽 유도 금메달리스트 출신 한국마사회 유도단 감독이 마사회 부회장에게 28만원                                       |
    ## | 9일 조선·해운 청문회에 출석한 최은영 전 한진해운 회장은 경영수업을 제대로 받았냐                                      |
    ## | 신세계그룹의 쇼핑테마파크 ‘스타필드 하남’이 정식 개장했다. 정용진 부회장(48·사                                        |
    ## +-----------------------------------------------------------------------------------------------------------------------+

3.7 실습2 - customer

  1. 테이블 생성

    CREATE TABLE customer(
      no INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
      name CHAR(20) NOT NULL,
      age TINYINT,
      phone VARCHAR(20),
      email VARCHAR(30) NOT NULL,
      address VARCHAR(50)
    ) 
    DEFAULT CHARACTER SET utf8 /* 한글입력 */
    DEFAULT COLLATE utf8_general_ci;
  2. 테이블 삭제

    DROP TABLE customer; 
    SHOW TABLES; /*테이블 리스트 */
  3. 자료 입력(INSERT INTO)

    INSERT INTO customer (name, age, email) VALUES
    ("jskim", 40, "jskim@gmail.com"); 
    
    INSERT INTO customer (name, age, email) VALUES
    ("김진석", 40, "jskim@gmail.com"); 
    
    SELECT * FROM customer;
  4. 자료 삭제(DELETE FROM)

    DELETE FROM  customer WHERE name = "jskim";
    
    SELECT * FROM customer;

3.8 실습 3 - R iris data

 head(iris, 2)
 write.table(iris, file="/DB/iris.csv", sep=",",
           col.names = FALSE, 
           row.names = FALSE, quote=FALSE)
  1. iris 테이블 생성

    CREATE TABLE test_db.iris(
    Sepal_Length DOUBLE,
    Sepal_Width  DOUBLE,
    Petal_Length DOUBLE,
    Petal_Width DOUBLE,
    Species VARCHAR(20),
    );
  2. 자료입력

    LOAD DATA LOCAL INFILE "C:/tmp/iris.csv" INTO TABLE test_db.iris
    FIELDS TERMINATED BY ",";

3.9 테이블의 속성변경(ALTER)

  • 컬럼추가

      ALTER TABLE [table_name] ADD [col_name] [column_type];
    • student 테이블에 age컬럼 추가, 속성을 int

        ALTER TABLE student ADD age int;
    • 결과 age컬럼값들은 모두 null값
  • 컬럼 제거

      ALTER TABLE [table_name] DROP [col_name];
    • student 테이블에서 idnum컬럼 제거

        ALTER TABLE student DROP idnum;
    • (주의) 이 명령은 모든 데이터베이스에서 지원하지 않음

  • 칼럼명을 변경

    alter table [table_name] change
        [old_name] [new_name] [column_type];
    alter table address change name firstname varchar(15);

3.10 테이블 데이터의 수정(UPDATE 문)

UPDATE jskim.pet SET birth="1989-08-31" WHERE name="Bowser";

4 테이블로부터 자료 검색

4.1 기본 검색

  • SELECT 일반 형식
    • 검색하려는 컬럼들을 쉼표(,)로 분리
    • 모든 열을 검색 *
    • WHERE 검색조건 (condition)
    SELECT [target] FROM [tbl_name] WHERE [condition];
  1. 모든 데이터 검색

    SELECT * FROM jskim.pet;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +----------+--------+---------+------+------------+------------+
    ## | name     | owner  | species | sex  | birth      | death      |
    ## +----------+--------+---------+------+------------+------------+
    ## | Bowser   | Dianne | dog     | m    | 1998-08-31 | 1995-07-29 |
    ## | Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
    ## | Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
    ## | Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
    ## | Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
    ## | Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
    ## | Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
    ## | Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
    ## +----------+--------+---------+------+------------+------------+
  2. 특정 열의 선택
    • 관심있는 열(column)들만 검색: 열이름을 쉼표로 구분
      • name, birth열 검색
    SELECT name, birth FROM jskim.pet;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +----------+------------+
    ## | name     | birth      |
    ## +----------+------------+
    ## | Bowser   | 1998-08-31 |
    ## | Buffy    | 1989-05-13 |
    ## | Chirpy   | 1998-09-11 |
    ## | Claws    | 1994-03-17 |
    ## | Fang     | 1990-08-27 |
    ## | Fluffy   | 1993-02-04 |
    ## | Slim     | 1996-04-29 |
    ## | Whistler | 1997-12-09 |
    ## +----------+------------+
  3. 중복된 행을 없애기 (distinct 이용)

    USE jskim;
    SELECT distinct owner FROM pet;

4.2 조건 검색 (WHERE)

  1. jskim.pet 테이블에서 name이 ’Bower’인 레코드만 검색

    SELECT * FROM jskim.pet WHERE name = "Bowser";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +--------+--------+---------+------+------------+------------+
    ## | name   | owner  | species | sex  | birth      | death      |
    ## +--------+--------+---------+------+------------+------------+
    ## | Bowser | Dianne | dog     | m    | 1998-08-31 | 1995-07-29 |
    ## +--------+--------+---------+------+------------+------------+
  2. 1998년 이후에 태어난 동물 검색

    SELECT * FROM jskim.pet WHERE birth >= "1998-1-1"
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +--------+--------+---------+------+------------+------------+
    ## | name   | owner  | species | sex  | birth      | death      |
    ## +--------+--------+---------+------+------------+------------+
    ## | Bowser | Dianne | dog     | m    | 1998-08-31 | 1995-07-29 |
    ## | Chirpy | Gwen   | bird    | f    | 1998-09-11 | NULL       |
    ## +--------+--------+---------+------+------------+------------+
  3. AND: (species=‘dog’)이면서 (sex=‘f’)인 동물 검색

    SELECT * FROM jskim.pet WHERE species = "dog" AND sex = "f";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +-------+--------+---------+------+------------+-------+
    ## | name  | owner  | species | sex  | birth      | death |
    ## +-------+--------+---------+------+------------+-------+
    ## | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
    ## +-------+--------+---------+------+------------+-------+
  4. OR : species=‘snake’)이거나 (species=‘bird’)인 동물 검색

    SELECT * FROM jskim.pet WHERE species = "snake" OR species = "bird";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +----------+-------+---------+------+------------+-------+
    ## | name     | owner | species | sex  | birth      | death |
    ## +----------+-------+---------+------+------------+-------+
    ## | Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
    ## | Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
    ## | Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
    ## +----------+-------+---------+------+------------+-------+
  5. AND/OR 조합

    SELECT * FROM jskim.pet WHERE
    (species = "cat" AND sex = "m") OR (species = "dog" AND sex = "f");
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +-------+--------+---------+------+------------+-------+
    ## | name  | owner  | species | sex  | birth      | death |
    ## +-------+--------+---------+------+------------+-------+
    ## | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
    ## | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
    ## +-------+--------+---------+------+------------+-------+

4.3 특정 행과 열의 조합

  • (WHERE 절을 이용) + (열이름을 지정)
    • dog와 cat에 대해서만 name, species, birth을 검색

      SELECT name, species, birth FROM jskim.pet
          WHERE species = "dog" OR species = "cat";
      ## mysql: [Warning] Using a password on the command line interface can be insecure.
      ## +--------+---------+------------+
      ## | name   | species | birth      |
      ## +--------+---------+------------+
      ## | Bowser | dog     | 1998-08-31 |
      ## | Buffy  | dog     | 1989-05-13 |
      ## | Claws  | cat     | 1994-03-17 |
      ## | Fang   | dog     | 1990-08-27 |
      ## | Fluffy | cat     | 1993-02-04 |
      ## +--------+---------+------------+

4.4 행 정렬(ORDER BY)

  • birth을 기준 정렬

    SELECT name, birth FROM jskim.pet ORDER BY birth;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +----------+------------+
    ## | name     | birth      |
    ## +----------+------------+
    ## | Buffy    | 1989-05-13 |
    ## | Fang     | 1990-08-27 |
    ## | Fluffy   | 1993-02-04 |
    ## | Claws    | 1994-03-17 |
    ## | Slim     | 1996-04-29 |
    ## | Whistler | 1997-12-09 |
    ## | Bowser   | 1998-08-31 |
    ## | Chirpy   | 1998-09-11 |
    ## +----------+------------+
  • 역순으로 정렬: desc 키워드를 열 이름 뒤에

    SELECT name, birth FROM jskim.pet ORDER BY birth desc;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +----------+------------+
    ## | name     | birth      |
    ## +----------+------------+
    ## | Chirpy   | 1998-09-11 |
    ## | Bowser   | 1998-08-31 |
    ## | Whistler | 1997-12-09 |
    ## | Slim     | 1996-04-29 |
    ## | Claws    | 1994-03-17 |
    ## | Fluffy   | 1993-02-04 |
    ## | Fang     | 1990-08-27 |
    ## | Buffy    | 1989-05-13 |
    ## +----------+------------+

4.5 여러 열에 대해 정렬

  • name으로 정렬하고, 생일(birth) 역순 정렬

    SELECT name, species, birth  FROM jskim.pet ORDER BY species, birth DESC;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +----------+---------+------------+
    ## | name     | species | birth      |
    ## +----------+---------+------------+
    ## | Chirpy   | bird    | 1998-09-11 |
    ## | Whistler | bird    | 1997-12-09 |
    ## | Claws    | cat     | 1994-03-17 |
    ## | Fluffy   | cat     | 1993-02-04 |
    ## | Bowser   | dog     | 1998-08-31 |
    ## | Fang     | dog     | 1990-08-27 |
    ## | Buffy    | dog     | 1989-05-13 |
    ## | Slim     | snake   | 1996-04-29 |
    ## +----------+---------+------------+

5 패턴 검색

5.1 패턴일치 기능(LIKE/REGEXP)을 이용한 검색

  • SQL에서 패턴일치기능은 보다 빠르고 정교하게 원하는 조건에 대한 매우 유용한 검색방법임
  • LIKE 혹은 REGEXP(정규 표현식)를 이용

5.2 LIKE를 이용한 패턴 검색

  • LIKE구문에서 패턴표현 기호
    • _ : 임의의 한 문자
    • % 는 임의의 수의 문자(0개의 문자를 포함) (예) b% : `b’로 시작하는 패턴
    • LIKE를 이용한 패턴에서 대소문자는 구분하지 않음

5.3 LIKE를 이용한 패턴 검색의 예

  1. 정확하게 5개의 글자로 이루어진 이름(name)에 대한 검색: 밑줄(_) 5개를 사용하여 검색조건을 나타낸다.

    SELECT * FROM jskim.pet WHERE name LIKE "_____";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +-------+--------+---------+------+------------+-------+
    ## | name  | owner  | species | sex  | birth      | death |
    ## +-------+--------+---------+------+------------+-------+
    ## | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
    ## | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
    ## +-------+--------+---------+------+------------+-------+
  2. 이름(name)이 b로 시작하는 패턴 검색

    SELECT * FROM jskim.pet WHERE name LIKE "b%";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +--------+--------+---------+------+------------+------------+
    ## | name   | owner  | species | sex  | birth      | death      |
    ## +--------+--------+---------+------+------------+------------+
    ## | Bowser | Dianne | dog     | m    | 1998-08-31 | 1995-07-29 |
    ## | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
    ## +--------+--------+---------+------+------------+------------+
  3. 이름(name)이 fy로 끝나는 패턴 검색

    SELECT * FROM jskim.pet WHERE name LIKE "%fy";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +--------+--------+---------+------+------------+-------+
    ## | name   | owner  | species | sex  | birth      | death |
    ## +--------+--------+---------+------+------------+-------+
    ## | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
    ## | Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
    ## +--------+--------+---------+------+------------+-------+
  4. 이름(name)에 w가 포함된 패턴에 대한 검색

    SELECT * FROM jskim.pet WHERE name LIKE "%w%";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +----------+--------+---------+------+------------+------------+
    ## | name     | owner  | species | sex  | birth      | death      |
    ## +----------+--------+---------+------+------------+------------+
    ## | Bowser   | Dianne | dog     | m    | 1998-08-31 | 1995-07-29 |
    ## | Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
    ## | Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
    ## +----------+--------+---------+------+------------+------------+

5.4 정규표현에 의한 패턴검색(REGEXP)

  • 정규표현(regular expression)에 기반한 패턴일치 검색 - REGEXP을 사용
  • 정규표현식에 사용되는 문자 및 용도
    1. . : 문자 하나
    2. *: 앞에 나온 문자의 0개 이상의 반복
    3. ^ : 문자열 처음
    4. $ : 문자열 끝
    5. [ ] : 괄호안의 문자들 중 하나이상과 일치
    6. { } : 반복연산자, 예를들어 5번 반복할 때 {5}으로 적는다.
  • LIKE vs REGEXP

    #ffy가 포함된 것
    SELECT * FROM jskim.pet WHERE name REGEXP "ffy";
    #반드시 ffy 인 것
    SELECT * FROM jskim.pet WHERE name LIKE "ffy";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +--------+--------+---------+------+------------+-------+
    ## | name   | owner  | species | sex  | birth      | death |
    ## +--------+--------+---------+------+------------+-------+
    ## | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
    ## | Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
    ## +--------+--------+---------+------+------------+-------+
  • 정규표현 예시
    • [abc] : 문자 abc중 하나를 가르키는 표현
    • [a-zA-Z] : 알파벳 문자를 하나라도 포함하는 표현 (정규표현은 대소문자를 구별)
    • x* : x, xx, xxx ... 를 나타낸다.
    • [0-9]* : 7, 12, 345, 678등 임의의 길이를 갖는 수
    • ^abc : 처음에 abc로 시작하는 패턴
    • abc$ : abc로 끝나는 문자열 패턴

5.5 정규표현에 의한 패턴검색 예

  • 이름(name)이 b 혹은 B로 시작하는 조건검색

    SELECT * FROM jskim.pet WHERE name REGEXP "^[bB]";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +--------+--------+---------+------+------------+------------+
    ## | name   | owner  | species | sex  | birth      | death      |
    ## +--------+--------+---------+------+------------+------------+
    ## | Bowser | Dianne | dog     | m    | 1998-08-31 | 1995-07-29 |
    ## | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
    ## +--------+--------+---------+------+------------+------------+

5.6 정규표현에 의한 패턴 일치(REGEXP)

  1. fy로 끝나는 이름검색 ($를 사용)

    SELECT * FROM jskim.pet WHERE name REGEXP "fy$";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +--------+--------+---------+------+------------+-------+
    ## | name   | owner  | species | sex  | birth      | death |
    ## +--------+--------+---------+------+------------+-------+
    ## | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
    ## | Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
    ## +--------+--------+---------+------+------------+-------+
  2. 정확하게 5개의 문자로 이름어진 값 검색

    #//정규 표현
    SELECT * FROM jskim.pet WHERE name REGEXP  "^.....$";
    #//정규표현(반복 연산자)
    SELECT * FROM jskim.pet WHERE name REGEXP  "^.{5}$";
    #//LIKE 표현
    SELECT * FROM jskim.pet WHERE name LIKE  "_____";
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +-------+--------+---------+------+------------+-------+
    ## | name  | owner  | species | sex  | birth      | death |
    ## +-------+--------+---------+------+------------+-------+
    ## | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
    ## | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
    ## +-------+--------+---------+------+------------+-------+
    ## +-------+--------+---------+------+------------+-------+
    ## | name  | owner  | species | sex  | birth      | death |
    ## +-------+--------+---------+------+------------+-------+
    ## | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
    ## | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
    ## +-------+--------+---------+------+------------+-------+
    ## +-------+--------+---------+------+------------+-------+
    ## | name  | owner  | species | sex  | birth      | death |
    ## +-------+--------+---------+------+------------+-------+
    ## | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
    ## | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
    ## +-------+--------+---------+------+------------+-------+

6 함수의 이용

6.1 집단함수를 이용한 요약정보의 검색

집단함수 의미
COUNT 행의 개수
SUM NULL을 제외한 모든 행의 합계
AVG NULL을 제외한 모든 행의 평균값
MAX NULL을 제외한 모든 행의 최대값
MIN NULL을 제외한 모든 행의 최소값

6.2 집단함수의 예

  1. 레코드의 수 : COUNT()함수 사용

    SELECT COUNT(*) FROM jskim.pet;
  2. 각 소유주가 소유한 애완동물의 수

    SELECT owner, COUNT(*) FROM jskim.pet GROUP BY owner;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +--------+----------+
    ## | owner  | COUNT(*) |
    ## +--------+----------+
    ## | Benny  |        2 |
    ## | Dianne |        1 |
    ## | Gwen   |        3 |
    ## | Harold |        2 |
    ## +--------+----------+

6.3 COUNT()와 GROUP BY

  • 각 종에 해당하는 동물의 수

    SELECT species, COUNT(*) FROM jskim.pet GROUP BY species;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +---------+----------+
    ## | species | COUNT(*) |
    ## +---------+----------+
    ## | bird    |        2 |
    ## | cat     |        2 |
    ## | dog     |        3 |
    ## | snake   |        1 |
    ## +---------+----------+
  • 성에 따른 동물의 수

    SELECT sex, COUNT(*) FROM jskim.pet GROUP BY sex;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +------+----------+
    ## | sex  | COUNT(*) |
    ## +------+----------+
    ## | NULL |        1 |
    ## | f    |        3 |
    ## | m    |        4 |
    ## +------+----------+
  • 종과 성에 따른 동물의 수

    SELECT species, sex, COUNT(*) FROM jskim.pet
          GROUP BY species, sex;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +---------+------+----------+
    ## | species | sex  | COUNT(*) |
    ## +---------+------+----------+
    ## | bird    | NULL |        1 |
    ## | bird    | f    |        1 |
    ## | cat     | f    |        1 |
    ## | cat     | m    |        1 |
    ## | dog     | f    |        1 |
    ## | dog     | m    |        2 |
    ## | snake   | m    |        1 |
    ## +---------+------+----------+
  • 개와 고양이의 경우에만 각 성별 마리 수

    SELECT species, sex, COUNT(*) FROM jskim.pet
        WHERE species = "dog" OR  species = "cat"
        GROUP BY species, sex;
    ## mysql: [Warning] Using a password on the command line interface can be insecure.
    ## +---------+------+----------+
    ## | species | sex  | COUNT(*) |
    ## +---------+------+----------+
    ## | cat     | f    |        1 |
    ## | cat     | m    |        1 |
    ## | dog     | f    |        1 |
    ## | dog     | m    |        2 |
    ## +---------+------+----------+

6.4 AS를 이용한 컬럼 별명 정의

  • 컬럼명을 다른 이름으로 변경하여 출력

    SELECT COUNT(*) AS N_STUDENT FROM STUDENT;

6.5 GROUP BY + HAVING

  • HAVING 절은 GROUP BY절과 같이 사용되며, GROUP BY절에 명시된 열에 대한 제약조건

    SELECT ID_LEC COUNT(*) AS N_REG,
      AVG(FINAL) AS MEAN_FINAL
      FROM LECTURES
      GROUP BY ID_LEC
      HAVING COUNT(*) >= 5;

6.6 기타 함수들

  1. 난수발생 RAND()

    SELECT id,name FROM STUDENT ORDER BY RAND() LIMIT 5;
    +----+--------+
    | id | name   |
    +----+--------+
    |  3 | 이수만 |
    |  2 | 임성훈 |
    |  6 | 박수홍 |
    | 12 | 정윤호 |
    | 13 | 권보아 |
    +----+--------+
  2. 문자열 결합 CONCAT()

    SELECT id, CONCAT(name,"(",alias,")") AS "이름(별명)" FROM STUDENT;
    +----+---------------------+
    | id | 이름(별명)          |
    +----+---------------------+
    |  1 | 조용필(작은거인)    |
    |  2 | 임성훈()            |
    |  3 | 이수만()            |
    |  4 | 임예진()            |
    ....
    |  9 | 한채영(바비인형)    |
    | 10 | 박수애(리틀 정윤희) |
    | 11 | 허영란(까치)        |
    | 12 | 정윤호(꼬비)        |
    | 13 | 권보아()            |
    | 14 | 문근영(국민 여동생) |
    +----+---------------------+