mac终端操作数据库--(5)一道综合题

mac终端操作数据库–(5)一道综合题

1. 题目

职员表

部门表

2. 创建数据库和表,插入数据

//登录MySQL
/usr/local/mysql/bin/mysql -u root -p

//创建数据库
1.create database zhouyu03;
2.use zhouyu03;
3.select database();

//创建表
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
);

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);
INSERT INTO emp VALUES(7981,'MILLER','CLERK',7788,'1992-01-23',2600,500,20);


CREATE TABLE dept(
	deptno INT,
	dname VARCHAR(14),
	loc VARCHAR(13)
);

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');

3. 相关问题与解答

//基本查询
//1.查询所有员工信息
select *from emp;
//2.薪资大于等于1000并且小于等于2000的员工信息
select *from emp e where e.sal >= 1000 and e.sal <= 2000;
select *from emp e where e.sal between 1000 and 2000;
select *from emp e where e.sal between 1000 and 2000 order by desc;
//3.从员工表中查询出所有部门的编号
select distinct e.deptno from emp e;
select distinct e.deptno as "所有部门编号" from emp e;
select distinct e.deptno "所有部门编号" from emp e;
//4.查询出名字以A开头的员工信息
select *from emp e where e.ename like "A%";
//5.查询出名字第二个字母是L的员工的信息
select *from emp e where e.ename like "_L%";
//6.查询出没有奖金的员工信息
select *from emp e where e.comm is null or e.comm = 0;
//7.所有员工的平均工资
select AVG(e.sal) from emp e;
//8.所有员工的工资总和
select SUM(e.sal) from emp e;
//9.所有员工的数量
select COUNT(*) from emp e;
//10.最高工资
select MAX(e.sal) from emp e;
//11.最少工资
select MIN(e.sal) from emp e;
//12.最高工资的员工信息
select *from emp e where e.sal = (select MAX(e.sal) from emp e);

//分组查询
//13.每个部门的平均工资
select *from dept d;
select d.deptno from dept d;
select d.dname,AVG(e.sal) from emp e, dept d where d.deptno = e.deptno group by d.dname;
select d.dname "部门名称",AVG(e.sal) "部门平均工资" from emp e, dept d where d.deptno = e.deptno group by d.dname;

//子查询

//单行子查询(>, <, >=, <=, =, <>)
//14.查询出高于10号部门的平均工资的员工信息: 1.10号部门的平均工资 2.高于10号部门的平均工资的员工信息
select AVG(e.sal) from emp e where e.deptno = 10;
select *from emp e where e.sal > (select AVG(e.sal) from emp e where e.deptno = 10);

//多行子查询(in, not in, any, all)
//15.查询出比20号部门任何员工工资都高的员工信息: 
//1.20号部门最高的工资 2.查询出比20号部门任何员工工资都高的员工信息
select MAX(e.sal) from emp e where e.deptno = 20;
select *from emp e where e.sal > (select MAX(e.sal) from emp e where e.deptno = 20);
//2.20号部门的所有工资信息 2.得出结果
select e.sal from emp e where e.deptno = 20;
select *from emp where sal > ALL(select sal from emp where deptno = 20);


//多列子查询(in)
//16.和10号部门同名同工作的员工信息: 1.10号部门员工姓名,工作信息 2.结果
select ename,job from emp where deptno = 10;
select *from emp where (ename,job) in (select ename,job from emp where deptno = 10) and deptno != 10;

//select接子查询
//17.获取员工的名字和部门的名字: 1.获取员工名字和部门id 2.获取员工部门名字
select e.ename,e.deptno from emp e;
select e.ename,(select d.dname from dept d where d.deptno = e.deptno) from emp e;
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;


//where接子查询
//18.薪资高于10号部门平均工资的员工信息
同14题

//having后面接子查询
//19.有哪些部门的平均工资高于30号部门的平均工资: 1.30号部门的平均工资 2.所有部门的平均工资 3.对比得出结果
select AVG(sal) from emp where deptno = 30;
select deptno,AVG(sal) from emp group by deptno;
select deptno,AVG(sal) from emp group by deptno having AVG(sal) > (select AVG(sal) from emp where deptno = 30);

//总和查询
//20.查询高于本部门平均工资的员工信息: 1.获取所有部门的平均工资 2.得出结果
select e.deptno,AVG(e.sal) from emp e group by e.deptno;
select *from emp e1 where e1.sal > (select AVG(e2.sal) from emp e2 where e1.deptno = e2.deptno group by e2.deptno);

//21.列出达拉斯加工作的人中,比纽约的平均工资高的员工信息: 1.找出纽约城市 2.纽约的平均工资 3.达拉斯加工作的员工 4.计算出结果
select deptno from dept where loc = "NEW YORK";
select AVG(e.sal) from emp e where e.deptno = (select d.deptno from dept d where d.loc = "NEW YORK");
select d.deptno from dept d where d.loc = "DALLAS";
select *from emp e1 where e1.deptno = (select d1.deptno from dept d1 where d1.loc = "DALLAS") and e1.sal > (select AVG(e2.sal) from emp e2 where e2.deptno = (select d2.deptno from dept d2 where d2.loc = "NEW YORK"));


//22.查询出各个部门薪资最高的员工信息 1.各个部门的最高薪资 2.本部门最高子薪资员工信息
select MAX(e1.sal) from emp e1 group by e1.deptno;
select *from emp e2 where e2.sal = (select MAX(e1.sal) from emp e1 where e2.deptno = e1.deptno group by e1.deptno);