目录
表的准备:dept(部门表)、emp(员工表)、salgrade(薪资等级表)、bonus(奖金表)
-- 表的准备:dept(部门表)、emp(员工表)、salgrade(薪资等级表)、bonus(奖金表)
##建立部门表
create table dept(
deptno int(2) not null,-- 部门编号
dname VARCHAR(14),-- 部门名称
loc VARCHAR(13)-- 部门的位置
);
##给部门表添加主键
alter table dept
add constraint pk_dept primary key (deptno);
##建立员工表
create table emp(
empno int(4) primary key,-- 员工编号
ename VARCHAR(10),-- 员工名字
job VARCHAR(9),-- 员工职位
MGR int(4),-- 员工的上级领导编号 外键:参考自身表即emp的empno字段,产生自关联
HIREDATE date,-- 入职日期
sal double(7,2),-- 工资
comm double(7,2),-- 当月补助
deptno int(2)-- 外键:参考dept表中deptno字段
);
##给员工表添加外键,关联dept表中的deptno字段
alter table emp
add CONSTRAINT fk_deptno FOREIGN KEY (deptno)
REFERENCES dept(deptno);
##创建工资等级表
create table salgrade(
grade int PRIMARY KEY,-- 等级
losal double(7,2),-- 最低
hisal double(7,2)-- 最高
);
##创建奖金表
create table bonus(
ename VARCHAR(10),
job VARCHAR(9),
sal double(7,2),
comm double(7,2)
);
##在部门表中插入数据
insert into dept(deptno,dname,loc) VALUES(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');
##在员工表中插入数据
insert into emp(empno,ename,job,MGR,HIREDATE,sal,comm,deptno)
VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20),
(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30),
(7902,'FORO','ANALYST',7566,'1981-12-03',3000,null,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);
##在薪资等级表中插入数据
INSERT into salgrade(grade,losal,hisal)values(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);
select * from dept;
select * from emp;
select * from salgrade;
select * from bonus;
dept表
emp表
salgrade表
一、最简单的SQL语句: