企业网站开发研究现状/seo系统是什么

注意

多条SQL语句必须以分号(；)分隔；

SQL语句不区分大小写；

在处理SQL语句时，其中所有空格都被忽略；

当选择多个列是，一定要在列名之间加上逗号，但最后一个列名后不加。

SELECT语句

检索单个列

mysql> SELECT prod_name FROM products;

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

| prod_name |

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

| .5 ton anvil |

| 1 ton anvil |

| 2 ton anvil |

| Detonator |

| Bird seed |

| Carrots |

| Fuses |

| JetPack 1000 |

| JetPack 2000 |

| Oil can |

| Safe |

| Sling |

| TNT (1 stick) |

| TNT (5 sticks) |

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

14 rows in set (0.00 sec)

检索所有列

mysql> SELECT * FROM products;

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

| prod_id | vend_id | prod_name | prod_price | prod_desc |

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

| ANV01 | 1001 | .5 ton anvil | 5.99 | .5 ton anvil, black, complete with handy hook |

| ANV02 | 1001 | 1 ton anvil | 9.99 | 1 ton anvil, black, complete with handy hook and carrying case |

| ANV03 | 1001 | 2 ton anvil | 14.99 | 2 ton anvil, black, complete with handy hook and carrying case |

| DTNTR | 1003 | Detonator | 13.00 | Detonator (plunger powered), fuses not included |

| FB | 1003 | Bird seed | 10.00 | Large bag (suitable for road runners) |

| FC | 1003 | Carrots | 2.50 | Carrots (rabbit hunting season only) |

| FU1 | 1002 | Fuses | 3.42 | 1 dozen, extra long |

| JP1000 | 1005 | JetPack 1000 | 35.00 | JetPack 1000, intended for single use |

| JP2000 | 1005 | JetPack 2000 | 55.00 | JetPack 2000, multi-use |

| OL1 | 1002 | Oil can | 8.99 | Oil can, red |

| SAFE | 1003 | Safe | 50.00 | Safe with combination lock |

| SLING | 1003 | Sling | 4.49 | Sling, one size fits all |

| TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick |

| TNT2 | 1003 | TNT (5 sticks) | 10.00 | TNT, red, pack of 10 sticks |

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

14 rows in set (0.00 sec)

检索不同行

mysql> SELECT vend_id FROM products;

+---------+

| vend_id |

+---------+

| 1001 |

| 1001 |

| 1001 |

| 1002 |

| 1002 |

| 1003 |

| 1003 |

| 1003 |

| 1003 |

| 1003 |

| 1003 |

| 1003 |

| 1005 |

| 1005 |

+---------+

14 rows in set (0.00 sec)

去重

mysql> SELECT DISTINCT vend_id FROM products;

+---------+

| vend_id |

+---------+

| 1001 |

| 1002 |

| 1003 |

| 1005 |

+---------+

4 rows in set (0.00 sec)

DISTINCT关键字，应用于所有列而不是前置它的列。

限制结果

mysql> SELECT vend_id FROM products LIMIT 5,5;

+---------+

| vend_id |

+---------+

| 1003 |

| 1003 |

| 1003 |

| 1003 |

| 1003 |

+---------+

5 rows in set (0.00 sec)

LIMIT 5,5 指示MySQL返回从行5开始的5行。

ORDER BY子句

单个排序

mysql> SELECT prod_name FROM products ORDER BY prod_name;

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

| prod_name |

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

| .5 ton anvil |

| 1 ton anvil |

| 2 ton anvil |

| Bird seed |

| Carrots |

| Detonator |

| Fuses |

| JetPack 1000 |

| JetPack 2000 |

| Oil can |

| Safe |

| Sling |

| TNT (1 stick) |

| TNT (5 sticks) |

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

14 rows in set (0.00 sec)

多个列排序

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进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

指定排序方向

mysql> SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name,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)

默认是顺序排序(ASC)；

如果要逆序就在需要逆序的列指定DESC。

WHERE 子句

WHERE 子句操作符

操 作 符

说 明

=

等于

<>

不等于

！=

不等于

<

小于

<=

小于等于

>

大于

>=

大于等于

=

等于

BETWEEN

在指定的两个值之间

检查单个值

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

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

| prod_id | prod_price | prod_name |

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

| FU1 | 3.42 | Fuses |

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

1 row in set (0.00 sec)

不匹配检查

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

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

| vend_id | prod_name |

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

| 1001 | .5 ton anvil |

| 1001 | 1 ton anvil |

| 1001 | 2 ton anvil |

| 1002 | Fuses |

| 1002 | Oil can |

| 1005 | JetPack 1000 |

| 1005 | JetPack 2000 |

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

7 rows in set (0.01 sec)

范围值检查

mysql> SELECT vend_id,prod_name FROM products WHERE prod_price BETWEEN 5 AND 10;

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

| vend_id | prod_name |

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

| 1001 | .5 ton anvil |

| 1001 | 1 ton anvil |

| 1003 | Bird seed |

| 1002 | Oil can |

| 1003 | TNT (5 sticks) |

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

5 rows in set (0.01 sec)

空值检查

mysql> SELECT cust_id FROM customers WHERE cust_email IS NULL;

+---------+

| cust_id |

+---------+

| 10002 |

| 10005 |

+---------+

2 rows in set (0.00 sec)