学生宿舍管理系统sql语句
时间: 2023-11-21 19:44:11 浏览: 64
以下是一个简单的学生宿舍管理系统的 SQL 语句示例:
创建学生表:
```
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
gender VARCHAR(10),
age INT,
dormitory_id INT,
FOREIGN KEY (dormitory_id) REFERENCES dormitories(id)
);
```
创建宿舍表:
```
CREATE TABLE dormitories (
id INT PRIMARY KEY,
name VARCHAR(50),
capacity INT
);
```
插入学生数据:
```
INSERT INTO students (id, name, gender, age, dormitory_id)
VALUES
(1, '张三', '男', 20, 1),
(2, '李四', '男', 19, 1),
(3, '王五', '女', 21, 2),
(4, '赵六', '女', 20, 2);
```
插入宿舍数据:
```
INSERT INTO dormitories (id, name, capacity)
VALUES
(1, '男生宿舍1', 2),
(2, '女生宿舍1', 2);
```
查询宿舍的所有学生:
```
SELECT dormitories.name, students.name
FROM dormitories
JOIN students ON dormitories.id = students.dormitory_id;
```
查询某个宿舍的空床位数:
```
SELECT dormitories.name, dormitories.capacity - COUNT(students.id) AS available_beds
FROM dormitories
LEFT JOIN students ON dormitories.id = students.dormitory_id
WHERE dormitories.id = 1;
```