MySQL单表查询

where子句（原始数据上做的筛选） on(可在临时数据上可以做筛选)

• select * from user where phone = ‘123456789’
• select * from user where phone != ‘123456789’
• select * from user where age>=20
• select * from user where age between 18 and 25
• select * from user where name is not null
• select * from user where name like '朱%'
• select * from user where name like '%朱%'
• select * from user where phone in （‘123456’，‘234567’）

聚合函数

• select max(age) from user
• select min(age) from user
• select avg(age) from user
• select count(*) from user
• select sum(age) from user
• 一般配合着 group by 进行使用
• select class，avg（age） from user group by class

having子句（分组查询之后、聚合函数计算之后 的结果中进行的筛选）

• select class，count（） from user group by class having count（）>2

order by 排序

• asc
• desc

MySQL多表查询

子查询（从一个表里查出一个字段，再从另一个表中查出想要的结果）

• 外键：一张表的主键拿到另一张表中做普通字段，外键可以把两张表连接起来
• select * from emp where deptNo in (select id from dept where deptName = '张三')
• select dept.deptName,avg(emp.salary) from dept,emp where emp.No=dept.id group by dept.deptName

关联查询

内连接

• inner join
  • select * from dept inner join emp on dept.id=emp.deptNo 外连接
• left join(左所有和右关联项)
  • 查询所有员工的部门信息
  • select emp.name,dept.deptName from emp left join dept on emp.deptNo=dept.id
• right join(右所有和左关联项)