2. Membuat Table
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View 2. Membuat Table as PDF for free.
More details
- Words: 2,132
- Pages: 13
MEMBUAT DATABASE 1. lakukan koneksi ke mysql 2. buatlah databasenya dengan perintah "create " Contoh: --------------TAMPILAN---------------mysql> create database akademis; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | akademis | | cdcol | | mysql | | penjualan | | phpmyadmin | | test | +--------------------+ 7 rows in set (0.00 sec) mysql>use akademis; Database changed mysql>
--------------END OF TAMPILAN---------------Untuk membuat sebuah tabel dengan nama mahasiswa dan struktur sbb: nama kolom type data lebar --------------------------------------nobp text 7 nama taxt 40 jurusan text 20 alamat text 45 tmpatlahir text 15 tanggallahir tanggal Perintah SQLnya sbb create table mahasiswa( nobp char(7), nama varchar(40), jurusan char(20),
alamat varchar(45), tempatlahir varchar(15), tanggallahir datetime ) -------------TAMPILAN-----------mysql> create table mahasiswa( -> nobp char(7), -> nama varchar(40), -> jurusan char(20), -> alamat varchar(45), -> tempatlahir varchar(15), -> tanggallahir datetime -> ) -> ; Query OK, 0 rows affected (0.00 sec) -------------END OF TAMPILAN---------Untuk memeriksa apakah table yang dibuat sudah ada dalam database, gunakan perintah: "show tables" ------TAMPILAN---------mysql> show tables; +--------------------+ | Tables_in_akademis | +--------------------+ | mahasiswa | +--------------------+ 1 row in set (0.00 sec)
----------END OF TAMPILAN----------jika ingin melihat daftar tabel yang ada di database yang berbeda dengan database aktif saat ini, misal saat ini kita berada di dalam database akademis dan ingin mengetahui table apa saja yang ada di dalam database penjualan, peritah yang digunakan adalah: show tables from; show tables from penjualan;
-------------TAMPILAN----------------mysql> create table matakuliah( -> kodemk char(5), -> namamk char(20), -> sks int) -> ; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +--------------------+ | Tables_in_akademis | +--------------------+ | mahasiswa | | matakuliah | +--------------------+ 2 rows in set (0.00 sec) mysql> -------------END OF TAMPILAN----------------------untuk mengisi data ke dalam table, contohnya adalah sebagai berikut: mysql> INSERT INTO mahasiswa (NOBP, NAMA, JURUSAN, ALAMAT, TEMPATLAHIR, TANGGALLAHIR) -> VALUES ("0800030","FITRIA NINGSIH","MANAJEMEN INFORMATIKA", ->-> "JL. S. PARMAN NO 117 D LOLONG PADANG","PADANG","1987-0905") -> ; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from mahasiswa; +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | nobp | nama | jurusan | alamat | tempatlahir | tanggallahir | +---------+---------------+----------------------+-------------------------------------+-------------+---------------------+ | 0800030 | FITRIA NINGSIH | MANAJEMEN INFORMATIK | JL. S. PARMAN NO 117 D LOLONG PADANG | PADANG | 1987-09-05 00:00:00 | +---------+---------------+----------------------+-------------------------------------+-------------+---------------------+ 1 row in set (0.00 sec)
mysql> insert into mahasiswa (nama, nobp, alamat, jurusan, tanggallahir, tempatlahir) values ("WEMPY JASENA","0800079","DAMAR 1 NO 10F OLO LADANG", -> "MANAJEMEN INFORMATIKA","1989-07-13","BONJOL"); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from mahasiswa; +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | nobp | nama | jurusan | alamat | tempatlahir | tanggallahir | +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | 0800030 | FITRIA NINGSIH | MANAJEMEN INFORMATIK | JL. S. PARMAN NO 117 D LOLONG PADANG | PADANG | 1987-09-05 00:00:00 | | 0800079 | WEMPY JASENA | MANAJEMEN INFORMATIK | DAMAR 1 NO 10F OLO LADANG | BONJOL | 1989-07-13 00:00:00 | +---------+---------------+----------------------+-------------------------------------+-------------+---------------------+ 2 rows in set (0.00 sec)
mysql> select * from mahasiwa; ERROR 1146 (42S02): Table 'akademis.mahasiwa' doesn't exist mysql> select * from mahasiswa; +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | nobp | nama | jurusan | alamat | tempatlahir | tanggallahir | +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | 0800030 | FITRIA NINGSIH | MANAJEMEN INFORMATIK | JL. S. PARMAN NO 117 D LOLONG PADANG | PADANG | 1987-09-05 00:00:00 | | 0800079 | WEMPY JASENA | MANAJEMEN INFORMATIK | DAMAR 1 NO 10F OLO LADANG | BONJOL | 1989-07-13 00:00:00 | | 0800040 | Leny Savitri | NULL | Jl. Damar 3 No. 4D Padang
| NULL | NULL | +---------+---------------+----------------------+-------------------------------------+-------------+---------------------+ 3 rows in set (0.00 sec)
mysql> insert into mahasiswa values ("0800008","ANDRE TORANI","MANAJEMEN INFORMATIKA","JL. VETERAN NO 6 PADANG","PADANG","1987-05-05"); mysql> select * from mahasiswa; +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | nobp | nama | jurusan | alamat | tempatlahir | tanggallahir | +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | 0800030 | FITRIA NINGSIH | MANAJEMEN INFORMATIK | JL. S. PARMAN NO 117 D LOLONG PADANG | PADANG | 1987-09-05 00:00:00 | | 0800079 | WEMPY JASENA | MANAJEMEN INFORMATIK | DAMAR 1 NO 10F OLO LADANG | BONJOL | 1989-07-13 00:00:00 | | 0800040 | Leny Savitri | NULL | Jl. Damar 3 No. 4D Padang | NULL | NULL | | 0800008 | ANDRE TORANI | MANAJEMEN INFORMATIK | JL. VETERAN NO 6 PADANG | PADANG | 1987-05-05 00:00:00 | | 0800008 | ANDRE TORANI | MANAJEMEN INFORMATIK | JL. VETERAN NO 6 PADANG | PADANG | 198705-05 00:00:00 | +--------+----------------+---------------------+--------------------------------------+------------+---------------------+ 5 rows in set (0.00 sec)
UNTUK MENGHAPUS DATA, MAKA PERINTAH YANG DIGUNAKAN ADALAH: DELETE FROM [WHERE ] Contoh, untuk menghapus data dengan no bp 0310039, perintahnya
adalah DELETE FROM mahasiswa WHERE NOBP="0310039" perhatikan kondisi penghapusan agar hanya data yang hendak dihapus saja yang hilang dari table ---------Cara merubah data, perintah yang digunakan adalah UPDATE.
misal, jika hendak merubah data jurusan untuk record dengan no bp 0800040 dari "NULL" menjadi "MANAJEMEN INFORMATIKA" perintah yang digunakan adalah: UPDATE mahasiswa SET JURUSAN="MANAJEMEN INFORMATIKA" where nobp="0800040"
--------------TAMPILAN------------------mysql> select nobp,jurusan from mahasiswa; +---------+----------------------+ | nobp | jurusan | +---------+----------------------+ | 0800030 | MANAJEMEN INFORMATIK | | 0800079 | MANAJEMEN INFORMATIK | | 0800040 | NULL | | 0800008 | MANAJEMEN INFORMATIK | | 0800008 | MANAJEMEN INFORMATIK | +---------+----------------------+ 5 rows in set (0.00 sec) mysql> UPDATE mahasiswa SET JURUSAN="MANAJEMEN INFORMATIKA" where nobp="0800040" -> ; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> mysql> select nobp,jurusan from mahasiswa; +---------+----------------------+ | nobp | jurusan | +---------+----------------------+ | 0800030 | MANAJEMEN INFORMATIK | | 0800079 | MANAJEMEN INFORMATIK | | 0800040 | MANAJEMEN INFORMATIK | | 0800008 | MANAJEMEN INFORMATIK |
| 0800008 | MANAJEMEN INFORMATIK | +---------+----------------------+ 5 rows in set (0.00 sec)
---------------END OF TAMPILAN-------------
----------------------TAMPILAN-------------mysql> select nobp, nama,tempatlahir, tanggallahir from mahasiswa; +---------+----------------+-------------+---------------------+ | nobp | nama | tempatlahir | tanggallahir | +---------+----------------+-------------+---------------------+ | 0800030 | FITRIA NINGSIH | PADANG | 1987-09-05 00:00:00 | | 0800079 | WEMPY JASENA | BONJOL | 1989-07-13 00:00:00 | | 0800040 | Leny Savitri | NULL | NULL | | 0800008 | ANDRE TORANI | PADANG | 1987-05-05 00:00:00 | | 0800008 | ANDRE TORANI | PADANG | 1987-05-05 00:00:00 | +---------+----------------+-------------+---------------------+ 5 rows in set (0.00 sec) mysql> -> -> ->
update mahasiswa set nama="LENY SAVITRI" , tempatlahir="PADANG", tanggallahir="1987-12-20" where nobp="0800040"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select nobp, nama,tempatlahir, tanggallahir from mahasiswa; +---------+----------------+------------+---------------------+ | nobp | nama | tempatlahir | tanggallahir | +---------+----------------+------------+---------------------+ | 0800030 | FITRIA NINGSIH | PADANG | 1987-09-05 00:00:00 | | 0800079 | WEMPY JASENA | BONJOL | 1989-07-13 00:00:00 | | 0800040 | LENY SAVITRI | PADANG | 1987-12-20 00:00:00 | | 0800008 | ANDRE TORANI | PADANG | 1987-05-05 00:00:00 | | 0800008 | ANDRE TORANI | PADANG | 1987-05-05
00:00:00 | +---------+----------------+------------+---------------------+ 5 rows in set (0.00 sec)
---------------END OF TAMPILAN--------------
Contoh select mysql> select nobp, nama,tempatlahir, tanggallahir from mahasiswa; mysql> select nama from mahasiswa where tempatlahir="padang"; mysql> SELECT NOBP,NAMA,tempatlahir FROM mahasiswa where tempatlahir<>"PADANG"
mysql> select nobp, nama, tanggallahir -> from mahasiswa -> where day(tanggallahir)=5; +---------+----------------+---------------------+ | nobp | nama | tanggallahir | +---------+----------------+---------------------+ | 0800030 | FITRIA NINGSIH | 1987-09-05 00:00:00 | | 0800008 | ANDRE TORANI | 1987-05-05 00:00:00 | | 0800008 | ANDRE TORANI | 1987-05-05 00:00:00 | +---------+----------------+---------------------+ 3 rows in set (0.00 sec) mysql> select nobp, nama, tanggallahir -> from mahasiswa -> where year(tanggallahir)>1987; +---------+--------------+---------------------+ | nobp | nama | tanggallahir | +---------+--------------+---------------------+ | 0800079 | WEMPY JASENA | 1989-07-13 00:00:00 | +---------+--------------+---------------------+ 1 row in set (0.00 sec)
mysql> select nama from mahasiswa
-> where length(nama)>14; +------------------+ | nama | +------------------+ | ANDRES FERNANDES | +------------------+ 1 row in set (0.00 sec)
mysql> select nama from mahasiswa where length(nama)<13; +--------------+ | nama | +--------------+ | WEMPY JASENA | | LENY SAVITRI | | ANDRE TORANI | | ANDRE TORANI | +--------------+ 4 rows in set (0.00 sec)
ql> select nobp,nama from mahasiswa -> where left(nama,1)="A"; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | +---------+------------------+ 3 rows in set (0.00 sec)
mysql> select nobp, nama from mahasiswa -> where right(nama,1)="i"; +---------+--------------+ | nobp | nama | +---------+--------------+ | 0800040 | LENY SAVITRI | | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | +---------+--------------+ 3 rows in set (0.00 sec)
ql> select nobp, nama from mahasiswa -> where left(nama,5)="andre"; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | +---------+------------------+ 3 rows in set (0.00 sec)
ql> select nobp, nama from mahasiswa -> where nama like "%andre%"; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | | 0900000 | JOHNY ANDREAN | +---------+------------------+ 4 rows in set (0.00 sec)
mysql> select nobp, nama from mahasiswa -> where nama like "%andre%"; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | | 0900000 | JOHNY ANDREAN | +---------+------------------+ 4 rows in set (0.00 sec) mysql> mysql> select nobp, nama from mahasiswa -> where nama like "%andre%" and nama not like "%torani%"; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800009 | ANDRES FERNANDES | | 0900000 | JOHNY ANDREAN |
+---------+------------------+ 2 rows in set (0.00 sec)
ql> select nobp, nama from mahasiswa order by nobp asc; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | | 0800030 | FITRIA NINGSIH | | 0800040 | LENY SAVITRI | | 0800079 | WEMPY JASENA | | 0900000 | JOHNY ANDREAN | +---------+------------------+ 7 rows in set (0.00 sec)
mysql> select nobp, nama from mahasiswa order by nobp asc; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | | 0800030 | FITRIA NINGSIH | | 0800040 | LENY SAVITRI | | 0800079 | WEMPY JASENA | | 0900000 | JOHNY ANDREAN | +---------+------------------+ 7 rows in set (0.00 sec)
mysql> select nobp, nama from mahasiswa order by nama asc; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | | 0800030 | FITRIA NINGSIH | | 0900000 | JOHNY ANDREAN | | 0800040 | LENY SAVITRI | | 0800079 | WEMPY JASENA | +---------+------------------+
7 rows in set (0.00 sec)
mysql> select nobp, nama from mahasiswa order by nama desc; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800079 | WEMPY JASENA | | 0800040 | LENY SAVITRI | | 0900000 | JOHNY ANDREAN | | 0800030 | FITRIA NINGSIH | | 0800009 | ANDRES FERNANDES | | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | +---------+------------------+ 7 rows in set (0.00 sec)
mysql> select nama, tempatlahir,"a" as keterangan from mahasiswa; +------------------+-------------+------------+ | nama | tempatlahir | keterangan | +------------------+-------------+------------+ | FITRIA NINGSIH | PADANG | a | | WEMPY JASENA | BONJOL | a | | LENY SAVITRI | PADANG | a | | ANDRE TORANI | PADANG | a | | ANDRE TORANI | PADANG | a | | ANDRES FERNANDES | PAINAN | a | | JOHNY ANDREAN | NULL | a | +------------------+-------------+------------+ 7 rows in set (0.00 sec)
mysql> select nama as "nama mahasiswa" from mahasiswa; +------------------+ | nama mahasiswa | +------------------+ | FITRIA NINGSIH | | WEMPY JASENA | | LENY SAVITRI | | ANDRE TORANI | | ANDRE TORANI |
| ANDRES FERNANDES | | JOHNY ANDREAN | +------------------+
mysql> select nama,tempatlahir, -> case tempatlahir -> when "PADANG" then "Dekat" -> end as "Keterangan" -> from mahasiswa; +------------------+-------------+------------+ | nama | tempatlahir | Keterangan | +------------------+-------------+------------+ | FITRIA NINGSIH | PADANG | Dekat | | WEMPY JASENA | BONJOL | NULL | | LENY SAVITRI | PADANG | Dekat | | ANDRE TORANI | PADANG | Dekat | | ANDRE TORANI | PADANG | Dekat | | ANDRES FERNANDES | PAINAN | NULL | | JOHNY ANDREAN | NULL | NULL | +------------------+-------------+------------+ 7 rows in set (0.00 sec) i
mysql> mysql> select nama,tempatlahir, -> case tempatlahir -> when "PADANG" then "Dekat" -> else "JAUH" -> end as keterangan -> from mahasiswa; +------------------+-------------+------------+ | nama | tempatlahir | keterangan | +------------------+-------------+------------+ | FITRIA NINGSIH | PADANG | Dekat | | WEMPY JASENA | BONJOL | JAUH | | LENY SAVITRI | PADANG | Dekat | | ANDRE TORANI | PADANG | Dekat | | ANDRE TORANI | PADANG | Dekat | | ANDRES FERNANDES | PAINAN | JAUH | | JOHNY ANDREAN | NULL | JAUH | +------------------+-------------+------------+ 7 rows in set (0.00 sec) -
--------------END OF TAMPILAN---------------Untuk membuat sebuah tabel dengan nama mahasiswa dan struktur sbb: nama kolom type data lebar --------------------------------------nobp text 7 nama taxt 40 jurusan text 20 alamat text 45 tmpatlahir text 15 tanggallahir tanggal Perintah SQLnya sbb create table mahasiswa( nobp char(7), nama varchar(40), jurusan char(20),
alamat varchar(45), tempatlahir varchar(15), tanggallahir datetime ) -------------TAMPILAN-----------mysql> create table mahasiswa( -> nobp char(7), -> nama varchar(40), -> jurusan char(20), -> alamat varchar(45), -> tempatlahir varchar(15), -> tanggallahir datetime -> ) -> ; Query OK, 0 rows affected (0.00 sec) -------------END OF TAMPILAN---------Untuk memeriksa apakah table yang dibuat sudah ada dalam database, gunakan perintah: "show tables" ------TAMPILAN---------mysql> show tables; +--------------------+ | Tables_in_akademis | +--------------------+ | mahasiswa | +--------------------+ 1 row in set (0.00 sec)
----------END OF TAMPILAN----------jika ingin melihat daftar tabel yang ada di database yang berbeda dengan database aktif saat ini, misal saat ini kita berada di dalam database akademis dan ingin mengetahui table apa saja yang ada di dalam database penjualan, peritah yang digunakan adalah: show tables from
-------------TAMPILAN----------------mysql> create table matakuliah( -> kodemk char(5), -> namamk char(20), -> sks int) -> ; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +--------------------+ | Tables_in_akademis | +--------------------+ | mahasiswa | | matakuliah | +--------------------+ 2 rows in set (0.00 sec) mysql> -------------END OF TAMPILAN----------------------untuk mengisi data ke dalam table, contohnya adalah sebagai berikut: mysql> INSERT INTO mahasiswa (NOBP, NAMA, JURUSAN, ALAMAT, TEMPATLAHIR, TANGGALLAHIR) -> VALUES ("0800030","FITRIA NINGSIH","MANAJEMEN INFORMATIKA", ->-> "JL. S. PARMAN NO 117 D LOLONG PADANG","PADANG","1987-0905") -> ; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from mahasiswa; +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | nobp | nama | jurusan | alamat | tempatlahir | tanggallahir | +---------+---------------+----------------------+-------------------------------------+-------------+---------------------+ | 0800030 | FITRIA NINGSIH | MANAJEMEN INFORMATIK | JL. S. PARMAN NO 117 D LOLONG PADANG | PADANG | 1987-09-05 00:00:00 | +---------+---------------+----------------------+-------------------------------------+-------------+---------------------+ 1 row in set (0.00 sec)
mysql> insert into mahasiswa (nama, nobp, alamat, jurusan, tanggallahir, tempatlahir) values ("WEMPY JASENA","0800079","DAMAR 1 NO 10F OLO LADANG", -> "MANAJEMEN INFORMATIKA","1989-07-13","BONJOL"); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from mahasiswa; +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | nobp | nama | jurusan | alamat | tempatlahir | tanggallahir | +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | 0800030 | FITRIA NINGSIH | MANAJEMEN INFORMATIK | JL. S. PARMAN NO 117 D LOLONG PADANG | PADANG | 1987-09-05 00:00:00 | | 0800079 | WEMPY JASENA | MANAJEMEN INFORMATIK | DAMAR 1 NO 10F OLO LADANG | BONJOL | 1989-07-13 00:00:00 | +---------+---------------+----------------------+-------------------------------------+-------------+---------------------+ 2 rows in set (0.00 sec)
mysql> select * from mahasiwa; ERROR 1146 (42S02): Table 'akademis.mahasiwa' doesn't exist mysql> select * from mahasiswa; +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | nobp | nama | jurusan | alamat | tempatlahir | tanggallahir | +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | 0800030 | FITRIA NINGSIH | MANAJEMEN INFORMATIK | JL. S. PARMAN NO 117 D LOLONG PADANG | PADANG | 1987-09-05 00:00:00 | | 0800079 | WEMPY JASENA | MANAJEMEN INFORMATIK | DAMAR 1 NO 10F OLO LADANG | BONJOL | 1989-07-13 00:00:00 | | 0800040 | Leny Savitri | NULL | Jl. Damar 3 No. 4D Padang
| NULL | NULL | +---------+---------------+----------------------+-------------------------------------+-------------+---------------------+ 3 rows in set (0.00 sec)
mysql> insert into mahasiswa values ("0800008","ANDRE TORANI","MANAJEMEN INFORMATIKA","JL. VETERAN NO 6 PADANG","PADANG","1987-05-05"); mysql> select * from mahasiswa; +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | nobp | nama | jurusan | alamat | tempatlahir | tanggallahir | +---------+----------------+---------------------+--------------------------------------+------------+---------------------+ | 0800030 | FITRIA NINGSIH | MANAJEMEN INFORMATIK | JL. S. PARMAN NO 117 D LOLONG PADANG | PADANG | 1987-09-05 00:00:00 | | 0800079 | WEMPY JASENA | MANAJEMEN INFORMATIK | DAMAR 1 NO 10F OLO LADANG | BONJOL | 1989-07-13 00:00:00 | | 0800040 | Leny Savitri | NULL | Jl. Damar 3 No. 4D Padang | NULL | NULL | | 0800008 | ANDRE TORANI | MANAJEMEN INFORMATIK | JL. VETERAN NO 6 PADANG | PADANG | 1987-05-05 00:00:00 | | 0800008 | ANDRE TORANI | MANAJEMEN INFORMATIK | JL. VETERAN NO 6 PADANG | PADANG | 198705-05 00:00:00 | +--------+----------------+---------------------+--------------------------------------+------------+---------------------+ 5 rows in set (0.00 sec)
UNTUK MENGHAPUS DATA, MAKA PERINTAH YANG DIGUNAKAN ADALAH: DELETE FROM
adalah DELETE FROM mahasiswa WHERE NOBP="0310039" perhatikan kondisi penghapusan agar hanya data yang hendak dihapus saja yang hilang dari table ---------Cara merubah data, perintah yang digunakan adalah UPDATE.
misal, jika hendak merubah data jurusan untuk record dengan no bp 0800040 dari "NULL" menjadi "MANAJEMEN INFORMATIKA" perintah yang digunakan adalah: UPDATE mahasiswa SET JURUSAN="MANAJEMEN INFORMATIKA" where nobp="0800040"
--------------TAMPILAN------------------mysql> select nobp,jurusan from mahasiswa; +---------+----------------------+ | nobp | jurusan | +---------+----------------------+ | 0800030 | MANAJEMEN INFORMATIK | | 0800079 | MANAJEMEN INFORMATIK | | 0800040 | NULL | | 0800008 | MANAJEMEN INFORMATIK | | 0800008 | MANAJEMEN INFORMATIK | +---------+----------------------+ 5 rows in set (0.00 sec) mysql> UPDATE mahasiswa SET JURUSAN="MANAJEMEN INFORMATIKA" where nobp="0800040" -> ; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> mysql> select nobp,jurusan from mahasiswa; +---------+----------------------+ | nobp | jurusan | +---------+----------------------+ | 0800030 | MANAJEMEN INFORMATIK | | 0800079 | MANAJEMEN INFORMATIK | | 0800040 | MANAJEMEN INFORMATIK | | 0800008 | MANAJEMEN INFORMATIK |
| 0800008 | MANAJEMEN INFORMATIK | +---------+----------------------+ 5 rows in set (0.00 sec)
---------------END OF TAMPILAN-------------
----------------------TAMPILAN-------------mysql> select nobp, nama,tempatlahir, tanggallahir from mahasiswa; +---------+----------------+-------------+---------------------+ | nobp | nama | tempatlahir | tanggallahir | +---------+----------------+-------------+---------------------+ | 0800030 | FITRIA NINGSIH | PADANG | 1987-09-05 00:00:00 | | 0800079 | WEMPY JASENA | BONJOL | 1989-07-13 00:00:00 | | 0800040 | Leny Savitri | NULL | NULL | | 0800008 | ANDRE TORANI | PADANG | 1987-05-05 00:00:00 | | 0800008 | ANDRE TORANI | PADANG | 1987-05-05 00:00:00 | +---------+----------------+-------------+---------------------+ 5 rows in set (0.00 sec) mysql> -> -> ->
update mahasiswa set nama="LENY SAVITRI" , tempatlahir="PADANG", tanggallahir="1987-12-20" where nobp="0800040"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select nobp, nama,tempatlahir, tanggallahir from mahasiswa; +---------+----------------+------------+---------------------+ | nobp | nama | tempatlahir | tanggallahir | +---------+----------------+------------+---------------------+ | 0800030 | FITRIA NINGSIH | PADANG | 1987-09-05 00:00:00 | | 0800079 | WEMPY JASENA | BONJOL | 1989-07-13 00:00:00 | | 0800040 | LENY SAVITRI | PADANG | 1987-12-20 00:00:00 | | 0800008 | ANDRE TORANI | PADANG | 1987-05-05 00:00:00 | | 0800008 | ANDRE TORANI | PADANG | 1987-05-05
00:00:00 | +---------+----------------+------------+---------------------+ 5 rows in set (0.00 sec)
---------------END OF TAMPILAN--------------
Contoh select mysql> select nobp, nama,tempatlahir, tanggallahir from mahasiswa; mysql> select nama from mahasiswa where tempatlahir="padang"; mysql> SELECT NOBP,NAMA,tempatlahir FROM mahasiswa where tempatlahir<>"PADANG"
mysql> select nobp, nama, tanggallahir -> from mahasiswa -> where day(tanggallahir)=5; +---------+----------------+---------------------+ | nobp | nama | tanggallahir | +---------+----------------+---------------------+ | 0800030 | FITRIA NINGSIH | 1987-09-05 00:00:00 | | 0800008 | ANDRE TORANI | 1987-05-05 00:00:00 | | 0800008 | ANDRE TORANI | 1987-05-05 00:00:00 | +---------+----------------+---------------------+ 3 rows in set (0.00 sec) mysql> select nobp, nama, tanggallahir -> from mahasiswa -> where year(tanggallahir)>1987; +---------+--------------+---------------------+ | nobp | nama | tanggallahir | +---------+--------------+---------------------+ | 0800079 | WEMPY JASENA | 1989-07-13 00:00:00 | +---------+--------------+---------------------+ 1 row in set (0.00 sec)
mysql> select nama from mahasiswa
-> where length(nama)>14; +------------------+ | nama | +------------------+ | ANDRES FERNANDES | +------------------+ 1 row in set (0.00 sec)
mysql> select nama from mahasiswa where length(nama)<13; +--------------+ | nama | +--------------+ | WEMPY JASENA | | LENY SAVITRI | | ANDRE TORANI | | ANDRE TORANI | +--------------+ 4 rows in set (0.00 sec)
ql> select nobp,nama from mahasiswa -> where left(nama,1)="A"; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | +---------+------------------+ 3 rows in set (0.00 sec)
mysql> select nobp, nama from mahasiswa -> where right(nama,1)="i"; +---------+--------------+ | nobp | nama | +---------+--------------+ | 0800040 | LENY SAVITRI | | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | +---------+--------------+ 3 rows in set (0.00 sec)
ql> select nobp, nama from mahasiswa -> where left(nama,5)="andre"; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | +---------+------------------+ 3 rows in set (0.00 sec)
ql> select nobp, nama from mahasiswa -> where nama like "%andre%"; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | | 0900000 | JOHNY ANDREAN | +---------+------------------+ 4 rows in set (0.00 sec)
mysql> select nobp, nama from mahasiswa -> where nama like "%andre%"; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | | 0900000 | JOHNY ANDREAN | +---------+------------------+ 4 rows in set (0.00 sec) mysql> mysql> select nobp, nama from mahasiswa -> where nama like "%andre%" and nama not like "%torani%"; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800009 | ANDRES FERNANDES | | 0900000 | JOHNY ANDREAN |
+---------+------------------+ 2 rows in set (0.00 sec)
ql> select nobp, nama from mahasiswa order by nobp asc; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | | 0800030 | FITRIA NINGSIH | | 0800040 | LENY SAVITRI | | 0800079 | WEMPY JASENA | | 0900000 | JOHNY ANDREAN | +---------+------------------+ 7 rows in set (0.00 sec)
mysql> select nobp, nama from mahasiswa order by nobp asc; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | | 0800030 | FITRIA NINGSIH | | 0800040 | LENY SAVITRI | | 0800079 | WEMPY JASENA | | 0900000 | JOHNY ANDREAN | +---------+------------------+ 7 rows in set (0.00 sec)
mysql> select nobp, nama from mahasiswa order by nama asc; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | | 0800009 | ANDRES FERNANDES | | 0800030 | FITRIA NINGSIH | | 0900000 | JOHNY ANDREAN | | 0800040 | LENY SAVITRI | | 0800079 | WEMPY JASENA | +---------+------------------+
7 rows in set (0.00 sec)
mysql> select nobp, nama from mahasiswa order by nama desc; +---------+------------------+ | nobp | nama | +---------+------------------+ | 0800079 | WEMPY JASENA | | 0800040 | LENY SAVITRI | | 0900000 | JOHNY ANDREAN | | 0800030 | FITRIA NINGSIH | | 0800009 | ANDRES FERNANDES | | 0800008 | ANDRE TORANI | | 0800008 | ANDRE TORANI | +---------+------------------+ 7 rows in set (0.00 sec)
mysql> select nama, tempatlahir,"a" as keterangan from mahasiswa; +------------------+-------------+------------+ | nama | tempatlahir | keterangan | +------------------+-------------+------------+ | FITRIA NINGSIH | PADANG | a | | WEMPY JASENA | BONJOL | a | | LENY SAVITRI | PADANG | a | | ANDRE TORANI | PADANG | a | | ANDRE TORANI | PADANG | a | | ANDRES FERNANDES | PAINAN | a | | JOHNY ANDREAN | NULL | a | +------------------+-------------+------------+ 7 rows in set (0.00 sec)
mysql> select nama as "nama mahasiswa" from mahasiswa; +------------------+ | nama mahasiswa | +------------------+ | FITRIA NINGSIH | | WEMPY JASENA | | LENY SAVITRI | | ANDRE TORANI | | ANDRE TORANI |
| ANDRES FERNANDES | | JOHNY ANDREAN | +------------------+
mysql> select nama,tempatlahir, -> case tempatlahir -> when "PADANG" then "Dekat" -> end as "Keterangan" -> from mahasiswa; +------------------+-------------+------------+ | nama | tempatlahir | Keterangan | +------------------+-------------+------------+ | FITRIA NINGSIH | PADANG | Dekat | | WEMPY JASENA | BONJOL | NULL | | LENY SAVITRI | PADANG | Dekat | | ANDRE TORANI | PADANG | Dekat | | ANDRE TORANI | PADANG | Dekat | | ANDRES FERNANDES | PAINAN | NULL | | JOHNY ANDREAN | NULL | NULL | +------------------+-------------+------------+ 7 rows in set (0.00 sec) i
mysql> mysql> select nama,tempatlahir, -> case tempatlahir -> when "PADANG" then "Dekat" -> else "JAUH" -> end as keterangan -> from mahasiswa; +------------------+-------------+------------+ | nama | tempatlahir | keterangan | +------------------+-------------+------------+ | FITRIA NINGSIH | PADANG | Dekat | | WEMPY JASENA | BONJOL | JAUH | | LENY SAVITRI | PADANG | Dekat | | ANDRE TORANI | PADANG | Dekat | | ANDRE TORANI | PADANG | Dekat | | ANDRES FERNANDES | PAINAN | JAUH | | JOHNY ANDREAN | NULL | JAUH | +------------------+-------------+------------+ 7 rows in set (0.00 sec) -
Related Documents
2. Membuat Table
July 2020 8
Membuat Table Saw Fence
August 2019 22
Table 2
May 2020 3
Table 2
June 2020 7
Table 2
May 2020 11