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);习惯用()调节条件计算顺序。