1. Menambahkan
Data melalui Loop
mysql> load
data local infile 'D://sn3/data.txt' into table T_Buku;
mysql> select
* from T_Buku;
+----------+-------------------------------+--------------+---------------------
----+-------+
| id_buku |
nama_buku |
pengarang | penerbit
| harga |
+----------+-------------------------------+--------------+---------------------
----+-------+
| AA-001 |
Pemrograman Pascal | Alfred
Riedl | Erlangga
| 50000 |
| AA-002 | SQL
Server | Jono Ricardo
| MyBook
| 85000 |
| BB-001 | Obati Anti
Galau | Tim Galau | Gramedia
| 40000 |
Primary key
mysql> use
sn3;
Database changed
mysql> create
table t_pegawai(id_peg varchar(8) primary
key, nama_peg varchar(5
), alamat_peg
varchar(50));
Query OK, 0 rows
affected (0.08 sec)
mysql> desc
t_pegawai;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_peg | varchar(8) | NO
| PRI | NULL | |
| nama_peg | varchar(50) | YES | |
NULL | |
| alamat_peg |
varchar(50) | YES | | NULL
| |
+------------+-------------+------+-----+---------+-------+
3 rows in set
(0.00 sec)
mysql> insert
into t_pegawai values('HRD-001','candra','jakarta');
Query OK, 1 row
affected (0.05 sec)
mysql> insert
into t_pegawai values('HRD-002','cindy','bandung');
Query OK, 1 row
affected (0.01 sec)
mysql> select
* from t_pegawai;
+---------+----------+------------+
| id_peg | nama_peg | alamat_peg |
+---------+----------+------------+
| HRD-001 |
candra | jakarta |
| HRD-002 |
cindy | bandung |
+---------+----------+------------+
2 rows in set
(0.00 sec)
mysql> insert
into t_pegawai values('HRD-002','cindy','bandung');
ERROR 1062
(23000): Duplicate entry 'HRD-002' for key 'PRIMARY'
mysql> insert
into t_pegawai values('HRD-002','cindy','bantul');
ERROR 1062
(23000): Duplicate entry 'HRD-002' for key 'PRIMARY'
Table dengan UNIQUE
mysql> create
table t_parkir(id_parkir varchar(5) primary key, plat_no varchar(
2), merk
varchar(10), UNIQUE(plat_no));
Query OK, 0 rows
affected (0.08 sec)
mysql> desc
t_parkir;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id_parkir |
varchar(5) | NO | PRI | NULL |
|
| plat_no | varchar(12) | YES | UNI | NULL
| |
| merk | varchar(10) | YES | |
NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set
(0.02 sec)
mysql> insert
into t_parkir values('11101','AB-1234-OP','TOYOTA');
Query OK, 1 row
affected (0.02 sec)
mysql> insert
into t_parkir values('11102','B-3454','BMW');
Query OK, 1 row
affected (0.03 sec)
mysql> select
* from t_parkir;
+-----------+------------+--------+
| id_parkir |
plat_no | merk |
+-----------+------------+--------+
| 11101 | AB-1234-OP | TOYOTA |
| 11102 | B-3454 | BMW
|
+-----------+------------+--------+
2 rows in set
(0.00 sec)
mysql> insert
into t_parkir values('11103','AB-1234-OP','JAGUAR');
ERROR 1062
(23000): Duplicate entry 'AB-1234-OP'
for key 'plat_no'
pernyataan IF NOT EXISTS
mysql> create
table IF NOT EXISTS t_pegawai(id_peg varchar(8) primary key, nama_
peg varchar(50),
alamat_peg varchar(50));
Query OK, 0 rows
affected, 1 warning (0.00 sec)
mysql> select
* t_pegawai;
ERROR 1064
(42000): You have an error in your SQL syntax; check the manual that
corresponds to
your MySQL server version for the right syntax to use near 't_peg
awai' at line 1
mysql> select
* from t_pegawai;
+---------+----------+------------+
| id_peg | nama_peg | alamat_peg |
+---------+----------+------------+
| HRD-001 |
candra | jakarta |
| HRD-002 |
cindy | bandung |
+---------+----------+------------+
2 rows in set
(0.00 sec)
menyalin table dengan statement SELECT
mysql> create
table t_parkir_copy AS select*from t_parkir;
Query OK, 2 rows
affected (0.08 sec)
Records: 2 Duplicates: 0
Warnings: 0
mysql> select
* from t_parkir_copy;
+-----------+------------+--------+
| id_parkir |
plat_no | merk |
+-----------+------------+--------+
| 11101 | AB-1234-OP | TOYOTA |
| 11102 | B-3454 | BMW
|
+-----------+------------+--------+
2 rows in set
(0.00 sec)
Membuat temporary tabel
mysql> create
temporary table t_temporary(id int(8));
Query OK, 0 rows
affected (0.05 sec)
mysql> desc t_temporary;
+-------+--------+------+-----+---------+-------+
| Field |
Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | int(8) | YES | |
NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set
(0.03 sec)
Jika kita lihat
dengan perintah show tables maka
temporary table tidak akan muncul.
mysql> show
tables;
+---------------+
| Tables_in_sn3 |
+---------------+
| t_parkir |
| t_parkir_copy |
| t_pegawai |
+---------------+
3 rows in set
(0.00 sec)
DAN KETIKA KITA
TUTUP MYSQL KITA LALU KITA BUKA LAGI DAN KITA AKSES T_TEMPORARY GAK AKAN
KELIATAN
