mysql联合表查询练习

1查询员工的姓名、年龄、职位、部门信息。
2查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
3查询拥有员工的部门ID、部门名称。
4查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来。
5查询所有员工的工资等级。
6查询“研发部”所有员工的信息及工资等级
7. 查询“研发部”员工的平均工资。
8查询工资比“灭绝”高的员工信息8.
9.查询比平均薪资高的员工信息。
10.查询低于本部门平均工资的员工信息

create table dept(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '部门名称'
)comment '部门表';

create table emp(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表';

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

insert into dept (id, name) values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办'),(6,'人事部');
insert into emp (id, name, age, job, salary, entrydate, managerid, dept_id)
values (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
       (2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
       (3,'杨桥',33,'开发',8400,'2000-11-03',2,1),
       (4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
       (5,'常遇春',43,'开发',10500,'2004-09-07',3,1),
       (6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1),
       (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
       (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
       (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
       (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
       (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
       (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
       (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
       (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
       (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
       (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
       (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);
create table salgrade(
    grade int,
    losal int,
    hisal int
)comment '薪资等级表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

#查询员工的姓名，年龄，职位，部门信息。
select emp.name,emp.age,emp.job,dept.name from dept,emp where emp.id=dept.id;
#查询年龄小于30岁的员工姓名，年龄，职位，部门信息。
select e.name,e.age,e.age,d.name from emp as e ,dept as d where e.id=d.id and age < 30;
#查询拥有员工的部门ID,部门名称
select emp.name,dept.id,dept.name from emp,dept where emp.id=dept.id;
#4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
select emp.id,emp.name,dept.name from emp,dept where emp.id=dept.id and age>40 or emp.dept_id is null ;
select emp.id,emp.name,emp.age from emp left join dept on emp.id=dept.id where age>40;
#查询所有员工的工资等级5.
select emp.name,emp.salary,salgrade.grade from emp left join salgrade on emp.salary>=salgrade.losal and emp.salary<=salgrade.hisal;
#查询“研发部”所有员工的信息及工资等级
select emp.name,emp.salary,dept.name,salgrade.grade from emp,salgrade,dept where emp.dept_id=dept.id and dept.name='研发部' and emp.salary >= salgrade.losal and emp.salary<=salgrade.hisal;
# 查询 "研发部" 员工的平均工资
select avg(emp.salary) from emp,dept where emp.id=dept.id and dept.name='研发部';
#查询工资比"灭绝”高的员工信息
select name,salary from emp where salary > (select salary from emp where name='灭绝');
#查询比平均薪资高的员工信息
select emp.name,emp.salary from emp,dept where emp.id=dept.id and salary > (select avg(emp.salary) from emp,dept where emp.id=dept.id );
# 查询低于本部门平均工资的员工信息
select dept_id，avg(salary)  from emp group by dept_id ;  ##按照部门求出每个部门的平均工资
select e.dept_id,e.name,e.salary,e2.avgsalary from emp as e,(select dept_id,avg(salary) 'avgsalary' from emp group by dept_id) as e2 where e.salary < e2.avgsalary and e.dept_id=e2.dept_id;   ##自链接的方式

第十题，可能出现的问题： select dept_id，avg(salary)  from emp group by dept_id ;

无法分组显示 [err]1140-在没有GROUP BY的聚合查询中，SELECT列表的表达式#包含非聚合列cust cx.c.card type name’;这与sqmode=不兼容只有完整组通过

原因是：当mysql的sql_mode存在only_full_group_by的时候，在不使用group by 并且select后面出现聚集函数的话，那么所有被select的都应该是聚集函数，否则就会报错。

问题溯源 mysql5.7以上版本中有了一个STRICT mode（严格模式），而在此模式下默认是不允许设置日期的值为全0值的，所以想要解决这个问题，就需要修改sql_mode的值。 方式一

临时修改：SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));