800 likes | 1.31k Vues
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.
E N D
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 • SELECT VERSION(); mysql> SELECT VERSION(); +------------------------------+ | VERSION() | +------------------------------+ | 4.1.12-Debian_1ubuntu3.1-log | +------------------------------+
Melihat Database MySQL SHOW DATABASES; mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+
Create Database • CREATE DATABASE <nama_databases>; mysql> CREATE DATABASE coba; mysql> SHOW DATABASES; +----------+ | Database | +----------+ | coba | | mysql | | test | +----------+
Menghapus Database • DROP DATABASE <nama_database>; mysql> DROP DATABASE coba; mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+
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 <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 DATABASE(); mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | universitas | +-------------+ SHOW TABLES; mysql> SHOW TABLES; +-----------------------+ | Tables_in_universitas | +-----------------------+ | pegawai | +-----------------------+
Menghapus table DROP TABLE <nama_table>; mysql> DROP TABLE karyawan; mysql> SHOW TABLES; Empty set (0.01 sec)
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 • ALTER TABLE <nama_table> RENAME <nama_baru>; mysql> ALTER TABLE pegawai RENAME karyawan; mysql> SHOW TABLES; +-----------------------+ | Tables_in_universitas | +-----------------------+ | karyawan | +-----------------------+
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 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 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 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 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 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 <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 | | +-----------+-------------+------+-----+---------+-------+
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 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 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 <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 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 TABLE <nama_table>; TRUNCATE <nama_table>; mysql> TRUNCATE TABLE mahasiswa; mysql> SELECT * FROM mahasiswa; Empty set (0.00 sec)
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) 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) 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 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 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 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 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 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 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 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 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 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 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 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) 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 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 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 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 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) 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 • 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 • 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 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 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 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 | +----------------------------------+