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

Source: Internet
Author: User
Tags aliases joins min regular expression

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&gt; select * from Wp_links; Query all mysql&gt; 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&gt; Select Id,post_title,post_date from wp_posts where id&gt;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&gt; Select Id,post_title,post_date from wp_posts where id&gt;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&gt; Select Id,post_title,post_date from wp_posts where id&gt;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&gt; select SUM (ID) from wp_posts;


mysql&gt; Select min (ID) from wp_posts; check early. The youngest


Mysql&gt; select Max (ID) from wp_posts; Find the largest


Mysql&gt; Select AVG (ID) from wp_posts; Average


Mysql&gt; Select COUNT (ID) from wp_posts; count


Grouping: Group BY, commonly used in conjunction with aggregation operations


As follows:

Mysql&gt; 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&gt; 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&gt; 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&gt; 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&gt; (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)


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.