1简单查询
mysql> SELECT CITY,TARGET,SALES FROM OFFICES WHERE REGION='Eastern';
ORACLE和DB2遇到NULL值时什么也不显示
2计算查询
mysql> SELECT CITY,REGION,(SALES-TARGET) FROM OFFICES;
mysql> SELECT NAME,MONTH(HIRE_DATE),YEAR(HIRE_DATE) FROM SALESREPS;
mysql> SELECT CITY,'HAS SALES OF ',SALES FROM OFFICES;
SQL常量可以被其自身用做选择列表的项
-------------+---------------+-----------+
| CITY | HAS SALES OF | SALES |
+-------------+---------------+-----------+
| Denver | HAS SALES OF | 186042.00 |
| New York | HAS SALES OF | 692637.00 |
| Chicage | HAS SALES OF | 735042.00 |
| Atlanta | HAS SALES OF | 367911.00 |
| Los Angeles | HAS SALES OF | 835915.00 |
+-------------+---------------+-----------+15UNION和排序
3选择所有的字段
SELECT ×
4重复的记录
mysql> SELECT DISTINCT MGR FROM OFFICES;
5选择记录
mysql> SELECT NAME,SALES FROM SALESREPS WHERE MANAGER=104;
mysql> SELECT NAME,SALES,QUOTA FROM SALESREPS WHERE EMPL_NUM=105;
mysql> SELECT CITY,SALES,TARGET FROM OFFICES WHERE SALES>TARGET;
6比较测试
mysql> SELECT NAME FROM SALESREPS WHERE HIRE_DATE<'2006-01-01';
mysql> SELECT CITY,MGR FROM OFFICES WHERE MGR<>108;
mysql> SELECT CITY,MGR FROM OFFICES WHERE MGR!=108;
其中<>也可以写作！=。
7范围测试
mysql> SELECT ORDER_NUM,ORDER_DATE,PRODUCT,AMOUNT FROM ORDERS WHERE ORDER_DATE BETWEEN '2007-10-01'AND '2007-12-31';
范围测试包括范围的端点。包括10月1日和12月31日。
A BETWEEN B AND C=（A>=B)AND(A<=C)。否定形式NOT BETWEEN。
8组成员测试
mysql> SELECT NAME,QUOTA,SALES FROM SALESREPS WHERE REP_OFFICE IN(11,13,22);
9模式匹配测试
百分号%通配符字符匹配任何顺序的0个或多个字符。
mysql> SELECT COMPANY,CREDIT_LIMIT FROM CUSTOMERS WHERE COMPANY like 'Smith% Corp.';
下划线_通配符字符匹配任何单个字符
mysql> SELECT COMPANY,CREDIT_LIMIT FROM CUSTOMERS WHERE COMPANY LIKE 'Smiths_n Corp.';
like测试必须应用到具有字符串数据类型的字段
转意字符
mysql> SELECT ORDER_NUM,PRODUCT FROM ORDERS WHERE PRODUCT LIKE 'A$%BC%' ESCAPE '$';
以上是用$来做转意字符的，也可以用不同的字符来做转意字符。
10NULL值测试
mysql> SELECT NAME FROM SALESREPS WHERE REP_OFFICE IS NOT NULL;
这里不能用REP_OFFICE=NULL;因为NULL不是一个真正的值而是一个符号，表示值是未知的，所以不能用=来匹配。
11复合搜索条件
mysql> SELECT NAME,QUOTA,SALES FROM SALESREPS WHERE SALES<QUOTA AND SALES<300000;
mysql> SELECT NAME,QUOTA,SALES FROM SALESREPS WHERE SALES<QUOTA AND NOT SALES<15000;
12排序查询结果
默认情况下排序为升序ASC，降序位DESC。
mysql> SELECT CITY,REGION,(SALES-TARGET) FROM OFFICES ORDER BY REGION ASC,3 DESC;
（不建议）
这里的3也可以用（SALES-TARGET）代替。
13组合查询结果
列出产品价格超过2000美元或在一个订单中订购了超过30000美元产品的所有产品。
mysql> SELECT MFR_ID,PRODUCT_ID FROM PRODUCTS WHERE PRICE>2000.00 UNION SELECT DISTINCT MFR,PRODUCT FROM ORDERS WHERE AMOUNT>30000.00;
注意三点：1,两个SELECT子句必须包含同样数目的字段
2,第一个表中的数据类型必须与第二个表中对应字段的数据类型相同
3,两个表都不能用ORDER BY子句排序，但组合后的查询结果可以排序。
14UNION和重复记录
UNION 默认是消除重复的，UNION ALL保留重复记录。
mysql> SELECT MFR_ID,PRODUCT_ID FROM PRODUCTS WHERE PRICE>2000.00 UNION ALL SELECT DISTINCT MFR,PRODUCT FROM ORDERS WHERE AMOUNT>30000.00;
15UNION和排序
mysql> SELECT MFR_ID,PRODUCT_ID FROM PRODUCTS WHERE PRICE>2000.00 UNION SELECT DISTINCT MFR,PRODUCT FROM ORDERS WHERE AMOUNT>30000.00 ORDER BY 1,2;
