[SQL] 마리아디비 (MariaDB) 테이블 생성, 삭제, 수정

 정보

• 업무명     : 마리아디비 (MariaDB) 테이블 생성, 삭제, 수정
• 작성자     : 이상호
• 작성일     : 2020-04-11
• 설   명      :
• 수정이력 :

 

 내용

[개요]

• 안녕하세요? 웹 개발 및 연구 개발을 담당하고 있는 해솔입니다.
• MySQL과 동일한 소스 코드를 기반으로 한 오픈소스 관계형 데이터베이스 (RDBMS)인 마리아디비 (MariaDB) 소개해 드리고자 합니다.

 

 

[특징]

• 웹 개발을 위해서 마리아디비 (MariaDB) 기술이 요구되며 이 프로그램은 이러한 목적을 달성하기 위한 기술서

 

[기능]

• 마리아디비 (MariaDB) 소개

 

[활용 자료]

• 없음

 

[자료 처리 방안 및 활용 분석 기법]

• 없음

 

[사용법]

• 없음

 

[사용 OS]

• Windows 10

 

[사용 언어]

• MariaDB v10.2.10
• SQL Gate for MariaDB Developer v9.8

 

 세부 내용

[테이블 생성]

• 데이터베이스 애플리케이션은 기본 애플리케이션과 별도로 존재하며 데이터 컬렉션을 저장합니다. 각 데이터베이스는 하나 이상의 API를 사용하여 포함된 데이터를 작성, 액세스, 관리, 검색 및 복사를 수행합니다.
• MariaDB에서 CREATE TABLE 명령문은 선택된 데이터베이스에서 테이블을 작성하는데 사용됩니다.
• CREATE TABLE 명령문을 사용하여 주어진 이름 (tbl_name) 으로 테이블을 작성할 수 있습니다. 테이블 CREATE을 작성하려면 테이블 또는 데이터베이스 권한이 필요합니다.

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...) [table_options    ]... [partition_options]
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)] [table_options   ]... [partition_options]
    select_statement
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
   { LIKE old_table_name | (LIKE old_table_name) }

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

 

• column_name를 기본 키로 지정할 수도 primary_key가 있습니다.

CREATE TABLE table_name (column_name column_type);

 

• 가장 기본적인 형태로, CREATE TABLE 명령문은 테이블 이름과 열, 인덱스 및 제약 조건을 제공합니다.
• 기본적으로 테이블은 기본 데이터베이스에 작성됩니다.
• db_name.tbl_name 데이터베이스를 지정하는 데 사용합니다. 즉 참조된 테이블 이름으로 각 데이터베이스 및 테이블 이름 (db_name. tbl_name)을 지정할 수 있습니다.
• 가장 기본적인 구문은 다음과 같습니다.

USE testdb;

CREATE TABLE students(  
    student_id INT NOT NULL AUTO_INCREMENT,  
    student_name VARCHAR(100) NOT NULL,  
    student_address VARCHAR(40) NOT NULL,  
    admission_date DATE,  
    PRIMARY KEY ( student_id )
);

• SHOW TABLES 명령어를 사용하여 테이블 작성에 성공했는지 확인할 수 있습니다.

SHOW CREATE TABLE `testdb`.`students`;

 

 

CREATE TABLE `students` (
    `student_id` INT(11) NOT NULL AUTO_INCREMENT,
    `student_name` VARCHAR(100) NOT NULL,
    `student_address` VARCHAR(40) NOT NULL,
    `admission_date` DATE NULL DEFAULT NULL,
    PRIMARY KEY (`student_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

 

 

• 테이블을 만드는데 일반적으로 사용되는 명령문입니다.

create table if not exists test (
    a bigint auto_increment primary key,
    name varchar(128) charset utf8,
    key name (name(32))
) engine=InnoDB default charset utf8;

 

• 아래 내용은 MariaDB v10.2.1 이상에만 적용됩니다.

CREATE TABLE t1(
  a int DEFAULT (1+1),
  b int DEFAULT (a+1),
  expires DATETIME DEFAULT(NOW() + INTERVAL 1 YEAR)
);

 

[테이블 수정]

• ALTER TABLE이 명령은 기존 테이블의 구조를 변경하는 데 사용됩니다. 예를 들어, 열을 추가 또는 삭제하거나 인덱스를 작성 또는 제거하거나 기존 열의 유형을 변경하거나 열 또는 테이블 이름을 바꿀 수 있습니다. 테이블의 주석과 테이블의 스토리지 엔진을 변경할 수도 있습니다.
• 다른 연결이 테이블을 사용하는 경우 메타 데이터 잠금이 활성화되고이 명령문은 잠금이 해제 될 때까지 기다립니다. 비 트랜잭션 테이블에 대해서도 마찬가지입니다.
• 값이 중복 된 열 (또는 열 그룹)에 UNIQUE색인 을 추가하면 오류가 발생하고 명령문이 중지됩니다. 오류를 억제하고 UNIQUE인덱스 작성을 강제 실행 하고 중복을 삭제 하기 위해 IGNORE옵션 을 지정할 수 있습니다. 열 (또는 열 집합)이 고유해야하지만 중복 된 값을 포함하는 경우이 방법이 유용 할 수 있지만이 기술은 저장되는 행과 삭제 된 행을 제어 할 수 없습니다.

 

• 열 추가
  • 원래 테이블에 열을 추가하려면 다음 구문을 참조하십시오

ALTER TABLE xxxx ADD COLUMN [IF NOT EXISTS]  (col_name column_definition,...)

 

CREATE TABLE t(id INT PRIMARY KEY, u INT UNSIGNED NOT NULL UNIQUE)
ENGINE=InnoDB;

INSERT INTO t(id,u) VALUES(1,1),(2,2),(3,3);

ALTER TABLE t ADD COLUMN
(d DATETIME DEFAULT current_timestamp(),
 p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'),
 t TEXT CHARSET utf8 DEFAULT 'The quick brown fox jumps over the lazy dog');

UPDATE t SET t=NULL WHERE id=3;

SELECT id,u,d,ST_AsText(p),t FROM t;

SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_instant_alter_column';

 

• 열 삭제

ALTER TABLE xxxx DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]

 

• 다중 열 UNIQUE제약 조건에 속하는 열 은 삭제할 수 없습니다. 예를 들면 다음과 같습니다

CREATE TABLE a (
  a int,
  b int,
  primary key (a,b)
);

ALTER TABLE x DROP COLUMN a;
[42000][1072] Key column 'A' doesn't exist in table

 

• 그 이유는 열을 삭제하면 a새로운 제약 조건 b이 생겨서 열의 모든 값이 고유하기 때문입니다. 이 열을 삭제하려면 명확한 DROP PRIMARY KEY합계 가 필요합니다 ADD PRIMARY KEY. MariaDB 10.2.7부터이 열은 삭제되었으며 추가 제한 조건이 적용되어 다음과 같은 구조가 나타납니다.

ALTER TABLE x DROP COLUMN a;
Query OK, 0 rows affected (0.46 sec)

DESC x;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

 

[열 수정]

ALTER TABLE x DROP COLUMN a;
Query OK, 0 rows affected (0.46 sec)

DESC x;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

 

ALTER TABLE x DROP COLUMN a;
Query OK, 0 rows affected (0.46 sec)

DESC x;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

 

• [제약 조건 추가] ADD CONSTRAINT
  • 테이블의 특정 열이나 열에 제약 조건을 추가하도록 수정하십시오.

ALTER TABLE table_name 
ADD CONSTRAINT [constraint_name] CHECK(expression);

 

• 행을 삽입하거나 업데이트하기 전에 모든 제약 조건이 정의 된 순서대로 평가됩니다. 제약 조건이 실패하면 행이 업데이트되지 않습니다.

CREATE TABLE account_ledger (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_name VARCHAR(100),
    credit_account VARCHAR(100),
    credit_amount INT,
    debit_account VARCHAR(100),
    debit_amount INT);

ALTER TABLE account_ledger 
ADD CONSTRAINT is_balanced 
    CHECK((debit_amount + credit_amount) = 0);

 

[DROP CONSTRAINT 제거]

• 테이블 구조를 수정하고 주어진 제한 조건을 삭제하십시오.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

 

• 패스 CREATE TABLE또는 ALTER TABLE ... ADD CONSTRAINT명령문 이든 테이블에 제약 조건을 추가 할 때 직접 설정 constraint_name하거나 MariaDB가 자동으로 제약 조건을 생성하도록 허용 할 수 있습니다.

CREATE TABLE t (
   a INT,
   b INT,
   c INT,
   CONSTRAINT CHECK(a > b),
   CONSTRAINT check_equals CHECK(a = c)); 

SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE 
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 't';

+-----------------+----------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME     | CONSTRAINT_TYPE |
+-----------------+----------------+-----------------+
| check_equals    | t              | CHECK           |
| CONSTRAINT_1    | t              | CHECK           |
+-----------------+----------------+-----------------+

 

• 테이블에서 제한 조건을 삭제하려면 ALTER TABLE ... DROP CONSTRAINT명령문을 사용하십시오 

ALTER TABLE t DROP CONSTRAINT is_unique;

 

[자주 사용하는 질의어]

• 열 추가

ALTER TABLE t1 ADD x INT;

 

• 열 삭제

ALTER TABLE t DROP CONSTRAINT is_unique;

 

• 열 유형을 수정

ALTER TABLE t1 MODIFY x bigint unsigned;

 

• 열의 이름과 유형을 변경

ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;

 

• ALTER TABLE쉼표로 구분 된 단일 명령문 에서 여러 절 을 결합하십시오.

ALTER TABLE t1 DROP x, ADD x2 INT,  CHANGE y y2 INT;

 

• 스토리지 엔진을 변경

ALTER TABLE t1 ENGINE = InnoDB;

 

• 테이블을 다시 작성하십시오 (이전 예제는 테이블이 이미 InnoDB 인 경우에도 테이블을 다시 작성 함

ALTER TABLE t1 FORCE;

 

 

[테이블 삭제]

• MariaDB에서 DROP TABLE명령문은 데이터베이스에서 테이블을 삭제하는 데 사용됩니다. 테이블을 영구적으로 삭제하며 테이블 구조 및 데이터를 복원 할 수 없습니다.
• "testdb"데이터베이스에서 students테이블 을 삭제 하십시오 . 

DROP TABLE students;

 

• delete 문을 실행 한 후 테이블이 성공적으로 삭제되었는지 확인할 수 있습니다. 다음 쿼리 테이블 문을 참조하십시오

SHOW TABLES;

 

 참고 문헌

[논문]

• 없음

[보고서]

• 없음

[URL]

• 없음

 

 문의사항

[기상학/프로그래밍 언어]

• sangho.lee.1990@gmail.com

[해양학/천문학/빅데이터]

• saimang0804@gmail.com