Lecture 04, SQL (1)

SQL(Structured Query Language)

• 관계형 데이터베이스 표준 언어로서 가장 많이 사용되는 데이터 언어
  • System R 이라는 실험용 DBMS를 위한 데이터 언어로 IBM 연구소에서 처음 개발
  • 현재는 미국 표준(ANSI)과 국제 표준(ISO) 관계형 데이터베이스 표준 언어로 승인
• ORACLE, MS SQL-Server, MySQL 등 거의 모든 관계형 DBMS가 지원
• SQL을 구성하는 3가지 부속 언어의 분류와 관련 주요 기능
• 현재의 주요 특성과 문법은 1990년대에 거의 완성
• SQL의 기능별 분류
  • 데이터 정의어(Data Definition Language)
    • DB 구조 정의
    • DB 객체 생성, 수정, 삭제 (테이블, 뷰, 인덱스 등)
  • 데이터 조작어(Data Manipulation Language)
    • DB 데이터 관리
    • 입력, 수정, 삭제, 검색
  • 데이터 제어어(Data Control Language)
    • DB 관리 및 통제
    • DB 백업, 복원
    • 사용자 등록, 권한 관리

MySQL

• 세계에서 가장 많이 사용하는 오픈소스 RDBMS, 1995년에 최초 개발
• 처리 속도가 빠르고 설치가 쉬움
• 오라클, MS-SQL Server와 함께 가장 많이 사용하는 DBMS 중 하나
• 2010년에 오라클에 인수되었지만 현재도 오픈소스 정책은 유지
• 기존 개발 인력들이 새롭게 많든 것이 MariaDB
• 장점
  • 리눅스, Mac, Windows 등 다양한 플랫폼 사용 가능
  • 프로그램이 가벼움
• 단점
  • 사용이 다른 상용 제품에 비해 불편함
• MySQL 서버 구동
• 클라이언트 관리 도구인 MySQL 워크벤치를 실행하거나 콘솔창을 사용함
• 대부분의 경우 Super User인 Root 계정을 사용하게 됨
• 실행 예시
  • Select version();
  • Select user()

Drop database if exists univDB;  # 해당 db가 존재한다면 삭제
Create database if not exists univDB;  # 해당 db가 존재하지 않는다면 만들기

Create table 학생(
학번 char(4) not null,  # null값은 안됨, 문자열 4byte 
이름 varchar(20) not null,  # null값은 안됨, 문자열 최대 20byte
주소 varchar(50) null default '미정',  # null값 가능, 문자열 최대 50byte default 값은 '미정'
학년 int not null,  # null값 불가능, 정수형
나이 int null,  # null값은 가능, 정수형
성별 char(1) not null,  # null값은 불가능, 문자열 1byte
휴대폰번호 char(14) null,  # null값 가능, 문자열 14byte
소속학과 varchar(20) null,  # null값 가능, 문자열 최대 20byte
Primary key (학번));  # pk는 학번으로 설정

Create table 과목(
과목번호 char(4) not null primary key,  # null값 불가, 문자열 4byte, pk 설정
이름 varchar(20) not null,  # null값 불가, 문자열 최대 20byte
강의실 char(3) not null,  # null값 불가, 문자열 3byte
개설학과 varchar(20) not null,  # null 값 불가, 문자열 최대 20byte
시수 int not null);  # null 값 불가, 정수형

Create table 수강(
학번 char(6) not null,  # null값 불가, 문자열 6byte
과목번호 char(4) not null,  # null값 불가, 문자열 4byte
신청날짜 date not null,  # null값 불가, 날짜형
중간성적 int null default 0,  # null값 가능, 정수형, default 값은 0
기말성적 int null default 0,  # null값 가능, 정수형, default 값은 0
평가학점 char(1) null,  # null값 가능, 문자형 1byte
Primary key(학번, 과목번호));  # pk는 학번과 과목번호로 설정

INSERT INTO 학생 VALUES('S001', '김연아', '서울 서초', 4, 23, '여', '010-1111-2222', '컴퓨터');
...
INSERT INTO 과목 VALUES('c001', '데이터베이스', '126', '컴퓨터');
...
INSERT INTO 수강 VALUES('s001', 'c002', '2019-09-03', 93, 98, A);
...

• 작업데이터베이스 선택

USE univDB; # 작업대상 데이터베이스 변경 
SELECT database(); # 현재 사용 데이터베이스 확인 
SHOW TABLES; # univDB 안의 생성 테이블 목록 확인

• SQL 데이터 조작문 기초
  • 행 검색
    • 테이블로부터 데이터를 검색하기 위해서는 SELECT 문을 사용

SELECT DISTINCT 소속학과 FROM 학생;
SELECT 이름, 주소 FROM 학생;

SELECT 학번, 이름, 주소, 학년, 나이, 성별, 휴대폰번호, 소속학과 
FROM 학생; 
SELECT * FROM 학생;

SELECT [DISTINCT/ALL] COL_LIST # 중복제거 or 그냥 다 # 5 
FROM TABLE_LIST # 1 
[WHERE SEARCH_CONDITION] # 생략 가능, 어디서 가져올 것인가? # 2 
[GROUP BY GROUP_STDCOL_LIST] # 생략 가능, 그룹화 할 기준 열 # 3 
[HAVING GROUP_CONDITION] # 생략 가능, 조건식이 들어감 # 4 
[ORDER BY {SORT_STDCOL [ASC/DESC][,]}^+]; # 6 # 정렬 기준열, 오름차순/내림차순 설정 +는 1회 이상 반복 * 0회 이상 반복

• WHERE절은 참/거짓을 판별할 수 있는 하나의 검색 조건을 define
  • 기본 검색 조건식은 보통 열이름, 비교연산자, 상수값의 순서로 정의
  • 비교연산자
    • =, <>(같지 않다), <, >, ≤, ≥
  • 문자나 날짜값은 작은 따옴표로 구분
  • 논리연산자 and, or, not
  • 비교연산자가 최우선으로 사용되며 not, and, or 순으로 사용

      SELECT 이름, 학년, 소속학과, 휴대폰번호 
      FROM 학생 
      WHERE (학년 BETWEEN 1 AND 3) OR NOT (소속학과='컴퓨터');

SELECT 이름, 학년, 소속학과, 휴대폰번호 
FROM 학생 
WHERE (학년>=1 AND 학년<=3) OR NOT (소속학과='컴퓨터');

SELECT 이름, 학년, 소속학과, 휴대폰번호
FROM 학생 WHERE 학년>=2 AND 소속학과='컴퓨터';

• ORDER BY 절은 정렬 기준이 되는 열을 지정
  • 여러개의 열을 나열하여 정의할 수 있음
  • ASC: 오름차순 / DESC: 내림차순

  SELECT 이름, 학년, 소속학과, 휴대폰번호 
  FROM 학생 
  WHERE 소속학과='컴퓨터' OR 소속학과='정보통신' 
  ORDER BY 학년 ASC, 이름 DESC;

SELECT * 
FROM 학생 
ORDER BY 학년 ASC, 이름 DESC;

SELECT * 
FROM 학생 
ORDER BY 학년 ASC, 이름 DESC 
LIMIT 3; # SELECT문 맨 마지막에 넣는 것이 원칙, LIMIT 5, 3은 6번째 행부터 3개

• GROUP BY
  • 개별 행이 아닌 행 그룹에 대한 검색을 위해 사용
  • 뒤에는 그룹을 분류하는 기준 열을 지정
  • FROM 절에 있는 TABLE을 GROUP BY 기준으로 나눔
  • GROUP BY 절에서 그룹화한 기준열은 SELECT 절에도 define되어야 결과의 의미를 구별할 수 있음

    SELECT 성별, MAX(나이) AS '최고 나이', MIN(나이) AS '최저 나이' 
    FROM 학생 
    GROUP BY 성별;

SELECT 나이, COUNT(*) AS '나이별 학생수' 
FROM 학생 
WHERE 나이>=20 AND 나이<30 
GROUP BY 나이;

• HAVING
  • GROUP BY절을 통해 생성된 그룹 중 특정 조건을 만족하는 그룹만으로 검색을 제한함
  • WHERE 절은 행에 대한 ‘검색_조건식’을 define
  • HAVING 절은 행 그룹에 대한 ‘그룹_조건식’을 define

SELECT 학년, COUNT(*) AS '학년별 학생수' 
FROM 학생 
GROUP BY 학년 
HAVING COUNT(*) >= 2;

• 집계함수
  • 실제 테이블 저장 값이 아닌 행의 개수 또는 특정 열의 값의 평균을 구하는 질의가 필요한 경우를 위해 사용
  • 각 열에 대한 기본 통계 결과를 반환함
  • COUNT(COL): 특정 열 값의 개수 또는 행의 개수
  • MAX(COL): 특정 열 값 중 최대값
  • MIN(COL): 특정 열 값 중 최소값
  • SUM(COL): 특정 열 값의 합계
  • AVG(COL): 특정 열 값의 평균

    SELECT COUNT(*) 
    FROM 학생;

    SELECT COUNT(학번) 
    FROM 학생;

    SELECT COUNT(*) AS 학생수1, COUNT(주소) AS 학생수2, COUNT(DISTINCT 주소) AS 학생수3 
    FROM 학생; # AS를 이용하면 집계함수를 이용한 새로운 테이블이 만들어질 때 각 COLUMN의 이름을 지어줄 수 있음

    SELECT AVG(나이) '여학생 평균나이' 
    FROM 학생 
    WHERE 성별='여';

• LIKE 문자 연산자
  • 부분 문자열(substring) 포함 여부를 검색하기 위해 WHERE절 ‘검색_조건식’에 사용되는 비교 연산자
  • 검색 문자열 값을 정확히 모르거나 일부만 알고있는 경우
  • LIKE를 사용하기 위해서는 와일드카드를 사용해야 함

    _	문자열 중 특정 위치에 1개의 모든 문자를 허용
    %	문자열 중 임의 위치에 0개 이상의 모든 문자(열)를 허용

  • 예시

    	검색 일치	검색 불일치
    LIKE ‘_ook’	book, look	good, books, ook
    LIKE ‘9_7_5’	98765, 91715, 9a7b5	975, 98754
    LIKE ‘%k’	book, milk, ok, k	books
    LIKE ‘b%k’	book, bblack, bk	kbkbk, bbooks

        SELECT 학번, 이름, 주소
        FROM 학생
        WHERE 주소 LIKE '%서울%'
        ORDER BY 학년 DESC;

            SELECT 학번, 이름 
            FROM 학생 
            WHERE 이름 
            LIKE '이__';

• null값 검사하기
  • = 비교 연산자 사용 불가
  • IS NULL 과 IS NOT NULL만 사용 가능함
  • SELECT 이름, 휴대폰번호 FROM 학생 WHERE 휴대폰번호 IS NULL;

• UNION 연산자
  • 여러 SELECT 명령문의 검색 결과를 결합
  • 결과 테이블에서 자동으로 중복 행을 제거한 나머지 행들을 반환
  • MySQL에서 INTERSECT와 EXCEPT 또는 MINUS 연산자를 지원하지 않음

  SELECT 학번 
  FROM 학생 
  WHERE 성별='여' 
  UNION SELECT 학번 
          FROM 수강 
          WHERE 평가학점='A';

• IN 연산자
  • 나열된 값들 중 하나라도 일치하는 경우 참을 반환
  • NOT IN은 나열된 값들 중 어떤 값도 일치하지 않을 경우 참 반환
  • 여러 값들과의 반복 비교를 위해 = 기호를 나열할 필요 없이 한꺼번에 표현하도록 함

    SELECT 이름 
    FROM 학생 
    WHERE 학번 IN ('s001', 's003', 's004'); 

    SELECT 이름 
    FROM 학생 
    WHERE 학번='s001' or 학번='s003' or 학번='s004';```

• JOIN
  • WHERE 절에서 복합적으로 조건식을 구성
  • JOIN 명을 통해 테이블을 결합한 후 조건식을 구성
  • FROM 절에 명세한 조인 대상 테이블 개수 N-1개의 조인 조건식이 필요함

SELECT 열_리스트 
FROM 조인테이블_리스트 
WHERE 조인조건식
AND 검색조건식 
SELECT 열_리스트 
FROM 테이블이름1{[INNER] JOIN 테이블이름2 ON 조인조건식}+ WHERE 검색 조건

• CROSS JOIN
  • 조인조건식 없이 이루어진 조인
  • 관계대수의 카티션 프로덕트 연산을 적용한 결과(두 테이블을 곱한 결과)를 반환
  • 대부분의 행이 의미 없는 기계적인 결합

SELECT * 
FROM 학생, 수강; 

SELECT * 
FROM 학생 CROSS JOIN 수강;

• EQUI JOIN
  • 조인조건식에 = 연산자를 사용하는 동등 조건에 의한 조인
  • 두 테이블 행들 사이의 의미 있는 조합만을 검색
  • 크로스 조인의 결과 테이블 중 의미없는 조합을 제외하고 관계대수의 동등조인 연산을 적용

    SELECT * FROM 학생, 수강 
    WHERE 학생.학번=수강.학번; 

    SELECT * FROM 학생 
    JOIN 수강 ON 학생.학번=수강.학번;

SELECT 학생.학번, 이름, 과목번호, 중간성적+(중간성적*0.1) AS 변환중간성적
FROM 학생, 수강
WHERE 학생.학번=수강.학번 AND 과목번호='c002';

SELECT 학생.학번, 이름, 과목번호, 중간성적+(중간성적*0.1) AS 변환중간성적
FROM 학생 JOIN 수강 ON 학생.학번=수강.학번
WHERE 과목번호='c002';

SELECT 학생.`학번`, 학생.이름, 수강.과목번호
FROM 학생, 수강, 과목
WHERE 학생.학번=수강.학번 AND 수강.과목번호=과목.과목번호 AND 과목.이름='정보보호';

SELECT 학생.`학번`, 학생.이름, 수강.과목번호
FROM (학생 JOIN 수강 ON 학생.학번=수강.학번) JOIN 과목 ON 수강.과목번호=과목.과목번호
WHERE 과목.이름='정보보호';