[SQLD]SQL 기본 및 활용(1)

DDL(Data Definition Language)

테이블과 같은 데이터 구조를 정의하는데 사용하는 명령어들

Oracle에서는 DDL명령후 자동 커밋을 수행하지만, MYSQL에서는 직접 커밋해주어야 한다.

 

1) CREATE

테이블 생성

테이블 이름 명명 조건 : 테이블과 컬럼명은 무조건 문자로 시작해야하고 특수문자는 " _ $ # "만 사용 가능

CREATE table 테이블이름(
	컬럼명 데이터타입 조건,
	컬럼명 데이터타입 조건,
      . . .
)

데이터타입	의미
CHAR	특정 문자열 개수 지정 ex) char(10)
VARCHAR	가변 문자열 지정 (현재 오라클 사용 안함)
VARCHAR2	가변 문자열 지정 (MYSQL, MariaDB지원 안함)
NUMBER	숫자 저장(정수, 실수) (MYSQL, MariaDB은 INT)
DATE	날짜에 사용하는 데이터 타입

제약조건	의미
NOT NULL	Null을 허용하지 않음!
UNIQUE	중복 불가! (null은 가능!)
PRIMARY KEY	기본키로 지정한다는 뜻으로 테이블 당 하나만 가능
FOREGIN KEY	외래키로 지정한다는 뜻으로 references 키워드와 같이 쓰임
CHECK	컬럼에 입력되는 데이터를 체크해 특정 조건에 맞는 데이터만 입력 받음
DEFAULT	만약 값이 없으면 디폴트 값을 부여

참조동작	의미
RESTRICT	개체를 변경/삭제 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 취소(제한한다)
CASCADE	개체를 변경/삭제 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 함께 변경/삭제
NO ACTION	MySQL에서는 RESTRICT와 동일
SET NULL	개체를 변경/삭제 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 참조하고 있는 값을 NULL로 세팅
AUTOMATIC	개체를 추가할 때 해당 테이블이 다른 테이블를 참조하고 있을 경우 참조하고 있는 값을 자동적으로 추가
DEPENDENT	개체를 추가할 때 해당 테이블이 다른 테이블를 참조하고 있을 경우 추가 불가

 

 

ex) Primary key

CREATE TABLE Student(
	ID VARCHAR(30) PRIMARY KEY,
    NAME VARCHAR(30),
    AGE NUMBER(10)
);

 

 

ex) Foregin key

CREATE TABLE Student(
	ID VARCHAR(30) PRIMARY KEY,
    NAME VARCHAR(30),
    AGE NUMBER(10),
    CONSTRAINT FK_ID FOREIGN KEY(NAME) REFERENCES NAMEINFO(NAME)
);

 

 

ex) Check

CREATE TABLE Student(
	ID VARCHAR(30),
    NAME VARCHAR(30),
    AGE NUMBER(10) CHECK(AGE IN(10,20,30)),
);

 

ex) Default

CREATE TABLE Student(
	ID VARCHAR(30),
    NAME VARCHAR(30) DEFAULT "JINNIE",
    AGE NUMBER(10)
);

주의!  만약, 직접 값을 Null로 지정한다면 디폴트 값으로 지정되지 않고, Null이 들어가게 된다.

 

 

 

인덱스 생성

데이터베이스로부터 매우 빠르게 데이터를 생성하고 검색하기 위해 사용

인덱스는 테이블의 단일 컬럼 또는 컬럼의 그룹을 사용함으로써 생성 가능하고, 인덱스가 생성되면 데이터를 정렬하기 전에 각 행마다 ROWID가 할당된다.

CREATE INDEX 인덱스테이블 ON 테이블(컬럼1, 컬럼2, ... );

 

 

 

 

 

---

2) ALTER

컬럼 추가

ALTER TABLE 테이블명 ADD 컬럼명 타입;

ALTER TABLE STUDENT ADD ADDRESS VARCHAR2(30) NOT NULL;

 

컬럼 타입 변경 > 이전에 있던 제약 조건도 사라질 수 있으니 주의! (똑같이 다시 지정해줘야함)

Oracle

ALTER TABLE 테이블명 MODIFY (컬럼명1 타입1 [DEFAULT] [NOT NULL], . . .);

ALTER TABLE STUDENT MODIFY NAME VARCHAR2(100);

MySQL 여러 컬럼 동시에 변경 불가

ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 타입 [DEFAULT] [NOT NULL];

ALTER TABLE STUDENT ALTER COLUMN NAME VARCHAR2(100) NOT NULL;

 

컬럼 삭제

ALTER TABLE 테이블명 DROP 컬럼명;

ALTER TABLE STUDENT DROP NAME;

 

PRIMARY KEY 지정하기

ALTER TABLE STUDENT ADD PRIMARY KEY(ID);

ALTER TABLE STUDENT ADD CONSTRAINT student_pk PRIMARY KEY(ID);

 

 

---

3) RENAME

RENAME 테이블명 TO 새테이블명;

 

 

---

4) DROP

storage와 데이터 모두 삭제

DROP TABLE 테이블명;

 

 

---

5) TRUNCATE

최초 생성되었을 때의 storage만 남기고 데이터는 모두 삭제

TRUNCATE TABLE 테이블명;

 

 

 

 

 

---

TCL(Transcation Control Language)

1) COMMIT

문제가 없다고 판단하여 데이터를 데이터베이스에 반영시킴

COMMIT;

 

 

---

2) ROLLBACK

COMMIT이전의 상태로 돌아감

ROLLBACK;

 

---

3) SAVEPOINT

ROLLBACK이 가능한 저장점을 직접 지정할 때, 사용하는 명령어

Oracle

SAVEPOINT SVPT1;
ROLLBACK TO SVPT1;

MySQL

SAFE TRANSACTION SVTR1;
ROLLBACK TRANSACTION SVTR1;

 

 

 

 

 

 

 

---

DML

1) INSERT

INSERT INTO 테이블명 VALUES(모든 컬럼 등록);

INSERT INTO 테이블명(컬럼1, 컬럼2) VALUES(컬럼1값, 컬럼2값);

 

 

---

2) DELETE

삭제 데이터에 대한 로그가 남으며 storage도 남는다.

DELETE FROM 테이블명 where 조건;

 

---

3) UPDATE

UPDATE 테이블명 SET 컬럼명 = 값 where 조건;

 

 

 

---

4) SELECT

실행 작동 순서 

FROM >> WHERE >> GROUP BY >> HAVING >> SELECT >> ORDER BY

SELECT 컬럼 FROM 테이블 WHERE 조건;

조건	의미
BETWEEN a AND b	a와 b값을 포함한 그 사이값
LIKE '비교문자열'	비교문자열과 형태가 일치한다(% , _ 사용)
IN (list)	리스트 내 값중 어느 하나와 일치
NOT	해당되지 않음 ex) WHERE NOT user_id = 1;
AND, OR	연산자 우선순위가 and에게 있다.
IS NULL	널인 경우
IS NOT NULL	널이 아닌 경우

연산자 우선순위

산술연산자 > 연결연산자(||) > 비교연산자 > IS NULL, LIKE ,IN > BETWEEN > NOT > AND > OR

 

---

내장함수(Built-in Function)

- 입력 행수에 따라 단일행 함수, 다중행 함수로 나뉜다.

- SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용 가능

- 단 하나의 결과만 리턴

 

1) 단일행 함수 : 단일행 값이 입력되는 함수

문자열 함수

문자열 함수	의미
ASCII(문자)	문자나 숫자를 아스키코드로 변환
CHAR(아스키번호)	아스키 번호를 문자나 숫자로 변환
LOWER(문자열)/UPPER(문자열)	대문자나 소문자로 변환
CONCAT(문자열1, 문자열2)	두 문자열을 결합 -> "||"(Oracle)이랑 "+"(MySQL)과 같은 의미이다
SUBSTR/SUBSTRING(문자열,m,n)	문자열에서 m위치부터 n개만큼 해당되는 문자열을 반환 -> n이 없다면 마지막 문자까지 -> m이 음수라면 마지막 문자로부터 m위치부터 n개 문자길이 반환
LENGTH/LEN(문자열)	문자열의 길이 반환 ->공백이나 줄바꿈도 1로 친다

 

숫자형 함수

숫자형 함수	의미
SIGN(n)	숫자가 양수면 1, 음수면 -1, 0이면 0을 반환
MOD(n , m)	n을 m으로 나눠서 나머지를 반환
CEIL/CEILING(n)	크거나 같은 최소 정수 반환
FLOOR(n)	작거나 같은 최대 정수 리턴

 

날짜형 함수

날짜형 함수	의미
SYSDATE/GETDATE()	현재날짜와 시각 출력
EXTRACT/DATEPART()	날짜에서 데이터 출력
TO_NUMBER(TO_CHAR(d,"YYYY"))/YEAR(d)	연도 출력

 

변환형 함수

변환형 함수	의미
TO_NUMBER(문자)	문자 데이터 타입을 숫자 데이터 타입으로 변환
TO_CHAR(숫자 혹은 날짜, [format])	숫자 혹은 날짜 데이터 타입을 지정된 format의 문자 데이터 타입으로 변환
TO_DATE(문자열, format)	문자 데이터 타입을 지정된 format의 날짜 데이터 타입으로 변환

 

NULL 관련 함수

NULL 관련 함수	의미
NVL(표현식1, 표현식2) 오라클 ISNULL(표현식1, 표현식2) MySQL	표현식1의 결과값이 null이면 표현식 2의 값을 출력
NULLIF(표현식1, 표현식2)	두 개의 값이 같으면 NULL을, 같지 않으면 첫 번째 값을 반환
COALESCE(표현식1, 표현식2, . . . )	NULL이 아닌 최초의 인자 값을 반환

+) NULL 의 연산

count 되지 않음, 연산시 NULL 리턴, 비교연산시 거짓 리턴

 

 

 

 

---

2)다중행 함수 : 다중행 값이 입력되는 함수