数据库系统设计-SQL语句
题目：
假设我们要为宿舍管理系统设计一个数据库，这个数据库包含用户，学生，宿舍，住宿，报修等信息。请用SQLY语句实现创建数据库，创建表，注意表中要实现各种完整性如建立主键，外键，check约束等，最后向每个表中输入5行数据。
一、表结构及样例数据
1. 学生表 (Student)
表结构:
| 列名 | 数据类型 | 允许空值 | 说明 |
|---|---|---|---|
| sno | CHAR(8) | NO | 主键，唯一标识学生 |
| sname | CHAR(8) | NO | 学生姓名 |
| age | SMALLINT | YES | 学生年龄 |
| sex | CHAR(2) | YES | 性别，仅限“男”或“女” |
| sdept | VARCHAR(50) | YES | 学生所在的系别 |
样例数据:
| sno | sname | age | sex | sdept |
|---|---|---|---|---|
| 20210001 | 张三 | 20 | 男 | 计算机系 |
| 20210002 | 李四 | 21 | 女 | 电子信息系 |
| 20210003 | 王五 | 22 | 男 | 土木工程系 |
| 20210004 | 赵六 | 19 | 女 | 化学系 |
| 20210005 | 孙七 | 23 | 男 | 物理系 |
2. 宿舍表 (Dormitory)
表结构:
| 列名 | 数据类型 | 允许空值 | 说明 |
|---|---|---|---|
| dorm_id | CHAR(6) | NO | 主键，唯一标识宿舍 |
| capacity | SMALLINT | NO | 宿舍容量，必须大于0 |
| available | SMALLINT | YES | 当前可用床位数，限制范围在0到capacity之间 |
样例数据:
| dorm_id | capacity | available |
|---|---|---|
| A101 | 4 | 3 |
| A102 | 4 | 2 |
| B201 | 6 | 4 |
| B202 | 6 | 5 |
| C301 | 8 | 8 |
3. 住宿表 (Accommodation)
表结构:
| 列名 | 数据类型 | 允许空值 | 说明 |
|---|---|---|---|
| accomm_id | INT | NO | 主键，自增字段 |
| sno | CHAR(8) | NO | 外键，关联到学生表 |
| dorm_id | CHAR(6) | NO | 外键，关联到宿舍表 |
| start_date | DATE | NO | 入住宿舍的开始日期 |
| end_date | DATE | YES | 退宿日期，允许为空表示仍在住宿中 |
样例数据:
| accomm_id | sno | dorm_id | start_date | end_date |
|---|---|---|---|---|
| 1 | 20210001 | A101 | 2024-09-01 | NULL |
| 2 | 20210002 | A102 | 2024-09-01 | NULL |
| 3 | 20210003 | B201 | 2023-09-01 | 2024-07-01 |
| 4 | 20210004 | B202 | 2024-09-01 | NULL |
| 5 | 20210005 | C301 | 2024-08-15 | NULL |
4. 报修表 (Repair)
表结构:
| 列名 | 数据类型 | 允许空值 | 说明 |
|---|---|---|---|
| repair_id | INT | NO | 主键，自增字段 |
| dorm_id | CHAR(6) | NO | 外键，关联到宿舍表 |
| report_date | DATE | NO | 报修日期 |
| repair_description | VARCHAR(255) | NO | 报修描述，说明故障或维修需求 |
| status | VARCHAR(20) | YES | 报修状态，仅限“待处理”、“处理中”、“已完成” |
样例数据:
| repair_id | dorm_id | report_date | repair_description | status |
|---|---|---|---|---|
| 1 | A101 | 2024-10-01 | 空调不制冷 | 处理中 |
| 2 | A102 | 2024-09-15 | 门锁损坏 | 已完成 |
| 3 | B201 | 2024-08-20 | 水管漏水 | 待处理 |
| 4 | B202 | 2024-10-05 | 电灯不亮 | 处理中 |
| 5 | C301 | 2024-09-10 | 床铺损坏 | 已完成 |
5. 管理员表 (Admin)
表结构:
| 列名 | 数据类型 | 允许空值 | 说明 |
|---|---|---|---|
| admin_id | CHAR(6) | NO | 主键，唯一标识管理员 |
| admin_name | CHAR(8) | NO | 管理员姓名 |
| phone | VARCHAR(15) | NO | 管理员电话，仅限数字 |
| dorm_id | CHAR(6) | NO | 外键，关联到宿舍表，表示负责的宿舍 |
样例数据:
| admin_id | admin_name | phone | dorm_id |
|---|---|---|---|
| A001 | 王老师 | 13812345678 | A101 |
| A002 | 李老师 | 13987654321 | A102 |
| B001 | 张老师 | 13698765432 | B201 |
| B002 | 赵老师 | 13587654321 | B202 |
| C001 | 孙老师 | 13712345678 | C301 |
6. 访客登记表 (VisitorRegister)
表结构:
| 列名 | 数据类型 | 允许空值 | 说明 |
|---|---|---|---|
| visit_id | INT | NO | 主键，自增字段 |
| visitor_name | CHAR(8) | NO | 访客姓名 |
| sno | CHAR(8) | YES | 外键，关联到学生表 |
| visit_date | DATE | NO | 访问日期 |
| visit_purpose | VARCHAR(255) | NO | 访问目的 |
| admin_id | CHAR(6) | NO | 外键，关联到管理员表 |
样例数据:
| visit_id | visitor_name | sno | visit_date | visit_purpose | admin_id |
|---|---|---|---|---|---|
| 1 | 刘一 | 20210001 | 2024-10-01 | 探望朋友 | A001 |
| 2 | 陈二 | 20210002 | 2024-09-28 | 递送物品 | A002 |
| 3 | 张三 | 20210003 | 2024-10-02 | 参观校园 | B001 |
| 4 | 李四 | NULL | 2024-10-03 | 业务洽谈 | B002 |
| 5 | 王五 | 20210005 | 2024-10-05 | 学习交流 | C001 |
二、 创建数据库并使用
-- 创建数据库
CREATE DATABASE DormitoryManagement;
--使用数据库
USE DormitoryManagement;
三、创建表并插入数据（完整代码）
-- 创建学生表，包括学号、姓名、年龄、性别和所在系的信息
CREATE TABLE Student (
sno CHAR(8) PRIMARY KEY, -- 学号，主键
sname CHAR(8) NOT NULL, -- 姓名，不允许为空
age SMALLINT, -- 年龄
sex CHAR(2) CHECK (sex IN ('男', '女')), -- 性别，限制为“男”或“女”
sdept VARCHAR(50) -- 所在系，最多50个字符
);
-- 插入数据到学生表
INSERT INTO Student (sno, sname, age, sex, sdept) VALUES
('20210001', '张三', 20, '男', '计算机系'),
('20210002', '李四', 21, '女', '电子信息系'),
('20210003', '王五', 22, '男', '土木工程系'),
('20210004', '赵六', 19, '女', '化学系'),
('20210005', '孙七', 23, '男', '物理系');
-- 创建宿舍表，包括宿舍编号、最大容量和当前可用床位数
CREATE TABLE Dormitory (
dorm_id CHAR(6) PRIMARY KEY, -- 宿舍编号，主键
capacity SMALLINT CHECK (capacity > 0), -- 宿舍容量，必须大于0
available SMALLINT -- 当前可用床位数
);
-- 插入数据到宿舍表
INSERT INTO Dormitory (dorm_id, capacity, available) VALUES
('A101', 4, 3),
('A102', 4, 2),
('B201', 6, 4),
('B202', 6, 5),
('C301', 8, 8);
-- 创建住宿表，记录学生住宿情况，包括住宿ID、学号、宿舍编号、入住日期和退宿日期
CREATE TABLE Accommodation (
accomm_id INT IDENTITY PRIMARY KEY, -- 住宿ID，自增主键
sno CHAR(8) FOREIGN KEY REFERENCES Student(sno), -- 学号，外键
dorm_id CHAR(6) FOREIGN KEY REFERENCES Dormitory(dorm_id), -- 宿舍编号，外键
start_date DATE NOT NULL, -- 入住日期，不允许为空
end_date DATE -- 退宿日期
);
-- 插入数据到住宿表
INSERT INTO Accommodation (sno, dorm_id, start_date, end_date) VALUES
('20210001', 'A101', '2024-09-01', NULL),
('20210002', 'A102', '2024-09-01', NULL),
('20210003', 'B201', '2023-09-01', '2024-07-01'),
('20210004', 'B202', '2024-09-01', NULL),
('20210005', 'C301', '2024-08-15', NULL);
-- 创建报修表，记录宿舍的报修情况，包括报修ID、宿舍编号、报修日期、报修描述和状态
CREATE TABLE Repair (
repair_id INT IDENTITY PRIMARY KEY, -- 报修ID，自增主键
dorm_id CHAR(6) FOREIGN KEY REFERENCES Dormitory(dorm_id), -- 宿舍编号，外键
report_date DATE NOT NULL, -- 报修日期，不允许为空
repair_description VARCHAR(255) NOT NULL, -- 报修描述，不允许为空
status VARCHAR(20) CHECK (status IN ('待处理', '处理中', '已完成')) -- 报修状态，限定为待处理、处理中或已完成
);
-- 插入数据到报修表
INSERT INTO Repair (dorm_id, report_date, repair_description, status) VALUES
('A101', '2024-10-01', '空调不制冷', '处理中'),
('A102', '2024-09-15', '门锁损坏', '已完成'),
('B201', '2024-08-20', '水管漏水', '待处理'),
('B202', '2024-10-05', '电灯不亮', '处理中'),
('C301', '2024-09-10', '床铺损坏', '已完成');
-- 创建管理员表，包括管理员ID、姓名、电话号码和管理的宿舍编号
CREATE TABLE Admin (
admin_id CHAR(6) PRIMARY KEY, -- 管理员ID，主键
admin_name CHAR(8) NOT NULL, -- 管理员姓名，不允许为空
phone VARCHAR(15) CHECK (phone NOT LIKE '%[^0-9]%'), -- 电话号码，只允许数字
dorm_id CHAR(6) FOREIGN KEY REFERENCES Dormitory(dorm_id) -- 管理的宿舍编号，外键
);
-- 插入数据到管理员表
INSERT INTO Admin (admin_id, admin_name, phone, dorm_id) VALUES
('A001', '王老师', '13812345678', 'A101'),
('A002', '李老师', '13987654321', 'A102'),
('B001', '张老师', '13698765432', 'B201'),
('B002', '赵老师', '13587654321', 'B202'),
('C001', '孙老师', '13712345678', 'C301');
-- 创建访客登记表，记录访客的访问情况，包括访客ID、访客姓名、学号、访问日期、访问目的和管理员ID
CREATE TABLE VisitorRegister (
visit_id INT IDENTITY PRIMARY KEY, -- 访客ID，自增主键
visitor_name CHAR(8) NOT NULL, -- 访客姓名，不允许为空
sno CHAR(8) FOREIGN KEY REFERENCES Student(sno), -- 学号，外键
visit_date DATE NOT NULL, -- 访问日期，不允许为空
visit_purpose VARCHAR(255) NOT NULL, -- 访问目的，不允许为空
admin_id CHAR(6) FOREIGN KEY REFERENCES Admin(admin_id) -- 管理员ID，外键
);
-- 插入数据到访客登记表
INSERT INTO VisitorRegister (visitor_name, sno, visit_date, visit_purpose, admin_id) VALUES
('刘一', '20210001', '2024-10-01', '探望朋友', 'A001'),
('陈二', '20210002', '2024-09-28', '递送物品', 'A002'),
('张三', '20210003', '2024-10-02', '参观校园', 'B001'),
('李四', NULL, '2024-10-03', '业务洽谈', 'B002'),
('王五', '20210005', '2024-10-05', '学习交流', 'C001');
-- 分离创建触发器的语句，以确保触发器的创建在单独的查询批次中进行
GO
-- 创建触发器，检查宿舍的available列的值范围
CREATE TRIGGER trg_check_available
ON Dormitory
AFTER INSERT, UPDATE
AS
BEGIN
-- 检查available是否在0到capacity之间
IF EXISTS (
SELECT 1
FROM inserted
WHERE available < 0 OR available > capacity
)
BEGIN
-- 如果available的值超出范围，触发错误并回滚事务
RAISERROR('The available value must be between 0 and capacity.', 16, 1);
ROLLBACK TRANSACTION;
END
END;
三、创建表并插入数据（拆分）
1. 创建表
学生表（Student）
-- 创建学生表，包括学号、姓名、年龄、性别和所在系的信息
CREATE TABLE Student (
sno CHAR(8) PRIMARY KEY, -- 学号，主键
sname CHAR(8) NOT NULL, -- 姓名，不允许为空
age SMALLINT, -- 年龄
sex CHAR(2) CHECK (sex IN ('男', '女')), -- 性别，限制为“男”或“女”
sdept VARCHAR(50) -- 所在系，最多50个字符
);
宿舍表（Dormitory）
-- 创建宿舍表，包括宿舍编号、最大容量和当前可用床位数
CREATE TABLE Dormitory (
dorm_id CHAR(6) PRIMARY KEY, -- 宿舍编号，主键
capacity SMALLINT CHECK (capacity > 0), -- 宿舍容量，必须大于0
available SMALLINT -- 当前可用床位数
);
触发器说明
- trg_check_available: 用于在插入或更新
Dormitory表时，确保available（可用床位数）在0到capacity（宿舍容量）之间。 - (创建触发器，实现“available在0到capacity之间”)，该代码放在最后以确保触发器的创建在单独的查询批次中进行：
-- 分离创建触发器的语句，以确保触发器的创建在单独的查询批次中进行
GO
-- 创建触发器，检查宿舍的available列的值范围
CREATE TRIGGER trg_check_available
ON Dormitory
AFTER INSERT, UPDATE
AS
BEGIN
-- 检查available是否在0到capacity之间
IF EXISTS (
SELECT 1
FROM inserted
WHERE available < 0 OR available > capacity
)
BEGIN
-- 如果available的值超出范围，触发错误并回滚事务
RAISERROR('The available value must be between 0 and capacity.', 16, 1);
ROLLBACK TRANSACTION;
END
END;
住宿表（Accommodation）
-- 创建住宿表，记录学生住宿情况，包括住宿ID、学号、宿舍编号、入住日期和退宿日期
CREATE TABLE Accommodation (
accomm_id INT IDENTITY PRIMARY KEY, -- 住宿ID，自增主键
sno CHAR(8) FOREIGN KEY REFERENCES Student(sno), -- 学号，外键
dorm_id CHAR(6) FOREIGN KEY REFERENCES Dormitory(dorm_id), -- 宿舍编号，外键
start_date DATE NOT NULL, -- 入住日期，不允许为空
end_date DATE -- 退宿日期
);
报修表（Repair）
-- 创建报修表，记录宿舍的报修情况，包括报修ID、宿舍编号、报修日期、报修描述和状态
CREATE TABLE Repair (
repair_id INT IDENTITY PRIMARY KEY, -- 报修ID，自增主键
dorm_id CHAR(6) FOREIGN KEY REFERENCES Dormitory(dorm_id), -- 宿舍编号，外键
report_date DATE NOT NULL, -- 报修日期，不允许为空
repair_description VARCHAR(255) NOT NULL, -- 报修描述，不允许为空
status VARCHAR(20) CHECK (status IN ('待处理', '处理中', '已完成')) -- 报修状态，限定为待处理、处理中或已完成
);
管理员表（Admin）
-- 创建管理员表，包括管理员ID、姓名、电话号码和管理的宿舍编号
CREATE TABLE Admin (
admin_id CHAR(6) PRIMARY KEY, -- 管理员ID，主键
admin_name CHAR(8) NOT NULL, -- 管理员姓名，不允许为空
phone VARCHAR(15) CHECK (phone NOT LIKE '%[^0-9]%'), -- 电话号码，只允许数字
dorm_id CHAR(6) FOREIGN KEY REFERENCES Dormitory(dorm_id) -- 管理的宿舍编号，外键
);
访客登记表（VisitorRegister）
-- 创建访客登记表，记录访客的访问情况，包括访客ID、访客姓名、学号、访问日期、访问目的和管理员ID
CREATE TABLE VisitorRegister (
visit_id INT IDENTITY PRIMARY KEY, -- 访客ID，自增主键
visitor_name CHAR(8) NOT NULL, -- 访客姓名，不允许为空
sno CHAR(8) FOREIGN KEY REFERENCES Student(sno), -- 学号，外键
visit_date DATE NOT NULL, -- 访问日期，不允许为空
visit_purpose VARCHAR(255) NOT NULL, -- 访问目的，不允许为空
admin_id CHAR(6) FOREIGN KEY REFERENCES Admin(admin_id) -- 管理员ID，外键
);
2. 插入数据
向学生表插入数据
-- 插入数据到学生表
INSERT INTO Student (sno, sname, age, sex, sdept) VALUES
('20210001', '张三', 20, '男', '计算机系'),
('20210002', '李四', 21, '女', '电子信息系'),
('20210003', '王五', 22, '男', '土木工程系'),
('20210004', '赵六', 19, '女', '化学系'),
('20210005', '孙七', 23, '男', '物理系');
向宿舍表插入数据
-- 插入数据到宿舍表
INSERT INTO Dormitory (dorm_id, capacity, available) VALUES
('A101', 4, 3),
('A102', 4, 2),
('B201', 6, 4),
('B202', 6, 5),
('C301', 8, 8);
向住宿表插入数据
-- 插入数据到住宿表
INSERT INTO Accommodation (sno, dorm_id, start_date, end_date) VALUES
('20210001', 'A101', '2024-09-01', NULL),
('20210002', 'A102', '2024-09-01', NULL),
('20210003', 'B201', '2023-09-01', '2024-07-01'),
('20210004', 'B202', '2024-09-01', NULL),
('20210005', 'C301', '2024-08-15', NULL);
向报修表插入数据
-- 插入数据到报修表
INSERT INTO Repair (dorm_id, report_date, repair_description, status) VALUES
('A101', '2024-10-01', '空调不制冷', '处理中'),
('A102', '2024-09-15', '门锁损坏', '已完成'),
('B201', '2024-08-20', '水管漏水', '待处理'),
('B202', '2024-10-05', '电灯不亮', '处理中'),
('C301', '2024-09-10', '床铺损坏', '已完成');
向管理员表插入数据
-- 插入数据到管理员表
INSERT INTO Admin (admin_id, admin_name, phone, dorm_id) VALUES
('A001', '王老师', '13812345678', 'A101'),
('A002', '李老师', '13987654321', 'A102'),
('B001', '张老师', '13698765432', 'B201'),
('B002', '赵老师', '13587654321', 'B202'),
('C001', '孙老师', '13712345678', 'C301');
向访客登记表插入数据
-- 插入数据到访客登记表
INSERT INTO VisitorRegister (visitor_name, sno, visit_date, visit_purpose, admin_id) VALUES
('刘一', '20210001', '2024-10-01', '探望朋友', 'A001'),
('陈二', '20210002', '2024-09-28', '递送物品', 'A002'),
('张三', '20210003', '2024-10-02', '参观校园', 'B001'),
('李四', NULL, '2024-10-03', '业务洽谈', 'B002'),
('王五', '20210005', '2024-10-05', '学习交流', 'C001');
原文地址：https://blog.csdn.net/m0_74102736/article/details/142959393
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！