MySQL

as起别名

# 作用：给显示结果中字段名表名 起别名
select id as '序号' from 表名;
# 在 MySQL 中 as 可以省略
select id '序号' from 表名;

distinct去重

# 作用：把结果中的重复行去除
select distinct 字段名 from 表名;

limit分页

# 起始下标从0开始
select * from subject limit 1,3;
# 排序 + 分页(先排序后分页)
select id from subject order by id desc limit 5;  

order by排序

asc 升序(ascend)    desc 降序(descend)
select * from students order by age asc;(升序默认可以不写)
select * from students order by age desc;
# 多个字段排序意义(当条件1重复时按条件2排序)
select * from students order by age, height desc;

group by分组

-- 1 group_concat  将每个分组的某个字段的数据拼接在一起显示
-- select * from students group by gender;  分组数据不再是二维结构所以出错
select gender from students group by gender;
mysql> select gender,group_concat(name),group_concat(id) from students group by gender;
-- 2 聚合+分组
-- 默认情况下聚合是把整个表当做一个组得到一个值
-- 结合使用 聚合会将每个小分组分别进行统计计算 得到一个值   
select gender,group_concat(name),avg(age) from students group by gender;
-- 3 分组条件   having 条件
-- 查看分组      平均年龄小于 100 岁的分组
select gender,group_concat(name),avg(age) from students group by gender having  avg(age) < 100;
mysql> select gender,group_concat(name) from students group by gender having count(*) > 3;
-- 4 新增汇总行 with rollup 将整个表进行一个汇总处理
mysql> select gender,group_concat(name),avg(age) from students group by gender with rollup;

where

# 作用：筛选表中满足条件的记录
# 关系运算符 <> != 不等于 =
select * from students where age != 100;
select * from students where age <> 100;
# 逻辑运算符 and or not (并且 或者 取反)
select * from students where age<100 and height<160;
select * from students where age<100 or height<160;
select * from students where not age>100;
# 模糊查询 like  %(任意个任意字符) _(一个任意字符)
select name from subject where name like '%语%';
select name from subject where name like '_语%';
# 连续范围
select * from subject where 10 < id < 5;
select * from subject where id between 10 and 20;
# 非连续范围
select * from subject where id in (1,5,15);
select * from subject where id not in (1,5,15);
# 判空操作 is null(是空) is not null(是非空) 
select * from students where height is null;
select * from students where height is not null;

聚合函数

count 数量  sum 和  avg 平均数  max 最大值  min 最小值 
-- 求出班级中学生数量
select count(age) from students;
select count(height) from students;
select count(*) from students;
-- 求出所有学生的年龄之和
select sum(age) from students;
-- 求出所有学生的平均年龄
select sum(age)/count(age) from students;
select avg(age) from students;
-- 最高身高
select max(height) from students;
-- 最小年龄
select min(age) from students;
-- ifnull 会将字段为 NULL的以 参数 2的值返回  最终参与运算
select avg(ifnull(height,200)) from students;