sql数据库学校的工资管理系统。实现部门、职务、职称等基本信息的管理； 实现教职工信息的管理； 实现工资项目的管理，工资项目设有启用标志和加扣标志； 实现教职工工资项目及其工资的管理； 创建触发器当往教职工工资项目表中插入记录或删除记录时，自动修改该职工的应发工资数和实发工资数； 创建存储过程统计某个月各种工资项目的发放总和； 创建视图查询各个员工的应发、应扣和实发工资； 建立数据库相关表之间的参照完整性约束。给出sql语句

以下是实现上述功能的 SQL 语句：

1. 部门、职务、职称等基本信息表

- 部门表

CREATE TABLE department (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);

- 职务表

CREATE TABLE position (
  pos_id INT PRIMARY KEY,
  pos_name VARCHAR(50) NOT NULL
);

- 职称表

CREATE TABLE title (
  title_id INT PRIMARY KEY,
  title_name VARCHAR(50) NOT NULL
);

2. 教职工信息表

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. 工资项目表

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. 教职工工资项目表

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. 创建触发器

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. 创建存储过程

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. 创建视图

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. 建立参照完整性约束

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);