MySQL basic SQL query statement: Multiple table Query and subquery sample

First, simple query:
Basic Syntax:

The code is as follows	Copy Code
SELECT * from Tb_name;

Query all

The code is as follows	Copy Code
SELECT field1,field2 from Tb_name;

Projection

The code is as follows	Copy Code
SELECT [DISTINCT] * from Tb_name WHERE qualification;

Choose
Description

FROM clause: Relational table, multiple tables, other SELECT statements to query

WHERE clause: A Boolean relationship expression that contains mainly the following types of expressions:

Comparison: =, >, >=, <=, <
Logical Relationship:
and
OR
Not

BETWEEN ...   And ... : Between two values
Like ""
%: Any length of any character
_: Any single character
REGEXP, Rlike: Regular expression, invalid index at this time
In
Is NULL
is not NULL

Check out the wp-links and wp_posts tables for this blog as follows:

The code is as follows

Copy Code

Mysql> select * from Wp_links; Query all mysql> select Link_name,link_url from Wp_links; Projection +-------------------+--------------------------------------+ | Link_name | Link_url | +-------------------+--------------------------------------+ | Do you Tencent Micro Blog | http://www.111cn.net | | Do you sina Micro Blog | HTTP://WEIBO.COM/GZ100WW | | 51CTO Technology Blog | http://www.111cn.net/| +-------------------+--------------------------------------+ Rows in Set (0.00 sec) Mysql> Select Id,post_title,post_date from wp_posts where id>1290 and post_status= ' publish '; +------+----------------------------------------------------------------+----------------------+ | ID | Post_title | Post_date | +------+----------------------------------------------------------------+----------------------+ | 1291 | "Turn" HP 3PAR Storage Concept III | 2013-08-29 17:21:27 | | 1298 | "Turn" HP 3PAR storage Concept Four | 2013-08-29 17:22:33 | | 1351 | "Go" XenDesktop 5.5+vsphere 5 Create a virtual machine error | 2013-09-04 17:41:26 | | 1357 | Linux under the powerful Network Tools Netcat | 2013-09-09 22:26:45 | | 1360 | MySQL common commands, tips and Precautions | 2013-09-20 11:04:15 | | 1369 | The principle of "go" Database design--b tree, B-tree, C + tree, b* tree | 2013-09-21 12:30:18 | | 1379 | MySQL basic SQL statements Common Management SQL | 2013-09-21 12:39:23 | +------+----------------------------------------------------------------+---------------------+ 7 rows in Set (0.01 sec)

To sort query results:
Order by Field_name {asc| DESC}
As follows:

The code is as follows	Copy Code
Mysql> Select Id,post_title,post_date from wp_posts where id>1290 and post_status= ' publish ' order by ID;

# #升序, id is a sorted field

The code is as follows	Copy Code
Mysql> Select Id,post_title,post_date from wp_posts where id>1290 and post_status= ' publish ' order by ID DESC;

# #降序
field alias: As
Select Col_name as col_aliases ... : Use aliases for fields

Select Col_name,... from Tb_name as tb_aliases ... : Use aliases for tables
As follows:

Mysql&gt; Select Post_title as article title from Wp_posts where id&gt;1290 and post_status= ' publish ';


+----------------------------------------------------------------+


| Article title |


+----------------------------------------------------------------+


| "Turn" HP 3PAR Storage Concept III |


| "Turn" HP 3PAR storage Concept Four |


| "Go" XenDesktop 5.5+vsphere 5 Create a virtual machine error |


| Linux under the powerful Network Tools Netcat |


| MySQL common commands, tips and Precautions |


| The principle of "go" Database design--b tree, B-tree, C + tree, b* tree |


| MySQL basic SQL statements Common Management SQL |


+----------------------------------------------------------------+


7 rows in Set (0.02 sec)

# #还可以这样:

The code is as follows

Copy Code

Mysql> Select 3+2 as SUM; +-----+ | SUM | +-----+ | 5 | +-----+ 1 row in Set (0.00 sec) LIMIT clause: LIMIT [Offset,]count As follows: Mysql> Select Id,post_title,post_date from wp_posts where id>1290 and post_status= ' publish ' limit 5; +------+--------------------------------------------------+---------------------+ | ID | Post_title | Post_date | +------+--------------------------------------------------+---------------------+ | 1291 | "Turn" HP 3PAR Storage Concept III | 2013-08-29 17:21:27 | | 1298 | "Turn" HP 3PAR storage Concept Four | 2013-08-29 17:22:33 | | 1351 | "Go" XenDesktop 5.5+vsphere 5 Create a virtual machine error | 2013-09-04 17:41:26 | | 1357 | Linux under the powerful Network Tools Netcat | 2013-09-09 22:26:45 | | 1360 | MySQL common commands, tips and Precautions | 2013-09-20 11:04:15 | +------+--------------------------------------------------+---------------------+ 5 rows in Set (0.01 sec) Mysql> Select Id,post_title,post_date from wp_posts where id>1290 and post_status= ' publish ' limit ' 2, 3; # #红色部分 (digits before commas) to represent offsets +------+--------------------------------------------------+---------------------+ | ID | Post_title | Post_date | +------+--------------------------------------------------+---------------------+ | 1351 | "Go" XenDesktop 5.5+vsphere 5 Create a virtual machine error | 2013-09-04 17:41:26 | | 1357 | Linux under the powerful Network Tools Netcat | 2013-09-09 22:26:45 | | 1360 | MySQL common commands, tips and Precautions | 2013-09-20 11:04:15 | +------+--------------------------------------------------+---------------------+ 3 Rows in Set (0.00 sec) Aggregation: SUM (), MIN (), MAX (), AVG (), COUNT (), field name in parentheses mysql> select SUM (ID) from wp_posts; mysql> Select min (ID) from wp_posts; check early. The youngest Mysql> select Max (ID) from wp_posts; Find the largest Mysql> Select AVG (ID) from wp_posts; Average Mysql> Select COUNT (ID) from wp_posts; count Grouping: Group BY, commonly used in conjunction with aggregation operations As follows: Mysql> Select COUNT (post_status) as the number of States, Post_status as status name from Wp_posts Group by Post_status; +-----------------+--------------+ | Each State quantity | Status Name | +-----------------+--------------+ | 1 | Auto-draft | | 9 | Draft | | 251 | Inherit | | 238 | Publish | | 2 | Trash | +-----------------+--------------+ 5 rows in Set (0.01 sec) Note: You can use the having qualification to filter the results of group by again, using the same where

Second, multiple table query
Connection:
Cross-Joins: Cartesian product
Natural connection: Connect two tables in a field equal to the following

The code is as follows

Copy Code

Mysql> SELECT students. Name,students. Age,courses. Cname,students. Gender from students,courses WHERE students. CID1 = courses. CID; +--------------+------+------------------+--------+ | Name | Age | Cname | Gender | +--------------+------+------------------+--------+ |   guojing | 19 | Taijiquan | M | |   Yangguo | 17 | Taijiquan | M | |   Dingdian | 25 | Qishangquan | M | |   Hufei | 31 | wanliduxing | M | |   Huangrong | 16 | Qianzhuwandushou | F | |   Yuelingshang | 18 | wanliduxing | F | |   Zhangwuji | 20 | Hamagong | M | |   Xuzhu | 26 | Taijiquan | M | +--------------+------+------------------+--------+ 8 rows in Set (0.00 sec)

Outer joins:
Left OUTER join: LEFT_TB to RIGHT_TB on ... : Take left table as the standard

The code is as follows

Copy Code

Mysql> SELECT S.name,c.cname from students as s left JOIN courses as C on s.cid1=c.cid; +--------------+--------------------+ | Name | Cname | +--------------+--------------------+ | guojing | Taijiquan | | Yangguo | Taijiquan | | Dingdian | Qishangquan | | Hufei | wanliduxing | | Huangrong | Qianzhuwandushou | | Yuelingshang | wanliduxing | | Zhangwuji | Hamagong | | Xuzhu | Taijiquan | | Linghuchong | NULL | | Yilin | NULL | +--------------+--------------------+ Rows in Set (0.00 sec)

Right outer join: LEFT_TB right-hand join RIGHT_TB on ... : With the right table as the standard

The code is as follows

Copy Code

Mysql> SELECT S.name,c.cname from students as s right joins courses as C on s.cid1=c.cid; +--------------+--------------------+ | Name | Cname | +--------------+--------------------+ | guojing | Taijiquan | | Yangguo | Taijiquan | | Dingdian | Qishangquan | | Hufei | wanliduxing | | Huangrong | Qianzhuwandushou | | Yuelingshang | wanliduxing | | Zhangwuji | Hamagong | | Xuzhu | Taijiquan | | NULL | Yiyangzhi | | NULL | Jinshejianfa | | NULL | Qiankundanuoyi | | NULL | Pixiejianfa | | NULL | Jiuyinbaiguzhua | +--------------+--------------------+ Rows in Set (0.01 sec)

Self-connection: connecting between different fields in this table

code is as follows	copy code
mysql> SELECT C.name as student,s.name as teacher from students as c,students as S WHERE c.tid=s.sid; +-----------+-------------+ | student   | teacher     | +-----------+----- --------+ | guojing   | dingdian    | | yangguo   | guojing     | | dingdian  | zhangwuji   | | hufei     | huangrong   | | Huangrong | Linghuchong | +-----------+-------------+ 5 rows in Set (0.02 sec)

Note: Use aliases
three, subqueries: nested another query in one query
: Query for students older than average in the students table

code is as follows	copy code
mysql> Select Name,age from Students WHERE > (select AVG.) from students); +-------------+------+ | name           | age    | +-------------+------+ | dingdian      |   25 | | hufei           |   31 | | xuzhu           |   26 | | Linghuchong |   22 | +-------------+------+ 4 rows in Set (0.08 sec)

Sub-query Considerations:

Use subqueries in comparison operations: Subqueries can only return a single value;
In (): using subqueries;
Using subqueries in from;
Union query: Union, merging the results of two queries

The code is as follows	Copy Code
Mysql> (select Name,age from students) UNION (select Tname,age from tutors); +--------------+------+ | Name | Age | +--------------+------+ |   guojing | 19 | |   Yangguo | 17 | |   Dingdian | 25 | |   Hufei | 31 | |   Huangrong | 16 | |   Yuelingshang | 18 | |   Zhangwuji | 20 | |   Huyidao | 42 | |   Ningzhongze | 49 | +--------------+------+ Rows in Set (0.00 sec)