mysql> use page
Database changed
mysql> show tables;
+----------------+
| Tables_in_page |
+----------------+
| bank |
| book |
+----------------+
2 rows in set (0.00 sec)
mysql> create table goods(id int primary key auto_increment,goods_name varchar(6
4),shop_price decimal(10,2),goods_number int)engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.31 sec)
mysql> create table `order`(goods_id int primary key auto_increment,goods_name v
archar(64),buy_number int)engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql> drop table goods;
Query OK, 0 rows affected (0.06 sec)
mysql> create table goods(goods_id int primary key auto_increment,goods_name var
char(64),shop_price decimal(10,2),goods_number int)engine=myisam default charset
=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql> desc goods;
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| goods_id | int(11) | NO | PRI | NULL | auto_increment |
| goods_name | varchar(64) | YES | | NULL | |
| shop_price | decimal(10,2) | YES | | NULL | |
| goods_number | int(11) | YES | | NULL | |
+--------------+---------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)
mysql> desc `order`;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| goods_id | int(11) | NO | PRI | NULL | auto_increment |
| goods_name | varchar(64) | YES | | NULL | |
| buy_number | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert goods values(null,'nokiaN85',2000,35),(null,'iphone4s',4500,30),(n
ull,'Lumia',5000,40),(null,'samsung',4200,20);
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 35 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> create trigger alert_goods_number after insert on `order` for each row up
date goods set goods_number = goods_number-5 where goods_id=1;
Query OK, 0 rows affected (0.34 sec)
mysql> insert into `order` values(1,'nokiaN85',5);
Query OK, 1 row affected (0.34 sec)
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 30 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> drop trigger alert_goods_number;
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger alter_goods_number after insert on `order` for each row up
date goods set goods_number = goods_number-new.buy_number where goods_id = new.g
oods_id;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into order values(4,'samsung',5);
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 'order
values(4,'samsung',5)' at line 1
mysql> insert into `order` values(4,'samsung',5);
Query OK, 1 row affected (0.05 sec)
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 30 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 15 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> create trigger back_goods_number after delete on `order` for each row upd
ate goods set goods_number = goods_number + old.buy_number where goods_id = old.
goods_id;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from order;
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 'order
' at line 1
mysql> select * from `order`;
+----------+------------+------------+
| goods_id | goods_name | buy_number |
+----------+------------+------------+
| 1 | nokiaN85 | 5 |
| 4 | samsung | 5 |
+----------+------------+------------+
2 rows in set (0.00 sec)
mysql> delete from `order` where goods_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from `order` where goods_id=4;
Query OK, 1 row affected (0.00 sec)
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 35 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> create trigger update_goods_number after update on `order` for each row u
pdate goods set goods_number = goods_number-new.buy_number+old.buy_number where
goods_id = new.goods_id;
Query OK, 0 rows affected (0.28 sec)
mysql> insert `order` values(1,'nokiaN85',5);
Query OK, 1 row affected (0.01 sec)
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 30 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> select * from `order`;
+----------+------------+------------+
| goods_id | goods_name | buy_number |
+----------+------------+------------+
| 1 | nokiaN85 | 5 |
+----------+------------+------------+
1 row in set (0.00 sec)
mysql> update `order` set buy_number=10 where goods_id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from `order`;
+----------+------------+------------+
| goods_id | goods_name | buy_number |
+----------+------------+------------+
| 1 | nokiaN85 | 10 |
+----------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 25 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
Database changed
mysql> show tables;
+----------------+
| Tables_in_page |
+----------------+
| bank |
| book |
+----------------+
2 rows in set (0.00 sec)
mysql> create table goods(id int primary key auto_increment,goods_name varchar(6
4),shop_price decimal(10,2),goods_number int)engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.31 sec)
mysql> create table `order`(goods_id int primary key auto_increment,goods_name v
archar(64),buy_number int)engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql> drop table goods;
Query OK, 0 rows affected (0.06 sec)
mysql> create table goods(goods_id int primary key auto_increment,goods_name var
char(64),shop_price decimal(10,2),goods_number int)engine=myisam default charset
=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql> desc goods;
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| goods_id | int(11) | NO | PRI | NULL | auto_increment |
| goods_name | varchar(64) | YES | | NULL | |
| shop_price | decimal(10,2) | YES | | NULL | |
| goods_number | int(11) | YES | | NULL | |
+--------------+---------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)
mysql> desc `order`;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| goods_id | int(11) | NO | PRI | NULL | auto_increment |
| goods_name | varchar(64) | YES | | NULL | |
| buy_number | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert goods values(null,'nokiaN85',2000,35),(null,'iphone4s',4500,30),(n
ull,'Lumia',5000,40),(null,'samsung',4200,20);
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 35 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> create trigger alert_goods_number after insert on `order` for each row up
date goods set goods_number = goods_number-5 where goods_id=1;
Query OK, 0 rows affected (0.34 sec)
mysql> insert into `order` values(1,'nokiaN85',5);
Query OK, 1 row affected (0.34 sec)
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 30 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> drop trigger alert_goods_number;
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger alter_goods_number after insert on `order` for each row up
date goods set goods_number = goods_number-new.buy_number where goods_id = new.g
oods_id;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into order values(4,'samsung',5);
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 'order
values(4,'samsung',5)' at line 1
mysql> insert into `order` values(4,'samsung',5);
Query OK, 1 row affected (0.05 sec)
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 30 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 15 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> create trigger back_goods_number after delete on `order` for each row upd
ate goods set goods_number = goods_number + old.buy_number where goods_id = old.
goods_id;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from order;
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 'order
' at line 1
mysql> select * from `order`;
+----------+------------+------------+
| goods_id | goods_name | buy_number |
+----------+------------+------------+
| 1 | nokiaN85 | 5 |
| 4 | samsung | 5 |
+----------+------------+------------+
2 rows in set (0.00 sec)
mysql> delete from `order` where goods_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from `order` where goods_id=4;
Query OK, 1 row affected (0.00 sec)
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 35 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> create trigger update_goods_number after update on `order` for each row u
pdate goods set goods_number = goods_number-new.buy_number+old.buy_number where
goods_id = new.goods_id;
Query OK, 0 rows affected (0.28 sec)
mysql> insert `order` values(1,'nokiaN85',5);
Query OK, 1 row affected (0.01 sec)
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 30 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)
mysql> select * from `order`;
+----------+------------+------------+
| goods_id | goods_name | buy_number |
+----------+------------+------------+
| 1 | nokiaN85 | 5 |
+----------+------------+------------+
1 row in set (0.00 sec)
mysql> update `order` set buy_number=10 where goods_id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from `order`;
+----------+------------+------------+
| goods_id | goods_name | buy_number |
+----------+------------+------------+
| 1 | nokiaN85 | 10 |
+----------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
| 1 | nokiaN85 | 2000.00 | 25 |
| 2 | iphone4s | 4500.00 | 30 |
| 3 | Lumia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+----------+------------+------------+--------------+
4 rows in set (0.00 sec)