-- 去重查询 distinct
select distinct gender from students;

-- 比较运算符 = < > <= >= != <> 
select * from students where age<>18;

-- 逻辑运算符 and or not
select * from students where age=18 and gender='男';

select * from students where age < 18 or age > 28;

select * from students where not (age=18 and gender='男');

-- 以关键字开头 xxx%
select * from students where name like '小%';

-- 关键字在name中 %xxx%
select * from students where name like '%小%';

-- 查询有两个字的名字 _
select * from students where name like '__';

-- 至少有两个字
select * from students where name like '__%';

-- in 在某个非连续范围内
select name,age from students where age in (18,34);

-- not in 不再某个范围
select name,age from students where age not in (18,34);

-- between 在某个连续的范围内
select name,age from students where age between 18 and 34;

-- not between 不再连续的范围内
select name,age from students where age not between 18 and 34;

-- 列空 is null
select * from students where height is null;

-- 列非空 is not null
select * from students where height is not null;

-- 生序 asc
select * from students where age between 18 and 34 order by height asc;

-- 降序 desc, 多个字段排序用逗号分隔
select * from students where age between 18 and 34 order by height desc, age desc;

-- 求总数 count
select count(*) from students where gender=1;

-- 最大值 max
select max(age) from students;

-- 最小值 min
select min(height) from students;

-- 求和 sum
select sum(age) from students;

-- 平均值 avg
select avg(age) from students;

-- 四舍五入 round
select round(avg(age), 2) from students;

-- 分组 group by 
select gender,count(*) from students group by gender;

-- 显示分组信息 group_concat(...)
select group_concat(name),gender from students group by gender;

-- 与聚合函数同用
select avg(age),gender from students group by gender;

-- 对每个组中的数据筛选(过滤的是组，不是组中元素) having
select group_concat(name), gender from students group by gender having avg(age) > 30;

-- 显示汇总结果(放在语句最后) with rollup 
select group_concat(name),avg(age),gender from students group by gender with rollup;

-- limit 起始记录,记录数
-- 查询前五条数据
select * from students limit 5;

-- 从第零个数据开始，显示两条
select * from students limit 0,2;

-- 从第一个数据开始，显示两条
select * from students limit 1,2;

-- 先按年龄排序，然后分页查询
select * from students order by age desc limit 0,2;

-- select 字段 from 表1 inner join 表2 on 表1.字段1=表2.字段2
select * from students inner join classes;

-- on 添加连接条件 别名必须使用，否则会报错
select a.name, b.name from students as a inner join classes as b on a.cls_id=b.id;

select a.name, b.name from students as a left join classes as b on a.cls_id=b.id;

select a.name, b.name from students as a right join classes as b on a.cls_id=b.id;

-- 建表
create table areas(
    id varchar(30) not null primary key, 
    title varchar(30), 
    pid varchar(30)
);
-- 导入数据
source areas.sql;
-- 通过自连接，获取所有省份与城市信息
select province.title, city.title  from areas as city inner join areas as province on city.pid = province.id where province.pid is null;

-- 查询所有大于平均身高的学生信息
select * from students where height >(select avg(height) from students);