mysql单表查询

环境：

#我们来创建一个员工表，然后对员工表进行一个简单的查询，来看一下效果，下面是员工表的字段company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int

创建表

create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum(‘male’,‘female’) not null default ‘male’, #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

查看表的结构 ：

mysql> desc employee;

插入记录

三个部门：教学，销售，运营

insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘egon’,‘male’,18,‘20170301’,‘老男孩驻沙河办事处外交大使’,7300.33,401,1), #以下是教学部，全都是老师
(‘alex’,‘male’,78,‘20150302’,‘teacher’,1000000.31,401,1),
(‘wupeiqi’,‘male’,81,‘20130305’,‘teacher’,8300,401,1),
(‘yuanhao’,‘male’,73,‘20140701’,‘teacher’,3500,401,1),
(‘liwenzhou’,‘male’,28,‘20121101’,‘teacher’,2100,401,1),
(‘jingliyang’,‘female’,18,‘20110211’,‘teacher’,9000,401,1),
(‘jinxin’,‘male’,18,‘19000301’,‘teacher’,30000,401,1),
(‘成龙’,‘male’,48,‘20101111’,‘teacher’,10000,401,1),

(‘歪歪’,‘female’,48,‘20150311’,‘sale’,3000.13,402,2),#以下是销售部门
(‘丫丫’,‘female’,38,‘20101101’,‘sale’,2000.35,402,2),
(‘丁丁’,‘female’,18,‘20110312’,‘sale’,1000.37,402,2),
(‘星星’,‘female’,18,‘20160513’,‘sale’,3000.29,402,2),
(‘格格’,‘female’,28,‘20170127’,‘sale’,4000.33,402,2),

(‘张野’,‘male’,28,‘20160311’,‘operation’,10000.13,403,3), #以下是运营部门
(‘程咬金’,‘male’,18,‘19970312’,‘operation’,20000,403,3),
(‘程咬银’,‘female’,18,‘20130311’,‘operation’,19000,403,3),
(‘程咬铜’,‘male’,18,‘20150411’,‘operation’,18000,403,3),
(‘程咬铁’,‘female’,18,‘20140512’,‘operation’,17000,403,3)
;
查看表中内容：

mysql> select * from employee;

简单查询

1 查出所有员工的名字，薪资,格式为
<名字:egon> <薪资:3000>

mysql> select concat('名字：',name),concat('工资:',salary) 
    -> from employee;

2 查出所有的岗位（去掉重复）

mysql> select distinct post 
    -> from employee;

3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year

mysql> select name,salary*12 annual_year 
    -> from employee;

条件查询

1.查看岗位是teacher的员工姓名、年龄

mysql> select name,age 
    -> from employee
    -> where post='teacher';

2.查看岗位是teacher且年龄大于30岁的员工姓名、年龄

mysql> select name,age from employee 
    -> where age>30 and post='teacher';

3.查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资

mysql> select name,age,salary 
    -> from employee
    -> where salary between 9000 and 10000;

4.查看岗位描述不为NULL的员工信息

mysql> select  * from employee where post_comment is not null;

5.查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资

mysql> select name,age,salary  
    -> from employee 
    -> where post='teacher' 
    -> and salary in (10000,9000,30000);

6.查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资

mysql> select name,age,salary from employee  where post='teacher'  and salary not in (10000,9000,30000);

7.查看岗位是teacher且名字是jin开头的员工姓名、年薪

mysql> select name,salary 
    -> from employee 
    -> where name like'jin%';

分组查询

1.查询岗位名以及岗位包含的所有员工名字

mysql> select post,group_concat(name)
    -> from employee 
    -> group by post;

`

#通过上面的需求来整理逻辑：a、先看一下和哪个表有关系：所有的信息都在employee这个表里面，所以先写from employee，找到表了 b、看有没有什么过滤条件，大于小于啊什么的，没有吧，所以是不是不需要写where条件啊 c、看看有没有分组的内容，也就是看看上面的需求里面有没有分类的概念，发现是不是有啊，按照岗位来分组，对不对，所以该写什么了：from employee group by post； d、然后再看需要查什么字段出来，发现是不是要看岗位名和所有员工的名字啊，所以怎么写：select post,group_concat(name) from employee group by post;这就是完整语句了，不信你试试

下面的题都按照上面这个逻辑来搞一搞：
2. 查询岗位名以及各岗位内包含的员工个数

mysql> select post,count(1)
    -> from employee 
    -> group by post;

3.查询公司内男员工和女员工的个数

mysql> select sex,count(1)
    -> from employee 
    -> group by sex;

4.查询岗位名以及各岗位的平均薪资

mysql> select post,avg(salary)
    -> from employee 
    -> group by post;

5.查询岗位名以及各岗位的最高薪资

mysql> select post,max(salary) from employee group by post;

6.查询岗位名以及各岗位的最低薪资

mysql> select post,min(salary) from employee group by post;

7.查询男员工与男员工的平均薪资，女员工与女员工的平均薪资。 #这道题我们自己提炼一下分组依据，是不是就是性别啊

mysql> select sex,avg(salary) from employee group by sex;

having过滤

1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

mysql> select post,group_concat(name),count(1)
    -> from employee 
    -> group by post
    -> having count(1)<2;

2. 查询各岗位平均薪资大于10000的岗位名、平均工资

mysql> select post,avg(salary) from employee group by post having avg(salary)>10000;

3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

mysql> select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;

查询排序

1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序

mysql> select * from employee  order by age asc,hire_date desc;

2.查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列

mysql> select post,avg(salary) 
    -> from employee
    -> group by post
    -> having avg(salary)>10000
    -> order by avg(salary) asc;

3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列

mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;

限制记录

1.取出工资最高的前三位

mysql> select salary from employee order by salary desc limit 3;

正则表达式查询

1.查看所有员工中名字是jin开头，n或者g结果的员工信息

mysql> select * from employee where name regexp '^jin.*[ng]$';