1) 전체 테이블 조회
SHOW TABLES;
2) 테이블 생성
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...,
PRIMARY KEY (column)
) ENGINE=storage_engine CHARACTER SET character_set COLLATE collation;
ex)
CREATE TABLE table_name1 (
team_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) DEFAULT 'Unknown'
);
CREATE TABLE table_name2 (
num INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) DEFAULT 'Unknown',
team_id INT NOT NULL,
email VARCHAR(50) UNIQUE,
age INT CHECK (age >= 18),
FOREIGN KEY (team_id) REFERENCES table_name1(team_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
▷ 상세 설명
(a) AUTO_INCREMENT : 자동 증가 값 할당
※ 임의로 값을 입력하면 (ex. 4) 입력된 값으로부터 순차적으로 증가합니다. (ex. 5)
또한, 임의로 입력된 값 이후를 삭제하더라도 순차적으로 증가합니다. (ex. 6)
※ AUTO_INCREMENT 값 변경하는 방법은 아래와 같습니다.
SHOW TABLE STATUS LIKE 'table_name'\G; // 확인
ALTER TABLE table_name auto_increment = 3; // 변경
단, 테이블에 입력되어 있는 AUTO_INCREMENT 값 이상의 정수로만 변경 가능합니다.
(b) 키
PRIMARY KEY : 기본 키
FOREIGN KEY : 외래 키
FOREIGN KEY (team_id) REFERENCES table_name1(team_id)
ON DELETE CASCADE ON UPDATE CASCADE
FOREIGN KEY (team_id) : team_id 컬럼을 외래 키로 설정
※ 외래 키는 다른 테이블의 특정 컬럼을 참조합니다.
즉, 다른 테이블과 연결하기 위해 사용합니다.
이를 통해 데이터의 무결성을 유지할 수 있습니다.
REFERENCES test_table(team_id): 외래 키가 참조하는 테이블과 컬럼 지정
ON DELETE CASCADE: 참조된 테이블(team_info)의 team_id 값이 삭제될 때,
이를 참조하고 있는 현재 테이블(user_info)의 관련 행들도 함께 삭제
ON UPDATE CASCADE: 참조된 테이블(team_info)의 team_id 값이 업데이트(변경)될 때, 이를 참조하고 있는 현재 테이블(user_info)의 관련 행들도 함께 업데이트
※ 위의 설정을 통해 데이터의 무결성을 유지하고 참조 무결성을 보장합니다.
CASCADE : 종속된 모든 객체에 적용
※ ON DELETE or ON UPDATE 등의 지정 행위 명령어와 함께 사용합니다.
(c) 그 외
DEFAULT : 기본 값 지정
NOT NULL : NULL 불허
UNIQUE : 중복된 값 불허
CHECK : 저장할 수 있는 값이나 조건 지정
(d) 기타
CONSTRAINT : 무결성 유지
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
CONSTRAINT constraint_name PRIMARY KEY (column1)
);
RESTRICT : 외래 키 참조하는 행 존재하는 경우, 해당 행 삭제 및 업데이트 불허
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
CONSTRAINT fk_name FOREIGN KEY (column1) REFERENCES other_table(column_name) ON DELETE RESTRICT ON UPDATE RESTRICT
);
SET NULL : 외래 키 참조하는 행이 삭제 및 업데이트 시, 해당 열을 NULL로 설정
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
CONSTRAINT fk_name FOREIGN KEY (column1) REFERENCES other_table(column_name) ON DELETE SET NULL ON UPDATE SET NULL
);
COLLATE : 문자열 정렬 순서 지정
CREATE TABLE table_name (
column1 VARCHAR(255) COLLATE utf8_unicode_ci
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
FULLTEXT : 특정 열에 대해 전체 텍스트 검색 지원하는 인덱스 생성
CREATE TABLE table_name (
column1 TEXT,
FULLTEXT (column1)
);
VIRTUAL GENERATED COLUMNS : 실시간 계산
CREATE TABLE table_name (
column1 INT,
column2 INT,
generated_column INT AS (column1 + column2) VIRTUAL
);
3) 테이블 정보 조회
(a) 특정 데이터베이스 내의 전체 테이블 설정 확인
SHOW TABLE STATUS FROM database_name\G;
(b) 특정 테이블 설정 확인
SHOW TABLE STATUS LIKE 'table_name'\G;
(c) 특정 테이블 구조 확인
DESCRIBE test_table;
ex)
DESCRIBE table_name[1/2];
(d) 특정 테이블 정의 확인
SHOW CREATE TABLE table_name;
4) character_set
※ 별도 지정하지 않으면 데이터베이스의 문자 집합과 정렬 순서를 기본으로 사용합니다.
(a) 특정 테이블 character_set 확인
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';
ex)
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'SQL_test' AND TABLE_NAME = 'table_name[1/2]';
(b) 특정 테이블 각 열(Column)별 character_set 확인
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';
ex)
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'SQL_test' AND TABLE_NAME = 'table_name[1/2]';
(c) 특정 테이블 character_set 변경
ALTER TABLE table_name CONVERT TO CHARACTER SET new_character_set COLLATE new_collation;
(d) 특정 테이블의 특정 열 character_set 변경
ALTER TABLE table_name MODIFY column_name column_type CHARACTER SET new_character_set COLLATE new_collation;
5) 테이블 칼럼 조작
(a) 추가
ALTER TABLE table_name ADD COLUMN new_column datatype constraints;
ex) ALTER TABLE table_name1 ADD COLUMN add_test INT NOT NULL;
(b) 복수 추가
ALTER TABLE table_name
ADD COLUMN first_column datatype constraints,
ADD COLUMN second_column datatype constraints;
(c) 이름 변경
ALTER TABLE table_name CHANGE COLUMN old_name new_name datatype;
ex) ALTER TABLE table_name1 CHANGE COLUMN add_test test INT;
※ datatype 새롭게 지정할 수 있습니다.
(d) 데이터타입 변경
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
ex) ALTER TABLE table_name1 MODIFY COLUMN test VARCHAR(20);
(e) 기본값 변경 및 삭제
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;
ex) ALTER TABLE table_name1 ALTER COLUMN test SET DEFAULT 0;
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
ex) ALTER TABLE table_name1 ALTER COLUMN test DROP DEFAULT;
(f) 위치 변경
ALTER TABLE table_name CHANGE COLUMN old_name new_name new_datatype AFTER another_column;
※ 칼럼 이름 변경하지 않을 시 old_column_name new_column_name 동일하게 작성합니다.
ex)
ALTER TABLE table_name1 CHANGE COLUMN test test INT AFTER team_id;
※ datatype 새롭게 지정할 수 있습니다.
ALTER TABLE table_name1 CHANGE COLUMN test test_drop VARCHAR(20) AFTER name;
(g) 삭제
ALTER TABLE table_name DROP COLUMN drop_column;
ex) ALTER TABLE table_name1 DROP COLUMN test_drop;
6) 키 조작
(a) 기본키 추가
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
(b) 외래키 추가
ALTER TABLE table_name ADD FOREIGN KEY (column_name)
REFERENCES other_table(other_column);
7) 테이블 이름 변경
RENAME TABLE old_table_name TO new_table_name;
ex)
RENAME TABLE table_name1 TO team_info;
RENAME TABLE table_name2 TO user_info;
8) 테이블 초기화
TRUNCATE TABLE table_name;
ex) TRUNCATE TABLE team_info;
----------------------------------------------------------------------------------------------------------------------------------------------------------
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`SQL_test`.`user_info`, CONSTRAINT `user_info_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `SQL_test`.`team_info` (`team_id`))
----------------------------------------------------------------------------------------------------------------------------------------------------------
※ 테이블이 외래 키 제약 조건에 의해 참조되고 있으면 오류가 발생합니다.
▷외래 키 제약 조건
1) SHOW VARIABLES LIKE 'foreign_key_checks';
2) 외래 키 제약 조건 목록 조회
SELECT TABLE_NAME,CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'database_name'AND TABLE_NAME = 'table_name'
AND CONSTRAINT_TYPE = 'FOREIGN KEY';
3) 각 외래 키 제약 조건의 세부 정보 조회
SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME,REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name'
AND REFERENCED_TABLE_NAME IS NOT NULL;
Step 1. 외래 키 제약 조건 비활성화
SET FOREIGN_KEY_CHECKS = 0;
Step 2. 테이블 비우기
TRUNCATE TABLE team_info;
Step 3. 외래 키 제약 조건 활성화
SET FOREIGN_KEY_CHECKS = 1;
9) 테이블 삭제
DROP TABLE table_name;
'여러가지 > DBMS' 카테고리의 다른 글
| [MariaDB] 프로시저 (0) | 2024.05.27 |
|---|---|
| [MariaDB] 트리거 (0) | 2024.05.27 |
| [MariaDB] information_schema (0) | 2024.05.27 |
| [MariaDB] 스토리지 엔진 (0) | 2024.05.27 |
| [MariaDB] 데이터베이스 (0) | 2024.05.27 |
