MySQL所有查询20210608

MySQL查询20210608

-- 所有查询操作：
-- 查询所有学生信息
select * from tb_student;
-- 查询所有课程名称及学分(投影和别名)
select * from tb_student;
select cou_name 课程名, cou_credit 学分 from tb_course;
-- 查询所有女学生的姓名和出生日期(筛选)
select stu_name, stu_birth from tb_student where stu_sex = 0;
select stu_name, case stu_sex when 1 then '男' else '女' end as 性别 from tb_student where stu_birth between '1980-01-01' and '1989-12-31';
select stu_name, if(stu_sex, '男', '女') as 性别 from tb_student where stu_birth between '1980-01-01' and '1989-12-31';

-- 查询所有80后学生的姓名、性别和出生日期(筛选)
select stu_name, stu_sex, stu_birth from tb_student where stu_birth between '1980-01-01' and '1989-12-31';
-- 查询姓”杨“的学生姓名和性别(模糊)
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 '%嫣%';
-- 并集union：去重  union all：不去重
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 '^杨.$' ;

-- 查询没有录入家庭住址的学生姓名(空值)
-- 空值做任何运算结果也是空值（null）
select stu_name from tb_student where stu_addr is null;
-- select stu_name from tb_student where stu_addr <> null;
-- 查询录入了家庭住址的学生姓名(空值)
select stu_name from tb_student where stu_addr is not null;
-- 查询学生选课的所有日期(去重)
select * from tb_record;
select distinct sel_date from tb_record;
-- 查询学生的家庭住址(去重)
select distinct stu_addr from tb_student;
-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
select stu_name, stu_birth from tb_student order by stu_birth asc;
-- 向下取整 floor
select 
	min(stu_birth)as 生日,
    floor((datediff(curdate(), stu_birth))/365) as 年龄
    from tb_student;

-- 查询年龄最大的学生的出生日期(聚合函数)
select min(stu_birth) from tb_student; 
-- 查询年龄最小的学生的出生日期(聚合函数)
select max(stu_birth) from tb_student;
-- 查询男女学生的人数(分组和聚合函数)
select if (stu_sex, '男', '女') 性别, count(*) 人数 from tb_student group by stu_sex;
-- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select * from tb_record;
select avg(score) 平均成绩 from tb_record where cid = 1111;
-- 查询学号为1001的学生所有课程的平均分(筛选和聚合函数)
select avg(score) 平均分 from tb_record where sid = 1001;
-- 查询每个学生的学号和平均成绩(分组和聚合函数)
-- 聚合函数遇到空值列会做忽略处理
select sid 学号, round(avg(score), 1) 平均分 from tb_record group by sid;
-- 查询平均成绩大于等于90分的学生的学号和平均成绩
-- having->分组前进行判断； where->分组后进行判断
select sid 学号, round(avg(score), 1) 平均分 from tb_record group by sid having 平均分 >= 90;
-- 查询年龄最大的学生的姓名(子查询)
select * from tb_student;
select stu_name from tb_student where stu_birth = (select min(stu_birth) from tb_student);
-- 查询年龄最大的学生姓名和年龄(子查询+运算)
select stu_name, (select timestampdiff(year, stu_birth, curdate())) as 年龄 from tb_student where stu_birth = (select min(stu_birth) from tb_student);
-- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
-- 方式1：（分组/内连接/子查询）
select 
	b.stu_name 
	from tb_student b
	inner join (select sid, count(*) 选课数 from tb_record group by sid having count(*) >= 2) as a on b.stu_id = a.sid;
-- 方式2：
select stu_name from tb_student where stu_id in (select sid from tb_record group by sid having count(*) >=2);
-- 查询学生姓名、课程名称以及成绩(连接查询)
select 
	a.stu_name,
    b.score,
	c.cou_name
    from tb_student a
    inner join tb_record b on b.sid = a.stu_id
    inner join tb_course c on c.cou_id = b.cid;
-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
select 
	a.stu_name 姓名,
    b.平均成绩
    from tb_student a
    inner join (select sid, avg(score) 平均成绩 from tb_record group by sid) b on b.sid = a.stu_id;
-- 查询每个学生的姓名和选课数量(左外连接和子查询) 
select 
	a.stu_name,
    b.选课数量
    from tb_student a
    left join (select sid, count(cid) 选课数量 from tb_record group by sid) b on b.sid = a.stu_id;