头歌MySQL数据定义与 *** 作实战(二)

实验二 1.单表查询2.连接查询3.子查询4.使用聚合函数查询5.复杂查询(二)

1.单表查询

4.1基本查询语句
查询字段Name和Salary的内容；查询整张表的内容。

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## retrieving the Name and Salary ##########
select Name,Salary 
FROM tb_emp;

########## retrieving all the table ##########
select * from tb_emp;

########## End ##########

4.2带 IN 关键字的查询
查询当Id不等于1时，字段Name和Salary的内容;

select Name,Salary from tb_emp where id not in (1);

4.3带 BETWEEN AND 的范围查询
查询当字段Salary范围在3000~5000时，字段Name和Salary的内容。

select Name,Salary from tb_emp where Salary BETWEEN 3000 and 5000;

5.1带 LIKE 的字符匹配查询
查询所有Name以字母C为起始的员工的Name和Salary的内容；

SELECT Name,Salary from tb_emp WHERE Name LIKE 'C%';

5.2查询空值与去除重复结果
1.使用关键字IS NULL返回数据表中字段DeptId为NULL的所有字段的内容，注意，返回的是指定行所有字段的内容；2.使用关键字DISTINCT返回数据表中字段Name不重复的内容，注意，只需返回字段Name的内容。

USE Company;

######### Begin #########
SELECT * FROM tb_emp WHERE DeptId IS NULL;

######### End #########

######### Begin #########
SELECT DISTINCT Name FROM tb_emp;

######### End #########

5.3带 AND 与 OR 的多条件查询
1.使用关键字AND返回数据表中字段DeptId为301并且薪水大于3000的所有字段的内容，其中DeptId的倒数第二个字母为i的大写；2.使用关键字IN返回数据表中字段DeptId为301和303的所有字段的内容。

USE Company;

######### Begin #########
SELECT * FROM tb_emp WHERE DeptId=301 AND Salary>3000;

######### End #########

######### Begin #########
SELECT * FROM tb_emp WHERE DeptId in(301,303);

######### End #########

6.1对查询结果进行排序
查询学生成绩表中1班同学的所有信息并以成绩降序的方式显示结果。

########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########
SELECT * FROM tb_score WHERE class_id=1 ORDER BY score DESC; 

6.2分组查询
对班级表中的班级名称进行分组查询

########## 对班级名称进行分组查询 ##########
SELECT * FROM tb_class GROUP BY class_id;

6.3使用 LIMIT 限制查询结果的数量
查询班级中第2名到第5名的学生信息，并根据学生成绩进行降序排序。

########## 查询班级中第2名到第5名的学生信息 ##########
SELECT * FROM tb_score  ORDER BY score DESC LIMIT 1,4;

2.连接查询

7.1内连接查询
查询数据表中学生姓名以及对应的班级名称，将其对应的列名分别另命名为studentName和className

########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########

SELECT tb_student.name as studentName,tb_class.name as className 
FROM tb_student join tb_class on tb_student.class_id=tb_class.id;


########## End ##########

7.2外连接查询
分别使用左外连接和右外连接查询数据表中所有学生姓名和对应的班级名称，查询结果列分别另命名为studentName和className。

USE School;

########## 使用左外连接查询所有学生姓名和对应的班级 ##########

#请在此处添加实现代码
########## Begin ##########

SELECT tb_student.name as studentName,tb_class.name as className 
FROM tb_student left join tb_class on tb_student.class_id=tb_class.id;


########## End ##########

########## 使用右外连接查询所有学生姓名和对应的班级 ##########

#请在此处添加实现代码
########## Begin ##########

SELECT tb_student.name as studentName,tb_class.name as className 
FROM tb_student right join tb_class on tb_student.class_id=tb_class.id;


########## End ##########

7.3复合条件连接查询
查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级，其中学生的姓名和学生所在班级分别另命名为studentName和className。

SELECT tb_student.name as studentName,tb_student.score,tb_class.name as className 
FROM tb_student left join tb_class on tb_student.class_id=tb_class.id
WHERE tb_student.score>90 order by tb_student.score DESC
;

3.子查询

8.1带比较运算符的子查询
查询大于所有平均年龄的员工姓名与年龄。

########## Begin ##########
#1.查询大于所有平均年龄的员工姓名与年龄

SELECT name,age FROM tb_emp where age>(SELECT AVG(age) FROM tb_emp);

########## End ##########

8.2关键字子查询
1.查询薪资表中比Java最高工资高的所有员工职位名称和薪资；

2.查询薪资表中比Java最低工资高的所有员工职位名称和薪资；

3.查询薪资表中职位为Java的所有员工职位名称和薪资。

#1.使用 ALL 关键字进行查询
SELECT position,salary from tb_salary 
where salary>ALL(SELECT salary from tb_salary where position='java');
#2.使用 ANY 关键字进行查询
SELECT position,salary from tb_salary 
where salary>ANY(SELECT salary from tb_salary where position='java');
#3.使用 IN 关键字进行查询
SELECT position,salary from tb_salary 
where position in ('java');

4.使用聚合函数查询

11.1COUNT( )函数
查询该表中一共有多少条数据；
查询此表中367 班有多少位学生,以班级id和对应人数格式输出。

########## 查询该表中一共有多少条数据 ##########
select count(*) from tb_class;

########## 查询此表中367班有多少位学生 ##########
select classid,count(*) from tb_class where classid=367;

11.2SUM( )函数
查询该表中学生的总成绩；
查询学生语文课程的总分数。

########## 查询所有学生总分数 ##########
select sum(score) from tb_class;

########## 查询学生语文科目的总分数 ##########
select course,sum(score) from tb_class where course='语文';

11.3AVG( )函数
查询表中该班级三位同学语文和英语课程的平均分数以及对应的课程名。

########## 查询学生语文科目的平均分数 ##########
select course,avg(score) from tb_class where course='语文';

########## 查询学生英语科目的平均分数 ##########
select course,avg(score) from tb_class where course='英语';

11.4MAX( )函数
分别查询语文和英语课程中的最高分数。

########## 查询语文课程中的最高分数 ##########
select course,max(score) from tb_class where course='语文';

########## 查询英语课程中的最高分数 ##########
select course,max(score) from tb_class where course='英语';

11.5MIN( )函数
分别查询语文和英语课程中的最低分数。

########## 查询语文课程中的最低分数 ##########
select course,min(score) from tb_class where course='语文';

########## 查询英语课程中的最低分数 ##########
select course,min(score) from tb_class where course='英语';

10.1GROUP BY 与 聚合函数
需要注意的一点，在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。
对年级Id和性别进行分组，分别统计表中2、3、4年级的男生总人数和女生总人数。

#1.查询表中2,3,4年级中分别男女的总人数
select gradeId,sex,count(*) from student 
group by gradeId,sex limit 1,4;

10.2使用 HAVING 与 ORDER BY
having子句用来对分组后的数据进行筛选，即having针对查询结果中的列发挥筛选数据作用。因此having通常与Group by连用。

select语句中，where、group by、having子句和聚合函数的执行次序如下：
1.where子句从数据源中去除不符合条件的数据；
2.然后group by子句搜集数据行到各个组中；
3.接着统计函数为各个组计算统计值；
4.最后having子句去掉不符合其组搜索条件的各组数据行。

select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [having 字段名 筛选条件]

where子句都可以用having代替，区别在于where过滤行，having过滤分组；

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行；
having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件过滤出特定的组，也可以使用多个分组标准进行分组。

Group By 和 Order By

select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [order by 字段名 排序方向]
若分组字段和排序字段一样时，可不需要order by关键字，则只需告知排序方向

1.我们要评选三好学生，条件是至少有两门课程在90分以上（包括90分）才能有资格，请列出符合的学生的学号（sno）及其90分以上（包括90分）科目总数；

2.学校评选先进学生，要求平均成绩大于90分（包括90分）的学生都有资格，并且语文课必须在95分以上（包括95分），请列出有资格的学生的学号（sno）及其科目的平均分。

#1.查询表中至少有两门课程在90分以上的学生信息
select sno,count(*) from tb_grade 
where score>=90 
group by sno having count(*)>=2;

#2.查询表中平均成绩大于90分且语文课在95分以上的学生信息
select sno,avg(score) from tb_grade 
where sno in(
    select sno from tb_grade
    where score>=95 and pno='语文'
)
group by sno having avg(score)>=90;

5.复杂查询(二)

10.1查询学生平均分
根据提供的表和数据，查询平均成绩小于60分的同学的学生编号（s_id）、学生姓名（s_name）和平均成绩（avg_score），要求平均成绩保留2位小数点。（注意：包括有成绩的和无成绩的）

########## Begin ##########
select s_id,s_name,ROUND(ifnull(avg(s_score),0.00),2) avg_score 
from (select student.s_id,student.s_name,score.s_score from student 
left join score on student.s_id=score.s_id) as emp 
group by s_id,s_name 
having ifnull(avg(s_score),0.00)<60;

########## End ##########

10.2查询修课相同学生信息

根据提供的表和数据，查询与s_id=01号同学学习的课程完全相同的其他同学的信息（学号s_id，姓名s_name，性别s_sex）。

select * from student 
where s_id in (select s_id from score 
where s_id in (select s_id from score 
where s_id not in (select s_id from score 
where c_id not in (select c_id from score 
where s_id='01'))
group by s_id 
having count(c_id) = (select count(c_id) from score 
where s_id='01') 
and s_id!='01')
);

10.3查询各科成绩并排序
查询各科成绩，进行排序并显示排名，按学生编号（s_id）、课程编号（c_id）、学生成绩（s_score）和排名（rank）进行输出。

select *,
(select count(s_score)+1 from score B where B.c_id=A.c_id and B.s_score>A.s_score)
as rank from score A
order by c_id,rank,s_id desc;

输出结果如下:

10.4查询张老师课程成绩最高的学生信息
查询选修“张三”老师所授课程的学生中，成绩最高的学生信息

select a.*,b.s_score,b.c_id,c.c_name 
from  student a
inner join score b on a.s_id=b.s_id
inner join course c on b.c_id=c.c_id
inner join teacher d on c.t_id=d.t_id
where b.s_score in (select MAX(s_score) from score 
where c_id in (select c_id from course 
inner join teacher on course.t_id=teacher.t_id and teacher.t_name="张三"))

输出结果如下:

10.5查询两门课程不及格同学信息
查询两门及其以上不及格课程的同学的学号（s_id）、姓名（s_name）及其平均成绩（avg_score），要求计算平均成绩后为整数。

select a.s_id,a.s_name,ROUND(AVG(b.s_score),0) avg_score
from student a 
inner join score b on a.s_id=b.s_id
where a.s_id in (select s_id from score where s_score<60)
group by s_id
having count(*)>=2;

输出结果如下: