mysql基础-数据库SELECT使用详解：LIMIT，JION...ON方法的运用

紧接上一篇博客，这一篇介绍详解SELECT使用，主要有LIMIT，JION…ON方法的运用。

上一篇连接：https://blog.csdn.net/brucewong0516/article/details/79873134

一、LIMIT限制查询出来的条数
显示前两条：

mysql> select * from user3 limit 2;
+----+--------+------+
| id | symbol | name |
+----+--------+------+
|  2 | 99     | hh   |
|  5 | 2      | lh   |
+----+--------+------+
2 rows in set (0.00 sec)

二、JOIN …ON在两个不同的表中查询数据
表user3：

mysql> desc user3;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| symbol | char(10)    | NO   |     | NULL    |                |
| name   | varchar(30) | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

表user4：

mysql> desc user4;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id      | int(10) | NO   |     | NULL    |       |
| dates   | date    | YES  |     | NULL    |       |
| enddate | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.1在user3与user4两张表中选择数据拼接：
user4.id = user3.id

mysql> select * from user3 join user4 on user4.id=user3.id;
+----+--------+------+----+------------+------------+
| id | symbol | name | id | dates      | enddate    |
+----+--------+------+----+------------+------------+
|  2 | 99     | hh   |  2 | 2010-02-05 | 2015-03-06 |
+----+--------+------+----+------------+------------+
1 row in set (0.00 sec)

user4.id < user3.id

mysql> select * from user3 join user4 on user4.id<user3.id;
+----+--------+------+----+------------+------------+
| id | symbol | name | id | dates      | enddate    |
+----+--------+------+----+------------+------------+
|  2 | 99     | hh   |  1 | 2014-04-10 | NULL       |
|  5 | 2      | lh   |  1 | 2014-04-10 | NULL       |
|  9 | 99     | hh   |  1 | 2014-04-10 | NULL       |
| 10 | 10     | th   |  1 | 2014-04-10 | NULL       |
|  5 | 2      | lh   |  2 | 2010-02-05 | 2015-03-06 |
|  9 | 99     | hh   |  2 | 2010-02-05 | 2015-03-06 |
| 10 | 10     | th   |  2 | 2010-02-05 | 2015-03-06 |
+----+--------+------+----+------------+------------+
7 rows in set (0.00 sec)

2.2LEFT JOIN …ON..关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL

mysql> select * from user3 left join user4 on user4.id=user3.id;
+----+--------+------+------+------------+------------+
| id | symbol | name | id   | dates      | enddate    |
+----+--------+------+------+------------+------------+
|  2 | 99     | hh   |    2 | 2010-02-05 | 2015-03-06 |
|  5 | 2      | lh   | NULL | NULL       | NULL       |
|  9 | 99     | hh   | NULL | NULL       | NULL       |
| 10 | 10     | th   | NULL | NULL       | NULL       |
+----+--------+------+------+------------+------------+
4 rows in set (0.00 sec)

2.3RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

mysql> select * from user3 right join user4 on user4.id=user3.id;
+------+--------+------+----+------------+------------+
| id   | symbol | name | id | dates      | enddate    |
+------+--------+------+----+------------+------------+
| NULL | NULL   | NULL |  1 | 2014-04-10 | NULL       |
|    2 | 99     | hh   |  2 | 2010-02-05 | 2015-03-06 |
+------+--------+------+----+------------+------------+
2 rows in set (0.00 sec)