|
mysql>
mysql> CREATE TABLE Books(
-> BookID SMALLINT NOT NULL PRIMARY KEY,
-> BookName VARCHAR(40) NOT NULL,
-> InStock SMALLINT NOT NULL
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> CREATE TABLE Orders(
-> OrderID SMALLINT NOT NULL PRIMARY KEY,
-> BookID SMALLINT NOT NULL,
-> Quantity TINYINT (40) NOT NULL DEFAULT 1,
-> DateOrdered TIMESTAMP,
-> FOREIGN KEY (BookID) REFERENCES Books (BookID)
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> INSERT INTO Books VALUES (101, 'Java', 12),
-> (102, 'PHP', 17),
-> (103, 'MySQL', 23),
-> (104, 'Perl', 32),
-> (105, 'Pyton', 6),
-> (106, 'www.navioo.com', 28);
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO Orders VALUES (1001, 103, 1, '2001-11-12 12:30:00'),
-> (1002, 101, 1, '2002-10-16 12:31:00'),
-> (1003, 103, 2, '2003-02-11 12:34:00'),
-> (1004, 104, 3, '2004-01-19 12:36:00'),
-> (1005, 102, 1, '2005-12-17 12:41:00'),
-> (1006, 103, 2, '2006-10-18 12:59:00'),
-> (1007, 101, 1, '2004-11-21 13:01:00'),
-> (1008, 103, 1, '2014-10-16 13:02:00'),
-> (1009, 102, 4, '1994-09-02 13:22:00'),
-> (1010, 101, 2, '1995-10-04 13:30:00'),
-> (1011, 103, 1, '1996-08-12 13:32:00'),
-> (1012, 105, 1, '2004-10-03 13:40:00'),
-> (1013, 106, 2, '2002-05-12 13:44:00'),
-> (1014, 103, 1, '2001-10-01 14:01:00'),
-> (1015, 106, 1, '1997-05-05 14:05:00'),
-> (1016, 104, 2, '1998-10-07 14:28:00'),
-> (1017, 105, 1, '2004-03-12 14:31:00'),
-> (1018, 102, 1, '2004-10-21 14:32:00'),
-> (1019, 106, 3, '1991-01-30 14:49:00'),
-> (1020, 103, 1, '1990-10-12 14:51:00');
Query OK, 20 rows affected (0.05 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from Books;
+--------+----------------+---------+
| BookID | BookName | InStock |
+--------+----------------+---------+
| 101 | Java | 12 |
| 102 | PHP | 17 |
| 103 | MySQL | 23 |
| 104 | Perl | 32 |
| 105 | Pyton | 6 |
| 106 | www.navioo.com | 28 |
+--------+----------------+---------+
6 rows in set (0.00 sec)
mysql>
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2001-11-12 12:30:00 |
| 1002 | 101 | 1 | 2002-10-16 12:31:00 |
| 1003 | 103 | 2 | 2003-02-11 12:34:00 |
| 1004 | 104 | 3 | 2004-01-19 12:36:00 |
| 1005 | 102 | 1 | 2005-12-17 12:41:00 |
| 1006 | 103 | 2 | 2006-10-18 12:59:00 |
| 1007 | 101 | 1 | 2004-11-21 13:01:00 |
| 1008 | 103 | 1 | 2014-10-16 13:02:00 |
| 1009 | 102 | 4 | 1994-09-02 13:22:00 |
| 1010 | 101 | 2 | 1995-10-04 13:30:00 |
| 1011 | 103 | 1 | 1996-08-12 13:32:00 |
| 1012 | 105 | 1 | 2004-10-03 13:40:00 |
| 1013 | 106 | 2 | 2002-05-12 13:44:00 |
| 1014 | 103 | 1 | 2001-10-01 14:01:00 |
| 1015 | 106 | 1 | 1997-05-05 14:05:00 |
| 1016 | 104 | 2 | 1998-10-07 14:28:00 |
| 1017 | 105 | 1 | 2004-03-12 14:31:00 |
| 1018 | 102 | 1 | 2004-10-21 14:32:00 |
| 1019 | 106 | 3 | 1991-01-30 14:49:00 |
| 1020 | 103 | 1 | 1990-10-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)
mysql>
mysql>
mysql> UPDATE Books, Orders
-> SET Orders.Quantity=Orders.Quantity+2,
-> Books.InStock=Books.InStock-2
-> WHERE Books.BookID=Orders.BookID
-> AND Orders.OrderID = 1002;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql>
mysql> select * from Books;
+--------+----------------+---------+
| BookID | BookName | InStock |
+--------+----------------+---------+
| 101 | Java | 10 |
| 102 | PHP | 17 |
| 103 | MySQL | 23 |
| 104 | Perl | 32 |
| 105 | Pyton | 6 |
| 106 | www.navioo.com | 28 |
+--------+----------------+---------+
6 rows in set (0.00 sec)
mysql>
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2001-11-12 12:30:00 |
| 1002 | 101 | 3 | 2007-07-23 19:09:39 |
| 1003 | 103 | 2 | 2003-02-11 12:34:00 |
| 1004 | 104 | 3 | 2004-01-19 12:36:00 |
| 1005 | 102 | 1 | 2005-12-17 12:41:00 |
| 1006 | 103 | 2 | 2006-10-18 12:59:00 |
| 1007 | 101 | 1 | 2004-11-21 13:01:00 |
| 1008 | 103 | 1 | 2014-10-16 13:02:00 |
| 1009 | 102 | 4 | 1994-09-02 13:22:00 |
| 1010 | 101 | 2 | 1995-10-04 13:30:00 |
| 1011 | 103 | 1 | 1996-08-12 13:32:00 |
| 1012 | 105 | 1 | 2004-10-03 13:40:00 |
| 1013 | 106 | 2 | 2002-05-12 13:44:00 |
| 1014 | 103 | 1 | 2001-10-01 14:01:00 |
| 1015 | 106 | 1 | 1997-05-05 14:05:00 |
| 1016 | 104 | 2 | 1998-10-07 14:28:00 |
| 1017 | 105 | 1 | 2004-03-12 14:31:00 |
| 1018 | 102 | 1 | 2004-10-21 14:32:00 |
| 1019 | 106 | 3 | 1991-01-30 14:49:00 |
| 1020 | 103 | 1 | 1990-10-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.02 sec)
mysql>
mysql>
mysql>
mysql> drop table Orders;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> drop table Books;
Query OK, 0 rows affected (0.05 sec)
mysql>
|
