《SQL必知必会》读书笔记(2)
《SQL必知必会》读书笔记(2)
函数
各个DBMS之函数实现差异较大，即SQL函数不可移植(unportable)。
常见的SQL函数功能：
| 类型 | 用途 |
|---|---|
| 文本函数 | 处理文本字符串（删除、填充、转大小写等） |
| 数值函数 | 在数值数据上进行算数操作（绝对值、代数运算） |
| 时间日期函数 | 处理日期和时间值等（计算日期之差，检查日期有效性等） |
| 系统函数 | 返回DBMS正在使用的特殊信息（如用户登录信息） |
文本处理函数
| 函数 | 说明 |
|---|---|
| LEFT() | 返回字符串左边字符 |
| LENGTH() | 返回字符串长度 |
| LOWER() | 转小写 |
| LTRIM() | 去掉左边空格 |
| RIGHT() | 返回字符串右边字符 |
| RTRIM() | 去掉右边空格 |
| SOUNDEX() | 返回字符串的SOUNDEX值 |
| UPPER() | 转大写 |
SOUNDEX是将文本串转为一个描述其发音的代码的函数。如Peterson将转为P362。
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green')
日期和时间处理函数
| 函数 | 说明 |
|---|---|
| AddDate() | 增加一个日期（天、周等） |
| AddTime() | 增加一个是时间（时、分等） |
| Now() | 返回当前日期和时间 |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Date() | 返回日期时间的日期部分 |
| DateDiff() | 计算两个日期之差 |
| Date_Add() | 高度灵活的日期运算函数 |
| Date_Format() | 返回一个格式化的日期或时间串 |
| DayOfWeek() | 对于一个日期，返回对应的星期几 |
| Time() | 返回一个日期时间的时间部分 |
| Year() | 返回一个日期的年份部分 |
| Month() | 返回一个日期的月份部分 |
| Day() | 返回一个日期的天数部分 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Second() | 返回一个时间的秒数部分 |
数值处理函数
| 函数 | 说明 |
|---|---|
| ABS() | 绝对值 |
| COS() | 余弦 |
| EXP() | 指数值 |
| PI() | 圆周率 |
| SIN() | 正弦 |
| SQRT() | 开平方根 |
| TAN() | 正切 |
汇总数据
- 聚集函数aggregate function
| 函数 | 说明 |
|---|---|
| AVG() | 平均值 |
| COUNT() | 某列的行数 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| SUM() | 和 |
SELECT AVG(prod_price) AS avg_price
FROM Products;
COUNT(*)包括空值
分组数据
创建分组
GROUP BY子句,必须在WHERE之后，ORDER BY之前
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
过滤分组
HAVING子句,用于过滤分组。
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) > 2;
子查询
- 查询query
- 子查询subquery
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'D1')
- 子查询总是从内到外处理
联结表
- 联结join
- 可伸缩scale
- 笛卡尔积cartesian product
- 叉联结cross join
- 等值联结equijoin
- 内联结inner join
- 自联结self-join
- 自然联结natural join
- 外联结outer join
创建联结
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
内联结
INNER JOIN xxx ON xxxx
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
表别名
SELECT cust_name, cust_contact
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 = 'D1'
- 自联结比子查询要快，因此应优先用联结
组合查询
UNION关键字
SELECT cust_name, cust_contact, cust_email
FROM Custmers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Custmers
WHERE cust_name = 'a'
等同于
SELECT cust_name, cust_contact, cust_email
FROM Custmers
WHERE cust_state IN ('IL', 'IN', 'MI')
OR cust_name = 'a'
- 每个查询必须包含相同的列
- 类型不必完全相同，但必须可以兼容（转换）
UNION会默认去重，如果想保留，可以用UNION ALL- 只能用一个
ORDER BY，即最后
