[Knowledge Base]-Advanced Data Query of Database_MySQL: Reduplication, Combination Query, Connection Query, Virtual Table

Brief Book Author: seay

Origin of the article: Advanced Data Query of Relational Database SQL: Reduplication, Composite Query, Connection Query, Virtual Table

Recalling: [Knowledge Base]-Basic Data Query of Database_MySQL: Subquery, Group Query, Fuzzy Query

 

Learn [has been tested and verified]

1. Deduplication (DISTINCT)

Composite queries

UNION (Consolidation)

Connection queries

Virtual tables

 

 

I. Deduplication (DISTINCT)

DISTINCT: Used to return unique, different values, mainly for a field

Grammar

SELECT DISTINCT < Column Name > * FROM < Table Name >

Examples

-- Query all class names
SELECT DISTINCT Class FROM Students

 

  

 

 

2. Combination Query

SQL composite queries can be operated with the following keywords:

  • UNION (union): Compare the results of two queries and return all non-repetitive rows of two sets.
  • INTERSECT (Intersection): Compare the results of two queries and return non-repetitive rows that are input and output from both left and right sides of the query.
  • EXCEPT (difference set): Compare the results of two queries and return non-repetitive rows that do not contain the intersection of left and right sets in the left query set.
  • Be careful:
    Basic rules for combining the result sets of two queries using EXCEPT or INTERSECT:
    The number and order of columns in all queries must be the same; data types must be compatible.

Grammar
[SQL Query expression 1]
  UNION | INTERSECT | EXCEPT
[SQL Query expression 2];

Basic data

-- Create data tables T1
CREATE TABLE T1(
    A int NULL,
    B int NULL,
    C int NULL
);
-- Create data tables T2
CREATE TABLE T2(
    A int NULL,
    B int NULL,
    C int NULL
);
-- Insert basic data
INSERT INTO T1 VALUES(1,2,3);
INSERT INTO T1 VALUES(2,3,4);
INSERT INTO T1 VALUES(3,4,5);
INSERT INTO T1 VALUES(4,5,6);
INSERT INTO T1 VALUES(5,6,7);
INSERT INTO T2 VALUES(3,4,5);
INSERT INTO T2 VALUES(5,6,7);
INSERT INTO T2 VALUES(6,7,8);
INSERT INTO T2 VALUES(7,8,9);

 

 

UNION (Consolidation)

Example 1 (to repeat)

--query T1 and T2 All data, to repeat
SELECT * FROM T1 UNION SELECT * FROM T2;

 

  

  

Example 2 (no repetition)

--query T1 and T2 All data, not duplicated, in UNION After the keyword, add ALL
SELECT * FROM T1 UNION ALL SELECT * FROM T2;

 

  

 

INTERSECT and EXCEPT are not implemented in MySQL

 

 

IV. Connection Query

The commonly used join queries are as follows:
INNER JOIN: Returns rows that all connected objects satisfy conditions.
Left Connection (LEFT JOIN): Returns all data on the left, even if there is no data on the right (no corresponding data is displayed as NULL).
RIGHT JOIN: Returns all data on the right, even if there is no data on the left (no corresponding data is displayed as NULL).
Full Connection (FULL JOIN): Returns left-right crossover data.
Tip: INNER JOIN is the same as JOIN.

Grammar

SELECT field list FROM < Datasheet | View > INNER JOIN < Datasheet | View > ON conditional expression;

Data

In the last article [Knowledge Base]-Basic Data Query of Database_MySQL: Subquery, Group Query, Fuzzy Query On the basis of basic data, add some more data.

INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,Address) 
VALUES(2016010,'Xiaoxia','Class One','male',19,'18817716689','Shenyang');
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,Address) 
VALUES(2016011,'Ni Ni','Class Two','female',20,'18817716698','Beijing');

INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016012,1,90,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016012,2,86,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016012,3,79,1);

  

Internal Connection (INNER JOIN)

--Query the transcript and display the course name
SELECT S.Id,S.SId,C.Name,S.Grades,S.IsPassed
 FROM Scores S INNER JOIN Courses C ON S.CId=C.Id;

 

  

 

Left Connection (LEFT JOIN)

--Query the student information transcript
SELECT * FROM Students ST LEFT JOIN Scores S ON ST.Id=S.SId;

  

  

 

 

Right Connection (RIGHT JOIN)

--Query the student information transcript
SELECT * FROM Students ST RIGHT JOIN Scores S ON ST.Id=S.SId;

 

  

 

MySQL does not support Full Connection (FULL JOIN)

 

V. Virtual Table

The SQL virtual table is a result set returned by SELECT query statement.
When virtual tables are constructed, they can be queried directly as real data tables, which are often used in practice.

Grammar

SELECT Field List 1 FROM
  (SELECT Field List 2 FROM < Table Name > [WHERE clause] < Virtual Table Name > 
[WHERE clause]

Examples

SELECT * FROM ( SELECT S.Id,S.Name CName,S.Grades,S.IsPassed,S.SId,ST.Name,ST.Class 
FROM Students ST LEFT JOIN (SELECT Scores.*,C.Name FROM
 Scores LEFT JOIN Courses C ON Scores.CId=C.Id) S ON ST.Id=S.SId ) T

 

  

[SELECT Scores. *, C. Name FROM Scores LEFT JOIN Courses C. ON Scores. CId = C. Id C and last T are virtual table names. ]

Tags: PHP SQL MySQL Database

Posted on Sun, 21 Jul 2019 00:54:37 -0700 by Chris_Evans