mysql 单表 软连接_mysql的单表查询和连表查询

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 > 10000

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

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