部门表

CREATE TABLE dept( deptno INT PRIMARY KEY, '部门编号' dname VARCHAR(14), '部门名称' loc VARCHAR(13) '部门地址' ); #部门表数据 INSERT INTO dept VALUES (10, '财务部', '北京'), (20, '市场部', '上海'), (30, '销售部', '广州'), (40, '运营部', '深圳');

员工表

CREATE TABLE emp( empno INT PRIMARY KEY, #员工编号 ename VARCHAR(10), #员工姓名 job VARCHAR(20), #员工工作 mgr INT, #员工直属领导编号 hiredate DATE, #入职时间 sal DOUBLE, #工资 comm DOUBLE, #奖金 deptno INT #对应dept表的外键 );

添加 部门 和 员工 之间的主外键关系

ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY emp(deptno) REFERENCES dept (deptno);

INSERT INTO emp VALUES(7369, 'smith', '保洁工作', 7902, '1980-12-17', 800, NULL, 20); INSERT INTO emp VALUES(7499, 'allen', '销售工作', 7698, '1981-02-20', 1600, 300, 30); INSERT INTO emp VALUES(7521, 'ward', '销售工作', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO emp VALUES(7566, 'jones', '管理工作', 7839, '1981-04-02', 2975, NULL, 20); INSERT INTO emp VALUES(7654, 'martin', '销售工作', 7698, '1981-09-28', 1250, 1400, 30); INSERT INTO emp VALUES(7698, 'blake', '管理工作', 7839, '1981-05-01', 2850, NULL, 30); INSERT INTO emp VALUES(7782, 'clark', '管理工作', 7839, '1981-06-09', 2450, NULL, 10); INSERT INTO emp VALUES(7788, 'scott', '策划工作', 7566, '1987-07-03', 3000, NULL, 20); INSERT INTO emp VALUES(7839, 'king', '大BOSS', NULL, '1981-11-17', 5000, NULL, 10); INSERT INTO emp VALUES(7844, 'turner', '销售工作', 7698, '1981-09-08', 1500, 0, 30); INSERT INTO emp VALUES(7876, 'adams', '保洁工作', 7788, '1987-07-13', 1100, NULL, 20); INSERT INTO emp VALUES(7900, 'james', '保洁工作', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO emp VALUES(7902, 'ford', '策划工作', 7566, '1981-12-03', 3000, NULL, 20); INSERT INTO emp VALUES(7934, 'miller', '保洁工作', 7782, '1981-01-23', 1300, NULL, 10);

工资等级表

CREATE TABLE salgrade( grade INT, #等级 losal DOUBLE, #最低工资 hisal DOUBLE #最高工资 );

INSERT INTO salgrade VALUES (1, 700, 1200), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999);

查询示例：返回工资处于第四级别的员工的姓名

SELECT ename FROM emp WHERE sal BETWEEN (SELECT losal FROM salgrade WHERE grade=4) AND (SELECT hisal FROM salgrade WHERE grade=4);