[DB] 데이터베이스 설계 및 관리 1

박소미·2024년 4월 25일
db
0

DB

목록 보기
13/14

📘2024.03.14 (2주차)

용어 정리

  • ⭐DBMS⭐ (Database Management System) : 사용자와 데이터베이스를 연결시켜 주는 소프트웨어로 주 기억장치에 상주한다.

  • SQL (Structured Query Language) : 데이터베이스 전용 언어이며 DDL, DCL, DML로 구성된다.

  • 데이터 정의어 DDL (Data Definition Language) : CREATE, ARTER, RENAME, DROP

  • 데이터 제어어 DCL (Data Control Language) : GRANT, REVORKE

  • 데이터 조작어 DML (Data Manipulation Language) : SELETE, UPDATE, INSERT, DELETE

  • 데이터베이스 관리자 DBA (Database Administrator)

  • 상표들의 예시: Oracle, Mysql, MongoDB, MariaDB

Command명령어

데이터베이스 만들기:
CREATE DATABASE 데이터베이스 이름;

  • 프로젝트 당 데이터베이스는 1개, 이 데이터베이스에 여러 테이블을 만들어서 사용하는 것

데이터베이스 확인 (다른 사람이 만들었는지 확인하여 중복을 방지):
SHOW DATABASES;

테이블 확인:
SHOW tables;

데이터베이스 사용하기:
USE 데이터베이스 이름;

테이블 스키마 확인 (테이블의 컬럼 이름, 데이터 타입, NULL 허용 여부 등의 정보를 확인) :
DESC 테이블 이름;


📘2024.03.21 (3주차)

데이터 입력

작은 따옴표 사용 (')

  • CHAR: 고정 길이 문자열을 저장하는 데 사용한다.

    • ex) CHAR(10)은 10자리의 고정 길이 문자열을 저장한다.

  • VARCHAR: 가변 길이 문자열을 저장하는 데 사용한다.

    • ex) VARCHAR(255)는 최대 255자까지의 가변 길이 문자열을 저장한다.

  • DATE: 날짜 정보를 저장하는 데 사용한다.

    • ex) 'YYYY-MM-DD' 형식으로 값을 저장한다.

  • DATETIME: 날짜와 시간 정보를 저장하는 데 사용한다.

    • ex) 'YYYY-MM-DD HH:MM:SS' 형식으로 값을 저장한다.

  • TIME: 시간 정보를 저장하는 데 사용한다.

    • ex) 'HH:MM:SS' 형식으로 값을 저장한다.

  • TIMESTAMP: 날짜와 시간 정보를 저장하는 데 사용되며, 자동으로 시간이 갱신된다.

  • BLOB: 이진 데이터 (Binary Large Object)를 저장하는 데 사용한다.

    • ex) 이미지, 동영상 등의 바이너리 데이터를 저장할 때 사용한다.

작은 따옴표 불필요

  • DEC: 고정 소수점 숫자를 저장하는 데 사용한다.

    • ex) DECIMAL(10, 2)는 총 10자리 중 소수점 2자리까지의 고정 소수점 숫자를 저장한다.

  • INT: 정수 숫자를 저장하는 데 사용한다.

    • ex) 32비트 또는 64비트 정수를 저장할 수 있다.

📌Char과 Varchar의 차이점
Char은 고정적이다. Char(8) = 글자 하나를 넣어도 8바이트 공간을 차지한다.
하지만 Varchar은 가변적이다. Varchar (8) = 글자 하나를 넣었을 때 1바이트만 차지한다.

📌AUTO_INCREMENT

  • 해당 컬럼의 값을 자동으로 1씩 증가시키며 유니크한 값을 생성하는 특징을 가지고 있다.

  • AUTO_INCREMENT는 주로 기본키(primary key) 또는 유니크 키(unique key)와 함께 사용되며, 데이터베이스에 새로운 레코드를 추가할 때 자동으로 고유한 식별자를 생성하는 데 사용된다.

  • AUTO_INCREMENT로 설정된 컬럼은 NULL 값을 허용하지 않는다. 즉, 새로운 레코드를 추가할 때 해당 컬럼에 값을 지정하지 않으면, 데이터베이스가 자동으로 다음 값을 할당한다.

  • AUTO_INCREMENT는 컬럼을 INSERT 하지 않고 자동 증가시킨다.

  • AUTO_INCREMENT를 사용하던 중 중간에 삭제 되었다면 그냥 무시하고 계속 증가시킴 (하나씩 땡겨 오지 않음)

  • AUTO_INCREMENT를 쓰면 무조건 PRIMARY

  • AUTO_INCREMENT를 없애고 싶다면 DROP 후 PRIMARY를 지워야한다.


📘2024.03.28 (4주차)

키 (key)

관계 데이터베이스에서 키는 릴레이션에서 특정 튜플을 식별할 때 사용하는 속성 혹은 속성의 집합이다.

  • 슈퍼키: 튜플을 유일하게 식별할 수 있는 하나의 속성 혹은 속성의 집합이다.
    ex) (고객번호), (고객번호, 이름) 고객번호는 슈퍼키가 될 수 있는데 이름은 중복이 될 가능성이 있어 슈퍼키가 될 수 없다. 하지만 고객번호와 이름을 묶은 집합은 슈퍼키가 될 수 있다.

  • 후보키: 튜플을 유일하게 식별할 수 있는 속성의 최소 집합이다. 즉 슈퍼키처럼 (고객번호, 이름) 이 집합은 후보키가 될 수 없다. (고객번호)만 가능하다.

  • 기본키: 여러 후보키 중 하나를 선정하여 대표로 삼는 키를 말한다.
    기본키 제약조건: 고유한 값, NULL 값 허용 불가, 키 값의 변동이 일어나지 않아야 함

  • 대리키, 인조키, 인공키 : 마땅한 기본키가 없을 때 일련번호 같은 가상의 속성을 만들어 기본키로 삼는다.

  • 대체키: 기본키로 선정되지 않은 후보키이다.

  • 외래키: 다른 릴레이션의 기본키를 참조하는 속성이다. 외래키가 성립하기 위해서는 참조하고 참조되는 양쪽 릴레이션의 도메인이 서로 같아야 한다. 참조하는 외래키 값이 참조되는 기본키 값에 연동된다는 의미로 외래키는 항상 데이터의 일관성을 유지해야 한다.

    외래키 특징: 참조되는(기본키) 값이 변경되면 참조하는(외래키) 값도 변경된다. NULL값과 중복 값 등이 허용된다. 자기 자신의 기본키를 참조하는 외래키도 가능하다. 외래키가 기본키의 일부가 될 수 있다. (자기자신 참조)

개체 무결성 제약조건

데이터 무결성은 데이터베이스에 저장된 데이터의 일관성과 정확성을 지키는 것이다.

도메인 무결성 제약조건
릴레이션 내의 튜플들이 각 속성의 도메인에 지정된 값만 가져야 한다는 조건이다. 즉 도메인 무결성 제약조건은 속성 값과 관련된 무결성으로 SQL문에서 데이터 형식, 널, 기본 값, 체크 등을 사용하여 지정할 수 있다.

ex) 주문 릴레이션에서 주문일자는 날짜 데이터만 사용해야 하는데 영문자 등의 다른 자료형을 사용하면 자료 저장 및 검색에 어려움을 준다.

개체 무결성 제약조건 (기본키 제약)
개체 무결성 제약조건은 참조 무결성 제약조건과 함께 관계 데이터 모델의 핵심 개념이다. 기본키가 NULL 값 없이 유일한 값을 가지면 개체 무결성 제약조건을 지키고 있는 것이다.

DBMS는 튜플을 삽입하거나 수정할 때마다 개체 무결성 제약조건을 지키는지 확인한다. 즉 기본키 값이 유일한지 NULL값이 아닌지를 검사한다. 조건에 맞지 않다면 삽입 연산을 거부하고 오류 메시지를 보낸다.

참조 무결성 제약조건 (외래키 제약)
자식 릴레이션의 외래키는 부모 릴레이션의 기본키와 도메인이 동일해야 하며 자식 릴레이션의 값이 변경될 때 부모 릴레이션의 제약을 받는다. 즉 부모 릴레이션의 도메인과 다른 값으로 삽입, 수정될 경우 거부되고 반대로 자식 릴레이션에서 참조하고 있는 부모 릴레이션에서 삭제하거나 다른 값으로 변경하려고 하면 거부된다.

무결성 제약조건의 수행
제약조건의 준수여부는 데이터의 변경(삽입, 수정, 삭제)이 있을 때마다 확인해야 한다.

삽입: 기본키 값이 같으면 삽입 금지

수정: 기본키 값이 같거나 NULL로 변경할 경우 수정 금지

삭제: 부모 릴레이션에서 튜플을 삭제할 경우 참조 무결성 조건을 수행하기 위한 고려사항, 특별한 확인이 필요하지 않으면 즉시 수행

CREATE 문

<CREATE TABLE 테이블 이름(
{
속성이름 데이터 타입
[NOT NULL]
[UNIQUE]
[DEFAULT 기본값]
[CHECK 체크조건]
}

[PRIMARY KEY 속성이름(들)]
{[CONSTRAINT symbol]
	FOREIGN KEY 속성이름 REFERENCES 테이블 이름(속성이름)]
    [ON UPDATE [NO ACTION | CASCADE | SET NULL | SET DEFAULT]]
    [ON DELETE [NO ACTION | CASCADE | SET NULL | SET DEFAULT]]
    }
}

참조 키가 있는 테이블의 생성

CREATE TABLE interests(
	int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    interest VARCHAR(50) NOT NULL,
    con_id INT NOT NULL,
    CONSTRAINT my_contacts_con_id_fk
    FOREIGN KEY (con_id)
    REFERENCES my_contacts (contact_id)
);

ALTER 문

ALTER TABLE 테이블 이름1
	[RENAME 테이블 이름1 TO 테이블 이름2  //테이블 1 이름을 테이블 2 이름으로 바꾸겠다.
    [ADD 속성이름 데이터타입]
    [DROP COLUMN 속성이름]
    [MODIFY COLUMN 속성이름 데이터 타입]
    [CHANGE COLUMN 속성이름1 속성이름 2  데이터 타입 [NULL | NOT NULL]
    [ADD PRIMARY KEY 속성이름]

ALTER 문 예시

Q1. NewBook테이블에 varchar(13) 자료형을 갖는 isbn 속성을 추가해라.
A1. ALTER TABLE NewBook
	ADD isbn varchar(13);
    
Q2. NewBook테이블에 isbn 속성의 데이터 타입을 INTEGER로 수정해라.
A2. ALTER TABLE NewBook
	MODIFY COLUMN isbn INTEGER;    
    
Q3. project_list의 start_date 컬럼을 삭제해라.
A3. ALTER TABLE project_list
	DROP COLUMN start_date;
    
Q4. NewBook 테이블의 bookid 속성을 기본키로 변경하시오
A4. ALTER TABLE NewBook 
	ADD PRIMARY KEY(bookid);

기본키 삭제하기

ALTER TABLE NewBook
	CHANGE bookid1 bookid2 INTEGER NOT NULL,
    DROP PRIMARY KEY(bookid);   (x) 잘못된 문법
    
ALTER TABLE NewBook
	CHANGE bookid1 bookid2 INTEGER NOT NULL,
    DROP PRIMARY KEY;   (o)

DROP 문

DROP TABLE 테이블 이름

📘2024.04.04 (5주차)

INSERT 문

INSERT INTO 테이블 이름 [(속성리스트)]
	   VALUES (값리스트);

대량 삽입
한꺼번에 여러 개의 튜플을 삽입한다.

INSERT INTO Book(bookid, bookname,price, publisher)
	SELECT bookid, bookname, price, publisher
    FROM Imported_book;

📘2024.04.11 (6주차)

UPADET 문

특정 속성 값을 수정하는 명령

UPDATE 테이블 이름
SET 속성이름1=값1, [속성이름2 = 값2, '''] 
[WHERE <검색조건>];
Q1. Customer 테이블에서 고객번호가 5인 고객의 주소를 '대한민국 부산'으로 변경해라.
A1. UPDATE Customer
	SET address = '대한민국 부산'
    WHERE custid = 5; 
    
Q2. Customer 테이블에서 박세리 고객의 주소를 김연아 고객의 주소로 변경해라.
A2. UPDATE Customer
	SET address = (SELECT address
    				FROM Customer
                    WHERE name = '김연아')
    WHERE name = '박세리';

나열은 콤마로만 해야한다.

DELETE문

테이블에 있는 기존 튜플을 삭제하는 명령

DELETE
FROM 테이블 이름
[WHERE 검색조건]

스키마는 살아 있고 안에 데이터들은 삭제된다.

CREATE 반대 DROP

구조도 없앨 때는 DROP

INSERT 반대 DELETE

SELECT 문

SELECT 문의 구성요소

SELECT 속성이름
FROM 테이블 이름
WHERE 검색 조건

SELECT 문의 기본 문법

SELECT [ALL/DISTINCT] 속성이름(들)
[FROM 테이블이름(들)]
[WHERE 검색조건(들)]
[GROUP BY 속성이름]
[HAVING 검색조건(들)]
[ORDER BY 속성이름 [ASC | DESC]]

📍오라클에서는 FROM이 필수

SELECT 1+1;
=> 2라고 계산해 줌

SELECT substring_index("태평양, 대서양, 인도양", ",", 2) ;
=> 태평양, 대서양

SELECT substring_index("태평양, 대서양, 인도양", ",", -1);
=> 인도양

📍 MY SQL에서만 가능 ORACLE은 불가능

⭐ 중복을 제거하고 싶으면 DISTINCT 라는 키워드를 사용한다. DISTINCT는 함수가 아닌 키워드이다.

SELECT DISTINCT publisher
FROM Book;

⭐WHERE 조건

IN은 OR 역할

SELECT 예제

Q1. 가격이 20,000원 미만인 도서를 검색하시오
A1. SELECT *
	FROM Book
    WHERE price < 20000;
    
Q2. 가격이 10,000원 이상 20,000 이하인 도서를 검색하시오
A2. SELECT * 
	FROM Book
    WHERE price BETWEEN 10000 AND 20000;
    
Q3. 축구의 역사를 출간한 출판사를 검색하시오
A3. SELECT bookname, publisher
	FROM Book
    WHERE publisher = '축구의 역사';

Q4. 도서이름에 '축구'가 포함된 출판사를 검색하시오
A4. SELECT bookname, publisher
	FROM Book
    WHERE bookname LIKE '%축구%';
    
Q5. 축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색하시오.
A5. SELECT * 
	FROM Book
    WHERE bookname LIKE '%축구%' AND price >= 20000;

Q6. 도서를 이름순으로 검색하시오.
A6. SELECT * 
	FROM Book
    ORDER BY bookname;

📘2024.04.18 (7주차)

집계 함수와 GROUP BY
📍 집계함수는 SELECT와 함께 쓴다.

집계함수 5가지:SUM, AVG, MAX, MIN, COUNT

별칭을 사용하여 열 이름을 부여하고 싶다면 AS를 사용한다.

WHERE절에 집계함수를 쓰고 싶다면 서브쿼리 SELECT를 작성해야 한다.

GROUP BY로 튜플을 그룹으로 묶은 후 SELECT 절에는 GROUP BY에서 사용한 속성과 집계함수만 나올 수 있다.

~별로 : 그룹핑해서 보여주는 것

HAVING
1) HAVING은 GROUP BY 밑에 있고 GROUP BY 없이 쓸 수 없다.
2) GROUP BY한 결과에 대해 다시 조건을 걸 때 HAVING 사용

📌GROUP BY에 SELECT에 없는 컬럼 써도 컴파일할 때 에러는 나지 않지만 원래는 에러

두 개 이상 테이블에서 SQL 질의

(FROM에 테이블 3개) 테이블 3개있는 상태에서 WHERE 절에 쓰려면?
WHERE 절에 = 이 2개 있어야함 무조건 엮어줘야함

Q1. 고객과 고객의 주문에 관한 데이터를 모두 보이시오.
A1. SELECT *
    FROM customer, orders
    WHERE customer.custid = orders.custid;
    
Q2. 고객과 고객의 주문에 관한 데이터를 고객별로 정렬하여 보이시오.
A2. SELECT *
    FROM customer, orders
    WHERE customer.custid = orders.custid;
    ORDER BY customer.custid;
    
Q3. 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오.
A3. SELECT name, SUM(saleprice)
    FROM Customer, Orders
    WHERE Customer.custid = Orders.custid
    GROUP BY Customer.name 
    ORDER BY  Customer.name;

📌ORDER BY와 GROUP BY의 차이점
ORDER BY는 결과 집합의 행을 특정 컬럼의 값에 따라 정렬하기 위해 사용한다. GROUP BY는 집계함수를 사용하여 여러 행의 데이터를 그룹화하고 각 그룹에 대한 하나의 결과 행을 생성할 때 사용된다. 고객별로 주문 건수나 주문 총액과 같은 통계 계산을 하려면 GROUP BY를 사용해야 한다.
ex) Q2와 Q3

외부 데이터 입력

LOAD DATA LOCAL INFILE 'D:/datafile/customer_data.txt'
	INTO TABLE customer
    	columns terminated by '|'
        lines terminated by '\n';

대용량 데이터 입력 (10만건 이상)

BULK INSERT TABLENAME FROM 'D:\filename.txt'
	WITH
    	(FIRSTROW = n,
        FIELDTERMINATOR = '',
        ROWTERMINATOR = '\n');

profile
박소미
이전 포스트

[DB] ch12 트랜잭션과 병행수행 제어

다음 포스트

[DB] 데이터베이스 설계 및 관리 2

0개의 댓글