Fidia Nara Shikamaru

MySQL
Cara Menguasai DDL

BAB6

mysql> CREATE TABLE jenis_barang (
-> kd_jenis CHAR(3) NOT NULL PRIMARY KEY,
-> nm_jenis VARCHAR(35) NOT NULL
-> );
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE jenis_barang (
-> kd_jenis CHAR(3) NOT NULL,
-> nm_jenis VARCHAR(35) NOT NULL,
-> PRIMARY KEY(kd_jenis)
-> );
ERROR 1050 (42S01): Table 'jenis_barang' already exists
mysql> desc jenis_barang;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| kd_jenis | char(3) | NO | PRI | NULL | |
| nm_jenis | varchar(35) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> drop table jenis_barang;
Query OK, 0 rows affected (0.03 sec)

mysql> create table jenis_barang (
-> kd_jenis int(3) not null auto_increment,
-> nm_jenis varchar(35) not null,
-> primary key(kd_jenis)
-> );
Query OK, 0 rows affected (0.09 sec)

mysql> desc jenis_barang;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| kd_jenis | int(3) | NO | PRI | NULL | auto_increment |
| nm_jenis | varchar(35) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table jenis_barang add deskripsi text
-> after nm_jenis;
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc jenis_barang;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| kd_jenis | int(3) | NO | PRI | NULL | auto_increment |
| nm_jenis | varchar(35) | NO | | NULL | |
| deskripsi | text | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table jenis_barang add
-> kd_baru char(3) first;
Query OK, 0 rows affected (0.77 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc jenis_barang;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| kd_baru | char(3) | YES | | NULL | |
| kd_jenis | int(3) | NO | PRI | NULL | auto_increment |
| nm_jenis | varchar(35) | NO | | NULL | |
| deskripsi | text | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

mysql> drop table jenis_barang;
Query OK, 0 rows affected (0.27 sec)

mysql> create table jenis_barang (
-> kd_jenis char(3) nott null,
-> nm_jenis varchar(35) not null
-> );
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 'nott
null,
nm_jenis varchar(35) not null
)' at line 2
mysql> create table jenis_barang (
-> kd_jenis CHAR(3) NOT NULL,
-> nm_jenis VARCHAR(35) NOT NULL
-> );
Query OK, 0 rows affected (0.17 sec)

mysql> desc jenis_barang;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| kd_jenis | char(3) | NO | | NULL | |
| nm_jenis | varchar(35) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table jenis_barang
-> add primari key(kd_jenis);
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 'key(k
d_jenis)' at line 2
mysql> ALTER TABLE jenis_barang
-> ADD PRIMARY KEY(kd_jenis);
Query OK, 0 rows affected (0.72 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC jenis_barang;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| kd_jenis | char(3) | NO | PRI | NULL | |
| nm_jenis | varchar(35) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE jenis_barang CHANGE nm_jenis
-> nama_jenis VARCHAR(50) NOT NULL;
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC jenis_barang;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| kd_jenis | char(3) | NO | PRI | NULL | |
| nama_jenis | varchar(50) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_bab6db |
+------------------+
| jenis_barang |
| tipe_barang |
+------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE jenis_barang RENAME TO data_jenis;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_bab6db |
+------------------+
| data_jenis |
| tipe_barang |
+------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE data_jenis DROP PRIMARY KEY;
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC data_jenis;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| kd_jenis | char(3) | NO | | NULL | |
| nama_jenis | varchar(50) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE data_jenis DROP nama_jenis;
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC data_jenis;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| kd_jenis | char(3) | NO | | NULL | |
+----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> RENAME DATABASE bab6db TO elektronikdb;
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 'DATAB
ASE bab6db TO elektronikdb' at line 1
mysql> RENAME bab6db to elektronikdb;
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 'bab6d
b to elektronikdb' at line 1
mysql> CREATE DATABASE elektronikdb;
Query OK, 1 row affected (0.00 sec)

mysql> use elektronik;
ERROR 1049 (42000): Unknown database 'elektronik'
mysql> use elektronikdb;
Database changed
mysql> CREATE TABLE jenis_barang;
ERROR 1113 (42000): A table must have at least 1 column
mysql> CREATE TABLE siswa (
-> nis CHAR (6) NOT NULL,
-> nama VARCHAR(60) NOT NULL,
-> kelamin ENUM('P','W'),
-> gdarah ENUM('A','B','AB','O'),
-> PRIMARY KEY(nis)
-> );
Query OK, 0 rows affected (0.41 sec)

mysql> desc siswa;
+---------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------------+------+-----+---------+-------+
| nis | char(6) | NO | PRI | NULL | |
| nama | varchar(60) | NO | | NULL | |
| kelamin | enum('P','W') | YES | | NULL | |
| gdarah | enum('A','B','AB','O') | YES | | NULL | |
+---------+------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE siswa2 (
-> nis CHAR (6) NOT NULL,
-> nama VARCHAR(60) NOT NUll,
-> tgl_lahir DATE,
-> PRIMARY KEY(nis)
-> );
Query OK, 0 rows affected (0.39 sec)

mysql> desc siswa2;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| nis | char(6) | NO | PRI | NULL | |
| nama | varchar(60) | NO | | NULL | |
| tgl_lahir | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> CREATE TABLE siswa3 (
-> nis CHAR (6) NOT NULL,
-> nama VARCHAR(60) NOT NULL,
-> foto BLOB,
-> PRIMARY KEY(nis)
-> );
Query OK, 0 rows affected (0.22 sec)

mysql> desc siswa3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| nis | char(6) | NO | PRI | NULL | |
| nama | varchar(60) | NO | | NULL | |
| foto | blob | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> CREATE TABLE pesan (
-> no_pesan CHAR(6) NOT NULL,
-> id_anggota CHAR(5) NOT NULL,
-> id_barang CHAR(5) NOT NULL,
-> jumlah INT(2) DEFAULT '1',
-> PRIMARY KEY (no_pesan)
-> );
Query OK, 0 rows affected (0.44 sec)

mysql> desc pesan;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| no_pesan | char(6) | NO | PRI | NULL | |
| id_anggota | char(5) | NO | | NULL | |
| id_barang | char(5) | NO | | NULL | |
| jumlah | int(2) | YES | | 1 | |
+------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE bukutamu (
-> idtamu INT(-4-) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT ,
-> nama VARCHAR(-60-) NOT NUL ,
-> email VARCHAR(-60-) NOT NULL ,
-> pesan TEXT NOT NULL,
-> PRIMARY KEY (idtamu)
-> );
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 '-4-)
UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT ,
nama VARCHAR(-60-) NOT NUL ,
em' at line 2