redhat9---MySQL8练习_mysql查询工资高于奖金60%的员工-CSDN博客

--创建表
create table dept(
deptno int(2),
dname varchar(14),
loc varchar(13),
constraint pk_dept primary key(deptno)
);

--插入数据
insert into dept values(10,'accounting','new york');
insert into dept values(20,'research','dallas');
insert into dept values(30,'sales','chicago');
insert into dept values(40,'operations','boston');

emp表：

--创建表
create table emp(
empno int(4),
ename varchar(10),
job varchar(9),
mgr int(4),
hiredate date,
sal float(7,2),
comm float(7,2),
deptno int(2),
constraint pk_emp primary key(empno),
foreign key(deptno) references dept(deptno)
);

--插入数据
insert into emp values( 7369,'smith','clerk',7902,str_to_date('1980-12-17','%Y-%m-%d'),800,null,20);
insert into emp values(7499,'allen','salesman',7698,str_to_date('1981-2-20','%Y-%m-%d'),1600,300,30);
insert into emp values(7521,'ward','salesman',7698,str_to_date('1981-2-22','%Y-%m-%d'),1250,500,30);
insert into emp values(7566,'jones','manager',7839,str_to_date('1981-4-2','%Y-%m-%d'),2975,null,20);
insert into emp values(7654,'martin','salesman',7698,str_to_date('1981-9-28','%Y-%m-%d'),1250,1400,30);
insert into emp values(7698,'blake','manager',7839,str_to_date('1981-5-1','%Y-%m-%d'),2850,null,30);
insert into emp values(7782,'clark','manager',7839,str_to_date('1981-6-9','%Y-%m-%d'),2450,null,10);
insert into emp values(7788,'scott','analyst',7566,str_to_date('1987-7-13','%Y-%m-%d'),3000,null,20);
insert into emp values(7839,'king','president',null,str_to_date('1981-11-17','%Y-%m-%d'),5000,null,10);
insert into emp values(7844,'turner','salesman',7698,str_to_date('1981-9-8','%Y-%m-%d'),1500,0,30);
insert into emp values(7876,'adams','clerk',7788,str_to_date('1987-7-13','%Y-%m-%d'),1100,null,20);
insert into emp values(7900,'james','clerk',7698,str_to_date('1981-12-3','%Y-%m-%d'),950,null,30);
insert into emp values(7902,'ford','analyst',7566,str_to_date('1981-12-3','%Y-%m-%d'),3000,null,20);
insert into emp values(7934,'miller','clerk',7782,str_to_date('1982-1-23','%Y-%m-%d'),1300,null,10);

bonus表：

create table bonus(
ename varchar(10),
job varchar(9),
sal float(7,2),
comm float(7,2)
);

salgrade表：

--创建表
create table salgrade(
grade int(4),
losal int(4),
hisal int(4));

--插入数据
insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,9999);

1、找出部门30中的所有员工；

mysql> select * from emp where deptno=30;

2、列出所有办事员(CLERK)的姓名，编号和部门编号；

mysql> select ename,empno,deptno from emp where job='clerk';

3、找出奖金高于工资的员工；

mysql> select * from emp where comm>sal;

4、找出奖金高于工资的60%的员工；

mysql> select * from emp where comm>sal*0.6;

5、找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料；

--写法一：
mysql> select * from emp where deptno=10 && job='manager' || deptno=20 && job='clerk';
--写法二：
mysql> select * from emp where (deptno=10 and job='manager') or (deptno=20 and job='clerk');

6、找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其工资大于或等于2000的所有员工的详细资料；

--写法一：
mysql> select * from emp where deptno=10 && job='manager' || deptno=20 && job='clerk' || sal>=2000 && job not in('manager','clerk');
--写法二：
mysql> select * from emp where (deptno=10 and job='manager') or (deptno=20 and job='clerk') or (deptno=20 and job='clerk') or (job not in ('manager','clerk') and sal>=2000);

7、找出收取奖金的员工的不同工作；

--写法一：
mysql> select distinct job from emp where comm>0;
--写法二：
mysql> select distinct job from emp where comm is not null;

8、找出不收取奖金或者收取的奖金低于100的员工；

mysql> select * from emp where comm is null || comm<100;
mysql> select * from emp where comm is null or comm<100;

9、找出各月倒数第三天受雇的所有员工；

mysql> select * from emp having day(hiredate) in (29,28,27,26);
mysql> select * from emp where last_day(hiredate)-2=hiredate;

10、找出早于40年前受雇的员工；

mysql> select * from emp having date_format(hiredate,'%Y-%m-%d')<date_format(now(),'%Y-%m-%d')-40;

11、以首字母大写的方式显示所有员工的姓名；

mysql> select concat(upper(left(ename,1)),substring(ename,2,(length(ename)-1))) as 员工姓名 from emp;

12、显示正好为5个字符的员工的姓名；

mysql> select * from emp having length(ename)=5;

13、显示不带有“r”的员工姓名；

--包含某个字符就是大于0，不包含就是等于0
mysql> select * from emp having locate('r',ename)=0;
mysql> SELECT * FROM emp WHERE ENAME NOT LIKE '%r%';

14、显示所有员工姓名的前三个字符；

mysql> select left(ename,3) as 名字前三个字符 from emp;
mysql> SELECT SUBSTR(ename,1,3)as 名字前三个字符 FROM emp;

15、显示所有员工的姓名，用“A”替换所有的“a”；

mysql> select ename,replace(ename,'a','A')as 替换后 from emp;

16、显示满40年服务年限的员工的姓名和受雇日期；

mysql> select ename,hiredate from emp having date_format(hiredate,'%Y-%m-%d')<date_format(now(),'%Y-%m-%d')-40;

17、显示员工的详细资料，按姓名排序；

mysql> select * from emp order by ename;

18、显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面；

mysql> select ename,hiredate from emp order by hiredate ;

19、显示所有员工的姓名、工作和工资，按工作的降序排序，若工作相同则按工资升序排序；

mysql> select ename,job,sal from emp order by job desc,sal;

20、显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面；

mysql> select ename,date_format(hiredate,'%Y')as 入职年份,date_format(hiredate,'%m')as 入职月份 from emp order by date_format(hiredate,'%m'),date_format(hiredate,'%Y');

21、显示在一个月为30天的情况所有员工的日工资，忽略余数；

mysql> select ename,sal div 30 as 日工资 from emp;

22、找出(任何年份的)2月受聘的所有员工；

mysql> select *  from emp where date_format(hiredate,'%m')=2;

23、对于每个员工，显示其加入公司的天数；

mysql> select ename,timestampdiff(day,hiredate,current_date())as 入职公司天数 from emp;

24、显示姓名字段的任何位置包含“a”的所有员工的姓名；

mysql> select ename from emp having locate('a',ename)>0;
mysql> SELECT ENAME FROM emp WHERE ENAME LIKE '%a%';

25、以年月日的方式显示所有员工的服务年限；

SELECT TIMESTAMPDIFF(MONTH,hiredate,DATE_FORMAT(NOW(), '%Y-%m-%d')) div 12 as 年,
mod(TIMESTAMPDIFF(MONTH,hiredate,DATE_FORMAT(NOW(), '%Y-%m-%d')),12) as 月,
datediff(curdate(),date_add(hiredate,interval (TIMESTAMPDIFF(MONTH,hiredate,DATE_FORMAT(NOW(), '%Y-%m-%d'))) month)) as 日 
from emp;