2.mysql练习（查询进阶）

/*#####            表2  category   */
#自连接查询 
#表结构语句

select 
c1.categoryname as '父结点',
c2.categoryName as '子结点'
from category c1,category c2
where c1.categoryid=c2.pid;

/* *#####      表3   card  */
/*
某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题
卡里面的字母"O和数字0"、"字母i和数字1"，用户反映说看不清楚
公司决定，把存储在数据库中的密码中所有的“O”都改成“0”，
把所有的“i”都改成“1”*/

update card set password=
replace(replace(password,'o','0'),'i','1');

/* *#####      表3   sellRecord  */

/*现在希望通过SQL语句进行排序，并且首先要按照前半部分的
数字进行排序，
然后再按照后半部分的数字进行排序，输出要排成这样
13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、
13-108、14-1、14-2、14-11    */#cast,索引从1开始
select ListNumber from sellrecord
order by
cast(substring(ListNumber,1,locate('-',ListNumber)-1) as unsigned),
cast(substring(ListNumber,locate('-',ListNumber)+1) as unsigned);

#学号为10000的学生总分
/*聚合函数只返回一个数值，因此，不能够直接与可能
返回多行的列一起使用来进行查询，
除非该列包含在另一个聚合函数中或在GROUP BY语句后。
下面的sql语句是错误的,sqlSever会报错，而mysql不会,所以mysql某些方面来说不好
select 
sum(studentresult) as 学号为10000的学生总分,
subjectno as 科目编号
from result
where studentno=10000;
*/
select 
sum(studentresult) as 学号为10000的学生总分,
count(distinct subjectno) as 科目次数
from result
where studentno=10000;

select 
sum(studentresult) as 学号为10000的学生总分,
subjectno as 科目编号
from result
where studentno=10000
group by subjectno;
#及格学生的平均成绩,最高分,最低分
select
avg(studentresult) as '平均成绩',
max(studentresult)  '最高分',
min(studentresult)  '最低分'
from result
where studentresult>=60;
#查询2011-11-04科目“java”的最高分、最低分、平均分
select
avg(studentresult) as '平均成绩',
max(studentresult)  '最高分',
min(studentresult)  '最低分'
from result
join course
on course.subjectno=result.subjectno
where subjectname='java' and examdate='2011-11-04';

#聚合函数会忽略null值,不计算
/*
create table if not exists temp(
 id int not null primary key,
 num int 
);
insert into temp(id,num) values(1,1),(2,2),(3,3),(4,null);
*/
select count(num) from temp;  
select count(*) from temp;  
select count(id) from temp;  
select count(1) from temp;  
select avg(num) from temp;   
#drop table temp;
/*
一旦分组以后,SELECT列表中只能包含
1. 被分组的列
2. 为每个分组返回一个值的表达式，如聚合函数
*/
#查询男女学生各多少人
select sex,count(*) from student group by sex;
#错的，却不报错  select * from student group by sex;
#查询每个年级总人数,输出(年级名称,每个年级总人数)
select gradename,count(*) from student join grade
on student.gradeid=grade.gradeid
group by gradename;
/*查询每个科目平均分，并且按照平均分由低到高的顺序排列显示,
输出(科目名称,每门课的平均分)*/
select subjectname,avg(studentresult) from course join result
on course.subjectno=result.subjectno
group by subjectname
order by avg(studentresult)#升序
/*统计每学期男女同学的人数(多列分组)
输出(年级名称,性别,人数)*/
select gradename,sex,count(*) from student join grade
on student.gradeid=grade.gradeid
group by gradename,sex
/*查询年级总人数小于3的年级名称(having)----对组进行筛选，作用同where
输出(年级名称,人数)---where不能对组筛选,count里面不为空即可
*/
select gradename,count(8) from student join grade
on student.gradeid=grade.gradeid
group by gradename
having count(*)<3
/*查询每个年级学时数大于等于70的课程数量。
输出(年级名称,课程数量)
*/
select gradename,count(*) from grade join course
on grade.gradeid=course.gradeid
where classhour>=70
group by gradename;`course`

/*查询每学期学生的平均年龄
输出(年级名称,平均年龄)
*/
select gradename,avg(year(current_timestamp)-year(borndate))
from student join grade
on student.gradeid=grade.gradeid
group by gradename;
/*查询参加考试的学生中，平均分及格的学生记录，
并按照平均分降序排列
输出(学生姓名,平均分)
*/
select studentname,avg(studentresult) 
from student join result
on student.studentno=result.studentno
group by studentname
having avg(studentresult)>=60
order by avg(studentresult) desc
/*查询参加考试至少两次考试不及格(包括补考)的
学生姓名、不及格次数。*/#where可用在group by之前
select studentname,count(*)
from student join result
on student.studentno=result.studentno
where studentresult<60
group by studentname#,subjectno,这是查询同一科目两次
having count(*)>1;

/*
    select语句执行的逻辑顺序:
    1.from:查询表中的所有记录,生成结果集
    2.join:根据连接条件,生成结果集
    3.where:对第2步生成结果集用where后面的
      条件表达式对每一条记录进行筛选
    4.group by:分组后只能对组进行操作,select子句中只能出现分组的列
        或聚合函数
    5.having:对组进行筛选
    6.select:输出查询结果
    7.order by:对输出的查询结果进行排序
    
    引用自定义名要注意
    
    先看from,再select,
*/
select studentname,avg(studentresult) as stuavg
from result inner join student
on result.studentno=student.studentno
where studentresult<90
group by studentname
having avg(studentresult)<70#不可引用，mysql不报错，sqlSever报错
order by stuavg;#可引用

select * from result join student 
on result.studentno=student.studentno
where studentresult<90