Sub link query and special query of MySQL basic syntax (Union and limit)

Time:2021-7-25

Subqueries and special queries (Union and limit)

The syntax of sub query and special query in MySQL is summarized

Subquery

Sub queries are divided intoSubquery in where statementSubquery in from statementSubquery in select statement

Because the subquery in the select statement is rarely used, the following tests only correspond to the subquery in the where statement and the subquery in the from statement

  • Subquery in where statement

The sub query in the where statement refers to putting the data found in an SQL query statement as a condition value in where for condition judgment

Basic grammar

Select field, field, field
From table name
Where field = (SQL query statement)// The SQL query statements in brackets are sub query statements

test

Check the names and heights of students taller than Zhang San in the class

select name,height
from student
Where height > (select height from student where name = 'Zhang San');
  • Subquery in from statement

The sub query in the from statement refers to using the statement queried by a query statement as a table for other queries

Basic grammar

Select field, field, field
From (SQL query statement) // the SQL query statement in parentheses is a sub query statement, which can be used as either the main table or the table to be connected
Where condition

test

Because there is no specific example, here we simply query multiple data as another table, and then query the data

Find out the name, height and age of students whose height and age are greater than Zhang San
select s.name,s.height,s.age
from student s
Join (select height, age from student where name = 'Zhang San') ns
on s.height > ns.height and s.age > ns.age

Union query

Union query refers to merging two queries rather than connecting them, which is more efficient than connecting two tables. However, it should be noted that the number of queries to be queried in these two queries must be the same. One table can be merged or multiple tables can be merged

Basic grammar

Select field
From table
Where condition
union
Select field  //注意,在合并查询的时候,两次查询的字段数要相等
From table
Where condition;

test

Query the information of Zhang San and Li Si

select * 
from student
Where name = 'Zhang San'
union
select * 
from student
Where name = 'Li Si'
  • Limit query

Limit query means that you can specify which data to query and how many. It is often used for paging query

Basic grammar

Select field
From table
Where condition
Limit number a number B // number a indicates which data to read from, and number B indicates how many data to read

test

Query all the information of the 3rd to 6th person in the class table

select *
from student
limit 2 4

Recommended Today

A detailed explanation of the differences between Perl and strawberry Perl and ActivePerl

Perl is the abbreviation of practical extraction and report language “practical report extraction language”. Application of activestateperl and strawberry PERL on Windows platformcompiler。 Perl   The relationship between the latter two is that C language and Linux system have their own GCC. The biggest difference between activestate Perl and strawberry Perl is that strawberry Perl […]