我的MySQL学习笔记（二.下）

day2.2——DQL 数据查询语言

1. 知识集合

- 投影和别名
  - 指定要查的列 这个操作叫投影
  - as 列的别名
- 分支结构 case when then end 构造分支结构
- if函数 mysql方言 (因为其他数据中可能没有if函数，Oracle中做同样事情的函数叫做Decode
- 不知道准确的名字 就不能写成等号 此时写like
- SQL中使用通配符 % ---> 匹配0个or任意多个字符
提示：前面带%的模糊查询性能基本上非常糟糕
- 正则表达式模糊查询 regexp ---> regular expression
- 杨. ---> 匹配杨和以后的任意字符  
- ^杨.{1,2}$ ---> 任意字符出现1次或2次
- Oder by 排序 ---> asc - 升序 （从小到大），decs 降序 （从大到小）
- group by XX 根据XX分组
- 四舍五入 round(X,2) 保留两位小数后的四舍五入

分组以前的数据筛选使用where子句，分组以后的数据筛选使用having子句

2. 统计学常识

- 描述性统计：能拿到全量数据
    - 集中趋势：均值、中位数、众数 
    - 离散趋势：极差、方差、标准差
    - 相关性：协方差、相关性(Spearman、Pearson、Kindall)
- 推断性统计：用样本推断总体
    - t检验和F检验：样本的均值和方差能不能代表总体的均值和方差
    - 方差分析：检查数据的改变是否是入籍波动造成的，是否具体显著性

SQL中获取数据的描述性统计信息的函数
sum / avg / min / max / count / stddev /var

3. SQL语句书写顺序

一定要记下来

select ..., ..., ...
from ..., ...
where ... and ... or ...
group by ..., ...
having ...
order by ... asc, ...desc

4. 代码合集

-- 数据写入
insert into `tb_student`
(`stu_id`,`stu_name`,`stu_sex`,`stu_birth`,`stu_add`, `col_id` )
values
(1001,'杨过过',1,'1990-3-4', '湖南长沙',1),
(1002,'任我行',1, ' 1992-2-2','湖南长沙',1),
(1033,'王语嫣',0,'989-12-3','四川成都',1),
(1572,'岳不群',1,'1993-7-19','shan西咸阳',1),
(1378,'纪嫣然',0,'1995-8-12','四川绵阳',1),
(1954,'林平之',1,'1994-9-20','福建莆田',1),
(2035,'东方败',1,'1988-6-30',null, 2),
(3011,'周震南',1,'1985-12-12','福建莆田',3),
(3755,'项少龙',1,'1993-1-25', null, 3),
(3923,'杨不悔',0,'1985-4-17','四川成都',3);

use school;


-- 查询所有学生的所有信息
-- 不建议写* 效率低下 
select * from tb_student;

-- 好的写法 把所有的课程列写出来 
select stu_id,stu_name,stu_sex,stu_birth,stu_add,col_id from tb_student;
-- 查询所有课程名称及学分(投影和别名) 
-- 指定要查的列 这个操作叫投影
-- as 列的别名
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'; 

-- 查询所有80后女学生的姓名、性别和出生日期(筛选)
select stu_name,stu_sex,stu_birth from tb_student 
where stu_birth between '1980-1-1' and '1989-12-31' and stu_sex = 0; 

-- 分支结构 case when then end 构造分支结构
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';

-- mysql方言 (因为其他数据中可能没有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';

-- 查询姓”杨“的学生姓名和性别(模糊)
-- 不知道准确的名字 就不能写成等号 此时写like
-- SQL中使用通配符 % ---> 匹配0个or任意多个字符
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 '杨__';

-- 查询名字中有”不“字或“嫣”字的学生的姓名(模糊)
-- 提示：前面带%的模糊查询性能基本上非常糟糕
-- or 后面直接写'%嫣%'是不对的！or后面应该要是一个条件
select stu_name,stu_sex from tb_student 
where stu_name like '%不%'or stu_name like '%嫣%';
-- 法二 union并集运算
select stu_name,stu_sex from tb_student where stu_name like '%不%'
union
select stu_name,stu_sex from tb_student where stu_name like '%嫣%';

update stu_name set stu_name='岳不嫣' where stu_id = 1572;

-- 正则表达式模糊查询 regexp ---> regular expression
-- 杨. 匹配杨和以后的任意字符  
-- ^杨.{1,2}$ 任意字符出现1次或2次
select stu_name,stu_sex from tb_student where stu_name regexp '杨.{2}';

-- 查询没有录入家庭住址的学生姓名(空值)
-- 空值做任何运算结果也是空值，null相当于条件不成立
select stu_name from tb_student where stu_add is null;


-- 查询录入了家庭住址的学生姓名(空值) 最好不要写等号 和不等号
-- SQL中不等号的写法<> 不支持！=  等号为<=>
select stu_name,stu_add from tb_student where stu_add is not null;
select stu_name,stu_add from tb_student where stu_add is null;

-- 查询家庭住址（去重） distinct
select distinct stu_add from tb_student where stu_add is not null;

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

-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
-- Oder by 排序
-- asc - 升序 （从小到大），decs - 降序 （从大到小）
select stu_name,stu_birth from tb_student
where stu_sex=1 order by stu_birth asc;

-- 查询年龄最大的学生的出生日期(聚合函数) ---> 找出最小的生日
select now(); -- 查询现在的时间和日期
select curdate(); -- 查询现在的日期

-- floor 向下取整
select
 min(stu_birth) as 生日,
    floor(datediff( curdate(),min(stu_birth))/365) as 年龄
from tb_student;

-- 查询年龄最小的学生的出生日期(聚合函数)
select
 min(stu_birth) as 生日,
    floor(datediff(curdate(),max(stu_birth))/365) as 年龄
from tb_student;


-- 查询所有考试的平均成绩
-- 聚合函数遇到null值会做忽略处理
select avg(score) from tb_record;

 -- 考虑空值 使用count(*)
    select sum(score) / count(*) from tb_record;

-- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select avg(score) from tb_record where cid=1111;

-- 查询学号为1001的学生所有课程的平均分(筛选和聚合函数)
select avg(score) from tb_record where sid=1111;


-- 查询男女学生的人数(分组和聚合函数)
-- group by 根据上面分组
-- 分完组以后在进行聚合函数
-- SAC(Split - Aggregate - Combine)
select 
 if(stu_sex,'男','女') as 性别,
 count(*) as 人数
from tb_student group by stu_sex;


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

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