97.创建表结构
# 01.创建表
MySQL> create database tomdb charset utf8;
MySQL> use tomdb;
1
2
2
# 1.1 表MUL
# 1.2 创建表
# --学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
# --课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
# --教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
# --成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
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
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
# 02.创建表
# 2.0 创建数据展示
# 2.1 学生表
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 2.2 课程表
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
1
2
3
4
2
3
4
# 2.3 教师表
--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
1
2
3
4
2
3
4
# 2.4 成绩表
--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 03.查询
# 3.1 链表查询
SELECT * from Student as a LEFT JOIN Score as b on a.s_id=b.s_id;
SELECT * from Student as a RIGHT JOIN Score as b on a.s_id=b.s_id;
SELECT * from Student as a INNER JOIN Score as b on a.s_id=b.s_id;
1
2
3
2
3
# 3.2 按姓名分组
MySQL> # SELECT name, COUNT(*) FROM user GROUP BY name;
+------+----------+
| name | COUNT(*) |
+------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+------+----------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 3.3 with rollup统计登录次数
- 按姓名进行分组，再统计每个人登录的次数：
with rollup
MySQL># SELECT name, SUM(singin) as singin_count FROM user GROUP BY name WITH ROLLUP;
+------+--------------+
| name | singin_count |
+------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL 16 |
+------+--------------+
4 rows in set (0.00 sec)
# WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
# 按姓名进行分组，再统计每个人登录的次数，其中记录 NULL 表示所有人的登录次数。
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 3.4 coalesce统计登录次数
- 按姓名进行分组，再统计每个人登录的次数：coalesce 来设置一个可以取代 NUll 的名称
MySQL># SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM user GROUP BY name WITH ROLLUP;
+------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+------------------------+--------------+
4 rows in set (0.00 sec)
#1、我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：
#2、select coalesce(a,b,c);
#3、参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
# 3.5 过滤分组HAVING
- 过滤分组HAVING： 查找登录次数大于2的所有用户
MySQL> SELECT name, SUM(singin) as singin_count FROM user GROUP BY name HAVING COUNT(*) >= 2;
+------+--------------+
| name | singin_count |
+------+--------------+
| 小明 | 7 |
| 小王 | 7 |
+------+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 3.6 按学生分组求各科平均成绩
MySQL> select *,avg(score) from student group by name;
+----+----------+-------+---------+------------+
| id | name | score | course | avg(score) |
+----+----------+-------+---------+------------+
| 4 | lisi | 88 | math | 88.0000 |
| 1 | zhangsan | 88 | english | 93.0000 |
+----+----------+-------+----- ----+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 3.7 时间过滤
- 10天前注册的用户中同名用户大于或等于2的数据
MySQL># select *,count(name) from user where datediff(NOW(),date)>10 group by name having count(name)>=2;
+----+------+---------------------+--------+-------------+
| id | name | date | singin | count(name) |
+----+------+---------------------+--------+-------------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 | 3 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 | 2 |
+----+------+---------------------+--------+-------------+
2 rows in set (0.00 sec)
# 原题：请查找商品表中最近30天至少有20天都有销售记录的商品
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
上次更新: 2024/10/15 16:27:13