MySQL学习之创建表—部门，员工，工资，奖金表

最新推荐文章于 2024-01-20 16:24:45 发布

原创最新推荐文章于 2024-01-20 16:24:45 发布 · 8.7k 阅读

49 ·

CC 4.0 BY-SA版权

zzz

文章标签：

#mysql #数据库 #1024程序员节

MySQL 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了如何使用SQL创建部门表、员工表和薪资等级表，通过外键关联实现数据一致性，并演示了数据插入、查询和表结构管理。核心内容包括数据库设计、员工信息管理与薪酬计算。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 部门表：
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), -- 上级领导 自身表 自关联
	hiredate date,
	sal double(7,2),
	comm double(7,2), -- 补助
	deptno int(2)
);

-- 设置外键：
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(18),
	job varchar(9),
	sal double(7,2),
	comm double(7,2)
);

-- 插入数据：

insert into dept (deptno,dname,loc) values(10,'accounting','new york');
insert into dept (deptno,dname,loc) values(20,'research','dallas');
insert into dept (deptno,dname,loc) values(40,'sales','chicago');
insert into dept (deptno,dname,loc) values(30,'oprations','boston');

INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7369, 'smith', 'clerk', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7499, 'allen', 'salesman', 7698, '1982-11-12', 1600.00, 300.00, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7521, 'ward', 'salesman', 7698, '1981-03-13', 1250.00, 500.00, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7566, 'jones', 'manager', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7654, 'martin', 'salesman', 7698, '1980-01-10', 1250.00, 1400.00, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7698, 'blake', 'manager', 7839, '1984-05-14', 2850.00, NULL, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7782, 'clark', 'manager', 7839, '1983-07-02', 2450.00, NULL, 10);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7788, 'scott', 'analyst', 7566, '1986-02-04', 3000.00, NULL, 20);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7839, 'king', 'president', NULL, '1987-11-02', 5000.00, NULL, 10);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7844, 'turner', 'salesman', 7698, '1983-05-03', 1500.00, 0.00, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7875, 'adams', 'clerk', 7788, '1984-11-12', 1100.00, NULL, 20);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7900, 'jamesford', 'clerk', 7698, '1987-12-11', 950.00, NULL, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7902, 'analyst', 'analyst', 7566, '1980-12-17', 3000.00, NULL, 20);


insert into salgrade(grade,losal,hisal) values(1,700,1200);
insert into salgrade(grade,losal,hisal) values(2,1201,1400);
insert into salgrade(grade,losal,hisal) values(3,1401,2000);
insert into salgrade(grade,losal,hisal) values(4,2001,3000);
insert into salgrade(grade,losal,hisal) values(5,3001,9999);


-- 查看表：
select * from dept;
select * from emp;
select * from salgrade;
select * from bonus;

show create table emp;
desc emp;