↓ Skip to main content
BisaCloud Docs

SQL Basics Cheat Sheet

·6 mins
Mysql Mysql
Table of Contents

Cheat Sheet Dasar-Dasar SQL yang dapat membantu Anda untuk membuat query pada kondisi yang berbeda. Contoh data pada panduan ini menggunakan data dari PowerDNS yang telah dimodifikasi untuk memudahkan pemahaman pada setiap skenario yang dibuat.

Aliases
#

Table domains

name type
srv1.de NATIVE
muterin.local NATIVE

Contoh query

select name as domain, type from domains;

Output

+---------------+--------+
| domain        | type   |
+---------------+--------+
| srv1.de       | NATIVE |
| muterin.local | NATIVE |
+---------------+--------+
2 rows in set (0.000 sec)

Monitoring
#

Untuk melihat jumlah koneksi user MySQL yang sedang terhubung.

SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short, GROUP_CONCAT(DISTINCT user) AS users, COUNT(*) AS threads
FROM information_schema.processlist
WHERE db IS NOT NULL
GROUP BY host_short;

Skenario 1
#

Tabel records

content ttl type domain_id disabled name
192.168.100.10 300 A 3 0 ns1.topan.host
192.168.100.31 300 A 3 0 ns2.topan.host
192.168.100.11 300 A 3 0 man.topan.host
192.168.100.12 300 A 3 0 w1.topan.host
192.168.100.13 300 A 3 0 w2.topan.host
192.168.100.40 300 A 6 0 demo.topan.biz
192.168.100.41 300 A 6 0 member.topan.biz

Tabel domains

id name type
3 topan.host NATIVE
6 topan.biz NATIVE

Buat query untuk menampilkan records pada domain topan.host.

select content,ttl,type,domain_id,disabled,name
from records
where domain_id=(select id from domains where name='topan.host')

Output.

+----------------+------+------+-----------+----------+----------------+
| content        | ttl  | type | domain_id | disabled | name           |
+----------------+------+------+-----------+----------+----------------+
| 192.168.100.10 |  300 | A    |         3 |        0 | ns1.topan.host |
| 192.168.100.31 |  300 | A    |         3 |        0 | ns2.topan.host |
| 192.168.100.11 |  300 | A    |         3 |        0 | man.topan.host |
| 192.168.100.12 |  300 | A    |         3 |        0 | w1.topan.host  |
| 192.168.100.13 |  300 | A    |         3 |        0 | w2.topan.host  |
+----------------+------+------+-----------+----------+----------------+
5 rows in set (0.004 sec)

Skenario 2
#

Table user

id username password name email role_id
1 admin $2b$12$3k9SPj8B6meId admin [email protected] 1

Buat query untuk menambah user dengan password dan role_id yang sama.

insert into user (username, password, name, email, role_id)
select 'manager', password, 'manager IT', '[email protected]', role_id
from user;

Output.

| id | username | password                 | name    | email             | role_id |
|----|----------|--------------------------|---------|-------------------|---------|
|  1 | admin    | $2b$12$3k9SPj8B6meId     | admin   | [email protected] |       1 |
|  2 | manager  | $2b$12$3k9SPj8B6meId     | admin   | [email protected] |       1 |
2 rows in set (0.006 sec)

Skenario 3
#

Tabel records

content ttl type domain_id disabled name
192.168.100.11 300 A 3 0 topan.host
192.168.100.12 300 A 3 0 w1.topan.host
topan.host 300 CNAME 3 1 w2.topan.host
192.168.100.40 300 A 6 0 demo.topan.biz
192.168.100.41 300 A 6 0 topan.biz
topan.biz 300 CNAME 6 1 member.topan.biz
topan.biz 300 CNAME 6 0 shop.topan.biz

Buat query untuk menampilkan semua record CNAME yang sedang aktif.

select content,ttl,type,domain_id,disabled,name
from records
where type='CNAME' and disabled=0;

Output

+----------------+------+--------+-----------+----------+----------------+
| content        | ttl  | type   | domain_id | disabled | name           |
+----------------+------+--------+-----------+----------+----------------+
| topan.biz      |  300 | CNAME  |         6 |        0 | shop.topan.biz |
+----------------+------+--------+-----------+----------+----------------+
1 rows in set (0.001 sec)

Skenario 4
#

Tabel records

content ttl prio type domain_id disabled name
192.168.100.40 300 0 A 6 0 demo.topan.biz
192.168.100.32 300 0 A 7 0 stage.jovis.my.id
192.168.100.32 300 0 NULL 7 0 null.jovis.my.id
192.168.100.32 300 0 NULL 7 0 null1.jovis.my.id
192.168.100.32 300 0 NULL 7 0 null2.jovis.my.id

Buat query untuk menghapus semua data dengan type NULL.

delete from records where type is null;

Skenario 5
#

Tabel domain

id name dnssec type serial
3 topan.host 0 Native 2023081105
6 topan.biz 0 Native 2023081404
7 jovis.my.id 0 Native 2023081502

Tabel domainmetadata

id domain_id kind content
6 3 SOA-EDIT-API DEFAULT
13 6 SOA-EDIT-API DEFAULT
15 7 SOA-EDIT-API DEFAULT

Buat query untuk menampilkan kind dan content pada tabel domainmetadata sesuai dengan name pada table domain.

select kind,content
from domain, domainmetadata
where domainmetadata.domain_id=domain.id and name='jovis.my.id';

Output

+--------------+---------+
| kind         | content |
+--------------+---------+
| SOA-EDIT-API | DEFAULT |
+--------------+---------+
1 row in set (0.003 sec)

Skenario 6
#

Table domains

id name type
3 srv1.de NATIVE
4 muterin.local NATIVE

Table records

domain_id name type content disabled
3 man.srv1.de A 192.168.33.11 0
4 muterin.local A 192.168.33.11 0
3 w1.srv1.de A 192.168.33.12 0
3 w2.srv1.de A 192.168.33.13 0
4 demo.muterin.local A 192.168.33.100 0
3 srv1.de A 192.168.160.22 0

Buat query untuk menampilkan semua domain yang ada di table domains utama beserta A record.

select domains.id, domains.name, records.content, domains.type
from domains
left join records on records.domain_id=domains.id and records.type='A' and records.name=domains.name
where records.disabled=0;

Output

+----+---------------+----------------+--------+
| id | name          | content        | type   |
+----+---------------+----------------+--------+
|  3 | srv1.de       | 192.168.160.22 | NATIVE |
|  4 | muterin.local | 192.168.33.11  | NATIVE |
+----+---------------+----------------+--------+
2 rows in set (0.001 sec)

Skenario 7
#

Table records

name type content ttl disabled
w2.srv1.de A 192.168.33.13 300 0
demo.muterin.local A 192.168.33.100 300 0
srv1.de A 192.168.160.22 300 0
man.srv1.de A 192.168.33.11 1800 0
w1.srv1.de A 192.168.33.12 1800 0
muterin.local A 192.168.33.11 1800 0

Buat query untuk menampilkan record dengan ttl lebih dari 300.

select name, type, content, ttl, disabled
from records
where ttl > 300 and disabled=0;

Output

+---------------+------+---------------+------+----------+
| name          | type | content       | ttl  | disabled |
+---------------+------+---------------+------+----------+
| man.srv1.de   | A    | 192.168.33.11 | 1800 |        0 |
| w1.srv1.de    | A    | 192.168.33.12 | 1800 |        0 |
| muterin.local | A    | 192.168.33.11 | 1800 |        0 |
+---------------+------+---------------+------+----------+
3 rows in set (0.000 sec)

Skenario 8
#

Table records

name type
a-demo.muterin.local TXT
demo.muterin.local A
demo.muterin.local TXT
man.srv1.de A
muterin.local A
muterin.local SOA
srv1.de A
srv1.de SOA
w1.srv1.de A
w2.srv1.de A

Buat query untuk menampilkan semua records kecuali A record.

select name,type from records where type !='A';

Output

+----------------------+------+
| name                 | type |
+----------------------+------+
| a-demo.muterin.local | TXT  |
| demo.muterin.local   | TXT  |
| muterin.local        | SOA  |
| srv1.de              | SOA  |
+----------------------+------+
4 rows in set (0.001 sec)

Skenario 9
#

Tabel domains

id name
4 muterin.local
3 srv1.de

Tabel records

domain_id name type
3 w2.srv1.de A
4 a-demo.muterin.local TXT
4 demo.muterin.local A
4 demo.muterin.local TXT
3 srv1.de A
3 man.srv1.de A
3 w1.srv1.de A
3 srv1.de SOA
4 muterin.local A
4 muterin.local SOA

Buat query untuk menampilkan semua record TXT dan SOA yang ada pada domain di table domains.

select d.id, d.name, r.type
from domain d
join records r on r.domain_id=d.id and r.type in ('SOA','TXT');

Output

+----+---------------+------+
| id | name          | type |
+----+---------------+------+
|  4 | muterin.local | TXT  |
|  4 | muterin.local | TXT  |
|  4 | muterin.local | SOA  |
|  3 | srv1.de       | SOA  |
+----+---------------+------+
4 rows in set (0.001 sec)

Skenario 10
#

Tabel records

domain_id name type
3 w2.srv1.de A
4 a-demo.muterin.local TXT
4 demo.muterin.local A
4 demo.muterin.local TXT
3 srv1.de A
3 man.srv1.de A
3 w1.srv1.de A
3 srv1.de SOA
4 muterin.local A
4 muterin.local SOA

Buat query untuk menampilkan record A dan TXT lalu sortir berdasarkan domain_id.

select domain_id,name,type
from records
where (type='A' OR type='TXT') order by domain_id asc;

Output

+-----------+----------------------+------+
| domain_id | name                 | type |
+-----------+----------------------+------+
|         3 | w2.srv1.de           | A    |
|         3 | srv1.de              | A    |
|         3 | man.srv1.de          | A    |
|         3 | w1.srv1.de           | A    |
|         4 | a-demo.muterin.local | TXT  |
|         4 | demo.muterin.local   | A    |
|         4 | demo.muterin.local   | TXT  |
|         4 | muterin.local        | A    |
+-----------+----------------------+------+
8 rows in set (0.001 sec)

Related