[DB 3편] SQL 기초

SQL 기초는 총 6개의 내용으로 구성됩니다.

책에는 SQL 실습 내용을 MS-SQL로 진행하지만, 여기서는 mySQL로 진행합니다.

 

1. SQL에 쓴 예제 소개

2. mySQL을 활용한 실습

3. mySQL 소개

4. 데이터 정의어

5. 데이터 조작어 - 검색

6. 데이터 조작어 - 삽입, 수정, 삭제

 

✔️SQL에 쓴 예제 소개

• 마당 서점 예시 활용.
• 사용자: 운영자, 경영자, 응용 프로그래머, 고객
• 테이블
  • Book(bookid, bookname, publisher, price)
  • Customer(custid, name, address, phone)
  • Orders(orderid, bookid, custid, salesprice, orderdate)
    • custid와 bookid는 외래키이다.
    • salesprice는 할인 가격.
    • orderdate는 YYYY-MM-DD 형태이다.
• 사용자별 요구 정보
  • 고객: 도서 이름, 가격, 본인 주문 내역
  • 운영자: 고객 주문 현황, 총액, 도서별 판매량, 남아있는 도서 수
  • 경영자: 운영자 요구 정보 + 고부가가치 정보(월별, 매출, 판매 동향)
  • 프로그래머: 사용자 그룹 별 요구사항 파악, DBMS에 익숙해져야 함, 일반 프로그래밍 언어 + SQL 모두 활용.

✔️mySQL을 활용한 실습

• macOS에 mysql 설치하기

brew install mysql
mysql -V // version check
mysql.server start // mysql 서버 시작 - SUCCESS 떠야 정상작동
mysql.server stop // mysql 서버 내리고 싶을 때 쓰는 명령어

//설정하기
mysql_secure_installation

//비밀번호 재설정하기
mysql.server stop
//root 인증 없이 재시작 가능
mysql.server start --skip-grant-tables

mysql -u root

//임시로 일단 null 비밀번호 변경하기.
update mysql.user set authentcation_string=null where user ="root";
//변경된 권한 재실행
flush privileges;

mysql.server restart
mysql -u root
alter user 'root'@'localhost' identified with cachins_sha2_password by '변경할 비밀번호';

• mysql 사용하기

//mysql database 만들기

//여기에 생성된 데이터 베이스 출력
show databases;

//데이터 베이스를 생성하기
create database 이름;

//해당 데이터베이스 사용하기
use 이름;

//테이블 생성하기
create table Book(
	bookid INT PRIMARY KEY,
    bookname VARCHAR(40),
    publisher VARCHAR(40),
    price INT);
    
create table Customer(
	custid INT PRIMARY KEY NONCLUSTERED,
    name VARCHAR(40),
    address VARCHAR(40),
    phone VARCHAR(30)
    );
create table Orders(
	orderid INT PRIMARY KEY,
    custid INT,
    bookid INT,
    salesprice INT,
    orderdate DATE,
    foreign key (custid) references Customer(custid),
    foreign key (bookid) references Book(bookid),
    );
    
 //table에 데이터 넣기.
 insert into Book values(1, '축구의 역사' '굿스포츠', 7000);

✔️mySQL 소개

• SQL: DB 전용 언어
• DBMS: SQL문을 해석해 프로그램 변환하여 실행 결과를 알려줌.

	SQL	일반프로그래밍 언어
용도	DB에서 데이터 추출	응용 문제 해결
입출력	table/table	모든 형태의 입출력
번역	DBMS 소프트웨어	컴파일러

• 데이터 정의어: 데이터 구조 정의 (CREATE, DROP, ALTER)
• 데이터 조작어: SELECT, INSERT, DELETE, UPDATE
• 데이터 제어어: GRANT, REVOKE, COMMIT, ROLLBACK(트랜잭션 관리)
• SQL은 비절차적 언어, 찾는 데이터에 대해서만 기술함.

✔️데이터 정의어

• 테이블 구조 관련 명령어
• 생성(CREATE), 변경(ALTER), 삭제(DROP)
  
  
• CREATE
  • 테이블 구성, 속성 + 속성 제약 정의.
  • CREATE TABLE 이름 (
    • 속성  데이터 타입 [NOT NULL, UNIQUE, DEFAULT 값, CHECK 조건]
    • PRIMARY KEY (속성)
    • FOREGIN KEY (속성) REFERENCES 테이블(속성) [ON UPDATE [NO ACTION|CASCADE|SET NULL | SET DEFAULT]] [ON DELETE [NO ACTION|CASCADE|SET NULL]] );
• ALTER
  • 테이블 속성, 속성에 대한 제약 변경, PK 나 FK 변경.
  • 속성 추가
    • ALTER TABLE Book ADD isbn VARCHAR(30);
  • 속성 제약 사항 변경
    • ALTER TABLE Book ALTER COLUMN isbn INT;
    • ALTER TABLE Book ALTER COLUMN isbn NOT NULL;
  • 속성 제거
    • ALTER TABLE Book DROP COLUMN isbn;
  • 기본키 설정
    • ALTER TABLE Book ADD PRIMARY KEY(bookid);
• DROP
  • 테이블 삭제
  • 구조와 데이터 모두 삭제
  • 데이터 삭제만 원할 경우, DELETE문 실행.
  • DROP TABLE 테이블 이름;
    • 삭제를 위해서는 해당 테이블을 참조하고 있는 다른 테이블도 삭제.

✔️데이터 조작어 - 검색(SELECT)

• SELECT 문
  • 데이터 검색(aka. 질의어)
    • ex) 10000원 이상인 도서 이름 및 출판사
      • SELECT bookname, publisher FROM Boook WHERE price >= 10000;
    • SELECT의 기본 문법
      • SELECT [ALL|DISTINCT] 속성
      • FROM 테이블
      • WHERE 검색 조건
      • GROUP BY 속성
      • HAVING 검색 조건
      • ORDER BY [ASC|DESC] 속성
• SELECT 문 예제
  • SELECT 문의 끝은 세미콜론으로 끝난다.
  • 속성의 순서가 출력 순이랑 같음.
  • *는 모든 속성을 출력하라.
  • 기본적으로 중복 제거가 되지 않음. DISTINCT 붙여야 중복 제거 가능.
    • SELECT DISTINCT publisher FROM Book;
  • 조건 검색
    • 비교: =, !=, > , < , >=, <=
    • 범위: BETWEEN A and B
      • A와 B 를 포함한 사이의 값
      • ex) SELECT * FROM Book
        • WHERE price >= 10000 and price <= 20000;
        • WHERE price BETWEEN 10000 and 20000;
    • 집합: in, not in, ()
      • ex) SELECT * FROM Book WHERE publisher in ('대한 미디어', '굿 스포츠');
      • 문자열 패턴: LIKE를 사용함. 기본적으로 " 작은 따옴표를 사용하지만, 띄어쓰기가 들어있다면 큰 따옴표를 사용함.
        • 와일드 문자: _(문자 1개), %(문자열), +(문자 연결), [](여기 안에 있는 것중에 겹치는 거), [^](하나도 없어야 함)
        • Ex) SELECT * FROM Book WHERE bookname LIKE '%_구%';
    • 복합조건 검색
      • AND, OR, NOT
        • ex) SELECT * FROM Book WHERE price >= 10000 and price <= 20000;
    • 검색결과 정렬
      • 특정 순서로 정렬하고 싶다면, order by 사용하기. (기본은 ASC)
      • 2개를 기준으로 하고 싶다면, 콤마로 이어서 쓰기.
        • ex) SELECT * FROM Book ORDER BY bookname ASC, publisher DESC;
• 집계 함수와 GROUP BY
  • 집계 함수: 각 열에 대한 계산
    • SUM, AVG, COUNT, MAX, MIN
    • 새 이름 출력하고 뒤에 AS 붙이기.
    • COUNT(*): NULL 포함하고 개수 세기
  • GROUP BY: 속성이 같은 것 끼리 그룹 만들기.
    • ex) 고객별 주문한 도서의 총 수량과 판매액
      • SELECT COUNT(*), SUM(price) FROM Book GROUP BY custid;
  • HAVING: GROUP BY로 인한 결과로 나타나는 그룹 제한
    • ex) 가격이 8000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량 구하기.단 구매한게 2개 이상.
      • SELECT COUNT(*) FROM Orders WHERE salesprice >= 8000 GROUP BY custid HAVING COUNT(*) >= 2;
  • GROUP BY와 HAVING의 주의 사항
    • GROUP BY의 주의 사항
      • SELECT 절에는 GROUP BY에 쓰인 속성이랑 집계 함수만 사용 가능.
    • HAVING의 주의 사항
      • WHERE보다 뒤에
      • 반드시 GROUP BY와 함께 쓰여야 함.
      • 검색 조건에는 반드시 집계 함수가 와야 함.
  • ✔️ SQL 문은 비절차적인 언어이지만, SQL문 내부적으로 실행 순서가 존재함.
    • HAVING의 예시 활용.
      • 1. FROM Orders 실행.
      • 2. WHERE salesprice >= 8000 실행.
      • 3. GROUP BY custid 실행.
      • 4. HAVING 조건 적용.
      • 5. SELECT 하기.
      • 6. ORDER BY 하기.
• 2개 이상의 테이블에서 질의: JOIN, SubQuery
  • JOIN
    • 한 테이블에 다른 테이블의 행을 연결하여 2개 이상의 테이블을 결합하는 연산.
    • 모든 연산 결과는 반드시 단일 테이블이다.
    • 동등조인(=내부조인): 동등 조건에 의해 테이블 조인하기
      • ex) SELECT name, SUM(salesprice) FROM Customer, ORDERS Where Customer.custid = Orders.custid;
    • 외부조인: 조인이 없는 곳에는 NULL을 채워주기
      • ex) SELECT names, salesprice FROM Customer OUTER JOIN Orders ON Customer.custid = Orders.custid;
  • 부속 질의
    • SQL 내의 또다른 SQL 문이 위치.
    • 괄호로 묶는 것.
    • ex) SELECT name FROM Book WHERE price = (SELECT MAX(price) FROM Book);
    • 만약 1Xn의 릴레이션이 돌아온다면? IN 연산 사용하기.
      • ex) SELECT name FROM customer WHERE custid IN (SELECT custid FROM Orders);
    • 일반 부속 질의는 상하 관계에 위치.
    • 상관 부속 질의는 상위 부속 질의로 하위 부속 질의를 계산. (독립적이지 않음).
      • ex) SELECT b1.bookname FROM Book b1 WHERE b1.price > (SELECT avg(b2.price) FROM Book b2 WHERE b1.publisher = b2.publisher);
• 집합 연산
  • 합집합(UNION), 차집합(EXCEPT), 교집합(INTERSECT)
    • ex) 도서를 구매하지 않은 고객의 이름.
      • SELECT name FROM Customer
      • EXCEPT
      • SELECT name FROM Cusotmer Where custid IN (SELECT custid FROM Orders);
• EXISTS
  • 상관부속질의문
  • 조건이 맞는 튜플 존재 시 결과에 포함. (하나라도 존재시 참)
  • NOT EXISTS(모든 행이 만족하지 않으면 참)
  • 모든 행을 가져다가 확인해봄.
    • Ex) SELECT name , address FROM customer cs WHERE EXISTS (SELECT * FROM Orders ord WHERE cs.custid = ord.custid);

 

✔️데이터 조작어 - 삽입, 삭제, 수정

• INSERT(삽입)
  • INSERT INTO 테이블 이름 VALUES(속성);
    • ex) INSERT INTO Book(bookid, bookname, publisher, price) VALUES(1, '체육의 역사', '한솔서적', 7000);
  • 속성 이름 생략 가능. 단 순서가 같아야 함.
  • 몇개만 입력하고 싶다면, 해당 속성 입력. (나머지는 NULL 처리됨)
  • 대량 삽입 시 SELECT 문을 사용하기도 함. 다만 속성이 같아야 함.
• UPDATE(수정)
  • UPDATE 테이블명 SET 속성 = 값 [WHERE];
    • ex) UPDATE Customer SET address = "서울시 종로구" WHERE custid = 5;
• DELETE(삭제)
  • 기존 튜플 삭제
  • DELETE FROM 테이블 WHERE 조건;
  • 테이블 구조는 그대로 두고, 튜플만 삭제.
  • 외래키로 참고하고 있는 자식 릴레이션이 존재하면 STOP.