230116 Acorn_SQL

kangjuju·2023년 1월 16일
ConstraintDDLDMLDatabaseHeidiSQLMariaDBUNIGUEdeleteprimary keyselectupdate정규화
0

Acorn

목록 보기
4/15
post-thumbnail


reference :

DataBase Management System

데이터베이스라는 데이터의 집합을 만들고, 저장 및 관리할 수 있는 기능들을 제공하는 응용 프로그램. 구조적으로 정보를 저장하고 원활하게 삽입 삭제 갱신작업을 할 수 있다.

정규화

기본적으로 높은 차수의 정규형은 낮은 차수의 정규형을 모두 만족하여야 한다. 즉, 제3정규형은 제1,2 정규형의 조건을 모두 만족하여야 하며, BCNF는 1,2,3 정규형을 기본적으로 모두 만족하고 있어야 한다

reference : https://gomcine.tistory.com/entry/Database-9-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EC%A0%95%EA%B7%9C%ED%99%94-%EA%B0%9C%EB%85%90-%EB%B0%8F-%EB%B0%A9%EB%B2%95

CMD로 MariaDB접근하기 tutorial

sql명 / use / 호스트 아이피 / 패스워드

MariaDB [(none)]> show databases;
+--------------------+
| Database       	   |
+--------------------+
| information_schema |
| mysql          	   	    |
| performance_schema |
| sys             			  |
+--------------------+
  • 프로그래밍 언어와 같이 명령어작성 후 세미콜론을 써야한다.
    show databases로 현재 메타데이터를 확인한모습.
drop / create database test;
  • DDL 명령어로 데이터베이스를 만들고 삭제함
> use test;
Database changed
show tables;

  • use로 test 데이터베이스에 접근함. 어떤 테이블이 있는지 확인.

  • 나갈땐 exit;로 빠져나와야 데이터 손실을 예방할 수 있다.

  • TABLE제작. 속성을 부여함

create table abc(no int,name varchar(10));

CREATE TABLE DEF(BUNHO INT(5),IRUM CHAR(10))ROW_FORMAT=COMPRESSED;

COMPRESSED는 압축

 INSERT INTO ABC(NO,NAME) VALUES(1,'신기해');
 INSERT INTO ABC(NO,NAME) VALUES('2','신기루');
DELETE FROM ABC WHERE NO = 2;

--- 사용자 계정 만들기 ---

cafe 63

Maria DB 자료형

String 형은 CHAR / VARCHAR 를 주로 사용한다.
VARCHAR는 동적이기때문에 메모리 절약차원에서는 좋다.
다만 CHAR가 처리속도가 더빠른편이다.

이미지나 사운드는 BLOB, 많은 자료는 TEXT,
매우 많은 자료를 다룰땐 CLOB를 사용한다.

HeidiSQL 사용해보기

INSERT

테이블 생성후 INSERT문의 여러 사용법

--테이블 생성
CREATE TABLE aa(bun INT, irum CHAR(10),inwon INT(5),juso VARCHAR(20));

--테이블 정보
DESCRIBE aa;  
DESC aa;

SHOW TABLE STATUS;
--자료추가
/*
--INSERT INTO 테이블명(칼럼명,..) VALUES(입력자료,...)
*/
INSERT INTO aa(bun,irum,inwon,juso) VALUES(1,'인사과',5,'강남구 역삼동 12번지');
INSERT INTO aa VALUES(2,'영업과',12,'강남구 역삼동 12번지');
INSERT INTO aa(bun,irum) VALUES(3,'자제과');
INSERT INTO aa(inwon,juso,bun,irum) VALUES(10,'서초구 서초1동 1',4,'총무과');

SELECT * FROM aa;

DELETE FROM aa WHERE(irum = '자제과');

SQL문은 양식에 맞지않게 순서를 어긴다거나 지정 크기보다 길게 입력하거나 제대로 지정하지않고 데이터를 작성하려 하면 에러가 발생한다.

INSERT INTO AA VALUES('자제2과',5); --ERR
INSERT INTO AA(BUN,IRUM) VALUES('자제2과',5); --ERR
INSERT INTO AA(BUN,IRUM) VALUES('기분좋은 일만 생기는 멋진과',5); --ERR

UPDATE

UPDATE 테이블 명 SET 칼럼명1 = 수정값1, .... WHERE 조건

DELETE

DELETE FROM 테이블명 WHERE 조건

SELECT * FROM AA;
DELETE FROM AA WHERE BUN = 4;
DELETE FROM AA; 
TRUNCATE TABLE AA; -- WHERE로 일부삭제가 불가
DROP TABLE AA;  -- 구조를 포함한 테이블 전체 삭제

DELETE FROM AA; 는 모든 레코드를 삭제한다. 구조(열)만 남게되고
로그를 남기기때문에 TRUNCATE에 비해 속도가 비교적 떨어진다.

제약조건 CONSTRAINT

  • 무결성 제약 조건 : 잘못된 데이터 입력 방지를 위한 다양한 제한 조건을 줄 수 있다.--
  • DOMAIN 제약조건 : 테이블 생성 시 각 컬럼의 이름과 성격, 크기, NULL 허용여부
  • 기본키 제약조건 : PRIMARY KEY [PK]
  • 사용자 정의 제약조건 : CHECK, UNIQUE, DEFAULT, FOREIGN KEY[FK]

기본키 제약 조건 : PRIMARY KEY

중복 레코드 방지. 특정 컬럼에 적용.
해당칼럼은 NULL을 허용하지 않게됨

CREATE TABLE AA(BUN INT PRIMARY KEY,IRUM CHAR(10));

DESC AA;

INSERT INTO AA VALUES(1,'AA');
INSERT INTO AA VALUES(1,'BB'); -- PK ERR
INSERT INTO aa(BUN) VALUES(2);

SELECT * FROM AA;

INSERT INTO BB(BUN) VALUES(1); -- IRUM 컬럼에는 자료를 반드시 입력해야함 NOT NULL

제약조건 수정

-- alter table 테이블명 add constraint 제약조건명 제약조건(대상컬럼명) 
-- alter table 테이블명 add constraint 제약조건명

ALTER TABLE aa ADD CONSTRAINT ck_name CHECK(irum IN('abc','mbc'));

이름은 abc와 mbc만 넣을 수 있다는 제약조건을 추가함

check 제약조건 : 입력되는 자료의 특정 컬럼값 검사

CREATE TABLE aa(bun INT , irum VARCHAR(10), nai INT CHECK(nai >= 20));
INSERT INTO aa VALUES(1,'tom',23);
INSERT INTO aa VALUES(2,'tom2',13); --err

입력 나이가 20세 이상만 허용함.

UNIGUE 제약조건 : 특정 칼럼의 동일값 불허 

CREATE TABLE aa(BUN INT PRIMARY KEY, IRUM CHAR(10) UNIQUE);
DESC aa;

INSERT INTO aa VALUES(1,'AA');
INSERT INTO aa VALUES(3,'BB');
INSERT INTO aa VALUES(2,'CC');
INSERT INTO aa VALUES(4,'BB'); -- UNIQUE ERR
INSERT INTO aa VALUES(3,'DD');  -- PK ERR

/* SQL 오류 (1062): Duplicate entry 'BB' for key 'IRUM' */

이름 칼럼은 UNIQUE이기때문에 동일한 BB를 입력할 수 없었다.

default : 특정칼럼에 초기치를 부여

CREATE TABLE CC(BUN INT PRIMARY KEY, IRUM CHAR(10) NOT NULL, JUSO VARCHAR(20) DEFAULT '역삼1동');

ALTER TABLE CC ALTER COLUMN JUSO SET DEFAULT '역삼1동';

INSERT INTO CC(BUN,IRUM) VALUES(2,'OSCAR');

CREATE 또는 ALTER에서 DEFAULT값을 줄 수 있다. 위 INSERT문에서 주소를 지정하지 않았지만 DEFAULT값으로 기본값이 들어가있는걸 확인했다.

AUTO_INCREMENT 특정 INT컬럼이 자동으로 증가

CREATE TABLE DD(BUN INT AUTO_INCREMENT PRIMARY KEY,IRUM CHAR(10) NOT NULL);
또는
ALTER TABLE DD AUTO_INCREMENT = 1;

INSERT INTO DD(IRUM) VALUES('JAMES');
INSERT INTO DD(IRUM) VALUES('TOM');
..
.

ROW가 증가할때마다 지정한 BUN변수도 함께 증가한다. 아예 지정한적이 없거나 NULL, 0으로 지정하더라도 BUN변수는 작성순서대로 번호가 붙는다.
만약 1,2,3을 넣다가 6을 넣으면 7부터 증가한다.

명령어는 툴마다 다를 수 있다.

SET @@AUTO_INCREMENT = 3;

번외로 SET으로 N씩 증가하도록 지정할 수도 있다.

FOREIGN KEY[FK] : 다른테이블의 컬럼값을 참조.

외래키는 두 테이블을 서로 연결하는 데 사용되는 키이다.
외래키가 포함된 테이블을 자식 테이블이라고 하고 외래키 값을 제공하는 테이블을 부모 테이블이라한다.

외래키 사용시 주의 사항

  • 외래키 값은 NULL이거나 부모 테이블의 기본키 값과 동일해야한다. (참조 무결성 제약조건)

  • 부모 테이블의 기본키, 고유키를 외래키로 지정할 수 있다.

  • 부모 테이블의 기본키, 고유키가 여러개의 컬럼으로 이루어져 있다면 부모가 가진 기본키, 고유키 컬럼을 원하는 개수만큼 묶어서 외래키로 지정할 수 있다.

  • 컬럼명이 일치할 필요는 없지만 동일한 성격을 지닌 컬럼, 기본키와 역할이 같은 컬럼이 외래키의 대상으로 적절하다

<부모테이블>
CREATE TABLE SAWON(
BUN INT PRIMARY KEY,
IRUM VARCHAR(10) NOT NULL,
BUSER CHAR(10)
);

<자식테이블>
CREATE TABLE GAJOK(
CODE INT PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
BIRTH DATETIME,
SAWON_BUN INT,
FOREIGN KEY(SAWON_BUN) -- FK
REFERENCES SAWON (BUN) -- PK
);

자식테이블에서 FOREIGN KEY(필드) REFERENCES 부모테이블(필드)
형태로 선언하여 두 테이블간의 연결고리를 만들었다.

ALTER로도 외래키를 사용할 수 있다.

ALTER TABLE 테이블이름
ADD [CONSTRAINT 제약조건이름]
FOREIGN KEY (필드이름)
REFERENCES 부모테이블이름 (필드이름)

실습

CREATE TABLE SAWON(
BUN INT PRIMARY KEY,
IRUM VARCHAR(10) NOT NULL,
BUSER CHAR(10)
);

CREATE TABLE GAJOK(
CODE INT,
NAME VARCHAR(10) NOT NULL,
BIRTH DATETIME,
SAWON_BUN INT,
PRIMARY KEY(CODE),
FOREIGN KEY(SAWON_BUN) -- FK
REFERENCES SAWON (BUN) -- PK
);

1> INSERT INTO GAJOK VALUES(103,'신선해','2000-2-12',4); -- FK ERR

2> DELETE FROM SAWON WHERE BUN = 1; -- ERR

1> 참조할때 부모테이블에 없는 외래키는 넣을 수 없다.

2> 부모에서 행을 지우려해도 지워지지 않았다.
이미 자식에서 해당 행을 참조중이기 때문이였는데 이는 관련된 자식 행을 먼저 지우고
부모에서 지우면 가능하다.
ON DELETE CASCADE 와 같은 옵션을 이용 할 수도 있다.

이는 DROP TABLE 에서도 똑같이 적용된다. 자식을 지워야 지울 수 있음

옵션들

1) On Delete

Cascade : 부모 데이터 삭제 시 자식 데이터도 삭제

Set null : 부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Null로 업데이트

Set default : 부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트

Restrict : 자식 테이블이 참조하고 있을 경우, 데이터 삭제 불가

No Action : Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.

2) On Update

Cascade : 부모 데이터 업데이트 시 자식 데이터도 업데이트

Set null : 부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Null로 업데이트

Set default : 부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트

Restrict : 자식 테이블이 참조하고 있을 경우, 업데이트 불가

No Action : Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.

profile
kangjuju
이전 포스트

230116 java 메모장

0개의 댓글