本节内容:
mysql子查询.html target=_blank class=infotextkey>mysql子查询使用EXISTS
/*
mysql> SELECT ArticleID, ArticleTitle
-> FROM Articles AS b
-> WHERE EXISTS
-> (
-> SELECT ArticleID
-> FROM AuthorArticle AS ab
-> WHERE b.ArticleID=ab.ArticleID
-> )
-> ORDER BY ArticleTitle;
+-----------+-------------------+
| ArticleID | ArticleTitle |
+-----------+-------------------+
| 19354 | AI |
| 15729 | Buy a paper |
| 16284 | Conferences |
| 12786 | How write a paper |
| 19264 | Information |
| 17695 | Journal |
| 14356 | Sell a paper |
+-----------+-------------------+
7 rows in set (0.01 sec)
*/
Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;
CREATE TABLE Articles (
ArticleID SMALLINT NOT NULL PRIMARY KEY,
ArticleTitle VARCHAR(60) NOT NULL,
Copyright YEAR NOT NULL
)
ENGINE=innodb;
INSERT INTO Articles VALUES (12786, 'How write a paper', 1934),
(13331, 'Publish a paper', 1919),
(14356, 'Sell a paper', 1966),
(15729, 'Buy a paper', 1932),
(16284, 'Conferences', 1996),
(17695, 'Journal', 1980),
(19264, 'Information', 1992),
(19354, 'AI', 1993);
CREATE TABLE Authors (
AuthID SMALLINT NOT NULL PRIMARY KEY,
AuthorFirstName VARCHAR(20),
AuthorMiddleName VARCHAR(20),
AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;
INSERT INTO Authors VALUES (1006, 'Henry', 'S.', 'Thompson'),
(1007, 'Jason', 'Carol', 'Oak'),
(1008, 'James', NULL, 'Elk'),
(1009, 'Tom', 'M', 'Ride'),
(1010, 'Jack', 'K', 'Ken'),
(1011, 'Mary', 'G.', 'Lee'),
(1012, 'Annie', NULL, 'Peng'),
(1013, 'Alan', NULL, 'Wang'),
(1014, 'Nelson', NULL, 'Yin');
CREATE TABLE AuthorArticle (
AuthID SMALLINT NOT NULL,
ArticleID SMALLINT NOT NULL,
PRIMARY KEY (AuthID, ArticleID),
FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;
INSERT INTO AuthorArticle VALUES (1006, 14356),
(1008, 15729),
(1009, 12786),
(1010, 17695),
(1011, 15729),
(1012, 19264),
(1012, 19354),
(1014, 16284);
查询实例:
您可能感兴趣的文章:
mysql子查询的实例总结
mysql多表子查询与左连接的代码
mysql子查询中EXISTS用法的例子
mysql子查询的五种方式
mysql 子查询与join性能对比
mysql子查询的用法举例
mysql update实现子查询的实例分享
mysql in 子查询效率慢的优化方法详解