Pemrograman internet mobile
Download
1 / 76

PIM6 - PowerPoint PPT Presentation


  • 338 Views
  • Uploaded on

Pemrograman Internet Mobile. Antonius R.C, S.Kom, M.Cs MySQL. SQL Pada MySQL. DDL – Data Definition Language CREATE ALTER DROP DML – Data Manipulation Language INSERT UPDATE DELETE Data Retrieving / Query SELECT DCL – Data Control Language Administrasi User. Melihat Versi MySQL.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'PIM6' - Anita


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Pemrograman internet mobile l.jpg

Pemrograman Internet Mobile

Antonius R.C, S.Kom, M.Cs

MySQL


Sql pada mysql l.jpg
SQL Pada MySQL

  • DDL – Data Definition Language

    • CREATE

    • ALTER

    • DROP

  • DML – Data Manipulation Language

    • INSERT

    • UPDATE

    • DELETE

  • Data Retrieving / Query

    • SELECT

  • DCL – Data Control Language

    • Administrasi User


Melihat versi mysql l.jpg
Melihat Versi MySQL

  • SELECT VERSION();

    mysql> SELECT VERSION();

    +------------------------------+

    | VERSION() |

    +------------------------------+

    | 4.1.12-Debian_1ubuntu3.1-log |

    +------------------------------+


Melihat database mysql l.jpg
Melihat Database MySQL

SHOW DATABASES;

mysql> SHOW DATABASES;

+----------+

| Database |

+----------+

| mysql |

| test |

+----------+


Create database l.jpg
Create Database

  • CREATE DATABASE <nama_databases>;

    mysql> CREATE DATABASE coba;

    mysql> SHOW DATABASES;

    +----------+

    | Database |

    +----------+

    | coba |

    | mysql |

    | test |

    +----------+


Menghapus database l.jpg
Menghapus Database

  • DROP DATABASE <nama_database>;

    mysql> DROP DATABASE coba;

    mysql> SHOW DATABASES;

    +----------+

    | Database |

    +----------+

    | mysql |

    | test |

    +----------+


Menggunakan database l.jpg
Menggunakan Database

  • USE <nama_database>;

    mysql> CREATE DATABASE universitas;

    mysql> SHOW DATABASES;

    +-------------+

    | Database |

    +-------------+

    | mysql |

    | test |

    | universitas |

    +-------------+

    mysql> USE universitas;

    Database changed


Create table l.jpg
Create Table

CREATE TABLE <nama_table>

(<nama_kolom1> <tipe_data1>,

<nama_kolom2> <tipe_data2>, ...);

mysql> CREATE TABLE pegawai

-> (nik CHAR(4), nama VARCHAR(20));


Select db dan show table l.jpg
Select Db dan Show Table

SELECT DATABASE();

mysql> SELECT DATABASE();

+-------------+

| DATABASE() |

+-------------+

| universitas |

+-------------+

SHOW TABLES;

mysql> SHOW TABLES;

+-----------------------+

| Tables_in_universitas |

+-----------------------+

| pegawai |

+-----------------------+


Menghapus table l.jpg
Menghapus table

DROP TABLE <nama_table>;

mysql> DROP TABLE karyawan;

mysql> SHOW TABLES;

Empty set (0.01 sec)


Melihat deskripsi table l.jpg
Melihat deskripsi table

DESCRIBE <nama_table>;

EXPLAIN <nama_table>;

SHOW COLUMNS FROM <nama_table>;

mysql> DESCRIBE pegawai;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| nik | varchar(4) | YES | | NULL | |

| nama | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+


Mengubah nama table l.jpg
MENGUBAH NAMA TABLE

  • ALTER TABLE <nama_table> RENAME <nama_baru>;

    mysql> ALTER TABLE pegawai RENAME karyawan;

    mysql> SHOW TABLES;

    +-----------------------+

    | Tables_in_universitas |

    +-----------------------+

    | karyawan |

    +-----------------------+


Menambah kolom di akhir l.jpg
Menambah Kolom di Akhir

ALTER TABLE <nama_table> ADD COLUMN <kolom_baru> <tipe_data>;

mysql> ALTER TABLE karyawan

-> ADD COLUMN gaji int(5);

mysql> DESCRIBE karyawan;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| nik | varchar(4) | YES | | NULL | |

| nama | varchar(20) | YES | | NULL | |

| gaji | int(5) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+


Tambah kolom di awal l.jpg
Tambah Kolom di Awal

ALTER TABLE <nama_table> ADD COLUMN <kolom_baru> <tipe_data> FIRST;

mysql> ALTER TABLE karyawan

-> ADD COLUMN tgl_lahir date FIRST;

mysql> DESCRIBE karyawan;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| tgl_lahir | date | YES | | NULL | |

| nik | varchar(4) | YES | | NULL | |

| nama | varchar(20) | YES | | NULL | |

| gaji | int(5) | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+


Menyisipkan kolom baru l.jpg
Menyisipkan kolom baru

ALTER TABLE <nama_table>

ADD COLUMN <kolom_baru> <tipe_data>

AFTER <nama_kolom>;

mysql> ALTER TABLE karyawan

-> ADD COLUMN bonus int(4) AFTER nik;

mysql> DESCRIBE karyawan;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| tgl_lahir | date | YES | | NULL | |

| nik | varchar(4) | YES | | NULL | |

| bonus | int(4) | YES | | NULL | |

| nama | varchar(20) | YES | | NULL | |

| gaji | int(5) | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+


Ubah nama kolom dan tipe datanya l.jpg
Ubah nama kolom dan tipe datanya

ALTER TABLE <nama_table> CHANGE <kolom_lama> <kolom_bar> <tipe_data>;

mysql> ALTER TABLE karyawan

-> CHANGE nik nip char(4);

mysql> DESCRIBE karyawan;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| tgl_lahir | date | YES | | NULL | |

| nip | varchar(4) | YES | | NULL | |

| bonus | int(4) | YES | | NULL | |

| nama | varchar(20) | YES | | NULL | |

| gaji | int(5) | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+


Mengubah tipe data kolom l.jpg
Mengubah tipe data kolom

ALTER TABLE <nama_table> MODIFY <nama_kolom> <tp_data_baru>;

mysql> ALTER TABLE karyawan

-> MODIFY nip int(5);

mysql> DESCRIBE karyawan;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| tgl_lahir | date | YES | | NULL | |

| nip | int(5) | YES | | NULL | |

| bonus | int(4) | YES | | NULL | |

| nama | varchar(20) | YES | | NULL | |

| gaji | int(5) | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+


Menghapus kolom l.jpg
Menghapus kolom

ALTER TABLE <nama_table> DROP COLUMN <nama_kolom>;

mysql> ALTER TABLE karyawan

-> DROP COLUMN tgl_lahir;

mysql> DESCRIBE karyawan;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| nip | int(5) | YES | | NULL | |

| bonus | int(4) | YES | | NULL | |

| nama | varchar(20) | YES | | NULL | |

| gaji | int(5) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+


Create table dgn primary key l.jpg
Create table dgn primary key

CREATE TABLE <nama_table> (kolom1 tipe_data1 PRIMARY KEY,

kolom2 tipe_data2);

mysql> CREATE TABLE ktp

-> (no_ktp int(15) PRIMARY KEY,

-> nama varchar(20),tgl_lahir date,

-> alamat varchar(30));

mysql> DESCRIBE ktp;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| no_ktp | int(15) | | PRI | 0 | |

| nama | varchar(20) | YES | | NULL | |

| tgl_lahir | date | YES | | NULL | |

| alamat | varchar(30) | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+


Slide20 l.jpg

mysql> DESCRIBE mahasiswa;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| nrp | varchar(7) | | PRI | | |

| nama | varchar(20) | YES | | NULL | |

| tgl_lahir | date | YES | | NULL | |

| ipk | double(3,2) | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+

mysql> DESCRIBE mata_kuliah;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| kd_mk | varchar(5) | | PRI | | |

| nama_mk | varchar(15) | YES | | NULL | |

| sks | int(1) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

mysql> DESCRIBE pengambilan;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| nrp | varchar(7) | YES | | NULL | |

| kd_mk | varchar(5) | YES | | NULL | |

| nilai | int(3) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+


Jawab l.jpg
Jawab

mysql> CREATE TABLE mahasiswa

-> (nrp varchar(7) PRIMARY KEY,

-> nama varchar(20),

-> tgl_lahir date,ipk double(3,2))

mysql> CREATE TABLE mata_kuliah

-> (kd_mk varchar(5)PRIMARY KEY,

-> nama_mk varchar(15),

-> sks int(1));

mysql> CREATE TABLE pengambilan

-> (nrp varchar(7),kd_mk char(5),

-> nilai int(3));


Tambah record l.jpg
Tambah record

INSERT INTO <nama_table> (kolom1,kolom2,kolom3)

VALUES (data1,data2,data3);

mysql> INSERT INTO mahasiswa

-> (nrp,nama,tgl_lahir,ipk) VALUES

-> ('0673006','Dodi','1988-04-23',3.5);

mysql> SELECT * FROM mahasiswa;

+---------+------+------------+------+

| nrp | nama | tgl_lahir | ipk |

+---------+------+------------+------+

| 0673006 | Dodi | 1988-04-23 | 3.50 |

+---------+------+------------+------+


Update record l.jpg
Update record

UPDATE <nama_table>

SET <nama_kolom> = <nilai_baru>

WHERE <kondisi>;

mysql> UPDATE mahasiswa SET ipk=3.25

-> WHERE nrp='0673006';

mysql> SELECT * FROM mahasiswa;

+---------+-------+------------+------+

| nrp | nama | tgl_lahir | ipk |

+---------+-------+------------+------+

| 0673006 | Dodi | 1988-04-23 | 3.25 |

| 0572123 | Billy | 1990-10-25 | 2.75 |

| 0471212 | Wiro | 1987-07-17 | 2.27 |

+---------+-------+------------+------+


Delete record l.jpg
Delete record

DELETE FROM <nama_table>

WHERE <kondisi>;

mysql> DELETE FROM mahasiswa

-> WHERE nama='Dodi';

mysql> SELECT * FROM mahasiswa;

+---------+-------+------------+------+

| nrp | nama | tgl_lahir | ipk |

+---------+-------+------------+------+

| 0572123 | Billy | 1990-10-25 | 2.75 |

| 0471212 | Wiro | 1987-07-17 | 2.27 |

+---------+-------+------------+------+


Truncate mengkosongkan table l.jpg
Truncate (Mengkosongkan table)

TRUNCATE TABLE <nama_table>;

TRUNCATE <nama_table>;

mysql> TRUNCATE TABLE mahasiswa;

mysql> SELECT * FROM mahasiswa;

Empty set (0.00 sec)


Auto increment l.jpg
Auto increment

CREATE TABLE <nama_table> (kolom_kunci> int(n) AUTO_INCREMENT

PRIMARY KEY, <kolom2> <tipe_data2>);

mysql> CREATE TABLE warga (

-> no int(3) AUTO_INCREMENT PRIMARY KEY,

-> nama varchar(20),usia int(2));

mysql> DESCRIBE warga;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| no | int(3) | | PRI | NULL | auto_increment |

| nama | varchar(20) | YES | | NULL | |

| usia | int(2) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+


Auto increment 2 l.jpg
Auto increment (2)

mysql> INSERT INTO warga(nama,usia)

-> VALUES('Tora',33);

mysql> INSERT INTO warga(nama) VALUES('Aming');

mysql> INSERT INTO warga(usia) VALUES(27);

mysql> SELECT * FROM warga;

+----+-------+------+

| no | nama | usia |

+----+-------+------+

| 1 | Tora | 33 |

| 2 | Aming | NULL |

| 3 | NULL | 27 |

+----+-------+------+


Auto increment 3 l.jpg
Auto increment (3)

mysql> DELETE FROM warga WHERE nama='Tora';

mysql> INSERT INTO warga(nama,usia)

-> VALUES('Jojon',40);

mysql> SELECT * FROM warga;

+----+-------+------+

| no | nama | usia |

+----+-------+------+

| 4 | Jojon | 40 |

| 2 | Aming | NULL |

| 3 | NULL | 27 |

+----+-------+------+


Nilai default l.jpg
Nilai default

ALTER TABLE <nama_table>

MODIFY <nama_kolom> <tipe_data>

DEFAULT <nilai_default>;

mysql> ALTER TABLE warga

-> MODIFY usia int(2) DEFAULT 25;

mysql> DESCRIBE warga;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| no | int(3) | | PRI | NULL | auto_increment |

| nama | varchar(20) | YES | | NULL | |

| usia | int(2) | YES | | 25 | |

+-------+-------------+------+-----+---------+----------------+


Nilai default30 l.jpg
Nilai Default

mysql> INSERT INTO warga(nama) VALUES('Eko');

mysql> INSERT INTO warga(nama,usia)

-> VALUES('Tarsan',68);

mysql> INSERT INTO warga() VALUES();

mysql> SELECT * FROM warga;

+----+--------+------+

| no | nama | usia |

+----+--------+------+

| 1 | Parto | 33 |

| 2 | Akri | 32 |

| 3 | Eko | 25 |

| 4 | Tarsan | 68 |

| 5 | NULL | 25 |

+----+--------+------+


Not null l.jpg
NOT NULL

ALTER TABLE <nama_table>

MODIFY <nama_kolom> <tipe_data>

NOT NULL;

mysql> CREATE TABLE anggota (

-> nama varchar(20) NOT NULL,

-> tgl_lahir date,telp varchar(15));

mysql> DESCRIBE anggota;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| nama | varchar(20) | | | | |

| tgl_lahir | date | YES | | NULL | |

| telp | varchar(15) | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+

NOT NULL berarti tidak kolom tersebut tidak boleh dikosongkan


Foreign key l.jpg
Foreign Key

ALTER TABLE <table_anak>

ADD FOREGIN KEY

(kolom_anak)REFERENCES

<table_induk>(<kolom_induk>);

mysql> ALTER TABLE pengambilan ADD FOREIGN

-> KEY(nrp) REFERENCES mahasiswa(nrp);

mysql> ALTER TABLE pengambilan ADD FOREIGN

-> KEY(kd_mk) REFERENCES

-> mata_kuliah(kd_mk);

  • Foreign Key = Kunci Tamu

  • Field yang menjadi kunci utama di table lain dan masuk menjadi field di suatu table yang berhubungan dengan table yg memiliki kunci utama tersebut


Zerofill l.jpg
Zerofill

mysql> CREATE TABLE barang

-> (nama varchar(20),

-> harga int(5) ZEROFILL);

mysql> DESCRIBE barang;

+-------+--------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------------------+------+-----+---------+-------+

| nama | varchar(20) | YES | | NULL | |

| harga | int(5) unsigned zerofill | YES | | NULL | |

+-------+--------------------------+------+-----+---------+-------+

mysql> INSERT INTO barang VALUES('Kopi',500);

mysql> INSERT INTO barang VALUES('Gula',300);

mysql> INSERT INTO barang VALUES('Susu',1100);

mysql> SELECT * FROM barang;

+------+-------+

| nama | harga |

+------+-------+

| Kopi | 00500 |

| Gula | 00300 |

| Susu | 01100 |

+------+-------+


Isi data l.jpg
Isi data

mysql> SELECT * FROM mahasiswa;

+---------+--------------+------------+------+

| nrp | nama | tgl_lahir | ipk |

+---------+--------------+------------+------+

| 0471007 | Spiderman | 1990-02-26 | 3.33 |

| 0471019 | Superman | 1988-10-22 | 2.45 |

| 0471011 | Batman | 1985-05-14 | 2.77 |

| 0571022 | Robin | 1992-03-27 | 3.77 |

| 0571029 | Profesor X | 1991-05-23 | 3.51 |

| 0471059 | Wonder Woman | 1992-07-12 | 2.19 |

+---------+--------------+------------+------+

mysql> SELECT * FROM mata_kuliah;

+-------+----------+------+

| kd_mk | nama_mk | sks |

+-------+----------+------+

| IF001 | Menembak | 3 |

| IF002 | Menyamar | 2 |

| IF003 | Terbang | 2 |

| IF004 | Memanjat | 3 |

+-------+----------+------+


Backup table l.jpg
Backup table

CREATE TABLE <table_backup> AS

SELECT * FROM <table_sumber>;

mysql> CREATE TABLE mhs AS

-> SELECT * FROM mahasiswa;

mysql> CREATE TABLE matkul AS

-> SELECT * FROM mata_kuliah;


Select and n or l.jpg
Select And n Or

mysql> SELECT * FROM pengambilan

-> WHERE kd_mk='IF002' AND nilai>85;

+---------+-------+-------+

| nrp | kd_mk | nilai |

+---------+-------+-------+

| 0471007 | IF002 | 88 |

| 0471019 | IF002 | 88 |

| 0471011 | IF002 | 88 |

+---------+-------+-------+

mysql> SELECT * FROM pengambilan

-> WHERE kd_mk='IF002' OR nilai>85;

+---------+-------+-------+

| nrp | kd_mk | nilai |

+---------+-------+-------+

| 0471019 | IF003 | 90 |

| 0471007 | IF002 | 88 |

| 0471019 | IF002 | 88 |

| 0471011 | IF002 | 88 |

| 0571022 | IF002 | 82 |

+---------+-------+-------+


Select not n alias l.jpg
Select Not n Alias

mysql> SELECT * FROM pengambilan

-> WHERE NOT (kd_mk='IF002');

+---------+-------+-------+

| nrp | kd_mk | nilai |

+---------+-------+-------+

| 0471007 | IF004 | 70 |

| 0471019 | IF003 | 90 |

+---------+-------+-------+

mysql> SELECT nama "Nama",

-> ipk "Indeks Prestasi Kumulatif"

-> FROM mahasiswa;

+--------------+---------------------------+

| Nama | Indeks Prestasi Kumulatif |

+--------------+---------------------------+

| Spiderman | 3.33 |

| Superman | 2.45 |

| Batman | 2.77 |

| Robin | 3.77 |

| Profesor X | 3.51 |

| Wonder Woman | 2.19 |

+--------------+---------------------------+


Order by l.jpg
Order by

SELECT <kolom> FROM <nama_table>

[WHERE <kondisi>]

ORDER BY <kolom_acuan> [ASC|DESC];

mysql> SELECT * FROM mahasiswa

-> WHERE ipk <3

-> ORDER BY nama DESC;

+---------+--------------+------------+------+

| nrp | nama | tgl_lahir | ipk |

+---------+--------------+------------+------+

| 0471059 | Wonder Woman | 1992-07-12 | 2.19 |

| 0471019 | Superman | 1988-10-22 | 2.45 |

| 0471011 | Batman | 1985-05-14 | 2.77 |

+---------+--------------+------------+------+


Operator like l.jpg
Operator like

Menampilkan data mahasiswa angkatan 2005

mysql> SELECT * FROM mahasiswa

-> WHERE nrp LIKE '05%';

+---------+------------+------------+------+

| nrp | nama | tgl_lahir | ipk |

+---------+------------+------------+------+

| 0571022 | Robin | 1992-03-27 | 3.77 |

| 0571029 | Profesor X | 1991-05-23 | 3.51 |

+---------+------------+------------+------+


Like 2 l.jpg
Like (2)

Menampilkan data mahasiswa yang berulang tahun

pada bulan mei

mysql> SELECT * FROM mahasiswa

-> WHERE tgl_lahir LIKE '%05-__';

+---------+------------+------------+------+

| nrp | nama | tgl_lahir | ipk |

+---------+------------+------------+------+

| 0471011 | Batman | 1985-05-14 | 2.77 |

| 0571029 | Profesor X | 1991-05-23 | 3.51 |

+---------+------------+------------+------+


Operator matematika l.jpg
Operator Matematika

mysql> SELECT ipk "IPK", ipk+1 "+10"

-> ipk-10 "-10", ipk*10 "*10",

-> ipk/10 "/10", ipk%2 “%2”

-> FROM mahasiswa;

+------+------+-------+-------+--------+------+

| IPK | +10 | -10 | *10 | /10 | %2 |

+------+------+-------+-------+--------+------+

| 3.33 | 4.33 | -6.67 | 33.30 | 0.3330 | 1.33 |

| 2.45 | 3.45 | -7.55 | 24.50 | 0.2450 | 0.45 |

| 2.77 | 3.77 | -7.23 | 27.70 | 0.2770 | 0.77 |

| 3.77 | 4.77 | -6.23 | 37.70 | 0.3770 | 1.77 |

| 3.51 | 4.51 | -6.49 | 35.10 | 0.3510 | 1.51 |

| 2.19 | 3.19 | -7.81 | 21.90 | 0.2190 | 0.19 |

+------+------+-------+-------+--------+------+


Sum count l.jpg
Sum, Count

mysql> SELECT SUM(nilai) "nilaitotal"

-> FROM pengambilan

-> WHERE nrp='0471007';

+-------------+

| nilai total |

+-------------+

| 158 |

+-------------+

mysql> SELECT COUNT(8) “Jml Mhs Angkt 04”

-> FROM mahasiswa

-> WHERE nrp LIKE '04%';

+------------------+

| Jml Mhs Angkt 04 |

+------------------+

| 4 |

+------------------+


Avg max min l.jpg
Avg, Max, Min

mysql> SELECT AVG(ipk)

-> FROM mahasiswa

-> WHERE nrp LIKE '05%';

+----------+

| AVG(ipk) |

+----------+

| 3.640000 |

+----------+

mysql> SELECT MIN(ipk) "IPK Terkecil",

-> MAX(ipk) "IPK Terbesar"

-> FROM mahasiswa;

+--------------+--------------+

| IPK Terkecil | IPK Terbesar |

+--------------+--------------+

| 2.19 | 3.77 |

+--------------+--------------+


Group by l.jpg
Group by

SELECT <fungsi_agregat>

FROM <nama_table>

GROUP BY <nama_kolom|alias_kolom>;

mysql> SELECT kd_mk,COUNT(kd_mk)

-> FROM pengambilan

-> GROUP BY kd_mk;

+-------+--------------+

| kd_mk | COUNT(kd_mk) |

+-------+--------------+

| IF002 | 4 |

| IF003 | 1 |

| IF004 | 1 |

+-------+--------------+


Group by 2 l.jpg
Group By (2)

mysql> SELECT nrp,COUNT(*),AVG(nilai),

-> MAX(nilai),MIN(nilai)

-> FROM pengambilan

-> GROUP BY nrp;

+---------+----------+------------+------------+------------+

| nrp | COUNT(*) | AVG(nilai) | MAX(nilai) | MIN(nilai) |

+---------+----------+------------+------------+------------+

| 0471007 | 2 | 79.0000 | 88 | 70 |

| 0471011 | 1 | 88.0000 | 88 | 88 |

| 0471019 | 2 | 89.0000 | 90 | 88 |

| 0571022 | 1 | 82.0000 | 82 | 82 |

+---------+----------+------------+------------+------------+


Fungsi matematika l.jpg
Fungsi matematika

  • ABS(n)

  • POW(x,y)

  • SQRT(n);

  • CEILING(n) dan FLOOR(n)

  • ROUND(n [ ,m ]) dan TRUNCATE(n,m)

  • RADIANS(n) dan DEGREES(n)

  • SIN(n), COS(n), dan TAN(n)

  • PI()

  • BIN(n), OCT(n), dan HEX(n)

  • FORMAT(m,n)


Fungsi string l.jpg
Fungsi String

  • UCASE(x) dan UPPER(x)

  • LCASE(x) dan LOWER(x)

  • ASCII(n) dan CHAR(x)

  • CHAR_LENGTH(x) dan LENGTH(x)

  • CONCAT(x,y [,z,..])

  • STRCMP(x,y);

  • ENCODE(x,y) dan DECODE(x,y)

  • ENRYPT(x) dan PASSWORD(x)

  • COALESCE(x,y);

  • INSERT(x,m,n,y)

  • LTRIM(x), RTRIM(x), dan TRIM(x)

  • LPAD(x,y,z) dan RPAD(x,y,z)

  • REVERSE(x)

  • REPLACE(x,y,z)

  • LEFT(x,n), RIGHT(x,n), dan MID(x,m,n)


Fungsi ascii dan char l.jpg
Fungsi ASCII dan CHAR

mysql> SELECT ASCII('B'),CHAR(66);

+------------+----------+

| ASCII('B') | CHAR(66) |

+------------+----------+

| 66 | B |

+------------+----------+

mysql> SELECT nama_mk,ASCII(nama_mk) FROM

mata_kuliah;

+----------+----------------+

| nama_mk | ASCII(nama_mk) |

+----------+----------------+

| Menembak | 77 |

| Menyamar | 77 |

| Terbang | 84 |

| Memanjat | 77 |

+----------+----------------+


Length dan char length l.jpg
LENGTH dan CHAR_LENGTH

mysql> SELECT nama,LENGTH(nama),

-> CHAR_LENGTH(nama)

-> FROM mahasiswa;

+--------------+--------------+-------------------+

| nama | LENGTH(nama) | CHAR_LENGTH(nama) |

+--------------+--------------+-------------------+

| Spiderman | 9 | 9 |

| Superman | 8 | 8 |

| Batman | 6 | 6 |

| Robin | 5 | 5 |

| Profesor X | 10 | 10 |

| Wonder Woman | 12 | 12 |

+--------------+--------------+-------------------+


Strcmp l.jpg
STRCMP

mysql> SELECT STRCMP('Spiderman','SPIDERMAN');

+---------------------------------+

| STRCMP('Spiderman','SPIDERMAN') |

+---------------------------------+

| 0 |

+---------------------------------+

mysql> SELECT STRCMP('Spiderman','Batman');

+------------------------------+

| STRCMP('Spiderman','Batman') |

+------------------------------+

| 1 |

+------------------------------+

mysql> SELECT STRCMP('Spiderman','Spidermanz');

+----------------------------------+

| STRCMP('Spiderman','Spidermanz') |

+----------------------------------+

| -1 |

+----------------------------------+


Encode dan decode l.jpg
ENCODE dan DECODE

mysql> SELECT nama,ENCODE(nama,'key'),

-> DECODE(ENCODE(nama,'key'),'key')

-> FROM mahasiswa;

+--------------+--------------+----------------------------------+

| nama | acak | DECODE(ENCODE(nama,'key'),'key') |

+--------------+--------------+----------------------------------+

| Spiderman | H? | Spiderman |

| Superman | .+ | Superman |

| Batman | { | Batman |

| Robin | ZG* | Robin |

| Profesor X | mn6R | Profesor X |

| Wonder Woman | $B-sb | Wonder Woman |

+--------------+--------------+----------------------------------+


Encrypt dan password l.jpg
ENCRYPT dan PASSWORD

mysql> SELECT nama,ENCRYPT(nama),PASSWORD(nama)

-> FROM mahasiswa;

+--------------+---------------+------------------+

| nama | ENCRYPT(nama) | PASSWORD(nama) |

+--------------+---------------+------------------+

| Spiderman | DyCsAXu0bZaao | 162ed2e56022ac99 |

| Superman | DydyB2x2xXmXo | 774280dd330aac14 |

| Batman | DyIlblnd77dfw | 0856077d423db292 |

| Robin | DyyMAZuf5ODFE | 406ad76459c5d464 |

| Profesor X | Dyy9LvL6qAONQ | 70a37e8212baf12a |

| Wonder Woman | Dysfyzf4PkiUk | 730b5163068e7931 |

+--------------+---------------+------------------+


Trim ltrim rtrim l.jpg
TRIM, LTRIM, RTRIM

mysql> SELECT LTRIM(' Batman '),

-> RTRIM(' Batman '),

-> TRIM(' Batman ');

+-----------------------+-----------------------+----------------------+

| LTRIM(' Batman ') | RTRIM(' Batman ') | TRIM(' Batman ') |

+-----------------------+-----------------------+----------------------+

| Batman | Batman | Batman |

+-----------------------+-----------------------+----------------------+

mysql> SELECT LENGTH(' Batman ') "asli",

-> LENGTH(LTRIM(' Batman ')) "LTRIM",

-> LENGTH(RTRIM(' Batman ')) "RTRIM",

-> LENGTH(TRIM(' Batman ')) "TRIM";

+------+-------+-------+------+

| asli | LTRIM | RTRIM | TRIM |

+------+-------+-------+------+

| 12 | 9 | 9 | 6 |

+------+-------+-------+------+


Lpad rpad l.jpg
LPAD, RPAD

mysql> SELECT nama,

-> LPAD(nama,15,'*'),

-> RPAD(nama,15,'#')

-> FROM mahasiswa;

+--------------+-------------------+-------------------+

| nama | LPAD(nama,15,'*') | RPAD(nama,15,'#') |

+--------------+-------------------+-------------------+

| Spiderman | ******Spiderman | Spiderman###### |

| Superman | *******Superman | Superman####### |

| Batman | *********Batman | Batman######### |

| Robin | **********Robin | Robin########## |

| Profesor X | *****Profesor X | Profesor X##### |

| Wonder Woman | ***Wonder Woman | Wonder Woman### |

+--------------+-------------------+-------------------+


Left right mid l.jpg
LEFT, RIGHT, MID

mysql> SELECT nama,

-> LEFT(nama,3),

-> RIGHT(nama,3),

-> MID(nama,2,3)

-> FROM mahasiswa;

+--------------+--------------+---------------+---------------+

| nama | LEFT(nama,3) | RIGHT(nama,3) | MID(nama,2,3) |

+--------------+--------------+---------------+---------------+

| Spiderman | Spi | man | pid |

| Superman | Sup | man | upe |

| Batman | Bat | man | atm |

| Robin | Rob | bin | obi |

| Profesor X | Pro | r X | rof |

| Wonder Woman | Won | man | ond |

+--------------+--------------+---------------+---------------+


Slide56 l.jpg
LEFT

mysql> SELECT LEFT(nrp,2)"angkatan",AVG(ipk)

-> FROM mahasiswa

-> GROUP BY angkatan;

+----------+----------+

| angkatan | AVG(ipk) |

+----------+----------+

| 04 | 2.685000 |

| 05 | 3.640000 |

+----------+----------+


Fungsi tanggal l.jpg
Fungsi Tanggal

  • CURDATE() dan CURRENT_DATE()

  • CURTIME() dan CURRENT_TIME()

  • SYSDATE() , NOW() , dan

  • CURRENT_TIMESTAMP()

  • DATE_ADD(d,INTERAL n DAY) dan

  • DATE_SUB(d,INTERVAL n DAY)

  • DATE_FORMAT(d,f)

  • DAYOFWEEK(d) , DAYOFMONTH(d) , dan

  • DAYOFYEAR(d)

  • DAYNAME(d) dan MONTHNAME(d)

  • DAY(d) , MONTH(d) , dan YEAR(d)

  • HOUR(d) , MINUTE(d) , dan SECOND(d)

  • QUARTER(d)


Date add date sub l.jpg
DATE_ADD, DATE_SUB

mysql> SELECT tgl_lahir "asli",

-> DATE_ADD(tgl_lahir, INTERVAL 7 DAY)

-> "DATE_ADD",

-> DATE_SUB(tgl_lahir,INTERVAL 7 DAY)

-> "DATE_SUB"

-> FROM mahasiswa;

+------------+------------+------------+

| asli | DATE_ADD | DATE_SUB |

+------------+------------+------------+

| 1990-02-26 | 1990-03-05 | 1990-02-19 |

| 1988-10-22 | 1988-10-29 | 1988-10-15 |

| 1985-05-14 | 1985-05-21 | 1985-05-07 |

| 1992-03-27 | 1992-04-03 | 1992-03-20 |

| 1991-05-23 | 1991-05-30 | 1991-05-16 |

| 1992-07-12 | 1992-07-19 | 1992-07-05 |

+------------+------------+------------+


Date format l.jpg
DATE_FORMAT

mysql> SELECT nama,tgl_lahir,

-> DATE_FORMAT(tgl_lahir,'%a,%d %M %Y')

-> "format tgl"

-> FROM mahasiswa;

+--------------+------------+----------------------+

| nama | tgl_lahir | format tgl |

+--------------+------------+----------------------+

| Spiderman | 1990-02-26 | Mon,26 February 1990 |

| Superman | 1988-10-22 | Sat,22 October 1988 |

| Batman | 1985-05-14 | Tue,14 May 1985 |

| Robin | 1992-03-27 | Fri,27 March 1992 |

| Profesor X | 1991-05-23 | Thu,23 May 1991 |

| Wonder Woman | 1992-07-12 | Sun,12 July 1992 |

+--------------+------------+----------------------+


Format untuk date format l.jpg
Format untuk DATE_FORMAT

  • %a Format singkat nama hari (Sun..Sat)

  • %b Format singkat bulan (Jan..Dec)

  • %D Format tanggal teurut (1st, 2nd, 3rd, ...)

  • %d Format tanggal 2 digit (00..31)

  • %H Format jam 24 jam (00..23)

  • %h Format jam 12 jam (01..12)

  • %i Format menit 2 digit (00..59)

  • %M Format nama bulan (January..December)

  • %m Format angka bulan (00..12)


Dayofweek month dan year l.jpg
DAYOFWEEK, MONTH, dan YEAR

mysql> SELECT tgl_lahir,

-> DAYOFWEEK(tgl_lahir) "dayofweek",

-> DAYOFMONTH(tgl_lahir) "dayofmonth",

-> DAYOFYEAR(tgl_lahir) "day of year"

-> FROM mahasiswa;

+------------+-------------+--------------+-------------+

| tgl_lahir | day of week | day of month | day of year |

+------------+-------------+--------------+-------------+

| 1990-02-26 | 2 | 26 | 57 |

| 1988-10-22 | 7 | 22 | 296 |

| 1985-05-14 | 3 | 14 | 134 |

| 1992-03-27 | 6 | 27 | 87 |

| 1991-05-23 | 5 | 23 | 143 |

| 1992-07-12 | 1 | 12 | 194 |

+------------+-------------+--------------+-------------+


Day dan monthname l.jpg
DAY dan MONTHNAME

mysql> SELECT tgl_lahir,

-> DAYNAME(tgl_lahir),

-> MONTHNAME(tgl_lahir)

-> FROM mahasiswa;

+------------+--------------------+----------------------+

| tgl_lahir | DAYNAME(tgl_lahir) | MONTHNAME(tgl_lahir) |

+------------+--------------------+----------------------+

| 1990-02-26 | Monday | February |

| 1988-10-22 | Saturday | October |

| 1985-05-14 | Tuesday | May |

| 1992-03-27 | Friday | March |

| 1991-05-23 | Thursday | May |

| 1992-07-12 | Sunday | July |

+------------+--------------------+----------------------+


Fungsi lainnya l.jpg
Fungsi lainnya

  • ISNULL(a)

  • IFNULL(a,b)

  • IF(a,b,c)


Isnull l.jpg
ISNULL

mysql> INSERT INTO pengambilan(nrp,kd_mk)

-> VALUES('0471019','IF004');

mysql> SELECT * FROM pengambilan

-> WHERE ISNULL(nilai);

+---------+-------+-------+

| nrp | kd_mk | nilai |

+---------+-------+-------+

| 0471019 | IF004 | NULL |

+---------+-------+-------+


Ifnull l.jpg
IFNULL

mysql> SELECT nrp,

-> kd_mk,

-> IFNULL(nilai,'kosong')

-> FROM pengambilan;

+---------+-------+------------------------+

| nrp | kd_mk | IFNULL(nilai,'kosong') |

+---------+-------+------------------------+

| 0471007 | IF004 | 70 |

| 0471019 | IF003 | 90 |

| 0471007 | IF002 | 88 |

| 0471019 | IF002 | 88 |

| 0471011 | IF002 | 88 |

| 0571022 | IF002 | 82 |

| 0471019 | IF004 | kosong |

+---------+-------+------------------------+


Slide66 l.jpg
IF

mysql> SELECT nrp,

-> nilai,

-> IF(nilai>=90,'Hebat','Baik')“keterangan”

-> FROM pengambilan;

+---------+-------+------------+

| nrp | nilai | keterangan |

+---------+-------+------------+

| 0471007 | 70 | Baik |

| 0471019 | 90 | Hebat |

| 0471007 | 88 | Baik |

| 0471019 | 88 | Baik |

| 0471011 | 88 | Baik |

| 0571022 | 82 | Baik |

| 0471019 | NULL | Baik |

+---------+-------+------------+


Slide67 l.jpg
IF (2)

mysql> SELECT nrp,nilai,

-> IF(nilai>=90,'A',IF(nilai>=80,'B','C'))

-> "keterangan"

-> FROM pengambilan;

+---------+-------+------------+

| nrp | nilai | keterangan |

+---------+-------+------------+

| 0471007 | 70 | C |

| 0471019 | 90 | A |

| 0471007 | 88 | B |

| 0471019 | 88 | B |

| 0471011 | 88 | B |

| 0571022 | 82 | B |

| 0471019 | NULL | C |

+---------+-------+------------+


Slide68 l.jpg
IF (3)

mysql> SELECT nrp,nilai,

-> IF(nilai>=90,'A',IF(nilai>=80,'B',

-> IF(ISNULL(nilai),'KOSONG','C')))

-> "keterangan"

-> FROM pengambilan;

+---------+-------+------------+

| nrp | nilai | keterangan |

+---------+-------+------------+

| 0471007 | 70 | C |

| 0471019 | 90 | A |

| 0471007 | 88 | B |

| 0471019 | 88 | B |

| 0471011 | 88 | B |

| 0571022 | 82 | B |

| 0471019 | NULL | KOSONG |

+---------+-------+------------+


Relasi cross join l.jpg
Relasi Cross Join

SELECT <nama_kolom>

FROM <table1> CROSS JOIN <table2>;

mysql> SELECT *

-> FROM mahasiswa,pengambilan;

mysql> SELECT *

-> FROM mahasiswa,pengambilan,mata_kuliah;

mysql> SELECT *

-> FROM mahasiswa CROSS JOIN pengambilan;

mysql> SELECT *

-> FROM mahasiswa CROSS JOIN pengambilan

-> CROSS JOIN mata_kuliah;


Inner join l.jpg
Inner Join

SELECT <kolom>

FROM <table1> INNER JOIN <table2>

ON <relasi>;

mysql> SELECT *

-> FROM mahasiswa INNER JOIN pengambilan

-> ON mahasiswa.nrp=pengambilan.nrp;

+---------+-----------+------------+------+---------+-------+-------+

| nrp | nama | tgl_lahir | ipk | nrp | kd_mk | nilai |

+---------+-----------+------------+------+---------+-------+-------+

| 0471007 | Spiderman | 1990-02-26 | 3.33 | 0471007 | IF004 | 70 |

| 0471019 | Superman | 1988-10-22 | 2.45 | 0471019 | IF003 | 90 |

| 0471007 | Spiderman | 1990-02-26 | 3.33 | 0471007 | IF002 | 88 |

| 0471019 | Superman | 1988-10-22 | 2.45 | 0471019 | IF002 | 88 |

| 0471011 | Batman | 1985-05-14 | 2.77 | 0471011 | IF002 | 88 |

| 0571022 | Robin | 1992-03-27 | 3.77 | 0571022 | IF002 | 82 |

| 0471019 | Superman | 1988-10-22 | 2.45 | 0471019 | IF004 | NULL |

+---------+-----------+------------+------+---------+-------+-------+


Table aliasing l.jpg
Table Aliasing

mysql> SELECT m.nrp ,m.nama, p.kd_mk, p.nilai

-> FROM mahasiswa m

-> INNER JOIN pengambilan p

-> ON m.nrp=p.nrp;

+---------+-----------+-------+-------+

| nrp | nama | kd_mk | nilai |

+---------+-----------+-------+-------+

| 0471007 | Spiderman | IF004 | 70 |

| 0471019 | Superman | IF003 | 90 |

| 0471007 | Spiderman | IF002 | 88 |

| 0471019 | Superman | IF002 | 88 |

| 0471011 | Batman | IF002 | 88 |

| 0571022 | Robin | IF002 | 82 |

| 0471019 | Superman | IF004 | NULL |

+---------+-----------+-------+-------+


Inner join 2 table l.jpg
Inner join > 2 table

mysql> SELECT m.nrp,m.nama,mk.nama_mk,p.nilai

-> FROM mahasiswa m

-> INNER JOIN pengambilan p

-> ON m.nrp=p.nrp

-> INNER JOIN mata_kuliah mk

-> ON p.kd_mk=mk.kd_mk;

+---------+-----------+----------+-------+

| nrp | nama | nama_mk | nilai |

+---------+-----------+----------+-------+

| 0471007 | Spiderman | Memanjat | 70 |

| 0471019 | Superman | Terbang | 90 |

| 0471007 | Spiderman | Menyamar | 88 |

| 0471019 | Superman | Menyamar | 88 |

| 0471011 | Batman | Menyamar | 88 |

| 0571022 | Robin | Menyamar | 82 |

| 0471019 | Superman | Memanjat | NULL |

+---------+-----------+----------+-------+


Bentuk lain inner join l.jpg
Bentuk lain inner join

SELECT <kolom> FROM <table1> , <table2> WHERE <relasi>;

mysql> SELECT m.nrp,m.nama,mk.nama_mk,p.nilai

-> FROM mahasiswa m, pengambilan p,

-> mata_kuliah mk

-> WHERE m.nrp=p.nrp AND p.kd_mk=mk.kd_mk;

+---------+-----------+----------+-------+

| nrp | nama | nama_mk | nilai |

+---------+-----------+----------+-------+

| 0471007 | Spiderman | Memanjat | 70 |

| 0471019 | Superman | Terbang | 90 |

| 0471007 | Spiderman | Menyamar | 88 |

| 0471019 | Superman | Menyamar | 88 |

| 0471011 | Batman | Menyamar | 88 |

| 0571022 | Robin | Menyamar | 82 |

| 0471019 | Superman | Memanjat | NULL |

+---------+-----------+----------+-------+


Data control language l.jpg
Data Control Language

CREATE USER <nama_user>@<nama_host> IDENTIFIED BY '<password>';

mysql> CREATE USER [email protected]

-> IDENTIFIED BY ‘123anton';

SELECT user,host,password FROM user;

mysql> SELECT user,host,password FROM user;

+------------------+-----------+------------------+

| user | host | password |

+------------------+-----------+------------------+

| root | localhost | |

| root | my-ubuntu | |

| debian-sys-maint | localhost | 1d272cd33c9de0ee |

| anton | localhost | 6a4c41e8176a7981 |

+------------------+-----------+------------------+


Drop user l.jpg
Drop User

DROP USER <nama_user>@<nama_host>;

mysql> DROP USER [email protected];

mysql> SELECT user,host,password FROM user;

+------------------+-----------+------------------+

| user | host | password |

+------------------+-----------+------------------+

| root | localhost | |

| root | my-ubuntu | |

| debian-sys-maint | localhost | 1d272cd33c9de0ee |

+------------------+-----------+------------------+


Slide76 l.jpg
NEXT

  • PHP


ad