JAVA__MySql

 

   --数据库的表设计如下： 

   --部门：部门编号，部门名称，地址； 

   --员工：员工编号，员工名字，职务，管理编号，入职日期，薪资，奖金，部门编号； 

   --创建部门表： 

   CREATE TABLE dept( 

   deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(20)  

   );

   CREATE TABLE emp(empno INT PRIMARY KEY,ename VARCHAR(20) NOT NULL,   

   job VARCHAR(20) CHECK (jobIN('CLERK','SALESMAN','MANAGER','ANALYST')),mgp INT, 

   hiredate DATE ,sal DECIMAL(10,2),comm DECIMAL(10,2), 

   deptno INT, 

   CONSTRAINT pk_we FOREIGN KEY (deptno) REFERENCES dept (deptno) 

    );

 

--部门表中插入数据： 

   INSERT INTO dept VALUES (10,'ACCOUNTING','NEWTORK'); 

   INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 

   INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 

   INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

 --员工表中插入数据： 

    insert into empvalues(7369,'SMITH','CLERK',7902,'1980-12-17',1640,NULL,20);

   insert into empvalues(7499,'ALLEN','SALESMAN',7698,'1981-2-20',11400,300,30); 

   insert into empvalues(7521,'WARD','SALESMAN',7698,'1981-2-22',5200,500,30); 

   insert into empvalues(7566,'JOENS','MANAGER',7839,'1981-4-2',7015,NULL,20); 

   insert into empvalues(7654,'MARTIN','SALESMAN',7698,'1981-9-28',5200,1400,30); 

   insert into empvalues(7698,'BLAKE','MANAGER',7839,'1981-5-1',5900,NULL,30); 

   insert into empvalues(7782,'CLARK','MANAGER',7839,'1981-6-9',2470,NULL,10); 

   insert into empvalues(7788,'SCOTT','ANALYST',7566,'1987-4-19',3040,NULL,20);

 

1、列出至少有一个雇员的所有部门

select dname,count(empno) as 部门人数 from dept,emp wheredept.deptno=emp.deptno

 group by dname

 having count(empno)>=1;

 

2、列出薪金比"SMITH"多的所有雇员

select * from emp where sal>(select salfrom emp where ename='SMITH');

 

3. 列出入职日期早于其直接上级的所有雇员

select * from emp e1 where hiredate<(select hiredate from emp e2 where e2.empno=e1.mgp);

 

4. 找员工姓名和直接上级的名字

select e1.ename 员工姓名,e2.ename 直接上级 from emp e1,emp e2 wheree1.mgp=e