使用sql语言设计一款学生住宿服务系统 要求有视图、存储过程、触发器等1）宿舍基本信息管理：包括宿舍编号，楼层，宿舍可容纳的人数，宿舍的 朝向、住宿的人数，住宿的学生详细信息、宿舍电话，学生交纳住宿费用等基本信息。 2）基本的统计功能：如某个宿舍楼内的住宿人数，某个学院所居住的宿舍 编号、某一年级所居住的宿舍编号等等。 3）住宿信息查询 4）住宿调整管理

下面是一个基本的学生住宿服务系统的 SQL 设计：

1. 宿舍基本信息管理表 dormitory_info

| 列名 | 数据类型 | 约束 |
| --------------- | ------------ | -------------- |
| dormitory_id | int | 主键，自增长 |
| floor | int | |
| capacity | int | |
| orientation | varchar(20) | |
| current_number | int | |
| dormitory_phone | varchar(20) | |
| payment | decimal(10,2) | |

2. 学生详细信息表 student_info

| 列名 | 数据类型 | 约束 |
| -------------- | ----------- | -------------- |
| student_id | int | 主键，自增长 |
| dormitory_id | int | 外键，关联 dormitory_info.dormitory_id |
| name | varchar(50) | |
| gender | varchar(10) | |
| major | varchar(50) | |
| grade | varchar(10) | |
| student_phone | varchar(20) | |
| check_in_date | date | |
| check_out_date | date | |

3. 视图：统计宿舍楼内的住宿人数

CREATE VIEW dormitory_population AS
SELECT dormitory_info.dormitory_id, COUNT(student_info.student_id) AS population
FROM dormitory_info
LEFT JOIN student_info ON dormitory_info.dormitory_id = student_info.dormitory_id
GROUP BY dormitory_info.dormitory_id;

4. 视图：统计某个学院所居住的宿舍编号

CREATE VIEW dormitory_by_college AS
SELECT dormitory_info.dormitory_id, student_info.major
FROM dormitory_info
LEFT JOIN student_info ON dormitory_info.dormitory_id = student_info.dormitory_id
WHERE student_info.major = 'xxx';

5. 触发器：住宿信息插入时更新宿舍当前人数

CREATE TRIGGER update_dormitory_number AFTER INSERT ON student_info
FOR EACH ROW
BEGIN
    UPDATE dormitory_info SET current_number = current_number + 1 WHERE dormitory_id = NEW.dormitory_id;
END;

6. 存储过程：住宿调整

CREATE PROCEDURE change_dormitory(IN student_id INT, IN new_dormitory_id INT)
BEGIN
    UPDATE student_info SET dormitory_id = new_dormitory_id WHERE student_id = student_id;
    UPDATE dormitory_info SET current_number = current_number + 1 WHERE dormitory_id = new_dormitory_id;
    UPDATE dormitory_info SET current_number = current_number - 1 WHERE dormitory_id = OLD.dormitory_id;
END;

以上是一个基本的学生住宿服务系统的 SQL 设计，可以根据实际需求进行修改和完善。