目录
5、找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料;
6、找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其工资大于或等于2000的所有员工的详细资料;
18、显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面;
19、显示所有员工的姓名、工作和工资,按工作的降序排序,若工作相同则按工资升序排序;
20、显示所有员工姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面;
21、显示在一个月为30天的情况所有员工的日工资,忽略余数;
dept表:
--创建表
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;