/ mysql  

MySQL SELECT

MySQL学习笔记(Day011:SELECT)

@(MySQL学习)

[TOC]

一. SELECT语法介绍

SELECT语法官方文档

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
-- -------------------------不推荐使用--------------------------
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[MAX_STATEMENT_TIME = N]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
-- -------------------------------------------------------------
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

二. LIMIT 和 ORDER BY

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
mysql> select * from employees limit 1;  -- 从employees中 随机 取出一条数据,结果是不确定的
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

--
-- order by col_name 根据某列的值进行排序
-- asc : 升序(default)
-- desc: 降序
--

mysql> select * from employees order by emp_no asc limit 1; -- 使用order by col_name asc进行升序排序
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

mysql> select * from employees order by emp_no limit 1; -- 默认就是升序的
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | -- 结果和上面一致
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

mysql> select * from employees order by emp_no desc limit 1; -- desc表示降序
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 499999 | 1958-05-01 | Sachin | Tsukuda | M | 1997-11-30 | -- 降序显示
+--------+------------+------------+-----------+--------+------------+
-- 通过order by排序后 limit 1 才是确定的
1 row in set (0.00 sec)

mysql> show create table employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`) -- emp_no 是主键,order by 主键 不会创建临时表的,主键(索引)本身有序
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from employees order by emp_no asc limit 5,5; -- limit start, offset
-- 从第5条 开始取,取5条出来
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)

-- 以上这个语法有一种分页的效果,但是会随着start的增加,性能会下降,因为会扫描表(从 1 到 start)

-- 相对比较推荐的方法
mysql> select * from employees where emp_no > 20000 order by emp_no limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 20001 | 1962-05-16 | Atreye | Eppinger | M | 1990-04-18 |
| 20002 | 1955-12-25 | Jaber | Brender | M | 1988-01-26 |
| 20003 | 1953-04-11 | Munehiko | Coors | F | 1991-02-07 |
| 20004 | 1952-03-07 | Radoslaw | Pfau | M | 1995-11-24 |
| 20005 | 1956-02-20 | Licheng | Przulj | M | 1992-07-17 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)
-- (当然推荐把热数据放cache里,比如Redis)

ORDER BY 是把已经查询好的结果集进行排序

三. WHERE

WHERE是将查询出来的结果,通过WHERE后面的条件(condition),对结果进行过滤

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
mysql> select * from employees  where emp_no > 30000  emp_no limit 4; -- 不加order by的limit是不确定的SQL
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 30001 | 1953-03-27 | Izaskun | Morton | M | 1988-05-21 |
| 30002 | 1960-08-23 | Branimir | Snedden | M | 1998-09-24 |
| 30003 | 1952-11-25 | Takahito | Vilarrasa | M | 1990-08-22 |
| 30004 | 1957-11-26 | Lucian | Penttonen | F | 1992-10-08 |
+--------+------------+------------+-----------+--------+------------+
4 rows in set (0.00 sec)

mysql> select * from employees where emp_no > 40000 order by emp_no limit 4;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 40001 | 1956-03-28 | Akemi | Maliniak | F | 1987-08-06 |
| 40002 | 1960-03-15 | Nakhoon | Badr | M | 1990-02-13 |
| 40003 | 1960-01-26 | Jacopo | Marshall | F | 1991-09-30 |
| 40004 | 1955-09-09 | Anneke | Stiles | F | 1986-03-05 |
+--------+------------+------------+-----------+--------+------------+
4 rows in set (0.02 sec)

mysql> select * from employees
-> where emp_no > 40000
-> and hire_date > '1991-01-01' -- 可以用 and 进行 逻辑与
-> order by emp_no limit 4;
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+------------+--------+------------+
| 40003 | 1960-01-26 | Jacopo | Marshall | F | 1991-09-30 |
| 40005 | 1961-02-27 | Zsolt | Fairtlough | F | 1991-07-08 |
| 40012 | 1955-02-07 | Chinhyun | Ozeri | F | 1995-08-12 |
| 40015 | 1964-10-08 | Ioana | Lemarechal | M | 1997-08-07 |
+--------+------------+------------+------------+--------+------------+
4 rows in set (0.00 sec)

mysql> select * from employees
-> where (emp_no > 40000 and birth_date > '1961-01-01') -- 使用()明确条件的逻辑规则
-> or (emp_no > 40000 and hire_date > '1991-01-01') -- 可以使用 or 做 逻辑或
-> order by emp_no limit 5;
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+------------+--------+------------+
| 40003 | 1960-01-26 | Jacopo | Marshall | F | 1991-09-30 |
| 40005 | 1961-02-27 | Zsolt | Fairtlough | F | 1991-07-08 |
| 40006 | 1962-11-07 | Basim | Panienski | F | 1986-12-27 |
| 40012 | 1955-02-07 | Chinhyun | Ozeri | F | 1995-08-12 |
| 40015 | 1964-10-08 | Ioana | Lemarechal | M | 1997-08-07 |
+--------+------------+------------+------------+--------+------------+
5 rows in set (0.00 sec)

四. JOIN

Alt text

4.1. INNER JOIN

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
--
-- ANSI SQL 89
-- 关联employees表和titles表
-- 要求是 employees的emp_no 等于 titles的emp_no
--
mysql> select * from employees,titles where employees.emp_no = titles.emp_no limit 5;
+--------+------------+------------+-----------+--------+------------+--------+-----------------+------------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date | emp_no | title | from_date | to_date |
+--------+------------+------------+-----------+--------+------------+--------+-----------------+------------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | 10002 | Staff | 1996-08-03 | 9999-01-01 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | 10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | 10004 | Engineer | 1986-12-01 | 1995-12-01 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | 10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
+--------+------------+------------+-----------+--------+------------+--------+-----------------+------------+------------+
5 rows in set (0.00 sec)

--
-- 在上面的基础上只显示emp_no,名字,性别和职位名称
--
mysql> select emp_no, concat(last_name,' ', first_name), gender, title
-> from employees,titles
-> where employees.emp_no = titles.emp_no limit 5;
ERROR 1052 (23000): Column 'emp_no' in field list is ambiguous -- 报错了,原因是emp_no两个表都有

mysql> select employees.emp_no, -- 指定了employees
-> concat(last_name,' ', first_name), gender, title
-> from employees,titles
-> where employees.emp_no = titles.emp_no limit 5;
+--------+-----------------------------------+--------+-----------------+
| emp_no | concat(last_name,' ', first_name) | gender | title |
+--------+-----------------------------------+--------+-----------------+
| 10001 | Facello Georgi | M | Senior Engineer |
| 10002 | Simmel Bezalel | F | Staff |
| 10003 | Bamford Parto | M | Senior Engineer |
| 10004 | Koblick Chirstian | M | Engineer |
| 10004 | Koblick Chirstian | M | Senior Engineer |
+--------+-----------------------------------+--------+-----------------+

mysql> select employees.emp_no,
-> concat(last_name,' ', first_name) as emp_name, gender, title -- 对名字的列取一个别名叫emp_name
-> from employees,titles
-> where employees.emp_no = titles.emp_no limit 5;
+--------+-------------------+--------+-----------------+
| emp_no | emp_name | gender | title | -- 这里就显示了emp_name
+--------+-------------------+--------+-----------------+
| 10001 | Facello Georgi | M | Senior Engineer |
| 10002 | Simmel Bezalel | F | Staff |
| 10003 | Bamford Parto | M | Senior Engineer |
| 10004 | Koblick Chirstian | M | Engineer |
| 10004 | Koblick Chirstian | M | Senior Engineer |
+--------+-------------------+--------+-----------------+
5 rows in set (0.00 sec)

mysql> select e.emp_no, -- 使用表的别名
-> concat(last_name,' ', first_name) as emp_name, gender, title
-> from employees as e,titles as t -- 对表做别名
-> where e.emp_no = t.emp_no limit 5; -- 使用报表的别名
+--------+-------------------+--------+-----------------+
| emp_no | emp_name | gender | title |
+--------+-------------------+--------+-----------------+
| 10001 | Facello Georgi | M | Senior Engineer |
| 10002 | Simmel Bezalel | F | Staff |
| 10003 | Bamford Parto | M | Senior Engineer |
| 10004 | Koblick Chirstian | M | Engineer |
| 10004 | Koblick Chirstian | M | Senior Engineer |
+--------+-------------------+--------+-----------------+
5 rows in set (0.00 sec)

--
-- ANSI SQL 92
-- inner join ... on ...语法
--
mysql> select e.emp_no,
-> concat(last_name,' ', first_name) as emp_name, gender, title
-> from employees as e inner join titles as t -- inner join 可以省略inner关键字
-> on e.emp_no = t.emp_no limit 5; -- 配合join使用on
+--------+-------------------+--------+-----------------+
| emp_no | emp_name | gender | title |
+--------+-------------------+--------+-----------------+
| 10001 | Facello Georgi | M | Senior Engineer |
| 10002 | Simmel Bezalel | F | Staff |
| 10003 | Bamford Parto | M | Senior Engineer |
| 10004 | Koblick Chirstian | M | Engineer |
| 10004 | Koblick Chirstian | M | Senior Engineer |
+--------+-------------------+--------+-----------------+
5 rows in set (0.00 sec)

--
-- 上面两种语句在效率上其实是一样的,只是语法上的区别
--
--- 第一种
mysql> explain select e.emp_no,
-> concat(last_name,' ', first_name) as emp_name, gender, title
-> from employees as e,titles as t
-> where e.emp_no = t.emp_no limit 5;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
| 1 | SIMPLE | e | NULL | ALL | PRIMARY | NULL | NULL | NULL | 298124 | 100.00 | NULL |
| 1 | SIMPLE | t | NULL | ref | PRIMARY | PRIMARY | 4 | employees.e.emp_no | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

--- 第二种
mysql> explain select e.emp_no,
-> concat(last_name,' ', first_name) as emp_name, gender, title
-> from employees as e inner join titles as t
-> on e.emp_no = t.emp_no limit 5;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
| 1 | SIMPLE | e | NULL | ALL | PRIMARY | NULL | NULL | NULL | 298124 | 100.00 | NULL |
| 1 | SIMPLE | t | NULL | ref | PRIMARY | PRIMARY | 4 | employees.e.emp_no | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

-- 通过explain看两条语句的执行计划,发现是一样的,所以性能上是一样的,只是语法的不同

4.2. OUTER JOIN

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
--
-- 左连接 left join
--
mysql> use burn_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test_left_join_1(a int);
Query OK, 0 rows affected (0.16 sec)

mysql> create table test_left_join_2(b int);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into test_left_join_1 values (1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into test_left_join_1 values (2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into test_left_join_2 values (1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_left_join_1;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from test_left_join_2;
+------+
| b |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> select * from
-> test_left_join_1 as t1
-> left join -- 使用left join
-> test_left_join_2 as t2
-> on t1.a = t2.b;
+------+------+
| a | b |
+------+------+
| 1 | 1 | -- 满足条件的,显示t2中该条记录的值
| 2 | NULL | -- 不满足条件的,用NULL填充
+------+------+
2 rows in set (0.00 sec)
-- left join : 左表 left join 右表 on 条件;
-- 左表全部显示,右表是匹配表,
-- 如果右表的某条记录满足 [on 条件] 匹配,则该记录显示
-- 如果右表的某条记录 不 满足 匹配,则该记录显示NULL

--
-- 右连接 right join (继续使用test_left_join_1和2两张表)
--
mysql> select * from
-> test_left_join_1 as t1
-> right join -- 使用right join
-> test_left_join_2 as t2
-> on t1.a = t2.b;
+------+------+
| a | b |
+------+------+
| 1 | 1 | -- 右表(t2)全部显示
+------+------+
1 row in set (0.00 sec)
-- right join : 左表 right join 右表 on 条件
-- 右表全部显示,左边是匹配表
-- 同样和left join,满足则显示,不满足且右表中有值,则填充NULL

mysql> insert into test_left_join_2 values (3); -- t2 中再增加一条记录
Query OK, 1 row affected (0.03 sec)

mysql> select * from
-> test_left_join_1 as t1
-> right join
-> test_left_join_2 as t2
-> on t1.a = t2.b;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| NULL | 3 | -- 右表存在,左表没有,用NULL填充
+------+------+
2 rows in set (0.00 sec)

--
-- 查找在t1表,而不在t2表的数据
--
mysql> select * from
-> test_left_join_1 as t1
-> left join
-> test_left_join_2 as t2
-> on t1.a = t2.b where t2.b is null;
+------+------+
| a | b |
+------+------+
| 2 | NULL | -- 数据1 在t1和t2中都有,所以不显示
+------+------+
1 row in set (0.00 sec)


-- left join : left outer join , outer关键字可以省略
-- right join: right outer join , outer 关键字可以省略

-- join无论inner还是outer,列名不需要一样,甚至列的类型也可以不一样,会进行转换。
-- 一般情况下,表设计合理,需要关联的字段类型应该是一样的


--
-- 查找哪些员工不是经理
--
mysql> select e.emp_no,
-> concat(last_name,' ', first_name) as emp_name, gender, d.dept_no
-> from employees as e left join dept_manager as d
-> on e.emp_no = d.emp_no
-> where d.emp_no is null limit 5;
+--------+-------------------+--------+---------+
| emp_no | emp_name | gender | dept_no | -- dept_no是dept_manager的字段
+--------+-------------------+--------+---------+
| 10001 | Facello Georgi | M | NULL |
| 10002 | Simmel Bezalel | F | NULL |
| 10003 | Bamford Parto | M | NULL |
| 10004 | Koblick Chirstian | M | NULL |
| 10005 | Maliniak Kyoichi | M | NULL |
+--------+-------------------+--------+---------+
5 rows in set (0.00 sec)

-- 在 inner join中,过滤条件放在where或者on中都是可以的
-- 在 outer join中 条件放在where和on中是不一样的
mysql> select * from
-> test_left_join_1 as t1
-> left join
-> test_left_join_2 as t2
-> on t1.a = t2.b
-> where t2.b is null;
+------+------+
| a | b |
+------+------+
| 2 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> select * from
-> test_left_join_1 as t1
-> left join
-> test_left_join_2 as t2
-> on t1.a = t2.b
-> and t2.b is null; -- 除了a=b, 还要找到b=null的,但是b里面没有null,所有a全部显示,b全为null
+------+------+
| a | b |
+------+------+
| 1 | NULL |
| 2 | NULL |
+------+------+
2 rows in set (0.00 sec)

-- ON 参与outer join的结果的生成,而where只是对结果的一个过滤

--
-- 作业:查处普通员工的title,部门名称,薪资
--

4.3. GROUP BY

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
--
-- 找出同一个部门的员工数量
--
mysql> select dept_no, count(dept_no) -- count是得到数量,这里就是分组函数
-> from dept_emp
-> group by dept_no; -- 通过 dept_no 分组
+---------+----------------+
| dept_no | count(dept_no) |
+---------+----------------+
| d001 | 20211 |
| d002 | 17346 |
| d003 | 17786 |
| d004 | 73485 |
| d005 | 85707 |
| d006 | 20117 |
| d007 | 52245 |
| d008 | 21126 |
| d009 | 23580 |
+---------+----------------+
9 rows in set (0.10 sec)

--
-- 选出部门人数 > 50000
--
mysql> select dept_no, count(dept_no)
-> from dept_emp
-> group by dept_no
-> having count(dept_no) > 50000; -- 如果是对分组的聚合函数做过滤,使用having,用where报语法错误
+---------+----------------+
| dept_no | count(dept_no) |
+---------+----------------+
| d004 | 73485 |
| d005 | 85707 |
| d007 | 52245 |
+---------+----------------+
3 rows in set (0.09 sec)

--
-- 每个用户每个月产生的订单数目
--
mysql> desc orders;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| o_orderkey | int(11) | NO | PRI | NULL | | -- 订单ID
| o_custkey | int(11) | YES | MUL | NULL | | -- 客户ID
| o_orderstatus | char(1) | YES | | NULL | |
| o_totalprice | double | YES | | NULL | |
| o_orderDATE | date | YES | MUL | NULL | | -- 订单日期
| o_orderpriority | char(15) | YES | | NULL | |
| o_clerk | char(15) | YES | | NULL | |
| o_shippriority | int(11) | YES | | NULL | |
| o_comment | varchar(79) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql> select o_orderkey, o_custkey, o_orderDATE from orders limit 3;
+------------+-----------+-------------+
| o_orderkey | o_custkey | o_orderDATE |
+------------+-----------+-------------+
| 1 | 36901 | 1996-01-02 |
| 2 | 78002 | 1996-12-01 |
| 3 | 123314 | 1993-10-14 |
+------------+-----------+-------------+
3 rows in set (0.00 sec)


--
-- 查找客户每年每月产生的订单数
--

mysql> select o_custkey, count(o_orderkey), -> year(o_orderDATE), month(o_orderDATE)
-> from orders
-> group by o_custkey, year(o_orderDATE), month(o_orderDATE)
-> limit 10;
+-----------+-------------------+-------------------+--------------------+
| o_custkey | count(o_orderkey) | year(o_orderDATE) | month(o_orderDATE) |
+-----------+-------------------+-------------------+--------------------+
| 1 | 1 | 1992 | 4 |
| 1 | 1 | 1992 | 8 |
| 1 | 1 | 1996 | 6 |
| 1 | 1 | 1996 | 7 |
| 1 | 1 | 1996 | 12 |
| 1 | 1 | 1997 | 3 |
| 2 | 1 | 1992 | 4 |
| 2 | 1 | 1994 | 5 |
| 2 | 1 | 1994 | 8 |
| 2 | 1 | 1994 | 12 |
+-----------+-------------------+-------------------+--------------------+
10 rows in set (8.97 sec)

-- 使用 date_format 函数

mysql> select o_custkey, count(o_orderkey),
-> date_format(o_orderDATE, '%Y-%m') as date
-> from orders
-> group by o_custkey, date_format(o_orderDATE, '%Y-%m')
-> limit 10;
+-----------+-------------------+---------+
| o_custkey | count(o_orderkey) | date |
+-----------+-------------------+---------+
| 1 | 1 | 1992-04 |
| 1 | 1 | 1992-08 |
| 1 | 1 | 1996-06 |
| 1 | 1 | 1996-07 |
| 1 | 1 | 1996-12 |
| 1 | 1 | 1997-03 |
| 2 | 1 | 1992-04 |
| 2 | 1 | 1994-05 |
| 2 | 1 | 1994-08 |
| 2 | 1 | 1994-12 |
+-----------+-------------------+---------+
10 rows in set (11.46 sec)


-- 作业:查找客户每周(以年,月,周 显示)产生的订单量