MySQL——SQL练习题⽬（部门员⼯表）1、建表
-- 部门表
create table dept(
deptno int primary key auto_increment, -- 部门编号
dname varchar(14) , -- 部门名字
loc varchar(13) -- 地址
) ;
-- 员⼯表
create table emp(
empno int primary key auto_increment,-- 员⼯编号
ename varchar(10), -- 员⼯姓名 -
job varchar(9), -- 岗位
mgr int, -- 直接领导编号
hiredate date, -- 雇佣⽇期，⼊职⽇期
sal int, -- 薪⽔
comm int, -- 提成
deptno int not null, -- 部门编号
foreign key (deptno) references dept(deptno)
);
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','⼴州');
insert into dept values(40,'⾏政部','深圳');
insert into emp values(7369,'刘⼀','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈⼆','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周⼋','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑⼗','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭⼗⼀','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'⼤锦鲤','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'⽊有钱','职员',7782,'1983-01-23',1300,null,10);
2、练习
create database test;
use test;
create table dept(
deptno smallint(5) primary key auto_increment comment '部门编号',
deptname varchar(20) comment '部门名称',
loc varchar(20)
)
engine=INNODB,charset =UTF8;
desc dept;
create table emp(
empno smallint(5) primary key auto_increment comment '员⼯编号',
empname varchar(10) comment '员⼯姓名',
job varchar(10) comment '岗位',
mgr smallint(5) comment '直属领导编号',
hiredate date comment '⼊职⽇期',
sal int(10) comment '薪⽔',
comm int(10) comment '提成',
comm int(10) comment '提成',
deptno smallint(5) not null comment '部门编号',
foreign key (deptno) references dept(deptno)
)engine=INNODB,charset =UTF8;
desc emp;vue转react
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','⼴州');
insert into dept values(40,'⾏政部','深圳');
insert into emp values(7369,'刘⼀','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈⼆','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周⼋','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑⼗','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭⼗⼀','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'⼤锦鲤','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'⽊有钱','职员',7782,'1983-01-23',1300,null,10);
idea永久破解
select * from dept;
select * from emp;
# 查询的是同⼀张表中的不同字段，————————————》使⽤内连接
# 查询的是不同表中的不同字段，——————————————》使⽤外查询
# 查询的条件有多个，———————————————————————》使⽤⼦查询
# 更新和删除都是根据的ID，所以先⼦查询出ID，在删除和更新
-- 1．列出⾄少有⼀个员⼯的所有部门。
phpcms客服咨询select deptno ,count(deptno) deptcount from emp group by deptno;
select d.deptno ,count(e.deptno) deptcount,deptname from emp e left join dept d on e.deptno = d.deptno group by e.deptno; -- 2．列出薪⽔⽐"刘⼀"多的所有员⼯。（查询有两层及以上条件的，使⽤⼦查询）
pname from emp where sal>(select sal from emp where empname='刘⼀');
-- 3．列出所有员⼯的姓名及其直接上级的姓名。(查询的是同⼀张表中的不同字段，使⽤内连接查询）
select a.pname mgrname from emp a inner join emp b =b.empno;
-- 4．列出受雇⽇期早于其直接上级的所有员⼯。
pname from emp a inner join emp b =b.empno and a.hiredate<b.hiredate;
-- 5．列出部门名称和这些部门的员⼯信息，同时列出那些没有员⼯的部门。
select d.deptname,e.* from emp e left join dept d on d.deptno = e.deptno;
-- 6．列出所有job为“职员”的姓名及其部门名称。
pname,d.deptname from emp e left join dept d on e.deptno = d.deptno where e.job='职员';
-- 7．列出最低薪⾦⼤于1500的各种⼯作。
select distinct(job) from emp where hiredate>1500 ;
-- 8．列出在部门 "销售部" ⼯作的员⼯的姓名，假定不知道销售部的部门编号。
pname from emp e left join dept d on e.deptno = d.deptno where d.deptname='销售部';
-- 9．列出薪⾦⾼于公司平均薪⾦的所有员⼯。
select empname, sal from emp where sal>(select avg(sal) from emp ) ;
-- 10．列出与"周⼋"从事相同⼯作的所有员⼯。
select empname from emp where job=(select job from emp where empname='周⼋');
-- 11．列出薪⾦等于部门30中员⼯的薪⾦的所有员⼯的姓名和薪⾦。
pname,b.sal from emp a inner join emp b where a.deptno=30 and a.sal=b.sal;
-- 12．列出薪⾦⾼于在部门30⼯作的所有员⼯的薪⾦的员⼯姓名和薪⾦。
pname,a.sal from emp a inner join emp b where a.deptno=30 and a.sal>b.sal;
-- 13．列出在每个部门⼯作的员⼯数量、平均⼯资。
select deptname,count(d.deptno) countOfDept,avg(e.sal) from dept d left join emp e on d.deptno = e.deptno group by d.deptno; -- 14．列出所有员⼯的姓名、部门名称和⼯资。
pname,d.deptname,e.sal from emp e left join dept d on e.deptno = d.deptno ;
-- 15．列出所有部门的详细信息和部门⼈数。
select d.*,count(e.deptno) from dept d left join emp e on d.deptno = e.deptno group by d.deptno;
-- 16．列出各种⼯作的最低⼯资。
-- 16．列出各种⼯作的最低⼯资。
select job,min(sal) from emp group by job;
-- 17．列出各个部门的经理的最低薪⾦。
select deptno,min(sal) from emp where job='经理' group by deptno;
-- 18．列出所有员⼯的年⼯资，按年薪从低到⾼排序。
select empname,sal*12 yearOfSal from emp order by sal asc ;
-- 19.查出emp表中薪⽔在3000以上（包括3000）的所有员⼯的员⼯号、姓名、薪⽔。
select empno,empname,sal from emp where sal>=3000;
-- 20.查询出所有薪⽔在'陈⼆'之上的所有⼈员信息。
select * from emp where sal>(select sal from emp where empname='陈⼆');
-- 21.查询出emp表中部门编号为20，薪⽔在2000以上（不包括2000）的所有员⼯，显⽰他们的员⼯号，姓名以及薪⽔，以如下列名显⽰：员⼯编号员⼯名字薪⽔select empno 员⼯编号,empname 员⼯名字,sal 薪⽔ from emp where deptno=20 and sal>2000;
-- 22.查询出emp表中所有的⼯作种类（⽆重复）
select distinct(job) from emp ;
-- 23.查询出所有奖⾦（comm）字段不为空的⼈员的所有信息。
select * from emp where comm is not null;
-- 24.查询出薪⽔在800到2500之间（闭区间）所有员⼯的信息。（注：使⽤两种⽅式实现and以及bet
ween and）
select * from emp where sal>=800 and sal<=2500;
select * from emp where sal between 800 and 2500;
-- 25.查询出员⼯号为7521，7900，7782的所有员⼯的信息。（注：使⽤两种⽅式实现，or以及in）
select * from emp where empno=7521 or empno=7900 or empno=7782;
select * from emp where empno in(7521,7900,7782);
-- 26.查询出名字中有“张”字符，并且薪⽔在1000以上（不包括1000）的所有员⼯信息。
select * from emp where empname like '%张%' and sal>1000;
-- 27.查询出名字第三个汉字是“多”的所有员⼯信息。
select * from emp where empname like '__多';
-- 28.将所有员⼯按薪⽔升序排序，薪⽔相同的按照⼊职时间降序排序。
mysql面试题库select * from emp order by sal asc ,hiredate desc;
-- 29.将所有员⼯按照名字⾸字母升序排序，⾸字母相同的按照薪⽔降序排序。 order by convert(name using gbk) asc;
select * from emp order by convert(empname using gbk) asc,sal desc ;
-- 30.查询出最早⼯作的那个⼈的名字、⼊职时间和薪⽔。
接口urlselect empname,hiredate,sal from emp order by hiredate asc limit 1;
-- 31.显⽰所有员⼯的名字、薪⽔、奖⾦，如果没有奖⾦，暂时显⽰100.
select empname,sal,ifnull(comm,100) from emp ;
-- 32.显⽰出薪⽔最⾼⼈的职位。
select empname,sal,job from emp order by sal desc limit 1;
-- 33.查出emp表中所有部门的最⾼薪⽔和最低薪⽔，部门编号为10的部门不显⽰。
select max(sal), min(sal) from emp where deptno not in (10) ;
-- 34.删除10号部门薪⽔最⾼的员⼯。
delete from emp
where empno =(select empno from (select empno from emp where deptno=10 and sal>=all(select sal from emp where deptno=10)) as temp) ;
-- 35.将薪⽔最⾼的员⼯的薪⽔降30%。
update emp set sal=sal*(1-0.3) where empno in (pno from (select empno from emp where sal>=all(select sal from emp)) temp);
-- 36.查询员⼯姓名，⼯资和⼯资级别(⼯资>=3000 为3级，⼯资>2000 为2级，⼯资<=2000 为1级)
-- 语法：case when ... then ... when ... then ... else ... end
红上衣配什么颜裤子select empname,case when sal>3000 then '3级'
when sal>2000 then '2级'
else '1级'
end level
from emp;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论