相关数据

1.建表语句

-- 部门信息表
CREATE TABLE IF NOT EXISTS dept(
deptno INT COMMENT'部门编号',
dname VARCHAR(20) COMMENT'部门名称',
loc VARCHAR(10) COMMENT'部门位置'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT'部门信息表';

-- 员工信息表
CREATE TABLE IF NOT EXISTS emp(
empno INT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT  '员工编号',
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(10) COMMENT '职位',
mgr INT COMMENT '经理',
sal INT COMMENT '薪水',
deptno INT COMMENT '部门'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT'员工信息表';

-- 工资等级
CREATE TABLE IF NOT EXISTS salgrade (
grade INT COMMENT '等级',
lowsal INT COMMENT '最低',
hisal INT COMMENT '最高'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '工资等级表';

 

2.插数据

-- 员工数据
INSERT INTO emp VALUE (1001,'甄氏','文员',1013,8000,20);
INSERT INTO emp VALUE (1002,'吕蒙','销售员',1006,16000,20);
INSERT INTO emp VALUE (1003,'马超','销售员',1006,12500,30);
INSERT INTO emp VALUE (1004,'陆逊','经理',1009,29750,20);
INSERT INTO emp VALUE (1005,'赵云','销售员',1006,12500,30);
INSERT INTO emp VALUE (1006,'张飞','经理',1009,28500,30);
INSERT INTO emp VALUE (1007,'关羽','经理',1009,24500,10);
INSERT INTO emp VALUE (1008,'诸葛亮','分析师',1004,30000,20);
INSERT INTO emp VALUE (1009,'刘备','董事长',NULL,50000,10);
INSERT INTO emp VALUE (1010,'魏延','销售员',1006,15000,30);
INSERT INTO emp VALUE (1011,'貂蝉','文员',1008,11000,30);
INSERT INTO emp VALUE (1012,'小乔','文员',1006,9500,30);
INSERT INTO emp VALUE (1013,'庞统','分析师',1004,30000,10);
INSERT INTO emp VALUE (1014,'大乔','文员',1007,13000,10);
INSERT INTO emp VALUE (1015,'华佗','保洁员',1001,8000,50);

 

-- 工资等级数据
insert into salgrade value (1,7000,12000);
insert into salgrade value (2,12001,14000);
insert into salgrade value (3,14001,20000);
insert into salgrade value (4,20001,30000);
insert into salgrade value (5,30001,90000);

 

-- 部门信息数据 
insert into dept value (10,'教研部','北京');
insert into dept value (20,'工学部','上海');
insert into dept value (30,'销售部','深圳');
insert into dept value (40,'财务部','广州');
insert into dept value (50,'董事会','太原');