데이터 조작어 (DML)
- 데이터 검색 / 입력 / 수정 / 삭제
- SELECT / INSERT / UPDATE / DELETE
INSERT(데이터 입력)
- 테이블에 새로운 행을 삽입하는 명령어
- 기본 형식
INSERT INTO 테이블명 (열이름 리스트) VALUES (값리스트);
ex)
INSERT INTO student (stdNo, stdName, stdYear, dptNo) VALUES ('20210101', '홍길동', 1, '1')
실습
CREATE SCHEMA sqldb2;
use sqldb2;
-- 출판사 테이블 생성
CREATE TABLE publisher(
pubNo VARCHAR(10) NOT NULL PRIMARY KEY,
pubName VARCHAR(30) NOT NULL
);
-- 도서 테이블 생성
CREATE TABLE book (
bookNo VARCHAR(10) NOT NULL PRIMARY KEY,
bookname VARCHAR(30) NOT NULL,
bookPrice INT DEFAULT 10000 CHECK(bookPrice > 1000),
bookDate DATE,
pubNo VARCHAR(10) NOT NULL,
CONSTRAINT FK_book_publisher FOREIGN KEY (pubNo) REFERENCES publisher (pubNo)
);
INSERT INTO publisher (pubNo, pubName) VALUES ('1','서울 출판사');
INSERT INTO publisher (pubNo, pubName) VALUES ('2','강남 출판사');
INSERT INTO publisher (pubNo, pubName) VALUES ('3','종로 출판사');
SELECT * FROM publisher;
INSERT INTO book (bookNo, bookName, bookPrice, bookDate, pubNo) VALUES('1', '자바', 20000, '2021-05-17', '1');
-- 모든 열에 데이터를 입력할 경우 열이름 생략 가능
INSERT INTO book VALUES('2', '자바스크립트', 23000, '2021-05-17', '3');
INSERT INTO book(bookNo, bookName,bookPrice,bookDate,pubNo)
VALUES('3', '데이터베이스', 35000, '2021-07-11', '2'),
('4', '알고리즘', 18000, '2021-05-22', '3'),
('5', '웹프로그래밍', 22000, '2021-09-10', '2');
-- 모든 열에 데이터를 입력할 경우 열이름 생략 가능
INSERT INTO book(bookNo, bookName,bookPrice,bookDate,pubNo)
VALUES('3', '데이터베이스', 35000, '2021-07-11', '2'),
('4', '알고리즘', 18000, '2021-05-22', '3'),
('5', '웹프로그래밍', 22000, '2021-09-10', '2');
연습문제
- INSERT 문을 사용하여 학괴/학생 테이블에 다음과 같이 데이터 입력
- SELECT 문으로 조회
-- 학과 테이블 생성
CREATE TABLE department(
dptNo VARCHAR(10) NOT NULL PRIMARY KEY,
dptName VARCHAR(30) NOT NULL,
dptTel VARCHAR(13)
);
-- 학생 테이블 생성
CREATE TABLE student (
stdNo VARCHAR(10) NOT NULL PRIMARY KEY,
stdName VARCHAR(30) NOT NULL,
stdYear INT DEFAULT 4 CHECK(stdYear >= 1 AND stdYear <= 4),
stdAddress VARCHAR(50),
stdBirthDay DATE,
dptNo VARCHAR(10) NOT NULL,
CONSTRAINT FK_student_department FOREIGN KEY (dptNo) REFERENCES department (dptNo)
);
INSERT INTO department (dptNo, dptName, dptTel)
VALUES ('1', '컴퓨터학과','02-1111-1111'),
('2', '경영학과','02-2222-2222'),
('3', '수학과','02-3333-3333');
INSERT INTO student (stdNo, stdName, stdYear, stdAddress, stdBirthday, dptNo)
VALUES ('2018002' ,'이몽룡', 4, '서울시 강남구', '1998-05-07', '1'),
('2019003' ,'홍길동', 3, '경기도 안양시', '1999-11-11', '2'),
('20211003' ,'성춘향', 1, '전라북도 남원시', '2002-01-02', '3'),
('2021004' ,'변학도', 1, '서울시 종로구', '2000-03-26', '2');
SELECT * FROM student;
데이터 import
- CSV 파일 읽어서 테이블 생성 및 데이터 입력
- product.csv
- 스키마 -> 우클릭 -> Table Data Import Wizard
- 파일 임포트 시 제약조건 없어짐 -> 제약조건 추가 필요(기본키, 외래키 제약조건)
- 문자 타입을 text로 읽어옴 -> 타입이 커서 오류 발생
- text 문자타입 -> VARCHAR( )로 변경
- product..csv 파일 import 해서 product 테이블 생성
DESCRIBE product;
DESC product;
- 파일 임포트 하면 제약조건 없어짐 -> 기본키 추가 필요
- 기본키 추가 전에 text를 VARCHAR( )로 변경 (안하면 오류)
- prdNo를 기본키 prdNo VARCHAR(10) NOT NULL로 변경
ALTER TABLE product MODIFY prdNo VARCHAR(10) NOT NULL;
ALTER TABLE product ADD CONSTRAINT PK_product_prdNo PRIMARY KEY (prdNo);
- 모든 text 타입을 VARCHAR로 변경하기
ALTER TABLE product MODIFY prdName VARCHAR(20),
MODIFY prdMaker VARCHAR(30),
MODIFY prdColor VARCHAR(10),
MODIFY ctgNo VARCHAR(10);
UPDATE (데이터 수정)
- 특정 열의 값을 수정하는 명령어
- 조건에 맞는 행을 찾아서 열의 값을 수정
UPDATE 테이블명 SET 열 = 새 값 WHERE 조건;
ex)
UPDATE product SET prdName = 'UHD TV' WHERE prdNO = '5';
실습
SELECT * FROM product;
- 상품번호가 1005인 상품의 상품명을 UHD TV로 변경
UPDATE product SET prdName = 'UHD TV' WHERE prdNO = '1005';
DELETE (데이터 삭제)
DELETE FROM 테이블명 WHERE 조건;
ex)
DELETE FROM product WHERE prdName = '그늘막 텐트';
DELETE FROM product;
실습
DELETE FROM product WHERE prdName = '그늘막 텐트';
연습문제 (INSERT~DELETE)
- book 테이블에 행 삽입 (그림 참조)
- book 테이블에서 도서명이 '자바'인 행의 가겨글 22000으로 변경
- book 테이블에서 발행일이 2018년도인 행 삭제
INSERT INTO book(bookNo, bookName,bookPrice,bookDate,pubNo)
VALUES('9', 'JAVA 프로그래밍', 30000, '2021-03-10','1'),
('10','파이썬 데이터 과학',24000,'2018-02-05','2');
UPDATE book SET bookPrice = 22000 WHERE bookName = '자바';
DELETE FROM book WHERE bookDate >= '2018-01-01' AND bookDate <= '2018-12-31';
종합 연습문제
- 고객 테이블 (customer) 생성
- 고객 테이블의 전화번호 열을 NOT NULL로 변경
- 고객 테이블에 ‘성별’, ‘나이’ 열 추가
- 고객 테이블에 데이터 삽입 (3개)
- 고객명이 홍길동인 고객의 전화번호 값 수정 (값은 임의로)
- 나이가 20살 미만인 고객 삭제
-- 고객 테이블 생성
CREATE TABLE customer(
custNo VARCHAR(10) NOT NULL PRIMARY KEY,
custName VARCHAR(30),
custPhone VARCHAR(13),
custAddress VARCHAR(50)
);
-- 고객 테이블 전화번호 not null 변경
ALTER TABLE customer MODIFY custPhone VARCHAR(13) NOT NULL;
-- 성별, 나이 열 추가
ALTER TABLE customer ADD (custSex VARCHAR(10), custAge int);
-- 고객 테이블 데이터 삽입
INSERT INTO customer(custNo, custName, custPhone, custAddress, custSex, custAge)
VALUES('1','김준면','010-1991-0522','서울시 영등포구','남',32),
('2','김민석','010-2345-6789','경기도 구리시','남',32),
('3','변백현','010-1992-0506','경기도 부천시','남',30);
-- 고객명이 김민석인 고객의 전화번호 값 수정 (값은 임의로)
UPDATE customer SET custPhone = '010-1990-0326' WHERE custName = '김민석';
-- 나이가 31살 미만인 고객 삭제
DELETE FROM customer WHERE custAge < 31;
SELECT문(데이터 검색)
SELECT 열이름 리스트 FROM 테이블명
WHERE 검색조건
GROUP BY 열이름
HAVING 조건
ORDER BY 열이름 [ASC|DESC];
- 추가 옵션 순서
- WHERE / GROUP BY / HAVING / ORDER BY
실습
- 스키마 생성 sqldb3
- CSV 파일의 데이터 읽어서 테이블 생성
- publisher
- book
- client
- bookSale
- 데이터 타입 변경
- text -> VARCHAR( )
- 모든 xxxNo는 문자타입
- 날짜는 DATE 타입
- 기본키 / 외래키 설정
CREATE DATABASE sqldb3;
use sqldb3;
-- 데이터 타입 변경하기
ALTER TABLE publisher
MODIFY pubNo VARCHAR(10),
MODIFY pubName VARCHAR(20);
ALTER TABLE book
MODIFY bookNo VARCHAR(10),
MODIFY bookName VARCHAR(20),
MODIFY bookAuthor VARCHAR(10),
MODIFY bookPrice INT,
MODIFY bookDate Date,
MODIFY bookStock VARCHAR(20);
ALTER TABLE client
MODIFY clientNo VARCHAR(10),
MODIFY clientName VARCHAR(10),
MODIFY clientPhone VARCHAR(13),
MODIFY clientAddress VARCHAR(50),
MODIFY clientBirth Date,
MODIFY clientHobby VARCHAR(20),
MODIFY clientGender VARCHAR(10);
ALTER TABLE bookSale
MODIFY bsNo VARCHAR(10),
MODIFY bsDate Date,
MODIFY bsQty INT;
ALTER TABLE publisher ADD CONSTRAINT PK_publisher_pubNo PRIMARY KEY (pubNo);
ALTER TABLE book ADD CONSTRAINT PK_book_bookNo PRIMARY KEY (bookNo);
ALTER TABLE client ADD CONSTRAINT PK_client_clientNo PRIMARY KEY (clientNo);
ALTER TABLE bookSale ADD CONSTRAINT PK_bookSale_bsNo PRIMARY KEY (bsNo);
ALTER TABLE book ADD CONSTRAINT FK_book_publisher
FOREIGN KEY(pubNo) REFERENCES publisher(pubNo);
ALTER TABLE bookSale ADD CONSTRAINT FK_bookSale_client
FOREIGN KEY (clientNo) REFERENCES client(clientNo);
ALTER TABLE bookSale ADD CONSTRAINT FK_bookSale_book
FOREIGN KEY (bookNo) REFERENCES book(bookNo);
실습 -1
SELECT * FROM book;
SELECT bookName, bookPrice FROM book;
SELECT bookAuthor FROM book;
- 저자만 검색하여 출력 & 중복되는 행은 1번만 출력
SELECT DISTINCT bookAuthor FROM book;
WHERE 조건
- 저자가 '홍길동'인 도서의 도서명, 저자 출력하기
SELECT bookName, bookAuthor
From book
WHERE bookAuthor = '홍길동';
- 가격이 30000원 이상인 도서의 도서명, 가격, 재고 출력
SELECT bookName, bookPrice, bookStock
FROM book
WHERE bookPrice >= 30000;
- 재고가 3~5 사이인 도서의 도서명, 재고 출력
SELECT bookName, bookStock
FROM book
WHERE bookStock >=3 AND bookStock <=5;
BETWEEN 조건
- 재고가 3~5 사이인 도서의 도서명, 재고 출력
SELECT bookName, bookStock
FROM book
WHERE bookStock BETWEEN 3 AND 5;
- 리스트에 포함(IN)
- 출판사명이 서울출판사(pubNo=1)와 도서출판 강남(pubNo=2)인 도서의 도서명, 출판사번호 출력
SELECT bookName, pubNo
FROM book
WHERE pubNo IN ('1','2');
- 리스트에 포함(NOT IN)
- 출판사명이 도서출판 강남(pubNo=2)이 아닌 도서의 도서명, 출판사
SELECT bookName, pubNo
FROM book
WHERE pubNo NOT IN ('2');
- NULL값 설정하기
- client 테이블에 호날두, 샤라포바의 취미 null로 설정하기
UPDATE client SET clientHobby = null WHERE clientName = '호날두';
UPDATE client SET clientHobby = null WHERE clientName = '샤라포바';
SELECT clientName, clientHobby
FROM client
WHERE clientHobby IS NULL;
SELECT clientName, clientHobby
FROM client
WHERE clientHobby IS NOT NULL;
- 취미에 공백이 있는 행만 출력
- ''와 ' '는 동일 (스페이스 개수 상관 없이 공백으로 인식)
SELECT clientName, clientHobby
FROM client
WHERE clientHobby= '';
논리(AND , OR)
- 저자가 '홍길동'이면서 재고가 3권 이상인 모든 도서 출력
SELECT *
FROM book
WHERE bookAuthor = '홍길동' AND bookStock >= 3;
SELECT *
FROM book
WHERE bookAuthor = '홍길동' OR bookAuthor = '성춘향';
패턴매칭(LIKE)
- 와일드카드 문자
- %: 0개 이상의 문자를 가진 문자열
- _: 단일 문자(수 만큼의 문자로 구성)
- '홍%': 홍으로 시작하는 문자열 검색
- '%길%': 길을 포함하는 문자열(
길 앞,뒤로 아무 문자나 와도 상관 X)
- '%동': 동으로 끝나는 문자열
- '____': 4개의 문자로 구성된 문자열(밑줄문자 1개가 1개문자)
- 출판사 테이블에서 출판사 명에 '출판사'가 포함된 모든 행 출력
SELECT *
FROM publisher
WHERE pubName LIKE '%출판사%';
- 고객 중 출생년도가 1990년대인 모든 고객 출력
SELECT *
FROM client
WHERE clientBirth LIKE '%199%';
- 고객 테이블에서 고객이 4글자인 모든 고객 정보 출력
SELECT *
FROM client
WHERE clientName LIKE '____';
- 도서 테이블에서 도서명에 '안드로이드'가 들어있지 않은 도서의 도서명 출력
SELECT bookName
FROM book
WHERE bookName NOT LIKE '%안드로이드%';
연습문제
- 고객 테이블에서 고객명, 생년월일, 성별 출력
SELECT clientName, clientBirth, clientGender
FROM client;
- 고객 테이블에서 주소만 검색하여 출력(중복되는 주소는 한번만 출력)
SELECT DISTINCT clientAddress
FROM client
- 고객 테이블에서 취미가 축구이거나 등산인 고객의 고객명, 취미 출력
SELECT clientName, clientHobby
FROM client
WHERE clientHobby = '축구' OR clientHobby = '등산';
- 도서 테이블에서 저자의 두 번째 위치에 '길'이 들어 있는 저자명 출력 (중복되는 저자명은 한번만 출력)
SELECT DISTINCT bookAuthor
FROM book
WHERE bookAuthor LIKE'%_길%';
- 도서 테이블에서 발행일이 2019년인 도서의 도서명, 저자, 발행일 출력
SELECT bookName, bookAuthor, bookDate
FROM book
WHERE bookDate LIKE'%2019%';
- 도서판매 테이블에서 고객번호1, 2를 제외한 모든 판매 데이터 출력
SELECT *
FROM bookSale
WHERE clientNo NOT IN('1', '2');
- 고객 테이블에서 취미가 NULL이 아니면서 주소가 '서울'인 고객의 고객명, 주소, 취미 출력
SELECT clientName, clientAddress, clientHobby
FROM client
WHERE clientHobby IS NOT NULL AND clientAddress = '서울';
- 도서 테이블에서 가격이 25,000원 이상이면서 저자 이름에 '길동'이 들어가는 도서의 도서명, 저자, 가격, 재고 출력
SELECT bookName, bookAuthor, bookPrice, bookStock
FROM book
WHERE bookPrice >= 25000 AND bookAuthor Like '%길동%';
- 도서 테이블에서 가격이 20,000 ~ 25,000원인 모든 도서 출력
SELECT bookName, bookAuthor, bookPrice, bookStock
FROM book
WHERE bookPrice >= 20000 AND bookPrice <= 25000;
- 도서 테이블에서 저자명에 '길동'이 들어 있지 않는 도서의 도서명, 저자 출력
SELECT bookName, bookAuthor
FROM book
WHERE bookAuthor NOT Like '%길동%';
ORDER BY
- 특정 열의 값을 기준으로 쿼리 결과 자ㅓㅇ렬
- ASC: 오름차순(생략 가능)
- DESC: 내림차순
- 도서명 순서대로 검색(기본: 오름차순 (주로 ASC는 생략))
SELECT *
FROM book
ORDER BY bookName ASC;
SELECT *
FROM book
ORDER BY bookName DESC;
SELECT *
FROM book
ORDER BY (
CASE WHEN ASCII(SUBSTRING(bookName, 1)) BETWEEN 48 AND 57 THEN 3
WHEN ASCII(SUBSTRING(bookName, 1)) < 122 THEN 2 ELSE 1 END), bookName;
-- 영어 대문자 A ~ Z : 65 - 90
-- 영어 소문자 a ~ z : 97 - 122
-- 숫자: 0 - 9 : 48 - 57
SELECT *
FROM book
ORDER BY (
CASE WHEN ASCII(SUBSTRING(bookName, 1)) BETWEEN 48 AND 57 THEN 3
WHEN ASCII(SUBSTRING(bookName, 1)) < 122 THEN 1 ELSE 2 END), bookName;
SELECT *
FROM book
ORDER BY bookName
-- 첫 번째부터 상위 5개
LIMIT 5;
- OFFSET 0 설정 (OFFSET: 시작위치) -> 첫 번째부터 시작
SELECT *
FROM book
ORDER BY bookName
LIMIT 5 OFFSET 0;
- LIMIT 시작, 개수
- LIMIT 0, 5 : 첫 번째부터 5개
- LIMIT 10, 5 : 11번째부터 5개
SELECT *
FROM book
ORDER BY bookName
LIMIT 0, 5;
SELECT *
FROM book
ORDER BY bookName
LIMIT 10, 5;
- 도서 테이블에서 재고 수량을 기준으로 내림차순 정렬하여 도서명, 저자, 재고 출력
SELECT bookName, bookAuthor, bookStock
FROM book
ORDER BY bookName DESC;
- 2차 정렬
- 도서 테이블에서 재고 수량을 기준으로 내림차순 정렬하여 도서명, 저자, 재고 출력
- 재고 수량이 동일한 경우, 저자명으로 오름차순 2차 정렬
SELECT bookName, bookAuthor, bookStock
FROM book
ORDER BY bookName DESC, bookAuthor ASC; -- ASC 생략 가능
집계함수
- SUM( ): 합계
- AVG( ): 평균
- COUNT( ): 선택된 열 행 수(널 값 제외)
- COUNT(*): 모든 열을 대상으로 전체 행 수
- MAX( ): 최대
- MIN( ): 최소
SELECT SUM(bookStock)
FROM book;
SELECT SUM(bookStock) As 'sum of bookStock'
FROM book;
- 도서판매 테이블에서 고객번호 2인 호날두가 주문한 도서의 총 주문수량 계산하여 출력
SELECT SUM(bsQty) AS '총 주문수량'
FROM bookSale
WHERE clientNo = '2';
SELECT MAX(bsQty) AS '최대 주문량', MIN(bsQty) AS '최소 주문량'
FROM bookSale;
- 도서 테이블에서 도서의 가격 총합, 평균 값, 최고가, 최저가 출력
SELECT SUM(bookPrice) AS '도서 가격 총합', AVG(bookPrice) AS '평균가',
MAX(bookPrice) AS '최고가', MIN(bookPrice) AS '최저가'
FROM bookSale;
- 평균가를 정수로 표현 (반올림): ROUND(숫자)
SELECT SUM(bookPrice) AS '도서 가격 총합', ROUND(AVG(bookPrice)) AS '평균가',
MAX(bookPrice) AS '최고가', MIN(bookPrice) AS '최저가'
FROM bookSale;
- 도서 판매 테이블에서 도서 판매 건수 출력(모든 행의 수)
SELECT COUNT(*) AS "총 판매 건수"
FROM bookSale;
- 고객 테이블에서 전체 고객 수(모든 행의 수 출력)
SELECT COUNT(*) AS "총 고객 수"
FROM client;
SELECT COUNT(clientHobby) AS 취미
FROM client;
SELECT COUNT(clientHobby) AS 취미
FROM client
WHERE clientHobby NOT IN ('');
GROUP BY
- 그룹 쿼리를 기술할 때 사용
- 특정 열로 그룹화한 후 각 그룹에 대해 한 행식 쿼리 결과 생성
- 도서 판매 테이블에서 도서별로 주문수량, 판매수량 합계 출력
SELECT bookNo, SUM(bsQty) AS '판매량 합계'
FROM bookSale
GROUP BY bookNo;
- GROUP BY 절에서 정렬: 열 이름으로 정렬
SELECT bookNo, SUM(bsQty) AS '판매량 합계'
FROM bookSale
GROUP BY bookNo;
ORDER BY bookNo;
SELECT bookNo, SUM(bsQty) AS '판매량 합계'
FROM bookSale
GROUP BY bookNo;
ORDER BY 1; -- 첫 번재 열로 정렬
HAVING BY
- HAVING 조건
- GROUP BY 절에 의해 구성된 그룹들에 대해 적용할 조건 기술
- SUM( ), AVG( ), MAX( ), MIN( ), COUNT( ) 등의 집계 함수와 함께 사용
- 주의
- 반드시 GROUP BY 절과 함께 사용
- WHERE 절보다 뒤에 검색조건에 집계함수가 와야 함
- 도서 테이블에서 가격이 25000원인 도서에 대해 출판사별로 도서 수 합계(출판사별: 그룹화)
- 단, '도서 수 합계'가 2인 이상만 출력
SELECT pubNo, COUNT(*) AS "도서 수 합계"
FROM book
WHERE bookPrice >= 25000
GROUP BY pubNo
HAVING COUNT(*) >= 2;
[결과]
1 2
2 6
SELECT pubNo, COUNT(*) AS "도서 수 합계"
FROM book
WHERE bookPrice >= 25000
GROUP BY pubNo;
[결과]
1 2
2 6
3 1
연습문제
- 도서 테이블에서 가격 순으로 내림차순 정렬하여, 도서명, 저자, 가격 출력(가격이 같으면 저자 순으로 오름차순 정렬)
SELECT bookName, bookAuthor, bookPrice
FROM book
ORDER BY bookPrice DESC, bookAuthor ASC;
- 도서 테이블에서 저자에 '길동'이 들어가는 도서의 총 재고 수량 계산하여 출력
SELECT SUM(bookStock) AS "총 재고 수량"
FROM book
WHERE bookAuthor LIKE '%길동%';
- 도서 테이블에서 ‘서울 출판사' 도서 중 최고가와 최저가 출력
SELECT MAX(bookPrice) AS 최고가, MIN(bookPrice) AS 최저가
FROM book
WHERE pubNo = '1';
- 도서 테이블에서 출판사별로 총 재고수량과 평균 재고 수량 계산하여 출력 (‘총 재고 수량’으로 내림차순 정렬)
SELECT pubNo, SUM(bookStock) AS 총재고수량,
AVG(bookStock) AS '평균 재고 수량'
FROM book
GROUP BY pubNo
ORDER BY 총재고수량 DESC;
- 주의
- MySQL에서는 ORDER BY 다음의 열이름에 따옴표 있으면 안 됨
SELECT pubNo, SUM(bookStock) AS '총재고수량',
AVG(bookStock) AS '평균 재고 수량'
FROM book
GROUP BY pubNo
ORDER BY '총재고수량' DESC; -- 따옴표 있으면 정렬 안 됨
- 도서판매 테이블에서 고객별로 ‘총 주문 수량’과 ‘총 주문 건수’ 출력. 단 주문 건수가 2이상인 고객만 해당
SELECT clientNo, COUNT(*) AS "총 주문 건수", SUM(bsQty) AS "총 주문 수량"
FROM bookSale
GROUP BY clientNo
HAVING COUNT(*) >= 2;
