相关数据
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,'董事会','太原');