Day-30 SQL查询基础练习1

Day-30 SQL查询基础练习1

1. 设计表

   • ER图 ----> Entity Relationship Diagram

     • 实体: 矩形框 —> 表
     • 属性: 椭圆框 —> 列(字段、属性、特征)
     • 关系: 菱形框
     • 重数: 一对一、一对多、多对多

   • EER图 —> Extended ER图

   • 正向工程: 先设计EER图, 然后根据ERR图生成数据库和表

   • 反向工程: 用设计好的数据库和表生成EER图

   • regular expression —> regexp

2. 统计学

   • 描述性统计: 能拿到全量数据
     • 集中趋势: 均值、 中位数、 众数
     • 离散趋势: 最大值、 最小值、 极差(ptp)、 方差、 标准差
     • 相关性: 协方差、 相关系数(斯皮尔曼(Spearman)、 皮尔逊(Pearson)、 肯德尔(Kindall))
   • 推断性统计: 用样本推断总体
     • t检验和f检验: 样本的均值和方差能不能代表总体的均值和方差
     • 方差分析: 检查数据的改变是否是随机波动造成的, 是否具体显著性

3. SQl中获取数据的描述性统计信息的函数

   • sum / avg / min / max / count / std / var

4. use school;
   
   -- 查询所有学生信息
   select * from tb_student;
   select stu_id, stu_name, stu_sex, stu_birth, stu_addr, col_id from tb_student;
   
   -- 查询所有课程名称及学分(投影和别名)
   select cou_name as 课程名称,cou_credit as 学分 from tb_course;
   
   -- 查询所有女学生的姓名和出生日期(筛选)
   select stu_name,stu_birth from tb_student where stu_sex=0;
   
   -- 查询所有80后学生的姓名、性别和出生日期(筛选)
   select stu_name,stu_sex, stu_birth from tb_student 
   where stu_birth >= 1980-1-1 and stu_birth<='1989-12-31';
   
   select stu_name,stu_sex, stu_birth from tb_student 
   where stu_birth between '1980-1-1' and '1989-12-31';
   
   select stu_name as 姓名,
   	case stu_sex when 1 then '男' else '女' end as 性别,
       stu_birth as 生日
   from tb_student 
   where stu_birth between '1980-1-1' and '1989-12-31';
   
   -- SQL方言 (因为其他的数据库可能没有if函数)
   -- 例如: Oracle中做同样事情的函数叫decode 
   select stu_name as 姓名,
   	if (stu_sex, '男' ,'女') as 性别,
       stu_birth as 生日
   from tb_student 
   where stu_birth between '1980-1-1' and '1989-12-31';
   
   -- 查询姓”杨“的学生姓名和性别(模糊)
   -- 在SQL中通配符%可以匹配零个或任意多个字符 
   select stu_name,stu_sex from tb_student where stu_name like '杨%';
   
   -- 查询姓”杨“名字两个字的学生姓名和性别(模糊)
   -- 在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 from tb_student where stu_name like '%不%' or stu_name like '%嫣%';
   
   update tb_student set stu_name='岳不嫣' where stu_id = 1572;
   
   -- 并集运算 
   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 like '%不%'
   union all
   select stu_name from tb_student where stu_name like '%嫣%';
   
   -- 正则表达式模糊查询
   select stu_name,stu_sex from tb_student where stu_name regexp '杨.';
    
   
   -- 查询没有录入家庭住址的学生姓名(空值)
   -- null作任何运算结果也是产生null, 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 distinct sel_date from tb_record;
   select sel_date from tb_record group by sel_date;
   
   -- 查询学生的家庭住址(去重)
   select distinct stu_addr from tb_student;
   
   -- 查询男学生的姓名和生日按年龄从大到小排列(排序)
   -- asc - 升序(从小到大, 默认) desc - 降序(从大到小) 
   select stu_name, stu_birth from tb_student where stu_sex = 1 
   order by stu_birth desc;
   
   -- 查询年龄最大的学生的出生日期(聚合函数)  --->  最小生日
   select now();
   select curdate();
   
   select min(stu_birth) from tb_student ;
   select 
   	min(stu_birth) as 生日,
   	floor(datediff(curdate(),min(stu_birth))/ 365) as 年龄
   from tb_student ;
   
   -- 查询年龄最小的学生的出生日期(聚合函数)
   select max(stu_birth) from tb_student;
   
   select 
   	max(stu_birth) as 生日,
   	floor(datediff(curdate(),max(stu_birth))/ 365) as 年龄
   from tb_student ;
   
   -- 查询所有课程的平均成绩
   -- 聚合函数在遇到null值会做忽略的处理 
   -- 如果做计数操作, 建议使用count(*) , 这样才不会漏掉空值
   select avg(score) from tb_record ;
   
   select sum(score) / count(score) from tb_record;
   
   select sum(score) / count(*) from tb_record;
   
   -- 查询男女学生的人数(分组和聚合函数) 
   -- SAC(Split - Aggregate - Combine)   
   select 
   	if(stu_sex, '男', '女') as 性别 ,
   	count(*) as 人数
   from tb_student group by stu_sex ; 
   
   -- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
   select avg(score) from tb_record where cid=1111;
   
   -- 查询学号为1001的学生所有课程的平均分(筛选和聚合函数)
   select avg(score) from tb_record where sid=1001;
   
   -- 查询每个学生的学号和平均成绩(分组和聚合函数)
   select sid as 学号,round(avg(score),1) as 平均分 from tb_record group by sid;
   
   -- 查询平均成绩大于等于90分的学生的学号和平均成绩
   -- 分组以前的数据筛选使用where子句, 分组以后的数据筛选使用having 
   select sid as 学号, round(avg(score), 1) as 平均分
   from tb_record
   group by sid having 平均分>=90;