返回顶部

收藏

mysql多表删除示例

更多
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered         |
+---------+--------+----------+---------------------+
|    1001 |    103 |        1 | 2004-01-12 12:30:00 |
|    1002 |    101 |        1 | 2001-02-12 12:31:00 |
|    1003 |    103 |        2 | 2002-03-12 12:34:00 |
|    1004 |    104 |        3 | 2003-04-12 12:36:00 |
|    1005 |    102 |        1 | 2004-05-12 12:41:00 |
|    1006 |    103 |        2 | 2001-06-12 12:59:00 |
|    1007 |    101 |        1 | 2002-07-12 13:01:00 |
|    1008 |    103 |        1 | 2003-08-12 13:02:00 |
|    1009 |    102 |        4 | 2004-09-12 13:22:00 |
|    1010 |    101 |        2 | 2005-11-12 13:30:00 |
|    1011 |    103 |        1 | 2006-12-12 13:32:00 |
|    1012 |    105 |        1 | 2001-02-12 13:40:00 |
|    1013 |    106 |        2 | 2002-04-12 13:44:00 |
|    1014 |    103 |        1 | 2003-06-12 14:01:00 |
|    1015 |    106 |        1 | 2005-01-12 14:05:00 |
|    1016 |    104 |        2 | 2003-11-12 14:28:00 |
|    1017 |    105 |        1 | 2002-03-12 14:31:00 |
|    1018 |    102 |        1 | 2001-05-12 14:32:00 |
|    1019 |    106 |        3 | 2003-07-12 14:49:00 |
|    1020 |    103 |        1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)

mysql> DELETE Orders.*
    -> FROM Books, Orders
    -> WHERE Books.BookID=Orders.BookID
    ->    AND Books.BookName='News';
Query OK, 3 rows affected (0.05 sec)

mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered         |
+---------+--------+----------+---------------------+
|    1001 |    103 |        1 | 2004-01-12 12:30:00 |
|    1002 |    101 |        1 | 2001-02-12 12:31:00 |
|    1003 |    103 |        2 | 2002-03-12 12:34:00 |
|    1004 |    104 |        3 | 2003-04-12 12:36:00 |
|    1006 |    103 |        2 | 2001-06-12 12:59:00 |
|    1007 |    101 |        1 | 2002-07-12 13:01:00 |
|    1008 |    103 |        1 | 2003-08-12 13:02:00 |
|    1010 |    101 |        2 | 2005-11-12 13:30:00 |
|    1011 |    103 |        1 | 2006-12-12 13:32:00 |
|    1012 |    105 |        1 | 2001-02-12 13:40:00 |
|    1013 |    106 |        2 | 2002-04-12 13:44:00 |
|    1014 |    103 |        1 | 2003-06-12 14:01:00 |
|    1015 |    106 |        1 | 2005-01-12 14:05:00 |
|    1016 |    104 |        2 | 2003-11-12 14:28:00 |
|    1017 |    105 |        1 | 2002-03-12 14:31:00 |
|    1019 |    106 |        3 | 2003-07-12 14:49:00 |
|    1020 |    103 |        1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
17 rows in set (0.00 sec)

*/

Drop table Books;
Drop table Orders;       

CREATE TABLE Books
(
   BookID SMALLINT NOT NULL PRIMARY KEY,
   BookName VARCHAR(40) NOT NULL,
   InStock SMALLINT NOT NULL
)
ENGINE=INNODB;

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;

INSERT INTO Orders VALUES (1001, 103, 1, '2004-01-12 12:30:00'),
                          (1002, 101, 1, '2001-02-12 12:31:00'),
                          (1003, 103, 2, '2002-03-12 12:34:00'),
                          (1004, 104, 3, '2003-04-12 12:36:00'),
                          (1005, 102, 1, '2004-05-12 12:41:00'),
                          (1006, 103, 2, '2001-06-12 12:59:00'),
                          (1007, 101, 1, '2002-07-12 13:01:00'),
                          (1008, 103, 1, '2003-08-12 13:02:00'),
                          (1009, 102, 4, '2004-09-12 13:22:00'),
                          (1010, 101, 2, '2005-11-12 13:30:00'),
                          (1011, 103, 1, '2006-12-12 13:32:00'),
                          (1012, 105, 1, '2001-02-12 13:40:00'),
                          (1013, 106, 2, '2002-04-12 13:44:00'),
                          (1014, 103, 1, '2003-06-12 14:01:00'),
                          (1015, 106, 1, '2005-01-12 14:05:00'),
                          (1016, 104, 2, '2003-11-12 14:28:00'),
                          (1017, 105, 1, '2002-03-12 14:31:00'),
                          (1018, 102, 1, '2001-05-12 14:32:00'),
                          (1019, 106, 3, '2003-07-12 14:49:00'),
                          (1020, 103, 1, '2004-01-12 14:51:00');

INSERT INTO Books VALUES (101, 'Writing', 12),
                         (102, 'News', 17),
                         (103, 'Angels', 23),
                         (104, 'Poet', 32),
                         (105, 'Dunces', 6),
                         (106, 'Solitude', 28);

select * from Orders;

DELETE Orders.*
FROM Books, Orders
WHERE Books.BookID=Orders.BookID
   AND Books.BookName='News';

select * from Orders;

标签:mysql,sql

收藏

0人收藏

支持

0

反对

0

相关聚客文章
  1. Salley 发表 2014-08-05 08:51:27 MySQL查询脚本分享_分类前十
  2. 博主 发表 2012-08-24 07:00:00 [转]MySQL数据库性能优化之SQL优化
  3. sulong 发表 2010-07-20 13:50:18 一次sql调优
  4. 雪姬 发表 2016-07-06 01:04:34 2016年6月数据库流行度排行榜 SQLite反超Redis
  5. aquan 发表 2012-04-06 09:51:10 批量替换wordpress文章内容
  6. Pangzi 发表 2015-06-06 16:05:58 MySQL中的那些坑
  7. 谢权 发表 2016-01-27 14:17:35 SQL入门练习(二)
  8. JeffJing 发表 2011-04-19 08:12:51 MySQL常用数据表操作SQL
  9. 小丁 发表 2017-02-18 22:39:44 关于日志级别的一些想法
  10. hanze 发表 2017-07-19 15:10:14 linux shell小脚本分享
  11. 尖兵 发表 2018-03-06 15:45:56 mysql报错ERROR 1093
  12. dormy 发表 2013-11-21 02:34:49 JNDI连接数据库

发表评论