数据库：聚合函数和联合查询的知识整理以及举例

【聚合函数】
COUT  数据的数量
SUM    数据求和
AVG     求平均数
MIN    最小数
MAX    最大数

 

create table emp(
    id int primary key auto_increment,
    name varchar(20) not null,
    role varchar(20) not null,
    salary numeric(11,2)
);

insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);

// select count(*) from emp;    计算总行数
//select sum(salary) from emp;   计算总的salary
//select max(salary) from emp;   最大数
//select mix(salary) from emp;    最小salary
[注意]：
select max(salary),name from emp;   会报错，因为max(salary)只有一行，而name 有六行，所以无法显示

group by 分组查询

select max(salary),role from emp group by role;  这样就不会报错

select max(salary),role,name from emp group by role;  还是会报错，因为group by role 之后会有四行，但是name有六行，所以啊还是无法显示

分组后的条件查询使用having:
//查询角色分组后，salary>2000的角色
select sum(salary),role from emp group by role having sum(salary)>2000;

【联合查询】（内连接和外连接都属于多表查询）
【内连接】：
  （语法1）：
  select * from classes,student where classes.id=1;   //班级id为1所以学生数据
  select cls.name classes_name,stu.name student_name from classes cls,student stu where cls.id=1;  //班级id为1的所有同学
  select cls.name classes_name,stu.name student_name from classes cls,student stu where cls.id=stu.classes_id and cls.id=1;  //班级id为1的所有同学
  //cls和stu分别为classes和student 的别名  ，   classes_name和student_name 分别为列的别名

   （语法2）：
  select cls.name classes_name,stu.name student_name from classes cls join student stu on cls.id=stu.id where cls.id=1;   //班级id为1的所有同学
  等同于语法1中的第二条，把，换成了join,还加了on..主键和外键的关系

 select classes.name,student.name from classes,student where classes.id=student.classes_id;   //查询的是每个学生的班级分布

 student和course 多对多， score是中间表

  
【外连接】
【自连接】
【子查询】:
  指嵌入在其他sql语句中的select语句，也叫嵌套查询
  1、单行子查询：
   例如：查询于“不想毕业”同学的同班同学
   select * from student where classes_id =(select casses_id from student where name='不想毕业')；
 2、多行查询
  例如：查询语文或英文课程的成绩信息
   (1)in查询
  select * from score sco join course cou on sco.course_id=cou.id where cou.name='英文' or cou.name='语文';  （不用子查询方法查询） 
  select * from score sco join course cou on sco.course_id=cou.id where cou.name in('英文','语文')； （子查询法 ）
  select  * from score where course_id in(select id from course where name='语文' or name='英文');                    (in)
  select * from score where course_id  not in(select id from course where name!='语文' and name!='英文');        (not in)  
  (2)exists查询
 select * from score sco where exists(select * from course cou where cou.id=sco.course_id and(cou.name='语文' or cou.name='英文'));                (exists)
 select * from score sco where not exists(select * from course cou where cou.id=sco.course_id and(cou.name=!'语文' and cou.name!='英文'));     (not exists) 
 
【合并查询】:
  为了合并多个select执行结果，可以使用操作符union,union all
 例如：查询id=1或3的成绩信息
 select * from score where id=1 union(或union all) select * from score where id=2; 
 union和union all的区别：
   union 可以自动去除重复部分，union all不行

 

select * from student,score;  //笛卡尔积  （用一张表的每一行去遍历另一张表的每一行）
简易为：
 //显示每个同学的每门课程成绩及课程id
（语法一）
select sco.score,stu.name,sco.course_id from score sco,student stu where sco.student_id=stu.id;   
（语法二）
select sco.score,stu.name,sco.course_id from score sco join student stu on sco.student_id=stu.id;

//查询许仙同学的所有成绩
select sco.score,stu.name,sco.course_id from score sco join student stu on sco.student_id=stu.id where stu.name='许仙'；

//查询英语成绩在60以上的同学
  (关联两张表)
select * from score sco,course cou where sco.course_id=cou.id and cou.name='英文' and sco.score>=60;
select * from score sco join course cou on sco.course_id=cou.id where cou.name='英文' and sco.score>=60;
（关联三张表）
select stu.name student_name,cou.name course_name,sco.score from score sco,student stu,course cou where sco.course_id=cou.id and sco.student_id=stu.id 
and cou.name='英文' and sco.score>=60;

//查询英语成绩在60以上的同学的英文分数总和

select sum(sco.score) from score sco join course cou on sco.course_id=cou.id where cou.name='英文' and sco.score>=60;

//查询平均成绩及格（>=60分）的信息   （要分组）