데이터베이스 관련 구문에 대해서 포스팅 하겠습니다.
우선 데이터베이스를 생성을 해야합니다.
즉 CREATE DATABASE문을 이용하면 됩니다.
CREATE DATABASE databasename;자신이 정하고자 하는 데이터베이스 이름을 적어주면 됩니다.
이를 확인하고자 한다면 SHOW DATABASES; 명령어를 실행시켜주면 됩니다.
만들었으니 데이터베이스를 삭제도 해야겠죠.
삭제를 하려면 DROP이라는 용어를 사용합니다.
DROP DATABASE databasename;
대신 주의사항으로 이를 삭제하면 데이터베이스 관련 정보들이 사라지게됩니다.
그래서 삭제를 하려면 관리자 권한이 있어야합니다. 이를 확인하려면 마찬가지로 SHOW DATABASES를 통해
확인 할 수 있습니다.
그래서 삭제하기전 전체 데이터베이스를 백업하는 경우도 많습니다.
백업을 하기 위해서는 BACKUP DATABASE와 TO DISK를 이용합니다. 전자는 해당 백업할 이름을 적어주는 명
령어 후자는 어디에 저장할지의 경로명을 적어주면 됩니다.
BACKUP DATABASE databasename
TO DISK = 'filepath';
또한 차등 백업이라는 것이 있는데 이는 백업 후에 변경된 부분만 변경하는 것을 말합니다.
BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;위 구문처럼 쓰시면 차등 백업이 됩니다. 즉 DIFFERNTIAL라는 차등 용어를 WITH와 함께 써주면 되겠습니다.
차등 백업은 전체 백업 보다 시간이 단축됩니다.
데이터베이스도 만들었겠다. 그럼 이제 테이블을 만들어야 겠죠 ??
테이블을 만들기 위해서는 CREATE TABLE 문을 사용합니다.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);이렇게 CREATE TABLE 뒤에는 테이블 명을 정해주고 안에서는 해당 column들을 적어 주고 데이터 형식
NOT NULL인지 기본키인지 등 적어줍니다.
datatype에는 varchar, 정수 , 날짜 등이 있습니다.
Note: The values are sorted in the order you enter them.
Data type
Description
CHAR(size)
Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size)
Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT
Holds a string with a maximum length of 255 characters
TEXT
Holds a string with a maximum length of 65,535 characters
BLOB
For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT
Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB
For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT
Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB
For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.)
Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
SET
Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice
<출처:w3schools>
간단하게 보면 학생이라는 테이블을 만들고 싶다면
CREATE TABLE STUDENTINFO(
StudentID int,
LastName varchar(255),
FirstName varchar(255),
Grade varchar(5),
City varchar(255),
primary key(StudentID)
);
이런 식으로 테이블을 만듭니다.
|
StudentID |
LastName | FirstName | Grade | City |
|---|---|---|---|---|
위 테이블처럼 나오게 될겁니다.
이를 앞서 배운 INSERT INTO 구문으로 채워넣으면 되겠죠.
마찬가지로 테이블을 만들었으면 삭제도 있겠죠.
DROP TABLE을 이용합니다.
DROP TABLE table_name;
삭제할 테이블 이름을 적어주면 됩니다.
항상 삭제전에는 주의하시기 바랍니다.
내부 데이터만 삭제하려면 TRUNCATE을 이용합니다.
TRUNCATE TABLE table_name;
이런 구문을 사용하면 테이블 데이터는 모로지 삭제는 되지만
테이블 자체는 삭제가 되지않습니다.
기존 테이블의 열 추가 삭제 수정은 ALTER TALBLE을 이용합니다.
ALTER TABLE - 열 추가(add)
ALTER TABLE table_name
ADD column_name datatype;
고객테이블에 이메일 열을 추가하려면
ALTER TABLE 고객
ADD 이메일 varchar(255);
이렇게 쓰시면 됩니다.
ALTER TABLE - 열 삭제(drop)
ALTER TABLE table_name
DROP COLUMN column_name;
열의 데이터 형식을 변경하려면 ALTER TABLE Modify column을 사용합니다.
ALTER TABLE - 열 수정(modify)
ALTER TABLE table_name
MODIFY(alter) COLUMN column_name datatype;
제약 조건을 만들기 위해서는 CREATE TABLE을 할 때 또는 ALTER TALBE로 지정 가능합니다.
CREATE TABLE에서는 datatype 뒤에 적어줍니다.
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);제약 조건은 테이블에 들어갈 수있는 데이터 유형을 제한하는 데 사용됩니다. 이렇게하면 표의 데이터 정확성과 신뢰성이 보장됩니다.
그리고 제약조건 범위는 두가지가 있는데 하나는 열 수준 두 번째는 테이블 수준입니다.
열 수준은 열에만 제약조건이 걸리고
테이블 수준은 테이블 전체에 걸립니다.
일반적으로 사용되는 제약조건은
구체적으로 제약조건에 대해 알아보죠.
NOT NULL
이 제약 조건은 널 값이 허용하지 않도록 하는 것입니다.
필드에 항상 값이 있어야 한다는 소리에요.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
이렇게 NOT NULL을 데이터 타입 뒤에 적어줍니다.
UNIQUE
이 제약 조건은 열의 모든 값이 서로 다른지 확인합니다.
기본키에 이 UNIQUE가 포함이 되어있죠.
UNIQUE는 여러 열에 적용 가능 하지만 PRIMARY KEY인 즉 기본키는 하나만 가집니다.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
이렇게 UNIQUE (ID)로 적어주시면 됩니다. ID가 UNIQUE라는 제약조건 이 걸리는겁니다. 같아서는 절대 안되겠죠..?? 각자 ID는 서로 다 달라야 된다는 겁니다.
마찬가지로 ALTER TALBE로 지정가능합니다.
ALTER TABLE Persons
ADD UNIQUE (ID);
or
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
아래는 여러 열에 제약조건 걸고 이름을 지정해주기 위함입니다.
제약 조건을 삭제 하기 위해선 ??
ALTER TABLE Persons
DROP INDEX UC_Person;
DROP INDEX로 해주면 되겠습니다.
PRIMARY KEY
이 제약 조건은 기본키 라고 합니다.
각 레코드를 고유하게 식별하죠.
무조건 하나만 있어야 합니다.
이 정하는 기준은 고유한 값입니다. 예를 들어 주민번호 핸드폰 번호 등등 . 같을 수 없는것을 말합니다.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
이렇게 PRIMARY KEY(ID)로 ID를 기본키로 지정해줍니다.
여러개 값을 통해 하나의 기본키를 지정해주는 경우는 아래와 같습니다.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
이는 하나의 PK_Person이라는 기본키입니다.
마찬가지로 ALTER TABLE을 이용하면
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
or
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
입니다.
삭제는 ??
ALTER TABLE Persons
DROP PRIMARY KEY;
FOREIGN KEY
이 제약 조건은 두 테이블을 연결하는데 사용됩니다.
외래키가 들어있는테이블을 하위 테이블
후보 키가 들어있는 테이블을 참조된 테이블 or 상위 테이블이라 합니다.
"사람"표 :
| PersonID | LastName | FirstName | Age |
|---|---|---|---|
| 1 | Hansen | Ola | 30 |
| 2 | Svendson | Tove | 23 |
| 3 | Pettersen | Kari | 20 |
'주문'표 :
| OrderID | OrderNumber | PersonID |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
여기서 주문 표의 PersonID는 외래키입니다. 이는 사람 표에서 PersonID가 주키인 기본키입니다.
즉 주문 표의 PersonID는 사람표의 PersonID를 가리키고있씁니다.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
이 두개의 방식으로 외래키를 지정해주게 됩니다.
CHECK
이 제약 조건은 열에 배치 범위를 제한합니다.
즉 특정 값만 허용 되게 만드는거죠.
키? 몸무게 처럼말이에요 아. 또한 나이도 될 수 있겠군요. 나이 제한같은거
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
여기서 CHECK(Age>=18)인데 즉 Age 열의 범위는 18 이상입니다.
즉 18세 미만은 안된다는거죠.
여러 열에대해 범위를 지정해주려면
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')
);
즉 나이는 18이상 도시는 Sandnes
ALTER 구문으로는
ALTER TABLE Persons
ADD CHECK (Age>=18);
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
입니다.
DEFAULT
이 제약 조건은 말 그대로 디펄트 조건
즉 값이 없을 때 기본적으로 들어갈 값을 얘기합니다.
값을 지정 하지 않으면 기본 값이 들어가죠.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
여기서는 도시에 값을 지정하지 않을 경우 Sandnes로 지정이 됩니다.
또한 함수에서도 사용가능한데
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
여기서 GETDATE()함수를 통해 날짜를 얻어 이 값을 기본 값으로 정해줄 수 있어요.
CREATE INDEX
이 구문은
테이블에 인덱스를 만드는데 사용 됩니다.
이는 검색을 빨리하게 만들어주는 거죠.
무조건 자주 검색할 열에 대해서만 지정을 해주세요.
CREATE INDEX 구문
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE INDEX idx_lastname
ON Persons (LastName);
이는 PErsons 테이블에서 LastName열을 인덱스로 idx_lastname으로 만들어주는 구문입니다.
AUTO INCREMENT
이 구문은 몹시 자주 쓰입니다.
예를 들어서 게시판에 대해 만들고 있다면 게시글 번호가 자동으로 증가하게 끔 만들거나 등이있습니다.
CREATE TABLE Persons (
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
기본적으로 AUTO_INCREMENT의 시작 값은 1이며 새 레코드마다 1 씩 증가합니다.
시작 값을 지정해주려면
ALTER TABLE Persons AUTO_INCREMENT=100;
으로 지정해줍니다.
이 경우에 ID에 값을 지정해 줄 필요가 없습니다.
SQL DATE
이 구문은 데이터베이스 날짜 형식과의 동기화가 어렵습니다.
다음 데이터 유형들이 있습니다.
- DATE - YYYY-MM-DD 형식
- DATETIME - 형식 : YYYY-MM-DD HH : MI : SS
- TIMESTAMP - 형식 : YYYY-MM-DD HH : MI : SS
- YEAR - YYYY 또는 YY 형식
그러나 시간 요소가 포함 되는 순간 많이 복잡해집니다.
따라서 시간이 없으면 두 날짜를 쉽게 비교도 가능합니다.
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 |
| 2 | Camembert Pierrot | 2008-11-09 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
| 4 | Mascarpone Fabioli | 2008-10-29 |
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
결과 세트는 다음과 같습니다.
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
시간이 포함된다면 ...ㅠ
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 13:23:44 |
| 2 | Camembert Pierrot | 2008-11-09 15:45:21 |
| 3 | Mozzarella di Giovanni | 2008-11-11 11:12:01 |
| 4 | Mascarpone Fabioli | 2008-10-29 14:56:59 |
위와 같은 SELECT 문을 사용하는 경우 :
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
결과가 안나오겠죠 .... 슬픈현상이네요
즉 날짜에 시간 구성요소를 되도록 넣지 마세요 ㅠㅠ
VIEW
view는 가상 테이블입니다.
SQL 함수, WHERE 및 JOIN 문을 뷰에 추가하고 데이터가 하나의 단일 테이블에서 오는 것처럼 데이터를 표시 할 수 있습니다.
편리하겠죠.
CREATE VIEW 구문
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;CREATE VIEW를 사용합니다.
그리고 담을 SELECT문
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = "Brazil";
위 구문은 나라가 브라질인 고객을 보여주는 뷰입니다.
이를 사용하려면
SELECT * FROM [Brazil Customers];
위 구문을 씁니다.
다음 포스팅은 함수에 대해 포스팅하겠습니다
.
'데이터베이스 > MySQL' 카테고리의 다른 글
| MySQL 함수들(2) (0) | 2019.01.01 |
|---|---|
| MySQL 함수들(1) (0) | 2018.12.29 |
| MySQL 저장 프로시저 (0) | 2018.12.27 |
| MySQL CASE (0) | 2018.12.27 |
| MySQL INSERT INTO SELECT (0) | 2018.12.27 |
