手撸Mysql原生语句--单表
select from where group by having order by limit
上面的所有操作是有执行的优先级的顺序的，我们将执行的过程可以总结为下面所示的七个步骤。
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.执行select（去重）
5.将分组的结果进行having过滤
6.将结果按条件排序：order by
7.限制结果的显示条数
select from的使用
简单查询
SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee;
SELECT * FROM employee;
SELECT emp_name,salary FROM employee;
避免重复DISTINCT
SELECT DISTINCT post FROM employee;
通过四则运算：
SELECT emp_name, salary*12 FROM employee;
SELECT emp_name, salary*12 AS Annual_salary FROM employee;
SELECT emp_name, salary*12 Annual_salary FROM employee;
1.查出所有员工的名字，薪资，格式为：<名字:egon> <薪资:3000>
select concat('<名字:',emp_name,'> ','<薪资:',salary,'>') from employee;
2.查询所有的岗位（去掉重复的岗位）
select distinct depart_id from employee;
3.查询所有员工的名字以及他们的年薪，年薪的字段名为annual_year
select emp_name, salary*12 annual_year from employee;
where约束
1.查看岗位是teacher的员工的姓名、年龄
select emp_name,age from emp_name where post = 'teacher';
2.查看岗位是teacher且年龄大于30岁的员工的姓名、年龄
select emp_name,age from employee where post ='teacher' and age >30;
3.查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
select emp_name,age,salary from employee where post ='teacher' and salary between 9000 and 10000;
select emp_name,age,salary from employee where post ='teacher' and salary >=9000 and salary <=10000;
4.查看岗位描述不为null的员工信息
select * from employee where post_comment is not null;
5.查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资。
select emp_name,age,salary from employee where post = 'teacher' and salary in (10000,9000,30000);
6.查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资。
select emp_name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
7.查看岗位是teacher且名字是jin开头的员工姓名、年薪
select emp_name,salary*12 annual_year from employee where post = 'teacher' and emp_name like 'jin%';
select emp_name,salary*12 annual_year from employee where post = 'teacher' and emp_name regexp '^jin';
group by--聚合(sum,avg,min,max,count)的使用
1.查询岗位名及岗位包含的所有员工名字
select post, group_concat(emp_name) from employee group by post;
select post, group_concat(emp_name) as emp_members from employee group by post;
2.查询岗位名以及岗位内包含的员工个数
select post,count(id) from employee group by post;
3.查询公司内男员工和女员工的个数
select sex,count(id) from employee group by sex;
4.查询岗位名以及各岗位的平均薪资
select post,avg(salary) from employee group by post;
5.查询岗位名以及各个岗位的最高薪资
select post,max(salary) from employee group by post;
6.查询敢为名以及各个岗位的最低信息
select post,min(salary) from employee group by post;
7.查询男员工与男员工的平均薪资，女员工和女员工的平均薪资
select sex,avg(salary) from employee group by sex;
8.查询岗位名以及岗位包含员工的所有薪资的总和
select post,sum(salary) from employee group by post;
having过滤的使用
！！！执行优先级从高到低：where > group by > having
- Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
- Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(emp_name),count(id) from employee group by post having count(id)<2;
2.查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) >10000;
3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均薪资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;
order by查询排序的使用
1.查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from employee order by age asc,hire_date desc;
2.查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) asc;
3.查询各岗位平均薪资大于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]$';
