0 ALTER TABLE PADA DATABASE MOTOR; CMD

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\pcs31>cd\xampp\mysql\bin

C:\xampp\mysql\bin>dir/w
 Volume in drive C has no label.
 Volume Serial Number is 3837-CD8F

 Directory of C:\xampp\mysql\bin

[.]                     [..]                    bug25714.exe
comp_err.exe            echo.exe                libmysql.dll
my.ini                  myisamchk.exe           myisamlog.exe
myisampack.exe          myisam_ftdump.exe       mysql.exe
mysqladmin.exe          mysqlbinlog.exe         mysqlcheck.exe
mysqld.exe              mysqldump.exe           mysqlimport.exe
mysqlmanager.exe        mysqlshow.exe           mysqlslap.exe
mysqltest.exe           mysql_client_test.exe   mysql_config.bat
mysql_config.pl         mysql_upgrade.exe       my_print_defaults.exe
perror.exe              replace.exe             resolveip.exe
              28 File(s)     42.888.587 bytes
               2 Dir(s)  55.417.294.848 bytes free

C:\xampp\mysql\bin>mysql -h localhost -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.41 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| coba               |
| mysql              |
| phpmyadmin         |
| test               |
+--------------------+
6 rows in set (0.00 sec)


mysql> create database motor;
Query OK, 1 row affected (0.00 sec)

mysql> use motor;
Database changed
mysql> create table vendor (id_vendor int not null auto_increment, id_type int not null, name varchar(100), primary key(id_vendor))engine=innodb;
Query OK, 0 rows affected (0.21 sec)

mysql> desc vendor
    -> ;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id_vendor | int(11)      | NO   | PRI | NULL    | auto_increment |
| id_type   | int(11)      | NO   |     | NULL    |                |
| name      | varchar(100) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> create table type (id_type int not null auto_increment, type varchar(100), warna varchar(100), primary key(id_type))engine=innodb;
Query OK, 0 rows affected (0.14 sec)

mysql> desc type;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id_type | int(11)      | NO   | PRI | NULL    | auto_increment |
| type    | varchar(100) | YES  |     | NULL    |                |
| warna   | varchar(100) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table vendor add constraint FK_motor foreign key (id_type) references type (id_type) on delete cascade on update cascade;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into type values(1,'blade','merah');
Query OK, 1 row affected (0.09 sec)s

mysql> insert into type (type,warna) values ('beat','biru');
Query OK, 1 row affected (0.09 sec)

mysql> insert into vendor (id_type,name) values (1,'yamaha'),(2,'honda');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select vendor.id_type, vendor.name, type.type, type.warna from vendor, type where vendor.id_type=type.id_type;
+---------+--------+------+-------+
| id_type | name   | type | warna |
+---------+--------+------+-------+
|       1 | yamaha | mio  | merah |
|       2 | honda  | beat | biru  |
+---------+--------+------+-------+
2 rows in set (0.04 sec)

mysql> insert into type (type, warna) values ('scoopy','hijau'),('astrea','hitam')
    -> ;
Query OK, 2 rows affected (0.31 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select*from type;
+---------+--------+-------+
| id_type | type   | warna |
+---------+--------+-------+
|       1 | mio    | merah |
|       2 | beat   | biru  |
|       3 | scoopy | hijau |
|       4 | astrea | hitam |
+---------+--------+-------+
4 rows in set (0.00 sec)

mysql> select *from vendor;
+-----------+---------+--------+
| id_vendor | id_type | name   |
+-----------+---------+--------+
|         1 |       1 | yamaha |
|         2 |       2 | honda  |
+-----------+---------+--------+
2 rows in set (0.00 sec)

mysql> select vendor.id_type, vendor.name, type.type, type.warna from vendor, type where vendor.id_type=type.id_type;
+---------+--------+------+-------+
| id_type | name   | type | warna |
+---------+--------+------+-------+
|       1 | yamaha | mio  | merah |
|       2 | honda  | beat | biru  |
+---------+--------+------+-------+
2 rows in set (0.00 sec)

mysql> insert into vendor (id_type,name) values (2,'honda'),(2,'honda');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select vendor.id_type, vendor.name, type.type, type.warna from vendor, type where vendor.id_type=type.id_type;
+---------+--------+------+-------+
| id_type | name   | type | warna |
+---------+--------+------+-------+
|       1 | yamaha | mio  | merah |
|       2 | honda  | beat | biru  |
|       2 | honda  | beat | biru  |
|       2 | honda  | beat | biru  |
+---------+--------+------+-------+
4 rows in set (0.00 sec)

mysql> select*from type;
+---------+--------+-------+
| id_type | type   | warna |
+---------+--------+-------+
|       1 | mio    | merah |
|       2 | beat   | biru  |
|       3 | scoopy | hijau |
|       4 | astrea | hitam |
+---------+--------+-------+
4 rows in set (0.00 sec)

mysql> select*from vendor;
+-----------+---------+--------+
| id_vendor | id_type | name   |
+-----------+---------+--------+
|         1 |       1 | yamaha |
|         2 |       2 | honda  |
|         3 |       2 | honda  |
|         4 |       2 | honda  |
+-----------+---------+--------+
4 rows in set (0.00 sec)

mysql> delete from vendor where id_vendor = 3 and 4;
Query OK, 1 row affected (0.05 sec)

mysql> select*from vendor;
+-----------+---------+--------+
| id_vendor | id_type | name   |
+-----------+---------+--------+
|         1 |       1 | yamaha |
|         2 |       2 | honda  |
|         4 |       2 | honda  |
+-----------+---------+--------+
3 rows in set (0.00 sec)

mysql> delete from vendor where id_vendor = 4;
Query OK, 1 row affected (0.06 sec)

mysql> select*from vendor;
+-----------+---------+--------+
| id_vendor | id_type | name   |
+-----------+---------+--------+
|         1 |       1 | yamaha |
|         2 |       2 | honda  |
+-----------+---------+--------+
2 rows in set (0.00 sec)

mysql> insert into vendor (id_type, name) values (3,'honda'), (4,'honda');
Query OK, 2 rows affected (0.28 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select*from vendor;
+-----------+---------+--------+
| id_vendor | id_type | name   |
+-----------+---------+--------+
|         1 |       1 | yamaha |
|         2 |       2 | honda  |
|         5 |       3 | honda  |
|         6 |       4 | honda  |
+-----------+---------+--------+
4 rows in set (0.00 sec)

mysql> select vendor.id_type, vendor.name, type.type, type.warna from vendor, type where vendor.id_type=type.id_type;
+---------+--------+--------+-------+
| id_type | name   | type   | warna |
+---------+--------+--------+-------+
|       1 | yamaha | mio    | merah |
|       2 | honda  | beat   | biru  |
|       3 | honda  | scoopy | hijau |
|       4 | honda  | astrea | hitam |
+---------+--------+--------+-------+
4 rows in set (0.00 sec)

mysql> Bye
Ctrl-C -- exit!