SELECT 是最常用的SQL语句,用来索引一个或者多个表信息。
作为SQL组成部分的字段,关键字不能作为表或者列的名字。使用SELECT索引数据,必须至少给出两条信息,想要什么? 从什么地方获取?
3.1. 检查单个列
SELECT prod_name FROM Products;
解释:使用SELECT 语句从 Products 表中检索一个名为prod_name 的列,FROM 关键字从指定的标名索引。
mysql> SELECT prod_name FROM Products;
+---------------------+
| prod_name |
+---------------------+
| Fish bean bag toy |
| Bird bean bag toy |
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+---------------------+
9 rows in set (0.00 sec)
- SQL语句分成多好容易阅读与调试,如果语句较长
- SQL语句必须以
;
结束。 - SQL语句不区分大小写,除了表名,跟值以外,SQL关键字使用大写,便于阅读
3.2. 索引多个列
与索引单列对比,唯一的不同是必须,在SELECT 关键字后给出多个列名,列名直接用,
分隔,最后一列不需要。
SELECT prod_id, prod_name, prod_price FROM Products;
解释:使用SELECT 从表Products 中选择数据,指定3个列名,prod_id, prod_name, prod_price
mysql> SELECT prod_id, prod_name, prod_price FROM Products;
+---------+---------------------+------------+
| prod_id | prod_name | prod_price |
+---------+---------------------+------------+
| BNBG01 | Fish bean bag toy | 3.49 |
| BNBG02 | Bird bean bag toy | 3.49 |
| BNBG03 | Rabbit bean bag toy | 3.49 |
| BR01 | 8 inch teddy bear | 5.99 |
| BR02 | 12 inch teddy bear | 8.99 |
| BR03 | 18 inch teddy bear | 11.99 |
| RGAN01 | Raggedy Ann | 4.99 |
| RYL01 | King doll | 9.49 |
| RYL02 | Queen doll | 9.49 |
+---------+---------------------+------------+
9 rows in set (0.01 sec)
3.3. 检索所有列
SELECT * FROM Products;
使用通配符 *
表示返回表中所有的列
mysql> SELECT * FROM Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BNBG01 | DLL01 | Fish bean bag toy | 3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02 | DLL01 | Bird bean bag toy | 3.49 | Bird bean bag toy, eggs are not included |
| BNBG03 | DLL01 | Rabbit bean bag toy | 3.49 | Rabbit bean bag toy, comes with bean bag carrots |
| BR01 | BRS01 | 8 inch teddy bear | 5.99 | 8 inch teddy bear, comes with cap and jacket |
| BR02 | BRS01 | 12 inch teddy bear | 8.99 | 12 inch teddy bear, comes with cap and jacket |
| BR03 | BRS01 | 18 inch teddy bear | 11.99 | 18 inch teddy bear, comes with cap and jacket |
| RGAN01 | DLL01 | Raggedy Ann | 4.99 | 18 inch Raggedy Ann doll |
| RYL01 | FNG01 | King doll | 9.49 | 12 inch king doll with royal garments and crown |
| RYL02 | FNG01 | Queen doll | 9.49 | 12 inch queen doll with royal garments and crown |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)
除非需要表中每一列,或者不明确指定列,否则不要使用 *
通配符。
下一节:数据过滤操作符:AND、OR、IN、NOT。