SQL의 데이터 조작 기능(2)

SQL의 데이터 조작 기능(2)

5. NULL을 이용한 검색

검색 조건에서 특성 속성의 값이 널 값인지를 비교하려면 IS NULL 키워드를 사용한다. 마찬가지로 특정 속성의 값이 널 값이 아닌지를 비교하려면 IS NOT NULL 키워드를 사용한다.

고객 테이블에서 나이가 아직 입력되지 않은 고객이름을 검색해보자.
SELECT name FROM customer WHERE age IS NULL;

검색 조건에서 나이가 아직 입력되지 않았다는 것은 나이 속성이 널 값임을 의미하며 이러한 검색 조건은 'age = NULL' 형태로 표현해서는 안된다.

고객 테이블에서 나이가 이미 입력된 고객의 고객 이름을 검색해보자.
SELECT name FROM customer WHERE age IS NOT NULL;

검색 조건에서 나이가 이미 입력되었다는 것은 나이 속성이 널 값이 아님을 의미한다. 이러한 검색 조건은 'age <> NULL' 의 형태로 표현하지 않고 반드시 'age IS NOT NULL' 형태로 표현해야 한다.

검색 조건에서 널 값은 다른 값과 크기를 비교하면 결과가 모두 거짓이 된다. 아래에서 재고량 stock의 값이 널이면 어떤 비교 연산자를 사용해도 결과가 모두 거짓이다.

재고량 > 10
재고량 < 10
재고량 >= 10
재고량 <= 10
재고량 = 10
재고량 <> 10

6. 정렬 검색

SELECT 문의 검새 결과 테이블은 일밙거으로 DBMS가 정한 순서로 출력된다. 결과 테이블의 내용을 사용자가 원하는 순서로 출력하려면 ORDER BY 키워드를 사용한다. 결과 테이블의 내용을 원하는 기준에 따라 정렬하여 출력하는 SELECT 문의 기본 형식은 다음과 같다.

SELECT [ ALL | DISTINCT ] 속성_리스트
FROM 테이블_리스트
[ WHERE 조건 ]
[ ORDER BY 속성_리스트 [ ASC | DESC ] ];

ORDER BY 키워드와 함께 정렬 기준이 되는 속성을 지정하고, 오름차순 정렬이면 ASC, 내림차순 정렬이면 DESC로 표현한다. 기본 정렬 방법은 오름차순이므로 특별히 지정하지 않으면 오름차순으로 기본 정렬한다.

문자 데이터를 오름차순으로 정렬하면 알파벳이나 사전 순으로 출력된다. 날짜 데이터는 빠른 날짜가 먼저 출력된다. 널 값은 오름차순에서 맨 먼저 출력되고 내림차순에서는 맨 마지막에 출력된다.

결과를 여러 기준에 따라 정렬하려면 ORDER BY 키워드와 함께 정렬 기준이 되는 속성을 차례로 제시하면 된다.

고객 테이블에서 고객이름, 등급, 나이를 검색하되, 나이를 기준으로 내림차순 정렬해보자.
SELECT name, grade, age FROM customer ORDER BY age DESC;

주문 테이블에서 수량이 10개 이상인 주문의 주문고객, 주문제품, 수량, 주문일자를 검색해보자. 단, 주문제품을 기준으로 오름차순 정렬하고, 동일 제품은 수량을 기준으로 내림차순 정렬해보자.
SELECT name, product, quantity, date FROM order_info ORDER BY product ASC, quantity DESC;

7. 집계함수

함수	의미	사용 가능한 속성의 타입
COUNT	속성 값의 개수
MAX	속성 값의 최대값	모든 데이터
MIN	속성 값의 최소값
SUM	속성 값의 합계	숫자 데이터
AVG	속성 값의 평균

집계 함수를 사용할 때 다음과 같은 사항에 주의해야 한다.

• 집계 함수는 널인 속성 값은 제외하고 계산한다.
• 집계 함수는 WHERE 절에서는 사용할 수 없고 SELECT 절이나 HAVING 절에서만 사용할 수 있다.

제품 테이블에서 모든 제품의 단가 평균을 검색해보자.

SELECT AVG(price) FROM product;

한빛제과에서 제조한 제품의 재고량 합계를 제품 테이블에서 검색해보자. 출력할 때 속성이름을 재고량 합계로 표시하자.

SELECT SUM(price) AS '재고량 합계' FROM product WHERE manufacturer = '한빛제과';

고객 테이블에 몇 명이 등록되어 있는지 검색해보자.

SELECT COUNT(*) FROM customer;

집계 함수는 널인 속성 값은 제외하기 때문에 널 값을 포함하고 있는 속성으로 COUNT 함수를 적용하면 다른 값이 나올 수 있다. 개수를 정확히 계산하려면 널 값이 없는 속성에 COUNT 함수를 적용하는 것이 좋기 때문에 보통 기본키 속성이나 * 를 이용해 계산한다.

DISTINCT 키워드를 사용해 특정 속성 값의 중복을 없애고 집계 함수를 적용할 수도 있다.

제품 테이블에서 제조업체의 수를 검색해보자. 속성값은 '제조업체 수'로 표시한다.

SELECT COUNT(DISTINCT manufacturer) AS '제조업체 수' FROM product;

[출처] 한빛아카데미 데이터베이스 개론 김현희지음

SQL의 데이터 조작 기능(1)

SQL의 데이터 조작 기능(1)

데이터 검색 SELECT
데이터 삽입 INSERT
데이터 수정 UPDATE
데이터 삭제 DELETE

INSERT INTO customer VALUES ('apple', '정소화', 20, 'gold', '학생', 1000);
INSERT INTO customer VALUES ('banana', '김선우', 25, 'vip', '간호사', 2500);
INSERT INTO customer VALUES ('carrot', '고명석', 28, 'gold', '교사', 4500);
INSERT INTO customer VALUES ('orange', '김용욱', 22, 'silver', '학생', 0);
INSERT INTO customer VALUES ('melon', '성원용', 35, 'gold', '회사원', 5000);
INSERT INTO customer VALUES ('pear', '채광주', 31, 'silver', '회사원', 500);
INSERT INTO customer VALUES ('peach', '오형준', NULL, 'silver', '의사', 300);

INSERT INTO product VALUES ('p01', '그냥만두', 5000, 4500, '대한식품');
INSERT INTO product VALUES ('p02', '매운쫄면', 2500, 5500, '민국푸드');
INSERT INTO product VALUES ('p03', '쿵떡파이', 3600, 2600, '한빛제과');
INSERT INTO product VALUES ('p04', '맛난초콜렛', 1250, 2500, '한빛제과');
INSERT INTO product VALUES ('p05', '얼큰라면', 2200, 1200, '대한식품');
INSERT INTO product VALUES ('p06', '통통우동', 1000, 1550, '민국푸드');
INSERT INTO product VALUES ('p07', '달콤비스켓', 1650, 1500, '한빛제과');

INSERT INTO order_info VALUES ('o01', 'apple', 'p03', 10, '서울시 마포구', '13/01/01');
INSERT INTO order_info VALUES ('o02', 'melon', 'p01', 5, '인천시 계양구', '13/01/10');
INSERT INTO order_info VALUES ('o03', 'banana', 'p06', 45, '경기도 부천시', '13/01/11');
INSERT INTO order_info VALUES ('o04', 'carrot', 'p02', 8, '부산시 금정구', '13/02/01');
INSERT INTO order_info VALUES ('o05', 'melon', 'p06', 36, '경기도 용인시', '13/02/20');
INSERT INTO order_info VALUES ('o06', 'banana', 'p01', 19, '충청북도 보은군', '13/03/02');
INSERT INTO order_info VALUES ('o07', 'apple', 'p03', 22, '서울시 영등포구', '13/03/15');
INSERT INTO order_info VALUES ('o08', 'pear', 'p02', 50, '강원도 춘천시', '13/04/10');
INSERT INTO order_info VALUES ('o09', 'banana', 'p04', 15, '전라남도 목포시', '13/04/11');
INSERT INTO order_info VALUES ('o10', 'carrot', 'p03', 20, '경기도 안양시', '13/05/22');

1. 데이터의 검색

1. 기본 검색

SELECT [ALL | DISTINCT ] 속성_리스트 FROM 테이블_리스트;

SELECT 키워드와 함께 검색하고 싶은 속성의 이름을 콤마(,) 로 구분하여 차례로 나열한다. 그리고 FROM 키워드와 함께 검색하고 싶은 속성이 있는 테이블의 이름을 콤마(,) 로 구분하여 차례로 나열한다. SELECT 문은 검색 결과를 테이블 형태로 반환한다. 즉, 테이블을 대상으로 하는 SELECT 문의 수행 결과도 테이블이다.

고객 테이블에서 고객아이디, 고객이름, 등급 속성을 검색해본다.
SELECT id, name, grade FROM customer;

고객 테이블에 존재하는 모든 속성을 검색해본다.
SELECT id, name, age, grade, job, saved_money FROM customer;

고객 테이블에 존재하는 모든 속성을 검색해보자.
SELECT * FROM customer;
결과는 컬럼을 모두 나열한 앞의 예와 동일하다.

제품 테이블에서 제조업체를 검색해보자.
SELECT manufacturer from product;

위 결과와 같이 SELECT 문의 결과 테이블은 관계 데이터 모델의 일반 릴레이션과 큰 차이가 있다. 관계 데이터 모델의 일반 릴레이션은 투플의 집합 개념으로 이해할 수 있으며 투플의 유일성을 만족해야 하기 때문에 릴레이션 하나에서 동일한 투플이 중복되면 안되다. 그러나 SELECT 문의 수행 결과로 반환되는 결과 테이블에서는 동일한 투플이 중복될 수 있다.

결과 테이블이 중복을 허용하도록 ALL 키워드를 명시적으로 사용할 수 있다.
SELECT ALL manufacturer from product;
결과는 위의 예와 동일하다.

결과 테이블에서 투플의 중복을 제거하고 한 번씩만 출력되도록 하려면 DISTINCT 키워드를 사용한다.

제품 테이블에서 제조업체 속성을 중복 없이 검색해보자.
SELECT DISTINCT manufacturer FROM product;

결과 테이블에서 출력되는 속성의 이름을 다른 이름으로 바꾸어 출력할 수도 있다. AS 키워드를 변경할 이름과 함께 지정하면 된다. 원래 테이블의 속성 이름이 실제로 바뀌는 것은 아니다. 지정하는 이름에 공백이 포함되어 있으면 작은 따옴표나 큰 따옴표로 묶어주어야 한다. 그리고 AS 키워는 생략할 수 있다.

제품 테이블에서 제품명과 단가를 검색하되, 단가를 가격이라는 새 이름으로 출력하자.
SELECT name, price AS 가격 FROM product;

2. 산술식을 이용한 검색

SELECT 키워드와 함께 산술식을 제시할 수 있다. 산술식은 속성의 이름과 +, -, *, / 등의 산술 연산자와 상수로 구성한다.

제품 테이블에서 제품명과 단가 속성을 검색하되, 단가에 500원을 더해 조정단가라는 새 이름으로 출력해보자.

SELECT name, price + 500 AS 조정단가 FROM product;

3. 조건 검색

조건을 만족하는 데이터만 검색하는 SELECT 문의 기본 형식은 다음과 같다.

SELECT [ ALL | DISTINCT ] 속성_리스트 FROM 테이블_리스트 [ WHERE 조건 ];

WHERE 키워드와 함께 비교 연산자(=, <>, <, >, <=, >=)와 논리 연산자(AND, OR, NOT)를 이용하여 검색 조건을 제시한다.

비교 연산자를 이용해 문자나 날짜 값을 비교할 수 있다.

'A' < 'C' '2013-12-01' < '2013-12-02'

조건에서 숫자값은 그대로 작성해도 되지만 문자나 날짜 값은 속성의 이름과 구별할 수 있도록 작은 따옴표로 묶어야 한다.

논리 연산자는 조건을 여러 개 결합하거나 조건을 만족하지 않는 데이터를 검색하고자 할 때 이용한다.

제품 테이블에서 한빛제과가 제조한 제품의 제품명, 재고량, 단가를 검색하자.

SELECT name, stock, price FROM product WHERE manufacturer = '한빛제과';

주문 테이블에서 apple 고객이 15개 이상 주문한 주문제품, 수량, 주문일자를 검색해보자.

SELECT product, quantity, order_date FROM order_info WHERE customer = 'apple' AND quantity >= 15;

주문 테이블에서 apple 고객이 주문했거나 15개 이상 주문된 제품의 주문제품, 수량, 주문일자, 주문고객을 검색해보자.

SELECT product, quantity, order_date, customer FROM order_info WHERE customer = 'apple' OR quantity >= 15;

제품 테이블에서 단가가 2000원 이상이면서 3000원 이하인 제품의 제품명, 단가, 제조업체를 검색해보자.

SELECT name, price, manufacturer FROM product WHERE price >= 2000 AND price <= 3000;

4. LIKE를 이용한 검색

검색 조건을 정확히 몰라 부분적으로 일치하는 데이터를 검색하고 싶다면 LIKE 키워드를 사용한다. 검색 조건을 정확히 알면 = 연산자로 조건을 표현한다. 단 LIKE 키워드는 문자열을 이요하는 조건에만 사용할 수 있다. 

LIKE 키워드와 함께 사용할 수 있는 기호

• % : 0개 이상의 문자(문자의 내용과 개수는 상관없음)
• _ : 한 개의 문자(문자의 내용을 상관 없음)

LIKE 키워드의 사용 예

• LIKE '데이터%' : 데이터로 시작하는 문자열(데이터로 시작하기만 하면 길이는 상관 없음)
• LIKE '%데이터' : 데이터로 끝나는 문자열(데이터로 끝나기만 하면 길이는 상관 없음)
• LIKE '%데이터%' : 데이터가 포함된 문자열
• LIKE '데이터___' : 데이터로 시작하는 6자 길이의 문자열
• LIKE '__한%' : 세 번째 글자가 '한'인 문자열

고객 테이블에서 성이 김씨인 고객의 고객이름, 나이, 등급, 적립금을 검색해보자.

SELECT name, age, grade, saved_money FROM customer WHERE name LIKE '김%' ;

고객 테이블에서 고객아이디가 5자인 고객의 고객아이디, 고객이름, 등급을 검색해보자.

SELECT id, name, grade FROM customer WHERE id LIKE '_____';

[출처] 한빛아카데미 데이터베이스 개론 김현희지음

Mysql root 패스워드 분실한 경우 처리

기본으로 윈도우에 Mysql을 설치한 경우 다음의 위치에
각 버전에 대한 데이터 디렉토리가 생성된다.

C:\ProgramData\MySQL\MySQL Server 5.6

해당 디렉토리에서
my.ini 파일을 열어서
skip-grant-tables 구문을 추가한다.

이후 Mysql을 재구동 하면  패스워드를 물어보지 않는다.

다음으로 명령창을 열어서 다음과 같이 실행한다.

$mysql -uroot mysql
mysql> UPDATE user SET password=PASSWORD('변경할 비밀번호') WHERE user='root';

마지막으로 my.ini 파일의
skip-grant-table 구문을 제거하고 Mysql을 재구동한다.

SQL의 데이터 정의 기능

SQL의 데이터 정의 기능

1. 테이블 생성

CREATE TABLE

CREATE TABLE 테이블_이름 (
    속성_이름 데이터 타입 [NOT NULL] [DEFAULT 기본_값]
    [PRIMARY KEY (속성_리스트)]
    [UNIQUE (속성_리스트)]
    [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)]
    [ON DELETE 옵션] [ON UPDATE 옵션]
    [CONSTRAINT 이름] [CHECK(조건)]
);

참조무결성 제약조건
왜리키가 어떤 테이블의 무슨 속성을 참조하는지를 REFERENCES 키워드 다음에 명확히 제시하여 참조되는 테이블에서 투플을 함부로 삭제하거나 변경하지 못하게 한다.

참조되는 테이블에서 투플을 삭제할 때 처리하는 방법
1) ON DELETE NO ACTION(투플을 삭제하지 못하게 함)(디폴트)
2) ON DELETE CASCADE(관련 투플을 함께 삭제함)
3) ON DELETE SET NULL(관련 투플의 외래키 값을 NULL로 변경함)
4) ON DELETE SET DEFAULT(관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경함)

참조되는 테이블에서 투플을 변경할 때 처리하는 방법
1) ON UPDATE NO ACTION(투플을 변경하지 못하게 함)(디폴트)
2) ON UPDATE CASCADE(관련 투플에서 외래키 값을 함께 변경함)
3) ON UPDATE SET NULL(관련 투플의 외래키 값을 NULL로 변경함)
4) ON UPDATE SET DEFAULT(관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경함)

데이터 무결성 제약조건의 정의
CREATE TABLE 문으로 테이블을 정의할 때 CHECK 키워드를 사용해 특정 속성에 대한 제약조건을 지정한다. 테이블에 새로운 투플을 삽입하거나 기존 투플을 수정할 때도 이 제약조건을 반드시 지켜야 한다. 이는 테이블에 항상 정확하고 유효한 데이터를 유지하기 위해 데이터 무결성을 위한 제약조건을 표현하는 방법이다.

CHECK(재고량 >=0 AND 재고량 <=1000)
or CONSTRAINT CHK_CPY CHECK(제조업체 = '한빛제과')

고객 테이블
CREATE TABLE customer (
    id                VARCHAR(20) NOT NULL, /* 고객 아이디 */
    name           VARCHAR(10) NOT NULL, /* 고객 이름 */
    age              INT, /* 나이 */
    grade           VARCHAR(10) NOT NULL, /* 등급 */
    job              VARCHAR(20), /* 직업 */
    saved_money INT DEFAULT 0, /* 적립금 */
    PRIMARY KEY(id)
);

제품 테이블
CREATE TABLE product (
    id                CHAR(3) NOT NULL, /* 제품번호 */
    name           VARCHAR(20), /* 제품명 */
    stock           INT, /* 재고량 */
    price            INT, /* 단가 */
    manufacturer VARCHAR(20), /* 제조업체 */
    PRIMARY KEY(id),
    CHECK (stock >= 0 AND stock <= 10000)
);

주문 테이블
CREATE TABLE order_info (
    id            CHAR(3) NOT NULL, /* 주문번호 */
    customer   VARCHAR(20), /* 주문고객 */
    product     CHAR(3), /* 주문제품 */
    quantity     INT, /* 수량 */
    destination VARCHAR(30), /* 배송지 */
    order_date DATETIME, /* 주문일자 */
    PRIMARY KEY(id),
    FOREIGN KEY(customer) REFERENCES customer(id),
    FOREIGN KEY(product) REFERENCES product(id)
);
-- 오라클에서는 주문일자 속성의 데이터 타입은 DATE로 지정

2. 테이블 변경

ALTER TABLE 문을 이용해 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능하다.

새로운 속성의 추가

ALTER TABLE 테이블_이름 ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];
ex) ALTER TABLE customer ADD join_date DATETIME;

기존 속성의 삭제

ALTER TABLE 테이블_이름 DROP 속성_이름 CASECADE | RESTRICT;

ALTER TABLE 문을 작성할 때는 삭제할 속성과 관련된 제약조건이 존재하거나 이 속성을 참조하는 다른 속성이 존재하는 경우에 처리하는 방법을 선택할 수 있다. 관련된 제약조건이나 참조하는 다른 속성을 함께 삭제하기 위해 CASCADE를 지정하거나, 관련된 제약조건이나 참조하는 다른 속성이 존재하면 삭제가 수행되지 않도록 RESTRICT를 지정할 수 있다.
ex) ALTER TABLE customer DROP grade CASCADE;

새로운 제약조건의 추가

ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_내용
ex) ALTER TABLE customer ADD CONSTRAINT CHK_AGE CHECK(age >= 20);

기존 제약조건의 삭제

ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름;
ex) ALTER TABLE customer DROP CONSTRAINT CHK_AGE;

3. 테이블 제거

DROP TABLE 테이블_이름 CASECADE | RESTRICT;

DROP TABLE 문을 작성할 때 삭제할 테이블을 참조하는 다른 테이블도 함께 삭제하려면 CASCADE를 지정한다. 반대로 삭제할 테이블을 참조하는 테이블이 있으면 삭제가 수행되지 않도록 하려면 RESTRICT를 지정한다.

ex) DROP TABLE customer RESTRICT;