Skip to content

Modifying Data and Table Structure

Info

Database, table dan records yang digunakan pada sesi ini anda dapat merujuk ke resource. Dan untuk menjamin kelancaran penggunaan setiap query yang ada pada catatan ini, maka saya sarankan untuk menggunakan schema lahmansbaseballdb, jika belum ada buat schema dengan nama tersebut.

Warning

Jika query dibawah ini tidak mensiratkan penggunaan nama schema maka saya menggunakan USE lahmansbaseballdb; pada session saya. Anda dapat mengikuti cara tersebut atau selalu siratkan nama scema anda jika menggunakan nama selain lahmansbaseballdb.

Contoh menyiratkan nama schema
    SELECT * 
    FROM nama_data_base.contoh_table;

Inserting Data Into Tables

Memasukan data adalah salah satu cara untuk mengisi tabel pada database dengan data. Statement INSERT akan menyisipkan data kedalam tabel pada database. Untuk memasukan data anda harus mengetahui informasi tentang tabel yang bersangkutan.

Gathering Information to Insert, Update, or Delete Data

Informasi yang dibutuhkan untuk meng-insert, update atau men-delete tabel.

  • Nama dari setiap kolom, anda dapat menggunakan nama kolom untuk menspesifikasi nama dari setiap kolom.

  • Urutan dari setiap kolom, secara khusus mengetahui urutan dari setiap kolom penting ketika anda men-select data dari tabel lain dan meng-insert-nya kedalam sebuah table.

  • Tipe data dari setiap kolom, mengetahui ini sangat penting karena bisa jadi ada kesalahan dalam meng-input data karena tipe data yang tidak sesuai.

  • Apakah kolom bagian dari key atau constraint, anda harus memperhatikan apa ada kolom pada table yang dikonfigurasi dengan NOT NULL, default, unique atau CHECK constraints yang dapat berdampak serta menentukan apaka data dapat dimodifikasi atua tidak.

Untuk mendapatkan informasi diatas kita dapat menggunakan beberapa query;

  1. Untuk mengetahui informasi struktur tabel gunakan DESCRIBE

    DESCRIBE managers;
    Output 
    +----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| playerID | varchar(10) | YES  |     | NULL    |       |
| yearID   | smallint    | NO   | PRI | NULL    |       |
| teamID   | varchar(3)  | NO   | PRI | NULL    |       |
| lgID     | varchar(2)  | YES  |     | NULL    |       |
| inseason | smallint    | NO   | PRI | NULL    |       |
| G        | smallint    | YES  |     | NULL    |       |
| W        | smallint    | YES  |     | NULL    |       |
| L        | smallint    | YES  |     | NULL    |       |
| rank     | smallint    | YES  |     | NULL    |       |
| plyrMgr  | varchar(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
10 rows in set (0,01 sec)

  2. Untuk mengetahui informasi mengenai constraint gunakan query dibawah ini

    SELECT * FROM information_schema.table_constraints
WHERE table_name = 'managers'
AND table_schema = 'lahmansbaseballdb';
    Output 
    +--------------------+-------------------+-----------------+-------------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA      | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+-------------------+------------+-----------------+----------+
| def                | lahmansbaseballdb | PRIMARY         | lahmansbaseballdb | managers   | PRIMARY KEY     | YES      |
| def                | lahmansbaseballdb | managers_chk_1  | lahmansbaseballdb | managers   | CHECK           | YES      |
+--------------------+-------------------+-----------------+-------------------+------------+-----------------+----------+
2 rows in set (0,00 sec)

    Perhatikan baris yang di highlight diatas, dapat kita lihat tabel managers memliki CHECK constraint dengan nama managers_chk_1. Untuk melihat conditional constrain tersebut statement SHOW yang diikuti dengan query CREATE TABLE untuk mengetahu detail tentang constraint tersebut.

    show CREATE TABLE managers;
    Output 
    +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| managers | CREATE TABLE `managers` (
`playerID` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`yearID` smallint NOT NULL,
`teamID` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`lgID` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`inseason` smallint NOT NULL,
`G` smallint DEFAULT NULL,
`W` smallint DEFAULT NULL,
`L` smallint DEFAULT NULL,
`rank` smallint DEFAULT NULL,
`plyrMgr` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
PRIMARY KEY (`yearID`,`teamID`,`inseason`),
CONSTRAINT `managers_chk_1` CHECK ((`yearID` <= 2020))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

    Dari informasi diatas kita dapat mengetahui bahwa constrain check managers_chk_1 akan memfilter nilai insert atau update hanya jika nilai dari yearid tidak lebih dari 2020.

Using Insert Statement

Ada dua cara untuk memasuka data pada table menggunakan INSERT. Anda dapat menggunakan penyisipan tunggal atau penyisipan ganda.

Single Row Insert

untuk menysipkan satu baris kedalam table gunakan syntax berikut;

INSERT INTO database_name.table_name (column_name1, column_name2, ... ,  column_namex)
VALUES (values_1, values_2, ... , values_x);

Statemen INSERT membuat anda dapat menambah nilai pada table collegeplaying. Untuk membuat query insert tepat dalam memasukan nilainya kedalam baris pada table collegeplaying, anda harus menambahkan nilai-nilai tersebut menggunakan statemen VALUE, sebagaimana query dibawah ini.

INSERT INTO collegeplaying (playerID, schoolID, yearID) VALUES('blaloha01', 'sandiegost', 1999);
Output 
Query OK, 1 row affected (0.04 sec)

Dari retrun value query diatas, ada satu buah baris yang affected. Jika kita men-SELECT tabel collegeplaying menggunakan query dibawah ini akan kita dapatkan penyisipan data yang telah kita lakukan berhasil tercatat pada table tersebut.

select * from collegeplaying where playerid='blaloha01';
Output 
+-----------+-------------+--------+
| playerID  | schoolID    | yearID |
+-----------+-------------+--------+
| blaloha01 | sandiegost  |   1999 |
+-----------+-------------+--------+
1 rows in set (0,02 sec)

Anda juga dapat menyisipkan data pada table tanpa harus menspesifikasikan kolom jika anda yakin menempatkan nilai pada statement VALUE dengan urutan yang benar.

INSERT INTO collegeplaying VALUES('blaloha01', 'sandiegost',2000);

Misalkan, anda ingin memasukan nilai hanya pada kolom tertentu, maka anda harus menspesifikasikan nama kolom dan disesuai urutan nilainya dengan statement VALUES.

INSERT INTO collegeplaying (playerID, yearID) VALUES ('blaloha01',2001);
Output hasil query select setelah insert statement diatas 
+-----------+-------------+--------+
| playerID  | schoolID    | yearID |
+-----------+-------------+--------+
| blaloha01 | NULL        |   2001 |
| blaloha01 | sandiegost  |   1999 |
| blaloha01 | sandiegost  |   2000 |
+-----------+-------------+--------+
8 rows in set (0,02 sec)

Dapat kita lihat nilai NULL diatas (highlight) karena kita tidak menspesifikasikan kolom schoolID pada query terakhir yang kita buat.

Struktur kolom collegePlaying

DESCRIBE collegePlaying;
Output
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| playerID | varchar(9)  | NO   | MUL | NULL    |       |
| schoolID | varchar(15) | YES  | MUL | NULL    |       |
| yearID   | smallint    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0,00 sec)

Misalkan, secara tidak sengaja kita ingin mengisi kolom tertentu saja namun lupa menuliskan nama kolom-kolom yang dipilih tersebut, maka MySql akan mengeluarkan exception.

INSERT INTO collegeplaying ---(1)! 
VALUES ('blaloha01',2001);
  1. Tidak menspesifikasikan kolom-kolom seperti INSERT INTO collegeplaying (playerID, yearID)
Output 
ERROR 1136 (21S01): Column count doesn't match value count at row 1

Oke sekarang kita akan membahas tentang converting data type pada statement insert. Misalkan, anda tidak sengaja salah menaruh urutan nilai yang ingin dimasukan, alih-alih memasukan yearID dengan tipe INT, anda malah terbalik dengan schoolID yang bertipe CHAR. Katakan query anda seperti dibawah ini.

INSERT INTO collegeplaying VALUES('blaloha01', 2002, 'sandiegost');
Failure 
ERROR 1366 (HY000): Incorrect integer value: 'sandiegost' for column 'yearID' at row 1

MySql akan mengeluarkan error yang mengatakan ketidak sesuaian antra integer dengan string. Karena MySql tidak dapat mengkonversi ‘sadieogost’ menjadi nilai smallint.

Namun, nilai smallint dapat dikonversi kedalam VARCHAR. Misalkan, anda menulis query dibawah ini,

INSERT INTO collegeplaying (playerID, schoolID) VALUES('blaloha01',2003);
Hasil dari query SELECT collegePlaying 
+-----------+-------------+--------+
| playerID  | schoolID    | yearID |
+-----------+-------------+--------+
| blaloha01 | NULL        |   2001 |
| blaloha01 | 2003        |   NULL |
| blaloha01 | sandiegost  |   1999 |
| blaloha01 | sandiegost  |   2000 |
+-----------+-------------+--------+
4 rows in set (0,00 sec)

Dapat kita lihat, nilai 2003 yang awalnya dalam bentuk smallint di konversi menjadi varchar.

Inserting on colomn that have default constraint

Jika table yang anda masukan data kepada kolom pada table yang memliki default constraint, maka anda dapat mengabaikan, tidak mensepsifikan pada daftar kolom ketika memasukan nilai.

Contoh, saya membuat default value untuk kolom schoolid pada table collegeplaying. 

ALTER TABLE collegeplaying ALTER schoolID SET DEFAULT 'Not Yet Set';
Query hasi DESCRIBE tabel collegePlaying 
+----------+-------------+------+-----+-------------+-------+
| Field    | Type        | Null | Key | Default     | Extra |
+----------+-------------+------+-----+-------------+-------+
| playerID | varchar(9)  | NO   | MUL | NULL        |       |
| schoolID | varchar(15) | YES  | MUL | Not Yet Set |       |
| yearID   | smallint    | YES  |     | NULL        |       |
+----------+-------------+------+-----+-------------+-------+
3 rows in set (0,00 sec)

Lalu jalankan query tanpa menaruh schoolID pada daftar spesifikasi insert data. Maka nilai dari kolom schoolID akan diisi dengan default value yang telah kita tetapkan. 

INSERT INTO collegeplaying(playerId,yearID) VALUES('blaloha01',2001);

Hasil dari query SELECT collegePlaying 
+-----------+-------------+--------+
| playerID  | schoolID    | yearID |
+-----------+-------------+--------+
| blaloha01 | NULL        |   2001 |
| blaloha01 | 2003        |   NULL |
| blaloha01 | Not Yet Set |   2001 |
| blaloha01 | sandiegost  |   1999 |
| blaloha01 | sandiegost  |   2000 |
+-----------+-------------+--------+
5 rows in set (0,00 sec)

Abstract

Demi keutuhan struktur data pada schema lahmansbaseballdb, setiap perubahan pada struktur tabel pada schema tersebut lebih baik dikembalikan agar tidak membuat anda bingung ketika anda menjalankan query pada bab-bab sebelumnya yang menyebabkan hasil keluarannya berbeda dengan yang ada di Output

Drop default value pada tabel collegeplaying
ALTER TABLE collegeplaying ALTER schoolID DROP DEFAULT;
Inserting on colomn that have check constraint

Jika anda ingin meng-insert data pada kolom yang memliki check constraint, anda harus memenuhi kondisi tersebut. Sebagai ilustrasi mari kita buat sebuah check constraint pada tabel collegeplaying dengan kondisi kolom yearID harus kurang dari tahun 2022.

alter table collegeplaying 
ADD CONSTRAINT  hck_collage_yearID  CHECK (yearID < 2022);

Untuk mengechecknya anda dapat melihat pada table_constraint menggunakan query dibawah ini.

select * 
from information_schema.table_constraints
WHERE table_name='collegeplaying';
Output 
+--------------------+-------------------+--------------------+-------------------+----------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME    | TABLE_SCHEMA      | TABLE_NAME     | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+--------------------+-------------------+----------------+-----------------+----------+
| def                | lahmansbaseballdb | hck_collage_yearID | lahmansbaseballdb | collegeplaying | CHECK           | YES      |
+--------------------+-------------------+--------------------+-------------------+----------------+-----------------+----------+
1 row in set (0,01 sec)

Lalu mari kita uji coba meng-insert value dengan yearID diatas atau sama dengan 2022.

INSERT INTO collegeplaying VALUES('blaloha01', 'sandiegost', 2023);
Failure 
ERROR 3819 (HY000): Check constraint 'hck_collage_yearID' is violated.

Oke, MySql akan memberitahu kita, nilai yang kita insert melanggar kondisi dari Check constraint dari hck_collage_yearID.

Abstract

Demi keutuhan struktur data pada schema lahmansbaseballdb, setiap perubahan pada struktur tabel pada schema tersebut lebih baik dikembalikan agar tidak membuat anda bingung ketika anda menjalankan query pada bab-bab sebelumnya yang menyebabkan hasil keluarannya berbeda dengan yang ada di Output

Drop Check Constraint pada tabel collegeplaying
ALTER TABLE collegeplaying DROP CONSTRAINT hck_collage_yearID;

Multiple row Inserts

Untuk meng-insert beberapa baris anda dapat menggunakan syntax dibawah ini

INSERT INTO table_name
VALUES ('x_1','x_2','x_3','x_n'),
('y_1','y_2','y_3','y_n'),
('z_1','z_2','z_3','z_n')

--Equal to--

INSERT INTO table_name
('columns_1','columns_2', 'columns_3')
VALUES ('x_1','x_2','x_3','x_n'),
('y_1','y_2','y_3','y_n'),
('z_1','z_2','z_3','z_n')

Oke, pada kasus ini mari kita coba untuk menginsert pada table collegeplaying menggunakan query dibawah ini

INSERT INTO collegeplaying
VALUES ('blaloha01','sandiegost', 2017),
('blaloha01','sandiegost', 2018),
('blaloha01','sandiegost', 2019);
Output 
+-----------+-------------+--------+
| playerID  | schoolID    | yearID |
+-----------+-------------+--------+
| blaloha01 | NULL        |   2001 |
| blaloha01 | 2003        |   NULL |
| blaloha01 | Not Yet Set |   2001 |
| blaloha01 | sandiegost  |   1999 |
| blaloha01 | sandiegost  |   2000 |
| blaloha01 | sandiegost  |   2017 |
| blaloha01 | sandiegost  |   2018 |
| blaloha01 | sandiegost  |   2019 |
+-----------+-------------+--------+
8 rows in set (0,00 sec)

Inserting data from one table into another table

Untuk meng-insert data dari satu table kedalam table lain ada bebera opsi untuk melakukannya;

  1. Buat table baru dan insert data kedalam table tersebut dari tabel yang sudah ada
  2. Insert data kedalam table yang sudah ada berdasarkan data dari tabel yang sudah ada juga.

Info

Semua table yang dibuat sementara akan kita drop langsung

Untuk opsi membuat tabel baru dan memasukan data dari tabel lain gunakan syntax dibawah ini

CREATE TABLE newtable
SELECT * FROM existingtable;

Oke mari sekarang kita coba menggunakan database lahmansbaseballdb. Kita akan membuat tabel baru dan menyalin semua data dari tabel managers ke tabel baru tersebut.

CREATE TABLE managers_copy SELECT * FROM managers;
Output 
Query OK, 3504 rows affected (0.11 sec)
Records: 3504  Duplicates: 0  Warnings: 0

Alhamdulillah berhasil, query diatas meng-copy seluruh kondisi dan data dari tabel managers. Sekarang mari kita buktikan isi dari keuda table tersebut. Saya hanya menampilkan 10 record teratas dan mensandingkan kedua tabel tersebut menggunakan INNER JOIN.

select asli.playerID, asli.teamID, asli.yearID, 'Asli' as ket, 
salinan.playerID, salinan.teamID, salinan.yearID, 'Salinan' as ket
FROM managers AS asli
INNER JOIN managers_copy AS salinan
ON asli.playerID = salinan.playerID
LIMIT 10;
Output 
+-----------+--------+--------+------+-----------+--------+--------+---------+
| playerID  | teamID | yearID | ket  | playerID  | teamID | yearID | ket     |
+-----------+--------+--------+------+-----------+--------+--------+---------+
| wrighha01 | PHI    |   1893 | Asli | wrighha01 | BS1    |   1871 | Salinan |
| wrighha01 | PHI    |   1892 | Asli | wrighha01 | BS1    |   1871 | Salinan |
| wrighha01 | PHI    |   1891 | Asli | wrighha01 | BS1    |   1871 | Salinan |
| wrighha01 | PHI    |   1890 | Asli | wrighha01 | BS1    |   1871 | Salinan |
| wrighha01 | PHI    |   1890 | Asli | wrighha01 | BS1    |   1871 | Salinan |
| wrighha01 | PHI    |   1889 | Asli | wrighha01 | BS1    |   1871 | Salinan |
| wrighha01 | PHI    |   1888 | Asli | wrighha01 | BS1    |   1871 | Salinan |
| wrighha01 | PHI    |   1887 | Asli | wrighha01 | BS1    |   1871 | Salinan |
| wrighha01 | PHI    |   1886 | Asli | wrighha01 | BS1    |   1871 | Salinan |
| wrighha01 | PHI    |   1885 | Asli | wrighha01 | BS1    |   1871 | Salinan |
+-----------+--------+--------+------+-----------+--------+--------+---------+
10 rows in set (0,00 sec)

Kita juga dapat memfilter record yang dimasukan pada table baru menggunakan statement #!sql WHERE`. Misalkan kita ingin membuat tabel baru untuk mengabil data dari table managers untuk setiap managers yang juga player.

CREATE TABLE managers_players_copy
SELECT * FROM managers
WHERE plyrMgr = 'Y';
Query Select dan ouput table managers_players_copy
Query Select
SELECT * FROM managers_players_copy
LIMIT 7;
Output
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| playerID  | yearID | teamID | lgID | inseason | G    | W    | L    | rank | plyrMgr |
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| wrighha01 |   1871 | BS1    | NA   |        1 |   31 |   20 |   10 |    3 | Y       |
| woodji01  |   1871 | CH1    | NA   |        1 |   28 |   19 |    9 |    2 | Y       |
| paborch01 |   1871 | CL1    | NA   |        1 |   29 |   10 |   19 |    8 | Y       |
| lennobi01 |   1871 | FW1    | NA   |        1 |   14 |    5 |    9 |    8 | Y       |
| deaneha01 |   1871 | FW1    | NA   |        2 |    5 |    2 |    3 |    8 | Y       |
| fergubo01 |   1871 | NY2    | NA   |        1 |   33 |   16 |   17 |    5 | Y       |
| mcbridi01 |   1871 | PH1    | NA   |        1 |   28 |   21 |    7 |    1 | Y       |
+-----------+--------+--------+------+----------+------+------+------+------+---------+
7 rows in set (0,00 sec)

Oke katakan kita ingin membuat sebuah table yang tidak memliki data (kosong) namun memliki struktur data dari tabel yang sudah ada. Anda harus menambahkan conditional logic yang nilainya selalu false pada setiap row.

CREATE TABLE blank_mgrs_plyrs
SELECT * FROM managers
WHERE 1 = 0;
Describe tabel blank_mgrs_plyrs 
describe blank_mgrs_plyrs;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| playerID | varchar(10) | YES  |     | NULL    |       |
| yearID   | smallint    | NO   |     | NULL    |       |
| teamID   | varchar(3)  | NO   |     | NULL    |       |
| lgID     | varchar(2)  | YES  |     | NULL    |       |
| inseason | smallint    | NO   |     | NULL    |       |
| G        | smallint    | YES  |     | NULL    |       |
| W        | smallint    | YES  |     | NULL    |       |
| L        | smallint    | YES  |     | NULL    |       |
| rank     | smallint    | YES  |     | NULL    |       |
| plyrMgr  | varchar(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
10 rows in set (0,01 sec)

Tabel diatas hanya menyalin struktur data, tidak ada record yang salin dari tabel managers.

Jika anda ingin memasukan data kedalam table yang sudah ada dari tabel yang sudah ada gunakan sytanx dibawah ini. 

INSERT INTO existingtable
SELECT * FROM anotherexistingtable

Sebagai simulasi kita coba masukan data pada managers kedalam tabel blank_mgrs_plyrs. Sebagimana yang telah kita ketahui, tabel tersebut hanya menyalin struktur, tidak dengan record. Maka kali ini kita akan meng-copy nilai dari tabel managers sebanyak 10 record paling atas saja.

INSERT INTO blank_mgrs_plyrs
SELECT * FROM managers LIMIT 10;
Output Select table managers 
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| playerID  | yearID | teamID | lgID | inseason | G    | W    | L    | rank | plyrMgr |
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| wrighha01 |   1871 | BS1    | NA   |        1 |   31 |   20 |   10 |    3 | Y       |
| woodji01  |   1871 | CH1    | NA   |        1 |   28 |   19 |    9 |    2 | Y       |
| paborch01 |   1871 | CL1    | NA   |        1 |   29 |   10 |   19 |    8 | Y       |
| lennobi01 |   1871 | FW1    | NA   |        1 |   14 |    5 |    9 |    8 | Y       |
| deaneha01 |   1871 | FW1    | NA   |        2 |    5 |    2 |    3 |    8 | Y       |
| fergubo01 |   1871 | NY2    | NA   |        1 |   33 |   16 |   17 |    5 | Y       |
| mcbridi01 |   1871 | PH1    | NA   |        1 |   28 |   21 |    7 |    1 | Y       |
| hastisc01 |   1871 | RC1    | NA   |        1 |   25 |    4 |   21 |    9 | Y       |
| pikeli01  |   1871 | TRO    | NA   |        1 |    4 |    1 |    3 |    6 | Y       |
| cravebi01 |   1871 | TRO    | NA   |        2 |   25 |   12 |   12 |    6 | Y       |
+-----------+--------+--------+------+----------+------+------+------+------+---------+
10 rows in set (0,00 sec)

Abstract

Demi kelancaran penggunaan semua query-query diatas, ada baiknya untuk menge-DROP semua tabel atas query yang dijalankan agar anda bisa menggunakan kembali query-query diatas tanpa ada error.

Drop table
drop table managers_copy;
drop table managers_players_copy;
drop table blank_mgrs_plyrs;

Deleting data from tables

Untuk menghapus data dari data yang sudha dimasukan kedalam database, gunakan statement DELETE. Anda dapat menghapus semua record dari sebuah table atau gunakan klausa WHERE untuk meghapus beberapa record dari sebuah tabel.

Using DELETE statement

DELETE FROM database.table_name

Using DELETE statement with WHERE statement

Gunakan syntax dibawha ini untuk menghapus record dengan kondisi WHERE

DELETE FROM database.table_name
WHERE <{where_expression}>;

Updating data in table

Untuk memperbaharui atau mengkoreksi nilai pada semua record didalam tabel anda dapat menggunakan UPDATE, lihatlah syntax dibawah ini,

UPDATE databasename.table_name 
SET `column_name` = <{value_1}>,
`column_name_2` = <{value_2}>,
`column_name_3` = <{value_3}>

Oke sekarang mari kita coba buat implementasi menggunakan database lahmansbaseballdb. Sebelumnya kita telah membuat tabel salinan dari managers namun telah kita drop. Untuk itu mari kita buat lagi table tersebut dan kita update semua record dari kolom lgID menjadi string "--"

Crate and insert tabel
CREATE TABLE managers_copy SELECT * FROM managers;
Update all data
UPDATE managers_copy SET lgID = "--";
Output query select tabel managers_copy 
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| playerID  | yearID | teamID | lgID | inseason | G    | W    | L    | rank | plyrMgr |
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| wrighha01 |   1871 | BS1    | --   |        1 |   31 |   20 |   10 |    3 | Y       |
| woodji01  |   1871 | CH1    | --   |        1 |   28 |   19 |    9 |    2 | Y       |
| paborch01 |   1871 | CL1    | --   |        1 |   29 |   10 |   19 |    8 | Y       |
| lennobi01 |   1871 | FW1    | --   |        1 |   14 |    5 |    9 |    8 | Y       |
| deaneha01 |   1871 | FW1    | --   |        2 |    5 |    2 |    3 |    8 | Y       |
| fergubo01 |   1871 | NY2    | --   |        1 |   33 |   16 |   17 |    5 | Y       |
| mcbridi01 |   1871 | PH1    | --   |        1 |   28 |   21 |    7 |    1 | Y       |
| hastisc01 |   1871 | RC1    | --   |        1 |   25 |    4 |   21 |    9 | Y       |
| pikeli01  |   1871 | TRO    | --   |        1 |    4 |    1 |    3 |    6 | Y       |
| cravebi01 |   1871 | TRO    | --   |        2 |   25 |   12 |   12 |    6 | Y       |

Namun jika anda hanya ingin mengupdate beberapa baris saja menggunakan kriteria tertentu anda gunakan WHERE. Lihat syntax dibawah ini

UPDATE databasename.table_name 
SET `column_name` = <{value_1}>,
`column_name_2` = <{value_2}>,
`column_name_3` = <{value_3}>
WHERE <{where_expression}>

Updating table from another existing table

Oke, katakan kita inign mengubah table managers_copy diatas khususnya kolom lgID menjadi nilai yang aslinya. Berarti kita butuh tabel managers, mengambil datanya dan ditaruh kedalam tabel managers_copy

UPDATE managers_copy AS mc
INNER JOIN managers AS m
ON m.playerID = mc.playerID AND
m.yearID = mc.yearID
AND m.teamID = mc.teamID
SET mc.lgID = m.lgID;
Output query select tabel managers_copy setelah diisi dengan nilai aslinya pada lgID 
-----------+--------+--------+------+----------+------+------+------+------+---------+
| playerID  | yearID | teamID | lgID | inseason | G    | W    | L    | rank | plyrMgr |
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| wrighha01 |   1871 | BS1    | NA   |        1 |   31 |   20 |   10 |    3 | Y       |
| woodji01  |   1871 | CH1    | NA   |        1 |   28 |   19 |    9 |    2 | Y       |
| paborch01 |   1871 | CL1    | NA   |        1 |   29 |   10 |   19 |    8 | Y       |
| lennobi01 |   1871 | FW1    | NA   |        1 |   14 |    5 |    9 |    8 | Y       |
| deaneha01 |   1871 | FW1    | NA   |        2 |    5 |    2 |    3 |    8 | Y       |
| fergubo01 |   1871 | NY2    | NA   |        1 |   33 |   16 |   17 |    5 | Y       |
| mcbridi01 |   1871 | PH1    | NA   |        1 |   28 |   21 |    7 |    1 | Y       |
| hastisc01 |   1871 | RC1    | NA   |        1 |   25 |    4 |   21 |    9 | Y       |
| pikeli01  |   1871 | TRO    | NA   |        1 |    4 |    1 |    3 |    6 | Y       |
| cravebi01 |   1871 | TRO    | NA   |        2 |   25 |   12 |   12 |    6 | Y       |

Using transactions to save or revert changes

Transaction pada SQL adalah pengelompokan dari satu atau lebih perubahan yang terjadi pada sebuah database. Fitur tersebut membantu kita untuk memastikan kondisi yang konsisten pada sebuah database. Pola umum yang paling sering digunakan pada fitur transaction ini adalah COMIT dan ROLLBACK. Commit membuat perubahan yang terjadi menjadi kondisi yang permanen sedangkan rollback akan membatalkan perubahan pada.

Ada empat properti yang harus anda pahami dari trasaction ini.

  • Atomicty → Properti ini memastikan semua perubahan dalam sebuah transaction berhasil secara sempurna. Jiak berhasil maka perubahan tersebut akan ditetapkan (committed), akan tetapi jika gagal maka setiap perubahan akan dikembalikan, rolled back.

  • Consistency → Konsistensi ini akan memastikan setiap perubahan tidak akan mengganggu database's integrity (baca lagi tentang database's integrity disini), seperti constraint. Perubahan yang gagal karena ada kesalahan akibat dari database integrity akan di kembalikan ke kondisi semua, rolled back.

  • Isolation → Semua trasactions terisolasi dengan transactions lainya. Sehingga tidak ada transactions yang dapat menggangu transactions yang lainnya meskipun berjalan secara bersamaan.

  • Durability → Ketika transactions telah di commited, gangguan apapun yang terjadi pada ketersedian database seperti system failure, atau restart, tidak akan berdampak pada konsistensi dari data.

Properti diatas dapat kita singkat menjadi ACID (Atomicity, Consistency, Isolation, Durability). Untuk lebih memahami saya akan jelaskan menggunakan kasus transfer antar rekening.

Misalkan kita ingin mentransfer dana dari rekening checking ke kerekening penyimpanan yang lainnya. Untuk atomicity, transfer akan dibatalkan kecuali jika saldo pada rekening checking dan rekening peyimpanan berubah. Karena anda tentu tidak ingin saldo anda berkurang pada rekening checking namun tidak bertambah pada rekening penyimpanan. Untuk consistency, jika terjadi gangguan lainnya atau gangguan yang disebabkan oleh database ketika proses transfer telah dilakukan akan menyebabkan pengembalian kondisi (rolled back) sehingga tidak berdampkan pada rekening checking dan rekening peyimpanan. Unuk Isolation, semua transaksi transfer yang ada bank tersebut terisolasi, jadi transfer yang anda lakukan tidak akan menyasar pada rekening yang bukan anda maksudkan. Untuk Durability, setelah transfer telah ditetapkan (committed) kedalam database, semua kendalan atau gangguan tidak akan berpengaruh pada saldo anda di database.

Understanding a SQL transaction

Kita telah mengetauhi transaction itu apa, yaitu pengelompokan dari satu atau lebih perubahan yang ditunda, dapat di commit atau rollback. Pertama kita akan membahas istilah dari kunci atau kata-kata yang digunakna oleh SQL Transaction.

  • Untuk memulai transaction akan melibatkan penggunakan kata kunci START TRANSACTION atau BEGIN. Kata kunci tersebut menandakan awal dari query yang kita ingin jalankan secara bersamaan. Umumnya, anda dapat menjalankan query tunggal tanpa menggunakan kata kunci tersebut (dan jika terjadi error, maka secara otomatis SQL akan mengembalikan seperti sebelumnya). Namun sangat penting untuk mengelompokan query terkait dengan UPDATE, INSERT atau DELETE kedalam satu buah transaction.

  • Menetapkan perubahan (changes) akan meenjadikan perubah tersebut permanen. Anda dapat menggunakan kata kunci COMMIT pada akhir dari query.

  • Kata kunci ROLLBACK yang diletakan pada block akhir query akan membatalkan query yang anda lakukan.

  • Auto-commit bawaanya aktif pada MySql. Namun anda dapat mematikan fitur tersebut menggunakan keyword SET autocommit. Dengan Auto-commit, anda tidak dapat membatalkan perubahan yang anda lakukan, akan tetapi jika terjadi error, perubahan akan otomatis dikembalikan, rolled back.

Implicit and Explicit Transaction

Sekarang kita akan membas tentang implicit dan explicit transacitions.

  • Implicit Transaction → Anda tidak menjelaskan penggunakan perintah transaction, sebagaimana yang telah disebutkan pada point sebelumnya. Implicit transaction menjalankan auto-commit dari sebuah perintah yang dijalankan. Beberapa hal yang anda jalankan akan memaksa auto-commit, tidak memperhatikan apakah anda menggunakan perintah transaction atau tidak Diantaranya adalah perubahan pada DDL (Data Definition Language), termasuk altering atau membuat table baru.

  • Explicit Transaction → Anda menjelaskan penggunakan perintah transaction. Tidak akan di commit hingga anda menggunakan perintah COMMIT. Namun, prilaku ini akan ditiban jika anda menggunakan perintah DDL pada explicit transaction. Transaction tersebut akan dikembalikan menjadi auto-commit.

Status Transacion

Learning the SQL syntax for SQL transactions

Ada beberapa kata kunci didalam MySql untuk memastika query kita berada didalam sebuah transaction. Untuk memulai sebuah transaction didalam MySql, gunakan kata kunci START TRANSACTION atau BEGIN. Untuk menetapkan transaction gunakan kata kunci COMMIT. Untuk mengembalikan kondisi gunakan katakunci ROLLBACK.

Untuk melihat aksi dari transaction mari kita uji coba menggunakan tabel managers_copy yang struktur dan isinya adalah imitasi dari tabel managers. Jika anda tidak memliki managers_copy gunakan query dibawah ini.

Duplkasi tabel managers_copy jika tidak ada
---Duplikasi tabel managers
CREATE TABLE managers_copy
SELECT * FROM managers;

Dibawah ini adalah query untuk mengetahui bagaimana hasil dari perintah transaction.

BEGIN;
UPDATE managers_copy
SET lgID = "--";
Output SELECT tabel managers_copy state partially commited 
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| playerID  | yearID | teamID | lgID | inseason | G    | W    | L    | rank | plyrMgr |
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| wrighha01 |   1871 | BS1    | --   |        1 |   31 |   20 |   10 |    3 | Y       |
| woodji01  |   1871 | CH1    | --   |        1 |   28 |   19 |    9 |    2 | Y       |
| paborch01 |   1871 | CL1    | --   |        1 |   29 |   10 |   19 |    8 | Y       |
| lennobi01 |   1871 | FW1    | --   |        1 |   14 |    5 |    9 |    8 | Y       |
| deaneha01 |   1871 | FW1    | --   |        2 |    5 |    2 |    3 |    8 | Y       |

Query update diatas berada dalam transactions, yang artinya jika hasil yang dikeuluar tidak sesuai dengan yang diinginkan anda dapat mengembalikannya menggunakan statement ROLLBACK. Namun jika sesuai anda dapat membuat hasil tersebut secara permanen menggunakan perintah COMMIT.

Jika anda tidak meng-commit transaction segera, penggunakan pada session lain saat meng-query table managers_copy tidak akan melihat perubahan apapun hingga anda menggunakan perintah COMMIT.

Ketika menguji sebuah query, sangatlah disarankan untukt tidak menuliskan perintah COMMIT setelah query anda langsung sebelu anda menguji hasil dari query yang ada dalam transaction, karena jika hasilnya dari UPDATE, DELETE atau INSERT tidak sesuai dengan yang anda harapkan anda dapat mengembalikannya menggunakan perintah ROLLBACK.

Outuput select tabel managers_copy setelah di Rollback 
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| playerID  | yearID | teamID | lgID | inseason | G    | W    | L    | rank | plyrMgr |
+-----------+--------+--------+------+----------+------+------+------+------+---------+
| wrighha01 |   1871 | BS1    | NA   |        1 |   31 |   20 |   10 |    3 | Y       |
| woodji01  |   1871 | CH1    | NA   |        1 |   28 |   19 |    9 |    2 | Y       |
| paborch01 |   1871 | CL1    | NA   |        1 |   29 |   10 |   19 |    8 | Y       |
| lennobi01 |   1871 | FW1    | NA   |        1 |   14 |    5 |    9 |    8 | Y       |
| deaneha01 |   1871 | FW1    | NA   |        2 |    5 |    2 |    3 |    8 | Y       |

Diatas kita telah mencoba transaction pada pemanipulasian data, yang mana kita ketahui ada didalam skop Data Manipulation Language. Mari kita uji coba pada query yang berkaitan dengan Data Definition Language. Sebelum itu kita harus membuat sebuah table baru dengan nama award namun memliki struktur tabel mirip dengan awardsmanagers atau awardsplayers.

Duplikasi struktur awardsmanagers untuk simulasi
    CREATE TABLE awards
    SELECT * FROM awardsmanagers WHERE 1 = 0;

Dibawah ini adalah query DDL dan MDL yang berada didalam sebuah transaction.

START TRANSACTION;
INSERT INTO awards
(SELECT * FROM awardsmanagers)
UNION
(SELECT * FROM awardsplayers); ---(1)!
DROP TABLE managers_copy;
DELETE FROM awards
WHERE awards.awardid = 'BBWAA Manager of the Year';
ROLLBACK;
  1. Dari baris ini hingga keatas akan ter-commit secara implisit

Semua query sebelum perintah DROP TABLE didalam transaction akan ter-commit walaupun anda tidak secara jelas menggunakan COMMIT statement. Berhati-hatilah untuk tidak menempatkan Data Definition Languge di dalam sebuah transaction, karena ini akan menyebabkan setiap perubahan yang tidak kita ingin auto-commit malah menjadi auto-commit walaupun transaction tersebut gagal diselesaikan query didalamnya.

MySql secara bawaanya menyalakan fitur auto-commit, namun MySql juga menyediakan kita cara untuk mematikan auto-commit pada session anda dengan perintah dibawah ini. Untuk menyalakan kembali auto commit anda tiggal menggati dari OFF menjadi ON.

SET autocommit = OFF;

Setelah anda mengeksekusi perintah diatas, anda harus menggunakan perintah COMMIT jika ingin DML anda seperti INSERT, UPDATE, atau DELETE berubah secara permanen walaupun anda tidak menggunakan transaction. Mari kita coba buktikan pada tabel awards, pastikan tabel tersebut kosong, namun jika anda sudah mendrop nya anda dapat membuat lagi menggunakan query ini.

INSERT INTO awards
(SELECT * FROM awardsmanagers LIMIT 5)
UNION
(SELECT * FROM awardsplayers LIMIT 5);

Query DML diatas status Partialy Committed walaupun anda menjalankan query tersebut tidak di dalam sebuah query. Akan tetapi anda mematikan autocommit pada session anda. Sehingga jika anda ingin membuat perubahan tersebut permanen anda harus menjalankan perintah COMMIT.

Info

MySql secara otomatis meng-commit perubahan pada database (Manipulation Data Langauge) kecuali anda menggunakan transaction atau mematikan autocommit.

MySql juga mendukung beberap tambahan pada katakunci tambahan pada transaction, Anda dapat memanfaatkan savepoints didalam sebuah transaction untuk kembali ke titik spesifik tertentu didalam transaction. Anda dapat melakukannya dengan memberikan nama pada SAVEPOINT dan me-rolling back ke titik tersebut. Query dibawah ini menampilkan bagaimana cara menggunakan save point. Untuk query tersebut menggunakan table awards yang telah kita delete semua recod nya.

Pastikan men-delete semua record pada tabel awards
    DELETE FROM awards;

Memulai transactions dan penggunaan SAVEPOINT

BEGIN;
INSERT INTO awards
(SELECT * FROM awardsmanagers LIMIT 5)
UNION
(SELECT * FROM awardsplayers LIMIT 5);
SAVEPOINT insertToAwardsFromManagersAndPlayersAwards;
DELETE FROM awards
WHERE awardId = "Gold Glove";
SAVEPOINT deleteAllGoldGloveAwardId;
UPDATE awards SET notes = "--";
SAVEPOINT editAllNotes;

Saya akan menjelaskans secara detail query diatas dan bagaimana savepoint bekerja. Jika anda menjalankan query tersebut dan anda belum menjalan COMMIT ataupun ROLLBACK maka status perubahan diatas masih dalam status sementara atau partialy committed. Pada awal query kita memulai transaction menggunakan kata kunci BEGIN yang diikuti dengan penetapan titik SAVEPOINT ke-1 pertama, yaitu insertToAwardsFromManagersAndPlayersAwards, pada titik tersebut kita memasukan record kedalam table awards. Savepoint ke-2, deleteAllGoldGloveAwardId, kita menghapus semua awardId "Gold Glove", dan pada savepoint ke-3 mengupdate semua notes.

Misalkan pada titik savepoint deleteAllGoldGloveAwardId adalah kondisi sesuai dengan yang diharapkan, anda dapat me-rollback pada titik tersebut dengan ROLLBACK TO {savepointname} dan langsung meng-commitnya dengan perintah COMMIT. Namun yang anda perlu perhatikan, jika anda hanya menggunakan perintah ROLLBACK maka setiap perubahan dan savepoint akan dihapus dan dikembalikan pada kondisi sebelumnya.

ROLLBACK TO deleteAllGoldGloveAwardId;
COMMIT;
Output query select tabel awards 
+-----------+---------------------------+--------+------+------+-------+
| playerID  | awardID                   | yearID | lgID | tie  | notes |
+-----------+---------------------------+--------+------+------+-------+
| aloufe01  | BBWAA Manager of the Year |   1994 | NL   | NULL | NULL  |
| aloufe01  | TSN Manager of the Year   |   1994 | NL   | NULL | NULL  |
| alstowa01 | TSN Manager of the Year   |   1955 | ML   | NULL | NULL  |
| alstowa01 | TSN Manager of the Year   |   1959 | ML   | NULL | NULL  |
| alstowa01 | TSN Manager of the Year   |   1963 | ML   | NULL | NULL  |
| aaronha01 | Lou Gehrig Memorial Award |   1970 | ML   |      |       |
| aaronha01 | Most Valuable Player      |   1957 | NL   |      |       |
+-----------+---------------------------+--------+------+------+-------+
7 rows in set (0,00 sec)

Sama dengan sebelumnya, jika anda menggunakan perintah dalam scope DDL semisal DROP table ditengah-tengah transaction atau savepoint, semua transaction sebelum titik DDL tersebut akan secara implisit di commit oleh MySql.

Untuk DDL, kita harus membuat sebuah tabel yang nantinya akan kita DROP didalam transaction.

Membuat tabel simulasi & restart kondisi tabel awards
CREATE TABLE IF NOT EXISTS TabelSimulasiDDL(
    `ujiCoba` VARCHAR (1) NOT NULL
);

DELETE FROM awards;

Setelah menjalankan query diatas, jalankan query transaction dibawah ini yang mana didalamnya ada DDL DROP tabel.

    BEGIN;
    INSERT INTO awards
    (SELECT * FROM awardsmanagers LIMIT 5)
    UNION
    (SELECT * FROM awardsplayers LIMIT 5);
    SAVEPOINT insertToAwardsFromManagersAndPlayersAwards;
    DELETE FROM awards
    WHERE awardId = "Gold Glove";
    SAVEPOINT deleteAllGoldGloveAwardId;
    UPDATE awards SET notes = "--";
    SAVEPOINT editAllNotes;
    DROP TABLE TabelSimulasiDDL; ---(1)!
    SAVEPOINT dropTabelSimulasiDDL;
  1. Pada titik ini, semua savepoint diatas (deleteAllGoldGloveAwardId, dan insertToAwardsFromManagersAndPlayersAwards) akan secara implisit di commit oleh MySql.

Katakan kita ingin kembali ke savepoint deleteAllGoldGloveAwardId. Seharusnya, tabel awards memliki records yang sama dengan query transaction sebelumnya. Oke lets we rollback and display table awards.

ROLLBACK TO deleteAllGoldGloveAwardId;

Failure

ERROR 1305 (42000): SAVEPOINT deleteAllGoldGloveAwardId does not exist

Mengapa savepoint tersebut tidak exist, pada kita telah membuatnya pada query diatas ? Yups, karena secara implisit MySql telah meng-commit savepoint tersebut dan menghapusnya.

Output query SELECT tabel awards

+-----------+---------------------------+--------+------+------+-------+
| playerID  | awardID                   | yearID | lgID | tie  | notes |
+-----------+---------------------------+--------+------+------+-------+
| aloufe01  | BBWAA Manager of the Year |   1994 | NL   | NULL | --    |
| aloufe01  | TSN Manager of the Year   |   1994 | NL   | NULL | --    |
| alstowa01 | TSN Manager of the Year   |   1955 | ML   | NULL | --    |
| alstowa01 | TSN Manager of the Year   |   1959 | ML   | NULL | --    |
| alstowa01 | TSN Manager of the Year   |   1963 | ML   | NULL | --    |
| aaronha01 | Lou Gehrig Memorial Award |   1970 | ML   |      | --    |
| aaronha01 | Most Valuable Player      |   1957 | NL   |      | --    |
+-----------+---------------------------+--------+------+------+-------+
7 rows in set (0,00 sec)

Abstract

Demi kelancaran penggunaan semua query-query diatas, ada baiknya untuk menge-DROP semua tabel atas query yang dijalankan agar anda bisa menggunakan kembali query-query diatas tanpa ada error.

Drop table
DROP TABLE IF EXISTS managers_copy, awards;

Modifying Table Structure

untuk memodifikasi sebuah tabel gunakan perintah ALTER TABLE. Perintah tersebut dapat digunakan untuk menambah/add, mengubah/edit, dan menghapus/delete objek-objek yang ada pada sebuah tabel seperti column, tipe data, constraint, dan index.

Prepare table for simulation

Pada sesi ini kita aka membuat schema dan tabel baru untuk simulasi. Jalankan query dibawah ini untuk mengikuti query selanjutnya yang ada pada catatan ini.

Membuat schema
CREATE SCHEMA IF NOT EXISTS schemaSimulasi;
membuat tabelSimulasi
CREATE TABLE IF NOT EXISTS schemaSimulasi.personal (
    id INT AUTO_INCREMENT ,
    name VARCHAR(30) NOT NULL,
    jmlAnak SMALLINT DEFAULT 0,
    schoolId VARCHAR(4),
    yaerStart SMALLINT, ---(1)!
    yearGraduate SMALLINT,
    PRIMARY KEY (id),
    KEY fk_schoolId(schoolId)
    );
  1. Terdapat typo, akan seharusnya yearStart buka yaerStart
Membuat tabelAnakSimulasi
CREATE TABLE IF NOT EXISTS schemaSimulasi.sekolah(
    schoolId VARCHAR(4),
    schoolName VARCHAR(30),
    addressSchool VARCHAR(50),
    PRIMARY KEY (schoolId),
    CONSTRAINT `FK_schoolID` FOREIGN KEY (schoolId) REFERENCES schemaSimulasi.personal(`schoolId`)
    ON DELETE RESTRICT ON UPDATE RESTRICT 
);
insert data pada personal
USE schemaSimulasi;
INSERT INTO personal
(name, schoolId, jmlAnak, yaerStart, yearGraduate)
 VALUES
("Muhammad Farras Ma'ruf", 'UTBG',2, 2013, 2017),
("Tania Dwi Haryanti", 'FTGT',2, 2017, 2020),
("Muhammad Faris Ma'ruf", NULL, NULL, NULL, NULL),
("Nu'man Noah Ma'ruf", NULL, NULL, NULL, NULL),
("Hilyah Ma'ruf", NULL, NULL, NULL, NULL);
insert data pada sekolah
USE schemaSimulasi;
INSERT INTO sekolah
(schoolId, schoolName, addressSchool)
 VALUES
("UTBG", "Universitas Terbuka Bogor", "Jl. Raya Parung"),
("FTGT", 'Future Gate Boarding School', "Bekasi raya");
Informasi tabel personal dan sekolah
Informasi struktur tabel personal
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| name         | varchar(30) | NO   |     | NULL    |                |
| jmlAnak      | smallint    | YES  |     | 0       |                |
| schoolId     | varchar(4)  | YES  | MUL | NULL    |                |
| yaerStart    | smallint    | YES  |     | NULL    |                |
| yearGraduate | smallint    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0,00 sec)
Informasi struktur tabel sekolah
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| schoolId      | varchar(4)  | NO   | PRI | NULL    |       |
| schoolName    | varchar(30) | YES  |     | NULL    |       |
| addressSchool | varchar(50) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0,00 sec)

SELECT * FROM information_schema.table_constraints WHERE table_schema = "schemaSimulasi"; 

+--------------------+-------------------+-----------------+----------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA   | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+----------------+------------+-----------------+----------+
| def                | schemaSimulasi    | PRIMARY         | schemaSimulasi | personal   | PRIMARY KEY     | YES      |
| def                | schemaSimulasi    | PRIMARY         | schemaSimulasi | sekolah    | PRIMARY KEY     | YES      |
| def                | schemaSimulasi    | FK_schoolID     | schemaSimulasi | sekolah    | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+----------------+------------+-----------------+----------+
3 rows in set (0,00 sec)

Adding Columns

Katakan anda ingin menambahkan tempat dan tanggal lahir pada tabel personal, untuk itu jalankan query dibawah ini.

ALTER TABLE personal
ADD COLUMN tempatLahir VARCHAR(15) NULL AFTER name,
ADD COLUMN tanggalLahir DATE NULL AFTER tempatLahir,
ADD COLUMN keterangan TEXT NULL;
Describe personal 
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| name         | varchar(30) | NO   |     | NULL    |                |
| tempatLahir  | varchar(15) | YES  |     | NULL    |                |
| tanggalLahir | date        | YES  |     | NULL    |                |
| jmlAnak      | smallint    | YES  |     | 0       |                |
| schoolId     | varchar(4)  | YES  | MUL | NULL    |                |
| yaerStart    | smallint    | YES  |     | NULL    |                |
| yearGraduate | smallint    | YES  |     | NULL    |                |
| keterangan   | text        | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
9 rows in set (0,00 sec)

Dropping a Column

Untuk mengehapus kolom anda dapat menggunakan DROP. Misalkan kita ingin menge-drop kolom keterangan yang baru saja kita buat di Adding Columns

ALTER TABLE personal
DROP COLUMN keterangan;
Describe personal 
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| name         | varchar(30) | NO   |     | NULL    |                |
| tempatLahir  | varchar(15) | YES  |     | NULL    |                |
| tanggalLahir | date        | YES  |     | NULL    |                |
| jmlAnak      | smallint    | YES  |     | 0       |                |
| schoolId     | varchar(4)  | YES  | MUL | NULL    |                |
| yaerStart    | smallint    | YES  |     | NULL    |                |
| yearGraduate | smallint    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
8 rows in set (0,00 sec)

Namun anda akan mendapatkan error jika mencoba men DROP bagain dari hubungan kolom FOREIGN KEY.

ALTER TABLE sekolah
DROP COLUMN schoolId;
Failure 
ERROR 1828 (HY000): Cannot drop column 'schoolId': needed in a foreign key constraint 'FK_schoolID'

Jika anda ingin tetap menghapusnya, setidaknya anda harus menghapus CONSTRAINT FOREIGN KEY terlebih dahulu sebelum DROP kolom.

ALTER TABLE sekolah
DROP FOREIGN KEY `FK_schoolID`,
DROP COLUMN schoolId;

Renaming Column

Sebelumnya kita telah salah mengetik nama kolom yareStart pada tabel personal. Sekarang kita harus membetulkan nama tersebut.

ALTER TABLE personal
CHANGE COLUMN yaerStart yearStart SMALLINT;

Output

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| name         | varchar(30) | NO   |     | NULL    |                |
| tempatLahir  | varchar(15) | YES  |     | NULL    |                |
| tanggalLahir | date        | YES  |     | NULL    |                |
| jmlAnak      | smallint    | YES  |     | 0       |                |
| schoolId     | varchar(4)  | YES  | MUL | NULL    |                |
| yearStart    | smallint    | YES  |     | NULL    |                |
| yearGraduate | smallint    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
8 rows in set (0,00 sec)

Hasil diatas (highlight) nama kolom sudah tidak typo lagi.

Changing Data Type of a Columns

Katakan kita ingin mengganti tipe data di tabel personal pada kolom yearStart dan yearGraduate yang sebelumnya smallint menjadi YEAR.

ALTER TABLE personal
CHANGE COLUMN yearStart yearStart YEAR,
CHANGE COLUMN yearGraduate yearGraduate YEAR;
Output describe 
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| name         | varchar(30) | NO   |     | NULL    |                |
| tempatLahir  | varchar(15) | YES  |     | NULL    |                |
| tanggalLahir | date        | YES  |     | NULL    |                |
| jmlAnak      | smallint    | YES  |     | 0       |                |
| schoolId     | varchar(4)  | YES  | MUL | NULL    |                |
| yearStart    | year        | YES  |     | NULL    |                |
| yearGraduate | year        | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
8 rows in set (0,00 sec)

Warning

Anda harus memperhatikan dan telti jika ingin hanya menggati tipe data dari kolom, nama kolom pertama dan kedua harus sama persis jika tidak ingin nama kolom berganti.

Dan seandainya kita ingin mengganti maksimal panjang kolom schoolId menjadi 3. Gunakan query dibawah ini

ALTER TABLE personal
CHANGE COLUMN schoolId schoolId VARCHAR(3);

Failure

ERROR 1265 (01000): Data truncated for column 'schoolId' at row 1

Anda tidak dapat menggati menjadi maksimal 3 karakter karena data yang terdapat nilai pada kolom tersebut yang panjang karakternya lebih dari 3. Solusinya anda dapat menetapkan maksimal karakter yang lebih tinggi (mengikuti panjang karakter terpanjang pada record di kolom tersebut) atau memotong terlebih dahulu record pada kolom tersebut.

Selain itu, anda ternyata ingin mengubah kolom schoolId lagi menjadi tipe integer

ALTER TABLE personal
CHANGE COLUMN schoolId schoolId INT;

Failure

ERROR 1366 (HY000): Incorrect integer value: 'UTBG' for column 'schoolId' at row 1

Opps, ternyata error juga. Kita tidak bisa mengkonversi nilai pada kolom tersebut seprti UTBG kedalam bentuk integer. Hal ini sama dengan kasus kita meng-insert String kedalam kolom Integer, Using Insert Statement.

Akan tetapi konversi dari Integer ke String dapat anda lakukan, MySql bisa untuk itu. Katakan anda ingin menggati tipe data int pada kolom jmlAnak kedalam tipe data String

ALTER TABLE personal
CHANGE COLUMN jmlAnak jmlAnak varchar(3);
Describe struktur terakhir tabel personal 
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| name         | varchar(30) | NO   |     | NULL    |                |
| tempatLahir  | varchar(15) | YES  |     | NULL    |                |
| tanggalLahir | date        | YES  |     | NULL    |                |
| jmlAnak      | varchar(3)  | YES  |     | NULL    |                |
| schoolId     | varchar(4)  | YES  | MUL | NULL    |                |
| yearStart    | year        | YES  |     | NULL    |                |
| yearGraduate | year        | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
8 rows in set (0,00 sec)

Adding or Changing Column Constraint

Katakan anda ingin menambahkan paksaan (Constraint) NOT NULL pada kolom tempatLahir di tabel personal, anda dapat menggunakan query dibawah ini.

ALTER TABLE personal
CHANGE COLUMN tempatLahir tempatLahir VARCHAR(15) NOT NULL;
Failure 
ERROR 1138 (22004): Invalid use of NULL value

Ups, error terjadi karena ada record pada kolom tersebut yang memiliki nilai NULL. Maka dari itu sebelum merubah menjadi NULL CONSTRAINT pastikan nilai NULL diganti dengan nilai lain.

UPDATE personal
SET tempatLahir = 'Belum'
WHERE tempatLahir IS NULL;
Describe personal 
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| name         | varchar(30) | NO   |     | NULL    |                |
| tempatLahir  | varchar(15) | NO   |     | NULL    |                |
| tanggalLahir | date        | YES  |     | NULL    |                |
| jmlAnak      | varchar(3)  | YES  |     | NULL    |                |
| schoolId     | varchar(4)  | YES  | MUL | NULL    |                |
| yearStart    | year        | YES  |     | NULL    |                |
| yearGraduate | year        | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
8 rows in set (0,00 sec)

Setelah NULL CONSTRAINT berhasil diganti maka anda dapat lihat pada kolom Null diatas, Field tempatLahir menjadi NO dari yang sebelumnya yes.

Selanjutnya anda ingin membuat check constraint untuk memvalidasi yearGraduate tidak boleh lebih kecil dari yearStart.

ALTER TABLE personal
ADD CONSTRAINT `Ck_Graduate_andYearSttart` CHECK (yearGraduate > yearStart);

Oke selanjutnya kita simulasikan dengan 3 kali simulasi

  1. UPDATE record nama Muhammad Faris Ma'ruf dengan mulai pendidikan tahun 2020 dan selesai tahun 2019.
  2. INSERT record baru dengan mulai pendidikan tahun 2028 dan selesai tahun 2031
  3. INSERT record baru dengan mulai pendidikan tahun 2035 dan selesai tahun NULL
UPDATE personal
SET yearStart = 2020, yearGraduate = 2019;

Failure

ERROR 3819 (HY000): Check constraint 'Ck_Graduate_andYearSttart' is violated.

INSERT INTO personal
(name, tempatLahir, tanggalLahir, jmlAnak, schoolId, yearStart, yearGraduate)
VALUES ("Fulan","Madinah", '2023-12-12',0, "MDNH", 2028, 2031);

Output

+----+------------------------+-------------+--------------+---------+----------+-----------+--------------+
| id | name                   | tempatLahir | tanggalLahir | jmlAnak | schoolId | yearStart | yearGraduate |
+----+------------------------+-------------+--------------+---------+----------+-----------+--------------+
|  1 | Muhammad Farras Ma'ruf | Belum       | NULL         | 2       | UTBG     |      2013 |         2017 |
|  2 | Tania Dwi Haryanti     | Belum       | NULL         | 2       | FTGT     |      2017 |         2020 |
|  3 | Muhammad Faris Ma'ruf  | Belum       | NULL         | NULL    | NULL     |      NULL |         NULL |
|  4 | Nu'man Noah Ma'ruf     | Belum       | NULL         | NULL    | NULL     |      NULL |         NULL |
|  5 | Hilyah Ma'ruf          | Belum       | NULL         | NULL    | NULL     |      NULL |         NULL |
|  6 | Fulan                  | Madinah     | 2023-12-12   | 0       | MDNH     |      2028 |         2031 |
+----+------------------------+-------------+--------------+---------+----------+-----------+--------------+
6 rows in set (0,00 sec)

INSERT INTO personal
(name, tempatLahir, tanggalLahir, jmlAnak, schoolId, yearStart, yearGraduate)
VALUES ("Fulanah", "Mecca", '2029-04-17', 0, "MDNH", 2035, NULL);

Output

+----+------------------------+-------------+--------------+---------+----------+-----------+--------------+
| id | name                   | tempatLahir | tanggalLahir | jmlAnak | schoolId | yearStart | yearGraduate |
+----+------------------------+-------------+--------------+---------+----------+-----------+--------------+
|  1 | Muhammad Farras Ma'ruf | Belum       | NULL         | 2       | UTBG     |      2013 |         2017 |
|  2 | Tania Dwi Haryanti     | Belum       | NULL         | 2       | FTGT     |      2017 |         2020 |
|  3 | Muhammad Faris Ma'ruf  | Belum       | NULL         | NULL    | NULL     |      NULL |         NULL |
|  4 | Nu'man Noah Ma'ruf     | Belum       | NULL         | NULL    | NULL     |      NULL |         NULL |
|  5 | Hilyah Ma'ruf          | Belum       | NULL         | NULL    | NULL     |      NULL |         NULL |
|  6 | Fulan                  | Madinah     | 2023-12-12   | 0       | MDNH     |      2028 |         2031 |
|  7 | Fulanah                | Mecca       | 2029-04-17   | 0       | MDNH     |      2035 |         NULL |
+----+------------------------+-------------+--------------+---------+----------+-----------+--------------+
7 rows in set (0,00 sec)

Dapat kita lihat dari 3 simulasi diatas, simulasi pertama menyebabkan error yang mengatakan constraint check dilanggar, dimana yearStart lebih besar dibandingkan dengan yearGraduate.

Adding Index

Catatan tentang index, anda dapat membaca kembali manfaat index saat querying pada single table, dan multiple table

Untuk membuat index gunakan quer dibawah ini

ALTER TABLE personal
ADD INDEX idx_id_personal (id ASC) VISIBLE;

Index tabel personal

+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| personal |          0 | PRIMARY         |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| personal |          1 | fk_schoolId     |            1 | schoolId    | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| personal |          1 | idx_id_personal |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0,22 sec)

ALTER TABLE personal
ADD INDEX idx_name_tmptLhr (name ASC, tempatLahir ASC) VISIBLE;

Index tabel personal

+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| personal |          0 | PRIMARY          |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| personal |          1 | fk_schoolId      |            1 | schoolId    | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| personal |          1 | idx_id_personal  |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| personal |          1 | idx_name_tmptLhr |            1 | name        | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| personal |          1 | idx_name_tmptLhr |            2 | tempatLahir | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0,08 sec)

Dropping Constraint, Key or Index

Untuk menge-drop constraint gunakan query berikut 

ALTER TABLE personal
DROP CHECK Ck_Graduate_andYearSttart;

Untuk meng-drop primary key 

ALTER TABLE personal
DROP PRIMARY KEY;

Untuk menge-drop index 

ALTER TABLE personal
DROP INDEX idx_id_personal;

ALTER TABLE personal
DROP INDEX idx_name_tmptLhr;

Drop Table

Untuk men-drop tabel yang perlu anda ketahui bahwa dengan drop tabel anda bukan hanya menghapus records dari tabel tersebut namun juga struktur tabel tersebut.

DROP TABLE personal;
DROP TABLE sekolah;

Abstract

Guna membersihan database, anda dapat menghapus schema yang digunakan pada bagian catatan ini 

DROP SCHEMA schemaSimulasi;