1，新建表和插入数据

创建表,设置字段的约束条件create table employee(    id int primary key 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;+--------------+-----------------------+------+-----+---------+----------------+| Field                | Type                              | Null | Key     | Default | Extra          |+--------------+-----------------------+------+-----+---------+----------------+| id                      | int(11)                            | NO   | PRI     | NULL    | auto_increment || emp_name             | varchar(20)                   | NO   |             | NULL    |                || sex                  | enum('male','female')   | NO   |             | male    |                || age                  | int(3) unsigned               | NO   |             | 28         |                || hire_date        | date                              | NO   |             | NULL    |                || post                 | varchar(50)                   | YES  |         | NULL    |                || post_comment     | varchar(100)                  | YES  |         | NULL    |                || salart               | double(15,2)                  | YES  |         | NULL    |                || office              | int(11)                           | YES  |         | NULL    |                || depart_id        | int(11)                           | YES  |         | NULL    |                |+--------------+-----------------------+------+-----+---------+----------------+10 rows in set (0.08 sec)#插入记录#三个部门：教学，销售，运营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),('xiaomage','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);

2，练习题

1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数select post,group_concat(name),count(1) from employee group by post having count(1) < 2;2. 查询各岗位平均薪资大于10000的岗位名、平均工资select * from employee where salary  not in (select avg(salary) as A from employee group by post having A > 10000);select post,avg(salary) as A from employee group by post having A > 100003. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;