1 / 76

Pemrograman Internet Mobile

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.

Anita
Download Presentation

Pemrograman Internet Mobile

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Pemrograman Internet Mobile Antonius R.C, S.Kom, M.Cs MySQL

  2. 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

  3. Melihat Versi MySQL • SELECT VERSION(); mysql> SELECT VERSION(); +------------------------------+ | VERSION() | +------------------------------+ | 4.1.12-Debian_1ubuntu3.1-log | +------------------------------+

  4. Melihat Database MySQL SHOW DATABASES; mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+

  5. Create Database • CREATE DATABASE <nama_databases>; mysql> CREATE DATABASE coba; mysql> SHOW DATABASES; +----------+ | Database | +----------+ | coba | | mysql | | test | +----------+

  6. Menghapus Database • DROP DATABASE <nama_database>; mysql> DROP DATABASE coba; mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+

  7. Menggunakan Database • USE <nama_database>; mysql> CREATE DATABASE universitas; mysql> SHOW DATABASES; +-------------+ | Database | +-------------+ | mysql | | test | | universitas | +-------------+ mysql> USE universitas; Database changed

  8. Create Table CREATE TABLE <nama_table> (<nama_kolom1> <tipe_data1>, <nama_kolom2> <tipe_data2>, ...); mysql> CREATE TABLE pegawai -> (nik CHAR(4), nama VARCHAR(20));

  9. Select Db dan Show Table SELECT DATABASE(); mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | universitas | +-------------+ SHOW TABLES; mysql> SHOW TABLES; +-----------------------+ | Tables_in_universitas | +-----------------------+ | pegawai | +-----------------------+

  10. Menghapus table DROP TABLE <nama_table>; mysql> DROP TABLE karyawan; mysql> SHOW TABLES; Empty set (0.01 sec)

  11. 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 | | +-------+-------------+------+-----+---------+-------+

  12. MENGUBAH NAMA TABLE • ALTER TABLE <nama_table> RENAME <nama_baru>; mysql> ALTER TABLE pegawai RENAME karyawan; mysql> SHOW TABLES; +-----------------------+ | Tables_in_universitas | +-----------------------+ | karyawan | +-----------------------+

  13. 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 | | +-------+-------------+------+-----+---------+-------+

  14. 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 | | +-----------+-------------+------+-----+---------+-------+

  15. 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 | | +-----------+-------------+------+-----+---------+-------+

  16. 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 | | +-----------+-------------+------+-----+---------+-------+

  17. 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 | | +-----------+-------------+------+-----+---------+-------+

  18. 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 | | +-------+-------------+------+-----+---------+-------+

  19. 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 | | +-----------+-------------+------+-----+---------+-------+

  20. 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 | | +-------+------------+------+-----+---------+-------+

  21. 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));

  22. 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 | +---------+------+------------+------+

  23. 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 | +---------+-------+------------+------+

  24. 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 | +---------+-------+------------+------+

  25. Truncate (Mengkosongkan table) TRUNCATE TABLE <nama_table>; TRUNCATE <nama_table>; mysql> TRUNCATE TABLE mahasiswa; mysql> SELECT * FROM mahasiswa; Empty set (0.00 sec)

  26. 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 | | +-------+-------------+------+-----+---------+----------------+

  27. 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 | +----+-------+------+

  28. 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 | +----+-------+------+

  29. 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 | | +-------+-------------+------+-----+---------+----------------+

  30. 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 | +----+--------+------+

  31. 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

  32. 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

  33. 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 | +------+-------+

  34. 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 | +-------+----------+------+

  35. 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;

  36. 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 | +---------+-------+-------+

  37. 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 | +--------------+---------------------------+

  38. 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 | +---------+--------------+------------+------+

  39. 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 | +---------+------------+------------+------+

  40. 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 | +---------+------------+------------+------+

  41. 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 | +------+------+-------+-------+--------+------+

  42. 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 | +------------------+

  43. 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 | +--------------+--------------+

  44. 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 | +-------+--------------+

  45. 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 | +---------+----------+------------+------------+------------+

  46. 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)

  47. 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)

  48. 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 | +----------+----------------+

  49. 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 | +--------------+--------------+-------------------+

  50. 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 | +----------------------------------+

More Related