SQL数据库表结构和初始化数据：员工信息管理系统

{/'title/':/'表结构和初始化数据/',/'description/':/'本页面展示了一个简单的员工信息管理系统的数据库设计，包括表结构定义、初始化数据以及一些常见的SQL查询语句示例。/',/'keywords/':/'SQL, 数据库, 表结构, 初始化数据, 员工信息管理系统, 查询语句/',/'content/':/'///'drop database if exists emps;//n//ncreate database emps;//n//nuse emps;//n//ncreate table emp(//n//nEMPNO INT, -- 员工号//n//nENAME VARCHAR(10), -- 员工姓名//n//nJOB VARCHAR(9), -- 工作岗位//n//nMGR int, -- 经理的员工号，外键//n//nHIREDATE date, -- 入职时间//n//nSAL double, -- 底薪//n//nCOMM double, -- 提成//n//nDEPTNO int, -- 部门编号//n//nprimary key(EMPNO)//n//n) ;//n//n//nCREATE TABLE dept (//n//nDEPTNO int, -- 部门编号//n//nDNAME varchar(13), -- 部门名称//n//nLOC VARCHAR(13), -- 部门地点//n//nprimary key(DEPTNO)//n//n) ;//n//n//n-- 插入数据//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,'2003-12-17',800,null,20);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,'2007-9-3',1600,300,30);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,'2005-3-8',1250,500,30);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,'2007-7-7',2975,null,20);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,'2005-5-6',1250,1400,30);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,'2005-5-6',2850,null,30);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,'2010-3-1',2450,null,10);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,'2005-5-6',3000,null,20);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,'2001-5-6',5000,null,10);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,'2008-8-8',1500,0,30);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,'2005-5-6',1100,null,20);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,'2005-5-6',950,null,30);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,'2005-5-6',3000,null,20);//n//nInsert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,'2005-5-6',1300,null,10);//n//n//nInsert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');//n//nInsert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');//n//nInsert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');//n//nInsert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');//n//n//n-- 1．列出至少有一个员工的所有部门。//n//n-- 2．列出薪金比“SMITH”多或者相等的所有员工。//n//n-- 3．列出所有员工的姓名及其直接上级的姓名。//n//n-- 4．列出受雇日期早于其直接上级的所有员工。//n//n-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门//n//n-- 6．列出所有“CLERK”（办事员）的姓名及其部门名称。//n//n-- 7．列出最低薪金大于1500的各种工作。//n//n-- 8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。//n//n-- 9．列出薪金高于公司平均薪金的所有员工。//n//n-- 10．列出与“SCOTT”从事相同工作的所有员工。//n//n内容：表结构和初始化数据已给出，以下是根据表结构和初始化数据给出的SQL查询语句：//n//n1. 列出至少有一个员工的所有部门。//n//nsql//nSELECT DISTINCT d.DNAME//nFROM DEPT d//nINNER JOIN EMP e ON d.DEPTNO = e.DEPTNO;//n//n//n2. 列出薪金比“SMITH”多或者相等的所有员工。//n//nsql//nSELECT *//nFROM EMP//nWHERE SAL >= (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');//n//n//n3. 列出所有员工的姓名及其直接上级的姓名。//n//nsql//nSELECT e.ENAME, m.ENAME AS MANAGER//nFROM EMP e//nLEFT JOIN EMP m ON e.MGR = m.EMPNO;//n//n//n4. 列出受雇日期早于其直接上级的所有员工。//n//nsql//nSELECT e.*//nFROM EMP e//nINNER JOIN EMP m ON e.MGR = m.EMPNO//nWHERE e.HIREDATE < m.HIREDATE;//n//n//n5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。//n//nsql//nSELECT d.DNAME, e.*//nFROM DEPT d//nLEFT JOIN EMP e ON d.DEPTNO = e.DEPTNO;//n//n//n6. 列出所有“CLERK”（办事员）的姓名及其部门名称。//n//nsql//nSELECT e.ENAME, d.DNAME//nFROM EMP e//nINNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO//nWHERE e.JOB = 'CLERK';//n//n//n7. 列出最低薪金大于1500的各种工作。//n//nsql//nSELECT DISTINCT JOB//nFROM EMP//nWHERE SAL > 1500;//n//n//n8. 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。//n//nsql//nSELECT e.ENAME//nFROM EMP e//nINNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO//nWHERE d.DNAME = 'SALES';//n//n//n9. 列出薪金高于公司平均薪金的所有员工。//n//nsql//nSELECT *//nFROM EMP//nWHERE SAL > (SELECT AVG(SAL) FROM EMP);//n//n//n10. 列出与“SCOTT”从事相同工作的所有员工。//n//nsql//nSELECT *//nFROM EMP//nWHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'SCOTT');//n//n//n/