SQL必知必会

第二章 检索数据

2.1 检索不同的值

• 返回不同的值

SELECT DISTINCT ID
FROM Product;

不能部分使用DISTINCT,作用于所有的列

2.2 限制结果

• 限制最多返回多少行

SELECT TOP 5 prod_name
FROM Product;

2.3 注释

• 用--来表示
• 用/**/来表示

第三章 排序检索数据

3.1 排序函数

• 按照某个列排序
  • order by 要放到select语句的最后一条子句
  • 通常order by子句中使用的列是显示的列，但是用非检索的列也是完全合法的

SELECT prod_name
FROM Products
ORDER BY prod_name;

• 按照多个列排序
  • 只有在prod_name相同时，才会按照prod_price排序

SELECT prod_name,prod_price
FROM Products
ORDER BY prod_name,prod_price;

• 按列位置排序

SELECT prod_name,prod_price
FROM Products
ORDER BY 2，3;

• 指定排序方向
  • DESC:降序排序
  • ASC:升序排序，默认

SELECT prod_name,prod_price
FROM Products
ORDER BY prod_price DESC;

第四章 过滤数据

4.1 使用WHERE子句

SELECT prod_name,prod_price
FROM Products
WHERE prod_price=3.9

4.2 WHERE子句操作符

• 等于 =；不等于 <>;不等于 ！=；不小于 ！<;不大于！>;BETWEEN AND 在指定的两个值之间；IS NULL 为空值

WHERE prod_price BETWEEN 5 AND 10;

第五章 高级数据过滤

5.1 组合WHERE子句

• AND操作符
• OR操作符
• IN操作符
  • IN操作符可以包含其他SELECT语句

WHERE vend_id IN ('DLL01','BRS01')

• NOT操作符

WHERE NOT vend_id='DLL01'

第六章 用通配符进行过滤

• % 通配符
  • %表示任何字符出现任意次数

WHERE prod_name LIKE 'Fish%'

• _ 通配符
  • _表示匹配单个字符
• 【】通配符
  • 【】 用来指定一个字符集

WHERE prod_name LIKE '[JM]%' --表示用J或M开头的单词
WHERE prod_name LIKE '[^JM]%' --表示不是用J或M开头的单词

第七章 创建计算字段

7.1 拼接字段

SELECT Concat(name,'(',country,')')

7.2 去掉数据中的空格

• RTRIM()函数

SELECT Concat(RTRIM(name),'(',country,')')

7.3 执行算术计算

select a*b

7.4 使用函数处理数据

7.4.1 文本处理函数

• 转大写：UPPER()
• 转小写：LOWER()
• 返回字符串左边的字符：LEFT()
• 返回字符串右边的字符：RIGHT()
• 返回字符串的长度：LEN()、LENGTH()、DATALENGTH()
• 去掉字符串右边的空格：RTRIM（）
• 提取字符串的组成部分：SUBSTR()或SUBSTRING()

7.4.2 数值处理函数

• 数的绝对值：ABS（）
• 角度的余弦值：COS（）
• 数的指数值：EXP（）
• 数的平方根：SQRT()

第九章 汇总数据

9.1 聚集函数

9.1.1 AVG()函数

• 通过对表中行数计数并计算其列值之和，求得该列的平均值
• 只用于单个列
• 忽略列值为NULL的行

SELECT AVG(prod_price) AS avg_price
FROM Products;

9.1.2 COUNT()函数

• 使用count(*)对表中行的数目进行计算，表列中的空值和非空值都计算在内。

SELECT COUNT(*) AS num_cust
FROM Customers

9.1.3 MAX()函数

• MAX()返回指定列中的最大值

SELECT MAX(prod_price) AS max_price
FROM Products;

9.1.4 MIN函数

• 返回指定列的最小值
• 该函数忽略列值为NULL的行

9.1.5 SUM函数

• 返回指定列值的和（总计）
• 也可以是多列的组合计算的求和
• SUM()函数忽略列值为NULL的行

SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num=2000

9.2 聚集不同值

• 对上面的5个聚集函数都可以如下使用：
  • 对所有行执行计算，指定ALL参数或不指定参数
  • 只包含不同的值，指定DISTINCT参数
• 应用
  • DISTINCT不能用于COUNT(*)，只能用于COUNT()
  • avg()函数

SELECT AVG(DISTINCT prod_price) AS avg_price

第十章 分组函数

10.1 创建分组

• GROUP BY子句
  • GROUP BY必须出现在WHERE子句之后，在ORDER BY之前

SELECT vend_id,count(*) AS num_prods
FROM Products
GROUP BY vend_id

10.2 过滤分组

• HAVING过滤分组

SELECT cust_id ,COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*)>=2;

10.3 排序

• ORDER BY
  • 一般给出GROUP BY的也应该给出ORDER BY

SELECT order_num,COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*)>=3
ORDER BY items,order_num

10.4 SELECT子句顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

第11章 使用子查询

11.1 使用子查询进行过滤

• 子查询总是从内向外处理
• 只能是查询单列

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id=123)

• 嵌套两个sql

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'
)
)

11.2 作为计算字段使用子查询

• 对于哪个表的哪个字段进行严格限定

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课 联结表

12.1 创建联结

• 指定要联结的表，以及关联它们的方式即可。

SELECT vend_name,prod_name,prod_price
FROM Vendors,Products
WHERE Vendors.vend_id=Products.vend_id

• 使用INNER JOIN的方式

SELECT vend_name,prod_name,prod_price
FROM Venders
INNER JOIN Products ON Vendors.vend_id=Products.vend_id;

第13课 创建高级联结

13.1 自联结

• 自己和自己联结

SELECT cust_id,cust_name,cust_contact
FROM Customers
WHERE cust_name=(SELECT cust_name FROM Customers WHERE cust_contact='Jim Jones');

另外一种相同效果的表：

SELECT c1.cust_id,c1.cust_name,c1.cust_contact
FROM Customers AS c1,Customers AS c2
WHERE c1.cust_name=c2.cust_name AND c2.cust_contact='Jim Jones';

13.2 自然联结

SELECT C.*,O.order_num,O.order_date,OI.prod_id,OI.quantity,OI.item_price
FROM Customers AS C,Orders AS O,OrderItems AS OI
WHERE C.cust_id=O.cust_id
AND OI.order_num=O.order_num
AND prod_id='RGAN01'

13.3 外联结

• 使用OUTER JOIN语法，必须使RIGHT或LEFT关键字指定包含其所有行的表

SELECT Customers.cust_id,Orders.order_num
FROM Customers
RIGHT OUTER JION Orders ON Customers.cust_id=Orders.cust_id;

• 全联结

SELECT Customers.cust_id,Orders.order_num
FROM Customers
FULL OUTER JOIN Orders on Customers.cust_id=Orders.cust_id;

13.4 使用带聚集函数的联结

SELECT Customers.cust_id,COUNT(Orders.order_num) AS num_ord
FROM Customers
INNER JOIN Orders ON Customers.cust_id=Orders.cust_id
GROUP BY Customers.cust_id;