데이터베이스의 정석 요약 (ch.5 SQL기초)

ch5. SQL 기초

1. SQL 개요
2. SQL 실습 준비

MySQL 실습 준비
 - 'univDB' 실습 예제 데이터베이스 생성
    -- 데이터베이스 생성
    DROP DATABASE IF EXISTS univeDB ;
    CREATE DATABASE IF NOT EXISTS univeDB ;

 

    -- SQL 명령어를 실행할 대상인 기본 데이터베이스를 univeDB로 지정
    USE univeDB ;

    -- 테이블 생성
    CREATE TABLE 과목
 ( 과목번호 char(4) NOT NULL PRIMARY KEY, 
  이름 VARCHAR(20) NOT NULL, 
  강의실 CHAR(3) NOT NULL,
  개설학과 VARCHAR(20) NOT NULL,
  시수 INT NOT NULL ) ; 

   CREATE TABLE 학생
( 학번 CHAR(4) NOT NULL,
  이름 VARCHAR(20) NOT NULL,
  주소 VARCHAR(50) NULL DEFAULT '미정', 
  학년 INT NOT NULL,
  나이 INT NULL,
  성별 CHAR(1) NOT NULL,
  휴대폰번호 CHAR(14) NULL, 
             소속학과 VARCHAR(20) NULL,
  PRIMARY KEY (학번) ) ; 

   CREATE TABLE 수강
( 학번 char(4) NOT NULL,
  과목번호 CHAR(4) NOT NULL,
  신청날짜 DATE NOT NULL,
  중간성적 INT NULL DEFAULT 0,
  기말성적 INT NULL DEFAULT 0, 
  평가학점 CHAR(1) NULL,        
  PRIMARY KEY(학번, 과목번호)  ) ; 
      
  -- 테이블 입력
INSERT INTO 학생 VALUES ('s001', '김연아', '서울 서초', 4, 23, '여', '010-1111-2222', '컴퓨터') ;
INSERT INTO 학생 (학번, 이름, 주소, 학년, 나이, 성별, 휴대폰번호, 소속학과) VALUES ('s002', '홍길동', DEFAULT, 1, 26, '남', NULL , '통계') ;
INSERT INTO 학생 (학년, 나이, 성별, 소속학과, 학번, 이름) VALUES (3, 30, '남', '정보통신', 's003', '이승엽') ;
INSERT INTO 학생 VALUES ('s004', '이영애', '경기 분당', 2, NULL, '여', '010-4444-5555', '정보통신') ;
INSERT INTO 학생 VALUES ('s005', '송윤아', '경기 분당', 4, 23, '여', '010-6666-7777', '컴퓨터') ;
INSERT INTO 학생 VALUES ('s006', '홍길동', '서울 종로', 2, 26, '남', '010-8888-9999', '컴퓨터') ;
INSERT INTO 학생 VALUES ('s007', '이은진', '경기 과천', 1, 23, '여', '010-2222-3333', '경영') ;

INSERT INTO 과목 VALUES ('c001', '데이터베이스', 126, '컴퓨터', 3) ;
INSERT INTO 과목 VALUES ('c002', '정보보호', 137, '정보통신', 3) ;
INSERT INTO 과목 VALUES ('c003', '모바일웹', 128, '컴퓨터', 3) ;
INSERT INTO 과목 VALUES ('c004', '철학개론', 117, '철학', 2) ;
INSERT INTO 과목 VALUES ('c005', '전공글쓰기', 120, '교양학부', 1) ;

INSERT INTO 수강 VALUES ('s001', 'c002', '2019-09-03', 93, 98, 'A') ;
INSERT INTO 수강 VALUES ('s004', 'c005', '2019-03-03', 72, 78, 'C') ;
INSERT INTO 수강 VALUES ('s003', 'c002', '2017-09-06', 85, 82, 'B') ;
INSERT INTO 수강 VALUES ('s002', 'c001', '2018-03-10', 31, 50, 'F') ;
INSERT INTO 수강 VALUES ('s001', 'c004', '2019-03-05', 82, 89, 'B') ;
INSERT INTO 수강 VALUES ('s004', 'c003', '2020-09-03', 91, 94, 'A') ;
INSERT INTO 수강 VALUES ('s001', 'c005', '2020-09-03', 74, 79, 'C') ;
INSERT INTO 수강 VALUES ('s003', 'c001', '2019-03-03', 81, 82, 'B') ;
INSERT INTO 수강 VALUES ('s004', 'c002', '2018-03-05', 92, 95, 'A') ;


 - 작업 데이터베이스 선택
    USE univeDB ;  -- 작업대상 데이터베이스 변경
    SELECT database() ;  -- 현재 사용 데이터베이스 확인
    SHOW TABLES ;  --univeDB 안의 생성 테이블 목록 확인
    DESC 학생 ;    -- 학생 테이블 생성 정보 확인
    ***MySQL은 auto commit 되며, Oracle은 commit 해야 저장됨.

3. SQL 데이터 조작문
 - SELECT 제외한 INSERT, DELETE, UPDATE는 데이터 수정 됨

3.1 데이터 검색 SELECT문
 - 행 검색 : 테이블로부터 데이터를 검색하기 위해서 SELECT문 사용
 - SELECT문의 형식: SELECT절, FROM절
   SELECT [DISTINCT|ALL] 열_리스트  ...(5)
   FROM 테이블_리스트  ...(1)
   [WEHRE 검색_조건식]  ...(2)
   [GROUP BY 그룹_기준열_리스트]  ...(3)
   [HAVING 그룹_조건식]   ...(4)
   [ORDER BY {정열_기준열 [ASC|DESC][.]}+];  ...(6)

 - 테이블의 특정열 검색
   SELECT 이름, 주소 FROM 학생 ;

 - 테이블의 모든 열 검색
   SELECT * FROM 학생 ;

 - 중복 행을 제거하고 검색 : DISTINCT
   SELECT DISTINCT 소속학과 FROM 학생 ;

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

   SELECT 이름, 학년, 소속학과, 휴대폰번호 FROM gkrtod
   WHERE (학년 BETWEEN 1 AND 3) OR NOT(소속학과='컴퓨터') ;
 
 - 순서화 검색 : ORDER BY절 
   SELECT 이름, 학년, 소속학과 FROM 학생
   WHERE 소속학과='컴퓨터' OR 소속학과='정보통신'
   ORDER BY 학년 ASC ;
   -> **ASC는 생략 가능. 기본값 설정되어 있음.

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

**검색 결과 제한 LIMIT (MySQL에만 있고, Oracle에는 없음**)
   상단데이터에서 검색함.
   SELECT* FROM 수강
   ORDER BY 중간성적 DESC
   LIMIT 3 ;   -- 처음3개의 행만 반환

   SELECT* FROM 수강
   ORDER BY 중간성적 DESC
   LIMIT 5, 3 ;   -- 상위5부터 3개의 행만 반환

집계함수 (aggregate function)
 - 집계함수 : 각 열에 대한 기본 통계 결과를 반환하는 함수
 - count(), max(), min(), sum(), avg()
   **group by, having 없는 경우 select 절에 사용 가능.
   **group by, having 있는 경우 having절에 사용해야 함.
     (MySQL, Oracle 동일 적용됨**)
 - 결과 컬럼명이 함수명으로 적용됨. 

 - 결과 컬럼명을 별칭이름 지정할 때 AS 사용함.
    SELECT COUNT(*) AS 학생수1, COUNT(주소) AS 학생수2, COUNT(DISTINCT 주소) AS 학생수2
    FROM 학생 ;
   
    SELECT AVG(나이) '여학생 평균나이' FROM 학생 WHERE 성별='여' ;
    ** AS 옵션 생략 시, 컬럼명 ''로 묶어주어야 함. (Oracle도 동일 방식)
    ** 원래 긴 컬럼명을 AS 옵션으로 약어로 쓸 수 있음.

 - COUNT 함수 사용할때 * 모든 행을 검색하면 NULL값도 포함하여 카운트 된다.
   특정 열을 COUNT 함수로 조회하면 NULL값 배제 된다. (단, 행 전체 값이 NULL이 아닐 경우만)
 - COUNT 함수내 중복제거 가능. 
 - COUNT 제외한 함수는 숫자만 들어가기 때문에 NULL 값 배제된다. 

그룹화 검색 : GROUP BY절
 - GROUP BY 키워드 뒤에 그룹을 분류하는 기준이 되는 열 지정
 - GROUP BY절에서 그룹화 기준으로 명세한 '그룹 기준열'은 SELECT절에도 명세해야 함
 - HAVING절의 '그룹_조건식'은 그룹에 대한 제한 조건을 명세함으로써 질의 결과로 나타나는 그룹을 제한하는 역할을 함
   (*having절이 있으면 where절 사용 불가)

 - GROUP BY절과 SELECT절의 관계
   -> GROUP BY절의 '그룹_기준열_리스트'에 속한 열이나 집계 함수에 사용되는 열, 
       각 그룹마다 같은 값을 갖는 열 이외에는 SELECT절에 사용 할 수 없음. 
       (그룹화 검색을 할 때 SELECT절은 각 그룹별 정보를 검색 대상으로 하기 때문)
 - GROUP BY절과 집계 함수
   ->  WHERE절은 전체 레코드에 대한 검색 조건,  HAVING은 그룹내 검색 조건
   -> WHERE절이 있는 경우, GROUP BY절의 적용 대상은 테이블 전체 행이 아닌
       WHERE절의 조건식을 만족하는 행들만을 대상으로 그룹화되는 점에 유의.
      **가장 많이 오류 나오는 부분. Oracle도 동일함. 
      select 나이, count(*) as '나이별 학생수' from 학생
      where 나이 >=20 and 나이 <30
      group by 나이 ;
 
- 그룹 조건 검색 : HAVING절
    -> GROUP BY절 통해 생성된 그룹 중에서도 특정 조건 만족하는 그룹만으로 검색 제한
      select 학년, count(*) as '학년별 학생수' from 학생
      group by 학년
      having count(*) >=2 ;

- LIKE 문자 연산자 검색: '_', '%' 와일드카드 (**Oracle에서도 동일)
  -> 부분 문자열 포함여부 검색하기 위해 WHERE절 '검색_조건식'에 사용되는
     비교연산자
   -> 검색 문자열 값을 정확히 모르거나 일부만 알고 있는 경우
   -> 기호 - : 문자열 중 특정 위치에 1개의 모든 문자를 허용
       기호 % : 문자열 중 임의 위치에 0개 이상의 모든 문자(열) 허용'
   -> LIKE 와일드카드 적용 예
       LIKE '_ook'
       LIKE '9_7_5'
       LIKE '%k'
       LIKE 'b%k'

     select 학번, 이름 from 학생
     where 이름 like '이__' ;

     select 학번, 이름 from 학생
     where 이름 like '이%' ;

     select 이름, 주소, 학년 from 학생
     where 주소 like '%서울%'
     order by 학년 desc ;

*널(NULL) 값 검색
  - IS NULL
  - IS NOT NULL
    -> NULL은 아직 지정되지 않은 값, 특정 열 값 입력 생략될 경우 입력됨.
       비교 연산자로 비교 불가능. 
    -> NULL값 연산 : NULL이 포함된 산술식 연산 값은 NULL,
        NULL이 포함된 행은 대부분 집계 함수 결과에서 제외,
        NULL을 일반 비교연산자로 비교하면 결과는 항상 FALSE

 

*출처: MySQL과 모바일 웹으로 만나는 데이터베이스의 정석 (배움터)