《Sam Teach Yourself SQL in 10 Minutes》学习笔记（1）

--SELECT * FROM Products;

--prod_id    vend_id  prod_name   prod_price   prod_desc

--ctrl + k  c
--ctrl + k  u

--SELECT prod_name FROM Products ;
--SELECT prod_name FROM Products ORDER BY prod_name;

--相同的意思   ORDER BY子句只能放到语句的最后面
--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;

--descend 下降     ascend  上升
--SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;

--WHERE子句
--SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;
--SELECT prod_name, prod_price FROM Products WHERE prod_price < 10;
--SELECT vend_id, prod_name FROM Products WHERE vend_id != 'DLL01';
--SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
--SELECT prod_name FROM Products WHERE prod_price IS NULL;
--SELECT vend_id FROM Vendors WHERE vend_state IS NULL;				--vend 出售

--高级数据过滤  组合WHERE子句
--SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;
--SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

--SELECT prod_name, prod_price, vend_id FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;  --等同于(vend_id = 'DLL01') OR (vend_id = 'BRS01' AND prod_price >= 10)
--SELECT prod_name, prod_price, vend_id FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
--IN操作符
--SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01', 'BRS01') ORDER BY prod_name;		--SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name;
--IN操作符完成的功能与OR操作符相同，而且一般比OR操作符的执行更快
--IN WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当

--NOT  WHERE子句用来否定后跟条件的关键字
--在与IN操作符联合使用时，NOT使找出与条件列表不匹配的行非常简单
--SELECT prod_name, vend_id FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;
--SELECT prod_name, vend_id FROM Products WHERE vend_id != 'DLL01' ORDER BY prod_name;

--LIKE操作符
--通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索
--SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'fish%';
--SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%';
--SELECT prod_name FROM Products WHERE prod_name LIKE 'F%f';		--%代表搜索模式中给定位置的0个，1个或多个字符

--SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';	--未匹配8 inch teddy bear    --下划线只匹配单个字符而不是多个字符
--SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '% inch teddy bear';
--[charlist] 字符列中的如何单一字符
--[!charlist]或[^charlist] 不在字符列中的如何单一字符
--SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;
--SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;   --同下   NOT ... LIKE ...
--SELECT cust_contact FROM Customers WHERE NOT cust_contact LIKE '[JM]%' ORDER BY cust_contact;
--不要过分的使用通配符，通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长  
--确实需要通配符的时候，除非有绝对必要，否则不要把他们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的


--field（字段）
--拼接（concatenate）将值联结到一起构成单个值  改操作符可用加号（+）或两个斜杠（||）表示
SELECT vend_name + ' (' + vend_country + ')' FROM Vendors ORDER BY vend_name;
--SELECT vend_name || ' (' || vend_country || ')' FROM Vendors ORDER BY vend_name;   --SQL Server不支持
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' FROM Vendors ORDER BY vend_name;