MySQL基础查找

use school;

– 查询所有学生的所有信息
select stu_id,
stu_name,
stu_sex,
stu_birth,
stu_addr,
stu_id
from tb_student;

– 查询学生的学号、姓名和籍贯(投影)
select stu_id as 学号,
stu_name as 姓名,
stu_addr as 籍贯
from tb_student;

– 查询所有课程的名称及学分(投影和别名)
select cou_name as 课程名称,
cou_credit as 学分
from tb_course;

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

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

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

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

– 方法二
select stu_name as 姓名,
stu_birth as 出生日期,
stu_sex as 性别
from tb_student
where stu_birth between ‘1980-1-1’ and ‘1989-12-31’;

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

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

– 方法二
select cou_name as 课程名称,
cou_credit as 学分
from tb_course
where cou_credit mod 2 <> 0;

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

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

– 方法二
select stu_name as 姓名,
case stu_sex
when 1 then ‘男’ else ‘女’ end as 性别
from tb_student
where stu_name = ‘杨过’;

– 方法三
select stu_name as 姓名,
if (stu_sex, ‘男’, ‘女’) as 性别
from tb_student
where stu_name = ‘杨过’;

– 查询姓“杨”的学生姓名和性别(模糊)
select stu_name as 姓名,
case stu_sex
when 1 then ‘男’ else ‘女’ end as 性别
from tb_student
where stu_name like ‘杨%’;
– % 表一个或多个

– 查询姓“杨”名字两个字的学生姓名和性别(模糊)
select stu_name as 姓名,
case stu_sex
when 1 then ‘男’ else ‘女’ end as 性别
from tb_student
where stu_name like ‘杨_’;
– _ 表一个

– 查询姓“杨”名字三个字的学生姓名和性别(模糊)
select stu_name as 姓名,
case stu_sex
when 1 then ‘男’ else ‘女’ end as 性别
from tb_student
where stu_name like ‘杨__’;

– 查询名字中有“不”字或“嫣”字的学生的学号和姓名(模糊)
select stu_name as 姓名,
stu_id as 学号
from tb_student
where stu_name like ‘%不%’
or stu_name like ‘%嫣%’;

– 查询姓“杨”或姓“林”名字三个字的学生的学号和姓名(正则表达式模糊查询)
select stu_name as 姓名,
stu_id as 学号
from tb_student
where stu_name regexp ‘[林杨][\u4e00-\u9fa5]{2}’;
– regexp 正则函数表达式

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

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

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

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

– 查询男学生的姓名和生日按年龄从大到小排列(排序)
– asc 从小大大排序
– desc 从大到小排序
select stu_name as 姓名,
stu_birth as 出生日期
from tb_student
where stu_sex = 1
order by stu_birth asc;

select curdate(); – 取现在日期
select now(); – 取现在日期和时间
select curtime(); – 取现在时间

– 补充：将上面的生日换算成年龄(日期函数、数值函数)
– floor 向下取整
– cell 向上取整
– datediff 相差的天数
select stu_name as 姓名,
floor(datediff(curdate(), stu_birth) / 365) as 年龄
from tb_student
where stu_sex = 1
order by 年龄 desc;

– 查询年龄最大的学生的出生日期(聚合函数)
– (聚合函数)
– sum 求和
– avg 求平均
– count 求数量
– max 求最大值
– min 求最小值
– std 求标准差
– stddev 求总体标准差
– variance 求方差
select max(stu_birth)
from tb_student;

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

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

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

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

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

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

– 查询男女学生的人数(分组和聚合函数)
select case stu_sex when 1 then ‘男’ else ‘女’ end as 性别,
count(*) as 人数
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 学号,
round(avg(score), 1) as 平均分
from tb_record
group by stu_id;

– 查询平均成绩大于等于90分的学生的学号和平均成绩
– 分组之前的数据筛选使用where子句
– 分组之后的数据筛选使用having子句
select stu_id as 学号,
round(avg(score), 1) as 平均分
from tb_record
group by stu_id
having 平均分 >= 90;

– 查询1111、2222、3333三门课程平均成绩大于等于90分的学生的学号和平均成绩
select stu_id as 学号,
round(avg(score), 1) 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 inner join tb_college
on 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_name,
score
from tb_student, tb_course, tb_record
where tb_student.stu_id = tb_record.stu_id
and tb_record.cou_id = tb_course.cou_id
and score is not null;

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

– 补充：上面的查询结果取第6-10条数据(分页查询)
select stu_name,
stu_name,
score
from tb_student, tb_course, tb_record
where tb_student.stu_id = tb_record.stu_id
and tb_record.cou_id = tb_course.cou_id
and score is not null
order by tb_course.cou_id asc, score desc
limit 5
offset 5; – 跳过前面5页的数据

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

– 查询选课学生的姓名和平均成绩(子查询和连接查询)
select stu_name,
avg_score
from tb_student
natural join(select stu_id,
round(avg(score), 1)as avg_score
from tb_record
group by stu_id) as tmp;

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

– 查询每个学生的姓名和选课数量(左外连接和子查询)
– 外连接：左外连接 / 右外连接 / 全外连接
select stu_name as 姓名,
coalesce(total, 0) as 选课数量
from tb_student as t1
left join (select stu_id,
count(*) as total
from tb_record
group by stu_id) as t2
on t1.stu_id = t2.stu_id;