手撸Mysql原生语句--单表 (2)

查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) asc;

查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;

limit限制查询的记录数

select * from employee limit 0,5;
select * from employee limit 5,5;
select * from employee limit 10,5;

备注：limit后面的两个数字分别表示的含义为：第一个数字表示的含义是从第几条数据开始，第二个数字表示的是需要取出几条的数据。

使用正则表达式查询

select * from employee where emp_name REGEXP '^ale';
SELECT * FROM employee WHERE emp_name REGEXP 'on$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';

小结：对字符串匹配的方式
WHERE emp_name = 'egon';
WHERE emp_name LIKE 'yua%';
WHERE emp_name REGEXP 'on$';

1.查看所有员工中名字是jin开头，n或者g结果的员工信息。
select * from employee where emp_name regexp '^jin.*[ng]$';