pemrograman internet mobile l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Pemrograman Internet Mobile PowerPoint Presentation
Download Presentation
Pemrograman Internet Mobile

Loading in 2 Seconds...

play fullscreen
1 / 76

Pemrograman Internet Mobile - PowerPoint PPT Presentation


  • 343 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 'Pemrograman Internet Mobile' - Anita


Download Now 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

Pemrograman Internet Mobile

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

MySQL

sql pada 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
Melihat Versi MySQL
  • SELECT VERSION();

mysql> SELECT VERSION();

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

| VERSION() |

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

| 4.1.12-Debian_1ubuntu3.1-log |

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

melihat database mysql
Melihat Database MySQL

SHOW DATABASES;

mysql> SHOW DATABASES;

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

| Database |

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

| mysql |

| test |

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

create database
Create Database
  • CREATE DATABASE <nama_databases>;

mysql> CREATE DATABASE coba;

mysql> SHOW DATABASES;

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

| Database |

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

| coba |

| mysql |

| test |

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

menghapus database
Menghapus Database
  • DROP DATABASE <nama_database>;

mysql> DROP DATABASE coba;

mysql> SHOW DATABASES;

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

| Database |

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

| mysql |

| test |

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

menggunakan database
Menggunakan Database
  • USE <nama_database>;

mysql> CREATE DATABASE universitas;

mysql> SHOW DATABASES;

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

| Database |

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

| mysql |

| test |

| universitas |

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

mysql> USE universitas;

Database changed

create table
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
Select Db dan Show Table

SELECT DATABASE();

mysql> SELECT DATABASE();

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

| DATABASE() |

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

| universitas |

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

SHOW TABLES;

mysql> SHOW TABLES;

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

| Tables_in_universitas |

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

| pegawai |

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

menghapus table
Menghapus table

DROP TABLE <nama_table>;

mysql> DROP TABLE karyawan;

mysql> SHOW TABLES;

Empty set (0.01 sec)

melihat deskripsi table
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Fungsi lainnya
  • ISNULL(a)
  • IFNULL(a,b)
  • IF(a,b,c)
isnull
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
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
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
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
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
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
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
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
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
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
Data Control Language

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

mysql> CREATE USER anton@localhost

-> 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
Drop User

DROP USER <nama_user>@<nama_host>;

mysql> DROP USER boedy@localhost;

mysql> SELECT user,host,password FROM user;

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

| user | host | password |

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

| root | localhost | |

| root | my-ubuntu | |

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

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

slide76
NEXT
  • PHP