mysql的检索方法_Mysql基本检索数据方法大全

1、检索单列

mysql> SELECT prod_name FROM products;2、检索多列

mysql> SELECT prod_name,prod_price,prod_desc FROM products;3、检索所有咧

mysql> SELECT * FROM products;4、过滤掉重复的行

mysql> SELECT DISTINCT vend_id FROM products;这样，相同的行就出现一次。结果如下：

+---------+

| vend_id |

+---------+

|    1001 |

|    1002 |

|    1003 |

|    1005 |

+---------+

4 rows in set (0.04 sec)

重要提示：DISTINCT关键字应用于所有的列而非仅仅是前置它的列，例如：

mysql> SELECT DISTINCT vend_id,prod_price FROM products;这样，只要不是完全相同的行，都会被检索出来。结果像这样：

+---------+------------+

| vend_id | prod_price |

+---------+------------+

|    1001 |       5.99 |

|    1001 |       9.99 |

|    1001 |      14.99 |

|    1003 |      13.00 |

|    1003 |      10.00 |

|    1003 |       2.50 |

|    1002 |       3.42 |

|    1005 |      35.00 |

|    1005 |      55.00 |

|    1002 |       8.99 |

|    1003 |      50.00 |

|    1003 |       4.49 |

+---------+------------+

12 rows in set (0.01 sec)

5、返回指定的行数

返回前3行：

mysql> SELECT prod_name FROM products LIMIT 3;返回从行号2开始的3行：

mysql> SELECT prod_name FROM products LIMIT 2,3;

6、指定表名和数据库名

mysql> SELECT products.prod_name FROM crashcourse.products;products是表名字，crashcourse是数据库名字。

7、排序检索出来的结果

按名称排序

mysql> SELECT prod_name FROM products ORDER BY prod_name;

按其他列排序

mysql> SELECT prod_name FROM products ORDER BY prod_price;默认情况下是升序排列，也可以指定降序排列(按prod_price降序排列)

mysql> SELECT prod_price FROM products ORDER BY prod_price DESC;

8、对多个列排序(先按一列排序，当此列有相同值时，按另一列排序)

mysql> SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price，prod_name;结果如下：

+---------+------------+----------------+

| prod_id | prod_price | prod_name      |

+---------+------------+----------------+

| FC      |       2.50 | Carrots        |

| TNT1    |       2.50 | TNT (1 stick)  |

| FU1     |       3.42 | Fuses          |

| SLING   |       4.49 | Sling          |

| ANV01   |       5.99 | .5 ton anvil   |

| OL1     |       8.99 | Oil can        |

| ANV02   |       9.99 | 1 ton anvil    |

| FB      |      10.00 | Bird seed      |

| TNT2    |      10.00 | TNT (5 sticks) |

| DTNTR   |      13.00 | Detonator      |

| ANV03   |      14.99 | 2 ton anvil    |

| JP1000  |      35.00 | JetPack 1000   |

| SAFE    |      50.00 | Safe           |

| JP2000  |      55.00 | JetPack 2000   |

+---------+------------+----------------+

14 rows in set (0.00 sec)

可以看到当prod_price相同时，按照prod_name排序。

9、可以指定不同列的排序顺序

mysql> SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;这样，prod_price按降序排列，prod_name按升序排列

+---------+------------+----------------+

| prod_id | prod_price | prod_name      |

+---------+------------+----------------+

| JP2000  |      55.00 | JetPack 2000   |

| SAFE    |      50.00 | Safe           |

| JP1000  |      35.00 | JetPack 1000   |

| ANV03   |      14.99 | 2 ton anvil    |

| DTNTR   |      13.00 | Detonator      |

| FB      |      10.00 | Bird seed      |

| TNT2    |      10.00 | TNT (5 sticks) |

| ANV02   |       9.99 | 1 ton anvil    |

| OL1     |       8.99 | Oil can        |

| ANV01   |       5.99 | .5 ton anvil   |

| SLING   |       4.49 | Sling          |

| FU1     |       3.42 | Fuses          |

| FC      |       2.50 | Carrots        |

| TNT1    |       2.50 | TNT (1 stick)  |

+---------+------------+----------------+

14 rows in set (0.00 sec)

10、结合LIMIT 最大值或者最小值

mysql> SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC LIMIT 1;这样检索出了价格最贵的商品id和商品价格和商品名称。

11、WHERE子句

mysql> SELECT prod_name,prod_price FROM products WHERE prod_price = 2.5;

mysql> SELECT prod_name,prod_price FROM products WHERE prod_name = 'Fuses';

mysql> SELECT prod_name,prod_price FROM products WHERE prod_price < 10;

mysql> SELECT prod_name,prod_price FROM products WHERE vend_id <> 1003;

mysql> SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5.99 AND 10;(结果包含5.99和10)

mysql> SELECT cust_id FROM customers WHERE cust_email IS NULL;

mysql> SELECT prod_name,prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;能用IN的时候不用OR。

mysql> SELECT prod_name,prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;

12、组合条件

mysql> SELECT prod_name,prod_price FROM products WHERE vend_id=1003 AND prod_price<=10;

mysql> SELECT prod_name,prod_price FROM products WHERE vend_id=1003 or vend_id=1002;

mysql> SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id=1002 OR (vend_id=1003 AND prod_price>10);习惯用()调节条件计算顺序。