例如:人 和 身份证 的关系 一个人只能对应一个身份证号
例如: 班级 和 学生 的关系 一个班级多个学生
例如:学生 和 课程 的关系 一个学生可以选择多个课程 一个课程可以被多个学生选择
案例:
创建学生表:学生 id ,姓名
create table student(
id int primary key auto_increment,
name varchar(20)
);
创建课程表:课程表,课程名
create table course(
courseId int primary key auto_increment,
courseName varchar(20)
);
创建关联表:学生和课程之间的关系,需要包含学生id 和课程id 作为外键。
create table student_course(
student_id int,
course_id int,
primary key (student_id, course_id),
foreign key (student_id) references student(id),
foreign key (course_id) references course(courseId)
);
常见的统计总数、计算平局值等操作,可以使用聚合函数来实现,常见的聚合函数有:
函数 | 说明 |
---|---|
COUNT([DISTINCT] expr) | 返回查询的数据的数量 |
SUM([DISTINCT] expr) | 返回查询到的数据的总和,忽略非数值 |
AVG([DISTINCT] expr) | 返回查询到的数据的平均值,忽略非数值 |
MAX([DISTINCT] expr) | 返回查询到的数据的最大值,忽略非数值 |
MIN([DISTINCT] expr) | 返回查询到的数据的最小值,忽略非数值 |
案例:
count:计数
-- 统计有多少位学生
select count(*) from student;
-- 统计学生表有多少个姓名,姓名为 NULL 不会计入结果
select count(name) from student;
sum:总和
-- 统计分数的总和
select sum(score) from score;
-- 统计分数小于70的总分,如果没有返回null
select sum(score) from score where score<70;
avg:平均值
-- 查询分数的平均值
select avg(score) from score;
-- 查询分数小于70的平均值,如果没有则返回 NULL
MAX:最大值
-- 查询分数的最大值
select max(score) from score;
-- 查询60到90之间的最大值
select max(score) from score where score>60 and score<90;
-- 查询大于90的最大值,如果没有则返回 NULL
select max(score) from score where score>90;
MIN:最小值
-- 查询分数的最小值
select min(score) from score;
-- 查询分数在60到90之间的最小值
select min(score) from score where score>60 and score<90;
-- 查询分数在60以下的最小值,如果没有则返回 NULL
select min(score) from score where score<60;
select
中使用 group by
子句可以对指定列进行分组查询。需要满足:使用 group by
进行分组查
询时,select
指定的字段必须是“分组依据字段”,其他字段若想出现在 select
中则必须包含在聚合函
数中。
select column1, sum(column2), .. from table group by column1,column3;
案例:
测试表:职工表 id,name(姓名),role(职位),salary(工资)
create table emp(id int, name varchar(20), role varchar(20), salary int);
insert into emp values (1,'张三','Java开发',10000);
insert into emp values (2,'李四','Java开发',9000);
insert into emp values (3,'王五','Web开发',8000);
insert into emp values (4,'赵六','Web开发',9000);
insert into emp values (5,'王麻子','运维',8500);
insert into emp values (6,'玛晕','老板',100000);
查询每个岗位的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;
group by
子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 where
语句,而需要用
having
。
查询平均工资低于9000的职位和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<9000;