本文共 13980 字，大约阅读时间需要 46 分钟。
SELECT用于从一个或多个表中检索信息，使用SELECT检索数据表需要明确：
想选择什么，以及从什么地方选择
检索单个列的sql语法：
SELECT 列名 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语法：
SELECT 列名1,列名2,...列名n FROM 表名
示例:
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 |+---------+---------------------+------------+
检索所有列使用*通配符：
SELECT * FROM 表名;
示例:
mysql> SELECT * FROM Orders;+-----------+---------------------+------------+| order_num | order_date | cust_id |+-----------+---------------------+------------+| 20005 | 2004-05-01 00:00:00 | 1000000001 || 20006 | 2004-01-12 00:00:00 | 1000000003 || 20007 | 2004-01-30 00:00:00 | 1000000004 || 20008 | 2004-02-03 00:00:00 | 1000000005 || 20009 | 2004-02-08 00:00:00 | 1000000001 |+-----------+---------------------+------------+5 rows in set (0.01 sec)
如果想查询某一列不重复的结果，可以使用DISTINCT关键字去除重复项返回不重复的结果。从Products表中查询不重复的供应商ID,返回所有结果的查询：
mysql> SELECT vend_id from Products;+---------+| vend_id |+---------+| DLL01 || DLL01 || DLL01 || BRS01 || BRS01 || BRS01 || DLL01 || FNG01 || FNG01 |+---------+9 rows in set (0.00 sec)
使用DISTINCT关键字去除重复项：
mysql> SELECT DISTINCT vend_id FROM Products;+---------+| vend_id |+---------+| DLL01 || BRS01 || FNG01 |+---------+3 rows in set (0.00 sec)
SELECT列名的方式会返回所有匹配的行，如果想返回一定数量的记录可以在SELECT查询后面加上限制。这一查询在不同的数据库中实现不相同。在mysql中使用limit关键字,语法：
SELECT 列名 FROM 表名 limit 条数;
比如返回prod_name的前4个：
mysql> SELECT prod_name from Products limit 4;+---------------------+| prod_name |+---------------------+| Fish bean bag toy || Bird bean bag toy || Rabbit bean bag toy || 8 inch teddy bear |+---------------------+4 rows in set (0.00 sec)
如果想从指定位置查询一定数目的记录，可以使用offset关键字，语法：
SELECT 列名 from 表名 limit 返回结果数目 offset 开始位置；
比如返回从第3条记录开始的4条数据，数据库中的第一条记录位置从0，因此第3条记录的位置为2：
mysql> SELECT prod_name from Products limit 4 offset 2;+---------------------+| prod_name |+---------------------+| Rabbit bean bag toy || 8 inch teddy bear || 12 inch teddy bear || 18 inch teddy bear |+---------------------+4 rows in set (0.00 sec)
SELECT语句中可以根据WHERE子句制定搜索条件进行过滤。例如:
mysql> select prod_id,prod_name,prod_price from Products where prod_price=3.49; +---------+---------------------+------------+| 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 |+---------+---------------------+------------+3 rows in set (0.00 sec)
WHERE子句可以使用常用的操作符：
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| > | 大于 |
> = | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| between A and B | 介于A和B之间,包含A和B |
| IS NULL | 为空值 |
空值检验例子：
mysql> select cust_id,cust_name,cust_email from Customers where cust_email IS NULL;+------------+---------------+------------+| cust_id | cust_name | cust_email |+------------+---------------+------------+| 1000000002 | Kids Place | NULL || 1000000005 | The Toy Store | NULL |+------------+---------------+------------+2 rows in set (0.00 sec)
mysql> select vend_id,prod_price,prod_name from Products Where vend_id='DLL01' AND prod_price <=4;+---------+------------+---------------------+| vend_id | prod_price | prod_name |+---------+------------+---------------------+| DLL01 | 3.49 | Fish bean bag toy || DLL01 | 3.49 | Bird bean bag toy || DLL01 | 3.49 | Rabbit bean bag toy |+---------+------------+---------------------+3 rows in set (0.00 sec)
mysql> select vend_id,prod_price,prod_name from Products Where vend_id='DLL01' OR prod_price <=4;+---------+------------+---------------------+| vend_id | prod_price | prod_name |+---------+------------+---------------------+| DLL01 | 3.49 | Fish bean bag toy || DLL01 | 3.49 | Bird bean bag toy || DLL01 | 3.49 | Rabbit bean bag toy || DLL01 | 4.99 | Raggedy Ann |+---------+------------+---------------------+4 rows in set (0.00 sec)
IN操作符用来制定条件范围，范围中的每个条件都可以进行匹配。和OR的功能一样，但比OR有更好的性能。
查询vend_id为”DLL01”或”BRS01”的产品：mysql> SELECT prod_name,vend_id,prod_price FROM Products WHERE vend_id IN('DLL01','BRS01') ORDER BY prod_price;+---------------------+---------+------------+| prod_name | vend_id | prod_price |+---------------------+---------+------------+| Fish bean bag toy | DLL01 | 3.49 || Bird bean bag toy | DLL01 | 3.49 || Rabbit bean bag toy | DLL01 | 3.49 || Raggedy Ann | DLL01 | 4.99 || 8 inch teddy bear | BRS01 | 5.99 || 12 inch teddy bear | BRS01 | 8.99 || 18 inch teddy bear | BRS01 | 11.99 |+---------------------+---------+------------+7 rows in set (0.00 sec) WHERE子句操作符的功能是否定其后所跟的任何条件。比如列出出了DLL01之外的所有供应商制造的产品：
SELECT prod_name,vend_id from Products where NOT vend_id='DLL01' ORDER BY prod_name;+--------------------+---------+| prod_name | vend_id |+--------------------+---------+| 12 inch teddy bear | BRS01 || 18 inch teddy bear | BRS01 || 8 inch teddy bear | BRS01 || King doll | FNG01 || Queen doll | FNG01 |+--------------------+---------+5 rows in set (0.00 sec)
查询以r开头的产品：
mysql> SELECT prod_name,vend_id from Products where prod_name like 'r%' ORDER BY prod_name;+---------------------+---------+| prod_name | vend_id |+---------------------+---------+| Rabbit bean bag toy | DLL01 || Raggedy Ann | DLL01 |+---------------------+---------+2 rows in set (0.00 sec)
找出prod_name以F起头以y结尾的产品：
mysql> SELECT prod_name,vend_id from Products where prod_name like 'F%y' ORDER BY prod_name;+-------------------+---------+| prod_name | vend_id |+-------------------+---------+| Fish bean bag toy | DLL01 |+-------------------+---------+1 row in set (0.00 sec)
一个下划线匹配一个字符：
mysql> select prod_id,prod_name from Products Where prod_name LIKE '__ inch teddy bear';+---------+--------------------+| prod_id | prod_name |+---------+--------------------+| BR02 | 12 inch teddy bear || BR03 | 18 inch teddy bear |+---------+--------------------+2 rows in set (0.00 sec)
存储在表中的数据有时候都不是应用程序所需要的，需要从数据库中转换、计算出来，比如有商品的价格和数量，打印报表的时候需要打印出总价，这就是计算字段的概念。
mysql> select CONCAT(cust_name,' (', cust_email,' )') as customer_email From Customers;+------------------------------------------+| customer_email |+------------------------------------------+| Village Toys (sales@villagetoys.com ) || NULL || Fun4All (jjones@fun4all.com ) || Fun4All (dstephens@fun4all.com ) || NULL |+------------------------------------------+5 rows in set (0.00 sec) Orders中包含所用订单，OrderItems表包含每个订单中的各项物品，检索订单号为20008的所有物品：
mysql> Select prod_id,quantity,item_price FROM OrderItems -> WHERE order_num=20008;+---------+----------+------------+| prod_id | quantity | item_price |+---------+----------+------------+| RGAN01 | 5 | 5 || BR03 | 5 | 12 || BNBG01 | 10 | 3 || BNBG02 | 10 | 3 || BNBG03 | 10 | 3 |+---------+----------+------------+5 rows in set (0.00 sec)
计算总价：
mysql> SELECT prod_id,quantity,item_price,quantity*item_price as expanded_price FROM OrderItems where order_num=20008;+---------+----------+------------+----------------+| prod_id | quantity | item_price | expanded_price |+---------+----------+------------+----------------+| RGAN01 | 5 | 5 | 25 || BR03 | 5 | 12 | 60 || BNBG01 | 10 | 3 | 30 || BNBG02 | 10 | 3 | 30 || BNBG03 | 10 | 3 | 30 |+---------+----------+------------+----------------+5 rows in set (0.00 sec)
为了数据的转换和处理方便，SQL提供了函数用来处理文本字符串、进行算术操作、处理日期和时间值、返回DBMS所使用的特殊信息。
文本处理函数常用的大小写转换、清除空格、统计长度、返回SOUNDEX同音字符串等。
大写转换使用UPPER()函数：mysql> select UPPER(cust_name) FROM Customers; +------------------+| UPPER(cust_name) |+------------------+| VILLAGE TOYS || KIDS PLACE || FUN4ALL || FUN4ALL || THE TOY STORE |+------------------+5 rows in set (0.00 sec)
小写转换使用LOWER()函数：
select LOWER(cust_name) FROM Customers;
SOUNDEX用于返回同音字符串，比如在Customers表中cust_contact列的名字为Michelle Green，如果输入错误Michael Green将会查不到结果：
mysql> SELECT cust_name,cust_contact FROM Customers where cust_contact = 'Michael Green';Empty set (0.00 sec)
使用SOUNDEX函数后：
mysql> SELECT cust_name,cust_contact FROM Customers where SOUNDEX(cust_contact) =SOUNDEX('Michael Green');+------------+----------------+| cust_name | cust_contact |+------------+----------------+| Kids Place | Michelle Green |+------------+----------------+1 row in set (0.00 sec) 提取日期年份(说明：不同的DBMS对日期处理函数的支持不一样，这里使用的是mysql)：
查询2010年的订单：
mysql> select * from Orders WHERE YEAR(ORDER_DATE)=2010;+-----------+---------------------+------------+| order_num | order_date | cust_id |+-----------+---------------------+------------+| 20010 | 2010-05-27 00:00:00 | 1000000010 || 20011 | 2010-09-27 00:00:00 | 1000000011 || 20013 | 2010-11-12 00:00:00 | 1000000013 || 20015 | 2010-04-15 00:00:00 | 1000000015 |+-----------+---------------------+------------+4 rows in set (0.01 sec)
数值处理函数可以用户代数、三角或几何运算。
| 函数 | 说明 |
|---|---|
| ABS() | 返回一个数的绝对值 |
| COS() | 返回一个角度的余弦值 |
| EXP() | 返回一个数的指数值 |
| PI() | 返回圆周率 |
| SIN() | 返回一个角度的正弦值 |
| SORT() | 返回一个数的平方根 |
| TAN() | 返回一个角度的正切 |
聚集函数可以用来求和、求平均值、求最大最小值、统计行数、求标准差。
以Products表的pord_price列为例：mysql> select SUM(prod_price) AS SUM FROM Products;+-------+| SUM |+-------+| 61.41 |+-------+1 row in set (0.00 sec)
mysql> select AVG(prod_price) AS AVG FROM Products;+----------+| AVG |+----------+| 6.823333 |+----------+1 row in set (0.00 sec)
mysql> select MAX(prod_price) AS MAX_PRICE FROM Products;+-----------+| MAX_PRICE |+-----------+| 11.99 |+-----------+1 row in set (0.00 sec)
mysql> select MIN(prod_price) AS MIN_PRICE FROM Products;+-----------+| MIN_PRICE |+-----------+| 3.49 |+-----------+1 row in set (0.01 sec)
mysql> select COUNT(*) AS TOTAL FROM PRODUCTS;+-------+| TOTAL |+-------+| 9 |+-------+1 row in set (0.00 sec)
mysql> select STD(prod_price) AS STD FROM PRODUCTS;+----------+| STD |+----------+| 3.036811 |+----------+1 row in set (0.00 sec)
select语句可以根据需求同时使用多个聚集函数：
mysql> select COUNT(*) AS 总个数,Max(prod_price) AS 最高价格 from Products;+-----------+--------------+| 总个数 | 最高价格 |+-----------+--------------+| 9 | 11.99 |+-----------+--------------+1 row in set (0.00 sec)
ORDER BY子句用于WHERE语句之后,ORDER BY子句之前。
以OrderItems表为列，先把OrderItems表列出来：mysql> select * from OrderItems;+-----------+------------+---------+----------+------------+| order_num | order_item | prod_id | quantity | item_price |+-----------+------------+---------+----------+------------+| 20005 | 1 | BR01 | 100 | 5 || 20005 | 2 | BR03 | 100 | 11 || 20006 | 1 | BR01 | 20 | 6 || 20006 | 2 | BR02 | 10 | 9 || 20006 | 3 | BR03 | 10 | 12 || 20007 | 1 | BR03 | 50 | 11 || 20007 | 2 | BNBG01 | 100 | 3 || 20007 | 3 | BNBG02 | 100 | 3 || 20007 | 4 | BNBG03 | 100 | 3 || 20007 | 5 | RGAN01 | 50 | 4 || 20008 | 1 | RGAN01 | 5 | 5 || 20008 | 2 | BR03 | 5 | 12 || 20008 | 3 | BNBG01 | 10 | 3 || 20008 | 4 | BNBG02 | 10 | 3 || 20008 | 5 | BNBG03 | 10 | 3 || 20009 | 1 | BNBG01 | 250 | 2 || 20009 | 2 | BNBG02 | 250 | 2 || 20009 | 3 | BNBG03 | 250 | 2 |+-----------+------------+---------+----------+------------+18 rows in set (0.00 sec)
如果要查出来prod_id为BNG01、BNG02。。。的各有多少个，可以使用GROUP BY，聚集的时候先分组数据，然后统计：
mysql> select prod_id,COUNT(*) AS prod_num from OrderItems group by prod_id;+---------+----------+| prod_id | prod_num |+---------+----------+| BNBG01 | 3 || BNBG02 | 3 || BNBG03 | 3 || BR01 | 2 || BR02 | 1 || BR03 | 4 || RGAN01 | 2 |+---------+----------+7 rows in set (0.00 sec)
当需要引用一个条件，而该条件需要引用一个聚集函数的时候，使用HAVING子句，比如在上面的分组查询中，筛选个数大于2的：
mysql> select prod_id,COUNT(*) AS prod_num from OrderItems group by prod_id HAVING COUNT(*)>2;+---------+----------+| prod_id | prod_num |+---------+----------+| BNBG01 | 3 || BNBG02 | 3 || BNBG03 | 3 || BR03 | 4 |+---------+----------+4 rows in set (0.00 sec)
转载地址：http://ibtoo.baihongyu.com/