mysql基础 -- 单表练习

drop table if exists emp;
create table emp(
empno int,
ename varchar(50),
job varchar(50),
mgr int,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int
)engine=innodb default charset=utf8;

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

1. 查询工资大于1200的员工姓名和工资

SELECT ename,sal 
FROM emp 
WHERE sal > 1200;

2. 查询员工号为7698的员工的姓名和部门号

SELECT ename,deptno 
FROM emp 
WHERE empno = 7698;

3. 选择工资不在500到1200的员工的姓名和工资

SELECT ename,sal 
FROM emp 

WHERE NOT ( sal BETWEEN 500 AND 1200 );
4. 选择雇用时间在1981-02-01到1987-05-01之间的员工姓名，编号和雇用时间

SELECT ename, empno, hiredate 
FROM emp 
WHERE hiredate BETWEEN "1981-02-01" AND "1987-05-01";

5. 选择在20或30号部门工作的员工姓名和部门号

SELECT ename, deptno
FROM emp
where deptno in (20,30);

6. 选择在1981年雇用的员工的姓名和雇用时间

SELECT ename, hiredate
FROM emp
WHERE YEAR(hiredate)="1981";

7. 选择公司中没有管理者的员工姓名及empno

SELECT ename, empno
FROM emp
WHERE mgr IS NULL;

8. 选择公司中有奖金的员工姓名，工资和奖金级别

SELECT ename, sal, comm
FROM emp
WHERE comm is not null and not CEIL(comm) = 0;

9. 选择员工姓名的第三个字母是a的员工姓名

SELECT ename
FROM emp 
where ename LIKE "__a%";

10. 选择姓名中有字母a和e的员工姓名

SELECT ename
FROM emp
WHERE ename LIKE "%a%" or ename LIKE "%e%";

11. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

SELECT empno, ename, sal, sal*1.2 "new salary"
FROM emp;

12. 将员工的姓名按首字母排序

select ename
FROM emp
ORDER BY ename;

13. 查询公司员工工资的最大值，最小值，平均值，总和

SELECT max(sal), min(sal), avg(sal), sum(sal)
from emp;

14. 查询各deptno的员工工资的最大值，最小值，平均值，总和

SELECT max(sal), min(sal), avg(sal), sum(sal), deptno
FROM emp
GROUP BY deptno;

15. 选择具有各个deptno的员工人数

SELECT deptno, count(*)
FROM emp
GROUP BY deptno;