mysql数据库select_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)