SQL 笔记（一）

1 ORDER BY 除了支持按列名排序，还支持按列位置排序（位置从1 开始计数）。
	select prod_id, prod_price, prod_name from products order by prod_price, prod_name;
	select prod_id, prod_price, prod_name from products order by 2, 3;

2. ASC ascending 升序排列（A-Z）DESC descending降序排列（Z-A）
	两个关键字只应用到直接位于其前面的列名。
	
3. WHERE 字句操作符
	=		等于
	<>		不等于
	!=		不等于
	<		小于
	<=		小于等于
	!<		不小于
	>		大于
	>=		大于等于
	!>		不大于
	BETWEEN	在指定的两个值之间
	IS NULL 为NULL值
	
4. BETWEEN  是闭区间
	select * from products where prod_price between 5.99 and 10;
	
5. NULL 值检查
	select vend_id from vendors where vend_state is null;
	
6. 逻辑操作符 OR AND
	在处理OR 操作符前，优先考虑AND。

7. IN 操作符。用来指定条件范围，范围中的每个条件都可以进行匹配。
	IN 取合法值的由逗号分隔的清单。
	select prod_name, prod_price from products
    -> where vend_id IN ('DELL01', 'BRS01')
    -> ORDER by prod_name;
	
8. NOT 否定之后所跟的条件。

9. LIKE 操作符。通配符搜索智能用于文本字符串，非文本数据类型不能使用。
	% 表示任意字符出现的任意次数。
	select prod_id, prod_name from products where prod_name like 'Fish%';
	select prod_id, prod_name from products where prod_name like 'F%y';
	 
	 _ 下划线通配符。只匹配单个字符而不是多个字符。
	 select prod_id, prod_name from products 
    -> where prod_name like '_ inch%';
	
	（MySQL 不支持）方括号[] 通配符。用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。
	select cust_contact from customers where 
    -> cust_contact like '[JM]%' ORDER by cust_contact;
	// 找出所有联系人以J或者M开头的。
	^ 脱字符来取反。
	select cust_contact from customers where 
    -> cust_contact like '[^JM]%' ORDER by cust_contact;
	
	通配符的使用技巧：
	不要过分使用。不要放在搜索条件的开始位置。

10. 计算字段
	拼接字段 MySQL contact
	select concat(vend_name, '(',vend_country,')') from vendors order by vend_name;
	其他数据库可能是+ 或者 ||
	
	去右边空格 RTRIM() LTRIM() TRIM()
	select rtrim(concat(vend_name, '(',vend_country,')')) from vendors order by vend_name;
	
	别名
	select rtrim(concat(vend_name, '(',vend_country,')')) as vend_name from vendors order by vend_name;
	select rtrim(concat(vend_name, '(',vend_country,')'))  vend_name from vendors order by vend_name;
	
	执行算数计算
	select prod_id, quantity, quantity*item_price as expanded_price from orderitems where order_num = 20008;
	+---------+----------+----------------+
	| prod_id | quantity | expanded_price |
	+---------+----------+----------------+
	| RGAN01  |        5 | 24.95          |
	| BR03    |        5 | 59.95          |
	| BNBG01  |       10 | 34.90          |
	| BNBG02  |       10 | 34.90          |
	| BNBG03  |       10 | 34.90          |
	+---------+----------+----------------+
	
	SQL 支持的算数操作符
	+		加
	-		减
	* 		乘
	/		除
	
11. 函数 一般在数据上执行。
	MySQL 提取串的组成部分 SUBSTRING()
	MySQL 数据类型转换	CONVERT()
	
	文本处理函数 UPPER()
	select vend_name, UPPER(vend_name) AS vend_name_upcase from
    -> vendors order by vend_name;
	+-----------------+------------------+
	| vend_name       | vend_name_upcase |
	+-----------------+------------------+
	| Bear Emporium   | BEAR EMPORIUM    |
	| Bears R Us      | BEARS R US       |
	| Doll House Inc. | DOLL HOUSE INC.  |
	| Fun and Games   | FUN AND GAMES    |
	| Furball Inc.    | FURBALL INC.     |
	| Jouets et ours  | JOUETS ET OURS   |
	+-----------------+------------------+
	
	LENGTH()
	select vend_name, LENGTH(vend_name) AS vend_name_upcase from
    -> vendors order by vend_name;
	
	SOUNDEX() 函数将任何文本串转换为器语音表示的字母数字模式的算法。
	select cust_name, cust_contact from customers where SOUNDEX(cust_contact) = 
    -> SOUNDEX('Michael Green');
	+------------+----------------+
	| cust_name  | cust_contact   |
	+------------+----------------+
	| Kids Place | Michelle Green |
	+------------+----------------+

	日期和时间处理函数
	select * from orders where YEAR(order_date) = 2004;
	+-----------+---------------------+------------+
	| 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 |
	+-----------+---------------------+------------+
	MySQL 使用YEAR() 从日期中提取年。

	数值处理函数
	ABS()		返回一个数的绝对值
	COS()		返回一个角度的余弦
	EXP()		返回一个数的指数
	PI()		返回圆周率
	SIN()		返回一个角度的正弦值
	SQRT()		返回一个数的平方根
	TAN()		返回一个角度的正切
	
12. 聚集函数 运行在行组上，计算和返回单个值的函数。
	AVG()		返回某列的平均值	忽略列值为NULL的行
	COUNT()		返回某列的行数
	MAX()		返回某列的最大值
	MIN()		返回某列的最小值
	SUM()		返回某列的和
	
	select AVG(prod_price) AS avg_price from products;
	+-----------+
	| avg_price |
	+-----------+
	| 6.823333  |
	+-----------+
	
	COUNT COUNT(*)表示对表中的数目进行计算，不管表列中包含的是NULL
	还是非空值。
	COUNT(column) 对特定列中具有值的行进行计数，忽略NULL值。
	select count(*) as num_cust from customers;
	+----------+
	| num_cust |
	+----------+
	|        5 |
	+----------+
	select count(cust_email) as num_cust from customers;
	+----------+
	| num_cust |
	+----------+
	|        3 |
	+----------+
	
	select SUM(quantity) AS items_ordered from orderitems;
	
	DINSTANCT 去重复
	select AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 'Dll01';
	// 只计算price 不同的行
	
	DISTINCT 必须使用列名，不能用于计算或表达式。只能用于COUNT(column), 不能用于COUNT(*);
	
13. 分组数据
	GROUP BY 
	select vend_id, count(*) as num_prods from products 
    -> group by vend_id;
	+---------+-----------+
	| vend_id | num_prods |
	+---------+-----------+
	| BRS01   |         3 |
	| DLL01   |         4 |
	| FNG01   |         2 |
	+---------+-----------+
	GROUP BY 指示DBMS分组数据，然后对每个组而不是整个结果集进行聚集。
	GROUP BY 必须出现在WHERE字句之后，ORDER BY子句之前。
	select cust_id, count(*) as orders from orders group by cust_id
    -> having count(*) >= 2;
	+------------+--------+
	| cust_id    | orders |
	+------------+--------+
	| 1000000001 |      2 |
	+------------+--------+
	
	WHERE和HAVING 
	where 在数据分组钱进行过滤，having 在数据分组后尽心过滤。

	列出具有两个以上、加个为4以上的产品的供应商
	select vend_id, count(*) as num_prods from products
    -> where prod_price >= 4 group by vend_id 
    -> having count(*) >= 2;
	+---------+-----------+
	| vend_id | num_prods |
	+---------+-----------+
	| BRS01   |         3 |
	| FNG01   |         2 |
	+---------+-----------+
	
	GROUP BY 排序
	select order_num, count(*) as items from orderitems
    -> group by order_num having count(*) >= 3 order by items, order_num;
	+-----------+-------+
	| order_num | items |
	+-----------+-------+
	|     20006 |     3 |
	|     20009 |     3 |
	|     20007 |     5 |
	|     20008 |     5 |
	+-----------+-------+

14. 使用子查询
	select cust_id from orders where order_num in 
    -> (select order_num from orderitems where prod_id='RGAN01');
	+------------+
	| cust_id    |
	+------------+
	| 1000000004 |
	| 1000000005 |
	+------------+
	select cust_name, cust_contact from customers where
    -> cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'RGAN01'));
	+---------------+--------------------+
	| cust_name     | cust_contact       |
	+---------------+--------------------+
	| Fun4All       | Denise L. Stephens |
	| The Toy Store | Kim Howard         |
	+---------------+--------------------+

	作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。
	
	作为计算字段使用子查询
	检索所有用户的订单数量
	select cust_name, cust_state, (select count(*)from orders where orders.cust_id = customers.cust_id) as orders from
    -> customers order by cust_name;