4：查询

--去重--distinct
select distinct sname from student

--排序--order by(ASC升序[默认] DESC降序)
select distinct sage from student order by sage asc

--限量--limit
select distinct sage from student order by sage asc limit 17

-- 单表查询-全部
select * from person;

-- 部分
select name,age from person;

-- 指定列名
select name as '名字', age as '年龄' from person;

-- 条件查询
select * from person where phone='13545269582';
select * from person where phone !='13545269582';
select * from person where age >= 24;

-- between(包含)
select * from person where age between 22 and 25;

-- like模糊 _单字符 %所有
-- 以朱开头
select * from person where name like '朱%';
-- 以1结尾
select * from person where name like '%1';
-- 所有带朱字的人
select * from person where name like '%朱%';

-- in 其中一个
select * from person where phone in (12645825963,12645825967);

-- 判断 空 is null not null

select * from person where name is null;
select * from person where name is not null;

/*-- 多表查询
外键:就是把一张表的主键拿到另一张表中作为一个普通字段
*/

select id from dept where deptName='明教'
select * from employee where deptNo=3

-- 子查询

select * from employee where deptNO in (select id from dept where deptName='明教')

-- 查询每个部门的平均工资--部门只有序号
select deptNO,avg(salary) from employee group by deptNO

-- 每个部门和员工平均工资一起查询(显示部门名)
select dept.deptName,avg(employee.salary) from dept,employee where dept.id=employee.deptNO group by dept.deptName

-- 关联查询 on
-- inner join 内链接
-- 通常关联项on后面的就是主外键
select * from dept join employee on dept.id=employee.deptNO

-- 左联 left join
select * from employee left join dept on dept.id=employee.deptNO

-- 查看所有员工的部门
select employee.name,dept.deptName from employee left join dept on employee.deptNO=dept.id
-- 右链接

-- 聚合函数--group by
-- 年龄最大
select max(age) from person
-- 年龄最小
select min(age) from person
-- 平均年龄
select avg(age) from person
-- 一共多少人
select count(*) from person
-- 求所有年龄之和
select sum(age) from person

-- 求每个班级的平均年龄
select cls,avg(age) from person group by cls
-- 查询每个班级的最大年龄
select cls,max(age) from person group by cls

-- having 一般在聚合函数或group by 以后进行分组在进行条件筛选
-- 查询每个班级有多少人
select cls,count(*) from person group by cls
-- 查询班级人数小于3人
select cls,count(*) from person group by cls having count(*) < 3

-- order by 排序 默认升序asc 降序desc
select * from person order by age asc

select * from person order by age desc, phone asc