SQL Basics Cheat Sheet

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

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

Tabel domains

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

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

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

Buat query untuk menghapus semua data dengan type NULL.

delete from records where type is null;

Skenario 5

#

Tabel domain

Tabel domainmetadata

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

Table records

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

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

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

Tabel records

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

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)