sql数据库之应付学校作业

use school;

-- 查询所有学生的所有信息-- 不建议在里面使用*,太影响性能

-- select *  from tb_student;

-- 查询学生的学号、姓名和籍贯(投影和别名)

-- select stu_id as 学号,stu_name as 姓名,stu_addr as 籍贯 from tb_student;

-- 查询所有课程的名称及学分(投影和别名)

-- select cou_name as 课程名字,cou_credit 学分 from tb_course;

-- 查询所有⼥学生的姓名和出生日期(数据筛选)

-- select stu_id

--       ,stu_name

--       ,stu_sex

--       ,stu_birth

--       ,stu_addr

--       ,col_id

--       from tb_student;

-- 查询所有女学生的姓名和出生日期(数据筛选)

-- select stu_name

--       ,stu_birth

--       from tb_student

--       where std_sex=0;

-- 查询籍贯为“四川成都”的⼥学⽣的姓名和出⽣⽇期(数据筛选)

-- select stu_name

--       ,stu_birth

--       from tb_student

--       where stu_sex=0 and stu_addr="四川成都";

-- 查询80后的学生姓名性别出生日期

-- select stu_name

--       ,stu_birth

--       from tb_student

--       where stu_birth>="1980-1-1" and stu_birth<="1989-12-31";

-- 补充

-- union 并集 --> union自带去重功能

 -- 查询所有80后学⽣的姓名、性别和出⽣⽇期(数据筛选)

 -- Python比较大小： 20 < x < 30

-- SQL语句提供了另一种连写的关键字。

-- 查询学分⼤于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 =1 ;

-- 查询选择选了1111的课程考试成绩在90分以上的学⽣学号(数据筛选)

-- 查询名字叫“杨过”的学⽣的姓名和性别(数据筛选)

-- select stu_name

--     ,stu_sex

--       from tb_student

--       where stu_name = "杨%";

-- SQL语句也有分支结构

-- 查询姓“杨”的学⽣姓名和性别(模糊匹配)

-- 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

--       ,stu_id

--       from tb_student

--       where stu_name like "杨__";

-- 查询学号最后一位是3的学生的学号和姓名(模糊匹配)

-- select stu_name

--       ,stu_id

--       from tb_student

--       where stu_id like "%3";

-- 查询名字中有“不”字或“嫣”字的学⽣的学号和姓名(模糊匹配和并集运算)

-- select stu_name

--       ,stu_id

--       from tb_student

--       where stu_name like "%不%" or stu_name  like "%焉%";

--      

--       select stu_name

--       ,stu_id

--       from tb_student

--       where stu_name like "%不%"

--   union

--       select stu_name

--       ,stu_id

--       from tb_student

--       where stu_name  like "%焉%";

     

--   case else 查询性别

-- select stu_name

--       ,case stu_sex

--       when 1 then "男"

--     when 0 then "女"

--        else "未知"

--    end as 性别

--      from tb_student

--      where stu_name = "杨过";

     

-- 并集运算

-- union：自带去重操作

-- union all：只完全将两个集合合并到一起school

-- 查询姓“杨”或姓“林”名字三个字的学⽣的学号和姓名(正则表达式模糊匹配)

-- select stu_id

--     ,stu_name

--       from tb_student

--       where stu_name regexp "[杨林].{2}";

-- SQL中正则表达式关键字：regexp

-- 查询没有录⼊籍贯的学⽣姓名(空值处理)

-- 查询录⼊了籍贯的学⽣姓名(空值处理)

-- 补充：空字符串的处理

-- 三值逻辑，true，false，unknow

-- select stu_name

--    from tb_student

--      where stu_addr is null;

--     

-- 补充空字符串的处理

-- 先对表插入空字符串

-- delete from tb_student where stu_id=9898;

-- insert into tb_student value(9990,"张四",3,"2021-2-1","  ","3");

-- 原来是北京，现在插入空字符串

-- select stu_name

--    from tb_student

--      where  trim(stu_addr)="";

-- trim 类似于python中字符串的strip方法 --> 将字符串的头尾的空白符号去除

-- 查询学⽣选课的所有⽇期(去重)

-- distinct 去重关键字

-- select distinct sel_date

--      from tb_record ;

-- 查询学⽣的籍贯(去重)

-- 查询男学⽣的姓名和⽣⽇按年龄从⼤到⼩排列(排序)

-- select stu_name

--     ,stu_birth

--       from tb_student

--       where stu_sex=1

--       order by stu_birth asc;

-- 排序关键字 ： order by

-- asc(默认):从小到大排序 --> ascending

-- desc:从大到小排序 --> deascending

-- 补充：将上⾯的⽣⽇换算成年龄(⽇期函数、数值函数)

-- 获取当前系统日期：curdate()

-- datediff :计算时间差

-- select datediff(curdate(),stu_birth)/365 from tb_student;

-- 虚岁、周岁

-- floor：向小取整

-- select floor(datediff(curdate(),stu_birth)/365) from tb_student;

-- ceil：向大取整

-- select ceil(datediff(curdate(),stu_birth)/365) from tb_student;

-- 查询年龄最⼤的学⽣的出⽣⽇期(聚合函数)

-- 聚合函数：用来描述统计信息的函数、方法

-- 查询年龄最小的学生的出生期(聚合函数)

-- select max(stu_birth) as 年龄最小学生 from tb_student;

-- 查询编号为1111的课程考试成绩的最⾼分(聚合函数)

-- select max(score) as 最高分 from tb_record where cou_id=1111;

-- 查询学号为1001的学⽣考试成绩的最低分、最⾼分、平均分、标准差、⽅差(聚合函数)

-- 平均分：描述了数据的集中程度。

-- 标准差、方差：描述了数据的离散程度。

-- select min(score) as 最低分,max(score) as 最高分

--  ,avg(score) as 平均分

--  ,stddev(score) as 标准差

--  ,variance(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;

-- ifnull() --> 如果为null，则怎么样 --> ifnull()函数为MySQL的方言

-- select ifnull(score,0) as 分数null转换为零 from tb_record where stu_id =1001;

-- 查询男女学生的分数(分组和聚合函数)

-- 分组：group by

-- select case stu_sex

--      when 1  then"男"

--        when 3 then "未知"

--        else "女"

--        end as 性别

--        ,count(stu_id)

--        from tb_student

--        group by stu_sex;

-- 查询每个学院学⽣⼈数(分组和聚合函数)

-- select col_id as "学号"

--        ,count(stu_id) as"数量"

--         from tb_student

--         group by col_id;

-- 查询每个学院男女学生分数(分组和聚合函数)

-- select col_id as 学院

-- ,stu_sex as 性别

-- ,count(stu_id) as 人数

-- from tb_student group by

-- col_id,stu_sex;

-- 查询每个学⽣的学号和平均成绩(分组和聚合函数)

-- select stu_id as 学号

--     ,avg(score) as 平均分

--       from tb_record

--        group  by stu_id

--        having avg(stu_id)

--         >=90;

-- 查询平均成绩⼤于等于90分的学⽣的学号和平均成绩(分组后的数据筛选)

-- having:条件子句

-- 查询1111、2222、3333三⻔课程平均成绩⼤于等于90分的学⽣的学号和平均成绩(分组前后的数据筛选)

-- where子句：分组前做筛选

-- select stu_id

--       ,stu_id

--       ,avg(score)

--       from tb_record

--       where cou_id in(1111,2222,3333) group by

--       stu_id having avg(score) >=90;

-- having子句：分组后做筛选

-- select stu_id as 学号

--     ,avg(score) as 平均分

--       from tb_record

--        group  by stu_id

--        having avg(stu_id)

--         >=90;

-- 先从record表中根据需要的课程筛选一遍，再从此基础上以学号分组，找到平均成绩>=90的信息。

-- 查询年龄最⼤的学⽣的姓名(⼦查询)

-- 自定义变量使用@符号声明这是一个自定义变量，:=是赋值符号

-- select @a:=min(stu_birth)from tb_student;

-- select @a as 年龄最大的学生的出生日期;

-- 嵌套查询(子查询)

-- select stu_name

--        from tb_student

--        where stu_birth=(select min(stu_birth)from tb_student);

-- 查询选了两门以上的课程的学生姓名(查询和集合运算)

-- select @c:= stu_id  from tb_record group by stu_id having count(stu_id)>2;

-- select stu_name  from tb_student

--  where stu_id in(@c);   ????????

-- select stu_name  from tb_student

--  where stu_id in(select stu_id

--  from tb_record group by stu_id

--  having count(stu_id)>2)

-- 查询学⽣的姓名、生日和所在学院名称(表连接)

-- select stu_name,stu_birth,col_name

--     from tb_student as t1 ,tb_college as t2

--       where t1.col_id=t2.col_id;

-- 查询学⽣姓名、课程名称以及成绩(表连接)

-- 将其为外联法，先按名字排序，再按分数降序排列

-- SELECT

--     stu_name, cou_name, score

-- FROM

--     tb_student t1,

--     tb_course t2,

--     tb_record t3

-- WHERE

--     t1.stu_id = t3.stu_id

--         AND t2.cou_id = t3.cou_id

-- ORDER BY stu_name , score DESC;

-- select stu_name ,cou_name,score

--        from tb_student,tb_record natural join tb_course

--        order by stu_name,score desc;

-- 补充：上⾯的查询结果取前5条数据(分⻚查询)

-- select stu_name,cou_name,score

-- from tb_student t1 inner join tb_record t3

-- on t1.stu_id=t3.stu_id inner join tb_course t2

-- on t2.cou_id =t3.cou_id

-- order by stu_name,score desc

-- limit 5;

-- 补充：上⾯的查询结果取第6-10条数据(分⻚查询)

-- select stu_name,cou_name,score

-- from tb_student t1 inner join tb_record t3

-- on t1.stu_id=t3.stu_id inner join tb_course t2

-- on t2.cou_id =t3.cou_id

-- order by stu_name,score desc

-- limit 10,5;

-- 补充：上⾯的查询结果取第11-15条数据(分⻚查询)

-- 查询选课学⽣的姓名和平均成绩(⼦查询和表连接)

-- select stu_id ,avg(score) as avg_score  from tb_record group by stu_id;

-- select stu_name ,avg_score

-- from tb_student  as t1 natural  join (select stu_id ,avg(score) as avg_score from tb_record group by stu_id) as t2 ;

-- 查询学⽣的姓名和选课的数量(⼦查询和表连接)

-- select stu_name,total from tb_student t1 natural join (select stu_id,count(*) as total

-- from tb_record group by stu_id) t2;

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

select stu_name,coalesce(total,0)from tb_student t1 left join

(select stu_id,count(*) as total from tb_record group by stu_id) t2

on t1.stu_id=t2.stu_id;v