50. 列出所有雇员的雇员名称、部门名称和薪金
create table dept( deptno int not null primary key, deptname varchar(30) not null, loc varchar(100) ); create table employee( empno int not null auto_increment primary key comment '员工编号', ename varchar(20) not null comment '员工姓名', mgr varchar(4) comment '上司的员工编号', hiredate date not null comment '雇用日期', job varchar(40) comment '职位', sal decimal(7,2), comm decimal(7,2), deptno int comment '所在部门编号', foreign key(deptno) references dept(deptno) ); 1、show tables; 2、show create table employee; 3、desc employee; 4、insert into dept values(10,'技术部','北京'); insert into employee values(9527,'scott',,'1990-01-22','ceo',5000,8000,10); 5、select * from employee; 6、insert into dept values (20,'销售部','北京'), (30,'人事部','北京'); insert into employee values (0,'zhangsan1','9527','2012-03-22','职员',5000,2500,10), (0,'zhangsan2','9522','2012-01-22','办事员',2000,3000,20), (0,'zhangsan3','9522','2012-05-22','办事员',2000,3300,20), (9522,'zhangsan4','9527','2012-07-22','总经理',5000,2500,10), (0,'smith','9559','2014-01-22','职员',3000,99,30), (9559,'zhangsan6','9527','2016-02-22','总经理',5000,2500,10), (0,'zhangsan7','9559','2014-05-22','职员',3000,,30), (0,'zhangsan8','9527','2015-05-22','经理',5000,,10), (0,'zhangsan9','9522','2014-08-22','办事员',2000,,20), (0,'zhangsanr','9559','2016-09-30','职员',3000,,30); 7、insert into dept values(40,'管理部','北京'); 8、update employee set sal=5000; 9、update employee set sal=3000 where ename='张三'; 10、update employee set sal=5000,job='销售员' where ename='张三'; 11、update employee set sal=sal+1000 where ename='张三'; 12、select * from employee where deptno=30; 13、select ename 姓名,empno 编号, deptno 部门 from employee where job='办事员'; 14、select * from employee where comm>sal; 15、select * from employee where comm>sal*0.6; 16、select * from employee where (deptno=10 and job='经理') or (deptno=20 and job='办事员'); 17、select * from employee where (deptno=10 and job='经理') or (deptno=20 and job='办事员') or (job<>'经理' and job<>'办事员' and sal>=2000); 19、select * from employee where comm is not null; 20、select * from employee where comm is null or comm<100; 21、select * from employee where monthname(hiredate)<>monthname(date_add(hiredate,interval 1 day)); 22、select * from employee where not now()between hiredate and date_add(hiredate,interval 22 year); 23、select ename from employee where ascii(left(ename,1))>=65 and ascii(left(ename,1))<=90; 24、select * from employee where length(ename)=5; 25、select * from employee where ename not like '%r%'; 26、select left(ename,3) from employee; 27、select replace(ename,'a','a') from employee; 28、select ename,date_add(hiredate,interval 10 year)10年服务年限 from employee; 29、select * from employee order by ename; 30、select ename,hiredate from employee order by timestampdiff(day,hiredate,now()) desc; 31、select ename,job,sal from employee order by job desc,sal; 32、select ename,year(hiredate)年份,month(hiredate)月份 from employee order by 年份,月份; 33、select ename,floor(sal/30) 日薪资 from employee; 34、select ename from employee where month(hiredate)=2; 35、select ename,timestampdiff(day,hiredate,now()) from employee; 36、select ename from employee where ename like '%a%'; 37、select a.deptno,a.deptname,b.人数 from dept a inner join (select deptno,count(*)人数 from employee group by deptno) b on a.deptno=b.deptno where b.人数>1; 38、select * from employee where sal>(select sal from employee where ename='smith'); 39、select a.empno 员工编号,a.ename 员工姓名,b.ename 上级姓名 from employee a left join (select * from employee) b on a.mgr=b.empno; 40、select a.ename 员工姓名,a.hiredate 入职时间,b.hiredate 上级入职时间 from employee a inner join (select * from employee) b on a.mgr=b.empno and (a.hiredate not between b.hiredate and now()); 41、select * from dept a left join employee b on a.deptno=b.deptno; 42、select * from dept a inner join employee b on a.deptno=b.deptno and job='办事员'; 43、select a.deptname,min(b.sal)最低薪资 from dept a left join employee b on a.deptno=b.deptno group by b.deptno; 44、select * from dept a inner join employee b on a.deptno=b.deptno and a.deptname='销售部'; 45、select * from employee where sal>(select avg(sal) from employee); 46、select * from employee where job=(select job from employee where ename='scott'); 47、select ename,sal from employee where sal in (select sal from employee where deptno=30); 48、select ename,sal from employee where sal>(select max(sal) from employee where deptno=30); 49、select a.*,b.人数 from dept a left join (select deptno,count(*)人数 from employee group by deptno) b on a.deptno=b.deptno; 50、select b.ename,a.deptname,b.sal from dept a inner join employee b on a.deptno=b.deptno;
如对本文有疑问, 点击进行留言回复!!
MySql添加新用户及为用户创建数据库和给用户分配权限方法介绍
Navicat出现无法远程连接MySql服务器问题的解决办法
网友评论