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> Select Post_title as article title from Wp_posts where id>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)
|