[DataBase] Constraint, CREATE USE ALTER DROP (DDL)

Constraint

 

데이터베이스의 테이블을 생성할때 각 컬럼은 각각의 제약 조건을 갖는다.

 

NOT NULL	NULL 값 (비어있는 값)을 저장할 수 없음
UNIQUE	같은 값을 저장할 수 없음
PRIMARY KEY	NOT NULL과 UNIQUE 의 제약 조건을 동시에 만족 그러므로 컬럼에 비어 있는 값과 동일한 값을 저장할 수 없음 하나의 테이블에 하나의 컬럼만 조건을 설정할 수 있음
FOREIGN KEY	다른 테이블과 연결되는 값이 저장
DEFAULT	데이터를 저장할 때 해당 컬럼에 별도의 저장값이 없으면 DEFAULT로 설정된 값이 저장
AUTO_INCREMENT	주로 테이블의 PRIMARY KEY 데이터를 저장할때 자동으로 숫자를 1씩 증가시켜 주는 기능으로 사용

CREATE database test;
USE test;
CREATE TABLE user(
	# filed, datatype, constraint
	user_id int PRIMARY KEY AUTO_INCREMENT
    , name VARCHAR(20) NOT NULL
    , email VARCHAR(30) UNIQUE NOT NULL
    , age TINYINT Unsigned DEFAULT 0 
    , rdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DESC user;

 

DDL (Data Definition Language)

 

데이터베이스를 정의하는 언어를 말하며 데이터를 생성하거나 수정, 삭제 등 데이터의 전체 골격을 결정하는 역할의 언어를 말한다.

 

# DDL : READ : show, desc
SHOW databases;
SHOW tables;
DESC user;

 

DML (Data Manipulation Language)

 

DML은 DDL에 의해 생성된 스키마 내에서 데이터를 조작하는 데 사용되는 일련의 SQL 명령이다.

 

# DML : CREATE : insert into
INSERT INTO user (name, email)
VALUE ('peter', 'peter@gmail.com');
SELECT * FROM user;

INSERT INTO user (name, email, age)
VALUES ('jhon', 'jhon@daum.net', 34)
, ('alice', 'alice@naver.com', 23)
, ('jin', 'jin@gmail.com', 39)
, ('andy', 'andy@naver.com', 45);
SELECT * FROM user;

 

CREATE

 

# 데이터베이스 생성
CREATE DATABASE <database_name>;

USE world;

SELECT countrycode, name, population
FROM city
WHERE population >= 500 * 10000;

CREATE TABLE city_500(
	countrycode CHAR(3),
    name VARCHAR(50),
    population INT
);
DESC city_500;

SELECT * FROM city_500;

INSERT INTO city_500
SELECT countrycode, name, population
FROM city
WHERE population >= 500 * 10000;

SELECT * FROM city_500;

 

USE

 

# DML : UPDATE : UPDATE SET
USE test;
SELECT * FROM user;

UPDATE user
SET age=27, email='peter@yd.com'
WHERE name = 'peter'
LIMIT 5;

SELECT * FROM user;

show processlist;
kill 18;

# DML : DELETE : DELETE FROM
SELECT * FROM user;

DELETE FROM user
WHERE age < 30
LIMIT 1;

SELECT * FROM user;

# 테이블 초기화
TRUNCATE TABLE user;
SELECT * FROM user;

 

ALTER

 

# DML : C(INSERT INTO)R(SELECT FROM)U(UPDATE SET)D(DELETE FROM)
# DDL : C(CREATE)R(SHOW,DESC)U(ALTER)D(DROP)
DESC user;
# DDL : UPDATE : ALTER
# TABLE : ADD, MODIFY, DROP
ALTER TABLE user ADD contents TEXT NOT NULL;
DESC user;

ALTER TABLE user 
MODIFY COLUMN contents VARCHAR(100) DEFAULT 'NO DATA';
DESC user;

ALTER TABLE user DROP contents;
DESC user;

 

DROP

 

# SHOW(목록), DESC(스키마)

DESC user;
ALTER TABLE user RENAME COLUMN email TO mail;
DESC user;
ALTER TABLE user RENAME COLUMN mail TO email;
DESC user;

SELECT * FROM user;
INSERT INTO user(name, email)
VALUES ('peter', 'peter@gmail.com')
, ('peter2', 'peter2@gmail.com');
SELECT * FROM user;

ALTER TABLE user AUTO_INCREMENT = 100;
INSERT INTO user(name, email)
VALUES ('alice1', 'alice1@gmail.com')
, ('alice2', 'alice2@gmail.com');
SELECT * FROM user;

# 데이터베이스 인코딩
# 데이터베이스, 테이블 컬럼 인코딩 설정 가능
SHOW VARIABLES LIKE 'character_set_database';
SHOW FULL COLUMNS FROM user;
ALTER DATABASE test CHARACTER SET = ascii;
ALTER DATABASE test CHARACTER SET = utf8mb4;

SHOW FULL COLUMNS FROM user;
ALTER TABLE user CONVERT TO character set ascii;
ALTER TABLE user CONVERT TO character set utf8mb4;

# DML : CRUD
# DDL : CRUD

# DDL : DELETE : DROP
DROP table user;
SHOW tables;

DROP DATABASE test;
SHOW databases;

# FOREIGN KEY : 외래키
# 사용이유 : 데이터 무결성 유지
# UNIQUE나 PRIMARY KEY 제약조건이 있어야 설정 가능
DROP DATABASE fk;
CREATE DATABASE fk;
USE fk;

# CREATE TABLES
DROP TABLE user;
CREATE TABLE user(
	ui INT PRIMARY KEY AUTO_INCREMENT
    , name VARCHAR(20) NOT NULL
);
DESC user;

DROP TABLE income;
CREATE TABLE income(
	ii INT PRIMARY KEY AUTO_INCREMENT
    , amount INT NOT NULL
    , ui INT
    , FOREIGN KEY (ui) REFERENCES user(ui)
);
DESC income;