mysql基本应用命令（三）

聚集函数的使用

查询学生总数

select count(*) from student;

查询选修了课程的学生(distinct 去掉重复值)

select count(distinct sno) from sc;

计算C01号课程的学生平均成绩

select avg(dagree) from sc where cno='c01';

查询选修了C01号课程的学生的最高分和最低分

select max(degree) 最高分，min(degree) 最低分 from sc where cno='c01';

查询学号为‘20050102’的学生的总成绩集平均成绩

select sum(degree) as 总成绩,avg(degree) as 平均成绩 from sc where sno='20050102';

查询有考试成绩的学生人数

select count(didtinct sno) from sc where degree is not null;

分组和排序

1、对查询结果集进行分组

统计各系学生人数(group by 子句将查询介个按照一列或多列数值进行分类)

select sdept,count(*) 各系人数 from student group by sdept;

统计student表中男生人数、女生人数

select ssex,count(*) 人数 from student group by ssex;

统计各系男生人数、女生人数

select sdept,ssex,count(*) 各系人数 from student group by sdept,ssex;

统计各系女生人数(where作用于表，having作用于结果组)

select sdept,ssex,count(*) 各系女生人数 from student where ssex='女' group by sdept;
select sdept,ssex,count(*) from student group by sdept,ssex having count(*)>=3;

查询选修了3门以上 课程的学生的学生学号

select sno from sc group by sno having count(*)>=3;

2、对查询结果集进行排序

查询选修了C03号课程的学生的学号和成绩，结果按分数的降序排序

select *from sc where cno='c03' order by degree desc;

查询全体学生，结果按所在系升序排序，同一系的学生按生日降序排列

select *from student order by sdept asc,sbirthday desc;

多表连接查询

交叉查询

select a.*,b.* from course a,sc b;

内查询
查询每个学生及其选修课的情况

select a.*,b.* from student a,sc b where a.sno=b.sno;
select a.*,b.* from student a inner join sc b on a.sno=b.sno;

自然连接查询每个学生及其选修课的情况

select student.sno,sname,ssex,sbirthday,saddress,sdept,speciality,cno,degree from student,sc where student.sno=sc.sno;

输出所以女学生的学号、姓名、课程及成绩

select a.sno,sname,cno,degree from student a,sc b where a.sno=b.sno and ssex='女';

输出计算机工程系学生的学号、姓名及课程

select a.sno,sname,cname,degree from student a,sc b,course c where a.sno=b.sno and b.cno=c.cno and sdept ='计算机工程系';
select a.sno,sname,cname,degree from student a inner join sc b on a.sno=b.sno inner join course c on b.cno=c.cno where sdept ='计算机工程系';

自连接
查询同时选修了c01、c04课程的学生学号

select a.sno from sc a,sc b where a.sno=b.sno and a.cno='c01' and b.cno='c03';

查询与刘晨在同一个系学习的学生的姓名和所在系

select b.sname,b.sdept from student a,student b where a.sdept=b.sdept and a.sname='刘晨' and b.sname!='刘晨';
select sno,sname,sdept from student s1 where exists (select *from student s2 where s2.sdept=s1.sdept and s2.sname='刘晨') and s1.sname!='刘晨';

select sname from student where year(curdate())-year(sbirthday)>(select avg(year(curdate())-year(sbirthday)) from student);

查询没有选修数学的学生的学号和姓名

select sno,sname from student where sno not in (select sno from sc where cno in (select cno from course where cname='数学'));

select sname,year(curdate())-year(sbirthday) as 年龄 from student where year(curdate())-year(sbirthday)<any(select year(curdate())-year(sbirthday) from student where sdept='计算机工程系') and sdept<>'计算机工程系';

select sname,year(curdate())-year(sbirthday) as 年龄 from student where year(curdate())-year(sbirthday)<all(select year(curdate())-year(sbirthday) from student where sdept='计算机工程系') and sdept<>'计算机工程系';

插入部分数据

insert into student(sno,sname) values ('20140201','张三');

把平均成绩大于80的学生的平均成绩存入另一个已知的基本表

insert into sc1(sno,cno,degree) select sno,cno,avg(degree) from sc group by sno having avg(degree)>80;