Terkadang kita ingin beralih ataupun sekedar ingin mencoba dari yang sebelumnya menggunakan aplikasi klien MySQL ke cara manual dengan mengetikan baris perintah dari command line.
Melalui command line memang dapat lebih cepat dan efisien untuk bekerja dengan database, contohnya saja ketika sekedar membuat database baru ataupun hanya melihat isi database, cukup mengetik sedikit perintah saja dan sudah selesai; tanpa harus membuka aplikasi.
Tampilan yang standar command line tentu saja tidak menjadi masalah, apalagi yang suka mengetik, atau bekerja bekerja dengan banyak tab di command line.
Pengaturan
Ada sedikit pengaturan sebelum kita dapat menggunakan myql di command line. Kita harus menambahkan path MySQL ke dalam system terlebih dahulu.
Jika belum mengaturnya, berikut langkah-langkah ke pengaturan system path.
Windows 10:
- Klik kanan Start menu dan pilih System
- Pada Related settings klik Advanced system settings
- Klik Environment Variable
- Pada bagian User variables pilih Path dan klik Edit
- Tambahkan path berikut:
C:\xampp\mysql\bin
Silahkan sesuaikan path jika lokasi mysql berbeda.
Daftar perintah mysql command line
Berikut adalah perintah-perintah MySQL untuk manajemen database di command line.
- Login:
mysql -u [username] -p @2W7N9W3 ➜ ~ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 22 Server version: 10.4.13-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ~ MariaDB [(none)]>
- Menampilkan semua database: show databases;
~ MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | db_laravel8 | | information_schema | | laravel_nuxt_app | | lavue | | mysql | | performance_schema | | phpmyadmin | | test | +--------------------+ 8 rows in set (0.002 sec)
- Memilih database: use [nama database];
~ MariaDB [(none)]> use db_laravel8; Database changed
- Melihat daftar tabel: show tables;
~ MariaDB [db_laravel8]> show tables; +------------------------+ | Tables_in_db_laravel8 | +------------------------+ | failed_jobs | | migrations | | password_resets | | personal_access_tokens | | sessions | | users | +------------------------+ 6 rows in set (0.001 sec)
- Melihat struktur tabel: describe [nama tabel];
~MariaDB [db_laravel8]> describe users; +---------------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------------+---------------------+------+-----+---------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | email | varchar(255) | NO | UNI | NULL | | | email_verified_at | timestamp | YES | | NULL | | | password | varchar(255) | NO | | NULL | | | two_factor_secret | text | YES | | NULL | | | two_factor_recovery_codes | text | YES | | NULL | | | remember_token | varchar(100) | YES | | NULL | | | current_team_id | bigint(20) unsigned | YES | | NULL | | | profile_photo_path | text | YES | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +---------------------------+---------------------+------+-----+---------+----------------+ 12 rows in set (0.135 sec)
- Manampilkan semua data pada tabel: select * from [nama tabel];
~MariaDB [db_laravel8]> select * from users; +----+--------+-----------------------------+-------------------+-------------------------------------------------------------- | id | name | email | email_verified_at | password +----+--------+-----------------------------+-------------------+-------------------------------------------------------------- | 1 | weenwe | [email protected] | NULL | $2y$10$Mi0LnAtiTYgv6Rg13dWsl.TLhRqYFnFQiv0cXHDnbqUunArllbkdO +----+--------+-----------------------------+-------------------+-------------------------------------------------------------- 1 row in set (0.159 sec)
- Memilih kolom pada tabel: select [nama kolom] from [nama tabel];
Bisa lebih dari satu kolom dengan memberi koma.
~MariaDB [db_laravel8]> select name, email from users; +--------+-----------------------------+ | name | email | +--------+-----------------------------+ | weenwe | [email protected] | +--------+-----------------------------+ 1 row in set (0.001 sec)
- Membuat tabel baru beserta kolom: CREATE TABLE [nama tabel] ([nama kolom] type data, dll);
~MariaDB [db_laravel8]> create table categories; ERROR 1113 (42000): A table must have at least 1 column ~MariaDB [db_laravel8]> create table categories (id int(20) not null auto_increment primary key, name varchar(25), create_at timestamp null, update_at timestamp null); Query OK, 0 rows affected (0.487 sec) ~MariaDB [db_laravel8]> desc categories; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(20) | NO | PRI | NULL | auto_increment | | name | varchar(25) | YES | | NULL | | | create_at | timestamp | YES | | NULL | | | update_at | timestamp | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.017 sec)
- Menambahkan data pada tabel: INSERT INTO [nama tabel] ([nama kolom]) VALUES ('value');
~MariaDB [db_laravel8]> INSERT INTO categories (id, name, create_at, update_at) VALUES ('1', 'MySQL', '2020-11-11 11:18:57', 'NULL');
Query OK, 1 row affected, 1 warning (0.129 sec)
~MariaDB [db_laravel8]> SELECT * FROM categories;
+----+-------+---------------------+---------------------+
| id | name | create_at | update_at |
+----+-------+---------------------+---------------------+
| 1 | MySQL | 2020-11-11 11:18:57 | 0000-00-00 00:00:00 |
+----+-------+---------------------+---------------------+
1 row in set (0.000 sec)
//lebih dari satu record sekaligus
~MariaDB [db_laravel8]> INSERT INTO categories (id, name, create_at, update_at) VALUES ('2', 'JavaScript', '2020-11-11 11:18:57', '2020-11-11 12:18:57'), ('3', 'Laravel', '2020-11-11 12:18:57', 'NULL'), ('4', 'Vue.js', '2020-11-11 12:18:57', '2020-11-11 12:18:57');
Query OK, 3 rows affected, 1 warning (0.098 sec)
Records: 3 Duplicates: 0 Warnings: 1
~MariaDB [db_laravel8]> select * from categories;
+----+------------+---------------------+---------------------+
| id | name | created_at | updated_at |
+----+------------+---------------------+---------------------+
| 1 | MySQL | 2020-11-11 11:18:57 | 0000-00-00 00:00:00 |
| 2 | JavaScript | 2020-11-11 11:18:57 | 2020-11-11 12:18:57 |
| 3 | Laravel | 2020-11-11 12:18:57 | 0000-00-00 00:00:00 |
| 4 | Vue.js | 2020-11-11 12:18:57 | 2020-11-11 12:18:57 |
+----+------------+---------------------+---------------------+
4 rows in set (0.000 sec)
- Menampilkan semua data / record berdasarkan value tertentu: SELECT * FROM [nama tabel] WHERE [kolom] = 'value';
~MariaDB [db_laravel8]> SELECT * FROM categories WHERE created_at = '2020-11-11 12:18:57'; +----+---------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+---------+---------------------+---------------------+ | 3 | Laravel | 2020-11-11 12:18:57 | 0000-00-00 00:00:00 | | 4 | Vue.js | 2020-11-11 12:18:57 | 2020-11-11 12:18:57 | +----+---------+---------------------+---------------------+ 2 rows in set (0.054 sec)
- Menampilkan semua data / record dengan kodisi dua value berbeda: SELECT * FROM [nama tabel] WHERE [kolom] = 'value' AND [kolom] = 'value';
~MariaDB [db_laravel8]> SELECT * FROM categories WHERE created_at = '2020-11-11 11:18:57' AND updated_at = '2020-11-11 12:18:57'; +----+------------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+------------+---------------------+---------------------+ | 2 | JavaScript | 2020-11-11 11:18:57 | 2020-11-11 12:18:57 | +----+------------+---------------------+---------------------+ 1 row in set (0.001 sec)
- Menampilkan semua data / records berdasarkan urutan ASC (ascending) atau DESC (descending): SELECT * FROM [nama tabel] ORDER BY [nama kolom] DESC/ASC;
~MariaDB [db_laravel8]> SELECT * FROM categories ORDER BY id DESC; +----+------------+---------------------+---------------------+ | id | name | created_at | updates_at | +----+------------+---------------------+---------------------+ | 4 | Vue.js | 2020-11-11 12:18:57 | 2020-11-11 12:18:57 | | 3 | Laravel | 2020-11-11 12:18:57 | 0000-00-00 00:00:00 | | 2 | JavaScript | 2020-11-11 11:18:57 | 2020-11-11 12:18:57 | | 1 | MySQL | 2020-11-11 11:18:57 | 0000-00-00 00:00:00 | +----+------------+---------------------+---------------------+ 4 rows in set (0.001 sec) ~MariaDB [db_laravel8]> SELECT * FROM categories WHERE updated_at='2020-11-11 12:18:57' ORDER BY id DESC; +----+------------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+------------+---------------------+---------------------+ | 4 | Vue.js | 2020-11-11 12:18:57 | 2020-11-11 12:18:57 | | 2 | JavaScript | 2020-11-11 11:18:57 | 2020-11-11 12:18:57 | +----+------------+---------------------+---------------------+ 2 rows in set (0.000 sec)
- Update data / record: UPDATE [nama tabel] SET [nama kolom] = 'value' WHERE [nama kolom]='value';
~MariaDB [db_laravel8]> UPDATE categories SET updated_at ='2020-11-12 13:2:32' WHERE name='MySQL'; Query OK, 1 row affected (0.082 sec) Rows matched: 1 Changed: 1 Warnings: 0 ~MariaDB [db_laravel8]> SELECT * FROM categories; +----+------------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+------------+---------------------+---------------------+ | 1 | MySQL | 2020-11-11 11:18:57 | 2020-11-12 13:02:32 | | 2 | JavaScript | 2020-11-11 11:18:57 | 2020-11-11 12:18:57 | | 3 | Laravel | 2020-11-11 12:18:57 | 0000-00-00 00:00:00 | | 4 | Vue.js | 2020-11-11 12:18:57 | 2020-11-11 12:18:57 | +----+------------+---------------------+---------------------+ 4 rows in set (0.000 sec)
- Hapus data / record: DELETE FROM [nama tabel] WHERE [nama kolom]='value';
~MariaDB [db_laravel8]> DELETE FROM categories WHERE name='Vue.js'; Query OK, 1 row affected (0.187 sec) ~MariaDB [db_laravel8]> SELECT * FROM categories; +----+------------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+------------+---------------------+---------------------+ | 1 | MySQL | 2020-11-11 11:18:57 | 2020-11-12 13:02:32 | | 2 | JavaScript | 2020-11-11 11:18:57 | 2020-11-11 12:18:57 | | 3 | Laravel | 2020-11-11 12:18:57 | 0000-00-00 00:00:00 | +----+------------+---------------------+---------------------+ 3 rows in set (0.000 sec)
- Hapus kolom: ALTER TABEL [nama tabel] DROP COLUMN [nama kolom];
~MariaDB [db_laravel8]> ALTER TABLE categories DROP COLUMN update_at; Query OK, 0 rows affected (0.161 sec) Records: 0 Duplicates: 0 Warnings: 0 ~MariaDB [db_laravel8]> SELECT * FROM categories; +----+------------+---------------------+ | id | name | create_at | +----+------------+---------------------+ | 1 | MySQL | 2020-11-11 11:18:57 | | 2 | JavaScript | 2020-11-11 11:18:57 | | 3 | Laravel | 2020-11-11 12:18:57 | +----+------------+---------------------+ 3 rows in set (0.001 sec)
- Hapus semua data tabel: DELETE FROM [nama tabel];
~MariaDB [db_laravel8]> DELETE FROM categories; Query OK, 3 rows affected (0.091 sec) ~MariaDB [db_laravel8]> SELECT * FROM categories; Empty set (0.000 sec) ~MariaDB [db_laravel8]> DESC categories; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(20) | NO | PRI | NULL | auto_increment | | name | varchar(25) | YES | | NULL | | | created_at | timestamp | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.019 sec)
- Ubah nama tabel: RENAME TABLE [nama lama] TO [nama baru];
~MariaDB [db_laravel8]> RENAME TABLE categories TO tags; Query OK, 0 rows affected (0.231 sec) ~MariaDB [db_laravel8]> DESC categories; ERROR 1146 (42S02): Table 'db_laravel8.categories' doesn't exist MariaDB [db_laravel8]> DESC tags; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+---------------+ | id | int(20) | NO | PRI | NULL | auto_increment| | name | varchar(25) | YES | | NULL | | | created_at | timestamp | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.034 sec)
- Hapus tabel: DROP TABLE [nama tabel];
~MariaDB [db_laravel8]> DROP TABLE tags; Query OK, 0 rows affected (0.304 sec) ~MariaDB [db_laravel8]> show tables; +------------------------+ | Tables_in_db_laravel8 | +------------------------+ | failed_jobs | | migrations | | password_resets | | personal_access_tokens | | sessions | | users | +------------------------+ 6 rows in set (0.001 sec)
- Hapus database: DROP TABLE [nama tabel];
~MariaDB [db_laravel8]> DROP DATABASE db_laravel8; Query OK, 6 rows affected (1.190 sec) ~MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | laravel_nuxt_app | | lavue | | mysql | | performance_schema | | phpmyadmin | | test | +--------------------+ 7 rows in set (0.002 sec)
- Logout: exit;
~MariaDB [(none)]> exit; Bye @2W7N9W3 ➜ ~ Itulah daftar perintah mysql yang dapat kita gunakan di command line. Semoga bermanfaat.