use school；

use school;

– 查询所有学生的所有信息
select * from tb_student;
select seu_id,
stu_name,
stu_sex,
stu_birth,
stu,addr
from tb_student;
– 查询学生的学号、姓名和籍贯(投影)
select stu_id,stu_name,stu_addr from tb_student;
select stu_id,
stu_addr,
stu_name
from tb_student;
– 查询所有课程的名称及学分(投影和别名)
select cou_name,cou_credit from tb_course;=
select cou_name as 名称,
cou_credit as 学分
from tb_course;

– 查询所有女学生的姓名和出生日期(筛选)
select stu_name,stu_birth from tb_student where stu_sex =0;
select stu_name,
stu_birth
from tb_student
where stu_sex=0;

– 查询籍贯为“四川成都”的女学生的姓名和出生日期(筛选)
select stu_name,stu_birth from tb_student where stu_addr =‘四川成都’ and stu_sex=0;
select stu_name,
stu_birth
from tb_student
where stu_addr =‘四川成都’
and stu_sex =0;
– 查询籍贯为“四川成都”或者性别是女的学生
select stu_name from tb_student where stu_addr=‘四川成都’ or stu_sex=‘0’;

– 查询所有80后学生的姓名、性别和出生日期(筛选)
– select stu_name,stu_sex,stu_birth from tb_student where stu_birth < ‘1990-01-01’;
select stu_name,
stu_birth,
stu_sex
from tb_student
where ‘1980-1-1’ <= stu_birth
and ‘1989-12-31’>= stu_birth;

select stu_name,
stu_birth,
stu_sex
from tb_student
where stu_birth between ‘1980-1-1’ and ‘1989-12-31’;

– 查询学分大于2的课程的名称和学分(筛选)
select cou_name,cou_credit from tb_course where cou_credit > 2;
select cou_name,
cou_credit
from tb_course
where cou_credit >2;

– 查询学分是奇数的课程的名称和学分(筛选)
select cou_name,cou_credit from tb_course where cou_credit %2 != 0;
select cou_name,
cou_credit
from tb_course
where cou_credit %2 <> 0;

– 查询选择选了1111的课程考试成绩在90分以上的学生学号(筛选)
select stu_id from tb_record where cou_id =1111 and score >90;
select stu_id
from tb_record
where cou_id =1111
and score>90;

– 查询名字叫“杨过”的学生的姓名和性别
select stu_name,stu_sex from tb_student where stu_name =‘杨过’;
select stu_name,
stu_sex
from tb_student
where stu_name = ‘杨过’;

– 查询姓“杨”的学生姓名和性别(模糊)
select stu_name,stu_sex from tb_student where stu_name LIKE ‘杨%’;
select stu_name,
stu_sex
from tb_student
where stu_name like ‘杨%’;

– 查询姓“杨”名字两个字的学生姓名和性别(模糊)
select stu_name,stu_sex from tb_student where stu_name LIKE’杨_';
select stu_name,
stu_sex
from tb_student
where stu_name like ‘杨_’;

– 查询姓“杨”名字三个字的学生姓名和性别(模糊)
select stu_name,stu_sex from tb_student where stu_name LIKE’杨__';
select stu_name,
stu_sex
from tb_student
where stu_name like ‘杨__’;
– 查询名字中有“不”字或“嫣”字的学生的姓名(模糊)
select stu_name
from tb_student
where stu_name LIKE ‘%不%’
or stu_name like ‘%嫣%’;

select stu_name
from tb_student
where stu_name LIKE ‘%不%’
union
select stu_name
from tb_student
where stu_name LIKE ‘%嫣%’;

– 查询姓“杨”或姓“林”名字三个字的学生的姓名(正则表达式模糊查询)
select stu_name from tb_student where stu_name regexp ‘¹.{2}$’;
– [一 - 龥]、[\u4e00 - \u9fa5]:表示匹配中文

– 查询没有录入籍贯的学生姓名(空值处理)
select stu_name
from tb_student
where stu_addr = ‘’
or stu_addr is null;

– 查询录入了籍贯的学生姓名(空值处理)
select stu_name
from tb_student
where stu_addr is not null
or stu_addr <> ‘’;

– 查询学生选课的所有日期(去重)
select distinct sel_date
from tb_record ;

– 查询学生的籍贯(去重)
select distinct stu_addr
from tb_student
where stu_addr <> ‘’
and stu_addr is not null;

– 查询男学生的姓名和生日按年龄从大到小排列(排序)
select stu_name,stu_birth from tb_student order by stu_birth;
– 补充：将上面的生日换算成年龄(日期函数、数值函数)
select stu_name as 姓名,
case stu_sex when 0 then ‘女’ when 1 then ‘男’ else ‘未知’ end as 性别,
floor(datediff(curdate(),stu_birth)/365 )as 年龄
from tb_student
where col_id=1
order by stu_sex desc,
stu_birth asc;

select now();

– 查询学院编号为1的学生的姓名、性别和生日按年龄从大到小排列(排序)
– asc - ascending - 上升
– desc - descending - 降序
select stu_name,
stu_sex ,
stu_birth
from tb_student
where col_id=1
order by stu_sex desc,
stu_birth asc;

– 查询年龄最大的学生的出生日期(聚合函数)
select min(stu_birth) as max_birth
from tb_student;

– 查询年龄最小的学生的出生日期(聚合函数)
select max(stu_birth) as max_birth
from tb_student;

– 查询编号为1111的课程考试成绩的最高分(聚合函数)
select max(score) as max_score from tb_record where cou_id =1111;

– 查询学号为1001的学生考试成绩的最低分(聚合函数)
select min(score) as min_score from tb_record where stu_id =1001;

– 查询学号为1001的学生考试成绩的平均分(聚合函数)
select avg(score) as avg_score from tb_record where stu_id =1001;

– 查询学号为1001的学 生考试成绩的平均分，如果有null值，null值算0分(聚合函数)
– 方法1：
select sum(score)/ count(*)
from tb_record
where stu_id =1001;

– 方法2：
select avg(coalesce(score,0))
from tb_record
where stu_id=1001;

– 查询学号为1001的学生考试成绩的标准差(聚合函数)
select round(std(score),3) as 标准差
from tb_record
where stu_id=1001;

– 查询男女学生的人数(分组和聚合函数)
select stu_sex ,
count(*)
from tb_student
group by stu_sex;

– 查询每个学院学生人数(分组和聚合函数)
select col_id as 学院编号,
count(*) as 人数
from tb_student
group by col_id;

– 查询每个学院男女学生人数(分组和聚合函数)
select col_id as 学院编号,
case stu_sex when 1 then ‘男’ else ‘女’ end as 性别,
count(*) as 人数
from tb_student
group by col_id,stu_sex;

– 查询每个学生的学号和平均成绩(分组和聚合函数)
select stu_id as 学号,
avg(score) as 平均分
from tb_record
group by stu_id;

– 查询平均成绩大于等于90分的学生的学号和平均成绩
select stu_id as 学号,
round(avg(score),2) as 平均成绩
from tb_record
group by stu_id
having avg(score) >=90 ;

– 查询1111、2222、3333三门课程平均成绩大于等于90分的学生的学号和平均成绩
select stu_id as 学号,
round(avg(score),2) as 平均成绩
from tb_record
where cou_id in (1111,2222,3333)
group by stu_id
having 平均成绩 >= 90 ;

– 查询年龄最大的学生的姓名(子查询/嵌套查询)
select stu_name
from tb_student
where stu_birth =(select min(stu_birth)
from tb_student);

– 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
select stu_name
from tb_student
where stu_id in (select stu_id
from tb_record
group by stu_id
having count(*)>2);

– 查询学生的姓名、生日和所在学院名称
select stu_name ,
stu_birth,
col_name
from tb_student,tb_college
where tb_student.col_id = tb_college.col_id;

select stu_name ,
stu_birth,
col_name
from tb_student natural join tb_college;

select stu_name ,
stu_birth,
col_name
from tb_student inner join tb_college
on tb_student.col_id = tb_college.col_id;

– 查询学生姓名、课程名称以及成绩(连接查询/联结查询)
select stu_name,
cou_name,
score
from tb_student,tb_course,tb_record
where tb_student.stu_id =tb_record.stu_id
and tb_course.cou_id =tb_record.cou_id;

select stu_name,
cou_name,
score
from tb_student natural join tb_course natural join tb_record
where score is not null;

– 补充：上面的查询结果取前5条数据(分页查询)
select stu_name,
cou_name,
score
from tb_student,tb_course,tb_record
where tb_student.stu_id =tb_record.stu_id
and tb_course.cou_id =tb_record.cou_id
limit 5;

– 补充：上面的查询结果取第6-10条数据(分页查询)
select stu_name,
cou_name,
score
from tb_student,tb_course,tb_record
where tb_student.stu_id =tb_record.stu_id
and tb_course.cou_id =tb_record.cou_id
limit 5,5;

– 补充：上面的查询结果取第11-15条数据(分页查询)
select stu_name,
cou_name,
score
from tb_student,tb_course,tb_record
where tb_student.stu_id =tb_record.stu_id
and tb_course.cou_id =tb_record.cou_id
limit 10,5;

– 查询选课学生的姓名和平均成绩(子查询和连接查询)

– 查询学生的姓名和选课的数量
select stu_name,
total
from tb_student t1 natural join ( select stu_id,
count(*) as total
from tb_record
group by stu_id) t2;

– 查询每个学生的姓名和选课数量(左外连接和子查询)

– inner join - 内连接 - 获取两张表满足连表条件的记录
– natural join - 自然连接
– outer join - 外连接
– 写在join关键字前面的表称为左表，写在join关键字后面的表称为右表
– 左外连接 : 完整的获取坐标数据，不满足连接条件的地方填充null
– 右外连接 ； 完整的获取右表数据，不满足连表条件的地方填充null
– 全外连接 ： 完整的获取左右两表的数据，不满足连表条件的地方填充null

– 补充练习
– 01. 查询课程1111比课程2222成绩高的所有学生的学号

– 02. 查询没有学过张三丰老师的课程的同学的学号和姓名

– 03. 查询学过课程1111和课程2222的同学的学号和姓名

– 04. 查询所有课程成绩大于80分的同学的学号和姓名

– 05. 查询学习了所有课程的同学的学号和姓名

– 06. 查询至少有一门课跟学号1001同学所学课程相同的同学的学号和姓名

– 07. 查询所学课程与1002同学完全相同的同学的学号和姓名

– 08. 查询有课程不及格（60分以下）的同学的学号和姓名

– 09. 查询每门课程的名称、最高分、最低分和平均分

– 10. 查询每门课程第2名和第3名的学生姓名和对应的成绩

– 11. 查询每门课程各分数段（100-90，90-80，80-70，70-60，60以下）的人数占比

– 12. 查询本周过生日的学生的姓名和生日

---

1. 杨林 ↩︎