3.3.2.起别名

3.3.2.1.字段别名

select 语句中使用 as为字段起别名 , 也可以省略as, 这样查询的结果以 别名为字段名

-- 字段别名
select stu_id as id, stu_name as name, stu_sex sex, stu_birth birth, stu_weight, stu_height, team_id, stu_info
from student;

3.3.3.2.表别名

from 语句 起别名, 不使用 as , 通常这时 使用表别名点来强调字段的所属

-- 表别名
select ss.stu_id, ss.stu_name, stu_sex, stu_birth, stu_weight, stu_height, team_id, stu_info
from student  ss;

3.3.3.select 查询

3.3.3.1.查询全字段

-- 查询 全部记录 全部字段
select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, team_id, stu_info from student;

--  查询 全部记录 全部字段, 用 * 代替全部字段
select * from student;

3.3.3.2.字段运算

可以进行 字段的运算操作, 包括调用函数等

select  concat('姓名:',stu_name) as name,
        stu_weight,  stu_weight + 10  as stu_weight_add,
        stu_height, stu_height * 10  as stu_height_mul,
        stu_weight/stu_height as stu_weight_div
from student;

注意 在使用 concat() 时, 如果有null 结果为null

3.3.3.3.去重复

select distinct stu_sex from student;

3.3.3.4.CaseWhenThen

case 开始
when 条件(是不是)
then 如果是
else 以上的when都没有满足
end 结束

案例1:

select  stu_name,  stu_sex,
        case stu_sex
            when 1 then '男'
            when 0 then '女'
            else '未知'
            end as sex
from student ;

案例2 :

select stu_name,  stu_height,
       case
           when stu_height > 180 then '>=180'
           when stu_height > 170 then '>=170'
           when stu_height > 160 then '>=160'
           else '<160'
           end height
from student;

3.3.4.where 语句

where 语句返回 boolean 型结果

3.3.4.1.恒真 恒假

-- 恒真
select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, team_id, stu_info from student  where  1=1;
-- 恒假
select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, team_id, stu_info from student  where  1!=1;

3.3.4.2.模糊条件

like %(匹配 n 个字符) _ (匹配 1 个符)

-- 模糊查询: %通配符 任意字符
select stu_name from student  where stu_name like '%小%';
select stu_name from student  where stu_name like '小%';
select stu_name from student  where stu_name like '%小';
-- 下划线: 任意单个字符
select stu_name from student  where stu_name like  '_小%';
select stu_name from student  where stu_name like  '__小%';

3.3.4.3.精确

= != <>

忽略 null

-- 数值类型
select stu_name, stu_sex from student where stu_sex = 1 ;
select stu_name, stu_sex from student where stu_sex != 1;
select stu_name, stu_sex from student where stu_sex <> 1;
-- 字符串类型
select stu_name from student where stu_name = '王小二';
select stu_name from student where stu_name != '王小二';
-- 时间类型
select stu_name, stu_birth from student  where stu_birth = '2004-03-05';

3.3.4.4.范围

< <= > >= between ... and ...

忽略 null

-- 大于
select stu_name, stu_weight from student  where stu_weight >50;
-- 小于等于
select stu_name, stu_weight from student  where stu_weight >=50;
-- 小于
select stu_name, stu_weight from student  where stu_weight <50;
-- 大于等于
select stu_name, stu_weight from student  where stu_weight <=50;

-- 大于>=50 同时 小于<=60
select stu_name, stu_weight from student  where stu_weight between 50  and 60;

3.3.4.5.筛选

in , not in

忽略 null

-- in 满足 1 或者 2 
select stu_name, team_id from student  where team_id  in (1 , 2  );
-- not in 不满足 1 和 2
select stu_name, team_id from student  where team_id  not in (1 , 2  );

3.3.4.6.判断为空

is null is not null

空 字符串 '' 不是null

-- 空值判断
select stu_name, stu_info from student where stu_info is null;
-- 非空判断
select stu_name, stu_info from student where stu_info is not null;

3.3.4.7.条件 连接

and , or

-- and 逻辑与 两个条件同时满足
select stu_name, stu_sex from student  where stu_sex = 1  and  stu_name like '%小%';
-- or 逻辑或  满足一个条件即可
select stu_name, stu_sex from student  where stu_sex = 1  or  stu_name like '%小%';

3.3.4.8.反选

not 字段值 为 <null> 也不会查询到

-- 查询 全部
select stu_name, stu_info from student ;
-- 查询 精确条件
select stu_name, stu_info from student  where stu_info = '对历史非常感兴趣' ;
-- 查询 非精确条件
select stu_name, stu_info from student  where not( stu_info = '对历史非常感兴趣' );

3.3.5.order by 排序

语法 : order by 字段名 asc(升序)/desc(降序)

3.3.5.1.升降序

desc 降序 /asc 升序(默认)

select rpt_name, rpt_num from repository order by rpt_num ;
select rpt_name, rpt_num from repository order by rpt_num asc ;
select rpt_name, rpt_num from repository order by rpt_num desc ;

3.3.5.2.多条件

当前面的条件值相同时, 再按后面的条件再排序

select stu_name, stu_height, stu_weight from student order by stu_height asc , stu_weight asc;
select stu_name, stu_height, stu_weight from student order by stu_height asc , stu_weight desc;
select stu_name, stu_height, stu_weight from student order by stu_height desc , stu_weight asc;
select stu_name, stu_height, stu_weight from student order by stu_height desc , stu_weight desc;

3.3.5.3.乱序

使用 rand() 生成随机的数字, 再根据随机的数字进行排序

这种方式在大数据量时执行效率比较低

select stu_name, stu_height, stu_weight from student 
order by rand();

3.3.6.分页 limit

limit firstRow, maxRows limit 从哪条开始( 从 0 开始 ) , 最多显示多少条

select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, team_id, stu_info from student 
limit 10;

select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, team_id, stu_info from student 
limit 0, 10;

select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, team_id, stu_info from student 
limit 10, 10;

3.3.7.聚合函数

(聚合)统计函数

count()数个数

sum() 求和

avg() 平均数

min() 最小值

max()最大值

注意 : null 不参与计算

select count(*), sum(stu_height), avg(stu_height), min(stu_height), max(stu_height)
from student ;

3.3.8.group by 分组

3.3.8.1.与聚合函数配合

• 一般 group by 和 聚合函数 配合使用

• 使用group by时 , select 列出字段, 要不就是使用聚合函数的字段, 要不就是用于 分组( group by 使用)的字段

select stu_sex, count(*), avg(stu_weight)
from student
group by stu_sex;

3.3.8.3.having

where 在 group by 之前执行 , 是为 from 的数据进行筛选

having 在 group by 之后执行, 是分组结果信息的筛选条件

select team_id, count(*), avg(stu_weight)
from student
where team_id is not null
group by team_id
having count(*) > 3;

3.3.8.4.多条件分组

根据 rpt_state , year(rpt_start_date) 两个条件进行分组

select team_id, stu_sex, count(*), avg(stu_weight)
from student
where team_id is not null and stu_sex is not null
group by team_id, stu_sex