Belajar MySQL

Hari ini saya ingin sekali belajar MySQL. Saya sudah install LAMP (Linux, Apache, MySQL, PHP) di linux, lalu saya coba-coba. Berikut ini catatan saya.

Semua perintah ini diketik di linux terminal. Kalau anda pakai Ubuntu atau Lubuntu linux, tekan tombol Ctrl+Alt+T. Bisa juga lewat menu Lubuntu > System Tools > LXTerminal. Mari kita mulai…

MySQL login

Format:

mysql -u user -p
mysql -u user -p db_name

db_name adalah nama database.

Contoh:

mysql -u root -p
mysql -u root -p siswa

Create new MySQL user

Format:

GRANT ALL PRIVILEGES ON db_name.*
TO 'username'@'localhost'
IDENTIFIED BY 'password';

Contoh:

GRANT ALL PRIVILEGES ON siswa.*
TO 'thole'@'localhost'
IDENTIFIED BY 'secret';

Setelah itu, cek username-nya..

Format:

SHOW GRANTS FOR 'username'@'localhost';

Contoh:

SHOW GRANTS FOR 'thole'@'localhost';

Database basic

Create database

Membuat database baru.

Format:

CREATE DATABASE db_name;

Contoh:

CREATE DATABASE sekolah;

List all databases

Melihat semua database.

SHOW DATABASES;

Hasilnya seperti ini…

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| belajar            |
| belajar_php        |
| tes01              |
+--------------------+
3 rows in set (0,08 sec)

Use databases

Memilih database yang ingin digunakan.

Format:

USE db_name;

Contoh:

USE sekolah;

Delete database

Menghapus database.

Format:

DROP DATABASE db_name;

Contoh:

DROP DATABASE sekolah;

Table basic

Create new table

Format:

CREATE TABLE db_table (
Val1, Val2, Val3, ...
PRIMARY KEY ()
);

Contoh:

CREATE TABLE siswa (
id INT(11) NOT NULL AUTO_INCREMENT,
nama VARCHAR(30),
alamat VARCHAR(30),
PRIMARY KEY (id)
);

atau bisa juga ditulis panjang (sebaris) seperti ini..

CREATE TABLE db_table (Val1, Val2, Val3, ... PRIMARY KEY ());

Contoh:

CREATE TABLE siswa (id INT(11) NOT NULL AUTO_INCREMENT,nama VARCHAR(30),alamat VARCHAR(30),PRIMARY KEY (id));

Bisa juga tambahkan FULLTEXT untuk proses pencarian yang lebih cepat.

Format:

CREATE TABLE db_table (
Val1, Val2, Val3, ...
PRIMARY KEY (),
FULLTEXT (Val1, Val2, Val3, ...)
);

Contoh:

CREATE TABLE siswa (
id INT(11) NOT NULL AUTO_INCREMENT,
nama VARCHAR(30),
alamat VARCHAR(30),
FULLTEXT (nama, alamat),
PRIMARY KEY (id)
);

View column

Format:

SHOW COLUMNS FROM db_table;

Contoh:

SHOW COLUMNS FROM siswa;

Add new column

Format:

ALTER TABLE db_table ADD new_col_name AFTER col_name;
ALTER TABLE db_table ADD new_col_name BEFORE col_name;

Contoh:

ALTER TABLE siswa ADD hobi VARCHAR(30) AFTER alamat;
ALTER TABLE siswa ADD telepon INT(12) BEFORE alamat;

Change/edit column

Format:

ALTER TABLE db_table CHANGE old_col new_col;

Contoh:

ALTER TABLE siswa CHANGE telp telepon INT(8);
ALTER TABLE siswa CHANGE alamat alamat VARCHAR(20);

Delete column

Format:

ALTER TABLE db_table DROP col_name;

Contoh:

ALTER TABLE siswa DROP hobi;

List all tables

Perintah:

SHOW TABLES;

Delete table

Format:

DROP TABLE db_table;

Contoh:

DROP TABLE siswa;

CRUD (Create, Read, Update, Delete) operations

CRUD ini yang paling sering dilakukan.

Create (aka Input) data

Menambah data

Format:

INSERT INTO db_table (column1, column2, column3) VALUES (val1, val2, val3);

Contoh:

INSERT INTO siswa (nama,alamat) VALUES ('Jono','Jogja');

Read data

Menampilkan data

Format:

SELECT * FROM db_table;
SELECT * FROM db_table WHERE bla bla bla;
SELECT * FROM db_table WHERE bla LIKE '%bla%';
SELECT * FROM db_table WHERE bla bla bla ORDER BY bla bla bla ASC/DESC;

Contoh:

SELECT * FROM siswa;
SELECT * FROM siswa WHERE id = 2;
SELECT * FROM siswa WHERE nama = 'Fulan';
SELECT * FROM siswa WHERE alamat LIKE '%bali%';
SELECT * FROM siswa WHERE alamat = 'Bandung';
SELECT * FROM siswa WHERE alamat = 'Bandung' ORDER BY nama ASC;

Contoh tampilannya

mysql> select * from siswa;
+----+-------+------------+--------+
| id | nama  | alamat     | telpon |
+----+-------+------------+--------+
|  1 | Jono  | Jogja      |   5758 |
|  2 | Rita  | Medan      |   5758 |
|  3 | Lala  | Solo       |   5758 |
|  4 | Dino  | Balikpapan |   5758 |
|  5 | Tino  | Rembang    |   5758 |
|  6 | Sarah | Kudus      |   5758 |
|  7 | Thole | Balikpapan |   5758 |
|  8 | Nonik | Bali       |   5758 |
|  9 | Sammy | Balikpapan |   5758 |
| 10 | Tom   | Semarang   |   5758 |
+----+-------+------------+--------+
10 rows in set (0,00 sec)

Mencari alamat yang mengandung kata tertentu, misal bali.

SELECT * FROM siswa WHERE alamat LIKE '%bali%';

Hasilnya seperti ini:

mysql> SELECT * FROM siswa WHERE alamat LIKE '%bali%';
+----+-------+------------+--------+
| id | nama  | alamat     | telpon |
+----+-------+------------+--------+
|  4 | Dino  | Balikpapan |   5758 |
|  7 | Thole | Balikpapan |   5758 |
|  8 | Nonik | Bali       |   5758 |
|  9 | Sammy | Balikpapan |   5758 |
+----+-------+------------+--------+
4 rows in set (0,01 sec)

Jadi Bali, Balikpapan, dan semua yang mengandung kata bali akan ikut muncul.

Kalau waktu bikin table pakai FULLTEXT, data bisa dicari dengan perintah seperti ini:

SELECT * FROM db_table WHERE MATCH (col1,col2,...) AGAINST ('keyword');
SELECT * FROM db_table WHERE MATCH (col1,col2,...) AGAINST ('keyword' WITH QUERY EXPANSION);

Contoh:

SELECT * FROM siswa WHERE MATCH (nama,alamat) AGAINST ('bali');
SELECT * FROM siswa WHERE MATCH (nama,alamat) AGAINST ('bali' WITH QUERY EXPANSION);

Perintah WITH QUERY EXPANSION di atas mencari semua data yang mengandung kata bali (misal: Bali, Balikpapan, etc).

Update data

Update atau mengedit data

Format:

UPDATE db_table SET col = val WHERE id = id;

Contoh:

UPDATE siswa SET nama = 'Fulan' WHERE id = 3;
UPDATE siswa SET nama = 'Fulan', alamat = 'Balikpapan' WHERE id = 3;

Bisa juga dipakai untuk update data secara massal berdasarkan id tertentu. Misal:

UPDATE siswa SET nama = 'Fulan', alamat="Jakarta" WHERE id = 3;
UPDATE siswa SET nama = 'Fulan', alamat="Bandung" WHERE id < 3;

Delete data

Format:

DELETE FROM db_table WHERE id = id;

Contoh:

DELETE FROM siswa WHERE id = 13;

MISC: menghitung jumlah record

Menghitung jumlah record aka jumlah data dalam tabel.

Format:

SELECT COUNT(*) FROM db_table;
SELECT COUNT(*) FROM db_table WHERE bla bla bla;

Contoh:

SELECT COUNT(*) FROM siswa;
SELECT COUNT(*) FROM siswa WHERE alamat='Balikpapan';


..
.

Selamat mencoba.. semoga bermanfaat.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s