MQL查询

模糊查询

name like
%匹配0或多个字符，一般不用左模糊（%放在左边，导致索引失效，降低查询效率）
select id,name,age from person where name like ‘%王’
_匹配一个字符
select id,name,age from person where name like ‘_王’

分页查询

语法 limit 起始索引，个数
分页查询第m页，每页展示n条数据 limit (m-1)*n,n
select id,name,age from person limit 0,5
select id,name,age from person limit 5,5
select id,name,age from person limit 10,5

排序

order by 字段名 ASC升序/DESC降序
select id,name,age from person order by age ASC
select id,name,age from person order by age DESC

聚合函数

count() 查询有多少条记录
sum() 求和
avg() 求平均值
max() 最大值
min() 最小值
select count(0) from person
select sum(age) from person
select avg(age) from person
select max(age) from person
select min(age) from person
select cast(avg(age) as decimal(10,1)) as avg from person

分组查询

group by 字段
select dept,avg(age) from person group by dept
having条件查询 where分组前，having分组后的条件
select dept,avg(age) from person where age>10 group by dept having avg(age)>50

子查询

把查询到的结果当作另一个查询的条件
select id,name,age,dept from person where dept in (select id from dept where name=‘人事部’ or name=‘小卖部’)

显示内连接

inner join inner可以省略
select person.id pid,person.name pname,age,dept.name dname from person inner join dept on person.dept=dept.id

隐式内连接

select person.id pid,person.name pname,age,dept.name dname from person,dept where person.dept=dept.id

左外连接

left outer join 查询的结果只关注左边的表
select person.id pid,person.name pname,age,dept.name dname from person left outer join dept on person.dept=dept.id

右外连接

right outer join 查询的结果只关注右边的表
select person.id pid,person.name pname,age,dept.name dname from person right outer join dept on person.dept=dept.id