SQL/MySQL/Select Clause/Sub query — различия между версиями

Текущая версия на 10:16, 26 мая 2010

Содержание

1 Delete command with sub query
2 Sub queries
3 Subqueries As Calculated Columns: Simple Subqueries
4 Subqueries in the WHERE Clause 2
5 Subqueries That Return Multiple Results
6 Sub query with ALL command
7 Sub query with ANY command
8 Sub query with calculation
9 Sub query with EXISTS command
10 Sub query with IN command
11 Sub query with not equal and order
12 Sub query with NOT EXISTS command
13 Sub query with NOT IN command
14 Sub query with string concatenate
15 Update command with sub query

Delete command with sub query

Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;
Drop table ArticleOrders;

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);
CREATE TABLE ArticleOrders
(
   OrderID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL,
   Quantity SMALLINT NOT NULL,
   PRIMARY KEY (OrderID, ArticleID),
   FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;

INSERT INTO ArticleOrders VALUES (101, 13331, 1), 
                                 (101, 12786, 1), 
                                 (101, 16284, 2), 
                                 (102, 19354, 1),
                                 (102, 15729, 3), 
                                 (103, 12786, 2), 
                                 (103, 19264, 1), 
                                 (103, 13331, 1),
                                 (103, 14356, 2), 
                                 (104, 19354, 1), 
                                 (105, 15729, 1), 
                                 (105, 14356, 2),
                                 (106, 16284, 2), 
                                 (106, 13331, 1), 
                                 (107, 12786, 3), 
                                 (108, 19354, 1),
                                 (108, 16284, 4), 
                                 (109, 15729, 1), 
                                 (110, 13331, 2), 
                                 (110, 12786, 2),
                                 (110, 14356, 2), 
                                 (111, 14356, 2);
  
DELETE ab, b
FROM AuthorArticle AS ab, Articles AS b
WHERE ab.ArticleID=b.ArticleID
   AND ab.AuthID=(SELECT AuthID FROM Authors WHERE AuthorLastName="Yin");

Sub queries

/*
mysql> SELECT CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName)
AS Author
    -> FROM Authors
    -> WHERE AuthID=
    ->    (
    ->       SELECT ab.AuthID
    ->       FROM AuthorArticle AS ab, Articles AS b
    ->       WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI"
    ->    );
+-------------+
| Author      |
+-------------+
| Annie Watts |
+-------------+
1 row in set (0.02 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);
  
SELECT CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author
FROM Authors
WHERE AuthID=
   (
      SELECT ab.AuthID
      FROM AuthorArticle AS ab, Articles AS b
      WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI"
   );

Subqueries As Calculated Columns: Simple Subqueries

/*
mysql> SELECT StudentID, Name,
    ->    (SELECT COUNT(*) FROM StudentExam
    ->     WHERE StudentExam.StudentID = Student.StudentID)
    ->     AS ExamsTaken
    -> FROM Student
    -> ORDER BY ExamsTaken DESC;
+-----------+-------------+------------+
| StudentID | Name        | ExamsTaken |
+-----------+-------------+------------+
|         1 | Joe Wang     |          2 |
|         2 | Cory But    |          1 |
|         3 | JJ Harvests |          0 |
+-----------+-------------+------------+
3 rows in set (0.00 sec)

*/
/* Create Student and StudentExam TABLE  */
Drop TABLE Student;
Drop TABLE StudentExam;
CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name      VARCHAR(50) NOT NULL
)TYPE = InnoDB;
CREATE TABLE StudentExam (
   StudentID  INT NOT NULL,
   Mark       INT,
   Comments   VARCHAR(255),
   CONSTRAINT FK_Student FOREIGN KEY (StudentID)
              REFERENCES Student(StudentID)
)TYPE = InnoDB;

/* Insert Data*/
INSERT INTO Student (StudentID,Name) VALUES (1,"John Jones");
INSERT INTO Student (StudentID,Name) VALUES (2,"Gary Burton");
INSERT INTO Student (StudentID,Name) VALUES (3,"Emily Scarlett");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (1,55,"Java");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (1,73,"C#");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (2,44,"JavaScript");

/* Real command */
SELECT StudentID, Name,
   (SELECT COUNT(*) FROM StudentExam
    WHERE StudentExam.StudentID = Student.StudentID)
    AS ExamsTaken
FROM Student
ORDER BY ExamsTaken DESC;

Subqueries in the WHERE Clause 2

/*
mysql> select * from Course;
+----------+----------------------+---------+
| CourseID | Name                 | Credits |
+----------+----------------------+---------+
|        1 | Mediaeval Romanian   |       5 |
|        2 | Philosophy           |       5 |
|        3 | History of Computing |       5 |
+----------+----------------------+---------+
3 rows in set (0.01 sec)
mysql> SELECT Name FROM Course
    -> WHERE CourseID =
    -> (
    -> SELECT CourseID from EXAM
    -> WHERE SustainedOn="10-MAR-03"
    -> );
Empty set, 1 warning (0.00 sec)

*/
Drop TABLE Course;
Drop TABLE Exam;       
       
CREATE TABLE Course (
   CourseID INT NOT NULL PRIMARY KEY,
   Name     VARCHAR(50),
   Credits  INT)
TYPE = InnoDB;
CREATE TABLE Exam (
   ExamID      INT NOT NULL PRIMARY KEY,
   CourseID    INT NOT NULL,
   SustainedOn DATE,
   Comments    VARCHAR(255),
   INDEX       examcourse_index(CourseID)
   
 
)TYPE = InnoDB;

INSERT INTO Course (CourseID,Name,Credits) VALUES (1,"Mediaeval Romanian",5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (2,"Philosophy",5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (3,"History of Computing",5);
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES 
      (1,1,"2003-03-12","JavaScript");
      
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES 
      (2,1,"2003-03-13","Java");
      
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES 
      (3,2,"2003-03-11","Python");
      
INSERT INTO Exam (ExamID,CourseID,SustainedOn) VALUES 
      (4,3,"2003-03-18","Swing");
select * from Course;
SELECT Name FROM Course
WHERE CourseID =
(
SELECT CourseID from EXAM
WHERE SustainedOn="10-MAR-03"
);

Subqueries That Return Multiple Results

/*
mysql> select * from Course;
+----------+----------------------+---------+
| CourseID | Name                 | Credits |
+----------+----------------------+---------+
|        1 | Mediaeval Romanian   |       5 |
|        2 | Philosophy           |       5 |
|        3 | History of Computing |       5 |
+----------+----------------------+---------+
3 rows in set (0.00 sec)
mysql> SELECT Name FROM Course
    -> WHERE CourseID IN
    -> (
    -> SELECT CourseID from EXAM
    -> WHERE SustainedOn="26-MAR-03"
    -> );
Empty set, 1 warning (0.00 sec)

*/
Drop TABLE Course;
Drop TABLE Exam;       
       
CREATE TABLE Course (
   CourseID INT NOT NULL PRIMARY KEY,
   Name     VARCHAR(50),
   Credits  INT)
TYPE = InnoDB;
CREATE TABLE Exam (
   ExamID      INT NOT NULL PRIMARY KEY,
   CourseID    INT NOT NULL,
   SustainedOn DATE,
   Comments    VARCHAR(255),
   INDEX       examcourse_index(CourseID)
   
 
)TYPE = InnoDB;

INSERT INTO Course (CourseID,Name,Credits) VALUES (1,"Mediaeval Romanian",5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (2,"Philosophy",5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (3,"History of Computing",5);
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES 
      (1,1,"2003-03-12","JavaScript");
      
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES 
      (2,1,"2003-03-13","Java");
      
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES 
      (3,2,"2003-03-11","Python");
      
INSERT INTO Exam (ExamID,CourseID,SustainedOn) VALUES 
      (4,3,"2003-03-18","Swing");

select * from Course;
  
SELECT Name FROM Course
WHERE CourseID IN
(
SELECT CourseID from EXAM
WHERE SustainedOn="26-MAR-03"
);

Sub query with ALL command

/*
mysql> SELECT ArticleTitle, Copyright
    -> FROM Articles
    -> WHERE Copyright > ALL
    ->    (
    ->       SELECT b.copyright
    ->       FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
    ->          JOIN Authors AS a USING (AuthID)
    ->       WHERE AuthorLastName="Yin"
    ->    )
    -> ORDER BY ArticleTitle;
Empty set (0.02 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);
  
SELECT ArticleTitle, Copyright
FROM Articles
WHERE Copyright > ALL
   (
      SELECT b.copyright
      FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
         JOIN Authors AS a USING (AuthID)
      WHERE AuthorLastName="Yin"
   )
ORDER BY ArticleTitle;

Sub query with ANY command

/*
mysql> SELECT ArticleTitle, Copyright
    -> FROM Articles
    -> WHERE Copyright > ANY
    ->    (
    ->       SELECT b.copyright
    ->       FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
    ->          JOIN Authors AS a USING (AuthID)
    ->       WHERE AuthorLastName="Yin"
    ->    )
    -> ORDER BY ArticleTitle;
Empty set (0.02 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);
  
SELECT ArticleTitle, Copyright
FROM Articles
WHERE Copyright > ANY
   (
      SELECT b.copyright
      FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
         JOIN Authors AS a USING (AuthID)
      WHERE AuthorLastName="Yin"
   )
ORDER BY ArticleTitle;

Sub query with calculation

/*
mysql> SELECT ArticleTitle, Copyright
    -> FROM Articles
    -> WHERE Copyright<(SELECT MAX(Copyright)-50 FROM Articles)
    -> ORDER BY ArticleTitle;
+-------------------+-----------+
| ArticleTitle      | Copyright |
+-------------------+-----------+
| Buy a paper       |      1932 |
| How write a paper |      1934 |
| Publish a paper   |      1919 |
+-------------------+-----------+
3 rows in set (0.05 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);
  
SELECT ArticleTitle, Copyright
FROM Articles
WHERE Copyright<(SELECT MAX(Copyright)-50 FROM Articles)
ORDER BY ArticleTitle;

Sub query with EXISTS command

/*
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);
  
SELECT ArticleID, ArticleTitle
FROM Articles AS b
WHERE EXISTS
   (
      SELECT ArticleID
      FROM AuthorArticle AS ab
      WHERE b.ArticleID=ab.ArticleID
   )
ORDER BY ArticleTitle;

Sub query with IN command

/*
mysql> SELECT ArticleTitle, Copyright
    -> FROM Articles
    -> WHERE Copyright IN
    ->    (
    ->       SELECT b.copyright
    ->       FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
    ->          JOIN Authors AS a USING (AuthID)
    ->       WHERE AuthorLastName="Yin"
    ->    )
    -> ORDER BY ArticleTitle;
+--------------+-----------+
| ArticleTitle | Copyright |
+--------------+-----------+
| Conferences  |      1996 |
+--------------+-----------+
1 row in set (0.00 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);
  
SELECT ArticleTitle, Copyright
FROM Articles
WHERE Copyright IN
   (
      SELECT b.copyright
      FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
         JOIN Authors AS a USING (AuthID)
      WHERE AuthorLastName="Yin"
   )
ORDER BY ArticleTitle;

Sub query with not equal and order

/*
mysql> SELECT DISTINCT CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorL
astName) AS Author
    -> FROM Authors
    -> WHERE AuthID <>
    ->    (
    ->       SELECT ab.AuthID
    ->       FROM AuthorArticle AS ab, Articles AS b
    ->       WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI"
    ->    )
    -> ORDER BY AuthorLastName;
+-------------------+
| Author            |
+-------------------+
| James Elk         |
| Jack K Ken        |
| Mary G. Lee       |
| Jason Carol Oak   |
| Tom M Ride        |
| Henry S. Thompson |
| Alan Wang         |
| Nelson Yin        |
+-------------------+
8 rows in set (0.16 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);
  
SELECT DISTINCT CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author
FROM Authors
WHERE AuthID <>
   (
      SELECT ab.AuthID
      FROM AuthorArticle AS ab, Articles AS b
      WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI"
   )
ORDER BY AuthorLastName;

Sub query with NOT EXISTS command

/*
mysql> SELECT ArticleID, ArticleTitle
    -> FROM Articles AS b
    -> WHERE NOT EXISTS
    ->    (
    ->       SELECT ArticleID
    ->       FROM AuthorArticle AS ab
    ->       WHERE b.ArticleID=ab.ArticleID
    ->    )
    -> ORDER BY ArticleTitle;
+-----------+-----------------+
| ArticleID | ArticleTitle    |
+-----------+-----------------+
|     13331 | Publish a paper |
+-----------+-----------------+
1 row in set (0.00 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);
  
SELECT ArticleID, ArticleTitle
FROM Articles AS b
WHERE NOT EXISTS
   (
      SELECT ArticleID
      FROM AuthorArticle AS ab
      WHERE b.ArticleID=ab.ArticleID
   )
ORDER BY ArticleTitle;

Sub query with NOT IN command

/*
mysql> SELECT ArticleTitle, Copyright
    -> FROM Articles
    -> WHERE Copyright NOT IN
    ->    (
    ->       SELECT b.copyright
    ->       FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
    ->          JOIN Authors AS a USING (AuthID)
    ->       WHERE AuthorLastName="Yin"
    ->    )
    -> ORDER BY ArticleTitle;
+-------------------+-----------+
| ArticleTitle      | Copyright |
+-------------------+-----------+
| AI                |      1993 |
| Buy a paper       |      1932 |
| How write a paper |      1934 |
| Information       |      1992 |
| Journal           |      1980 |
| Publish a paper   |      1919 |
| Sell a paper      |      1966 |
+-------------------+-----------+
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);
  
SELECT ArticleTitle, Copyright
FROM Articles
WHERE Copyright NOT IN
   (
      SELECT b.copyright
      FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
         JOIN Authors AS a USING (AuthID)
      WHERE AuthorLastName="Yin"
   )
ORDER BY ArticleTitle;

Sub query with string concatenate

/*
mysql> SELECT CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName)
AS Author
    -> FROM Authors
    -> WHERE AuthID=
    ->    (
    ->       SELECT ab.AuthID
    ->       FROM AuthorArticle AS ab, Articles AS b
    ->       WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI"
    ->    );
+-------------+
| Author      |
+-------------+
| Annie Watts |
+-------------+
1 row 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);
  
SELECT CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author
FROM Authors
WHERE AuthID=
   (
      SELECT ab.AuthID
      FROM AuthorArticle AS ab, Articles AS b
      WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI"
   );

Update command with sub query

/*
mysql> UPDATE Articles
    -> SET ArticleTitle="The Way of Zen", Copyright=1957
    -> WHERE ArticleID=
    ->    (
    ->       SELECT ab.ArticleID
    ->       FROM Authors AS a, AuthorArticle AS ab
    ->       WHERE a.AuthID=ab.AuthID AND a.AuthorLastName="Yin"
    ->    );
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

*/
Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;
Drop table ArticleOrders;

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);
CREATE TABLE ArticleOrders
(
   OrderID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL,
   Quantity SMALLINT NOT NULL,
   PRIMARY KEY (OrderID, ArticleID),
   FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;

INSERT INTO ArticleOrders VALUES (101, 13331, 1), 
                                 (101, 12786, 1), 
                                 (101, 16284, 2), 
                                 (102, 19354, 1),
                                 (102, 15729, 3), 
                                 (103, 12786, 2), 
                                 (103, 19264, 1), 
                                 (103, 13331, 1),
                                 (103, 14356, 2), 
                                 (104, 19354, 1), 
                                 (105, 15729, 1), 
                                 (105, 14356, 2),
                                 (106, 16284, 2), 
                                 (106, 13331, 1), 
                                 (107, 12786, 3), 
                                 (108, 19354, 1),
                                 (108, 16284, 4), 
                                 (109, 15729, 1), 
                                 (110, 13331, 2), 
                                 (110, 12786, 2),
                                 (110, 14356, 2), 
                                 (111, 14356, 2);
  
UPDATE Articles
SET ArticleTitle="The Way of Zen", Copyright=1957
WHERE ArticleID=
   (
      SELECT ab.ArticleID
      FROM Authors AS a, AuthorArticle AS ab
      WHERE a.AuthID=ab.AuthID AND a.AuthorLastName="Yin"
   );

SQL/MySQL/Select Clause/Sub query — различия между версиями

Текущая версия на 10:16, 26 мая 2010

Содержание

Delete command with sub query

Sub queries

Subqueries As Calculated Columns: Simple Subqueries

Subqueries in the WHERE Clause 2

Subqueries That Return Multiple Results

Sub query with ALL command

Sub query with ANY command

Sub query with calculation

Sub query with EXISTS command

Sub query with IN command

Sub query with not equal and order

Sub query with NOT EXISTS command

Sub query with NOT IN command

Sub query with string concatenate

Update command with sub query

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты

Версия 13:46, 26 мая 2010 (просмотреть исходный код) (обсуждение) ← Предыдущая правка	Текущая версия на 10:16, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)