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;