Pelajari MySQL/MariaDB untuk Pemula - Bagian 1

Dalam artikel ini kami akan menunjukkan cara membuat database (juga dikenal sebagai skema), tabel (dengan tipe data), dan menjelaskan cara melakukan Bahasa Manipulasi Data (DML ) operasi dengan data di server MySQL/MariaDB.

Diasumsikan bahwa Anda sebelumnya telah 1) menginstal paket yang diperlukan pada sistem Linux Anda, dan 2) menjalankan mysql_secure_installation untuk meningkatkan keamanan server database . Jika tidak, ikuti panduan di bawah ini untuk menginstal server MySQL/MariaDB.

Instal Database MySQL Terbaru di Sistem Linux
Instal Database MariaDB Terbaru di Sistem Linux

Agar singkatnya, kami akan mengacu pada MariaDB secara eksklusif di seluruh artikel ini, namun konsep dan perintah yang dijelaskan di sini juga berlaku untuk MySQL.

Membuat Database, Tabel, dan Pengguna Resmi

Seperti yang Anda ketahui, database dapat didefinisikan secara sederhana sebagai kumpulan informasi yang terorganisir. Khususnya, MariaDB adalah sistem manajemen basis data relasional (RDBMS) dan menggunakan Structure Query Language untuk melakukan operasi pada basis data. Selain itu, perlu diingat bahwa MariaDB menggunakan istilah database dan skema secara bergantian.

Untuk menyimpan informasi persisten dalam database, kita akan menggunakan tabel yang menyimpan baris data. Seringkali, dua tabel atau lebih akan terkait satu sama lain dalam beberapa cara. Itu adalah bagian dari organisasi yang menjadi ciri penggunaan database relasional.

Membuat Basis Data Baru

Untuk membuat database baru bernama BooksDB, masukkan prompt MariaDB dengan perintah berikut (Anda akan diminta memasukkan kata sandi untuk pengguna root MariaDB):

[root@TecMint ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>

Setelah database dibuat, kita perlu membuat setidaknya dua tabel di dalamnya. Tapi pertama-tama mari kita jelajahi konsep tipe data.

Memperkenalkan Tipe data MariaDB

Seperti yang kami jelaskan sebelumnya, tabel adalah objek database tempat kami menyimpan informasi persisten. Setiap tabel terdiri dari dua atau lebih bidang (juga dikenal sebagai kolom) dari tipe data tertentu (tipe informasi) yang dapat disimpan oleh bidang tersebut.

Tipe data yang paling umum di MariaDB adalah sebagai berikut (Anda dapat melihat daftar lengkapnya di dokumentasi online resmi MariaDB):

numerik :

BOOLEAN menganggap 0 sebagai salah dan nilai lainnya sebagai benar.
TINYINT, jika digunakan dengan SIGNED, mencakup rentang dari -128 hingga 127, sedangkan rentang UNSIGNED adalah 0 hingga 255.
SMALLINT, jika digunakan dengan SIGNED, mencakup rentang dari -32768 hingga 32767. Rentang UNSIGNED adalah 0 hingga 65535.
INT, jika digunakan dengan UNSIGNED, mencakup rentang dari 0 hingga 4294967295, dan -2147483648 hingga 2147483647 jika tidak.

Catatan: Dalam TINYINT, SMALLINT, dan INT, default SIGNED diasumsikan.

DOUBLE(M, D), dengan M adalah jumlah total digit dan D adalah jumlah digit setelah koma desimal, mewakili a bilangan floating-point presisi ganda. Jika UNSIGNED ditentukan, nilai negatif tidak diperbolehkan.

Rangkaian :

VARCHAR(M) mewakili string dengan panjang variabel dengan M adalah panjang kolom maksimum yang diperbolehkan dalam byte (secara teori 65.535). Dalam kebanyakan kasus, jumlah byte sama dengan jumlah karakter, kecuali untuk beberapa karakter yang dapat memakan waktu hingga 3 byte. Misalnya, huruf Spanyol ñ mewakili satu karakter tetapi membutuhkan 2 byte.
TEXT(M) mewakili kolom dengan panjang maksimum 65.535 karakter. Namun, seperti yang terjadi pada VARCHAR(M), panjang maksimum sebenarnya berkurang jika karakter multi-byte disimpan. Jika M ditentukan, kolom dibuat sebagai tipe terkecil yang dapat menyimpan sejumlah karakter tersebut.
MEDIUMTEXT(M) dan LONGTEXT(M) mirip dengan TEXT(M), hanya saja panjang maksimum yang diperbolehkan adalah 16.777.215 dan 4.294.967.295 karakter, masing-masing.

Tanggal dan waktu:

DATE mewakili tanggal dalam format YYYY-MM-DD.
TIME mewakili waktu dalam format HH:MM:SS.sss (jam, menit, detik, dan milidetik).
DATETIME adalah kombinasi dari DATE dan TIME dalam format YYYY-MM-DD HH:MM:SS.
TIMESTAMP digunakan untuk menentukan kapan suatu baris ditambahkan atau diperbarui.

Setelah meninjau tipe data ini, Anda akan berada dalam posisi yang lebih baik untuk menentukan tipe data mana yang perlu Anda tetapkan ke kolom tertentu dalam tabel.

Misalnya, nama seseorang dapat dengan mudah dimasukkan ke dalam VARCHAR(50), sedangkan entri blog memerlukan jenis TEXT (pilih M sebagai sesuai kebutuhan spesifik Anda).

Membuat Tabel dengan Kunci Utama dan Kunci Asing

Sebelum kita mendalami pembuatan tabel, ada dua konsep dasar tentang database relasional yang perlu kita tinjau: kunci utama dan asing.

kunci utama berisi nilai yang secara unik mengidentifikasi setiap baris, atau data, dalam tabel. Di sisi lain, kunci asing digunakan untuk membuat tautan antara data dalam dua tabel, dan untuk mengontrol data yang dapat disimpan dalam tabel tempat kunci asing berada. Baik kunci primer maupun kunci asing umumnya adalah INT.

Sebagai ilustrasi, mari gunakan BookstoreDB dan buat dua tabel bernama AuthorsTBL dan BooksTBL sebagai berikut. Batasan NOT NULL menunjukkan bahwa bidang terkait memerlukan nilai selain NULL.

Selain itu, AUTO_INCREMENT digunakan untuk menambah satu nilai kolom kunci utama INT ketika data baru dimasukkan ke dalam tabel.

MariaDB [(none)]> USE BookstoreDB;

MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);

MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);

MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
    -> AuthorID INT NOT NULL AUTO_INCREMENT,
    -> AuthorName VARCHAR(100),
    -> PRIMARY KEY(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
    -> BookID INT NOT NULL AUTO_INCREMENT,
    -> BookName VARCHAR(100) NOT NULL,
    -> AuthorID INT NOT NULL,
    -> BookPrice DECIMAL(6,2) NOT NULL,
    -> BookLastUpdated TIMESTAMP,
    -> BookIsAvailable BOOLEAN,
    -> PRIMARY KEY(BookID),
    -> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]>

Sekarang kita dapat melanjutkan dan mulai memasukkan catatan ke dalam AuthorsTBL dan BooksTBL.

Memilih, Menyisipkan, Memperbarui, dan Menghapus Baris

Pertama-tama kita akan mengisi tabel AuthorsTBL. Mengapa? Karena kita perlu memiliki nilai untuk AuthorID sebelum memasukkan catatan ke dalam BooksTBL.

Jalankan kueri berikut dari perintah MariaDB Anda:

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

Setelah itu, kami akan memilih semua catatan dari AuthorsTBL. Ingatlah bahwa kita memerlukan AuthorID untuk setiap data guna membuat kueri INSERT untuk BooksTBL.

Jika Anda ingin mengambil satu record dalam satu waktu, Anda dapat menggunakan klausa WHERE untuk menunjukkan kondisi yang harus dipenuhi oleh suatu baris agar dapat dikembalikan. Misalnya,

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Alternatifnya, Anda dapat memilih semua rekaman secara bersamaan:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
|        3 | Paulo Coelho    |
+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [BookstoreDB]>

Sekarang mari kita buat kueri INSERT untuk BooksTBL, menggunakan AuthorID yang sesuai untuk mencocokkan penulis setiap buku. Nilai 1 pada BookIsAvailable menunjukkan buku tersebut tersedia, 0 sebaliknya:

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
    -> VALUES ('And Then There Were None', 1, 14.95, 1),
    -> ('The Man in the Brown Suit', 1, 23.99, 1),
    -> ('The Stand', 2, 35.99, 1),
    -> ('Pet Sematary', 2, 17.95, 0),
    -> ('The Green Mile', 2, 29.99, 1),
    -> ('The Alchemist', 3, 25, 1),
    -> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

Pada titik ini kita akan melakukan SELECT untuk melihat catatan di BooksTBL. Kalau begitu mari kita UPDATE harga “The Alchemist ” oleh Paulo Coelho dan SELECT rekor spesifik itu lagi.

Perhatikan bagaimana bidang BookLastUpdated kini menampilkan nilai yang berbeda. Seperti yang kami jelaskan sebelumnya, kolom TIMESTAMP menunjukkan nilai kapan data disisipkan atau terakhir diubah.

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |
|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |
|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |
|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |
|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |
|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41 |               1 |
|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)

MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]>

Meskipun kami tidak akan melakukannya di sini, Anda juga dapat menghapus catatan jika tidak digunakan lagi. Misalnya, kita ingin menghapus “The Alchemist ” dari BooksTBL.

Untuk melakukannya, kita akan menggunakan pernyataan DELETE sebagai berikut:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

Seperti halnya UPDATE, sebaiknya lakukan SELECT terlebih dahulu untuk melihat data yang mungkin terpengaruh oleh HAPUS.

Selain itu, jangan lupa untuk menambahkan klausa WHERE dan kondisi (BookID=6) untuk memilih data tertentu yang akan dihapus. Jika tidak, Anda berisiko menghapus semua baris dalam tabel!

Jika Anda ingin menggabungkan dua (atau lebih) kolom, Anda dapat menggunakan pernyataan CONCAT. Misalnya, kita ingin mengembalikan kumpulan hasil yang terdiri dari satu bidang dengan nama buku dan penulis dalam bentuk “Sang Alkemis (Paulo Coelho) ” dan kolom lain dengan harga.

Hal ini memerlukan JOIN antara AuthorsTBL dan BooksTBL pada bidang umum yang digunakan bersama oleh kedua tabel (AuthorID):

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

Seperti yang bisa kita lihat, CONCAT memungkinkan kita menggabungkan beberapa ekspresi string yang dipisahkan dengan koma. Anda juga akan melihat bahwa kami memilih alias Deskripsi untuk mewakili kumpulan hasil penggabungan.

Output dari query di atas ditunjukkan pada gambar di bawah ini:

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description                                            | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie)             |     14.95 |
| The Man in the Brown Suit (Agatha Christie)            |     23.99 |
| The Stand (Stephen King)                               |     35.99 |
| Pet Sematary (Stephen King)                            |     17.95 |
| The Green Mile (Stephen King)                          |     29.99 |
| The Alchemist (Paulo Coelho)                           |     25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)

Buat Pengguna untuk Mengakses Database BookstoreDB

Menggunakan root untuk melakukan semua operasi DML dalam database adalah ide yang buruk. Untuk menghindari hal ini, kita dapat membuat akun pengguna MariaDB baru (kita akan menamainya pengguna toko buku) dan menetapkan semua izin yang diperlukan untuk Toko Buku:

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Memiliki pengguna khusus dan terpisah untuk setiap basis data akan mencegah kerusakan pada keseluruhan basis data jika satu akun disusupi.

Kiat Ekstra MySQL

Untuk menghapus perintah MariaDB, ketik perintah berikut dan tekan Enter:

MariaDB [BookstoreDB]> \! clear

Untuk memeriksa konfigurasi tabel tertentu, lakukan:

MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];

Misalnya,

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| BookName        | varchar(100) | NO   |     | NULL              |                             |
| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |
| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |
| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

Pemeriksaan cepat menunjukkan bahwa bidang BookIsAvailable menerima nilai NULL. Karena kami tidak ingin mengizinkannya, kami akan UBAH tabelnya sebagai berikut:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(Silakan tampilkan kolom itu lagi – tanda YA yang disorot pada gambar di atas sekarang seharusnya menjadi TIDAK).

Terakhir, untuk melihat semua database di server Anda, lakukan:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;

[root@TecMint ~]# mysql -u bookstoreuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

Gambar berikut menunjukkan hasil dari perintah di atas setelah mengakses perintah MariaDB sebagai pengguna toko buku (perhatikan bagaimana akun ini tidak dapat “melihat ” database apa pun selain BookstoreDB dan information_schema (tersedia untuk semua pengguna):

Ringkasan

Dalam artikel ini kami telah menjelaskan cara menjalankan operasi DML dan cara membuat database, tabel, dan pengguna khusus pada database MariaDB. Selain itu, kami membagikan beberapa tip yang mungkin membuat hidup Anda sebagai administrator sistem/database lebih mudah.

Bagian Administrasi Basis Data MySQL – 1
Bagian Administrasi Basis Data MySQL – 2
Penyetelan dan Pengoptimalan Kinerja MySQL – Bagian 3

Jika Anda memiliki pertanyaan tentang artikel ini, jangan ragu untuk memberi tahu kami! Jangan ragu untuk menggunakan formulir komentar di bawah untuk menghubungi kami.