mysql数据库SELECT使用详解

数据库的查询是重中之重，查询的时候有很多的选项可以使用。

（1）LIMIT：限制查询出来的条数

mysql> SELECT  *  FROM stu_tbl;
+-------+------+-------+
| name  | id   | score |
+-------+------+-------+
| du    | 1003 |    90 |
| he    | 1004 |    90 |
| wang  | 1001 |   100 |
| zhang | 1004 |    90 |
| zhao  | 1003 |    90 |
+-------+------+-------+
5 rows in set (0.00 sec)

mysql> SELECT  *  FROM stu_tbl LIMIT 2;
+------+------+-------+
| name | id   | score |
+------+------+-------+
| du   | 1003 |    90 |
| he   | 1004 |    90 |
+------+------+-------+
2 rows in set (0.00 sec)

（2）JOIN ...ON..在两个不同的表中查询数据

mysql> SELECT name,id,tea_name FROM stu_tbl JOIN teacher ON teacher.tea_name=stu_tbl.name;
+------+------+----------+
| name | id   | tea_name |
+------+------+----------+
| wang | 1001 | wang     |
+------+------+----------+
1 row in set (0.00 sec)

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

mysql> SELECT name,id,tea_name FROM stu_tbl LEFT JOIN teacher ON teacher.tea_name=stu_tbl.name;
+-------+------+----------+
| name  | id   | tea_name |
+-------+------+----------+
| du    | 1003 | NULL     |
| he    | 1004 | NULL     |
| wang  | 1001 | wang     |
| zhang | 1004 | NULL     |
| zhao  | 1003 | NULL     |
+-------+------+----------+
5 rows in set (0.00 sec)

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

mysql> SELECT name,id,tea_name FROM stu_tbl RIGHT JOIN teacher ON teacher.tea_name=stu_tbl.name;
+------+------+-----------+
| name | id   | tea_name  |
+------+------+-----------+
| wang | 1001 | wang      |
| NULL | NULL | xiaowang  |
| NULL | NULL | xiaozhang |
+------+------+-----------+
3 rows in set (0.00 sec)