【学习笔记-SQL必知必会】课内示例查询代码

--第4课 过滤数据
SELECT prod_name,prod_price
FROM Products
WHERE prod_price=3.49;

--检查单个值
SELECT prod_name,prod_price
FROM Products
WHERE prod_price<10;

SELECT prod_name,prod_price
FROM Products
WHERE prod_price<=10;

--不匹配检查,使用<>符号
SELECT vend_id,prod_name
FROM Products
WHERE vend_id <>'DLL01';

--不匹配检查,使用!=符号
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 cust_name
FROM Customers
WHERE cust_email IS NULL;

--第5课 高级数据过滤
--组合WHERE语句,AND操作符
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id='DLL01' AND prod_price<=4;

--组合WHERE语句,OR操作符
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id='DLL01' OR vend_id='BRS01';

--组合WHERE语句,没有区分求值顺序
SELECT prod_name,prod_price
FROM Products
WHERE vend_id='DLL01' OR vend_id='BRS01'
AND prod_price>=10;

--组合WHERE语句,区分求值顺序
SELECT prod_name,prod_price
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');

--NOT操作符
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

--第6课 通配符
--%通配符，位于末尾
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%y';

--下划线通配符
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

--方括号通配符
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%';

--方括号通配符，使用前缀字符^进行否定
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%';

--第7课 创建计算字段
--拼接字段，使用+
SELECT vend_name + ' (' + vend_country +')'
FROM Vendors
ORDER BY vend_name;

--拼接字段，使用+，RTRIM函数去除空格
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) +')'
FROM Vendors
ORDER BY vend_name;

--拼接字段，使用+，RTRIM函数去除空格,使用别名
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) +')'
 AS vend_title 
FROM Vendors
ORDER BY vend_name;

--执行算术计算
SELECT prod_id,
       quantity,
	   item_price,
	   quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num=20008;

--第8课 使用函数处理数据
--UPPER函数，将文本全部转换为大写
SELECT vend_name,UPPER(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');

--日期处理函数DATEPART
SELECT order_num
FROM Orders
WHERE DATEPART(yy,order_date)=2020;

--第9课 汇总数据
--AVG()函数
SELECT AVG(prod_price) AS avg_price
FROM Products;

--AVG()函数,WHERE子句过滤
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

--COUNT函数,对所有行计数
SELECT COUNT(*) AS num_cust
FROM Customers;

--COUNT函数,对某一行计数
SELECT COUNT(cust_email) AS num_cust
FROM Customers;

--MAX函数
SELECT MAX(prod_price) AS max_pirce
FROM Products;

--MIN函数
SELECT MIN(prod_price) AS min_pirce
FROM Products;

--SUM函数,合计指定列
SELECT SUM(quantity) AS item_ordered
FROM OrderItems
WHERE order_num = 20005;

--SUM函数,合计计算值
SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;

--使用DISTINCT参数，聚集不同值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

--组合聚集函数
SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
	   MAX(prod_price) AS price_max,
	   AVG(prod_price) AS price_avg
FROM Products;

--第10课 分组数据
--创建分组
SELECT vend_id,COUNT(*) AS num_prods
FROM Products
Group BY vend_id;

--使用HAVING语句过滤分组
SELECT cust_id,COUNT(*) AS orders
FROM Orders
Group BY cust_id
HAVING COUNT(*)>=2;

--同时使用WHERE和HAVING
SELECT vend_id,COUNT(*) AS num_prods
FROM Products
WHERE prod_price>=4
Group BY vend_id
HAVING COUNT(*)>=2;

--第11课 分组数据
--两层子查询
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
					WHERE prod_id='RGAN01');

--三层子查询
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'));

--作为计算字段使用子查询
SELECT cust_name,
       cust_state,
	   (SELECT COUNT(*)
	    FROM Orders  
		WHERE Orders.cust_id = Customers.cust_id) AS Orders
FROM Customers
ORDER BY cust_name;

--第12课 联结表
--等值联结
SELECT vend_name,prod_name,prod_price
FROM Vendors,Products
WHERE Vendors.vend_id = Products.vend_id;

--内联结
SELECT vend_name,prod_name,prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

--使用WHERE语句，联结多个表
SELECT prod_name,vend_name,prod_price,quantity
FROM OrderItems,Products,Vendors
WHERE Products.vend_id = Vendors.vend_id
 AND OrderItems.prod_id = Products.prod_id
 AND order_num = 20007;

 --使用WHERE语句，联结多个表
 SELECT cust_name,cust_contact
 FROM Customers,Orders,OrderItems
 WHERE Customers.cust_id = Orders.cust_id
 AND OrderItems.order_num = Orders.order_num
 AND prod_id = 'RGAN01';