MySQL语句

创建员工信息表

create table EmployeeInfo(
Eid int not null auto_increment primary key comment ‘员工编号’,
Ename varchar(20) comment ‘员工姓名’,
Esex char(2) comment ‘性别’,
Ebirthday date comment ‘生日’,
EAddress varchar(50) default ‘地址不详’ comment ‘地址’,
Edate date comment ‘入职时间’
)engine=InnoDB default charset=utf8;

创建员工业绩表

create table EScore(
Sid int not null auto_increment primary key comment ‘销售编号’,
id int comment ‘员工编号’,
Sdate date comment ‘销售日期’,
Smoney decimal(18,2) comment ‘销售金额’,
Sdiscount decimal(18,1) comment ‘提成比例’,
foreign key (id) references EmployeeInfo(Eid)
)engine=InnoDB default charset=utf8;

插入员工信息表数据

insert into EmployeeInfo values(1,‘张无忌’,‘男’,‘1983-11-15’,‘河南嵩山’,‘2009-10-6’),
(2,‘左冷禅’,‘男’,‘1990-3-2’,‘河北昆仑山’,‘2009-7-1’),
(3,‘段延庆’,‘男’,‘1990-9-28’,‘云南大理’,‘2012-9-12’),
(4,‘孔仲尼’,‘男’,‘1980-4-4’,‘山东曲阜’,‘2003-1-1’),
(5,‘李莫愁’,‘女’,‘1992-8-2’,‘燕大小吃街’,‘2011-10-16’),
(6,‘王语嫣’,‘女’,‘199-8-12-1’,‘燕子坞’,‘2014-1-2’);

插入员工业绩表信息
insert into escore values
(’’,1,‘2013-11-12’,64532,0.1),
(’’,2,‘2013-10-11’,46211,0.1),
(’’,2,‘2014-1-15’,7000,0.2),
(’’,3,‘2015-3-1’,83231,0.2),
(’’,1,‘2014-12-11’,8742,0.2),
(’’,3,‘2011-10-19’,23224,0.1),
(’’,5,‘2014-4-4’,50000,0.1),
(’’,5,‘2011-11-11’,90000,0.1),
(’’,6,‘2015-2-28’,30000,0.2),
(’’,4,‘2012-12-12’,99999,0.1),
(’’,4,‘2015-1-14’,84230,0.2);
1.修改 左冷禅的地址为 河北太行山
update EmployeeInfo set EAddress=‘河北太行山’ where Ename=‘左冷禅’;
2. 修改李莫愁的地址为燕大云龙小吃城 生日为 1984-10-17
update employeeinfo set EAddress=‘燕大云龙小吃城’,Ebirthday=‘1984-10-17’ where Ename=‘李莫愁’;
3. 查询全部员工信息
select * from employeeinfo;
4. 查询所有男员工的姓名、地址、生日并显示中文列名
select Ename ‘姓名’,EAddress ‘地址’,Ebirthday ‘生日’ from employeeinfo where Esex=‘男’;
5. 查询地址中包含山的员工信息
select * from employeeinfo where EAddress like ‘%山%’;
6. 查询地址中包含燕的女员工信息
select * from employeeinfo where EAddress like ‘%燕%’ and Esex=‘女’;
7. 查询员工的姓名、地址、生日、公司名称（常量列‘大软公司’）

select Ename,EAddress,Ebirthday,‘大软公司’ as ‘公司名称’ from employeeinfo;
8. 删除孔子这名员工
delete from employeeinfo where Ename=‘孔子’;
9. 查询入职时间在2011到2013年之间的员工信息
select * from employeeinfo where Edate between ‘2011-00-00’ and ‘2013-00-00’;
10.查询有销售业绩的员工信息(in 子查询)
select * from employeeinfo where Eid in(select id from escore where id<>0);
11.查询员工姓名、地址、销售日期、销售金额（表连接）
select Ename,EAddress,Sdate,Smoney from EmployeeInfo,escore where EmployeeInfo.Eid=escore.id order by Ename;
select Ename,EAddress,Sdate,Smoney from EmployeeInfo a join escore b on a.Eid=b.id order by Ename;
12.查询员工业绩表 显示 员工编号、销售日期、销售金额、提成比例、提成金额并显示为中文列名
select id ‘员工编号’,Sdate ‘销售日期’,Smoney ‘销售金额’,Sdiscount ‘提成比例’,Smoney * Sdiscount ‘提成金额’
from escore order by id;
13.查询销售金额前三的员工编号、销售日期、销售金额
select id,Sdate,Smoney from escore order by Smoney desc LIMIT 0,3;

14.统计员工人数
select count(Eid) from employeeinfo;
15.计算销售总额、销售平均值、最大销售金额、最小销售金额
select sum(Smoney) ‘计算销售总额’,avg(Smoney) ‘销售平均值’,max(Smoney) ‘最大销售金额’,min(Smoney) ‘最小销售金额’ from escore;
16.分组统计每位员工的销售总额
select id,sum(Smoney) from escore group by id;

17.分组统计每位员工的销售平均值，筛选出销售平均值大于50000的信息并按照销售平均值降序排序
select id,avg(Smoney) avg from escore group by id HAVING avg>50000 order by avg desc;