初步接触 DBMS: 不就是存个数据吗, 有什么难的. 增删改查,
我上我也行.
进一步了解 DBMS: 好像有点东西(全然余裕).
看完一本 DBMS 的书后: B+树索引结构, 关系模型和外键约束,
连接优化、投影消除...
记这么多DML作甚? 实际还不是用的GUI.
涉及复杂业务逻辑的时候, 写SQL语句照样烧脑. 设计表的时候还不是照样头疼.
不考虑优化, 结果性能巨差有谁来负责. 没个 Cheatsheet
还真没一下想不出来.
检索数据
基础操作: SELECT
- 所有的 SQL 语句用
; 结尾.
- SQL 关键字大写, 变量名小写是好习惯.
- 结果是未排序的.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SELECT course_id FROM courses;
SELECT course_id, course_name FROM courses;
SELECT * FROM courses;
SELECT DISTINCT course_id FROM courses;
SELECT course_id FROM courses
LIMIT 5;
SELECT courses.course_id FROM courses;
|
排序检索: ORDER BY
SQL 语句由子句(clause)构成, 有些子句是必须的, 有些则不是.
ORDER BY 字句
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT course_name FROM courses
ORDER BY course_name;
SELECT course_id, course_name FROM courses
ORDER BY course_id, course_name;
SELECT course_id, course_name FROM courses
ORDER BY course_id DESC, course_name;
|
数据过滤: WHERE
- 通常数据过滤在服务器而不是在客户端解决. 想想为什么?
ORDER BY 得放在 WHERE 后面.- 使用圆括号分组操作符是个好习惯.
- MySQL 里的
NOT 和其他 SQL 语句略有不同.
NOT IN, NOT BETWEEN 也支持.
= |
等于 |
<> |
不等于 |
!= |
不等于 |
< |
小于 |
<= |
小于等于 |
> |
大于 |
>= |
大于等于 |
BETWEEN AND |
在指定的两个值之间 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;
SELECT prod_name FROM products
WHERE prod_price IS NULL;
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003) AND prod_price >= 10;
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;
|
通配符和 regex
- 通配符(wildcard)用来匹配值的一部分的特殊字符.
- 搜索模式(search pattern)由字面值(literal),
通配符或两者组合构成的搜索条件.
- 通配符就是 WHERE 字句中具有特殊含义的字符.
% 匹配不了 NULL.- 不要过度使用通配符, 注意通配符位置.
- MySQL 只实现 REGEX 里的很小一块子集.
LIKE 和 REGEXP的区别:
完全匹配整个列和部分匹配.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';
SELECT prod_name, prod_price
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
SELECT prod_id, prod_name
FROM products
WHERE prod_name REGEXP 'JetPack .000';
|
计算字段
- 字段(field)基本上就是列的意思.
- 拼接字段得到一个导出列(derived column)
AS 关键字: 别名(alias)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SELECT Concat(vend_name, ' (', 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 = 20005;
SELECT 3 * 2
SELECT Trim('abc')
SELECT Now()
|
数据处理函数
文本处理函数:
| CONCAT |
用于连接多个字符串 |
| UPPER |
将字符串转换为大写 |
| LOWER |
将字符串转换为小写 |
| SUBSTRING |
从字符串中提取子字符串 |
| LENGTH |
返回字符串的长度 |
| TRIM |
去除字符串首尾的空格 |
| LTRIM |
去除字符串开头的空格 |
| RTRIM |
去除字符串末尾的空格 |
| LEFT |
返回字符串左边指定长度的子字符串 |
| RIGHT |
返回字符串右边指定长度的子字符串 |
| REPLACE |
替换字符串中的指定子字符串 |
| CHARINDEX |
返回指定子字符串在字符串中第一次出现的位置 |
| REVERSE |
反转字符串 |
| FORMAT |
将数值或日期格式化为指定的字符串表示 |
| COALESCE |
返回第一个非空表达式的值 |
| CASE |
条件语句，用于在结果中根据条件返回不同的值 |
| REGEXP_REPLACE |
使用正则表达式替换字符串中的内容 |
| TRANSLATE |
替换字符串中的指定字符 |
| SOUNDEX |
返回字符串的 SOUNDEX 码，用于模糊匹配字符串的发音 |
| CONCAT_WS |
用指定的分隔符连接多个字符串，并去除空字符串 |
| INITCAP |
将字符串的首字母大写 |
| STRPOS |
返回指定子字符串在字符串中第一次出现的位置 |
| LEFTPAD |
在字符串的左侧填充指定字符 |
| RIGHTPAD |
在字符串的右侧填充指定字符 |
日期函数:
| AddDate |
增加一个日期（天、周等） |
| AddTime |
增加一个时间（时、分等） |
| CurDate |
返回当前日期 |
| CurTime |
返回当前时间 |
| Date |
返回日期时间的日期部分 |
| DateDiff |
计算两个日期之差 |
| Date_Add |
高度灵活的日期运算函数 |
| Date_Format |
返回一个格式化的日期或时间串 |
| Day |
返回一个日期的天数部分 |
| DayOfWeek |
对于一个日期，返回对应的星期几 |
| Hour |
返回一个时间的小时部分 |
| Minute |
返回一个时间的分钟部分 |
| Month |
返回一个日期的月份部分 |
| Now |
返回当前日期和时间 |
| Second |
返回一个时间的秒部分 |
| Time |
返回一个日期时间的时间部分 |
| Year |
返回一个日期的年份部分 |
不同的 DBMS 对函数的实现和语法有些区别. 这么多谁记得住.
建议看文档.
1
2
3
4
|
SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
|
汇总数据: 聚集函数
- 汇总数据严格来说不是检索数据.
- 聚集函数(aggregate function): 运行在行组上,
计算和返回单个值的函数.
COUNT(*): 行的值为空依然计数
COUNT(column): column 有值的计数MAX() 要求指定列名ALL 默认 DISTINCT 只包含不同的值- 可以组合聚集函数.
聚合函数:
| AVG() |
返回某列的平均值 |
| COUNT() |
返回某列的行数 |
| MAX() |
返回某列的最大值 |
| MIN() |
返回某列的最小值 |
| SUM() |
返回某列值之和 |
1
2
3
4
5
6
7
8
9
|
SELECT MAX(column_name) AS max_value
FROM table_name;
SELECT column_name FROM table_name
ORDER BY column_name
DESC LIMIT 1;
|
分组数据: GROUP BY 和 HAVING
GROUP BY(创建分组) 和
HAVING(过滤分组)GROUP BY 支持多级分组.
数据将在最后规定的分组上进行汇总.GROUP BY 在 WHERE 后,
ORDER BY 前WHERE 过滤行, 而 HAVING
过滤分组(两者很相似, 操作符通用)- 可以这么想:
WHERE 在分组前过滤, HAVING
在分组后过滤.
- 而且
HAVING 可以和 WHERE 混着用
| 输出 |
按指定列排序的结果 |
分组后的结果，但输出可能不按照分组的顺序 |
| 列的使用 |
任意列，甚至非选择的列也可以使用 |
只能使用选择列或表达式列，必须使用每个选择列表达式 |
| 必要性 |
不一定需要 |
如果与聚集函数一起使用列（或表达式），则必须使用 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
SELECT customer_id, order_date, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id, order_date;
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
|
在SQL查询中, SELECT 子句的一般顺序如下:
SELECT：指定要检索的列或表达式。FROM：指定要从中检索数据的表或视图。WHERE：应用条件来筛选满足特定条件的行。GROUP BY：根据指定的列或表达式对结果进行分组。HAVING：筛选分组后的结果集。ORDER BY：指定结果集的排序顺序。LIMIT /
OFFSET（在某些数据库中可用）：限制返回的行数或指定结果集的偏移量。
使用子查询
- 子查询(subquery): 查找一遍又一遍
- 子查询总是从内向外处理.
- 相关子查询(correlated subquery): 涉及外部查询的子查询.
- 逐渐增加子查询来建立查询. 也就是先分成多部查询,
确定正常后再写进子查询.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2');
SELECT cust_name,
cust_state,
(SELECT COUNT(*) FROM orders
WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name
|