CREATE, ALTER, DAN DROP TABLE

CREATE TABLE

Bentuk Umum CREATE TABLE:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]

atau

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    select_statement

atau

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
        [index_type]
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
        [index_type]
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
        [index_name] [index_type] (index_col_name,...)
        [index_type]
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
        [index_type]
  | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string'] [reference_definition]

data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

table_options:
    table_option [[,] table_option] ...

table_option:
    {ENGINE|TYPE} [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | UNION [=] (tbl_name[,tbl_name]...)

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

Fungsi statement CREATE TABLE adalah untuk menciptakan tabel baru dalam suatu database. Satu hal yang harus dipahami, syarat untuk menciptakan suatu tabel adalah tabel yang diciptakan harus terdapat sekurang-kurangnya satu kolom atau field.

Buat database baru dengan nama universitas.

CREATE DATABASE universitas;
use universitas;
Database changed

Statement use universitas digunakan untuk memilih database yang akan dipakai untuk menciptakan tabel, yaitu database universitas. Untuk menciptakan tabel baru gunakan perintah CREATE TABLE nama_tabel;. Misalkan kita akan membuat tabel dengan nama mahasiswa dengan field / kolom npm dan nama.

CREATE TABLE mahasiswa(npm VARCHAR(8) NOT NULL PRIMARY KEY,
nama VARCHAR(30) NOT NULL);
SHOW TABLES;
+-----------------------+
| Tables_in_universitas |
+-----------------------+
| mahasiswa             |
+-----------------------+

varchar(8) menyatakan bahwa kolom npm hanya bisa menampung tipe data varchar dengan panjang maksimal 8 karakter. not null menyatakan bahwa kolom tersebut tidak boleh kosong atau harus diisi. Statement primary key menyatakan data yang dimasukkan kedalam kolom tersebut haruslah unik atau tidak boleh sama. Untuk melihat struktur dari tabel mahasiswa gunakan perintah DESC nama_tabel;

DESC mahasiswa;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| npm    | varchar(8)  | NO   | PRI | NULL    |       |
| nama   | varchar(30) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

ALTER TABLE

Bentuk Umum ALTER TABLE:

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_type]
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_type]
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_type]
  | ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name]
        (index_col_name,...) [index_type]
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}

table_options:
    table_option [[,] table_option] ...

Fungsi statement ALTER TABLE adalah untuk mengubah struktur tabel, seperti menambah kolom, menghapus kolom, menghapus primary key, menambahkan index, dan lainnya. Berikut contoh penggunan statement ALTER:

Menambahkan kolom umur dengan type int dengan panjang maksimal 3 karakter.

ALTER TABLE mahasiswa ADD umur INT(3) NOT NULL;
DESC mahasiswa;
+--------+----------- -+
| Field  | Type        | 
+--------+-------------+
| npm    | varchar(8)  | 
| nama   | varchar(30) |
| umur   | int(3)      |
+--------+-------------+

Menambahkan kolom agama dengan tipe varchar dan diletakkan pada posisi pertama dari struktur tabel.

ALTER TABLE mahasiswa ADD agama VARCHAR(10) NOT NULL FIRST;
DESC mahasiswa;
+--------+----------- -+
| Field  | Type        | 
+--------+-------------+
| agama  | varchar(10) | 
| npm    | varchar(8)  | 
| nama   | varchar(30) |
| umur   | int(3)      |
+--------+-------------+

Menghapus primary key.

ALTER TABLE mahasiswa DROP PRIMARY KEY;
DESC mahasiswa;
+--------+-------------+------+-----+
| Field  | Type        | Null | Key |
+--------+-------------+------+-----+
| agama  | varchar(10) | NO   |     |
| npm    | varchar(8)  | NO   |     |
| nama   | varchar(30) | NO   |     |
| umur   | int(3)      | NO   |     |
+--------+-------------+------+-----+

Menambahkan primary key pada kolom npm.

ALTER TABLE mahasiswa ADD PRIMARY KEY(npm);
DESC mahasiswa;
+--------+-------------+------+-----+
| Field  | Type        | Null | Key |
+--------+-------------+------+-----+
| agama  | varchar(10) | NO   |     |
| npm    | varchar(8)  | NO   | PRI |
| nama   | varchar(30) | NO   |     |
| umur   | int(3)      | NO   |     |
+--------+-------------+------+-----+

Mengubah posisi kolom agama menjadi setelah kolom nama;

ALTER TABLE mahasiswa CHANGE agama agama varchar(10) not null AFTER nama;
DESC mahasiswa;
+--------+-------------+------+-----+
| Field  | Type        | Null | Key |
+--------+-------------+------+-----+
| npm    | varchar(8)  | NO   | PRI |
| nama   | varchar(30) | NO   |     |
| agama  | varchar(10) | NO   |     |
| umur   | int(3)      | NO   |     |
+--------+-------------+------+-----+

Mengubah kolom agama menjadi hobi dengan panjang maksimal 30 karakter.

ALTER TABLE mahasiswa CHANGE agama hobi varchar(30) not null;
DESC mahasiswa;
+--------+-------------+------+-----+
| Field  | Type        | Null | Key |
+--------+-------------+------+-----+
| npm    | varchar(8)  | NO   | PRI |
| nama   | varchar(30) | NO   |     |
| hobi   | varchar(30) | NO   |     |
| umur   | int(3)      | NO   |     |
+--------+-------------+------+-----+

Menghapus kolom hobi.

ALTER TABLE mahasiswa DROP hobi;
DESC mahasiswa;
+--------+-------------+------+-----+
| Field  | Type        | Null | Key |
+--------+-------------+------+-----+
| npm    | varchar(8)  | NO   | PRI |
| nama   | varchar(30) | NO   |     |
| umur   | int(3)      | NO   |     |
+--------+-------------+------+-----+

DROP TABLE

Bentuk Umum DROP TABLE:

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

Fungsi statement DROP TABLE adalah untuk menghapus tabel yang ada dalam suatu database. Tidak hanya tabel yang akan terhapus, tetapi juga semua record yang terdapat dalam tabel tersebut. Oleh karenanya, berhati-hatilah menggunakan statement ini.

Untuk menghapus tabel gunakan perintah DROP TABLE nama_table. Misalkan kita akan menghapus tabel mahasiswa.

DROP TABLE mahasiswa;
SHOW TABLES;
Empty set (0.00 sec)

Good luck..!!

INGAT! Syarat untuk menciptakan suatu tabel adalah minimal mengandung satu kolom atau field, dan jangan lupa menggunakan use nama_database untuk memilih database yang akan digunakan dalam menciptakan tabel. REFERENSI