sql数据库创建部门表、职务表、职称表
时间: 2023-07-10 21:15:22 浏览: 38
好的,下面是创建这三个表的 SQL 语句:
1. 部门表:
```
CREATE TABLE department (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
manager_id INT,
description TEXT
);
```
2. 职务表:
```
CREATE TABLE job (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
description TEXT
);
```
3. 职称表:
```
CREATE TABLE title (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
description TEXT
);
```
以上是基本的表结构,具体的字段和属性可以根据实际需求进行调整。
相关问题
sql数据库学校的工资管理系统。实现部门、职务、职称等基本信息的管理; 实现教职工信息的管理; 实现工资项目的管理,工资项目设有启用标志和加扣标志; 实现教职工工资项目及其工资的管理; 创建触发器当往教职工工资项目表中插入记录或删除记录时,自动修改该职工的应发工资数和实发工资数; 创建存储过程统计某个月各种工资项目的发放总和; 创建视图查询各个员工的应发、应扣和实发工资; 建立数据库相关表之间的参照完整性约束。给出sql语句
以下是实现上述功能的 SQL 语句:
1. 部门、职务、职称等基本信息表
- 部门表
```sql
CREATE TABLE department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
```
- 职务表
```sql
CREATE TABLE position (
pos_id INT PRIMARY KEY,
pos_name VARCHAR(50) NOT NULL
);
```
- 职称表
```sql
CREATE TABLE title (
title_id INT PRIMARY KEY,
title_name VARCHAR(50) NOT NULL
);
```
2. 教职工信息表
```sql
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
gender VARCHAR(10) NOT NULL,
birthdate DATE NOT NULL,
hiredate DATE NOT NULL,
dept_id INT,
pos_id INT,
title_id INT,
FOREIGN KEY (dept_id) REFERENCES department(dept_id),
FOREIGN KEY (pos_id) REFERENCES position(pos_id),
FOREIGN KEY (title_id) REFERENCES title(title_id)
);
```
3. 工资项目表
```sql
CREATE TABLE salary_item (
item_id INT PRIMARY KEY,
item_name VARCHAR(50) NOT NULL,
enable_flag INT NOT NULL,
add_sub_flag INT NOT NULL
);
```
4. 教职工工资项目表
```sql
CREATE TABLE emp_salary_item (
emp_id INT NOT NULL,
item_id INT NOT NULL,
salary DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (emp_id) REFERENCES employee(emp_id),
FOREIGN KEY (item_id) REFERENCES salary_item(item_id),
PRIMARY KEY (emp_id, item_id)
);
```
5. 创建触发器
```sql
CREATE TRIGGER update_salary
AFTER INSERT OR DELETE ON emp_salary_item
FOR EACH ROW
BEGIN
DECLARE total_salary DECIMAL(10, 2);
DECLARE emp_salary DECIMAL(10, 2);
SELECT SUM(salary) INTO total_salary FROM emp_salary_item WHERE emp_id = NEW.emp_id;
UPDATE employee SET total_salary = total_salary WHERE emp_id = NEW.emp_id;
SELECT total_salary INTO emp_salary FROM employee WHERE emp_id = NEW.emp_id;
UPDATE employee SET actual_salary = emp_salary WHERE emp_id = NEW.emp_id;
END;
```
6. 创建存储过程
```sql
CREATE PROCEDURE calculate_salary(IN month INT)
BEGIN
SELECT item_name, SUM(salary)
FROM emp_salary_item
JOIN salary_item ON emp_salary_item.item_id = salary_item.item_id
JOIN employee ON emp_salary_item.emp_id = employee.emp_id
WHERE MONTH(hiredate) <= month
GROUP BY item_name;
END;
```
7. 创建视图
```sql
CREATE VIEW emp_salary AS
SELECT emp_id, emp_name,
SUM(CASE WHEN add_sub_flag = 1 THEN salary ELSE 0 END) AS salary_add,
SUM(CASE WHEN add_sub_flag = -1 THEN salary ELSE 0 END) AS salary_sub,
SUM(salary) AS salary_total
FROM emp_salary_item
JOIN employee ON emp_salary_item.emp_id = employee.emp_id
JOIN salary_item ON emp_salary_item.item_id = salary_item.item_id
GROUP BY emp_id, emp_name;
```
8. 建立参照完整性约束
```sql
ALTER TABLE employee ADD CONSTRAINT dept_fk FOREIGN KEY (dept_id) REFERENCES department(dept_id);
ALTER TABLE employee ADD CONSTRAINT pos_fk FOREIGN KEY (pos_id) REFERENCES position(pos_id);
ALTER TABLE employee ADD CONSTRAINT title_fk FOREIGN KEY (title_id) REFERENCES title(title_id);
ALTER TABLE emp_salary_item ADD CONSTRAINT emp_fk FOREIGN KEY (emp_id) REFERENCES employee(emp_id);
ALTER TABLE emp_salary_item ADD CONSTRAINT item_fk FOREIGN KEY (item_id) REFERENCES salary_item(item_id);
```
请用sql语言写出下列要求。学校人力资源管理系统 实现学校部门信息,职务、职称和教职工信息管理; 实现教师的学籍经历管理; 实现教师的家庭关系管理; 实现教师的奖惩信息管理; 创建存储过程查询学校各部门各种职称的教职工数量; 创建触发器,当增加、删除教职工和修改教职工部门信息时自动修改相应部门的职工人数; 创建规则用于保证教职工的E-Mail的输入格式正确; 建立数据库相关表之间的参照完整性约束。
下面是针对学校人力资源管理系统的SQL语句:
1.创建部门信息表
```sql
CREATE TABLE department (
dep_id INT PRIMARY KEY,
dep_name VARCHAR(50) NOT NULL,
dep_manager VARCHAR(50) NOT NULL
);
```
2.创建职务信息表
```sql
CREATE TABLE position (
pos_id INT PRIMARY KEY,
pos_name VARCHAR(50) NOT NULL
);
```
3.创建职称信息表
```sql
CREATE TABLE title (
title_id INT PRIMARY KEY,
title_name VARCHAR(50) NOT NULL
);
```
4.创建教职工信息表
```sql
CREATE TABLE teacher (
teacher_id INT PRIMARY KEY,
teacher_name VARCHAR(50) NOT NULL,
teacher_email VARCHAR(50) NOT NULL,
teacher_dep_id INT NOT NULL,
teacher_pos_id INT NOT NULL,
teacher_title_id INT NOT NULL,
FOREIGN KEY (teacher_dep_id) REFERENCES department(dep_id),
FOREIGN KEY (teacher_pos_id) REFERENCES position(pos_id),
FOREIGN KEY (teacher_title_id) REFERENCES title(title_id)
);
```
5.创建学籍经历信息表
```sql
CREATE TABLE academic (
academic_id INT PRIMARY KEY,
academic_name VARCHAR(50) NOT NULL,
academic_start_date DATE NOT NULL,
academic_end_date DATE NOT NULL,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);
```
6.创建家庭关系信息表
```sql
CREATE TABLE family (
family_id INT PRIMARY KEY,
family_name VARCHAR(50) NOT NULL,
family_relation VARCHAR(50) NOT NULL,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);
```
7.创建奖惩信息表
```sql
CREATE TABLE reward_punish (
rp_id INT PRIMARY KEY,
rp_content VARCHAR(50) NOT NULL,
rp_date DATE NOT NULL,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);
```
8.创建存储过程查询学校各部门各种职称的教职工数量
```sql
CREATE PROCEDURE teacher_count()
BEGIN
SELECT dep_name, pos_name, title_name, COUNT(teacher_id)
FROM teacher
INNER JOIN department ON teacher_dep_id = dep_id
INNER JOIN position ON teacher_pos_id = pos_id
INNER JOIN title ON teacher_title_id = title_id
GROUP BY dep_name, pos_name, title_name;
END;
```
9.创建触发器,当增加、删除教职工和修改教职工部门信息时自动修改相应部门的职工人数
```sql
CREATE TRIGGER teacher_count_update
AFTER INSERT ON teacher
FOR EACH ROW
BEGIN
UPDATE department
SET dep_manager = dep_manager,
COUNT = COUNT + 1
WHERE dep_id = NEW.teacher_dep_id;
END;
CREATE TRIGGER teacher_count_delete
AFTER DELETE ON teacher
FOR EACH ROW
BEGIN
UPDATE department
SET dep_manager = dep_manager,
COUNT = COUNT - 1
WHERE dep_id = OLD.teacher_dep_id;
END;
CREATE TRIGGER teacher_count_update
AFTER UPDATE ON teacher
FOR EACH ROW
BEGIN
UPDATE department
SET dep_manager = dep_manager,
COUNT = COUNT + 1
WHERE dep_id = NEW.teacher_dep_id;
UPDATE department
SET dep_manager = dep_manager,
COUNT = COUNT - 1
WHERE dep_id = OLD.teacher_dep_id;
END;
```
10.创建规则用于保证教职工的E-Mail的输入格式正确
```sql
CREATE RULE email_rule AS
ON INSERT TO teacher
WHERE NOT (teacher_email LIKE '%@%.%')
DO INSTEAD
RAISE EXCEPTION 'Invalid email format';
```
11.建立数据库相关表之间的参照完整性约束
```sql
ALTER TABLE teacher
ADD FOREIGN KEY (teacher_dep_id) REFERENCES department(dep_id) ON DELETE CASCADE;
ALTER TABLE teacher
ADD FOREIGN KEY (teacher_pos_id) REFERENCES position(pos_id) ON DELETE CASCADE;
ALTER TABLE teacher
ADD FOREIGN KEY (teacher_title_id) REFERENCES title(title_id) ON DELETE CASCADE;
ALTER TABLE academic
ADD FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE CASCADE;
ALTER TABLE family
ADD FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE CASCADE;
ALTER TABLE reward_punish
ADD FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE CASCADE;
```