[SQL] TABLE 생성

TABLE

Create		CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );
기존 테이블 복사해서 테이블 생성		CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE ....;
DROP		DROP TABLE table_name;
ALTER	열 추가	ALTER TABLE table_name ADD column_name datatype;
	열 삭제	ALTER TABLE table_name DROP COLUMN column_name;
	열 변경 열 수정	SQL Server MS Access	ALTER TABLE table_name ALTER COLUMN column_name datatype;
		My SQL Oracle (prior version 10G)	ALTER TABLE table_name MODIFY COLUMN column_name datatype;
		Oracle 10G and later	ALTER TABLE table_name MODIFY column_name datatype;

 

★ SQL 제약 조건

NOT NULL	컬럼이 NULL 값을 가질 수 없도록 보장
UNIQUE	열의 모든 값이 서로 다른지 확인
PRIMARY KEY	(A)의 조합 NOT NULL과 UNIQUE. 테이블의 각 행을 고유하게 식별
FOREIGN KEY	외래키/ PRIMARY KEY다른 테이블에서 참조하는 한 테이블의 필드
CHECK	컬럼의 값이 특정 조건을 만족하는지 확인
DEFAULT	값이 지정되지 않은 경우 열의 기본값을 설정합니다.
CREATE INDEX	데이터베이스에서 데이터를 매우 빠르게 생성하고 검색하는 데 사용

 

[ NOT NULL ] CREATE TABLE

컬럼이 NULL 값을 가질 수 없도록 보장

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );

 

SQL Server Oracle MS Access	CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );
MySQL	CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE (ID) );
MySQL SQL Server Oracle MS Access	CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT UC_Person UNIQUE (ID,LastName) ); ※ CONSTRAINT 제약 조건의 이름을 지정

 

[ UNIQUE ] CREATE TABLE

 

[ UNIQUE ] ALTER TABLE에 대한 SQL UNIQUE 제약 조건

MySQLSQL ServerOracle MS Access	ALTER TABLE Persons ADD UNIQUE (ID);
	ALTER TABLE Persons ADD CONSTRAINT 제약조건명 UNIQUE (ID,LastName); → 제약 조건의 이름을 정하고 여러 컬럼의 UNIQUE 처리

 

[ UNIQUE ] 고유 제약 조건 삭제(UNIQUE제약 조건을 삭제)

MySQL	ALTER TABLE Persons DROP INDEX UC_Person;
SQL ServerOracleMS Access	ALTER TABLE Persons DROP CONSTRAINT UC_Person;

 

[ PRIMARY KEY ] CREATE TABLE

MySQL	CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) );
SQL Server Oracle MS Access	CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );
PRIMARY KEY제약 조건의 이름을 지정	CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID,LastName) );

 

[ PRIMARY KEY ] ALTER TABLE의 SQL 기본 키

MySQL SQL ServerOracleMS Access	ALTER TABLE Persons ADD PRIMARY KEY (ID);
	ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

 

[ PRIMARY KEY ] 기본 키 제약 조건 삭제

MySQL	ALTER TABLE Persons DROP PRIMARY KEY;
SQL ServerOracleMS Access	ALTER TABLE Persons DROP CONSTRAINT PK_Person;

 

[ FOREIGN KEY ] CREATE TABLE

MySQL	CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES 부모테이블명(PersonID) );
SQL ServerOracleMS Access	CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderNumber int NOT NULL, PersonID int FOREIGN KEY REFERENCES 부모테이블명(PersonID) );

 

[ FOREIGN KEY ] ALTER TABLE의 SQL 외래 키

MySQL SQL ServerOracleMS Access	ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
	ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

 

[ FOREIGN KEY ] 외래 키 제약 조건 삭제

MySQL	ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;
SQL ServerOracleMS Access	ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;

 

[ CHECK ] CREATE TABLE에 대한 SQL CHECK

MySQL	CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );
SQL ServerOracleMS Access	CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) );
MySQL SQL ServerOracleMS Access	CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes') );

 

[ CHECK ] ALTER TABLE에 대한 SQL CHECK

MySQL SQL ServerOracleMS Access	ALTER TABLE Persons ADD CHECK (Age>=18);
	ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

 

[ CHECK ] CHECK 제약 조건 삭제

MySQL	ALTER TABLE Persons DROP CHECK CHK_PersonAge;
SQL ServerOracleMS Access	ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;

 

 

[ DEFAULT ] CREATE TABLE에 대한 SQL DEFAULT

MySQL SQL ServerOracleMS Access	CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT 'Sandnes' );
	CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE( ) );

 

[ DEFAULT ] ALTER TABLE의 SQL DEFAULT

MySQL

ALTER TABLE Persons [ALTER City SET DEFAULT 'Sandnes';

[ DEFAULT ] DEFAULT 제약 조건 삭제

MySQL	ALTER TABLE Persons ALTER City DROP DEFAULT;
SQL ServerOracleMS Access	ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;

 

[ INDEX ] SQL CREATE INDEX 문

인덱스 생성 구문(중복 ○)	CREATE INDEX index_name ON table_name (column1, column2, ...);
고유 인덱스 생성 구문(중복 ×)	CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

 

[ INDEX ] DROP INDEX 문

MySQL	ALTER TABLE table_name DROP INDEX index_name;
Oracle	DROP INDEX index_name;

 

SQL AUTO INCREMENT 필드

MySQL	CREATE TABLE Persons ( Personid int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (Personid) );
시작 숫자 지정	ALTER TABLE Persons AUTO_INCREMENT=100;

Oracle

CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;

 

[ SQL 날짜 데이터 유형 ]

DATE - YYYY-MM-DD 형식

DATETIME - 형식: YYYY-MM-DD HH:MI:SS

TIMESTAMP - 형식: YYYY-MM-DD HH:MI:SS

YEAR - YYYY 또는 YY 형식

 

[ SQL CREATE VIEW 문 ] 가상 테이블

CREATE VIEW view_name ASSELECT column1, column2, ...FROM table_nameWHERE condition;

 

MySQL 데이터 유형

 

문자열 데이터 유형

타입	정의	길이
CHAR(n)	- 고정 길이 데이터 타입- 지정된 길이보다 짧은 데이터 입력 시 나머지 길이는 공백으로 채워짐- 검색시, PAD_CHAR_TO_FULL_LENGTH 모드를 설정하지 않으면 공백은 제거됨	0 ~ 255 (byte)
VACHAR(n)	- 가변 길이 데이터 타입- 지정된 길이보다 짧은 데이터 입력시 공백으로 채우지 않음-저장시 1-byte 혹은 2-byte 길이 Prefix 데이터를 저장. 이 Prefix 데이터는 값의 바이트 수에 대한 정보를 담는다.(https://dev.mysql.com/doc/refman/8.0/en/char.html)	0 ~ 65,535 (byte)
TINYTEXT(n)	- 문자열 데이터 타입(최대 255 byte)- TINYBLOB와 같은 길이값을 저장 가능(단 차이점은 저장 될때 nonbinary string으로 저장)https://dev.mysql.com/doc/refman/8.0/en/blob.html	0 ~ 255 (byte)
TEXT(n)	- 문자열 데이터 타입(최대 65,535 byte)- BLOB와 같은 길이값을 저장 가능(단 차이점은 저장 될때 nonbinary string으로 저장)	0 ~ 65,535 (byte)
MEDIUMTEXT(n)	- 문자열 데이터 타입(최대 16,777,215 byte)- MEDIRMBLOB와 같은 길이값을 저장 가능(단 차이점은 저장 될때 nonbinary string으로 저장)	0 ~ 16,777,215 (byte)
LONGTEXT(n)	- 문자열 데이터 타입(최대 4,294,967,295 byte)- LONGBLOB와 같은 길이값을 저장 가능(단 차이점은 저장 될때 nonbinary string으로 저장)	0 ~ 4,294,967,295 (byte)

 

숫자 데이터 유형

타입	정의	범위	UNSIGNED 사용가능 여부
TINYINT(n)	정수형 데이터 타입	- 1 Byte(2^8)- 128 ~ + 127 또는 0 ~ 255 수 표현 가능- 1Byte(2^8)- 128 ~ + 127 또는 0 ~ 255 수 표현 가능	O
SMALLINT(n)	정수형 데이터 타입	- 2 Byte(2^16)- 32,768 ~ 32,167 또는 0 ~ 65536수 표현 가능	O
MEDIUMINT(n)	정수형 데이터 타입	- 3 Byte- 8,388,608 ~ 8,388,607 또는 0 ~ 16,777,215 수 표현 가능	O
INT(n)	정수형 데이터 타입	- 4 Byte- 2,147,483,648 ~ 2,147,483,647 또는 0 4,294,967,295 수 표현 가능	O
BIGINT(n)	정수형 데이터 타입(LONG)	- 8 byte- 2^64 - 1 표현 가능(무한 수 표현 가능이라고도 함)	O
DECIMAL(m, d)	- 고정 소수형 데이터 타입고정(길이+1 byte)- 화폐 데이터와 같이 데이터의 정확도를 요하는 경우에 주로 사용- M의 최대값은 65, D는 소수 자릿수이며 0이면 소수점 가지지 않음	- 소수점을 사용한 형태- Default: m ⇒ 10	X
FLOAT(n)	부동 소수형 데이터 타입	- 4 byte- 부동 소수점을 사용한 형태	X
DOUBLE(n)	부동 소수형 데이터 타입	- 8 byte- DOUBLE을 문자열로 저장	X

 

날짜 및 시간 데이터 유형

타입	정의	길이	형식	길이
DATE	날짜(년도, 월, 일) 형태의 기간 표현 데이터	3 byte	0000-00-00 (YYYY-MM-DD)	1000-01-01 ~ 9999-12-31
TIME	시간(시, 분, 초) 형태의 기간 표현 데이터	3 byte	00:00:00	.
DATETIME	날짜와 시간 형태의 기간 표현 데이터	8 byte	0000-00-00 00:00:00 (YYYY-MM-DD hh:mm:ss)	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
TIMESTAMP	날짜와 시간 형태의 기간 표현 데이터 타입 시스템 변경 시 자동으로 그 날짜와 시간이 저장	4 byte	Integer	.
YEAR	년도 표현 데이터 타입	1 byte	0000	.